Home
Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups
General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement |
|
Generic Metrics Collection Instrumentation Library |
| Supporting Tables |
CREATE TABLE sc_metrics_log (
instance_id NUMBER,
run_no NUMBER,
service_name VARCHAR2(30),
schema_name VARCHAR2(30),
seg_name VARCHAR2(30),
beg_run DATE,
end_run DATE,
cpu_time NUMBER,
phys_reads NUMBER,
phys_writes NUMBER,
blocks_changed NUMBER,
failure_desc VARCHAR2(256),
outcome VARCHAR2(1) DEFAULT 'F')
PARTITION BY RANGE (instance_id) INTERVAL (1) (
PARTITION "ROOT_PAR" VALUES LESS THAN (2) PCTFREE 0 PCTUSED 99);
-- Fail / Success
ALTER TABLE sc_metrics_log
ADD CONSTRAINT cc_sc_metrics_log_outcome
CHECK (outcome IN ('F','S'));
CREATE OR REPLACE VIEW sc_failures AS
SELECT run_no, schema_name, seg_name, beg_run, failure_desc
FROM sc_metrics_log
WHERE outcome = 'F'
ORDER BY 1,2,3;
CREATE OR REPLACE VIEW sc_daily_review AS
SELECT schema_name, TO_CHAR(MIN(beg_run), 'HH24:MI:SS') BRUN,
TO_CHAR(MAX(end_run), 'HH24:MI:SS') ERUN
FROM sc_metrics_log
WHERE run_no = (SELECT MAX(run_no) FROM sc_metrics_log)
GROUP BY schema_name
ORDER BY 1; |
| Package Header |
CREATE OR REPLACE PACKAGE mlib_metrics AUTHID CURRENT_USER IS
/************************************************************************
* NAME: mlib_metrics
* TYPE: Package Specification
*
* PURPOSE: Collect and log metrics that can be used to quantify resource
* usage that may be used for diagnostic and predictive purposes
*
* Revision History:
********************************************************************************
* Date Author Comments *
* 01-AUG-2011 DA Morgan Initial release *
* *
* This code is protected under applicable copyright law and may be freely *
* used and distributed as long as in so doing it remains free. Any *
* modifications to this code do not constitute a waiver of this restriction. *
* *
* Copyright (c) 2011 Daniel A. Morgan *
* Made Available Pursuant To GNU General Public License 2' *
*******************************************************************************/
/*********** GLOBAL CONSTANTS *************/
objVer CONSTANT NUMBER := 2.00;
/* -- TYPE DEFINITIONS -- */
TYPE metrics_array IS RECORD (
instance_id sc_metrics_log.instance_id%TYPE,
run_no sc_metrics_log.run_no%TYPE,
service_name sc_metrics_log.service_name%TYPE,
schema_name sc_metrics_log.schema_name%TYPE,
seg_name sc_metrics_log.seg_name%TYPE,
beg_run sc_metrics_log.beg_run%TYPE,
end_run sc_metrics_log.end_Run%TYPE,
cpu_time sc_metrics_log.cpu_time%TYPE,
phys_reads sc_metrics_log.phys_reads%TYPE,
phys_writes sc_metrics_log.phys_reads%TYPE,
blocks_changed sc_metrics_log.blocks_changed%TYPE,
failure_desc sc_metrics_log.failure_desc%TYPE,
outcome sc_metrics_log.outcome%TYPE);
FUNCTION get_version RETURN VARCHAR2;
FUNCTION Get_BlockChanges RETURN NUMBER;
FUNCTION Get_CPU RETURN NUMBER;
FUNCTION Get_DBLink_Bytes RETURN PLS_INTEGER;
FUNCTION Get_PhysRead_Bytes RETURN NUMBER;
FUNCTION Get_PhysWrite_Bytes RETURN NUMBER;
PROCEDURE Write_Metrics(metrics_rec IN metrics_array);
END mlib_metrics;
/
sho err |
| Package Body |
CREATE OR REPLACE PACKAGE BODY mlib_metrics IS
/************************************************************************
* NAME: mlib_metric
* TYPE: Package Body
*
* PURPOSE: capture metrics that can be used to quantify resource
* usage that may be used for diagnostic and predictive purposes
*
* Revision History:
********************************************************************************
* Date Author Comments *
* 01-AUG-2011 DA Morgan Initial release *
* *
* This code is protected under applicable copyright law and may be freely *
* used and distributed as long as in so doing it remains free. Any *
* modifications to this code do not constitute a waiver of this restriction. *
* *
* Copyright (c) 2011 Daniel A. Morgan *
* Made Available Pursuant To GNU General Public License 2' *
*******************************************************************************/
FUNCTION get_version RETURN VARCHAR2 IS
BEGIN
RETURN '2.0.0';
END get_version;
--========================================================================
FUNCTION Get_BlockChanges RETURN NUMBER IS
RetVal NUMBER;
BEGIN
SELECT ms.value
INTO RetVal
FROM v$mystat ms
WHERE ms.statistic# = 67;
RETURN RetVal;
END Get_BlockChanges;
--========================================================================
FUNCTION Get_CPU RETURN NUMBER IS
RetVal NUMBER;
BEGIN
SELECT ms.value
INTO RetVal
FROM v$mystat ms
WHERE ms.statistic# = 12;
RETURN RetVal;
END Get_CPU;
--========================================================================
FUNCTION Get_DBLink_Bytes RETURN PLS_INTEGER IS
-- returns bytes received via SQL*Net from dblink
RetVal NUMBER;
BEGIN
SELECT ms.value
INTO RetVal
FROM v$mystat ms
WHERE ms.statistic# = 456;
RETURN RetVal;
END Get_DBLink_Bytes;
--==============================================================
FUNCTION Get_PhysRead_Bytes RETURN NUMBER IS
RetVal NUMBER;
BEGIN
SELECT value
INTO RetVal
FROM v$mystat ms
WHERE ms.statistic# = 66;
RETURN RetVal;
END Get_PhysRead_Bytes;
--==============================================================
FUNCTION Get_PhysWrite_Bytes RETURN NUMBER IS
RetVal NUMBER;
BEGIN
SELECT ms.value
INTO RetVal
FROM v$mystat ms
WHERE ms.statistic# = 78;
RETURN RetVal;
END Get_PhysWrite_Bytes;
--==============================================================
PROCEDURE Write_Metrics(metrics_rec IN metrics_array) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO sc_metrics_log
(instance_id, run_no, seg_name, beg_run, end_run,
cpu_time, phys_reads, phys_writes, blocks_changed, outcome)
VALUES
(sys_context('USERENV', 'INSTANCE'), metrics_rec.run_no,
metrics_rec.seg_name, metrics_rec.beg_run, metrics_rec.end_run,
metrics_rec.cpu_time, metrics_rec.phys_reads, metrics_rec.phys_writes,
metrics_rec.blocks_changed, metrics_rec.outcome);
COMMIT;
END Write_Metrics;
--========================================================================
BEGIN
NULL;
END mlib_metrics;
/
sho err |
| ... |
|
