CODEWITHSUNDEEP
oracle-databaseperformancecascading-deletes
Roman
Roman

Reputation: 567

delete from a table takes long time and how to find a reason

I know that there are a lot of questions about delete taking long time but I want to find an instrument to solve this riddle.

Here's my preset. We have a table and do deletes by ID from it. ID is a primary key on a table. There are a few FK looking at my table with on delete no action. I double checked that all the referencing tables have indexes on referencing columns. There are also several triggers on the table.

We tried to trace the session that does deletes. We fetch 1000 rows and then delete them using FORALL DELETE in PL/SQL. We got traces from this session and tkprofed it. You can see the output below.

SQL ID: 71vbsharan1f1 Plan Hash: 663052182

delete /*+ parallel(1) */  from DOCUMENTS s 
where
s.id IN (:c_id)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      6    264.88     460.49       2591     140034  245669840        6000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7    264.88     460.49       2591     140034  245669840        6000

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 23     (recursive depth: 3)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                      2539        0.01          2.06
  library cache: mutex X                        555        0.01          0.56
  log file switch completion                      3        0.05          0.11
  latch: cache buffers chains                     9        0.00          0.00
  buffer busy waits                            2373        0.00          0.02
  buffer deadlock                                 8        0.00          0.00
  Disk file operations I/O                        1        0.00          0.00
********************************************************************************

Traces don't have information on checking referencing tables so I can't say whether it takes a lot of time or not. There are some waits but they took just about 3 seconds so I don't think we have problems here.

What I want to get is some extra analytic options like another tracing options or getting some logs to see how Oracle does checking on referencing table and what took a lot of time. Any advices appreciated!

Upvotes: 0

Views: 1359

Answers (2)

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

I would suggest two options

Tracing Session

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8' ;
-- then delete
ALTER SESSION SET EVENTS '10046 trace name context off' ;

SQL Monitor

For using SQL Monitor you require Enterprise Edition and the Diagnostics and Tuning option. Make sure you have the correct licensing before using this functionality. Just identify the SQL_ID of one of those expensive operations and try to run this command. You can run the report type in HTML or TEXT.

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SPOOL /host/report_sql_detail.htm
SELECT DBMS_SQLTUNE.report_sql_detail(
  sql_id       => '526mvccm5nfy4',
  type         => 'ACTIVE',
  report_level => 'ALL') AS report
FROM dual;
SPOOL OFF

For tracing details and other options look into this article

Tracing Sessions

For SQL monitor you can check this one

SQL Monitor Examples

Upvotes: 1

Jim Macaulay
Jim Macaulay

Reputation: 5155

When you have a trigger on the table, it will definitely slow down the performance based on its functionality. Please try to disable all the triggers and try once again. Check the explain plan before and after disabling the triggers for analysis.

You can use explain plan to analyse further

explain plan for
delete /*+ parallel(1) */  from DOCUMENTS s 
where
s.id IN (:c_id) ;

Avoid using parallel and try optimizing the query if your table is huge

Upvotes: 0

Related Questions