RE: Need some 10053 Guidance to help me solve a puzzler

Reading about a subject is kind of academic compared to getting your hands into it I think.

Let me get together some information and I'll reply in more depth.

Chris

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Thursday, October 11, 2012 1:02 PM To: oracle-l_at_freelists.org
Subject: Re: Need some 10053 Guidance to help me solve a puzzler

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_at_parallon.net> To: <jonathan_at_jlcomp.demon.co.uk>; <oracle-l_at_freelists.org> 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).

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Oct 11 2012 - 20:05:40 CEST