Testen von ADOP (Automatic Degree of Parellelism) in Oracle 11g

CONN / AS SYSDBA
SET LINESIZE 300
SET ECHO ON


ALTER SESSION SET PARALLEL_DEGREE_POLICY=AUTO;

CREATE TABLE TESTDOP (S1 NUMBER(8), S2 VARCHAR2(30), S3 VARCHAR2(30)) TABLESPACE USERS;
PAUSE
BEGIN
FOR I IN 1..1000000
LOOP
    INSERT INTO TESTDOP VALUES(I,'DAS IST WERT '||I, 'HIER AUCH NOCHMAL '||I);
END LOOP;
COMMIT;
END;
/
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','TESTDOP')
PAUSE

CREATE TABLE TESTDOP2 (S1 NUMBER(8), S2 VARCHAR2(30), S3 VARCHAR2(30)) TABLESPACE USERS;
PAUSE
BEGIN
FOR I IN 1..10000
LOOP
        INSERT INTO TESTDOP VALUES(I,'DAS IST WERT '||I, 'HIER AUCH NOCHMAL '||I);
END LOOP;
COMMIT;
END;
/
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','TESTDOP')
PAUSE
ALTER SESSION SET PARALLEL_DEGREE_POLICY=AUTO;
PAUSE
SET AUTOTRACE TRACEONLY EXPLAIN
PAUSE
SELECT COUNT(*) FROM TESTDOP;
PAUSE
SELECT COUNT(*) FROM TESTDOP2;
PAUSE
ALTER SESSION SET PARALLEL_DEGREE_POLICY=MANUAL;
PAUSE
SELECT COUNT(*) FROM TESTDOP;
PAUSE
SET AUTOTRACE OFF
DROP TABLE TESTDOP PURGE;
DROP TABLE TESTDOP2 PURGE;