Home » RDBMS Server » Performance Tuning » Query wih bind variables
Query wih bind variables [message #294203] Wed, 16 January 2008 16:29 Go to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Hi,

There is a query which takes 1 day to complete.Since it is fired from application, i dont have the quer handy.I got the query through toad application but it has lot of bind variables with it .

I tried with following command (from sys user)to trace that session , but it is not generating any trace-file in user_dump_dest.

exec sys.dbms_system.set_sql_trace_in_session(35, 15, true);

any idea what could be the issue here.

Thanks
Re: Query wih bind variables [message #294271 is a reply to message #294203] Thu, 17 January 2008 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure you are in the correct server directory?
Are you sure you are searching for the correct file?
Are you sure the session (35,15) is really executing something?

Regards
Michel
Re: Query wih bind variables [message #294280 is a reply to message #294271] Thu, 17 January 2008 01:25 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I suspect SQL*Trace will only create the file when the first trace message arrives (someone else may be able to confirm or deny this).

If so, then if you have a big join or sort then it might spend all day between the execution and the fetch of the first row. If you started the trace after the execute then the trace file might not be created for a while, but it will get created eventually.

Ross Leishman
Re: Query wih bind variables [message #294317 is a reply to message #294203] Thu, 17 January 2008 03:55 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Michel ,
Yes, everything is correct .

rleishman,
What you said is correct .it may/maynot generate trace immediately.i got trace only one time though i tried more than 10 times.

all,
what is the procedure to check explain plan for a query with bind variables ?Will trace file has query with bind variables replaced ?

[Updated on: Thu, 17 January 2008 04:00]

Report message to a moderator

Re: Query wih bind variables [message #294329 is a reply to message #294317] Thu, 17 January 2008 04:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Trace file has query with bind variables and a section with bind variable values if the option has been set.
Execution plan is generated at parse time or chooses at execution time, if you start the trace after these steps you will not have the execution plan in the file.

Regards
Michel
Re: Query wih bind variables [message #294335 is a reply to message #294203] Thu, 17 January 2008 04:46 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
So if its already start running .. what is the alternative ,to capture why exactly it takes long time to run ?
Re: Query wih bind variables [message #294341 is a reply to message #294335] Thu, 17 January 2008 05:00 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
- Run it manually in your current session.
- Query v$ views that are available in your version

Regards
Michel
Previous Topic: SQL statement takes too long to run
Next Topic: Delete data and transaction log problem
Goto Forum:
  


Current Time: Sat Dec 10 20:26:26 CST 2016

Total time taken to generate the page: 0.07596 seconds