OPTIMIZAR SELECT ORACLE 10g

[rcastaneda]

Mi nuevo plan de ejecucion quedo asi.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1296477202

------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 198K| 29M| | 2907K (19)| 08:52:58 | | | | | |
| 1 | UNION-ALL | | | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10008 | 198K| 29M| | 2521K (6)| 07:42:16 | | | Q1,08 | P->S | QC (RAND) |
|* 4 | HASH JOIN BUFFERED | | 198K| 29M| | 2521K (6)| 07:42:16 | | | Q1,08 | PCWP | |
| 5 | PX RECEIVE | | 198K| 25M| | 2509K (6)| 07:40:06 | | | Q1,08 | PCWP | |
| 6 | PX SEND HASH | :TQ10006 | 198K| 25M| | 2509K (6)| 07:40:06 | | | Q1,06 | P->P | HASH |
|* 7 | HASH JOIN | | 198K| 25M| | 2509K (6)| 07:40:06 | | | Q1,06 | PCWP | |
| 8 | BUFFER SORT | | | | | | | | | Q1,06 | PCWC | |
| 9 | PX RECEIVE | | 419 | 3352 | | 3 (0)| 00:00:01 | | | Q1,06 | PCWP | |
| 10 | PX SEND BROADCAST | :TQ10001 | 419 | 3352 | | 3 (0)| 00:00:01 | | | | S->P | BROADCAST |
| 11 | TABLE ACCESS FULL | PPGA_TIPBONO | 419 | 3352 | | 3 (0)| 00:00:01 | | | | | |
|* 12 | HASH JOIN | | 198K| 24M| | 2509K (6)| 07:40:06 | | | Q1,06 | PCWP | |
| 13 | BUFFER SORT | | | | | | | | | Q1,06 | PCWC | |
| 14 | PX RECEIVE | | 1716 | 24024 | | 4 (0)| 00:00:01 | | | Q1,06 | PCWP | |
| 15 | PX SEND BROADCAST | :TQ10002 | 1716 | 24024 | | 4 (0)| 00:00:01 | | | | S->P | BROADCAST |
| 16 | TABLE ACCESS FULL | PPGA_BONOS | 1716 | 24024 | | 4 (0)| 00:00:01 | | | | | |
|* 17 | HASH JOIN | | 68M| 7351M| 76M| 2509K (6)| 07:40:04 | | | Q1,06 | PCWP | |
| 18 | PX RECEIVE | | 5990K| 542M| | 2084K (7)| 06:22:14 | | | Q1,06 | PCWP | |
| 19 | PX SEND HASH | :TQ10005 | 5990K| 542M| | 2084K (7)| 06:22:14 | | | Q1,05 | P->P | HASH |
|* 20 | HASH JOIN | | 5990K| 542M| 29M| 2084K (7)| 06:22:14 | | | Q1,05 | PCWP | |
| 21 | PX RECEIVE | | 5990K| 165M| | 945K (2)| 02:53:24 | | | Q1,05 | PCWP | |
| 22 | PX SEND HASH | :TQ10004 | 5990K| 165M| | 945K (2)| 02:53:24 | | | Q1,04 | P->P | HASH |
| 23 | PX BLOCK ITERATOR | | 5990K| 165M| | 945K (2)| 02:53:24 | 1 | 20 | Q1,04 | PCWC | |
|* 24 | TABLE ACCESS FULL | PPGA_ACTABOPRE | 5990K| 165M| | 945K (2)| 02:53:24 | 1 | 20 | Q1,04 | PCWP | |
| 25 | BUFFER SORT | | | | | | | | | Q1,05 | PCWC | |
| 26 | PX RECEIVE | | 19M| 1223M| | 1127K (10)| 03:26:45 | | | Q1,05 | PCWP | |
| 27 | PX SEND HASH | :TQ10000 | 19M| 1223M| | 1127K (10)| 03:26:45 | | | | S->P | HASH |
| 28 | PARTITION LIST ALL | | 19M| 1223M| | 1127K (10)| 03:26:45 | 1 | 20 | | | |
|* 29 | TABLE ACCESS FULL | PPGA_ADQUISICIONES | 19M| 1223M| | 1127K (10)| 03:26:45 | 1 | 20 | | | |
| 30 | BUFFER SORT | | | | | | | | | Q1,06 | PCWC | |
| 31 | PX RECEIVE | | 228M| 3921M| | 375K (3)| 01:08:50 | | | Q1,06 | PCWP | |
| 32 | PX SEND HASH | :TQ10003 | 228M| 3921M| | 375K (3)| 01:08:50 | | | | S->P | HASH |
| 33 | PARTITION LIST ALL | | 228M| 3921M| | 375K (3)| 01:08:50 | 1 | 20 | | | |
| 34 | TABLE ACCESS FULL | PPGA_BONOPRE | 228M| 3921M| | 375K (3)| 01:08:50 | 1 | 20 | | | |
| 35 | PX RECEIVE | | 23M| 469M| | 11799 (9)| 00:02:10 | | | Q1,08 | PCWP | |
| 36 | PX SEND HASH | :TQ10007 | 23M| 469M| | 11799 (9)| 00:02:10 | | | Q1,07 | P->P | HASH |
| 37 | PX BLOCK ITERATOR | | 23M| 469M| | 11799 (9)| 00:02:10 | 1 | 20 | Q1,07 | PCWC | |
|* 38 | TABLE ACCESS FULL | PPGA_ABOPREPCOM | 23M| 469M| | 11799 (9)| 00:02:10 | 1 | 20 | Q1,07 | PCWP | |
|* 39 | FILTER | | | | | | | | | | | |
| 40 | NESTED LOOPS | | 19 | 1482 | | 385K (6)| 01:10:43 | | | | | |
| 41 | NESTED LOOPS | | 19 | 1292 | | 385K (6)| 01:10:43 | | | | | |
| 42 | PARTITION LIST ALL | | 22 | 968 | | 385K (6)| 01:10:42 | 1 | 20 | | | |
|* 43 | TABLE ACCESS FULL | PPGA_BONOPRE | 22 | 968 | | 385K (6)| 01:10:42 | 1 | 20 | | | |
| 44 | PARTITION LIST ITERATOR | | 1 | 24 | | 2 (0)| 00:00:01 | KEY | KEY | | | |
|* 45 | TABLE ACCESS BY LOCAL INDEX ROWID| PPGA_ABOPREPCOM | 1 | 24 | | 2 (0)| 00:00:01 | KEY | KEY | | | |
|* 46 | INDEX UNIQUE SCAN | PK_ABOPREPCOM | 1 | | | 1 (0)| 00:00:01 | KEY | KEY | | | |
| 47 | TABLE ACCESS BY INDEX ROWID | PPGA_TIPBONO | 1 | 10 | | 1 (0)| 00:00:01 | | | | | |
|* 48 | INDEX UNIQUE SCAN | PK_PPGA_TIPBONO | 1 | | | 0 (0)| 00:00:01 | | | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------