RE: how many LIOs is too many
Date: Mon, 20 Jan 2014 21:08:48 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CF0226D774_at_WIN02.hotsos.com>
A given SQL should never do more than 10 X <number of tables> X <number of rows in final set>
So a simple query getting 5 rows from 2 tables shouldn't do more than 100 LIOs. (5 X 2 X 10 = 100)
Are all the rows just from the ONE table? Then per execution you shouldn't be doing more than 5000 LIOs. From the numbers is looks like this thing is doing about 2,532 LIOs per execution (1,998,534,791/ 789,388) as you say in your message. So it's not "excessive", yet. But that still mean it could use some work, just being below this number doesn't mean it doesn't have issues. Which is seems to have some scaling issues it the number of LIOs is going up that fast, is the table growing at an equal rate?
If there is an index it's using to get the "top 500 rows" then that shouldn't be a big deal that it's stopping after 500 rows. It will use the index to find the top 500 and the ROWNUM will stop the sort at that point. Not really any sorting going on. However if the index isn't on the order by column, well then things could go very badly.
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
Ric Van Dyke
Education Director
Hotsos Ltd.
Hotsos Symposium March 2-6 2014
Make your plans to be there now!
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ram Raman
Sent: Monday, January 20, 2014 6:32 PM
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.251820107.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
- Has anyone in this list worked on an app that does repeated executions of a sql retrieving so many rows.
- 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.
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 21 2014 - 04:08:48 CET