PL/SQL package to mimic the DBMS_XPLAN package while augmenting the execution plan information with real-time "actual" metrics from Active Session History (ASH) and SQL Plan Monitor, using queries on GV$ACTIVE_SESSION_HISTORY and GV$SQL_PLAN_MONITOR, respectively.

Rather than trying to cram all of these metrics into a single report output, this package instead prepends one metric apiece into multiple displays of the execution plan.

This package is described in my presentation entitled "Real-Time SQL Monitoring", presented at the Hotsos Symposium 2014 in Irving TX, the UTOUG "Training Days 2014" in Sandy UT, and Collaborate 2014 in Las Vegas.

SQL*Plus script illustrating one way to run the DISPLAY_CURSOR function in the ASH_XPLAN package.

SQL*Plus script to display “trends” for all general wait-classes at first, then prompt to select a specific wait-class for more specific analysis of the wait-events comprising that wait-class, captured in the AWR repository over time, in order to display summary totals hourly and daily as a ratio.  The intent is to start from a high level and then drill down into the trends of wait-events over time, highlighting the greatest deviations from the norm.  It is also useful for use after a change has been made, to demonstrate the impact (i.e. “before” vs “after” effects).

SQL*Plus script to display “trends” for specific wait events captured in the AWR repository over time, and display summary totals hourly and daily as a ratio.  The intent is to highlight the greatest deviations from the norm over time.  It is also useful for use after a change has been made, to demonstrate the impact (i.e. “before” vs “after” effects).

SQL*Plus script to display changes to initialization parameters captured in the AWR repository over time.  The intent is to help answer the perennial question of “what changed?”, at least with respect to database initialization parameters.

SQL*Plus script to display “trends” for specific database statistics captured in the AWR repository over time, and display summary totals hourly and daily as a ratio.  The intent is to highlight the greatest deviations from the norm over time.  It is also useful for use after a change has been made, to demonstrate the impact (i.e. “before” vs “after” effects).

SQL*Plus script to Calculate Average Row Length based on a full or partial sample of a table, and then calculate what the average row length would be if the columns were re-arranged so that the most-NULL columns trail in each row, potentially taking advantage of Oracle’s manner of not storing anything for trailing NULL columns.  This is a very useful script when you have a very wide and sparsely-populated table – the space savings can be tremendous.

Updated on 03-June 2013 - fixed serious bug, please download again to replace previous copies...

SQL*Plus script to display the statistics used by the cost-based optimizer (CBO) at the global table- and index-levels, as well as at the partition and sub-partition levels, if they exist.

SQL*Plus script with DDL commands to create a view named DBA_COLUMN_USAGE on the useful (but normally hidden) data dictionary table SYS.COL_USAGE$.  This internal table is populated by the Oracle cost-based optimizer (CBO) in Oracle9i and above with information about how columns are used in the WHERE clauses of SQL statements.  This view makes this data more readily accessible for usage-tracking purposes.  The intent of this script is to create the view under the SYSTEM schema, or any schema other than SYS, so as not to interfere with Oracle's data dictionary.

SQL*Plus script to query the RMAN “recovery catalog” repository to estimate how long a restore/recovery operation might take, based on how long it took to perform the backups of the pieces that will need to be restored.  This is certainly not a perfect estimate, as the amount of time to perform a backup is not a reliable predictor of the amount of time to perform a restore and recovery, but it provides a good general idea.

SQL*Plus script with DDL commands to create the package EXCHPART, with its procedures PREPARE and FINISH.  These packaged procedures facilitate the use of the ALTER TABLE … EXCHANGE PARTITION command while loading data into a data warehouse.

SQL*Plus script to display the “history” of resource consumption and execution plans used by a specified SQL statement, using the detailed snapshot information captured in AWR. This script is intended to augment the cumulative information found in the standard “awrsqrpt.sql”. It is useful for use after a change has been made, to demonstrate the impact (i.e. “before” vs “after” effects), and it is useful for detecting when something caused a SQL statement’s execution plan to change.

This is the older STATSPACK-based predecessor to the newer AWR-based "sqlhistory.sql" script.  Like the "sqlhistory.sql" script, this script will display execution plan information along with a history of "sightings" within STATSPACK for the SQL statement specified.

SQL*Plus script containing DDL to create views that automatically calculate the "delta" or difference between adjacent samples of data gathered by the STATSPACK package.  Each of these views is named the same as the corresponding tables in the STATSPACK repository, except that the prefix of "STATS$" has been replaced by a prefix of "DELTA$".  These views are extremely useful in using the STATSPACK repository like a data warehouse of Oracle performance tuning information.

SQL*Plus script containing DDL to create a database-level AFTER LOGON event trigger to enable extended SQL Trace at level 12 (i.e. waits and binds) when a specific Oracle account/user connects to the database.  The script creates the trigger and leaves it in DISABLED state so that trace files are not created unintentionally.  Be sure to ENABLE the trigger when you want to use it, and DISABLE it again when you are done.

SQL*Plus script to create the TRCLVL12 stored procedure, which uses the DBMS_SYSTEM.SET_EV procedure to enable SQL Tracing at level 12 (i.e. display "bind variable" values as well as "wait event" information).  Use the SQL*Plus script trclvl12.sql (also downloadable here) to make using the TRCLVL12 stored procedure a little more "user-friendly"…

SQL*Plus script to make using the TRCLVL12 stored procedure a little more "user-friendly";  the TRCLVL12 procedure source code is downloadable on this page as the file "trclvl12.ddl".