Rem drv:
Rem
Rem $Header: eval_tables.sql 28-jun-2005.10:39:37 chyu Exp $
Rem
Rem eval_tables.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.
Rem
Rem NAME
Rem eval_tables.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem chyu 06/28/05 - New repmgr header impl
Rem scgrover 02/02/05 - compress indexes,
Rem bkesavan 10/13/04 - Change pk order for mgmt_sql_plan and
Rem mgmt_sql_summary
Rem yfeng 12/06/03 - Use snap_time instead in trigger table
Rem yfeng 09/11/03 - Add mgmt_sql_helper table
Rem yfeng 09/19/02 - Add sql_summary and sql_plan
Rem vchao 08/21/02 - alter mgmt_sql_evaluation, mgmt_sqlproblem_factors
Rem vchao 08/11/02 - Add mgmt_sql_reuse table
Rem yfeng 08/10/02 - Add snap_id to mgmt_sql_evaluation
Rem yfeng 08/06/02 - Replace digest with address and hash_value
Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts
Rem rpinnama 05/15/02 - Created
Rem
rem
rem PURPOSE
rem
rem This table contains the evaluation data for inefficient SQL metrics.
rem Each record represents one detected inefficiency (an 'inefficiency' here
rem is for only one class of problem: a given cursor may suffer from
rem multiple different classes of problems).
rem
rem COLUMNS
rem
rem TARGET_GUID - The unique id of the target
rem
rem COLLECTION_TIMESTAMP - The time of collection of the original metric
rem data.
rem
rem EVAL_TIMESTAMP - The time of evaluation of the statement.
rem
rem DIGEST - A message digest representing the full SQL text with
rem statistical uniqueness.
rem
rem PLAN_ID - An identifier representing each distinct plan used by a shared
rem parent cursor, used to uniquely identify this set of
rem execution statistics as it relates to a particular plan for a
rem particular statement executed by a particular user.
rem
rem REASON_CODE - The reason (class of problem) for the inefficiency.
rem
rem DERIVED_METRIC - A derived metric specific to each problem type.
rem
rem SEVERITY_CODE - The value representing this problem in OEM severity
rem terms (only values of 20 and 25 are expected for real
rem problems).
rem
rem SEVERITY - A numeric measure of how severe this problem is relative
rem to other SQL inefficiency problems.
rem
rem NOTES
rem
CREATE TABLE MGMT_SQL_EVALUATION
(target_guid RAW(16) NOT NULL,
collection_timestamp DATE NOT NULL,
eval_timestamp DATE DEFAULT SYSDATE,
snap_id NUMBER(6) NOT NULL,
address RAW(8) NOT NULL,
hash_value NUMBER NOT NULL,
plan_hash_value NUMBER DEFAULT -1,
reason_code NUMBER DEFAULT -1,
derived_metric NUMBER DEFAULT -1,
severity_code NUMBER DEFAULT 15,
severity NUMBER DEFAULT 15,
eval_type NUMBER NOT NULL,
sql_text VARCHAR2(4000) DEFAULT ' ')
MONITORING;
show error;
rem
rem PURPOSE
rem
rem This table contains selected SQL execution statistics for the problem
rem SQL bind variables metric.
rem
rem COLUMNS
rem
rem TARGET_GUID - The unique id of the target
rem
rem METRIC_NAME - The name of the metric that collected this data.
rem
rem COLLECTION_TIMESTAMP - The time of collection of the statistics.
rem
rem LOAD_TIMESTAMP - The time this metric data was loaded into the
rem repository (this table).
rem
rem DIGEST - A message digest representing the full SQL text with
rem statistical uniqueness.
rem
rem APPLICATION - The application issuing this statement.
rem
rem MODULE - The module setting for this statement (as specified via
rem dbms_application_info).
rem
rem NORMALIZED_DIGEST - A message digest representing the normalized full
rem SQL text (literals, bind variables, whitespace
rem adjusted) with statistical uniqueness.
rem
rem SHARABLE_MEM - The amount of sharable memory required by this cursor.
rem
rem PERSISTENT_MEM - The amount of persistent memory required by this
rem cursor.
rem
rem RUNTIME_MEM - The size of the ephemeral frame required by this cursor.
rem
rem LOADED_VERSIONS - 1 if context heap was loaded, 0 otherwise.
rem
rem EXECUTIONS - The number of executions.
rem
rem LOADS - The number of times this statement was loaded or reloaded.
rem
rem INVALIDATIONS - The number of times this child cursor has been
rem invalidated.
rem
rem PARSE_CALLS - The number of parse calls per execution.
rem
rem PARSING_USER - The user who executed this statement.
rem
rem KEPT_VERSIONS - Indicates whether this child cursor has been marked to
rem be kept pinned in cache using the DBMS_SHARED_POOL
rem package.
rem
rem HASH_VALUE - The hash value of the parent statement in the library cache.
rem
rem ACTION - The name of the action that was executing at the time the
rem statement was first parsed (as specified via
rem dbms_application_info.
rem
rem
rem NOTES
rem
rem For now we are including all columns from the 9i V$SQL table in this
rem table. We may remove columns if we determine that they are not needed
rem for any of the inefficient SQL metric data evaluations.
rem
CREATE TABLE MGMT_SQL_BIND_VARS
(target_guid RAW(16) NOT NULL,
metric_name VARCHAR2(64) NOT NULL,
collection_timestamp DATE NOT NULL,
load_timestamp DATE DEFAULT SYSDATE,
digest RAW(20) NOT NULL,
application VARCHAR2(256) DEFAULT NULL,
module VARCHAR2(256) DEFAULT NULL,
normalized_digest RAW(20) DEFAULT NULL,
sharable_mem NUMBER DEFAULT 0,
persistent_mem NUMBER DEFAULT 0,
runtime_mem NUMBER DEFAULT 0,
loaded_versions NUMBER DEFAULT 0,
executions NUMBER DEFAULT 0,
loads NUMBER DEFAULT 0,
invalidations NUMBER DEFAULT 0,
parse_calls NUMBER DEFAULT 0,
parsing_user VARCHAR2(30) DEFAULT ' ',
kept_versions NUMBER DEFAULT 0,
hash_value NUMBER DEFAULT 0,
action VARCHAR2(64) DEFAULT NULL)
MONITORING;
rem
rem PURPOSE
rem
rem This table contains a list of factors which contributed to the
rem identification of a given problem recorded in MGMT_SQL_EVALUATION.
rem These 'problem factors' are specific to the cursor and do not reflect
rem issues observed in the cursor's plan.
rem
rem COLUMNS
rem
rem TARGET_GUID - The unique id of the target.
rem
rem EVAL_TIMESTAMP - The time of evaluation of the statement.
rem
rem DIGEST - A message digest representing the full SQL text with
rem statistical uniqueness.
rem
rem PLAN_ID - An identifier representing each distinct plan used by a shared
rem parent cursor, used to uniquely identify this set of
rem execution statistics as it relates to a particular plan for a
rem particular statement executed by a particular user.
rem
rem REASON_CODE - The reason (class of problem) for the inefficiency.
rem
rem ATTRIBUTE - The statistic central to this factor.
rem
rem IMPORTANCE - The relative importance of this factor.
rem
rem CONFIDENCE - How certain the evaluation is of the problem.
rem
rem NOTES
rem
CREATE TABLE MGMT_SQLPROBLEM_FACTORS
(target_guid RAW(16) NOT NULL,
eval_timestamp DATE NOT NULL,
address RAW(8) NOT NULL,
hash_value NUMBER NOT NULL,
plan_hash_value NUMBER DEFAULT -1,
reason_code NUMBER NOT NULL,
attribute VARCHAR2(64) NOT NULL,
importance VARCHAR2(64) DEFAULT 'NOTICE',
confidence VARCHAR2(64) DEFAULT 'PROBLEMATIC',
sql_text VARCHAR2(4000) DEFAULT ' ')
MONITORING;
rem
rem PURPOSE
rem
rem This table contains a list of factors which contributed to the
rem identification of a given problem recorded in MGMT_SQL_EVALUATION.
rem These 'problem factors' are specific to issues observed in individual
rem steps of the cursor's plan.
rem
rem COLUMNS
rem
rem TARGET_GUID - The unique id of the target.
rem
rem EVAL_TIMESTAMP - The time of evaluation of the statement.
rem
rem DIGEST - A message digest representing the full SQL text with
rem statistical uniqueness.
rem
rem PLAN_ID - An identifier representing each distinct plan used by a shared
rem parent cursor, used to uniquely identify this set of
rem execution statistics as it relates to a particular plan for a
rem particular statement executed by a particular user.
rem
rem ID - The particular step of the plan contributing to the problem.
rem
rem REASON_CODE - The reason (class of problem) for the inefficiency.
rem
rem ATTRIBUTE - The plan statistic central to this factor.
rem
rem IMPORTANCE - The relative importance of this factor.
rem
rem CONFIDENCE - How certain the evaluation is of the problem.
rem
rem NOTES
rem
CREATE TABLE MGMT_PLANPROBLEM_FACTORS
(target_guid RAW(16) NOT NULL,
eval_timestamp DATE NOT NULL,
address RAW(8) NOT NULL,
hash_value NUMBER NOT NULL,
plan_hash_value NUMBER DEFAULT -1,
id NUMBER NOT NULL,
reason_code NUMBER NOT NULL,
attribute VARCHAR2(64) NOT NULL,
importance VARCHAR2(64) DEFAULT 'NOTICE',
confidence VARCHAR2(64) DEFAULT 'PROBLEMATIC',
sql_text VARCHAR2(4000) DEFAULT ' ')
MONITORING;
rem
rem PURPOSE
rem
rem This table contains selected SQL execution statistics for the problem
rem SQL bind variables metric.
rem
rem COLUMNS
rem
rem TARGET_GUID - The unique id of the target
rem
rem METRIC_NAME - The name of the metric that collected this data.
rem
rem COLLECTION_TIMESTAMP - The time of collection of the statistics.
rem
rem LOAD_TIMESTAMP - The time this metric data was loaded into the
rem repository (this table).
rem
rem SQL_TEXT - The full SQL text
rem
rem APPLICATION - The application issuing this statement.
rem
rem MODULE - The module setting for this statement (as specified via
rem dbms_application_info).
rem
rem NORMALIZED_SQL - the normalized full
rem SQL text (literals, bind variables, whitespace
rem adjusted)
rem
rem SHARABLE_MEM - The amount of sharable memory required by this cursor.
rem
rem
CREATE TABLE MGMT_SQL_REUSE
(target_guid RAW(16) NOT NULL,
metric_name VARCHAR2(64) NOT NULL,
collection_timestamp DATE NOT NULL,
load_timestamp DATE DEFAULT SYSDATE,
sql_text VARCHAR2(4000) DEFAULT NULL,
application VARCHAR2(256) DEFAULT NULL,
module VARCHAR2(256) DEFAULT NULL,
normalized_sql VARCHAR2(4000) DEFAULT NULL,
sharable_mem NUMBER DEFAULT 0
) MONITORING;
/* ------------------------------------------------------------------------- */
rem This table contains statistics on high load SQL statements.
rem Each record represents the execution statistics for a particular
rem explain plan, summed across all children of a particular SQL cursor
rem that share this explain plan. Execution statistics are only stored for
rem SQL cursors that have exceeded one or more thresholds.
rem
rem This table is the functional equivalent of the Statspack
rem STATS$SQL_SUMMARY table. There is; however, a semantic
rem difference between the tables -- STATS$SQL_SUMMARY stores
rem one row per address/hash_value pair. MGMT_SQL_SUMMARY stores
rem one row per address/hash_value/explain_plan triplet. This
rem means the statistics are gathered on a per-plan basis,
rem which is a finer level of granularity which should make it
rem easier to identify the poorly performing plans.
create table MGMT_SQL_SUMMARY
(snap_id number(6) not null
,target_guid raw(16) not null
,collection_timestamp date not null
,sql_text varchar2(64) default null
,piece number not null
,plan_hash_value number not null
,application varchar(256) default null
,module varchar(64) default null
,sharable_mem number default 0
,sorts number default 0
,loaded_versions number default 0
,executions number default 0
,loads number default 0
,invalidations number default 0
,parse_calls number default 0
,disk_reads number default 0
,buffer_gets number default 0
,rows_processed number default 0
,command_type number default 0
,address raw(8) not null
,hash_value number not null
,version_count number default 0
,cpu_time number default 0
,elapsed_time number default 0
,outline_sid number default 0
,outline_category varchar2(64) default null
,persistent_mem number default 0
,runtime_mem number default 0
,optimizer_mode varchar2(25) default null
,optimizer_cost number default 0
,parsing_user varchar2(30) default ' '
,parsing_schema varchar2(30) default null
,action varchar2(64) default null
,literal_hash_value number default 0
,first_load_time varchar2(19) default null
,last_load_time varchar2(19) default null
,constraint MGMT_SQL_SUMMARY_PK primary key
(target_guid, snap_id, address, hash_value, plan_hash_value, piece)
using index (CREATE UNIQUE INDEX MGMT_SQL_SUMMARY_PK ON MGMT_SQL_SUMMARY (target_guid, snap_id, address, hash_value, plan_hash_value, piece) COMPRESS 2)
) storage (freelists 4) initrans 2 monitoring;
/* ------------------------------------------------------------------------- */
rem This table contains the SQL Explain Plan data for all SQL inefficiency
rem metrics that have associated explain plans. Each record represents one
rem row of a particular explain plan for a particular SQL statement that has\
rem exceeded one or more thresholds.
rem
rem This table is the functional equivalent of the Statspack
rem STATS$SQL_PLAN table (and eliminates the need for the
rem STATS$SQL_PLAN_USAGE table).
create table MGMT_SQL_PLAN
(snap_id number(6) not null
,target_guid raw(16) not null
,collection_timestamp date not null
,address raw(8) not null
,hash_value number not null
,operation varchar2(30) default null
,options varchar2(30) default null
,object_node varchar2(128) default null
,object_owner varchar2(30) default null
,object_name varchar2(64) default null
,optimizer varchar2(20) default null
,id number not null
,parent_id number default null
,position number default 0
,cost number default 0
,cardinality number default 0
,bytes number default null
,other_tag varchar2(35) default null
,partition_start varchar2(5) default null
,partition_stop varchar2(5) default null
,partition_id number default 0
,other varchar2(4000) default null
,distribution varchar2(30) default null
,cpu_cost number default 0
,io_cost number default 0
,temp_space number default 0
,plan_hash_value number not null
,object_number number default -1
,depth number default -1
,constraint MGMT_SQL_PLAN_PK primary key
(target_guid, snap_id, address, hash_value, plan_hash_value, id)
using index (CREATE UNIQUE INDEX MGMT_SQL_PLAN_PK ON MGMT_SQL_PLAN (target_guid, snap_id, address, hash_value, plan_hash_value, id) COMPRESS 2)
) storage (freelists 4) initrans 2 monitoring;
CREATE TABLE MGMT_SQL_METRIC_HELPER (
TARGET_GUID RAW(16) NOT NULL,
COLLECTION_TIMESTAMP DATE DEFAULT SYSDATE,
SNAP_TIME DATE NOT NULL,
CONSTRAINT MGMT_SQL_METRIC_HELPER PRIMARY KEY (TARGET_GUID))
MONITORING;