Scripts

Exadata

check_row_comp.sql —– show compression format being used
fsx.sql —– Find SQL and report whether it was Offloaded and % of I/O saved.
fsx_awr.sql —– Find SQL in AWR and report whether it was Offloaded and % of I/O saved.
fsxo.sql —– Find SQL and report whether it was Offloaded and % of I/O saved.
report_sql_monitor.sql —– Execute DBMS_SQLTUNE.REPORT_SQL_MONITOR function – Realtime SQL Monitoring

Performance Monitoring

as.sql —– Query Active Sessions
awr_evtrends.sql —– Tim Gorman’s script to display event trends
dbtime.sql —– Find busiest time periods in AWR.
mystats.sql —– Just queries v$mystat for stat_name
os_pid.sql —– find os pid from sid, or sid from os pid
sql_monitor.sql —– Query the v$sql_monitor view
we.sql —– show wait events for a SQL_ID or SQL_IDs that have waited for some event – ASH
we_awr.sql —– show wait events for a SQL_ID or SQL_IDs that have waited for some event – ASH DBA_HIST_ACTIVE_SESS_HISTORY
ss.sh —– runs script how_many times in background via nohup

Plan Stability

accept_STA_profile.sql —– creates recommended profile
accept_sql_profile.sql —– Accept SQL Profile
accept_tuning_task.sql —– Accept tuning task
alter_sql_profile.sql —– (dbms_sql_tune.alter_sql_profile) – (name, category, status, description, fixed)
awr_plan_change.sql —– shows history of plan changes
awr_plan_stats.sql —– shows stats (avg_etime, avg_lio, etc) for statements by plan
baseline_hints.sql —– Get baseline hints
baselines.sql —– Find SQLs with SQL baseline
bbv.sql —– Build SQL*Plus test script w/ variable definitions – including peeked binds (OTHER_XML) – formerly build_bind_vars.sql
bbv2.sql —– Build SQL*Plus test script w/ variable definitions – including binds from V$SQL_BIND_CAPTURE – formerly build_bind_vars2.sql
bbva.sql —– Build SQL*Plus test script w/ variable definitions – including binds from AWR – formerly build_bind_vars_awr.sql
bbvp.sql —– Build SQL*Plus test script using pl/sql for dates
check_use_outlines.sql —– This script uses oradebug to see if USE_STORED_OUTLINES has been set.
child_load_time.sql —– Show the last_load_time of the SQL
coe_xfr_sql_profile.sql —– SQL Profile building script from SQLTXPLAIN
col_stats.sql —– Shows column stats including max and min
create_1_hint_sql_profile.sql —– Prompts for a hint and makes a profile out of it – creates single line profile
create_STA_task.sql —– creates a single statement task
create_baseline.sql —– Creates a SQL Baseline on a SQL statement in the shared pool.
create_display_raw.sql —– Used by col_stats.sql to convert raw max and min
create_new_rowid.sql —– Creates function new_rowid, returns the new format rowid if you give it the obj#, file#, block#, and row#
create_old_rowid.sql —– Creates function old_rowid, returns the old format rowid if given the new format id
create_outline.sql —– Create outline with DBMS_OUTLN.CREATE_OUTLINE
create_outline_signature.sql —– Create outline_signature function
create_sql_profile.sql —– Create SQL Profile based on Outline hints in V$SQL.OTHER_XML.
create_sql_profile_awr.sql —– creates profile for plan in AWR history
create_tuning_task.sql —– Create Tuning Task – SQL Tuning Advisor
diff_table_stats.sql —– Shows what’s changed
disable_sql_profile.sql —– (dbms_sql_tune.alter_sql_profile) – disable
display_raw.sql —– a little function to display raw from Greg Rahn
dplan.sql —– show plan for statement in V$SQL (uses dbms_xplan)
dplan_alias.sql —– dbms_xplan alias format option
dplan_allstats.sql —– show plan with extended stats
dplan_awr.sql —– show plan for statement in AWR history
dplan_binds.sql —– show plan typical +peeked_binds
drop_sql_profile.sql —– (dbms_sql_tune.drop_sql_profile)
drop_tuning_task.sql —– Drop Tuning Task – SQL Tuning Advisor
enable_sql_profile.sql —– (dbms_sql_tune.alter_sql_profile) – enable
exchange_outline_hints.sql —– Exchange the hints between two outlines, this will not drop either of the outlines
execute_tuning_task.sql —– Execute Tuning Task – SQL Tuning Advisor
find_sql.sql —– find sql in V$SQL – also shows plan_hash, etime, username
find_sql2.sql —– find sql in V$SQL – also shows outline info
find_sql9.sql —– find sql in V$SQL 9i
find_sql_acs.sql —– Queries v$sql and shows ACS related columns
find_sql_awr.sql —– find sql in DBA_HIST_SQLSTAT
find_sql_by_cost.sql —– Queries v$sql_plan and v$sqlarea for SQL cost related info
find_sql_outline.sql —– Find SQLs with outlines
find_sql_profile.sql —– Find SQL profile
find_sql_rows.sql —– Queries rows_processed of SQL
find_sql_stats.sql —– Queries v$sql for detailed SQL stats
find_sql_stats9.sql —– Queries v$sql for detailed SQL stats – 9i
find_sql_using_outline.sql —– (v$sql where outline_category is not null)
find_sql_using_profile.sql —– (v$sql where sql_profile is not null)
fix_sql_profile_hint.sql —– Replaces a hint in a sql profile.
fixed_view_def.sql —– Query view definition
flush_sql.sql —– flush a single SQL statement using dbms_shared_pool.purge (11g & 10.2.0.4)
flush_sql10.sql —– Flush a single SQL statement – prompts for two values (sql_id & child_number)
flush_sql10p.sql —– Flush a single SQL statement – prompts for sql_id, creates and drops a Profile which as a side affect flushes SQL
fm.sql —– Creates a SQL Profile on a statement adding the MONITOR hint.
fs.sql —– find sql in V$SQL – same as find_sql.sql but shorter filename
gps.sql —– Creates a SQL Profile on a statement adding the GATHER_PLAN_STATISTICS hint.
lock_STA_profile.sql —– Create new SQL Profile to “lock” plan generated by STA Profile – removes opt_estimate hints
mismatch.sql —– shows why new child created using V$SHARED_CURSOR
move_sql_profile.sql —– Moves a SQL Profile from one statement to another.
multi_plans.sql —– This script searchs the shared pool for SQL stataments with How_Many (or more) distinct plans
other_xml.sql —– Hints are stored for every statement – OTHER_XML
outline_hints.sql —– This script lists the hints associated with a given outline.
outline_startup_trigger.sql —– Outline startup trigger
outlines.sql —– This script lists all outlines and shows whether they are enabled and if they have been used
outlines2.sql —– Same as outlines.sql but it also joins to the v$sqlarea view to get the sql_id for the SQL
parm_mods.sql —– show parameters that have changed
parms.sql —– Display parameters and values – (regular and hidden)
pif.sql —– Fix INDEX hints in SQL Profiles.
px_mystats.sql —– Queries v$pq_tqstat for px info
report_tuning_task.sql —– View recommendations of SQL Tuning Advisor
reset_parameter.sql —– Reset database parameter
rg_sqlprof1.sql —– Does the real work to create a profile for a statement in the shared pool
sql_hints.sql —– see the outline hints for a SQL statement – v$sql_plan
sql_hints_awr.sql —– see the outline hints for a SQL statement – AWR dba_hist_sql_plan
sql_profile_distinct_hints.sql —– sql profile distinct hints
sql_profile_hints.sql —– Show hints associated with a SQL Profile.
sql_profile_hints11.sql —– Show hints associated with a SQL Profile – 11g
sql_profiles.sql —– lists profiles (dba_sql_profiles)
table_stats.sql —– Shows table, column, index, stats for a table
tuning_task_hints.sql —– Check the hints that will be applied by SQL Profile
tuning_tasks.sql —– Monitor the tuning tasks
unstable_plans.sql —– Attempts to find SQL statements with plan instability.
whats_changed.sql —– Find statements that have significantly different elapsed time than before.

Trace and Dumps

dump_block.sql —– dump the block
dump_blocks.sql —– dump the min/max blocks
find_trace.sql —– Find my trace file name
find_trace11.sql —– Find the trace file name 11g
find_trace2.sql —– Find the trace file name
obj_blocks.sql —– Get the list of all the blocks mapped to a specific object
obj_by_hex.sql —– Shows object from hex_value
save_u.sql —– Saving Rows from Corrupt Blocks based on Toon Koppelaars write up

You can also download all of the scripts as one zip file here last updated 20110529