Skript zum Testen von Deferred Statistics in Oracle 11g

Skript zum Testen von Deferred Statistics in Oracle 11g. Ab Oracle 11g können nicht veröffentlichte Statistiken für Objekte gesammelt werden. Nachdem diese Statistiken getestet und für gut befunden wurden, können sie veröffentlicht werden.

CONN HR/hr
SET ECHO OFF
SET LINESIZE 1000
COL PUBLISH_STATE FORMAT A20
ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS'
/

SET ECHO ON
CL SCR

CREATE SEQUENCE SQ_PUBLISHSTATS
/

CREATE TABLE TAB_PUBLISHSTATS
(
    S1 NUMBER,
    S2 VARCHAR2(2000)
)
TABLESPACE USERS
/

EXEC DBMS_STATS.SET_TABLE_PREFS('HR','TAB_PUBLISHSTATS','PUBLISH','TRUE')

BEGIN
    FOR I IN 1..100000
    LOOP
        INSERT INTO TAB_PUBLISHSTATS VALUES(1,'TESTWERT1TESTWERT1TESTWERT1TESTWERT1');
    END LOOP;
    COMMIT;
END;
/

CREATE INDEX IDX_PUBLISHSTATS ON TAB_PUBLISHSTATS(s1)
TABLESPACE USERS
/

EXEC DBMS_STATS.GATHER_TABLE_STATS('HR','TAB_PUBLISHSTATS', CASCADE=>TRUE)
PAUSE

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT *
FROM TAB_PUBLISHSTATS
WHERE S1=1
/
SET AUTOTRACE OFF
PAUSE

UPDATE TAB_PUBLISHSTATS SET S1=SQ_PUBLISHSTATS.NEXTVAL
/
COMMIT
/

EXEC DBMS_STATS.SET_TABLE_PREFS('HR','TAB_PUBLISHSTATS','PUBLISH','FALSE')
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR','TAB_PUBLISHSTATS')

PAUSE

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT *
FROM TAB_PUBLISHSTATS
WHERE S1=1
/
SET AUTOTRACE OFF
PAUSE

ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS=TRUE
/

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT *
FROM TAB_PUBLISHSTATS
WHERE S1=1
/
SET AUTOTRACE OFF
ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS=FALSE
/
PAUSE

EXEC DBMS_STATS.PUBLISH_PENDING_STATS('HR','TAB_PUBLISHSTATS')
PAUSE

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT *
FROM TAB_PUBLISHSTATS
WHERE S1=1
/
SET AUTOTRACE OFF

DROP TABLE TAB_PUBLISHSTATS PURGE;
DROP SEQUENCE SQ_PUBLISHSTATS;