3.0 KiB
Executable File
Performance
Displaying daily top SQL for last 7 days:
@exadata/mon_topsql.sql
ASH
ASH Report for SQL ID:
@ash/sqlid_activity.sql
AWR
Display Execution plan history from AWR:
@awr_xplan <sql_id> <PHV> <days> <where_condition>
Example:
@awr_xplan h6hYfr4esZrz % 14 "and 1=1"
Display SQL Text from AWR:
@awr/awr_sqlid <sql_id>
SQL Monitor
List SQL Monitor reports based on a where clause:
@sqlmon_lsrep.sql <condition> <order by>
# @sqlmon_lsrep "x.session_id='303'"
# @sqlmon_lsrep "x.sql_id='g9n768y28mu9m'"
# @sqlmon_lsrep "x.sql_id='g9n768y28mu9m'" 6 asc
SQL Monitor report detail:
@sqlmon_detrep <report_id> <type>
# @sqlmon_detrep 172
# @sqlmon_detrep 172 html
# @sqlmon_detrep 172 active
Run DBMS_SQLTUNE.REPORT_SQL_MONITOR (text mode) for session:
@xp <session_id>
Run custom DBMS_SQLTUNE.REPORT_SQL_MONITOR:
@xprof <report_level> <type> <sql_id|session_id> <sql_id|sid>
# Protect sql_id as in example: @xprof BASIC TEXT sql_id "'a4fqzw4mszwck'"
Explain plan
Display execution plan for last statement for this session from library cache:
@x.sql
@xb.sql
Plan for library cache:
@xi <sql_id> <child#>
@xbi <sql_id> <child#>
Plan for AWR:
@xawr.sql <sql_id> <child#>
Statistics
Column stats details:
@stats_col <OWNER> <TABLE-NAME> % % % %
History of the optimizer statistic operations. Optionally filters on the start time in the format DD/MM/YYYY and the target name (which supports wildcards)
@list_optstat_history.sql <START_DATE> <TARGET>
List STATS operations:
@stats_opls <since> <until> <level> <format>
# @stats_opls sysdate-14 sysdate BASIC TEXT
# @stats_opls "timestamp'2023-01-12 14:00:00'" "timestamp'2023-02-12 14:00:00'" TYPICAL HTML
Detail of a STATS operation:
@stats_opdet <operation_id> <level> <format>
# @stats_opdet 1482
# @stats_opdet 1482 TYPICAL HTML
Trace activation
Display current trace file name:
@t
Activate/deactivate 10046 trace:
@46on <level>
@46off
Divers
Display SQL_ID and PHV for the last SQL:
@hash
Display SQL hint:
@hint <HINT>
Database layout
Tablespaces
@tbs %
Redolog
Redolog informations
@redolog
Redolog switch history
@perf_log_switch_history_count_daily_all.sql
Oracle Directories
@dba_directories
Database links
@dblinks.sql
Table informations:
@dba_table_info
@tab <owner>.<table_name>
@tab_details <owner> <table_name>
Partition informations:
@part_info.sql
@tabpart <owner>.<table_name>
@tab_parts_summary <owner> <table_name>
@tab_parts <owner> <table_name>
Restore points:
@restore_points
Locks
Blocking locks tree RAC aware:
@raclock
Blocking Locks in the databases:
@locks_blocking.sql
@locks_blocking2.sql
Undo
Active undo segments and the sessions that are using them:
@undo_users.sql