Índice do fórum Treinamentos Avançados Treinamento Oracle Tuning - SQL Selecionando o melhor plano de execução

Selecionando o melhor plano de execução

Dúvidas, dicas e atualizações sobre o Treinamento Oracle Tuning - SQL.


Mensagens: 0
Localização: Germany

Olá Portilho, estou analizando uma query em especifico, e gostaria de uma opinião para qual das execuções pode ser a melhor...

SQL_ID 7w9r45cvq9phg
--------------------
Plan hash value: 157066266
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 875 (100)| |
| 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 5 | 26600 | 875 (2)| 00:00:06 |
| 3 | SORT ORDER BY STOPKEY | | 5 | 26600 | 875 (2)| 00:00:06 |
| 4 | VIEW | | 5 | 26600 | 874 (2)| 00:00:06 |
| 5 | SORT UNIQUE | | 5 | 4514 | 874 (2)| 00:00:06 |
| 6 | UNION-ALL | | | | | |
| 7 | NESTED LOOPS | | 1 | 344 | 130 (2)| 00:00:01 |
| 8 | HASH JOIN OUTER | | 1 | 291 | 126 (2)| 00:00:01 |
| 9 | MAT_VIEW ACCESS BY INDEX ROWID | MV2_MODELS | 1 | 237 | 78 (3)| 00:00:01 |
| 10 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 11 | BITMAP AND | | | | | |
| 12 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 13 | SORT ORDER BY | | | | | |
| 14 | DOMAIN INDEX | IDX2_MOD_NAME | | | 3 (0)| 00:00:01 |
| 15 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 16 | INDEX RANGE SCAN | IDX2_MOD_PUBLISH | | | 67 (0)| 00:00:01 |
| 17 | MAT_VIEW ACCESS FULL | MV2_THEMES2MODELS | 22016 | 1161K| 48 (0)| 00:00:01 |
| 18 | MAT_VIEW ACCESS BY INDEX ROWID | MV2_MODEL_ATTRIBUTES | 1 | 53 | 130 (2)| 00:00:01 |
| 19 | DOMAIN INDEX | IDX2_MOA_VALUE | 2 | | 4 (0)| 00:00:01 |
| 20 | HASH JOIN OUTER | | 1 | 610 | 53 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 1 | 556 | 5 (0)| 00:00:01 |
| 22 | MAT_VIEW ACCESS BY INDEX ROWID | MV2_MODELS | 1 | 503 | 1 (0)| 00:00:01 |
| 23 | DOMAIN INDEX | IDX2_MOD_DESCRIPTION | | | 1 (0)| 00:00:01 |
| 24 | MAT_VIEW ACCESS BY INDEX ROWID | MV2_MODEL_ATTRIBUTES | 1 | 53 | 4 (0)| 00:00:01 |
| 25 | INDEX RANGE SCAN | MODEL_ATTRIBUTES_PK2 | 2 | | 2 (0)| 00:00:01 |
| 26 | MAT_VIEW ACCESS FULL | MV2_THEMES2MODELS | 22016 | 1161K| 48 (0)| 00:00:01 |
| 27 | HASH JOIN OUTER | | 1 | 344 | 56 (2)| 00:00:01 |
| 28 | NESTED LOOPS | | 1 | 290 | 7 (0)| 00:00:01 |
| 29 | MAT_VIEW ACCESS BY INDEX ROWID | MV2_MODELS | 1 | 237 | 3 (0)| 00:00:01 |
| 30 | DOMAIN INDEX | IDX2_MOD_ABSTRACT | | | 1 (0)| 00:00:01 |
| 31 | MAT_VIEW ACCESS BY INDEX ROWID | MV2_MODEL_ATTRIBUTES | 1 | 53 | 4 (0)| 00:00:01 |
| 32 | INDEX RANGE SCAN | MODEL_ATTRIBUTES_PK2 | 2 | | 2 (0)| 00:00:01 |
| 33 | MAT_VIEW ACCESS FULL | MV2_THEMES2MODELS | 22016 | 1161K| 48 (0)| 00:00:01 |
| 34 | HASH JOIN OUTER | | 1 | 407 | 93 (2)| 00:00:01 |
| 35 | NESTED LOOPS | | 1 | 353 | 45 (3)| 00:00:01 |
| 36 | NESTED LOOPS | | 1 | 300 | 41 (3)| 00:00:01 |
| 37 | SORT UNIQUE | | 68 | 4284 | 32 (0)| 00:00:01 |
| 38 | MAT_VIEW ACCESS BY INDEX ROWID| MV2_INSTANCES | 68 | 4284 | 32 (0)| 00:00:01 |
| 39 | DOMAIN INDEX | IDX2_INS_NAME | | | 14 (0)| 00:00:01 |
| 40 | MAT_VIEW ACCESS BY INDEX ROWID | MV2_MODELS | 1 | 237 | 1 (0)| 00:00:01 |
| 41 | INDEX UNIQUE SCAN | MODELS_PK2 | 1 | | 0 (0)| |
| 42 | MAT_VIEW ACCESS BY INDEX ROWID | MV2_MODEL_ATTRIBUTES | 1 | 53 | 4 (0)| 00:00:01 |
| 43 | INDEX RANGE SCAN | MODEL_ATTRIBUTES_PK2 | 2 | | 2 (0)| 00:00:01 |
| 44 | MAT_VIEW ACCESS FULL | MV2_THEMES2MODELS | 22016 | 1161K| 48 (0)| 00:00:01 |
| 45 | HASH JOIN OUTER | | 1 | 552 | 536 (1)| 00:00:04 |
| 46 | NESTED LOOPS | | 1 | 498 | 488 (1)| 00:00:04 |
| 47 | MERGE JOIN CARTESIAN | | 2 | 934 | 484 (1)| 00:00:04 |
| 48 | NESTED LOOPS | | 1 | 290 | 436 (1)| 00:00:03 |
| 49 | MAT_VIEW ACCESS FULL | MV2_MODELS | 8 | 1896 | 404 (1)| 00:00:03 |
| 50 | MAT_VIEW ACCESS BY INDEX ROWID| MV2_MODEL_ATTRIBUTES | 1 | 53 | 436 (1)| 00:00:03 |
| 51 | DOMAIN INDEX | IDX2_MOA_VALUE | 2 | | 4 (0)| 00:00:01 |
| 52 | BUFFER SORT | | 190 | 33630 | 48 (0)| 00:00:01 |
| 53 | MAT_VIEW ACCESS BY INDEX ROWID| MV2_INSTANCE_ATTRIBUTES | 190 | 33630 | 484 (1)| 00:00:04 |
| 54 | DOMAIN INDEX | IDX2_INA_VALUE | | | 48 (0)| 00:00:01 |
| 55 | INDEX RANGE SCAN | IDX2_INS_ID_LANG_MODID | 1 | 31 | 2 (0)| 00:00:01 |
| 56 | MAT_VIEW ACCESS FULL | MV2_THEMES2MODELS | 22016 | 1161K| 48 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

SQL_ID 7w9r45cvq9phg
--------------------
Plan hash value: 2360753451
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 867 (100)| |
| 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 5 | 26600 | 867 (2)| 00:00:06 |
| 3 | SORT ORDER BY STOPKEY | | 5 | 26600 | 867 (2)| 00:00:06 |
| 4 | VIEW | | 5 | 26600 | 866 (2)| 00:00:06 |
| 5 | SORT UNIQUE | | 5 | 4508 | 866 (2)| 00:00:06 |
| 6 | UNION-ALL | | | | | |
| 7 | HASH JOIN OUTER | | 1 | 343 | 68 (2)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 289 | 23 (5)| 00:00:01 |
| 9 | MAT_VIEW ACCESS BY INDEX ROWID | MV2_MODELS | 1 | 237 | 20 (5)| 00:00:01 |
| 10 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 11 | BITMAP AND | | | | | |
| 12 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 13 | SORT ORDER BY | | | | | |
| 14 | DOMAIN INDEX | IDX2_MOD_NAME | | | 3 (0)| 00:00:01 |
| 15 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 16 | INDEX RANGE SCAN | IDX2_MOD_PUBLISH | | | 11 (0)| 00:00:01 |
| 17 | MAT_VIEW ACCESS BY INDEX ROWID | MV2_MODEL_ATTRIBUTES | 1 | 52 | 3 (0)| 00:00:01 |
| 18 | INDEX RANGE SCAN | MODEL_ATTRIBUTES_PK2 | 2 | | 2 (0)| 00:00:01 |
| 19 | INDEX FAST FULL SCAN | THEMES2MODELS_PK2 | 22034 | 1161K| 45 (0)| 00:00:01 |
| 20 | HASH JOIN OUTER | | 1 | 610 | 49 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 1 | 556 | 4 (0)| 00:00:01 |
| 22 | MAT_VIEW ACCESS BY INDEX ROWID | MV2_MODELS | 1 | 504 | 1 (0)| 00:00:01 |
| 23 | DOMAIN INDEX | IDX2_MOD_DESCRIPTION | | | 1 (0)| 00:00:01 |
| 24 | MAT_VIEW ACCESS BY INDEX ROWID | MV2_MODEL_ATTRIBUTES | 1 | 52 | 3 (0)| 00:00:01 |
| 25 | INDEX RANGE SCAN | MODEL_ATTRIBUTES_PK2 | 2 | | 2 (0)| 00:00:01 |
| 26 | INDEX FAST FULL SCAN | THEMES2MODELS_PK2 | 22034 | 1161K| 45 (0)| 00:00:01 |
| 27 | HASH JOIN OUTER | | 1 | 343 | 51 (0)| 00:00:01 |
| 28 | NESTED LOOPS | | 1 | 289 | 6 (0)| 00:00:01 |
| 29 | MAT_VIEW ACCESS BY INDEX ROWID | MV2_MODELS | 1 | 237 | 3 (0)| 00:00:01 |
| 30 | DOMAIN INDEX | IDX2_MOD_ABSTRACT | | | 1 (0)| 00:00:01 |
| 31 | MAT_VIEW ACCESS BY INDEX ROWID | MV2_MODEL_ATTRIBUTES | 1 | 52 | 3 (0)| 00:00:01 |
| 32 | INDEX RANGE SCAN | MODEL_ATTRIBUTES_PK2 | 2 | | 2 (0)| 00:00:01 |
| 33 | INDEX FAST FULL SCAN | THEMES2MODELS_PK2 | 22034 | 1161K| 45 (0)| 00:00:01 |
| 34 | HASH JOIN OUTER | | 1 | 406 | 88 (2)| 00:00:01 |
| 35 | NESTED LOOPS | | 1 | 352 | 43 (3)| 00:00:01 |
| 36 | NESTED LOOPS | | 1 | 300 | 40 (3)| 00:00:01 |
| 37 | SORT UNIQUE | | 69 | 4347 | 33 (0)| 00:00:01 |
| 38 | MAT_VIEW ACCESS BY INDEX ROWID| MV2_INSTANCES | 69 | 4347 | 33 (0)| 00:00:01 |
| 39 | DOMAIN INDEX | IDX2_INS_NAME | | | 14 (0)| 00:00:01 |
| 40 | MAT_VIEW ACCESS BY INDEX ROWID | MV2_MODELS | 1 | 237 | 1 (0)| 00:00:01 |
| 41 | INDEX UNIQUE SCAN | MODELS_PK2 | 1 | | 0 (0)| |
| 42 | MAT_VIEW ACCESS BY INDEX ROWID | MV2_MODEL_ATTRIBUTES | 1 | 52 | 3 (0)| 00:00:01 |
| 43 | INDEX RANGE SCAN | MODEL_ATTRIBUTES_PK2 | 2 | | 2 (0)| 00:00:01 |
| 44 | INDEX FAST FULL SCAN | THEMES2MODELS_PK2 | 22034 | 1161K| 45 (0)| 00:00:01 |
| 45 | HASH JOIN OUTER | | 1 | 552 | 604 (1)| 00:00:05 |
| 46 | NESTED LOOPS | | 1 | 498 | 558 (1)| 00:00:04 |
| 47 | MERGE JOIN CARTESIAN | | 4 | 1868 | 550 (1)| 00:00:04 |
| 48 | NESTED LOOPS | | 1 | 289 | 422 (1)| 00:00:03 |
| 49 | MAT_VIEW ACCESS FULL | MV2_MODELS | 6 | 1422 | 404 (1)| 00:00:03 |
| 50 | MAT_VIEW ACCESS BY INDEX ROWID| MV2_MODEL_ATTRIBUTES | 1 | 52 | 3 (0)| 00:00:01 |
| 51 | INDEX RANGE SCAN | MODEL_ATTRIBUTES_PK2 | 2 | | 2 (0)| 00:00:01 |
| 52 | BUFFER SORT | | 568 | 98K| 547 (1)| 00:00:04 |
| 53 | MAT_VIEW ACCESS BY INDEX ROWID| MV2_INSTANCE_ATTRIBUTES | 568 | 98K| 550 (1)| 00:00:04 |
| 54 | DOMAIN INDEX | IDX2_INA_VALUE | | | 128 (0)| 00:00:01 |
| 55 | INDEX RANGE SCAN | IDX2_INS_ID_LANG_MODID | 1 | 31 | 2 (0)| 00:00:01 |
| 56 | INDEX FAST FULL SCAN | THEMES2MODELS_PK2 | 22034 | 1161K| 45 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

SQL_ID 7w9r45cvq9phg
--------------------
Plan hash value: 2444774446
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 917 (100)| |
| 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 5 | 26600 | 917 (2)| 00:00:07 |
| 3 | SORT ORDER BY STOPKEY | | 5 | 26600 | 917 (2)| 00:00:07 |
| 4 | VIEW | | 5 | 26600 | 916 (2)| 00:00:07 |
| 5 | SORT UNIQUE | | 5 | 4506 | 916 (2)| 00:00:07 |
| 6 | UNION-ALL | | | | | |
| 7 | HASH JOIN OUTER | | 1 | 343 | 137 (2)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 289 | 88 (2)| 00:00:01 |
| 9 | MAT_VIEW ACCESS BY INDEX ROWID | MV1_MODELS | 1 | 237 | 85 (2)| 00:00:01 |
| 10 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 11 | BITMAP AND | | | | | |
| 12 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 13 | SORT ORDER BY | | | | | |
| 14 | DOMAIN INDEX | IDX1_MOD_NAME | | | 3 (0)| 00:00:01 |
| 15 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 16 | INDEX RANGE SCAN | IDX1_MOD_PUBLISH | | | 75 (0)| 00:00:01 |
| 17 | MAT_VIEW ACCESS BY INDEX ROWID | MV1_MODEL_ATTRIBUTES | 1 | 52 | 3 (0)| 00:00:01 |
| 18 | INDEX RANGE SCAN | MODEL_ATTRIBUTES_PK1 | 2 | | 2 (0)| 00:00:01 |
| 19 | MAT_VIEW ACCESS FULL | MV1_THEMES2MODELS | 22127 | 1166K| 48 (0)| 00:00:01 |
| 20 | HASH JOIN OUTER | | 1 | 610 | 52 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 1 | 556 | 4 (0)| 00:00:01 |
| 22 | MAT_VIEW ACCESS BY INDEX ROWID | MV1_MODELS | 1 | 504 | 1 (0)| 00:00:01 |
| 23 | DOMAIN INDEX | IDX1_MOD_DESCRIPTION | | | 1 (0)| 00:00:01 |
| 24 | MAT_VIEW ACCESS BY INDEX ROWID | MV1_MODEL_ATTRIBUTES | 1 | 52 | 3 (0)| 00:00:01 |
| 25 | INDEX RANGE SCAN | MODEL_ATTRIBUTES_PK1 | 2 | | 2 (0)| 00:00:01 |
| 26 | MAT_VIEW ACCESS FULL | MV1_THEMES2MODELS | 22127 | 1166K| 48 (0)| 00:00:01 |
| 27 | NESTED LOOPS | | 1 | 343 | 54 (0)| 00:00:01 |
| 28 | HASH JOIN OUTER | | 1 | 291 | 51 (0)| 00:00:01 |
| 29 | MAT_VIEW ACCESS BY INDEX ROWID | MV1_MODELS | 1 | 237 | 3 (0)| 00:00:01 |
| 30 | DOMAIN INDEX | IDX1_MOD_ABSTRACT | | | 1 (0)| 00:00:01 |
| 31 | MAT_VIEW ACCESS FULL | MV1_THEMES2MODELS | 22127 | 1166K| 48 (0)| 00:00:01 |
| 32 | MAT_VIEW ACCESS BY INDEX ROWID | MV1_MODEL_ATTRIBUTES | 1 | 52 | 3 (0)| 00:00:01 |
| 33 | INDEX RANGE SCAN | MODEL_ATTRIBUTES_PK1 | 2 | | 2 (0)| 00:00:01 |
| 34 | HASH JOIN OUTER | | 1 | 406 | 90 (2)| 00:00:01 |
| 35 | NESTED LOOPS | | 1 | 352 | 42 (3)| 00:00:01 |
| 36 | NESTED LOOPS | | 1 | 300 | 39 (3)| 00:00:01 |
| 37 | SORT UNIQUE | | 63 | 3969 | 30 (0)| 00:00:01 |
| 38 | MAT_VIEW ACCESS BY INDEX ROWID| MV1_INSTANCES | 63 | 3969 | 30 (0)| 00:00:01 |
| 39 | DOMAIN INDEX | IDX1_INS_NAME | | | 13 (0)| 00:00:01 |
| 40 | MAT_VIEW ACCESS BY INDEX ROWID | MV1_MODELS | 1 | 237 | 1 (0)| 00:00:01 |
| 41 | INDEX UNIQUE SCAN | MODELS_PK1 | 1 | | 0 (0)| |
| 42 | MAT_VIEW ACCESS BY INDEX ROWID | MV1_MODEL_ATTRIBUTES | 1 | 52 | 3 (0)| 00:00:01 |
| 43 | INDEX RANGE SCAN | MODEL_ATTRIBUTES_PK1 | 2 | | 2 (0)| 00:00:01 |
| 44 | MAT_VIEW ACCESS FULL | MV1_THEMES2MODELS | 22127 | 1166K| 48 (0)| 00:00:01 |
| 45 | HASH JOIN OUTER | | 1 | 551 | 577 (1)| 00:00:04 |
| 46 | NESTED LOOPS | | 1 | 497 | 528 (1)| 00:00:04 |
| 47 | MERGE JOIN CARTESIAN | | 4 | 1864 | 520 (1)| 00:00:04 |
| 48 | NESTED LOOPS | | 1 | 289 | 428 (1)| 00:00:03 |
| 49 | MAT_VIEW ACCESS FULL | MV1_MODELS | 8 | 1896 | 404 (1)| 00:00:03 |
| 50 | MAT_VIEW ACCESS BY INDEX ROWID| MV1_MODEL_ATTRIBUTES | 1 | 52 | 3 (0)| 00:00:01 |
| 51 | INDEX RANGE SCAN | MODEL_ATTRIBUTES_PK1 | 2 | | 2 (0)| 00:00:01 |
| 52 | BUFFER SORT | | 394 | 69738 | 517 (1)| 00:00:04 |
| 53 | MAT_VIEW ACCESS BY INDEX ROWID| MV1_INSTANCE_ATTRIBUTES | 394 | 69738 | 520 (1)| 00:00:04 |
| 54 | DOMAIN INDEX | IDX1_INA_VALUE | | | 92 (0)| 00:00:01 |
| 55 | INDEX RANGE SCAN | IDX1_INS_ID_LANG_MODID | 1 | 31 | 2 (0)| 00:00:01 |
| 56 | MAT_VIEW ACCESS FULL | MV1_THEMES2MODELS | 22127 | 1166K| 48 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

SQL_ID 7w9r45cvq9phg
--------------------
Plan hash value: 2807705601
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 946 (100)| |
| 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 5 | 26600 | 946 (2)| 00:00:07 |
| 3 | SORT ORDER BY STOPKEY | | 5 | 26600 | 946 (2)| 00:00:07 |
| 4 | VIEW | | 5 | 26600 | 945 (2)| 00:00:07 |
| 5 | SORT UNIQUE | | 5 | 4504 | 945 (2)| 00:00:07 |
| 6 | UNION-ALL | | | | | |
| 7 | HASH JOIN OUTER | | 1 | 343 | 151 (1)| 00:00:02 |
| 8 | NESTED LOOPS | | 1 | 289 | 103 (1)| 00:00:01 |
| 9 | MAT_VIEW ACCESS BY INDEX ROWID | MV2_MODELS | 1 | 237 | 100 (1)| 00:00:01 |
| 10 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 11 | BITMAP AND | | | | | |
| 12 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 13 | SORT ORDER BY | | | | | |
| 14 | DOMAIN INDEX | IDX2_MOD_NAME | | | 3 (0)| 00:00:01 |
| 15 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 16 | INDEX RANGE SCAN | IDX2_MOD_PUBLISH | | | 88 (0)| 00:00:01 |
| 17 | MAT_VIEW ACCESS BY INDEX ROWID | MV2_MODEL_ATTRIBUTES | 1 | 52 | 3 (0)| 00:00:01 |
| 18 | INDEX RANGE SCAN | MODEL_ATTRIBUTES_PK2 | 2 | | 2 (0)| 00:00:01 |
| 19 | MAT_VIEW ACCESS FULL | MV2_THEMES2MODELS | 22126 | 1166K| 48 (0)| 00:00:01 |
| 20 | HASH JOIN OUTER | | 1 | 609 | 52 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 1 | 555 | 4 (0)| 00:00:01 |
| 22 | MAT_VIEW ACCESS BY INDEX ROWID | MV2_MODELS | 1 | 503 | 1 (0)| 00:00:01 |
| 23 | DOMAIN INDEX | IDX2_MOD_DESCRIPTION | | | 1 (0)| 00:00:01 |
| 24 | MAT_VIEW ACCESS BY INDEX ROWID | MV2_MODEL_ATTRIBUTES | 1 | 52 | 3 (0)| 00:00:01 |
| 25 | INDEX RANGE SCAN | MODEL_ATTRIBUTES_PK2 | 2 | | 2 (0)| 00:00:01 |
| 26 | MAT_VIEW ACCESS FULL | MV2_THEMES2MODELS | 22126 | 1166K| 48 (0)| 00:00:01 |
| 27 | HASH JOIN OUTER | | 1 | 343 | 55 (0)| 00:00:01 |
| 28 | NESTED LOOPS | | 1 | 289 | 7 (0)| 00:00:01 |
| 29 | MAT_VIEW ACCESS BY INDEX ROWID | MV2_MODELS | 1 | 237 | 4 (0)| 00:00:01 |
| 30 | DOMAIN INDEX | IDX2_MOD_ABSTRACT | | | 1 (0)| 00:00:01 |
| 31 | MAT_VIEW ACCESS BY INDEX ROWID | MV2_MODEL_ATTRIBUTES | 1 | 52 | 3 (0)| 00:00:01 |
| 32 | INDEX RANGE SCAN | MODEL_ATTRIBUTES_PK2 | 2 | | 2 (0)| 00:00:01 |
| 33 | MAT_VIEW ACCESS FULL | MV2_THEMES2MODELS | 22126 | 1166K| 48 (0)| 00:00:01 |
| 34 | HASH JOIN OUTER | | 1 | 406 | 95 (2)| 00:00:01 |
| 35 | NESTED LOOPS | | 1 | 352 | 47 (3)| 00:00:01 |
| 36 | NESTED LOOPS | | 2 | 600 | 41 (3)| 00:00:01 |
| 37 | SORT UNIQUE | | 64 | 4032 | 30 (0)| 00:00:01 |
| 38 | MAT_VIEW ACCESS BY INDEX ROWID| MV2_INSTANCES | 64 | 4032 | 30 (0)| 00:00:01 |
| 39 | DOMAIN INDEX | IDX2_INS_NAME | | | 13 (0)| 00:00:01 |
| 40 | MAT_VIEW ACCESS BY INDEX ROWID | MV2_MODELS | 1 | 237 | 1 (0)| 00:00:01 |
| 41 | INDEX UNIQUE SCAN | MODELS_PK2 | 1 | | 0 (0)| |
| 42 | MAT_VIEW ACCESS BY INDEX ROWID | MV2_MODEL_ATTRIBUTES | 1 | 52 | 3 (0)| 00:00:01 |
| 43 | INDEX RANGE SCAN | MODEL_ATTRIBUTES_PK2 | 2 | | 2 (0)| 00:00:01 |
| 44 | MAT_VIEW ACCESS FULL | MV2_THEMES2MODELS | 22126 | 1166K| 48 (0)| 00:00:01 |
| 45 | HASH JOIN OUTER | | 1 | 551 | 586 (1)| 00:00:04 |
| 46 | NESTED LOOPS | | 1 | 497 | 538 (1)| 00:00:04 |
| 47 | MERGE JOIN CARTESIAN | | 5 | 2330 | 528 (1)| 00:00:04 |
| 48 | NESTED LOOPS | | 1 | 289 | 436 (1)| 00:00:03 |
| 49 | MAT_VIEW ACCESS FULL | MV2_MODELS | 10 | 2370 | 406 (1)| 00:00:03 |
| 50 | MAT_VIEW ACCESS BY INDEX ROWID| MV2_MODEL_ATTRIBUTES | 1 | 52 | 3 (0)| 00:00:01 |
| 51 | INDEX RANGE SCAN | MODEL_ATTRIBUTES_PK2 | 2 | | 2 (0)| 00:00:01 |
| 52 | BUFFER SORT | | 395 | 69915 | 525 (1)| 00:00:04 |
| 53 | MAT_VIEW ACCESS BY INDEX ROWID| MV2_INSTANCE_ATTRIBUTES | 395 | 69915 | 528 (1)| 00:00:04 |
| 54 | DOMAIN INDEX | IDX2_INA_VALUE | | | 92 (0)| 00:00:01 |
| 55 | INDEX RANGE SCAN | IDX2_INS_ID_LANG_MODID | 1 | 31 | 2 (0)| 00:00:01 |
| 56 | MAT_VIEW ACCESS FULL | MV2_THEMES2MODELS | 22126 | 1166K| 48 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
--
Mit Freundlichen Grüßen / Saludos / Best Regards / Saudações
IT Expert, DBA Oracle, DBA RAC Oracle, DMA Oracle, Solaris, Linux, OS X.

André Augusto Ribas
andre.ribas@icloud.com
dba.ribas@gmail.com
DE: +49 (0)151 660 76921

portilho Site Admin

Mensagens: 463
Oi !
O menor custo é o melhor plano, desde que as estatísticas (incluindo System Statistics) estejam corretas.
Não deu para ver muito bem o restante do plano, se utilizar a marca "Code" como abaixo fica melhor.

Exemplo


A forma mais real de extrair um plano de execução é pela DISPLAY_CURSOR, se possível co o parâmetro (pode ser apenas em SESSION) STATISTICS_LEVEL=ALL.


Voltar para Treinamento Oracle Tuning - SQL

cron