Re: Primary Key Index Gone?

Actually, it appears that it was something much stranger. I noticed that even on very small
deletes, there was an enormous increase in v$filestat on many different files, and I tracked it down to tables with foreign keys on the table. Even though the tables were empty, massive I/O was occuring. I then noticed that the segment for the automatically created primary key index was missing. I re-created it, and the delete of the entire table was about 5 seconds or less. Does anyone have any experience or knowledge of A) How someone or something could have gotten rid of the primary key default index and B) What sort of strange trips Oracle would go on if it was gone?

Thanks,
Dc.

Ulrik Hoffmann wrote:

> 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 - 20:48:16 CDT