“New” function for viewing execution plans – dbms_xplan.display_cursor

by Granville Bonyata on January 24, 2012

Having worked with Oracle for many (twenty!) years, I sometimes find new features released several years ago that I didn’t catch at the time. Most recently, it would be that 10g added the display_cursor function to dbms_xplan. Most usefully, you can pass in the Sql-Id (from v$sql) and get the formatted execution plan for a SQL statement:

If in the cache:
SELECT * FROM TABLE(dbms_xplan.display_cursor('d5dj7zctjwy1y', format=>'advanced'));

Or pull all the plans that are in the AWR data:
SELECT * FROM TABLE(DBMS_XPLAN.display_awr('d5dj7zctjwy1y', format=>'advanced'));

More information here: http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_xplan.htm

Very useful when you’re at a site that doesn’t have TOAD…

Previous post:

Next post: