Sitemap

Amicuk Programming Answers

Database performance degrade - delete operation

-0001-11-30   Views:0

Advertisement

Hi, I have a big database. One of the table contains 120 milion records and many tables (more than 50) has referential integrity to this table. Table structure Customer (Cust_ID, and other columns). Cust_ID is Primary Key. Other tables has referentia

Hi,
I have a big database. One of the table contains 120 milion records and many tables (more than 50) has referential integrity to this table.
Table structure
Customer (Cust_ID, and other columns). Cust_ID is Primary Key. Other tables has referential integrity to Customer.Cust_ID.
There are around 100 thousand records that have entry only in this (Customer) table. These records are identified and kept in a table Temp_cust(Cust_ID).
I am running a PL/SQL which fetches a Cust_ID from Temp_cust and delete that record from Customer.
It is observed that a delete command takes long time and the whole system performance degrades. Even a on-line service that inserts row into this table looks like almost in hand stage.
The system is 24/7 and I have no option to disable any constraint.
Can someone explain why such a simple operation degrades system performance. Please also suggest how to complete the operation without affecting performance of other operations.
Regards
Karim

The replay answer
Advertisement
Hi antti.koskinen
There is no on delete rule. All are simple
referential integrity.
Like REFERS CUSTOMER (Cust_ID).
Regards,
KarimCan you run the following snippet just to make sure (params are name and owner of the Customer table):
select table_name, constraint_name, constraint_type, delete_rule
from dba_constraints
where r_constraint_name in
       select constraint_name
       from dba_constraints
       where owner = upper('&owner')
       and table_name = upper('&table_name')
       and constraint_type = 'P'
/Also check the last time the table was rebuilt - deletes w/o rebuilds tend to raise the high water mark.

Go to See the other 8 answers

Database performance degrade - delete operation

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I have a big database. One of the table contains 120 milion records and many tables (more than 50) has referential integrity to this table. Table structure Customer (Cust_ID, and other columns). Cust_ID is Primary Key. Other tables has referentia[More]

On performing continuous delete operation database hangs

Category:DefaultRelease time:-0001-11-30Views:130

Dear All, On performing continuous delete operation , the instance on which delete operation is performed database hangs i.e it does not allows to make new connection and some times on alert log we get ORA-3136 error Database version 10.2.0.3 , OS :[More]

How Increase performance of delete operation

Category:DefaultRelease time:-0001-11-30Views:130

Hi, How Increase performance of delete operation. This delete is done on a table which has around millions of records and loaded back every day . The statement is in a procedure and is as follows. #$%%$#$; commit; delete from TVRBC_SITE_ROLLUP_T; com[More]

Database performance degradation issue

Category:DefaultRelease time:-0001-11-30Views:130

Hi, We are having the database performance related problem. Oracle database 8.1.7.0 when we use statement, SQL> select name,value from v$sysstat where name ='redo buffer allocation retries'; NAME VALUE redo buffer allocation retries 2540 Here, Redo r[More]

SCOM reports "A significant portion of the database buffer cache has been written out to the system paging file. This may result in severe performance degradation"

Category:DefaultRelease time:-0001-11-30Views:130

This was discussed here, with no resolution http://social.technet.microsoft.com/Forums/en-US/exchange2010/thread/bb073c59-b88f-471b-a209-d7b5d9e5aa28?prof=required I have the same issue.  This is a single-purpose physical mailbox server with 320 user[More]

ORA-01456 : may not perform insert/delete/update operation

Category:DefaultRelease time:-0001-11-30Views:130

When I use following stored procedure with crystal reports, following error occurs. ORA-01456 : may not perform insert/delete/update operation inside a READ ONLY transaction Kindly help me on this, please. My stored procedure is as under:- create or[More]

Need Performance tuning in delete operation

Category:DefaultRelease time:-0001-11-30Views:130

Hi Gurus, I am performing delete operation by following SQL query. delete from gl_account where bu_id = -99 but it take long time to execute. Table contains 1 trigger and 5 index. I have disabled the trigger and rebuild the index but still it not exe[More]

Database Auditing to record DELETE operation on a schema for all tables.

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I am using ORACLE DATABASE 11g. I want to apply the AUDIT feature to record all the DELETE operations happening on the schema tables. I did the following steps but dint got the proper output :- I logged into the SYS as sysdba user and set alter s[More]

When table with clustered columnstore indexe is partitioned the performance degrades if data is located in multiple partitions

Category:DefaultRelease time:2015-10-11Views:130

Hello, Below I provide a complete code to re-produce the behavior I am observing.  You could run it in tempdb or any other database, which is not important.  The test query provided at the top of the script is pretty silly, but I have observed the sa[More]

Performance degradation when using foreign keys

Category:DefaultRelease time:2015-10-11Views:130

Hi, I face drastic performance degradation when I add foreign keys to a table and perform insert / update on that table. I have a row store table to  which I need to insert around 1,50,000 records. If the table has no foreign key reference it takes m[More]

Delete Operation in JDBC Sender Adapter not works

Category:DefaultRelease time:2015-10-11Views:130

Hi,     I have one student table which contains the fields ID,Name,BirthMonth,BirthYear,ReadFlag fields. ReadFlag is a character field or lenght 1 which contains only values either 'Y' or ' '.       I want to execute delete operation in this table ie[More]

Hot
Hi all, I could use some help (using version CS5.5). I want to import (via Javascript) an XML file similar to this: <movieRoot>      <dvds>           <movie>                <title>2001: A Space Odyssey</title>                [More]
I am a former customer and user of CS5. I have already payed for it, but since I have changed pc I need to install the suite all over again. Could you give an URL to where this version is available for download?if you follow all 7 steps you can dl a [More]
I'm getting lost trying to figure this parent/child stuff out, mostly in regard to trying to use "removeChild". Here's the nutshell of what I'm doing: In my public class I'm declaring: var skillPointDisplay:mcSkillPointWindow = new mcSkillPointW [More]
Hi, I'm new to Labview. I want to control the XPS motion controller using Labview and I wanted to get started with the examples provided by XPS along with the drivers. But I get a warning while loading the example. I have attached a screenshot of it. [More]
I imported approx 7000 keywords (most with hierarchical synonyms and some with sub-keywords). In other words a BIG keyword collection. Now I cannot use Lightroom. When I start it it becomes non-responsive (Windows XP) while burning huge amounts of CP [More]
Currently I have a work calendar and a personal calendar both on Outlook 2007. I want to be able to sync both onto the phone w/o having the respective calendar going onto the computer. I would also like to know how to make each one a separate calenda [More]
Hi Please let me know the patch number for 11.5.10.2 HRMS FAMILY PACK K release 3, Is it "11i.HR_PF.K.DELTA.3 -- 6699770"? I checked for the installed patches in fnd_product_installations table I found 11i.PER.N is installed for Human Resources [More]
on howmany PCs can i activate Labview Pro. development system with a Serial Number ? thanks  RENN Kudos always welcome for helpful postsNickerbocker wrote: I had a bad couple of days worth of OS re-installs trying to get my laptop working.  I think I [More]
Hi, how to update dates and quantites in PTO sales order from PO with partial MIGO. I could find the FM 'SD_PURCHASE_CHANGE_ORDER' which can change the date in PTO sales order from PO with MIGO fully done. Can we do it through any other means?I Could [More]
Hi SDN-ers We would like to load flatfiles into BW in batch but .... and this is the tricky part, without storing the file on the application server. We are able to call remote function modules from outside the system so maybe we can attach the file [More]