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
Just typing "kstat" in the command line displays all the kstat modules and statistics available. For example, on my Solaris 10: % kstat module: cpu_stat instance: 0 name: cpu_stat class: misc anonfree 0 anonpgin 958 I am trying to write code in [More]
First of all, I'm posting here because I'm not sure how to post in the previous version of Exchange forums. Secondly, I'm re-posting this from the Outlook forums as I'm not getting any responses there despite of views. Hi, I'm having an issue in Outl [More]
Hi, I have three items on a region Item 1 Item 2 item 3 10 20 30 As soon as i enter 10 in item1 and 20 in item2 then item3 needs to be pupulated with the total of it. Could any one guide me the processs of doing this. Please advice KrisKris, This can [More]
I'm having this problem where every time I turn on the Mac, the screen is set at medium (7 I think) brightness. I don't know what could have caused this. I remember before upgrading to 10.6.3 there was a problem where it would auto-set at maximum bri [More]
my printer will not print in color even though i have new color cartridges.Hi, Have you turned Grayscale printing Off ? Please try (for Windows): (a) Click printer icon on desktop (or Start > HP > HP [your printer], (b) Click Set preferences (c) Cli [More]
Hi,    I have created XML  forms.After creating when im executing the forms im getting the following error: XML - Forms  Error applying XSL stylesheet to XML com.sapportals.wcm.WcmException: com.sap.engine.lib.xml.util.NestedException: org.w3c.dom.DO [More]
Since upgrading to OS X 10.8.2 I'm having a lot of problems with my email (works fine on other devices I have). The main one is that isn't not displaying messages, it shows there's 3 new mail messages, but the message list and preview window are empt [More]
Hi, How do i change the default file path of logical file . I need to change path of a logical file in MI34 tcode from  C:\usr\sap\DEV\SYS\global  to out new Virtually created directory. Recetly We moved from OS/400 to WINTEL environment I wenin to F [More]
My setup for Adobe is flawed.   When I open an Excel spread sheet, Adobe wants to operate the spreadsheet and save it as PDF.   How do I change the setup to eliminate problem?Hi Camacho, Acrobat will not save the spreadsheet as PDF unless you click o [More]
I've started two discussions one a singular question and with approximately 50 views, not a single response. Are there any people out there who actually answer questions?Hello Saxon, We strive to provide support though many channels and I am sorry th [More]