How to define high load SQL in Oracle using oracle representations?

Question

I am looking for a query that will return a list of high load sql statements. I do not want to use any Oracle tools like ADDM or AWR. I need a query statement that will return high-level sql statements.

Answer 1

You can directly query AWR tables, which may be the easiest way.

Or the simple method that I used for Oracle 8i without statistics included was to select the SQL with the highest buffer in order to get the execution coefficient from v $ sql. You can play with this query only to search for queries with a high number of queries, or for those who do high physical IO, etc.

The AWR and ASH tables will provide better information, but this may 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 lengthy processes consuming your resources, see 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 also take a look at this: http://docs.oracle.com/cd/B28359_01/server.111/b28274/instance_tune.htm

Then you can take 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 use unix, you can also take a look at the top command ( top10 or topas on different unix styles), you could take the request process identifiers that consume most processors, and then use the following to get offensive 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