Scripts

Scripts are slowly being re-constituted from the old website, please check back soon or email me if you can’t find what you’re seeking…

ash_xplan.sql

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.


ash_xplan.sql324 downloads

run_ash_xplan.sql

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


awr_evclasstrends.sql

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).


awr_evtrends.sql

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).


awr_evtrends.sql656 downloads

awr_parm_changes.sql

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.


awr_stattrends.sql

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).


awr_stattrends.sql656 downloads

carl.sql

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...


carl.sql666 downloads

cbo_stats.sql

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.


cbo_stats.sql628 downloads

dba_column_usage.sql

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.


estimate_mttr.sql

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.


estimate_mttr.sql536 downloads

exchpart.sql

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.


exchpart.sql621 downloads

sqlhistory.sql

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.


sqlhistory.sql927 downloads

sphistory.sql

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.


Download216 downloads

sp_delta_views.sql

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.


sp_delta_views.sql586 downloads

tracetrg.sql

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.


tracetrg.sql447 downloads

trclvl12.ddl

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"…


trclvl12.ddl457 downloads

trclvl12.sql

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".


trclvl12.sql461 downloads

20 thoughts on “Scripts”

  1. Hey Tim –

    How are you doing? Long time. Hope all is well with you and family!

    Glad to see your new site… miss the scripts I “borrow” from the site and looking forward to seeing them again! I am looking for the top 10 queries script you used to have for 11gr2. O’well dealing with a company with 2 node RAC with constant Cluster Reconfiguration… and yikes things take longer all the time so looking to identify and start tuning the top queries. Looking forward to your RMOUG 2013 presentations.

    Regards,
    Ravi

    1. Ravi, Good to hear from you! Sorry my papers and scripts are off-line at the moment — I’m just doing a million things at once right now, including learning more about WordPress. I’ll try have the papers and scripts posted in a media-gallery widget over the next 2 weeks.
      More soon…

  2. TIM, EXCELLENT POST ….. YOUR SCRIPTS ARE VERY USEFULL TO LEARN AND TO RESEARCH IN MY JOB.
    I’M FROM ARGENTINA
    BEST REGARDS AND THANKS A LOT!

    NICOLAS

  3. Hi,
    Very informative and helpful scripts you have posted here.
    I am looking for trigger referred in a post (http://www.evdbt.com/trclvl12.ddl) which is helpful to find the column name in case of ora-01438, but not able to find it. Can you please share it.
    looking forward to hear from you soon.

    Regards,
    Sahir Nadeem

    1. Sorry for breaking the link; I switched over from my old HTML-based website to the present one powered by WordPress back in November, and I’m slowly getting the new website re-constituted with all the stuff that was on the old. You should find the TRCLVL12 script available for download on this page now.

      Thanks!!!

  4. Hi Ravi,

    Good collection of scripts !!! I have not gone through all but few of them. Wants to know do you have any script or data for statistics ? or else if you can guide me about what is the optimal way to look for statistics then good for me.

    Thanks, Sonia

    1. Sonia,
      There is my bare-bones “cbo_stats.sql” script posted here, but if you want the complete picture on statistics used by and relevant for a specific SQL statement, look toward SQLT by Carlos Sierra of Oracle and available for download from http://support.oracle.com.
      Hope this helps…
      -Tim

  5. Hi Tim,

    Thanks for your reply. From your reply I try to find on metalink but did not find “cbo_Stat.sql” in their search tool. can you guide me with link?

    Thanks again.
    Sonia

    1. Sonia,

      “cbo_stats.sql” is my script, available on this page. SQLT (or SQLTXPLAIN) is a package available from Oracle Support on their website.

      Sorry for the confusion…

      -Tim

  6. Hi Tim,

    How are you ?
    Hope you are doing well.

    To be very frank, I was really not aware of who is “Tim Gorman” until I was assigned on a task to find out all the oracle instances running on our company network. So when I have done some troubleshooting, I came to know about you and your contributions for oracle community. we all oracle users will be always thankful to you for all of your contributions.

    Now without stretching it much, straight forward coming to the point. Can you please explain the logic you have used in your famous scripts tnsprobe.sh and oraprobe.sh to find out running oracle instance on network.

    Or it would be great if you can upload those scripts here. :)

    Regards,
    Anurag

  7. Hi!

    Could you please add the sphistory.sql script? In the comments of sqlhistory.sql the script is mentioned.
    The AWR script has been really useful for me!

    -Jari

  8. Thank you SO MUCH for your SQLHistory script! I was looking for exactly this! You have saved me hours of combing through dba_hist tables or, yikes!, trying to write this myself.

Leave a Reply

Your email address will not be published. Required fields are marked *


× three = 3

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Evergreen Database Technologies, Inc.