Home » RDBMS Server » Performance Tuning » get list of all sql's executed in a session with out sql trace (Oracle, 10g, Unix)
get list of all sql's executed in a session with out sql trace [message #402104] Thu, 07 May 2009 11:19 Go to next message
yugi.adapala
Messages: 10
Registered: April 2009
Junior Member
I need list of all SQL id's or SQL statements executed in a
session(we have an in house product which runs one process in a session).

why i need them:

1) I want to get the execution plans for the SQL's executed in a session(thats for one process). I know we can get execution plan in trace file but i just want to get execution plan from V$SQL_PLAN table

2) I want to pass all SQL statements to dbms_sqltune built in
package to get recommendations on how to tune them.

my approach now is:

turn on plsql trace with trace level set to 32(i.e., SQL's only)

i want to know if i can use v$active_session_history(limitation with this table is it can only hold sql's executed in last 1 second) or dba_hist_active_sess_history which is a snap shot table for v$active_session_history.

how can i get all SQL id's from dba_hist_active_sess_history executed in a session> or is there any other way to get SQL's executed in a session

i appreciate your thoughts and your work

thanks
Yugi
Re: get list of all sql's executed in a session with out sql trace [message #402110 is a reply to message #402104] Thu, 07 May 2009 11:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) i just want to get execution plan from V$SQL_PLAN table
so do it
2) I want to pass all SQL statements to dbms_sqltune built in package to get recommendations on how to tune them.
so do it

how can i get all SQL id's from dba_hist_active_sess_history executed in a session
With SELECT

Quote:
I need list of all SQL id's or SQL statements executed in a session

No other way than extended audit all statements or trace.

Regards
Michel
Re: get list of all sql's executed in a session with out sql trace [message #402118 is a reply to message #402104] Thu, 07 May 2009 11:54 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Why process SQL statements that are performing OK?
Why not focus on the SQL with the largest elapsed time?
Re: get list of all sql's executed in a session with out sql trace [message #402127 is a reply to message #402118] Thu, 07 May 2009 13:18 Go to previous messageGo to next message
yugi.adapala
Messages: 10
Registered: April 2009
Junior Member
i will be filtering based on elapsed time after i have all SQL's.



thanks
Yugi

[Updated on: Thu, 07 May 2009 13:26]

Report message to a moderator

Re: get list of all sql's executed in a session with out sql trace [message #402130 is a reply to message #402127] Thu, 07 May 2009 13:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't just try to get the max elapsed queries which is easily possible rather than trying to get ALL sql which is not possible and then filter?

Regards
Michel
Re: get list of all sql's executed in a session with out sql trace [message #402293 is a reply to message #402130] Fri, 08 May 2009 09:04 Go to previous messageGo to next message
yugi.adapala
Messages: 10
Registered: April 2009
Junior Member
how can i get max elapsed queries? which table should i be querying?


thanks
Yugi
Re: get list of all sql's executed in a session with out sql trace [message #402296 is a reply to message #402293] Fri, 08 May 2009 09:29 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Either run Statspack, or have a look at v$sql.
On it you'll find such wonderfully useful columns as Executions, cpu_time and Elapsed_time.

Getting the Sql with the longest elapsed time per execution from here is left as an excercise for the reader.
Previous Topic: 10-15 seconds on a 200 record DataSet
Next Topic: Query "Statements run in past 24 hrs with execution time"
Goto Forum:
  


Current Time: Wed Dec 07 03:13:49 CST 2016

Total time taken to generate the page: 0.06128 seconds