| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> HELP! Deletes taking FOREVER
Hi, I have a table with about 14,000 rows in it, and after watching a
delete on the table
hang, and hang, and hange again, I bounced the database, (after checking
for blockers)
and saw no improvement. I was the only one on the system, and began
deleting 10 or
20 rows at a time, each time this would take about 30 seconds. It is a
dimension table,
there are many tables with foreign keys aimed at this table, but they
are all empty. I
did an alter table deallocate unused on all the tables. - I flush the
shared pool - I looked
at my BSTAT ESTAT and noticed the buffer cache could be much bigger, but
I don't think that's enough to explain this. No locks, no nothing. I
have some tkprof output of
the actual delete of 20-30 rows itself, and it looks normal except for
the recursive stuff.
Despite the CPU stats reporting relatively low, and the range scan using
the primary key index, this query took 30 seconds to run. Any
thoughts? Here's the tkprof output.....
TKPROF: Release 8.0.5.0.0 - Production on Tue Jun 15 14:24:42 1999
(c) Copyright 1998 Oracle Corporation. All rights reserved.
Trace file: delete.trc
Sort options: default
count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ********************************************************************************
alter session set sql_trace = TRUE
call count cpu elapsed disk query current rows
Parse 0 0.00 0.00 0 0 0 0 Execute 1 0.00 0.05 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0
total 1 0.00 0.05 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 25 (MMS)
delete from geography_dim where geography_dim_key < 3900 and geography_dim_key > 3980
call count cpu elapsed disk query current rows
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.01 0 300 0 0 Fetch 0 0.00 0.00 0 0 0 0
total 2 0.01 0.01 0 300 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 25 (MMS)
Rows Execution Plan
------- ---------------------------------------------------
0 DELETE STATEMENT GOAL: CHOOSE
0 DELETE OF 'GEOGRAPHY_DIM'
1 INDEX (RANGE SCAN) OF 'XPKGEOGRAPHY_DIM' (UNIQUE)
********************************************************************************
delete from geography_dim where geography_dim_key < 4000 and geography_dim_key > 3980
call count cpu elapsed disk query current rows
Parse 1 0.01 0.01 0 0 0 0 Execute 1 0.07 0.06 1 2 59 19 Fetch 0 0.00 0.00 0 0 0 0
total 2 0.08 0.07 1 2 59 19
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 25 (MMS)
Rows Execution Plan
------- ---------------------------------------------------
0 DELETE STATEMENT GOAL: CHOOSE
0 DELETE OF 'GEOGRAPHY_DIM'
20 INDEX (RANGE SCAN) OF 'XPKGEOGRAPHY_DIM' (UNIQUE)
********************************************************************************
commit
call count cpu elapsed disk query current rows
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 1 0 Fetch 0 0.00 0.00 0 0 0 0
total 2 0.00 0.00 0 0 1 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 25 (MMS)
alter session set sql_Trace = false
call count cpu elapsed disk query current rows
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 25 (MMS)
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
Parse 4 0.01 0.01 0 0 0 0 Execute 5 0.08 0.12 1 302 60 19 Fetch 0 0.00 0.00 0 0 0 0
total 9 0.09 0.13 1 302 60 19
Misses in library cache during parse: 4 Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
Parse 5 0.00 0.00 0 0 0 0 Execute 95 0.01 0.02 0 0 0 0 Fetch 95 30.47 30.48 126576 154337 228 95
total 195 30.48 30.50 126576 154337 228 95
Misses in library cache during parse: 0
5 user SQL statements in session.
5 internal SQL statements in session.
10 SQL statements in session.
2 statements EXPLAINed in this session.
Trace file: delete.trc
Trace file compatibility: 7.03.02
Sort options: default
1 session in tracefile.
5 user SQL statements in trace file.
5 internal SQL statements in trace file.
10 SQL statements in trace file.
10 unique SQL statements in trace file.
2 SQL statements EXPLAINed using schema:
MMS.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
280 lines in trace file.
Received on Tue Jun 15 1999 - 14:11:34 CDT
 |
 |