Home » RDBMS Server » Performance Tuning » How to identify a parse of SQL statement?
How to identify a parse of SQL statement? [message #265769] Fri, 07 September 2007 05:53 Go to next message
trantuananh24hg
Messages: 668
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 messageGo to next message
Michel Cadot
Messages: 64103
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

Re: How to identify a parse of SQL statement? [message #265921 is a reply to message #265769] Fri, 07 September 2007 20:58 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Dear Michael!!

I wish you're my teacher, every time, till to end of my life.

Thank you!

[Updated on: Fri, 07 September 2007 20:58]

Report message to a moderator

Re: How to identify a parse of SQL statement? [message #266095 is a reply to message #265921] Sun, 09 September 2007 12:34 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
trantuananh24hg wrote on Fri, 07 September 2007 20:58
Dear Michael!!

I wish you're my teacher, every time, till to end of my life.

Thank you!


Where is Michael's Payment?
Razz
Re: How to identify a parse of SQL statement? [message #266101 is a reply to message #266095] Sun, 09 September 2007 13:43 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I gave me my payment: let me know that I helped him.

Regards
Michel
Previous Topic: How can we get statistics on a table being used 24/7 in a database
Next Topic: Table lock Blocking
Goto Forum:
  


Current Time: Sat Dec 03 15:46:54 CST 2016

Total time taken to generate the page: 0.04538 seconds