Query Parsing [message #419261] |
Thu, 20 August 2009 13:59  |
Nirmala
Messages: 43 Registered: October 2004
|
Member |
|
|
I am trying to make sense of a tkprof output. I am running a simple query with a bind variable. I defined a variable atest and gave it a value. Then i enabled the trace session. Then i ran the below query two times hoping that(since the query has a bind variable )my query would be parsed only once but executed two times. But when I check the tkprof output( I am attaching the file) the result is not the same. I am wondering why the output says parse=2. I checked the size of my shared pool and it has a value of 83886080. Can anyone guide me as what else i should be checking and if i missing anything here.
select *
from ms_sales_test
where sales_id = :atest
-
Attachment: output7.txt
(Size: 5.13KB, Downloaded 725 times)
|
|
|
|
|
Re: Query Parsing [message #419269 is a reply to message #419261] |
Thu, 20 August 2009 14:41   |
Nirmala
Messages: 43 Registered: October 2004
|
Member |
|
|
I am attaching the trace file. Also i have noticed that in the v$sql table there is only one entry for the sql that i am executing. But in the trace as well as the tkprof files i see the parse count=1 each time i execute the query.
I am using Oracle9i Enterprise Edition Release 9.2.0.8.0 for the database and SQL*Plus: Release 9.2.0.7.0 for SQLPLUS.
|
|
|
Re: Query Parsing [message #419270 is a reply to message #419269] |
Thu, 20 August 2009 14:48   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
The parse has been explicitly asked by the client application (SQL*Plus for instance ask it each time) but you can see in raw trace that Oracle returns immediatly from the second parse and does not generate another plan indicating it found everything it needs in the shared pool.
Regards
Michel
[Updated on: Thu, 20 August 2009 14:49] Report message to a moderator
|
|
|
Re: Query Parsing [message #419272 is a reply to message #419270] |
Thu, 20 August 2009 14:58   |
Nirmala
Messages: 43 Registered: October 2004
|
Member |
|
|
Thanks for the clarification Michel. But why does SQLPLUS ask for the parse each time. Is there a way we can make the client application(SQLPLUS in this case) not ask for the parse each time. Sorry if this is dumb and basic question. If you can refer me to further reading it would be of great help.
|
|
|
Re: Query Parsing [message #419295 is a reply to message #419272] |
Fri, 21 August 2009 01:05  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL*Plus ask for a parse each because it is a generic SQL client that is it does not execute static query but always dynamic ones as it can't know which query you will ask.
So to execute a dynamic query, the steps always start with a parse to determine which query it is.
Regards
Michel
|
|
|