How to identify high-load SQL in Oracle, using oracle views?

Question

I'm looking for a query, that will return a list of high-load sql statements. I don't want to use any Oracle tools like ADDM or AWR. I need a query statement, that will return high-load sql statements.

Answer 1

You could query the AWR tables directly, that may be the most simple way.

Or, a simple technique I used to use on Oracle 8i with no statistics enabled, was to select the SQL with the highest buffer gets to execution ratio from v$sql. You can play with this query to only search for high execution count queries, or those doing high physical IO etc.

The AWR and ASH tables will give better information, but this could be a simple first step:

select a.sql_id, a.ratio, a.executions
from  
(
  select sql_id, buffer_gets / executions ratio, executions
  from v$sql
  where executions > 10
  order by 2 desc
) a
where rownum <= 10

Answer 2

To quickly find if you have long running processes taking up your resources look at v$sesson_long_ops :

SELECT * FROM v$session_longops

see: http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2092.htm

I would advise you to take a look at this too: http://docs.oracle.com/cd/B28359_01/server.111/b28274/instance_tune.htm

You can then take the sid to find the sql that is running:

SELECT sql_text FROM v$session s 
LEFT JOIN v$sqlarea sa ON s.sql_hash_value=sa.hash_value AND s.sql_address=sa.address 
WHERE sid=&sid

If you are running unix You could also take a look at the top command (top10 or topas on different unix flavours) you could then take the process ids of the queries consuming the most cpu then use the following to get the offending sql.

SELECT
    s.username,
    sa.sql_text
FROM v$process p
INNER JOIN v$session s ON p.addr=s.paddr
LEFT JOIN v$sqlarea sa ON s.sql_hash_value=sa.hash_value AND s.sql_address=sa.address 
WHERE s.username IS NOT NULL AND p.spid=&SPID