Oracle Release 10.2
AWR Advanced Graphical Reports
AWR Basic Graphical Reports
AWR Reports
Automatic SQL Tuning
Buffer Cache
Database Monitoring
Database Objects
Database Overview
Database Parameters
Enqueues
Fixed Tables
I/O Tuning
Latches
Miscellaneous
RMAN
Redo Logs
Reports from the Dump Destinations
Rollback Segments
SQL Tuning
Sessions
Shared Pool
Statspack Advanced Graphical Reports
Statspack Basic Graphical Reports
Statspack Reports
Stored Outlines
System and OS Statistics
Wait Events
Workareas
Working under normal (not SYS) Account

Back to Directory of Topics

AWR Advanced Graphical Reports
Start Comment Description SQL Result Protocol
awr_5_top_enq_graph10g
 reports 5 top enqueues from the 
AWR repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_enq_stat - y-range in the following
                       format: <number>:<number>
Show SQL Show Result On/Off
awr_5_top_events_graph10g
 reports 5 top non-idle wait events from the 
AWR repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_wait_event - y-range in the following
                       format: <number>:<number>
Show SQL Show Result On/Off
awr_5_top_files_graph10g
 reports 5 top data files for the average
duration of one physical read from the 
AWR repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_file_stat - y-range in the following
                       format: <number>:<number>
Show SQL Show Result On/Off
awr_5_top_latches_graph10g
 reports 5 top latches from the 
AWR repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_latch_stat - y-range in the following
                       format: <number>:<number>
Show SQL Show Result On/Off
awr_5_top_segs_graph10g
 reports 5 top segments for physical reads 
from the AWR repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_seg_stat - y-range in the following
                       format: <number>:<number>
Show SQL Show Result On/Off
awr_5_top_sp_stats_graph10g
 reports 5 top shared pool statistics 
from the AWR repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_sga_stat - y-range for the sga
                   statistics in the following
                   format: <number>:<number>
Show SQL Show Result On/Off
awr_all_cpu_stats_graph10g
 reports all system cpu statistics and the 
number of processes  from the AWR 
repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_sys_stat - y-range in the following
                     format: <number>:<number>,
gr_yrange_sys_curr_stat - y-range for the number
                   of processes in the following
                   format: <number>:<number>
Show SQL Show Result On/Off
awr_cpu_osstats_graph102
 reports cpu os statistics from the AWR 
repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_os_stat - y-range in the following
                     format: <number>:<number>,
gr_yrange_os_curr_stat - y-range in the following
                     format: <number>:<number>
Show SQL Show Result On/Off
awr_io_events_stats_graph10g
 reports io wait events and statistics  
from the AWR repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_wait_event - y-range in the following
                       format: <number>:<number>,
gr_yrange_sys_stat - y-range in the following
                       format: <number>:<number>
Show SQL Show Result On/Off
awr_link_events_stats_graph10g
 reports dblink wait events and statistics  
from the AWR repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_wait_event - y-range in the following
                       format: <number>:<number>,
gr_yrange_sys_stat - y-range in the following
                       format: <number>:<number>
Show SQL Show Result On/Off
awr_logons_graph10g
 reports logons statistics from the AWR 
repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_sys_stat - y-range for the cumulative
                     logons in the following
                     format: <number>:<number>,
gr_yrange_sys_curr_stat - y-range for the current
                   logons in the following
                   format: <number>:<number>
Show SQL Show Result On/Off
awr_page_osstats_graph102
 reports paging os statistics from the AWR 
repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_os_stat - y-range in the following
                     format: <number>:<number>
Show SQL Show Result On/Off
awr_pq_downgr_stats_graph10g
 reports downgrade statistics for parallel 
operations from the AWR repository as a 
graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_sys_stat - y-range in the following
                     format: <number>:<number>
Show SQL Show Result On/Off
awr_pq_stats_graph10g
 reports system statistics for parallel 
operations from the AWR repository as a 
graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_sys_stat - y-range in the following
                     format: <number>:<number>
Show SQL Show Result On/Off
awr_wait_cpu_stat_graph10g
 reports total waits for non-idle 
events and cpu statistics from the AWR
repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_sum_waits - 
            y-range in the following format: 
            <number>:<number>,
gr_yrange_sys_stat - 
            y-range in the following format:
            <number>:<number>
Show SQL Show Result On/Off

Back to Directory of Topics

AWR Basic Graphical Reports
Start Comment Description SQL Result Protocol
awr_enq_stats_bgraph10g
 reports enqueue statistics from the 
AWR repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default: S,
gr_title - graphics title,
enq_stat_name - statistics to select
	    (TOTAL_REQ#, TOTAL_WAIT#,
             SUCC_REQ#, FAILED_REQ#,
             CUM_WAIT_TIME),
            default : CUM_WAIT_TIME,
gr_abs_or_rel_enq_stat - 
   (abs - absolute values will be 
          presented on the graphics,
    rel - values per sec. will be
          presented on the graphics,
    default : rel),
gr_y_or_y2_enq_stat -
    (y - y-axis will be used,
     y2 - y2-axis will be used,
     default : y),
gr_yrange_enq_stat - y-range in the following
                       format: <number>:<number>,
gr_top_enq_stat - number of top enqueues,
                   default : 0,
gr_stat_name_N - name of an enqueue in the
                 following format:
                 <type> - <req. reason>, for ex.
                  TX - contention,
                 default : no name 
Show SQL Show Result On/Off
awr_file_stats_bgraph10g
 reports data file statistics from the 
AWR repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default: S,
gr_title - graphics title,
file_stat_name - statistics to select
            (READTIM,WRITETIM,PHYRDS, 
             PHYWRTS, PHYBLKRD, 
             PHYBLKWRT, READTIM/PHYRDS),
            default : READTIM/PHYRDS,
gr_abs_or_rel_file_stat - 
   (abs - absolute values will be 
          presented on the graphics,
    rel - values per sec. will be
          presented on the graphics,
    default : rel),
gr_y_or_y2_file_stat -
    (y - y-axis will be used,
     y2 - y2-axis will be used,
     default : y),
gr_yrange_file_stat - y-range in the following
                       format: <number>:<number>,
gr_top_file_stat - number of top data files,
                   default : 0,
gr_stat_name_N - name of a data file,
                 default : no name 
Show SQL Show Result On/Off
awr_latch_stats_bgraph10g
 reports latch statistics from the 
AWR repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default: S,
gr_title - graphics title,
latch_stat_name - statistics to select
            (GETS,MISSES,SLEEPS, 
             IMMEDIATE_GETS, 
             IMMEDIATE_MISSES, 
             SPIN_GETS,WAIT_TIME),
            default : SLEEPS,
gr_abs_or_rel_latch_stat - 
   (abs - absolute values will be 
          presented on the graphics,
    rel - values per sec. will be
          presented on the graphics,
    default : rel),
gr_y_or_y2_latch_stat -
    (y - y-axis will be used,
     y2 - y2-axis will be used,
     default : y),
gr_yrange_latch_stat - y-range in the following
                       format: <number>:<number>,
gr_top_latch_stat - number of top latches,
                   default : 0,
gr_stat_name_N - name of a latch,
                 default : no name 
Show SQL Show Result On/Off
awr_os_curr_stats_bgraph10g
 reports current (not cumulative) os 
statistics from the AWR repository 
as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S
gr_title - graphics title,
gr_y_or_y2_os_curr_stat -
    (y - y-axis will be used,
     y2 - y2-axis will be used,
     default : y),
gr_yrange_os_curr_stat - y-range in 
      he following format: <number>:<number>,
gr_stat_name_N - name of a system statistics,
                 default : no name   
Show SQL Show Result On/Off
awr_os_stats_bgraph10g
 reports os statistics from the AWR
repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S
gr_title - graphics title,
gr_abs_or_rel_os_stat - 
   (abs - absolute values will be 
          presented on the graphics,
    rel - values per sec. will be
          presented on the graphics,
    default : rel),
gr_y_or_y2_os_stat -
    (y - y-axis will be used,
     y2 - y2-axis will be used,
     default : y),
gr_yrange_os_stat - y-range in the following
                       format: <number>:<number>,
gr_stat_name_N - name of a system statistics,
                 default : no name   
Show SQL Show Result On/Off
awr_seg_stats_bgraph10g
 reports segment statistics from the 
AWR repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default: S,
gr_title - graphics title,
seg_stat_name - statistics to select
  (LOGICAL_READS, BUFFER_BUSY_WAITS,
   DB_BLOCK_CHANGES, PHYSICAL_READS,
   PHYSICAL_WRITES, ITL_WAITS,
   PHYSICAL_READS_DIRECT,
   PHYSICAL_WRITES_DIRECT,
   GC_CR_BLOCKS_RECEIVED,
   GC_CURRENT_BLOCKS_RECEIVED,
   GC_BUFFER_BUSY, ROW_LOCK_WAITS,
   GLOBAL_CACHE_CR_BLOCKS_SERVED,
   GLOBAL_CACHE_CU_BLOCKS_SERVED)
  default : PHYSICAL_READS,
object_type - object type 
  (TABLE, INDEX,
   TABLE PARTITION, INDEX PARTITION,
   LOB, etc.),
  default: all,
gr_abs_or_rel_seg_stat - 
   (abs - absolute values will be 
          presented on the graphics,
    rel - values per sec. will be
          presented on the graphics,
    default : rel),
gr_y_or_y2_seg_stat -
    (y - y-axis will be used,
     y2 - y2-axis will be used,
     default : y),
gr_yrange_seg_stat - y-range in the following
                   format: <number>:<number>,
gr_top_seg_stat - number of top segments,
                   default : 0,
gr_stat_name_N - name of a segment as
          <owner>.<obj_name>[.<subobj_name>],
                 default : no name 
Show SQL Show Result On/Off
awr_sga_stats_bgraph10g
 reports current sga statistics from 
the AWR repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S
gr_title - graphics title,
gr_y_or_y2_sga_stat -
    (y - y-axis will be used,
     y2 - y2-axis will be used,
     default : y),
gr_yrange_sga_stat - y-range in 
      the following format: <number>:<number>,
pool_name - pool name, default: no name,
gr_stat_name_N - name of a sga statistics in
                 the following form:
                 <pool>:<stat_name>, for ex.
                 shared pool:free memory,
                 default : no name   
Show SQL Show Result On/Off
awr_sum_nonidle_wait_bgraph10g
 reports total waits for non-idle events
from the AWR repository as a graphics
input parameters: 
begin_time - start time, 
              (format - dd.mm.yyyy hh24:mi:ss),
             default : sysdate - 1,
end_time   - end time, 
              (format - dd.mm.yyyy hh24:mi:ss),
             default : sysdate,
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_title - graphics title,
gr_abs_or_rel_sum_waits - 
   (abs - absolute wait times will be 
          presented on the graphics,
    rel - wait times per sec. will be
          presented on the graphics,
    default : rel),
gr_y_or_y2_sum_waits -
    (y - y-axis will be used for wait times,
     y2 - y2-axis will be used for wait times,
     default : y),
gr_yrange_sum_waits - 
            y-range in the following format:
            <number>:<number>
Show SQL Show Result On/Off
awr_sys_curr_stats_bgraph10g
 reports current (not cumulative) system 
statistics from the AWR repository 
as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S
gr_title - graphics title,
gr_y_or_y2_sys_curr_stat -
    (y - y-axis will be used,
     y2 - y2-axis will be used,
     default : y),
gr_yrange_sys_curr_stat - y-range in 
      he following format: <number>:<number>,
gr_stat_name_N - name of a system statistics,
                 default : no name   
Show SQL Show Result On/Off
awr_sys_stats_bgraph10g
 reports system statistics from the AWR
repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S
gr_title - graphics title,
gr_abs_or_rel_sys_stat - 
   (abs - absolute values will be 
          presented on the graphics,
    rel - values per sec. will be
          presented on the graphics,
    default : rel),
gr_y_or_y2_sys_stat -
    (y - y-axis will be used,
     y2 - y2-axis will be used,
     default : y),
gr_yrange_sys_stat - y-range in the following
                       format: <number>:<number>,
gr_stat_name_N - name of a system statistics,
                 default : no name   
Show SQL Show Result On/Off
awr_wait_events_bgraph10g
 reports wait statistics from the AWR
repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S
only_non_idle_events - 
   (Y - only non idle events in the output,
    N - all events,
    default : Y)
gr_title - graphics title,
gr_abs_or_rel_wait_event - 
   (abs - absolute wait times will be 
          presented on the graphics,
    rel - wait times per sec. will be
          presented on the graphics,
    default : rel),
gr_y_or_y2_wait_event -
    (y - y-axis will be used for wait times,
     y2 - y2-axis will be used for wait times,
     default : y),
gr_yrange_wait_event - y-range in the following
                       format: <number>:<number>,
gr_top_wait_event - number of top wait events,
                    default : 0,
gr_stat_name_N - name of a wait event,
                 default : no name   
Show SQL Show Result On/Off

Back to Directory of Topics

AWR Reports
Start Comment Description SQL Result Protocol
ash_report_html10g
 produces standard active session
history report,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss) 
Show SQL Show Result On/Off
awr_blocking_sess_event10g
 reports blocking sessions 
for an event from AWR,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),

event_name_like - default: all events 
Show SQL Show Result On/Off
awr_control10g
 Shows the control parameters for AWR
Show SQL Show Result On/Off
awr_obj_sqltus10g
 top SQL statements for one object from 
the AWR repository are reported,
input parameters: 
begin_time - start time (default : sysdate - 1),
end_time   - end time (default : sysdate),
(format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default: don't group data,
num_stmt - number of top statements, default 5,
with_sqltext - with SQL text in the output:
          ( Y - yes, N - no, default: Y),
trunc_sqltext_to - truncate SQL texts to N characters,
	           default - do not truncate,
with_exec_plan - with execution plans in the output:
	         (Y - yes, N - no, default: Y),
object_owner - object owner (default: all owners),
object_name - object name (default: all names),
object_type - object type (default: all types), 
criteria - the criteria for top SQL statements:
 STATS [ E ], where STATS in
 (DR - disk reads, BG - buffer gets,
  CT - CPU time, ET - elapsed time,
  PL - PL/SQL execution time, 
  JA - JAVA execution time,
  WT - wait time, EX - executions,
  RP - rows processed, PC - parse calls,
  DW - direct writes, AP - application wait time,
  CO - concurrency wait time, CL - cluster wait time,
  IO - user io wait time),
  E - per execution. Combination EXE isn't allowed.
  Default : DR
Show SQL Show Result On/Off
awr_param_hist10g
 reports initialization parameters from the
AWR repository,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
param_like - parameter name
              (default : all parameters),
not_default - not default ? (TRUE, FALSE)
              (default : TRUE)
not_autom_sized - not automatically sized
                  parameters ? (TRUE, FALSE),
                  (default : TRUE)
Show SQL Show Result On/Off
awr_report_html10g
 produces standard AWR report,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss) 
Show SQL Show Result On/Off
awr_show_all_os_stats10g
 shows names of os statistics,
input parameter : 
stat_name_like - statistics name,
                 (default : all) 
Show SQL Show Result On/Off
awr_snap_overwiew10g
 overviews the snapshots from the AWR
repository,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss)
Show SQL Show Result On/Off
awr_sqltus10g
 top SQL statements from the AWR
repository are reported,
input parameters: 
begin_time - start time (default : sysdate - 1),
end_time   - end time (default : sysdate),
(format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default: don't group data,
num_stmt - number of top statements, default 5,
with_sqltext - with SQL text in the output:
          ( Y - yes, N - no, default: Y),
trunc_sqltext_to - truncate SQL texts to N characters,
	           default - do not truncate,
with_exec_plan - with execution plans in the output:
	         (Y - yes, N - no, default: Y),
sql_id - sql id of the statement,
             default - all statemens,
signature - force matching signature of the
            statement, default - all statements,
criteria - the criteria for top SQL statements:
 STATS [ E ], where STATS in
 (DR - disk reads, BG - buffer gets,
  CT - CPU time, ET - elapsed time,
  PL - PL/SQL execution time, 
  JA - JAVA execution time,
  WT - wait time, EX - executions,
  RP - rows processed, PC - parse calls,
  DW - direct writes, AP - application wait time,
  CO - concurrency wait time, CL - cluster wait time,
  IO - user io wait time),
  E - per execution. Combination EXE isn't allowed.
  Default : DR
Show SQL Show Result On/Off
awr_startup_time10g
 reports instance startup times from the 
AWR repository,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss) 
Show SQL Show Result On/Off
awr_what_is_sess10g
 shows session details from AWR,
input parameters: 
   begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
   end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
   sid,
   serial (default: all serials)
 
Show SQL Show Result On/Off
one_exec_xplan_awr10g
 shows an execution plan for one SQL statement 
from AWR,
input parameter: SQL Id,
                 plan_hash_value
                 (default: all)
Show SQL Show Result On/Off

Back to Directory of Topics

Automatic SQL Tuning
Start Comment Description SQL Result Protocol
accept_sql_profile10g
 accepts a tuning task recommended
by Advisor (sql profile),
input parameters:
profile_name, 
   default: TUTOOL_SQL_PROFILE,
task_owner,
   default: current user,
task_name,
   default: TUTOOL_TUNING_TASK,
sqltune_category, 
   default: 'DEFAULT',
force_match - if TRUE this causes 
   SQL profiles to target all SQL 
   statements which have the same
   text after normalizing all literal
   values into bind variables,
   default: FALSE
Show SQL Show Result On/Off
alter_sql_profile10g
 alters a sql profile 
input parameters:
   profile_name,
      default: TUTOOL_SQL_PROFILE, 
   attr_name (STATUS, 
              NAME, CATEGORY),
   attr_value - STATUS can be set 
        to "ENABLED" or "DISABLED"
Show SQL Show Result On/Off
create_sqltext_from_sqlprof10
 creates a file with formatted sqltext from sql profile
input parameter: sql_profile_name
Show SQL Show Result On/Off
create_tuning_task_sql10g
 creates tuning task for a sql
defined via sqltext,
input parameters:
   user_name - the username for who 
        the statement will be tuned,
        default: current user,
   task_name, 
        default: TUTOOL_TUNING_TASK,
   time_limit (in sec.),
        default: 900
Show Desc Show SQL Show Result On/Off
create_tuning_task_sqlid10g
 creates tuning task for a sql
defined via sqlid,
input parameters:
   task_name, 
        default: TUTOOL_TUNING_TASK,
   sql_id,
   time_limit (in sec.),
        default: 900
Show SQL Show Result On/Off
create_tuning_task_sqlid_awr10g
 creates tuning task for a sql
from the AWR defined via sqlid,
input parameters:
begin_time - start time,
     default : sysdate - 1,
     (format - dd.mm.yyyy hh24:mi:ss),
end_time - end time,
     default : sysdate,
     (format - dd.mm.yyyy hh24:mi:ss),
task_name, 
     default: TUTOOL_TUNING_TASK,
sql_id,
time_limit (in sec.),
     default: 900
Show SQL Show Result On/Off
dba_sql_profiles10
 shows sql profile(s),
input parameters:sql_profile_name, default: all,
                 category, default: all,
                 description_like, default: all,
                 signature, default: all
Show SQL Show Result On/Off
dba_sqltune_tasks10
 shows sqltune task(s),
input parameters: owner, default: all,
                  task_name, default: all,
                  and/or task_id, default: all,
                  description_like, default: all
Show SQL Show Result On/Off
drop_sql_profile10g
 drops a sql profile 
input parameter: profile_name,
      default: TUTOOL_SQL_PROFILE
Show SQL Show Result On/Off
drop_sqltune_cat10
 dropes all sql profiles of one category,
input parameters: 
  sqltune_category - sqltune category
Show SQL Show Result On/Off
drop_tuning_task10g
 drops a tuning task 
input parameters:
   task_name, 
        default: TUTOOL_TUNING_TASK
Show SQL Show Result On/Off
execute_tuning_task10g
 executes a tuning task 
input parameters:
   task_name, 
        default: TUTOOL_TUNING_TASK
Show SQL Show Result On/Off
report_tuning_task10g
 reports a tuning task 
input parameters:
   task_owner,
        default: current user,
   task_name, 
        default: TUTOOL_TUNING_TASK,
   level - ALL, ADVANCED,
        default: ALL
Show SQL Show Result On/Off
set_sqltune_category_in_sess10g
 generates a command to enable
sqltune category in a session,
input parameter: sqltune category,
          default: DEFAULT
Show SQL Show Result On/Off
sql_prof_with_hints_from_awr10g
 creates sql profile to fix with optimizer
hints an execution plan from AWR,
input parameters:
      sql_id,
      plan_hash_value,
      sql_profile - name of sql profile
                    (default: TUTOOL_SQL_PROFILE),
      sqltune_category . default: DEFAULT,
      force_match - true, false 
                    (default: false)
Show SQL Show Result On/Off
sql_prof_with_hints_from_explain10g
 creates sql profile to fix with optimizer
hints an execution plan from explain plan,
input parameters:
      sql_profile - name of sql profile
                    (default: TUTOOL_SQL_PROFILE),
      sqltune_category . default: DEFAULT,
      force_match - true, false 
                    (default: false)
Show SQL Show Result On/Off
sql_prof_with_hints_from_sga10g
 creates sql profile to fix with optimizer
hints an execution plan from sqlarea,
input parameters:
      sql_id,
      plan_hash_value,
      sql_profile - name of sql profile
                    (default: TUTOOL_SQL_PROFILE),
      sqltune_category . default: DEFAULT,
      force_match - true, false 
                    (default: false)
Show SQL Show Result On/Off
sql_prof_with_hints_from_task10g
 creates sql profile to fix with optimizer
hints an execution plan from sql profile,
input parameters:
      sql_profile - name of sql profile
                (default: TUTOOL_SQL_PROFILE),
      sqltune_category . default: DEFAULT,
      task_owner - task owner
                (default: current user),
      task_name - name of task     
                (default: TUTOOL_TUNING_TASK),
      force_match - true, false 
                (default: false)
Show SQL Show Result On/Off
sqlid_to_signature10
 shows signature for a sql
from sqlarea (for selects 
from dba_sql_profiles, etc),
input parameters: sql_id
Show SQL Show Result On/Off
sqltext_to_signature10
 generates signature for a sql
defined via sqltext (for selects 
from dba_sql_profiles, etc),
input parameter: force_match (TRUE, FALSE),
              default: FALSE
Show Desc Show SQL Show Result On/Off
sqltext_to_signature_freeware10
 shows a signature for a sql
defined via sqltext (for selects 
from dba_sql_profiles, etc),

You do not need TP licence to run this script!
Show SQL Show Result On/Off

Back to Directory of Topics

Buffer Cache
Start Comment Description SQL Result Protocol
buff_cache_hitratio9i
 reports the buffer hitratio for each buffer pool
Show SQL Show Result On/Off
buffer_busy_waits9i
 reports the block classes and data files affected
by buffer busy waits.
Pior to release 8.1.6 run first the script 
$ORACLE_HOME/rdbms/admin/catperf.sql to
create the view V$BUFFER_POOL_STATISTICS
Show SQL Show Result On/Off
bw_stats_monitor
 monitors buffer busy wait statistics,
input parameter:
sleep_interval_sec - sleep interval in sec.
                     (default: 30),
requirement : TIMED_STATISTICS=TRUE 
Show SQL Show Result On/Off
file_bw_monitor8
 monitors buffer waits for datafiles,
input parameters:
sleep_interval_sec - sleep interval in sec.
                      (default: 30),
num_top_data_files - number of top datafiles
                      (default: all), 
requirement : TIMED_STATISTICS=TRUE 
Show SQL Show Result On/Off
obj_in_buff_cache9i
 reports object blocks in the buffer cache,
input parameters:
        object_owner (default: all) 
        object_name (default: all)
Show SQL Show Result On/Off
objs_hot_blocks9i
 reports the objects for top
hot blocks in the buffer cache,
input parameter: 
     num_top_blocks - number of top
                      hot blocks
                     (default : 20)
Show SQL Show Result On/Off
segs_hot_blocks8i
 reports the segments for top
hot blocks in the buffer cache,
input parameter: 
     num_top_blocks - number of top
                      hot blocks
                     (default : 5 )
Show SQL Show Result On/Off
sum_buff_cache_hitratio
 shows the summary buffer cache hitratio 
Show SQL Show Result On/Off

Back to Directory of Topics

Database Monitoring
Start Comment Description SQL Result Protocol
block_sess_monitor10g
 monitoring of blocking sessions
for an event,
input parameters: event_name_like,
      (default: all events),
                  sleep interval (sec.),
      (default: 60)
Show SQL Show Result On/Off
bw_stats_monitor
 monitors buffer busy wait statistics,
input parameter:
sleep_interval_sec - sleep interval in sec.
                     (default: 30),
requirement : TIMED_STATISTICS=TRUE 
Show SQL Show Result On/Off
event_monitor
 monitors wait events,
input parameters:
sleep_interval_sec - sleep interval in sec.
                     (default: 30),
num_top_events - number of top events
                     (default: all), 
requirement : TIMED_STATISTICS=TRUE 
Show SQL Show Result On/Off
file_bw_monitor8
 monitors buffer waits for datafiles,
input parameters:
sleep_interval_sec - sleep interval in sec.
                      (default: 30),
num_top_data_files - number of top datafiles
                      (default: all), 
requirement : TIMED_STATISTICS=TRUE 
Show SQL Show Result On/Off
file_pr_monitor8i
 monitors physical reads for datafiles,
input parameters:
sleep_interval_sec - sleep interval in sec.
                      (default: 30),
num_top_data_files - number of top datafiles
                      (default: all) 
Show SQL Show Result On/Off
file_pr_time_monitor8i
 monitors physical reads time for datafiles,
input parameters:
sleep_interval_sec - sleep interval in sec.
                      (default: 30),
num_top_data_files - number of top datafiles
                      (default: all), 
requirement : TIMED_STATISTICS=TRUE 
Show SQL Show Result On/Off
file_pwr_monitor8i
 monitors physical writes for datafiles,
input parameters:
sleep_interval_sec - sleep interval in sec.
                      (default: 30),
num_top_data_files - number of top datafiles
                      (default: all) 
Show SQL Show Result On/Off
file_pwr_time_monitor8i
 monitors physical writes time for datafiles,
input parameters:
sleep_interval_sec - sleep interval in sec.
                      (default: 30),
num_top_data_files - number of top datafiles
                      (default: all), 
requirement : TIMED_STATISTICS=TRUE 
Show SQL Show Result On/Off
latch_hold_waits_monitor
 monitors latch waits holding a latch,
input parameters:
sleep_interval_sec - sleep interval in sec.
                      (default: 30),
num_top_latches - number of top latches
                      (default: all) 
Show SQL Show Result On/Off
latch_immed_miss_monitor
 monitors immediate latch misses,
input parameters:
sleep_interval_sec - sleep interval in sec.
                      (default: 30),
num_top_latches - number of top latches
                      (default: all) 
Show SQL Show Result On/Off
latch_sleeps_monitor
 monitors latch sleeps,
input parameters:
sleep_interval_sec - sleep interval in sec.
                      (default: 30),
num_top_latches - number of top latches
                      (default: all) 
Show SQL Show Result On/Off
latch_wait_time_monitor9
 monitors latch waits time,
input parameters:
sleep_interval_sec - sleep interval in sec.
                      (default: 30),
num_top_latches - number of top latches
                      (default: all), 
requirement : TIMED_STATISTICS=TRUE
Show SQL Show Result On/Off
nonidle_event_monitor10
 monitors non-idle wait events,
input parameter : sleep interval (sec.),
                  (default : 60),
requirement : TIMED_STATISTICS=TRUE
Show SQL Show Result On/Off
os_stats_monitor10g
 monitors os statistics,
input parameter:
stat_name_like - statistics name 
                 (default: AVG%TIME),
sleep_interval_sec - sleep interval (sec.)
                 (default: 30)
Show SQL Show Result On/Off
sess_all_events_monitor
 monitors wait events for one session,
input parameters:
sid - SID of the session,
like_event_name - event name
                (default: all events),
sleep_interval_sec - sleep interval in sec.
                (default: 30),
num_top_events - number of top events. 
                (default: all),
requirement : TIMED_STATISTICS=TRUE 
Show SQL Show Result On/Off
sess_all_stats_monitor
 monitors system statistics for one session,
input parameters:
sid - SID of the session,
like_stat_name - statistics name 
                (default: all),
sleep_interval_sec - sleep interval in sec.
                (default: 30)
Show SQL Show Result On/Off
sess_event_monitor
 monitors one wait event for all sessions,
input parameters:
event_name - event name
                (default: "latch free"),
sleep_interval_sec - sleep interval in sec.
                (default: 30),
num_top_sessions - number of top sessions. 
                (default: 10),
requirement : TIMED_STATISTICS=TRUE 
Show SQL Show Result On/Off
sess_stats_monitor
 monitors one statistics for all sessions,
input parameters:
statistics_name - statistics name
            (default: "CPU used by this session"),
sleep_interval_sec - sleep interval in sec.
            (default: 30),
num_top_sessions - number of top sessions. 
            (default: 10), 
requirement : TIMED_STATISTICS=TRUE 
Show SQL Show Result On/Off
sga_stats_monitor8i
 monitors SGA statistics,
input parameter : sleep interval (sec.)
                  (default : 60)
Show SQL Show Result On/Off
show_all_stats
 shows names of system statistics,
input parameter : 
stat_name_like - statistics name,
                 (default : all) 
Show SQL Show Result On/Off
sys_stats_monitor
 monitors system statistics,
input parameter:
stat_name_like - statistics name 
                 (default: physical%),
sleep_interval_sec - sleep interval (sec.)
                 (default: 30),
requirement : TIMED_STATISTICS=TRUE
Show SQL Show Result On/Off
ts_free_space_monitor8i
 monitors free space in tablespaces,
input parameters : 
thresh_pct_free - threshold of percent free 
                   (default : all tablespaces),
sleep_interval_sec - sleep interval in sec.
                   (default : 30)
Show SQL Show Result On/Off

Back to Directory of Topics

Database Objects
Start Comment Description SQL Result Protocol
find_table_name_in_dict
 finds a complete table name in the data dictionary,
input parameter : 
table_name_like - table name
Show SQL Show Result On/Off
gen_create_function
 generates a create script for a function,
input parameters: owner (default: current user),
                  function name
Show SQL Show Result On/Off
gen_create_pack
 generates a create script for a package header,
input parameters: owner (default: current user),
                  package name
Show SQL Show Result On/Off
gen_create_pack_body
 generates a create script for a package body,
input parameters: owner (default: current user),
                  package name
Show SQL Show Result On/Off
gen_create_procedure
 generates a create script for a stored procedure,
input parameters: owner (default: current user),
                  procedure name
Show SQL Show Result On/Off
gen_create_view7
 generates a create script for a view,
input parameters: owner (default: current user),
                  view name
Show SQL Show Result On/Off
gen_ddl_9i
 generates create script for an object,
input parameters: owner (default: null),
                  object name,
                  object typ (default: TABLE)
Show SQL Show Result On/Off
invalid_obj
 reports invalid objects
Show Desc Show SQL Show Result On/Off
stored_obj_error
 shows errors for a stored object,
input parameters: owner (default: current user),
                  object name,
                  object type (default: all)
Show SQL Show Result On/Off
what_is_obj
 gives object information,
input parameter : object name and/or
                  object id and/or
                  data object id
Show SQL Show Result On/Off

Back to Directory of Topics

Database Overview
Start Comment Description SQL Result Protocol
dbconfig_html10g
 shows database configuration 
Show SQL Show Result On/Off
perf_overview10
 overviews instance performance 
Show SQL Show Result On/Off

Back to Directory of Topics

Database Parameters
Start Comment Description SQL Result Protocol
all_opt_relevant_params10g
 shows complete (also hidden) system 
optimizer environment,
input parameter: 
    IsDefault - Default?  (Y, N),
    (default : all parameters)
Show SQL Show Result On/Off
awr_param_hist10g
 reports initialization parameters from the
AWR repository,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
param_like - parameter name
              (default : all parameters),
not_default - not default ? (TRUE, FALSE)
              (default : TRUE)
not_autom_sized - not automatically sized
                  parameters ? (TRUE, FALSE),
                  (default : TRUE)
Show SQL Show Result On/Off
db_parameter8i
 shows initialization parameters,
input parameters : 
parameter_like - parameter name
            (default : all parameters),
IsDefault - Default?  (TRUE, FALSE),
            (default : all parameters)
Show SQL Show Result On/Off
diff_sid_opt_env10g
 shows differencies in 2 session 
optimizer environments,
input parameter: 
    sid1,
    sid2 
Show SQL Show Result On/Off
diff_sql_opt_env10g
 shows differencies in 2 sql 
optimizer environments,
input parameter: 
    sql_id,
    child_nr1
    child_nr2 
Show SQL Show Result On/Off
res_limit8
 shows resource limits
from the view sys.v_$resource_limit 
Show SQL Show Result On/Off
show_modified_sess_params10
 shows modified parameters in a session 
by turning on tracing, 
input parameter: SID

ATTENTION: a directory and an external table 
will be created and finally droped 
Show SQL Show Result On/Off
sid_opt_env10g
 shows session optimizer environment,
input parameter: 
    sid,
    IsDefault - Default?  (Y, N),
    (default : all parameters)
Show SQL Show Result On/Off
sp_param_hist8i
 reports initialization parameters from the
STATSPACK repository,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
param_like - parameter name
              (default : all parameters),
not_default - not default ? (TRUE, FALSE)
              (default : TRUE)
not_autom_sized - not automatically sized
                  parameters ? (TRUE, FALSE),
                  (default : TRUE)
Show SQL Show Result On/Off
stat_levels9i
 shows the impact of the parameter statistics_level
Show SQL Show Result On/Off
sys_opt_env10g
 shows system optimizer environment,
input parameter: 
    IsDefault - Default?  (Y, N),
    (default : all parameters)
Show SQL Show Result On/Off

Back to Directory of Topics

Enqueues
Start Comment Description SQL Result Protocol
act_sql_wait_enqueue
 shows a current SQL of the sessions
waiting for an enqueue,
input parameter: enqueue type,
                 (for ex. TX) 
Show SQL Show Result On/Off
blocking_sess_enq10g
 reports blocking sessions 
for enqueues
Show SQL Show Result On/Off
dba_waiters
 shows sessions waiting for an enqueue and
their blockers,
requirement: run first the script
catblock.sql to create the necessary
views in the schema SYS
Show SQL Show Result On/Off
enqueue_locks_blocked
 finds all locks causing the waits,
requirement: run first the script
catblock.sql to create the necessary
views in the schema SYS.
Show SQL Show Result On/Off
enqueue_stats10
 provides enqueue statistics 
Show SQL Show Result On/Off
locks8
 provides fully DECODED information regarding 
the locks currently held in the database,
input parameter: instance Id 
                 (default: all instances),
                 SID (default: all sessions)
Show SQL Show Result On/Off
missing_fk_idx
 checks for foreign key constraints 
which are not supported by any index 
Show SQL Show Result On/Off
obj_block
 finds objects which are blocked by enqueues,
requirement: run first the script
catblock.sql to create the necessary 
views in the schema SYS
Show SQL Show Result On/Off
sid_enqueue_wait
 reports sessions waiting for enqueues,
input parameter : sid
       (default : all sessions)
Show SQL Show Result On/Off
temp_ts_in_use8i
 checks the contents of the temporary 
tablespaces in use 
Show SQL Show Result On/Off
top_lock_holders8i
 reports top lock holders,
input parameter: number of top sessions
                 (default: 10),
requirement: run first the script catblock.sql
to create the necessary views in the schema SYS
Show SQL Show Result On/Off

Back to Directory of Topics

Fixed Tables
Start Comment Description SQL Result Protocol
create_X_Views
 generates a create script for the X_$ views,
input parameters: 
table_name_like - fixed table name 
           (default: for all fixed tables),
grant - user to grant select privileges on
        the X_$ views
           (default: public)
Show SQL Show Result On/Off
find_fixed_table_name
 finds a complete fixed table name, 
input parameter : 
table_name_like - table name
Show SQL Show Result On/Off
fixed_tab_idx
 gives indexes for fixed tables,
input parameter: 
table_name_like - fixed table name
                  (default : all)
Show SQL Show Result On/Off
fixed_view_text
 shows a view text of V$ views,
input parameter: 
view_name_like - view name
       (default: all views)
Show SQL Show Result On/Off

Back to Directory of Topics

I/O Tuning
Start Comment Description SQL Result Protocol
estimate_sparse_norm_idx10g
 estimates and reports normal indexes that should be rebuilt,
input parameters : 
        index owner, default: all,
        index name, default: all,
        only_without_stats, (Y;N), default: Y,
        sample_percent, 0 - 100, default 10,
        density in %, default: 75.
Attention: a temporary table will be created and 
           finally droped,
           this script can be very expensive!
Show SQL Show Result On/Off
file_pr_monitor8i
 monitors physical reads for datafiles,
input parameters:
sleep_interval_sec - sleep interval in sec.
                      (default: 30),
num_top_data_files - number of top datafiles
                      (default: all) 
Show SQL Show Result On/Off
file_pr_time_monitor8i
 monitors physical reads time for datafiles,
input parameters:
sleep_interval_sec - sleep interval in sec.
                      (default: 30),
num_top_data_files - number of top datafiles
                      (default: all), 
requirement : TIMED_STATISTICS=TRUE 
Show SQL Show Result On/Off
file_pwr_monitor8i
 monitors physical writes for datafiles,
input parameters:
sleep_interval_sec - sleep interval in sec.
                      (default: 30),
num_top_data_files - number of top datafiles
                      (default: all) 
Show SQL Show Result On/Off
file_pwr_time_monitor8i
 monitors physical writes time for datafiles,
input parameters:
sleep_interval_sec - sleep interval in sec.
                      (default: 30),
num_top_data_files - number of top datafiles
                      (default: all), 
requirement : TIMED_STATISTICS=TRUE 
Show SQL Show Result On/Off
sparse_bitmap_idx9i
 reports bitmap indexes that should be rebuilt,
input parameter : density in %
        default : 10,
requirement : accurate optimizer statistics
Show SQL Show Result On/Off
sparse_norm_idx9i
 reports normal indexes that should be rebuilt,
input parameter : density in %
        default : 75,
requirement : accurate optimizer statistics
Show Desc Show SQL Show Result On/Off

Back to Directory of Topics

Latches
Start Comment Description SQL Result Protocol
act_sql_sess_wait_latch
 shows a curent SQL of the sessions
waiting for a latch,
input parameter: latch name
Show SQL Show Result On/Off
all_sess_wait_latch
 shows the number of waiting sessions per latch
Show SQL Show Result On/Off
cache_buff_chain_latch_addr
 reports addresses of the "cache buffers chains" 
latches having the highest number of sleeps,
input parameter : threshold of sleep number
                    (default: 1000)
Show SQL Show Result On/Off
cache_buff_chain_latch_objs8
 reports objects from the x$bh for 
an address of "cache buffers chains" latch,
input parameter : latch address 
               (the output of the 
            cache_buff_chain_latch_addr)
Show SQL Show Result On/Off
cache_buff_chain_latch_segs8
 reports segments from the x$bh for 
an address of "cache buffers chains" latch,
input parameter : latch address 
               (the output of the 
            cache_buff_chain_latch_addr)
Show SQL Show Result On/Off
latch_blockers
 reports latch blockers 
Show SQL Show Result On/Off
latch_get_stats
 shows latch get statistics
Show SQL Show Result On/Off
latch_hold_waits_monitor
 monitors latch waits holding a latch,
input parameters:
sleep_interval_sec - sleep interval in sec.
                      (default: 30),
num_top_latches - number of top latches
                      (default: all) 
Show SQL Show Result On/Off
latch_immed_miss_monitor
 monitors immediate latch misses,
input parameters:
sleep_interval_sec - sleep interval in sec.
                      (default: 30),
num_top_latches - number of top latches
                      (default: all) 
Show SQL Show Result On/Off
latch_levels
 shows latch levels 
Show SQL Show Result On/Off
latch_sleeps
 reports latch sleeps 
Show SQL Show Result On/Off
latch_sleeps_monitor
 monitors latch sleeps,
input parameters:
sleep_interval_sec - sleep interval in sec.
                      (default: 30),
num_top_latches - number of top latches
                      (default: all) 
Show SQL Show Result On/Off
latch_wait_time9
 reports latch waits time, 
requirement : TIMED_STATISTICS=TRUE
Show SQL Show Result On/Off
latch_wait_time_monitor9
 monitors latch waits time,
input parameters:
sleep_interval_sec - sleep interval in sec.
                      (default: 30),
num_top_latches - number of top latches
                      (default: all), 
requirement : TIMED_STATISTICS=TRUE
Show SQL Show Result On/Off
latch_where_sleeps
 shows the distribution of latch sleeps
by code locations
Show SQL Show Result On/Off
sid_wait_latch
 reports sessions waiting for latches,
input parameter: sid
       (default: all sessions)
Show SQL Show Result On/Off

Back to Directory of Topics

Miscellaneous
Start Comment Description SQL Result Protocol
dba_feature_usage10
 reports dba features in use 
Show SQL Show Result On/Off
dba_to_segment9i
 finds out a segment via DBA,
input parameter : dba (data block address)
Show SQL Show Result On/Off
err_next_ext8i
 shows segments, which couldn't be extended
Show SQL Show Result On/Off
pq_qc_sql
 finds a current SQL of the QC (query coordinator)
through a current SQL of a running PQ (parallel query) 
slave.
input parameter: SQL address of a PQ slave
                 and / or
                 SQL hash value of a PQ slave
Show SQL Show Result On/Off
pq_sql_addr
 shows current SQL addresses of running PQ 
(parallel queries)
Show SQL Show Result On/Off
scn_to_timestamp10g
 converts SCN to timestamp,
input parameter : scn as decimal or as
		  <scn wrap>.<scn base>,
		  where scn wrap and base
		  are hexadecimal
Show Desc Show SQL Show Result On/Off
seg_types_in_ts
 shows segment types in tablespaces,
input parameter : tablespace name
       (default : all tablespaces)
Show SQL Show Result On/Off
segment_ext8
 shows the number of extents for segments,
input parameter : tablespace name
       (default : all tablespaces)
Show SQL Show Result On/Off
segs_free_blocks8
 reports the number of segment
free blocks in a free list,
input parameters :
     segment owner (default: current user),
     segment name,
     segment type (default: TABLE),
     partition name (default: none),
     free list group Id (default 0)
Show SQL Show Result On/Off
segs_hwm8
 reports a HWM (highwater mark) for a segment,
input parameters :
     segment owner (default: current user),
     segment name,
     segment type (default: TABLE),
     partition name (default: none)
Show Desc Show SQL Show Result On/Off
segs_in_file8
 shows segments having extents in the datafile,
input parameters: 
             file_id - file Id and/or 
             file_name_like - file name
Show SQL Show Result On/Off
space_usage9i
 reports a space usage for segments in ASSM 
("automatic segment space management") tablespaces,
input parameters :
    segment owner (default: current user),
    segment name,
    segment type (default : TABLE)
Show SQL Show Result On/Off
too_many_ext8i
 reports top segments in dictionary managed 
tablespaces having many extents,
input parameter : threshold of the 
                  number of the extents
                  (default : 1000)
Show SQL Show Result On/Off
ts_free_space8i
 shows free space in tablespaces 
Show SQL Show Result On/Off
user_obj_privs9
 reports granted object privileges for a given user
or for a given role,
input parameter : name - user name or role
Show SQL Show Result On/Off
user_roles9
 reports granted roles for a given user
or for a given role,
input parameter : name - user name or role
Show SQL Show Result On/Off
user_system_privs9
 reports granted system privileges for a given user
or for a given role,
input parameter : name - user name or role
Show SQL Show Result On/Off
write_into_alertlog
 writes a string into alert.log,
input parameter: text
Show SQL Show Result On/Off

Back to Directory of Topics

RMAN
Start Comment Description SQL Result Protocol
rman_backup_hist_html9i
 shows RMAN backup history 
for the last 15 days,
input parameter: backup type
(F - full backup,
 A - archived redologs,
 I - incremental backup,
 default: full backup)
Show SQL Show Result On/Off

Back to Directory of Topics

Redo Logs
Start Comment Description SQL Result Protocol
lgwr_stats8i
 shows redo log statistics
Show SQL Show Result On/Off
log_file_usage8
 to see how much of the current log 
file has been used,
attention: checkpoint will be done !
Show SQL Show Result On/Off
redolog_switch_history_html8i
 shows redo log switch history
for the last 30 days
Show SQL Show Result On/Off

Back to Directory of Topics

Reports from the Dump Destinations
Start Comment Description SQL Result Protocol
alertlog_errors9i
 reports the errors in the alert.log, 
input parameters:
begin_date - begin date, default: sysdate - 1,
             (format: dd.mm.yyyy),
end_date   - end date, default: sysdate,
             (format: dd.mm.yyyy),
ATTENTION: a directory and an external table 
will be created and finally droped
Show SQL Show Result On/Off
alertlog_n_lines9i
 reports the last N lines of the alert.log, 
input parameters:
n_last_lines - n last lines of the alert.log
               or
               FULL - the entire alert.log,
               default: 1000,
ATTENTION: a directory and an external table 
will be created and finally droped
Show SQL Show Result On/Off
alertlog_time_int9i
 reports the time section of the alert.log, 
input parameters:
begin_date - begin date, default: sysdate - 1,
             (format: dd.mm.yyyy),
end_date   - end date, default: sysdate,
             (format: dd.mm.yyyy),
ATTENTION: a directory and an external table 
will be created and finally droped
Show SQL Show Result On/Off
any_trace9i
 reports the first N lines of an arbitrary trace
file from an arbitrary destination,
input parameters:
destination - directory with a trace file,
trace_name - the name of trace file,
n_first_lines - n first lines of the trace
                or
                FULL - the entire trace,
                default: 1000,
ATTENTION: a directory and an external table 
will be created and finally droped
Show SQL Show Result On/Off
background_trace9i
 reports the first N lines of the background
trace file from the background dump destination,
input parameters:
trace_name - the name of background trace file,
n_first_lines - n first lines of the trace
                or
                FULL - the entire trace,
                default: 1000,
ATTENTION: a directory and an external table 
will be created and finally droped
Show SQL Show Result On/Off
cleanout_tool_ext_tables9i
 drops all tools external tables
and directories
Show SQL Show Result On/Off
show_modified_sess_params10
 shows modified parameters in a session 
by turning on tracing, 
input parameter: SID

ATTENTION: a directory and an external table 
will be created and finally droped 
Show SQL Show Result On/Off
sid_sql_trace92
 turns on sql tracing for some minutes in 
a session, transfers the trace file to the 
client site, starts tkprof utility, 
input parameters: 
SID,
trace level: 
  1 - without bind variables and wait stats,
  4 - with bind variables and without wait stats,
  8 - without bind variables and with wait stats, 
 12 - with bind variables and with wait stats,
 default: 1,
duration_min - duration of tracing in min.,
  default: 5
tkprof_explain - 
  y - with connection to the database,
  n - without connection to the database,
  default: n,
tkprof_sort - sort criteria for tkprof,
  default: prsela exeela fchela (elapsed time).
ATTENTION: a directory and an external table 
will be created and finally droped           
 
Show SQL Show Result On/Off
sid_sql_trace_com92
 generates a script to turn on/off the sql
tracing for a session,
input parameters: SID,
                  trace level: 
1 - without bind variables and wait stats,
4 - with bind variables and without wait stats,
8 - without bind variables and with wait stats, 
12 - with bind variables and with wait stats,
default: 1
Show SQL Show Result On/Off
tkprof9i
 transfers trace file to the client site,
starts tkprof utility,
input parameters: 
trace_name - name of the trace file,
tkprof_explain - 
  y - with connection to the database,
  n - without connection to the database,
  default: n,
tkprof_sort - sort criteria for tkprof,
  default: prsela exeela fchela (elapsed time).
ATTENTION: a directory and an external table 
will be created and finally droped 
 
Show SQL Show Result On/Off
user_trace9i
 reports the first N lines of the user trace
file from the user dump destination,
input parameters:
trace_name - the name of user trace file,
n_first_lines - n first lines of the trace
                or
                FULL - the entire trace,
                default: 1000,
ATTENTION: a directory and an external table 
will be created and finally droped
Show SQL Show Result On/Off

Back to Directory of Topics

Rollback Segments
Start Comment Description SQL Result Protocol
enough_rollback_segs8
 to check the number of rollback segments
Show SQL Show Result On/Off
rbs_contention_html
 reports rollback segments contention 
Show Desc Show SQL Show Result On/Off
rollback_status_html
 reports rollback segments status 
Show Desc Show SQL Show Result On/Off
sess_rbs
 finds a rollback segment for a session,
input parameter: sid, 
                (default: all sessions)
Show SQL Show Result On/Off
undo_stats10g
 reports undo statistics,
input parameters : 
      start_time in the format
          'dd.mm.yyyy hh24:mi:ss',
          (default : sysdate - 1),
      end_time in the format
          'dd.mm.yyyy hh24:mi:ss',
          (default : sysdate),
      group - grouping:
          D - day, H - hour, M - minute
          (default : M)
Show SQL Show Result On/Off

Back to Directory of Topics

SQL Tuning
Start Comment Description SQL Result Protocol
awr_obj_sqltus10g
 top SQL statements for one object from 
the AWR repository are reported,
input parameters: 
begin_time - start time (default : sysdate - 1),
end_time   - end time (default : sysdate),
(format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default: don't group data,
num_stmt - number of top statements, default 5,
with_sqltext - with SQL text in the output:
          ( Y - yes, N - no, default: Y),
trunc_sqltext_to - truncate SQL texts to N characters,
	           default - do not truncate,
with_exec_plan - with execution plans in the output:
	         (Y - yes, N - no, default: Y),
object_owner - object owner (default: all owners),
object_name - object name (default: all names),
object_type - object type (default: all types), 
criteria - the criteria for top SQL statements:
 STATS [ E ], where STATS in
 (DR - disk reads, BG - buffer gets,
  CT - CPU time, ET - elapsed time,
  PL - PL/SQL execution time, 
  JA - JAVA execution time,
  WT - wait time, EX - executions,
  RP - rows processed, PC - parse calls,
  DW - direct writes, AP - application wait time,
  CO - concurrency wait time, CL - cluster wait time,
  IO - user io wait time),
  E - per execution. Combination EXE isn't allowed.
  Default : DR
Show SQL Show Result On/Off
awr_sqltus10g
 top SQL statements from the AWR
repository are reported,
input parameters: 
begin_time - start time (default : sysdate - 1),
end_time   - end time (default : sysdate),
(format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default: don't group data,
num_stmt - number of top statements, default 5,
with_sqltext - with SQL text in the output:
          ( Y - yes, N - no, default: Y),
trunc_sqltext_to - truncate SQL texts to N characters,
	           default - do not truncate,
with_exec_plan - with execution plans in the output:
	         (Y - yes, N - no, default: Y),
sql_id - sql id of the statement,
             default - all statemens,
signature - force matching signature of the
            statement, default - all statements,
criteria - the criteria for top SQL statements:
 STATS [ E ], where STATS in
 (DR - disk reads, BG - buffer gets,
  CT - CPU time, ET - elapsed time,
  PL - PL/SQL execution time, 
  JA - JAVA execution time,
  WT - wait time, EX - executions,
  RP - rows processed, PC - parse calls,
  DW - direct writes, AP - application wait time,
  CO - concurrency wait time, CL - cluster wait time,
  IO - user io wait time),
  E - per execution. Combination EXE isn't allowed.
  Default : DR
Show SQL Show Result On/Off
create_sqltext_from_awr10
 creates a file with formatted sqltext from AWR,
input parameter: sql_id
Show SQL Show Result On/Off
create_sqltext_from_outln8i
 creates a file with formatted sqltext from outline
input parameter: outln_name
Show SQL Show Result On/Off
create_sqltext_from_sga10
 creates a file with formatted sqltext from sqlarea
input parameter: sql_id
Show SQL Show Result On/Off
create_sqltext_from_sp10
 creates a file with formatted sqltext from 
statspack repository,
input parameters: old_hash_value
Show SQL Show Result On/Off
create_sqltext_from_sqlprof10
 creates a file with formatted sqltext from sql profile
input parameter: sql_profile_name
Show SQL Show Result On/Off
diff_sql_opt_env10g
 shows differencies in 2 sql 
optimizer environments,
input parameter: 
    sql_id,
    child_nr1
    child_nr2 
Show SQL Show Result On/Off
exec_sql10g
 executes one sql defined via sqltext,
shows execution plan with runtime statistics
(you can set bind variables in prescript)
Show Desc Show SQL Show Result On/Off
explain_plan10g
 selects an explain plan from SYS.PLAN_TABLE,
requirement: table PLAN_TABLE 
               in the schema SYS
Show Desc Show SQL Show Result On/Off
index_stats10g
 shows index statistics,
input parameter: table_owner (default: all),
                 table_name (default: all),
                 index_owner (defailt: all),
		 index_name (default: all)
Show SQL Show Result On/Off
one_exec_plan_sqlarea10g
 shows an execution plan for one SQL statement 
from the sqlarea,
input parameters: 
   SQL hash value and/or
   SQL Id and/or
   signature (exact or force matching_signature),
   SQL child number (default: all children),
   without_pred_info - without predicate information ?
                       (Y,N), default Y,
   truncate_other_to - truncate "OTHER" from
                       v$sql_plan to N 
                       characters,
                       (default : don't truncate)
Show Desc Show SQL Show Result On/Off
one_exec_xplan_awr10g
 shows an execution plan for one SQL statement 
from AWR,
input parameter: SQL Id,
                 plan_hash_value
                 (default: all)
Show SQL Show Result On/Off
one_exec_xplan_sqlarea10g
 shows an execution plan for one SQL statement 
from the sqlarea,
input parameter: SQL Id,
                 Child Number,
                 (default : 0)
Show SQL Show Result On/Off
pq_qc_sql
 finds a current SQL of the QC (query coordinator)
through a current SQL of a running PQ (parallel query) 
slave.
input parameter: SQL address of a PQ slave
                 and / or
                 SQL hash value of a PQ slave
Show SQL Show Result On/Off
pq_sql_addr
 shows current SQL addresses of running PQ 
(parallel queries)
Show SQL Show Result On/Off
restore_opt_tab_stats10g
 restores optimizer statistics for one table,
input parameters:
owner - table owner,
table_name - table name,
restore_time - restore time 
     (stats_update_time - 
      from stored stats for the table,
      (not for partition !))
  in 'DD-MON-YY HH.MI.SS.FF6 AM TZH:TZM' format. 
     Do not set this parameter, if you want only
     to see the history of stats modifications,
display_ind_stats - display index statistics
    (default: n),
force - restore statistis even if the table 
        statistics are locked (Y, N),
        default: N.

If optimizer stats were generated separately for
the table and for the indexes, then there is 
no common solution for clear restoring!  

Be careful with this script on the productive system ! 
Show SQL Show Result On/Off
sess_with_open_cur10g
 reports sessions having an open cursor
for a SQL statement,
input parameters: hash value and/or
                  sql_id
                  of the SQL statement
Show SQL Show Result On/Off
sid_sql_trace92
 turns on sql tracing for some minutes in 
a session, transfers the trace file to the 
client site, starts tkprof utility, 
input parameters: 
SID,
trace level: 
  1 - without bind variables and wait stats,
  4 - with bind variables and without wait stats,
  8 - without bind variables and with wait stats, 
 12 - with bind variables and with wait stats,
 default: 1,
duration_min - duration of tracing in min.,
  default: 5
tkprof_explain - 
  y - with connection to the database,
  n - without connection to the database,
  default: n,
tkprof_sort - sort criteria for tkprof,
  default: prsela exeela fchela (elapsed time).
ATTENTION: a directory and an external table 
will be created and finally droped           
 
Show SQL Show Result On/Off
sid_sql_trace_com92
 generates a script to turn on/off the sql
tracing for a session,
input parameters: SID,
                  trace level: 
1 - without bind variables and wait stats,
4 - with bind variables and without wait stats,
8 - without bind variables and with wait stats, 
12 - with bind variables and with wait stats,
default: 1
Show SQL Show Result On/Off
sp_obj_sqltus10g
 top SQL statements from the STATSPACK
repository are reported,
input parameters: 
begin_time - start time (default : sysdate - 1),
end_time   - end time (default : sysdate),
(format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default: don't group data,
num_stmt - number of top statements, default 5,
with_sqltext - with SQL text in the output:
          ( Y - yes, N - no, default: Y),
trunc_sqltext_to - truncate SQL texts to N characters,
	           default - do not truncate,
with_exec_plan - with execution plans in the output:
	         (Y - yes, N - no, default: Y),
object_owner - object owner (default: all owners),
object_name - object name (default: all names),
object_type - object type (default: all types), 
command_type - one of the following SQL types:
CREATE TABLE,CREATE INDEX,ALTER INDEX,
SELECT,DELETE,INSERT,UPDATE,MERGE,
	 default : all commands types,
criteria - the criteria for top SQL statements:
 STATS [ E ], where STATS in
 (DR - disk reads, BG - buffer gets,
  CT - CPU time, ET - elapsed time,
  PL - PL/SQL execution time, 
  JA - JAVA execution time,
  WT - wait time, EX - executions,
  RP - rows processed, PC - parse calls,
  DW - direct writes, AP - application wait time,
  CO - concurrency wait time, CL - cluster wait time,
  IO - user io wait time),
  E - per execution. Combination EXE isn't allowed.
  Default : DR
Show SQL Show Result On/Off
sp_sqltus10g
 top SQL statements from the STATSPACK
repository are reported,
input parameters: 
begin_time - start time (default : sysdate - 1),
end_time   - end time (default : sysdate),
(format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default: don't group data,
num_stmt - number of top statements, default 5,
with_sqltext - with SQL text in the output:
          ( Y - yes, N - no, default: Y),
trunc_sqltext_to - truncate SQL texts to N characters,
	           default - do not truncate,
with_exec_plan - with execution plans in the output:
	         (Y - yes, N - no, default: Y),
program_id - object id,
             default - SQL's from all programs,
(hash_value - hash value of the statement
and/or
old_hash_value - old hash value of the statement,
             default - all statemens),
signature - exact or force matching signature of SQL,
            default - all statements,
command_type - one of the following SQL types:
CREATE TABLE,CREATE INDEX,ALTER INDEX,
SELECT,DELETE,INSERT,UPDATE,MERGE,
	 default : all commands types,
criteria - the criteria for top SQL statements:
 STATS [ E ], where STATS in
 (DR - disk reads, BG - buffer gets,
  CT - CPU time, ET - elapsed time,
  PL - PL/SQL execution time, 
  JA - JAVA execution time,
  WT - wait time, EX - executions,
  RP - rows processed, PC - parse calls,
  DW - direct writes, AP - application wait time,
  CO - concurrency wait time, CL - cluster wait time,
  IO - user io wait time),
  E - per execution. Combination EXE isn't allowed.
  Default : DR
Show Desc Show SQL Show Result On/Off
sql_bind_value10g
 shows bind values for one SQL statement 
from the sqlarea,
input parameters: SQL Id,
                 Child Number,
                 (default : 0)
Show SQL Show Result On/Off
sql_id_via_signature10
 searches sql_id in v$sql,
input parameters: 
       exact_signature, default: all,
       force_signature, default: all
Show SQL Show Result On/Off
sqlid_to_signature10
 shows signature for a sql
from sqlarea (for selects 
from dba_sql_profiles, etc),
input parameters: sql_id
Show SQL Show Result On/Off
sqltext_to_signature10
 generates signature for a sql
defined via sqltext (for selects 
from dba_sql_profiles, etc),
input parameter: force_match (TRUE, FALSE),
              default: FALSE
Show Desc Show SQL Show Result On/Off
sqltext_to_signature_freeware10
 shows a signature for a sql
defined via sqltext (for selects 
from dba_sql_profiles, etc),

You do not need TP licence to run this script!
Show SQL Show Result On/Off
sqltus10g
 top SQL's from the sqlarea with
execution plans are reported,
input parameters:
what - the criteria for top SQL's:
[S|U|P] DR|BG|DW|CT|ET|WT|PL|JA|AP|CO|CL|IO [E]
      DR - disk reads,
      BG - block gets,
      DW - direct writes,
      CT - CPU time,
      ET - elapsed time,
      PL - PL/SQL execution time,
      JA - JAVA execution time,
      WT - wait time,
      AP - application wait time,
      CO - concurrency wait time,
      CL - cluster wait time,
      IO - user i/o wait time,
       E - per execution, 
       S - SQL's of one Session,
       U - SQL's of one user,
       P - SQL's of one Program,
num_stats - the number of the top SQL's,
par3 - [ <user name> | SID | Program (Object) Id ]
(defaults : what = DR, num_stats = 10, par3 = 1)
Show Desc Show SQL Show Result On/Off
tab_idx8i
 reports indexes for a table,
input parameters: table owner 
                  (default: all),
                  table name.
Show SQL Show Result On/Off
table_stats10g
 shows table statistics,
input parameter: owner (defailt: all),
		 table_name (default: all)
Show SQL Show Result On/Off
tkprof9i
 transfers trace file to the client site,
starts tkprof utility,
input parameters: 
trace_name - name of the trace file,
tkprof_explain - 
  y - with connection to the database,
  n - without connection to the database,
  default: n,
tkprof_sort - sort criteria for tkprof,
  default: prsela exeela fchela (elapsed time).
ATTENTION: a directory and an external table 
will be created and finally droped 
 
Show SQL Show Result On/Off
user_ind_last_analyzed8
 shows min. and max. of last analyzed dates
for user indexes,
input parameter: user name
                 (default: all users)
Show SQL Show Result On/Off
user_tab_last_analyzed8
 shows min. and max. of last analyzed dates
for user tables,
input parameter: user name
                 (default: all users)
Show SQL Show Result On/Off
xplan10
 selects an explain plan from
SYS.PLAN_TABLE via. DBMS_XPLAN.DISPLAY,
input parameter: format - ALL, ADVANCED,
                      default: ALL,
requirement: table PLAN_TABLE in the
             schema SYS
Show Desc Show SQL Show Result On/Off

Back to Directory of Topics

Sessions
Start Comment Description SQL Result Protocol
act_sid_sql
 shows a current SQL for a session,
input parameter: sid
Show SQL Show Result On/Off
awr_what_is_sess10g
 shows session details from AWR,
input parameters: 
   begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
   end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
   sid,
   serial (default: all serials)
 
Show SQL Show Result On/Off
orapid_sid
 finds SID through ORAPID,
input parameter: orapid 
Show SQL Show Result On/Off
pid_sid
 finds SID through OS PID.
input parameter: pid 
Show SQL Show Result On/Off
prev_sid_sql
 shows previous SQL for a session,
input parameter : sid
Show SQL Show Result On/Off
sess_all_stats
 shows system statistics for a session,
input parameters : 
   sid,
   stat_name_like - statistics name 
                    (default : all)
Show SQL Show Result On/Off
sess_longops8
 shows sessions long operations,
input parameter : sid
       (default : all)
Show SQL Show Result On/Off
sess_mem_stats
 shows system memory statistics
for running sessions,
input parameter: sid
      (default: all sessions)
Show SQL Show Result On/Off
sess_open_cur10g
 reports open cursors for a session,
input parameter: sid 
Show SQL Show Result On/Off
sess_pga_mem9i
 shows pga memory 
for running processes
Show SQL Show Result On/Off
sess_pga_stats9
 shows PGA statistics for sessions, 
input parameter : sid
        (default: all sessions)
Show SQL Show Result On/Off
sess_rbs
 finds a rollback segment for a session,
input parameter: sid, 
                (default: all sessions)
Show SQL Show Result On/Off
sess_sort_usage8
 reports sort usage of sessions,
input parameter : sid,
       (default : all sessions)
Show SQL Show Result On/Off
sess_time_model10g
 shows time model statistics for one session,
input parameter: sid,
requirement: TIMED_STATISTICS=TRUE 
Show SQL Show Result On/Off
sess_with_open_cur10g
 reports sessions having an open cursor
for a SQL statement,
input parameters: hash value and/or
                  sql_id
                  of the SQL statement
Show SQL Show Result On/Off
sess_workarea_activity9
 shows work area activity for 
sessions,
input parameter: sid
    (default : all Sessions) 
Show SQL Show Result On/Off
sid_opt_env10g
 shows session optimizer environment,
input parameter: 
    sid,
    IsDefault - Default?  (Y, N),
    (default : all parameters)
Show SQL Show Result On/Off
sid_orapid
 finds ORAPID through SID,
input parameter: sid 
Show SQL Show Result On/Off
sid_pid
 finds OS PID through SID,
input parameter: sid
Show SQL Show Result On/Off
what_is_sess10g
 shows session details,
input parameters:
  sid,
  serial (default: all serials)
Show SQL Show Result On/Off

Back to Directory of Topics

Shared Pool
Start Comment Description SQL Result Protocol
dictionary_cache
 shows dictionary cache usage
Show SQL Show Result On/Off
library_cache
 shows library cache usage
Show SQL Show Result On/Off
lp9
 shows a breakdown for the large pool
Show SQL Show Result On/Off
obj_to_pin
 creates a script to pin the objects in SGA
Show SQL Show Result On/Off
objects_flush_others9
 shows allocations causing shared pool memory 
to be aged out
Show SQL Show Result On/Off
rsp
 shows space in reserved shared pool and ORA-4031
Show SQL Show Result On/Off
sga_stats8
 shows SGA statistics 
Show SQL Show Result On/Off
sga_stats_monitor8i
 monitors SGA statistics,
input parameter : sleep interval (sec.)
                  (default : 60)
Show SQL Show Result On/Off
sp9
 shows a breakdown for the shared pool,
attention: this script may cause performance
           problems because it needs to hold
           shared pool latch
Show SQL Show Result On/Off
sp_free_space9
 shows free space in the shared pool,
attention: this script may cause performance
           problems because it needs to hold
           shared pool latch
Show SQL Show Result On/Off
sql_exe1
 reports SQL's from the sqlarea
which have been executed only ones
Show SQL Show Result On/Off

Back to Directory of Topics

Statspack Advanced Graphical Reports
Start Comment Description SQL Result Protocol
sp_5_top_enq_graph10g
 reports 5 top enqueues from the 
STATSPACK repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_enq_stat - y-range in the following
                       format: <number>:<number>
Show SQL Show Result On/Off
sp_5_top_events_graph9i
 reports 5 top non-idle wait events from the 
STATSPACK repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_wait_event - y-range in the following
                       format: <number>:<number>
Show SQL Show Result On/Off
sp_5_top_files_graph8i
 reports 5 top data files for the average
duration of one physical read from the 
STATSPACK repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_file_stat - y-range in the following
                       format: <number>:<number>
Show SQL Show Result On/Off
sp_5_top_latches_graph9i
 reports 5 top latches from the 
STATSPACK repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_latch_stat - y-range in the following
                       format: <number>:<number>
Show SQL Show Result On/Off
sp_5_top_segs_graph9i
 reports 5 top segments for physical reads 
from the STATSPACK repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_seg_stat - y-range in the following
                       format: <number>:<number>
Show SQL Show Result On/Off
sp_5_top_sp_stats_graph817
 reports 5 top shared pool statistics 
from the STATSPACK repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_sga_stat - y-range for the sga
                   statistics in the following
                   format: <number>:<number>
Show SQL Show Result On/Off
sp_all_cpu_stats_graph8i
 reports all system cpu statistics and the 
number of processes  from the STATSPACK 
repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_sys_stat - y-range in the following
                     format: <number>:<number>,
gr_yrange_sys_curr_stat - y-range for the number
                   of processes in the following
                   format: <number>:<number>
Show SQL Show Result On/Off
sp_cpu_osstats_graph102
 reports cpu os statistics from the STATSPACK 
repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_os_stat - y-range in the following
                     format: <number>:<number>,
gr_yrange_os_curr_stat - y-range in the following
                     format: <number>:<number>
Show SQL Show Result On/Off
sp_io_events_stats_graph9i
 reports io wait events and statistics  
from the STATSPACK repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_wait_event - y-range in the following
                       format: <number>:<number>,
gr_yrange_sys_stat - y-range in the following
                       format: <number>:<number>
Show SQL Show Result On/Off
sp_link_events_stats_graph9i
 reports dblink wait events and statistics  
from the STATSPACK repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_wait_event - y-range in the following
                       format: <number>:<number>,
gr_yrange_sys_stat - y-range in the following
                       format: <number>:<number>
Show SQL Show Result On/Off
sp_logons_graph8i
 reports logons statistics from the STATSPACK 
repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_sys_stat - y-range for the cumulative
                     logons in the following
                     format: <number>:<number>,
gr_yrange_sys_curr_stat - y-range for the current
                   logons in the following
                   format: <number>:<number>
Show SQL Show Result On/Off
sp_page_osstats_graph102
 reports paging os statistics from the STATSPACK 
repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_os_stat - y-range in the following
                     format: <number>:<number>
Show SQL Show Result On/Off
sp_pq_downgr_stats_graph8i
 reports downgrade statistics for parallel 
operations from the STATSPACK repository as a 
graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_sys_stat - y-range in the following
                     format: <number>:<number>
Show SQL Show Result On/Off
sp_pq_stats_graph8i
 reports system statistics for parallel 
operations from the STATSPACK repository as a 
graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_sys_stat - y-range in the following
                     format: <number>:<number>
Show SQL Show Result On/Off
sp_wait_cpu_stat_graph9i
 reports total waits for non-idle 
events and cpu statistics from the STATSPACK
repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_yrange_sum_waits - 
            y-range in the following format: 
            <number>:<number>,
gr_yrange_sys_stat - 
            y-range in the following format:
            <number>:<number>
Show SQL Show Result On/Off

Back to Directory of Topics

Statspack Basic Graphical Reports
Start Comment Description SQL Result Protocol
sp_enq_stats_bgraph10g
 reports enqueue statistics from the 
STATSPACK repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default: S,
gr_title - graphics title,
enq_stat_name - statistics to select
            (TOTAL_REQ#, TOTAL_WAIT#,
             SUCC_REQ#, FAILED_REQ#,
             CUM_WAIT_TIME),
            default : CUM_WAIT_TIME,
gr_abs_or_rel_enq_stat - 
   (abs - absolute values will be 
          presented on the graphics,
    rel - values per sec. will be
          presented on the graphics,
    default : rel),
gr_y_or_y2_enq_stat -
    (y - y-axis will be used,
     y2 - y2-axis will be used,
     default : y),
gr_yrange_enq_stat - y-range in the following
                       format: <number>:<number>,
gr_top_enq_stat - number of top enqueues,
                   default : 0,
gr_stat_name_N - name of an enqueue in the
                 following format:
                 <type> - <req. reason>, for ex.
                  TX - contention,
                 default : no name 
Show SQL Show Result On/Off
sp_file_stats_bgraph8i
 reports data file statistics from the 
STATSPACK repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default: S,
gr_title - graphics title,
file_stat_name - statistics to select
            (READTIM,WRITETIM,PHYRDS, 
             PHYWRTS, PHYBLKRD, 
             PHYBLKWRT, READTIM/PHYRDS),
            default : READTIM/PHYRDS,
gr_abs_or_rel_file_stat - 
   (abs - absolute values will be 
          presented on the graphics,
    rel - values per sec. will be
          presented on the graphics,
    default : rel),
gr_y_or_y2_file_stat -
    (y - y-axis will be used,
     y2 - y2-axis will be used,
     default : y),
gr_yrange_file_stat - y-range in the following
                       format: <number>:<number>,
gr_top_file_stat - number of top data files,
                   default : 0,
gr_stat_name_N - name of a data file,
                 default : no name 
Show SQL Show Result On/Off
sp_latch_stats_bgraph8i
 reports latch statistics from the 
STATSPACK repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default: S,
gr_title - graphics title,
latch_stat_name - statistics to select
            (GETS,MISSES,SLEEPS, 
             IMMEDIATE_GETS, 
             IMMEDIATE_MISSES, 
             SPIN_GETS,WAIT_TIME),
            default : SLEEPS,
gr_abs_or_rel_latch_stat - 
   (abs - absolute values will be 
          presented on the graphics,
    rel - values per sec. will be
          presented on the graphics,
    default : rel),
gr_y_or_y2_latch_stat -
    (y - y-axis will be used,
     y2 - y2-axis will be used,
     default : y),
gr_yrange_latch_stat - y-range in the following
                       format: <number>:<number>,
gr_top_latch_stat - number of top latches,
                   default : 0,
gr_stat_name_N - name of a latch,
                 default : no name 
Show SQL Show Result On/Off
sp_os_curr_stats_bgraph102
 reports current (not cumulative) os 
statistics from the STATSPACK repository 
as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S
gr_title - graphics title,
gr_y_or_y2_os_curr_stat -
    (y - y-axis will be used,
     y2 - y2-axis will be used,
     default : y),
gr_yrange_os_curr_stat - y-range in 
      he following format: <number>:<number>,
gr_stat_name_N - name of a system statistics,
                 default : no name   
Show SQL Show Result On/Off
sp_os_stats_bgraph102
 reports os statistics from the STATSPACK
repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S
gr_title - graphics title,
gr_abs_or_rel_os_stat - 
   (abs - absolute values will be 
          presented on the graphics,
    rel - values per sec. will be
          presented on the graphics,
    default : rel),
gr_y_or_y2_os_stat -
    (y - y-axis will be used,
     y2 - y2-axis will be used,
     default : y),
gr_yrange_os_stat - y-range in the following
                       format: <number>:<number>,
gr_stat_name_N - name of a system statistics,
                 default : no name   
Show SQL Show Result On/Off
sp_seg_stats_bgraph9i
 reports segment statistics from the 
STATSPACK repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default: S,
gr_title - graphics title,
seg_stat_name - statistics to select
  (LOGICAL_READS, BUFFER_BUSY_WAITS,
   DB_BLOCK_CHANGES, PHYSICAL_READS,
   PHYSICAL_WRITES, ITL_WAITS,
   DIRECT_PHYSICAL_READS,
   DIRECT_PHYSICAL_WRITES,
   GC_CR_BLOCKS_RECEIVED,
   GC_CURRENT_BLOCKS_RECEIVED,
   GC_BUFFER_BUSY, ROW_LOCK_WAITS,
   GLOBAL_CACHE_CR_BLOCKS_SERVED,
   GLOBAL_CACHE_CU_BLOCKS_SERVED)
  default : PHYSICAL_READS,
object_type - object type 
  (TABLE, INDEX,
   TABLE PARTITION, INDEX PARTITION,
   LOB, etc.),
  default: all,
gr_abs_or_rel_seg_stat - 
   (abs - absolute values will be 
          presented on the graphics,
    rel - values per sec. will be
          presented on the graphics,
    default : rel),
gr_y_or_y2_seg_stat -
    (y - y-axis will be used,
     y2 - y2-axis will be used,
     default : y),
gr_yrange_seg_stat - y-range in the following
                   format: <number>:<number>,
gr_top_seg_stat - number of top segments,
                   default : 0,
gr_stat_name_N - name of a segment as
          <owner>.<obj_name>[.<subobj_name>],
                 default : no name 
Show SQL Show Result On/Off
sp_sga_stats_bgraph8i
 reports current sga statistics from 
the STATSPACK repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default: S,
        if group <> S, then average statistics 
        values will be calculated,
gr_title - graphics title,
gr_y_or_y2_sga_stat -
    (y - y-axis will be used,
     y2 - y2-axis will be used,
     default : y),
gr_yrange_sga_stat - y-range in the following
                       format: <number>:<number>,
pool_name - pool name, default: no name,
gr_stat_name_N - name of a sga statistics in
                 the following form:
                 <pool>:<stat_name>, for ex.
                 shared pool:free memory,
                 default : no name 
Show SQL Show Result On/Off
sp_sum_nonidle_waits_bgraph9i
 reports total waits for non-idle events
from the STATSPACK repository as a graphics
input parameters: 
begin_time - start time, 
              (format - dd.mm.yyyy hh24:mi:ss),
             default : sysdate - 1,
end_time   - end time, 
              (format - dd.mm.yyyy hh24:mi:ss),
             default : sysdate,
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S,
gr_title - graphics title,
gr_abs_or_rel_sum_waits - 
   (abs - absolute wait times will be 
          presented on the graphics,
    rel - wait times per sec. will be
          presented on the graphics,
    default : rel),
gr_y_or_y2_sum_waits -
    (y - y-axis will be used for wait times,
     y2 - y2-axis will be used for wait times,
     default : y),
gr_yrange_sum_waits - 
            y-range in the following format:
            <number>:<number>
Show SQL Show Result On/Off
sp_sys_curr_stats_bgraph8i
 reports current (not cumulative) system 
statistics from the STATSPACK repository as 
a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default: S,
        if group <> S, then average statistics 
        values will be calculated,
gr_title - graphics title,
gr_y_or_y2_sys_curr_stat -
    (y - y-axis will be used,
     y2 - y2-axis will be used,
     default : y),
gr_yrange_sys_curr_stat - y-range in the following
                       format: <number>:<number>,
gr_stat_name_N - name of a system statistics,
                 default : no name 
Show SQL Show Result On/Off
sp_sys_stats_bgraph8i
 reports system statistics from the 
STATSPACK repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default: S,
gr_title - graphics title,
gr_abs_or_rel_sys_stat - 
   (abs - absolute values will be 
          presented on the graphics,
    rel - values per sec. will be
          presented on the graphics,
    default : rel),
gr_y_or_y2_sys_stat -
    (y - y-axis will be used,
     y2 - y2-axis will be used,
     default : y),
gr_yrange_sys_stat - y-range in the following
                       format: <number>:<number>,
gr_stat_name_N - name of a system statistics,
                 default : no name 
Show SQL Show Result On/Off
sp_wait_events_bgraph9i
 reports wait statistics from the STATSPACK
repository as a graphics,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default : S
only_non_idle_events - 
   (Y - only non idle events in the output,
    N - all events,
    default : Y)
gr_title - graphics title,
gr_abs_or_rel_wait_event - 
   (abs - absolute wait times will be 
          presented on the graphics,
    rel - wait times per sec. will be
          presented on the graphics,
    default : rel),
gr_y_or_y2_wait_event -
    (y - y-axis will be used for wait times,
     y2 - y2-axis will be used for wait times,
     default : y),
gr_yrange_wait_event - y-range in the following
                       format: <number>:<number>,
gr_top_wait_event - number of top wait events,
                    default : 0,
gr_stat_name_N - name of a wait event,
                 default : no name   
Show SQL Show Result On/Off

Back to Directory of Topics

Statspack Reports
Start Comment Description SQL Result Protocol
sp_obj_sqltus10g
 top SQL statements from the STATSPACK
repository are reported,
input parameters: 
begin_time - start time (default : sysdate - 1),
end_time   - end time (default : sysdate),
(format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default: don't group data,
num_stmt - number of top statements, default 5,
with_sqltext - with SQL text in the output:
          ( Y - yes, N - no, default: Y),
trunc_sqltext_to - truncate SQL texts to N characters,
	           default - do not truncate,
with_exec_plan - with execution plans in the output:
	         (Y - yes, N - no, default: Y),
object_owner - object owner (default: all owners),
object_name - object name (default: all names),
object_type - object type (default: all types), 
command_type - one of the following SQL types:
CREATE TABLE,CREATE INDEX,ALTER INDEX,
SELECT,DELETE,INSERT,UPDATE,MERGE,
	 default : all commands types,
criteria - the criteria for top SQL statements:
 STATS [ E ], where STATS in
 (DR - disk reads, BG - buffer gets,
  CT - CPU time, ET - elapsed time,
  PL - PL/SQL execution time, 
  JA - JAVA execution time,
  WT - wait time, EX - executions,
  RP - rows processed, PC - parse calls,
  DW - direct writes, AP - application wait time,
  CO - concurrency wait time, CL - cluster wait time,
  IO - user io wait time),
  E - per execution. Combination EXE isn't allowed.
  Default : DR
Show SQL Show Result On/Off
sp_param_hist8i
 reports initialization parameters from the
STATSPACK repository,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),
param_like - parameter name
              (default : all parameters),
not_default - not default ? (TRUE, FALSE)
              (default : TRUE)
not_autom_sized - not automatically sized
                  parameters ? (TRUE, FALSE),
                  (default : TRUE)
Show SQL Show Result On/Off
sp_report102
 produces standard statspack report,
input parameters:
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss)
Show SQL Show Result On/Off
sp_snap_overview8i
 overviews the snapshots from the STATSPACK
repository,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss)
Show SQL Show Result On/Off
sp_sqltus10g
 top SQL statements from the STATSPACK
repository are reported,
input parameters: 
begin_time - start time (default : sysdate - 1),
end_time   - end time (default : sysdate),
(format - dd.mm.yyyy hh24:mi:ss),
group - grouping criteria:
       ( D - day, H - hour, S - snap_id),
        default: don't group data,
num_stmt - number of top statements, default 5,
with_sqltext - with SQL text in the output:
          ( Y - yes, N - no, default: Y),
trunc_sqltext_to - truncate SQL texts to N characters,
	           default - do not truncate,
with_exec_plan - with execution plans in the output:
	         (Y - yes, N - no, default: Y),
program_id - object id,
             default - SQL's from all programs,
(hash_value - hash value of the statement
and/or
old_hash_value - old hash value of the statement,
             default - all statemens),
signature - exact or force matching signature of SQL,
            default - all statements,
command_type - one of the following SQL types:
CREATE TABLE,CREATE INDEX,ALTER INDEX,
SELECT,DELETE,INSERT,UPDATE,MERGE,
	 default : all commands types,
criteria - the criteria for top SQL statements:
 STATS [ E ], where STATS in
 (DR - disk reads, BG - buffer gets,
  CT - CPU time, ET - elapsed time,
  PL - PL/SQL execution time, 
  JA - JAVA execution time,
  WT - wait time, EX - executions,
  RP - rows processed, PC - parse calls,
  DW - direct writes, AP - application wait time,
  CO - concurrency wait time, CL - cluster wait time,
  IO - user io wait time),
  E - per execution. Combination EXE isn't allowed.
  Default : DR
Show Desc Show SQL Show Result On/Off
sp_startup_time8i
 reports instance startup times from the 
STATSPACK repository,
input parameters: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss)
Show SQL Show Result On/Off

Back to Directory of Topics

Stored Outlines
Start Comment Description SQL Result Protocol
change_outln_category8i
 changes outlines category,
input parameter: outline_name,
                 new category
Show SQL Show Result On/Off
create_outln_sqlarea10g
 creates outline for one SQL
from sqlarea (sql_fulltext,v$sqlarea),
input parameters:
sql_id,
ouline_name (default: id_<sql_id>),
category (default: DEFAULT)
 
Show SQL Show Result On/Off
create_outln_sqlarea8i
 creates outline for one SQL
from sqlarea (v$sqltext_with_newlines),
input parameters:
address and/or
hash_value,
outline_name (default: hv_<hash_value>_<sysdate>),
category (default: DEFAULT)
Show SQL Show Result On/Off
create_outln_sqltext8i
 creates outline for one SQL
defined via sqltext,
input parameters:
outline_name,
category (default: DEFAULT)
 
Show Desc Show SQL Show Result On/Off
create_sqltext_from_outln8i
 creates a file with formatted sqltext from outline
input parameter: outln_name
Show SQL Show Result On/Off
dba_outlines
 selects from dba_outlines,
input parameters: outline_name
                  (default : all),
                  category
                  (default : all),
                  signature
                  (default : all)
Show SQL Show Result On/Off
drop_outline
 drops an outline,
input parameter: outline_name
Show SQL Show Result On/Off
drop_outline_category
 drops outlines by category,
input parameter: category
Show SQL Show Result On/Off
insert_hidden_hints_in_outln8i
 inserts hidden hints in stored outline
for a sql without hints from stored outline
for the same sql with hints,
input parameters: 
     originalsql_outln_name - name of stored
                              outline for sql
                              without hints,
     hintedsql_outln_name - name of stored 
                            outline for the
                            same sql with hints
Show SQL Show Result On/Off
outln_clear_used8i
 clear status "used" for outline,
input parameter: ouline_name
Show SQL Show Result On/Off
set_outline_category_in_sess
 generates a command to enable
outline category in a session,
input parameter: category,
          default: DEFAULT
Show SQL Show Result On/Off
sql_id_to_outln_signature10
 generates outline signature for a sql
from sqlarea (for selects from 
dba_outlines, etc) 
input parameter: sql_id
Show SQL Show Result On/Off
sqltext_to_outln_signature81
 generates outline signature for a sql
defined via sqltext (for selects 
from dba_outlines, etc)
Show Desc Show SQL Show Result On/Off

Back to Directory of Topics

System and OS Statistics
Start Comment Description SQL Result Protocol
os_stats10g
 shows os statistics,
input parameter : 
stat_name_like - statistic name
                (default : all)
Show SQL Show Result On/Off
os_stats_monitor10g
 monitors os statistics,
input parameter:
stat_name_like - statistics name 
                 (default: AVG%TIME),
sleep_interval_sec - sleep interval (sec.)
                 (default: 30)
Show SQL Show Result On/Off
sess_all_stats
 shows system statistics for a session,
input parameters : 
   sid,
   stat_name_like - statistics name 
                    (default : all)
Show SQL Show Result On/Off
sess_all_stats_monitor
 monitors system statistics for one session,
input parameters:
sid - SID of the session,
like_stat_name - statistics name 
                (default: all),
sleep_interval_sec - sleep interval in sec.
                (default: 30)
Show SQL Show Result On/Off
sess_mem_stats
 shows system memory statistics
for running sessions,
input parameter: sid
      (default: all sessions)
Show SQL Show Result On/Off
sess_stats_monitor
 monitors one statistics for all sessions,
input parameters:
statistics_name - statistics name
            (default: "CPU used by this session"),
sleep_interval_sec - sleep interval in sec.
            (default: 30),
num_top_sessions - number of top sessions. 
            (default: 10), 
requirement : TIMED_STATISTICS=TRUE 
Show SQL Show Result On/Off
sess_time_model10g
 shows time model statistics for one session,
input parameter: sid,
requirement: TIMED_STATISTICS=TRUE 
Show SQL Show Result On/Off
show_all_osstat10g
 shows names of os statistics,
input parameter : 
stat_name_like - statistics name,
                 (default : all) 
Show SQL Show Result On/Off
show_all_stats
 shows names of system statistics,
input parameter : 
stat_name_like - statistics name,
                 (default : all) 
Show SQL Show Result On/Off
sys_sort_stats9
 reports system sort statistics
Show SQL Show Result On/Off
sys_stats8i
 shows system statistics,
input parameter : 
stat_name_like - statistic name
                (default : all)
Show SQL Show Result On/Off
sys_stats_monitor
 monitors system statistics,
input parameter:
stat_name_like - statistics name 
                 (default: physical%),
sleep_interval_sec - sleep interval (sec.)
                 (default: 30),
requirement : TIMED_STATISTICS=TRUE
Show SQL Show Result On/Off
sys_time_model10g
 shows system time model statistics 
requirement: TIMED_STATISTICS=TRUE 
Show SQL Show Result On/Off
sys_time_model_rel10g
 shows relationships between the 
system time model statistics 
Show SQL Show Result On/Off

Back to Directory of Topics

Wait Events
Start Comment Description SQL Result Protocol
act_sql_wait_event
 shows a current SQL of the sessions
waiting for an event,
input parameter: event name 
Show SQL Show Result On/Off
all_sess_act_event_time
 shows, how long are sessions waiting 
for an event,
input parameter: event name,
requirement : TIMED_STATISTICS=TRUE 
Show SQL Show Result On/Off
all_sess_event
 shows the number of waiting sessions per event 
Show SQL Show Result On/Off
all_sess_sum_event_time
 shows, how long the running sessions 
waited for an event,
input parameter: event name,
requirement : TIMED_STATISTICS=TRUE 
Show SQL Show Result On/Off
block_sess_monitor10g
 monitoring of blocking sessions
for an event,
input parameters: event_name_like,
      (default: all events),
                  sleep interval (sec.),
      (default: 60)
Show SQL Show Result On/Off
blocking_sess_event10g
 reports blocking sessions 
for an event,
input parameter: event_name_like,
       default : all events
Show SQL Show Result On/Off
blocking_sess_hist10g
 reports history of blocking sessions 
for an event,
input parameter: 
begin_time - start time (default : sysdate - 1),
              (format - dd.mm.yyyy hh24:mi:ss),
end_time   - end time (default : sysdate),
              (format - dd.mm.yyyy hh24:mi:ss),

event_name_like - default: all events
Show SQL Show Result On/Off
buffer_busy_waits9i
 reports the block classes and data files affected
by buffer busy waits.
Pior to release 8.1.6 run first the script 
$ORACLE_HOME/rdbms/admin/catperf.sql to
create the view V$BUFFER_POOL_STATISTICS
Show SQL Show Result On/Off
bw_stats_monitor
 monitors buffer busy wait statistics,
input parameter:
sleep_interval_sec - sleep interval in sec.
                     (default: 30),
requirement : TIMED_STATISTICS=TRUE 
Show SQL Show Result On/Off
cursor_waiters10g
 reports sessions waiting for the
following wait events:
cursor: pin S wait on X,
cursor: pin S,
cursor: pin X
cursor: mutex X
Show SQL Show Result On/Off
event_monitor
 monitors wait events,
input parameters:
sleep_interval_sec - sleep interval in sec.
                     (default: 30),
num_top_events - number of top events
                     (default: all), 
requirement : TIMED_STATISTICS=TRUE 
Show SQL Show Result On/Off
file_bw_monitor8
 monitors buffer waits for datafiles,
input parameters:
sleep_interval_sec - sleep interval in sec.
                      (default: 30),
num_top_data_files - number of top datafiles
                      (default: all), 
requirement : TIMED_STATISTICS=TRUE 
Show SQL Show Result On/Off
nonidle_event_monitor10
 monitors non-idle wait events,
input parameter : sleep interval (sec.),
                  (default : 60),
requirement : TIMED_STATISTICS=TRUE
Show SQL Show Result On/Off
nonidle_event_report10
 reports waiting time for non-idle events,
requirement : TIMED_STATISTICS=TRUE
Show SQL Show Result On/Off
sess_wait_lib_cache_lock8
 shows sessions waiting for library cache lock
Show SQL Show Result On/Off
sess_wait_lib_cache_pin8
 shows sessions waiting for library cache pin
Show SQL Show Result On/Off
sess_waits_all
 shows wait statistics for one session,
input parameter: SID,
requirement: TIMED_STATISTICS=TRUE 
Show SQL Show Result On/Off
show_all_events10g
 shows complete names of wait events with 
wait classes and descriptions of respective 
parameters from the view sys.v_$session_wait,
input parameter: 
event_name_like - event name
                 (default: all events) 
Show SQL Show Result On/Off
sum_waits_event_sess
 shows summary wait statistics for 
running sessions,
requirement : TIMED_STATISTICS=TRUE 
Show SQL Show Result On/Off
sum_waits_event_sys
 shows system wait statistics, 
requirement : TIMED_STATISTICS=TRUE 
Show SQL Show Result On/Off

Back to Directory of Topics

Workareas
Start Comment Description SQL Result Protocol
one_exec_plan_sqlarea10g
 shows an execution plan for one SQL statement 
from the sqlarea,
input parameters: 
   SQL hash value and/or
   SQL Id and/or
   signature (exact or force matching_signature),
   SQL child number (default: all children),
   without_pred_info - without predicate information ?
                       (Y,N), default Y,
   truncate_other_to - truncate "OTHER" from
                       v$sql_plan to N 
                       characters,
                       (default : don't truncate)
Show Desc Show SQL Show Result On/Off
pgastat9
 shows PGA statistics 
Show SQL Show Result On/Off
sess_pga_mem9i
 shows pga memory 
for running processes
Show SQL Show Result On/Off
sess_pga_stats9
 shows PGA statistics for sessions, 
input parameter : sid
        (default: all sessions)
Show SQL Show Result On/Off
sess_sort_usage8
 reports sort usage of sessions,
input parameter : sid,
       (default : all sessions)
Show SQL Show Result On/Off
sess_workarea_activity9
 shows work area activity for 
sessions,
input parameter: sid
    (default : all Sessions) 
Show SQL Show Result On/Off
sys_sort_stats9
 reports system sort statistics
Show SQL Show Result On/Off
workarea_histograms9
 shows workarea histograms 
Show SQL Show Result On/Off

Back to Directory of Topics

Working under normal (not SYS) Account
Start Comment Description SQL Result Protocol
create_tool_X_views
 generates a create script for the X_$ views
used in this tool,
input parameter : user to grant select privileges
on the X_$ views
(default: public)
Show Desc Show SQL Show Result On/Off
grant_tool_sys_tables
 generates a script to grant the select privileges
on the SYS tables used in this tool,
input parameter : user to grant select privileges
on the SYS tables
(default: public)
Show Desc Show SQL Show Result On/Off
prepare_not_sys_user9
 prepares not sys user for working with this tool,
input parameter : user name
                  (default : user XYZ)
Show Desc Show SQL Show Result On/Off