Execute to parse ratio:
Execute to parse ratio is a measure of how many times you execute a sql statement versus parse it.
This 'ratio' will go towards 100 as the number of executes goes up and up and up, while the number of parses remains the same.
This will go to zero as the number of parses and executes are equal.
This will go negative if you parse more than you execute.
That percentage is computed as: round(100*(1-:prse/:exe),2)
Your developers have all of the control here - we cannot change this ratio without touching the way the application interacts with the database.
When Execute to Parse values is too low:
That means, someone is parsing statements highly, but not executing properly. They are just chewing up your CPU, latching the shared pool, killing your performance.
If the database is parsing every statement that is executing, the parse to execute ratio will be close to 1%.
How could parse be bigger than execution? Or, Why Execute to Parse values become too low?
(http://www.dba-oracle.com/m_parse_to_execute_ratio.htm)
There may be tools that parse a statement but never execute it. Or they parse it to discover what inputs/outputs it takes and then later on parse it again to execute it (so the parse/execute is 2:1).
It is commonly referred to as "inefficient coding techniques". They parse like there is no tomorrow -- works great in a single user environment -- starts to stink with two users (or more).
Not using bind variable in query can cause such low value.
Some applications (generic apps typically) parse a "select * from T" to describe a table, never execute the cursor -- their parses exceed their executes.
Oracle does what it is told to do. Oracle is told by you to PARSE. If you do not tell Oracle to execute the statement, it won't. That is how you have parse but no execute.
How I can identify those SQL's, Which PARSE but never EXECUTE:
v$sql -- look at the parse and execute counts.
Example:
select PARSE_CALLS,EXECUTIONS,SQL_TEXT
from v$sql
where executions = 0 and parse_calls > 0
order by parse_calls asc;
Select PARSE_CALLS, EXECUTIONS, SQL_TEXT
from v$sql
where executions < PARSE_CALLS
order by parse_calls;
How they can be eliminated or efficiently coded ?
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"
When Parse is very high and execution is very low, Check in coding --
cursor-sharing=force must be on?
**Make cursor-sharing=similar. That will reduce hard parse.
(http://asktom.oracle.com/pls/asktom/f?p=100:11:2626091586804596::::P11_QUESTION_ID:4032595293314)
SQL> show parameter cursor_sharing
Setting CURSOR_SHARING to EXACT allows SQL statements to share the SQL area only when their texts match exactly. This is the default behavior. Using this setting, similar statements cannot shared; only textually exact statements can be shared.
Some Oracle databases with high ad-hoc query activity (Crystal Reports, Business Objects cannot avoid in-line literals inside the SQL, and that's why Oracle introduced the cursor_sharing parameter. Cursor_sharing=similar allows for bind variable "peeking", and in my opinion, it's too buggy to use until Oracle 11i, when you get adaptive cursor sharing. (http://www.dba-oracle.com/t_cursor_sharing_similar.htm)
If you have a routine that will be executed many times to generate a page, you should definitely parse once per page and execute it over and over, closing the cursor if necessary before your connection is returned to the connection pool.
It is 100% controlled by the client code and cannot be affected by the server settings at all. It is 100% a function of the number of times the client parses a sql statement and how many times they execute it.
How a Statement given for parsing cannot Execute?
Example:
dbms_sql.parse ( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns ( l_theCursor, l_colCnt, l_descTbl );
That just parsed the query, described it (to find the number of columns, data types, names, sizes, etc).
It never executed it -- If you closed the cursor, you would have a PARSE without an execute.
cursor sharing and parse:
Cursor sharing cannot reduce the NUMBER of parse calls (only your coders can do that! really - that is the ONLY way to reduce parse calls, they developers must call PARSE less often!)
cursor sharing can reduce the NUMBER OF HARD PARSES, that is all.
Hard parse:
A hard parse is expensive because each incoming SQL statement must be re-loaded into the shared pool; with the associated overhead involved in shared pool RAM allocation and memory management. Once loaded, the SQL must then be completely re-checked for syntax & semantics and an executable generated. Excessive hard parsing can occur when your shared_pool_size is too small or when you have non-reusable SQL statements without host variables and bind variables.
http://www.ooug.org/2005slides/0720/ToolsIuse/trace_no.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:2626091586804596::::P11_QUESTION_ID:4032595293314
Execute to parse ratio is a measure of how many times you execute a sql statement versus parse it.
This 'ratio' will go towards 100 as the number of executes goes up and up and up, while the number of parses remains the same.
This will go to zero as the number of parses and executes are equal.
This will go negative if you parse more than you execute.
That percentage is computed as: round(100*(1-:prse/:exe),2)
Your developers have all of the control here - we cannot change this ratio without touching the way the application interacts with the database.
When Execute to Parse values is too low:
That means, someone is parsing statements highly, but not executing properly. They are just chewing up your CPU, latching the shared pool, killing your performance.
If the database is parsing every statement that is executing, the parse to execute ratio will be close to 1%.
How could parse be bigger than execution? Or, Why Execute to Parse values become too low?
It is possible that the application is not using shareable SQL, or the database has sub-optimal parameters that are reducing the effectiveness of cursor sharing. A problem like excessive parsing is likely to manifest itself as additional network traffic between the application server and clients. The additional parse activity may also show up as a marked increase in CPU consumption on the database server.
(http://www.dba-oracle.com/m_parse_to_execute_ratio.htm)
There may be tools that parse a statement but never execute it. Or they parse it to discover what inputs/outputs it takes and then later on parse it again to execute it (so the parse/execute is 2:1).
It is commonly referred to as "inefficient coding techniques". They parse like there is no tomorrow -- works great in a single user environment -- starts to stink with two users (or more).
Not using bind variable in query can cause such low value.
Some applications (generic apps typically) parse a "select * from T" to describe a table, never execute the cursor -- their parses exceed their executes.
Oracle does what it is told to do. Oracle is told by you to PARSE. If you do not tell Oracle to execute the statement, it won't. That is how you have parse but no execute.
How I can identify those SQL's, Which PARSE but never EXECUTE:
v$sql -- look at the parse and execute counts.
Example:
select PARSE_CALLS,EXECUTIONS,SQL_TEXT
from v$sql
where executions = 0 and parse_calls > 0
order by parse_calls asc;
Select PARSE_CALLS, EXECUTIONS, SQL_TEXT
from v$sql
where executions < PARSE_CALLS
order by parse_calls;
How they can be eliminated or efficiently coded ?
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"
When Parse is very high and execution is very low, Check in coding --
cursor-sharing=force must be on?
SQL> show parameter cursor_sharing
Setting CURSOR_SHARING to EXACT allows SQL statements to share the SQL area only when their texts match exactly. This is the default behavior. Using this setting, similar statements cannot shared; only textually exact statements can be shared.
Some Oracle databases with high ad-hoc query activity (Crystal Reports, Business Objects cannot avoid in-line literals inside the SQL, and that's why Oracle introduced the cursor_sharing parameter. Cursor_sharing=similar allows for bind variable "peeking", and in my opinion, it's too buggy to use until Oracle 11i, when you get adaptive cursor sharing. (http://www.dba-oracle.com/t_cursor_sharing_similar.htm)
If you have a routine that will be executed many times to generate a page, you should definitely parse once per page and execute it over and over, closing the cursor if necessary before your connection is returned to the connection pool.
It is 100% controlled by the client code and cannot be affected by the server settings at all. It is 100% a function of the number of times the client parses a sql statement and how many times they execute it.
How a Statement given for parsing cannot Execute?
Example:
dbms_sql.parse ( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns ( l_theCursor, l_colCnt, l_descTbl );
That just parsed the query, described it (to find the number of columns, data types, names, sizes, etc).
It never executed it -- If you closed the cursor, you would have a PARSE without an execute.
cursor sharing and parse:
Cursor sharing cannot reduce the NUMBER of parse calls (only your coders can do that! really - that is the ONLY way to reduce parse calls, they developers must call PARSE less often!)
cursor sharing can reduce the NUMBER OF HARD PARSES, that is all.
Hard parse:
A hard parse is expensive because each incoming SQL statement must be re-loaded into the shared pool; with the associated overhead involved in shared pool RAM allocation and memory management. Once loaded, the SQL must then be completely re-checked for syntax & semantics and an executable generated. Excessive hard parsing can occur when your shared_pool_size is too small or when you have non-reusable SQL statements without host variables and bind variables.
http://www.ooug.org/2005slides/0720/ToolsIuse/trace_no.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:2626091586804596::::P11_QUESTION_ID:4032595293314
