Oracle Enterprise Manager is a perpetual well of joy!
In the Performance Hub page, it can show a yellow warning sign (⚠️). I didn't see any degradation because if this, but my inner Monk is disturbed, So I have to fix it.
This warning sign gibes a little information about an ASH package version and how it should be replaced.
For some reasons it's not always that easy to run these jobs (or they run, but the triangle doesn't disappear). However, there is a quite straight forward method to deploy the proper packages, make the triangle disappear (and my Monk happy):
the Scripts which need to be applied to the target DB are located on the OMS host in
<OMS_HOME>middleware/plugins/oracle.sysman.db.oms.plugin_*/sql/db/latest/instance/
(or whatever the plugin version is).
The files there are
.
└── middleware
└── plugins
└── oracle.sysman.db.oms.plugin_13.5.1.0.0
└── sql
└── db
└── latest
└── instance
├── ashviewer_pkgbodys.sql
├── ashviewer_pkgdefs.sql
├── dbms_compare_period.sql
├── eaddm_pkgbody.sql
├── eaddm_pkgdef.sql
├── omc_ashv_pkg_body.sql
├── omc_ashv_pkg.sql
├── priv_grant_omc_ash.sql
├── prvs_awr_data_cp.sql
├── prvs_awr_data.sql
├── prvt_awr_data_cp.sql
├── prvt_awr_data.sql
├── prvt_compare_period.sql
├── README.txt
└── test.sql
Not all these files are required. in fact, only these 3:
omc_ashv_pkg.sql omc_ashv_pkg_body.sql priv_grant_omc_ash.sql
To apply them into DBSNMP, it's required to grant execute on DBMS_SQL and DBMS_LOB to DBSNMP. The 3 scripts must be executed as DBSNMP.
With these steps, OEM is happy and the warning is gone. - everything is fine. (and especially for automated mass deployments, I see it harder to schedule some jobs in OEM and hope for their success, than simply run some scripts).
Just for the curious, the (more important from my perspective) parts of the package are
PACKAGE omc_ash_viewer
...
REPORT_INTERNAL_VERSION CONSTANT VARCHAR2(64) := '53';
-- date format to be used for communications with package.
ASH_TIME_FORMAT CONSTANT VARCHAR2(30) := 'MM/DD/YYYY HH24:MI:SS';
-- error ratio to be acceptable for not mixing in-memory with on disk.
-- We set it to 1 to always mix memory and disk in cases where neither
-- covers all the time period.
ASH_ALLOWED_ERR_RATIO CONSTANT NUMBER := 1;
-- -----------------------------------------------------
-- controlling the number of buckets.
-- constants are for settings of "LOW", "MEDIUM", "HIGH"
--
-- The setting of "MAX" (or "ALL") is:
-- same number of buckets as high, but no down sampling
-- -----------------------------------------------------
ASH_LOW_RESOLUTION CONSTANT VARCHAR2(10) := 'LOW';
ASH_LOW_BUCKETS CONSTANT NUMBER := 120;
ASH_LOW_ROWS_PER_BUCKET CONSTANT NUMBER := 20;
ASH_MED_RESOLUTION CONSTANT VARCHAR2(10) := 'MEDIUM';
ASH_MED_BUCKETS CONSTANT NUMBER := 180;
ASH_MED_ROWS_PER_BUCKET CONSTANT NUMBER := 35;
ASH_HIGH_RESOLUTION CONSTANT VARCHAR2(10) := 'HIGH';
ASH_HIGH_BUCKETS CONSTANT NUMBER := 360;
ASH_HIGH_ROWS_PER_BUCKET CONSTANT NUMBER := 50;
ASH_MAX_RESOLUTION CONSTANT VARCHAR2(10) := 'MAX';
-- default REAL TIME min bucket size in seconds
ASH_DEF_MEM_BUCKET_SIZE CONSTANT NUMBER := 10;
-- default Historical min bucket size in seconds
ASH_DEF_DISK_BUCKET_SIZE CONSTANT NUMBER := 20;
-- length of SQL text to fetch
ASH_DEF_SQLTEXT_LEN CONSTANT NUMBER := 200;
-- database version constants
VER_12_2 CONSTANT VARCHAR2(12) := '1202000000';
VER_12_1_2 CONSTANT VARCHAR2(12) := '1201000200';
VER_12_1 CONSTANT VARCHAR2(12) := '1201000000';
VER_12 CONSTANT VARCHAR2(12) := '1200000000';
VER_11_MIN CONSTANT VARCHAR2(12) := '1102000200';
VER_19 CONSTANT VARCHAR2(12) := '1900000000';
VER_20 CONSTANT VARCHAR2(12) := '2000000000';
TOP_ADD_INFO_COUNT CONSTANT BINARY_INTEGER := 20;
MAX_INFO_TIME_LIMIT CONSTANT BINARY_INTEGER := 2;
-- menu categories --
-- when you add a new category here, make sure to visit the function
-- generate_menu_xml and add the category there.
RSRC_CONS_CAT CONSTANT VARCHAR2(128) := 'resource_consumption_cat';
SESS_ID_CAT CONSTANT VARCHAR2(128) := 'session_identifiers_cat';
SESS_ATTR_CAT CONSTANT VARCHAR2(128) := 'session_attributes_cat';
SQL_CAT CONSTANT VARCHAR2(128) := 'sql_cat';
PLSQL_CAT CONSTANT VARCHAR2(128) := 'pl_sql_cat';
TARGET_CAT CONSTANT VARCHAR2(128) := 'target_category';
-- -------------------------------------------------------------------------
-- error number constants
-- -------------------------------------------------------------------------
ERR_DIMNAME_TOO_LONG CONSTANT NUMBER := -13720;
ERR_DIMNAME_INVALID CONSTANT NUMBER := -13721;
-- str_to_ascii converts a string in the DB language and character set to
-- ASCII8 that is safe to use in XML and XMLCDATA elements. Special
-- characters are masked based on UTF16 standard of \xxxx using asciistr
-- SQL function.
FUNCTION str_to_ascii(s IN VARCHAR) RETURN VARCHAR;
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
-- getSqlText
-- Returns the text and command of a SQL (if found) as an XML document
-- Data sources are gv$sql and AWR.
-- In case of a PDB: if AWR snapshots are taken regularly in the PDB,
-- The AWR data source is AWR_PDB_SQLTEXT, otherwise it is AWR_ROOT_SQLTEXT
--
-- Arguments:
-- p_dbid: the dbid to use.
-- NULL: fetch from the local RDBMS
-- If it matches the local dbid or con_dbid, fetch local.
-- Otherwise, assume imported snapshots.
-- p_sql_ids: the sql_id values we are interested in.
-- Option 1: commad separated list with no spaces.
-- Option 2: XML document in the format
-- <sqlid><m v="1q1spprb9m55h"></m><m v="a2k1zqcbp5nxf"</m></sqlid>
--
-- Returns: XML document containing the data, only of SQL that were found.
-- The format is:
-- <sqlid>
-- <m v="1q1spprb9m55h" op="SELECT">
-- <![CDATA[WITH MONITOR_DATA AS (SELECT INST_ID, KE ... ]]></m>
-- <m v="a2k1zqcbp5nxf" op="INSERT">
-- <![CDATA[insert into foo values(2)]]></m>
-- </sqlid>
--
-- SQL text is truncated to size ASH_DEF_SQLTEXT_LEN, same as data APIs
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
FUNCTION getSqlText(p_dbid IN NUMBER, p_sql_ids IN VARCHAR)
RETURN XMLTYPE;
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
-- fetch_sqltext
-- Returns the text and command of a SQL (if found) as an XML element
-- Subject to time limit: may return NULL if no time is left.
-- p_sqlid: the SQL ID of the SQL we are looking for.
-- p_is_local: 'y' if the data is from the RDBMS we query
-- any other value indicates an imported AWR snapshot
-- p_dbid and p_con_dbid: In case of a local (p_is_local = 'y') p_dbid
-- is the root's dbid and p_con_dbid is the PDB's dbid.
-- p_con_dbid can be null in case of old RDBMS, or in case
-- we are using a standard RDBMS.
-- p_is_pdb: 'y' if it is a local query from inside a pdb.
-- p_is_old: 'y' if the local RDBMS version is 12.1 or below.
-- p_time_limit: 'y' if we are bound by time limit.
--
-- We may search for SQL text in various places depending on the situation.
-- Order of search for the various cases:
-- A. Local snapshots, Non-CDB RDBMS or Root of CDB:
-- 1. V$SQL using sql_id
-- 2. DBA_HIST_SQLTEXT using sql_id, root_dbid
--
-- B. Local snapshots, inside PDB, version 12.1 (is_old)
-- 1. V$SQL using sql_id
-- 2. DBA_HIST_SQLTEXT using sql_id, root_dbid
--
-- C. Local snapshots, inside PDB, versions 12.2 and above
-- 1. V$SQL using sql_id
-- 2. AWR_PDB_SQLTEXT using sql_id, con_dbid
-- 2. AWR_ROOT_SQLTEXT using sql_id, root_dbid
--
-- D. Imported snapshots, standard RDBMS or Root of CDB
-- 1. DBA_HIST_SQLTEXT using sql_id, dbid
--
-- E. Imported snapshots, PDB versions 12.2 and onwards
-- 1. WR_PDB_SQLTEXT using sql_id, dbid
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
FUNCTION fetch_sqltext(p_sqlid IN VARCHAR,
p_dbid IN NUMBER, p_con_dbid IN NUMBER,
p_is_local IN VARCHAR, p_is_pdb IN VARCHAR,
p_is_old IN VARCHAR, p_time_limit IN VARCHAR)
RETURN XMLTYPE;
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
-- fetch_obj_name
-- Returns the fully qualified name of an object and its type as
-- an XML element.
-- Subject to time limit: may return NULL if no time is left.
-- p_obj_id: the ID of the segment.
-- p_is_local: 'y' if the data is from the RDBMS we query
-- any other value indicates an imported AWR snapshot
-- p_dbid and p_con_dbid: In case of a local (p_is_local = 'y') p_dbid
-- is the root's dbid and p_con_dbid is the PDB's dbid.
-- p_con_dbid can be null in case of old RDBMS, or in case
-- we are using a standard RDBMS.
-- p_local_type: determines what RDBMS we are connected to (local)
-- 'ROOT' is the root of a CDB
-- 'PDB' is a PDB
-- All other values mean a standard RDBMS
-- p_is_old: 'y' if the local RDBMS version is 12.1 or below.
-- p_time_limit: 'y' if we are bound by time limit.
--
-- We may search for object in various places depending on the situation.
-- a. Dictionary (dba_objects or cdb_objects)
-- dba_objects is moderately expensive to try
-- cdb_objects is very expensive: should be tried last
-- b. Local AWR using awr_pdb_seg_stat_obj or dba_hist_seg_stat_obj
-- This is the cheapest data source. Should be tried first.
-- c. Object link AWR (when inside a PDB and looking at the root).
-- View is awr_root_seg_stat_obj (versions 12.2 and above) or
-- dba_hist_seg_stat_obj in version 12.1
-- This is very expensive.
--
-- Order of search for the various cases:
--
-- A. Local snapshots, CDB Root:
-- 1. AWR(obj_id, root_dbid, con_dbid, 'DBA_HIST')
-- 2. Dictionary(obj_id, con_dbid, CDB_OBJECTS)
--
-- B. Local Snapshots, Non-CDB DB:
-- 1. AWR(obj_id, dbid, 'DBA_HIST')
-- 2. Dictionary(obj_id, DBA_OBJECTS)
--
-- C. Local Snapshots, PDB versions 12.1 and below
-- 1. Dictionary(obj_id, DBA_OBJECTS)
-- 2. AWR(obj_id, root_dbid, con_dbid, 'DBA_HIST')
--
-- D. Local snapshots, PDB versions 12.2 and above
-- 1. AWR(obj_id, con_dbid, 'AWR_PDB')
-- 2. Dictionary(obj_id, DBA_OBJECTS)
-- 3. AWR(obj_id, root_dbid, con_dbid, 'AWR_ROOT')
--
-- E. Imported snapshots, CDB root, standard RDBMS
-- 1. AWR(obj_id, dbid, con_dbid, 'DBA_HIST')
--
-- F. Imported Snapshots, inside PDB
-- 1. AWR(obj_id, dbid, con_dbid, 'AWR_PDB')
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
FUNCTION fetch_obj_name(p_obj_id IN NUMBER, p_dbid IN NUMBER,
p_con_dbid IN NUMBER, p_is_local IN VARCHAR,
p_local_type IN VARCHAR, p_is_old IN VARCHAR,
p_time_limit IN VARCHAR)
RETURN XMLTYPE;
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
-- fetch_procedure_name
-- Returns the full qualified PL/SQL procedure name (if found)
-- Subject to time limit: may return NULL if no time is left.
-- This can only run on local DB - looks at dictionary
-- ROOT: look at cdb_procedures
-- PDB/standard DB: look at dba_procedures
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
FUNCTION fetch_procedure_name(p_obj_id IN NUMBER, p_subobj_id IN NUMBER,
p_con_dbid IN NUMBER, p_time_limit IN VARCHAR)
RETURN VARCHAR;
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
-- fetch_user_name
-- Returns the user name (if found)
-- Subject to time limit: may return NULL if no time is left.
-- This can only run on local DB - looks at dictionary
-- ROOT: look at cdb_users
-- PDB/standard DB: look at dba_users
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
FUNCTION fetch_user_name(p_user_id IN NUMBER, p_con_dbid IN NUMBER,
p_time_limit IN VARCHAR)
RETURN VARCHAR;
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
-- getVersion
-- Returns the version of the package
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
FUNCTION getVersion RETURN VARCHAR;
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
-- getData
-- Single API for all other public functions in this package.
-- "data_type"
-- specifies which type of API is to be used. accepted values are:
-- "data" for ASH data
-- "timepicker" for the Time Picker graph
-- "histogram" for a filtered time picker.
-- "version" for getting just the package version
-- "awr" for getting AWR info
-- "cpu" for getting CPU info
-- If an invalid value is given, ORA-20001 is raised as an error.
-- "time_type"
-- specifies how the time period is to be interpreted.
-- (useful only for "data", "timepicker", "histogram") APIs
-- "realtime" for all Real Time interfaces (from some time in the past to NOW)
-- "incremental" for an increment over real time (bucket size must be defined)
-- "historical" for a longer time period or a time period in the past (two time stamps)
-- If an invalid value is given, ORA-20002 is raised as an error.
-- "filter_list"
-- is the filter used in the same way as the original package
-- "args"
-- contains the rest of the arguments in XML format.
-- The xml format is as follows (example containing all valid arguments)
-- If a mandatory argument is missing, ORA-20003 is raised as an error.
--
-- <args>
-- <dbid>87658765</dbid>
-- <instance_number>1</instance_number>
-- <time_since_sec>3600</time_since_sec>
-- <begin_time_utc>07/23/2018 10:20:00</begin_time_utc>
-- <end_time_utc>07/24/2018 08:30:00</end_time_utc>
-- <bucket_size>30</bucket_size>
-- <show_sql>n</show_sql>
-- <verbose_xml>n</verbose_xml>
-- <include_bg>n</include_bg>
-- <minimize_cost>n</minimize_cost>
-- <awr_info>n</awr_info>
-- <resolution>medium</resolution>
-- </args>
--
-- Arguments that are not needed or that you wish to use the default values for,
-- do not need to be specified in the XML doc.
--
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
FUNCTION getData(data_type VARCHAR2,
time_type VARCHAR2,
filter_list VARCHAR2,
args VARCHAR2
) RETURN XMLTYPE;
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
-- getAWRInfo
-- Returns information about AWR snapshots and ADDM Tasks
-- p_dbid - specifies which dbid to look for
-- p_inst_num - specifies the instance (if we want just one instance),
-- use the default of NULL to get info on all instances
-- p_begin_time_utc, p_end_time_utc - the time interval for information
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
FUNCTION getAWRInfo(p_dbid IN NUMBER, p_begin_time_utc IN VARCHAR2,
p_end_time_utc IN VARCHAR2, p_inst_num IN NUMBER := NULL)
RETURN XMLTYPE;
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
-- getCPUInfo
-- Returns information about availbale CPUs in XML format at a single
-- point in time.
-- - dbid : specifies which db to look for, default (NULL) is DB we are
-- conncted to.
-- - observationTime : approximate time in which to look for data.
-- default (NULL) is the latest possible data available
-- (NOW if possible).
-- - ignore_cpu_history : when 'y', the API will not search for CPU
-- history from AWR
-- If instance_number is NULL it means all instances. Otherwise, fetch
-- only data for the specified instance number.
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
FUNCTION getCPUInfo(dbid IN NUMBER := NULL,
observationTime IN VARCHAR := NULL,
instance_number IN NUMBER := NULL,
ignore_cpu_history IN VARCHAR := 'n')
RETURN XMLTYPE;
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
-- getTimePickerRealTime
-- Returns the time picker data for Real Time usage in XML format
-- Time period is from NOW-time_since_sec to NOW.
-- The default time period is the last hour.
-- data is for entire database (all instances) we ara currently connected to
-- ,foreground only, and in case we connect to a PDB - limited to that PDB.
-- If instance_number is NULL it means all instances. Otherwise, fetch
-- only data for the specified instance number.
--
-- The format of the output is (example):
-- <report> <report_parameters> ... </report_parameters>
-- <awr_snaps>
-- <snap snap_id="15" snap_time="11/27/2018 11:00:54" cnt_inst="1"
-- task_id="34" owner="SYS" task_name="ADDM:1997586511_1_15"
-- fdg_count="0"/>
-- <snap snap_id="16" snap_time="11/27/2018 12:00:08" cnt_inst="1"
-- task_id="35" owner="SYS" task_name="ADDM:1997586511_1_16"
-- fdg_count="0"/>
-- </awr_snaps>
-- </report>
-- An xml element of the list of snapshots is included as an option
-- for all time picker reports over local data.
-- The element's meaning:
-- 1. snap_id : the id of the AWR snapshot
-- 2. snap_time: the timestamp of the end time of the snapshot (i.e.
-- the time the snapshot was taken). In case of RAC, it
-- is the average time across all instances for the same
-- snapshot
-- 3. cnt_inst: number of instances participating in the snapshot.
-- In case the API is at instance level, this will be
-- always '1' even in RAC
-- 4. task_id: The id of the automatically generated ADDM task
-- associated with the snapshot (we choose the minimal
-- task_id in case there is more than one)
-- 5. owner, task_name: Another way to identify a task in advisor
-- framework.
-- 6. fdg_count: the number of findings in the ADDM task
-- 7. resolution: valid values are 'low', 'medium', 'high', 'max' (or 'all')
-- "reolution" control the number of buckets and rows per bucket.
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
FUNCTION getTimePickerRealTime(
time_since_sec IN NUMBER := 3600
, show_sql IN VARCHAR2 := 'n'
, verbose_xml IN VARCHAR2 := 'n'
, instance_number IN NUMBER := NULL
, awr_info IN VARCHAR2 := 'n'
, ignore_cpu_history IN VARCHAR := 'n'
, resolution IN VARCHAR2 := 'medium'
, include_bg IN VARCHAR2 := 'n')
RETURN XMLTYPE;
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
-- incrementTimePicker
-- Returns the time picker data for Real Time usage in XML format
-- This function is used to get incremental data after the initial load.
-- Incremental use case is only for Real Time.
-- Time period is from begin_time_utc to NOW.
-- There is no default time period.
-- The time is bucketized using bucket_size (in seconds).
-- The bucket boundaries are:
-- begin_time_utc, begin_time_utc+bucket_size,
-- begin_time_utc+2*bucket_size etc.
-- If instance_number is NULL it means all instances. Otherwise, fetch
-- only data for the specified instance number.
-- resolution: valid values are 'low', 'medium', 'high', 'max' (or 'all')
-- "reolution" control the rows per bucket.
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
FUNCTION incrementTimePicker(
begin_time_utc IN VARCHAR2
, bucket_size IN NUMBER
, show_sql IN VARCHAR2 := 'n'
, verbose_xml IN VARCHAR2 := 'n'
, instance_number IN NUMBER := NULL
, awr_info IN VARCHAR2 := 'n'
, resolution IN VARCHAR2 := 'medium'
, include_bg IN VARCHAR2 := 'n')
RETURN XMLTYPE;
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
-- getTimePickerHistorical
-- Returns the time picker data for historical in an XML format
-- dbid determines which RDBMS to look for, default is the one we are
-- connected to.
-- Time period is one of the following
-- a) From begin_time_utc to end_time_utc if both are specified.
-- b) From NOW-time_since_sec to NOW (default is 24 hours)
-- If instance_number is NULL it means all instances. Otherwise, fetch
-- only data for the specified instance number.
-- resolution: valid values are 'low', 'medium', 'high', 'max' (or 'all')
-- "reolution" control the number of buckets and rows per bucket.
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
FUNCTION getTimePickerHistorical(
dbid IN NUMBER := NULL
, begin_time_utc IN VARCHAR2 := NULL
, end_time_utc IN VARCHAR2 := NULL
, time_since_sec IN NUMBER := 86400
, show_sql IN VARCHAR2 := 'n'
, verbose_xml IN VARCHAR2 := 'n'
, instance_number IN NUMBER := NULL
, awr_info IN VARCHAR2 := 'n'
, ignore_cpu_history IN VARCHAR := 'n'
, resolution IN VARCHAR2 := 'medium'
, include_bg IN VARCHAR2 := 'n')
RETURN XMLTYPE;
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
-- getHistogramRealTime
-- Returns the ASH histogram for Real Time Usage in an XML format
-- Time period is from NOW-time_since_sec to NOW (default is one hour)
-- The data can be filtered using the filter list.
-- data is for entire database (all instances) we ara currently connected to
-- ,foreground only, and in case we connect to a PDB - limited to that PDB.
-- If instance_number is NULL it means all instances. Otherwise, fetch
-- only data for the specified instance number.
-- resolution: valid values are 'low', 'medium', 'high', 'max' (or 'all')
-- "reolution" control the number of buckets and rows per bucket.
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
FUNCTION getHistogramRealTime(
filter_list IN VARCHAR2 := NULL
, time_since_sec IN NUMBER := 3600
, show_sql IN VARCHAR2 := 'n'
, verbose_xml IN VARCHAR2 := 'n'
, include_bg IN VARCHAR2 := 'n'
, instance_number IN NUMBER := NULL
, ignore_cpu_history IN VARCHAR := 'n'
, resolution IN VARCHAR2 := 'medium')
RETURN XMLTYPE;
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
-- incrementHistogram
-- Returns the ASH histogram for Real Time usage in XML format
-- This function is used to get incremental data after the initial load.
-- Incremental use case is only for Real Time.
-- Time period is from begin_time_utc to NOW.
-- There is no default time period.
-- The time is bucketized using bucket_size (in seconds).
-- The bucket boundaries are:
-- begin_time_utc, begin_time_utc+bucket_size,
-- begin_time_utc+2*bucket_size etc.
-- The data can be filtered using the filter list.
-- If instance_number is NULL it means all instances. Otherwise, fetch
-- only data for the specified instance number.
-- resolution: valid values are 'low', 'medium', 'high', 'max' (or 'all')
-- "reolution" control the rows per bucket.
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
FUNCTION incrementHistogram(
filter_list IN VARCHAR2 := NULL
, begin_time_utc IN VARCHAR2
, bucket_size IN NUMBER
, show_sql IN VARCHAR2 := 'n'
, verbose_xml IN VARCHAR2 := 'n'
, include_bg IN VARCHAR2 := 'n'
, instance_number IN NUMBER := NULL
, resolution IN VARCHAR2 := 'medium')
RETURN XMLTYPE;
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
-- getHistogramHistorical
-- Returns the ASH histogram for historical usage in an XML format
-- dbid determines which RDBMS to look for, default is the one we are
-- connected to.
-- Time period is one of the following
-- a) From begin_time_utc to end_time_utc if both are specified.
-- b) From NOW-time_since_sec to NOW (default is 24 hours)
-- The data can be filtered using the filter list.
-- If instance_number is NULL it means all instances. Otherwise, fetch
-- only data for the specified instance number.
-- resolution: valid values are 'low', 'medium', 'high', 'max' (or 'all')
-- "reolution" control the number of buckets and rows per bucket.
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
FUNCTION getHistogramHistorical(
dbid IN NUMBER := NULL
, filter_list IN VARCHAR2 := NULL
, begin_time_utc IN VARCHAR2 := NULL
, end_time_utc IN VARCHAR2 := NULL
, time_since_sec IN NUMBER := 86400
, show_sql IN VARCHAR2 := 'n'
, verbose_xml IN VARCHAR2 := 'n'
, include_bg IN VARCHAR2 := 'n'
, instance_number IN NUMBER := NULL
, ignore_cpu_history IN VARCHAR := 'n'
, resolution IN VARCHAR2 := 'medium')
RETURN XMLTYPE;
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
-- getDataRealTime
-- Returns the ASH data for Real Time Usage in an XML format
-- Time period is from NOW-time_since_sec to NOW (default is one hour)
-- The data can be filtered using the filter list.
-- data is for entire database (all instances) we ara currently connected to
-- ,foreground only, and in case we connect to a PDB - limited to that PDB.
-- If instance_number is NULL it means all instances. Otherwise, fetch
-- only data for the specified instance number.
-- Minimize_cost: If set to 'y',
-- a. the time budget for additional information is 0.
-- b. on disk data is disabled
-- resolution: valid values are 'low', 'medium', 'high', 'max' (or 'all')
-- "reolution" control the number of buckets and rows per bucket.
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
FUNCTION getDataRealTime(
filter_list IN VARCHAR2 := NULL
, time_since_sec IN NUMBER := 3600
, show_sql IN VARCHAR2 := 'n'
, verbose_xml IN VARCHAR2 := 'n'
, include_bg IN VARCHAR2 := 'n'
, instance_number IN NUMBER := NULL
, minimize_cost IN VARCHAR2 := 'n'
, resolution IN VARCHAR2 := 'medium')
RETURN XMLTYPE;
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
-- incrementData
-- Returns the ASH data for Real Time usage in XML format
-- This function is used to get incremental data after the initial load.
-- Incremental use case is only for Real Time.
-- Time period is from begin_time_utc to NOW.
-- There is no default time period.
-- The time is bucketized using bucket_size (in seconds).
-- The bucket boundaries are:
-- begin_time_utc, begin_time_utc+bucket_size,
-- begin_time_utc+2*bucket_size etc.
-- The data can be filtered using the filter list.
-- If instance_number is NULL it means all instances. Otherwise, fetch
-- only data for the specified instance number.
-- Minimize_cost: If set to 'y',
-- the time budget for additional information is 0.
-- resolution: valid values are 'low', 'medium', 'high', 'max' (or 'all')
-- "reolution" control the rows per bucket.
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
FUNCTION incrementData(
filter_list IN VARCHAR2 := NULL
, begin_time_utc IN VARCHAR2
, bucket_size IN NUMBER
, show_sql IN VARCHAR2 := 'n'
, verbose_xml IN VARCHAR2 := 'n'
, include_bg IN VARCHAR2 := 'n'
, instance_number IN NUMBER := NULL
, minimize_cost IN VARCHAR2 := 'n'
, resolution IN VARCHAR2 := 'medium')
RETURN XMLTYPE;
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
-- getDataHistorical
-- Returns the ASH data for historical usage in an XML format
-- dbid determines which RDBMS to look for, default is the one we are
-- connected to.
-- Time period is one of the following
-- a) From begin_time_utc to end_time_utc if both are specified.
-- b) From NOW-time_since_sec to NOW (default is 24 hours)
-- The data can be filtered using the filter list.
-- If instance_number is NULL it means all instances. Otherwise, fetch
-- only data for the specified instance number.
-- Minimize_cost: If set to 'y',
-- the time budget for additional information is 0.
-- resolution: valid values are 'low', 'medium', 'high', 'max' (or 'all')
-- "reolution" control the number of buckets and rows per bucket.
-- ------------------------------------------------------------------------
-- ------------------------------------------------------------------------
FUNCTION getDataHistorical(
dbid IN NUMBER := NULL
, filter_list IN VARCHAR2 := NULL
, begin_time_utc IN VARCHAR2 := NULL
, end_time_utc IN VARCHAR2 := NULL
, time_since_sec IN NUMBER := 86400
, show_sql IN VARCHAR2 := 'n'
, verbose_xml IN VARCHAR2 := 'n'
, include_bg IN VARCHAR2 := 'n'
, instance_number IN NUMBER := NULL
, minimize_cost IN VARCHAR2 := 'n'
, resolution IN VARCHAR2 := 'medium')
RETURN XMLTYPE;
As the package body isn't wrapped at all, it's easy to search for implementation details there also.
