Re: explain plan, can you explain this?

Original post: http://www.freelists.org/archives/oracle-l/01-2008/msg00227.html

Did some more research, but still not clear on a few things. Here are answers 
to several of the replies I got:
--This query always has to return a single row. For a given program_id, 
index_start and index_end define ranges that do not overlap
--_optim_peek_user_binds is explicitly set FALSE
--As was discussed on the list, the 111k executions are, in fact, the net 
executions for the 1-hour interval
--With an average of 30 executions per second, it is nearly impossible for this 
to get flushed from the shared pool (shared pool has over 200Mb free memory in 
it)
--I did a trace with several bind values picked at random, never took more than 
4 logical reads. Exec plan matched v$sql_plan matched tkprof report exec plan.
--During the 1-hour timeframe of the statspack snapshots, the table was 
completely static.

Now, I am starting to theorize that somehow, some way, there's one or two 
operations during the 1-hour interval that cause this statement to do very 
large amounts of I/O (how?--that's a mystery to me still). Those operations are 
somehow affecting the 1-hour average by making it 273 I/Os per exec. 

Alberto's reponse 
(http://www.freelists.org/archives/oracle-l/01-2008/msg00232.html) makes the 
most sense as far as explaining some of the why, but I don't know that I agree 
with the possible solution. I don't think that adding columns to the index 
would make a big difference since finding the right index_start should be all 
that's needed. I'm going to start testing that now and see what happens. 

Thanks everyone for all your help!

Dan 

----- Original Message ----
From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
To: dannorris@xxxxxxxxxxxxx; Oracle L <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, January 9, 2008 6:09:05 PM
Subject: RE: explain plan, can you explain this?




 
DIV {
MARGIN:0px;}



When doing an inefficient index range scan on a table, 
it's common to do many more buffer gets than the number of blocks in the 
table & index - that's why it's generally much more efficient to 
use a FTS if you will be touching more than 5% of the table blocks (just a rule 
off thumb).  To understand why this is, you need to understand how an index 
range scan works - check out the concepts guide.  Basically Oracle is 
searching through the b-tree to the table block for each row, and has to 
repeat that process for every row, even within the same block, so a block with 
100 rows will be "consistent read" 100 times, i.e. 100 buffer_gets of the same 
block.  Sounds like the CBO is expecting the query to be much more 
selective (low cardinality) than it really is so you might want to try a 
FULL hint and see if that works better, then drill into the estimated 
cardinality and possibly a 10053 trace to see why the CBO's calculations are 
going wrong.

 

Regards,

Brandon




From: oracle-l-bounce@xxxxxxxxxxxxx 
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Dan 
Norris





So, if all the table data can be scanned in 128 blocks and even if 
you do full scans on both indexes, there's only 168 blocks, then how can this 
thing do 273 buffer gets per execution on average? 


Privileged/Confidential Information may be contained in this message or 
attachments hereto. Please advise immediately if you or your employer do not 
consent to Internet email for messages of this kind. Opinions, conclusions and 
other information in this message that do not relate to the official business 
of this company shall be understood as neither given nor endorsed by it.

Other related posts: