For example, one of query plans was obtained with
select * from table(dbms_xplan.display_cursor('&1',null,'LAST ALLSTATS'));
Its run time statistics confirms the fact that last start was executed as "individual calculation"
Plan hash value: 645901738
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 1 | 172 |00:00:00.01 | 16 | 766K| 766K| |
| 2 | MERGE JOIN CARTESIAN | | 1 | 12 | 228 |00:00:00.01 | 10 | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| PORDER | 1 | 1 | 12 |00:00:00.01 | 7 | | | |
| 4 | NESTED LOOPS | | 1 | 1 | 14 |00:00:00.01 | 6 | | | |
|* 5 | TABLE ACCESS FULL | TMP_PNF_TO_CALC | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
|* 6 | INDEX RANGE SCAN | IDX$$_0EFF0001 | 1 | 2 | 12 |00:00:00.01 | 3 | | | |
| 7 | BUFFER SORT | | 12 | 12 | 228 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 8 | TABLE ACCESS FULL | TMP_TAR_DATES | 1 | 12 | 19 |00:00:00.01 | 3 | | | |
|* 9 | TABLE ACCESS FULL | TMP_CALC_P_ROWS | 1 | 178 | 287 |00:00:00.01 | 6 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------
But after I have examined overall run time statistics for this query with
select * from table(dbms_xplan.display_cursor('&1',null,'ALL ALLSTATS'));
it became clear that plan for "mass calculation" exactly match the plan for "individual calculation". In other words it was reused.
Plan hash value: 645901738
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | O/1/M |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 84 | 1 | 404 | 11 (10)| 00:00:01 | 5680 |00:00:00.14 | 1201 | 0 | 766K| 766K| |
| 2 | MERGE JOIN CARTESIAN | | 84 | 12 | 900 | 8 (0)| 00:00:01 | 191M|00:03:11.20 | 22759 | 282 | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| PORDER | 84 | 1 | 27 | 4 (0)| 00:00:01 | 8995 |00:00:03.14 | 22424 | 282 | | | |
| 4 | NESTED LOOPS | | 84 | 1 | 53 | 6 (0)| 00:00:01 | 19632 |00:00:03.04 | 22040 | 280 | | | |
|* 5 | TABLE ACCESS FULL | TMP_PNF_TO_CALC | 84 | 1 | 26 | 2 (0)| 00:00:01 | 10554 |00:00:00.03 | 308 | 0 | | | |
|* 6 | INDEX RANGE SCAN | IDX$$_0EFF0001 | 10554 | 2 | | 2 (0)| 00:00:01 | 8995 |00:00:02.96 | 21732 | 280 | | | |
| 7 | BUFFER SORT | | 8995 | 12 | 264 | 4 (0)| 00:00:01 | 191M|00:00:00.07 | 335 | 0 | 2048 | 2048 | 66/0/0|
| 8 | TABLE ACCESS FULL | TMP_TAR_DATES | 66 | 12 | 264 | 2 (0)| 00:00:01 | 23269 |00:00:00.01 | 335 | 0 | | | |
|* 9 | TABLE ACCESS FULL | TMP_CALC_P_ROWS | 65 | 178 | 58562 | 2 (0)| 00:00:01 | 12305 |00:00:00.03 | 313 | 0 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Notice, that last start of this query was failed with "ORA-01652: unable to extend temp segment by string in tablespace string", which had occurred approximately at step 2 of plan. Probably that`s why the run time statistics seems to be inconsistent. Especially between the second and the first steps of plan in "A-Rows" and "Buffers" columns.
For solving this problem I had recommended to create another copy of code of "individual calculations" and slightly change the text of sql queries, which was shared for both calculation approaches.
After implementing my recommendations, the new plan had appeared:
Plan hash value: 3979030168
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 4 | 128K|00:00:05.82 | 447K| | | |
| 2 | NESTED LOOPS | | 1 | 1 | 128K|00:00:04.15 | 318K| | | |
|* 3 | HASH JOIN | | 1 | 8 | 9200 |00:00:00.13 | 338 | 1308K| 1162K| 1820K (0)|
|* 4 | TABLE ACCESS BY INDEX ROWID| PORDER | 1 | 8 | 9200 |00:00:00.03 | 276 | | | |
|* 5 | INDEX RANGE SCAN | XIF1660_PORDER | 1 | 39 | 9200 |00:00:00.01 | 47 | | | |
|* 6 | TABLE ACCESS FULL | TMP_PNF_TO_CALC | 1 | 11167 | 11167 |00:00:00.03 | 62 | | | |
|* 7 | TABLE ACCESS BY INDEX ROWID | TMP_CALC_P_ROWS | 9200 | 1 | 128K|00:00:03.88 | 318K| | | |
|* 8 | INDEX RANGE SCAN | XIF_TCPR_SET1 | 9200 | 26 | 302K|00:00:00.55 | 19333 | | | |
|* 9 | INDEX RANGE SCAN | XIF_TTD_TMP_SET1 | 128K| 3 | 128K|00:00:01.50 | 128K| | | |
------------------------------------------------------------------------------------------------------------------------------------------
I emphasize, that it is only the first stage of optimization with very low cost of implementation. It was allowed to speed up "mass calculation" about three times and reduce the tension of overall project.
Further steps will be a) investigation of each query from top query consumers but more likely b) redesign of whole "mass calculation" algorithm.
