Re: how many LIOs is too many

  • From: Ram Raman <veeeraman@xxxxxxxxx>
  • To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
  • Date: Tue, 21 Jan 2014 18:07:42 -0600

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
>>
>>
>

Other related posts: