find poor sql

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

You can use utility tools like ezsql or TOAD to see how the query works and the execution plan for each query

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.,

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;