Home » RDBMS Server » Performance Tuning » retrieve execution plan for running session in Oracle 8i database
retrieve execution plan for running session in Oracle 8i database [message #292442] Tue, 08 January 2008 23:30 Go to next message
wkk1020
Messages: 10
Registered: January 2008
Location: Singapore
Junior Member

hi, anyone can enlighten me the script to retrieve the
execution plan for a running session (like the one we see
in the DBA Studio) from sqlplus for an Oracle 8i database??

Thank you!!
Re: retrieve execution plan for running session in Oracle 8i database [message #292445 is a reply to message #292442] Tue, 08 January 2008 23:41 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
SQL> HELP SET

look at AUTOTRACE
Re: retrieve execution plan for running session in Oracle 8i database [message #292460 is a reply to message #292445] Wed, 09 January 2008 00:22 Go to previous messageGo to next message
wkk1020
Messages: 10
Registered: January 2008
Location: Singapore
Junior Member

Hi, I suppose the can only generate the execution plan for the
current session, how to generate execution plan for other
running sessions (SIDs)??

Thank you.
Re: retrieve execution plan for running session in Oracle 8i database [message #292493 is a reply to message #292460] Wed, 09 January 2008 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Take the query and use EXPLAIN PLAN.

Regards
Michel
Re: retrieve execution plan for running session in Oracle 8i database [message #292503 is a reply to message #292442] Wed, 09 January 2008 01:41 Go to previous messageGo to next message
wkk1020
Messages: 10
Registered: January 2008
Location: Singapore
Junior Member

Hi Michel, thanks for the advise!

I can get the execution plan now with the provided SQL.

I also found below way to generate sql trace (in bdump/udump)
using spid: -

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Obtain the SPID from v$process.
SQL> select username, spid from v$process;

Turn on SQL Trace for the session
SQL> conn / as sysdba
SQL> oradebug setospid 15750
SQL> oradebug unlimit
SQL> oradebug event 10046 trace name context forever, level 12

Turn off SQL Trace for the session
SQL> conn / as sysdba
SQL> oradebug setospid 15750
SQL> oradebug event 10046 trace name context off

Format Trace file using TKprof
- tkprof tracefile outfile

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Just to share......

[Updated on: Wed, 09 January 2008 01:42]

Report message to a moderator

Re: retrieve execution plan for running session in Oracle 8i database [message #292521 is a reply to message #292503] Wed, 09 January 2008 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Instead of using oradebug (normally only for debugging with support help), use dbms_system (before 10g) and dbms_monitor (from 10g).

Regards
Michel
Re: retrieve execution plan for running session in Oracle 8i database [message #292791 is a reply to message #292521] Wed, 09 January 2008 20:49 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
V$SQLPLAN does not exist in 8i, so the best you could do would be to suck the SQL out of V$SQLTEXT and then run it through Explain Plan.

The problem is that in 8i, V$SQLTEXT busts the SQL up into equal-length strings and is hideously difficult to re-concatenate in syntactically correct form.

Ross Leishman
Previous Topic: performance tuning
Next Topic: Split partitions in oracle 8.0.4.3
Goto Forum:
  


Current Time: Mon Dec 05 06:44:11 CST 2016

Total time taken to generate the page: 0.06537 seconds