Hi all,
I'm dealing with the query below.
INSERT INTO TCLRJO_TT (ORVEW, NATUR, TYPRD, DATYP, FATSM, NUBIX, NUFDP, NTOPE, COINT, NUCPT, CDVMA, CDVFO, CDVCO, COMAR, DATOP, CNACT, CNACN, LCACT,
COBCN, NUCON, CMECH, CAECH, DAECA, CSOPT, CSENS, QTFEA, QTFEV, MTSNA, MTNEG, MTPRE, MCMKT, MCBRK, MCCMP, MACRT, MACOU, DATRA, CTCOT, COTSJ, ISOPT,
NUFLI, NUBLI, MRESU, MTNLI, DAOLI, NUCLI, CABIN, ISCRJ, CTCOF, IDENT, LBSCR, COLAN, NUIAV, CTAVC, CTAVI, RGCOD, RGCID, NMINT, LCPTE, COINV, COINA,
COPOR, NUOTH, NUTRI, NUSPE, NUSPF, NUBCP, TYTRN, LFACT, CMPFG) SELECT 'VPUSCJ', V.NATUR, V.TYPRD, 0, V.FATSM, V.NUBIX, V.NUFDP, V.NTOPE, V.COINT,
V.NUCPT, V.CDVMA, V.CDVFO, V.CDVCO, V.COMAR, V.DATOP, V.CNACT, V.CNACN, V.LCACT, V.COBCN, V.NUCON, V.CMECH, V.CAECH, V.DAECA, V.CSOPT, V.CSENS, V.QTFEA,
V.QTFEV, V.MTSNA, V.MTNEG, V.MTPRE, V.MCMKT, V.MCBRK, V.MCCMP, V.MACRT, V.MACOU, V.DATRA, V.CTCOT, V.COTSJ, V.ISOPT, V.NUFLI, V.NUBLI, V.MRESU, V.MTNLI,
V.DAOLI, V.NUCLI, V.CABIN, V.ISCRJ, V.CTCOF, V.IDENT, V.LBSCR, V.COLAN, V.NUIAV, V.CTAVC, V.CTAVI, V.RGCOD, V.RGCID, V.NMINT, V.LCPTE, V.COINV, V.COINA,
V.COPOR, V.NUOTH, V.NUTRI, V.NUSPE, V.NUSPF, V.NUBCP, V.TYTRN, LFACT, V.CMPFG
FROM VPUSCJV;
-- script de la vue
CREATE OR REPLACE VIEW VPUSCJ
(NUBIX, NUFDP, NTOPE, COINT, NUCPT,
CDVMA, CDVFO, CDVCO, COMAR, DATOP,
CNACT, CNACN, LCACT, COBCN, NUCON,
CMECH, CAECH, DAECA, CSOPT, CSENS,
QTFEA, QTFEV, MTSNA, MTNEG, MTPRE,
MCMKT, MCBRK, MCCMP, MACRT, MACOU,
DATRA, CTCOT, COTSJ, ISOPT, NUFLI,
NUBLI, MRESU, MTNLI, DAOLI, NUCLI,
CABIN, ISCRJ, CTCOF, IDENT, LBSCR,
COLAN, NUIAV, CTAVC, CTAVI, RGCOD,
RGCID, NMINT, LCPTE, COINV, COINA,
COPOR, NUOTH, NUTRI, NUSPE, NUSPF,
NUBCP, TYTRN, NUINS, NATUR, TYPRD,
FATSM, CMPFG, LFACT)
AS
SELECT
V.NUBIX,V.NUFDP,V.NTOPE,V.COINC,V.NUCPT,V.CDVMA,NULL,V.CDVCO,V.COMAR,V.DATOP,
A.CNACT,A.CNACN,
PC.LIBEL,
A.COBCN,V.NUCON,V.CMECH,V.CAECH,V.DAECA,V.CSOPT,V.CSENS,
DECODE(V.CSENS,'A',V.QTAVI,0),DECODE(V.CSENS,'V',V.QTAVI,0),
V.MTSNA,V.MTNEG,V.MTPRE+NVL(V.MTADJ,0),NULL,NULL,NULL,
0,-NVL(C3.MTCOF*V.QTAVI/V3.QTAVI,0),
V.DATRA,A.CTCOT,A.COTSJ,DECODE(A.CNACN,'T',NULL,DECODE(A.COPRI,'O',NULL,1)),
V.NUFLI,V.NUBLI,V.MRESU,V.MTNLI,V.DAOLI,V.NUCLI,V.CABIN,V.ISCRJ,NVL(S.NUGRP,S.CTCOF),
ROWIDTOCHAR(C3.ROWID),S.LBSCR,L.COLAN,V.NUIAV,NULL,V.CTAVI,V.RGCOD,V.RGCID,
I.NMINT,X.LCPTE,NULL,NULL,V.COPOR,NULL,S.NUTRI,
R.NUSPE,G.NUSPF,V.NUBCP,NULL,
V.NUINS,Y.NATUR,Y.TYPRD,Y.FATSM,DECODE(INSTR(NVL(V.NURFD,'*'),'CFIFIFO'),0,NULL,1) CMPFG,NULL
FROM
RENCOM R,
RENGEN G,
DATRAI D,
TPOOIN_TT Y,
IAVOPE V,
IAVOPE V3,
AVICRC C3,
NATACF A,
POOLIB_TT PC,
TPOOGF_TT S,
LANGUE L,
INTERV I,
COMPTE X,
POOPAR P
WHERE
Y.POVEW='VPUSCJ' AND
Y.POTMP='TCLRJO_TT' AND
INSTR(',' || Y.LIMAR || ',',',' || V.COMAR || ',',1) != 0 AND
V.COINC=Y.COINT AND
V.DATRA BETWEEN Y.DATRA AND Y.DAMAX AND
V.COMAR=D.COMAR AND
V.DATRA=D.DATRA AND
P.NUMPA=8 AND
(NVL(V.ISCRJ,'N')='N' OR P.COPAR='O') AND
V.NTOPE=99 AND
V.NUBLI IS NOT NULL AND
V3.NUBIX(+)=V.NUBIX AND
V3.NUFDP(+)=V.NUFDP AND
V3.CTAVI(+)=V.CTAVI AND
(NVL(V3.NTOPE,-1) IN (0,5,20) OR (NVL(V3.NTOPE,-1)=9 AND NVL(V3.NTOPO,-1) IN (0,5,20))) AND
C3.NUIAV(+)=V3.NUIAV AND
A.ID_CNACT=V.ID_CNACT AND
PC.CNACT=A.CNACT AND
PC.CMECH=NVL(V.CMECH,'*') AND
PC.CAECH=NVL(V.CAECH,-1) AND
PC.CSOPT=NVL(V.CSOPT,'*') AND
PC.MTSNA=NVL(V.MTSNA,-1) AND
PC.COLAN=L.COLAN AND
L.COLAM='F' AND
L.COLAN=Y.COLAN AND
S.COINT=V3.COINC AND -- RÚcup de la bucket ou groupe de bucket
S.CTCOF=NVL(C3.CTCOF,S.CTCOF) AND -- *
S.COLAN=L.COLAN AND -- *
I.COINT=V.COINC AND
X.COINT=V.COINC AND
X.NUCPT=V.NUCPT;
If you look at the execution plan below you can note that cardinalities are not estimated for operations ID 22 to 26.
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | 0 |00:04:44.28 | 5739K| 36154 |
| 1 | LOAD TABLE CONVENTIONAL | | 1 | | 0 |00:04:44.28 | 5739K| 36154 |
| 2 | MERGE JOIN CARTESIAN | | 1 | 1 | 2439K|00:02:20.30 | 3927K| 13047 |
| 3 | NESTED LOOPS | | 1 | 1 | 2439K|00:02:12.25 | 3927K| 13047 |
| 4 | NESTED LOOPS OUTER | | 1 | 1 | 3296K|00:01:43.10 | 3190K| 13047 |
| 5 | NESTED LOOPS | | 1 | 1 | 94039 |00:00:22.96 | 1624K| 2090 |
| 6 | NESTED LOOPS | | 1 | 1 | 212K|00:00:10.53 | 948K| 1039 |
| 7 | NESTED LOOPS | | 1 | 1 | 212K|00:00:09.09 | 734K| 1039 |
| 8 | NESTED LOOPS | | 1 | 1 | 212K|00:00:07.76 | 520K| 1039 |
| 9 | NESTED LOOPS | | 1 | 1 | 212K|00:00:06.46 | 280K| 1039 |
| 10 | NESTED LOOPS | | 1 | 1 | 212K|00:00:04.66 | 16946 | 1031 |
|* 11 | HASH JOIN | | 1 | 1 | 212K|00:00:03.94 | 15808 | 1031 |
|* 12 | TABLE ACCESS FULL | TPOOIN_TT | 1 | 3289 | 13739 |00:00:00.99 | 2357 | 1029 |
| 13 | NESTED LOOPS | | 1 | | 245K|00:00:01.83 | 13451 | 2 |
| 14 | NESTED LOOPS | | 1 | 1942 | 245K|00:00:00.44 | 167 | 0 |
| 15 | MERGE JOIN CARTESIAN | | 1 | 9 | 9 |00:00:00.01 | 8 | 0 |
| 16 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 5 | 0 |
| 17 | TABLE ACCESS BY INDEX ROWID| POOPAR | 1 | 1 | 1 |00:00:00.01 | 2 | 0 |
|* 18 | INDEX UNIQUE SCAN | POOPAR1 | 1 | 1 | 1 |00:00:00.01 | 1 | 0 |
| 19 | TABLE ACCESS FULL | RENGEN | 1 | 1 | 1 |00:00:00.01 | 3 | 0 |
| 20 | BUFFER SORT | | 1 | 9 | 9 |00:00:00.01 | 3 | 0 |
| 21 | TABLE ACCESS FULL | DATRAI | 1 | 9 | 9 |00:00:00.01 | 3 | 0 |
| 22 | BITMAP CONVERSION TO ROWIDS | | 9 | | 245K|00:00:00.17 | 159 | 0 |
| 23 | BITMAP MINUS | | 9 | | 4 |00:00:00.01 | 159 | 0 |
| 24 | BITMAP AND | | 9 | | 4 |00:00:00.01 | 142 | 0 |
|* 25 | BITMAP INDEX SINGLE VALUE | IAVOPE7_BMP | 9 | | 68 |00:00:00.01 | 53 | 0 |
|* 26 | BITMAP INDEX SINGLE VALUE | IAVOPE8_BMP | 9 | | 48 |00:00:00.01 | 51 | 0 |
|* 27 | BITMAP INDEX SINGLE VALUE | IAVOPEA_BMP | 9 | | 28 |00:00:00.01 | 38 | 0 |
|* 28 | BITMAP INDEX SINGLE VALUE | IAVOPE3_BMP | 9 | | 10 |00:00:00.01 | 17 | 0 |
|* 29 | TABLE ACCESS BY INDEX ROWID | IAVOPE | 245K| 216 | 245K|00:00:00.86 | 13284 | 2 |
|* 30 | INDEX UNIQUE SCAN | LANGUE1 | 212K| 1 | 212K|00:00:00.46 | 1138 | 0 |
| 31 | TABLE ACCESS BY INDEX ROWID | COMPTE | 212K| 1 | 212K|00:00:01.54 | 263K| 8 |
|* 32 | INDEX UNIQUE SCAN | COMPTE1 | 212K| 1 | 212K|00:00:00.51 | 50494 | 0 |
| 33 | TABLE ACCESS BY INDEX ROWID | INTERV | 212K| 1 | 212K|00:00:01.03 | 240K| 0 |
|* 34 | INDEX UNIQUE SCAN | INTERV1 | 212K| 1 | 212K|00:00:00.41 | 28015 | 0 |
| 35 | TABLE ACCESS BY INDEX ROWID | NATACF | 212K| 1 | 212K|00:00:01.06 | 213K| 0 |
|* 36 | INDEX UNIQUE SCAN | NATACF5 | 212K| 1 | 212K|00:00:00.36 | 1152 | 0 |
| 37 | TABLE ACCESS BY INDEX ROWID | POOLIB_TT | 212K| 1 | 212K|00:00:01.16 | 213K| 0 |
|* 38 | INDEX UNIQUE SCAN | POOLIB_TT1 | 212K| 1 | 212K|00:00:00.53 | 1154 | 0 |
|* 39 | TABLE ACCESS BY INDEX ROWID | IAVOPE | 212K| 1 | 94039 |00:00:12.22 | 676K| 1051 |
|* 40 | INDEX RANGE SCAN | IAVOPE4 | 212K| 1 | 685K|00:00:01.62 | 316K| 2 |
| 41 | TABLE ACCESS BY INDEX ROWID | AVICRC | 94039 | 29 | 3296K|00:01:17.31 | 1566K| 10957 |
|* 42 | INDEX RANGE SCAN | AVICRC1 | 94039 | 29 | 3296K|00:00:41.94 | 330K| 6032 |
| 43 | TABLE ACCESS BY INDEX ROWID | TPOOGF_TT | 3296K| 1 | 2439K|00:00:25.53 | 736K| 0 |
|* 44 | INDEX RANGE SCAN | TPOOGF_TT1 | 3296K| 1 | 2439K|00:00:18.53 | 669K| 0 |
| 45 | BUFFER SORT | | 2439K| 1 | 2439K|00:00:03.80 | 3 | 0 |
| 46 | TABLE ACCESS FULL | RENCOM | 1 | 1 | 1 |00:00:00.01 | 3 | 0 |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
11 - access("V"."COINC"="Y"."COINT")
filter((INSTR(','||"Y"."LIMAR"||',',','||"V"."COMAR"||',',1)<>0 AND "V"."DATRA">="Y"."DATRA" AND
"V"."DATRA"<="Y"."DAMAX"))
12 - filter(("Y"."POVEW"='VPUSCJ' AND "Y"."POTMP"='TCLRJO_TT'))
18 - access("P"."NUMPA"=8)
25 - access("V"."DATRA"="D"."DATRA")
26 - access("V"."NTOPE"=99)
27 - access("V"."COMAR"="D"."COMAR")
28 - access("V"."NUBLI" IS NULL)
29 - filter((NVL("V"."ISCRJ",'N')='N' OR "P"."COPAR"='O'))
30 - access("L"."COLAN"="Y"."COLAN" AND "L"."COLAM"='F')
32 - access("X"."COINT"="V"."COINC" AND "X"."NUCPT"="V"."NUCPT")
34 - access("I"."COINT"="V"."COINC")
36 - access("A"."ID_CNACT"="V"."ID_CNACT")
38 - access("PC"."CNACT"="A"."CNACT" AND "PC"."CMECH"=NVL("V"."CMECH",'*') AND "PC"."CAECH"=NVL("V"."CAECH",(-1))
AND "PC"."CSOPT"=NVL("V"."CSOPT",'*') AND "PC"."MTSNA"=NVL("V"."MTSNA",(-1)) AND "PC"."COLAN"="L"."COLAN")
39 - filter(((INTERNAL_FUNCTION("V3"."NTOPE") OR ("V3"."NTOPE"=9 AND (NVL("V3"."NTOPO",(-1))=0 OR
NVL("V3"."NTOPO",(-1))=5 OR NVL("V3"."NTOPO",(-1))=20))) AND "V3"."NUFDP"="V"."NUFDP" AND "V3"."CTAVI"="V"."CTAVI"))
40 - access("V3"."NUBIX"="V"."NUBIX")
42 - access("C3"."NUIAV"="V3"."NUIAV")
44 - access("S"."COINT"="V3"."COINC" AND "S"."COLAN"="L"."COLAN")
filter(("S"."CTCOF"=NVL("C3"."CTCOF","S"."CTCOF") AND "S"."COLAN"="L"."COLAN"))
So no cardinalities are estimated for "BITMAP CONVERSION TO ROWIDS" operation though the A-ROWS colum shows that there are 245k rows processed.
This poor estimation leads to a set of Nested Loops and Merge Jon Cartesian wich can lead to dramatic performance.
My question is why does the CBO can't estimate the cardinalities for BITMAP Index Access ?
Is there an other way to tune this query ?
Here is some informations and statistics:
SQL> select 'TPOOIN_TT', num_rows from user_tables where table_name = 'TPOOIN_TT'
2 union all
3 select 'POOLIB_TT', num_rows from user_tables where table_name = 'POOLIB_TT'
4 union all
5 select 'TPOOGF_TT', num_rows from user_tables where table_name = 'TPOOGF_TT'
6 union all
7 select 'RENCOM', num_rows from user_tables where table_name = 'RENCOM'
8 union all
9 select 'RENGEN', num_rows from user_tables where table_name = 'RENGEN'
10 union all
11 select 'DATRAI', num_rows from user_tables where table_name = 'DATRAI'
12 union all
13 select 'IAVOPE', num_rows from user_tables where table_name = 'IAVOPE'
14 union all
15 select 'AVICRC', num_rows from user_tables where table_name = 'AVICRC'
16 union all
17 select 'NATACF', num_rows from user_tables where table_name = 'NATACF'
18 union all
19 select 'XXXX', num_rows from user_tables where table_name = 'XXXX'
20 union all
21 select 'LANGUE', num_rows from user_tables where table_name = 'LANGUE'
22 union all
23 select 'INTERV', num_rows from user_tables where table_name = 'INTERV'
24 union all
25 select 'COMPTE', num_rows from user_tables where table_name = 'COMPTE'
26 union all
27 select 'POOPAR', num_rows from user_tables where table_name = 'POOPAR';
'TPOOIN_T NUM_ROWS
--------- ----------
TPOOIN_TT 179792
POOLIB_TT 13790
TPOOGF_TT 178620
RENGEN 1
DATRAI 9
IAVOPE 4509017
AVICRC 131225387
INTERV 20204
COMPTE 40403
POOPAR 21
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> sho parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_optimizer_mjc_enabled boolean TRUE
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
The table ended by "_TT" are temporary tables. I inserted data into these tables before running my query and I gathered statistics for this test case but usually we use Dynamic Sampling at level 2 for temporary tables.
Thanks for helping