How stored outlines saved the day
Just before the last month closing in a 11i system running on 10.2.0.4 database some patches were applied to solve couple of critical functional issues. All went fine until the month closing processes were started. It turned out the patches introduced a new issue - this time it was a performance issue, that had managed to escape the testing efforts. This post is about how a workaround using stored outlines had been applied until a proper fix was implemented.
This is the query that caused the trouble:
EXPLAIN PLAN FOR
SELECT /*+ LEADING(EI) ORDERED INDEX ( RDL PA_CUST_REV_DIST_LINES_N1 ) */ 'X' FROM PA_EXPENDITURE_ITEMS_ALL EI , PA_CUST_REV_DIST_LINES_ALL RDL WHERE EI.REQUEST_ID+0 = :B3 AND EI.RAW_REVENUE IS NOT NULL AND EI.ACCRUED_REVENUE IS NOT NULL AND EI.REVENUE_DISTRIBUTED_FLAG||'' = 'A'||'' AND EI.EXPENDITURE_ITEM_ID = RDL.EXPENDITURE_ITEM_ID AND EI.RAW_REVENUE <> EI.ACCRUED_REVENUE AND RDL.REQUEST_ID+0 = EI.REQUEST_ID AND RDL.PROJECT_ID = :B2 AND NVL(RDL.FUNCTION_CODE,'*') NOT IN ('LRL','LRB','URL','URB') AND RDL.LINE_NUM_REVERSED+0 IS NULL AND NVL(RDL.REVERSED_FLAG, 'N' ) = 'N' AND RDL.DRAFT_REVENUE_NUM = :B1 HAVING SUM(EI.ACCRUED_REVENUE) = SUM(EI.RAW_REVENUE);
select * from table(dbms_xplan.display);
Plan hash value: 2869790318
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 374K (5)| 00:22:12 |
| 1 | FILTER | | | | | |
| 2 | SORT AGGREGATE | | 1 | 47 | | |
| 3 | TABLE ACCESS BY INDEX ROWID| PA_CUST_REV_DIST_LINES_ALL | 1 | 29 | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 47 | 374K (5)| 00:22:12 |
| 5 | TABLE ACCESS FULL | PA_EXPENDITURE_ITEMS_ALL | 1 | 18 | 374K (5)| 00:22:12 |
| 6 | INDEX RANGE SCAN | PA_CUST_REV_DIST_LINES_N1 | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
At the peak of the day we had about 20 of PRC: Generate Draft Revenue for a Range of Projects and PRC: Generate Draft Revenue for a Single Project concurrent requests running at the same time and each of them was executing this exact query. The problem here was the full scan over 23Gb sized PA_EXPENDITURE_ITEMS_ALL table, the disks were trashing and the whole system became slow. It was quickly found that that the same query without the hints gives a much better execution plan which allows the select statement to finish in few seconds:
EXPLAIN PLAN FOR
SELECT 'X' FROM PA_EXPENDITURE_ITEMS_ALL EI , PA_CUST_REV_DIST_LINES_ALL RDL WHERE EI.REQUEST_ID+0 = :B3 AND EI.RAW_REVENUE IS NOT NULL AND EI.ACCRUED_REVENUE IS NOT NULL AND EI.REVENUE_DISTRIBUTED_FLAG||'' = 'A'||'' AND EI.EXPENDITURE_ITEM_ID = RDL.EXPENDITURE_ITEM_ID AND EI.RAW_REVENUE <> EI.ACCRUED_REVENUE AND RDL.REQUEST_ID+0 = EI.REQUEST_ID AND RDL.PROJECT_ID = :B2 AND NVL(RDL.FUNCTION_CODE,'*') NOT IN ('LRL','LRB','URL','URB') AND RDL.LINE_NUM_REVERSED+0 IS NULL AND NVL(RDL.REVERSED_FLAG, 'N' ) = 'N' AND RDL.DRAFT_REVENUE_NUM = :B1 HAVING SUM(EI.ACCRUED_REVENUE) = SUM(EI.RAW_REVENUE);
select * from table(dbms_xplan.display);
Plan hash value: 479970841
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 6 (0)| 00:00:01 |
| 1 | FILTER | | | | | |
| 2 | SORT AGGREGATE | | 1 | 47 | | |
| 3 | NESTED LOOPS | | 1 | 47 | 6 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| PA_CUST_REV_DIST_LINES_ALL | 1 | 29 | 4 (0)| 00:00:01 |
| 5 | INDEX RANGE SCAN | PA_CUST_REV_DIST_LINES_N1 | 1 | | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| PA_EXPENDITURE_ITEMS_ALL | 1 | 18 | 2 (0)| 00:00:01 |
| 7 | INDEX UNIQUE SCAN | PA_EXPENDITURE_ITEMS_U1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Unfortunately we had no time to open a SR to get an “official” fix and the executable files of the concurrent programs were binaries with the statement located in paigil.o library, meaning we had no way to customize it.
Here’s how a workaround was implemented using stored outlines to force execution using the “good” plan.
First I create a stored outline for the original query, notice the little trick to get the query exactly as it is - we get it in clob from v$sql and then create the outline using it:
SQL> DECLARE
2 stm CLOB;
3 BEGIN
4 SELECT sql_fulltext INTO stm FROM v$sql WHERE sql_id='9ydxyxzquwhaq';
5 EXECUTE immediate 'CREATE OUTLINE PARGDR_WORKAROUND FOR CATEGORY WORKAROUND ON '||dbms_lob.substr(stm);
6 END;
/
PL/SQL procedure successfully completed.
Now we create another outline for the same query without hints, this outline is temporary and will be removed after a few steps.
SQL> CREATE OUTLINE TEMPORARY_WORKAROUND FOR CATEGORY WORKAROUND ON SELECT 'X' FROM PA_EXPENDITURE_ITEMS_ALL EI , PA_CUST_REV_DIST_LINES_ALL RDL WHERE EI.REQUEST_ID+0 = :B3 AND EI.RAW_REVENUE IS NOT NULL AND EI.ACCRUED_REVENUE IS NOT NULL AND EI.REVENUE_DISTRIBUTED_FLAG||'' = 'A'||'' AND EI.EXPENDITURE_ITEM_ID = RDL.EXPENDITURE_ITEM_ID AND EI.RAW_REVENUE <> EI.ACCRUED_REVENUE AND RDL.REQUEST_ID+0 = EI.REQUEST_ID AND RDL.PROJECT_ID = :B2 AND NVL(RDL.FUNCTION_CODE,'*') NOT IN ('LRL','LRB','URL','URB') AND RDL.LINE_NUM_REVERSED+0 IS NULL AND NVL(RDL.REVERSED_FLAG, 'N' ) = 'N' AND RDL.DRAFT_REVENUE_NUM = :B1 HAVING SUM(EI.ACCRUED_REVENUE) = SUM(EI.RAW_REVENUE);
CREATE OUTLINE succeeded.
Outline is simply a stored collection of hints that are used to determine the execution plan. The hints are stored in OUTLN.OL$HINTS table, for this emergency situation the solution was to exchange the sets of hints between both freshly cooked outlines. Outline “TEMPORARY_WORKAROUND” will be associated with the hints of outline “PARGDR_WORKAROUND” and vice-versa. After that is done, the PARGDR_WORKAROUND will provide the “good” execution plan, but the other one will be deleted. See how it’s done:
SQL> UPDATE OUTLN.OL$HINTS
2 SET ol_name =DECODE(ol_name,'TEMPORARY_WORKAROUND','PARGDR_WORKAROUND','PARGDR_WORKAROUND','TEMPORARY_WORKAROUND',NULL)
3 WHERE category='WORKAROUND'
4 AND OL_NAME IN ('TEMPORARY_WORKAROUND','PARGDR_WORKAROUND');
22 rows updated.
SQL> commit;
Commit complete.
SQL> DROP outline TEMPORARY_WORKAROUND;
Outline dropped.
After that is done the new stored outline group can be enabled and tested. keep in mind “use_stored_outlines” is not an initialization parameter, so it has to be set after each restart of the instance. The last line of the output confirms the outline is used to provide the execution plan.
SQL> alter system set use_stored_outlines=WORKAROUND;
System altered.
EXPLAIN PLAN FOR SELECT /*+ LEADING(EI) ORDERED INDEX ( RDL PA_CUST_REV_DIST_LINES_N1 ) */ 'X' FROM PA_EXPENDITURE_ITEMS_ALL EI , PA_CUST_REV_DIST_LINES_ALL RDL WHERE EI.REQUEST_ID+0 = :B3 AND EI.RAW_REVENUE IS NOT NULL AND EI.ACCRUED_REVENUE IS NOT NULL AND EI.REVENUE_DISTRIBUTED_FLAG||'' = 'A'||'' AND EI.EXPENDITURE_ITEM_ID = RDL.EXPENDITURE_ITEM_ID AND EI.RAW_REVENUE <> EI.ACCRUED_REVENUE AND RDL.REQUEST_ID+0 = EI.REQUEST_ID AND RDL.PROJECT_ID = :B2 AND NVL(RDL.FUNCTION_CODE,'*') NOT IN ('LRL','LRB','URL','URB') AND RDL.LINE_NUM_REVERSED+0 IS NULL AND NVL(RDL.REVERSED_FLAG, 'N' ) = 'N' AND RDL.DRAFT_REVENUE_NUM = :B1 HAVING SUM(EI.ACCRUED_REVENUE) = SUM(EI.RAW_REVENUE);
select * from table(dbms_xplan.display);
Plan hash value: 479970841
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 6 (0)| 00:00:01 |
| 1 | FILTER | | | | | |
| 2 | SORT AGGREGATE | | 1 | 47 | | |
| 3 | NESTED LOOPS | | 1 | 47 | 6 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| PA_CUST_REV_DIST_LINES_ALL | 1 | 29 | 4 (0)| 00:00:01 |
| 5 | INDEX RANGE SCAN | PA_CUST_REV_DIST_LINES_N1 | 1 | | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| PA_EXPENDITURE_ITEMS_ALL | 1 | 18 | 2 (0)| 00:00:01 |
| 7 | INDEX UNIQUE SCAN | PA_EXPENDITURE_ITEMS_U1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Note
-----
- outline 'PARGDR_WORKAROUND' used for this statement
If the old bad execution plan for the sql statement is still cached, the last thing to do is to invalidate the shared cursor, otherwise the outline is still not used. We don’t want to flush the shared pool in the middle of the day, therefore a smarter way can be used to invalidate cursors involving the table PA_EXPENDITURE_ITEMS_ALL. There are multiple options to do that, e.g, gather statistics on the table using “no_invlaidate=false” option, or simply alter the comment of the table comment on table PA.PA\_EXPENDITURE\_ITEMS\_ALL is ' ';
NOTE, The situation I used this method for was really an emergency, and this kind of “solution” should be trated as the last option, the better approach of course would be solving it the official way by opening a SR or applying some patch (if exists) to fix the issue, or creating a customization. In my case the time pressure paid key role to choose this one.