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:

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:

(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:

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):

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?)

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: