在 GaussDB T 中，完成数据库创建的最后一个脚本是 initwsr.sql ，这是高斯数据库的 『AWR』。
DROP TABLE IF EXISTS WSR_EXCEPTION_LOG
/
CREATE TABLE WSR_EXCEPTION_LOG
(
CTIME DATE NOT NULL,
PROCEDURENAME VARCHAR(100) NOT NULL,
ERRORINFO VARCHAR(8000) NOT NULL
)
TABLESPACE SYSTEM
/
CREATE INDEX IX_EXCEPTION_LOG ON WSR_EXCEPTION_LOG(CTIME)
TABLESPACE SYSTEM
/
CREATE OR REPLACE PROCEDURE WSR$INSERT_ERRORLOG
(
STR_IN_PNAME IN VARCHAR,
STR_IN_ERRORINFO IN VARCHAR
)
AS
BEGIN
INSERT INTO WSR_EXCEPTION_LOG
( CTIME,
PROCEDURENAME,
ERRORINFO
)
VALUES( SYSDATE,
STR_IN_PNAME,
STR_IN_ERRORINFO
);
COMMIT;
END;
/
DECLARE
I_L_COUNT INT;
I_L_NUM INT := 0;
BEGIN
FOR ITEM IN (SELECT * FROM MY_JOBS WHERE WHAT IN (‘WSRDROP_SNAPSHOT_TIME();’, ‘WSR$CREATE_SESSION_SNAPSHOT();’)) LOOP
DBMS_JOB.BROKEN(ITEM.JOB, TRUE);
END LOOP;
COMMIT;
LOOP
SELECT COUNT(*)
INTO I_L_COUNT
FROM MY_JOBS A, ADM_JOBS_RUNNING B
WHERE WHAT IN ('WSR$CREATE_SNAPSHOT();', 'WSR$DROP_SNAPSHOT_TIME();', 'WSR$CREATE_SESSION_SNAPSHOT();')
AND A.JOB = B.JOB;
IF (I_L_COUNT > 0) THEN
SLEEP(1);
I_L_NUM := I_L_NUM + 1;
IF (I_L_NUM > 15) THEN
RAISE_APPLICATION_ERROR(-20000, 'CAN''T STOP WSR JOB!');
END IF;
ELSE
EXIT;
END IF;
END LOOP;
END;
/
DROP TABLE IF EXISTS WSR_CONTROL_BK
/
DECLARE
I_L_COUNT BINARY_INTEGER;
BEGIN
SELECT COUNT(*)
INTO I_L_COUNT
FROM SYS_TABLES
WHERE NAME = ‘WSR_CONTROL’
AND USER# = 0;
IF (I_L_COUNT > 0) THEN
EXECUTE IMMEDIATE 'CREATE TABLE WSR_CONTROL_BK AS SELECT * FROM WSR_CONTROL';
END IF;
END;
/
DROP TABLE IF EXISTS WRM$_WR_CONTROL
/
DROP TABLE IF EXISTS WSR_CONTROL
/
CREATE TABLE WSR_CONTROL
(
DBID BINARY_INTEGER NOT NULL,
SNAP_INTERVAL INTERVAL DAY(5) TO SECOND(1) NOT NULL,
SNAPINT_NUM BINARY_INTEGER NOT NULL,
RETENTION INTERVAL DAY(5) TO SECOND(1) NOT NULL,
RETENTION_NUM BINARY_INTEGER NOT NULL,
MOST_RECENT_SNAP_ID BINARY_INTEGER,
MOST_RECENT_SNAP_TIME TIMESTAMP(3),
STATUS VARCHAR(3) NOT NULL,
MOST_RECENT_PURGE_TIME TIMESTAMP(3),
TOPNSQL BINARY_INTEGER NOT NULL,
LOG_DAYS BINARY_INTEGER DEFAULT 30 NOT NULL,
SESSION_STATUS VARCHAR(3) NOT NULL,
SESSION_INTERVAL BINARY_INTEGER NOT NULL
)
TABLESPACE SYSTEM
/
INSERT INTO WSR_CONTROL(DBID, SNAP_INTERVAL, SNAPINT_NUM, RETENTION, RETENTION_NUM, STATUS, TOPNSQL, LOG_DAYS, SESSION_STATUS, SESSION_INTERVAL)
SELECT DBID, NUMTODSINTERVAL(30, ‘MINUTE’), 1800, NUMTODSINTERVAL(2, ‘DAY’), 2*86400, ‘Y’, 200, 30, ‘Y’, 30
FROM DV_DATABASE
/
CREATE OR REPLACE PROCEDURE WSR$CHECK_COLUMN
(
STR_IN_COLUMNNAME IN VARCHAR
)
AS
I_L_COUNT INTEGER;
BEGIN
SELECT COUNT(*)
INTO I_L_COUNT
FROM MY_TAB_COLUMNS
WHERE TABLE_NAME = ‘WSR_CONTROL_BK’
AND COLUMN_NAME = UPPER(STR_IN_COLUMNNAME);
IF (I_L_COUNT > 0) THEN
EXECUTE IMMEDIATE 'UPDATE WSR_CONTROL SET ' || STR_IN_COLUMNNAME || ' = NVL((SELECT ' || STR_IN_COLUMNNAME || ' FROM WSR_CONTROL_BK), ' || STR_IN_COLUMNNAM
E ||’)’;
END IF;
EXCEPTION
WHEN OTHERS THEN
WSRCHECK_COLUMN’, SUBSTR(SQLERRM,1,2000));
END;
/
CALL WSRCHECK_COLUMN(‘SNAPINT_NUM’)
/
CALL WSRCHECK_COLUMN(‘RETENTION_NUM’)
/
CALL WSRCHECK_COLUMN(‘TOPNSQL’)
/
CALL WSRCHECK_COLUMN(‘SESSION_STATUS’)
/
CALL WSR$CHECK_COLUMN(‘SESSION_INTERVAL’)
/
DROP PROCEDURE WSR$CHECK_COLUMN
/
DROP TABLE IF EXISTS WSR_CONTROL_BK
/
DROP VIEW IF EXISTS DBA_HIST_WR_CONTROL
/
CREATE OR REPLACE VIEW ADM_HIST_WR_CONTROL AS
SELECT DBID, SNAP_INTERVAL, RETENTION, TOPNSQL, STATUS, LOG_DAYS, SESSION_STATUS, SESSION_INTERVAL
FROM SYS.WSR_CONTROL
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_HIST_WR_CONTROL FOR SYS.ADM_HIST_WR_CONTROL
/
–WSR_TRANSACTION
DROP TABLE IF EXISTS WSR_TRANSACTION
/
CREATE TABLE WSR_TRANSACTION
(
SNAP_TIME DATE NOT NULL,
SID BINARY_INTEGER NOT NULL,
UNDO_COUNT BINARY_INTEGER,
BEGIN_TIME DATE
)
PARTITION BY RANGE(SNAP_TIME) INTERVAL (NUMTODSINTERVAL(1,‘DAY’)) (PARTITION P_0 VALUES LESS THAN (TO_DATE(‘2009-02-01’,‘YYYY-MM-DD’)))
TABLESPACE SYSTEM
/
–WSR_LOCK
DROP TABLE IF EXISTS WSR_LOCK_OBJECT
/
CREATE TABLE WSR_LOCK_OBJECT
(
SNAP_TIME DATE NOT NULL,
SID BINARY_INTEGER,
OBJECT_NAME CHAR(68 BYTE),
LMODE CHAR(10 BYTE)
)
PARTITION BY RANGE(SNAP_TIME) INTERVAL (NUMTODSINTERVAL(1,‘DAY’)) (PARTITION P_0 VALUES LESS THAN (TO_DATE(‘2009-02-01’,‘YYYY-MM-DD’)))
TABLESPACE SYSTEM
/
CREATE INDEX IX_WSR_LOCK_OBJECT ON WSR_LOCK_OBJECT(SNAP_TIME, SID) LOCAL
TABLESPACE SYSTEM
/
–WSR_SESSION_SUMMARY
DROP TABLE IF EXISTS WSR_INSTANCE_SNAP
/
CREATE TABLE WSR_INSTANCE_SNAP
(
SNAP_TIME DATE,
SESSION_NUM BINARY_INTEGER,
ACTIVE_SESS_NUM BINARY_INTEGER,
LOCK_NUM BINARY_INTEGER,
TRANSACTION_NUM BINARY_INTEGER,
TEMP_BUFFER_FREE BINARY_INTEGER,
DATA_BUFFER_PIN BINARY_INTEGER,
TS_SYSTEM_FREE BINARY_BIGINT,
TS_SYSWARE_FREE BINARY_BIGINT,
TS_USER_FREE BINARY_BIGINT,
TXN_PAGES BINARY_INTEGER,
UNDO_PAGES BINARY_INTEGER
)
PARTITION BY RANGE(SNAP_TIME) INTERVAL (NUMTODSINTERVAL(1,‘DAY’)) (PARTITION P_0 VALUES LESS THAN (TO_DATE(‘2009-02-01’,‘YYYY-MM-DD’)))
TABLESPACE SYSTEM
/
CREATE INDEX IX_WSR_INSTANCE_SNAP ON WSR_INSTANCE_SNAP(SNAP_TIME) LOCAL
TABLESPACE SYSTEM
/
–WSR_SESSION_SQL
DROP TABLE IF EXISTS WSR_SESSION_SQL
/
CREATE TABLE WSR_SESSION_SQL
(
SID BINARY_INTEGER,
CTIME DATE,
SQL_ID VARCHAR(32 BYTE),
CURR_SCHEMA VARCHAR(64 BYTE),
CLIENT_IP VARCHAR(20 BYTE),
PROGRAM VARCHAR(256 BYTE),
AUTO_COMMIT BOOLEAN,
LOGON_TIME DATE,
WAIT_SID BINARY_INTEGER,
SQL_EXEC_START DATE,
MODULE VARCHAR(64 BYTE),
EVENT VARCHAR(64 BYTE)
)
PARTITION BY RANGE(CTIME) INTERVAL (NUMTODSINTERVAL(1,‘DAY’)) (PARTITION P_0 VALUES LESS THAN (TO_DATE(‘2009-02-01’,‘YYYY-MM-DD’)))
TABLESPACE SYSTEM
/
CREATE INDEX IX_WSR_SESSION_SQL ON WSR_SESSION_SQL(CTIME) LOCAL
TABLESPACE SYSTEM
/
DROP TABLE IF EXISTS WRM$_SNAPSHOT
/
DROP TABLE IF EXISTS WSR_SNAPSHOT
/
CREATE TABLE WSR_SNAPSHOT
(
SNAP_ID BINARY_INTEGER NOT NULL,
DBID BINARY_INTEGER NOT NULL,
INSTANCE_ID BINARY_INTEGER NOT NULL,
STARTUP_TIME TIMESTAMP(3),
SNAP_TIME TIMESTAMP(3) NOT NULL,
FLUSH_ELAPSED INTERVAL DAY(5) TO SECOND(1) NOT NULL,
SESSIONS BINARY_INTEGER NOT NULL,
CURSORS BINARY_INTEGER NOT NULL
)
TABLESPACE SYSTEM
/
ALTER TABLE WSR_SNAPSHOT
ADD CONSTRAINT WSR_SNAPSHOT_PK PRIMARY KEY (SNAP_ID, DBID, INSTANCE_ID)
/
DECLARE
I_L_COUNT INT;
BEGIN
SELECT COUNT(*)
INTO I_L_COUNT
FROM MY_SEQUENCES
WHERE SEQUENCE_NAME = UPPER(‘SNAP_ID$’);
IF (I_L_COUNT > 0) THEN
EXECUTE IMMEDIATE 'DROP SEQUENCE SNAP_ID$';
END IF;
END;
/
CREATE SEQUENCE SNAP_ID$
MINVALUE 1
NOCACHE
/
DROP VIEW IF EXISTS DBA_HIST_SNAPSHOT
/
CREATE OR REPLACE VIEW ADM_HIST_SNAPSHOT AS
SELECT SNAP_ID, DBID, INSTANCE_ID, STARTUP_TIME,
SNAP_TIME,
FLUSH_ELAPSED, SESSIONS, CURSORS
FROM SYS.WSR_SNAPSHOT
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_HIST_SNAPSHOT FOR SYS.ADM_HIST_SNAPSHOT
/
DROP TABLE IF EXISTS WRH$_SYSSTAT
/
DROP TABLE IF EXISTS WSR_SYS_STAT
/
CREATE TABLE WSR_SYS_STAT
(
SNAP_ID BINARY_INTEGER NOT NULL,
STAT_ID BINARY_INTEGER NOT NULL,
STAT_NAME VARCHAR(64) NOT NULL,
VALUE BINARY_BIGINT
)
TABLESPACE SYSTEM
/
ALTER TABLE WSR_SYS_STAT
ADD CONSTRAINT WSR_SYS_STAT_PK PRIMARY KEY (SNAP_ID, STAT_NAME)
/
DROP VIEW IF EXISTS DBA_HIST_SYSSTAT
/
CREATE OR REPLACE VIEW ADM_HIST_SYSSTAT AS
SELECT S.SNAP_ID, S.STAT_ID, S.STAT_NAME, S.VALUE
FROM SYS.WSR_SYS_STAT S
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_HIST_SYSSTAT FOR SYS.ADM_HIST_SYSSTAT
/
DROP TABLE IF EXISTS WRH$_SYSTEM
/
DROP TABLE IF EXISTS WSR_SYSTEM
/
CREATE TABLE WSR_SYSTEM
(
SNAP_ID BINARY_INTEGER NOT NULL,
STAT_ID BINARY_INTEGER NOT NULL,
STAT_NAME VARCHAR(64) NOT NULL,
VALUE VARCHAR(128)
)
TABLESPACE SYSTEM
/
ALTER TABLE WSR_SYSTEM
ADD CONSTRAINT WSR_SYSTEM_PK PRIMARY KEY (SNAP_ID, STAT_ID)
/
DROP VIEW IF EXISTS DBA_HIST_SYSTEM
/
CREATE OR REPLACE VIEW ADM_HIST_SYSTEM AS
SELECT S.SNAP_ID, S.STAT_ID, S.STAT_NAME, S.VALUE
FROM SYS.WSR_SYSTEM S
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_HIST_SYSTEM FOR SYS.ADM_HIST_SYSTEM
/
DROP TABLE IF EXISTS WRH$_SYSTEM_EVENT
/
DROP TABLE IF EXISTS WSR_SYSTEM_EVENT
/
CREATE TABLE WSR_SYSTEM_EVENT
(
SNAP_ID BINARY_INTEGER NOT NULL,
EVENT# BINARY_INTEGER NOT NULL,
EVENT VARCHAR(64),
WAIT_CLASS VARCHAR(64),
TOTAL_WAITS VARCHAR(64),
TIME_WAITED BINARY_BIGINT,
TIME_WAITED_MIRCO BINARY_BIGINT,
AVERAGE_WAIT BINARY_DOUBLE,
AVERAGE_WAIT_MIRCO BINARY_BIGINT
)
TABLESPACE SYSTEM
/
ALTER TABLE WSR_SYSTEM_EVENT
ADD CONSTRAINT WSR_SYSTEM_EVENT_PK PRIMARY KEY (SNAP_ID, EVENT#)
/
DROP VIEW IF EXISTS DBA_HIST_SYSTEM_EVENT
/
CREATE OR REPLACE VIEW ADM_HIST_SYSTEM_EVENT AS
SELECT S.SNAP_ID, EVENT#, EVENT, WAIT_CLASS, TOTAL_WAITS,
TIME_WAITED, TIME_WAITED_MIRCO, AVERAGE_WAIT, AVERAGE_WAIT_MIRCO
FROM SYS.WSR_SYSTEM_EVENT S
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_HIST_SYSTEM_EVENT FOR SYS.ADM_HIST_SYSTEM_EVENT
/
DROP TABLE IF EXISTS WRH$_SQLAREA
/
DROP TABLE IF EXISTS WSR_SQLAREA
/
CREATE TABLE WSR_SQLAREA
(
SNAP_ID BINARY_INTEGER NOT NULL,
SQL_ID VARCHAR(10),
SQL_TEXT VARCHAR(8000),
MODULE VARCHAR(64),
EXECUTIONS BINARY_BIGINT,
DISK_READS BINARY_BIGINT,
BUFFER_GETS BINARY_BIGINT,
SORTS BINARY_BIGINT,
PARSE_CALLS BINARY_BIGINT,
PROCESSED_ROWS BINARY_BIGINT,
IO_WAIT_TIME BINARY_BIGINT,
CON_WAIT_TIME BINARY_BIGINT,
CPU_TIME BINARY_BIGINT,
ELAPSED_TIME BINARY_BIGINT,
REF_COUNT BINARY_BIGINT,
IS_FREE BOOLEAN,
CLEANED BOOLEAN,
CR_GETS BINARY_BIGINT,
PARSE_TIME BINARY_BIGINT,
PARSING_USER_NAME VARCHAR(64),
PROGRAM_ID BINARY_BIGINT,
PROGRAM_LINE# BINARY_INTEGER,
VM_PAGES_USED BINARY_BIGINT
)
PARTITION BY RANGE(SNAP_ID) INTERVAL(1) (PARTITION P_0 VALUES LESS THAN (1))
TABLESPACE SYSTEM
/
DROP VIEW IF EXISTS DBA_HIST_SQLAREA
/
CREATE OR REPLACE VIEW ADM_HIST_SQLAREA
AS
SELECT SNAP_ID, SQL_ID, SQL_TEXT, MODULE, EXECUTIONS, DISK_READS,
BUFFER_GETS, SORTS, PARSE_CALLS, PROCESSED_ROWS,
IO_WAIT_TIME, CON_WAIT_TIME, CPU_TIME, ELAPSED_TIME,
REF_COUNT, IS_FREE, CLEANED, CR_GETS, PARSE_TIME, PARSING_USER_NAME, PROGRAM_ID, PROGRAM_LINE#, VM_PAGES_USED
FROM SYS.WSR_SQLAREA
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_HIST_SQLAREA FOR SYS.ADM_HIST_SQLAREA
/
DROP TABLE IF EXISTS WRH$_PARAMETER
/
DROP TABLE IF EXISTS WSR_PARAMETER
/
CREATE TABLE WSR_PARAMETER
(
SNAP_ID BINARY_INTEGER NOT NULL,
NAME VARCHAR(64) NOT NULL,
VALUE VARCHAR(2048),
RUNTIME_VALUE VARCHAR(2048),
DEFAULT_VALUE VARCHAR(2048),
ISDEFAULT VARCHAR(20),
MODIFIABLE VARCHAR(20),
DESCRIPTION VARCHAR(2048),
RANGE VARCHAR(2048),
DATATYPE VARCHAR(20),
EFFECTIVE VARCHAR(20)
)
TABLESPACE SYSTEM
/
ALTER TABLE WSR_PARAMETER
ADD CONSTRAINT WSR_PARAMETER_PK PRIMARY KEY (SNAP_ID, NAME)
/
DROP VIEW IF EXISTS DBA_HIST_PARAMETER
/
CREATE OR REPLACE VIEW ADM_HIST_PARAMETER
AS
SELECT SNAP_ID, NAME, VALUE, RUNTIME_VALUE,DEFAULT_VALUE,
ISDEFAULT, MODIFIABLE, DESCRIPTION, RANGE
DATATYPE, EFFECTIVE
FROM SYS.WSR_PARAMETER
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_HIST_PARAMETER FOR SYS.ADM_HIST_PARAMETER
/
DROP TABLE IF EXISTS WSR$_WAITSTAT
/
DROP TABLE IF EXISTS WSR_WAITSTAT
/
CREATE TABLE WSR_WAITSTAT
(
SNAP_ID BINARY_INTEGER NOT NULL,
CLASS VARCHAR(64 BYTE),
COUNT BINARY_INTEGER,
TIME BINARY_INTEGER
)
TABLESPACE SYSTEM
/
CREATE INDEX IX_WSR_WAITSTAT ON WSR_WAITSTAT(SNAP_ID)
/
DROP TABLE IF EXISTS WSR$_LATCH
/
DROP TABLE IF EXISTS WSR_LATCH
/
DROP VIEW IF EXISTS DBA_HIST_WAITSTAT
/
CREATE OR REPLACE VIEW ADM_HIST_WAITSTAT
AS
SELECT SNAP_ID, CLASS, COUNT, TIME
FROM SYS.WSR_WAITSTAT
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_HIST_WAITSTAT FOR SYS.ADM_HIST_WAITSTAT
/
CREATE TABLE WSR_LATCH
(
SNAP_ID BINARY_INTEGER NOT NULL,
ID BINARY_INTEGER,
NAME VARCHAR(64 BYTE),
GETS BINARY_INTEGER,
MISSES BINARY_INTEGER,
SPIN_GETS BINARY_INTEGER,
WAIT_TIME BINARY_INTEGER
)
TABLESPACE SYSTEM
/
ALTER TABLE WSR_LATCH
ADD CONSTRAINT WSR_LATCH_PK PRIMARY KEY (SNAP_ID, ID)
/
DROP TABLE IF EXISTS WSR$_LIBRARYCACHE
/
DROP TABLE IF EXISTS WSR_LIBRARYCACHE
/
DROP VIEW IF EXISTS DBA_HIST_LATCH
/
CREATE OR REPLACE VIEW ADM_HIST_LATCH
AS
SELECT SNAP_ID, ID, NAME, GETS, MISSES, SPIN_GETS, WAIT_TIME
FROM SYS.WSR_LATCH
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_HIST_LATCH FOR SYS.ADM_HIST_LATCH
/
CREATE TABLE WSR_LIBRARYCACHE
(
SNAP_ID BINARY_INTEGER NOT NULL,
NAMESPACE VARCHAR(20 BYTE),
GETS BINARY_BIGINT,
GETHITS BINARY_BIGINT,
PINS BINARY_BIGINT,
PINHITS BINARY_BIGINT,
RELOADS BINARY_BIGINT,
INVLIDATIONS BINARY_BIGINT
)
TABLESPACE SYSTEM
/
CREATE INDEX IX_WSR_LIBRARYCACHE ON WSR_LIBRARYCACHE(SNAP_ID)
/
DROP VIEW IF EXISTS DBA_HIST_LIBRARYCACHE
/
CREATE OR REPLACE VIEW ADM_HIST_LIBRARYCACHE
AS
SELECT SNAP_ID, NAMESPACE, GETS, GETHITS, PINS, PINHITS, RELOADS, INVLIDATIONS
FROM SYS.WSR_LIBRARYCACHE
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_HIST_LIBRARYCACHE FOR SYS.ADM_HIST_LIBRARYCACHE
/
DROP TABLE IF EXISTS WSR$_SEGMENT
/
DROP TABLE IF EXISTS WSR_SEGMENT
/
CREATE TABLE WSR_SEGMENT
(
SNAP_ID BINARY_INTEGER NOT NULL,
OWNER VARCHAR(64 BYTE),
OBJECT_NAME VARCHAR(64 BYTE),
SUBOBJECT_NAME VARCHAR(64 BYTE),
TS# BINARY_INTEGER,
OBJECT_TYPE VARCHAR(64 BYTE),
STATISTIC_NAME VARCHAR(64 BYTE),
STATISTIC# BINARY_INTEGER,
VALUE BINARY_INTEGER
)
PARTITION BY RANGE(SNAP_ID) INTERVAL(1) (PARTITION P_0 VALUES LESS THAN (1))
TABLESPACE SYSTEM
/
CREATE INDEX IX_WSR_SEGMENT ON WSR_SEGMENT(STATISTIC#) LOCAL
/
DROP VIEW IF EXISTS DBA_HIST_SEGMENT
/
CREATE OR REPLACE VIEW ADM_HIST_SEGMENT
AS
SELECT SNAP_ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME, TS#, OBJECT_TYPE, STATISTIC_NAME, STATISTIC#, VALUE
FROM SYS.WSR_SEGMENT
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_HIST_SEGMENT FOR SYS.ADM_HIST_SEGMENT
/
DROP TABLE IF EXISTS WSR$_DBA_SEGMENTS
/
DROP TABLE IF EXISTS WSR_DBA_SEGMENTS
/
CREATE TABLE WSR_DBA_SEGMENTS
(
SNAP_ID BINARY_INTEGER NOT NULL,
OWNER VARCHAR(64 BYTE),
SEGMENT_NAME VARCHAR(133 BYTE),
PARTITION_NAME VARCHAR(145 BYTE),
SEGMENT_TYPE CHAR(5 BYTE),
TABLESPACE_NAME VARCHAR(64 BYTE),
BYTES BINARY_BIGINT,
PAGES BINARY_BIGINT,
EXTENTS BINARY_BIGINT
)
PARTITION BY RANGE(SNAP_ID) INTERVAL(1) (PARTITION P_0 VALUES LESS THAN (1))
TABLESPACE SYSTEM
/
DROP VIEW IF EXISTS DBA_HIST_DBASEGMENTS
/
CREATE OR REPLACE VIEW ADM_HIST_DBASEGMENTS
AS
SELECT SNAP_ID, OWNER, SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES, PAGES, EXTENTS
FROM SYS.WSR_DBA_SEGMENTS
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_HIST_DBASEGMENTS FOR SYS.ADM_HIST_DBASEGMENTS
/
–WSR_SQLTEXT
DROP TABLE IF EXISTS WSR_SQLTEXT
/
CREATE TABLE WSR_SQLTEXT
(
CTIME DATE,
SQL_ID VARCHAR(32 BYTE),
SQL_TEXT VARCHAR(8000 BYTE)
)
PARTITION BY RANGE(CTIME) INTERVAL (NUMTODSINTERVAL(1,‘DAY’)) (PARTITION P_0 VALUES LESS THAN (TO_DATE(‘2009-02-01’,‘YYYY-MM-DD’)))
TABLESPACE SYSTEM
/
CREATE INDEX IX_WSR_SQLTEXT ON WSR_SQLTEXT (SQL_ID) LOCAL
TABLESPACE SYSTEM
/
–WSR_SQLPLAN
DROP TABLE IF EXISTS WSR_SQLPLAN
/
CREATE TABLE WSR_SQLPLAN
(
CTIME DATE,
SQL_ID VARCHAR(32 BYTE),
EXPLAIN_ID VARCHAR(32 BYTE),
EXPLAIN_TEXT VARCHAR(8000 BYTE)
)
PARTITION BY RANGE(CTIME) INTERVAL (NUMTODSINTERVAL(1,‘DAY’)) (PARTITION P_0 VALUES LESS THAN (TO_DATE(‘2009-02-01’,‘YYYY-MM-DD’)))
TABLESPACE SYSTEM
/
CREATE INDEX IX_WSR_SQLPLAN ON WSR_SQLPLAN (SQL_ID, EXPLAIN_ID) LOCAL
TABLESPACE SYSTEM
/
–WSR_LONGSQL
DROP TABLE IF EXISTS WSR$_LONGSQL
/
DROP TABLE IF EXISTS WSR_LONGSQL
/
CREATE TABLE WSR_LONGSQL
(
SNAP_ID BINARY_INTEGER NOT NULL,
CTIME DATE,
ELAPSED_TIME NUMBER(38, 2),
SQL_ID VARCHAR(32 BYTE),
EXPLAIN_ID VARCHAR(32 BYTE),
STAGE VARCHAR(12 BYTE),
SQL_TEXT VARCHAR(100)
)
PARTITION BY RANGE(SNAP_ID) INTERVAL(1) (PARTITION P_0 VALUES LESS THAN (1))
TABLESPACE SYSTEM
/
CREATE INDEX IX_WSR_LONGSQL_SQLID ON WSR_LONGSQL(SQL_ID) LOCAL
/
DROP VIEW IF EXISTS DBA_HIST_LONGSQL
/
CREATE OR REPLACE VIEW ADM_HIST_LONGSQL
AS
SELECT SNAP_ID, CTIME, ELAPSED_TIME, SQL_ID, STAGE, SQL_TEXT
FROM SYS.WSR_LONGSQL
/
CREATE OR REPLACE PUBLIC SYNONYM ADM_HIST_LONGSQL FOR SYS.ADM_HIST_LONGSQL
/
DROP TABLE IF EXISTS WSR$SQL_LIST
/
DROP TABLE IF EXISTS WSR_SQL_LIST
/
–WSR_SESSION_EVENTS
DROP TABLE IF EXISTS WSR_SESSION_EVENTS
/
CREATE TABLE WSR_SESSION_EVENTS
(
SNAP_ID BINARY_INTEGER NOT NULL,
SID BINARY_INTEGER,
EVENT# BINARY_INTEGER,
EVENT VARCHAR(64 BYTE),
P1 VARCHAR(64 BYTE),
WAIT_CLASS VARCHAR(64 BYTE),
TOTAL_WAITS BINARY_BIGINT,
TIME_WAITED_MIRCO BINARY_BIGINT
)
PARTITION BY RANGE(SNAP_ID) INTERVAL(1) (PARTITION P_0 VALUES LESS THAN (1))
TABLESPACE SYSTEM
/
CREATE INDEX IX_WSR_SESSION_EVENTS_SID ON WSR_SESSION_EVENTS(SID) LOCAL
/
CREATE GLOBAL TEMPORARY TABLE WSR_SQL_LIST
(
SQL_ID VARCHAR(100) PRIMARY KEY,
SQL_TEXT CLOB
)
ON COMMIT DELETE ROWS
/
DROP TABLE IF EXISTS WSR$SQL_LIST_PLAN
/
DROP TABLE IF EXISTS WSR_SQL_LIST_PLAN
/
CREATE GLOBAL TEMPORARY TABLE WSR_SQL_LIST_PLAN
(
SQL_ID VARCHAR(100) PRIMARY KEY,
ELAPSED_TIME NUMBER(38, 2),
EXPLAIN_TEXT CLOB
)
ON COMMIT DELETE ROWS
/
CREATE OR REPLACE PROCEDURE WSR$INSERT_SQL_LIST
(
STR_SQL_ID IN VARCHAR,
STR_SQL_TEXT IN VARCHAR,
I_IN_STARTSNAPID IN BINARY_INTEGER,
I_IN_ENDSNAPID IN BINARY_INTEGER
)
AS
I_L_COUNT NUMBER(3);
I_L_ELAPSED_TIME NUMBER(38, 2);
STR_L_EXPLAIN_TEXT VARCHAR(8000);
STR_EXPLAIN_ID VARCHAR(32 BYTE);
BEGIN
SELECT COUNT(*)
INTO I_L_COUNT
FROM WSR_SQL_LIST
WHERE SQL_ID = STR_SQL_ID;
IF (I_L_COUNT = 0) THEN
IF (STR_SQL_TEXT IS NOT NULL AND STR_SQL_TEXT <> '') THEN
INSERT INTO WSR_SQL_LIST
( SQL_ID,
SQL_TEXT
)
VALUES( STR_SQL_ID,
STR_SQL_TEXT
);
ELSE
INSERT INTO WSR_SQL_LIST
( SQL_ID,
SQL_TEXT
)
SELECT SQL_ID,
SQL_TEXT
FROM WSR_SQLTEXT
WHERE SQL_ID = STR_SQL_ID
LIMIT 1;
END IF;
BEGIN
SELECT ELAPSED_TIME, EXPLAIN_ID
INTO I_L_ELAPSED_TIME, STR_EXPLAIN_ID
FROM WSR_LONGSQL
WHERE SQL_ID = STR_SQL_ID
AND SNAP_ID BETWEEN I_IN_STARTSNAPID AND I_IN_ENDSNAPID
ORDER BY ELAPSED_TIME DESC
LIMIT 1;
SELECT SUBSTR(REPLACE(EXPLAIN_TEXT, '"'), 1, 7500)
INTO STR_L_EXPLAIN_TEXT
FROM WSR_SQLPLAN
WHERE SQL_ID = STR_SQL_ID
AND EXPLAIN_ID = STR_EXPLAIN_ID
LIMIT 1;
INSERT INTO WSR_SQL_LIST_PLAN
( SQL_ID,
ELAPSED_TIME,
EXPLAIN_TEXT
)
VALUES( STR_SQL_ID,
I_L_ELAPSED_TIME,
STR_L_EXPLAIN_TEXT
);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END IF;
END;
/
CREATE OR REPLACE PROCEDURE WSRCREATE_SNAPSHOT
AS
I_L_SNAP_ID BINARY_INTEGER;
I_L_DBID BINARY_INTEGER;
I_L_INSTANCE_ID BINARY_INTEGER;
DT_L_STARTTIME TIMESTAMP := SYSTIMESTAMP;
DT_L_SNAPDAY DATE := TRUNC(SYSDATE);
I_L_COUNT BINARY_INTEGER;
I_L_RET BINARY_INTEGER;
STR_L_SQLTEXT VARCHAR2(8000);
BEGIN
IF (GET_LOCK('SYS.WSRCREATE_SNAPSHOT’) <> 1) THEN
RETURN;
END IF;
SELECT SNAP_ID$.NEXTVAL
INTO I_L_SNAP_ID
FROM SYS_DUMMY;
SELECT INSTANCE_ID
INTO I_L_INSTANCE_ID
FROM DV_INSTANCE;
SELECT DBID
INTO I_L_DBID
FROM DV_DATABASE;
INSERT INTO WSR_SYS_STAT
( SNAP_ID,
STAT_ID,
STAT_NAME,
VALUE
)
SELECT I_L_SNAP_ID,
STATISTIC#,
NAME,
VALUE
FROM DV_SYS_STATS;
INSERT INTO WSR_SYSTEM
( SNAP_ID,
STAT_ID,
STAT_NAME,
VALUE
)
SELECT I_L_SNAP_ID,
ID,
NAME,
VALUE
FROM DV_SYSTEM;
INSERT INTO WSR_SYSTEM_EVENT
( SNAP_ID,
EVENT#,
EVENT,
WAIT_CLASS,
TOTAL_WAITS,
TIME_WAITED,
TIME_WAITED_MIRCO,
AVERAGE_WAIT,
AVERAGE_WAIT_MIRCO
)
SELECT I_L_SNAP_ID,
EVENT#,
EVENT,
WAIT_CLASS,
TOTAL_WAITS,
TIME_WAITED,
TIME_WAITED_MIRCO,
AVERAGE_WAIT,
AVERAGE_WAIT_MIRCO
FROM DV_SYS_EVENTS;
INSERT INTO WSR_SYSTEM_EVENT
( SNAP_ID,
EVENT#,
EVENT,
WAIT_CLASS,
TOTAL_WAITS,
TIME_WAITED,
TIME_WAITED_MIRCO,
AVERAGE_WAIT,
AVERAGE_WAIT_MIRCO
)
SELECT I_L_SNAP_ID,
-1,
'CPU',
'',
NULL,
VALUE/1000000,
VALUE,
NULL,
NULL
FROM WSR_SYS_STAT
WHERE SNAP_ID = I_L_SNAP_ID
AND STAT_ID = 2;
FOR ITEM IN (SELECT SQL_ID,
MIN(SQL_TEXT) SQL_TEXT,
MIN(MODULE) MODULE,
SUM(EXECUTIONS) EXECUTIONS,
SUM(DISK_READS) DISK_READS,
SUM(BUFFER_GETS) BUFFER_GETS,
SUM(SORTS) SORTS,
SUM(PARSE_CALLS) PARSE_CALLS,
SUM(PROCESSED_ROWS) PROCESSED_ROWS,
SUM(IO_WAIT_TIME) IO_WAIT_TIME,
SUM(CON_WAIT_TIME) CON_WAIT_TIME,
SUM(CPU_TIME) CPU_TIME,
SUM(ELAPSED_TIME) ELAPSED_TIME,
SUM(REF_COUNT) REF_COUNT,
SUM(CR_GETS) CR_GETS,
SUM(PARSE_TIME) PARSE_TIME,
MIN(PARSING_USER_NAME) PARSING_USER_NAME,
MIN(PROGRAM_ID) PROGRAM_ID,
MIN(PROGRAM_LINE#) PROGRAM_LINE#,
SUM(VM_FREE_PAGES) VM_PAGES_USED
FROM DV_SQLS
WHERE EXECUTIONS > 0
GROUP BY SQL_ID) LOOP
BEGIN
STR_L_SQLTEXT := SUBSTR(ITEM.SQL_TEXT, 1, 7000);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
SELECT COUNT(*)
INTO I_L_COUNT
FROM WSR_SQLTEXT
WHERE CTIME = DT_L_SNAPDAY
AND SQL_ID = ITEM.SQL_ID;
IF (I_L_COUNT = 0) THEN
INSERT INTO WSR_SQLTEXT
( CTIME,
SQL_ID,
SQL_TEXT
)
VALUES( DT_L_SNAPDAY,
ITEM.SQL_ID,
STR_L_SQLTEXT
);
END IF;
INSERT INTO WSR_SQLAREA
( SNAP_ID,
SQL_ID,
SQL_TEXT,
MODULE,
EXECUTIONS,
DISK_READS,
BUFFER_GETS,
SORTS,
PARSE_CALLS,
PROCESSED_ROWS,
IO_WAIT_TIME,
CON_WAIT_TIME,
CPU_TIME,
ELAPSED_TIME,
REF_COUNT,
IS_FREE,
CLEANED,
CR_GETS,
PARSE_TIME,
PARSING_USER_NAME,
PROGRAM_ID,
PROGRAM_LINE#,
VM_PAGES_USED
)
VALUES( I_L_SNAP_ID,
ITEM.SQL_ID,
SUBSTR(STR_L_SQLTEXT, 1, 30),
ITEM.MODULE,
ITEM.EXECUTIONS,
ITEM.DISK_READS,
ITEM.BUFFER_GETS,
ITEM.SORTS,
ITEM.PARSE_CALLS,
ITEM.PROCESSED_ROWS,
ITEM.IO_WAIT_TIME,
ITEM.CON_WAIT_TIME,
ITEM.CPU_TIME,
ITEM.ELAPSED_TIME,
ITEM.REF_COUNT,
NULL,
NULL,
ITEM.CR_GETS,
ITEM.PARSE_TIME,
ITEM.PARSING_USER_NAME,
ITEM.PROGRAM_ID,
ITEM.PROGRAM_LINE#,
ITEM.VM_PAGES_USED );
END LOOP;
INSERT INTO WSR_PARAMETER
( SNAP_ID, NAME, VALUE, RUNTIME_VALUE, DEFAULT_VALUE,
ISDEFAULT, MODIFIABLE, DESCRIPTION, RANGE,
DATATYPE, EFFECTIVE
)
SELECT I_L_SNAP_ID, NAME, VALUE, RUNTIME_VALUE, DEFAULT_VALUE,
ISDEFAULT, MODIFIABLE, DESCRIPTION, RANGE,
DATATYPE, EFFECTIVE
FROM DV_PARAMETERS;
INSERT INTO WSR_LATCH
( SNAP_ID,
ID,
NAME,
GETS,
MISSES,
SPIN_GETS,
WAIT_TIME
)
SELECT I_L_SNAP_ID,
ID,
NAME,
GETS,
MISSES,
SPIN_GETS,
WAIT_TIME
FROM DV_LATCHS;
INSERT INTO WSR_WAITSTAT
( SNAP_ID,
CLASS,
COUNT,
TIME
)
SELECT I_L_SNAP_ID,
CLASS,
COUNT,
TIME
FROM DV_WAIT_STATS;
INSERT INTO WSR_LIBRARYCACHE
( SNAP_ID,
NAMESPACE,
GETS,
GETHITS,
PINS,
PINHITS,
RELOADS,
INVLIDATIONS
)
SELECT I_L_SNAP_ID,
NAMESPACE,
GETS,
GETHITS,
PINS,
PINHITS,
RELOADS,
INVLIDATIONS
FROM DV_LIBRARY_CACHE;
INSERT INTO WSR_SEGMENT
( SNAP_ID,
OWNER,
OBJECT_NAME,
SUBOBJECT_NAME,
TS#,
OBJECT_TYPE,
STATISTIC_NAME,
STATISTIC#,
VALUE
)
SELECT I_L_SNAP_ID,
OWNER,
OBJECT_NAME,
SUBOBJECT_NAME,
TS#,
OBJECT_TYPE,
STATISTIC_NAME,
STATISTIC#,
VALUE
FROM DV_SEGMENT_STATS
WHERE VALUE > 0;
INSERT INTO WSR_DBA_SEGMENTS
(
SNAP_ID,
OWNER,
SEGMENT_NAME,
PARTITION_NAME,
SEGMENT_TYPE,
TABLESPACE_NAME,
BYTES,
PAGES,
EXTENTS
)
SELECT I_L_SNAP_ID,
OWNER,
SEGMENT_NAME,
PARTITION_NAME,
SEGMENT_TYPE,
TABLESPACE_NAME,
BYTES,
PAGES,
EXTENTS
FROM ADM_SEGMENTS
WHERE PAGES > 0;
FOR ITEM IN ( SELECT I_L_SNAP_ID,
TO_DATE(CTIME, 'YYYY-MM-DD HH24:MI:SS') CTIME,
ELAPSED_TIME,
SQL_ID,
EXPLAIN_ID,
EXPLAIN_TEXT,
STAGE,
SQL_TEXT
FROM DV_LONG_SQL
WHERE CTIME <= TO_CHAR(DT_L_STARTTIME :: DATE, 'YYYY-MM-DD HH24:MI:SS')
AND CTIME > TO_CHAR((SELECT MOST_RECENT_SNAP_TIME :: DATE FROM WSR_CONTROL), 'YYYY-MM-DD HH24:MI:SS')) LOOP
BEGIN
STR_L_SQLTEXT := SUBSTR(ITEM.SQL_TEXT, 2, 7000);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
INSERT INTO WSR_LONGSQL
(
SNAP_ID,
CTIME,
ELAPSED_TIME,
SQL_ID,
EXPLAIN_ID,
STAGE,
SQL_TEXT
)
VALUES( I_L_SNAP_ID,
ITEM.CTIME,
ITEM.ELAPSED_TIME,
ITEM.SQL_ID,
ITEM.EXPLAIN_ID,
ITEM.STAGE,
SUBSTR(STR_L_SQLTEXT, 1, 30)
);
SELECT COUNT(*)
INTO I_L_COUNT
FROM WSR_SQLTEXT
WHERE CTIME = DT_L_SNAPDAY
AND SQL_ID = ITEM.SQL_ID;
IF (I_L_COUNT = 0) THEN
INSERT INTO WSR_SQLTEXT
( CTIME,
SQL_ID,
SQL_TEXT
)
VALUES( DT_L_SNAPDAY,
ITEM.SQL_ID,
STR_L_SQLTEXT
);
END IF;
IF (ITEM.EXPLAIN_ID <> '0000000000') THEN
SELECT COUNT(*)
INTO I_L_COUNT
FROM WSR_SQLPLAN
WHERE CTIME = DT_L_SNAPDAY
AND EXPLAIN_ID = ITEM.EXPLAIN_ID
AND SQL_ID = ITEM.SQL_ID;
IF (I_L_COUNT = 0) THEN
INSERT INTO WSR_SQLPLAN
( CTIME,
SQL_ID,
EXPLAIN_ID,
EXPLAIN_TEXT
)
VALUES( DT_L_SNAPDAY,
ITEM.SQL_ID,
ITEM.EXPLAIN_ID,
ITEM.EXPLAIN_TEXT
);
END IF;
END IF;
END LOOP;
INSERT INTO WSR_SNAPSHOT
( SNAP_ID,
DBID,
INSTANCE_ID,
STARTUP_TIME,
SNAP_TIME,
FLUSH_ELAPSED,
SESSIONS,
CURSORS
)
VALUES( I_L_SNAP_ID,
I_L_DBID,
I_L_INSTANCE_ID,
(SELECT STARTUP_TIME FROM DV_INSTANCE),
DT_L_STARTTIME,
NUMTODSINTERVAL((SYSDATE - CAST (DT_L_STARTTIME AS DATE)) * 86400, 'SECOND'),
(SELECT COUNT(*) FROM DV_SESSIONS),
(SELECT COUNT(*) FROM DV_OPEN_CURSORS)
);
INSERT INTO WSR_SESSION_EVENTS
( SNAP_ID,
SID,
EVENT#,
EVENT,
P1,
WAIT_CLASS,
TOTAL_WAITS,
TIME_WAITED_MIRCO
)
SELECT I_L_SNAP_ID,
SID,
EVENT#,
EVENT,
P1,
WAIT_CLASS,
TOTAL_WAITS,
TIME_WAITED_MIRCO
FROM DV_SESSION_EVENTS
WHERE WAIT_CLASS <> 'Idle';
UPDATE WSR_CONTROL SET MOST_RECENT_SNAP_TIME = DT_L_STARTTIME, MOST_RECENT_SNAP_ID = I_L_SNAP_ID;
COMMIT;
I_L_RET := RELEASE_LOCK('SYS.WSR$CREATE_SNAPSHOT');
EXCEPTION
WHEN OTHERS THEN
I_L_RET := RELEASE_LOCK('SYS.WSRINSERT_ERRORLOG(‘WSR$CREATE_SNAPSHOT’, SUBSTR(SQLERRM,1,2000));
END;
/
CREATE OR REPLACE PROCEDURE WSR$DROP_SNAPSHOT_PARTITION
(
STR_IN_TABLENAME IN VARCHAR,
I_IN_SNAPID IN INTEGER
)
AS
STR_L_PARTITION VARCHAR(200);
BEGIN
SELECT PARTITION_NAME
INTO STR_L_PARTITION
FROM ADM_TAB_PARTITIONS
WHERE TABLE_OWNER = ‘SYS’
AND TABLE_NAME = UPPER(STR_IN_TABLENAME)
AND HIGH_VALUE = TO_CHAR(I_IN_SNAPID + 1);
EXECUTE IMMEDIATE 'ALTER TABLE ' || STR_IN_TABLENAME || ' DROP PARTITION ' || STR_L_PARTITION;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
WSRDROP_SNAPSHOT_PARTITION’, SUBSTR(SQLERRM,1,2000));
END WSR$DROP_SNAPSHOT_PARTITION;
/
CREATE OR REPLACE PROCEDURE WSR$DROP_SNAPSHOT_RANGE
(
LOW_SNAP_ID IN BINARY_INTEGER,
HIGH_SNAP_ID IN BINARY_INTEGER
)
AS
BEGIN
IF (LOW_SNAP_ID IS NULL OR HIGH_SNAP_ID IS NULL) THEN
RAISE_APPLICATION_ERROR(-20000, ‘LOW_SNAP_ID & HIGH_SNAP_ID CAN’‘T BE NULL!’);
END IF;
FOR I IN LOW_SNAP_ID .. HIGH_SNAP_ID LOOP
DELETE FROM WSR_SNAPSHOT
WHERE SNAP_ID = I;
DELETE FROM WSR_SYS_STAT
WHERE SNAP_ID = I;
DELETE FROM WSR_SYSTEM
WHERE SNAP_ID = I;
DELETE FROM WSR_SYSTEM_EVENT
WHERE SNAP_ID = I;
DELETE FROM WSR_PARAMETER
WHERE SNAP_ID = I;
DELETE FROM WSR_LATCH
WHERE SNAP_ID = I;
DELETE FROM WSR_WAITSTAT
WHERE SNAP_ID = I;
DELETE FROM WSR_LIBRARYCACHE
WHERE SNAP_ID = I;
WSR$DROP_SNAPSHOT_PARTITION('WSR_SQLAREA', I);
WSR$DROP_SNAPSHOT_PARTITION('WSR_SEGMENT', I);
WSR$DROP_SNAPSHOT_PARTITION('WSR_DBA_SEGMENTS', I);
WSR$DROP_SNAPSHOT_PARTITION('WSR_LONGSQL', I);
WSR$DROP_SNAPSHOT_PARTITION('WSR_SESSION_EVENTS', I);
COMMIT;
END LOOP;
END;
/
CREATE OR REPLACE PROCEDURE WSR$DROP_CTIME_PARTITION
(
STR_IN_TABLENAME IN VARCHAR,
I_IN_DAYS IN BINARY_INTEGER
)
AS
DT_L_HIGHVALUE DATE := TRUNC(SYSDATE - I_IN_DAYS);
STR_L_HIGHVALUE VARCHAR(200);
I_L_COUNT BINARY_INTEGER;
BEGIN
FOR ITEM IN (
SELECT PARTITION_NAME, HIGH_VALUE
FROM ADM_TAB_PARTITIONS
WHERE TABLE_OWNER = ‘SYS’
AND TABLE_NAME = UPPER(STR_IN_TABLENAME)
AND PARTITION_NAME <> ‘P_0’) LOOP
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM SYS_DUMMY WHERE TO_DATE(''' || ITEM.HIGH_VALUE || ''', ''YYYY-MM-DD HH24:MI:SS'') <= :1 ' INTO I_L_COUNT USING DT_L
_HIGHVALUE;
IF (I_L_COUNT > 0) THEN
EXECUTE IMMEDIATE 'ALTER TABLE ' || STR_IN_TABLENAME || ' DROP PARTITION ' || ITEM.PARTITION_NAME;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
WSRDROP_CTIME_PARTITION’, SUBSTR(SQLERRM,1,2000));
END;
/
CREATE OR REPLACE PROCEDURE WSR$DROP_SNAPSHOT_TIME
AS
I_L_DAYS BINARY_INTEGER;
I_L_LOGDAYS BINARY_INTEGER;
BEGIN
SELECT RETENTION_NUM / 86400, LOG_DAYS
INTO I_L_DAYS, I_L_LOGDAYS
FROM WSR_CONTROL;
FOR ITEM IN (SELECT SNAP_ID FROM ADM_HIST_SNAPSHOT WHERE SNAP_TIME < SYSDATE - I_L_DAYS) LOOP
WSR$DROP_SNAPSHOT_RANGE(ITEM.SNAP_ID, ITEM.SNAP_ID);
END LOOP;
WSR$DROP_CTIME_PARTITION('WSR_SQLTEXT', I_L_DAYS);
WSR$DROP_CTIME_PARTITION('WSR_SQLPLAN', I_L_DAYS);
WSR$DROP_CTIME_PARTITION('WSR_INSTANCE_SNAP', I_L_DAYS);
WSR$DROP_CTIME_PARTITION('WSR_SESSION_SQL', I_L_DAYS);
WSR$DROP_CTIME_PARTITION('WSR_TRANSACTION', I_L_DAYS);
WSR$DROP_CTIME_PARTITION('WSR_LOCK_OBJECT', I_L_DAYS);
DELETE FROM WSR_EXCEPTION_LOG
WHERE CTIME < SYSDATE - I_L_LOGDAYS;
UPDATE WSR_CONTROL SET MOST_RECENT_PURGE_TIME = CURRENT_TIMESTAMP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
WSRDROP_SNAPSHOT_TIME’, SUBSTR(SQLERRM,1,2000));
END;
/
CREATE OR REPLACE PROCEDURE WSR$MODIFIY_SETTING
(
I_IN_INTERVAL_MINUTES IN BINARY_INTEGER DEFAULT NULL,
I_IN_RETENTION_DAYS IN BINARY_INTEGER DEFAULT NULL,
I_IN_TOPSQL IN BINARY_INTEGER DEFAULT NULL,
STR_IN_STATUS IN VARCHAR DEFAULT NULL,
STR_IN_SESSION_STATUS IN VARCHAR DEFAULT NULL,
I_IN_SESSION_INTERVAL IN BINARY_INTEGER DEFAULT NULL,
I_IN_LOG_DAYS IN BINARY_INTEGER DEFAULT NULL
)
AS
I_L_COUNT BINARY_INTEGER;
I_L_JOBNO BINARY_INTEGER;
STR_L_INTERVAL VARCHAR(1000);
I_L_SNAPINT_NUM BINARY_INTEGER;
I_SESSION_INTERVAL BINARY_INTEGER;
BEGIN
IF (I_IN_INTERVAL_MINUTES < 5 OR I_IN_INTERVAL_MINUTES > 1440) THEN
RAISE_APPLICATION_ERROR(-20000, ‘I_IN_INTERVAL_MINUTES SHOULD BETWEEN 5 AND 1440!’);
END IF;
IF (I_IN_RETENTION_DAYS < 1 OR I_IN_RETENTION_DAYS > 3000) THEN
RAISE_APPLICATION_ERROR(-20000, 'I_IN_RETENTION_DAYS SHOULD BETWEEN 1 AND 3000!');
END IF;
IF (I_IN_TOPSQL < 1 OR I_IN_TOPSQL > 1000) THEN
RAISE_APPLICATION_ERROR(-20000, 'I_IN_TOPSQL SHOULD BETWEEN 1 AND 1000!');
END IF;
IF (STR_IN_SESSION_STATUS NOT IN ('Y', 'N')) THEN
RAISE_APPLICATION_ERROR(-20000, 'STR_IN_SESSION_STATUS SHOULD BE Y OR N!');
END IF;
IF (I_IN_SESSION_INTERVAL < 1 OR I_IN_SESSION_INTERVAL > 1000) THEN
RAISE_APPLICATION_ERROR(-20000, 'I_IN_TOPSQL SHOULD BETWEEN 1 AND 1000!');
END IF;
IF (STR_IN_STATUS NOT IN ('Y', 'N')) THEN
RAISE_APPLICATION_ERROR(-20000, 'STR_IN_STATUS SHOULD BE Y OR N!');
END IF;
IF (I_IN_LOG_DAYS < 1 OR I_IN_LOG_DAYS > 1000) THEN
RAISE_APPLICATION_ERROR(-20000, 'I_IN_LOG_DAYS SHOULD BETWEEN 1 AND 1000!');
END IF;
UPDATE WSR_CONTROL
SET SNAP_INTERVAL = NVL(NUMTODSINTERVAL(I_IN_INTERVAL_MINUTES, 'MINUTE'), SNAP_INTERVAL),
SNAPINT_NUM = NVL(I_IN_INTERVAL_MINUTES * 60, SNAPINT_NUM),
RETENTION = NVL(NUMTODSINTERVAL(I_IN_RETENTION_DAYS, 'DAY'), RETENTION),
RETENTION_NUM = NVL(I_IN_RETENTION_DAYS * 86400, RETENTION_NUM),
TOPNSQL = NVL(I_IN_TOPSQL, TOPNSQL),
STATUS = NVL(STR_IN_STATUS, STATUS),
SESSION_STATUS = NVL(STR_IN_SESSION_STATUS, SESSION_STATUS),
SESSION_INTERVAL = NVL(I_IN_SESSION_INTERVAL, SESSION_INTERVAL),
LOG_DAYS = NVL(I_IN_LOG_DAYS, LOG_DAYS);
IF (I_IN_SESSION_INTERVAL IS NOT NULL) THEN
BEGIN
SELECT JOB
INTO I_L_JOBNO
FROM MY_JOBS
WHERE WHAT = 'WSR$CREATE_SESSION_SNAPSHOT();'
AND ROWNUM <= 1;
DBMS_JOB.REMOVE(I_L_JOBNO);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
STR_L_INTERVAL := 'SYSDATE + ' || I_IN_SESSION_INTERVAL || '/86400';
DBMS_JOB.SUBMIT(I_L_JOBNO, 'WSR$CREATE_SESSION_SNAPSHOT();', SYSDATE, STR_L_INTERVAL);
END IF;
IF (STR_IN_SESSION_STATUS IS NOT NULL) THEN
BEGIN
SELECT JOB
INTO I_L_JOBNO
FROM MY_JOBS
WHERE WHAT = 'WSR$CREATE_SESSION_SNAPSHOT();'
AND ROWNUM <= 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
SELECT SESSION_INTERVAL
INTO I_SESSION_INTERVAL
FROM WSR_CONTROL;
DBMS_JOB.SUBMIT(I_L_JOBNO, 'WSR$CREATE_SESSION_SNAPSHOT();', SYSDATE, 'SYSDATE + ' || TO_CHAR(I_SESSION_INTERVAL) || '/86400');
END;
IF (STR_IN_SESSION_STATUS = 'Y') THEN
DBMS_JOB.BROKEN(JOB => I_L_JOBNO, BROKEN => FALSE);
ELSE
DBMS_JOB.BROKEN(JOB => I_L_JOBNO, BROKEN => TRUE);
END IF;
END IF;
IF (I_IN_INTERVAL_MINUTES IS NOT NULL) THEN
BEGIN
SELECT JOB
INTO I_L_JOBNO
FROM MY_JOBS
WHERE WHAT = 'WSR$CREATE_SNAPSHOT();'
AND ROWNUM <= 1;
DBMS_JOB.REMOVE(I_L_JOBNO);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
STR_L_INTERVAL := 'SYSDATE + ' || I_IN_INTERVAL_MINUTES || '/1440';
DBMS_JOB.SUBMIT(I_L_JOBNO, 'WSR$CREATE_SNAPSHOT();', SYSDATE, STR_L_INTERVAL);
END IF;
IF (STR_IN_STATUS IS NOT NULL) THEN
BEGIN
SELECT JOB
INTO I_L_JOBNO
FROM MY_JOBS
WHERE WHAT = 'WSR$CREATE_SNAPSHOT();'
AND ROWNUM <= 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
SELECT SNAPINT_NUM
INTO I_L_SNAPINT_NUM
FROM WSR_CONTROL;
DBMS_JOB.SUBMIT(I_L_JOBNO, 'WSR$CREATE_SNAPSHOT();', TRUNC(SYSDATE, 'HH') + 1/24, 'SYSDATE + ' || TO_CHAR(I_L_SNAPINT_NUM) || '/86400' );
END;
IF (STR_IN_STATUS = 'Y') THEN
DBMS_JOB.BROKEN(JOB => I_L_JOBNO, BROKEN => FALSE);
ELSE
DBMS_JOB.BROKEN(JOB => I_L_JOBNO, BROKEN => TRUE);
END IF;
END IF;
COMMIT;
END;
/
CREATE OR REPLACE PROCEDURE WSRMODIFIY_SETTING(I_IN_INTERVAL_MINUTES, I_IN_RETENTION_DAYS, I_IN_TOPSQL, STR_IN_STATUS, STR_IN_SESSION_STATUS, I_IN_SESSION_INTERVAL, I_IN_LOG_DAYS);
END;
/
DECLARE
I_L_COUNT INT;
BEGIN
SELECT COUNT(*)
INTO I_L_COUNT
FROM SYS_ROLES
WHERE NAME = ‘STATISTICS’;
IF (I_L_COUNT = 0) THEN
EXECUTE IMMEDIATE 'CREATE ROLE STATISTICS';
ELSE
SELECT COUNT(*) INTO I_L_COUNT FROM ADM_TAB_PRIVS WHERE GRANTEE = 'STATISTICS' AND OBJECT_NAME = 'DBMS_JOB';
IF (I_L_COUNT > 0) THEN
EXECUTE IMMEDIATE 'REVOKE EXECUTE ON DBMS_JOB FROM STATISTICS';
END IF;
SELECT COUNT(*) INTO I_L_COUNT FROM ADM_TAB_PRIVS WHERE GRANTEE = 'STATISTICS' AND OBJECT_NAME = 'WSR$MODIFY_SETTING' AND OWNER = 'SYS';
IF (I_L_COUNT > 0) THEN
EXECUTE IMMEDIATE 'REVOKE EXECUTE ON WSR$MODIFY_SETTING FROM STATISTICS';
END IF;
SELECT COUNT(*) INTO I_L_COUNT FROM ADM_TAB_PRIVS WHERE GRANTEE = 'STATISTICS' AND OBJECT_NAME = 'WSR$MODIFIY_SETTING' AND OWNER = 'SYS';
IF (I_L_COUNT > 0) THEN
EXECUTE IMMEDIATE 'REVOKE EXECUTE ON WSR$MODIFIY_SETTING FROM STATISTICS';
END IF;
END IF;
END;
/
CREATE OR REPLACE PROCEDURE WSR$TOPSQL_ELAPSED_TIME
(
I_IN_LOW_SNAP_ID IN BINARY_INTEGER,
I_IN_HIGH_SNAP_ID IN BINARY_INTEGER,
I_IN_TOTAL IN BINARY_BIGINT,
I_IN_TOPNUM IN BINARY_INTEGER
)
AS
CUR_RESULT SYS_REFCURSOR;
BEGIN
OPEN CUR_RESULT FOR
SELECT TO_CHAR(ELAPSED_TIME / 1000000, 'FM99999999999999999990.000'),
EXECUTIONS,
TO_CHAR(ELAPSED_TIME / EXECUTIONS / 1000000, 'FM99999999999999999990.000'),
TO_CHAR(DECODE(I_IN_TOTAL, 0, 0 :: BINARY_BIGINT, ELAPSED_TIME / I_IN_TOTAL * 100), 'FM99999999999999999990.000'),
TO_CHAR(CPU_TIME / ELAPSED_TIME * 100, 'FM99999999999999999990.000'),
TO_CHAR(IO_TIME / ELAPSED_TIME * 100, 'FM99999999999999999990.000'),
SQL_TEXT || '...',
SQL_ID,
MODULE,
REPLACE((SELECT SQL_TEXT FROM WSR_SQLTEXT WHERE SQL_ID = B.SQL_ID LIMIT 1), '''', ''''''),
TO_CHAR(BUFFER_GETS / EXECUTIONS, 'FM99999999999999999990.000'),
TO_CHAR(DISK_READS / EXECUTIONS, 'FM99999999999999999990.000'),
TO_CHAR(PROCESSED_ROWS / EXECUTIONS, 'FM99999999999999999990.000'),
TO_CHAR(PARSE_TIME / 1000000, 'FM99999999999999999990.000'),
TO_CHAR(CR_GETS / EXECUTIONS, 'FM99999999999999999990.000'),
PARSING_USER_NAME,
PROGRAM_LINE#,
NVL((SELECT OBJECT_NAME FROM ADM_PROCEDURES WHERE OBJECT_ID = PROGRAM_ID LIMIT 1), ' '),
TO_CHAR(CON_WAIT_TIME / 1000000, 'FM99999999999999999990.000'),
TO_CHAR(VM_PAGES_USED / EXECUTIONS, 'FM99999999999999999990.000')
FROM (SELECT *
FROM (SELECT B.ELAPSED_TIME - NVL(A.ELAPSED_TIME, 0) ELAPSED_TIME,
B.EXECUTIONS - NVL(A.EXECUTIONS, 0) EXECUTIONS,
B.CPU_TIME - NVL(A.CPU_TIME, 0) CPU_TIME,
B.IO_WAIT_TIME - NVL(A.IO_WAIT_TIME, 0) IO_TIME,
B.SQL_ID,
B.MODULE,
B.BUFFER_GETS - NVL(A.BUFFER_GETS, 0) BUFFER_GETS,
B.DISK_READS - NVL(A.DISK_READS, 0) DISK_READS,
B.PROCESSED_ROWS - NVL(A.PROCESSED_ROWS, 0) PROCESSED_ROWS,
B.PARSE_TIME - NVL(A.PARSE_TIME, 0) PARSE_TIME,
B.CON_WAIT_TIME - NVL(A.CON_WAIT_TIME, 0) CON_WAIT_TIME,
B.CR_GETS - NVL(A.CR_GETS, 0) CR_GETS,
B.PARSING_USER_NAME,
B.PROGRAM_LINE#,
B.PROGRAM_ID,
B.SQL_TEXT,
B.VM_PAGES_USED - NVL(A.VM_PAGES_USED, 0) VM_PAGES_USED
FROM (SELECT * FROM ADM_HIST_SQLAREA WHERE SNAP_ID = I_IN_HIGH_SNAP_ID) B
LEFT JOIN ADM_HIST_SQLAREA A
ON A.SNAP_ID = I_IN_LOW_SNAP_ID
AND A.SQL_ID = B.SQL_ID)
WHERE ELAPSED_TIME > 0
AND EXECUTIONS > 0) B
ORDER BY ELAPSED_TIME DESC LIMIT I_IN_TOPNUM;
DBMS_SQL.RETURN_RESULT(CUR_RESULT);
END;
/
CREATE OR REPLACE PROCEDURE WSR$TOPSQL_LONGSQL_TIME
(
I_IN_LOW_SNAP_ID IN BINARY_INTEGER,
I_IN_HIGH_SNAP_ID IN BINARY_INTEGER,
I_IN_TOPNUM IN BINARY_INTEGER
)
AS
CUR_RESULT SYS_REFCURSOR;
BEGIN
OPEN CUR_RESULT FOR
SELECT COUNT(CASE WHEN STAGE IN ('EXECUTE', 'QUERY', 'PREP_EXEC') THEN 1 ELSE 0 END) EXECUTIONS,
TO_CHAR(SUM(ELAPSED_TIME) / 1000, 'FM99999999999999999990.000'),
TO_CHAR(MAX(CASE WHEN STAGE IN ('EXECUTE', 'QUERY', 'PREP_EXEC') THEN ELAPSED_TIME ELSE 0 END) / 1000, 'FM99999999999999999990.000') MAX_EXEC,
COUNT(CASE WHEN STAGE = 'PARSE' THEN 1 ELSE 0 END) PARSES,
TO_CHAR(SUM(CASE WHEN STAGE = 'PARSE' THEN ELAPSED_TIME ELSE 0 END)/ 1000, 'FM99999999999999999990.000') PARSE_TIME,
COUNT(CASE WHEN STAGE = 'FETCH' THEN 1 ELSE 0 END) FETCHS,
TO_CHAR(SUM(CASE WHEN STAGE = 'FETCH' THEN ELAPSED_TIME ELSE 0 END)/ 1000, 'FM99999999999999999990.000') FETCH_TIME,
SQL_ID,
MIN(SQL_TEXT) || '...',
REPLACE((SELECT SQL_TEXT FROM WSR_SQLTEXT WHERE SQL_ID = A.SQL_ID LIMIT 1), '''', '''''')
FROM ADM_HIST_LONGSQL A
WHERE SNAP_ID > I_IN_LOW_SNAP_ID AND SNAP_ID <= I_IN_HIGH_SNAP_ID
GROUP BY SQL_ID
ORDER BY SUM(ELAPSED_TIME) DESC LIMIT I_IN_TOPNUM;
DBMS_SQL.RETURN_RESULT(CUR_RESULT);
END;
/
CREATE OR REPLACE PROCEDURE WSR$TOPSQL_LONGSQL_TIME_PREFIX
(
I_IN_LOW_SNAP_ID IN BINARY_INTEGER,
I_IN_HIGH_SNAP_ID IN BINARY_INTEGER,
I_IN_TOPNUM IN BINARY_INTEGER,
I_IN_LETTER_NUM IN BINARY_INTEGER
)
AS
CUR_RESULT SYS_REFCURSOR;
BEGIN
OPEN CUR_RESULT FOR
SELECT COUNT(CASE WHEN STAGE IN ('EXECUTE', 'QUERY', 'PREP_EXEC') THEN 1 ELSE 0 END) EXECUTIONS,
TO_CHAR(SUM(ELAPSED_TIME) / 1000, 'FM99999999999999999990.000'),
TO_CHAR(MAX(CASE WHEN STAGE IN ('EXECUTE', 'QUERY', 'PREP_EXEC') THEN ELAPSED_TIME ELSE 0 END) / 1000, 'FM99999999999999999990.000') MAX_EXEC,
COUNT(CASE WHEN STAGE = 'PARSE' THEN 1 ELSE 0 END) PARSES,
TO_CHAR(SUM(CASE WHEN STAGE = 'PARSE' THEN ELAPSED_TIME ELSE 0 END)/ 1000, 'FM99999999999999999990.000') PARSE_TIME,
COUNT(CASE WHEN STAGE = 'FETCH' THEN 1 ELSE 0 END) FETCHS,
TO_CHAR(SUM(CASE WHEN STAGE = 'FETCH' THEN ELAPSED_TIME ELSE 0 END)/ 1000, 'FM99999999999999999990.000') FETCH_TIME,
SQL_TEXT
FROM( SELECT SUBSTR(SQL_TEXT, 1, I_IN_LETTER_NUM) SQL_TEXT, STAGE, ELAPSED_TIME
FROM ADM_HIST_LONGSQL A
WHERE SNAP_ID > I_IN_LOW_SNAP_ID AND SNAP_ID <= I_IN_HIGH_SNAP_ID
)
GROUP BY SQL_TEXT
ORDER BY SUM(ELAPSED_TIME) DESC LIMIT I_IN_TOPNUM;
DBMS_SQL.RETURN_RESULT(CUR_RESULT);
END;
/
CREATE OR REPLACE PROCEDURE WSR$TOPSQL_CPU_TIME
(
I_IN_LOW_SNAP_ID IN BINARY_INTEGER,
I_IN_HIGH_SNAP_ID IN BINARY_INTEGER,
I_IN_TOTAL IN BINARY_BIGINT,
I_IN_TOPNUM IN BINARY_INTEGER
)
AS
CUR_RESULT SYS_REFCURSOR;
BEGIN
OPEN CUR_RESULT FOR
SELECT TO_CHAR(CPU_TIME / 1000000, 'FM99999999999999999990.000'),
EXECUTIONS,
TO_CHAR(CPU_TIME / EXECUTIONS / 1000000, 'FM99999999999999999990.000'),
TO_CHAR(DECODE(I_IN_TOTAL, 0, 0 :: BINARY_BIGINT, CPU_TIME / I_IN_TOTAL * 100), 'FM99999999999999999990.000'),
TO_CHAR(ELAPSED_TIME / 1000000, 'FM99999999999999999990.000'),
TO_CHAR(CPU_TIME / ELAPSED_TIME * 100, 'FM99999999999999999990.000'),
TO_CHAR(IO_TIME / ELAPSED_TIME * 100, 'FM99999999999999999990.000'),
SQL_TEXT || '...',
SQL_ID,
MODULE,
REPLACE((SELECT SQL_TEXT FROM WSR_SQLTEXT WHERE SQL_ID = B.SQL_ID LIMIT 1), '''', '''''')
FROM (SELECT *
FROM (SELECT B.ELAPSED_TIME - NVL(A.ELAPSED_TIME, 0) ELAPSED_TIME,
B.EXECUTIONS - NVL(A.EXECUTIONS, 0) EXECUTIONS,
B.CPU_TIME - NVL(A.CPU_TIME, 0) CPU_TIME,
B.IO_WAIT_TIME - NVL(A.IO_WAIT_TIME, 0) IO_TIME,
B.SQL_ID,
B.SQL_TEXT,
B.MODULE
FROM (SELECT * FROM ADM_HIST_SQLAREA WHERE SNAP_ID = I_IN_HIGH_SNAP_ID) B
LEFT JOIN ADM_HIST_SQLAREA A
ON A.SNAP_ID = I_IN_LOW_SNAP_ID
AND A.SQL_ID = B.SQL_ID)
WHERE ELAPSED_TIME > 0
AND EXECUTIONS > 0) B
ORDER BY CPU_TIME DESC, 5 DESC LIMIT I_IN_TOPNUM;
DBMS_SQL.RETURN_RESULT(CUR_RESULT);
END;
/
CREATE OR REPLACE PROCEDURE WSR$TOPSQL_IO_WAIT
(
I_IN_LOW_SNAP_ID IN BINARY_INTEGER,
I_IN_HIGH_SNAP_ID IN BINARY_INTEGER,
I_IN_TOTAL IN BINARY_BIGINT,
I_IN_TOPNUM IN BINARY_INTEGER
)
AS
CUR_RESULT SYS_REFCURSOR;
BEGIN
OPEN CUR_RESULT FOR
SELECT TO_CHAR(IO_TIME / 1000000, 'FM99999999999999999990.000'),
EXECUTIONS,
TO_CHAR(IO_TIME / EXECUTIONS / 1000000, 'FM99999999999999999990.000'),
TO_CHAR(DECODE(I_IN_TOTAL, 0, 0 :: BINARY_BIGINT, IO_TIME / I_IN_TOTAL * 100), 'FM99999999999999999990.000'),
TO_CHAR(ELAPSED_TIME / 1000000, 'FM99999999999999999990.000'),
TO_CHAR(CPU_TIME / ELAPSED_TIME * 100, 'FM99999999999999999990.000'),
TO_CHAR(IO_TIME / ELAPSED_TIME * 100, 'FM99999999999999999990.000'),
SQL_TEXT || '...',
SQL_ID,
MODULE,
REPLACE((SELECT SQL_TEXT FROM WSR_SQLTEXT WHERE SQL_ID = B.SQL_ID LIMIT 1), '''', '''''')
FROM (SELECT *
FROM (SELECT B.ELAPSED_TIME - NVL(A.ELAPSED_TIME, 0) ELAPSED_TIME,
B.EXECUTIONS - NVL(A.EXECUTIONS, 0) EXECUTIONS,
B.CPU_TIME - NVL(A.CPU_TIME, 0) CPU_TIME,
B.IO_WAIT_TIME - NVL(A.IO_WAIT_TIME, 0) IO_TIME,
B.SQL_ID,
B.SQL_TEXT,
B.MODULE
FROM (SELECT * FROM ADM_HIST_SQLAREA WHERE SNAP_ID = I_IN_HIGH_SNAP_ID) B
LEFT JOIN ADM_HIST_SQLAREA A
ON A.SNAP_ID = I_IN_LOW_SNAP_ID
AND A.SQL_ID = B.SQL_ID)
WHERE ELAPSED_TIME > 0
AND EXECUTIONS > 0) B
ORDER BY IO_TIME DESC, 5 DESC LIMIT I_IN_TOPNUM;
DBMS_SQL.RETURN_RESULT(CUR_RESULT);
END;
/
CREATE OR REPLACE PROCEDURE WSR$TOPSQL_GETS
(
I_IN_LOW_SNAP_ID IN BINARY_INTEGER,
I_IN_HIGH_SNAP_ID IN BINARY_INTEGER,
I_IN_TOTAL IN BINARY_BIGINT,
I_IN_TOPNUM IN BINARY_INTEGER
)
AS
CUR_RESULT SYS_REFCURSOR;
BEGIN
OPEN CUR_RESULT FOR
SELECT BUFFER_GETS,
EXECUTIONS,
TO_CHAR(BUFFER_GETS / EXECUTIONS, 'FM99999999999999999990.000'),
TO_CHAR(DECODE(I_IN_TOTAL, 0, 0 :: BINARY_BIGINT, BUFFER_GETS / I_IN_TOTAL * 100), 'FM99999999999999999990.000'),
TO_CHAR(ELAPSED_TIME / 1000000, 'FM99999999999999999990.000'),
TO_CHAR(CPU_TIME / ELAPSED_TIME * 100, 'FM99999999999999999990.000'),
TO_CHAR(IO_TIME / ELAPSED_TIME * 100, 'FM99999999999999999990.000'),
SQL_TEXT || '...',
SQL_ID,
MODULE,
REPLACE((SELECT SQL_TEXT FROM WSR_SQLTEXT WHERE SQL_ID = B.SQL_ID LIMIT 1), '''', '''''')
FROM (SELECT *
FROM (SELECT B.ELAPSED_TIME - NVL(A.ELAPSED_TIME, 0) ELAPSED_TIME,
B.EXECUTIONS - NVL(A.EXECUTIONS, 0) EXECUTIONS,
B.CPU_TIME - NVL(A.CPU_TIME, 0) CPU_TIME,
B.BUFFER_GETS - NVL(A.BUFFER_GETS, 0) BUFFER_GETS,
B.IO_WAIT_TIME - NVL(A.IO_WAIT_TIME, 0) IO_TIME,
B.SQL_ID,
B.SQL_TEXT,
B.MODULE
FROM (SELECT * FROM ADM_HIST_SQLAREA WHERE SNAP_ID = I_IN_HIGH_SNAP_ID) B
LEFT JOIN ADM_HIST_SQLAREA A
ON A.SNAP_ID = I_IN_LOW_SNAP_ID
AND A.SQL_ID = B.SQL_ID)
WHERE ELAPSED_TIME > 0
AND EXECUTIONS > 0) B
ORDER BY 1 DESC, 5 DESC LIMIT I_IN_TOPNUM;
DBMS_SQL.RETURN_RESULT(CUR_RESULT);
END;
/
CREATE OR REPLACE PROCEDURE WSR$TOPSQL_READS
(
I_IN_LOW_SNAP_ID IN BINARY_INTEGER,
I_IN_HIGH_SNAP_ID IN BINARY_INTEGER,
I_IN_TOTAL IN BINARY_BIGINT,
I_IN_TOPNUM IN BINARY_INTEGER
)
AS
CUR_RESULT SYS_REFCURSOR;
BEGIN
OPEN CUR_RESULT FOR
SELECT DISK_READS,
EXECUTIONS,
TO_CHAR(DISK_READS / EXECUTIONS, 'FM99999999999999999990.000'),
TO_CHAR(DECODE(I_IN_TOTAL, 0, 0 :: BINARY_BIGINT, DISK_READS / I_IN_TOTAL * 100), 'FM99999999999999999990.000'),
TO_CHAR(ELAPSED_TIME / 1000000, 'FM99999999999999999990.000'),
TO_CHAR(CPU_TIME / ELAPSED_TIME * 100, 'FM99999999999999999990.000'),
TO_CHAR(IO_TIME / ELAPSED_TIME * 100, 'FM99999999999999999990.000'),
SQL_TEXT || '...',
SQL_ID,
MODULE,
REPLACE((SELECT SQL_TEXT FROM WSR_SQLTEXT WHERE SQL_ID = B.SQL_ID LIMIT 1), '''', '''''')
FROM (SELECT *
FROM (SELECT B.ELAPSED_TIME - NVL(A.ELAPSED_TIME, 0) ELAPSED_TIME,
B.EXECUTIONS - NVL(A.EXECUTIONS, 0) EXECUTIONS,
B.CPU_TIME - NVL(A.CPU_TIME, 0) CPU_TIME,
B.BUFFER_GETS - NVL(A.BUFFER_GETS, 0) BUFFER_GETS,
B.IO_WAIT_TIME - NVL(A.IO_WAIT_TIME, 0) IO_TIME,
B.DISK_READS - NVL(A.DISK_READS, 0) DISK_READS,
B.SQL_ID,
B.SQL_TEXT,
B.MODULE
FROM (SELECT * FROM ADM_HIST_SQLAREA WHERE SNAP_ID = I_IN_HIGH_SNAP_ID) B
LEFT JOIN ADM_HIST_SQLAREA A
ON A.SNAP_ID = I_IN_LOW_SNAP_ID
AND A.SQL_ID = B.SQL_ID)
WHERE ELAPSED_TIME > 0
AND EXECUTIONS > 0) B
ORDER BY 1 DESC, 5 DESC LIMIT I_IN_TOPNUM;
DBMS_SQL.RETURN_RESULT(CUR_RESULT);
END;
/
CREATE OR REPLACE PROCEDURE WSR$TOPSQL_EXECUTIONS
(
I_IN_LOW_SNAP_ID IN BINARY_INTEGER,
I_IN_HIGH_SNAP_ID IN BINARY_INTEGER,
I_IN_TOPNUM IN BINARY_INTEGER
)
AS
CUR_RESULT SYS_REFCURSOR;
BEGIN
OPEN CUR_RESULT FOR
SELECT EXECUTIONS,
PROCESSED_ROWS,
TO_CHAR(PROCESSED_ROWS / EXECUTIONS, 'FM99999999999999999990.000'),
TO_CHAR(ELAPSED_TIME / 1000000, 'FM99999999999999999990.000'),
TO_CHAR(CPU_TIME / ELAPSED_TIME * 100, 'FM99999999999999999990.000'),
TO_CHAR(IO_TIME / ELAPSED_TIME * 100, 'FM99999999999999999990.000'),
SQL_TEXT || '...',
SQL_ID,
MODULE,
REPLACE(SQL_TEXT, '''', '''''')
FROM (SELECT *
FROM (SELECT B.ELAPSED_TIME - NVL(A.ELAPSED_TIME, 0) ELAPSED_TIME,
B.EXECUTIONS - NVL(A.EXECUTIONS, 0) EXECUTIONS,
B.CPU_TIME - NVL(A.CPU_TIME, 0) CPU_TIME,
B.BUFFER_GETS - NVL(A.BUFFER_GETS, 0) BUFFER_GETS,
B.IO_WAIT_TIME - NVL(A.IO_WAIT_TIME, 0) IO_TIME,
B.PROCESSED_ROWS - NVL(A.PROCESSED_ROWS, 0) PROCESSED_ROWS,
B.SQL_TEXT,
B.SQL_ID,
B.MODULE
FROM (SELECT * FROM ADM_HIST_SQLAREA WHERE SNAP_ID = I_IN_HIGH_SNAP_ID) B
LEFT JOIN ADM_HIST_SQLAREA A
ON A.SNAP_ID = I_IN_LOW_SNAP_ID
AND A.SQL_ID = B.SQL_ID)
WHERE ELAPSED_TIME > 0
AND EXECUTIONS > 0)
ORDER BY 1 DESC, 4 DESC LIMIT I_IN_TOPNUM;
DBMS_SQL.RETURN_RESULT(CUR_RESULT);
END;
/
CREATE OR REPLACE PROCEDURE WSR$TOPSQL_PARSES
(
I_IN_LOW_SNAP_ID IN BINARY_INTEGER,
I_IN_HIGH_SNAP_ID IN BINARY_INTEGER,
I_IN_TOTAL IN BINARY_BIGINT,
I_IN_TOPNUM IN BINARY_INTEGER
)
AS
CUR_RESULT SYS_REFCURSOR;
BEGIN
OPEN CUR_RESULT FOR
SELECT PARSE_CALLS,
EXECUTIONS,
TO_CHAR(DECODE(I_IN_TOTAL, 0, 0 :: BINARY_BIGINT, PARSE_CALLS / I_IN_TOTAL * 100), 'FM99999999999999999990.000'),
SQL_TEXT || '...',
SQL_ID,
MODULE,
REPLACE(SQL_TEXT, '''', '''''')
FROM (SELECT *
FROM (SELECT B.ELAPSED_TIME - NVL(A.ELAPSED_TIME, 0) ELAPSED_TIME,
B.EXECUTIONS - NVL(A.EXECUTIONS, 0) EXECUTIONS,
B.CPU_TIME - NVL(A.CPU_TIME, 0) CPU_TIME,
B.BUFFER_GETS - NVL(A.BUFFER_GETS, 0) BUFFER_GETS,
B.IO_WAIT_TIME - NVL(A.IO_WAIT_TIME, 0) IO_TIME,
B.PARSE_CALLS - NVL(A.PARSE_CALLS, 0) PARSE_CALLS,
B.SQL_TEXT,
B.SQL_ID,
B.MODULE
FROM (SELECT * FROM ADM_HIST_SQLAREA WHERE SNAP_ID = I_IN_HIGH_SNAP_ID) B
LEFT JOIN ADM_HIST_SQLAREA A
ON A.SNAP_ID = I_IN_LOW_SNAP_ID
AND A.SQL_ID = B.SQL_ID)
WHERE ELAPSED_TIME > 0
AND EXECUTIONS > 0)
ORDER BY 1 DESC, 2 DESC LIMIT I_IN_TOPNUM;
DBMS_SQL.RETURN_RESULT(CUR_RESULT);
END;
/
CREATE OR REPLACE PROCEDURE WSR$TOPSESSION_TOPEVENT
(
I_IN_LOW_SNAP_ID IN BINARY_INTEGER,
I_IN_HIGH_SNAP_ID IN BINARY_INTEGER,
I_IN_TOPNUM IN BINARY_INTEGER,
I_IN_SID IN BINARY_INTEGER
)
AS
CUR_RESULT SYS_REFCURSOR;
BEGIN
OPEN CUR_RESULT FOR
SELECT EVENT,
TO_CHAR(TIME_WAITED_MIRCO/1000000, ‘FM99999999999999999990.000’),
TOTAL_WAITS,
TO_CHAR(TIME_WAITED_MIRCO/TOTAL_WAITS/1000, ‘FM99999999999999999990.000’)
FROM (SELECT *
FROM (SELECT B.TOTAL_WAITS - NVL(A.TOTAL_WAITS, 0) TOTAL_WAITS,
B.TIME_WAITED_MIRCO - NVL(A.TIME_WAITED_MIRCO, 0) TIME_WAITED_MIRCO,
B.EVENT
FROM (SELECT * FROM SYS.WSR_SESSION_EVENTS WHERE SNAP_ID = I_IN_HIGH_SNAP_ID AND SID = I_IN_SID) B
LEFT JOIN (SELECT * FROM SYS.WSR_SESSION_EVENTS WHERE SNAP_ID = I_IN_LOW_SNAP_ID AND SID = I_IN_SID) A
ON A.EVENT = B.EVENT)
WHERE TOTAL_WAITS > 0
AND TIME_WAITED_MIRCO > 0)
ORDER BY TIME_WAITED_MIRCO DESC LIMIT I_IN_TOPNUM;
DBMS_SQL.RETURN_RESULT(CUR_RESULT);
END;
/
CREATE OR REPLACE PROCEDURE WSR$TOPSESSION
(
I_IN_LOW_SNAP_ID IN BINARY_INTEGER,
I_IN_HIGH_SNAP_ID IN BINARY_INTEGER,
I_IN_TOPNUM IN BINARY_INTEGER
)
AS
CUR_RESULT SYS_REFCURSOR;
BEGIN
OPEN CUR_RESULT FOR
SELECT SID,
TO_CHAR(TIME_WAITED_MIRCO/1000000, 'FM99999999999999999990.000'),
TOTAL_WAITS
FROM (SELECT *
FROM (SELECT B.TOTAL_WAITS - NVL(A.TOTAL_WAITS, 0) TOTAL_WAITS,
B.TIME_WAITED_MIRCO - NVL(A.TIME_WAITED_MIRCO, 0) TIME_WAITED_MIRCO,
B.SID
FROM (SELECT SID, SUM(TOTAL_WAITS) TOTAL_WAITS, SUM(TIME_WAITED_MIRCO) TIME_WAITED_MIRCO FROM SYS.WSR_SESSION_EVENTS WHERE SNAP_ID = I_IN
_HIGH_SNAP_ID GROUP BY SID) B
LEFT JOIN (SELECT SID, SUM(TOTAL_WAITS) TOTAL_WAITS, SUM(TIME_WAITED_MIRCO) TIME_WAITED_MIRCO FROM SYS.WSR_SESSION_EVENTS WHERE SNAP_ID =
I_IN_LOW_SNAP_ID GROUP BY SID) A
ON A.SID = B.SID)
WHERE TOTAL_WAITS > 0
AND TIME_WAITED_MIRCO > 0)
ORDER BY TIME_WAITED_MIRCO DESC LIMIT I_IN_TOPNUM;
DBMS_SQL.RETURN_RESULT(CUR_RESULT);
END;
/
CREATE OR REPLACE PROCEDURE WSR$QUERY_TOPSESSION_SQL
(
I_IN_LOW_SNAP_ID IN BINARY_INTEGER,
I_IN_HIGH_SNAP_ID IN BINARY_INTEGER,
I_IN_TOPNUM IN BINARY_INTEGER
)
AS
CUR_RESULT SYS_REFCURSOR;
DT_L_STARTTIME DATE;
DT_L_ENDTIME DATE;
BEGIN
SELECT SNAP_TIME
INTO DT_L_STARTTIME
FROM ADM_HIST_SNAPSHOT
WHERE SNAP_ID = I_IN_LOW_SNAP_ID;
SELECT SNAP_TIME
INTO DT_L_ENDTIME
FROM ADM_HIST_SNAPSHOT
WHERE SNAP_ID = I_IN_HIGH_SNAP_ID;
OPEN CUR_RESULT FOR
SELECT NVL(TO_CHAR((CTIME - SQL_EXEC_START) *86400, 'FM99999999999999999990.000'), ' '),
SID,
NVL(TO_CHAR(SQL_ID), ' '),
CURR_SCHEMA,
CLIENT_IP,
NVL(TRIM(PROGRAM), ' '),
AUTO_COMMIT,
TO_CHAR(LOGON_TIME, 'YYYY-MM-DD HH24:MI:SS'),
NVL(TO_CHAR(WAIT_SID), ' '),
NVL(TO_CHAR(SQL_EXEC_START, 'YYYY-MM-DD HH24:MI:SS'), ' '),
TO_CHAR(CTIME, 'YYYY-MM-DD HH24:MI:SS'),
MODULE,
EVENT,
(SELECT SUBSTR(SQL_TEXT, 1, 30) FROM WSR_SQLTEXT WHERE SQL_ID = A.SQL_ID LIMIT 1) || '...'
FROM (SELECT CTIME,
SID,
SQL_ID,
CURR_SCHEMA,
CLIENT_IP,
PROGRAM,
AUTO_COMMIT,
LOGON_TIME,
WAIT_SID,
SQL_EXEC_START,
MODULE,
EVENT,
ROW_NUMBER() OVER(PARTITION BY SID, SQL_ID, SQL_EXEC_START ORDER BY CTIME DESC) RK
FROM WSR_SESSION_SQL
WHERE CTIME BETWEEN DT_L_STARTTIME AND DT_L_ENDTIME
AND SQL_EXEC_START IS NOT NULL) A
WHERE RK = 1
ORDER BY CTIME - SQL_EXEC_START DESC LIMIT I_IN_TOPNUM;
DBMS_SQL.RETURN_RESULT(CUR_RESULT);
END;
/
CREATE OR REPLACE PROCEDURE WSR$QUERY_TRANSACTION
(
I_IN_LOW_SNAP_ID IN BINARY_INTEGER,
I_IN_HIGH_SNAP_ID IN BINARY_INTEGER,
I_IN_TOPNUM IN BINARY_INTEGER
)
AS
CUR_RESULT SYS_REFCURSOR;
DT_L_STARTTIME DATE;
DT_L_ENDTIME DATE;
BEGIN
SELECT SNAP_TIME
INTO DT_L_STARTTIME
FROM ADM_HIST_SNAPSHOT
WHERE SNAP_ID = I_IN_LOW_SNAP_ID;
SELECT SNAP_TIME
INTO DT_L_ENDTIME
FROM ADM_HIST_SNAPSHOT
WHERE SNAP_ID = I_IN_HIGH_SNAP_ID;
OPEN CUR_RESULT FOR
SELECT NVL(TO_CHAR((SNAP_TIME - BEGIN_TIME) *86400, 'FM99999999999999999990.000'), ' '),
SID,
UNDO_COUNT,
TO_CHAR(BEGIN_TIME, 'YYYY-MM-DD HH24:MI:SS'),
NVL(TO_CHAR(SNAP_TIME, 'YYYY-MM-DD HH24:MI:SS'), ' '),
NVL(SQL_ID, ' '),
(SELECT SUBSTR(SQL_TEXT, 1, 30) FROM WSR_SQLTEXT WHERE SQL_ID = A.SQL_ID LIMIT 1) || '...',
NVL((SELECT GROUP_CONCAT(OBJECT_NAME) FROM (SELECT OBJECT_NAME FROM WSR_LOCK_OBJECT WHERE SNAP_TIME = A.SNAP_TIME AND SID = A.SID LIMIT 20)), ' 
')
FROM( SELECT (SELECT SQL_ID FROM WSR_SESSION_SQL WHERE CTIME = SNAP_TIME LIMIT 1) SQL_ID,
SNAP_TIME,
SID,
UNDO_COUNT,
BEGIN_TIME
FROM (SELECT SNAP_TIME,
SID,
UNDO_COUNT,
BEGIN_TIME,
ROW_NUMBER() OVER(PARTITION BY SID, BEGIN_TIME ORDER BY SNAP_TIME DESC) RK
FROM WSR_TRANSACTION
WHERE SNAP_TIME BETWEEN DT_L_STARTTIME AND DT_L_ENDTIME) A
WHERE RK = 1
ORDER BY SNAP_TIME - BEGIN_TIME DESC LIMIT I_IN_TOPNUM
) A;
DBMS_SQL.RETURN_RESULT(CUR_RESULT);
END;
/
CREATE OR REPLACE PROCEDURE WSR$WRITE_INSTANCE_SNAP
AS
I_L_SESSION_NUM BINARY_INTEGER;
I_L_ACTIVE_SESS_NUM BINARY_INTEGER;
I_L_TS_SYSTEM_FREE BINARY_BIGINT;
I_L_TS_SYSWARE_FREE BINARY_BIGINT;
I_L_TS_USER_FREE BINARY_BIGINT;
I_L_TXN_PAGES BINARY_INTEGER;
I_L_UNDO_PAGES BINARY_INTEGER;
BEGIN
SELECT COUNT(*), COUNT(DECODE(STATUS, ‘ACTIVE’, 1, NULL))
INTO I_L_SESSION_NUM, I_L_ACTIVE_SESS_NUM
FROM DV_SESSIONS;
SELECT SUM(DECODE(TABLESPACE_NAME, 'SYSTEM', TOTAL_SIZE - USED_SIZE, 0)), SUM(DECODE(TABLESPACE_NAME, 'SYSWARE', TOTAL_SIZE - USED_SIZE, 0)),
SUM(CASE WHEN TABLESPACE_NAME IN ('SYSTEM', 'SYSWARE', 'TEMP', 'UNDO', 'TEMP2', 'TEMP2_UNDO') THEN 0 ELSE TOTAL_SIZE - USED_SIZE END)
INTO I_L_TS_SYSTEM_FREE, I_L_TS_SYSWARE_FREE, I_L_TS_USER_FREE
FROM ADM_TABLESPACES;
SELECT SUM(TXN_PAGES), SUM(UNDO_PAGES)
INTO I_L_TXN_PAGES, I_L_UNDO_PAGES
FROM DV_UNDO_SEGMENTS;
INSERT INTO WSR_INSTANCE_SNAP
( SNAP_TIME,
SESSION_NUM,
ACTIVE_SESS_NUM,
LOCK_NUM,
TRANSACTION_NUM,
TEMP_BUFFER_FREE,
DATA_BUFFER_PIN,
TS_SYSTEM_FREE,
TS_SYSWARE_FREE,
TS_USER_FREE,
TXN_PAGES,
UNDO_PAGES
)
VALUES( SYSDATE,
I_L_SESSION_NUM,
I_L_ACTIVE_SESS_NUM,
(SELECT COUNT(*) FROM DV_LOCKS),
(SELECT COUNT(*) FROM DV_TRANSACTIONS),
(SELECT SUM(FREE_PAGES) FROM DV_TEMP_POOLS),
(SELECT SUM(CNUM_PINNED) FROM DV_BUFFER_POOL_STATS),
I_L_TS_SYSTEM_FREE,
I_L_TS_SYSWARE_FREE,
I_L_TS_USER_FREE,
I_L_TXN_PAGES,
I_L_UNDO_PAGES
);
COMMIT;
END;
/
CREATE OR REPLACE PROCEDURE WSRCREATE_SESSION_SNAPSHOT
AS
DT_L_CURRENT DATE := SYSDATE;
DT_L_DAY DATE := TRUNC(SYSDATE);
I_L_COUNT BINARY_INTEGER;
I_L_RET BINARY_INTEGER;
BEGIN
IF (GET_LOCK('SYS.WSRCREATE_SNAPSHOT’) <> 1) THEN
RETURN;
END IF;
SELECT COUNT(*)
INTO I_L_COUNT
FROM WSR_CONTROL
WHERE STATUS = 'Y'
AND SESSION_STATUS = 'Y';
IF (I_L_COUNT = 0) THEN
RETURN;
END IF;
FOR ITEM IN (SELECT * FROM DV_SESSIONS WHERE STATUS = 'ACTIVE' AND (CLIENT_IP <> '0.0.0.0' OR EVENT <> 'idle wait')) LOOP
INSERT INTO SYS.WSR_SESSION_SQL
( CTIME,
SID,
SQL_ID,
CURR_SCHEMA,
CLIENT_IP,
PROGRAM,
AUTO_COMMIT,
LOGON_TIME,
WAIT_SID,
SQL_EXEC_START,
MODULE,
EVENT
)
VALUES( DT_L_CURRENT,
ITEM.SID,
ITEM.SQL_ID,
ITEM.CURR_SCHEMA,
ITEM.CLIENT_IP,
ITEM.PROGRAM,
ITEM.AUTO_COMMIT,
ITEM.LOGON_TIME,
ITEM.WAIT_SID,
ITEM.SQL_EXEC_START,
ITEM.MODULE,
ITEM.EVENT
);
IF (ITEM.SQL_ID IS NOT NULL) THEN
SELECT COUNT(*)
INTO I_L_COUNT
FROM SYS.WSR_SQLTEXT
WHERE CTIME = DT_L_DAY
AND SQL_ID = ITEM.SQL_ID;
IF (I_L_COUNT = 0) THEN
INSERT INTO SYS.WSR_SQLTEXT
( CTIME,
SQL_ID,
SQL_TEXT
)
VALUES( DT_L_DAY,
ITEM.SQL_ID,
ITEM.CURRENT_SQL
);
END IF;
END IF;
END LOOP;
INSERT INTO WSR_TRANSACTION
( SNAP_TIME,
SID,
UNDO_COUNT,
BEGIN_TIME
)
SELECT DT_L_CURRENT,
SID,
UNDO_COUNT,
BEGIN_TIME
FROM DV_TRANSACTIONS;
INSERT INTO WSR_LOCK_OBJECT
( SNAP_TIME,
SID,
OBJECT_NAME,
LMODE
)
SELECT DT_L_CURRENT,
SESSION_ID,
OBJECT_NAME,
LMODE
FROM DV_LOCKED_OBJECTS;
WSR$WRITE_INSTANCE_SNAP;
I_L_RET := RELEASE_LOCK('SYS.WSR$CREATE_SNAPSHOT');
EXCEPTION
WHEN OTHERS THEN
I_L_RET := RELEASE_LOCK('SYS.WSRINSERT_ERRORLOG(‘WSR$CREATE_SESSION_SNAPSHOT’, SUBSTR(SQLERRM,1,2000));
END;
/
CREATE OR REPLACE PROCEDURE WSR$INSTANCE_SNAP
(
I_IN_LOW_SNAP_ID IN BINARY_INTEGER,
I_IN_HIGH_SNAP_ID IN BINARY_INTEGER
)
AS
CUR_RESULT SYS_REFCURSOR;
DT_L_STARTTIME DATE;
DT_L_ENDTIME DATE;
BEGIN
SELECT SNAP_TIME
INTO DT_L_STARTTIME
FROM ADM_HIST_SNAPSHOT
WHERE SNAP_ID = I_IN_LOW_SNAP_ID;
SELECT SNAP_TIME
INTO DT_L_ENDTIME
FROM ADM_HIST_SNAPSHOT
WHERE SNAP_ID = I_IN_HIGH_SNAP_ID;
OPEN CUR_RESULT FOR
SELECT TO_CHAR(SNAP_TIME, 'YYYY-MM-DD HH24:MI:SS'),
SESSION_NUM,
ACTIVE_SESS_NUM,
LOCK_NUM,
TRANSACTION_NUM,
TEMP_BUFFER_FREE,
DATA_BUFFER_PIN,
TS_SYSTEM_FREE,
TS_SYSWARE_FREE,
TS_USER_FREE,
TXN_PAGES,
UNDO_PAGES
FROM SYS.WSR_INSTANCE_SNAP
WHERE SNAP_TIME BETWEEN DT_L_STARTTIME AND DT_L_ENDTIME
ORDER BY SNAP_TIME LIMIT 1000;
DBMS_SQL.RETURN_RESULT(CUR_RESULT);
END;
/
DECLARE
I_L_JOBNO BINARY_INTEGER;
I_L_SNAPINT_NUM BINARY_INTEGER;
STR_L_STATUS VARCHAR(3);
BEGIN
FOR ITEM IN (
SELECT JOB
FROM MY_JOBS
WHERE WHAT = ‘WSR$CREATE_SNAPSHOT();’ ) LOOP
DBMS_JOB.REMOVE(ITEM.JOB);
END LOOP;
SELECT SNAPINT_NUM, STATUS
INTO I_L_SNAPINT_NUM, STR_L_STATUS
FROM WSR_CONTROL;
DBMS_JOB.SUBMIT(I_L_JOBNO, 'WSR$CREATE_SNAPSHOT();', TRUNC(SYSDATE, 'HH') + 1/24, 'SYSDATE + ' || TO_CHAR(I_L_SNAPINT_NUM) || '/86400' );
IF (STR_L_STATUS = 'N') THEN
DBMS_JOB.BROKEN(I_L_JOBNO, TRUE);
END IF;
COMMIT;
END;
/
DECLARE
I_L_JOBNO BINARY_INTEGER;
BEGIN
FOR ITEM IN (
SELECT JOB
FROM MY_JOBS
WHERE WHAT = ‘WSR$DROP_SNAPSHOT_TIME();’ ) LOOP
DBMS_JOB.REMOVE(ITEM.JOB);
END LOOP;
DBMS_JOB.SUBMIT(I_L_JOBNO, 'WSR$DROP_SNAPSHOT_TIME();', TRUNC(SYSDATE) + 1, 'TRUNC(SYSDATE) + 1');
COMMIT;
END;
/
DECLARE
I_L_JOBNO BINARY_INTEGER;
I_SESSION_INTERVAL BINARY_INTEGER;
STR_L_SESSION_STATUS VARCHAR(3);
BEGIN
FOR ITEM IN (
SELECT JOB
FROM MY_JOBS
WHERE WHAT = ‘WSR$CREATE_SESSION_SNAPSHOT();’ ) LOOP
DBMS_JOB.REMOVE(ITEM.JOB);
END LOOP;
SELECT SESSION_INTERVAL, SESSION_STATUS
INTO I_SESSION_INTERVAL, STR_L_SESSION_STATUS
FROM WSR_CONTROL
LIMIT 1;
DBMS_JOB.SUBMIT(I_L_JOBNO, 'WSR$CREATE_SESSION_SNAPSHOT();', SYSDATE, 'SYSDATE + ' || TO_CHAR(I_SESSION_INTERVAL) || '/86400');
IF (STR_L_SESSION_STATUS = 'N') THEN
DBMS_JOB.BROKEN(I_L_JOBNO, TRUE);
END IF;
COMMIT;
END;
/
GRANT EXECUTE ON SYS.WSR TO STATISTICS
/
GRANT SELECT ON SYS.DV_INSTANCE TO STATISTICS
/
GRANT SELECT ON SYS.DV_DATABASE TO STATISTICS
/
GRANT UPDATE, INSERT, SELECT ON SYS.WSR_CONTROL TO STATISTICS
/
GRANT INSERT, SELECT, DELETE ON SYS.WSR_SYS_STAT TO STATISTICS
/
GRANT INSERT, SELECT, DELETE ON SYS.WSR_SYSTEM TO STATISTICS
/
GRANT INSERT, SELECT, DELETE ON SYS.WSR_SYSTEM_EVENT TO STATISTICS
/
GRANT SELECT ON SYS.DV_SYS_STATS TO STATISTICS
/
GRANT SELECT ON SYS.DV_SYSTEM TO STATISTICS
/
GRANT SELECT ON SYS.DV_SYS_EVENTS TO STATISTICS
/
GRANT INSERT, SELECT ON SYS.WSR_SQLAREA TO STATISTICS
/
GRANT SELECT ON SYS.DV_SQLS TO STATISTICS
/
GRANT INSERT, SELECT, DELETE ON SYS.WSR_PARAMETER TO STATISTICS
/
GRANT SELECT ON SYS.DV_PARAMETERS TO STATISTICS
/
GRANT INSERT, SELECT, DELETE ON SYS.WSR_LATCH TO STATISTICS
/
GRANT SELECT ON SYS.DV_LATCHS TO STATISTICS
/
GRANT INSERT, SELECT, DELETE ON SYS.WSR_WAITSTAT TO STATISTICS
/
GRANT SELECT ON SYS.DV_WAIT_STATS TO STATISTICS
/
GRANT INSERT, SELECT, DELETE ON SYS.WSR_LIBRARYCACHE TO STATISTICS
/
GRANT SELECT ON SYS.DV_LIBRARY_CACHE TO STATISTICS
/
GRANT INSERT, SELECT ON SYS.WSR_SEGMENT TO STATISTICS
/
GRANT SELECT ON SYS.DV_SEGMENT_STATS TO STATISTICS
/
GRANT INSERT, SELECT, DELETE ON SYS.WSR_SNAPSHOT TO STATISTICS
/
GRANT SELECT ON SYS.DV_SESSIONS TO STATISTICS
/
GRANT SELECT ON SYS.DV_OPEN_CURSORS TO STATISTICS
/
GRANT INSERT, SELECT ON SYS.WSR_DBA_SEGMENTS TO STATISTICS
/
GRANT SELECT ON SYS.ADM_SEGMENTS TO STATISTICS
/
GRANT SELECT ON SYS.ADM_HIST_SQLAREA TO STATISTICS
/
GRANT SELECT ON SYS.ADM_HIST_SNAPSHOT TO STATISTICS
/
GRANT SELECT ON SYS.ADM_HIST_WR_CONTROL TO STATISTICS
/
GRANT SELECT ON SYS.ADM_HIST_SYSSTAT TO STATISTICS
/
GRANT SELECT ON SYS.ADM_HIST_SYSTEM_EVENT TO STATISTICS
/
GRANT SELECT ON SYS.ADM_HIST_SYSTEM TO STATISTICS
/
GRANT EXECUTE ON SYS.WSR$INSERT_SQL_LIST TO STATISTICS
/
GRANT SELECT ON SYS.ADM_HIST_PARAMETER TO STATISTICS
/
GRANT INSERT, SELECT ON SYS.WSR_SQL_LIST TO STATISTICS
/
GRANT INSERT, SELECT ON SYS.WSR_SQL_LIST_PLAN TO STATISTICS
/
GRANT EXECUTE ON DBMS_JOB TO STATISTICS
/
GRANT SELECT ON SYS.ADM_HIST_LONGSQL TO STATISTICS
/
GRANT SELECT ON SYS.DV_LONG_SQL TO STATISTICS
/
GRANT INSERT, SELECT ON SYS.WSR_LONGSQL TO STATISTICS
/
GRANT INSERT, SELECT ON SYS.WSR_SQLTEXT TO STATISTICS
/
GRANT INSERT, SELECT ON SYS.WSR_SQLPLAN TO STATISTICS
/
GRANT EXECUTE ON WSRTOPSQL_LONGSQL_TIME TO STATISTICS
/
GRANT EXECUTE ON WSRTOPSQL_CPU_TIME TO STATISTICS
/
GRANT EXECUTE ON WSRTOPSQL_GETS TO STATISTICS
/
GRANT EXECUTE ON WSRTOPSQL_EXECUTIONS TO STATISTICS
/
GRANT EXECUTE ON WSR$TOPSQL_PARSES TO STATISTICS
/
GRANT STATISTICS TO DBA
/
GRANT INSERT, SELECT ON SYS.WSR_INSTANCE_SNAP TO STATISTICS
/
GRANT INSERT, SELECT ON SYS.WSR_SESSION_SQL TO STATISTICS
/
GRANT INSERT, SELECT ON SYS.WSR_EXCEPTION_LOG TO STATISTICS
/
GRANT INSERT, SELECT ON SYS.WSR_SESSION_EVENTS TO STATISTICS
/
GRANT INSERT, SELECT ON SYS.WSR_TRANSACTION TO STATISTICS
/
GRANT EXECUTE ON WSRTOPSESSION_TOPEVENT TO STATISTICS
/
GRANT EXECUTE ON WSRQUERY_TOPSESSION_SQL TO STATISTICS
/
GRANT EXECUTE ON WSRCREATE_SESSION_SNAPSHOT TO STATISTICS
/
GRANT EXECUTE ON WSR$INSTANCE_SNAP TO STATISTICS
/
GRANT EXECUTE ON WSR$QUERY_TRANSACTION TO STATISTICS
/
GRANT SELECT ON ADM_TABLESPACES TO STATISTICS
/
GRANT SELECT ON DV_UNDO_SEGMENTS TO STATISTICS
/
GRANT SELECT ON DV_LOCKS TO STATISTICS
/
GRANT SELECT ON DV_TRANSACTIONS TO STATISTICS
/
GRANT SELECT ON DV_TEMP_POOLS TO STATISTICS
/
GRANT SELECT ON DV_BUFFER_POOL_STATS TO STATISTICS
/
GRANT SELECT ON DV_SESSION_EVENTS TO STATISTICS
/
GRANT SELECT ON DV_LOCKED_OBJECTS TO STATISTICS
/
BEGIN
EXECUTE IMMEDIATE ‘REVOKE EXECUTE ON SYS.WSRDROP_SNAPSHOT_PARTITION FROM STATISTICS’;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
BEGIN
EXECUTE IMMEDIATE ‘REVOKE EXECUTE ON WSRDROP_SNAPSHOT_TIME FROM STATISTICS’;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
