Performance Tuning Part 3

3) PERFORMANCE TUNING

1) Shows waiting sessions and the events they await

select  sw.sid     sid,p.spid     spid,s.username username,s.osuser   osuser,sw.event   event,s.machine  machine,s.program  program
,decode(sw.event,'db file sequential read', sw.p3,'db file scattered read',  sw.p3, null) blocks
from    v$session_wait sw,v$session s,v$process  p
where   s.paddr = p.addr and sw.event not in ('pipe get','client message') and sw.sid  = s.sid;

2) Script shows explain plan without running statement. Note that you must have a PLAN_TABLE to run this

select     lpad(' ', 2*(level - 1))||operation||' '||options||' '||object_name||' '||object_type||' '||object_instance||' '||

           decode(id, 0, 'Cost = '||position) "Query Plan"

from       plan_table

3)
start with id = 0
connect by prior id = parent_id;
current values of init.ora parameters and other values in question.  Branch around multi-threaded server output if MTS not being used.
Cleaned up the formatting of the output for consistency.

select substr(substr(global_name,1,30),1,instr(substr(global_name,1,30),'.')-1) SID from global_name;

4) Bypass multi-threaded server checks if there aren''t any mts servers.

select '/*' from v$parameter where name = 'mts_servers' and value = '0';

5) Tuning Multi-Threaded Server

If SERVERS_HIGHWATER exceeds the MTS_SERVERS parameter, increase the number of MTS_SERVERS in the init&SID..ora file.

select 'Current MTS_SERVERS number is ' || value from v$parameter where name = 'mts_servers';

6) Packages you might want to consider pinning into the shared pool:

select owner, name, type, loads, executions, sharable_mem
from v$db_object_cache
where kept = 'NO'and loads > 1 and executions > 50 and sharable_mem > 10000 and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
order by loads desc;

SELECT operation,count(*) FROM v$logmnr_contents group by operation;
select *FROM DBA_DIRECTORIES;