81 lines
3.3 KiB
Plaintext
81 lines
3.3 KiB
Plaintext
create or replace package moats as
|
|
|
|
-- --------------------------------------------------------------------------
|
|
-- MOATS v1.0 (c) Tanel Poder & Adrian Billington, 2010
|
|
--
|
|
-- See supplied README.txt for usage and disclaimers.
|
|
--
|
|
-- http://www.e2sn.com
|
|
-- http://www.oracle-developer.net
|
|
-- --------------------------------------------------------------------------
|
|
|
|
-- Pipelined function to show TOP instance summary activity. The refresh rate
|
|
-- defines how many ASH/SYSSTAT samples to take and is roughly equivalent to
|
|
-- the number of seconds between screen refreshes...
|
|
-- --------------------------------------------------------------------------
|
|
function top (
|
|
p_refresh_rate in integer default null,
|
|
p_ash_window_size in integer default null
|
|
) return moats_output_ntt pipelined;
|
|
|
|
-- Helper procedure to size window to TOP output...
|
|
-- --------------------------------------------------------------------------
|
|
procedure format_window;
|
|
|
|
-- Internal Use Only
|
|
-- -----------------
|
|
-- All functions and procedures from this point onwards are for internal use
|
|
-- only in v1.01. Some of these will be exposed in future versions of MOATS.
|
|
|
|
-- Pipelined function to return dynamic samples, queries, aggregations etc
|
|
-- of active session data. The refresh rate defines (in seconds) how many
|
|
-- ASH samples to take before running the query components...
|
|
-- --------------------------------------------------------------------------
|
|
function ash (
|
|
p_refresh_rate in integer default null,
|
|
p_select in varchar2 default null,
|
|
p_where in varchar2 default null,
|
|
p_group_by in varchar2 default null,
|
|
p_order_by in varchar2 default null
|
|
) return moats_output_ntt pipelined;
|
|
|
|
-- Until 11g you can't bind UDTs into DBMS_SQL cursors, so this is an
|
|
-- internal workaround. Can also be used to query all of the collected ASH
|
|
-- samples. Note that gc_all_rows returns all ASH records between the two
|
|
-- snapshots and gc_deltas returns only the lower and upper snapshots...
|
|
-- --------------------------------------------------------------------------
|
|
gc_all_rows constant pls_integer := 0;
|
|
gc_deltas constant pls_integer := 1;
|
|
|
|
function get_ash (
|
|
p_lower_snap in pls_integer default null,
|
|
p_upper_snap in pls_integer default null,
|
|
p_return_set in pls_integer default moats.gc_all_rows
|
|
) return moats_ash_ntt pipelined;
|
|
|
|
-- Constants, get/set for moats parameters such as polling rate, ASH size...
|
|
-- --------------------------------------------------------------------------
|
|
gc_ash_polling_rate constant pls_integer := 0;
|
|
gc_ash_threshold constant pls_integer := 1;
|
|
gc_top_refresh_rate constant pls_integer := 2;
|
|
gc_ash_window_size constant pls_integer := 3;
|
|
|
|
procedure set_parameter(
|
|
p_parameter_code in pls_integer,
|
|
p_parameter_value in integer
|
|
);
|
|
|
|
function get_parameter(
|
|
p_parameter_code in pls_integer
|
|
) return integer;
|
|
|
|
procedure restore_default_parameters;
|
|
|
|
-- Debugging...
|
|
-- --------------------------------------------------------------------------
|
|
procedure show_snaps;
|
|
|
|
end moats;
|
|
/
|
|
|