EXECUTE TO PARSE IS LOW

EXECUTE TO PARSE EXPLANATION:
*****************************

Execute to parse ratio is a measure of how many times we execute a sql statement versus parse it.

This 'ratio' will go towards 100 as the number of executes goes up and up, while the number of parses remains the same.

It will go to zero as the number of parses and executes are equal

It'll go negative if you parse more than you execute.

This happens when an application parses a query but never executes it. Some applications
(generic apps typically) parse a "select * from T" to describe a table, never execute the
cursor -- their parses exceed their executes.

So:

Below will have a parse/execute ratio near 100

parse insert into t values ( :x )
for i in 1 .. 100000
loop
bind i
execute insert
end loop


Below will have a parse/execute ration near 0

for i in 1 .. 100000
loop
parse insert into t values ( :x );
bind i
execute insert
close insert
end loop


The only way to influence that number is to either change

a) the number of times you parse.
b) the number of times you execute.Its in the Hand of Application Developer. But we can consider the Below Points.

i)We can use as much as PL/SQL than SQL for this issue only. Since PL/SQL cache itself.

ii)We can use Bind variables.

iii)We can change the CURSOR_SHARING parameter but this also cant prevent parsing if application ask to do.
cursor sharing = similar MIGHT change a hard parse into a soft parse,
cursor sharing similar CANNOT change the number of times parse is invoked however.

When Apps say "parse this", It parse it - it matters not what the value of cursor sharing is.


iv)The developers must cache open cursors they know will be used over and over.
to accomplish this is to move all SQL into plsql, plsql automagically caches statements for us,
it is the most efficient method to interface with the database. But Here i am doubtful whether ATG will allow us to handle the
code since its handling that(if i am right).

v)by only parsing a statment ONCE per session,

not once per execution
not once to "describe a table"
not once to "describe the ith column in a table"





In Parsing.

We need to go through Below steps.
i)syntax
ii)semantic
iii)Optimization
iv)Row source generation.etc


Two Kind of Parsing is there.

i)Hard Parsing
ii)Soft Parsing.

Hard Parsing: Parsing the sql from Scratch,go through above all steps and Its very costly.

Soft Parsing: It will do first two steps then If same syntax already used then it will use the already parsed statement from shared_pool.

Here, If we want more soft parses, we need to use Bind Variables.





Below is the formula used to find Execute to Parse Ratio.

round(100*(1-prse/exe),2)


If we want to see the from views.

Select x.sql_text , x.parse_calls , x.executions
,round( 100*(1-( x.parse_calls / x.executions )),2) execute_to_parse_ratio
FROM v$sql x
WHERE x.parse_calls >0
AND x.executions !=0
AND x.parsing_schema_name='EMP'
ORDER BY execute_to_parse_ratio ;

select PARSE_CALLS,EXECUTIONS,SQL_TEXT
from v$sql
where executions = 0 and parse_calls > 0
order by parse_calls desc;

Select PARSE_CALLS, EXECUTIONS, SQL_TEXT
from v$sql
where executions < PARSE_CALLS
order by parse_calls;