Although there is no data in the dependent tables Oracle still has to check
for content dependent on the parent. I can;t comment on the missing primary
index segment though
Regards
--
Kevin A Lewis (BOCM PAULS LTD - Animal Feed Manufacturer - Ipswich England)
<KevinALewis_at_HotMail.com>
The views expressed herein by the author of this document
are not necessarily those of BOCM PAULS Ltd.
Doug Cowles <dcowles_at_bigfoot.com> wrote in message
news:37670260.EF9B18E0_at_bigfoot.com...
> 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 Wed Jun 16 1999 - 09:27:50 CDT
