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, Our client is making some changes to the fiscal year period. Becuase of some business requirements, we will have to shortened the fiscal year. Can you please suggest - what are the watch points when we do this?                                 [More]
Hi all, I am a newbie to the Apple World and Yesterday I bought an APPLE IPAD (4th Generation).. My primary purpose is to read pdf files on IPAD4. I have 3 questions .. 1. Can we connect a usb flash drive to the ipad4 and read pdf files from it? 2.  [More]
Hi, An invoice shows wrong MWST value because of incorrect customer tax classification as Tax exempt. I corrected the cust tax classification to "Liable for Tax". Then I tried to create a debit memo request with reference to the incorrect invoic [More]
Hello everyone, I have a iPod 4th gen grayscale and I'm running Ubuntu, everything has worked well for about a year now, I have been syncing my iPod through VirtualBox with Windows XP for as long as I have been using Linux, though, for some reason, m [More]
hello friends well i am working on a project on phishing for that i had usinf javamail to read out the mails while i open my any email account while i working on the net . actually what i want is whenever i open my account my program could detect the [More]
I'm attempting to modify an ABAP Query Infoset (using transaction SQ02) that already has a join in it between tables VBAP and VBAK, and two additional alias tables on KONV (to get price history for sales documents).  I wish to get history of all PN00 [More]
Need help with setting up a group of contacts to my phone. Need to be able to send group e-mail and textsYou can create group of contacts on your computer, and then sync them on your iPhone. But you will not be able to select them as text / email rec [More]
I'm having trouble connecting Brother DCP197c to Mac Mini - it worked before Yosemite. I have downloaded drivers from Brother I have tried searching for the printer once the drivers have been installed, but nothing... Not sure what else to do - any h [More]
Menu graphic was created in Photoshop. Overlay is included in the same document. The overlay is for a text higlight. In the Simulator, the overlay highlights where the alpha channel is, not where the black text is. Weird. Using simple colors, btw. Th [More]
I want to work with Linkedin. They demand a password for my email adress [email protected] I dont have a password and I dont understand what to do to get one. Greatful for help!You need to create a dedicated password for use to login to Linkedin. Go [More]