Home » RDBMS Server » Performance Tuning » How to find a SQL query which is taking entire resources of an oracle database
How to find a SQL query which is taking entire resources of an oracle database [message #186058] Sat, 05 August 2006 07:11 Go to next message
machis
Messages: 1
Registered: August 2006
Junior Member
Hi,
do any one knows
How to find a SQL query which is taking entire resources of an oracle database at present, can we find it out through any dynamic views or by means of any method
Re: How to find a SQL query which is taking entire resources of an oracle database [message #186111 is a reply to message #186058] Sun, 06 August 2006 00:35 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Try V$SQL_AREA.
Re: How to find a SQL query which is taking entire resources of an oracle database [message #187169 is a reply to message #186058] Fri, 11 August 2006 03:39 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
This ould be a point to start from:

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 NOT IN ('SYS', 'SYSTEM')
          ORDER BY ELAPSED_TIME DESC)
 WHERE ROWNUM < 21;
Re: How to find a SQL query which is taking entire resources of an oracle database [message #187172 is a reply to message #186058] Fri, 11 August 2006 03:45 Go to previous message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
To find large full scans:

SELECT SQL_TEXT, TOTAL_LARGE_SCANS, EXECUTIONS, EXECUTIONS * TOTAL_LARGE_SCANS AS SUM_LARGE_SCANS
  FROM (SELECT SQL_TEXT, COUNT(*) AS TOTAL_LARGE_SCANS, EXECUTIONS
           FROM V_$SQL_PLAN A, DBA_SEGMENTS B, V_$SQL C
          WHERE A.OBJECT_OWNER(+) = B.OWNER
            AND A.OBJECT_NAME(+) = B.SEGMENT_NAME
            AND B.SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION')
            AND A.OPERATION LIKE '%TABLE%'
            AND A.OPTIONS = 'FULL'
            AND C.HASH_VALUE = A.HASH_VALUE
            AND B.BYTES / 1024 > 1024
          GROUP BY SQL_TEXT, EXECUTIONS)
 ORDER BY 4 DESC
Previous Topic: Releasing SYSTEM TABLE SPACE to USERDATA,INDEX
Next Topic: Query tuning
Goto Forum:
  


Current Time: Sat Dec 03 07:46:39 CST 2016

Total time taken to generate the page: 0.06311 seconds