Scripts - Tuning Sql Queries

Finding High Disk and/or Memory Reads

SQL>select disk_reads, sql_text
from v$sqlarea
where disk_reads > 100000
order by disk_reads desc
/

Finding potential over indexed tables

SQL>select buffer_gets, sql_text
from v$sqlarea
where buffer_gets > 200000
order by buffer_gets desc
/


To find the Worst Query in the Database

SQL>select b.username username, a.disk_reads reads,
a.executions exec, a.disk_reads /decode
(a.executions, 0, 1,a.executions) rds_exec_ratio,
a.sql_text Statement
from V$sqlarea a, dba_users b
where a.parsing_user_id = b.user_id
and a.disk_reads > 100000
order by a.disk_reads desc
/

SQL>select snap_id, disk_reads_delta reads_delta,
executions_delta exec_delta, disk_reads_delta /decode
(executions_delta, 0, 1,executions_delta) rds_exec_ratio,
sql_id
from dba_hist_sqlstat
where disk_reads_delta > 100000
order by disk_reads_delta desc;

Finding Literal Sql (Not using Bind variables)

SQL>SELECT substr(sql_text,1,20) "SQL",
count(*) ,
sum(executions) "TotExecs"
FROM v$sqlarea
GROUP BY substr(sql_text,1,20)
HAVING count(*) > 3
ORDER BY 2
/

Find the versions of a statement for which character identical but underlying object are different binds etc.

SQL>SELECT address, hash_value,
version_count ,
users_opening ,
users_executing,
substr(sql_text,1,40) "SQL"
FROM v$sqlarea
WHERE version_count > 10
/

Finding statement/s which use lots of shared pool memory:
Finding sql statement's which use lots of shared pool memory where MEMSIZE is about 10% of the shared pool size in bytes.
This should show if there are similar literal statements, or multiple versions of a statements which account for a large portion of the memory in the shared pool.

SQL> SELECT substr(sql_text,1,40) "Stmt", count(*),
sum(sharable_mem) "Mem",
sum(users_opening) "Open",
sum(executions) "Exec"
FROM v$sql
GROUP BY substr(sql_text,1,40)
HAVING sum(sharable_mem) >
(select sum(bytes)/100*10 from V$sgastat where pool = 'shared pool')
/


Long running Queries:-

select sql_text ,round((elapsed_time/executions)/1000000,2) "Time_Taken in secs",module
from v$sql
where first_load_time like (to_char(sysdate-1,'yyyy-mm-dd')||'%') and executions>0 and round((elapsed_time/executions)/1000000,2) >5
order by 2 desc;

select b.*,a.elapsed_seconds from
(select distinct sql_hash_value,elapsed_seconds
from v$session_longops where trunc(start_time) = trunc(sysdate) and opname NOT LIKE 'RMAN%')a,
(select sql_text,hash_value,module from v$sql )b
where a.sql_hash_value=b.hash_value
order by 4 desc
/