11g, Sherlock Holming it with Statistics, Histograms and Bind Peeking
Yes, my dear Watson, Bind Peeking is still an issue in 11g….
We recently upgraded from 10.2.0.4 to 11.2.0.2.0 and come across a couple interesting performance challenges. I do like this type of problem and have been quite busy working on the fixes for each one that has shown itself. This was one of the latest, requiring a solid, positive relationship between the developer and the DBA to solve- The DBA may have the answer, but the developer must be comfortable with the solution and make the code/design change that is required to correct the issue. I do work with a number of such developers, so this was a quick fix in our fast-paced environment…
Oracle’s Cost Based Optimizer makes the best decisions when the design and the code are in accordance with “best practices” as well. I know the word “best practices” is a bit over-used, so we will discuss first what this means.
In the example I’ll be using in my case below, the following is the design practices that should have been applied when the objects were first introduced to production, but were not. I wanted to include the example so that folks could see just how many different ways Oracle could slice and dice the data when deciding what path to take through the database to a completion of a process.
Best practice in this case would be:
1. partitions with similar sizing.
2. No truncated partitions.
3. Single column partitioning keys.
4. Stage tables that are utilized for a process and then promptly emptied after the completion of the process.
Scenario:
Performance challenge on one step in feed process. This process utilizes the following steps:
1. Chooses a “partition ID”, which is the partitioning key on a stage table. There is no logic to the partitioning key, (p_id) simply chosen as part of the steps from a list marked as a truncated partition that is ready to be utilized.
2. Inserts into this partition. Some loads can be quite small, (less than 1M, others can be quite large, 100G+.
3. Merge statement is then performed on stage table to delete from partition any duplicates and other non-loading indicators.
4. This partition is then used to join to the final table to decide what is inserted, updated and deleted from another partition. The final partition has the same vulnerability in design of askew sizing in partition data.
5. The partition in the staging table is then truncated.
First Step by the DBA:
dbms_xplan.display_awr
Twelve different explain plans were found to have been used for the insert statement in question.
Second step by the DBA:
Checked table, partition and column stats.
Select count(column_name) Col_cnt, last_analyzed from dba_tab_columns where table_name='CUSTOMER_ACCOUNTS' group by Last_analyzed;
COL_CNT LAST_ANALYZED 27 1/24/2012 3:09:58 AM 3 2/23/2012 12:41:23 PM
The columns with the newest date in question are the indexed columns, which is good in this environment. The bad is the amount of histograms on columns, we have already researched and found our waits are on the data dictionary and the scheduler in 11g since moving to collecting histograms on tons of columns, on tons of partitions that we do not use in any way for where clauses, etc., but no way to notify the database of this in the largely CTAS type process code base.
So, we now know we do have new stats, histograms were bloated, set to auto on method_opt, but they are still accurate and up to date. The performance issue started before the date we changed over to the method option on the stats..
Question by DBA: How does the database know what and how to build histograms when different logic is used depending on the code, when a partition can be used by any one of the processe? This results in the CBO making poor decisions on column stats.
This is quickly verified when column stats are compared between one partition and the next for large feeds using the same logic last use of the partition.
Unique keys are multi-column, partitioning key is askew on final tables. This key is in where clause, so we do have accurate information outside of the CTAS code/stats histogram issue. With more data involving histograms and partition data askew across the partitions, does this leave us vulnerable to bind peeking?
DBA Test case for bind peeking:
After pinpointing the hash_value for the plan used by the poor performing, large processing version of the performance issue, detail what the values for both bind variables that would have been used.
Replaced the bind variables, one at a time, with the actual values, noted the change in execution plan.
Noted that when the partition was a literal vs. the bind variable, the execution plan changed drastically and contained valid values for the cost and row count for the process.
Although the :B1 bind variable appears to be just as susceptible to the bind peek, it is the partitioning key for the staging table, (CTS1) that is the true culprit on large processing.
DBA recommendation:
Change from inserting into a partition to performing a CTAS for the logic involved, bypassing any deletes or merges, simply creating the table with the data it requires, uniquely named.
Use this in the insert’s subselect to eliminate the bind peek issue.
Summary:
The simple concept behind the “CTS1” table was that is was a “parking lot”, where a process could simply find an empty parking space, (partition) and proceed to utilize it. The problem is that Oracle can’t figure out if it’s just parked a smart car or a semi in the spot. To eliminate the issue, the parking lot was removed by going to a working table vs. the partitioned stage table and then the database simply had to work with whatever size of “vehicle” it had been issued in the process, (post stats collection, of course!)
DBMS_XPLAN.DISPLAY_AWR Output: PLAN_TABLE_OUTPUT SQL_ID 7rjk8mr85s2nq -------------------- INSERT INTO DW_USR.CT1 (COL1,COL2,COL3,COL4,COL5,COL6,COL7..... COL18,COL19,COL20) SELECT * FROM ( SELECT S.COL1, S.COL2, S.COL3, ROW_NUMBER () OVER (PARTITION BY S.COL5, S.COL6,S.COL7, S.COL8 ORDER BY S.COL12) COL6,S.COL2, S.COL12, S.COL15, S.COL19, S.COL5, S.COL15, S.COL22, S.COL27, S.LAST_FILE_ID, S.COL28, S.COL29, S.COL31, S.COL33, S.COL35, S.COL36, S.COL37, S.COL38, S.COL39, S.COL40, S.COL43, S.COL44, S.F1, S.F2, S.F3, S.F4, S.F5, S.F6, S.F7, S.F8, S.F9, S.F10, S.COL45, S.COL46, S.COL47, S.COL48, S.COL81 FROM ( SELECT /*+ use_hash(cts ca) */ CTS.COL5, CA.COL3, CTS.COL6, CTS.COl76 AS COL6, CTS.COL2, CTS.COL12, CTS.COL15, NULL AS COL19, CTS.COL5, CTS.COL15, CTS.COL22, CTS.COL27, CTS.COL7 AS LFID, CTS.COL28, CTS.COL17, CTS.COL31, CTS.COL33, CTS.COL35, CTS.COL36, CTS.COL37, CTS.COL38, CTS.COL39, CTS.COL40 AS COL40, :B5 AS COL43, :B5 AS COL44, CTS.F1, CTS.F2, CTS.F3, CTS.F4, CTS.F5, CTS.F6, CTS.F7, CTS.F8, CTS.F9, CTS.F10, CTS.COL45, :B4 AS COL46, :B3 AS COL47, CTS.COL48, CTS.COL49, CTS.COL50, CTS.COL51, CTS.COL52, CTS.COL53, CTS.COL54, CTS.COL55, CTS.COL48, CTS.COL28 FROM DW_USR.CTS1 CTS, (SELECT COL2, COL3, COL5 FROM (SELECT COL2, COL3, COL5, ROW_NUMBER () OVER(PARTITION BY COL2, COL5 ORDER BY COL43 DESC, IND_ID) C_RNK FROM DW_USR.CA1 WHERE COL2 = :B1 ) C WHERE C_RNK = 1) CA WHERE CTS.P_ID = :B2 AND CTS.COL2 = :B1 AND CTS.COL2 = CA.COL2 (+) AND CTS.COL5 = CA.COL5 (+) AND CTS.COL72 = 'N' ) S ) X WHERE X.COL3 IS NOT NULL AND NOT EXISTS ( SELECT /*+ use_hash(ct) */ 1 FROM DW_USR.CT1 CT WHERE X.COL9 = CT.COL9 AND X.COL4 = CT.COL4 AND X.COL2 = CT.COL2 AND X.COL2 = CT.COL3 AND CT.COL3 = :B1 )
Plan hash value: 653243445
------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | 1659 | | | | 1 | HASH JOIN RIGHT ANTI | | 23 | 110K| 1659 | | | | 2 | PARTITION LIST SINGLE | | 1 | 52 | 1 | KEY | KEY | | 3 | TABLE ACCESS BY LOCAL INDEX ROWID | CT1 | 1 | 52 | 1 | KEY | KEY | | 4 | INDEX RANGE SCAN | CT1_IDX | 1 | | 1 | KEY | KEY | | 5 | VIEW | | 23 | 109K| 1657 | | | | 6 | WINDOW SORT | | 23 | 4232 | 1657 | | | | 7 | HASH JOIN | | 23 | 4232 | 1644 | | | | 8 | VIEW | | 1 | 56 | 14 | | | | 9 | WINDOW SORT PUSHED RANK | | 1 | 52 | 14 | | | | 10 | FILTER | | | | | | | | 11 | PARTITION LIST SINGLE | | 1 | 52 | 1 | KEY | KEY | | 12 | TABLE ACCESS BY LOCAL INDEX ROWID| CA1 | 1 | 52 | 1 | KEY | KEY | | 13 | INDEX RANGE SCAN | CA1 _PK | 1 | | 1 | KEY | KEY | | 14 | PARTITION RANGE SINGLE | | 11M| 1346M| 1385 | KEY | KEY | | 15 | TABLE ACCESS FULL | CTS1 | 11M| 1346M| 1385 | KEY | KEY | -------------------------------------------------------------------------------------------------------------------
Note ----- - cpu costing is off (consider enabling it)
Plan hash value: 1492830811
--------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop | --------------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | | 23810 | | | | 1 | HASH JOIN RIGHT ANTI | | 23 | 110K| | 23810 | | | | 2 | PARTITION LIST SINGLE | | 1 | 52 | | 1 | KEY | KEY | | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| CT1 | 1 | 52 | | 1 | KEY | KEY | | 4 | INDEX RANGE SCAN | CT1_IDX | 1 | | | 1 | KEY | KEY | | 5 | VIEW | | 23 | 109K| | 23808 | | | | 6 | WINDOW SORT | | 23 | 4646 | | 23808 | | | | 7 | HASH JOIN | | 23 | 4646 | | 23795 | | | | 8 | PARTITION RANGE SINGLE | | 1 | 146 | | 20 | KEY | KEY | | 9 | TABLE ACCESS FULL | CTS1 | 1 | 146 | | 20 | KEY | KEY | | 10 | VIEW | | 14M| 753M| | 23623 | | | | 11 | WINDOW SORT PUSHED RANK | | 14M| 457M| 1293M| 23623 | | | | 12 | FILTER | | | | | | | | | 13 | PARTITION LIST SINGLE | | 14M| 457M| | 2267 | KEY | KEY | | 14 | TABLE ACCESS FULL | CA1 | 14M| 457M| | 2267 | KEY | KEY | ---------------------------------------------------------------------------------------------------------------------
Note ----- - cpu costing is off (consider enabling it)
Plan hash value: 1588626705
------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | | 3699K| | | | 1 | HASH JOIN RIGHT ANTI | | 62M| 287G| 2977M| 3699K| | | | 2 | PARTITION LIST SINGLE | | 70M| 2165M| | 10018 | KEY | KEY | | 3 | INDEX FAST FULL SCAN | CTS1_PK | 70M| 2165M| | 10018 | KEY | KEY | | 4 | VIEW | | 62M| 285G| | 546K| | | | 5 | WINDOW SORT | | 62M| 9461M| 27G| 546K| | | | 6 | HASH JOIN | | 62M| 9461M| 2794M| 182K| | | | 7 | VIEW | | 43M| 2301M| | 75523 | | | | 8 | WINDOW SORT PUSHED RANK| | 43M| 1315M| 3620M| 75523 | | | | 9 | FILTER | | | | | | | | | 10 | PARTITION LIST SINGLE| | 43M| 1315M| | 13076 | KEY | KEY | | 11 | TABLE ACCESS FULL | CA1 | 43M| 1315M| | 13076 | KEY | KEY | | 12 | PARTITION RANGE SINGLE | | 62M| 6107M| | 5407 | KEY | KEY | | 13 | TABLE ACCESS FULL | CTS1 | 62M| 6107M| | 5407 | KEY | KEY | -------------------------------------------------------------------------------------------------------------
Note ----- - cpu costing is off (consider enabling it)
Plan hash value: 1672751078
------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------------ | 0 | INSERT STATEMENT | | | | | 235K(100)| | | | | 1 | LOAD TABLE CONVENTIONAL | | | | | | | | | | 2 | HASH JOIN ANTI | | 1355 | 6522K| 632M| 235K (7)| 00:03:24 | | | | 3 | VIEW | | 135K| 631M| | 134K (10)| 00:01:57 | | | | 4 | WINDOW SORT | | 135K| 24M| 26M| 134K (10)| 00:01:57 | | | | 5 | HASH JOIN | | 135K| 24M| 18M| 132K (10)| 00:01:55 | | | | 6 | PARTITION RANGE SINGLE | | 135K| 16M| | 287 (32)| 00:00:01 | KEY | KEY | | 7 | TABLE ACCESS FULL | CTS1 | 135K| 16M| | 287 (32)| 00:00:01 | KEY | KEY | | 8 | VIEW | | 11M| 610M| | 90184 (13)| 00:01:19 | | | | 9 | WINDOW SORT PUSHED RANK| | 11M| 338M| 480M| 90184 (13)| 00:01:19 | | | | 10 | FILTER | | | | | | | | | | 11 | PARTITION LIST SINGLE| | 11M| 338M| | 38574 (9)| 00:00:34 | KEY | KEY | | 12 | TABLE ACCESS FULL | CA1 | 11M| 338M| | 38574 (9)| 00:00:34 | KEY | KEY | | 13 | PARTITION LIST SINGLE | | 20M| 840M| | 6426 (5)| 00:00:06 | KEY | KEY | | 14 | INDEX RANGE SCAN | CTS1_PK | 20M| 840M| | 6426 (5)| 00:00:06 | KEY | KEY | ------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1930179405
------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------------ | 0 | INSERT STATEMENT | | | | | 1572K(100)| | | | | 1 | LOAD TABLE CONVENTIONAL | | | | | | | | | | 2 | HASH JOIN RIGHT ANTI | | 3033K| 13G| 691M| 1572K (2)| 00:22:44 | | | | 3 | PARTITION LIST SINGLE | | 17M| 489M| | 4192 (6)| 00:00:04 | KEY | KEY | | 4 | INDEX RANGE SCAN | CTS1_PK | 17M| 489M| | 4192 (6)| 00:00:04 | KEY | KEY | | 5 | VIEW | | 5152K| 23G| | 246K (9)| 00:03:34 | | | | 6 | WINDOW SORT | | 5152K| 835M| 909M| 246K (9)| 00:03:34 | | | | 7 | HASH JOIN | | 5152K| 835M| 619M| 155K (11)| 00:02:15 | | | | 8 | PARTITION RANGE SINGLE | | 5152K| 560M| | 11305 (31)| 00:00:10 | KEY | KEY | | 9 | TABLE ACCESS FULL | CTS1 | 5152K| 560M| | 11305 (31)| 00:00:10 | KEY | KEY | | 10 | VIEW | | 11M| 608M| | 69790 (16)| 00:01:01 | | | | 11 | WINDOW SORT PUSHED RANK| | 11M| 325M| 478M| 69790 (16)| 00:01:01 | | | | 12 | FILTER | | | | | | | | | | 13 | PARTITION LIST SINGLE| | 11M| 325M| | 20402 (16)| 00:00:18 | KEY | KEY | | 14 | TABLE ACCESS FULL | CA1 | 11M| 325M| | 20402 (16)| 00:00:18 | KEY | KEY | ------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2157924392
-------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop | -------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | | 267K| | | | 1 | HASH JOIN RIGHT ANTI | | 4459K| 20G| 152M| 267K| | | | 2 | PARTITION LIST SINGLE | | 3204K| 116M| | 564 | KEY | KEY | | 3 | TABLE ACCESS FULL | CT1 | 3204K| 116M| | 564 | KEY | KEY | | 4 | VIEW | | 4459K| 20G| | 44503 | | | | 5 | WINDOW SORT | | 4459K| 723M| 1574M| 44503 | | | | 6 | HASH JOIN | | 4459K| 723M| 357M| 16818 | | | | 7 | VIEW | | 5516K| 294M| | 8663 | | | | 8 | WINDOW SORT PUSHED RANK| | 5516K| 173M| 505M| 8663 | | | | 9 | FILTER | | | | | | | | | 10 | PARTITION LIST SINGLE| | 5516K| 173M| | 492 | KEY | KEY | | 11 | TABLE ACCESS FULL | CA1 | 5516K| 173M| | 492 | KEY | KEY | | 12 | PARTITION RANGE SINGLE | | 3052K| 331M| | 329 | KEY | KEY | | 13 | TABLE ACCESS FULL | C | 3052K| 331M| | 329 | KEY | KEY | --------------------------------------------------------------------------------------------------------------
Note ----- - cpu costing is off (consider enabling it)
Plan hash value: 2900900913
--------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop | --------------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | | 306K| | | | 1 | HASH JOIN RIGHT ANTI | | 35M| 164G| | 306K| | | | 2 | PARTITION LIST SINGLE | | 1 | 52 | | 1 | KEY | KEY | | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| CT1 | 1 | 52 | | 1 | KEY | KEY | | 4 | INDEX RANGE SCAN | CT1_IDX | 1 | | | 1 | KEY | KEY | | 5 | VIEW | | 35M| 163G| | 278K| | | | 6 | WINDOW SORT | | 35M| 6046M| 16G| 278K| | | | 7 | HASH JOIN | | 35M| 6046M| 687M| 47011 | | | | 8 | VIEW | | 10M| 566M| | 17018 | | | | 9 | WINDOW SORT PUSHED RANK | | 10M| 343M| 971M| 17018 | | | | 10 | FILTER | | | | | | | | | 11 | PARTITION LIST SINGLE | | 10M| 343M| | 988 | KEY | KEY | | 12 | TABLE ACCESS FULL | CA1 | 10M| 343M| | 988 | KEY | KEY | | 13 | PARTITION RANGE SINGLE | | 14M| 1624M| | 1595 | KEY | KEY | | 14 | TABLE ACCESS FULL | CTS1 | 14M| 1624M| | 1595 | KEY | KEY | ---------------------------------------------------------------------------------------------------------------------
Note ----- - cpu costing is off (consider enabling it)
Plan hash value: 2973217172
------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | | 208K| | | | 1 | HASH JOIN RIGHT ANTI | | 6346K| 29G| | 208K| | | | 2 | PARTITION LIST SINGLE | | 12908 | 630K| | 3 | KEY | KEY | | 3 | INDEX RANGE SCAN | CTS1_PK | 12908 | 630K| | 3 | KEY | KEY | | 4 | VIEW | | 6346K| 28G| | 85943 | | | | 5 | WINDOW SORT | | 6346K| 1373M| 2916M| 85943 | | | | 6 | HASH JOIN | | 6346K| 1373M| 533M| 33969 | | | | 7 | VIEW | | 8231K| 439M| | 15389 | | | | 8 | WINDOW SORT PUSHED RANK| | 8231K| 282M| 754M| 15389 | | | | 9 | FILTER | | | | | | | | | 10 | PARTITION LIST SINGLE| | 8231K| 282M| | 2423 | KEY | KEY | | 11 | TABLE ACCESS FULL | CA1 | 8231K| 282M| | 2423 | KEY | KEY | | 12 | PARTITION RANGE SINGLE | | 6346K| 1034M| | 1171 | KEY | KEY | | 13 | TABLE ACCESS FULL | CTS1 | 6346K| 1034M| | 1171 | KEY | KEY | -------------------------------------------------------------------------------------------------------------
Note ----- - cpu costing is off (consider enabling it)
****This was the hash value used in our poorest performer!!!!*****
Plan hash value: 3005115193
------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | | 89378 | | | | 1 | HASH JOIN RIGHT ANTI | | 287K| 1347M| 912M| 89378 | | | | 2 | PARTITION LIST SINGLE | | 22M| 658M| | 2193 | KEY | KEY | | 3 | INDEX FAST FULL SCAN | CTS1_PK | 22M| 658M| | 2193 | KEY | KEY | | 4 | VIEW | | 287K| 1338M| | 63268 | | | | 5 | WINDOW SORT | | 287K| 41M| 92M| 63268 | | | | 6 | HASH JOIN | | 287K| 41M| 29M| 61637 | | | | 7 | PARTITION RANGE SINGLE | | 287K| 26M| | 25 | KEY | KEY | | 8 | TABLE ACCESS FULL | CTS1 | 287K| 26M| | 25 | KEY | KEY | | 9 | VIEW | | 27M| 1446M| | 42660 | | | | 10 | WINDOW SORT PUSHED RANK| | 27M| 852M| 2482M| 42660 | | | | 11 | FILTER | | | | | | | | | 12 | PARTITION LIST SINGLE| | 27M| 852M| | 2548 | KEY | KEY | | 13 | TABLE ACCESS FULL | CA1 | 27M| 852M| | 2548 | KEY | KEY | -------------------------------------------------------------------------------------------------------------
Note ----- - cpu costing is off (consider enabling it)
Plan hash value: 3149367802
-------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop | -------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | | 87386 | | | | 1 | HASH JOIN RIGHT ANTI | | 1260K| 5917M| 210M| 87386 | | | | 2 | PARTITION LIST SINGLE | | 4603K| 158M| | 327 | KEY | KEY | | 3 | TABLE ACCESS FULL | CT1 | 4603K| 158M| | 327 | KEY | KEY | | 4 | VIEW | | 1260K| 5874M| | 22365 | | | | 5 | WINDOW SORT | | 1260K| 251M| 539M| 22365 | | | | 6 | HASH JOIN | | 1260K| 251M| 198M| 12826 | | | | 7 | PARTITION RANGE SINGLE | | 1260K| 183M| | 142 | KEY | KEY | | 8 | TABLE ACCESS FULL | CTS1 | 1260K| 183M| | 142 | KEY | KEY | | 9 | VIEW | | 4701K| 251M| | 7346 | | | | 10 | WINDOW SORT PUSHED RANK| | 4701K| 152M| 430M| 7346 | | | | 11 | FILTER | | | | | | | | | 12 | PARTITION LIST SINGLE| | 4701K| 152M| | 238 | KEY | KEY | | 13 | TABLE ACCESS FULL | CA1 | 4701K| 152M| | 238 | KEY | KEY | --------------------------------------------------------------------------------------------------------------
Note ----- - cpu costing is off (consider enabling it)
Plan hash value: 3517833265
------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | | 142K| | | | 1 | HASH JOIN ANTI | | 37130 | 174M| 173M| 142K| | | | 2 | VIEW | | 37130 | 173M| | 130K| | | | 3 | WINDOW SORT | | 37130 | 6563K| 13M| 130K| | | | 4 | HASH JOIN | | 37130 | 6563K| 4992K| 130K| | | | 5 | PARTITION RANGE SINGLE | | 37130 | 4532K| | 5 | KEY | KEY | | 6 | TABLE ACCESS FULL | CTS1 | 37130 | 4532K| | 5 | KEY | KEY | | 7 | VIEW | | 57M| 3089M| | 90806 | | | | 8 | WINDOW SORT PUSHED RANK| | 57M| 1655M| 4859M| 90806 | | | | 9 | FILTER | | | | | | | | | 10 | PARTITION LIST SINGLE| | 57M| 1655M| | 12448 | KEY | KEY | | 11 | TABLE ACCESS FULL | CA1 | 57M| 1655M| | 12448 | KEY | KEY | | 12 | PARTITION LIST SINGLE | | 14M| 548M| | 2629 | KEY | KEY | | 13 | INDEX FAST FULL SCAN | CTS1_PK | 14M| 548M| | 2629 | KEY | KEY | -------------------------------------------------------------------------------------------------------------
Note ----- - cpu costing is off (consider enabling it)
Plan hash value: 3518559863
------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | 49 | | | | 1 | HASH JOIN ANTI | | 1 | 4924 | 49 | | | | 2 | VIEW | | 1 | 4887 | 32 | | | | 3 | WINDOW SORT | | 1 | 154 | 32 | | | | 4 | HASH JOIN | | 1 | 154 | 19 | | | | 5 | VIEW | | 1 | 56 | 14 | | | | 6 | WINDOW SORT PUSHED RANK | | 1 | 52 | 14 | | | | 7 | FILTER | | | | | | | | 8 | PARTITION LIST SINGLE | | 1 | 52 | 1 | KEY | KEY | | 9 | TABLE ACCESS BY LOCAL INDEX ROWID| CA1 | 1 | 52 | 1 | KEY | KEY | | 10 | INDEX RANGE SCAN | CA1 _PK | 1 | | 1 | KEY | KEY | | 11 | PARTITION RANGE SINGLE | | 23150 | 2215K| 4 | KEY | KEY | | 12 | TABLE ACCESS FULL | CTS1 | 23150 | 2215K| 4 | KEY | KEY | | 13 | PARTITION LIST SINGLE | | 1963K| 69M| 1 | KEY | KEY | | 14 | TABLE ACCESS BY LOCAL INDEX ROWID | CT1 | 1963K| 69M| 1 | KEY | KEY | | 15 | INDEX RANGE SCAN | CT1_IDX | 1333 | | 1 | KEY | KEY | -------------------------------------------------------------------------------------------------------------------
Note ----- - cpu costing is off (consider enabling it)