Satyavani

• Jul 28th, 2005

 

When a SQL query is executed it follows the following steps: 
 
1) Search the shared pool. This is to ensure that the copy of the parsed statement is available in the library cache. 
2) Validate the statement 
3) Validate the data source 
4) Acquire locks 
5) Check privileges 
6) Parse the statement 
7) Execute the statement 
8) Fetch values from cursor. 
 
In case the parse tree is not available in the library cache it needs to reparse the statement. 
 
Thanks, 
satyavani

Shrikant Pande

• Aug 24th, 2005

 

If SGA is not properly sized it caused reparse, Oracle checks data in buffer cache first if it is not found it goes to datafile. if SGA sized properly oracle will get data in buffer and reparse wil not accur

praim Sankar

• Sep 1st, 2005

 

The Library Cache was introduced because parsing (and validating and checking privileges) is expensive in terms of CPU cycles.  
 
Oracle uses a LRU/MRU mechanism to keep sql statements in cache. When more room is needed in the cache Oracle drops off the LRU block. 
When a sql statement is executed Oracle checks the library cache first. If it finds it then there is no need to reparse and it is a hit. 
If the statement does not exist in cache then it's a miss and oracle needs to read the data from the data blocks. The statement will be parsed.

sandeep vig

• Oct 13th, 2005

 

there are main two causes for reparsing

1)the objects which the query is refrencing has been modified

2) or the parsed version of sql text has been aged out of the library cache.