Skip to content

Instantly share code, notes, and snippets.

/newbad plan

Created August 6, 2012 19:57
Show Gist options
  • Save anonymous/3277973 to your computer and use it in GitHub Desktop.
Save anonymous/3277973 to your computer and use it in GitHub Desktop.
Download ZIP
plans
Raw
newbad plan
SQL_ID 9k9186psmmjqt
--------------------
INSERT INTO CPPIPELINEREPORT (EXCEPTIONSTATUS, EXCEPTIONFLAG,
CUSTOMERNAME, ACTUALTIME, TRIPID, EQUIPMENTNUMBER, SHIPDATE,
RLSIGHTINGDATE, LOADEMPTY, SHIPPERNAME, CONSIGNEENAME, ORIGINCITY,
ORIGINSTATE, DESTINATIONCITY, DESTINATIONSTATE, ROUTE, CURRENTLOCATION,
CURRENTSTATUSCODE, CURRENTSIGHTINGDATE,
CURRENTCARRIER,CURRENTSIGHTINGDESTINATIONCITY, LASTKEYEVENTCITY,
LASTKEYEVENTSTATE, LASTKEYEVENT,LASTKEYEVENTSIGHTINGDATE,
NEXTLOCATIONCITY, NEXTEVENTSIGHTINGCODE, NEXTEXPECTEDTIME,
CUTOFFDATE,RLCITY, LASTDAYWEEK, DIFF, EXCEPTIONS, BOLNUMBER, ONTIME,
REPORTTYPE) SELECT * FROM VW_CP_COAL_TIME_BASED_ALERT
Plan hash value: 2037554326 (new plan)
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 1186 (100)| | | |
| 1 | LOAD TABLE CONVENTIONAL | | | | | | | |
| 2 | VIEW | VW_CP_COAL_TIME_BASED_ALERT | 1 | 719 | 1186 (1)| 00:00:06 | | |
| 3 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 4 | LOAD AS SELECT | | | | | | | |
| 5 | WINDOW SORT | | 130 | 23270 | 72 (7)| 00:00:01 | | |
| 6 | WINDOW SORT | | 130 | 23270 | 72 (7)| 00:00:01 | | |
| 7 | HASH JOIN | | 130 | 23270 | 70 (5)| 00:00:01 | | |
| 8 | TABLE ACCESS FULL | TRANSITSCHEDULE | 2 | 50 | 17 (0)| 00:00:01 | | |
| 9 | VIEW | | 510 | 78540 | 52 (4)| 00:00:01 | | |
| 10 | WINDOW BUFFER | | 510 | 64260 | 52 (4)| 00:00:01 | | |
| 11 | SORT GROUP BY | | 510 | 64260 | 52 (4)| 00:00:01 | | |
| 12 | HASH JOIN | | 510 | 64260 | 51 (2)| 00:00:01 | | |
| 13 | TABLE ACCESS BY INDEX ROWID | TRIGGEREVENT_SIGHTING | 11 | 132 | 3 (0)| 00:00:01 | | |
| 14 | INDEX RANGE SCAN | IX_TRGEVENTSIGHTING_CUSTOMER | 22 | | 1 (0)| 00:00:01 | | |
| 15 | NESTED LOOPS | | | | | | | |
| 16 | NESTED LOOPS | | 464 | 52896 | 47 (0)| 00:00:01 | | |
| 17 | TABLE ACCESS FULL | TRANSITSCHEDULE | 6 | 348 | 17 (0)| 00:00:01 | | |
| 18 | INDEX RANGE SCAN | IX_DETAIL_SCHEDULE | 77 | | 2 (0)| 00:00:01 | | |
| 19 | TABLE ACCESS BY INDEX ROWID | TRANSITSCHEDULEDETAIL | 77 | 4312 | 5 (0)| 00:00:01 | | |
| 20 | VIEW | | 1 | 689 | 1115 (1)| 00:00:06 | | |
| 21 | WINDOW SORT | | 1 | 578 | 1115 (1)| 00:00:06 | | |
| 22 | NESTED LOOPS | | 1 | 578 | 1114 (1)| 00:00:06 | | |
| 23 | NESTED LOOPS | | 1 | 569 | 1113 (1)| 00:00:06 | | |
| 24 | NESTED LOOPS | | 1 | 566 | 1113 (1)| 00:00:06 | | |
| 25 | NESTED LOOPS | | 1 | 557 | 1112 (1)| 00:00:06 | | |
| 26 | NESTED LOOPS | | 1 | 531 | 1074 (1)| 00:00:06 | | |
| 27 | NESTED LOOPS | | 1 | 384 | 1072 (1)| 00:00:06 | | |
| 28 | NESTED LOOPS | | 1 | 358 | 1067 (1)| 00:00:06 | | |
| 29 | NESTED LOOPS | | 1 | 298 | 1066 (1)| 00:00:06 | | |
| 30 | HASH JOIN | | 1 | 280 | 1064 (1)| 00:00:06 | | |
| 31 | NESTED LOOPS | | | | | | | |
| 32 | NESTED LOOPS | | 1 | 163 | 1060 (1)| 00:00:06 | | |
| 33 | NESTED LOOPS | | 1 | 158 | 1059 (1)| 00:00:06 | | |
| 34 | HASH JOIN | | 1 | 149 | 1058 (1)| 00:00:05 | | |
| 35 | NESTED LOOPS | | | | | | | |
| 36 | NESTED LOOPS | | 1 | 72 | 1054 (1)| 00:00:05 | | |
| 37 | VIEW | | 1 | 39 | 1053 (1)| 00:00:05 | | |
| 38 | SORT GROUP BY | | 1 | 52 | 1053 (1)| 00:00:05 | | |
| 39 | VIEW | | 1 | 52 | 1052 (1)| 00:00:05 | | |
| 40 | WINDOW SORT PUSHED RANK | | 1 | 136 | 1052 (1)| 00:00:05 | | |
| 41 | NESTED LOOPS | | | | | | | |
| 42 | NESTED LOOPS | | 1 | 136 | 1051 (1)| 00:00:05 | | |
| 43 | NESTED LOOPS | | 1 | 99 | 1013 (1)| 00:00:05 | | |
| 44 | NESTED LOOPS | | 1 | 89 | 1010 (1)| 00:00:05 | | |
| 45 | INDEX FAST FULL SCAN | UK_TRANSITSCHEDULE | 2 | 102 | 9 (0)| 00:00:01 | | |
| 46 | WID TABLE ACCESS BY GLOBAL INDEX RO | SEARCH_DATA | 1 | 38 | 501 (1)| 00:00:03 | 3 | 3 |
| 47 | INDEX RANGE SCAN | IX_SEARCHDATA_DESTINATIONCITY | 1304 | | 10 (0)| 00:00:01 | | |
| 48 | PARTITION LIST SINGLE | | 1 | 10 | 3 (0)| 00:00:01 | KEY | KEY |
| 49 | ID TABLE ACCESS BY LOCAL INDEX ROW | CUSTOMERTRIP | 1 | 10 | 3 (0)| 00:00:01 | 126 | 126 |
| 50 | INDEX RANGE SCAN | IX_CUSTOMERTRIP_TRIP | 1 | | 2 (0)| 00:00:01 | 126 | 126 |
| 51 | INDEX RANGE SCAN | IX_TRIPSIGHTING_TRIPID | 39 | | 3 (0)| 00:00:01 | | |
| 52 | TABLE ACCESS BY GLOBAL INDEX ROWID | TRIPSIGHTING | 2 | 74 | 38 (0)| 00:00:01 | ROWID | ROWID |
| 53 | INDEX UNIQUE SCAN | PK_SIGHTINGLOCATION | 1 | | 0 (0)| | | |
| 54 | TABLE ACCESS BY INDEX ROWID | SIGHTINGLOCATION | 1 | 33 | 1 (0)| 00:00:01 | | |
| 55 | VIEW | | 130 | 10010 | 3 (0)| 00:00:01 | | |
| 56 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6682_AB9BA181 | 130 | 27430 | 3 (0)| 00:00:01 | | |
| 57 | INDEX RANGE SCAN | PK_TRIGGEREVENT_SIGHTING | 2 | 18 | 1 (0)| 00:00:01 | | |
| 58 | INDEX UNIQUE SCAN | PK_TRIGGEREVENT | 1 | | 0 (0)| | | |
| 59 | TABLE ACCESS BY INDEX ROWID | TRIGGEREVENT | 1 | 5 | 1 (0)| 00:00:01 | | |
| 60 | VIEW | | 130 | 15210 | 3 (0)| 00:00:01 | | |
| 61 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6682_AB9BA181 | 130 | 27430 | 3 (0)| 00:00:01 | | |
| 62 | TABLE ACCESS BY INDEX ROWID | SIGHTINGLOCATION | 1 | 18 | 2 (0)| 00:00:01 | | |
| 63 | INDEX RANGE SCAN | IX_UNIQUE_LOCATION | 1 | | 1 (0)| 00:00:01 | | |
| 64 | TABLE ACCESS BY INDEX ROWID | TRANSITSCHEDULE | 1 | 60 | 1 (0)| 00:00:01 | | |
| 65 | INDEX UNIQUE SCAN | PK_TRANSITSCHEDULE | 1 | | 0 (0)| | | |
| 66 | TABLE ACCESS BY INDEX ROWID | TRANSITSCHEDULEDETAIL | 1 | 26 | 5 (0)| 00:00:01 | | |
| 67 | INDEX RANGE SCAN | IX_DETAIL_SCHEDULE | 77 | | 2 (0)| 00:00:01 | | |
| 68 | TABLE ACCESS BY GLOBAL INDEX ROWID | SEARCH_DATA | 1 | 147 | 2 (0)| 00:00:01 | 3 | 3 |
| 69 | INDEX UNIQUE SCAN | PK_SEARCH_DATA | 1 | | 1 (0)| 00:00:01 | | |
| 70 | TABLE ACCESS BY GLOBAL INDEX ROWID | TRIPSIGHTING | 1 | 26 | 38 (0)| 00:00:01 | ROWID | ROWID |
| 71 | INDEX RANGE SCAN | IX_TRIPSIGHTING_TRIPID | 39 | | 3 (0)| 00:00:01 | | |
| 72 | INDEX RANGE SCAN | PK_TRIGGEREVENT_SIGHTING | 1 | 9 | 1 (0)| 00:00:01 | | |
| 73 | INDEX UNIQUE SCAN | PK_TRIGGEREVENT | 1 | 3 | 0 (0)| | | |
| 74 | INDEX RANGE SCAN | PK_TRIGGEREVENT_SIGHTING | 1 | 9 | 1 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Raw
oldgood plan
SQL_ID 9k9186psmmjqt
--------------------
INSERT INTO CPPIPELINEREPORT (EXCEPTIONSTATUS, EXCEPTIONFLAG,
CUSTOMERNAME, ACTUALTIME, TRIPID, EQUIPMENTNUMBER, SHIPDATE,
RLSIGHTINGDATE, LOADEMPTY, SHIPPERNAME, CONSIGNEENAME, ORIGINCITY,
ORIGINSTATE, DESTINATIONCITY, DESTINATIONSTATE, ROUTE, CURRENTLOCATION,
CURRENTSTATUSCODE, CURRENTSIGHTINGDATE,
CURRENTCARRIER,CURRENTSIGHTINGDESTINATIONCITY, LASTKEYEVENTCITY,
LASTKEYEVENTSTATE, LASTKEYEVENT,LASTKEYEVENTSIGHTINGDATE,
NEXTLOCATIONCITY, NEXTEVENTSIGHTINGCODE, NEXTEXPECTEDTIME,
CUTOFFDATE,RLCITY, LASTDAYWEEK, DIFF, EXCEPTIONS, BOLNUMBER, ONTIME,
REPORTTYPE) SELECT * FROM VW_CP_COAL_TIME_BASED_ALERT
Plan hash value: 227154702 (old plan)
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 712 (100)| | | |
| 1 | LOAD TABLE CONVENTIONAL | | | | | | | |
| 2 | VIEW | VW_CP_COAL_TIME_BASED_ALERT | 1 | 719 | 712 (2)| 00:00:04 | | |
| 3 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 4 | LOAD AS SELECT | | | | | | | |
| 5 | WINDOW SORT | | 299 | 53521 | 72 (7)| 00:00:01 | | |
| 6 | WINDOW SORT | | 299 | 53521 | 72 (7)| 00:00:01 | | |
| 7 | HASH JOIN | | 299 | 53521 | 69 (3)| 00:00:01 | | |
| 8 | TABLE ACCESS FULL | TRANSITSCHEDULE | 4 | 100 | 17 (0)| 00:00:01 | | |
| 9 | VIEW | | 458 | 70532 | 52 (4)| 00:00:01 | | |
| 10 | WINDOW BUFFER | | 458 | 57708 | 52 (4)| 00:00:01 | | |
| 11 | SORT GROUP BY | | 458 | 57708 | 52 (4)| 00:00:01 | | |
| 12 | HASH JOIN | | 458 | 57708 | 51 (2)| 00:00:01 | | |
| 13 | TABLE ACCESS BY INDEX ROWID | TRIGGEREVENT_SIGHTING | 11 | 132 | 3 (0)| 00:00:01 | | |
| 14 | INDEX RANGE SCAN | IX_TRGEVENTSIGHTING_CUSTOMER | 22 | | 1 (0)| 00:00:01 | | |
| 15 | NESTED LOOPS | | | | | | | |
| 16 | NESTED LOOPS | | 458 | 52212 | 47 (0)| 00:00:01 | | |
| 17 | TABLE ACCESS FULL | TRANSITSCHEDULE | 6 | 348 | 17 (0)| 00:00:01 | | |
| 18 | INDEX RANGE SCAN | IX_DETAIL_SCHEDULE | 78 | | 2 (0)| 00:00:01 | | |
| 19 | TABLE ACCESS BY INDEX ROWID | TRANSITSCHEDULEDETAIL | 78 | 4368 | 5 (0)| 00:00:01 | | |
| 20 | VIEW | | 1 | 689 | 640 (1)| 00:00:04 | | |
| 21 | WINDOW SORT | | 1 | 577 | 640 (1)| 00:00:04 | | |
| 22 | NESTED LOOPS | | 1 | 577 | 639 (1)| 00:00:04 | | |
| 23 | NESTED LOOPS | | 1 | 568 | 638 (1)| 00:00:04 | | |
| 24 | NESTED LOOPS | | 1 | 542 | 633 (1)| 00:00:03 | | |
| 25 | HASH JOIN | | 1 | 524 | 632 (1)| 00:00:03 | | |
| 26 | NESTED LOOPS | | 1 | 407 | 627 (1)| 00:00:03 | | |
| 27 | NESTED LOOPS | | 1 | 404 | 627 (1)| 00:00:03 | | |
| 28 | NESTED LOOPS | | 1 | 395 | 626 (1)| 00:00:03 | | |
| 29 | NESTED LOOPS | | 1 | 369 | 585 (1)| 00:00:03 | | |
| 30 | HASH JOIN | | 1 | 360 | 584 (1)| 00:00:03 | | |
| 31 | NESTED LOOPS | | | | | | | |
| 32 | NESTED LOOPS | | 1 | 355 | 580 (1)| 00:00:03 | | |
| 33 | NESTED LOOPS | | 1 | 208 | 578 (1)| 00:00:03 | | |
| 34 | HASH JOIN | | 1 | 149 | 577 (1)| 00:00:03 | | |
| 35 | NESTED LOOPS | | | | | | | |
| 36 | NESTED LOOPS | | 1 | 72 | 572 (1)| 00:00:03 | | |
| 37 | VIEW | | 1 | 39 | 571 (1)| 00:00:03 | | |
| 38 | SORT GROUP BY | | 1 | 52 | 571 (1)| 00:00:03 | | |
| 39 | VIEW | | 1 | 52 | 570 (1)| 00:00:03 | | |
| 40 | WINDOW SORT PUSHED RANK | | 1 | 136 | 570 (1)| 00:00:03 | | |
| 41 | NESTED LOOPS | | | | | | | |
| 42 | NESTED LOOPS | | 1 | 136 | 569 (1)| 00:00:03 | | |
| 43 | NESTED LOOPS | | 1 | 99 | 528 (1)| 00:00:03 | | |
| 44 | NESTED LOOPS | | 1 | 89 | 525 (1)| 00:00:03 | | |
| 45 | INDEX RANGE SCAN | UK_TRANSITSCHEDULE | 1 | 51 | 7 (0)| 00:00:01 | | |
| 46 | WID TABLE ACCESS BY GLOBAL INDEX RO | SEARCH_DATA | 1 | 38 | 518 (1)| 00:00:03 | 3 | 3 |
| 47 | INDEX RANGE SCAN | IX_SEARCHDATA_DESTINATIONCITY | 1330 | | 10 (0)| 00:00:01 | | |
| 48 | PARTITION LIST SINGLE | | 1 | 10 | 3 (0)| 00:00:01 | KEY | KEY |
| 49 | ID TABLE ACCESS BY LOCAL INDEX ROW | CUSTOMERTRIP | 1 | 10 | 3 (0)| 00:00:01 | 126 | 126 |
| 50 | INDEX RANGE SCAN | IX_CUSTOMERTRIP_TRIP | 1 | | 2 (0)| 00:00:01 | 126 | 126 |
| 51 | INDEX RANGE SCAN | IX_TRIPSIGHTING_TRIPID | 42 | | 3 (0)| 00:00:01 | | |
| 52 | TABLE ACCESS BY GLOBAL INDEX ROWID | TRIPSIGHTING | 2 | 74 | 41 (0)| 00:00:01 | ROWID | ROWID |
| 53 | INDEX UNIQUE SCAN | PK_SIGHTINGLOCATION | 1 | | 0 (0)| | | |
| 54 | TABLE ACCESS BY INDEX ROWID | SIGHTINGLOCATION | 1 | 33 | 1 (0)| 00:00:01 | | |
| 55 | VIEW | | 299 | 23023 | 5 (0)| 00:00:01 | | |
| 56 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662F_AB9BA181 | 299 | 63089 | 5 (0)| 00:00:01 | | |
| 57 | TABLE ACCESS BY INDEX ROWID | TRANSITSCHEDULE | 1 | 59 | 1 (0)| 00:00:01 | | |
| 58 | INDEX UNIQUE SCAN | PK_TRANSITSCHEDULE | 1 | | 0 (0)| | | |
| 59 | INDEX UNIQUE SCAN | PK_SEARCH_DATA | 1 | | 1 (0)| 00:00:01 | | |
| 60 | TABLE ACCESS BY GLOBAL INDEX ROWID | SEARCH_DATA | 1 | 147 | 2 (0)| 00:00:01 | 3 | 3 |
| 61 | TABLE ACCESS FULL | TRIGGEREVENT | 13 | 65 | 3 (0)| 00:00:01 | | |
| 62 | INDEX RANGE SCAN | PK_TRIGGEREVENT_SIGHTING | 1 | 9 | 1 (0)| 00:00:01 | | |
| 63 | TABLE ACCESS BY GLOBAL INDEX ROWID | TRIPSIGHTING | 42 | 1092 | 41 (0)| 00:00:01 | ROWID | ROWID |
| 64 | INDEX RANGE SCAN | IX_TRIPSIGHTING_TRIPID | 42 | | 3 (0)| 00:00:01 | | |
| 65 | INDEX RANGE SCAN | PK_TRIGGEREVENT_SIGHTING | 1 | 9 | 1 (0)| 00:00:01 | | |
| 66 | INDEX UNIQUE SCAN | PK_TRIGGEREVENT | 1 | 3 | 0 (0)| | | |
| 67 | VIEW | | 299 | 34983 | 5 (0)| 00:00:01 | | |
| 68 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662F_AB9BA181 | 299 | 63089 | 5 (0)| 00:00:01 | | |
| 69 | TABLE ACCESS BY INDEX ROWID | SIGHTINGLOCATION | 1 | 18 | 1 (0)| 00:00:01 | | |
| 70 | INDEX UNIQUE SCAN | PK_SIGHTINGLOCATION | 1 | | 0 (0)| | | |
| 71 | TABLE ACCESS BY INDEX ROWID | TRANSITSCHEDULEDETAIL | 1 | 26 | 5 (0)| 00:00:01 | | |
| 72 | INDEX RANGE SCAN | IX_DETAIL_SCHEDULE | 78 | | 2 (0)| 00:00:01 | | |
| 73 | INDEX RANGE SCAN | PK_TRIGGEREVENT_SIGHTING | 1 | 9 | 1 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment