Re: how many LIOs is too many
Date: Tue, 21 Jan 2014 15:11:22 -0600
Message-ID: <CAHSa0M2ucHS-DcDnzE_CcgWX-C8eOArWGaDTiZeM5g_zZVddFA_at_mail.gmail.com>
Thanks to everyone who responded. Jonathan, how do you say that it is 1800 exec/hr? I did (789388/ (sysdate-first_load_time)); with about 33 days, it came to abuot 1,000 exec/hr.
I have some more information about the SQL: https://drive.google.com/file/d/0B9YC82qZ8_3eNGxFeHdQdlJiN1k/edit?usp=sharing
> 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.
>
> 2) No formula that can be generally applied - especially if you're
> interested in precision. Rick's suggestion is a generous over-estimate and
> talks about "final set" - but doesn't make cleara that "final set" could
> have to allow for an aggregate: your query might be required to aggregate
> 20,000 rows to 500 - any estimate should be about the 20,000 not the 500.
> In your case (as Mark indicates) you may be acquiring and sorting a very
> large volume of data and then discarding all but 500 rows, and it's the
> volume acquired that matters, not the volume returned.
>
> Bottom line - don't worry about the details at present, that query (with
> its current plan) is clearly too CPU-intensive to be run thousands of times
> per hour.
> a) find out what it's doing and why - you may be able to reduce the
> execution count or fetched volume
> b) review the current execution plan to see if there is a better path
> possible
> c) review the SQL to see if it can be re-written
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 21 2014 - 22:11:22 CET