How to find trouble sql statement?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: How to find trouble sql statement?

  1. #1
    Join Date
    Jan 2001
    Location
    Vancouver, Canada
    Posts
    87
    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.

    Mohammad Zahid
    Software Developer
    Database Management Applications.
    Vancouver, Canada
    E-mail: mzahid@shaw.ca

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    *********************** 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width