Home » SQL & PL/SQL » SQL & PL/SQL » Parse (Oracle9.2.0.3)
Parse [message #411163] Thu, 02 July 2009 03:41 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member

While parsing does all are different?

A.  SELECT LNAME FROM EMP WHERE EMPNO = 12; 
B.  SELECT lname FROM emp WHERE empno = 12;
C.  SELECT lname FROM emp WHERE empno = :id;
D.  SELECT lname FROM   emp     WHERE  empno = 12;




My answer is Yes...
Please let me know if i m wrong here
Re: Parse [message #411168 is a reply to message #411163] Thu, 02 July 2009 03:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
They are different for the parsing steps.

Regards
Michel
Re: Parse [message #411169 is a reply to message #411168] Thu, 02 July 2009 03:55 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Thank you Sir
Re: Parse [message #411187 is a reply to message #411169] Thu, 02 July 2009 05:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I just wrote a page in the Wiki on parsing.

Regards
Michel
Re: Parse [message #411312 is a reply to message #411187] Fri, 03 July 2009 01:38 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
A nice Article Thanks for sharing
Re: Parse [message #411948 is a reply to message #411187] Tue, 07 July 2009 05:59 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Here a few questions come to my mind:


select col1,col2 from test;

select col2,col1 from test;



How parsing impacts the above two statement? Will there be different response time for the above two sql if the parsing cause performance issue?

Thanks
Re: Parse [message #411950 is a reply to message #411948] Tue, 07 July 2009 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Parsing will lead to the same execution plan in the same conditions for both these statements.

Regards
Michel
Re: Parse [message #411960 is a reply to message #411950] Tue, 07 July 2009 06:14 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
But while parsing Oracle wont find that both statement are similar.Am I wrong?

While parsing the second sql will it find the shared pool? What I understood is Parsing is done at compilation level.Before executing the real sql
Re: Parse [message #411964 is a reply to message #411960] Tue, 07 July 2009 06:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But while parsing Oracle wont find that both statement are similar.Am I wrong?

You are wrong.
Parse will find 2 different statements.

Regards
Michel
Re: Parse [message #411974 is a reply to message #411964] Tue, 07 July 2009 06:30 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
If parse will find 2 different statement? How it is going to lead to same performance ?


Quote:

Parsing will lead to the same execution plan in the same conditions for both these statements.



if parse finds two statement are different.what will be its impact? nothing?

Sir, I am a bit confused here?

If Parse will find two different statement ..and as you said
plan will be the same...whats the use of parse then? Its finding two different statement and finally no impact?

Re: Parse [message #411977 is a reply to message #411974] Tue, 07 July 2009 06:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The two statements are not identical pieces of text - therefore the Parser will treat them as different pieces of SQL.

After the statements have been parsed, the execution plans for them will be the same, as they retrieve the same data in the same manner.

The only impact is that your application will do two hard parses rather than one, and will hold details of two pieces of sql rather than one.

Re: Parse [message #411994 is a reply to message #411977] Tue, 07 July 2009 07:05 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
so that means its nothing with performance...response time ?
Re: Parse [message #412006 is a reply to message #411163] Tue, 07 July 2009 07:30 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well if you run the 1st statement a lot it might run slow the first time and then much faster each subsequent time as it no longer has to do the hard parse.
If you then run the 2nd statement once, it'll take about as long as the first one did the first time you ran it.
Re: Parse [message #412007 is a reply to message #411994] Tue, 07 July 2009 07:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Exactly, nothing.

Regards
Michel
Re: Parse [message #412013 is a reply to message #412007] Tue, 07 July 2009 07:35 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
But the same statement is written 100 times for say 10 combination of columns in different order then it will go for 100 different hard parse and also, take extra space in memory.
Am I right?

Re: Parse [message #412016 is a reply to message #412013] Tue, 07 July 2009 07:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yes - if all your SQL requires hard parses you will notice a drop in performance.
Re: Parse [message #412021 is a reply to message #412016] Tue, 07 July 2009 07:45 Go to previous message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Many thanks to all of you...
Previous Topic: update query failing
Next Topic: create excel
Goto Forum:
  


Current Time: Sun Dec 04 18:31:34 CST 2016

Total time taken to generate the page: 0.10140 seconds