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 Guru, I am trying to post a return to the vendor by a returns PO. i am using VL10B to create a outbound delivery. But unfortunately the outbound delivery is created but delivery quantity is not copied from the PO.  After creating a outbound delive [More]
Dear Team, During archiving log backup , I am faccing the problem. Job started Step 001 started (program RSDBAJOB, variant &0000000000008, user ID BASIS) Execute logical command BRARCHIVE On host sapprd Parameters:-u / -jid LOG__20090729213120 -c for [More]
OK forum folks. I'm working on my first e-learning project and have ran into a problem with question formating. The instructions/help basically tell you to click here and click there. I can easily figure that out. The questions I seem to have are qui [More]
Guru My client as maintained the Minium level stok in MRP2 view in material masterIs it posiable to to issue the material below level if not is their special setting or configration is their Pl guide me i Thanks SudhirHi Sudhir, You can very well iss [More]
Hello all, I am using User32.dll to make a parent-child relationship between two VIs. I use SetParent function, and pass the handle of parent VI and child VI to this function. Now, after this relationship has been established, I want to remove the ch [More]
hi All, I am using 64 bit windows. i have installed SOA suite PS3 version (11.1.1.4) i am able to start weblogic 10.3.4 from command prompt when i start soa server i get following error. any idea ? i do have boot.properties file with following entry [More]
anyone can tell me where i will find the occi inlcude file for linux application development. i search them for a lot time. thanksoh , i have cpoy these include file from another machine that run oracle10g in linux.Read other 2 answers [More]
I have a piece of code that is currently generating a casting exception... I inherited this so I don't know if it even works right. Could someone point me in the right direction as to how to fix this? Here's some of the code in question: public void [More]
Just curious. I still have it on my old PowerBook G4, but I haven't used it for years. I am sure it still works. Heh.John Galt wrote: Meh. I still use mine on occasion but it runs Tiger. Yours can too, Leopard if you really want. No need to stick wit [More]
Does anyone have recommendations for the best headset/hardware to use to enhance audio? Also, suggestions for settings in  Captivate that enhance audio?Nowadays I prefer to use good quality USB podcasting microphones instead of headset mics.  The dia [More]