RE: What are the factors causing the reparsing of SQL statements in SGA
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.
RE: What are the factors causing the reparsing of SQL statements in SGA
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
RE: What are the factors causing the reparsing of SQL statements in SGA
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.
