How to identify a parse of SQL statement? [message #265769] |
Fri, 07 September 2007 05:53 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68678 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
|
|
|
|
|
|