RE: how many LIOs is too many

You're thinking of my rough guide of :  10,000 buffer gets per second per 100MHz of CPU.  (I think 100MHz was the unit, but I can't find the note at present).
I wasn't using that in this case (after all, we now know that the SQL was doing a lot of sorting and it;'s also applying some analytics).

This was simple:
EXECUTIONS = 789388         
CPU_SEC = 1719853.25

CPU per Exec = 2.1787 seconds per exec


Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

________________________________________
From: Dba DBA [oracledbaquestions_at_gmail.com]
Sent: 28 January 2014 18:35
To: ORACLE-L
Cc: Jonathan Lewis
Subject: Re: how many LIOs is too many

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_at_jlcomp.demon.co.uk> 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.
>
>
--