Queries From Rampant

Queries From Rampant

Total CPU time taken for PARSING - 

column parsing heading 'Parsing|(seconds)'

column total_cpu heading 'Total CPU|(seconds)'

column waiting heading 'Read Consistency|Wait (seconds)'

column pct_parsing heading 'Percent|Parsing'

select total_CPU,parse_CPU parsing, parse_elapsed-parse_CPU waiting,trunc(100*parse_elapsed/total_CPU,2) pct_parsing from (select value/100 total_CPU from v$sysstat where name = 'CPU used by this session'), (select value/100 parse_CPU from v$sysstat where name = 'parse time CPU'), (select value/100 parse_elapsed from v$sysstat where name = 'parse time elapsed');

Needs Bigger Shared Pool if the PCT is more than 1 percent -

select sum(pins) executions,sum(reloads) cache_misses_while_executing, trunc(sum(reloads)/sum(pins)*100,2) pct from v$librarycache where namespace in ('TABLE/PROCEDURE','SQL AREA','BODY','TRIGGER');

Current shared pool size and free space -

column name format a25

column bytes format 999,999,999,999

select name,to_number(value) bytes

from v$parameter where name ='shared_pool_size' union all select name,bytes from v$sgastat where pool = 'shared pool' and name = 'free memory';

Open Cursors per Users -

select b.sid, a.username, b.value open_cursors from v$session a, v$sesstat b, v$statname c where c.name in ('opened cursors current') and b.statistic#=c.statistic# and a.sid =b.sid and a.username is not null and b.value >0 order by 3;

How often we are finding the cursor in the session cache: - The sessions that are below 50 percent should be investigated.

select a.sid,a.parse_cnt,b.cache_cnt,trunc(b.cache_cnt/a.parse_cnt*100,2) pct from (select a.sid,a.value parse_cnt from v$sesstat a, v$statname b where a.statistic#=b.statistic# and b.name = 'parse count (total)' and value >0) a ,(select a.sid,a.value cache_cnt from v$sesstat a, v$statname b where a.statistic#=b.statistic# and b.name ='session cursor cache hits') b where a.sid=b.sid order by 4,2;