RE: how many LIOs is too many
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "veeeraman@xxxxxxxxx" <veeeraman@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 21 Jan 2014 07:45:29 +0000
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:
