Low 'Execute to Parse' ratio analysis

Suddenly, got a call from one of the Unix team members, one of our side applications was not responding properly.

I looked at database if something wrong going on there. Found something very interesting -

* Queries were taking longer time than previous (longer execution time than needed before 2 weeks)

* More CPU were being used.

* Execute to Parse ratio went down to 3%-5%. Earlier, we had this ration about 52%-65%. This could be an indication of a major problem.

* Got some stats from AWR - high number of sessions were being established during that problem hours. About 30 connections/sessions established per minute. Our normal range was 7-10 connections per minute. We do use connection pooling in Weblogic.

AWR Report showed -

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:	100.00	Redo NoWait %:	100.00
Buffer Hit %:	99.53	In-memory Sort %:	100.00
Library Hit %:	99.64	Soft Parse %:	99.83
Execute to Parse %:	4.18	Latch Hit %:	99.98
Parse CPU to Parse Elapsd %:	91.48	% Non-Parse CPU:	99.42

The 'Execute to Parse' thing drew my attention first.

What is Execute to Parse ratio? What does it tell?

Every query needs to be parsed before it got executed. If some queries are quite frequently used, a good design will be reuse the parsed query. That means, it will not parse the same query again (provided that we are using bind variables), rather will reuse the existing parsed query and then execute it. In an ideal world, execute should be more than the parse. More parsing requires more CPU. That's why the concept of query reuse came in to play.

When execute is more than parse, the figure will be positive. The ratio goes down if there are more parses than the execute. Some of the queries are parsed, but less or never executed!

What may be the possible reasons for such low ratio?

This "Execute to Parse ratio" actually tells many things and seeks many areas to look into for further information. Causes could be one of the followings or something else -

* There might be no "Prepared Statement caching" in java layer or in jdbc connection pool
* There might be the case, before the execution of a query, the cursor was closed
* There might not be enough "session cached cursors" to hold the queries to reuse
* There might be the case where new queries were coming to play quite frequently
* There might be connection interruptions

I have to find out the queries those were being parsed but executed less during the problem hours -

set linesize 200;
set pagesize 1000;
col sql_text format a40;

SELECT sq.sql_text, st.executions_total, st.parse_calls_total
, round( 100*(1-( st.parse_calls_total / st.executions_total )),2) execute_to_parse
, st.executions_delta, st.parse_calls_delta
, round( 100*(1-( st.parse_calls_delta / st.executions_delta )),2) delta_ratio
FROM DBA_HIST_SQLSTAT st
, DBA_HIST_SQLTEXT sq
, DBA_HIST_SNAPSHOT s
WHERE s.snap_id = st.snap_id
AND s.begin_interval_time >= to_date('2009-08-04 11:30 am','YYYY-MM-DD HH:MI am')
AND s.end_interval_time <= to_date('2009-08-04 12:01 pm','YYYY-MM-DD HH:MI pm') AND st.sql_id = sq.sql_id AND st.parsing_schema_name='PROD' AND st.executions_total !=0 AND st.executions_delta !=0 ORDER BY delta_ratio;

The delta ratio is the current for the snapshot. So, I have a list of queries those parsed but less or not executed - I can now look at application code for a better understanding.

Tough time ahead!