PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. _yetCkBFaEeybd5Btn3fajw _yfCZwhFaEeybd5Btn3fajw 170 SQL_ID 22f6y8yx7txxf, child number 0 ------------------------------------- SELECT /* orig */ /*+ gather_plan_statistics */ t1.item_id, t1.state_id, t1.jz_discriminator FROM (SELECT t1q.*, T1V.item_type_dbid FROM qa_qm.execution_execution_task_reslt t1q INNER JOIN qa_qm.repository_version T1V ON T1V.state_id = t1q.state_id WHERE T1V.item_type_dbid = :v1 AND T1V.config_id = :v2 AND T1V.is_current = 1 AND t1q.jz_discriminator = :v3) t1 LEFT OUTER JOIN qa_qm.process_process_area t3 ON ( t1.project_area_item_id = t3.item_id ) WHERE ( EXISTS (SELECT 1 FROM qa_qm.execution_exctn_rslt_stp_rslts x1 WHERE t1.execution_result_step_results = x1.jz_parent_id AND x1.item_id = :v4) AND ( t3.project_area_item_id = '_0yZ0cL1WEei-d-PmOaCn5A' ) ) Plan hash value: 3027614311 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 60034 (100)| | 1 |00:00:12.76 | 4545K| | | | | 1 | NESTED LOOPS | | 1 | 1 | 209 | 60034 (1)| 00:00:03 | 1 |00:00:12.76 | 4545K| | | | | 2 | NESTED LOOPS | | 1 | 1 | 209 | 60034 (1)| 00:00:03 | 1 |00:00:12.76 | 4545K| | | | |* 3 | HASH JOIN RIGHT SEMI | | 1 | 1 | 161 | 60033 (1)| 00:00:03 | 1 |00:00:12.76 | 4545K| 1355K| 1355K| 432K (0)| |* 4 | INDEX RANGE SCAN | XCTNXCTNXCTNRSLTSTPRSLTSHNDLDX | 1 | 1 | 48 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | | | 5 | VIEW | | 1 | 282K| 30M| 60032 (1)| 00:00:03 | 1494K|00:00:13.16 | 4545K| | | | | 6 | NESTED LOOPS | | 1 | 282K| 40M| 60032 (1)| 00:00:03 | 1494K|00:00:12.93 | 4545K| | | | | 7 | NESTED LOOPS | | 1 | 282K| 40M| 60032 (1)| 00:00:03 | 1494K|00:00:06.83 | 3051K| | | | | 8 | TABLE ACCESS BY INDEX ROWID BATCHED| REPOSITORY_VERSION | 1 | 282K| 14M| 3479 (1)| 00:00:01 | 1494K|00:00:01.53 | 63356 | | | | |* 9 | INDEX RANGE SCAN | REPOSITOR_VRSN_DBD_CNFG_SCRRNT | 1 | 282K| | 165 (0)| 00:00:01 | 1494K|00:00:00.35 | 8793 | | | | |* 10 | INDEX UNIQUE SCAN | XCTNXCTNXCTNTSKRSLTPK0 | 1494K| 1 | | 1 (0)| 00:00:01 | 1494K|00:00:04.68 | 2988K| | | | |* 11 | TABLE ACCESS BY INDEX ROWID | EXECUTION_EXECUTION_TASK_RESLT | 1494K| 1 | 95 | 1 (0)| 00:00:01 | 1494K|00:00:05.56 | 1494K| | | | |* 12 | INDEX UNIQUE SCAN | PROCSS_PRCSS_PRCSS_R_PK | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | | | | |* 13 | TABLE ACCESS BY INDEX ROWID | PROCESS_PROCESS_AREA | 1 | 1 | 48 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$A5F16742 4 - SEL$A5F16742 / X1@SEL$5 5 - SEL$64EAE176 / T1@SEL$1 6 - SEL$64EAE176 8 - SEL$64EAE176 / T1V@SEL$2 9 - SEL$64EAE176 / T1V@SEL$2 10 - SEL$64EAE176 / T1Q@SEL$2 11 - SEL$64EAE176 / T1Q@SEL$2 12 - SEL$A5F16742 / T3@SEL$1 13 - SEL$A5F16742 / T3@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('19.1.0') DB_VERSION('19.1.0') OPT_PARAM('_complex_view_merging' 'false') OPT_PARAM('_optimizer_use_feedback' 'false') OPT_PARAM('_px_adaptive_dist_method' 'off') OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false') OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false') OPT_PARAM('optimizer_index_cost_adj' 10) FIRST_ROWS(100) OUTLINE_LEAF(@"SEL$64EAE176") MERGE(@"SEL$2" >"SEL$3") OUTLINE_LEAF(@"SEL$A5F16742") UNNEST(@"SEL$5") OUTLINE(@"SEL$3") OUTLINE(@"SEL$2") OUTLINE(@"SEL$78AF12E3") OUTER_JOIN_TO_INNER(@"SEL$7ADEC907" "T3"@"SEL$1") OUTLINE(@"SEL$5") OUTLINE(@"SEL$7ADEC907") MERGE(@"SEL$8812AA4E" >"SEL$F6245AF9") OUTLINE(@"SEL$F6245AF9") ANSI_REARCH(@"SEL$4") OUTLINE(@"SEL$8812AA4E") ANSI_REARCH(@"SEL$1") OUTLINE(@"SEL$4") OUTLINE(@"SEL$1") NO_ACCESS(@"SEL$A5F16742" "T1"@"SEL$1") INDEX(@"SEL$A5F16742" "X1"@"SEL$5" ("EXECUTION_EXCTN_RSLT_STP_RSLTS"."ITEM_ID" "EXECUTION_EXCTN_RSLT_STP_RSLTS"."JZ_PARENT_ID")) INDEX(@"SEL$A5F16742" "T3"@"SEL$1" ("PROCESS_PROCESS_AREA"."ITEM_ID")) LEADING(@"SEL$A5F16742" "T1"@"SEL$1" "X1"@"SEL$5" "T3"@"SEL$1") USE_HASH(@"SEL$A5F16742" "X1"@"SEL$5") USE_NL(@"SEL$A5F16742" "T3"@"SEL$1") NLJ_BATCHING(@"SEL$A5F16742" "T3"@"SEL$1") SWAP_JOIN_INPUTS(@"SEL$A5F16742" "X1"@"SEL$5") INDEX_RS_ASC(@"SEL$64EAE176" "T1V"@"SEL$2" ("REPOSITORY_VERSION"."ITEM_TYPE_DBID" "REPOSITORY_VERSION"."CONFIG_ID" "REPOSITORY_VERSION"."IS_CURRENT")) BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$64EAE176" "T1V"@"SEL$2") INDEX(@"SEL$64EAE176" "T1Q"@"SEL$2" ("EXECUTION_EXECUTION_TASK_RESLT"."STATE_ID")) LEADING(@"SEL$64EAE176" "T1V"@"SEL$2" "T1Q"@"SEL$2") USE_NL(@"SEL$64EAE176" "T1Q"@"SEL$2") NLJ_BATCHING(@"SEL$64EAE176" "T1Q"@"SEL$2") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T1"."EXECUTION_RESULT_STEP_RESULTS"="X1"."JZ_PARENT_ID") 4 - access("X1"."ITEM_ID"=:V4) 9 - access("T1V"."ITEM_TYPE_DBID"=:V1 AND "T1V"."CONFIG_ID"=:V2 AND "T1V"."IS_CURRENT"=1) 10 - access("T1V"."STATE_ID"="T1Q"."STATE_ID") 11 - filter("T1Q"."JZ_DISCRIMINATOR"=:V3) 12 - access("T1"."PROJECT_AREA_ITEM_ID"="T3"."ITEM_ID") 13 - filter("T3"."PROJECT_AREA_ITEM_ID"='_0yZ0cL1WEei-d-PmOaCn5A') Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "T1"."STATE_ID"[CHARACTER,23], "T1"."ITEM_ID"[CHARACTER,23], "T1"."JZ_DISCRIMINATOR"[NUMBER,22] 2 - "T1"."STATE_ID"[CHARACTER,23], "T1"."ITEM_ID"[CHARACTER,23], "T1"."JZ_DISCRIMINATOR"[NUMBER,22], "T3".ROWID[ROWID,10] 3 - (#keys=1) "T1"."STATE_ID"[CHARACTER,23], "T1"."ITEM_ID"[CHARACTER,23], "T1"."PROJECT_AREA_ITEM_ID"[CHARACTER,23], "T1"."JZ_DISCRIMINATOR"[NUMBER,22] 4 - "X1"."JZ_PARENT_ID"[CHARACTER,23] 5 - "T1"."STATE_ID"[CHARACTER,23], "T1"."ITEM_ID"[CHARACTER,23], "T1"."PROJECT_AREA_ITEM_ID"[CHARACTER,23], "T1"."JZ_DISCRIMINATOR"[NUMBER,22], "T1"."EXECUTION_RESULT_STEP_RESULTS"[CHARACTER,23] 6 - "T1Q"."STATE_ID"[CHARACTER,23], "T1Q"."ITEM_ID"[CHARACTER,23], "T1Q"."PROJECT_AREA_ITEM_ID"[CHARACTER,23], "T1Q"."JZ_DISCRIMINATOR"[NUMBER,22], "T1Q"."EXECUTION_RESULT_STEP_RESULTS"[CHARACTER,23] 7 - "T1Q".ROWID[ROWID,10], "T1Q"."STATE_ID"[CHARACTER,23] 8 - "T1V"."STATE_ID"[CHARACTER,23] 9 - "T1V".ROWID[ROWID,10] 10 - "T1Q".ROWID[ROWID,10], "T1Q"."STATE_ID"[CHARACTER,23] 11 - "T1Q"."ITEM_ID"[CHARACTER,23], "T1Q"."PROJECT_AREA_ITEM_ID"[CHARACTER,23], "T1Q"."JZ_DISCRIMINATOR"[NUMBER,22], "T1Q"."EXECUTION_RESULT_STEP_RESULTS"[CHARACTER,23] 12 - "T3".ROWID[ROWID,10] 129 rows selected. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. _yetCkBFaEeybd5Btn3fajw _yfCZwhFaEeybd5Btn3fajw 170 SQL_ID 22f6y8yx7txxf, child number 0 ------------------------------------- SELECT /* orig */ /*+ gather_plan_statistics */ t1.item_id, t1.state_id, t1.jz_discriminator FROM (SELECT t1q.*, T1V.item_type_dbid FROM qa_qm.execution_execution_task_reslt t1q INNER JOIN qa_qm.repository_version T1V ON T1V.state_id = t1q.state_id WHERE T1V.item_type_dbid = :v1 AND T1V.config_id = :v2 AND T1V.is_current = 1 AND t1q.jz_discriminator = :v3) t1 LEFT OUTER JOIN qa_qm.process_process_area t3 ON ( t1.project_area_item_id = t3.item_id ) WHERE ( EXISTS (SELECT 1 FROM qa_qm.execution_exctn_rslt_stp_rslts x1 WHERE t1.execution_result_step_results = x1.jz_parent_id AND x1.item_id = :v4) AND ( t3.project_area_item_id = '_0yZ0cL1WEei-d-PmOaCn5A' ) ) Plan hash value: 3027614311 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 60034 (100)| | 1 |00:00:08.89 | 4545K| | | | | 1 | NESTED LOOPS | | 1 | 1 | 209 | 60034 (1)| 00:00:03 | 1 |00:00:08.89 | 4545K| | | | | 2 | NESTED LOOPS | | 1 | 1 | 209 | 60034 (1)| 00:00:03 | 1 |00:00:08.89 | 4545K| | | | |* 3 | HASH JOIN RIGHT SEMI | | 1 | 1 | 161 | 60033 (1)| 00:00:03 | 1 |00:00:08.89 | 4545K| 1355K| 1355K| 668K (0)| |* 4 | INDEX RANGE SCAN | XCTNXCTNXCTNRSLTSTPRSLTSHNDLDX | 1 | 1 | 48 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | | | 5 | VIEW | | 1 | 282K| 30M| 60032 (1)| 00:00:03 | 1494K|00:00:09.24 | 4545K| | | | | 6 | NESTED LOOPS | | 1 | 282K| 40M| 60032 (1)| 00:00:03 | 1494K|00:00:09.05 | 4545K| | | | | 7 | NESTED LOOPS | | 1 | 282K| 40M| 60032 (1)| 00:00:03 | 1494K|00:00:05.50 | 3051K| | | | | 8 | TABLE ACCESS BY INDEX ROWID BATCHED| REPOSITORY_VERSION | 1 | 282K| 14M| 3479 (1)| 00:00:01 | 1494K|00:00:00.96 | 63356 | | | | |* 9 | INDEX RANGE SCAN | REPOSITOR_VRSN_DBD_CNFG_SCRRNT | 1 | 282K| | 165 (0)| 00:00:01 | 1494K|00:00:00.29 | 8793 | | | | |* 10 | INDEX UNIQUE SCAN | XCTNXCTNXCTNTSKRSLTPK0 | 1494K| 1 | | 1 (0)| 00:00:01 | 1494K|00:00:04.04 | 2988K| | | | |* 11 | TABLE ACCESS BY INDEX ROWID | EXECUTION_EXECUTION_TASK_RESLT | 1494K| 1 | 95 | 1 (0)| 00:00:01 | 1494K|00:00:03.11 | 1494K| | | | |* 12 | INDEX UNIQUE SCAN | PROCSS_PRCSS_PRCSS_R_PK | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | | | | |* 13 | TABLE ACCESS BY INDEX ROWID | PROCESS_PROCESS_AREA | 1 | 1 | 48 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$A5F16742 4 - SEL$A5F16742 / X1@SEL$5 5 - SEL$64EAE176 / T1@SEL$1 6 - SEL$64EAE176 8 - SEL$64EAE176 / T1V@SEL$2 9 - SEL$64EAE176 / T1V@SEL$2 10 - SEL$64EAE176 / T1Q@SEL$2 11 - SEL$64EAE176 / T1Q@SEL$2 12 - SEL$A5F16742 / T3@SEL$1 13 - SEL$A5F16742 / T3@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('19.1.0') DB_VERSION('19.1.0') OPT_PARAM('_complex_view_merging' 'false') OPT_PARAM('_optimizer_use_feedback' 'false') OPT_PARAM('_px_adaptive_dist_method' 'off') OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false') OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false') OPT_PARAM('optimizer_index_cost_adj' 10) FIRST_ROWS(100) OUTLINE_LEAF(@"SEL$64EAE176") MERGE(@"SEL$2" >"SEL$3") OUTLINE_LEAF(@"SEL$A5F16742") UNNEST(@"SEL$5") OUTLINE(@"SEL$3") OUTLINE(@"SEL$2") OUTLINE(@"SEL$78AF12E3") OUTER_JOIN_TO_INNER(@"SEL$7ADEC907" "T3"@"SEL$1") OUTLINE(@"SEL$5") OUTLINE(@"SEL$7ADEC907") MERGE(@"SEL$8812AA4E" >"SEL$F6245AF9") OUTLINE(@"SEL$F6245AF9") ANSI_REARCH(@"SEL$4") OUTLINE(@"SEL$8812AA4E") ANSI_REARCH(@"SEL$1") OUTLINE(@"SEL$4") OUTLINE(@"SEL$1") NO_ACCESS(@"SEL$A5F16742" "T1"@"SEL$1") INDEX(@"SEL$A5F16742" "X1"@"SEL$5" ("EXECUTION_EXCTN_RSLT_STP_RSLTS"."ITEM_ID" "EXECUTION_EXCTN_RSLT_STP_RSLTS"."JZ_PARENT_ID")) INDEX(@"SEL$A5F16742" "T3"@"SEL$1" ("PROCESS_PROCESS_AREA"."ITEM_ID")) LEADING(@"SEL$A5F16742" "T1"@"SEL$1" "X1"@"SEL$5" "T3"@"SEL$1") USE_HASH(@"SEL$A5F16742" "X1"@"SEL$5") USE_NL(@"SEL$A5F16742" "T3"@"SEL$1") NLJ_BATCHING(@"SEL$A5F16742" "T3"@"SEL$1") SWAP_JOIN_INPUTS(@"SEL$A5F16742" "X1"@"SEL$5") INDEX_RS_ASC(@"SEL$64EAE176" "T1V"@"SEL$2" ("REPOSITORY_VERSION"."ITEM_TYPE_DBID" "REPOSITORY_VERSION"."CONFIG_ID" "REPOSITORY_VERSION"."IS_CURRENT")) BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$64EAE176" "T1V"@"SEL$2") INDEX(@"SEL$64EAE176" "T1Q"@"SEL$2" ("EXECUTION_EXECUTION_TASK_RESLT"."STATE_ID")) LEADING(@"SEL$64EAE176" "T1V"@"SEL$2" "T1Q"@"SEL$2") USE_NL(@"SEL$64EAE176" "T1Q"@"SEL$2") NLJ_BATCHING(@"SEL$64EAE176" "T1Q"@"SEL$2") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T1"."EXECUTION_RESULT_STEP_RESULTS"="X1"."JZ_PARENT_ID") 4 - access("X1"."ITEM_ID"=:V4) 9 - access("T1V"."ITEM_TYPE_DBID"=:V1 AND "T1V"."CONFIG_ID"=:V2 AND "T1V"."IS_CURRENT"=1) 10 - access("T1V"."STATE_ID"="T1Q"."STATE_ID") 11 - filter("T1Q"."JZ_DISCRIMINATOR"=:V3) 12 - access("T1"."PROJECT_AREA_ITEM_ID"="T3"."ITEM_ID") 13 - filter("T3"."PROJECT_AREA_ITEM_ID"='_0yZ0cL1WEei-d-PmOaCn5A') Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "T1"."STATE_ID"[CHARACTER,23], "T1"."ITEM_ID"[CHARACTER,23], "T1"."JZ_DISCRIMINATOR"[NUMBER,22] 2 - "T1"."STATE_ID"[CHARACTER,23], "T1"."ITEM_ID"[CHARACTER,23], "T1"."JZ_DISCRIMINATOR"[NUMBER,22], "T3".ROWID[ROWID,10] 3 - (#keys=1) "T1"."STATE_ID"[CHARACTER,23], "T1"."ITEM_ID"[CHARACTER,23], "T1"."PROJECT_AREA_ITEM_ID"[CHARACTER,23], "T1"."JZ_DISCRIMINATOR"[NUMBER,22] 4 - "X1"."JZ_PARENT_ID"[CHARACTER,23] 5 - "T1"."STATE_ID"[CHARACTER,23], "T1"."ITEM_ID"[CHARACTER,23], "T1"."PROJECT_AREA_ITEM_ID"[CHARACTER,23], "T1"."JZ_DISCRIMINATOR"[NUMBER,22], "T1"."EXECUTION_RESULT_STEP_RESULTS"[CHARACTER,23] 6 - "T1Q"."STATE_ID"[CHARACTER,23], "T1Q"."ITEM_ID"[CHARACTER,23], "T1Q"."PROJECT_AREA_ITEM_ID"[CHARACTER,23], "T1Q"."JZ_DISCRIMINATOR"[NUMBER,22], "T1Q"."EXECUTION_RESULT_STEP_RESULTS"[CHARACTER,23] 7 - "T1Q".ROWID[ROWID,10], "T1Q"."STATE_ID"[CHARACTER,23] 8 - "T1V"."STATE_ID"[CHARACTER,23] 9 - "T1V".ROWID[ROWID,10] 10 - "T1Q".ROWID[ROWID,10], "T1Q"."STATE_ID"[CHARACTER,23] 11 - "T1Q"."ITEM_ID"[CHARACTER,23], "T1Q"."PROJECT_AREA_ITEM_ID"[CHARACTER,23], "T1Q"."JZ_DISCRIMINATOR"[NUMBER,22], "T1Q"."EXECUTION_RESULT_STEP_RESULTS"[CHARACTER,23] 12 - "T3".ROWID[ROWID,10] 129 rows selected.