RE: Need some 10053 Guidance to help me solve a puzzler
Date: Thu, 11 Oct 2012 15:04:36 -0400
Message-ID: <056201cda7e3$4283a170$c78ae450$_at_rsiz.com>
Returning about 10% of the table - but what is the cluster factor on the index? The CBO might well assess that you'll read fewer total blocks via FTS. I *thought* that was already explained in this thread. If the cluster factor is lousy, the CBO figures the block read via that index to fetch the next row will have to be largely random and therefore you might have to fetch it again if ever the index points you back at a block you've read before to fetch a row. That's on top of the drilling down to the particular rowid you need from the index.
I didn't catch whether your stats gathering was cascaded to do the index or not. If using the index plan beats the fts, then it probably has a good actual cluster factor and a stale bad cluster factor in the stats. Or you could be hitting the algorithm gaps JL mentioned.
Oh - and I'm not biased. DAMN GOOD BOOK. Even as some of the specifics age, the concepts remain first quality and will stand the test of time.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Christopher.Taylor2_at_parallon.net
Sent: Thursday, October 11, 2012 2:32 PM
To: Christopher.Taylor2_at_parallon.net; jonathan_at_jlcomp.demon.co.uk;
niall.litchfield_at_gmail.com
Cc: oracle-l_at_freelists.org
Subject: RE: Need some 10053 Guidance to help me solve a puzzler
Further info on table size:
Query Result Set = 197,188 rows
Table Size:
18,272,128 Rows
5958.75MB (~6GB)
23835 Extents
Returning 10.791% of the table.
Chris
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Christopher.Taylor2_at_parallon.net
Sent: Thursday, October 11, 2012 1:15 PM
To: jonathan_at_jlcomp.demon.co.uk; oracle-l_at_freelists.org
Subject: RE: Need some 10053 Guidance to help me solve a puzzler
1.)
Quote:
-- Okay, bit of a coincidence on the costing, then, but
- what's the block size
- what do the system stats look like
db_block_size=8192
system stats:
SYSSTATS_MAIN CPUSPEEDNW 1878.73888439774 SYSSTATS_MAIN IOSEEKTIM 10 SYSSTATS_MAIN IOTFRSPEED 4096 SYSSTATS_MAIN SREADTIM 1.562 SYSSTATS_MAIN MREADTIM 26.342 SYSSTATS_MAIN CPUSPEED 3003 SYSSTATS_MAIN MBRC 871 SYSSTATS_MAIN MAXTHR 287382528 SYSSTATS_MAIN SLAVETHR 29696
(see anything here that bears on this issue?)
2.)
Quote:
--should have been more like "the bit of the table needed by the indexed
access path is nearly completely cached"
Okay that would make sense.
3.)
Quote:
-- Oracle thinks that the data you want is all over the place and will
require a very large number of random reads
-- it's possible that the data you want is actually all packed into a fairly
small part of the table
That also makes sense.
4.)
Quote:
-- How big is the table really, how big is the cache. My comment was that
the table was largely UNcached.
Table Size:
5958.75MB (~6GB) 23835 Extents
Cache:
Buffer Cache: 24,832 MB (~24GB)
5.)
Quote
-- There's a very good book about this called "Cost Based Oracle -
Fundamentals" (I wrote it, so I am biased, though).
I have your book :-p (Reading about something is a lot different than getting to get your hands onto it and really understanding it!)
Chris
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 11 2012 - 21:04:36 CEST