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 > >