Hello All,
I am trying to find out what sql is taking too long and causing my database to hang for 12 minutes and taking up CPU resources. I queried V$sqlarea view and did not get the result I was looking for.
Thank you for your help in advance.
Printable View
Hello All,
I am trying to find out what sql is taking too long and causing my database to hang for 12 minutes and taking up CPU resources. I queried V$sqlarea view and did not get the result I was looking for.
Thank you for your help in advance.
*********************** SCRIPT STARTS NEXT LINE **********************
REM
REM Module Name: BAD_SQL.SQL
REM Purpose: When executed from an Oracle session connected with the
REM rights to see the SYS.V$* tables, this script produces a
REM list of the top 10 "offending" SQL statements. It REM displays the
REM top 10 SQL statements with respect to buffer gets.
REM SQL Text is included.
REM
REM Parameters: None
REM
REM
BREAK ON ADDRESS SKIP 2
SET PAGESIZE 9999
SET LONG 4096
SET LINE 132
SELECT SQLAREA.BUFFER_GETS "Buffer Gets",
SQLAREA.DISK_READS "Disk Reads", SQLTEXT.SQL_TEXT "SQL Text",
EXECUTIONS "Executions",SQLAREA.ADDRESS
FROM SYS.V$SQLTEXT SQLTEXT, SYS.V$SQLAREA SQLAREA
WHERE SQLTEXT.ADDRESS = SQLAREA.ADDRESS AND
SQLTEXT.HASH_VALUE = SQLAREA.HASH_VALUE AND
EXECUTIONS > 0
GROUP BY SQLAREA.ADDRESS, BUFFER_GETS, DISK_READS, SQLTEXT.SQL_TEXT,
SQLTEXT.PIECE, EXECUTIONS
ORDER BY BUFFER_GETS DESC, DISK_READS DESC, PIECE;
*********************** SCRIPT ENDS PREVIOUS LINE **********************
************************* SCRIPT STARTS NEXT LINE **********************
REM
REM Purpose: When executed from an Oracle session connected with the
REM rights to see the SYS.V$* tables, this script produces a
REM list of the top 10 "offending" SQL statements. It
REM displays the
REM top 10 SQL statements with respect to disk accesses.
REM SQL Text is included.
REM
REM Parameters: None
REM
REM
BREAK ON ADDRESS SKIP 2
SET PAGESIZE 9999
SET LONG 4096
SET LINE 132
SELECT SQLAREA.DISK_READS "Disk Reads", SQLAREA.BUFFER_GETS "Buffer Gets",
SQLTEXT.SQL_TEXT "SQL Text",
EXECUTIONS "Executions",SQLAREA.ADDRESS
FROM SYS.V$SQLTEXT SQLTEXT, SYS.V$SQLAREA SQLAREA
WHERE SQLTEXT.ADDRESS = SQLAREA.ADDRESS AND
SQLTEXT.HASH_VALUE = SQLAREA.HASH_VALUE AND
EXECUTIONS > 0
GROUP BY SQLAREA.ADDRESS, BUFFER_GETS, DISK_READS, SQLTEXT.SQL_TEXT,
SQLTEXT.PIECE, EXECUTIONS
ORDER BY DISK_READS DESC, BUFFER_GETS DESC, PIECE;
regards
Hrishy