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;

HOW TO CLONE A DATABASE IN WINDOWS IN SAME SERVER:
************************************************************
Assume Old Database Name=ORCL and New Database Name=AUX
Follow the below 7 steps:
Step 1:Create a password file.
orapwd file=$ORACLE_HOME/database/orapwaux password=pwd entries=5
Connect to orcl database and create pfile
Step 2: Create pfile='path/initaux.ora from spfile;
Step 3:Edit the pfile according to the new database path for adump,bdump,cdump and udump. If its not already there create those directories and place the path in pfile.
and we need to change the db_name=AUX and we need to include two important parameters in new init file.
db_file_name_convert= ('C:\oracle\oradata\orcl','C:\auxdir\oradata\aux')
log_file_name_convert=('C:\oracle\oradata\orcl','C:\auxdir\oradata\aux')
Step 4:Add the tnsnames.ora and listener.ora file for the new database(aux).
Below is the example for tnsentry and listener entry.
aux=
(Description=
(Address=(protocol=TCP)(HOST=santhanamuthu.in.ibm.com)(port=1521)
(connect_data=
(server=dedicated)
(server_name=aux)
)
)
Listener entry
(SID_DESC=
(GLOBAL_DBNAME=AUX)
(ORACLE_HOME=C:\oracle\product\10.2.0\db_1)
(SID_NAME=aux)
)
)
Step 5: Now we need to stop and start the listener or we need to do reload.
lsnrctl stop
lsnrctl start
Step 6:Create a service in window, use the below command:
oradim -new -sid AUX -starmode m - pfile=C:\oracle\product\10.2.0\db_1\database\initaux.ora
Step 6:In RMAN prompt give the below commands.
rman>connect auxilary sys/pwd@aux
rman>connect target sys/pwd@orcl
rman>startup clone nomount force;
instance started
rman>duplicate target database to aux;
It will take some time depend on size of the source database and we can exit once completed.
rman>exit
Now cloned database is ready;
set the environment to new aux database
rman>connect target sys/pwd;
you will get the below message
Connected to target database : AUX (DBID=157465780900)
If we want we can verify the new cloned database now.. :)