to cpature sql not sung indexes / poor sql

[geobg]

Are thare any ready made scripts to capture poor performing sql and the ones not utilizing indexes.
How do we find out if certain query would perform better with indexes.

Thanks, in advsnce,

Geo

[pipo]

this is called tuning, if there was a script doing tuning alone, I guess it's creator would be billionnaire
seriously, it is a work you need to do on every query, knowing which tables are impacted, which indexes are on these tables, and some more things ...
to find out which queries are not really well written/executed, you can look in v$sqlarea for queries which consume a lot of disk (disk_reads), or memory (buffer_gets) ... from there you can make an explain plan of these queries, look how they are executed, look for indexes which are used or not used, and make a battle plan for the query to run better (using a hint, dropping or creating an index ...)
well it's a job in itself

good luck !!

[bmycroft]

I agree with pio - tuning is an iterative process that never really ends. Its also something that you get better at with time and there is unfortunately no single script to do it for you.

There are a few things you can try though as a starter for ten.
If you look in the SQLArea for queries that are run often and are performing high buffer_gets or worse, high physical IO, you are a long way to finding the "EvilSql" in your system.

Queries to do this are:

column sql_text format a80 word_wrap
set long 160
set linesize 160
set pagesize 40
SELECT /*+ RULE */
substr(rownum,1,2) as "No",
sql_text,
executions,
buffer_gets,
disk_reads,
rows_processed
FROM v$sql st, dual
WHERE -1*st.disk_reads = DECODE(dual.dummy(+), 'X',NULL, NULL)
AND rownum <= 10
ORDER BY st.disk_reads DESC
/

and

column sql_text format a80 word_wrap
set long 160
set linesize 160
set pagesize 40
SELECT /*+ RULE */
substr(rownum,1,2) as "No",
sql_text,
executions,
buffer_gets,
disk_reads,
rows_processed
FROM v$sql st, dual
WHERE -1*st.executions = DECODE(dual.dummy(+), 'X',NULL, NULL)
AND rownum <= 10
ORDER BY st.executions DESC

and

column sql_text format a80 word_wrap
set long 160
set linesize 160
set pagesize 40
SELECT /*+ RULE */
substr(rownum,1,2) as "No",
sql_text,
executions,
buffer_gets,
disk_reads,
rows_processed
FROM v$sql st, dual
WHERE -1*st.buffer_gets = DECODE(dual.dummy(+), 'X',NULL, NULL)
AND rownum <= 10
ORDER BY st.buffer_gets DESC
/

When you get a list of the queries from these scripts then you need to explain-plan them and look for full-table-scans and odd execution paths. trust me its time consuming but its probably the only way.