RE: how many LIOs is too many
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, <veeeraman@xxxxxxxxx>, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 21 Jan 2014 08:37:01 -0600
Roger that Jonathan, an aggregate does skew things and this is an UPPER
limit, if your over this number things are really bad. If you below you
still may have work to do but at least it's not "really bad". Anything
over this is a really in trouble.
With an aggregate you need to apply the formula on the step just before
the aggregate is applied. Of course for that you need something like
stat lines from a 10046 or data from v$sql_plan_statistics.
The formula is a generous as you say, and that was on purpose. To make
it maybe more accurate change the 10 to the max height of your indexes
+1 or so. The idea of the formula is pretty simple. To get one row
from an index it will take the number of LIOs to drive to the bottom of
the index and one more for the table access. Of course there are all
kinds of times that Oracle rereads a block and then of course there are
migrated/chained rows. So you need a little wiggle room in the formula.
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
Ric Van Dyke
Education Director
Hotsos Ltd.
Hotsos Symposium March 2-6 2014
Make your plans to be there now!
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jonathan Lewis
Sent: Tuesday, January 21, 2014 2:45 AM
To: veeeraman@xxxxxxxxx; ORACLE-L
Subject: RE: how many LIOs is too many
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
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
behalf of Ram Raman [veeeraman@xxxxxxxxx]
Sent: 20 January 2014 23:31
To: ORACLE-L
Subject: how many LIOs is too many
List,
We have a SQL that is being executed few thousand times in an hour. The
SQL belongs to an application supplied by a 3rd party vendor. The SQL
accesses 500 rows from a million row table every time it executes, using
idx range scan. This is spiking up the resource usage consistently. (Do
not know why an application would need 500 rows constantly, it uses
rownum to filter out top 500 rows after order by asc).
As of now this is the stats:
SQL_ID FETCHES EXECUTIONS DISK_READS BUFFER_GETS CPU_SEC
ELAPSEDSEC FIRST_LOAD_TIME
------------- ---------- ---------- ---------- ----------- ----------
---------- -----------------------
037xx0tb72t5r 789387 789388 80 1998534791 1719853.25
1820107.77 2013-12-18/08:06:26
The LIO per exec was at ~1600 yesterday, it has gone to 2500 today.
I have couple of questions
1) Has anyone in this list worked on an app that does repeated
executions of a sql retrieving so many rows.
2) Is there any formula to calculate the number of LIOs for a SQL when
it is time to say the sql is doing too many LIOs per exec.
v11.2
TIA,
Ram.
Other related posts:
