RE: how many LIOs is too many


It looks as if that code is trying to sweep through the table picking up "the 
oldest data not yet processed", remembering to start of from where it ended on 
the last pass (based on a two-part key).  It also looks as if the developer 
expected it to use an index on (maudrecno, memrecno) to walk the index order so 
that it could stop after 500 suitable rows and return the data without sorting. 
If that's the case it's not obvious why the optimizer is choosing the wrong 
index.

As someone else said, concurrency (in the same blocks) also pushes up the 
number of LIOs, so if this is "find the recent activity" the query is 
constantly scanning through recent data which is either not yet committed, or 
may be in need of block cleanout, and therefore produces lots of extra LIOs as 
visits to the undo segment.

Off the top of my head I'm not certain that the optimizer can use the predicate 
at line 4 as an access predicate to do a "count stopkey" with "sort order by 
nosort" - but I think it should be able to if you've got the right index in 
place.  So my next move would be to check if the index exists, run a test to 
see if it can do what I think it can, and if so create an SQL Baseline to force 
the use of that index.


Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: Ram Raman [veeeraman@xxxxxxxxx]
Sent: 21 January 2014 21:11
To: Jonathan Lewis
Cc: ORACLE-L
Subject: Re: how many LIOs is too many

Thanks to everyone who responded. Jonathan, how do you say that it is 1800 
exec/hr? I did (789388/ (sysdate-first_load_time)); with about 33 days, it came 
to abuot 1,000 exec/hr.

I have some more information about the SQL:  
https://drive.google.com/file/d/0B9YC82qZ8_3eNGxFeHdQdlJiN1k/edit?usp=sharing



In this case the number of LIOs per execution is probably not the important bit 
- the important bit is that the query seems to take a little over 2 CPU seconds 
per execution.
At 1,800 executions per hour (rather than the "few thousand" you suggest, this 
would be equivalent to eliminating one  of your CPUs. Unless you've got at 
least 8 (real) CPUs, you don't want to be running this query; if you've got a 
small number of real CPUs which are using threads to fake themselves up to look 
like lots of CPUs you really don't want to be running this query.

To answer your question
1) Yes - and the bizarre thing is that the code fetch a couple of hundred rows 
in order, processed and updated the first one (which took it off the list) then 
re-ran the query to fetch a couple of hundred again.  If you can't see the 
code, try tracing it (and read the trace file) to see what the process does 
next after fetching the 500.

2) No formula that can be generally applied - especially if you're interested 
in precision.  Rick's suggestion is a generous over-estimate and talks about 
"final set" - but doesn't make cleara that "final set" could have to allow for 
an aggregate:  your query might be required to aggregate 20,000 rows to 500 - 
any estimate should be about the 20,000 not the 500. In your case (as Mark 
indicates) you may be acquiring and sorting a very large volume of data and 
then discarding all but 500 rows, and it's the volume acquired that matters, 
not the volume returned.

Bottom line - don't worry about the details at present, that query (with its 
current plan) is clearly too CPU-intensive to be run thousands of times per 
hour.
a) find out what it's doing and why - you may be able to reduce the execution 
count or fetched volume
b) review the current execution plan to see if there is a better path possible
c) review the SQL to see if it can be re-written

Other related posts: