Re: how many LIOs is too many
- From: Thomas P S <royxavier@xxxxxxxxx>
- To: "veeeraman@xxxxxxxxx" <veeeraman@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 21 Jan 2014 04:25:18 -0800 (PST)
Just a thought - LIOs can be vary depends on the number of transactions going
on your system.
http://ora600tom.wordpress.com/2012/10/17/consistent-reads-who-is-the-culprit/
So, I don't think any formula will work here.
Thanks,
Thomas Saviour
http://ora600tom.wordpress.com
On Tuesday, January 21, 2014 5:02 AM, Ram Raman <veeeraman@xxxxxxxxx> wrote:
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:
