Some time ago, I wrote a note about using the packaged function dbms_xplan.display_cursor().
I’ve just seen a note on Rob van Wijk’s blog where he investigates many more parameter values that can be used with the function.
When I tried to add a comment to the blog listing a couple more of the options that I knew, I found that I couldn’t do so without creating a google or blogger account. So I’ve posted them here, and I’d be happy for someone with a suitable acccount to add them to his blog so that they’re all in the same place.
‘All’: Prints the Query block/Object Alias section, the Predicate information, and the Column Projection information after the basic plan.
‘Advanced’: as for ‘All’, but also include the Outline information (the set of hints that will reproduce the plan).
‘Outline’: prints just the Outline information and the Predicate information after the basic plan.
I got the ‘Advanced’ one from one of the Oak Table members – I think it may have been Christian Antognini – and guessed the ‘Outline’ one when I saw that it was possible to get the outline from the advanced output.
Oracle documentation explain about ‘ALL’ parameter, but not mentioned any thing about ‘Advanced’ and ‘Outline’. ORACLE_HOME/rdbms/admin/dbmsxpln.sql file has many good example for different procedure/ function and there parameters.
Nice to know 2 more parameter of display_cursor(), Thanks
Comment by Virag Sharma — March 6, 2008 @ 10:41 am UTC Mar 6,2008 |
Why do you suppose Oracle are Parameterising their functions in such an unusual way?
Are they deliberately attempting to make performance tuning the obfuscated art of a closed society?
Why not add these options as pseudo-boolean type parameters and atleast allow them to be revealed by “describe” – if keeping the documentation up to date is too onerous?
Comment by Basil — March 6, 2008 @ 2:56 pm UTC Mar 6,2008 |
Thanks Jonathan for letting me know and for making the list more complete.
I’ve added a small update section because of it.
Regards,
Rob.
Comment by Rob van Wijk — March 6, 2008 @ 10:36 pm UTC Mar 6,2008 |
Basil,
I don’t think they’re trying to make life difficult. It’s probably more a case of “and here’s another good idea” and a quick bolt-on. Once you start doing it one way, it’s hard to re-engineer.
Personally I’d love it if the Statspack code allowed you to select which bit of functionality went into the snapshot by setting boolean flags – as it is I have to hack the code to get just the bits I want. (But at least the source is there for hacking).
Comment by Jonathan Lewis — March 11, 2008 @ 6:34 am UTC Mar 11,2008 |
At least we have access to the spreport.sql script and can query the PERFSTAT schema
so that we can design modified versions of spreport.sql
Can’t do the same with awrrpt.sql at all ! It just calls a stored procedure which
“automagically” generates the report for us.
(BTW, have you used the “NO_OPTIONS=8″ to “ENABLE_ADDM”, modifying awrrpti.sql ?
Hemant
Comment by Hemant K Chitale — March 12, 2008 @ 7:20 am UTC Mar 12,2008 |
Hi Jonathan,
Is there a way to check which step of a SQL execution plan currently being executed?
If we can get timing prediction on it that will be even better.
This will be very helpful for long running sqls with big execution plan and multi table joins etc.
Regards,
Darshan
Comment by Darshan — June 13, 2009 @ 7:27 pm UTC Jun 13,2009 |
Darshan,
There are “approximations” that you can try.
If you know the steps of the plan you could keep checking V$session_wait for the session to check for calls related to disk I/O – this may give you some clues.
If you keep an eye on v$session_longops this may tell you about things like tablescans, sorts and other long operations – but again it’s just clues, not firm information. Gints Plivna has a good article on v$session_longops, and Hemant Chitale has a nice example of the potential traps.
But if you’re on 11g, there’s an option to do real-time monitoring of SQL statements. Greg Rahn wrote up an example of this last year.
Comment by Jonathan Lewis — June 14, 2009 @ 6:36 pm UTC Jun 14,2009 |
Hi Jonathan,
Thanks for the knowledge and links you shared.
Do we have any information documented somewhere with examples on V$SQL_WORKAREA views? How one can leverage information in these.
I have seen these views are populated with some steps of the execution plan underway for a session id. Again it looks like it covers only few cases.
Regards,
Darshan
Comment by Darshan — June 16, 2009 @ 9:56 pm UTC Jun 16,2009 |
Darshan,
The view v$sql_workarea is populated only for lines of execution plans (v$sql_plan) that relate to workarea usage, such as hash joins and sorts. If you’re running 9i and query v$sql_plan, you can do an outer join to v$sql_workarea to get information the most recent and cumulative use made of work areas. But in 10g it’s much easier to get this information with a call to dbms_xplan.display_cursor(). I’ve described this function in a few previous posts, commenting particularly on the hint /*+ gather_plan_statistics */ – but the work area information is always reported by this function – even if you haven’t used the hint.
Comment by Jonathan Lewis — June 20, 2009 @ 5:34 pm UTC Jun 20,2009 |