RE: Need some 10053 Guidance to help me solve a puzzler
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <Christopher.Taylor2@xxxxxxxxxxxx>, <jonathan@xxxxxxxxxxxxxxxxxx>, <niall.litchfield@xxxxxxxxx>
- Date: Thu, 11 Oct 2012 15:04:36 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Christopher.Taylor2@xxxxxxxxxxxx
Sent: Thursday, October 11, 2012 2:32 PM
To: Christopher.Taylor2@xxxxxxxxxxxx; jonathan@xxxxxxxxxxxxxxxxxx;
niall.litchfield@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Christopher.Taylor2@xxxxxxxxxxxx
Sent: Thursday, October 11, 2012 1:15 PM
To: jonathan@xxxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
