Parse [message #411163] |
Thu, 02 July 2009 03:41  |
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 #411948 is a reply to message #411187] |
Tue, 07 July 2009 05:59   |
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 #411960 is a reply to message #411950] |
Tue, 07 July 2009 06:14   |
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 #411974 is a reply to message #411964] |
Tue, 07 July 2009 06:30   |
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   |
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 #412006 is a reply to message #411163] |
Tue, 07 July 2009 07:30   |
cookiemonster
Messages: 13965 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 #412013 is a reply to message #412007] |
Tue, 07 July 2009 07:35   |
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   |
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.
|
|
|
|