Thursday 28 February 2013

Query to find to Soft Parse and Hard Parse Ratio

Query to find to Soft Parse and Hard Parse ratio



select 'Soft Parses ' "Ratio"
,round(
((select sum(value) from v$sysstat where name = 'parse count (total)')
- (select sum(value) from v$sysstat where name = 'parse count (hard)'))
/(select sum(value) from v$sysstat where name = 'execute count')
*100,2)||'%' "percentage"
from dual
union
select 'Hard Parses ' "Ratio"
,round(
(select sum(value) from v$sysstat where name = 'parse count (hard)')
/(select sum(value) from v$sysstat where name = 'execute count')
*100,2)||'%' "percentage"
from dual
union
select 'Parse Failure ' "Ratio"
,round(
(select sum(value) from v$sysstat where name = 'parse count (failures)')
/(select sum(value) from v$sysstat where name = 'parse count (total)')
*100,2)||'%' "percentage"
from dual
/


select sql_text from v$sqlarea where version_count > 1;
 
 
 
 

2 comments:

  1. why is it showing two hard parse values ?

    Ratio percentage
    ------------ -----------------------------------------
    Hard Parses .05%
    Hard Parses .15%
    Soft Parses 47.76%

    ReplyDelete
  2. why is it showing two hard parse values ?

    Ratio percentage
    ------------ -----------------------------------------
    Hard Parses .05%
    Hard Parses .15%
    Soft Parses 47.76%

    ReplyDelete