Re: Unit of TIME in dbms_xplan

On Dec 27, 6:17 pm, zigzag..._at_yahoo.com wrote:
> On Dec 27, 5:02 pm, Greg Rahn <g..._at_structureddata.org> wrote:
>
>
>
>
>
> > On Dec 27, 1:20 pm, zigzag..._at_yahoo.com wrote:
>
> > > dbms_xplan apperas to generate estimated time and not actual  time, so
> > > I am rephrasing my question. Is there a way to ge actaul time for
> > > each  operation after statement has been executed.
>
> > Yes.  One option is to use the gather_plan_statistics hint followed by
> > dbms_xplan.display_cursor with format=>'ALLSTATS LAST'
> > Demonstrated here:http://structureddata.org/2007/11/21/troubleshooting-bad-execution-pl...
>
> > If you are wanting to go "back in time" and find this info, it's not
> > really available until 11g with real-time SQL monitoring.
>
> > --
> > Regards,
> > Greg Rahnhttp://structureddata.org
>
> Thanks a lot, that gives me information I am looking for,- Hide quoted text -
>
> - Show quoted text -

For versions prior to 11g I would say running SQL Trace on the session is about the best you can do. You will find elapsed time in the raw trace file but depending on your platform and Oracle version the times shown may or may not be reasonably accurate.

HTH -- Mark D Powell -- Received on Mon Dec 29 2008 - 09:14:19 CST