December 18, 2010 (Modified December 19, 2010)
(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)
The previous blog article in this series resulted in several interesting comments with very useful advice. But was the performance issue just an isolated case, one that only happens in one database, with one Oracle Database release version? We need a test case to determine how frequently this problem may occur. First, we will create two tables, table T1 will emulate table EDI830 from the previous blog article, and table T1_LINES will emulate the EDI830_LINES table. A PADDING column will be used to make the average row length roughly equivalent to that of the original tables:
CREATE TABLE T1( CUSTPO VARCHAR2(12) NOT NULL, RELID VARCHAR2(12) NOT NULL, PADDING VARCHAR2(40), CONSTRAINT T1_PK PRIMARY KEY (CUSTPO,RELID)); CREATE TABLE T1_LINES( CUSTPO VARCHAR2(12) NOT NULL, RELID VARCHAR2(12) NOT NULL, FCSTQTY NUMBER NOT NULL, FC VARCHAR2(1), FCDUEDATE DATE NOT NULL, CONSTRAINT T1_LINES_PK PRIMARY KEY (CUSTPO,RELID,FCDUEDATE,FCSTQTY));
Next, the table T1 is populated in a repeatable way that as best as possible simulates how the original table was actually populated in production:
INSERT INTO T1
SELECT
RPAD(CHR(MOD(ROWNUM-1,26)+65)||TO_CHAR(ROWNUM),12,'A') CUSTPO,
LPAD('1',12,'0') RELID,
LPAD('A',40,'A') PADDING
FROM
DUAL
CONNECT BY
LEVEL<=700;
INSERT INTO
T1
SELECT
CUSTPO,
LPAD(TO_CHAR(V2.RN+1),12,'0') RELID,
LPAD('A',40,'A') PADDING
FROM
(SELECT /*+ NO_MERGE */
CUSTPO,
PADDING,
MOD(ROWNUM,10)*30+19 RN
FROM
(SELECT /*+ NO_MERGE */
CUSTPO,
PADDING
FROM
T1
ORDER BY
CUSTPO)) V1,
(SELECT /*+ NO_MERGE */
ROWNUM RN
FROM
DUAL
CONNECT BY
LEVEL<=319) V2
WHERE
V2.RN<=V1.RN;
If we select from table T1, we are able to see how the row counts are distributed in an unequal, yet repeatable way, with 700 distinct customer purchase order numbers:
SELECT CUSTPO, MIN(RELID), MAX(RELID) FROM T1 GROUP BY CUSTPO ORDER BY CUSTPO; CUSTPO MIN(RELID) MAX(RELID) ------------ ------------ ------------ A105AAAAAAAA 000000000001 000000000050 A131AAAAAAAA 000000000001 000000000080 A157AAAAAAAA 000000000001 000000000110 A183AAAAAAAA 000000000001 000000000140 A1AAAAAAAAAA 000000000001 000000000170 A209AAAAAAAA 000000000001 000000000200 A235AAAAAAAA 000000000001 000000000230 A261AAAAAAAA 000000000001 000000000260 ... Z624AAAAAAAA 000000000001 000000000230 Z650AAAAAAAA 000000000001 000000000260 Z676AAAAAAAA 000000000001 000000000290 Z78AAAAAAAAA 000000000001 000000000020 700 rows selected.
Just as a quick experiment, let’s take a look at the output of the SQL statement that will be used to populate the line detail table (T1_LINES) for the customer POs:
SELECT
CUSTPO,
RELID,
10 FCSTQTY,
'A' FC,
TO_DATE('01-JAN-2011','DD-MON-YYYY') + (V2.RN*3 - V1.REL*2) FCDUEDATE
FROM
(SELECT /*+ NO_MERGE */
CUSTPO,
RELID,
ROW_NUMBER() OVER (PARTITION BY RELID ORDER BY CUSTPO) RN,
ROW_NUMBER() OVER (PARTITION BY CUSTPO ORDER BY RELID DESC) REL
FROM
T1
WHERE
CUSTPO IN ('Z78AAAAAAAAA','Y155AAAAAAAA')) V1,
(SELECT /*+ NO_MERGE */
ROWNUM RN
FROM
DUAL
CONNECT BY
LEVEL<=100) V2
WHERE
V2.RN < (MOD(V1.RN,100)+10)
ORDER BY
CUSTPO,
RELID,
5;
CUSTPO RELID FCSTQTY F FCDUEDATE
------------ ------------ ---------- - ---------
Y155AAAAAAAA 000000000001 10 A 26-SEP-10
Y155AAAAAAAA 000000000001 10 A 29-SEP-10
Y155AAAAAAAA 000000000001 10 A 02-OCT-10
Y155AAAAAAAA 000000000001 10 A 05-OCT-10
Y155AAAAAAAA 000000000001 10 A 08-OCT-10
Y155AAAAAAAA 000000000001 10 A 11-OCT-10
Y155AAAAAAAA 000000000001 10 A 14-OCT-10
Y155AAAAAAAA 000000000001 10 A 17-OCT-10
Y155AAAAAAAA 000000000001 10 A 20-OCT-10
Y155AAAAAAAA 000000000001 10 A 23-OCT-10
Y155AAAAAAAA 000000000002 10 A 28-SEP-10
Y155AAAAAAAA 000000000002 10 A 01-OCT-10
...
Z78AAAAAAAAA 000000000020 10 A 17-JAN-11
Z78AAAAAAAAA 000000000020 10 A 20-JAN-11
Z78AAAAAAAAA 000000000020 10 A 23-JAN-11
Z78AAAAAAAAA 000000000020 10 A 26-JAN-11
Z78AAAAAAAAA 000000000020 10 A 29-JAN-11
Z78AAAAAAAAA 000000000020 10 A 01-FEB-11
720 rows selected.
In the above, you can see how the dates will automatically shift from one release of a customer PO to the next, as well as the order in which the rows will be inserted. The actual dates and number of rows for these two customer POs will actually be a bit different when the rows are inserted into the table, so use the above as just an illustration.
Next, let’s insert 6,079,050 rows into the line detail table in a repeatable way:
INSERT INTO
T1_LINES
SELECT
CUSTPO,
RELID,
10 FCSTQTY,
'A' FC,
TO_DATE('01-JAN-2011','DD-MON-YYYY') + (V2.RN*3 - V1.REL*2) FCDUEDATE
FROM
(SELECT /*+ NO_MERGE */
CUSTPO,
RELID,
ROW_NUMBER() OVER (PARTITION BY RELID ORDER BY CUSTPO) RN,
ROW_NUMBER() OVER (PARTITION BY CUSTPO ORDER BY RELID DESC) REL
FROM
T1) V1,
(SELECT /*+ NO_MERGE */
ROWNUM RN
FROM
DUAL
CONNECT BY
LEVEL<=100) V2
WHERE
V2.RN < (MOD(V1.RN,100)+10)
ORDER BY
RELID,
CUSTPO,
5;
COMMIT;
Now let’s create table T2 (simulates table CUSTOMER_ORDER) and table T2_LINES (simulates table CUST_ORDER_LINE). Table T2_LINES will be populated with 224,700 rows:
CREATE TABLE T2(
ID VARCHAR2(15),
CUSTOMER_ID VARCHAR2(15),
CUSTOMER_PO_REF VARCHAR2(40),
PADDING VARCHAR2(150),
PRIMARY KEY(ID));
CREATE TABLE T2_LINES(
CUST_ORDER_ID VARCHAR2(15),
LINE_NO NUMBER,
PART_ID VARCHAR2(30),
DESIRED_SHIP_DATE DATE,
PADDING VARCHAR2(100),
PRIMARY KEY(CUST_ORDER_ID,LINE_NO));
INSERT INTO
T2
SELECT DISTINCT
CUSTPO ID,
'AAAAAAAAAA' CUSTOMER_ID,
CUSTPO CUSTOMER_PO_REF,
RPAD('A',150,'A') PADDING
FROM
T1
ORDER BY
DBMS_RANDOM.VALUE;
INSERT INTO
T2_LINES
SELECT
CUST_ORDER_ID,
ROW_NUMBER() OVER (PARTITION BY CUST_ORDER_ID ORDER BY DESIRED_SHIP_DATE) LINE_NO,
RPAD(TO_CHAR((MOD(ROWNUM,3000)+1) * 33)||'PART',10,'A') PART_ID,
DESIRED_SHIP_DATE,
RPAD('A',100,'A') PADDING
FROM
(SELECT DISTINCT
CUSTPO CUST_ORDER_ID,
FCDUEDATE DESIRED_SHIP_DATE
FROM
T1_LINES
WHERE
FCDUEDATE<= TO_DATE('15-JAN-2011','DD-MON-YYYY')) V1;
COMMIT;
Finally, we will gather statistics for the tables and the primary key indexes:
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE) EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1_LINES',CASCADE=>TRUE) EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2',CASCADE=>TRUE) EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2_LINES',CASCADE=>TRUE)
We are now able to start transforming the original query sections, these sections will be listed one at a time, as they were in the earlier article:
(SELECT E.CUSTPO, MAX(RELID) RELID FROM T1 E GROUP BY E.CUSTPO) E
The inline view that uses the above inline view:
(SELECT /*+ LEADING(E) */
E.CUSTPO,
EL.FCSTQTY,
EL.FCDUEDATE
FROM
(SELECT
E.CUSTPO,
MAX(RELID) RELID
FROM
T1 E
GROUP BY
E.CUSTPO) E,
T1_LINES EL
WHERE
E.CUSTPO=EL.CUSTPO
AND E.RELID=EL.RELID
AND EL.FCDUEDATE BETWEEN TO_DATE('01-JAN-2011','DD-MON-YYYY')-365 AND TO_DATE('01-JAN-2011','DD-MON-YYYY')+1200
AND EL.FCSTQTY>0) E
The inline view for the customer orders:
(SELECT CO.CUSTOMER_PO_REF, MAX(COL.DESIRED_SHIP_DATE) DESIRED_SHIP_DATE, MAX(COL.PART_ID) PART_ID FROM T2 CO, T2_LINES COL WHERE CO.ID=COL.CUST_ORDER_ID AND CUSTOMER_PO_REF IS NOT NULL AND COL.DESIRED_SHIP_DATE>=SYSDATE-365 GROUP BY CO.CUSTOMER_PO_REF) CO
Finally, the full SQL statement with all of the inline views joined:
SELECT /*+ LEADING(E) */ COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID, E.FCDUEDATE REQUIRED_DATE, SUM(E.FCSTQTY) QTY, 1 PLAN_LEVEL FROM (SELECT /*+ LEADING(E) */ E.CUSTPO, EL.FCSTQTY, EL.FCDUEDATE FROM (SELECT E.CUSTPO, MAX(RELID) RELID FROM T1 E GROUP BY E.CUSTPO) E, T1_LINES EL WHERE E.CUSTPO=EL.CUSTPO AND E.RELID=EL.RELID AND EL.FCDUEDATE BETWEEN SYSDATE-365 AND SYSDATE+1200 AND EL.FCSTQTY>0) E, (SELECT CO.CUSTOMER_PO_REF, MAX(COL.DESIRED_SHIP_DATE) DESIRED_SHIP_DATE, MAX(COL.PART_ID) PART_ID FROM T2 CO, T2_LINES COL WHERE CO.ID=COL.CUST_ORDER_ID AND CUSTOMER_PO_REF IS NOT NULL AND COL.DESIRED_SHIP_DATE>=SYSDATE-365 GROUP BY CO.CUSTOMER_PO_REF) CO WHERE E.CUSTPO=CO.CUSTOMER_PO_REF(+) AND E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE ORDER BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE;
Next, we need a simple table to hold the contents of the INSERT INTO… SELECT statement:
CREATE TABLE T1_DEST ( PART_ID VARCHAR2(30), REQUIRED_DATE DATE, QTY NUMBER, PLAN_LEVEL NUMBER);
Now the script that will actually perform the test with different values for OPTIMIZER_FEATURES_ENABLE, OPTIMIZER_INDEX_COST_ADJ, and OPTIMIZER_INDEX_CACHING (Edit Dec 19, 2010: the original version of the script set the TRACEFILE_IDENTIFIER value identically for the SELECT and the INSERT INTO versions of the SQL statement when the OPTIMIZER_FEATURES_ENABLED parameter was greater than 10.1.0.4, and that error resulted in the SELECT and the INSERT INTO execution plans appearing in the same trace file, rather than in separate trace files – the script below has been corrected):
SET TIMING ON SET ARRAYSIZE 1000 SET AUTOTRACE TRACEONLY STATISTICS SPOOL Capture_Results.txt ALTER SESSION SET OPTIMIZER_INDEX_CACHING=0; ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=100; ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.1.0.4'; ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SELECT_10.1.0.4'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; SELECT /*+ LEADING(E) */ COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID, E.FCDUEDATE REQUIRED_DATE, SUM(E.FCSTQTY) QTY, 1 PLAN_LEVEL FROM (SELECT /*+ LEADING(E) */ E.CUSTPO, EL.FCSTQTY, EL.FCDUEDATE FROM (SELECT /*+ */ E.CUSTPO, MAX(RELID) RELID FROM T1 E GROUP BY E.CUSTPO) E, T1_LINES EL WHERE E.CUSTPO=EL.CUSTPO AND E.RELID=EL.RELID AND EL.FCDUEDATE BETWEEN SYSDATE-365 AND SYSDATE+1200 AND EL.FCSTQTY>0) E, (SELECT /*+ */ CO.CUSTOMER_PO_REF, MAX(COL.DESIRED_SHIP_DATE) DESIRED_SHIP_DATE, MAX(COL.PART_ID) PART_ID FROM T2 CO, T2_LINES COL WHERE CO.ID=COL.CUST_ORDER_ID AND CUSTOMER_PO_REF IS NOT NULL AND COL.DESIRED_SHIP_DATE>=SYSDATE-365 GROUP BY CO.CUSTOMER_PO_REF) CO WHERE E.CUSTPO=CO.CUSTOMER_PO_REF(+) AND E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE ORDER BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF'; ALTER SESSION SET TRACEFILE_IDENTIFIER = 'INSERT_10.1.0.4'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; INSERT INTO T1_DEST SELECT /*+ LEADING(E) */ COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID, E.FCDUEDATE REQUIRED_DATE, SUM(E.FCSTQTY) QTY, 1 PLAN_LEVEL FROM (SELECT /*+ LEADING(E) */ E.CUSTPO, EL.FCSTQTY, EL.FCDUEDATE FROM (SELECT /*+ */ E.CUSTPO, MAX(RELID) RELID FROM T1 E GROUP BY E.CUSTPO) E, T1_LINES EL WHERE E.CUSTPO=EL.CUSTPO AND E.RELID=EL.RELID AND EL.FCDUEDATE BETWEEN SYSDATE-365 AND SYSDATE+1200 AND EL.FCSTQTY>0) E, (SELECT /*+ */ CO.CUSTOMER_PO_REF, MAX(COL.DESIRED_SHIP_DATE) DESIRED_SHIP_DATE, MAX(COL.PART_ID) PART_ID FROM T2 CO, T2_LINES COL WHERE CO.ID=COL.CUST_ORDER_ID AND CUSTOMER_PO_REF IS NOT NULL AND COL.DESIRED_SHIP_DATE>=SYSDATE-365 GROUP BY CO.CUSTOMER_PO_REF) CO WHERE E.CUSTPO=CO.CUSTOMER_PO_REF(+) AND E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE ORDER BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF'; TRUNCATE TABLE T1_DEST; ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.2.0.2'; ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SELECT_10.2.0.2'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; SELECT /*+ LEADING(E) */ COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID, E.FCDUEDATE REQUIRED_DATE, SUM(E.FCSTQTY) QTY, 1 PLAN_LEVEL FROM (SELECT /*+ LEADING(E) */ E.CUSTPO, EL.FCSTQTY, EL.FCDUEDATE FROM (SELECT /*+ */ E.CUSTPO, MAX(RELID) RELID FROM T1 E GROUP BY E.CUSTPO) E, T1_LINES EL WHERE E.CUSTPO=EL.CUSTPO AND E.RELID=EL.RELID AND EL.FCDUEDATE BETWEEN SYSDATE-365 AND SYSDATE+1200 AND EL.FCSTQTY>0) E, (SELECT /*+ */ CO.CUSTOMER_PO_REF, MAX(COL.DESIRED_SHIP_DATE) DESIRED_SHIP_DATE, MAX(COL.PART_ID) PART_ID FROM T2 CO, T2_LINES COL WHERE CO.ID=COL.CUST_ORDER_ID AND CUSTOMER_PO_REF IS NOT NULL AND COL.DESIRED_SHIP_DATE>=SYSDATE-365 GROUP BY CO.CUSTOMER_PO_REF) CO WHERE E.CUSTPO=CO.CUSTOMER_PO_REF(+) AND E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE ORDER BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF'; ALTER SESSION SET TRACEFILE_IDENTIFIER = 'INSERT_10.2.0.2'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; INSERT INTO T1_DEST SELECT /*+ LEADING(E) */ COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID, E.FCDUEDATE REQUIRED_DATE, SUM(E.FCSTQTY) QTY, 1 PLAN_LEVEL FROM (SELECT /*+ LEADING(E) */ E.CUSTPO, EL.FCSTQTY, EL.FCDUEDATE FROM (SELECT /*+ */ E.CUSTPO, MAX(RELID) RELID FROM T1 E GROUP BY E.CUSTPO) E, T1_LINES EL WHERE E.CUSTPO=EL.CUSTPO AND E.RELID=EL.RELID AND EL.FCDUEDATE BETWEEN SYSDATE-365 AND SYSDATE+1200 AND EL.FCSTQTY>0) E, (SELECT /*+ */ CO.CUSTOMER_PO_REF, MAX(COL.DESIRED_SHIP_DATE) DESIRED_SHIP_DATE, MAX(COL.PART_ID) PART_ID FROM T2 CO, T2_LINES COL WHERE CO.ID=COL.CUST_ORDER_ID AND CUSTOMER_PO_REF IS NOT NULL AND COL.DESIRED_SHIP_DATE>=SYSDATE-365 GROUP BY CO.CUSTOMER_PO_REF) CO WHERE E.CUSTPO=CO.CUSTOMER_PO_REF(+) AND E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE ORDER BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF'; TRUNCATE TABLE T1_DEST; ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='11.1.0.7'; ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SELECT_11.1.0.7'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; SELECT /*+ LEADING(E) */ COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID, E.FCDUEDATE REQUIRED_DATE, SUM(E.FCSTQTY) QTY, 1 PLAN_LEVEL FROM (SELECT /*+ LEADING(E) */ E.CUSTPO, EL.FCSTQTY, EL.FCDUEDATE FROM (SELECT /*+ */ E.CUSTPO, MAX(RELID) RELID FROM T1 E GROUP BY E.CUSTPO) E, T1_LINES EL WHERE E.CUSTPO=EL.CUSTPO AND E.RELID=EL.RELID AND EL.FCDUEDATE BETWEEN SYSDATE-365 AND SYSDATE+1200 AND EL.FCSTQTY>0) E, (SELECT /*+ */ CO.CUSTOMER_PO_REF, MAX(COL.DESIRED_SHIP_DATE) DESIRED_SHIP_DATE, MAX(COL.PART_ID) PART_ID FROM T2 CO, T2_LINES COL WHERE CO.ID=COL.CUST_ORDER_ID AND CUSTOMER_PO_REF IS NOT NULL AND COL.DESIRED_SHIP_DATE>=SYSDATE-365 GROUP BY CO.CUSTOMER_PO_REF) CO WHERE E.CUSTPO=CO.CUSTOMER_PO_REF(+) AND E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE ORDER BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF'; ALTER SESSION SET TRACEFILE_IDENTIFIER = 'INSERT_11.1.0.7'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; INSERT INTO T1_DEST SELECT /*+ LEADING(E) */ COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID, E.FCDUEDATE REQUIRED_DATE, SUM(E.FCSTQTY) QTY, 1 PLAN_LEVEL FROM (SELECT /*+ LEADING(E) */ E.CUSTPO, EL.FCSTQTY, EL.FCDUEDATE FROM (SELECT /*+ */ E.CUSTPO, MAX(RELID) RELID FROM T1 E GROUP BY E.CUSTPO) E, T1_LINES EL WHERE E.CUSTPO=EL.CUSTPO AND E.RELID=EL.RELID AND EL.FCDUEDATE BETWEEN SYSDATE-365 AND SYSDATE+1200 AND EL.FCSTQTY>0) E, (SELECT /*+ */ CO.CUSTOMER_PO_REF, MAX(COL.DESIRED_SHIP_DATE) DESIRED_SHIP_DATE, MAX(COL.PART_ID) PART_ID FROM T2 CO, T2_LINES COL WHERE CO.ID=COL.CUST_ORDER_ID AND CUSTOMER_PO_REF IS NOT NULL AND COL.DESIRED_SHIP_DATE>=SYSDATE-365 GROUP BY CO.CUSTOMER_PO_REF) CO WHERE E.CUSTPO=CO.CUSTOMER_PO_REF(+) AND E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE ORDER BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF'; TRUNCATE TABLE T1_DEST; ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='11.2.0.1'; ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SELECT_11.2.0.1'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; SELECT /*+ LEADING(E) */ COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID, E.FCDUEDATE REQUIRED_DATE, SUM(E.FCSTQTY) QTY, 1 PLAN_LEVEL FROM (SELECT /*+ LEADING(E) */ E.CUSTPO, EL.FCSTQTY, EL.FCDUEDATE FROM (SELECT /*+ */ E.CUSTPO, MAX(RELID) RELID FROM T1 E GROUP BY E.CUSTPO) E, T1_LINES EL WHERE E.CUSTPO=EL.CUSTPO AND E.RELID=EL.RELID AND EL.FCDUEDATE BETWEEN SYSDATE-365 AND SYSDATE+1200 AND EL.FCSTQTY>0) E, (SELECT /*+ */ CO.CUSTOMER_PO_REF, MAX(COL.DESIRED_SHIP_DATE) DESIRED_SHIP_DATE, MAX(COL.PART_ID) PART_ID FROM T2 CO, T2_LINES COL WHERE CO.ID=COL.CUST_ORDER_ID AND CUSTOMER_PO_REF IS NOT NULL AND COL.DESIRED_SHIP_DATE>=SYSDATE-365 GROUP BY CO.CUSTOMER_PO_REF) CO WHERE E.CUSTPO=CO.CUSTOMER_PO_REF(+) AND E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE ORDER BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF'; ALTER SESSION SET TRACEFILE_IDENTIFIER = 'INSERT_11.2.0.1'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; INSERT INTO T1_DEST SELECT /*+ LEADING(E) */ COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID, E.FCDUEDATE REQUIRED_DATE, SUM(E.FCSTQTY) QTY, 1 PLAN_LEVEL FROM (SELECT /*+ LEADING(E) */ E.CUSTPO, EL.FCSTQTY, EL.FCDUEDATE FROM (SELECT /*+ */ E.CUSTPO, MAX(RELID) RELID FROM T1 E GROUP BY E.CUSTPO) E, T1_LINES EL WHERE E.CUSTPO=EL.CUSTPO AND E.RELID=EL.RELID AND EL.FCDUEDATE BETWEEN SYSDATE-365 AND SYSDATE+1200 AND EL.FCSTQTY>0) E, (SELECT /*+ */ CO.CUSTOMER_PO_REF, MAX(COL.DESIRED_SHIP_DATE) DESIRED_SHIP_DATE, MAX(COL.PART_ID) PART_ID FROM T2 CO, T2_LINES COL WHERE CO.ID=COL.CUST_ORDER_ID AND CUSTOMER_PO_REF IS NOT NULL AND COL.DESIRED_SHIP_DATE>=SYSDATE-365 GROUP BY CO.CUSTOMER_PO_REF) CO WHERE E.CUSTPO=CO.CUSTOMER_PO_REF(+) AND E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE ORDER BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF'; SPOOL Capture_Results80.txt TRUNCATE TABLE T1_DEST; ALTER SESSION SET OPTIMIZER_INDEX_CACHING=100; ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=80; ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.1.0.4'; ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SELECT_10.1.0.4_80'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; SELECT /*+ LEADING(E) */ COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID, E.FCDUEDATE REQUIRED_DATE, SUM(E.FCSTQTY) QTY, 1 PLAN_LEVEL FROM (SELECT /*+ LEADING(E) */ E.CUSTPO, EL.FCSTQTY, EL.FCDUEDATE FROM (SELECT /*+ _80 */ E.CUSTPO, MAX(RELID) RELID FROM T1 E GROUP BY E.CUSTPO) E, T1_LINES EL WHERE E.CUSTPO=EL.CUSTPO AND E.RELID=EL.RELID AND EL.FCDUEDATE BETWEEN SYSDATE-365 AND SYSDATE+1200 AND EL.FCSTQTY>0) E, (SELECT /*+ */ CO.CUSTOMER_PO_REF, MAX(COL.DESIRED_SHIP_DATE) DESIRED_SHIP_DATE, MAX(COL.PART_ID) PART_ID FROM T2 CO, T2_LINES COL WHERE CO.ID=COL.CUST_ORDER_ID AND CUSTOMER_PO_REF IS NOT NULL AND COL.DESIRED_SHIP_DATE>=SYSDATE-365 GROUP BY CO.CUSTOMER_PO_REF) CO WHERE E.CUSTPO=CO.CUSTOMER_PO_REF(+) AND E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE ORDER BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF'; ALTER SESSION SET TRACEFILE_IDENTIFIER = 'INSERT_10.1.0.4_80'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; INSERT INTO T1_DEST SELECT /*+ LEADING(E) */ COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID, E.FCDUEDATE REQUIRED_DATE, SUM(E.FCSTQTY) QTY, 1 PLAN_LEVEL FROM (SELECT /*+ LEADING(E) */ E.CUSTPO, EL.FCSTQTY, EL.FCDUEDATE FROM (SELECT /*+ _80 */ E.CUSTPO, MAX(RELID) RELID FROM T1 E GROUP BY E.CUSTPO) E, T1_LINES EL WHERE E.CUSTPO=EL.CUSTPO AND E.RELID=EL.RELID AND EL.FCDUEDATE BETWEEN SYSDATE-365 AND SYSDATE+1200 AND EL.FCSTQTY>0) E, (SELECT /*+ */ CO.CUSTOMER_PO_REF, MAX(COL.DESIRED_SHIP_DATE) DESIRED_SHIP_DATE, MAX(COL.PART_ID) PART_ID FROM T2 CO, T2_LINES COL WHERE CO.ID=COL.CUST_ORDER_ID AND CUSTOMER_PO_REF IS NOT NULL AND COL.DESIRED_SHIP_DATE>=SYSDATE-365 GROUP BY CO.CUSTOMER_PO_REF) CO WHERE E.CUSTPO=CO.CUSTOMER_PO_REF(+) AND E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE ORDER BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF'; TRUNCATE TABLE T1_DEST; ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.2.0.2'; ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SELECT_10.2.0.2_80'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; SELECT /*+ LEADING(E) */ COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID, E.FCDUEDATE REQUIRED_DATE, SUM(E.FCSTQTY) QTY, 1 PLAN_LEVEL FROM (SELECT /*+ LEADING(E) */ E.CUSTPO, EL.FCSTQTY, EL.FCDUEDATE FROM (SELECT /*+ _80 */ E.CUSTPO, MAX(RELID) RELID FROM T1 E GROUP BY E.CUSTPO) E, T1_LINES EL WHERE E.CUSTPO=EL.CUSTPO AND E.RELID=EL.RELID AND EL.FCDUEDATE BETWEEN SYSDATE-365 AND SYSDATE+1200 AND EL.FCSTQTY>0) E, (SELECT /*+ */ CO.CUSTOMER_PO_REF, MAX(COL.DESIRED_SHIP_DATE) DESIRED_SHIP_DATE, MAX(COL.PART_ID) PART_ID FROM T2 CO, T2_LINES COL WHERE CO.ID=COL.CUST_ORDER_ID AND CUSTOMER_PO_REF IS NOT NULL AND COL.DESIRED_SHIP_DATE>=SYSDATE-365 GROUP BY CO.CUSTOMER_PO_REF) CO WHERE E.CUSTPO=CO.CUSTOMER_PO_REF(+) AND E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE ORDER BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF'; ALTER SESSION SET TRACEFILE_IDENTIFIER = 'INSERT_10.2.0.2_80'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; INSERT INTO T1_DEST SELECT /*+ LEADING(E) */ COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID, E.FCDUEDATE REQUIRED_DATE, SUM(E.FCSTQTY) QTY, 1 PLAN_LEVEL FROM (SELECT /*+ LEADING(E) */ E.CUSTPO, EL.FCSTQTY, EL.FCDUEDATE FROM (SELECT /*+ _80 */ E.CUSTPO, MAX(RELID) RELID FROM T1 E GROUP BY E.CUSTPO) E, T1_LINES EL WHERE E.CUSTPO=EL.CUSTPO AND E.RELID=EL.RELID AND EL.FCDUEDATE BETWEEN SYSDATE-365 AND SYSDATE+1200 AND EL.FCSTQTY>0) E, (SELECT /*+ */ CO.CUSTOMER_PO_REF, MAX(COL.DESIRED_SHIP_DATE) DESIRED_SHIP_DATE, MAX(COL.PART_ID) PART_ID FROM T2 CO, T2_LINES COL WHERE CO.ID=COL.CUST_ORDER_ID AND CUSTOMER_PO_REF IS NOT NULL AND COL.DESIRED_SHIP_DATE>=SYSDATE-365 GROUP BY CO.CUSTOMER_PO_REF) CO WHERE E.CUSTPO=CO.CUSTOMER_PO_REF(+) AND E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE ORDER BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF'; TRUNCATE TABLE T1_DEST; ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='11.1.0.7'; ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SELECT_11.1.0.7_80'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; SELECT /*+ LEADING(E) */ COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID, E.FCDUEDATE REQUIRED_DATE, SUM(E.FCSTQTY) QTY, 1 PLAN_LEVEL FROM (SELECT /*+ LEADING(E) */ E.CUSTPO, EL.FCSTQTY, EL.FCDUEDATE FROM (SELECT /*+ _80 */ E.CUSTPO, MAX(RELID) RELID FROM T1 E GROUP BY E.CUSTPO) E, T1_LINES EL WHERE E.CUSTPO=EL.CUSTPO AND E.RELID=EL.RELID AND EL.FCDUEDATE BETWEEN SYSDATE-365 AND SYSDATE+1200 AND EL.FCSTQTY>0) E, (SELECT /*+ */ CO.CUSTOMER_PO_REF, MAX(COL.DESIRED_SHIP_DATE) DESIRED_SHIP_DATE, MAX(COL.PART_ID) PART_ID FROM T2 CO, T2_LINES COL WHERE CO.ID=COL.CUST_ORDER_ID AND CUSTOMER_PO_REF IS NOT NULL AND COL.DESIRED_SHIP_DATE>=SYSDATE-365 GROUP BY CO.CUSTOMER_PO_REF) CO WHERE E.CUSTPO=CO.CUSTOMER_PO_REF(+) AND E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE ORDER BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF'; ALTER SESSION SET TRACEFILE_IDENTIFIER = 'INSERT_11.1.0.7_80'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; INSERT INTO T1_DEST SELECT /*+ LEADING(E) */ COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID, E.FCDUEDATE REQUIRED_DATE, SUM(E.FCSTQTY) QTY, 1 PLAN_LEVEL FROM (SELECT /*+ LEADING(E) */ E.CUSTPO, EL.FCSTQTY, EL.FCDUEDATE FROM (SELECT /*+ _80 */ E.CUSTPO, MAX(RELID) RELID FROM T1 E GROUP BY E.CUSTPO) E, T1_LINES EL WHERE E.CUSTPO=EL.CUSTPO AND E.RELID=EL.RELID AND EL.FCDUEDATE BETWEEN SYSDATE-365 AND SYSDATE+1200 AND EL.FCSTQTY>0) E, (SELECT /*+ */ CO.CUSTOMER_PO_REF, MAX(COL.DESIRED_SHIP_DATE) DESIRED_SHIP_DATE, MAX(COL.PART_ID) PART_ID FROM T2 CO, T2_LINES COL WHERE CO.ID=COL.CUST_ORDER_ID AND CUSTOMER_PO_REF IS NOT NULL AND COL.DESIRED_SHIP_DATE>=SYSDATE-365 GROUP BY CO.CUSTOMER_PO_REF) CO WHERE E.CUSTPO=CO.CUSTOMER_PO_REF(+) AND E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE ORDER BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF'; TRUNCATE TABLE T1_DEST; ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='11.2.0.1'; ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SELECT_11.2.0.1_80'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; SELECT /*+ LEADING(E) */ COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID, E.FCDUEDATE REQUIRED_DATE, SUM(E.FCSTQTY) QTY, 1 PLAN_LEVEL FROM (SELECT /*+ LEADING(E) */ E.CUSTPO, EL.FCSTQTY, EL.FCDUEDATE FROM (SELECT /*+ _80 */ E.CUSTPO, MAX(RELID) RELID FROM T1 E GROUP BY E.CUSTPO) E, T1_LINES EL WHERE E.CUSTPO=EL.CUSTPO AND E.RELID=EL.RELID AND EL.FCDUEDATE BETWEEN SYSDATE-365 AND SYSDATE+1200 AND EL.FCSTQTY>0) E, (SELECT /*+ */ CO.CUSTOMER_PO_REF, MAX(COL.DESIRED_SHIP_DATE) DESIRED_SHIP_DATE, MAX(COL.PART_ID) PART_ID FROM T2 CO, T2_LINES COL WHERE CO.ID=COL.CUST_ORDER_ID AND CUSTOMER_PO_REF IS NOT NULL AND COL.DESIRED_SHIP_DATE>=SYSDATE-365 GROUP BY CO.CUSTOMER_PO_REF) CO WHERE E.CUSTPO=CO.CUSTOMER_PO_REF(+) AND E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE ORDER BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF'; ALTER SESSION SET TRACEFILE_IDENTIFIER = 'INSERT_11.2.0.1_80'; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; INSERT INTO T1_DEST SELECT /*+ LEADING(E) */ COALESCE(CO.PART_ID,'NOT-IN-SYSTEM') PART_ID, E.FCDUEDATE REQUIRED_DATE, SUM(E.FCSTQTY) QTY, 1 PLAN_LEVEL FROM (SELECT /*+ LEADING(E) */ E.CUSTPO, EL.FCSTQTY, EL.FCDUEDATE FROM (SELECT /*+ _80 */ E.CUSTPO, MAX(RELID) RELID FROM T1 E GROUP BY E.CUSTPO) E, T1_LINES EL WHERE E.CUSTPO=EL.CUSTPO AND E.RELID=EL.RELID AND EL.FCDUEDATE BETWEEN SYSDATE-365 AND SYSDATE+1200 AND EL.FCSTQTY>0) E, (SELECT /*+ */ CO.CUSTOMER_PO_REF, MAX(COL.DESIRED_SHIP_DATE) DESIRED_SHIP_DATE, MAX(COL.PART_ID) PART_ID FROM T2 CO, T2_LINES COL WHERE CO.ID=COL.CUST_ORDER_ID AND CUSTOMER_PO_REF IS NOT NULL AND COL.DESIRED_SHIP_DATE>=SYSDATE-365 GROUP BY CO.CUSTOMER_PO_REF) CO WHERE E.CUSTPO=CO.CUSTOMER_PO_REF(+) AND E.FCDUEDATE>COALESCE(CO.DESIRED_SHIP_DATE,SYSDATE-365) GROUP BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE ORDER BY COALESCE(CO.PART_ID,'NOT-IN-SYSTEM'), E.FCDUEDATE; ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF'; SPOOL OFF
For this blog article, I decided to use the Enterprise Edition of Oracle Database 11.2.0.1, with the following manually set system (CPU) statistics (as printed in the 10053 trace files):
SYSTEM STATISTICS INFORMATION ----------------------------- Using WORKLOAD Stats CPUSPEED: 2664 millions instructions/sec SREADTIM: 8.000000 milliseconds MREADTIM: 10.000000 millisecons MBRC: 16 blocks MAXTHR: 19181568 bytes/sec SLAVETHR: -1 bytes/sec
The tablespace used for the test case uses ASSM with auto-allocated extent sizes, and the following instance-wide parameters specified:
SGA_TARGET = 12000M PGA_AGGREGATE_TARGET = 2000M OPTIMIZER_MODE = ALL_ROWS
Just for fun, for now I will show you only the first two execution plans from the 10053 trace files:
SELECT_10.1.0.4 (OPTIMIZER_FEATURES_ENABLE=’10.1.0.4′, OPTIMIZER_INDEX_CACHING=0, OPTIMIZER_INDEX_COST_ADJ=100), completed in 0.23 seconds when selecting 10,478 rows:
--------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 1425 | |
| 1 | SORT GROUP BY | | 487 | 46K | 1425 | 00:00:12 |
| 2 | FILTER | | | | | |
| 3 | HASH JOIN RIGHT OUTER | | 19K | 1884K | 1423 | 00:00:12 |
| 4 | VIEW | | 700 | 27K | 368 | 00:00:03 |
| 5 | SORT GROUP BY | | 700 | 40K | 368 | 00:00:03 |
| 6 | HASH JOIN | | 146K | 8475K | 362 | 00:00:03 |
| 7 | TABLE ACCESS FULL | T2 | 700 | 18K | 3 | 00:00:01 |
| 8 | TABLE ACCESS FULL | T2_LINES | 146K | 4676K | 358 | 00:00:03 |
| 9 | NESTED LOOPS | | 19K | 1126K | 1055 | 00:00:09 |
| 10 | VIEW | | 700 | 14K | 65 | 00:00:01 |
| 11 | SORT GROUP BY | | 700 | 18K | 65 | 00:00:01 |
| 12 | FILTER | | | | | |
| 13 | INDEX FAST FULL SCAN | T1_PK | 106K | 2755K | 60 | 00:00:01 |
| 14 | INDEX RANGE SCAN | T1_LINES_PK| 28 | 1036 | 3 | 00:00:01 |
--------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter(("EL"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365) AND COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365)<SYSDATE@!+1200))
3 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
6 - access("CO"."ID"="COL"."CUST_ORDER_ID")
7 - filter("CUSTOMER_PO_REF" IS NOT NULL)
8 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)
12 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
14 - access("E"."CUSTPO"="EL"."CUSTPO" AND "E"."RELID"="EL"."RELID" AND "EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCSTQTY">0 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200)
14 - filter("EL"."FCSTQTY">0)
INSERT_10.1.0.4 (OPTIMIZER_FEATURES_ENABLE=’10.1.0.4′, OPTIMIZER_INDEX_CACHING=0, OPTIMIZER_INDEX_COST_ADJ=100), completed in… 30 minutes, 18.01 seconds when inserting 10,478 rows:
-------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------+-----------------------------------+
| 0 | INSERT STATEMENT | | | | 4967K | |
| 1 | LOAD TABLE CONVENTIONAL | | | | | |
| 2 | SORT GROUP BY | | 842 | 60K | 4967K | 11:18:11 |
| 3 | FILTER | | | | | |
| 4 | HASH JOIN OUTER | | 842 | 60K | 4967K | 11:18:11 |
| 5 | VIEW | | 842 | 28K | 4967K | 11:18:08 |
| 6 | FILTER | | | | | |
| 7 | SORT GROUP BY | | 842 | 53K | 4967K | 11:18:08 |
| 8 | FILTER | | | | | |
| 9 | HASH JOIN | | 852M | 54G | 7035 | 00:00:57 |
| 10 | INDEX FAST FULL SCAN | T1_PK | 106K | 2755K | 60 | 00:00:01 |
| 11 | TABLE ACCESS FULL | T1_LINES| 5631K | 214M | 3388 | 00:00:28 |
| 12 | VIEW | | 700 | 27K | 368 | 00:00:03 |
| 13 | SORT GROUP BY | | 700 | 40K | 368 | 00:00:03 |
| 14 | HASH JOIN | | 146K | 8475K | 362 | 00:00:03 |
| 15 | TABLE ACCESS FULL | T2 | 700 | 18K | 3 | 00:00:01 |
| 16 | TABLE ACCESS FULL | T2_LINES| 146K | 4676K | 358 | 00:00:03 |
-------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - filter("E"."FCDUEDATE">COALESCE("CO"."DESIRED_SHIP_DATE",SYSDATE@!-365))
4 - access("E"."CUSTPO"="CO"."CUSTOMER_PO_REF")
6 - filter("EL"."RELID"=MAX("RELID"))
8 - filter(SYSDATE@!-365<=SYSDATE@!+1200)
9 - access("E"."CUSTPO"="EL"."CUSTPO")
11 - filter(("EL"."FCDUEDATE">=SYSDATE@!-365 AND "EL"."FCDUEDATE"<=SYSDATE@!+1200 AND "EL"."FCSTQTY">0))
14 - access("CO"."ID"="COL"."CUST_ORDER_ID")
15 - filter("CUSTOMER_PO_REF" IS NOT NULL)
16 - filter("COL"."DESIRED_SHIP_DATE">=SYSDATE@!-365)
——————-
Just one more example of the value of test cases. By the way, make certain that the air conditioning is working well in the room where your test server is located – your test server could be swamped for a couple of hours while the table creation scripts run and the actual test script is executed. So, what is ahead in part three of this blog article series?
(P.S. Did anyone’s test result for the first INSERT INTO statement complete in less than 30 minutes?)
Recent Comments