Re: Display access plan during runtime

On Apr 3, 7:43 am, "sybrandb" <sybra..._at_gmail.com> wrote:
> On Apr 3, 1:09 pm, joker197cin..._at_gmail.com wrote:
>
> > How can I obtain access plan at runtime during a long procedure?
>
> > Any help appreciated.
>
> from 9i onwards: v$sql_plan (you need the address and the hash_value
> of the statement, which can be obtained from v$session_longops)
>
> --
> Sybrand Bakker
> Senior Oracle DBA

Besides view v$sql_plan you also can turn on a trace for the target session from another session, run it for a few minutes, then stop the trace. Take the trace file and run it through tkprof (as well as review the raw trace)

In some cases it is good enought to capture the SQL being ran in a session via one of the v$sql views, edit it a little, and run it through explain plan.

Which method you use will depend on how the target sessions works and the type of performance problem you are facing. Is the session in question stuck running just one long running SQL statement, or if it is cycling through a series of statements and you need to figure out which ones are slower than necessary.

HTH -- Mark D Powell -- Received on Tue Apr 03 2007 - 09:16:32 CDT