Metrics Instrumentation Library
 
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
...
 
Related Topics
DBMS_STATS
Sequences
Tables
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx