There is no index available on (maudrecno, memrecno) combo. I have uploaded the same document again with more information, including the list of all indexes on the table: https://drive.google.com/file/d/0B9YC82qZ8_3eMF9HZXVHeVhOcDA/edit?usp=sharing On Tue, Jan 21, 2014 at 3:49 PM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx > wrote: > > > 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 >> >> >