Oracle Database Discussions

Hi

Oracle Version 10.2.0.5
CURSOR_SHARING="SIMILAR"
hidden parameter optimpeek_user_binds = "TRUE"
stats gathering with "for all columnes size 1 "

SQL>
SQL> set linesize 300
SQL> set pagesize 1000
SQL> /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bh7hrghvacvqn
--------------------
select * from ( select f_p_person0_.BPP_IDENT as col_0_0_ from F_P_PERSONNE f_p_person0_
left outer join T_ETABLISS t_etabliss1_ on f_p_person0_.BPP_PTRETABIDENT=t_etabliss1_.TA_IDEN
T left outer join F_INTERMEDIAIRE f_intermed2_ on
f_p_person0_.BPP_PTR_INIDENT=f_intermed2_.IN_IDENT where (f_p_person0_.BPP_PP_ACTIVE is
null) and (f_p_person0_.BPP_SOCIETE=:1 or f_p_person0_.BPP_SOCIETE=:2 ) and
(t_etabliss1_.TA_CODE=:3 or t_etabliss1_.TA_IDENT is null) and
(t_etabliss1_.TA_PTRTDILVID=:4 or t_etabliss1_.TA_IDENT is null) and (exists (select
f_polices3_.POL_IDENT, f_p_c_clie4_.BPCL_IDENT from F_POLICES f_polices3_, F_P_C_CLIENT
f_p_c_clie4_ where f_p_c_clie4_.BPCL_PTRBPPIDENT=f_p_person0_.BPP_IDENT and
f_p_c_clie4_.BPCL_IDENT=f_polices3_.POL_PTRCLID and (f_polices3_.POL_NUMPOL like :5 ))) )
where rownum <= :6

Plan hash value: 1858865683

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 909 (100)| |
| 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS OUTER | | 11 | 1210 | 909 (1)| 00:00:11 |
| 3 | FILTER | | | | | |
| 4 | HASH JOIN RIGHT OUTER | | 11 | 1166 | 909 (1)| 00:00:11 |
| 5 | TABLE ACCESS FULL | T_ETABLISS | 4 | 72 | 3 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 88 | 3872 | 905 (1)| 00:00:11 |
| 7 | VIEW | VW_SQ_1 | 4689 | 60957 | 815 (1)| 00:00:10 |
| 8 | HASH UNIQUE | | 4689 | 151K| | |
| 9 | HASH JOIN | | 4689 | 151K| 815 (1)| 00:00:10 |
| 10 | VIEW | index$_join$_007 | 4666 | 100K| 609 (1)| 00:00:08 |
| 11 | HASH JOIN | | | | | |
| 12 | HASH JOIN | | | | | |
| 13 | INDEX RANGE SCAN | IND_POL_NUMPOL | 4666 | 100K| 6 (17)| 00:00:01 |
| 14 | INDEX FAST FULL SCAN | IND_POL_PTRCLID | 4666 | 100K| 325 (1)| 00:00:04 |
| 15 | INDEX FAST FULL SCAN | PK_POL_IDENT | 4666 | 100K| 421 (1)| 00:00:06 |
| 16 | TABLE ACCESS FULL | F_P_C_CLIENT | 53835 | 578K| 205 (1)| 00:00:03 |
| 17 | TABLE ACCESS BY INDEX ROWID| F_P_PERSONNE | 1 | 31 | 1 (0)| 00:00:01 |
| 18 | INDEX UNIQUE SCAN | PK_BPP_IDENT | 1 | | 0 (0)| |
| 19 | INDEX UNIQUE SCAN | PK_IN_IDENT | 1 | 4 | 0 (0)| |
-----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$E1B20FB2
5 - SEL$E1B20FB2 / T_ETABLISS1_@SEL$2
7 - SEL$3C459230 / VW_SQ_1@SEL$E00E1480
8 - SEL$3C459230
10 - SEL$6A96EA55 / F_POLICES3_@SEL$7
11 - SEL$6A96EA55
13 - SEL$6A96EA55 / indexjoin$_alias$_001@SEL$6A96EA55
14 - SEL$6A96EA55 / indexjoin$_alias$_002@SEL$6A96EA55
15 - SEL$6A96EA55 / indexjoin$_alias$_003@SEL$6A96EA55
16 - SEL$3C459230 / F_P_C_CLIE4_@SEL$7
17 - SEL$E1B20FB2 / F_P_PERSON0_@SEL$3
18 - SEL$E1B20FB2 / F_P_PERSON0_@SEL$3
19 - SEL$E1B20FB2 / F_INTERMED2_@SEL$4

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
OPT_PARAM('_optim_peek_user_binds' 'false')
OUTLINE_LEAF(@"SEL$6A96EA55")
OUTLINE_LEAF(@"SEL$3C459230")
OUTLINE_LEAF(@"SEL$E1B20FB2")
UNNEST(@"SEL$7")
OUTLINE(@"SEL$3C459230")
OUTLINE(@"SEL$7")
OUTLINE(@"SEL$E00E1480")
OUTLINE(@"SEL$7C4821CC")
MERGE(@"SEL$C76F8E91")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$C76F8E91")
MERGE(@"SEL$829C93BE")
OUTLINE(@"SEL$6")
OUTLINE(@"SEL$829C93BE")
MERGE(@"SEL$4")
MERGE(@"SEL$64EAE176")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$64EAE176")
MERGE(@"SEL$2")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$2")
NO_ACCESS(@"SEL$E1B20FB2" "VW_SQ_1"@"SEL$E00E1480")
INDEX_RS_ASC(@"SEL$E1B20FB2" "F_P_PERSON0_"@"SEL$3" ("F_P_PERSONNE"."BPP_IDENT"))
FULL(@"SEL$E1B20FB2" "T_ETABLISS1_"@"SEL$2")
INDEX(@"SEL$E1B20FB2" "F_INTERMED2_"@"SEL$4" ("F_INTERMEDIAIRE"."IN_IDENT"))
LEADING(@"SEL$E1B20FB2" "VW_SQ_1"@"SEL$E00E1480" "F_P_PERSON0_"@"SEL$3"
"T_ETABLISS1_"@"SEL$2" "F_INTERMED2_"@"SEL$4")
USE_NL(@"SEL$E1B20FB2" "F_P_PERSON0_"@"SEL$3")
USE_HASH(@"SEL$E1B20FB2" "T_ETABLISS1_"@"SEL$2")
USE_NL(@"SEL$E1B20FB2" "F_INTERMED2_"@"SEL$4")
SWAP_JOIN_INPUTS(@"SEL$E1B20FB2" "T_ETABLISS1_"@"SEL$2")
INDEX_JOIN(@"SEL$3C459230" "F_POLICES3_"@"SEL$7" ("F_POLICES"."POL_NUMPOL")
("F_POLICES"."POL_PTRCLID") ("F_POLICES"."POL_IDENT"))
FULL(@"SEL$3C459230" "F_P_C_CLIE4_"@"SEL$7")
LEADING(@"SEL$3C459230" "F_POLICES3_"@"SEL$7" "F_P_C_CLIE4_"@"SEL$7")
USE_HASH(@"SEL$3C459230" "F_P_C_CLIE4_"@"SEL$7")
USE_HASH_AGGREGATION(@"SEL$3C459230")
END_OUTLINE_DATA
*/

SQL_ID bh7hrghvacvqn
--------------------
select * from ( select f_p_person0_.BPP_IDENT as col_0_0_ from F_P_PERSONNE f_p_person0_
left outer join T_ETABLISS t_etabliss1_ on f_p_person0_.BPP_PTRETABIDENT=t_etabliss1_.TA_IDEN
T left outer join F_INTERMEDIAIRE f_intermed2_ on
f_p_person0_.BPP_PTR_INIDENT=f_intermed2_.IN_IDENT where (f_p_person0_.BPP_PP_ACTIVE is
null) and (f_p_person0_.BPP_SOCIETE=:1 or f_p_person0_.BPP_SOCIETE=:2 ) and
(t_etabliss1_.TA_CODE=:3 or t_etabliss1_.TA_IDENT is null) and
(t_etabliss1_.TA_PTRTDILVID=:4 or t_etabliss1_.TA_IDENT is null) and (exists (select
f_polices3_.POL_IDENT, f_p_c_clie4_.BPCL_IDENT from F_POLICES f_polices3_, F_P_C_CLIENT
f_p_c_clie4_ where f_p_c_clie4_.BPCL_PTRBPPIDENT=f_p_person0_.BPP_IDENT and
f_p_c_clie4_.BPCL_IDENT=f_polices3_.POL_PTRCLID and (f_polices3_.POL_NUMPOL like :5 ))) )
where rownum <= :6

Plan hash value: 1942090705

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3954 (100)| |
| 1 | COUNT STOPKEY | | | | | |
| 2 | CONCATENATION | | | | | |
| 3 | FILTER | | | | | |
| 4 | NESTED LOOPS SEMI | | 1 | 98 | 1977 (1)| 00:00:24 |
| 5 | FILTER | | | | | |
| 6 | HASH JOIN RIGHT OUTER | | 1 | 92 | 14 (8)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T_ETABLISS | 4 | 72 | 3 (0)| 00:00:01 |
| 8 | NESTED LOOPS OUTER | | 845 | 31265 | 10 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | F_P_PERSONNE | 845 | 27885 | 10 (0)| 00:00:01 |
| 10 | INDEX UNIQUE SCAN | PK_IN_IDENT | 1 | 4 | 0 (0)| |
| 11 | VIEW | VW_SQ_1 | 1481 | 8886 | 1963 (1)| 00:00:24 |
| 12 | HASH JOIN | | 6223 | 218K| 1963 (1)| 00:00:24 |
| 13 | VIEW | index$_join$_007 | 6223 | 145K| 863 (1)| 00:00:11 |
| 14 | HASH JOIN | | | | | |
| 15 | HASH JOIN | | | | | |
| 16 | INDEX RANGE SCAN | IND_POL_NUMPOL | 6223 | 145K| 30 (4)| 00:00:01 |
| 17 | INDEX FAST FULL SCAN | IND_POL_PTRCLID | 6223 | 145K| 439 (1)| 00:00:06 |
| 18 | INDEX FAST FULL SCAN | PK_POL_IDENT | 6223 | 145K| 564 (1)| 00:00:07 |
| 19 | TABLE ACCESS FULL | F_P_C_CLIENT | 294K| 3446K| 1098 (1)| 00:00:14 |
| 20 | FILTER | | | | | |
| 21 | NESTED LOOPS OUTER | | 201 | 21507 | 1977 (1)| 00:00:24 |
| 22 | FILTER | | | | | |
| 23 | HASH JOIN RIGHT OUTER | | 201 | 20703 | 1977 (1)| 00:00:24 |
| 24 | TABLE ACCESS FULL | T_ETABLISS | 4 | 72 | 3 (0)| 00:00:01 |
| 25 | HASH JOIN SEMI | | 201 | 7839 | 1974 (1)| 00:00:24 |
| 26 | TABLE ACCESS FULL | F_P_PERSONNE | 845 | 27885 | 10 (0)| 00:00:01 |
| 27 | VIEW | VW_SQ_1 | 6223 | 37338 | 1963 (1)| 00:00:24 |
| 28 | HASH JOIN | | 6223 | 218K| 1963 (1)| 00:00:24 |
| 29 | VIEW | index$_join$_007 | 6223 | 145K| 863 (1)| 00:00:11 |
| 30 | HASH JOIN | | | | | |
| 31 | HASH JOIN | | | | | |
| 32 | INDEX RANGE SCAN | IND_POL_NUMPOL | 6223 | 145K| 30 (4)| 00:00:01 |
| 33 | INDEX FAST FULL SCAN| IND_POL_PTRCLID | 6223 | 145K| 439 (1)| 00:00:06 |
| 34 | INDEX FAST FULL SCAN | PK_POL_IDENT | 6223 | 145K| 564 (1)| 00:00:07 |
| 35 | TABLE ACCESS FULL | F_P_C_CLIENT | 294K| 3446K| 1098 (1)| 00:00:14 |
| 36 | INDEX UNIQUE SCAN | PK_IN_IDENT | 1 | 4 | 0 (0)| |
-----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$E1B20FB2
7 - SEL$E1B20FB2_1 / T_ETABLISS1_@SEL$2
9 - SEL$E1B20FB2_1 / F_P_PERSON0_@SEL$3
10 - SEL$E1B20FB2_1 / F_INTERMED2_@SEL$4
11 - SEL$3C459230 / VW_SQ_1@SEL$E00E1480
12 - SEL$3C459230
13 - SEL$6A96EA55 / F_POLICES3_@SEL$7
14 - SEL$6A96EA55
16 - SEL$6A96EA55 / indexjoin$_alias$_001@SEL$6A96EA55
17 - SEL$6A96EA55 / indexjoin$_alias$_002@SEL$6A96EA55
18 - SEL$6A96EA55 / indexjoin$_alias$_003@SEL$6A96EA55
19 - SEL$3C459230 / F_P_C_CLIE4_@SEL$7
24 - SEL$E1B20FB2_2 / T_ETABLISS1_@SEL$E1B20FB2_2
26 - SEL$E1B20FB2_2 / F_P_PERSON0_@SEL$E1B20FB2_2
27 - SEL$3C459230 / VW_SQ_1@SEL$E1B20FB2_2
29 - SEL$6A96EA55 / F_POLICES3_@SEL$7
32 - SEL$6A96EA55 / indexjoin$_alias$_001@SEL$6A96EA55
33 - SEL$6A96EA55 / indexjoin$_alias$_002@SEL$6A96EA55
34 - SEL$6A96EA55 / indexjoin$_alias$_003@SEL$6A96EA55
35 - SEL$3C459230 / F_P_C_CLIE4_@SEL$7
36 - SEL$E1B20FB2_2 / F_INTERMED2_@SEL$E1B20FB2_2

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
OUTLINE_LEAF(@"SEL$6A96EA55")
OUTLINE_LEAF(@"SEL$3C459230")
OUTLINE_LEAF(@"SEL$E1B20FB2")
UNNEST(@"SEL$7")
OUTLINE_LEAF(@"SEL$E1B20FB2_1")
USE_CONCAT(@"SEL$E1B20FB2" 8)
OUTLINE_LEAF(@"SEL$E1B20FB2_2")
OUTLINE(@"SEL$3C459230")
OUTLINE(@"SEL$7")
OUTLINE(@"SEL$E00E1480")
OUTLINE(@"SEL$E1B20FB2")
UNNEST(@"SEL$7")
OUTLINE(@"SEL$7C4821CC")
MERGE(@"SEL$C76F8E91")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$C76F8E91")
MERGE(@"SEL$829C93BE")
OUTLINE(@"SEL$6")
OUTLINE(@"SEL$829C93BE")
MERGE(@"SEL$4")
MERGE(@"SEL$64EAE176")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$64EAE176")
MERGE(@"SEL$2")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$2")
FULL(@"SEL$E1B20FB2_1" "F_P_PERSON0_"@"SEL$3")
INDEX(@"SEL$E1B20FB2_1" "F_INTERMED2_"@"SEL$4" ("F_INTERMEDIAIRE"."IN_IDENT"))
FULL(@"SEL$E1B20FB2_1" "T_ETABLISS1_"@"SEL$2")
NO_ACCESS(@"SEL$E1B20FB2_1" "VW_SQ_1"@"SEL$E00E1480")
FULL(@"SEL$E1B20FB2_2" "F_P_PERSON0_"@"SEL$E1B20FB2_2")
NO_ACCESS(@"SEL$E1B20FB2_2" "VW_SQ_1"@"SEL$E1B20FB2_2")
FULL(@"SEL$E1B20FB2_2" "T_ETABLISS1_"@"SEL$E1B20FB2_2")
INDEX(@"SEL$E1B20FB2_2" "F_INTERMED2_"@"SEL$E1B20FB2_2" ("F_INTERMEDIAIRE"."IN_IDENT"))
LEADING(@"SEL$E1B20FB2_1" "F_P_PERSON0_"@"SEL$3" "F_INTERMED2_"@"SEL$4"
"T_ETABLISS1_"@"SEL$2" "VW_SQ_1"@"SEL$E00E1480")
LEADING(@"SEL$E1B20FB2_2" "F_P_PERSON0_"@"SEL$E1B20FB2_2" "VW_SQ_1"@"SEL$E1B20FB2_2"
"T_ETABLISS1_"@"SEL$E1B20FB2_2" "F_INTERMED2_"@"SEL$E1B20FB2_2")
USE_NL(@"SEL$E1B20FB2_1" "F_INTERMED2_"@"SEL$4")
USE_HASH(@"SEL$E1B20FB2_1" "T_ETABLISS1_"@"SEL$2")
USE_NL(@"SEL$E1B20FB2_1" "VW_SQ_1"@"SEL$E00E1480")
USE_HASH(@"SEL$E1B20FB2_2" "VW_SQ_1"@"SEL$E1B20FB2_2")
USE_HASH(@"SEL$E1B20FB2_2" "T_ETABLISS1_"@"SEL$E1B20FB2_2")
USE_NL(@"SEL$E1B20FB2_2" "F_INTERMED2_"@"SEL$E1B20FB2_2")
SWAP_JOIN_INPUTS(@"SEL$E1B20FB2_1" "T_ETABLISS1_"@"SEL$2")
SWAP_JOIN_INPUTS(@"SEL$E1B20FB2_2" "T_ETABLISS1_"@"SEL$E1B20FB2_2")
INDEX_JOIN(@"SEL$3C459230" "F_POLICES3_"@"SEL$7" ("F_POLICES"."POL_NUMPOL")
("F_POLICES"."POL_PTRCLID") ("F_POLICES"."POL_IDENT"))
FULL(@"SEL$3C459230" "F_P_C_CLIE4_"@"SEL$7")
LEADING(@"SEL$3C459230" "F_POLICES3_"@"SEL$7" "F_P_C_CLIE4_"@"SEL$7")
USE_HASH(@"SEL$3C459230" "F_P_C_CLIE4_"@"SEL$7")
END_OUTLINE_DATA
*/

Peeked Binds (identified by position):
--------------------------------------

1 - :1 (VARCHAR2(30), CSID=873): 'S0001'
2 - :2 (VARCHAR2(30), CSID=873): '*****'
3 - :3 (VARCHAR2(30), CSID=873): 'GACNC'
4 - :4 (NUMBER): 1200014
5 - :5 (VARCHAR2(30), CSID=873): '%18726%'
6 - :6 (NUMBER): 201


262 rows selected.