DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: find poor sql

  1. #1
    Join Date
    Oct 2000
    Posts
    139
    Hi

    Is there anyway to find those SQL that are badly coded in Oracle, like not using indexes or disks access? Any script or views?

    THAnks

  2. #2
    Join Date
    Jan 2001
    Posts
    44
    You can use utility tools like ezsql or TOAD to see how the query works and the execution plan for each query

  3. #3
    Join Date
    Nov 2000
    Posts
    57

    find poor sql

    Invoke the SQL Facility on and check for the same. It will help you in identifying the bottle neck on the query by identiying the scanning mode like full table scan , index scan etc.,

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    try this, this will show sql which causes high I/O

    set feed off;
    set pagesize 10000;
    set wrap off;
    set linesize 200;
    set heading on;
    set tab on;
    set scan on;
    set verify off;
    --
    column sql_text format a40 heading 'SQL-Statement'
    column executions format 999,999 heading 'Total|Runs'
    column reads_per_run format 999,999,999.9 heading 'Read-Per-Run|[Number of]'
    column disk_reads format 999,999,999 heading 'Disk-Reads|[Number of]'
    column buffer_gets format 999,999,999 heading 'Buffer-Gets|[Number of]'
    column hit_ratio format 99 heading 'Hit|Ratio [%]'

    ttitle left 'I/O-intensive SQL-Statements in the memory (V$SQLAREA)' -
    skip 2

    SELECT sql_text, executions,
    round(disk_reads / executions, 2) reads_per_run,
    disk_reads, buffer_gets,
    round((buffer_gets - disk_reads) / buffer_gets, 2)*100 hit_ratio
    FROM v$sqlarea
    WHERE executions > 0
    AND buffer_gets > 0
    AND (buffer_gets - disk_reads) / buffer_gets < 0.80
    ORDER BY 3 desc;

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