Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\db\latest\eval\eval_tables.sql
Rem drv: <create type="tables"/> 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 - <one-line expansion of the name> Rem Rem DESCRIPTION Rem <short description of component this file declares/defines> Rem Rem NOTES Rem <other useful comments, qualifications, etc.> 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;
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de