Re: how many LIOs is too many
- From: Dba DBA <oracledbaquestions@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 28 Jan 2014 13:35:43 -0500
Jonathan,
A few years ago I think you had a blog post about roughly how to
translate LIOs to CPU. So your saying that with the typical modern
CPU, it can handle:
1719853 buffer gets * 1800 executions = per hour
How are you getting these numbers? This could be very useful when
doing capacity planning.
On 1/21/14, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> wrote:
>
> 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.
>
>
> 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.
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
