Re: find Stale process

Hi Felix,

Have a read of the tuning portion of the Oracle Manual to understand tuning CPU problems first.

What is STALE sql? Is it SQL which can only be used for toast now? :)

Note: Being CPU bound might not be the problem, finding what Oracle is

      waiting on is more important!

Attached is an example looking for high buffer get SQL

Have Fun

Paul

p.s. welcome to the world of performance tuning!

• Locating CPU-Heavy SQLs - No. of buffer gets per execution

define buffergets = &&1

column buffer_gets format 9,999,999
column executions format 9,999,999
column "gets/execs" format 9,999,999.99
column sql_text format a30 wrap

set pagesize 1000

SELECT
buffer_gets, executions, buffer_gets/executions "gets/execs", rows_processed "Rows" ,sql_text
FROM v$sqlarea
WHERE
buffer_gets > &&buffergets --THIS NEEDS TO BE ADJUSTED TO SHOW TOP 20 AND executions > 0
ORDER BY buffer_gets desc
;

--
Posted via http://dbforums.com