| How to identify a parse of SQL statement? [message #265769] |
Fri, 07 September 2007 05:53  |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Hi all!
A SQL statement includes 3 parts, one is called as execution, one is called as parse, rest called as fit.
I just only know about execution which is method that a SQL statement is physically executed. To identify a parse, actually, there are 2 concepts of parse: Hard parse and soft parse, it's dependent to Shared Pool.
If the current statement has already been processed, the parse operation can skip the next two functions in the process: Optimization and Row Source Generation. If the parse phase does, in fact, skip these two functions, it is called a soft parse.
If the current SQL statement has never been parsed by another session, the parse phase must execute ALL of the parsing steps. This type of parse is called a hard parse.
And there are my questions about parse:
How to identify the SQL Statement is a hard parse or soft parse? I mean that to identifie by SQL statement or something visually.
Can I calculate the time of parse exactly by any formula?
Can I attempt to take set or all of SQL Statment into soft parse?
Thank you very much!
[Updated on: Fri, 07 September 2007 20:54] Report message to a moderator |
|
|
|
| Re: How to identify a parse of SQL statement? [message #265792 is a reply to message #265769] |
Fri, 07 September 2007 07:28  |
 |
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
| Quote: | How to identify the SQL Statement is a hard parse or soft parse?
|
All SQL statements are hard parsed, a SQL statement execution request can include a hard parse, a soft parse or even a softer soft parse.
| Quote: | Can I calculate the time of parse exactly by any formula?
|
* v$sysstat/v$sesstat, "parse time%" statistics.
* 10046 trace.
| Quote: | Can I attempt to take set or all of SQL Statment into soft parse?
|
All SQL statements must be at least hard parsed once.
To get soft parse use shared SQL statements: bind variables, same type of variables, same NLS parameters, same optimizer parameters...
Better, use softer soft part that is set session_cached_cursors to a value > 0...
Regards
Michel
[Edit: correct typos]
[Updated on: Sat, 08 September 2007 00:51] Report message to a moderator |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
