Re: Need some 10053 Guidance to help me solve a puzzler
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 11 Oct 2012 19:02:08 +0100
Notes in-line
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
----- Original Message -----
From: <Christopher.Taylor2@xxxxxxxxxxxx>
To: <jonathan@xxxxxxxxxxxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, October 11, 2012 6:36 PM
Subject: RE: Need some 10053 Guidance to help me solve a puzzler
Ok, so now I'm confused a bit and I think I'm probably several mental steps
behind you on this one so bear with me:
First:
db_file_multiblock_read_count=16
Systems Stats have been gathered (always one of the first things I do when
taking over a system)
-- Okay, bit of a coincidence on the costing, then, but
-- what's the block size
-- what do the system stats look like
Second:
I ran the non-indexes query multiple times - if the table was nearly
completely cached, wouldn't the access times improve on the non-indexed
query as well? (There's probably some other considerations here that I'm
missing/not thinking about)
-- How big is the table really, how big is the cache. My comment was that
the table was largely UNcached.
-- Bad wording on my part about the index access though,
-- "the table is nearly completely cached for the indexed access"
-- should have been more like "the bit of the table needed by the indexed
acces path is neraly completely cached"
-- 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
Third:
Why doesn't Oracle choose the Index and Drive the NL to get the 159K rows
on its own?
-- because it thinks the volume of data required is scattered over a very
large number of blocks and will require a very large number of random reads
to acquire and that the time it will take to scan the entire table is much
less than the time it will take to perform that number of single block
reads.
-- There's a very good book about this called "Cost Based Oracle -
Fundamentals" (I wrote it, so I am biased, though).
--
//www.freelists.org/webpage/oracle-l
Other related posts:
