Top 5 time-consuming SQL queries in Oracle

Question

How can I find poor performing SQL queries in Oracle   Oracle maintains statistics on shared SQL area and contains one row per SQL string v sqlarea   But how can we identify which one of them are badly performing

User · Answer

It depends which version of oracle you have  for 9i and below Statspack is what you are after  10g and above  you want awr   both these tools will give you the top sql s and lots of other stuff

User · Answer

While searching I got the following query which does the job with one assumption query execution time  6 seconds     SELECT username  sql text  sofar  totalwork  units   FROM v sql v session longops  WHERE sql address   address AND sql hash value   hash value  ORDER BY address  hash value  child number     I think above query will list the details for current user   Comments are welcome

User · Answer

The following query returns SQL statements that perform large numbers of disk reads  also includes the offending user and the number of times the query has been run    SELECT t2 username  t1 disk reads  t1 executions      t1 disk reads   DECODE t1 executions  0  1  t1 executions  as exec ratio      t1 command type  t1 sql text   FROM v sqlarea t1  dba users t2   WHERE t1 parsing user id   t2 user id     AND t1 disk reads  gt  100000   ORDER BY t1 disk reads DESC   Run the query as SYS and adjust the number of disk reads depending on what you deem to be excessive  100 000 works for me    I have used this query very recently to track down users who refuse to take advantage of Explain Plans before executing their statements   I found this query in an old Oracle SQL tuning book  which I unfortunately no longer have   so apologies  but no attribution

User · Answer

I found this SQL statement to be a useful place to start  sorry I can t attribute this to the original author  I found it somewhere on the internet    SELECT   FROM  SELECT     sql fulltext      sql id      elapsed time      child number      disk reads      executions      first load time      last load time FROM    v sql ORDER BY elapsed time DESC  WHERE ROWNUM  lt  10     This finds the top SQL statements that are currently stored in the SQL cache ordered by elapsed time   Statements will disappear from the cache over time  so it might be no good trying to diagnose last night s batch job when you roll into work at midday   You can also try ordering by disk reads and executions   Executions is useful because some poor applications send the same SQL statement way too many times   This SQL assumes you use bind variables correctly   Then  you can take the sql id and child number of a statement and feed them into this baby -  SELECT   FROM table DBMS XPLAN DISPLAY CURSOR   amp sql id    amp child      This shows the actual plan from the SQL cache and the full text of the SQL

User · Answer

There are a number of possible ways to do this  but have a google for tkprof   There s no GUI    it s entirely command line and possibly a touch intimidating for Oracle beginners  but it s very powerful   This link looks like a good start   http   www oracleutilities com OSUtil tkprof html

User · Answer

I found this SQL statement to be a useful place to start  sorry I can t attribute this to the original author  I found it somewhere on the internet    SELECT   FROM  SELECT     sql fulltext      sql id      elapsed time      child number      disk reads      executions      first load time      last load time FROM    v sql ORDER BY elapsed time DESC  WHERE ROWNUM  lt  10     This finds the top SQL statements that are currently stored in the SQL cache ordered by elapsed time   Statements will disappear from the cache over time  so it might be no good trying to diagnose last night s batch job when you roll into work at midday   You can also try ordering by disk reads and executions   Executions is useful because some poor applications send the same SQL statement way too many times   This SQL assumes you use bind variables correctly   Then  you can take the sql id and child number of a statement and feed them into this baby -  SELECT   FROM table DBMS XPLAN DISPLAY CURSOR   amp sql id    amp child      This shows the actual plan from the SQL cache and the full text of the SQL

User · Answer

I found this SQL statement to be a useful place to start  sorry I can t attribute this to the original author  I found it somewhere on the internet    SELECT   FROM  SELECT     sql fulltext      sql id      elapsed time      child number      disk reads      executions      first load time      last load time FROM    v sql ORDER BY elapsed time DESC  WHERE ROWNUM  lt  10     This finds the top SQL statements that are currently stored in the SQL cache ordered by elapsed time   Statements will disappear from the cache over time  so it might be no good trying to diagnose last night s batch job when you roll into work at midday   You can also try ordering by disk reads and executions   Executions is useful because some poor applications send the same SQL statement way too many times   This SQL assumes you use bind variables correctly   Then  you can take the sql id and child number of a statement and feed them into this baby -  SELECT   FROM table DBMS XPLAN DISPLAY CURSOR   amp sql id    amp child      This shows the actual plan from the SQL cache and the full text of the SQL

User · Answer

You could take the average buffer gets per execution during a period of activity of the instance   SELECT username         buffer gets         disk reads         executions         buffer get per exec         parse calls         sorts         rows processed         hit ratio         module         sql text        -- elapsed time  cpu time  user io wait time      FROM  SELECT sql text                 b username                 a disk reads                 a buffer gets                 trunc a buffer gets   a executions  buffer get per exec                 a parse calls                 a sorts                 a executions                 a rows processed                 100 - ROUND  100   a disk reads   a buffer gets  2  hit ratio                 module                -- cpu time  elapsed time  user io wait time           FROM v sqlarea a  dba users b          WHERE a parsing user id   b user id            AND b username NOT IN   SYS    SYSTEM    RMAN   SYSMAN              AND a buffer gets  gt  10000          ORDER BY buffer get per exec DESC   WHERE ROWNUM  lt   20

User · Answer

There are a number of possible ways to do this  but have a google for tkprof   There s no GUI    it s entirely command line and possibly a touch intimidating for Oracle beginners  but it s very powerful   This link looks like a good start   http   www oracleutilities com OSUtil tkprof html

User · Answer

You could find disk intensive full table scans with something like this   SELECT Disk Reads DiskReads  Executions  SQL ID  SQL Text SQLText      SQL FullText SQLFullText  FROM      SELECT Disk Reads  Executions  SQL ID  LTRIM SQL Text  SQL Text         SQL FullText  Operation  Options         Row Number   OVER            Partition By sql text ORDER BY Disk Reads   Executions DESC            KeepHighSQL    FROM             SELECT Avg Disk Reads  OVER  Partition By sql text  Disk Reads             Max Executions  OVER  Partition By sql text  Executions             t SQL ID  sql text  sql fulltext  p operation p options        FROM v sql t  v sql plan p        WHERE t hash value p hash value AND p operation  TABLE ACCESS          AND p options  FULL  AND p object owner NOT IN   SYS   SYSTEM          AND t Executions  gt  1          ORDER BY DISK READS   EXECUTIONS DESC   WHERE KeepHighSQL   1 AND rownum  lt  5

User · Answer

The following query returns SQL statements that perform large numbers of disk reads  also includes the offending user and the number of times the query has been run    SELECT t2 username  t1 disk reads  t1 executions      t1 disk reads   DECODE t1 executions  0  1  t1 executions  as exec ratio      t1 command type  t1 sql text   FROM v sqlarea t1  dba users t2   WHERE t1 parsing user id   t2 user id     AND t1 disk reads  gt  100000   ORDER BY t1 disk reads DESC   Run the query as SYS and adjust the number of disk reads depending on what you deem to be excessive  100 000 works for me    I have used this query very recently to track down users who refuse to take advantage of Explain Plans before executing their statements   I found this query in an old Oracle SQL tuning book  which I unfortunately no longer have   so apologies  but no attribution

User · Answer

You could take the average buffer gets per execution during a period of activity of the instance   SELECT username         buffer gets         disk reads         executions         buffer get per exec         parse calls         sorts         rows processed         hit ratio         module         sql text        -- elapsed time  cpu time  user io wait time      FROM  SELECT sql text                 b username                 a disk reads                 a buffer gets                 trunc a buffer gets   a executions  buffer get per exec                 a parse calls                 a sorts                 a executions                 a rows processed                 100 - ROUND  100   a disk reads   a buffer gets  2  hit ratio                 module                -- cpu time  elapsed time  user io wait time           FROM v sqlarea a  dba users b          WHERE a parsing user id   b user id            AND b username NOT IN   SYS    SYSTEM    RMAN   SYSMAN              AND a buffer gets  gt  10000          ORDER BY buffer get per exec DESC   WHERE ROWNUM  lt   20

User · Answer

It depends which version of oracle you have  for 9i and below Statspack is what you are after  10g and above  you want awr   both these tools will give you the top sql s and lots of other stuff

User · Answer

It depends which version of oracle you have  for 9i and below Statspack is what you are after  10g and above  you want awr   both these tools will give you the top sql s and lots of other stuff

User · Answer

There are a number of possible ways to do this  but have a google for tkprof   There s no GUI    it s entirely command line and possibly a touch intimidating for Oracle beginners  but it s very powerful   This link looks like a good start   http   www oracleutilities com OSUtil tkprof html

User · Answer

You could take the average buffer gets per execution during a period of activity of the instance   SELECT username         buffer gets         disk reads         executions         buffer get per exec         parse calls         sorts         rows processed         hit ratio         module         sql text        -- elapsed time  cpu time  user io wait time      FROM  SELECT sql text                 b username                 a disk reads                 a buffer gets                 trunc a buffer gets   a executions  buffer get per exec                 a parse calls                 a sorts                 a executions                 a rows processed                 100 - ROUND  100   a disk reads   a buffer gets  2  hit ratio                 module                -- cpu time  elapsed time  user io wait time           FROM v sqlarea a  dba users b          WHERE a parsing user id   b user id            AND b username NOT IN   SYS    SYSTEM    RMAN   SYSMAN              AND a buffer gets  gt  10000          ORDER BY buffer get per exec DESC   WHERE ROWNUM  lt   20

User · Answer

You could find disk intensive full table scans with something like this   SELECT Disk Reads DiskReads  Executions  SQL ID  SQL Text SQLText      SQL FullText SQLFullText  FROM      SELECT Disk Reads  Executions  SQL ID  LTRIM SQL Text  SQL Text         SQL FullText  Operation  Options         Row Number   OVER            Partition By sql text ORDER BY Disk Reads   Executions DESC            KeepHighSQL    FROM             SELECT Avg Disk Reads  OVER  Partition By sql text  Disk Reads             Max Executions  OVER  Partition By sql text  Executions             t SQL ID  sql text  sql fulltext  p operation p options        FROM v sql t  v sql plan p        WHERE t hash value p hash value AND p operation  TABLE ACCESS          AND p options  FULL  AND p object owner NOT IN   SYS   SYSTEM          AND t Executions  gt  1          ORDER BY DISK READS   EXECUTIONS DESC   WHERE KeepHighSQL   1 AND rownum  lt  5

User · Answer

There are a number of possible ways to do this  but have a google for tkprof   There s no GUI    it s entirely command line and possibly a touch intimidating for Oracle beginners  but it s very powerful   This link looks like a good start   http   www oracleutilities com OSUtil tkprof html

User · Answer

While searching I got the following query which does the job with one assumption query execution time  6 seconds     SELECT username  sql text  sofar  totalwork  units   FROM v sql v session longops  WHERE sql address   address AND sql hash value   hash value  ORDER BY address  hash value  child number     I think above query will list the details for current user   Comments are welcome

User · Answer

While searching I got the following query which does the job with one assumption query execution time  6 seconds     SELECT username  sql text  sofar  totalwork  units   FROM v sql v session longops  WHERE sql address   address AND sql hash value   hash value  ORDER BY address  hash value  child number     I think above query will list the details for current user   Comments are welcome

User · Answer

the complete information one that I got from askTom-Oracle  I hope it helps you  select    from v sql  where buffer gets  gt  1000000  or disk reads  gt  100000  or executions  gt  50000

User · Answer

the complete information one that I got from askTom-Oracle  I hope it helps you  select    from v sql  where buffer gets  gt  1000000  or disk reads  gt  100000  or executions  gt  50000

User · Answer

You could take the average buffer gets per execution during a period of activity of the instance   SELECT username         buffer gets         disk reads         executions         buffer get per exec         parse calls         sorts         rows processed         hit ratio         module         sql text        -- elapsed time  cpu time  user io wait time      FROM  SELECT sql text                 b username                 a disk reads                 a buffer gets                 trunc a buffer gets   a executions  buffer get per exec                 a parse calls                 a sorts                 a executions                 a rows processed                 100 - ROUND  100   a disk reads   a buffer gets  2  hit ratio                 module                -- cpu time  elapsed time  user io wait time           FROM v sqlarea a  dba users b          WHERE a parsing user id   b user id            AND b username NOT IN   SYS    SYSTEM    RMAN   SYSMAN              AND a buffer gets  gt  10000          ORDER BY buffer get per exec DESC   WHERE ROWNUM  lt   20

User · Answer

I found this SQL statement to be a useful place to start  sorry I can t attribute this to the original author  I found it somewhere on the internet    SELECT   FROM  SELECT     sql fulltext      sql id      elapsed time      child number      disk reads      executions      first load time      last load time FROM    v sql ORDER BY elapsed time DESC  WHERE ROWNUM  lt  10     This finds the top SQL statements that are currently stored in the SQL cache ordered by elapsed time   Statements will disappear from the cache over time  so it might be no good trying to diagnose last night s batch job when you roll into work at midday   You can also try ordering by disk reads and executions   Executions is useful because some poor applications send the same SQL statement way too many times   This SQL assumes you use bind variables correctly   Then  you can take the sql id and child number of a statement and feed them into this baby -  SELECT   FROM table DBMS XPLAN DISPLAY CURSOR   amp sql id    amp child      This shows the actual plan from the SQL cache and the full text of the SQL

User · Answer

You could find disk intensive full table scans with something like this   SELECT Disk Reads DiskReads  Executions  SQL ID  SQL Text SQLText      SQL FullText SQLFullText  FROM      SELECT Disk Reads  Executions  SQL ID  LTRIM SQL Text  SQL Text         SQL FullText  Operation  Options         Row Number   OVER            Partition By sql text ORDER BY Disk Reads   Executions DESC            KeepHighSQL    FROM             SELECT Avg Disk Reads  OVER  Partition By sql text  Disk Reads             Max Executions  OVER  Partition By sql text  Executions             t SQL ID  sql text  sql fulltext  p operation p options        FROM v sql t  v sql plan p        WHERE t hash value p hash value AND p operation  TABLE ACCESS          AND p options  FULL  AND p object owner NOT IN   SYS   SYSTEM          AND t Executions  gt  1          ORDER BY DISK READS   EXECUTIONS DESC   WHERE KeepHighSQL   1 AND rownum  lt  5

User · Answer

You could find disk intensive full table scans with something like this   SELECT Disk Reads DiskReads  Executions  SQL ID  SQL Text SQLText      SQL FullText SQLFullText  FROM      SELECT Disk Reads  Executions  SQL ID  LTRIM SQL Text  SQL Text         SQL FullText  Operation  Options         Row Number   OVER            Partition By sql text ORDER BY Disk Reads   Executions DESC            KeepHighSQL    FROM             SELECT Avg Disk Reads  OVER  Partition By sql text  Disk Reads             Max Executions  OVER  Partition By sql text  Executions             t SQL ID  sql text  sql fulltext  p operation p options        FROM v sql t  v sql plan p        WHERE t hash value p hash value AND p operation  TABLE ACCESS          AND p options  FULL  AND p object owner NOT IN   SYS   SYSTEM          AND t Executions  gt  1          ORDER BY DISK READS   EXECUTIONS DESC   WHERE KeepHighSQL   1 AND rownum  lt  5

User · Answer

While searching I got the following query which does the job with one assumption query execution time  6 seconds     SELECT username  sql text  sofar  totalwork  units   FROM v sql v session longops  WHERE sql address   address AND sql hash value   hash value  ORDER BY address  hash value  child number     I think above query will list the details for current user   Comments are welcome

User · Answer

It depends which version of oracle you have  for 9i and below Statspack is what you are after  10g and above  you want awr   both these tools will give you the top sql s and lots of other stuff

[performance] Top 5 time-consuming SQL queries in Oracle

Examples related to performance

Examples related to oracle