RE: how many LIOs is too many
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, "'Ram Raman'" <veeeraman@xxxxxxxxx>
- Date: Tue, 21 Jan 2014 18:36:18 -0500
I didn't read the sql, but if JL's observation that you're picking up data
not yet processed is correct, a leading candidate to optimize your use case
is a nullable column getting the date or timestamp on insertion and becoming
null when processed. This becomes the action control column. A single column
index on such a column tends to remain very small (and can be rebuilt if you
have large epochs of new row arrival with no processing followed by
processing windows.) IF you need to keep the insert date or timestamp, then
you update the action control column to null and the permanent time or date
column (born null) to the actual value upon processing in the same commit if
you'd like to minimize row length change.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jonathan Lewis
Sent: Tuesday, January 21, 2014 4:50 PM
To: Ram Raman
Cc: ORACLE-L
Subject: 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=sharin
g
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:
