Wednesday, April 30, 2008

How to get the 100 most expensive sql statements

The below sql statement will give the 100 most expensive sql statements for a scpecific user,in the following example i run it for user SCOTT.

SELECT *
FROM (SELECT SQL_TEXT, B.USERNAME, ROUND(ELAPSED_TIME / 1000000, 3) ELAPSED_TIME_SECS,
ROUND((A.DISK_READS / DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS)), 2) AS DISK_READS_PER_EXEC,
A.DISK_READS, A.BUFFER_GETS,
ROUND((A.BUFFER_GETS / DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS)), 2) AS BUFFER_GETS_PER_EXEC,
A.PARSE_CALLS, A.SORTS, A.EXECUTIONS, A.LOADS, A.ROWS_PROCESSED,
100 - ROUND(100 * A.DISK_READS / GREATEST(A.BUFFER_GETS, 1), 2) AS HIT_RATIO,
A.FIRST_LOAD_TIME, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, ROUND(CPU_TIME / 1000000, 3) AS CPU_TIME_SECS,
ROUND((CPU_TIME / 1000000) / DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS), 3) AS CPU_TIME_SECS_PER_EXECUTE,
ROUND((ELAPSED_TIME / 1000000) / DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS), 3) AS ELAPSED_TIME_SECS_PER_EXECUTE, ADDRESS, HASH_VALUE
FROM V_$SQLAREA A, ALL_USERS B
WHERE A.PARSING_USER_ID = B.USER_ID
AND B.USERNAME IN ('SCOTT')
ORDER BY ELAPSED_TIME DESC)
WHERE ROWNUM <>

No comments: