Home » SQL & PL/SQL » SQL & PL/SQL » Query Parsing (9.2.0.8.0)
Query Parsing [message #419261] Thu, 20 August 2009 13:59 Go to next message
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 #419264 is a reply to message #419261] Thu, 20 August 2009 14:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Put the raw trace not tkprof one.

Regards
Michel
Re: Query Parsing [message #419265 is a reply to message #419261] Thu, 20 August 2009 14:18 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
See if this helps:
http://tkyte.blogspot.com/2007_04_01_archive.html#8670154816112999970

Are you using sqlplus? What version of client & database?
update: I do see you mention 9.2.0.8

[Updated on: Thu, 20 August 2009 14:20]

Report message to a moderator

Re: Query Parsing [message #419269 is a reply to message #419261] Thu, 20 August 2009 14:41 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: ORA-08177
Next Topic: Find unmatched(3 merged)
Goto Forum:
  


Current Time: Thu Feb 06 20:28:40 CST 2025