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_at_OneNeck.com> To: dannorris_at_dannorris.com; Oracle L <oracle-l_at_freelists.org> 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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.

--

http://www.freelists.org/webpage/oracle-l Received on Thu Jan 10 2008 - 14:12:58 CST