1332 lines
46 KiB
SQL
1332 lines
46 KiB
SQL
@@header
|
|
|
|
set term off
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Create STATSPACK repository with partition table to
|
|
* hold consolidated data from multiple databases.
|
|
* Version : 9.2.x
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 05-Aug-07 Vishal Gupta First Draft
|
|
*/
|
|
set term on
|
|
|
|
|
|
DEFINE tablespace_name=&&tablespace_name
|
|
prompt Using &&tablespace_name tablespace to store Statspack objects
|
|
prompt
|
|
|
|
SET VERIFY OFF
|
|
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
Prompt ... Creating STATS$... tables
|
|
|
|
create table STATS$DATABASE_INSTANCE
|
|
(dbid number not null
|
|
,instance_number number not null
|
|
,startup_time date not null
|
|
,snap_id number (6) not null
|
|
,parallel varchar2(3) not null
|
|
,version varchar2(17) not null
|
|
,db_name varchar2(9) not null
|
|
,instance_name varchar2(16) not null
|
|
,host_name varchar2(64)
|
|
,constraint STATS$DATABASE_INSTANCE_PK primary key
|
|
(dbid, instance_number, startup_time)
|
|
using index tablespace &&tablespace_name
|
|
LOCAL
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$LEVEL_DESCRIPTION
|
|
(snap_level number not null
|
|
,description varchar2(300)
|
|
,constraint STATS$LEVEL_DESCRIPTION_PK primary key (snap_level)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 100k next 100k pctincrease 0)
|
|
) tablespace &&tablespace_name
|
|
storage (initial 100k next 100k pctincrease 0)
|
|
;
|
|
|
|
insert into STATS$LEVEL_DESCRIPTION (snap_level, description)
|
|
values (0, 'This level captures general statistics, including rollback segment, row cache, SGA, system events, background events, session events, system statistics, wait statistics, lock statistics, and Latch information');
|
|
|
|
insert into STATS$LEVEL_DESCRIPTION (snap_level, description)
|
|
values (5, 'This level includes capturing high resource usage SQL Statements, along with all data captured by lower levels');
|
|
|
|
insert into STATS$LEVEL_DESCRIPTION (snap_level, description)
|
|
values (6, 'This level includes capturing SQL plan and SQL plan usage information for high resource usage SQL Statements, along with all data captured by lower levels');
|
|
|
|
insert into STATS$LEVEL_DESCRIPTION (snap_level, description)
|
|
values (7, 'This level captures segment level statistics, including logical and physical reads, row lock, itl and buffer busy waits, along with all data captured by lower levels');
|
|
|
|
insert into STATS$LEVEL_DESCRIPTION (snap_level, description)
|
|
values (10, 'This level includes capturing Child Latch statistics, along with all data captured by lower levels');
|
|
|
|
commit;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$SNAPSHOT
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,snap_time date not null
|
|
,startup_time date not null
|
|
,session_id number not null
|
|
,serial# number
|
|
,snap_level number
|
|
,ucomment varchar2(160)
|
|
,executions_th number
|
|
,parse_calls_th number
|
|
,disk_reads_th number
|
|
,buffer_gets_th number
|
|
,sharable_mem_th number
|
|
,version_count_th number
|
|
,seg_phy_reads_th number not null
|
|
,seg_log_reads_th number not null
|
|
,seg_buff_busy_th number not null
|
|
,seg_rowlock_w_th number not null
|
|
,seg_itl_waits_th number not null
|
|
,seg_cr_bks_sd_th number not null
|
|
,seg_cu_bks_sd_th number not null
|
|
,all_init varchar2(5)
|
|
,constraint STATS$SNAPSHOT_PK primary key (snap_id, dbid, instance_number)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$SNAPSHOT_LVL_FK
|
|
foreign key (snap_level) references STATS$LEVEL_DESCRIPTION
|
|
,constraint STATS$SNAPSHOT_FK foreign key (dbid, instance_number, startup_time)
|
|
references STATS$DATABASE_INSTANCE on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$DB_CACHE_ADVICE
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,id number not null
|
|
,name varchar2(20) not null
|
|
,block_size number not null
|
|
,buffers_for_estimate number not null
|
|
,advice_status varchar2(3)
|
|
,size_for_estimate number
|
|
,size_factor number
|
|
,estd_physical_read_factor number
|
|
,estd_physical_reads number
|
|
,constraint STATS$DB_CACHE_ADVICE_PK primary key
|
|
(snap_id, dbid, instance_number, id, buffers_for_estimate)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$DB_CACHE_ADVICE_FK foreign key
|
|
(snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$FILESTATXS
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,tsname varchar2 (30) not null
|
|
,filename varchar2 (513) not null
|
|
,phyrds number
|
|
,phywrts number
|
|
,singleblkrds number
|
|
,readtim number
|
|
,writetim number
|
|
,singleblkrdtim number
|
|
,phyblkrd number
|
|
,phyblkwrt number
|
|
,wait_count number
|
|
,time number
|
|
,constraint STATS$FILESTATXS_PK primary key
|
|
(snap_id, dbid, instance_number, tsname, filename)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$FILESTATXS_FK foreign key (snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$TEMPSTATXS
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,tsname varchar2(30) not null
|
|
,filename varchar2(513) not null
|
|
,phyrds number
|
|
,phywrts number
|
|
,singleblkrds number
|
|
,readtim number
|
|
,writetim number
|
|
,singleblkrdtim number
|
|
,phyblkrd number
|
|
,phyblkwrt number
|
|
,wait_count number
|
|
,time number
|
|
,constraint STATS$TEMPSTATXS_PK primary key
|
|
(snap_id, dbid, instance_number, tsname, filename)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$TEMPSTATXS_FK foreign key (snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$LATCH
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,name varchar2(64) not null
|
|
,latch# number not null
|
|
,level# number
|
|
,gets number
|
|
,misses number
|
|
,sleeps number
|
|
,immediate_gets number
|
|
,immediate_misses number
|
|
,spin_gets number
|
|
,sleep1 number
|
|
,sleep2 number
|
|
,sleep3 number
|
|
,sleep4 number
|
|
,wait_time number
|
|
,constraint STATS$LATCH_PK primary key
|
|
(snap_id, dbid, instance_number, name)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$LATCH_FK foreign key (snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$LATCH_CHILDREN
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,latch# number not null
|
|
,child# number not null
|
|
,gets number
|
|
,misses number
|
|
,sleeps number
|
|
,immediate_gets number
|
|
,immediate_misses number
|
|
,spin_gets number
|
|
,sleep1 number
|
|
,sleep2 number
|
|
,sleep3 number
|
|
,sleep4 number
|
|
,wait_time number
|
|
,constraint STATS$LATCH_CHILDREN_PK primary key
|
|
(snap_id, dbid, instance_number, latch#, child#)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$LATCH_CHILDREN_FK foreign key
|
|
(snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$LATCH_PARENT
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,latch# number not null
|
|
,level# number not null
|
|
,gets number
|
|
,misses number
|
|
,sleeps number
|
|
,immediate_gets number
|
|
,immediate_misses number
|
|
,spin_gets number
|
|
,sleep1 number
|
|
,sleep2 number
|
|
,sleep3 number
|
|
,sleep4 number
|
|
,wait_time number
|
|
,constraint STATS$LATCH_PARENT_PK primary key
|
|
(snap_id, dbid, instance_number, latch#)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$LATCH_PARENT_FK foreign key
|
|
(snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$LATCH_MISSES_SUMMARY
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,parent_name varchar2(50)
|
|
,where_in_code varchar2(64)
|
|
,nwfail_count number
|
|
,sleep_count number
|
|
,wtr_slp_count number
|
|
,constraint STATS$LATCH_MISSES_SUMMARY_PK primary key
|
|
(snap_id, dbid, instance_number, parent_name, where_in_code)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$LATCH_MISSES_SUMMARY_FK foreign key
|
|
(snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$LIBRARYCACHE
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,namespace varchar2(15) not null
|
|
,gets number
|
|
,gethits number
|
|
,pins number
|
|
,pinhits number
|
|
,reloads number
|
|
,invalidations number
|
|
,dlm_lock_requests number
|
|
,dlm_pin_requests number
|
|
,dlm_pin_releases number
|
|
,dlm_invalidation_requests number
|
|
,dlm_invalidations number
|
|
,constraint STATS$LIBRARYCACHE_PK primary key
|
|
(snap_id, dbid, instance_number, namespace)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$LIBRARYCACHE_FK foreign key
|
|
(snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$BUFFER_POOL_STATISTICS
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,id number not null
|
|
,name varchar2(20)
|
|
,block_size number
|
|
,set_msize number
|
|
,cnum_repl number
|
|
,cnum_write number
|
|
,cnum_set number
|
|
,buf_got number
|
|
,sum_write number
|
|
,sum_scan number
|
|
,free_buffer_wait number
|
|
,write_complete_wait number
|
|
,buffer_busy_wait number
|
|
,free_buffer_inspected number
|
|
,dirty_buffers_inspected number
|
|
,db_block_change number
|
|
,db_block_gets number
|
|
,consistent_gets number
|
|
,physical_reads number
|
|
,physical_writes number
|
|
,constraint STATS$BUFFER_POOL_STATS_PK primary key
|
|
(snap_id, dbid, instance_number, id)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$BUFFER_POOL_STATS_FK foreign key
|
|
(snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$ROLLSTAT
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,usn number not null
|
|
,extents number
|
|
,rssize number
|
|
,writes number
|
|
,xacts number
|
|
,gets number
|
|
,waits number
|
|
,optsize number
|
|
,hwmsize number
|
|
,shrinks number
|
|
,wraps number
|
|
,extends number
|
|
,aveshrink number
|
|
,aveactive number
|
|
,constraint STATS$ROLLSTAT_PK primary key
|
|
(snap_id, dbid, instance_number, usn)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$ROLLSTAT_FK foreign key (snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$ROWCACHE_SUMMARY
|
|
(snap_id number (6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,parameter varchar2 (32)
|
|
,total_usage number
|
|
,usage number
|
|
,gets number
|
|
,getmisses number
|
|
,scans number
|
|
,scanmisses number
|
|
,scancompletes number
|
|
,modifications number
|
|
,flushes number
|
|
,dlm_requests number
|
|
,dlm_conflicts number
|
|
,dlm_releases number
|
|
,constraint STATS$ROWCACHE_SUMMARY_PK primary key
|
|
(snap_id, dbid, instance_number, parameter)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$ROWCACHE_SUMMARY_FK foreign key
|
|
(snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$SGA
|
|
(snap_id number (6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,name varchar2(64) not null
|
|
,value number not null
|
|
,startup_time date
|
|
,parallel varchar2(3)
|
|
,version varchar2(17)
|
|
,constraint STATS$SGA_PK primary key
|
|
(snap_id, dbid, instance_number, name)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$SGA_FK foreign key
|
|
(snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$SGASTAT
|
|
(snap_id number not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,name varchar2(64) not null
|
|
,pool varchar2(11)
|
|
,bytes number
|
|
,constraint STATS$SGASTAT_U unique
|
|
(snap_id, dbid, instance_number, name, pool)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$SGASTAT_FK foreign key
|
|
(snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$SYSSTAT
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,statistic# number not null
|
|
,name varchar2 (64) not null
|
|
,value number
|
|
,constraint STATS$SYSSTAT_PK primary key
|
|
(snap_id, dbid, instance_number, name)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$SYSSTAT_FK foreign key (snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$SESSTAT
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,statistic# number not null
|
|
,value number
|
|
,constraint STATS$SESSTAT_PK primary key
|
|
(snap_id, dbid, instance_number, statistic#)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$SESSTAT_FK foreign key (snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$SYSTEM_EVENT
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,event varchar2(64) not null
|
|
,total_waits number
|
|
,total_timeouts number
|
|
,time_waited_micro number
|
|
,constraint STATS$SYSTEM_EVENT_PK primary key
|
|
(snap_id, dbid, instance_number, event)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$SYSTEM_EVENT_FK foreign key (snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$SESSION_EVENT
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,event varchar2(64) not null
|
|
,total_waits number
|
|
,total_timeouts number
|
|
,time_waited_micro number
|
|
,max_wait number
|
|
,constraint STATS$SESSION_EVENT_PK primary key
|
|
(snap_id, dbid, instance_number, event)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$SESSION_EVENT_FK foreign key
|
|
(snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$BG_EVENT_SUMMARY
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,event varchar2(64) not null
|
|
,total_waits number
|
|
,total_timeouts number
|
|
,time_waited_micro number
|
|
,constraint STATS$BG_EVENT_SUMMARY_PK primary key
|
|
(snap_id, dbid, instance_number, event)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$BG_EVENT_SUMMARY_FK foreign key (snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$WAITSTAT
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,class varchar2(18)
|
|
,wait_count number
|
|
,time number
|
|
,constraint STATS$WAITSTAT_PK primary key
|
|
(snap_id, dbid, instance_number, class)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$WAITSTAT_FK foreign key (snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$ENQUEUE_STAT
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,eq_type varchar2(2) not null
|
|
,total_req# number
|
|
,total_wait# number
|
|
,succ_req# number
|
|
,failed_req# number
|
|
,cum_wait_time number
|
|
,constraint STATS$ENQUEUE_STAT_PK primary key
|
|
(snap_id, dbid, instance_number, eq_type)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$ENQUEUE_STAT_FK foreign key (snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$SQL_SUMMARY
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,text_subset varchar2(31) not null
|
|
,sql_text varchar2(1000)
|
|
,sharable_mem number
|
|
,sorts number
|
|
,module varchar2(64)
|
|
,loaded_versions number
|
|
,fetches number
|
|
,executions number
|
|
,loads number
|
|
,invalidations number
|
|
,parse_calls number
|
|
,disk_reads number
|
|
,buffer_gets number
|
|
,rows_processed number
|
|
,command_type number
|
|
,address raw(8)
|
|
,hash_value number
|
|
,version_count number
|
|
,cpu_time number
|
|
,elapsed_time number
|
|
,outline_sid number
|
|
,outline_category varchar2(64)
|
|
,child_latch number
|
|
,constraint STATS$SQL_SUMMARY_PK primary key
|
|
(snap_id, dbid, instance_number, hash_value, text_subset)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$SQL_SUMMARY_FK foreign key (snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$SQLTEXT
|
|
(hash_value number not null
|
|
,text_subset varchar2(31) not null
|
|
,piece number not null
|
|
,sql_text varchar2(64)
|
|
,address raw(8)
|
|
,command_type number
|
|
,last_snap_id number
|
|
,constraint STATS$SQLTEXT_PK primary key (hash_value, text_subset, piece)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 5m next 5m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$SQL_STATISTICS
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,total_sql number not null
|
|
,total_sql_mem number not null
|
|
,single_use_sql number not null
|
|
,single_use_sql_mem number not null
|
|
,constraint STATS$SQL_STATISTICS_PK primary key
|
|
(snap_id, dbid, instance_number)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$SQL_STATISTICS_FK foreign key
|
|
(snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$RESOURCE_LIMIT
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,resource_name varchar2(30) not null
|
|
,current_utilization number
|
|
,max_utilization number
|
|
,initial_allocation varchar2(10)
|
|
,limit_value varchar2(10)
|
|
,constraint STATS$RESOURCE_LIMIT_PK primary key
|
|
(snap_id, dbid, instance_number, resource_name)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$RESOURCE_LIMIT_FK foreign key
|
|
(snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$DLM_MISC
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,statistic# number not null
|
|
,name varchar2(38)
|
|
,value number
|
|
,constraint STATS$DLM_MISC_PK primary key
|
|
(snap_id, dbid, instance_number, statistic#)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$DLM_MISC_FK foreign key
|
|
(snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$UNDOSTAT
|
|
(begin_time date not null
|
|
,end_time date not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,snap_id number(6) not null
|
|
,undotsn number not null
|
|
,undoblks number
|
|
,txncount number
|
|
,maxquerylen number
|
|
,maxconcurrency number
|
|
,unxpstealcnt number
|
|
,unxpblkrelcnt number
|
|
,unxpblkreucnt number
|
|
,expstealcnt number
|
|
,expblkrelcnt number
|
|
,expblkreucnt number
|
|
,ssolderrcnt number
|
|
,nospaceerrcnt number
|
|
,constraint STATS$UNDOSTAT_PK primary key
|
|
(begin_time, end_time, dbid, instance_number)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$SQL_PLAN_USAGE
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,hash_value number not null
|
|
,text_subset varchar2(31) not null
|
|
,plan_hash_value number not null
|
|
,cost number
|
|
,address raw(8)
|
|
,optimizer varchar2(20)
|
|
,constraint STATS$SQL_PLAN_USAGE_PK primary key
|
|
(snap_id, dbid, instance_number
|
|
,hash_value, text_subset, plan_hash_value, cost)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$SQL_PLAN_USAGE_FK foreign key
|
|
(snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 5m next 5m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
create index STATS$SQL_PLAN_USAGE_HV ON STATS$SQL_PLAN_USAGE (hash_value)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0);
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$SQL_PLAN
|
|
(plan_hash_value number not null
|
|
,id number not null
|
|
,operation varchar2(30)
|
|
,options varchar2(30)
|
|
,object_node varchar2(10)
|
|
,object# number
|
|
,object_owner varchar2(30)
|
|
,object_name varchar2(30)
|
|
,optimizer varchar2(20)
|
|
,parent_id number
|
|
,depth number
|
|
,position number
|
|
,search_columns number
|
|
,cost number
|
|
,cardinality number
|
|
,bytes number
|
|
,other_tag varchar2(35)
|
|
,partition_start varchar2(5)
|
|
,partition_stop varchar2(5)
|
|
,partition_id number
|
|
,other varchar2(4000)
|
|
,distribution varchar2(20)
|
|
,cpu_cost number
|
|
,io_cost number
|
|
,temp_space number
|
|
,access_predicates varchar2(4000)
|
|
,filter_predicates varchar2(4000)
|
|
,snap_id number
|
|
,constraint STATS$SQL_PLAN_PK primary key
|
|
(plan_hash_value, id)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 5m next 5m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$SEG_STAT
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,dataobj# number not null
|
|
,obj# number not null
|
|
,ts# number not null
|
|
,logical_reads number
|
|
,buffer_busy_waits number
|
|
,db_block_changes number
|
|
,physical_reads number
|
|
,physical_writes number
|
|
,direct_physical_reads number
|
|
,direct_physical_writes number
|
|
,global_cache_cr_blocks_served number
|
|
,global_cache_cu_blocks_served number
|
|
,itl_waits number
|
|
,row_lock_waits number
|
|
, constraint STATS$SEG_STAT_PK primary key
|
|
(snap_id, dbid, instance_number, dataobj#, obj#)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$SEG_STAT_FK foreign key
|
|
(snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 3m next 3m pctincrease 0);
|
|
|
|
|
|
-- Segment names having statistics
|
|
|
|
create table STATS$SEG_STAT_OBJ
|
|
(dataobj# number not null
|
|
,obj# number not null
|
|
,ts# number not null
|
|
,dbid number not null
|
|
,owner varchar(30) not null
|
|
,object_name varchar(30) not null
|
|
,subobject_name varchar(30)
|
|
,object_type varchar2(18)
|
|
,tablespace_name varchar(30) not null
|
|
,constraint STATS$SEG_STAT_OBJ_PK primary key
|
|
(dataobj#, obj#, dbid)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0);
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$PGASTAT
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,name varchar2(64) not null
|
|
,value number
|
|
,constraint STATS$SQL_PGASTAT_PK primary key
|
|
(snap_id, dbid, instance_number, name)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$SQL_PGASTAT_FK foreign key
|
|
(snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$IDLE_EVENT
|
|
(event varchar2(64) not null
|
|
,constraint STATS$IDLE_EVENT_PK primary key (event)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 100k next 100k pctincrease 0)
|
|
) tablespace &&tablespace_name
|
|
storage (initial 100k next 100k pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
insert into STATS$IDLE_EVENT (event) values ('smon timer');
|
|
insert into STATS$IDLE_EVENT (event) values ('pmon timer');
|
|
insert into STATS$IDLE_EVENT (event) values ('rdbms ipc message');
|
|
insert into STATS$IDLE_EVENT (event) values ('Null event');
|
|
insert into STATS$IDLE_EVENT (event) values ('parallel query dequeue');
|
|
insert into STATS$IDLE_EVENT (event) values ('pipe get');
|
|
insert into STATS$IDLE_EVENT (event) values ('client message');
|
|
insert into STATS$IDLE_EVENT (event) values ('SQL*Net message to client');
|
|
insert into STATS$IDLE_EVENT (event) values ('SQL*Net message from client');
|
|
insert into STATS$IDLE_EVENT (event) values ('SQL*Net more data from client');
|
|
insert into STATS$IDLE_EVENT (event) values ('dispatcher timer');
|
|
insert into STATS$IDLE_EVENT (event) values ('virtual circuit status');
|
|
insert into STATS$IDLE_EVENT (event) values ('lock manager wait for remote message');
|
|
insert into STATS$IDLE_EVENT (event) values ('PX Idle Wait');
|
|
insert into STATS$IDLE_EVENT (event) values ('PX Deq: Execution Msg');
|
|
insert into STATS$IDLE_EVENT (event) values ('PX Deq: Table Q Normal');
|
|
insert into STATS$IDLE_EVENT (event) values ('wakeup time manager');
|
|
insert into STATS$IDLE_EVENT (event) values ('slave wait');
|
|
insert into STATS$IDLE_EVENT (event) values ('i/o slave wait');
|
|
insert into STATS$IDLE_EVENT (event) values ('jobq slave wait');
|
|
insert into STATS$IDLE_EVENT (event) values ('null event');
|
|
insert into STATS$IDLE_EVENT (event) values ('gcs remote message');
|
|
insert into STATS$IDLE_EVENT (event) values ('gcs for action');
|
|
insert into STATS$IDLE_EVENT (event) values ('ges remote message');
|
|
insert into STATS$IDLE_EVENT (event) values ('queue messages');
|
|
commit;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$PARAMETER
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,name varchar2(64) not null
|
|
,value varchar2(512)
|
|
,isdefault varchar2(9)
|
|
,ismodified varchar2(10)
|
|
,constraint STATS$PARAMETER_PK primary key
|
|
(snap_id, dbid, instance_number, name)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$PARAMETER_FK foreign key (snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$INSTANCE_RECOVERY
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,recovery_estimated_ios number
|
|
,actual_redo_blks number
|
|
,target_redo_blks number
|
|
,log_file_size_redo_blks number
|
|
,log_chkpt_timeout_redo_blks number
|
|
,log_chkpt_interval_redo_blks number
|
|
,fast_start_io_target_redo_blks number
|
|
,target_mttr number
|
|
,estimated_mttr number
|
|
,ckpt_block_writes number
|
|
,constraint STATS$INSTANCE_RECOVERY_PK primary key
|
|
(snap_id, dbid, instance_number)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$INSTANCE_RECOVERY_FK foreign key
|
|
(snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$STATSPACK_PARAMETER
|
|
(dbid number not null
|
|
,instance_number number not null
|
|
,session_id number not null
|
|
,snap_level number not null
|
|
,num_sql number not null
|
|
,executions_th number not null
|
|
,parse_calls_th number not null
|
|
,disk_reads_th number not null
|
|
,buffer_gets_th number not null
|
|
,sharable_mem_th number not null
|
|
,version_count_th number not null
|
|
,pin_statspack varchar2(10) not null
|
|
,all_init varchar2(5) not null
|
|
,last_modified date
|
|
,ucomment varchar2(160)
|
|
,job number
|
|
,seg_phy_reads_th number not null
|
|
,seg_log_reads_th number not null
|
|
,seg_buff_busy_th number not null
|
|
,seg_rowlock_w_th number not null
|
|
,seg_itl_waits_th number not null
|
|
,seg_cr_bks_sd_th number not null
|
|
,seg_cu_bks_sd_th number not null
|
|
,constraint STATS$STATSPACK_PARAMETER_PK primary key
|
|
(dbid, instance_number)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 100k next 100k pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$STATSPACK_LVL_FK
|
|
foreign key (snap_level) references STATS$LEVEL_DESCRIPTION
|
|
,constraint STATS$STATSPACK_P_PIN_CK
|
|
check (pin_statspack in ('TRUE', 'FALSE'))
|
|
,constraint STATS$STATSPACK_ALL_INIT_CK
|
|
check (all_init in ('TRUE', 'FALSE'))
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 100k next 100k pctincrease 0);
|
|
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$SHARED_POOL_ADVICE
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,shared_pool_size_for_estimate number not null
|
|
,shared_pool_size_factor number
|
|
,estd_lc_size number
|
|
,estd_lc_memory_objects number
|
|
,estd_lc_time_saved number
|
|
,estd_lc_time_saved_factor number
|
|
,estd_lc_memory_object_hits number
|
|
,constraint STATS$SHARED_POOL_ADVICE_PK primary key
|
|
(snap_id, dbid, instance_number, shared_pool_size_for_estimate)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$SHARED_POOL_ADVICE_FK foreign key
|
|
(snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$SQL_WORKAREA_HISTOGRAM
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,low_optimal_size number not null
|
|
,high_optimal_size number not null
|
|
,optimal_executions number
|
|
,onepass_executions number
|
|
,multipasses_executions number
|
|
,total_executions number
|
|
,constraint STATS$SQL_WORKAREA_HIST_PK primary key
|
|
(snap_id, dbid, instance_number, low_optimal_size, high_optimal_size)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$SQL_WORKAREA_HIST_FK foreign key
|
|
(snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
create table STATS$PGA_TARGET_ADVICE
|
|
(snap_id number(6) not null
|
|
,dbid number not null
|
|
,instance_number number not null
|
|
,pga_target_for_estimate number not null
|
|
,pga_target_factor number
|
|
,advice_status varchar2(3)
|
|
,bytes_processed number
|
|
,estd_extra_bytes_rw number
|
|
,estd_pga_cache_hit_percentage number
|
|
,estd_overalloc_count number
|
|
,constraint STATS$PGA_TARGET_ADVICE_PK primary key
|
|
(snap_id, dbid, instance_number, pga_target_for_estimate)
|
|
using index tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0)
|
|
LOCAL
|
|
,constraint STATS$PGA_TARGET_ADVICE_FK foreign key
|
|
(snap_id, dbid, instance_number)
|
|
references STATS$SNAPSHOT on delete cascade
|
|
)
|
|
PARTITION BY LIST (DBID)
|
|
(
|
|
PARTITION others VALUES (DEFAULT)
|
|
)
|
|
tablespace &&tablespace_name
|
|
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;
|
|
|
|
|
|
/* ------------------------------------------------------------------------- */
|
|
|
|
|
|
undefine tablespace_name default_tablespace temporary_tablespace
|
|
|
|
@@footer |