RE: how many LIOs is too many
Date: Mon, 20 Jan 2014 21:25:29 -0500
Message-ID: <021a01cf1650$0f5b2520$2e116f60$_at_rsiz.com>
The mention of top 500 rows after order by asc is troubling to me in that it could mean
- First you retrieve and sort all the rows in the set bounded by the predicates feeding the index range scan, order that, and then select from that result set with the 500 limit, OR
- You’re yanking the first 500 rows you get from the predicated index range scan, and then ordering that
In the second case (which is very easy to accidentally do), you are probably getting the wrong answer.
In the first case the number of rows you fetch can vary wildly depending on the index range scan predicates. Things that do this, for example, on “current month” get many fewer rows retrieved early in the month when no rows exist for subsequent days. (That’s just one example.)
In either case you could get wildly differing results for a given range if the effective cluster factor for that range is much worse than the overall cluster factor.
If you have sporadically migrated and/or chained rows, the hilarity multiplies.
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Chris Taylor
Sent: Monday, January 20, 2014 8:53 PM
To: hurleyjohnb_at_yahoo.com
Cc: veeeraman_at_gmail.com; oracle-l_at_freelists.org
Subject: Re: how many LIOs is too many
Actually I think Cary Millsap has a reasonable way to calculate expected number if LIOs (except when aggregations are used). It took a bit of number crunching and I never took the time to work out the best LIo count. Always attack problem from business needs - as long as the query executes quicker than the requirement I'm not horribly worried about wasting some LIOs.
Sent from my iPhone
On Jan 20, 2014, at 7:00 PM, John Hurley <hurleyjohnb_at_yahoo.com> wrote:
Most of us have probably seen some SQL at a point in time that has much worse LIO than this ... some of us much much worse. There is no magic formula on when to attack problematic sql it depends on so many factors.
If you asked me if I have seen SQL that at times goes into the hundred thousands LIO per execution I would have to say "mumble mumble" ...
From: Ram Raman <veeeraman_at_gmail.com>
To: ORACLE-L <oracle-l_at_freelists.org>
Sent: Monday, January 20, 2014 6:31 PM
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
- 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-l
Received on Tue Jan 21 2014 - 03:25:29 CET