Optimize collection of (CBO) optimizer statistics?
 
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
Working with DBMS_STATS
Lets be honest here ... Oracle's default database backup job isn't worth the bytes used to construct it. All of the power and flexibility built into the DBMS_STATS package is ignored in favour of a one-size fits all which is not going to be a good solution unless you purchased your Oracle database licenses to track sales and inventory of a one-person hotdog stand.

So this code, released under the Gnu license addresses the real-world stats collection issues by driving statistics collection from metadata tables and by collecting statistics that can be used to continuously manage and improve the statistics collection cycle.

First lets build the metadata tables and their related infrastructure. In the following context "SC" stands for "statistics collection," "sch" is short for "schema," and "seg" is short for "segment." Inst is the working abbreviation for "instance."

Warning: Be aware of the implications of running DBMS_STATS and collecting histograms where histograms have not previously existed or exist but differ from the collection set in this application. It is possible for a small number of histogram changes to have a major impact on an application.
CREATE SEQUENCE sc_run_seq;

CREATE TABLE sc_schema_metadata (
schema_name     VARCHAR2(30),
inst_id         NUMBER(2),
where_clause    VARCHAR2(4000)        NOT NULL,
schema_priority NUMBER(1)   DEFAULT 5 NOT NULL,
active_flag     VARCHAR2(1) DEFAULT 'A')
pctfree 0 pctused 99;

ALTER TABLE sc_schema_metadata
ADD CONSTRAINT pk_sc_sch_metadata
PRIMARY KEY (schema_name, inst_id);

ALTER TABLE sc_schema_metadata
ADD CONSTRAINT cc_sc_sch_metadata_inst
CHECK (inst_id BETWEEN 1 AND 8);

ALTER TABLE sc_schema_metadata
ADD CONSTRAINT cc_sc_sch_priority
CHECK (schema_priority BETWEEN 1 AND 9);

ALTER TABLE sc_schema_metadata
ADD CONSTRAINT cc_sc_sch_metadata_flag
CHECK (active_flag IN ('A','I'));

CREATE TABLE sc_segment_metadata (
schema_name      VARCHAR2(30),
inst_id          NUMBER(2),
table_name       VARCHAR2(30),
est_pct          NUMBER DEFAULT 1            NOT NULL,
block_sample     VARCHAR2(5)  DEFAULT 'TRUE' NOT NULL,
method_opt       VARCHAR2(50) DEFAULT 'FOR ALL INDEXED COLUMNS SIZE 1' NOT NULL,
degree           NUMBER(3)    DEFAULT 2      NOT NULL,
granularity      VARCHAR2(20)                NOT NULL,
cascade          VARCHAR2(5)  DEFAULT 'TRUE' NOT NULL,
no_invalidate    VARCHAR2(5)  DEFAULT 'TRUE' NOT NULL,
stattype         VARCHAR2(10) DEFAULT 'DATA' NOT NULL,
force            VARCHAR2(5)  DEFAULT 'TRUE' NOT NULL,
segment_priority NUMBER(1)    DEFAULT 9      NOT NULL,
active_flag      VARCHAR2(1)  DEFAULT 'A'    NOT NULL)
pctfree 0 pctused 99;

ALTER TABLE sc_segment_metadata
ADD CONSTRAINT pk_sc_seg_metadata
PRIMARY KEY (schema_name, inst_id, table_name);

ALTER TABLE sc_segment_metadata
ADD CONSTRAINT fk_sc_seg_schema_inst
FOREIGN KEY (schema_name, inst_id)
REFERENCES sc_schema_metadata(schema_name, inst_id)
INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE sc_segment_metadata
ADD CONSTRAINT cc_sc_seg_metadata_estpct
CHECK (est_pct BETWEEN 0.000001 AND 100);

ALTER TABLE sc_segment_metadata
ADD CONSTRAINT cc_sc_seg_metadata_blksamp
CHECK (block_sample IN ('TRUE','FALSE'));

-- the following assumes a maximum allowed 12 degrees of parallelism for collection.
-- most likely you should use a far smaller number to avoid consuming too much CPU.

ALTER TABLE sc_segment_metadata
ADD CONSTRAINT cc_sc_seg_metadata_degree
CHECK (degree BETWEEN 0 AND 12);

ALTER TABLE sc_segment_metadata
ADD CONSTRAINT cc_sc_seg_granularity
CHECK (granularity IN ('AUTO', 'DEFAULT', 'GLOBAL', 'GLOBAL AND PARTITION', 'PARTITION', 'SUBPARTITION'));

ALTER TABLE sc_segment_metadata
ADD CONSTRAINT cc_sc_seg_cascade
CHECK (cascade IN ('TRUE','FALSE'));

ALTER TABLE sc_segment_metadata
ADD CONSTRAINT cc_sc_seg_no_invalidate
CHECK (no_invalidate IN ('TRUE','FALSE'));

ALTER TABLE sc_segment_metadata
ADD CONSTRAINT cc_sc_seg_stattype
CHECK (stattype IN ('DATA'));

ALTER TABLE sc_segment_metadata
ADD CONSTRAINT cc_sc_seg_force
CHECK (force IN ('TRUE','FALSE'));

ALTER TABLE sc_segment_metadata
ADD CONSTRAINT cc_sc_segment_priority
CHECK (segment_priority BETWEEN 1 AND 9);

ALTER TABLE sc_segment_metadata
ADD CONSTRAINT cc_sc_seg_metadata_flag
CHECK (active_flag IN ('A','I'));

CREATE TABLE sc_event_log (
instance_id      NUMBER,
run_no           NUMBER,
program_name     VARCHAR2(30),
line_number      NUMBER,
beg_date         DATE,
end_date         DATE,
host_name        VARCHAR2(64),
instance_name    VARCHAR2(16),
active_instances NUMBER(3,0),
sql_errno        VARCHAR2(6),
event_text       VARCHAR2(256),
log_comment      VARCHAR2(256))
PARTITION BY RANGE (instance_id) INTERVAL (1) (
PARTITION "ROOT_PAR" VALUES LESS THAN (2) PCTFREE 0 PCTUSED 99);
Then the stats collection package header
CREATE OR REPLACE PACKAGE stats_collection AUTHID CURRENT_USER IS
/*******************************************************************************
* NAME: stats_collection                                                       *
* TYPE: Package Header                                                         *
*                                                                              *
* PURPOSE: Metadata driven table and partition statistics collection with      *
* associated logging and instrumentation                                       *
*                                                                              *
* Revision History:                                                            *
/*******************************************************************************
* Date Author Comments                                                         *
* 01-AUG-2011 DA Morgan Initial Release                                        *
/******************************************************************************/

TYPE event_array IS RECORD (
 instance_id      sc_event_log.instance_id%TYPE,
 run_no           sc_event_log.run_no%TYPE,
 program_name     sc_event_log.program_name%TYPE,
 line_number      sc_event_log.line_number%TYPE,
 beg_date         sc_event_log.beg_date%TYPE,
 end_date         sc_event_log.end_date%TYPE,
 host_name        sc_event_log.host_name%TYPE,
 instance_name    sc_event_log.instance_name%TYPE,
 active_instances sc_event_log.active_instances%TYPE,
 sql_errno        sc_event_log.sql_errno%TYPE,
 event_text       sc_event_log.event_text%TYPE,
 log_comment      sc_event_log.log_comment%TYPE);

 i       NUMBER;
 NumRecs INTEGER;
 eArray  event_array;
 mArray  mlib_metrics.metrics_array;

 AbortExecution     EXCEPTION;
 InstanceNotRunning EXCEPTION;
 InvalidObj         EXCEPTION;
 InvalidMethodOpt   EXCEPTION;
 InvalidSize        EXCEPTION;
 InvalidUpdate      EXCEPTION;

 FUNCTION  get_version RETURN VARCHAR2;
 PROCEDURE collect_table_stats(pInstID IN NUMBER);
 PROCEDURE purge_stats_jobs;
 PROCEDURE insert_log_event(pEArray IN event_array);
 PROCEDURE update_log_event(pEArray IN event_array);
 PROCEDURE log_metrics(pMArray IN mlib_metrics.metrics_array);
END stats_collection;
/
sho err
Then the stats collection package body
CREATE OR REPLACE PACKAGE BODY stats_collection IS
/*******************************************************************************
* NAME: stats_collection                                                       *
* TYPE: Package Header                                                         *
*                                                                              *
* PURPOSE: Metadata driven table and partition statistics collection with      *
* associated logging and instrumentation as well as an API for basic           *
* DBMS_STATS package functionality                                             *
*                                                                              *
* Revision History:                                                            *
********************************************************************************
* Date Author Comments                                                         *
* 21-FEB-2011 DA Morgan Version 1 Initial release                              *
* 30-MAR-2011 DA Morgan Version 2 Initial release                              *
* 12-AUG-2011 DA Morgan Version 3 Initial release                              *
*******************************************************************************/

FUNCTION get_version RETURN VARCHAR2 IS
BEGIN
RETURN '3.0.1';
END get_version;
--------------------------------------------------------------------------------
PROCEDURE collect_table_stats(pInstID IN NUMBER) IS
sysRefCur SYS_REFCURSOR;
sdmrec sc_segment_metadata%ROWTYPE;

CURSOR simcur IS
SELECT /* stats_collection.collect_table_stats1 */ som.schema_name, som.where_clause
FROM sc_schema_metadata som
WHERE som.inst_id = pInstID
AND som.active_flag = 'A'
ORDER BY som.schema_priority, som.schema_name;

vErrMesg   sc_metrics_log.failure_desc%TYPE;
vLADate    dba_tables.last_analyzed%TYPE;
vMetricsAr sc_metrics_log%ROWTYPE;
-------------------------------------------------------------- private procedure
PROCEDURE child(
 p2InstID     IN  sc_schema_metadata.inst_id%TYPE,
 pSchemaName  IN  sc_schema_metadata.schema_name%TYPE,
 pWhereClause IN  sc_schema_metadata.where_clause%TYPE,
 pRetCur      OUT SYS_REFCURSOR,
 pNumRecs     OUT INTEGER)
IS
 sqlStr VARCHAR2(2048);
BEGIN
  sqlStr := 'SELECT COUNT(*) FROM sc_segment_metadata ' ||
            ' ' || pWhereClause || ' ' ||
            ' AND inst_id = :B1 ' ||
            ' AND schema_name = :B2 ' ||
            ' AND active_flag = ''A'' ';

  execute immediate sqlStr
  INTO pNumRecs
  USING p2InstID, pSchemaName;

OPEN pRetCur FOR 'SELECT * FROM sc_segment_metadata ' ||
' ' || pWhereClause || ' ' ||
' AND schema_name = ''' || pSchemaName || ''' ' ||
' AND inst_id = ' || p2InstID || ' ' ||
' AND active_flag = ''A'' ';
END child;
-------------------------------------------------------- private inline function
FUNCTION to_boolean(pString IN VARCHAR2) RETURN BOOLEAN DETERMINISTIC IS
BEGIN
IF pString = 'TRUE' THEN
RETURN TRUE;
ELSIF pString = 'FALSE' THEN
RETURN FALSE;
ELSE
RETURN FALSE;
END IF;
END to_boolean;
-------------------------------------------------------- private inline function
/*
need to explain explicit assumptions with respect to partition naming
*/

FUNCTION go_nogo(pSegName IN VARCHAR2, pDateVal IN LONG) RETURN BOOLEAN DETERMINISTIC IS
 vAnalyze BOOLEAN := TRUE;
 vDateVal DATE;
BEGIN
  -- convert long to date
  vDateVal := TO_DATE(SUBSTR(TO_CHAR(pDateVal),11,19), 'SYYYY-MM-DD HH24:MI:SS');

  IF pSegName LIKE '%MO' THEN
    IF vDateVal < SYSDATE-365 THEN -- ignore partitions more than 365 days old
      vAnalyze := FALSE;
    END IF;
  ELSIF pSegName LIKE '%WK' THEN
    IF vDateVal < SYSDATE-21 THEN -- ignore partitions more than 21 days old
      vAnalyze := FALSE;
    END IF;
  ELSIF pSegName LIKE '%DY' THEN
    IF vDateVal < SYSDATE-14 THEN -- ignore partitions more than 14 days old
      vAnalyze := FALSE;
    END IF;
  ELSE
    IF vDateVal < SYSDATE-14 THEN -- ignore partitions more than 14 days old
      vAnalyze := FALSE;
    END IF;
  END IF;
  RETURN vAnalyze;
EXCEPTION
  WHEN OTHERS THEN -- if not a date range partition analyze always
    RETURN TRUE;
END go_nogo;
--------------------------------------------------------------------------------
BEGIN
  -- if already running on the instance do not run
  SELECT COUNT(*)
  INTO i
  FROM gv$session gs
  WHERE gs.client_info = 'SC' || TO_CHAR(pInstID);

  IF i <> 0 THEN
    RAISE AbortExecution;
  ELSE
    mlib_utils.set_clientinfo('SC' || TO_CHAR(pInstID));
    mlib_utils.set_module('stats_collection.load_metadata_tables', 'log start');
    earray.run_no := sc_run_seq.NEXTVAL;
    earray.program_name := 'COLLECT_TABLE_STATS' || TO_CHAR(pInstID);
    earray.beg_date := SYSDATE;
    insert_log_event(eArray);

    marray.run_no := earray.run_no;
  END IF;

  -- purge both logs of obsolete rows
  mlib_utils.set_module('stats_collection.load_metadata_tables', 'Purge Events Log');

  DELETE /* stats_collection.collect_table_stats2 */ sc_event_log WHERE end_date < TRUNC(SYSDATE-90);

  mlib_utils.set_module('stats_collection.load_metadata_tables', 'Purge Metrics Log');

  DELETE /* stats_collection.collect_table_stats3 */ sc_metrics_log WHERE end_run < TRUNC(SYSDATE-30);
  COMMIT;

  mlib_utils.set_module('stats_collection.load_metadata_tables', 'Purge Completed');

  FOR simrec IN simcur LOOP
    child(pInstID, simrec.schema_name, simrec.where_clause, sysRefCur, NumRecs);

    FOR i IN 1..NumRecs LOOP
      FETCH sysRefCur INTO sdmrec;
      -- start table-level metrics collection
      mArray.schema_name := simrec.schema_name;
      mArray.seg_name := sdmrec.table_name;
      mArray.beg_run := SYSTIMESTAMP;
      mArray.cpu_time := mlib_metrics.get_CPU;
      mArray.phys_reads := mlib_metrics.get_PhysRead_Bytes;
      mArray.blocks_changed := mlib_metrics.get_BlockChanges;
      mArray.outcome := 'S';

      IF sdmrec.granularity = 'PARTITION' THEN
        -- process partitions segments
        DECLARE
         CURSOR acur is
         SELECT /* stats_collection.launch_collection2 */
                dtp.partition_name, dtp.high_value
         FROM dba_tab_partitions dtp
         WHERE dtp.table_owner = sdmrec.schema_name
         AND dtp.table_name = sdmrec.table_name
         AND ((dtp.last_analyzed IS NULL) OR (dtp.last_analyzed < SYSDATE-12/24))
         ORDER BY partition_name;
        BEGIN
          -- loop through qualified partitions
          FOR arec IN acur LOOP
            mlib_utils.set_module(sdmrec.schema_name, sdmrec.table_name || ':' ||
                                  arec.partition_name);
            PRAGMA INLINE(GO_NOGO, 'YES');
            IF go_nogo(arec.partition_name, arec.high_value) THEN
              -- process qualified partitions
              PRAGMA INLINE(TO_BOOLEAN, 'YES');
              dbms_stats.gather_table_stats(sdmrec.schema_name, sdmrec.table_name,
              arec.partition_name, sdmrec.est_pct, TO_BOOLEAN(sdmrec.block_sample),
              sdmrec.method_opt, sdmrec.degree, sdmrec.granularity,
              TO_BOOLEAN(sdmrec.cascade), NULL, NULL, NULL, TO_BOOLEAN(sdmrec.no_invalidate),
              sdmrec.stattype, TO_BOOLEAN(sdmrec.force));

              dbms_stats.lock_partition_stats(sdmrec.schema_name, sdmrec.table_name,
              arec.partition_name);
            END IF;
          END LOOP;
        EXCEPTION
          WHEN OTHERS THEN
            mArray.failure_desc := 'PT: ' || sdmrec.table_name;
            mArray.outcome := 'F'; -- record the failure and move to the next table
        END;
      ELSIF sdmrec.granularity = 'GLOBAL' THEN
        -- process non-partitioned segments
        BEGIN
          mlib_utils.set_module(sdmrec.schema_name, sdmrec.table_name);

          SELECT /* stats_collection.launch_collection3 */ dt.last_analyzed
          INTO vLADate
          FROM dba_tables dt
          WHERE dt.owner = sdmrec.schema_name
          AND dt.table_name = sdmrec.table_name;

          IF (vLADate IS NULL) OR vLADate < SYSDATE-12/24 THEN
            -- process the table and lock table stats
            PRAGMA INLINE(TO_BOOLEAN, 'YES');
            dbms_stats.gather_table_stats(sdmrec.schema_name, sdmrec.table_name, NULL,
            sdmrec.est_pct, TO_BOOLEAN(sdmrec.block_sample), sdmrec.method_opt, sdmrec.degree,
            sdmrec.granularity, TO_BOOLEAN(sdmrec.cascade), NULL, NULL, NULL,
            TO_BOOLEAN(sdmrec.no_invalidate), sdmrec.stattype, TO_BOOLEAN(sdmrec.force));
            dbms_stats.lock_table_stats(sdmrec.schema_name, sdmrec.table_name);
          END IF;
        EXCEPTION
          WHEN OTHERS THEN
            mArray.failure_desc := 'HT: ' || sdmrec.table_name || ' - ' || SQLERRM;
            mArray.outcome := 'F'; -- record the failure and move to the next table
        END;
      ELSE -- trap for a granularity that the program is not designed to handle
        mArray.failure_desc := 'Unknown Granularity: ' || sdmrec.granularity;
        mArray.outcome := 'F'; -- record the failure and move to the next table
      END IF;

      -- record table-level metrics and loop for next table
      mArray.cpu_time := (mlib_metrics.get_CPU - mArray.cpu_time);
      mArray.phys_reads := (mlib_metrics.get_PhysRead_Bytes - mArray.phys_reads);
      mArray.blocks_changed := (mlib_metrics.get_BlockChanges - mArray.blocks_changed);
      log_metrics(mArray);
    END LOOP;
    -- record owner-level events
    earray.end_date := SYSDATE;
    earray.sql_errno := 0;
    earray.event_text := 'stats_collection.collect_table_stats';
    earray.log_comment := 'Stats Collection Success';
    update_log_event(eArray);
  END LOOP;

  mlib_utils.reset_module;
  mlib_utils.reset_clientinfo;
EXCEPTION
  WHEN AbortExecution THEN
    mlib_utils.reset_module;
    mlib_utils.reset_clientinfo;
    RAISE_APPLICATION_ERROR(-20001, 'Statstics Collection Is Already Running On This Node');
  WHEN OTHERS THEN
    vErrMesg := SUBSTR(SQLERRM,1,256);
    mArray.cpu_time := (mlib_metrics.get_CPU - mArray.cpu_time);
    mArray.phys_reads := (mlib_metrics.get_PhysRead_Bytes - mArray.phys_reads);
    mArray.blocks_changed := (mlib_metrics.get_BlockChanges - mArray.blocks_changed);
    mArray.failure_desc := vErrMesg;
    mArray.outcome := 'F';
    log_metrics(mArray);

    mlib_utils.reset_module;
    mlib_utils.reset_clientinfo;
END collect_table_stats;
--------------------------------------------------------------------------------
PROCEDURE purge_stats_jobs IS
/*******************************************************************************
* Procedure PURGE_STATS_JOBS business rules                                    *
* Loop through all jobs with the prefix "SCJOB" and drop them with the FORCE   *
* parameter set to TRUE                                                        *
*******************************************************************************/

 CURSOR sjcur IS
 SELECT dsj.job_name
 FROM dba_scheduler_jobs dsj
 WHERE dsj.job_name LIKE 'SCZZYZX%';
BEGIN
  FOR sjrec IN sjcur LOOP
    dbms_scheduler.drop_job(sjrec.job_name, TRUE);
  END LOOP;
END purge_stats_jobs;
--------------------------------------------------------------------------------
PROCEDURE insert_log_event(pEArray IN event_array) IS
/*******************************************************************************
* Procedure INSERT_LOG_EVENT business rules                                    *
* 1. if any array elements are null provide the missing values                 *
* 2. provide missing values and insert the results into the event log table    *
*******************************************************************************/

 leArray event_array;
 vInstCount  sc_event_log.active_instances%TYPE;
 vInstTab    dbms_utility.instance_table;
 vLineNumber all_source.line%TYPE;
 vObjType    all_objects.object_type%TYPE;
 vPkgName    all_objects.object_name%TYPE;
 vSchemaName all_objects.owner%TYPE;

 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  owa_util.who_called_me(vSchemaName, vPkgName, vLineNumber, vObjType);
  IF dbms_utility.is_cluster_database THEN
    dbms_utility.active_instances(vInstTab, vInstCount);
  END IF;

  leArray.instance_id := COALESCE(pEArray.instance_id, sys_context('USERENV', 'INSTANCE'));
  leArray.run_no := pEArray.run_no;
  leArray.program_name := pEArray.program_name;
  leArray.line_number := vLineNumber;
  leArray.beg_date := COALESCE(pEArray.beg_date, SYSDATE);
  leArray.end_date := COALESCE(pEArray.end_date, SYSDATE);
  leArray.host_name := sys_context('USERENV', 'SERVER_HOST');
  leArray.instance_name := sys_context('USERENV', 'INSTANCE_NAME');
  leArray.active_instances := vInstCount;
  leArray.sql_errno := pEArray.sql_errno;
  leArray.event_text := pEArray.event_text;
  leArray.log_comment := pEArray.log_comment;

  INSERT /* stats_collection.INSERT_LOG_EVENT */
  INTO sc_event_log
  VALUES leArray;
  COMMIT;
END insert_log_event;
--------------------------------------------------------------------------------
PROCEDURE update_log_event(pEArray IN event_array) IS
/*******************************************************************************
* Procedure UPDATE_LOG_EVENT business rules                                    *
* 1. if any array elements are null provide the missing values                 *
* 2. insert the results into the event log table                               *
*******************************************************************************/

 leArray event_array;
 vInstCount  sc_event_log.active_instances%TYPE;
 vInstTab    dbms_utility.instance_table;
 vLineNumber all_source.line%TYPE;
 vObjType    all_objects.object_type%TYPE;
 vPkgName    all_objects.object_name%TYPE;
 vSchemaName all_objects.owner%TYPE;

 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  owa_util.who_called_me(vSchemaName, vPkgName, vLineNumber, vObjType);

  UPDATE /* stats_collection.UPDATE_LOG_EVENT */ sc_event_log
  SET end_date = COALESCE(pEArray.end_date,SYSDATE),
      line_number = vLineNumber,
      sql_errno = pEArray.sql_errno,
      event_text = pEArray.event_text,
      log_comment = pEArray.log_comment
  WHERE run_no = pEArray.run_no;
  COMMIT;
END update_log_event;
--------------------------------------------------------------------------------
PROCEDURE log_metrics(pMArray IN mlib_metrics.metrics_array) IS
/*******************************************************************************
* Procedure LOG_EVENT business rules                                           *
* 1. if any array elements are null provide the missing values                 *
* 2. calculate metric value deltas and insert the results into the metrics log *
*    table                                                                     *
*******************************************************************************/

 lmArray mlib_metrics.metrics_array;

 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  lmArray.instance_id := COALESCE(mArray.instance_id, sys_context('USERENV', 'INSTANCE'));
  lmArray.run_no := COALESCE(mArray.run_no, sc_run_seq.NEXTVAL);
  lmArray.service_name := sys_context('USERENV', 'SERVICE_NAME');
  lmArray.schema_name := mArray.schema_name;
  lmArray.seg_name := mArray.seg_name;
  lmArray.beg_run := COALESCE(mArray.beg_run, SYSTIMESTAMP);
  lmArray.end_run := COALESCE(mArray.end_run, SYSTIMESTAMP);
  lmArray.cpu_time := mlib_metrics.get_CPU - COALESCE(mArray.cpu_time, mlib_metrics.get_CPU);
  lmArray.phys_reads := mlib_metrics.get_PhysRead_Bytes - COALESCE(mArray.phys_reads,  mlib_metrics.get_PhysRead_Bytes);
  lmArray.blocks_changed := mlib_metrics.get_BlockChanges - COALESCE(mArray.blocks_changed, mlib_metrics.get_BlockChanges);
  lmArray.failure_desc := marray.failure_desc;
  lmArray.outcome := COALESCE(mArray.outcome,'F');

  INSERT /* stats_collection.LOG_METRICS */
  INTO sc_metrics_log
  VALUES lmArray;
  COMMIT;
END log_metrics;
--------------------------------------------------------------------------------
PROCEDURE add_sc_schema(
 pSchema  IN dba_users.username%TYPE,
 pInstID  IN sc_schema_metadata.inst_id%TYPE,
 pWClause IN sc_schema_metadata.where_clause%TYPE DEFAULT 'WHERE 1=1')
IS
 eiStr          VARCHAR2(4000);
BEGIN
  -- if any stats collection jobs are running do not run
  SELECT COUNT(*)
  INTO i
  FROM gv$session
  WHERE client_info LIKE 'SC%';

  IF i <> 0 THEN
    RAISE AbortExecution;
  ELSE
    mlib_utils.set_clientinfo('SC_ADD');
    mlib_utils.set_module('stats_collection.add_sc_schema', 'adding schema');
  END IF;

  INSERT INTO sc_schema_metadata
  (schema_name, inst_id, where_clause, schema_priority, active_flag)
  VALUES
  (pSchema, pInstID, pWClause, 9, 'A');

  INSERT /*+ APPEND */ INTO sc_segment_metadata
  SELECT /* stats_collection.load_metadata_tables16 */
        DISTINCT dtp.table_owner, sim.inst_id, dtp.table_name, 1,
        'TRUE', 'FOR ALL INDEXED COLUMNS SIZE 1', 1, 'PARTITION',
        'TRUE', 'TRUE', 'DATA', 'TRUE', 9, 'A'
  FROM dba_tab_partitions dtp, sc_schema_metadata sim
  WHERE dtp.table_owner = sim.schema_name
  AND dtp.table_name NOT LIKE 'ERR%'
  AND dtp.table_name NOT LIKE 'EXT%'
  AND dtp.table_name NOT LIKE 'TMP%'
  AND dtp.table_name NOT LIKE 'UPGRADE';

  INSERT /*+ APPEND */ INTO sc_segment_metadata
  SELECT /* stats_collection.load_metadata_tables17 */ DISTINCT dt.owner, sim.inst_id,
  dt.table_name, 1, 'TRUE', 'FOR ALL INDEXED COLUMNS SIZE 1', 1, 'GLOBAL', 'TRUE', 'TRUE',
  'DATA', 'TRUE', 9, 'A'
  FROM dba_tables dt, sc_schema_metadata sim
  WHERE dt.owner = sim.schema_name
  AND (owner, table_name) NOT IN (
    SELECT schema_name, table_name
    FROM sc_segment_metadata)
  AND dt.table_name NOT LIKE 'ERR%'
  AND dt.table_name NOT LIKE 'EXT%'
  AND dt.table_name NOT LIKE 'TMP%'
  AND dt.table_name NOT LIKE 'UPGRADE';
  COMMIT;

  mlib_utils.reset_module;
  mlib_utils.reset_clientinfo;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END add_sc_schema;
--------------------------------------------------------------------------------
PROCEDURE drop_sc_schema(pSchema IN dba_users.username%TYPE) IS
BEGIN
  -- if any stats collection or load jobs are running do not run
  SELECT COUNT(*)
  INTO i
  FROM gv$session
  WHERE client_info LIKE 'SC%';

  IF i <> 0 THEN
    RAISE AbortExecution;
  ELSE
    earray.beg_date := SYSDATE;
    mlib_utils.set_clientinfo('SC_DROP');
    mlib_utils.set_module('stats_collection.drop_sc_schema', 'dropping schema');
  END IF;

  DELETE sc_segment_metadata WHERE schema_name = pSchema;
  DELETE sc_schema_metadata WHERE schema_name = pSchema;
  COMMIT;

  mlib_utils.reset_module;
  mlib_utils.reset_clientinfo;
EXCEPTION
  WHEN OTHERS THEN
    earray.sql_errno := sqlcode;
    earray.event_text := 'stats_collection.collect_table_stats';
    earray.end_date := SYSDATE;
    earray.log_comment := 'When Others Then Capture';
    update_log_event(eArray);

    mlib_utils.reset_module;
    mlib_utils.reset_clientinfo;
END drop_sc_schema;
--------------------------------------------------------------------------------
END stats_collection;
/
sho err
The next step is to create a trigger to enforce data integrity not possible using a constraint
CREATE OR REPLACE TRIGGER biu_sc_segment_metadata
BEFORE INSERT OR UPDATE
ON sc_segment_metadata
FOR EACH ROW
DECLARE
 i        NATURAL;
 vSizeNum NUMBER;
 earray   stats_collection.event_array;

-- AbortExecution     EXCEPTION;
-- InstanceNotRunning EXCEPTION;
 InvalidObj         EXCEPTION;
 InvalidMethodOpt   EXCEPTION;
 InvalidSize        EXCEPTION;
 InvalidUpdate      EXCEPTION;
BEGIN
  IF INSERTING THEN
    -- enforce that schema_name+table_name combo integrity
    :NEW.table_name := UPPER(:NEW.table_name);

    SELECT /* BIU_SC_SEGMENT_METADATA */ COUNT(*)
    INTO i
    FROM dba_tables
    WHERE owner = :NEW.schema_name
    AND table_name = :NEW.table_name;

    IF i <> 1 THEN
      RAISE InvalidObj;
    END IF;
  ELSE
    IF :NEW.schema_name <> :OLD.schema_name THEN
      RAISE InvalidUpdate;
    END IF;

    IF :NEW.table_name <> :OLD.table_name THEN
      RAISE InvalidUpdate;
    END IF;
  END IF;

  -- enforce some method_opt integrity
  :NEW.method_opt := UPPER(:NEW.method_opt);
  IF :NEW.method_opt IN ('FOR ALL COLUMNS SIZE AUTO',
    'FOR ALL COLUMNS SIZE REPEAT',
    'FOR ALL COLUMNS SIZE SKEWONLY',
    'FOR ALL COLUMNS SKEWONLY SIZE AUTO',
    'FOR ALL INDEXED COLUMNS',
    'FOR ALL INDEXED COLUMNS SIZE AUTO') THEN
      NULL;
  ELSE
    -- perform size check
    IF SUBSTR(:NEW.method_opt,1,21) = 'FOR ALL COLUMNS SIZE ' THEN
      NULL;
    ELSIF SUBSTR(:NEW.method_opt,1,28) = 'FOR ALL HIDDEN COLUMNS SIZE ' THEN
      NULL;
    ELSIF SUBSTR(:NEW.method_opt,1,29) = 'FOR ALL INDEXED COLUMNS SIZE ' THEN
      NULL;
    ELSE
      RAISE InvalidMethodOpt;
    END IF;
    -- eval size parameter
   
vSizeNum := TO_NUMBER(TRIM(TRANSLATE(SUBSTR(:NEW.method_opt,INSTR(:NEW.method_opt,'SIZE', 1, 1)+5), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ ',' ')));

    IF vSizeNum NOT BETWEEN 1 AND 256 THEN
      RAISE InvalidSize;
    END IF;
  END IF;
EXCEPTION
  WHEN InvalidObj THEN
    earray.sql_errno := SQLCODE;
    earray.event_text := SQLERRM;
    earray.log_comment := 'InvalidObj Exception - ' ||:NEW.schema_name||':'||:NEW.table_name;
    stats_collection.insert_log_event(earray);
  WHEN InvalidMethodOpt THEN
    earray.sql_errno := SQLCODE;
    earray.event_text := SQLERRM;
    earray.log_comment := 'InvalidMethodOpt Exception - ' || :NEW.schema_name || ':' || :NEW.table_name;
    stats_collection.insert_log_event(earray);
  WHEN InvalidSize THEN
    earray.sql_errno := SQLCODE;
    earray.event_text := SQLERRM;
    earray.log_comment := 'InvalidSize Exception - ' || :NEW.schema_name || ':' || :NEW.table_name;
    stats_collection.insert_log_event(earray);
  WHEN InvalidUpdate THEN
    earray.sql_errno := 0;
    earray.event_text := 'Schema and Table Names Can Not Be Altered';
    earray.log_comment := 'InvalidUpdate Exception - ' || :NEW.schema_name || ':' || :NEW.table_name;
    stats_collection.insert_log_event(earray);
  WHEN OTHERS THEN
    earray.sql_errno := SQLCODE;
    earray.event_text := SQLERRM;
    earray.log_comment := 'WHEN OTHERS Exception - ' || :NEW.schema_name || ':' || :NEW.table_name;
    stats_collection.insert_log_event(earray);
END biu_sc_segment;
/
sho err
And finally the job classes and scheduler jobs to run the stats collection procedure. If you have stand-alone then you need only create job SC_NODE1 though it would be better if you created the job class as well to become more familiar with "best practice."
... CREATE JOB CLASS(es)

BEGIN
  sys.dbms_scheduler.create_job(
  job_name        => 'SC_NODE1',
  start_date      => TRUNC(SYSDATE),
  repeat_interval => 'FREQ=DAILY; BYHOUR=3;BYMINUTE=0;BYSECOND=0',
  end_date        => NULL,
  job_class       => 'JC_INST1',
  job_type        => 'PLSQL_BLOCK',
  job_action      => 'BEGIN sc_pkg.collect_table_stats(1); END; ',
  comments        => 'CBO statistics collection for Node 1');

  sys.dbms_scheduler.create_job(
  job_name        => 'SC_NODE2',
  start_date      => TRUNC(SYSDATE),
  repeat_interval => 'FREQ=DAILY; BYHOUR=3;BYMINUTE=1;BYSECOND=0',
  end_date        => NULL,
  job_class       => 'JC_INST2',
  job_type        => 'PLSQL_BLOCK',
  job_action      => 'BEGIN sc_pkg.collect_table_stats(2); END; ',
  comments        => 'CBO statistics collection for Node 2');

  sys.dbms_scheduler.create_job(
  job_name        => 'SC_NODE3',
  start_date      => TRUNC(SYSDATE),
  repeat_interval => 'FREQ=DAILY; BYHOUR=3;BYMINUTE=2;BYSECOND=0',
  end_date        => NULL,
  job_class       => 'JC_INST3',
  job_type        => 'PLSQL_BLOCK',
  job_action      => 'BEGIN sc_pkg.collect_table_stats(3); END; ',
  comments        => 'CBO statistics collection for Node 3');

  sys.dbms_scheduler.create_job(
  job_name        => 'SC_NODE4',
  start_date      => TRUNC(SYSDATE),
  repeat_interval => 'FREQ=DAILY; BYHOUR=3;BYMINUTE=3;BYSECOND=0',
  end_date        => NULL,
  job_class       => 'JC_INST4',
  job_type        => 'PLSQL_BLOCK',
  comments        => 'CBO statistics collection for Node 4');

  sys.dbms_scheduler.set_attribute('SC_NODE1', 'job_priority', '1');
  sys.dbms_scheduler.set_attribute('SC_NODE2', 'job_priority', '1');
  sys.dbms_scheduler.set_attribute('SC_NODE3', 'job_priority', '1');
  sys.dbms_scheduler.set_attribute('SC_NODE4', 'job_priority', '1');

  sys.dbms_scheduler.enable('SC_NODE1');
  sys.dbms_scheduler.enable('SC_NODE2');
  sys.dbms_scheduler.enable('SC_NODE3');
  sys.dbms_scheduler.enable('SC_NODE4');

  sys.dbms_scheduler.run_job('SC_NODE1', FALSE);
  sys.dbms_scheduler.run_job('SC_NODE2', FALSE);
  sys.dbms_scheduler.run_job('SC_NODE3', FALSE);
  sys.dbms_scheduler.run_job('SC_NODE4', FALSE);
END;
/
show errors;
When collecting stats it is important that you understand the difference between how the EST_PCT value affects results. The following is a demo of the affect on two very small tables using different samplings. Note how the values vary from the actual.
SQL> select count(*) from postal_code;

COUNT(*)
----------
42456

SQL> select count(*) from airplanes;

COUNT(*)
----------
250000

-- 1%
SQL> exec stats_collection.COLLECT_TABLE_STATS(1);

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, last_analyzed from user_tables;

-- actual
TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- --------------------
POSTAL_CODE                         44086 19-OCT-2011 19:35:29
AIRPLANES                          283900 19-OCT-2011 19:35:29

-- 10%
TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- --------------------
POSTAL_CODE                         42456 20-OCT-2011 07:42:03
AIRPLANES                          242120 20-OCT-2011 07:42:02

-- 20%
TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- --------------------
POSTAL_CODE                         47847 21-OCT-2011 16:28:48
AIRPLANES                          270655 21-OCT-2011 16:28:48

-- 30%
TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- --------------------
POSTAL_CODE                         39325 22-OCT-2011 20:36:20
AIRPLANES                          249097 22-OCT-2011 20:36:19

-- 40%
TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- --------------------
POSTAL_CODE                         35908 23-OCT-2011 19:21:54
AIRPLANES                          237928 23-OCT-2011 19:21:53

-- 50% next
x
 
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-2013 Daniel A. Morgan All Rights Reserved