Hi Doug,
did you check the ORCLALRT.LOG ? Are there
any 'CHECKPOINT NOT COMPLETES' or
Errors?
Maybe it has to do with the redo-log-files.
Bye
Uli
Doug Cowles <dcowles_at_bigfoot.com> schrieb in im Newsbeitrag:
3766A565.88201DCA_at_bigfoot.com...
> 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 - 15:24:06 CDT
