Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, ZS.

Asked: June 02, 2002 - 10:02 pm UTC

Last updated: September 15, 2009 - 12:42 pm UTC

Version: 8.1.7

Viewed 50K+ times! This question is

You Asked

Why cartesian merge join?

I have the following SQL and I keep getting a merge join cartesian and dont understand why?

SELECT vacctbgr.NO_ACCOUNT
,vacctbgr.cd_company_system
FROM
TVP298SCHEDBILLCYL schbllcyc
,TVP313SYSSHEDDEFLT schdfl
,TVP719VACCTBILLGRP vacctbgr
WHERE
schdfl.ST_PRODUCTION = 'R'
AND schbllcyc.no_version = schdfl.no_version
AND schbllcyc.yr_calender = schdfl.yr_calender
AND schbllcyc.cd_company_system = schdfl.cd_company_system

AND schbllcyc.no_billing_cycle = vacctbgr.no_vol_bill_cycle
AND schbllcyc.cd_company_system = vacctbgr.cd_company_system

AND schbllcyc.cd_company_system='RRRR'
AND schbllcyc.dt_schedule = to_date('20-apr-02','DD-MON-YY')
/

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=41)
1 0 NESTED LOOPS (Cost=2 Card=1 Bytes=41)
2 1 MERGE JOIN (CARTESIAN) (Cost=2 Card=1 Bytes=21)
3 2 TABLE ACCESS (FULL) OF 'TVP313SYSSHEDDEFLT' (Cost=1
Card=1 Bytes=12)
4 2 SORT (JOIN) (Cost=1 Card=1 Bytes=9)
5 4 TABLE ACCESS (FULL) OF 'TVP719VACCTBILLGRP' (Cost=1
Card=1 Bytes=9)
6 1 INDEX (UNIQUE SCAN) OF 'PVP298' (UNIQUE)

------------------------------------------------------------
However the join in the SQL is:
schbllcyc --> schdfl
|
|
vacctbgr
why Oracle is joining schdfl --> vacctbgr ?

Thanks for your help,
ZS


and we said...

Good gosh -- those table names are indistinguishable from gibberish. Amazing how hard that makes it to actually follow the example. Thankfully it is a small query with a small plan.

Look at the plan. It is assuming that the full scan on 'TVP313SYSSHEDDEFLT' (schdfl) will return one row. It is also assuming that the full scan on 'TVP719VACCTBILLGRP' (vacctbgr) will return 1 row as well. It just puts those two rows together (cartesian product) and joins that result to an index on TVP298SCHEDBILLCYL.

It is because of the low cardinality expected back from the two tables that it went this way. It is a perfectly valid approach and can be very fast.

Rating

  (87 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, June 05, 2002 - 9:44 am UTC

Thanks Tom this was very useful. regarding the names, unfortunatly the table names are coming from a package the company bought and we have to live with them :(

Merge Join Cartesian

Vivek Sharma, May 26, 2003 - 12:41 pm UTC

Dear Tom,

I have a query which is running out of temp tablespace. The explain plan shows merge join cartesian. I have analyzed the tables which had further reduced teh cost of the plan but the cost even now is too high and ther merge join cartesian does not disappers.
The Query is

select
trades.user_code as user_code,
null as amc_code,
trades.tran_id as trxn_number,
null as folio_no,
null as check_digit_number,
trades.tran_type as trxn_type,
trandet.appln_no as application_number,
trades.rb_sch_code as scheme_code,
cusmashead.app_first_name as Inv_first_name,
cusmashead.joint_name1 ,
cusmashead.joint_name2,
cusmasdet.app_add1,
cusmasdet.app_add2,
cusmasdet.app_add3,
cusmasdet.app_city,
cusmasdet.app_pin,
cusmasdet.app_tel_o,
to_char(trandet.tran_date,'dd-mon-yyyy') as trxn_date,
to_char(trandet.tran_date,'hh24-mm-ss') as trxn_time,
'Y' as units,
trades.amount,
trandet.pay_mode,
trades.brok_code,
trades.sub_brok_code,
null,
trandet.issuing_bank,
trandet.issuing_branch,
trandet.instr_issue_date,
trandet.instr_no,
null,
null,
null,
cusmasdet.app_dob,
cusmasdet.gaur_name,
null as guardian_father_name,
null as guardian_mother_name,
cusmasdet.gaur_birth,
cusmasdet.APP_PAN_GIR_NO,
cusmasdet.app_tel_r,
null,
null,
cusmasdet.app_email,
null,
null,
null,
trades.bnk_acc_no,
trades.bnk_acc_type,
trades.bnk_name,
trades.bnk_branch,
null,
null,
cusmasdet.app_hold_mode,
null,
null,
cusmasdet.j1d_birth,
null,
null,
cusmasdet.j2d_birth,
cusmasdet.app_occup,
bnkrbstatusmast.cons_name,
null as remarks,
cusmasdet.app_state as state,
null as sub_tran_type,
trades.value_date as value_date,
trades.payment_mandate as pay_mechanism,
trades.payment_mandate as div_pay_mechanism,
null as ecs_number,
null as alt_broker,
null as alt_folio_number,
null as redeem_dividend,
null as payin_slip_no,
null as bank_charges,
trandet.pay_mode as instrm_type,
cusnommas.nom_name ,
cusnommas.nom_relation,
cusnommas.nom_add1,
cusnommas.nom_add2,
cusnommas.nom_add3,
null as nom_city,
null as nom_pincode ,
null as nom_state,
null as nom_country,
cusnommas.nom_guar_name,
cusnommas.nom_dob,
null as currency,
null as bank_code,
null as sign_verified,
trades.inv_acc_no,
trades.opt_code,
cusmasdet.a_detail,
cusmasdet.o_detail
from
bnkrbtrandet trandet,
bnkrbtrades trades,
bnkrbcusmashead cusmashead,
bnkrbcusmasdet cusmasdet,
bnkrbcusnommas cusnommas,
bnkrbstatusmast
where trades.tran_id = trandet.tran_id
and trades.bla_no = cusmashead.bla_no
and trades.inv_acc_no = cusmasdet.inv_acc_no
and trades.nom_no (+) = cusnommas.nom_no
and cusmasdet.app_tax_status = bnkrbstatusmast.cons_name
and rownum < 3
order by
trades.tran_id, trades.bla_no, trades.inv_acc_no
/

and the Explain Plan for this query is

SELECT STATEMENT [CHOOSE] Cost = 1087275803324200000
SORT ORDER BY
COUNT STOPKEY
NESTED LOOPS
NESTED LOOPS
FILTER
MERGE JOIN OUTER
SORT JOIN
MERGE JOIN CARTESIAN
HASH JOIN
TABLE ACCESS FULL BNKRBSTATUSMAST

TABLE ACCESS FULL BNKRBCUSMASDET [ANALYZED]
SORT JOIN
TABLE ACCESS FULL BNKRBCUSNOMMAS [ANALYZED]
SORT JOIN
TABLE ACCESS FULL BNKRBTRADES [ANALYZED]
TABLE ACCESS BY INDEX ROWID BNKRBTRANDET [ANALYZED]
INDEX UNIQUE SCAN RBTRANDET_TRAN_ID_PK [ANALYZED]
TABLE ACCESS BY INDEX ROWID BNKRBCUSMASHEAD [ANALYZED]
INDEX UNIQUE SCAN CUSMAST_BLA_NO_PK [ANALYZED]

Just check the cost it is too high.
The records in all of the above tables are
BNKRBTRANDET - 152418 Records
BNKRBTRADES - 226598 Records
BNKRCUSMASHEAD - 131028 Records
BNKRBCUSMASHEAD - 137309 Records
BNKRBCUSNOMMAS - 57118 Records
BNKRSTATUSMAST - 44 Records.

How do I elimate Merge Join Cartesian. Please help me out.
Regards
Vivek Sharma

Tom Kyte
May 26, 2003 - 1:50 pm UTC

first step, lose the (+).

You have:

trades.nom_no (+) = cusnommas.nom_no

but also you have:

trades.tran_id = trandet.tran_id


Will, if the row in trades is "made up" from the (+), then tran_id will be null and NULL is never equal (nor not equal) to anything else. So the outer join is there simply to "remove access paths from the optimizer" in this case. It is not semantically relevant.



Secondly -- you do realize that an order by on a query block using rownum like that is almost always a "bug". Where rownum <= 3 is done BEFORE the order. so, it gets 3 random rows -- and then sorts them.

So, suggestions:

a) get rid of the outer join, and begin a review of all queries in your system that use outerjoins and keep them IF and ONLY IF you actually need them. Here, I KNOW we can safely remove it -- it is meaningless.

b) query like this:


select *
from ( select /*+ FIRST_ROWS */ ........ order by ..... )
where rownum <= 3;


and if you have an index on (trades.tran_id, trades.bla_no, trades.inv_acc_no), you should find this query returns instantly or faster (assuming one of those three columns is defined as NOT NULL anyway)




Helena Marková, May 27, 2003 - 2:57 am UTC


Why Merge Join Cartesian

Vivek Sharma, June 03, 2003 - 11:56 am UTC

Dear Tom,

I have created 2 tables as

create table t as
select object_id, object_name, 0 user_id
from all_objects;

create table t1 as
select 0 user_id, object_name name 
from all_objects
where rownum<=200;

Then I updated user_id from both the tables with values as 1,2 and 3 and the proportion were

SQL> select user_id, count(*) from t
  2  group by user_id;

   USER_ID   COUNT(*)   -- Total 28298
---------- ----------
         1      10000    -- 35% 
         2      15000    -- 53%
         3       3298    -- 12% 


  1  select user_id, count(*) from t1
  2* group by user_id
SQL> /

   USER_ID   COUNT(*)   -- Total 200
---------- ----------
         1         99    -- 50%
         2         49    -- 25%
         3         52    -- 25%
I have created indexes on both the tables on user_id column. But when I join these 2 tables

SQL> set autot traceonly
SQL> select t.user_id, object_id, object_name, name
  2  from t, t1
  3  where t.user_id=t1.user_id
  4  and   t.user_id=1;

990000 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1783 Card=990000 Bytes=50490000)
   1    0   MERGE JOIN (CARTESIAN) (Cost=1783 Card=990000 Bytes=50490000)
   2    1     TABLE ACCESS (FULL) OF 'T1' (Cost=1 Card=99 Bytes=1881)
   3    1     SORT (JOIN) (Cost=1782 Card=10000 Bytes=320000)
   4    3       TABLE ACCESS (FULL) OF 'T' (Cost=18 Card=10000 Bytes=320000)

Why is it going for Cartesian Join and also the number of rows are multiplied ?

Also please let me know from where can I get details of difference in Nested Loops, Sort Joins and Hash Joins. I need to know, in which scenario each of these are better than others.

Tom your answer will be very helpful for clearing my doubts as I need to improve my skills in SQL Tuning.

Thanks in advance
Regards
Vivek Sharma
 

Tom Kyte
June 03, 2003 - 12:36 pm UTC

why not? it sees you want to join every row in T where user_id = 1 with every row in T1 where user_id = 1.

You have a cartesian product that looks like a join. It did the most efficient thing for you.

your 99 rows in T are joined to EACH of the 10,000 rows in T2 -- you got exactly what you asked for. The optimizer developed the best plan possible for this query and gave you what you wanted (or, at least, what you asked for)

read the performance guide. it goes into the ins and outs of each type.

Notify when Merge Join Cartesian is used in the database

A reader, February 12, 2004 - 9:01 pm UTC

Hi Tom,

Is it possible to have OEM to monitor any Merge Join Cartesian being used by the optimizer and have it notify me?

Thanks.

Tom Kyte
February 13, 2004 - 9:47 am UTC

you'd have to script it yourself. if you have 9i, tis easy (v$sql_plan is sitting right there). if you have 8i, you'll be wanting to see when you can upgrade.

Cartesian Merge Join

A reader, April 14, 2004 - 4:44 pm UTC

Tom, I found this cartesian merge join, and I thought it might be a problem. But reading your thread, I tried to set other things, like indexes -- on tables "historico" and "ticket" (all the same, the CBO still prefers a FTS) -- memory, sorts, etc. Even so, it's taking too long...

I analyzed the schema with:

begin
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname => 'H_APP_GPA_TICKET_V1D1',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',
cascade => TRUE
);
end;
/

a. What are "buffer sort" and "inlist iterator" ?

b. What do suggest I take a look into here, by the TKPROF output ?

c. What could be a startig point for me to improve the query ?

d. Why so high fetch time and parse and exec so low ?

My last guess would be rewriting it completely. :(

Thanks ! ;))))))))))))

PS1://Ooops, this is a 9iR2.

PS2://Please, don't take my lack of binds into account. I tried it with binds too, and it did not make much difference.

select "VISAO_CLIENTE","ID","IDSIR","DATACRICACAO","DATAULTIMAATUALIZACAO",
"STATUS","CLIENTE","CENTRO_AFETADO","EQUIPAMENTO_AFETADO","PROBLEMA",
"CAUSA","CENTRO_OFENSOR","EQUIPAMENTO_OFENSOR","PREVISAO_RETORNO",
"ID_RESP_CAUSA","OBSERVACAO","ID_STATUS","ID_ULTIMO_HISTORICO_CLIENTE",
"ID_ULTIMO_HISTORICO","GPA","ID_RESP_ITEM","ID_PESSOA",
"FALHA_NO_ATENDIMENTO","NOME_CONTATO_RECLAMANTE",
"TELEFONE_CONTATO_RECLAMANTE","EMAIL_CONTATO_RECLAMANTE",
"NOME_CONTATO_PONTA","TELEFONE_CONTATO_PONTA"
from
(SELECT /*+FIRST_ROWS*/ ticket.visao_cliente, ticket.ID_TICKET as id,
historico.id_nota_externa as idsir,to_char(ticket.DATA_EVENTO,'MM/DD/YYYY
HH24:MI:SS') as datacricacao, to_char(historico.DATA_EVENTO,'MM/DD/YYYY
HH24:MI:SS') as dataultimaatualizacao, status.descricao as status,
qry_clientes.nome as cliente, ticket.CENTRO_AFETADO,
historico.EQUIPAMENTO_AFETADO, problema.descricao as problema,
causa.descricao as causa, ticket.CENTRO_OFENSOR, ticket.EQUIPAMENTO_OFENSOR,
previsao_retorno.descricao as previsao_retorno, ticket.ID_RESP_CAUSA,
historico.OBSERVACAO, historico.id_status,
ticket.ID_ULTIMO_HISTORICO_CLIENTE, ticket.ID_ULTIMO_HISTORICO, ticket.gpa,
historico.ID_RESP_ITEM, historico.id_pessoa, ticket.falha_no_atendimento,
ticket.nome_contato_reclamante, '('||ticket.ddd_contato_reclamante||')
'||ticket.telefone_contato_reclamante as telefone_contato_reclamante,
ticket.email_contato_reclamante, ticket.nome_contato_ponta,
'('||ticket.ddd_contato_ponta||') '||ticket.telefone_contato_ponta as
telefone_contato_ponta from ticket join historico on
ticket.ID_ULTIMO_HISTORICO = historico.id_historico join problema on
problema.id_problema = historico.id_problema join status on
status.id_status = historico.id_status join RESP_CAUSA on
ticket.ID_RESP_CAUSA = RESP_CAUSA.ID_RESP_CAUSA join causa on
causa.id_causa = ticket.id_causa join previsao_retorno on
historico.id_prev_retorno = previsao_retorno.id_previsao join qry_clientes
on qry_clientes.id = ticket.id_cliente where ( qry_clientes.id in ( 575,
22720, 1917, 2128, 21103, 921 ) and ( ( historico.id_status<>2 and
historico.id_status<>5 ) or ( historico.id_status in (2,5) and
historico.data_evento > to_date('2004/04/10 16:44:29', 'YYYY/MM/DD
HH24:MI:SS') ) ) ) order by TRANSLATE(to_char(historico.id_status),'521346',
'110000'), historico.data_evento desc, ticket.id_ticket desc) where rownum
<= :n


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.02 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 7.21 11.64 87 171051 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 7.24 11.67 87 171053 0 2

Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 95

Rows Row Source Operation
------- ---------------------------------------------------
2 COUNT STOPKEY
2 VIEW
2 SORT ORDER BY STOPKEY
135 NESTED LOOPS
135 NESTED LOOPS
135 NESTED LOOPS
135 HASH JOIN
21195 NESTED LOOPS
21215 NESTED LOOPS
21215 NESTED LOOPS
21215 MERGE JOIN CARTESIAN
5 TABLE ACCESS BY INDEX ROWID PREVISAO_RETORNO
5 BITMAP CONVERSION TO ROWIDS
5 BITMAP INDEX FULL SCAN NDX_TBL_PREVISAO_RETORNO (object id 41386)
21215 BUFFER SORT
4243 TABLE ACCESS FULL TICKET
21215 TABLE ACCESS BY INDEX ROWID EMPRESA
21215 INDEX UNIQUE SCAN SYS_C004306 (object id 33110)
21215 INLIST ITERATOR
21215 INDEX UNIQUE SCAN PK_CLIENTE (object id 35785)
21195 BITMAP CONVERSION TO ROWIDS
21195 BITMAP INDEX SINGLE VALUE NDX_TBL_RESP_CAUSA (object id 41387)
15349 TABLE ACCESS FULL HISTORICO
135 TABLE ACCESS BY INDEX ROWID CAUSA
135 INDEX UNIQUE SCAN CAUSA_PK11049574450818 (object id 33567)
135 TABLE ACCESS BY INDEX ROWID STATUS
135 INDEX UNIQUE SCAN STATUS_PK11049574471311 (object id 33576)
135 TABLE ACCESS BY INDEX ROWID PROBLEMA
135 INDEX UNIQUE SCAN PROBLEMA_PK11049484157278 (object id 33573)


Tom Kyte
April 15, 2004 - 7:21 am UTC

can we have an autotrace traceonly explain to compare to. above shows "reality", it would be interesting to bump reality against expectations to see if the plan is using the correct asumptions.


buffer sort is an optimization -- in this case, it is buffering the results of that full scan so as to avoid having to perform LIOS on the data over and over and over in the cartesian join. the data may or may not actually be sorted for real.

inlist iterator is sort of like a "for loop", eg:
qry_clientes.id in ( 575, 22720, 1917, 2128, 21103, 921 ) might be doing 6 index probes in "a loop" -- we iterate over the inlist values.


starting point for me would be to format the query in a readable fashion so others can see at a glance what it is doing :)


the parse and execute (parse and open) are fast cause they do not do much work. the FETCH is what causes IO to take place.

Me again (from right above) !!!! :)))))))))

A reader, April 22, 2004 - 5:09 pm UTC

Tom, following your suggestions, I have formatted the query, and run tkprof and autotrace traceonly explain.

What I did additionally is get the worst case. That is, I got the longest running query so you can give some clues how to improve it. It is also a catesian join.

It was analyzed the same way as indicated above.

Could you go on helping me ?

Thanks !

PS:// Doubt: Was is it so important to compare tkprof against autotrace ? I know tkprof is the real execution and autotrace is an estimate. But why compare both ?

select * from
(SELECT /*+FIRST_ROWS*/ ticket.visao_cliente,
ticket.ID_TICKET as id,
historico.id_nota_externa as idsir,
to_char(ticket.DATA_EVENTO,'MM/DD/YYYY HH24:MI:SS') as datacricacao,
to_char(historico.DATA_EVENTO,'MM/DD/YYYY HH24:MI:SS') as dataultimaatualizacao,
status.descricao as status, qry_clientes.nome as cliente,
ticket.CENTRO_AFETADO,
historico.EQUIPAMENTO_AFETADO,
problema.descricao as problema,
causa.descricao as causa,
ticket.CENTRO_OFENSOR,
ticket.EQUIPAMENTO_OFENSOR,
previsao_retorno.descricao as previsao_retorno,
ticket.ID_RESP_CAUSA,
historico.OBSERVACAO,
historico.id_status,
ticket.ID_ULTIMO_HISTORICO_CLIENTE,
ticket.ID_ULTIMO_HISTORICO,
ticket.gpa,
historico.ID_RESP_ITEM,
historico.id_pessoa,
ticket.falha_no_atendimento,
ticket.nome_contato_reclamante,
'('||ticket.ddd_contato_reclamante||')'||ticket.telefone_contato_reclamante as telefone_contato_reclamante,
ticket.email_contato_reclamante,
ticket.nome_contato_ponta,
'('||ticket.ddd_contato_ponta||') '||ticket.telefone_contato_ponta as telefone_contato_ponta
from
ticket join historico on ticket.ID_ULTIMO_HISTORICO = historico.id_historico
join problema on problema.id_problema = historico.id_problema
join status on status.id_status = historico.id_status
join RESP_CAUSA on ticket.ID_RESP_CAUSA = RESP_CAUSA.ID_RESP_CAUSA
join causa on causa.id_causa = ticket.id_causa
join previsao_retorno on historico.id_prev_retorno = previsao_retorno.id_previsao
join qry_clientes on qry_clientes.id = ticket.id_cliente
where
qry_clientes.id in (575, 2571, 22720, 215, 1390, 1104,
36220, 1917, 26481, 20740, 3824, 170, 349,
33100, 231, 34000, 2128, 21103, 393, 850,
250, 2627, 67, 447, 676, 884,
2173, 1531, 921, 3360, 5873, 352, 30740, 1453, 38104 ) and
( ( historico.id_status<>2 and historico.id_status<>5 ) or
( historico.id_status in (2,5) and
historico.data_evento > to_date('2004/04/12 17:14:39', 'YYYY/MM/DD HH24:MI:SS') ) )
order by
TRANSLATE(to_char(historico.id_status),'521346','110000'),
historico.data_evento desc,
ticket.id_ticket desc
) where rownum <= 50


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=44730 Card=50 Bytes=18930405)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=44730 Card=7527 Bytes=18930405)
3 2 SORT (ORDER BY STOPKEY) (Cost=44730 Card=7527 Bytes=2890368)
4 3 NESTED LOOPS (Cost=44306 Card=7527 Bytes=2890368)
5 4 NESTED LOOPS (Cost=36779 Card=7527 Bytes=2476383)
6 5 NESTED LOOPS (Cost=29252 Card=7527 Bytes=2250573)
7 6 NESTED LOOPS (Cost=21720 Card=7532 Bytes=1837808)
8 7 NESTED LOOPS (Cost=21720 Card=7747 Bytes=1851533)
9 8 NESTED LOOPS (Cost=13973 Card=7747 Bytes=1681099)
10 9 NESTED LOOPS (Cost=1941 Card=16265 Bytes=1903005)
11 10 MERGE JOIN (CARTESIAN) (Cost=21 Card=30 Bytes=510)
12 11 TABLE ACCESS (BY INDEX ROWID) OF 'PREVISAO_RETORNO' (Cost=13 Card=5 Bytes=70)
13 12 BITMAP CONVERSION (TO ROWIDS)
14 13 BITMAP INDEX (FULL SCAN) OF 'NDX_TBL_PREVISAO_RETORNO'
15 11 BUFFER (SORT) (Cost=7 Card=1 Bytes=3)
16 15 BITMAP CONVERSION (TO ROWIDS)
17 16 BITMAP INDEX (FULL SCAN) OF 'NDX_TBL_RESP_CAUSA'
18 10 TABLE ACCESS (BY INDEX ROWID) OF 'TICKET' (Cost=64 Card=186 Bytes=18600)
19 18 INDEX (RANGE SCAN) OF 'IDX_T_RES_C' (NON-UNIQUE) (Cost=2 Card=1085)
20 9 TABLE ACCESS (BY INDEX ROWID) OF 'HISTORICO' (Cost=13973 Card=2584 Bytes=258400)
21 20 BITMAP CONVERSION (TO ROWIDS)
22 21 BITMAP AND
23 22 BITMAP CONVERSION (FROM ROWIDS)
24 23 INDEX (RANGE SCAN) OF 'IDX_H_ID_H' (NON-UNIQUE)
25 22 BITMAP CONVERSION (FROM ROWIDS)
26 25 INDEX (RANGE SCAN) OF 'IDX_H_PR_R' (NON-UNIQUE) (Cost=8 Card=1)
27 8 TABLE ACCESS (BY INDEX ROWID) OF 'EMPRESA' (Cost=1 Card=1 Bytes=22)
28 27 INDEX (UNIQUE SCAN) OF 'SYS_C004306' (UNIQUE)
29 7 INLIST ITERATOR
30 29 INDEX (UNIQUE SCAN) OF 'PK_CLIENTE' (UNIQUE)
31 6 TABLE ACCESS (BY INDEX ROWID) OF 'CAUSA' (Cost=1 Card=1 Bytes=55)
32 31 INDEX (UNIQUE SCAN) OF 'CAUSA_PK11049574450818' (UNIQUE)
33 5 TABLE ACCESS (BY INDEX ROWID) OF 'STATUS' (Cost=1 Card=6 Bytes=180)
34 33 INDEX (UNIQUE SCAN) OF 'STATUS_PK11049574471311' (UNIQUE)
35 4 TABLE ACCESS (BY INDEX ROWID) OF 'PROBLEMA' (Cost=1 Card=13 Bytes=715)
36 35 INDEX (UNIQUE SCAN) OF 'PROBLEMA_PK11049484157278' (UNIQUE)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.03 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 312.65 409.34 0 445841 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 312.65 409.37 0 445843 0 2

Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 95

Rows Row Source Operation
------- ---------------------------------------------------
2 COUNT STOPKEY
2 VIEW
2 SORT ORDER BY STOPKEY
1067 NESTED LOOPS
1067 NESTED LOOPS
1067 NESTED LOOPS
1067 NESTED LOOPS
1067 NESTED LOOPS
1067 NESTED LOOPS
33830 NESTED LOOPS
30 MERGE JOIN CARTESIAN
5 TABLE ACCESS BY INDEX ROWID PREVISAO_RETORNO
5 BITMAP CONVERSION TO ROWIDS
5 BITMAP INDEX FULL SCAN NDX_TBL_PREVISAO_RETORNO (object id 41386)
30 BUFFER SORT
6 BITMAP CONVERSION TO ROWIDS
6 BITMAP INDEX FULL SCAN NDX_TBL_RESP_CAUSA (object id 41387)
33830 TABLE ACCESS BY INDEX ROWID TICKET
33830 INDEX RANGE SCAN IDX_T_RES_C (object id 42337)
1067 TABLE ACCESS BY INDEX ROWID HISTORICO
6765 BITMAP CONVERSION TO ROWIDS
6765 BITMAP AND
33825 BITMAP CONVERSION FROM ROWIDS
33825 INDEX RANGE SCAN IDX_H_ID_H (object id 42324)
25526 BITMAP CONVERSION FROM ROWIDS
155229690 INDEX RANGE SCAN IDX_H_PR_R (object id 42328)
1067 TABLE ACCESS BY INDEX ROWID EMPRESA
1067 INDEX UNIQUE SCAN SYS_C004306 (object id 33110)
1067 INLIST ITERATOR
1067 INDEX UNIQUE SCAN PK_CLIENTE (object id 35785)
1067 TABLE ACCESS BY INDEX ROWID CAUSA
1067 INDEX UNIQUE SCAN CAUSA_PK11049574450818 (object id 33567)
1067 TABLE ACCESS BY INDEX ROWID STATUS
1067 INDEX UNIQUE SCAN STATUS_PK11049574471311 (object id 33576)
1067 TABLE ACCESS BY INDEX ROWID PROBLEMA
1067 INDEX UNIQUE SCAN PROBLEMA_PK11049484157278 (object id 33573)


Tom Kyte
April 23, 2004 - 9:56 am UTC

where is the autotrace?

the reason the autotrace is relevant is it shows us the ASSUMPTIONS the optimizer used. if the assumptions the optimizer use are radically different from reality, we need to figure out why.


why do you have first rows -- is that what you *really* mean to have.

from above

A reader, April 23, 2004 - 9:28 pm UTC

In my latter posting, the first explain plan is the output from autotrace traceonly explain. Then there is the tkprof listing ( stats + explain plan ).

What do you make of it ?

I just haven't seen any conspicuous difference between autotrace and tkprof that might impact performance.

Has something caught your eye ?

What do you think of the bitmap indexes ? Maybe they are causing contention ?

I am not the one who wrote the model nor the query. I'm the dba who wants to help the development group.

It just looks there are way too many joins for an online, oltp report. See... an online report taking some tens of minutes is no good deal.

My hunch is we need some "remodelling" around here... agree ?

Tom Kyte
April 26, 2004 - 5:01 am UTC

sorry -- read too quick.

can you explain this part:

21 20 BITMAP CONVERSION (TO ROWIDS)
22 21 BITMAP AND
23 22 BITMAP CONVERSION (FROM ROWIDS)
24 23 INDEX (RANGE SCAN) OF 'IDX_H_ID_H'
(NON-UNIQUE)
25 22 BITMAP CONVERSION (FROM ROWIDS)
26 25 INDEX (RANGE SCAN) OF 'IDX_H_PR_R'
(NON-UNIQUE) (Cost=8 Card=1)

tell me about those indexes (table/columns) -- seems we are misjudging by alot the cardinalities there.

the bitmaps won't cause contention on select ( modifications -- absolutely).

There is no such thing as "too many joins :)" databases were born to join.

can you tell me how the statistics are gathered on these objects? exact commands.

Going on...

Same, April 26, 2004 - 5:23 pm UTC

Ok,

o The ouput of your desc script may help:

Table historico
================
Data Data
Column Name Type Length Nullable
------------------------------ -------------------- ----------- --------
ID_HISTORICO NUMBER not null
ID_TICKET NUMBER not null
DATA_REGISTRO DATE 7 not null
DATA_EVENTO DATE 7 not null
ID_STATUS NUMBER (38,0) not null
ID_PESSOA NUMBER not null
VISAO_CLIENTE NUMBER (1,0) not null
EQUIPAMENTO_AFETADO VARCHAR2 50 null
PLACA_AFETADO VARCHAR2 30 null
LINK_AFETADO VARCHAR2 150 null
SEVERIDADE_LINK NUMBER (10,0) null
ID_PROBLEMA NUMBER (38,0) not null
ID_PREV_RETORNO NUMBER null
ID_ACAO NUMBER (38,0) null
OBSERVACAO_REMOVEME VARCHAR2 500 null
ID_NOTA_EXTERNA_REMOVEME NUMBER (38,0) null
ID_RESP_ITEM NUMBER (38,0) null
OBSERVACAO CLOB 4000 null
SIR_CONTATOCLIENTE VARCHAR2 50 null
SIR_ESTADOPOSIC VARCHAR2 20 null
SIR_CFRESPONSAVEL VARCHAR2 20 null
SIR_NOMETECNICO VARCHAR2 50 null
SIR_OBS CLOB 4000 null
ID_NOTA_EXTERNA_NOVO VARCHAR2 30 null
ID_NOTA_EXTERNA VARCHAR2 30 null

Indexes on historico

Index Is
Name Unique COLUMNS
------------------------------ ------ --------------------------------
IDX_H_PES No ID_PESSOA
IDX_H_PRB No ID_PROBLEMA
IDX_H_STA No ID_STATUS
IDX_H_TCK No ID_TICKET
IDX_H_ID_H No ID_HISTORICO <=== These are the
IDX_H_PR_R No ID_PREV_RETORNO <=== 2 indexes
IDX_H_RESP No ID_RESP_ITEM
IDX_H_V_CL No VISAO_CLIENTE
NDX_TBL_HISTORICO No ID_TICKET, ID_HISTORICO,
ID_RESP_ITEM, ID_STATUS,
ID_PESSOA, ID_PROBLEMA,
ID_PREV_RETORNO, ID_ACAO,
VISAO_CLIENTE

NDX_TBL_HISTORICO_ID_SIR No ID_NOTA_EXTERNA
NDX_TBL_HISTORICO_DATAEVENTO No DATA_EVENTO


o Now the indexes from USER_INDEXES:

(idle)> exec print_table ('select * from user_indexes where index_name=''IDX_H_ID_H''')
------------------------------
INDEX_NAME : IDX_H_ID_H
INDEX_TYPE : NORMAL
TABLE_OWNER : APP_GPA_TICKET
TABLE_NAME : HISTORICO
TABLE_TYPE : TABLE
UNIQUENESS : NONUNIQUE
COMPRESSION : DISABLED
PREFIX_LENGTH :
TABLESPACE_NAME : USERS
INI_TRANS : 2
MAX_TRANS : 255
INITIAL_EXTENT : 65536
NEXT_EXTENT :
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE :
PCT_THRESHOLD :
INCLUDE_COLUMN :
FREELISTS :
FREELIST_GROUPS :
PCT_FREE : 10
LOGGING : YES
BLEVEL : 1
LEAF_BLOCKS : 47
DISTINCT_KEYS : 21244
AVG_LEAF_BLOCKS_PER_KEY : 1
AVG_DATA_BLOCKS_PER_KEY : 1
CLUSTERING_FACTOR : 14630
STATUS : VALID
NUM_ROWS : 21244
SAMPLE_SIZE : 21244
LAST_ANALYZED : 15-apr-2004 23:19:56
DEGREE : 1
INSTANCES : 1
PARTITIONED : NO
TEMPORARY : N
GENERATED : N
SECONDARY : N
BUFFER_POOL : DEFAULT
USER_STATS : NO
DURATION :
PCT_DIRECT_ACCESS :
ITYP_OWNER :
ITYP_NAME :
PARAMETERS :
GLOBAL_STATS : YES
DOMIDX_STATUS :
DOMIDX_OPSTATUS :
FUNCIDX_STATUS :
JOIN_INDEX : NO
------------------------------

(idle)> exec print_table ('select * from user_indexes where index_name=''IDX_H_PR_R''')
------------------------------
INDEX_NAME : IDX_H_PR_R
INDEX_TYPE : NORMAL
TABLE_OWNER : APP_GPA_TICKET
TABLE_NAME : HISTORICO
TABLE_TYPE : TABLE
UNIQUENESS : NONUNIQUE
COMPRESSION : DISABLED
PREFIX_LENGTH :
TABLESPACE_NAME : USERS
INI_TRANS : 2
MAX_TRANS : 255
INITIAL_EXTENT : 65536
NEXT_EXTENT :
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE :
PCT_THRESHOLD :
INCLUDE_COLUMN :
FREELISTS :
FREELIST_GROUPS :
PCT_FREE : 10
LOGGING : YES
BLEVEL : 1
LEAF_BLOCKS : 43
DISTINCT_KEYS : 5
AVG_LEAF_BLOCKS_PER_KEY : 8
AVG_DATA_BLOCKS_PER_KEY : 172
CLUSTERING_FACTOR : 861
STATUS : VALID
NUM_ROWS : 21244
SAMPLE_SIZE : 21244
LAST_ANALYZED : 15-apr-2004 23:19:52
DEGREE : 1
INSTANCES : 1
PARTITIONED : NO
TEMPORARY : N
GENERATED : N
SECONDARY : N
BUFFER_POOL : DEFAULT
USER_STATS : NO
DURATION :
PCT_DIRECT_ACCESS :
ITYP_OWNER :
ITYP_NAME :
PARAMETERS :
GLOBAL_STATS : YES
DOMIDX_STATUS :
DOMIDX_OPSTATUS :
FUNCIDX_STATUS :
JOIN_INDEX : NO
------------------------------

o I have the database analyzed on a daily basis with:

begin
for c in (select username
from dba_users
where username<>'SYS' and
username<>'SYSTEM') loop
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname => c.username,
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',
cascade => TRUE
);
end loop;
end;
/


Thanks again ! ;))

Tom Kyte
April 27, 2004 - 7:28 am UTC

what happens if you remove histograms on those indexed columns or gather histograms with a user specified bucket size of say 254.

same

A reader, April 27, 2004 - 10:09 am UTC

I did that, but there are no significant changes. :(((

o The plan has remained the basically the same.

o The number of LIOs remains more or less the same (from 450,000 to 471,000). In fact, it looks slightly higher because the number of rows in the table has increased in the meantime.

o CPU time has decreased slighly (from 314 to 304).

o What are these "bitmap conversion (to/from rowid)" and "bitmap and" ?

What was done:

analyze table historico compute statistics for all indexed columns size 254;
/

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 1.63 1.67 1 28 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 51 302.61 533.53 1 471630 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 53 304.24 535.21 2 471658 0 50


Execution Plan (from autotrace)
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=48291 Card
=50 Bytes=20434375)


1 0 COUNT (STOPKEY)


2 1 VIEW (Cost=48291 Card=8125 Bytes=20434375)


3 2 SORT (ORDER BY STOPKEY) (Cost=48291 Card=8125 Bytes=31
20000)


4 3 NESTED LOOPS (Cost=47833 Card=8125 Bytes=3120000)


5 4 NESTED LOOPS (Cost=39708 Card=8125 Bytes=2673125)


6 5 NESTED LOOPS (Cost=31583 Card=8125 Bytes=2429375
)


7 6 NESTED LOOPS (Cost=23453 Card=8130 Bytes=19837
20)


8 7 NESTED LOOPS (Cost=23453 Card=8362 Bytes=199
8518)


9 8 NESTED LOOPS (Cost=14851 Card=8601 Bytes=1
866417)


10 9 NESTED LOOPS (Cost=2061 Card=17335 Bytes
=2028195)


11 10 MERGE JOIN (CARTESIAN) (Cost=21 Card=3
0 Bytes=510)


12 11 TABLE ACCESS (BY INDEX ROWID) OF 'PR
EVISAO_RETORNO' (Cost=13 Card=5 Bytes=70)


13 12 BITMAP CONVERSION (TO ROWIDS)


14 13 BITMAP INDEX (FULL SCAN) OF 'NDX
_TBL_PREVISAO_RETORNO'


15 11 BUFFER (SORT) (Cost=7 Card=1 Bytes=3
)


16 15 BITMAP CONVERSION (TO ROWIDS)


17 16 BITMAP INDEX (FULL SCAN) OF 'NDX
_TBL_RESP_CAUSA'


18 10 TABLE ACCESS (BY INDEX ROWID) OF 'TICK
ET' (Cost=68 Card=198 Bytes=19800)


19 18 INDEX (RANGE SCAN) OF 'IDX_T_RES_C'
(NON-UNIQUE) (Cost=2 Card=1156)


20 9 TABLE ACCESS (BY INDEX ROWID) OF 'HISTOR
ICO' (Cost=14851 Card=2869 Bytes=286900)


21 20 BITMAP CONVERSION (TO ROWIDS)


22 21 BITMAP AND


23 22 BITMAP CONVERSION (FROM ROWIDS)


24 23 INDEX (RANGE SCAN) OF 'IDX_H_ID_
H' (NON-UNIQUE)


25 22 BITMAP CONVERSION (FROM ROWIDS)


26 25 INDEX (RANGE SCAN) OF 'IDX_H_PR_
R' (NON-UNIQUE) (Cost=10 Card=1)


27 8 TABLE ACCESS (BY INDEX ROWID) OF 'EMPRESA'
(Cost=1 Card=1 Bytes=22)


28 27 INDEX (UNIQUE SCAN) OF 'SYS_C004306' (UN
IQUE)


29 7 INLIST ITERATOR


30 29 INDEX (UNIQUE SCAN) OF 'PK_CLIENTE' (UNIQU
E)


31 6 TABLE ACCESS (BY INDEX ROWID) OF 'CAUSA' (Cost
=1 Card=1 Bytes=55)


32 31 INDEX (UNIQUE SCAN) OF 'CAUSA_PK110495744508
18' (UNIQUE)


33 5 TABLE ACCESS (BY INDEX ROWID) OF 'STATUS' (Cost=
1 Card=6 Bytes=180)


34 33 INDEX (UNIQUE SCAN) OF 'STATUS_PK1104957447131
1' (UNIQUE)


35 4 TABLE ACCESS (BY INDEX ROWID) OF 'PROBLEMA' (Cost=
1 Card=13 Bytes=715)


36 35 INDEX (UNIQUE SCAN) OF 'PROBLEMA_PK1104948415727
8' (UNIQUE)



Merge Cartesian Join

KU, April 27, 2004 - 2:17 pm UTC

By changing certain session/system level settings [optimizer_index_caching (like 80) and optimizer_index_cost_Adj (like 10)] I used to get the optimizer to use a different and better xplain plan (not using merge cartesian join) in 8.1.7 particulrly.

May be useful for some who haven't tried it.

KU

same

A reader, April 27, 2004 - 2:35 pm UTC

But, KU.... from what I gathered from Tom's comments, the cartesian join may be a good thing, not necessarily evil. I first would like to understand the steps in the plan and why it's taking so long and why the LIOs are so high.

same

A reader, April 28, 2004 - 9:21 am UTC

Tom, me again...

I got something new for us... I re-wrote the query incrementally and I found that the problem is the way I am dealing with the order by clause. 

We can isolate the problem unto it:

order by
  TRANSLATE(to_char(historico.id_status),'521346','110000'),
  historico.data_evento desc,
  ticket.id_ticket desc

When I used no order by, the time was great, around 2 seconds. But as I added the order by columns, the timing  got worse, getting to nearly 5 minutes. 

The first thing I considered was sorting, but it does not seem to be a problem: ( from the entire session running the 3 queries altogether ):  

sorts (memory)                                                          148
sorts (disk)                                                              0
sorts (rows)                                                           6819

And what strikes me most (I may be quite wrong) is that I thought indexes on the order by columns would speed up the query. I see it's sorting the order by columns instead (right ?). So, I have the 3 indexes on the order by columns:    

SQL> exec print_table ('select * from user_indexes where index_name=''IDX_FUNC_H_TRANS''')
------------------------------
INDEX_NAME                    : IDX_FUNC_H_TRANS
INDEX_TYPE                    : FUNCTION-BASED NORMAL
TABLE_OWNER                   : APP_GPA_TICKET
TABLE_NAME                    : HISTORICO
TABLE_TYPE                    : TABLE
UNIQUENESS                    : NONUNIQUE
COMPRESSION                   : DISABLED
PREFIX_LENGTH                 :
TABLESPACE_NAME               : USERS
INI_TRANS                     : 2
MAX_TRANS                     : 255
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   :
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
PCT_INCREASE                  :
PCT_THRESHOLD                 :
INCLUDE_COLUMN                :
FREELISTS                     :
FREELIST_GROUPS               :
PCT_FREE                      : 10
LOGGING                       : YES
BLEVEL                        : 1
LEAF_BLOCKS                   : 43
DISTINCT_KEYS                 : 2
AVG_LEAF_BLOCKS_PER_KEY       : 21
AVG_DATA_BLOCKS_PER_KEY       : 822
CLUSTERING_FACTOR             : 1645
STATUS                        : VALID
NUM_ROWS                      : 23786
SAMPLE_SIZE                   : 23786
LAST_ANALYZED                 : 28-apr-2004 09:38:48
DEGREE                        : 1
INSTANCES                     : 1
PARTITIONED                   : NO
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
BUFFER_POOL                   : DEFAULT
USER_STATS                    : NO
DURATION                      :
PCT_DIRECT_ACCESS             :
ITYP_OWNER                    :
ITYP_NAME                     :
PARAMETERS                    :
GLOBAL_STATS                  : NO
DOMIDX_STATUS                 :
DOMIDX_OPSTATUS               :
FUNCIDX_STATUS                : ENABLED
JOIN_INDEX                    : NO
------------------------------

SQL> exec print_table ('select * from user_ind_expressions where index_name=''IDX_FUNC_H_TRANS''')
------------------------------
INDEX_NAME                    : IDX_FUNC_H_TRANS
TABLE_NAME                    : HISTORICO
COLUMN_EXPRESSION             :
TRANSLATE(TO_CHAR("ID_STATUS"),'521346','110000')
COLUMN_POSITION               : 1
------------------------------

SQL> exec print_table ('select * from user_indexes where index_name=''IDX_H_ID_H''')
------------------------------
INDEX_NAME                    : IDX_H_ID_H
INDEX_TYPE                    : NORMAL
TABLE_OWNER                   : APP_GPA_TICKET
TABLE_NAME                    : HISTORICO
TABLE_TYPE                    : TABLE
UNIQUENESS                    : NONUNIQUE
COMPRESSION                   : DISABLED
PREFIX_LENGTH                 :
TABLESPACE_NAME               : USERS
INI_TRANS                     : 2
MAX_TRANS                     : 255
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   :
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
PCT_INCREASE                  :
PCT_THRESHOLD                 :
INCLUDE_COLUMN                :
FREELISTS                     :
FREELIST_GROUPS               :
PCT_FREE                      : 10
LOGGING                       : YES
BLEVEL                        : 1
LEAF_BLOCKS                   : 54
DISTINCT_KEYS                 : 23789
AVG_LEAF_BLOCKS_PER_KEY       : 1
AVG_DATA_BLOCKS_PER_KEY       : 1
CLUSTERING_FACTOR             : 15922
STATUS                        : VALID
NUM_ROWS                      : 23789
SAMPLE_SIZE                   : 23789
LAST_ANALYZED                 : 28-apr-2004 09:42:50
DEGREE                        : 1
INSTANCES                     : 1
PARTITIONED                   : NO
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
BUFFER_POOL                   : DEFAULT
USER_STATS                    : NO
DURATION                      :
PCT_DIRECT_ACCESS             :
ITYP_OWNER                    :
ITYP_NAME                     :
PARAMETERS                    :
GLOBAL_STATS                  : YES
DOMIDX_STATUS                 :
DOMIDX_OPSTATUS               :
FUNCIDX_STATUS                :
JOIN_INDEX                    : NO
------------------------------

SQL> exec print_table ('select * from user_ind_columns where index_name=''IDX_H_ID_H''')
------------------------------
INDEX_NAME                    : IDX_H_ID_H
TABLE_NAME                    : HISTORICO
COLUMN_NAME                   : ID_HISTORICO
COLUMN_POSITION               : 1
COLUMN_LENGTH                 : 22
CHAR_LENGTH                   : 0
DESCEND                       : ASC
------------------------------

SQL> exec print_table ('select * from user_indexes where index_name=''IDX_H_STA''')
------------------------------
INDEX_NAME                    : IDX_H_STA
INDEX_TYPE                    : NORMAL
TABLE_OWNER                   : APP_GPA_TICKET
TABLE_NAME                    : HISTORICO
TABLE_TYPE                    : TABLE
UNIQUENESS                    : NONUNIQUE
COMPRESSION                   : DISABLED
PREFIX_LENGTH                 :
TABLESPACE_NAME               : USERS
INI_TRANS                     : 2
MAX_TRANS                     : 255
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   :
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
PCT_INCREASE                  :
PCT_THRESHOLD                 :
INCLUDE_COLUMN                :
FREELISTS                     :
FREELIST_GROUPS               :
PCT_FREE                      : 10
LOGGING                       : YES
BLEVEL                        : 1
LEAF_BLOCKS                   : 51
DISTINCT_KEYS                 : 6
AVG_LEAF_BLOCKS_PER_KEY       : 8
AVG_DATA_BLOCKS_PER_KEY       : 472
CLUSTERING_FACTOR             : 2835
STATUS                        : VALID
NUM_ROWS                      : 23553
SAMPLE_SIZE                   : 23553
LAST_ANALYZED                 : 26-apr-2004 23:21:49
DEGREE                        : 1
INSTANCES                     : 1
PARTITIONED                   : NO
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
BUFFER_POOL                   : DEFAULT
USER_STATS                    : NO
DURATION                      :
PCT_DIRECT_ACCESS             :
ITYP_OWNER                    :
ITYP_NAME                     :
PARAMETERS                    :
GLOBAL_STATS                  : YES
DOMIDX_STATUS                 :
DOMIDX_OPSTATUS               :
FUNCIDX_STATUS                :
JOIN_INDEX                    : NO
------------------------------

SQL> exec print_table ('select * from user_ind_columns where index_name=''IDX_H_STA''')
------------------------------
INDEX_NAME                    : IDX_H_STA
TABLE_NAME                    : HISTORICO
COLUMN_NAME                   : ID_STATUS
COLUMN_POSITION               : 1
COLUMN_LENGTH                 : 22
CHAR_LENGTH                   : 0
DESCEND                       : ASC
------------------------------

So I can use the function-based index (1st index):

SQL> select privilege from session_privs where privilege like '%REWRITE%';

PRIVILEGE
----------------------------------------
QUERY REWRITE
GLOBAL QUERY REWRITE


Here is the ouput from tkprof for the 3 cases (1 col, 2 cols, 3 cols):

********************************************************************************

select * from
(SELECT /*+FIRST_ROWS*/ ticket.visao_cliente,
                        ticket.ID_TICKET as id,
                        historico.id_nota_externa as idsir,
                        to_char(ticket.DATA_EVENTO,'MM/DD/YYYY HH24:MI:SS') as datacricacao,
                        to_char(historico.DATA_EVENTO,'MM/DD/YYYY HH24:MI:SS') as dataultimaatualizacao,
                        status.descricao as status, qry_clientes.nome as cliente,
                        ticket.CENTRO_AFETADO,
                        historico.EQUIPAMENTO_AFETADO,
                        problema.descricao as problema,
                        causa.descricao as causa,
                        ticket.CENTRO_OFENSOR,
                        ticket.EQUIPAMENTO_OFENSOR,
                        previsao_retorno.descricao as previsao_retorno,
                        ticket.ID_RESP_CAUSA,
                        historico.OBSERVACAO,
                        historico.id_status,
                        ticket.ID_ULTIMO_HISTORICO_CLIENTE,
                        ticket.ID_ULTIMO_HISTORICO,
                        ticket.gpa,
                        historico.ID_RESP_ITEM,
                        historico.id_pessoa,
                        ticket.falha_no_atendimento,
                        ticket.nome_contato_reclamante,
                        '('||ticket.ddd_contato_reclamante||')'||ticket.telefone_contato_reclamante as telefone_contato_reclam
ante,
                        ticket.email_contato_reclamante,
                        ticket.nome_contato_ponta,
                        '('||ticket.ddd_contato_ponta||') '||ticket.telefone_contato_ponta as telefone_contato_ponta
 from
      ticket join historico on ticket.ID_ULTIMO_HISTORICO = historico.id_historico
             join problema on problema.id_problema = historico.id_problema
             join status on status.id_status = historico.id_status
             join RESP_CAUSA on ticket.ID_RESP_CAUSA = RESP_CAUSA.ID_RESP_CAUSA
             join causa on causa.id_causa = ticket.id_causa
             join previsao_retorno on historico.id_prev_retorno = previsao_retorno.id_previsao
             join qry_clientes on qry_clientes.id = ticket.id_cliente
 where
      qry_clientes.id in (575, 2571, 22720, 215, 1390, 1104,
                           36220, 1917, 26481, 20740, 3824, 170, 349,
                           33100, 231, 34000, 2128, 21103, 393, 850,
                           250, 2627, 67, 447, 676, 884,
                           2173, 1531, 921, 3360, 5873, 352, 30740, 1453, 38104 ) and
     ( ( historico.id_status<>2 and historico.id_status<>5 ) or
     ( historico.id_status in (2,5) and
       historico.data_evento > to_date('2004/04/12 17:14:39', 'YYYY/MM/DD HH24:MI:SS') ) )
--  order by
--    TRANSLATE(to_char(historico.id_status),'521346','110000'),
--    historico.data_evento desc,
--    ticket.id_ticket desc
) where rownum <= 50

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      1.70       1.67          1         30          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.81       0.78          0       1747          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      2.51       2.46          1       1777          0           2

Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 95

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  COUNT STOPKEY
      2   NESTED LOOPS
      2    NESTED LOOPS
      2     NESTED LOOPS
      2      NESTED LOOPS
      2       NESTED LOOPS
      2        NESTED LOOPS
    330         NESTED LOOPS
      1          MERGE JOIN CARTESIAN
      1           TABLE ACCESS BY INDEX ROWID PREVISAO_RETORNO
      1            BITMAP CONVERSION TO ROWIDS
      1             BITMAP INDEX FULL SCAN NDX_TBL_PREVISAO_RETORNO (object id 41386)
      1           BUFFER SORT
      6            BITMAP CONVERSION TO ROWIDS
      6             BITMAP INDEX FULL SCAN NDX_TBL_RESP_CAUSA (object id 41387)
    330          TABLE ACCESS BY INDEX ROWID TICKET
    330           INDEX RANGE SCAN IDX_T_RES_C (object id 42337)
      2         TABLE ACCESS BY INDEX ROWID HISTORICO
      2          BITMAP CONVERSION TO ROWIDS
      2           BITMAP AND
    330            BITMAP CONVERSION FROM ROWIDS
    330             INDEX RANGE SCAN IDX_H_ID_H (object id 42324)
    330            BITMAP CONVERSION FROM ROWIDS
 419430             INDEX RANGE SCAN IDX_H_PR_R (object id 42328)
      2        TABLE ACCESS BY INDEX ROWID EMPRESA
      2         INDEX UNIQUE SCAN SYS_C004306 (object id 33110)
      2       INLIST ITERATOR
      2        INDEX UNIQUE SCAN PK_CLIENTE (object id 35785)
      2      TABLE ACCESS BY INDEX ROWID CAUSA
      2       INDEX UNIQUE SCAN CAUSA_PK11049574450818 (object id 33567)
      2     TABLE ACCESS BY INDEX ROWID STATUS
      2      INDEX UNIQUE SCAN STATUS_PK11049574471311 (object id 33576)
      2    TABLE ACCESS BY INDEX ROWID PROBLEMA
      2     INDEX UNIQUE SCAN PROBLEMA_PK11049484157278 (object id 33573)

********************************************************************************

...
 order by
--    TRANSLATE(to_char(historico.id_status),'521346','110000'),
--    historico.data_evento desc,
    ticket.id_ticket desc
) where rownum <= 50

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      1.84       1.81          0          3          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      3.40       3.40          0      74934          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      5.24       5.22          0      74937          0           1

Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 95

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  COUNT STOPKEY
      1   VIEW
      1    SORT ORDER BY STOPKEY
   1285     NESTED LOOPS
   1285      NESTED LOOPS
   1285       NESTED LOOPS
   1285        NESTED LOOPS
   1285         NESTED LOOPS
   6984          NESTED LOOPS
   6984           NESTED LOOPS
   6988            NESTED LOOPS
     35             INLIST ITERATOR
     35              TABLE ACCESS BY INDEX ROWID EMPRESA
     35               INDEX RANGE SCAN SYS_C004306 (object id 33110)
   6988             INLIST ITERATOR
   6988              TABLE ACCESS BY INDEX ROWID TICKET
   6988               INDEX RANGE SCAN IDX_T_CLI (object id 42332)
   6984            BITMAP CONVERSION TO ROWIDS
   6984             BITMAP INDEX SINGLE VALUE NDX_TBL_RESP_CAUSA (object id 41387)
   6984           TABLE ACCESS BY INDEX ROWID CAUSA
   6984            INDEX UNIQUE SCAN CAUSA_PK11049574450818 (object id 33567)
   1285          TABLE ACCESS BY INDEX ROWID HISTORICO
   6983           INDEX RANGE SCAN IDX_H_ID_H (object id 42324)
   1285         TABLE ACCESS BY INDEX ROWID PREVISAO_RETORNO
   1285          BITMAP CONVERSION TO ROWIDS
   1285           BITMAP INDEX FULL SCAN NDX_TBL_PREVISAO_RETORNO (object id 41386)
   1285        TABLE ACCESS BY INDEX ROWID STATUS
   1285         INDEX UNIQUE SCAN STATUS_PK11049574471311 (object id 33576)
   1285       TABLE ACCESS BY INDEX ROWID PROBLEMA
   1285        INDEX UNIQUE SCAN PROBLEMA_PK11049484157278 (object id 33573)
   1285      INLIST ITERATOR
   1285       INDEX UNIQUE SCAN PK_CLIENTE (object id 35785)

********************************************************************************
 
...
order by
--    TRANSLATE(to_char(historico.id_status),'521346','110000'),
    historico.data_evento desc,
    ticket.id_ticket desc
) where rownum <= 50

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      1.64       1.61          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2    386.94     476.74          0     474629          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4    388.58     478.35          0     474629          0           2

Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 95

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  COUNT STOPKEY
      2   VIEW
      2    SORT ORDER BY STOPKEY
   1285     NESTED LOOPS
   1285      NESTED LOOPS
   1285       NESTED LOOPS
   1285        NESTED LOOPS
   1285         NESTED LOOPS
   1285          NESTED LOOPS
  34920           NESTED LOOPS
     30            MERGE JOIN CARTESIAN
      5             TABLE ACCESS BY INDEX ROWID PREVISAO_RETORNO
      5              BITMAP CONVERSION TO ROWIDS
      5               BITMAP INDEX FULL SCAN NDX_TBL_PREVISAO_RETORNO (object id 41386)
     30             BUFFER SORT
      6              BITMAP CONVERSION TO ROWIDS
      6               BITMAP INDEX FULL SCAN NDX_TBL_RESP_CAUSA (object id 41387)
  34920            TABLE ACCESS BY INDEX ROWID TICKET
  34920             INDEX RANGE SCAN IDX_T_RES_C (object id 42337)
   1285           TABLE ACCESS BY INDEX ROWID HISTORICO
   6983            BITMAP CONVERSION TO ROWIDS
   6983             BITMAP AND
  34915              BITMAP CONVERSION FROM ROWIDS
  34915               INDEX RANGE SCAN IDX_H_ID_H (object id 42324)
  26209              BITMAP CONVERSION FROM ROWIDS
165957978               INDEX RANGE SCAN IDX_H_PR_R (object id 42328)
   1285          TABLE ACCESS BY INDEX ROWID EMPRESA
   1285           INDEX UNIQUE SCAN SYS_C004306 (object id 33110)
   1285         INLIST ITERATOR
   1285          INDEX UNIQUE SCAN PK_CLIENTE (object id 35785)
   1285        TABLE ACCESS BY INDEX ROWID CAUSA
   1285         INDEX UNIQUE SCAN CAUSA_PK11049574450818 (object id 33567)
   1285       TABLE ACCESS BY INDEX ROWID STATUS
   1285        INDEX UNIQUE SCAN STATUS_PK11049574471311 (object id 33576)
   1285      TABLE ACCESS BY INDEX ROWID PROBLEMA
   1285       INDEX UNIQUE SCAN PROBLEMA_PK11049484157278 (object id 33573)

********************************************************************************

...
order by
    TRANSLATE(to_char(historico.id_status),'521346','110000'),
    historico.data_evento desc,
    ticket.id_ticket desc
) where rownum <= 50

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      1.61       3.37          0          0          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch        1    277.21     343.93          0     474624          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3    278.83     347.31          0     474624          0           1

Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 95

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  COUNT STOPKEY
      1   VIEW
      1    SORT ORDER BY STOPKEY
   1285     NESTED LOOPS
   1285      NESTED LOOPS
   1285       NESTED LOOPS
   1285        NESTED LOOPS
   1285         NESTED LOOPS
   1285          NESTED LOOPS
  34920           NESTED LOOPS
     30            MERGE JOIN CARTESIAN
      5             TABLE ACCESS BY INDEX ROWID PREVISAO_RETORNO
      5              BITMAP CONVERSION TO ROWIDS
      5               BITMAP INDEX FULL SCAN NDX_TBL_PREVISAO_RETORNO (object id 41386)
     30             BUFFER SORT
      6              BITMAP CONVERSION TO ROWIDS
      6               BITMAP INDEX FULL SCAN NDX_TBL_RESP_CAUSA (object id 41387)
  34920            TABLE ACCESS BY INDEX ROWID TICKET
  34920             INDEX RANGE SCAN IDX_T_RES_C (object id 42337)
   1285           TABLE ACCESS BY INDEX ROWID HISTORICO
   6983            BITMAP CONVERSION TO ROWIDS
   6983             BITMAP AND
  34915              BITMAP CONVERSION FROM ROWIDS
  34915               INDEX RANGE SCAN IDX_H_ID_H (object id 42324)
  26208              BITMAP CONVERSION FROM ROWIDS
165978927               INDEX RANGE SCAN IDX_H_PR_R (object id 42328)
   1285          TABLE ACCESS BY INDEX ROWID EMPRESA
   1285           INDEX UNIQUE SCAN SYS_C004306 (object id 33110)
   1285         INLIST ITERATOR
   1285          INDEX UNIQUE SCAN PK_CLIENTE (object id 35785)
   1285        TABLE ACCESS BY INDEX ROWID CAUSA
   1285         INDEX UNIQUE SCAN CAUSA_PK11049574450818 (object id 33567)
   1285       TABLE ACCESS BY INDEX ROWID STATUS
   1285        INDEX UNIQUE SCAN STATUS_PK11049574471311 (object id 33576)
   1285      TABLE ACCESS BY INDEX ROWID PROBLEMA
   1285       INDEX UNIQUE SCAN PROBLEMA_PK11049484157278 (object id 33573)


I hope this will give you enough material to help me.

Thanks ! 

Tom Kyte
April 28, 2004 - 12:46 pm UTC

you are ordering by columns from two different tables. no single index is going to "presort that"

you do understand that when you add the order by - in order to find the FIRST row, it had to find "THE LAST ROW" so it could sort.

you are comparing apples with toaster ovens

select * from ( select ... <no order by> ) where rownum < :n

is not even REMOTELY comparable to

select * from ( select ... order by anything ) where rownum < :n

same

A reader, April 28, 2004 - 1:33 pm UTC

Tom, ok... but how do you see I can improve that query, or is it impossible to do better than that ? Maybe merge the tables and build an only index on it for the order by ?

same

A reader, April 28, 2004 - 2:41 pm UTC

Tom, from what you said, I understand that if I create an index on the columns on the same table "historico", instead of on different tables, I will utilize the index and therefore the sort will be done on the index -- faster than the in-memory sort. 

And I understood also that I'll have to wait Oracle sort all the rows before if goes to the outer select. Which doesn't happen when I don't use the order by -- in this latter case the lines go directly out to the outer query in a "pipelined" fashion and that's why it is so fast. Is that right ? 

Anyway.... I tried the approach of indexing the order by columns on one only table. It was simple. The column "id_ticket" from the other table is also in "historico" as a foreign key. So I was able to "reproduce" the three columns on the same table "historico" very easily.

Instead of:

 order by 
    TRANSLATE(to_char historico.id_status),'521346','110000'),
    historico.data_evento desc,
==> ticket.id_ticket desc   

I did:

 order by 
    TRANSLATE(to_char(historico.id_status),'521346','110000'),
    historico.data_evento desc,
==> historico.id_ticket desc

So:

SQL> create index ix_h_orderby on historico(TRANSLATE(to_char(id_status),'521346','110000'), data_evento desc, id_ticket desc);

Index created.

SQL> analyze index ix_h_orderby compute statistics;

Index analyzed.

SQL> select privilege from session_privs where privilege like '%REWRITE%';

PRIVILEGE
----------------------------------------
QUERY REWRITE
GLOBAL QUERY REWRITE

However... (back to Tom Kyte's PROFiler ;)))))

select * from
(SELECT /*+FIRST_ROWS*/ ticket.visao_cliente,
                        ticket.ID_TICKET as id,
                        historico.id_nota_externa as idsir,
                        to_char(ticket.DATA_EVENTO,'MM/DD/YYYY HH24:MI:SS') as datacricacao,
                        to_char(historico.DATA_EVENTO,'MM/DD/YYYY HH24:MI:SS') as dataultimaatualizacao,
                        status.descricao as status, qry_clientes.nome as cliente,
                        ticket.CENTRO_AFETADO,
                        historico.EQUIPAMENTO_AFETADO,
                        problema.descricao as problema,
                        causa.descricao as causa,
                        ticket.CENTRO_OFENSOR,
                        ticket.EQUIPAMENTO_OFENSOR,
                        previsao_retorno.descricao as previsao_retorno,
                        ticket.ID_RESP_CAUSA,
                        historico.OBSERVACAO,
                        historico.id_status,
                        ticket.ID_ULTIMO_HISTORICO_CLIENTE,
                        ticket.ID_ULTIMO_HISTORICO,
                        ticket.gpa,
                        historico.ID_RESP_ITEM,
                        historico.id_pessoa,
                        ticket.falha_no_atendimento,
                        ticket.nome_contato_reclamante,
                        '('||ticket.ddd_contato_reclamante||')'||ticket.telefone_contato_reclamante as telefone_contato_reclam
ante,
                        ticket.email_contato_reclamante,
                        ticket.nome_contato_ponta,
                        '('||ticket.ddd_contato_ponta||') '||ticket.telefone_contato_ponta as telefone_contato_ponta
 from
      ticket join historico on ticket.ID_ULTIMO_HISTORICO = historico.id_historico
             join problema on problema.id_problema = historico.id_problema
             join status on status.id_status = historico.id_status
             join RESP_CAUSA on ticket.ID_RESP_CAUSA = RESP_CAUSA.ID_RESP_CAUSA
             join causa on causa.id_causa = ticket.id_causa
             join previsao_retorno on historico.id_prev_retorno = previsao_retorno.id_previsao
             join qry_clientes on qry_clientes.id = ticket.id_cliente
 where
      qry_clientes.id in (575, 2571, 22720, 215, 1390, 1104,
                           36220, 1917, 26481, 20740, 3824, 170, 349,
                           33100, 231, 34000, 2128, 21103, 393, 850,
                           250, 2627, 67, 447, 676, 884,
                           2173, 1531, 921, 3360, 5873, 352, 30740, 1453, 38104 ) and
     ( ( historico.id_status<>2 and historico.id_status<>5 ) or
     ( historico.id_status in (2,5) and
       historico.data_evento > to_date('2004/04/12 17:14:39', 'YYYY/MM/DD HH24:MI:SS') ) )
 order by
    TRANSLATE(to_char(historico.id_status),'521346','110000'),
    historico.data_evento desc,
    historico.id_ticket desc
) where rownum <= 50

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      1.74       1.74          0        506          0           0
Execute      1      0.01       0.00          0          0          0           0
Fetch        2    317.15     440.15          0     476629          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4    318.90     441.90          0     477135          0           2

Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 95

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  COUNT STOPKEY
      2   VIEW
      2    SORT ORDER BY STOPKEY
   1305     NESTED LOOPS
   1305      NESTED LOOPS
   1305       NESTED LOOPS
   1305        NESTED LOOPS
   1305         NESTED LOOPS
   1305          NESTED LOOPS
  35020           NESTED LOOPS
     30            MERGE JOIN CARTESIAN
      5             TABLE ACCESS BY INDEX ROWID PREVISAO_RETORNO
      5              BITMAP CONVERSION TO ROWIDS
      5               BITMAP INDEX FULL SCAN NDX_TBL_PREVISAO_RETORNO (object id 41386)
     30             BUFFER SORT
      6              BITMAP CONVERSION TO ROWIDS
      6               BITMAP INDEX FULL SCAN NDX_TBL_RESP_CAUSA (object id 41387)
  35020            TABLE ACCESS BY INDEX ROWID TICKET
  35020             INDEX RANGE SCAN IDX_T_RES_C (object id 42337)
   1305           TABLE ACCESS BY INDEX ROWID HISTORICO
   7003            BITMAP CONVERSION TO ROWIDS
   7003             BITMAP AND
  35015              BITMAP CONVERSION FROM ROWIDS
  35015               INDEX RANGE SCAN IDX_H_ID_H (object id 42324)
  26238              BITMAP CONVERSION FROM ROWIDS
166993538               INDEX RANGE SCAN IDX_H_PR_R (object id 42328)
   1305          TABLE ACCESS BY INDEX ROWID EMPRESA
   1305           INDEX UNIQUE SCAN SYS_C004306 (object id 33110)
   1305         INLIST ITERATOR
   1305          INDEX UNIQUE SCAN PK_CLIENTE (object id 35785)
   1305        TABLE ACCESS BY INDEX ROWID CAUSA
   1305         INDEX UNIQUE SCAN CAUSA_PK11049574450818 (object id 33567)
   1305       TABLE ACCESS BY INDEX ROWID STATUS
   1305        INDEX UNIQUE SCAN STATUS_PK11049574471311 (object id 33576)
   1305      TABLE ACCESS BY INDEX ROWID PROBLEMA
   1305       INDEX UNIQUE SCAN PROBLEMA_PK11049484157278 (object id 33573)

It did not use the index :(((((((((
Why ??? Wouldn't it make all the sense in the world ?
Is there some prereq I'm missing ?

So........... ???  

Tom Kyte
April 28, 2004 - 7:41 pm UTC

it is very hard to examine and understand a query in this particular fashion (followup and review)...

i've no idea (please please don't put it all here -- wait for a question opportunity) what indexes go with what tables.

it is a large query -- with lots of tables, lots of predicates.

getting the data "sorted" does not mean "data will be fast"

for example -- say it used your "orderby" index. It could pull 50,000,000,000 records "sorted" -- find they join to *nothing* in the other tables -- before finding the very very first row.

why would that make any sense in any world? no reason that the first "n records" from historico that match your predicates on that table would:

a) join to any of the other tables
b) after joining, passing the OTHER predicates!!


is funny that:

( historico.id_status in (2,5) and
historico.data_evento > to_date('2004/04/12 17:14:39', 'YYYY/MM/DD
HH24:MI:SS') ) )
order by
TRANSLATE(to_char(historico.id_status),'521346','110000'),
historico.data_evento desc,
historico.id_ticket desc

means that a id status of 521346 is *impossible*.... the decode is not "useful" in this particular query.


same

A reader, April 29, 2004 - 7:32 am UTC

*flush* cursor sharing was set to similar...

When I set it to exact, it was really fast...

In fact, all cartesian joins ceased.

Why ???

Tom Kyte
April 29, 2004 - 8:21 am UTC

because:

...
order by
TRANSLATE(to_char(historico.id_status),'521346','110000'),
historico.data_evento desc,
ticket.id_ticket desc
) where rownum <= 50


would become:

...
order by
TRANSLATE(to_char(historico.id_status),:"SYS_BVn", :"SYS_BVm" ),
historico.data_evento desc,
ticket.id_ticket desc
) where rownum <= :"SYS_BVx"


and the translate would no longer MATCH the translate in the fbi and hence the fbi would NEVER be considered.


Reason 43214 why cursor sharing is a weak crutch at best.

Really ?

Matt, April 29, 2004 - 8:59 am UTC

"Reason 43214 why cursor sharing is a weak crutch at best. "

Can you list the other 43213 please ? ;-D

Tom Kyte
April 29, 2004 - 10:30 am UTC

there are so many, so so many reasons..... :) this is just one of the ones in the middle actually

curious

another reader, April 29, 2004 - 9:03 am UTC

reader, curious why cursor sharing=exact was on your radar for things to look at, or was it just a stab in the dark that happened to be successful.

Tom obviously understood why it mattered, but since you didn't seem to know why it mattered, why were you changing it with expectations that it would be any different?

How many other knobs did you tweak? :)

To "another reader"

A reader, April 30, 2004 - 11:42 am UTC

Well, What happens is that I already had a hard time with cursor_sharing last week, where Tom helped me. And I know that running with cursor_sharing turned on may create very strange, unexpected things. So, when cursor_sharing in on, that's one of the first culprits I suspect of.

We are running with cursor_sharing on for some time, until we put all the bind variables in there.

It must be TEMPORARY, a remedy ... as Tom says. At first, I thought he was going a bit overboard on that. But now I admit he is darn right.



Merge Join Cartesian VS Nested Loop

Purushotham, May 04, 2004 - 7:56 am UTC

Hi Tom,

Thanks for maintaining knowledge hub like this.
Recently my team was involved in analyzing the performance of couple of queries we were developing. The initial query operated on entire dataset and has a cartesian join (We have a sample of 50K records).
We were asked to remove the cartesian join. So one suggestion proposed is to do semi-join between the tables, another suggestion was to restrict the rows and let the Merge Join cartesian happen.

Even semi-join restricts the rows but it's not accurate.

The semi-join results in nested-loop and faired marginally better than the restricted form (involves merge join cartesian). Below I pasted the execution plan of both approach.

All I want to know is what do you choose when it comes between "merge join cartersian" and "Nested Loops"

Filter Plan
==========


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 10.11 11.12 69 4452 0 12
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 10.11 11.12 69 4452 0 12

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 313

Rows Row Source Operation
------- ---------------------------------------------------
12 SORT ORDER BY (cr=4452 r=69 w=0 time=11122747 us)
12 HASH JOIN OUTER (cr=4452 r=69 w=0 time=11120324 us)
12 TABLE ACCESS FULL PERIOD_OFFSETS (cr=3 r=2 w=0 time=68072 us)
12 VIEW (cr=4449 r=67 w=0 time=11000230 us)
12 SORT GROUP BY (cr=4449 r=67 w=0 time=11000148 us)
29556 MERGE JOIN CARTESIAN (cr=4449 r=67 w=0 time=1641661 us)
2463 TABLE ACCESS BY INDEX ROWID CHANGE_ORDER_MV (cr=4446 r=67 w=0 time=1391541 us)
4562 BITMAP CONVERSION TO ROWIDS (cr=22 r=22 w=0 time=275114 us)
1521 BITMAP INDEX RANGE SCAN CHANGE_ORDER_MV_N1 (cr=22 r=22 w=0 time=233360 us)(object id 7527371)
29556 BUFFER SORT (cr=3 r=0 w=0 time=185182 us)
12 TABLE ACCESS FULL PERIOD_OFFSETS (cr=3 r=0 w=0 time=514 us)


Join Plan
=========


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.12 0.19 1 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 8.11 9.23 68 53337 0 12
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 8.23 9.43 69 53339 0 12

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 313

Rows Row Source Operation
------- ---------------------------------------------------
12 SORT ORDER BY (cr=53337 r=68 w=0 time=9231803 us)
12 HASH JOIN OUTER (cr=53337 r=68 w=0 time=9229831 us)
12 TABLE ACCESS FULL PERIOD_OFFSETS (cr=3 r=1 w=0 time=20518 us)
12 VIEW (cr=53334 r=67 w=0 time=9187569 us)
12 SORT GROUP BY (cr=53334 r=67 w=0 time=9187507 us)
7456 TABLE ACCESS BY INDEX ROWID CHANGE_ORDER_MV (cr=53334 r=67 w=0 time=5973150 us)
54757 NESTED LOOPS (cr=257 r=22 w=0 time=884372 us)
12 TABLE ACCESS FULL PERIOD_OFFSETS (cr=3 r=0 w=0 time=732 us)
54744 BITMAP CONVERSION TO ROWIDS (cr=254 r=22 w=0 time=664085 us)
18252 BITMAP INDEX RANGE SCAN CHANGE_ORDER_MV_N1 (cr=254 r=22 w=0 time=402676 us)(object id 7527371)


I believe the optimizer will perfer to do Merge join when the row count is less.

Thanks

Tom Kyte
May 04, 2004 - 8:03 am UTC

well, I would look at that and say that while the mjc used slightly more CPU, it was much nicer as far as scaling will go (less than 10% of the LIO's) and if the data we needed didn't happen to be in the buffer cache -- it would more likely be the better wall clock performer (less LIOs, perhaps less PIO's to satisfy them)


without seeing queries, it is very very hard to comment (and autotrace traceonly explain would be better as it shows the assumptions the optimizer was using -- the number of rows flowing from row sources).

Merge as compared to merge cartesian

Manjunath Kottur, March 17, 2005 - 1:36 pm UTC

Hi Tom,
One of the sql-statments in our application behaves very differently on our development compared to our external clients. I have used the same schema exported from our client for my test. The difference in performance is that, it takes about 8 minutes for the sql to execute where as it was not completed even after 6 days in our client's environment. We have the same init parameters, analyze using the same dbms_stats syntax, etc.
The statement is an insert statement in the form 'insert into T#LYDATASPECITEMCONVINS_SKEY
select ... from T#LYDATASPECITEMCONVERT_SKEY, LYDATASPECFILE, CTXDSP_TEMP where....'. Both the t# tables are GTTs. The problem has been identified as the 'select' part.

I could trace once on the clients machine and the tkprofs from client and our dev environment are as follows:

**********From our client (we had to kill it midway)***********

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.03 0 0 0 0
Execute 1 1657.26 1620.65 2140149 4998952 92 2041
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1657.30 1620.69 2140149 4998952 92 2041

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 80 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
2041 FILTER
2041 NESTED LOOPS
2041 NESTED LOOPS
2057 TABLE ACCESS FULL T#LYDATASPECITEMCONVERT_SKEY
2041 TABLE ACCESS FULL CTXDSP_TEMP
2041 TABLE ACCESS BY INDEX ROWID LYDATASPECFILE
2041 INDEX UNIQUE SCAN UKLYDATASPECFILE1 (object id 16000)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
latch free 2 0.00 0.00
buffer busy waits 1 0.00 0.00
db file sequential read 426 0.02 0.47
db file scattered read 214286 0.02 91.94


********From our environment(completed successfully)***********

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 13 0.00 0.08 9 56 0 0
Execute 26 18.51 62.14 42823 36040 5655 120612
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 39 18.51 62.23 42832 36096 5655 120612

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 94 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
9911 FILTER
9911 MERGE JOIN
9911 SORT JOIN
9911 MERGE JOIN
10000 SORT JOIN
10000 TABLE ACCESS BY INDEX ROWID T#LYDATASPECITEMCONVERT_SKEY
10000 INDEX RANGE SCAN IDX_T#LYDATASITMCONVERT_SKEY (object id 83345)
9911 SORT JOIN
120612 TABLE ACCESS FULL CTXDSP_TEMP
9911 SORT JOIN
1152 TABLE ACCESS FULL LYDATASPECFILE


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 847 0.45 5.07
db file scattered read 2135 0.26 13.33
direct path write 52 0.02 0.21
direct path read 171 0.05 0.46


As a fix, I tried to use different hints which could result in the plan similar to our dev.
The closest I came was the following :

*********from the client environment using the ***********
**/*+ USE_MERGE(T#LYDATASPECITEMCONVERT_SKEY,TMP,LD) */hint and set autotrace traceonly****
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=254 Card=1 Bytes=112
)

1 0 MERGE JOIN (Cost=254 Card=1 Bytes=112)
2 1 SORT (JOIN) (Cost=4 Card=1 Bytes=42)
3 2 MERGE JOIN (CARTESIAN) (Cost=2 Card=1 Bytes=42)
4 3 TABLE ACCESS (FULL) OF 'T#LYDATASPECITEMCONVERT_SKEY' (Cost=2 Card=1 Bytes=26)

5 3 BUFFER (SORT)
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'LYDATASPECFILE'
7 6 INDEX (RANGE SCAN) OF 'UKLYDATASPECFILE1' (UNIQUE)
8 1 SORT (JOIN) (Cost=251 Card=1173 Bytes=82110)
9 8 TABLE ACCESS (FULL) OF 'CTXDSP_TEMP' (Cost=234 Card=1173 Bytes=82110)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
1269 bytes sent via SQL*Net to client
211 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed

Though I am confident that the above plan will result in a successful completion, I am still hesitant as the index in the GTT, T#LYDATASPECITEMCONVERT_SKEY is not being used, and also the join order is different than the one in our development environment.

The number of records in tables are T#LYDATASPECITEMCONVERT_SKEY(121764 rows), LYDATASPECFILE(2,199), and CTXDSP_TEMP(243,528).

Can you please help in understanding the importance of the different behaviour(index usage and merge as compared to merge cartesian)?

Thanks
Manjunath



Tom Kyte
March 17, 2005 - 2:05 pm UTC

get explain plans and compare those.

Explain plans are not being very helpful

Manjunath, March 17, 2005 - 2:30 pm UTC

Tom,

Thanks, I had done the same thing but still could not understand why it shows up differently.
Here is the sql-statement (truncated for clarity).

SELECT /*+ USE_MERGE(T#LYDATASPECITEMCONVERT_SKEY,TMP,LD) */
ld.dataspecfilekey, TO_NUMBER(tmp.rownbr),
.....................................
..................................
FROM T#LYDATASPECITEMCONVERT_SKEY,
CTXDSP_TEMP tmp, LYDATASPECFILE ld
WHERE tmp.enterprisekey = 2
AND tmp.tempskey = T#LYDATASPECITEMCONVERT_SKEY.skey
AND ld.enterprisekey = tmp.enterprisekey
AND ld.name = tmp.name
AND T#LYDATASPECITEMCONVERT_SKEY."RowNum" BETWEEN 1 AND 10000
AND (tmp.rownbr IS NOT NULL AND LYUTILITY_PKG.F_ISNUMERIC(tmp.rownbr) = 1
AND tmp.rownbr NOT IN( '0000', '00000'))
AND (tmp.rowky NOT LIKE '$%'
AND tmp.rowky NOT LIKE '&'||'%')
/

Here are the hints and the explain plans:
Please help.

Thanks
Manjunath

Using the hint:/*+ USE_MERGE(T#LYDATASPECITEMCONVERT_SKEY,TMP,LD) */

*****Explain plan from development********

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=273 Card=10 Bytes=1120)

1 0 MERGE JOIN (Cost=273 Card=10 Bytes=1120)
2 1 SORT (JOIN) (Cost=264 Card=20 Bytes=1920)
3 2 MERGE JOIN (Cost=262 Card=20 Bytes=1920)
4 3 SORT (JOIN) (Cost=8 Card=20 Bytes=520)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'T#LYDATASPECITEMCONVERT_SKEY' (Cost=6 Card=20 Bytes=520)
6 5 INDEX (RANGE SCAN) OF 'IDX_T#LYDATASITMCONVERT_SKEY' (NON-UNIQUE) (Cost=2 Card=37)
7 3 SORT (JOIN) (Cost=255 Card=1173 Bytes=82110)
8 7 TABLE ACCESS (FULL) OF 'CTXDSP_TEMP' (Cost=239 Card=1173 Bytes=82110)
9 1 SORT (JOIN) (Cost=9 Card=1100 Bytes=17600)
10 9 TABLE ACCESS (FULL) OF 'LYDATASPECFILE' (Cost=4 Card=1100 Bytes=17600)

*****Explain plan from our client********

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=254 Card=1 Bytes=112)

1 0 MERGE JOIN (Cost=254 Card=1 Bytes=112)
2 1 SORT (JOIN) (Cost=4 Card=1 Bytes=42)
3 2 MERGE JOIN (CARTESIAN) (Cost=2 Card=1 Bytes=42)
4 3 TABLE ACCESS (FULL) OF 'T#LYDATASPECITEMCONVERT_SKEY' (Cost=2 Card=1 Bytes=26)
5 3 BUFFER (SORT)
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'LYDATASPECFILE'
7 6 INDEX (RANGE SCAN) OF 'UKLYDATASPECFILE1' (UNIQUE)
8 1 SORT (JOIN) (Cost=251 Card=1173 Bytes=82110)
9 8 TABLE ACCESS (FULL) OF 'CTXDSP_TEMP' (Cost=234 Card=1173 Bytes=82110)


Using the hint /*+ FIRST_ROWS(50000) */

*****Explain plan from development********

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=247 Card=1 Bytes=112)
1 0 NESTED LOOPS (Cost=247 Card=1 Bytes=112)
2 1 HASH JOIN (Cost=246 Card=1 Bytes=96)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T#LYDATASPECITEMCONVERT_SKEY' (Cost=6 Card=20 Bytes=520)
4 3 INDEX (RANGE SCAN) OF 'IDX_T#LYDATASITMCONVERT_SKEY'(NON-UNIQUE) (Cost=2 Card=37)
5 2 TABLE ACCESS (FULL) OF 'CTXDSP_TEMP' (Cost=239 Card=1173 Bytes=82110)
6 1 TABLE ACCESS (BY INDEX ROWID) OF 'LYDATASPECFILE' (Cost=1 Card=1 Bytes=16)
7 6 INDEX (UNIQUE SCAN) OF 'UKLYDATASPECFILE1' (UNIQUE)

*****Explain plan from our client********

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=237 Card=1 Bytes=112)
1 0 HASH JOIN (Cost=237 Card=1 Bytes=112)
2 1 MERGE JOIN (CARTESIAN) (Cost=2 Card=1 Bytes=42)
3 2 TABLE ACCESS (FULL) OF 'T#LYDATASPECITEMCONVERT_SKEY' (Cost=2 Card=1 Bytes=26)
4 2 BUFFER (SORT)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'LYDATASPECFILE'
6 5 INDEX (RANGE SCAN) OF 'UKLYDATASPECFILE1' (UNIQUE)
7 1 TABLE ACCESS (FULL) OF 'CTXDSP_TEMP' (Cost=234 Card=1173 Bytes=82110)



Tom Kyte
March 17, 2005 - 3:49 pm UTC

something is very different. (the explain plans were most helpful)

see the estimated cardinalities,


'T#LYDATASPECITEMCONVERT_SKEY', you think 20 rows, they think ONE row.

LYDATASPECFILE, you think 1100 rows, they think less.

sort of ctxdsp_temp, you think 1,173 rows -- they agree.


your stats are very different -- are their stats reasonable?

Comparative stats from our and client's environments

Manjunath, March 17, 2005 - 5:00 pm UTC

Tom,

Thanks. I looked at the stats.
T# tables are GTTS and so there will be nothing on them.

But for the other 2 tables here are the stats:
Please excuse me if this is not going to be formatted properly. On textpad it is perfectly formatted.

The one difference that I noticed was that the index stats on LYDATASPECFILE show 0 records and are comparatively older than table's.
Will this alone explain the difference?

Thanks a lot for your help.
Manjunath


Table:

LYDATASPECFILE

********on client's environment********

Table Number Empty Average Chain Average Global User Sample Date
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- ------------ -------- -------- ------- -------- ------- ------ ------ ------------ ----------
LYDATASPECFILE 2,199 24 0 0 0 61 YES NO 2,199 03-09-2005

Column Column Distinct Number Number Global User Sample Date
Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ----------- ------ ------ ------------ ----------
DATASPECFILEKEY NUMBER(10,0) NOT NULL 2,199 0 1 0 YES NO 2,199 03-09-2005
ENTERPRISEKEY NUMBER(10,0) 2 1 1 0 YES NO 2,199 03-09-2005
NAME CHAR(8) NOT NULL 1,152 0 1 0 YES NO 2,199 03-09-2005
DESCRIPTION VARCHAR2(40) 615 0 1 50 YES NO 2,149 03-09-2005
DETAILSORTORDER CHAR(2) 7 0 1 10 YES NO 2,189 03-09-2005
HASSPREADSHEET NUMBER(3,0) 1 1 1 0 YES NO 2,199 03-09-2005
SPREADSHEETTYPE NUMBER(3,0) 0 0 1 2,199 YES NO 03-09-2005
SPREADSHEETFILE CHAR(255) 0 0 1 2,199 YES NO 03-09-2005
SPREADSHEETSTARTCOL CHAR(4) 0 0 1 2,199 YES NO 03-09-2005
SPREADSHEETSTARTROW NUMBER(4,0) 0 0 1 2,199 YES NO 03-09-2005
SPREADSHEETCOLINC NUMBER(4,0) 0 0 1 2,199 YES NO 03-09-2005
LAST_MODIFIED_DATE DATE NOT NULL 10 0 1 0 YES NO 2,199 03-09-2005
USRLOCK VARCHAR2(60) 0 0 1 2,199 YES NO 03-09-2005
ISDELETED NUMBER(3,0) NOT NULL 1 1 1 0 YES NO 2,199 03-09-2005

B Average Average
Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date
Name Unique Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYYY
--------------- --------- ----- ------------ ------------ ------------ ----------- ----------- ------------ ------ ------ ------------ ----------
PKLYDATASPECFIL UNIQUE 0 0 0 0 0 0 0 NO NO 2,500 02-23-2005
E

UKLYDATASPECFIL UNIQUE 0 0 0 0 0 0 0 NO NO 2,500 02-23-2005
E1

**********our development environment**********

Table Number Empty Average Chain Average Global User Sample Date
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- ------------ -------- -------- ------- -------- ------- ------ ------ ------------ ----------
LYDATASPECFILE 2,199 23 0 0 0 61 YES NO 2,199 03-10-2005

Column Column Distinct Number Number Global User Sample Date
Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ----------- ------ ------ ------------ ----------
DATASPECFILEKEY NUMBER(10,0) NOT NULL 2,199 0 1 0 YES NO 2,199 03-10-2005
ENTERPRISEKEY NUMBER(10,0) 2 1 1 0 YES NO 2,199 03-10-2005
NAME CHAR(8) NOT NULL 1,152 0 1 0 YES NO 2,199 03-10-2005
DESCRIPTION VARCHAR2(40) 615 0 1 50 YES NO 2,149 03-10-2005
DETAILSORTORDER CHAR(2) 7 0 1 10 YES NO 2,189 03-10-2005
HASSPREADSHEET NUMBER(3,0) 1 1 1 0 YES NO 2,199 03-10-2005
SPREADSHEETTYPE NUMBER(3,0) 0 0 1 2,199 YES NO 03-10-2005
SPREADSHEETFILE CHAR(255) 0 0 1 2,199 YES NO 03-10-2005
SPREADSHEETSTARTCOL CHAR(4) 0 0 1 2,199 YES NO 03-10-2005
SPREADSHEETSTARTROW NUMBER(4,0) 0 0 1 2,199 YES NO 03-10-2005
SPREADSHEETCOLINC NUMBER(4,0) 0 0 1 2,199 YES NO 03-10-2005
LAST_MODIFIED_DATE DATE NOT NULL 10 0 1 0 YES NO 2,199 03-10-2005
USRLOCK VARCHAR2(60) 0 0 1 2,199 YES NO 03-10-2005
ISDELETED NUMBER(3,0) NOT NULL 1 1 1 0 YES NO 2,199 03-10-2005

B Average Average
Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date
Name Unique Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYYY
--------------- --------- ----- ------------ ------------ ------------ ----------- ----------- ------------ ------ ------ ------------ ----------
PKLYDATASPECFIL UNIQUE 1 5 2,199 2,199 1 1 22 YES NO 2,199 03-10-2005
E

UKLYDATASPECFIL UNIQUE 1 7 2,199 2,199 1 1 22 YES NO 2,199 03-10-2005
E1


Table CTXDSP_TEMP

********on client's environment********

Table Number Empty Average Chain Average Global User Sample Date
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- ------------ -------- -------- ------- -------- ------- ------ ------ ------------ ----------
CTXDSP_TEMP 243,528 2,414 0 0 0 70 YES NO 243,528 03-09-2005

Column Column Distinct Number Number Global User Sample Date
Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ----------- ------ ------ ------------ ----------
TEMPSKEY NUMBER(10,0) 243,528 0 1 0 YES NO 243,528 03-09-2005
ENTERPRISEKEY NUMBER(10,0) 2 1 1 0 YES NO 243,528 03-09-2005
NAME CHAR(8) 1,152 0 1 0 YES NO 243,528 03-09-2005
ROWNBR CHAR(5) 7,310 0 1 0 YES NO 243,528 03-09-2005
DSPTYP CHAR(1) 2 1 1 0 YES NO 243,528 03-09-2005
ROWKY CHAR(18) 7,828 0 1 0 YES NO 243,528 03-09-2005
DES VARCHAR2(40) 615 0 1 241,276 YES NO 2,252 03-09-2005
USRLOCK CHAR(3) 0 0 1 243,528 YES NO 03-09-2005
HLSSCOL CHAR(4) 0 0 1 243,528 YES NO 03-09-2005
HLSSCOLINC NUMBER(4,0) 0 0 1 243,528 YES NO 03-09-2005
HLSSTYP CHAR(1) 0 0 1 243,528 YES NO 03-09-2005
HLSSROW NUMBER(4,0) 0 0 1 243,528 YES NO 03-09-2005
YRFLG CHAR(1) 4 0 1 74,212 YES NO 169,316 03-09-2005
MLN CHAR(2) 18 0 1 77,742 YES NO 165,786 03-09-2005
ACCTDETLVL CHAR(1) 5 0 1 69,158 YES NO 174,370 03-09-2005
ADJDETLVL CHAR(1) 4 0 1 69,158 YES NO 174,370 03-09-2005
TYPDETLVL CHAR(1) 3 0 1 68,998 YES NO 174,530 03-09-2005
CRYFWD CHAR(1) 1 1 1 225,380 YES NO 18,148 03-09-2005
DBCR CHAR(1) 2 1 1 74,016 YES NO 169,512 03-09-2005
DSFAC NUMBER(12,5) 17 0 1 26,956 YES NO 216,572 03-09-2005
INDACCT VARCHAR2(8) 39 0 1 225,254 YES NO 18,274 03-09-2005
INDFLG CHAR(1) 3 0 1 243,374 YES NO 154 03-09-2005
EVWI CHAR(1) 4 0 1 69,872 YES NO 173,656 03-09-2005
COLNBR NUMBER(5,0) 277 0 1 206,594 YES NO 36,934 03-09-2005
DTLSRTORD CHAR(1) 7 0 1 241,234 YES NO 2,294 03-09-2005
HLSSNAME VARCHAR2(8) 0 0 1 243,528 YES NO 03-09-2005
LOC VARCHAR2(5) 75 0 1 237,624 YES NO 5,904 03-09-2005

**********our development environment**********

Table Number Empty Average Chain Average Global User Sample Date
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- ------------ -------- -------- ------- -------- ------- ------ ------ ------------ ----------
CTXDSP_TEMP 243,528 2,466 0 0 0 70 YES NO 243,528 03-10-2005

Column Column Distinct Number Number Global User Sample Date
Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ----------- ------ ------ ------------ ----------
TEMPSKEY NUMBER(10,0) 243,528 0 1 0 YES NO 243,528 03-10-2005
ENTERPRISEKEY NUMBER(10,0) 2 1 1 0 YES NO 243,528 03-10-2005
NAME CHAR(8) 1,152 0 1 0 YES NO 243,528 03-10-2005
ROWNBR CHAR(5) 7,310 0 1 0 YES NO 243,528 03-10-2005
DSPTYP CHAR(1) 2 1 1 0 YES NO 243,528 03-10-2005
ROWKY CHAR(18) 7,828 0 1 0 YES NO 243,528 03-10-2005
DES VARCHAR2(40) 615 0 1 241,276 YES NO 2,252 03-10-2005
USRLOCK CHAR(3) 0 0 1 243,528 YES NO 03-10-2005
HLSSCOL CHAR(4) 0 0 1 243,528 YES NO 03-10-2005
HLSSCOLINC NUMBER(4,0) 0 0 1 243,528 YES NO 03-10-2005
HLSSTYP CHAR(1) 0 0 1 243,528 YES NO 03-10-2005
HLSSROW NUMBER(4,0) 0 0 1 243,528 YES NO 03-10-2005
YRFLG CHAR(1) 4 0 1 74,212 YES NO 169,316 03-10-2005
MLN CHAR(2) 18 0 1 77,742 YES NO 165,786 03-10-2005
ACCTDETLVL CHAR(1) 5 0 1 69,158 YES NO 174,370 03-10-2005
ADJDETLVL CHAR(1) 4 0 1 69,158 YES NO 174,370 03-10-2005
TYPDETLVL CHAR(1) 3 0 1 68,998 YES NO 174,530 03-10-2005
CRYFWD CHAR(1) 1 1 1 225,380 YES NO 18,148 03-10-2005
DBCR CHAR(1) 2 1 1 74,016 YES NO 169,512 03-10-2005
DSFAC NUMBER(12,5) 17 0 1 26,956 YES NO 216,572 03-10-2005
INDACCT VARCHAR2(8) 39 0 1 225,254 YES NO 18,274 03-10-2005
INDFLG CHAR(1) 3 0 1 243,374 YES NO 154 03-10-2005
EVWI CHAR(1) 4 0 1 69,872 YES NO 173,656 03-10-2005
COLNBR NUMBER(5,0) 277 0 1 206,594 YES NO 36,934 03-10-2005
DTLSRTORD CHAR(1) 7 0 1 241,234 YES NO 2,294 03-10-2005
HLSSNAME VARCHAR2(8) 0 0 1 243,528 YES NO 03-10-2005
LOC VARCHAR2(5) 75 0 1 237,624 YES NO 5,904 03-10-2005


Tom Kyte
March 17, 2005 - 6:00 pm UTC

dbms_stats.set_table_stats could be used to set them (and why are yours DIFFERENT from theirs?)


if they have stale stats, that could be an issue, yes.

Thanks, It was indeed the stats

A reader, March 17, 2005 - 8:36 pm UTC

Tom,

Yes, I could finally alter 'MY' stats to mimic the clients and establish that the problem is the faulty index stats.
Thanks for all your help.

Manjunath

Tom Kyte
March 18, 2005 - 6:51 am UTC

(see, those explain plans where really handy!)

Cartesian Merge Join in Explain Plan

Madhu Jeevan, June 13, 2005 - 7:28 pm UTC

SELECT STATEMENT
SORT GROUP BY
FILTER
PX COORDINATOR
PX SEND QC (RANDOM) SYS.:TQ10001
MAT_VIEW REWRITE ACCESS BY LOCAL INDEX ROWID DMADM.FACT_DIV_CUST_BY_DT
NESTED LOOPS
BUFFER SORT
PX RECEIVE
PX SEND BROADCAST SYS.:TQ10000
MERGE JOIN CARTESIAN
HASH JOIN
TABLE ACCESS BY INDEX ROWID DMADM.DM_TM
INDEX RANGE SCAN DMADM.UNQ_DIM_TM
MERGE JOIN CARTESIAN
INDEX FULL SCAN DMADM.XAK1DM_CLNDR_MTH
BUFFER SORT
INDEX FAST FULL SCAN DMADM.XAK1DM_FISC_WK
BUFFER SORT
TABLE ACCESS FULL DMADM.DM_DIV
PX PARTITION RANGE ALL
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP INDEX SINGLE VALUE DMADM.XBMDCDMV_TM_SK
BITMAP INDEX SINGLE VALUE DMADM.XBMDCDMV_DIV_SK
----------------------------------------------------------

The joins are defined as
DM_CLNDR_MTH-->DM_TM-->FACT_SLS_DTL-->DM_SLS_GEO-->DM_DIV
OR DM_FISC_WK-->DM_TM

DM_DIV and DM_FISC_WK and DM_CLNDR_MTH are having very less records compared FACT_SLS_DTL and DM_SLS_GEO

Why the oracle is joining DM_DIV to DM_FISC_WK or DM_CLNDR_MTH?

Thanks
Madhu

Tom Kyte
June 13, 2005 - 9:05 pm UTC

probably because it thinks there will be one or two rows only and so puts them together and then joins that result to the rest.

it is perfectly legitimate and many times "sensible"

VA, June 29, 2005 - 5:57 pm UTC

Please see the following plan

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3015 | 72360 | | 364 |
| 1 | VIEW | | 3015 | 72360 | | 364 |
| 2 | SORT UNIQUE | | 3015 | 353K| 776K| 364 |
| 3 | NESTED LOOPS OUTER | | 3015 | 353K| | 306 |
| 4 | NESTED LOOPS | | 3015 | 317K| | 305 |
|* 5 | HASH JOIN | | 65M| 4079M| | 66 |
| 6 | INLIST ITERATOR | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | SCT_BURST | 22616 | 640K| | 27 |
| 8 | BITMAP CONVERSION TO ROWIDS | | | | | |
|* 9 | BITMAP INDEX SINGLE VALUE | SCT_BURST#I5 | | | | |
| 10 | MERGE JOIN CARTESIAN | | 43646 | 1534K| | 2 |
|* 11 | INDEX RANGE SCAN | IX1_UC_PK | 2 | 48 | | 1 |
| 12 | BUFFER SORT | | 21823 | 255K| | 1 |
| 13 | INDEX FULL SCAN | LGL_ENTITY#PK | 21823 | 255K| | 5 |
| 14 | TABLE ACCESS BY INDEX ROWID | ACCNT | 1 | 43 | | 305 |
| 15 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 16 | BITMAP AND | | | | | |
| 17 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 18 | INDEX RANGE SCAN | ACCNT#ACCNT_NO_UK | 1 | | | |
| 19 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 20 | INDEX RANGE SCAN | ACCNT#LGL_ENTITY_ID | 1 | | | |
|* 21 | INDEX UNIQUE SCAN | ACCNT_FEE#ACCNT_FEE_ID_PK | 1 | 12 | | |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("UCO"."OFFICE_LOC"="T"."EMPLOYEE_LOCATION_CODE")
9 - access("T"."ROLE"='BDO' OR "T"."ROLE"='Senior CAM')
11 - access("UCO"."USER_CLASS"='New York Office Head')
18 - access("T"."SUBACCOUNT_NO"="A"."ACCNT_NO")
20 - access("A"."LGL_ENTITY_ID"="L"."LGL_ENTITY_ID")
21 - access("A"."ACCNT_ID"="AF"."ACCNT_ID"(+))

Note: cpu costing is off

The query is

SELECT s.subaccount_no,s.subaccount_desc
FROM
sct_prod_sc_bdo sc,subaccount s,user_class_office uco,accnt sa
WHERE 1=1
AND s.subaccount_no=sa.accnt_no
AND uco.office_loc=sc.employee_location_code
AND sc.subaccount_no=s.subaccount_no
AND uco.user_class='New York Office Head'

All the cardinalities in the plan above are 100% accurate. Still, the merge join cartesian is killing the query. I let it run for 6 hours and it was still running, I had to kill it.

If I change the literal above to 'California Office Head' it comes back in a second. I tried gathering stats with histograms on that column, size 254, on all indexed columns, everything. But I just cant get the query with New York to run. [Yes, New York has much more data than other regions, so it is highly skewed thats why I thought histograms might help]

My question is, why is it hash joining the 43646 rows with the 22616 rows to get the insane number of 65 million rows?

Help? Thanks

Tom Kyte
June 29, 2005 - 8:24 pm UTC

question: why do you think it is the merge join cartesian?

(you do know the plan is very hard to read unless you make it fit a bit...)

A reader, June 29, 2005 - 8:23 pm UTC

optimizer_index_cost_adj=10 and index_caching=90 at the instance level

Tom Kyte
June 29, 2005 - 8:43 pm UTC

why, just to make it go really slow?

what is your theory here? it already appears "index happy"

Why merge join cartesian?

A reader, June 29, 2005 - 8:49 pm UTC

I think the merge join cartesian is the problem because when the plan does NOT contain it (when I specify anything other than New York in the predicate), it comes back in less than a second.

If I dont gather histograms, queries with all regions have the merge join cartesian and they all tank. Thats why I have gathered histograms size 254 for all indexed columns.

But is there a thing as too much information for the CBO?

Any other ideas?

Thanks

[How can I make the plan fit? I pasted it straight from sqlplus, it looks nice in my window, your textarea widget wraps lines after a certain width!]

Tom Kyte
June 29, 2005 - 9:04 pm UTC

you remove white space to make it fit. Yes, using the fixed font on this site, I make things wrap.... you have a preview of what it'll look like - i make them fit for you...

optimizer_index_cost_adj=10 and index_caching=90

A reader, June 29, 2005 - 8:52 pm UTC

Tom, as I said, these 2 parameters were set at the instance level after extensive testing involving all applications, proceses, systems using the database. They gave the best overall performance.

Do you think they are not appropriate for this query? Why? If thats the case, I am in trouble, I cant change init.ora parameters for just a query using a hint (that would be nice!). I cant do ALTER SESSIONs because who knows what queries might be issued in a session and I dont want to fix this query and make all others tank!

Thanks

Tom Kyte
June 29, 2005 - 9:06 pm UTC

see, here is the problem

I have no idea "who" you are.

or what the "context is"

or what you were talking about by putting them "there"

or whatever -- you understand this is a "big page" and you are just "a reader"

More readable plan

VA, June 29, 2005 - 9:12 pm UTC

---------------------------------------------------------------------------
Operation | Name | Rows |
-------------------------------------------------------------------------- -
SELECT STATEMENT | | 3015 |
VIEW | | 3015 |
SORT UNIQUE | | 3015 |
NESTED LOOPS OUTER | | 3015 |
NESTED LOOPS | | 3015 |
HASH JOIN | | 65M|
INLIST ITERATOR | | |
TABLE ACCESS BY INDEX ROWID | SCT_BURST | 22616 |
BITMAP CONVERSION TO ROWIDS | | |
BITMAP INDEX SINGLE VALUE | SCT_BURST#I5 | |
MERGE JOIN CARTESIAN | | 43646 |
INDEX RANGE SCAN | IX1_UC_PK | 2 |
BUFFER SORT | | 21823 |
INDEX FULL SCAN | LGL_ENTITY#PK | 21823 |
TABLE ACCESS BY INDEX ROWID | ACCNT | 1 |
BITMAP CONVERSION TO ROWIDS | | |
BITMAP AND | | |
BITMAP CONVERSION FROM ROWIDS| | |
INDEX RANGE SCAN | ACCNT#ACCNT_NO_UK | 1 |
BITMAP CONVERSION FROM ROWIDS| | |
INDEX RANGE SCAN | ACCNT#LGL_ENTITY_ID | 1 |
INDEX UNIQUE SCAN | ACCNT_FEE#ACCNT_FEE_ID_PK | 1 |
----------------------------------------------------------------------------

Ok I made the plan fit.

Any ideas?

Tom Kyte
June 30, 2005 - 9:16 am UTC

You say the cardinalities are correct right? so:

MERGE JOIN CARTESIAN | | 43646 |
INDEX RANGE SCAN | IX1_UC_PK | 2 |
BUFFER SORT | | 21823 |
INDEX FULL SCAN | LGL_ENTITY#PK | 21823 |

cannot really be the issue can it?

are those cardinalities accurate as you say they are?



CBO shortcomings

VA, June 29, 2005 - 10:54 pm UTC

I think I am running into some of the shortcomings of the CBO that Wolfgang Breitling talks about in

</code> http://www.centrexcc.com/Fallacies%20of%20the%20Cost%20Based%20Optimizer.pdf <code>

Let me explain how I arrived at that conclusion

FROM
sct_prod_sc_bdo sc,subaccount s,user_class_office uco,accnt sa
WHERE 1=1
AND s.subaccount_no=sa.accnt_no
AND uco.office_loc=sc.employee_location_code
AND sc.subaccount_no=s.subaccount_no
AND uco.user_class='New York Office Head'

The estimated cardinality from "sc" in the plan is 22616. This is the same as the estimated cardinality I get if I do a "select * from sc". In other words, the CBO thinks it is going to get all the rows from "sc". Not sure why.

If I add a /*+ cardinality(sc 2000) */ hint to the query, the merge join cartesian is gone and the query returns in less than a second!

Now...as I change the 2000 in the hint above to 3000, 4000.. the plan stays the same until 16000. When it hits 17000, the plan reverts back to the "bad" plan above.

Also, note the join between sc and uco above...

from sc,uco
where sc.col1=uco.col1
and uco.col2='foo'

For uco.col2='foo', there are 3 values of sc.col1 (a,b,c).

Instead of the cardinality hint, if I do

where sc.col1=uco.col1
and uco.col2='foo'
and sc.col1 in ('a','b','c')

again the query works fine, returns in less than a second!

Shouldnt the CBO know that for uc.col2='foo', there are 3 values of sc.col1 and come up with the good plan? Or does this have something to do with what Wolfgang refers to as the "predicate independence assumption" shortcoming of the CBO.

Comments?

I cant use the cardinality hint because this is a dynamic query generated by a reporting tool based on user inputs and there is no hook to put in hints

I cant use dbms_stats.set_* on "sc" because it is a view!

I cant add that sc.col1 predicate above for the same reason as above. The query is dynamically generated using user inputs (New York in this case) and the database is supposed to handle all the joins after that!

What are my options? Any ideas?

Thanks

Cardinalities

VA, June 30, 2005 - 10:01 am UTC

When I say those cardinalities are correct, I mean the index "lgl_entity#pk" has 21823 rows, so yes, the index full scan returning 21823 rows is accurate. My predicate on the "uco" table returns 2 rows so INDEX RANGE SCAN=2 is again accurate. So the merge join cartesian returning 21823x2 ~ 43646 is again accurate.

The problem is that it now hash joins these 43646 rows with the INLIST ITERATOR (22616 rows) and that step has the insanely high estimated cardinality of 65M.

The "good" plan, when I added the cardinality hint to the query is

---------------------------------------------------------------------------
Operation | Name | Rows |
---------------------------------------------------------------------------
SELECT STATEMENT | | 667 |
VIEW | | 667 |
SORT UNIQUE | | 667 |
NESTED LOOPS OUTER | | 667 |
NESTED LOOPS | | 667 |
NESTED LOOPS | | 667 |
NESTED LOOPS | | 667 |
INLIST ITERATOR | | |
TABLE ACCESS BY INDEX ROWID | SCT_BURST | 5000 |
BITMAP CONVERSION TO ROWIDS| | |
BITMAP INDEX SINGLE VALUE | SCT_BURST#I5 | |
INDEX UNIQUE SCAN | IX1_UC_PK | 1 |
TABLE ACCESS BY INDEX ROWID | ACCNT | 1 |
INDEX UNIQUE SCAN | ACCNT#ACCNT_NO_UK | 1 |
INDEX UNIQUE SCAN | LGL_ENTITY#PK | 1 |
INDEX UNIQUE SCAN | ACCNT_FEE#ACCNT_FEE_ID_PK | 1 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

10 - access("T"."ROLE"='BDO' OR "T"."ROLE"='Senior CAM')
11 - access("UCO"."USER_CLASS"='New York Office Head' AND
"UCO"."OFFICE_LOC"="T"."EMPLOYEE_LOCATION_CODE")
13 - access("T"."SUBACCOUNT_NO"="A"."ACCNT_NO")
14 - access("A"."LGL_ENTITY_ID"="L"."LGL_ENTITY_ID")
15 - access("A"."ACCNT_ID"="AF"."ACCNT_ID"(+))

Note: cpu costing is off

Maybe my interpretation of this is all wrong, but I tried to give my reasoning above

Comments? Thanks

Any comments?

VA, June 30, 2005 - 6:34 pm UTC

Any comments on my recent findings? Thanks

Tom Kyte
June 30, 2005 - 8:36 pm UTC

using dynamic sampling, we'd be able to get over some of the correlation issues - but I'm lead to believe that won't be possible given your constraints above.

Dynamic sampling

VA, July 01, 2005 - 1:05 pm UTC

Not sure I understand. Why do you think dynamic sampling would help here? How would that address the correlation or incorrect cardinality issue I am seeing? I mean, if static sampling with COMPUTE and histograms size 254 didnt help, what chance would dynamic sampling have?

Thanks

Tom Kyte
July 01, 2005 - 1:53 pm UTC

dynamic sampling can cross columns and understand correlations between them.

Dynamic sampling

A reader, July 01, 2005 - 3:19 pm UTC

"dynamic sampling can cross columns and understand correlations between them"

Where is this documented? Nothing at

</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/sql_1016.htm#33071 <code>

talks about how it can cross columns and understand correlations between them.

Tom Kyte
July 01, 2005 - 3:26 pm UTC

Estimate single-table predicate selectivities when collected statistics cannot be used or are likely to lead to significant errors in estimation.

... and to apply the relevant single table predicates to estimate predicate selectivities.....



Dynamic sampling

VA, July 01, 2005 - 10:19 pm UTC

OK how exactly can I use dynamic sampling in my situation to fix the correlation issue? Should I use the dynamic_sampling hint at the query level or specify a table and a level of sampling? Should I use the dynamic_sampling_est_cdn hint? Can you please elaborate? Thanks

Tom Kyte
July 02, 2005 - 9:14 am UTC

you might be able to have a complex predicate get a better cardinality using dynamic sampling.  here is a small example showing how it can affect the estimated cardinality:

ops$tkyte@ORA9IR2> create table t
  2  as
  3  select mod(rownum,5) x, mod(rownum,10) y, mod(rownum,100) z
  4    from all_objects;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for all columns size 254' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @plan "select * from t where x = 3"
ops$tkyte@ORA9IR2> delete from plan_table;
 
2 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for &1;
old   1: explain plan for &1
new   1: explain plan for select * from t where x = 3
 
Explained.
 
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  5568 | 44544 |    11 |
|*  1 |  TABLE ACCESS FULL   | T           |  5568 | 44544 |    11 |
--------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("T"."X"=3)
 
Note: cpu costing is off
 
14 rows selected.
 
<b>that is dead on, exact</b>


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @plan "select * from t where y = 7"
ops$tkyte@ORA9IR2> delete from plan_table;
 
2 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for &1;
old   1: explain plan for &1
new   1: explain plan for select * from t where y = 7
 
Explained.
 
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  2784 | 22272 |    11 |
|*  1 |  TABLE ACCESS FULL   | T           |  2784 | 22272 |    11 |
--------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("T"."Y"=7)
 
Note: cpu costing is off
 
14 rows selected.

<b>so was that</b>
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @plan "select * from t where x = 3 and y = 7"
ops$tkyte@ORA9IR2> delete from plan_table;
 
2 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for &1;
old   1: explain plan for &1
new   1: explain plan for select * from t where x = 3 and y = 7
 
Explained.
 
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |   557 |  4456 |    11 |
|*  1 |  TABLE ACCESS FULL   | T           |   557 |  4456 |    11 |
--------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("T"."X"=3 AND "T"."Y"=7)
 
Note: cpu costing is off
 
14 rows selected.
<b>but that is off by 557:</b>
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select count(*) from t where x = 3 and y = 7;
 
  COUNT(*)
----------
         0
 
ops$tkyte@ORA9IR2> alter session set optimizer_dynamic_sampling=4;
 
Session altered.
 
ops$tkyte@ORA9IR2> @plan "select * from t where x = 3 and y = 7"
ops$tkyte@ORA9IR2> delete from plan_table;
 
2 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for &1;
old   1: explain plan for &1
new   1: explain plan for select * from t where x = 3 and y = 7
 
Explained.
 
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |     8 |    11 |
|*  1 |  TABLE ACCESS FULL   | T           |     1 |     8 |    11 |
--------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("T"."X"=3 AND "T"."Y"=7)
 
Note: cpu costing is off
 
14 rows selected.
<b>Now it got it, and too see "how", we'll sql trace a hard parse:</b>

 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter session set sql_trace=true;
 
Session altered.
 
ops$tkyte@ORA9IR2> select * from t where x = 3 and y = 7;
 
no rows selected

<b>
and tkprof shows the query the optimizer ran:

SELECT /*+ ALL_ROWS IGNORE_WHERE_CLAUSE */ NVL(SUM(C1),0), NVL(SUM(C2),0)
FROM
 (SELECT /*+ IGNORE_WHERE_CLAUSE NOPARALLEL("T") */ 1 AS C1, CASE WHEN
  "T"."X"=3 AND "T"."Y"=7 THEN 1 ELSE 0 END AS C2 FROM "T" "T") SAMPLESUB

to figure out our cardinality</b>
 

Merge join cartesian incorrect cardinality estimate?

Charu Joshi, October 24, 2005 - 5:23 am UTC

Tom,

First, a suggestion if you don't mind. Since much of the interaction that happens on this site is through 'review comments', would it be better to include the Oracle version and OS version fields in the review comments dialogue as well. I often forget to mention this information. Thanks.

Now to the question.

Version: 9.2.0.6 EE
OS: Solaris 8 64-bit
Env: Data warehousing

Please have a look at the query and the execution plan below. This is from our production environment:

select *
from W_LOV_D T32507, W_DAY_D T33030
where T32507.TYPE = 'EVENT_STATUS'
and T33030.PER_NAME_YEAR = '2005'
and T32507.VAL in ('Completed', 'Failed', 'Rescheduled')

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=366)
1 0 MERGE JOIN (CARTESIAN) (Cost=12 Card=1 Bytes=366)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'W_LOV_D' (Cost=3 Card=1 Bytes=65)
3 2 INDEX (RANGE SCAN) OF 'W_LOV_D_M1' (NON-UNIQUE) (Cost=2 Card=23)
4 1 BUFFER (SORT) (Cost=9 Card=352 Bytes=105952)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'W_DAY_D' (Cost=10 Card=352 Bytes=105952)
6 5 INDEX (RANGE SCAN) OF 'W_DAY_D_M20' (NON-UNIQUE) (Cost=2 Card=352)

As can be seen, the estimated cardinalities of the two 'child operations' is 1 and 352 respectively. So if the 'parent operation' is merge cartesian join you would expect it's estimated cardinality to be 1*352 = 352.

The relevant statistics for these objects are:

TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
W_DAY_D 11746 226
W_LOV_D 27180 132


TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY NUM_BUCKETS
---------- ---------------- ------------ ---------- -----------
W_DAY_D PER_NAME_YEAR 32 .000042568 31
W_LOV_D TYPE 2835 .000851064 185
W_LOV_D VAL 17704 .000056484 1

This query is actually just a part of a huge query. Because of this glaring error in cardinality estimation the entire execution plan has gone bad.

I tried to create a test case on the same environment:

create table tb1 as select * from dba_objects;

create table tb2 as select * from dba_users;

begin dbms_stats.gather_table_stats(ownname=>user, tabname=>'TB1',
method_opt=>'FOR ALL COLUMNS SIZE 1', cascade=>true);
end;

begin dbms_stats.gather_table_stats(ownname=>user, tabname=>'TB2',
method_opt=>'FOR ALL COLUMNS SIZE 1', cascade=>true);
end;

siebel@VOLANL>set autotrace traceonly

1 select user_id, username, object_name from tb1, tb2
2* where tb2.username = 'CJ'
siebel@VOLANL>/

no rows selected

Elapsed: 00:00:04.03

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=187 Card=28603 Bytes=972502)
1 0 MERGE JOIN (CARTESIAN) (Cost=187 Card=28603 Bytes=972502)
2 1 TABLE ACCESS (FULL) OF 'TB2' (Cost=4 Card=1 Bytes=10)
3 1 BUFFER (SORT) (Cost=183 Card=28603 Bytes=686472)
4 3 TABLE ACCESS (FULL) OF 'TB1' (Cost=184 Card=28603 Bytes=686472)

Here the estimated cardinality is as expected.

The question is, what might be going wrong in the earlier case?

The options in front of me seem to be:

1. Dynamic sampling
2. Stored outline
3. Statistics manipulation

Turning on dynamic sampling at the session level sorted out the cardinality estimation problem, but the overall execution plan chosen was still not good. Adding the /*+ dynamic_sampling(T32507 5) */ hint also sorted out the problem, but in our environment is very difficult to add hints.

Is there any way by which we can turn on sampling for a selected table without adding hint?

Is there any other easier solution to this problem?

Thanks & regards,
Charu Joshi



Tom Kyte
October 24, 2005 - 6:25 am UTC

does setting dynamic sampling to 3 or 4 at your session level have the same effect?

Incorrect cardinality estimate in Merge Cartesian Join

Charu Joshi, October 24, 2005 - 7:47 am UTC

Tom,

If I set dynamic sampling to anything less than 5, the plan doesn't change at all.

But why should it happen in the first place? Where do I need to look to correct this miscalculation?

Just out of curiosity, does any future version have the facility to turn dynamic sampling for a table like

ALTER TABLE t1 SET DYNAMIC_SAMPLING=5;

I think it would be most useful to be able to turn on the dynamic sampling only for all the 'dimension' tables.

Thanks & regards,
Charu


Tom Kyte
October 24, 2005 - 11:38 am UTC

If you want to find out if this is a bug - you'll need to persue that via support - I'm most concerned about finding you a viable way to get going.

Why card = 1

Jonathan Lewis, October 24, 2005 - 9:19 am UTC

A possible reason for the apparent contradiction is that "explain plan" reports figures as integers, but the CBO calculates to several decimal places.

Your computed cardinality on the first table may actually be more like 0.0002 - which gets reported as 1, which causes the apparent contradiction.

Re a "sample state" for the table. I haven't tried it, but this might have the necessary effect:
create view t1 as
select /*+ dynamic_sampling(t 5) */ * from t
;



Tom Kyte
October 24, 2005 - 11:39 am UTC

now that is a neat idea, the view.

David Aldridge http://oraclesponge.blogspot.com, October 24, 2005 - 2:04 pm UTC

From looking at this query:

select *
from W_LOV_D T32507, W_DAY_D T33030
where T32507.TYPE = 'EVENT_STATUS'
and T33030.PER_NAME_YEAR = '2005'
and T32507.VAL in ('Completed', 'Failed', 'Rescheduled')

... it seems to me that there may be a design issue here also.

I'm just guessing, but W_LOV_D looks like a generic list-of-values table, and T32507.TYPE = 'EVENT_STATUS' is specifying which logical list to use within the table.

Now if this is so then it's an excellent example of why these generic LoV tables are A Very Bad Idea. The CBO has to use dynamic sampling in order to get a good cardinality estimate for the query and without that the execution plan is poor. What the designers know is that within the range of rows for which TYPE = 'EVENT_STATUS' there are very few distinct values of VAL (certainly not thousands of them) but the optimizer can only determine that with dynamic sampling.

If you were using a dedicated list of values table for event status then your query would become:

select *
from W_LOV_ES_D T32507, W_DAY_D T33030
where T33030.PER_NAME_YEAR = '2005'
and T32507.VAL in ('Completed', 'Failed', 'Rescheduled')

If this isn't just speculative jibber-jabber then I'd urge Charu to just test the effect that such a design change would have on the execution plans.

A "hybrid solution" would be to list-partition W_LOV_D on TYPE, but licensing costs aside that would probably end up being awkward in other ways.

List partition

Jonathan Lewis, October 24, 2005 - 2:46 pm UTC

David,

You clearly won't need to read chapter 6 of my book. The list partition on the type column is the ideal (cash no problem) solution to the generic lookup table. But an alternative that can often get close is to create a frequency distribution histogram on the type column and then make sure you always use a literal (i.e. never use a bind) for predicates on the type column. Limited, of course, to cases where there are less than 255 values for the type.


Another solution for problematic MJC

Rob, October 25, 2005 - 2:49 pm UTC

In 9.2.0.6 there is an event to disable Merge Join Cartesian. In init.ora you can set
EVENT="38043 trace name context forever, level 1"

Feedback about JPL sir and David's suggestions

Charu Joshi, October 26, 2005 - 6:17 am UTC

Jonathan,

<Quote>
Your computed cardinality on the first table may actually be more like 0.0002 -
which gets reported as 1, which causes the apparent contradiction.
</Quote>

You are absolutely right there. See the excerpts from the 10053 trace.


SINGLE TABLE ACCESS PATH
Column: TYPE Col#: 6 Table: W_LOV_D Alias: T32507
NDV: 2835 NULLS: 0 DENS: 8.5106e-04
HEIGHT BALANCED HISTOGRAM: #BKT: 254 #VAL: 186
Column: VAL Col#: 7 Table: W_LOV_D Alias: T32507
NDV: 17704 NULLS: 0 DENS: 5.6484e-05
NO HISTOGRAM: #BKT: 1 #VAL: 2
TABLE: W_LOV_D ORIG CDN: 27180 ROUNDED CDN: 1 CMPTD CDN: 0

And again:

Join cardinality: 1 = outer (0) * inner (352) * sel (1.0000e+00) [flag=0]
Join result: cost: 12 cdn: 1 rcz: 71


Final - All Rows Plan:
JOIN ORDER: 1
CST: 12 CDN: 1 RSC: 11 RSP: 11 BYTES: 71
IO-RSC: 10 IO-RSP: 10 CPU-RSC: 571551 CPU-RSP: 571551

QUERY
explain plan set statement_id='ADMINISTRATOR:102605064400' into PLAN_TABLE For select T32507.NAME,
T33030.MONTH_NAME as c8,
T33030.PER_NAME_MONTH as c9
from
W_LOV_D T32507,
W_DAY_D T33030
where T32507.TYPE = 'EVENT_STATUS' and T33030.PER_NAME_YEAR = '2005'
and (T32507.VAL in ('Completed', 'Failed', 'Rescheduled'))


List partitioning had the desired effect and the cardinality estimate was accurate using that.


However, creating the view didn't seem to have the desired effect. Please see below:

First the original query with dynamic_sampling hint:

siebel@VOLANL>ed
Wrote file afiedt.buf

1 select /*+ dynamic_sampling(T32507 5) */ *
2 from W_LOV_D T32507, W_DAY_D T33030
3 where T32507.TYPE = 'EVENT_STATUS'
4 and T33030.PER_NAME_YEAR = '2005'
5* and T32507.VAL in ('Completed', 'Failed', 'Rescheduled')
siebel@VOLANL>/

1116 rows selected.

Elapsed: 00:00:13.02

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=30 Card=1056 Bytes=386496)
1 0 MERGE JOIN (CARTESIAN) (Cost=30 Card=1056 Bytes=386496)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'W_LOV_D' (Cost=3 Card=3 Bytes=195)
3 2 INDEX (RANGE SCAN) OF 'W_LOV_D_M1' (NON-UNIQUE) (Cost=2 Card=23)
4 1 BUFFER (SORT) (Cost=27 Card=352 Bytes=105952)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'W_DAY_D' (Cost=10 Card=352 Bytes=105952)
6 5 INDEX (RANGE SCAN) OF 'W_DAY_D_M20' (NON-UNIQUE) (Cost=2 Card=352)

The cardinality estimate is accurate here. Now to create the view:

siebel@VOLANL>create view w_lov_d1 as select /*+ dynamic_sampling(w_lov_d 5) */* from w_lov_d

View created.

And test the query:

siebel@VOLANL>ed
Wrote file afiedt.buf

1 select *
2 from W_LOV_D1 T32507, W_DAY_D T33030
3 where T32507.TYPE = 'EVENT_STATUS'
4 and T33030.PER_NAME_YEAR = '2005'
5* and T32507.VAL in ('Completed', 'Failed', 'Rescheduled')
siebel@VOLANL>/

1116 rows selected.

Elapsed: 00:00:21.07

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=366)
1 0 MERGE JOIN (CARTESIAN) (Cost=12 Card=1 Bytes=366)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'W_LOV_D' (Cost=3 Card=1 Bytes=65)
3 2 INDEX (RANGE SCAN) OF 'W_LOV_D_M1' (NON-UNIQUE) (Cost=2 Card=23)
4 1 BUFFER (SORT) (Cost=9 Card=352 Bytes=105952)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'W_DAY_D' (Cost=10 Card=352 Bytes=105952)
6 5 INDEX (RANGE SCAN) OF 'W_DAY_D_M20' (NON-UNIQUE) (Cost=2 Card=352)

Again the cardinality estimate is inaccurate. Not sure what's gone wrong here.

David,

I agree that the design suggested by you is better than the current one. The schema design is part of Siebel Analytics 7.5.2 and provided by Siebel. The more we customize their design, the harder it is to upgrade to the new version. List partitioning option would be easier and probably just as effective.

BTW, I am quite enamoured by the idea of turning dynamic sampling on for all the smallish dimension tables only. The logic is that:
A. Since most of the dimension tables are quite small, their sampling shouldn't take much time.
B. Since dimension tables are at the starting points of almost all execution plans, a little accuracy in cardinality estimate there would help in a big way in choosing the right access path/join mechanism for the huge fact tables.

Does anybody see any obvious downside to that?

Thanks to Jonathan, Tom and David.

Charu.


Tom Kyte
October 26, 2005 - 12:19 pm UTC

#b

pound-sign B is the culprit of the bold stuff here. I use it as a flag to turn on bold and pound-sign b as the end of bold (of which I've added one...)


for datawarehousing type queries, dynamic sample (impacts the parse and shouldn't be affected to much by the size of the table as the sample is small unless you go way up there with 6, 7, 8... ) can be quite good as you are worried about queries taht take minutes or longer to run in some cases - the parse time is relativly speaking "small".

Have I been too bold?? :-)

A reader, October 26, 2005 - 6:21 am UTC

Don't know how, but much of my earlier post has unnecessarily and unintentionally got written in bold letters.

Tom,

Is there anyway to change it all to normal, so that it will be less offensive to the eyes.

Charu.

Tom Kyte
October 27, 2005 - 2:36 pm UTC


David Aldridge http://oraclesponge.blogspot.com, October 26, 2005 - 4:21 pm UTC

For small DW dimension tables I would think that gathering histograms of size 254 would mostly be adequate. However what this usually won't tell you is the cardinality of the result set from the fact table -- just because you have selected the dates between 01-jan-2005 and 07-jan-2005, giving seven dimension table rows, how does that affect the number of rows from the fact table?

Dynamic sampling really comes into its own when you are applying predicates to more than one column of a table because that's where column statistics and even histograms can mislead the optimizer.

To take a simple example, if you have a fact table of retail and wholesale sales then you're probably going to see very different distributions of the number of sales based on the date of day. Retail sales might peak on Saturdays, wholesale sales might peak on Fridays or Mondays, and histograms on a retail/wholesale code and on the date are not going to reveal this.

Dynamic sampling levels are interesting in this respect -- if you accept that in a data warehouse you always collect statistics, and preferably collect histograms, then at Level 4 you are sampling on tables for which there are two or more predicates and I don't think this is arbitrary, it's an indication that the designers of this functionality recognise that two-or-more-predicates are ill-served by histograms.

So what this means to me is a couple of things (and some of this is currently-untested speculation, though hopefully of the informed variety):

i) that dynamic sampling is actually of interest more on fact tables than on dimension tables, because it is those which have the problem of difficult cardinality estimation due to having two-or-more predicates placed on them (directly or through joins to dimension tables)
ii) possibly, that in order to leverage sampling we ought to try to place dimension predicates directly on the fact table instead of the dimension table, and this may mean introducing redundant columns in the fact table (eg. month on a day-level table)
iii) since partitioning helps us to get a form of multi-value histogram (eg. a histogram for column A for a given value or range of column B where column B is the partitioning key) then for queries for which single partition pruning occurs the standard statistics are more useful and the relative benefits of dynamic sampling proportionally reduced.
iv) Because of the lack of multi-column histograms for anything other than single partitions, dynamic sampling may be preferable to the use of global statistics. Use of dynamic sampling would be promoted by gathering only partition/subpartition statistics and avoiding global statistics completely.



Now here's an oddity.

Using the following script:

drop table t
/

create table t
as
select * from dba_objects
/

create materialized view log on t
with rowid (owner,object_type)
including new values
/

create materialized view t_mv
build immediate
refresh fast on commit
enable query rewrite
as
select owner,object_type,count(*),sum(1)
from t
group by owner,object_type
/

begin
dbms_stats.gather_table_stats(
ownname=> user,
tabname => 'T',
method_opt => 'for all columns size 254'
);
dbms_stats.gather_table_stats(
ownname=> user,
tabname => 'T_MV',
method_opt => 'for all columns size 254'
);
end;
/

alter session set events '10046 trace name context forever, level 8'
/

select count(*),max(created) from t
where owner = 'SYS' and object_type = 'TABLE'
/

select /*+ dynamic_sampling(t 10) */ count(*),max(created) from t
where owner = 'SYS' and object_type = 'TABLE'
/

This shows that the level 10 dynamic sampling query is:

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) */
NVL(SUM(C1),0),
NVL(SUM(C2),0)
FROM (SELECT /*+ NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */
1 AS C1,
1 AS C2
FROM "T" "T"
WHERE "T"."OWNER"='SYS' AND
"T"."OBJECT_TYPE"='TABLE') SAMPLESUB

By including the expression SUM(1) in the MV definition I can show that this query will rewrite against the MV when executed through SQL*Plus, but as far as I can tell it won't rewrite as recursive SQL. Shame. I would speculate that the code path for MV rewrite is not "available" at that point, and any decision on allowing rewrite is dependent upon the result of the dynamic sampling query. That appears to be the case when submitting a query rewrite-eligible statement such as:

select /*+ dynamic_sampling(t 10) */ count(*) from t
where owner = 'SYS' and object_type = 'TABLE'
/

Although the query is rewritten against T_MV, the execution plan in the trace file for the dynamic sampling query shows a full scan of T not T_MV -- in fact the dynamic sampling query was considerably slower than the rewritten user query in this case.

I suppose I now have to go and delete the blog entry I was preparing on this subject. Tut.

Charu Joshi, October 27, 2005 - 8:42 am UTC

David,

<Quote>
Dynamic sampling really comes into its own when you are applying predicates to
more than one column of a table because that's where column statistics and even
histograms can mislead the optimizer.
</Quote>

Absolutely correct. The problem I was facing (mentioned earlier in the thread) is a classic example of this.

<Quote>
just because you have
selected the dates between 01-jan-2005 and 07-jan-2005, giving seven dimension
table rows, how does that affect the number of rows from the fact table?
</Quote>

I see your point there. Still I believe it can be of help in (often) choosing the right 'leading' dimension table out of multiple tables in queries where star transformation is not applied. In our case, in most queries the filter condition through one dimension filters out far more rows from the fact table than those on other dimensions. And since the stats on FK columns of the fact table are reasonably accurate, getting the most selective dimension table as leading table results in reasonably efficient execution plan.

<Quote>
ii) possibly, that in order to leverage sampling we ought to try to place dimension predicates directly on the fact table instead of the dimension table, and this may mean ntroducing redundant columns in the fact table (eg. month on
a day-level table)
</Quote>

Agree. Initially I naively used to think of this as a drawback of the dimension-fact style design with Oracle database.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:40116109090800#40488470662673 <code>

I now think that it is more due the limitation of the statistics that can be collected in Oracle. Credit to Tom, at that time also he had suggested dynamic sampling, the benefit of which is now dawning on me.

Agree on points iii) and iv) as well.

Regarding the test case you have provided:

My DB is 9.2.0.6 EE and the query doesn't get rewritten in either case (with or without dynamic sampling) even with query_rewrite_enabled = force and query_rewrite_integrity=stale_tolerated. Not sure how the can be rewritten when it contains the column 'created' that is not in the MV.

Overall, quite useful post. Thanks very much David.

Regards,
Charu



David Aldridge http://oraclesponge.blogspot.com, October 27, 2005 - 3:16 pm UTC

>> My DB is 9.2.0.6 EE and the query doesn't get rewritten in either case (with or without dynamic sampling) even with query_rewrite_enabled = force and query_rewrite_integrity=stale_tolerated. Not sure how the can be rewritten when it contains the column 'created' that is not in the MV. <<

Ah, let me clarify what I was doing there, it's probably a bit obscure.

I wanted to find out whether it was possible to use an MV to promote faster response on the dynamic sampling query, independently of whether the user query got rewritten or not. Since the dynamic sampling query is just intended to return an estimate (at level < 10) for the number of rows to be returned then theoreticaly this could be answered by a suitably constructed MV.

I actually selected from the "created" column specifically to prevent query rewrite on the user query. However that wouldn't prevent the (theoretical) use of query rewrite for the dynamic sampling query, which would just use the owner and object_type columns and hence could be rewritten against T_MV.

A vaguely related point is that in many cases the dynamic sampling query ought (again theoretically) to be able to use a set of bitmap indexes to get the row estimate without touching the table segment at all, which would be very fast of course, but the dynamic sampling queries that i've seen are hinted to use a full scan for some reason. They also use block sampling, which implies to me that they're not going to use a bitmap index scan anyway, even though it is potentially faster than a table scan. (That's why I was using level 10, by the way, as it doesn't use sampling at all). Maybe I just haven't seen it in the right situation, or maybe the optimizer hints in the dynamic sampling query are there to reduce its parse time as much as possible, which would be a valid concern with an OLTP query but not such a concern with a DW-type query.

So long story short, as far as I can see neither the presence of indexes or MV's can speed up the dynamic sampling query. I'd be very happy to be proven wrong on that though.

To David

Charu Joshi, October 28, 2005 - 2:01 am UTC

Oh I get you now. Although I can't offer anything helpful on this, I appreciate the work you are doing. You blog is a useful read too for D/W related issues.

Thanks,
Charu.

Malinga, September 08, 2006 - 3:08 am UTC

Sir,
select o.individual_id,
sum(case when (o.transaction_dt between p.p12_roll_mth_start_dt and p.p12_roll_mth_end_dt) then
1
else
0
end) P12_Visits
from mrtrollup.visit t,
mrtlookup.period_dates p,
mrtcustomer.individual i,
mrttransaction.order_basket o
where o.transaction_dt between p.p12_roll_mth_start_dt and p.c12_roll_mth_end_dt
and t.individual_id=i.individual_id
and i.individual_id=o.individual_id
group by o.individual_id



Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=917746 Card=6970319 Bytes=404278502)

1 0 SORT (GROUP BY NOSORT) (Cost=917746 Card=6970319 Bytes=404278502)

2 1 MERGE JOIN (Cost=917746 Card=85846390 Bytes=4979090620)
3 2 MERGE JOIN (Cost=338488 Card=10242063 Bytes=501861087)
4 3 SORT (JOIN) (Cost=270411 Card=10242063 Bytes=409682520)

5 4 NESTED LOOPS (Cost=165062 Card=10242063 Bytes=409682520)

6 5 TABLE ACCESS (FULL) OF 'PERIOD_DATES' (TABLE) (Cost=9 Card=1 Bytes=24)

7 5 PARTITION RANGE (ITERATOR) (Cost=165053 Card=10242063 Bytes=163873008)

8 7 INDEX (FAST FULL SCAN) OF 'BTC1_IND_ID__TEN_TY_TRA_DT_OB' (INDEX) (Cost=165053 Card=10242063

Bytes=163873008)

9 3 SORT (JOIN) (Cost=68077 Card=15740587 Bytes=141665283)

10 9 PARTITION RANGE (ALL) (Cost=7274 Card=15740587 Bytes=141665283)

11 10 INDEX (FAST FULL SCAN) OF 'PK_IND' (INDEX (UNIQUE)) (Cost=7274 Card=15740587 Bytes=141665283)

12 2 SORT (JOIN) (Cost=579258 Card=131933631 Bytes=1187402679)

13 12 PARTITION RANGE (ALL) (Cost=68235 Card=131933631 Bytes=1187402679)

14 13 INDEX (FAST FULL SCAN) OF 'FK1_IND_ID_VIS' (INDEX)(Cost=68235 Card=131933631 Bytes=1187402679)

Here in my case why Merge Join is used?

Also the BTC1_IND_ID__TEN_TY_TRA_DT_OB index is on INDIVIDUAL_ID, TENDER_TYPE and TRANSACTION_DT column. The TENDER_TYPE column is not selected then why this index is used? There is no composite index on INDIVIDUAL_ID and TRANSACTION_DT columns but there are seperate indexes on these columns.




Another workaround for problematic MJCs

Brandon Allen, October 09, 2006 - 2:42 pm UTC

Lots of good info in this thread, and I see how the MJC could be good in certain situations, but for me I think it has been more troublesome than beneficial. It has been the partial cause of a lot of headaches when used in conjunction with bind variable peeking, which occasionaly causes execution plans to be shared when they should not. So, I've decided to disable MJCs with the following hidden parameter I just found in 10.2.0.2:

_optimizer_mjc_enabled=false

Someone else mentioned earlier that MJCs could be disabled by setting an event code in 9.2 but it looks like they've added this hidden parameter to do the same in 10g.

Different explain plan in similar DBs

Raj, January 05, 2007 - 4:37 pm UTC

Hi Tom,
Can you please tell me why do I get a different explain plan when I run the same query in 2 similar DBs. I have 2 DBs PROD and QA (Refreshed from PROD just a week ago so not must of a difference between QA and PROD) I have all the same indexes in PROD and QA, but still I get the different explain plan
I have a query which runs in 1 min in QA and 10 mins in production. When I took the trace file I saw that the number of rows is too large in production. I don't think there is a much of the difference between data in PROD and QA, but still I don't understand why should this query take 10 mins in PROD and 1 min in QA.
Please find below the trace file for your reference
PROD
====
Rows Row Source Operation
------- ---------------------------------------------------
6 SORT UNIQUE
6 UNION-ALL
0 MINUS
120 SORT UNIQUE
720 FILTER
38880 NESTED LOOPS
15552000 MERGE JOIN CARTESIAN
129600 MERGE JOIN CARTESIAN
1080 TABLE ACCESS BY INDEX ROWID BILL_GROUP_BILL
1101 NESTED LOOPS
20 MERGE JOIN CARTESIAN
20 NESTED LOOPS
1 NESTED LOOPS
1 INDEX UNIQUE SCAN UICASE (object id 48973)
1 TABLE ACCESS BY INDEX ROWID BILL_GROUP
1 INDEX UNIQUE SCAN UIBILLGROUP (object id 48967)
20 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
20 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 48943)
20 BUFFER SORT
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 48974)
1 SORT AGGREGATE
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 48974)
1080 INDEX RANGE SCAN IBLLGRPBLL1 (object id 48535)
129600 BUFFER SORT
120 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
120 INDEX RANGE SCAN IPLANEMPGRPASSOCA1 (object id 48951)
15552000 BUFFER SORT
120 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
120 INDEX RANGE SCAN IPEGAEXPGRPASSOC (object id 48949)
38880 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
15552000 INDEX UNIQUE SCAN UIPEGAEXPGRPASSOC (object id 49019)
26016 SORT AGGREGATE
26016 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
78048 NESTED LOOPS
26016 NESTED LOOPS
26016 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
26016 INDEX RANGE SCAN UIPEGAEXPGRPASSOC (object id 49019)
26016 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
520320 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 48943)
26016 INDEX RANGE SCAN UIPLANEMPGRPASSOC (object id 49021)
1 SORT AGGREGATE
54 TABLE ACCESS BY INDEX ROWID BILL_GROUP_BILL
54 INDEX RANGE SCAN IBLLGRPBLL1 (object id 48535)
126 SORT UNIQUE
756 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
883 NESTED LOOPS
126 MERGE JOIN CARTESIAN
126 NESTED LOOPS
2646 MERGE JOIN CARTESIAN
21 NESTED LOOPS
1 NESTED LOOPS
1 INDEX UNIQUE SCAN UICASE (object id 48973)
1 TABLE ACCESS BY INDEX ROWID BILL_GROUP
1 INDEX UNIQUE SCAN UIBILLGROUP (object id 48967)
21 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
21 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 48943)
2646 BUFFER SORT
126 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
126 INDEX RANGE SCAN IPLANEMPGRPASSOCA1 (object id 48951)
126 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
2646 INDEX UNIQUE SCAN UIPEGAEXPGRPASSOC (object id 49019)
546 SORT AGGREGATE
546 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
1638 NESTED LOOPS
546 NESTED LOOPS
546 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
546 INDEX RANGE SCAN UIPEGAEXPGRPASSOC (object id 49019)
546 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
11466 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 48943)
546 INDEX RANGE SCAN UIPLANEMPGRPASSOC (object id 49021)
126 BUFFER SORT
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 48974)
1 SORT AGGREGATE
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 48974)
756 INDEX RANGE SCAN IPEGAEXPGRPASSOC (object id 48949)
6 MINUS
126 SORT UNIQUE
756 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
883 NESTED LOOPS
126 MERGE JOIN CARTESIAN
126 NESTED LOOPS
2646 MERGE JOIN CARTESIAN
21 NESTED LOOPS
1 NESTED LOOPS
1 INDEX UNIQUE SCAN UICASE (object id 48973)
1 TABLE ACCESS BY INDEX ROWID BILL_GROUP
1 INDEX UNIQUE SCAN UIBILLGROUP (object id 48967)
21 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
21 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 48943)
2646 BUFFER SORT
126 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
126 INDEX RANGE SCAN IPLANEMPGRPASSOCA1 (object id 48951)
126 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
2646 INDEX UNIQUE SCAN UIPEGAEXPGRPASSOC (object id 49019)
546 SORT AGGREGATE
546 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
1638 NESTED LOOPS
546 NESTED LOOPS
546 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
546 INDEX RANGE SCAN UIPEGAEXPGRPASSOC (object id 49019)
546 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
11466 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 48943)
546 INDEX RANGE SCAN UIPLANEMPGRPASSOC (object id 49021)
126 BUFFER SORT
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 48974)
1 SORT AGGREGATE
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 48974)
756 INDEX RANGE SCAN IPEGAEXPGRPASSOC (object id 48949)
120 SORT UNIQUE
720 FILTER
38880 NESTED LOOPS
15552000 MERGE JOIN CARTESIAN
129600 MERGE JOIN CARTESIAN
1080 TABLE ACCESS BY INDEX ROWID BILL_GROUP_BILL
1101 NESTED LOOPS
20 MERGE JOIN CARTESIAN
20 NESTED LOOPS
1 NESTED LOOPS
1 INDEX UNIQUE SCAN UICASE (object id 48973)
1 TABLE ACCESS BY INDEX ROWID BILL_GROUP
1 INDEX UNIQUE SCAN UIBILLGROUP (object id 48967)
20 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
20 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 48943)
20 BUFFER SORT
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 48974)
1 SORT AGGREGATE
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 48974)
1080 INDEX RANGE SCAN IBLLGRPBLL1 (object id 48535)
129600 BUFFER SORT
120 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
120 INDEX RANGE SCAN IPLANEMPGRPASSOCA1 (object id 48951)
15552000 BUFFER SORT
120 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
120 INDEX RANGE SCAN IPEGAEXPGRPASSOC (object id 48949)
38880 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
15552000 INDEX UNIQUE SCAN UIPEGAEXPGRPASSOC (object id 49019)
26016 SORT AGGREGATE
26016 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
78048 NESTED LOOPS
26016 NESTED LOOPS
26016 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
26016 INDEX RANGE SCAN UIPEGAEXPGRPASSOC (object id 49019)
26016 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
520320 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 48943)
26016 INDEX RANGE SCAN UIPLANEMPGRPASSOC (object id 49021)
1 SORT AGGREGATE
54 TABLE ACCESS BY INDEX ROWID BILL_GROUP_BILL
54 INDEX RANGE SCAN IBLLGRPBLL1 (object id 48535)

QA
==
Rows Row Source Operation
------- ---------------------------------------------------
126 SORT UNIQUE
126 UNION-ALL
0 MINUS
0 SORT UNIQUE
0 FILTER
0 NESTED LOOPS
0 MERGE JOIN CARTESIAN
0 MERGE JOIN CARTESIAN
0 TABLE ACCESS BY INDEX ROWID BILL_GROUP_BILL
21 NESTED LOOPS
20 MERGE JOIN CARTESIAN
20 NESTED LOOPS
1 NESTED LOOPS
1 INDEX UNIQUE SCAN UICASE (object id 57260)
1 TABLE ACCESS BY INDEX ROWID BILL_GROUP
1 INDEX UNIQUE SCAN UIBILLGROUP (object id 57253)
20 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
20 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 57214)
20 BUFFER SORT
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 57262)
1 SORT AGGREGATE
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 57262)
0 INDEX RANGE SCAN IBLLGRPBLL1 (object id 57250)
0 BUFFER SORT
0 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
0 INDEX RANGE SCAN IPLANEMPGRPASSOCA1 (object id 57223)
0 BUFFER SORT
0 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
0 INDEX RANGE SCAN IPEGAEXPGRPASSOC (object id 57221)
0 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
0 INDEX UNIQUE SCAN UIPEGAEXPGRPASSOC (object id 57318)
0 SORT AGGREGATE
0 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
0 INDEX RANGE SCAN UIPEGAEXPGRPASSOC (object id 57318)
0 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
0 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 57214)
0 INDEX RANGE SCAN UIPLANEMPGRPASSOC (object id 57320)
0 SORT AGGREGATE
0 TABLE ACCESS BY INDEX ROWID BILL_GROUP_BILL
0 INDEX RANGE SCAN IBLLGRPBLL1 (object id 57250)
126 SORT UNIQUE
756 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
883 NESTED LOOPS
126 MERGE JOIN CARTESIAN
126 NESTED LOOPS
2646 MERGE JOIN CARTESIAN
21 NESTED LOOPS
1 NESTED LOOPS
1 INDEX UNIQUE SCAN UICASE (object id 57260)
1 TABLE ACCESS BY INDEX ROWID BILL_GROUP
1 INDEX UNIQUE SCAN UIBILLGROUP (object id 57253)
21 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
21 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 57214)
2646 BUFFER SORT
126 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
126 INDEX RANGE SCAN IPLANEMPGRPASSOCA1 (object id 57223)
126 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
2646 INDEX UNIQUE SCAN UIPEGAEXPGRPASSOC (object id 57318)
546 SORT AGGREGATE
546 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
1638 NESTED LOOPS
546 NESTED LOOPS
546 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
546 INDEX RANGE SCAN UIPEGAEXPGRPASSOC (object id 57318)
546 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
11466 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 57214)
546 INDEX RANGE SCAN UIPLANEMPGRPASSOC (object id 57320)
126 BUFFER SORT
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 57262)
1 SORT AGGREGATE
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 57262)
756 INDEX RANGE SCAN IPEGAEXPGRPASSOC (object id 57221)
126 MINUS
126 SORT UNIQUE
756 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
883 NESTED LOOPS
126 MERGE JOIN CARTESIAN
126 NESTED LOOPS
2646 MERGE JOIN CARTESIAN
21 NESTED LOOPS
1 NESTED LOOPS
1 INDEX UNIQUE SCAN UICASE (object id 57260)
1 TABLE ACCESS BY INDEX ROWID BILL_GROUP
1 INDEX UNIQUE SCAN UIBILLGROUP (object id 57253)
21 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
21 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 57214)
2646 BUFFER SORT
126 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
126 INDEX RANGE SCAN IPLANEMPGRPASSOCA1 (object id 57223)
126 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
2646 INDEX UNIQUE SCAN UIPEGAEXPGRPASSOC (object id 57318)
546 SORT AGGREGATE
546 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
1638 NESTED LOOPS
546 NESTED LOOPS
546 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
546 INDEX RANGE SCAN UIPEGAEXPGRPASSOC (object id 57318)
546 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
11466 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 57214)
546 INDEX RANGE SCAN UIPLANEMPGRPASSOC (object id 57320)
126 BUFFER SORT
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 57262)
1 SORT AGGREGATE
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 57262)
756 INDEX RANGE SCAN IPEGAEXPGRPASSOC (object id 57221)
0 SORT UNIQUE
0 FILTER
0 NESTED LOOPS
0 MERGE JOIN CARTESIAN
0 MERGE JOIN CARTESIAN
0 TABLE ACCESS BY INDEX ROWID BILL_GROUP_BILL
21 NESTED LOOPS
20 MERGE JOIN CARTESIAN
20 NESTED LOOPS
1 NESTED LOOPS
1 INDEX UNIQUE SCAN UICASE (object id 57260)
1 TABLE ACCESS BY INDEX ROWID BILL_GROUP
1 INDEX UNIQUE SCAN UIBILLGROUP (object id 57253)
20 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
20 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 57214)
20 BUFFER SORT
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 57262)
1 SORT AGGREGATE
1 TABLE ACCESS BY INDEX ROWID CASE_ADMIN_PROVISION
1 INDEX RANGE SCAN UICASEADMINPROV (object id 57262)
0 INDEX RANGE SCAN IBLLGRPBLL1 (object id 57250)
0 BUFFER SORT
0 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
0 INDEX RANGE SCAN IPLANEMPGRPASSOCA1 (object id 57223)
0 BUFFER SORT
0 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
0 INDEX RANGE SCAN IPEGAEXPGRPASSOC (object id 57221)
0 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
0 INDEX UNIQUE SCAN UIPEGAEXPGRPASSOC (object id 57318)
0 SORT AGGREGATE
0 TABLE ACCESS BY INDEX ROWID PLAN_EMP_GROUP_ASSOC
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID PEGA_EXPRNCGRP_ASSOC
0 INDEX RANGE SCAN UIPEGAEXPGRPASSOC (object id 57318)
0 TABLE ACCESS BY INDEX ROWID EXPRNCGRP_BLLGRP_ASSOC
0 INDEX RANGE SCAN IEXPBLLGRPASSOC (object id 57214)
0 INDEX RANGE SCAN UIPLANEMPGRPASSOC (object id 57320)
0 SORT AGGREGATE
0 TABLE ACCESS BY INDEX ROWID BILL_GROUP_BILL
0 INDEX RANGE SCAN IBLLGRPBLL1 (object id 57250)

Please help me
Tom Kyte
January 06, 2007 - 10:54 am UTC

you have totally different data volumes, look at the estimated cardinalities.

totally different data.

Merge Join cartesian

ravi, January 11, 2007 - 12:21 pm UTC

Hi Tom,
I am trying to execute the following query.
select sub.ID_NUMBER,op.X_OR_APPT_DATE,sp.X_XMPF_ID,sub.CREATION_TIME
from
table_case c,
table_subcase sub,
table_site_part sp,
table_x_openreach op
where
sub.SUBCASE2CASE=c.OBJID
and op.X_OPENREACH2SUBCASE=sub.OBJID
and c.X_LLU_FLAG='Y'
and c.X_SFI_COUNT=1
and sub.CREATION_TIME >to_date('01/01/07 00:00:00', 'dd/mm/yy HH24:MI:SS')
and c.CASE_PROD2SITE_PART=sp.OBJID

when i remove table_x_openreach the query is executing resonably fast(Around 23sec). when i include this particular table it is taking 45min to execute the query.
This tables has around 37K records. Though it is going for a full table scan i feel it should not take this much time.
when i looked at the query plan (below here)

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=FIRST_ROWS 1 100
NESTED LOOPS 1 66 100
NESTED LOOPS 1 56 98
MERGE JOIN CARTESIAN 1 26 96
TABLE ACCESS FULL SA.TABLE_X_OPENREACH 44 K 567 K 96
BUFFER SORT 1 13
TABLE ACCESS BY GLOBAL INDEX ROWID SA.TABLE_CASE 1 13 ROWID ROW L
INDEX RANGE SCAN SA.IX_X_LLU_FLAG 1
TABLE ACCESS BY INDEX ROWID SA.TABLE_SUBCASE 1 30 2
INDEX UNIQUE SCAN SA.SUBCASE_OBJINDEX 1 1
TABLE ACCESS BY INDEX ROWID SA.TABLE_SITE_PART 1 10 2
INDEX UNIQUE SCAN SA.SITE_PART_OBJINDEX 1 1

the extralines that i can see in the plan are buffer sort and merge join caretsian (when i add the new table table_x_openreach).. I feel that this making the query take a lot of time.

1) why is this buffer sort appering in the query? and why is the merge join cartesian appear.
2) I felt that oracle will first get the result set from table_case,table_sub_case,table_site_part and then do a nested loop join on the table table_x_openreach. To my surprise the plan is different.

so i tried a creating a global temp table with result set with the above said 3 tables and joined it with table_x_openreach and the entire process took 2sec.

Is there any way that we can tell hint optimizer to create a temp table (like in the process i followed) with result set and join it with table_x_openreach then?
If not how can i avoid the Merge join cartesian.

Thanks in advcance,
Ravi

Merge using minus

Scott, March 06, 2007 - 9:24 pm UTC

G'day Tom,

In an effort to increase performance of a merge statement I thought about the concepts behind what's going on.
- Given data set
  - Insert if not matched
  - Update if matched
  - ** But what if exactly the same?

If I have a set of data that doesn't change very often and use the merge statement to identify records changed or updated, it means all records will have DML affected on them during each load.

This seems like a bit of a waste.

Would it be worthwhile adding a minus set operator on your data set?
MERGE INTO actual_table
SELECT xyz
FROM   my_fresh_data
MINUS  xyz
FROM   actual_table
ON ...

The MINUS seems to add full table access on actual_table on top of existing table access index by rowid.
Looking at autotrace, it drastically reduces db block gets and consistent gets, and eliminates most redo size (because of now limited DML).

Cheers
Tom Kyte
March 07, 2007 - 10:13 am UTC

if most of the rows are

a) present in the "source"
b) not changed

then this could save a lot of work (but realize it changes the locking semantics!! you won't lock all of the rows, might not affect you, might affect you - only you know)

The "uh oh" moment just happened...

Scott, March 06, 2007 - 9:29 pm UTC

Apologies, I've just realised this review, while in a topic with a million MERGE words, is probably too far off original question.

Let me know and I'll add it to the right one!

Cartesian Join

Suzanne, May 29, 2007 - 1:37 pm UTC

I've got a question that pertains to the very first question in this thread. My users are running a report that fails on temp space every time they run it. I've tracked down the issue to a Cartesian Join in the Explain Plan. The Cartesian Join makes sense if the cardinality estimate of the tables involved is reasonable. The cardinality of one of my tables is estimated at 1 (as in the first question in this thread). My problem is that the actual result set is 25,000 not 1! I'm enclosing the offending table layout, an extract of the report query that deals with my table, and the autotrace with statistics. Please see if you can't point me in the right direction - Thank you.

CREATE TABLE SDBA.TEMP_SBL_ORDER_INFO
(
ORDER_ID VARCHAR2(15) NOT NULL,
ACCNT_ID VARCHAR2(15) NULL,
CREATE_BY VARCHAR2(50) NULL,
PRODUCT VARCHAR2(100) NULL,
STATUS_CD VARCHAR2(30) NULL,
SERVICE_START_DT DATE NULL,
SERVICE_END_DT DATE NULL,
DISCONNECT_REASON VARCHAR2(30) NULL,
MRC NUMBER(22,7) NULL
)

SQL> select temp_sbl_order_info.service_start_dt,
2 temp_sbl_order_info.product, temp_sbl_order_info.order_id,
3 temp_sbl_order_info.service_end_dt
4 FROM temp_sbl_order_info
5 Where temp_sbl_order_info.product IN ('3SIXTY BASIC OFFICE',
6 '3SIXTY BROKER BASIC', '3SIXTY CARRIER BASIC', '3SIXTY ENHANCED',
7 '3SIXTY ENHANCED OFFICE', '3SIXTY PREMIUM', '3SIXTY PREMIUM OFFICE',
8 '3SIXTY VIP', '3sixty Basic', '3sixty Enhanced', '3sixty Premium',
9 'CONNECT.COM', 'DAT Connect.com', 'DAT Partners', 'PARTNERS',
10 'TransCore Exchange', 'TCX')
11 AND temp_sbl_order_info.disconnect_reason NOT IN ('Add List', 'Add User',
12 'Admin Error', 'Delete User(s)', 'Discount Ended',
13 'Duplicate Account', 'Seasonal', 'Testing Only', 'Test', 'Trial');

25209 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=157 Card=1 Bytes=4
4)

1 0 TABLE ACCESS (FULL) OF 'TEMP_SBL_ORDER_INFO' (Cost=157 Car
d=1 Bytes=44)





Statistics
----------------------------------------------------------
417 recursive calls
0 db block gets
5796 consistent gets
4033 physical reads
0 redo size
826633 bytes sent via SQL*Net to client
12035 bytes received via SQL*Net from client
1682 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
25209 rows processed
Tom Kyte
May 30, 2007 - 11:05 am UTC

if you enable dynamic sampling at level 3, what then?

(dynamic sample examples on this page)

Cartesian Join

A reader, May 30, 2007 - 2:14 pm UTC

I'm afraid there is no joy in Mudville. I changed the parameter setting to three, that didn't help. I added a hint and that didn't help. I did do a little poking around at the data and I found out that the disconnect_reason field is NULL in 774645 records out of 845261. Would this make a difference? I'm enclosing a copy of my tests.

SQL> show parameter optimizer

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 3
optimizer_features_enable string 9.2.0
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_max_permutations integer 2000
optimizer_mode string ALL_ROWS

SQL> set autotrace traceonly
SQL> select /*+ dymanic_sampling(3) */ temp_sbl_order_info.service_start_dt,
2 temp_sbl_order_info.product, temp_sbl_order_info.order_id,
3 temp_sbl_order_info.service_end_dt
4 FROM temp_sbl_order_info
5 Where temp_sbl_order_info.product IN ('3SIXTY BASIC OFFICE',
6 '3SIXTY BROKER BASIC', '3SIXTY CARRIER BASIC', '3SIXTY ENHANCED',
7 '3SIXTY ENHANCED OFFICE', '3SIXTY PREMIUM', '3SIXTY PREMIUM OFFICE',
8 '3SIXTY VIP', '3sixty Basic', '3sixty Enhanced', '3sixty Premium',
9 'CONNECT.COM', 'DAT Connect.com', 'DAT Partners', 'PARTNERS',
10 'TransCore Exchange', 'TCX')
11 AND temp_sbl_order_info.disconnect_reason NOT IN ('Add List', 'Add User',
12 'Admin Error', 'Delete User(s)', 'Discount Ended',
13 'Duplicate Account', 'Seasonal', 'Testing Only', 'Test', 'Trial');

25209 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=157 Card=1 Bytes=4
4)

1 0 TABLE ACCESS (FULL) OF 'TEMP_SBL_ORDER_INFO' (Cost=157 Car
d=1 Bytes=44)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5709 consistent gets
0 physical reads
0 redo size
826634 bytes sent via SQL*Net to client
12035 bytes received via SQL*Net from client
1682 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
25209 rows processed

Tom Kyte
May 30, 2007 - 4:12 pm UTC

can you put the tablename in the hint.

select /*+ dymanic_sampling(temp_sbl_order_info 3) */

Cartesian Join

Suzanne, May 30, 2007 - 2:15 pm UTC

Oops, forgot to include my particulars. Sorry.

"dymanic" is not going to work ;)

Gabe, May 30, 2007 - 8:34 pm UTC

/*+ dymanic_sampling(3) */ 
/*+ dymanic_sampling(temp_sbl_order_info 3) */

that's only going to make the query "die of overexcitement" :)

Tom Kyte
May 30, 2007 - 9:26 pm UTC

laughing out loud :)

yah, I did not see the TYPO :)

Cartesian Join

Suzanne, June 01, 2007 - 4:18 pm UTC

Whaooo - spelling counts

This is the real query I've been trying to run and the hint actually gets it to return without an error.

SQL> SELECT /*+ dynamic_sampling(temp_sbl_order_info 3) */ boxi_admin.sbl_acct.tcs_account_id, boxi_
admin.sbl_acct.tcs_company_id,
2 boxi_admin.sbl_acct.child_name, sbl_asset_attr2.char_val,
3 boxi_admin.sbl_acct.client_type, temp_sbl_order_info.service_start_dt,
4 temp_sbl_order_info.product, temp_sbl_order_info.order_id,
5 temp_sbl_order_info.service_end_dt
6 FROM boxi_admin.sbl_asset_attr sbl_asset_attr2, boxi_admin.sbl_acct,
7 boxi_admin.sbl_asset sbl_asset2, temp_sbl_order_info
8 WHERE boxi_admin.sbl_acct.child_rowid = sbl_asset2.serv_acct_id
9 AND sbl_asset2.s_asset_rowid = sbl_asset_attr2.asset_id
10 AND temp_sbl_order_info.accnt_id = boxi_admin.sbl_acct.child_rowid
11 AND temp_sbl_order_info.product IN ('3SIXTY BASIC OFFICE',
12 '3SIXTY BROKER BASIC', '3SIXTY CARRIER BASIC', '3SIXTY ENHANCED',
13 '3SIXTY ENHANCED OFFICE', '3SIXTY PREMIUM', '3SIXTY PREMIUM OFFICE',
14 '3SIXTY VIP', '3sixty Basic', '3sixty Enhanced', '3sixty Premium',
15 'CONNECT.COM', 'DAT Connect.com', 'DAT Partners', 'PARTNERS',
16 'TransCore Exchange', 'TCX')
17 AND NOT temp_sbl_order_info.disconnect_reason IN ('Add List', 'Add User',
18 'Admin Error', 'Delete User(s)', 'Discount Ended',
19 'Duplicate Account', 'Seasonal', 'Testing Only', 'Test', 'Trial');

257118 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2824 Card=1231536
Bytes=168720432)

1 0 HASH JOIN (Cost=2824 Card=1231536 Bytes=168720432)
2 1 TABLE ACCESS (FULL) OF 'TEMP_SBL_ORDER_INFO' (Cost=157 C
ard=32677 Bytes=1764558)

3 1 HASH JOIN (Cost=1779 Card=739902 Bytes=61411866)
4 3 TABLE ACCESS (FULL) OF 'SBL_ACCT' (Cost=135 Card=16593
0 Bytes=8130570)

5 3 HASH JOIN (Cost=1313 Card=365407 Bytes=12423838)
6 5 TABLE ACCESS (FULL) OF 'SBL_ASSET_ATTR' (Cost=99 Car
d=181420 Bytes=2539880)

7 5 TABLE ACCESS (FULL) OF 'SBL_ASSET' (Cost=905 Card=64
0450 Bytes=12809000)





Statistics
----------------------------------------------------------
584 recursive calls
0 db block gets
43704 consistent gets
26533 physical reads
0 redo size
7115953 bytes sent via SQL*Net to client
120262 bytes received via SQL*Net from client
17143 SQL*Net roundtrips to/from client
15 sorts (memory)
0 sorts (disk)
257118 rows processed

OK, why does this table require a hint?
Tom Kyte
June 03, 2007 - 5:06 pm UTC

well, the hint could be a session setting (alter session) or even database wide setting.

it tells the optimizer "hey, when you had to guess at a cardinality on a predicate - don't, take a small sample and get better information based on the WHERE CLAUSE the end user supplied in the query"

the optimizer works with statistics on
A table
AN index
A column

sometimes, many times - it can make better "guesses" with more statistics, this sampling lets it do that.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:40230704959128#69134369478883


Yet another Cartesian join

Zak, July 19, 2007 - 7:10 am UTC

Hi Tom,

I've been tasked to have a look at a query which is running really slow - so slow infact that it takes around 3 hours to run! The explain plan shows that there is a merge cartesian join, however I don't think this is the problem. I think the problem is with the condition:

AND (:pi_user_type IS NULL
OR q.userid_type = :pi_user_type)

If I replace this with " AND q.userid_type = 'KOL' " then it seems to run within minutes.

But I need to cater for a null being passed in. I tried the following: " AND q.userid_type = NVL(:pi_user_type, q.userid_type) ". This made no difference to the timing. Is this because the index is not being used with a NVL?

Here is the query:

SELECT COUNT(1)
--INTO v_query_count
FROM quotation_models qm,
quotation_status_codes qsc,
models m,
products p,
quotation_profiles qpr,
quotations q,
external_accounts ea,
payment_headers ph
WHERE q.quo_id = NVL(:quo_id, q.quo_id)
AND (:quoteType IS NULL
OR (:quoteType = 'S' AND q.mul_quote IS NULL) -- Changes for
OR (:quoteType = 'M' AND q.mul_quote = 'Y')) -- defect 611 (CS)
AND (:pi_userid IS NULL
OR q.userid = :pi_userid)
AND (:pi_user_type IS NULL
OR q.userid_type = :pi_user_type)
-- AND q.userid_type = 'KOL'
AND qm.quote_no = NVL(:pi_quote_refquote_no, qm.quote_no)
AND qm.revision_no = NVL(:pi_quote_refrevision_no, qm.revision_no)
AND qm.quo_quo_id = q.quo_id
AND NVL(qm.marked_saved, 'N') = NVL(:markedSaved, NVL(qm.marked_saved, 'N'))
AND qsc.quotation_status = qm.quote_status
AND ea.c_id = q.c_id
AND ea.account_type = q.account_type
AND ea.account_code = q.account_code
AND ea.account_code = NVL(:pi_accountaccount_code, ea.account_code)
AND ea.account_type = NVL(:pi_accountaccount_type, ea.account_type)
AND ea.c_id = NVL(:pi_accountc_id, ea.c_id)
AND TRUNC(revision_date) BETWEEN NVL(:MinDate, TO_DATE(1721424, 'J'))
AND NVL(:MaxDate, TO_DATE(5373484, 'J'))
AND qpr.qpr_id = q.qpr_qpr_id
AND (NVL(:orderStatus, 'B') = 'B'
OR (:orderStatus = 'N'
AND quote_status NOT IN (2, 3, 7, 8, 15, 16, 17, 18, 20, 22, 23))
OR (:orderStatus = 'Y'
AND quote_status IN (2, 3, 15, 16, 20, 22, 23)))
AND p.product_code = qpr.prd_product_code
AND m.mdl_id = qm.mdl_mdl_id
AND ph.payment_header_id = qm.payment_id
AND NVL(recalc_quote, 'N') = 'N'
AND EXISTS (SELECT '1'
FROM quotation_elements
WHERE qmd_qmd_id = qmd_id)
AND LOWER(qsc.description) = LOWER(NVL(:quoteStatus, qsc.description));

The explain plan for this is:

Plan
SELECT STATEMENT CHOOSECost: 175 Bytes: 97 Cardinality: 1
45 SORT AGGREGATE Bytes: 97 Cardinality: 1
44 CONCATENATION
22 FILTER
20 FILTER
19 NESTED LOOPS Cost: 9 Bytes: 97 Cardinality: 1
17 NESTED LOOPS Cost: 3 Bytes: 32 Cardinality: 1
15 NESTED LOOPS Cost: 3 Bytes: 28 Cardinality: 1
12 NESTED LOOPS Cost: 9 Bytes: 93 Cardinality: 1
10 NESTED LOOPS Cost: 8 Bytes: 73 Cardinality: 1
7 NESTED LOOPS Bytes: 20 Cardinality: 1
4 MERGE JOIN CARTESIAN Cost: 4 Bytes: 40 Cardinality: 1
1 TABLE ACCESS FULL WILLOW2K.QUOTATION_STATUS_CODES Cost: 1 Bytes: 17 Cardinality: 1
3 SORT JOIN Bytes: 8 Cardinality: 1
2 INDEX FAST FULL SCAN UNIQUE WILLOW2K.EA_PK Cost: 1 Bytes: 8 Cardinality: 1
6 TABLE ACCESS BY INDEX ROWID WILLOW2K.QUOTATIONS Cost: 2 Bytes: 20 Cardinality: 1
5 INDEX RANGE SCAN NON-UNIQUE WILLOW2K.QUO_EA_FK_I Cost: 1 Cardinality: 1
9 TABLE ACCESS BY INDEX ROWID WILLOW2K.QUOTATION_MODELS Cost: 4 Bytes: 33 Cardinality: 1
8 INDEX RANGE SCAN UNIQUE WILLOW2K.QMD_UK1 Cost: 3 Cardinality: 1
11 INDEX UNIQUE SCAN UNIQUE WILLOW2K.PH_PK Bytes: 150 Cardinality: 50
14 TABLE ACCESS BY INDEX ROWID WILLOW2K.QUOTATION_PROFILES Cost: 1 Bytes: 101,008 Cardinality: 12,626
13 INDEX UNIQUE SCAN UNIQUE WILLOW2K.QPR_PK Cardinality: 12,626
16 INDEX UNIQUE SCAN UNIQUE WILLOW2K.PRD_PK Bytes: 300 Cardinality: 75
18 INDEX UNIQUE SCAN UNIQUE WILLOW2K.MDL_PK Bytes: 191,660 Cardinality: 47,915
21 INDEX RANGE SCAN NON-UNIQUE WILLOW2K.QEL_QMD_FK_I Cost: 3 Bytes: 45 Cardinality: 9
43 FILTER
42 FILTER
41 NESTED LOOPS Cost: 9 Bytes: 97 Cardinality: 1
39 NESTED LOOPS Cost: 9 Bytes: 93 Cardinality: 1
37 NESTED LOOPS Cost: 9 Bytes: 90 Cardinality: 1
34 NESTED LOOPS Cost: 8 Bytes: 73 Cardinality: 1
31 NESTED LOOPS Cost: 4 Bytes: 40 Cardinality: 1
29 NESTED LOOPS Cost: 3 Bytes: 32 Cardinality: 1
27 NESTED LOOPS Cost: 3 Bytes: 28 Cardinality: 1
24 TABLE ACCESS BY INDEX ROWID WILLOW2K.QUOTATIONS Cost: 2 Bytes: 20 Cardinality: 1
23 INDEX UNIQUE SCAN UNIQUE WILLOW2K.QUO_PK Cost: 1 Cardinality: 1
26 TABLE ACCESS BY INDEX ROWID WILLOW2K.QUOTATION_PROFILES Cost: 1 Bytes: 101,008 Cardinality: 12,626
25 INDEX UNIQUE SCAN UNIQUE WILLOW2K.QPR_PK Cardinality: 12,626
28 INDEX UNIQUE SCAN UNIQUE WILLOW2K.PRD_PK Bytes: 300 Cardinality: 75
30 INDEX UNIQUE SCAN UNIQUE WILLOW2K.EA_PK Cost: 1 Bytes: 8 Cardinality: 1
33 TABLE ACCESS BY INDEX ROWID WILLOW2K.QUOTATION_MODELS Cost: 4 Bytes: 33 Cardinality: 1
32 INDEX RANGE SCAN UNIQUE WILLOW2K.QMD_UK1 Cost: 3 Cardinality: 1
36 TABLE ACCESS BY INDEX ROWID WILLOW2K.QUOTATION_STATUS_CODES Cost: 1 Bytes: 17 Cardinality: 1
35 INDEX UNIQUE SCAN UNIQUE WILLOW2K.QSTC_PK Cardinality: 1
38 INDEX UNIQUE SCAN UNIQUE WILLOW2K.PH_PK Bytes: 150 Cardinality: 50
40 INDEX UNIQUE SCAN UNIQUE WILLOW2K.MDL_PK Bytes: 191,660 Cardinality: 47,915

Could you help me out please.

Thanks,

Zak


Tom Kyte
July 19, 2007 - 11:01 am UTC

sounds like you really need, want, desire........

two queries. one with a predicate and one without.


that is what you want. You can accomplish that easily using ref cursors in plsql or regular old sql with an IF condition in whatever language you are using...


nvl or explansion *might* work for you if you use:


and q.userid_type = nvl(:pi_user_type,q.userid_type)


instead of:

 AND (:pi_user_type IS NULL
           OR  q.userid_type = :pi_user_type)


http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7555175291763

Execution Plan

Alex, August 22, 2007 - 7:58 pm UTC

Hi Tom,

We are running Oracle 9.2.0.6 on Solaris 9.

We are experiencing an issue with the execution plan for the query listed below. The database seems to be choosing the wrong execution plan for the query.

FACILITY_CLAIM fact table -- partitions accessed for this query contain ~123M rows.
MEMBER_COVERAGE fact table -- partition accessed for this query contains ~94M rows.
MEMBER_INDIVIDUAL_ID_16466_17 -- filter table containing ~24K rows.
All other tables in the query are dimention tables and are relatively small (under a million rows).
These numbers are confirmed by num_rows in *_tables, *_tab_partitions and subpartitions.

As you can see from plans below, the cardinalities for the big tables reported seem to be wrong (as opposed to the numbers above):
FACILITY_CLAIM -- 54M
MEMBER_COVERAGE -- 73M

This may be part of the problem. Could you please comment on this?

Our desired execution plan is described in point 3. However, we only seem to be able to achieve that with leading(MEMBER_COVERAGE) hint, which is not very intuitive (leading(MEMBER_INDIVIDUAL_ID_16466_17) is more intuitive since that is the table we want to use as a filter). The query with this plan runs in 1 hour as opposed to the plans in point 1 or 2, which run out of temp space after several hours.

How can we achive the desired execution plan preferrably without using hints?
If we have to use hints, is "leading(MEMBER_COVERAGE)" the right hint?
Why doesn't "leading(MEMBER_INDIVIDUAL_ID_16466_17)" work?
Could you explain the execution plans in points 1 and 2?


1. Without any hints, it uses FACILITY_CLAIM as the leading table in the join, then it joins some of the dimension tables then MEMBER_COVERAGE (at which point it runs out of temp space), and only after it joins to MEMBER_INDIVIDUAL_ID_16466_17.

2. With leading(MEMBER_INDIVIDUAL_ID_16466_17) hint, first, it does a cartesian product of MEMBER_INDIVIDUAL_ID_16466_17 and a small dimension table, then, it joins that to FACILITY_CLAIM. Then, it joins to MEMBER_COVERAGE. At this point, this is even worse than no hints!

3. With leading(MEMBER_COVERAGE) hint, it joins MEMBER_INDIVIDUAL_ID_16466_17 to MEMBER_COVERAGE, then, (at that point, the result set is a few thousand records). Then, it joins that to FACILITY_CLAIM and so on.
This is the desired execution plan.

Query: I will paste in the second follow up due to message size limitation.

Explain Plan for point 1:

| Id  | Operation                                          |  Name                          | Rows  | Bytes |TempSpc| Cost  |
-----------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                                   |                                |    44G|    29T|       |   132K|

|*  1 |  HASH JOIN                                         |                                |    44G|    29T|       |   132K|

|   2 |   TABLE ACCESS FULL                                | PROCEDURE                      |   160K|  2190K|       |    37 |

|*  3 |   HASH JOIN                                        |                                |    26G|    17T|       |   132K|

|   4 |    TABLE ACCESS FULL                               | PROCEDURE                      |   160K|  2190K|       |    37 |

|*  5 |    HASH JOIN                                       |                                |    15G|    10T|       |   132K|

|   6 |     TABLE ACCESS FULL                              | PROCEDURE                      |   160K|  2190K|       |    37 |

|*  7 |     HASH JOIN                                      |                                |  9361M|  6033G|       |   132K|

|   8 |      TABLE ACCESS FULL                             | PROCEDURE                      |   160K|  2190K|       |    37 |

|*  9 |      HASH JOIN                                     |                                |  5549M|  3504G|       |   132K|

|  10 |       TABLE ACCESS FULL                            | PROCEDURE                      |   160K|  2190K|       |    37 |

|* 11 |       HASH JOIN                                    |                                |  3289M|  2034G|       |   132K|

|  12 |        TABLE ACCESS FULL                           | PROCEDURE                      |   160K|  3128K|       |    37 |

|* 13 |        HASH JOIN                                   |                                |  1950M|  1169G|       |   132K|

|  14 |         TABLE ACCESS FULL                          | REVENUE                        | 10026 |   127K|       |     3 |

|* 15 |         HASH JOIN                                  |                                |  1950M|  1146G|       |   132K|

|  16 |          TABLE ACCESS FULL                         | PROCEDURE                      |   160K|  2190K|       |    37 |

|* 17 |          HASH JOIN                                 |                                |  1156M|   664G|       |   132K|

|  18 |           TABLE ACCESS FULL                        | DIAGNOSIS                      |   113K|  1554K|       |    33 |

|* 19 |           HASH JOIN                                |                                |   726M|   408G|       |   132K|

|  20 |            TABLE ACCESS FULL                       | DIAGNOSIS                      |   113K|  1554K|       |    33 |

|* 21 |            HASH JOIN                               |                                |   457M|   250G|       |   132K|

|  22 |             TABLE ACCESS FULL                      | DIAGNOSIS                      |   113K|  1554K|       |    33 |

|* 23 |             HASH JOIN                              |                                |   287M|   153G|       |   132K|

|  24 |              TABLE ACCESS FULL                     | DIAGNOSIS                      |   113K|  1554K|       |    33 |

|* 25 |              HASH JOIN                             |                                |   180M|    94G|       |   132K|

|  26 |               TABLE ACCESS FULL                    | DIAGNOSIS                      |   113K|  1554K|       |    33 |

|* 27 |               HASH JOIN                            |                                |   113M|    57G|       |   132K|

|  28 |                TABLE ACCESS FULL                   | DIAGNOSIS                      |   113K|  1554K|       |    33 |

|* 29 |                HASH JOIN                           |                                |    71M|    35G|       |   131K|

|  30 |                 TABLE ACCESS FULL                  | DIAGNOSIS                      |   113K|  1554K|       |    33 |

|* 31 |                 HASH JOIN                          |                                |    44M|    21G|       |   131K|

|  32 |                  TABLE ACCESS FULL                 | DIAGNOSIS                      |   113K|  1554K|       |    33 |

|* 33 |                  HASH JOIN                         |                                |    28M|    13G|       |   131K|

|  34 |                   TABLE ACCESS FULL                | DIAGNOSIS                      |   113K|  1554K|       |    33 |

|* 35 |                   HASH JOIN                        |                                |    17M|  8314M|       |   131K|

|* 36 |                    TABLE ACCESS FULL               | GEOGRAPHIC                     | 53327 |  1770K|       |    29 |

|* 37 |                    HASH JOIN                       |                                |    11M|  5067M|       |   131K|

|  38 |                     TABLE ACCESS FULL              | REASON                         |  2591 | 41456 |       |     4 |

|* 39 |                     HASH JOIN                      |                                |    11M|  4889M|       |   131K|

|  40 |                      TABLE ACCESS FULL             | REASON                         |  2591 | 41456 |       |     4 |

|* 41 |                      HASH JOIN                     |                                |    11M|  4712M|       |   131K|

|  42 |                       TABLE ACCESS FULL            | MEMBER_INDIVIDUAL_ID_16466_17  | 24182 |   141K|       |     4 |

|* 43 |                       HASH JOIN                    |                                |    54M|    21G|   440M|   131K|

|* 44 |                        TABLE ACCESS FULL           | MEMBER_COVERAGE                |    73M|  2676M|       | 13303 |

|* 45 |                        HASH JOIN                   |                                |    54M|    19G|    44M| 71197 |

|  46 |                         TABLE ACCESS FULL          | PROVIDER                       |  6504K|   285M|       |  9673 |

|* 47 |                         HASH JOIN                  |                                |    54M|    16G|       | 25195 |

|  48 |                          TABLE ACCESS FULL         | PLACE_OF_SERVICE               |    45 |   720 |       |     2 |

|* 49 |                          HASH JOIN                 |                                |    54M|    16G|       | 25193 |

|  50 |                           TABLE ACCESS FULL        | DRG                            |   668 |  8684 |       |     2 |

|* 51 |                           HASH JOIN                |                                |    54M|    15G|       | 25191 |

|* 52 |                            TABLE ACCESS FULL       | PRODUCT                        |   191 |  4584 |       |     2 |

|  53 |                            PARTITION RANGE ITERATOR|                                |       |       |       |       |

|* 54 |                             TABLE ACCESS FULL      | FACILITY_CLAIM                 |    54M|    14G|       | 25189 |

-----------------------------------------------------------------------------------------------------------------------------


Explain Plan for point 2:

| Id  | Operation                                         |  Name                          | Rows  | Bytes |TempSpc| Cost  |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                  |                                |    45G| 30T|          |  1837M|
|*  1 |  HASH JOIN                                        |                                |    45G| 30T|  2307G|  1837M|
|   2 |   TABLE ACCESS FULL                               | PROCEDURE                      |   160K|  2190K|       |    37 |
|*  3 |   HASH JOIN                                       |                                |    27G| 17T|  1341G|  1799M|
|   4 |    TABLE ACCESS FULL                              | PROCEDURE                      |   160K|  2190K|       |    37 |
|*  5 |    HASH JOIN                                      |                                |    16G| 10T|   780G|  1778M|
|   6 |     TABLE ACCESS FULL                             | PROCEDURE                      |   160K|  2190K|       |    37 |
|*  7 |     HASH JOIN                                     |                                |  9517M|  6133G|   453G|  1765M|
|   8 |      TABLE ACCESS FULL                            | PROCEDURE                      |   160K|  2190K|       |    37 |
|*  9 |      HASH JOIN                                    |                                |  5642M|  3562G|   263G|  1757M|
|  10 |       TABLE ACCESS FULL                           | PROCEDURE                      |   160K|  2190K|       |    37 |
|* 11 |       HASH JOIN                                   |                                |  3344M|  2068G|   151G|  1753M|
|  12 |        TABLE ACCESS FULL                          | PROCEDURE                      |   160K|  3128K|       |    37 |
|* 13 |        HASH JOIN                                  |                                |  1982M|  1189G|   148G|  1751M|
|  14 |         TABLE ACCESS FULL                         | REVENUE                        | 10026 |   127K|       |     3 |
|* 15 |         HASH JOIN                                 |                                |  1982M|  1165G|    86G|  1748M|
|  16 |          TABLE ACCESS FULL                        | PROCEDURE                      |   160K|  2190K|       |    37 |
|* 17 |          HASH JOIN                                |                                |  1175M|   675G|    52G|  1747M|
|  18 |           TABLE ACCESS FULL                       | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 19 |           HASH JOIN                               |                                |   739M|   415G|    32G|  1746M|
|  20 |            TABLE ACCESS FULL                      | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 21 |            HASH JOIN                              |                                |   464M|   254G|    19G|  1745M|
|  22 |             TABLE ACCESS FULL                     | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 23 |             HASH JOIN                             |                                |   292M|   156G|    12G|  1745M|
|  24 |              TABLE ACCESS FULL                    | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 25 |              HASH JOIN                            |                                |   183M| 95G|  7696M|  1745M|
|  26 |               TABLE ACCESS FULL                   | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 27 |               HASH JOIN                           |                                |   115M| 58G|  4718M|  1745M|
|  28 |                TABLE ACCESS FULL                  | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 29 |                HASH JOIN                          |                                |    72M| 36G|  2890M|  1745M|
|  30 |                 TABLE ACCESS FULL                 | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 31 |                 HASH JOIN                         |                                |    45M| 22G|  1769M|  1745M|
|  32 |                  TABLE ACCESS FULL                | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 33 |                  HASH JOIN                        |                                |    28M| 13G|  1082M|  1745M|
|  34 |                   TABLE ACCESS FULL               | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 35 |                   HASH JOIN                       |                                |    18M|  8453M|   660M|  1745M|
|* 36 |                    TABLE ACCESS FULL              | GEOGRAPHIC                     | 53327 |  1770K|       |    29 |
|* 37 |                    HASH JOIN                      |                                |    11M|  5151M|   638M|  1745M|
|  38 |                     TABLE ACCESS FULL             | REASON                         |  2591 | 41456 |       |     4 |
|* 39 |                     HASH JOIN                     |                                |    11M|  4971M|   615M|  1745M|
|  40 |                      TABLE ACCESS FULL            | REASON                         |  2591 | 41456 |       |     4 |
|* 41 |                      HASH JOIN                    |                                |    11M|  4790M|   550M|  1745M|
|  42 |                       TABLE ACCESS FULL           | PROVIDER                       |  6504K|   285M|       |  9673 |
|* 43 |                       HASH JOIN                   |                                |    11M|  4272M|   532M|  1745M|
|  44 |                        TABLE ACCESS FULL          | DRG                            |   668 |  8684 |       |     2 |
|* 45 |                        HASH JOIN                  |                                |    11M|  4125M|    50T|  1745M|
|* 46 |                         TABLE ACCESS FULL         | MEMBER_COVERAGE                |    73M|  2676M|       | 13303 |
|* 47 |                         HASH JOIN                 |                                |  1306G|   389T|    46T|   785M|
|* 48 |                          TABLE ACCESS FULL        | PRODUCT                        |   191 |  4584 |       |     2 |
|* 49 |                          HASH JOIN                |                                |  1308G|   361T|       | 73557 |
|  50 |                           MERGE JOIN CARTESIAN    |                                |  1088K| 22M|          | 48368 |
|  51 |                            TABLE ACCESS FULL      | MEMBER_INDIVIDUAL_ID_16466_17  | 24182 |   141K|       |     4 |
|  52 |                            BUFFER SORT            |                                |    45 |   720 |       | 48364 |
|  53 |                             TABLE ACCESS FULL     | PLACE_OF_SERVICE               |    45 |   720 |       |     2 |
|  54 |                           PARTITION RANGE ITERATOR|                                |       |    |          |       |
|* 55 |                            TABLE ACCESS FULL      | FACILITY_CLAIM                 |    54M| 14G|          | 25189 |
----------------------------------------------------------------------------------------------------------------------------


Explain Plan for point 3:

| Id  | Operation                                         |  Name                          | Rows  | Bytes |TempSpc| Cost  |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                  |                                |    46G| 31T|          |    91M|
|*  1 |  HASH JOIN                                        |                                |    46G| 31T|  2347G|       91M|
|   2 |   TABLE ACCESS FULL                               | PROCEDURE                      |   160K|  2190K|       |    37 |
|*  3 |   HASH JOIN                                       |                                |    27G| 18T|  1364G|       53M|
|   4 |    TABLE ACCESS FULL                              | PROCEDURE                      |   160K|  2190K|       |    37 |
|*  5 |    HASH JOIN                                      |                                |    16G| 10T|   793G|       31M|
|   6 |     TABLE ACCESS FULL                             | PROCEDURE                      |   160K|  2190K|       |    37 |
|*  7 |     HASH JOIN                                     |                                |  9679M|  6238G|   460G|    18M|
|   8 |      TABLE ACCESS FULL                            | PROCEDURE                      |   160K|  2190K|       |    37 |
|*  9 |      HASH JOIN                                    |                                |  5738M|  3623G|   267G|    10M|
|  10 |       TABLE ACCESS FULL                           | PROCEDURE                      |   160K|  2190K|       |    37 |
|* 11 |       HASH JOIN                                   |                                |  3401M|  2103G|   155G|  6458K|
|  12 |        TABLE ACCESS FULL                          | PROCEDURE                      |   160K|  2190K|       |    37 |
|* 13 |        HASH JOIN                                  |                                |  2016M|  1220G|    89G|  3922K|
|  14 |         TABLE ACCESS FULL                         | PROCEDURE                      |   160K|  3128K|       |    37 |
|* 15 |         HASH JOIN                                 |                                |  1195M|   701G|    54G|  2464K|
|  16 |          TABLE ACCESS FULL                        | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 17 |          HASH JOIN                                |                                |   751M|   431G|    33G|  1568K|
|  18 |           TABLE ACCESS FULL                       | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 19 |           HASH JOIN                               |                                |   472M|   264G|    20G|  1017K|
|  20 |            TABLE ACCESS FULL                      | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 21 |            HASH JOIN                              |                                |   297M|   162G|    12G|   678K|
|  22 |             TABLE ACCESS FULL                     | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 23 |             HASH JOIN                             |                                |   186M| 99G|  8010M|   470K|
|  24 |              TABLE ACCESS FULL                    | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 25 |              HASH JOIN                            |                                |   117M| 61G|  4913M|   342K|
|  26 |               TABLE ACCESS FULL                   | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 27 |               HASH JOIN                           |                                |    73M| 37G|  3011M|   264K|
|  28 |                TABLE ACCESS FULL                  | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 29 |                HASH JOIN                          |                                |    46M| 23G|  1844M|   216K|
|  30 |                 TABLE ACCESS FULL                 | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 31 |                 HASH JOIN                         |                                |    29M| 14G|  1129M|   186K|
|  32 |                  TABLE ACCESS FULL                | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 33 |                  HASH JOIN                        |                                |    18M|  8825M|   690M|   168K|
|* 34 |                   TABLE ACCESS FULL               | GEOGRAPHIC                     | 53327 |  1770K|       |    29 |
|* 35 |                   HASH JOIN                       |                                |    12M|  5388M|   667M|   157K|
|  36 |                    TABLE ACCESS FULL              | PLACE_OF_SERVICE               |    45 |   720 |       |     2 |
|* 37 |                    HASH JOIN                      |                                |    12M|  5205M|   649M|   147K|
|  38 |                     TABLE ACCESS FULL             | DRG                            |   668 |  8684 |       |     2 |
|* 39 |                     HASH JOIN                     |                                |    12M|  5056M|   626M|   136K|
|  40 |                      TABLE ACCESS FULL            | REASON                         |  2591 | 41456 |       |     4 |
|* 41 |                      HASH JOIN                    |                                |    12M|  4872M|   603M|   126K|
|  42 |                       TABLE ACCESS FULL           | REASON                         |  2591 | 41456 |       |     4 |
|* 43 |                       HASH JOIN                   |                                |    12M|  4689M|   584M|   117K|
|  44 |                        TABLE ACCESS FULL          | REVENUE                        | 10026 |   127K|       |     3 |
|* 45 |                        HASH JOIN                  |                                |    12M|  4540M|   518M|   107K|
|  46 |                         TABLE ACCESS FULL         | PROVIDER                       |  6504K|   285M|       |  9673 |
|* 47 |                         HASH JOIN                 |                                |    12M|  4012M|   484M| 89341 |
|* 48 |                          TABLE ACCESS FULL        | PRODUCT                        |   191 |  4584 |       |     2 |
|* 49 |                          HASH JOIN                |                                |    12M|  3741M|   106M| 81613 |
|* 50 |                           HASH JOIN               |                                |    15M|   666M|   704M| 24529 |
|  51 |                            TABLE ACCESS FULL      | MEMBER_INDIVIDUAL_ID_16466_17  | 24182 |   141K|       |     4 |
|* 52 |                            TABLE ACCESS FULL      | MEMBER_COVERAGE                |    73M|  2676M|       | 13303 |
|  53 |                           PARTITION RANGE ITERATOR|                                |       |    |          |       |
|* 54 |                            TABLE ACCESS FULL      | FACILITY_CLAIM                 |    54M| 14G|          | 25189 |
----------------------------------------------------------------------------------------------------------------------------


Thanks in advance for your help,

Alex

Tom Kyte
August 23, 2007 - 10:57 am UTC

i will not look at a multi-page explain plan in a review/followup. not going to happen.

there isn't even a query to look at.

when I see the same table referenced over and over and over again, I get suspicious that the person that wrote the query doesn't know SQL as well as they should probably - they are joining when they should be aggregating or using analytics.

Execution Plan

Alex, August 22, 2007 - 8:01 pm UTC

Hi Tom,

Here is the query and the desired execution plan (point 3) for my message above.

Thanks

Alex

Query:

SELECT 
        DRG.CODE AS "drgsystm",
        FACILITY_CLAIM.ADMIT_CHANNEL_CODE AS "admitsrc",
        FACILITY_CLAIM.AMT_CALCULATED_PAID AS "amtpaid",
        FACILITY_CLAIM.AMT_RESERVE AS "amtreser",
        FACILITY_CLAIM.AMT_SUBMITTED_CHARGE AS "amtclm",
        FACILITY_CLAIM.AMT_COPAY AS "amtcopay",
        FACILITY_CLAIM.AMT_COVERED AS "amtcover",
        FACILITY_CLAIM.AMT_DEDUCTIBLE AS "amtdedc",
        FACILITY_CLAIM.AMT_DISCOUNT AS "amtdisc",
        FACILITY_CLAIM.AMT_NET_PAID AS "amtnetpd",
        FACILITY_CLAIM.BENEFIT_LEVEL_CODE AS "serv_par",
        FACILITY_CLAIM.CLAIM_NBR AS "audit",
        FACILITY_CLAIM.COB_CODE AS "cobcode",
        FACILITY_CLAIM.COVERAGE_TYPE_CODE AS "covertyp",
        FACILITY_CLAIM.DATA_SOURCE_CODE AS "hl_datasrc",
        FACILITY_CLAIM.DISCHARGE_STATUS_CODE AS "patstat",
        FACILITY_CLAIM.ENCOUNTER_CODE AS "encntr_cd",
        FACILITY_CLAIM.CLAIM_CAPITATION_STATUS_CODE AS "encntflg",
        FACILITY_CLAIM.PROVIDER_PAR_CODE AS "parcode",
        FACILITY_CLAIM.QUANTITY_UNITS AS "nbr_unit",
        FACILITY_CLAIM.SERVICE_LINE_NBR AS "dtl_nbr",
        TO_CHAR (FACILITY_CLAIM.SERVICE_POST_DATE, 'MM-DD-YYYY') AS "postdate",
        TO_CHAR (FACILITY_CLAIM.SERVICE_FROM_DATE, 'MM-DD-YYYY') AS "svc_date",
        TO_CHAR (FACILITY_CLAIM.SERVICE_THRU_DATE,
                 'MM-DD-YYYY'
                ) AS "svc_thrudate",
        TO_CHAR (FACILITY_CLAIM.HEADER_EARLIEST_DATE,
                 'MM-DD-YYYY'
                ) AS "fromdate",
        TO_CHAR (FACILITY_CLAIM.HEADER_LATEST_DATE,
                 'MM-DD-YYYY') AS "thrudate",
        FACILITY_CLAIM.COSMOS_SITE_CODE AS "hmoid",
        FACILITY_CLAIM.COSMOS_LEGAL_ENTITY AS "fin_lgl",
        HEADER_DIAGNOSIS_1.CODE AS "diag1", HEADER_DIAGNOSIS_2.CODE AS "diag2",
        HEADER_DIAGNOSIS_3.CODE AS "diag3", HEADER_DIAGNOSIS_4.CODE AS "diag4",
        HEADER_DIAGNOSIS_5.CODE AS "diag5", HEADER_DIAGNOSIS_6.CODE AS "diag6",
        HEADER_DIAGNOSIS_7.CODE AS "diag7", HEADER_DIAGNOSIS_8.CODE AS "diag8",
        HEADER_DIAGNOSIS_9.CODE AS "diag9", HEADER_PROCEDURE_1.CODE AS "proc1",
        HEADER_PROCEDURE_1.TYPE_CODE AS "proc1_meth",
        HEADER_PROCEDURE_2.CODE AS "proc2", HEADER_PROCEDURE_3.CODE AS "proc3",
        HEADER_PROCEDURE_4.CODE AS "proc4", HEADER_PROCEDURE_5.CODE AS "proc5",
        HEADER_PROCEDURE_6.CODE AS "proc6", MEMBER_COVERAGE.GENDER AS "sex",
        MEMBER_COVERAGE.YEAR_OF_BIRTH AS "yob",
        PLACE_OF_SERVICE.AMA_CODE AS "ama_site",
        PLACE_OF_SERVICE.COSMOS_POS_CODE AS "site_cde",
        PRODUCT.ID AS "fin_prd", PRODUCT.BUSINESS_LINE_CODE AS "bus_line",
        PRODUCT.COSMOS_PRODUCT_TYPE_CODE AS "prod_typ",
        PRODUCT.FIDUCIARY_TYPE_CODE AS "process", REVENUE.CODE AS "revcode",
        SERVICE_PROCEDURE.CODE AS "cptcode",
        SERVICE_PROVIDER.UNIQUE_ID AS "prvkey",
        SERVICE_PROVIDER.COSMOS_PROVIDER_ID AS "provid",
        MEMBER_GEOGRAPHIC.CENSUS_DIVISION_CODE AS "mg_censcode",
        MEMBER_GEOGRAPHIC.CENSUS_DIVISION_DESC AS "mg_censdesc",
        MEMBER_GEOGRAPHIC.CENSUS_REGION_CODE AS "mg_censremg_code",
        MEMBER_GEOGRAPHIC.STATE AS "mg_state",
        FACILITY_CLAIM.AMT_COINSURANCE AS "amtcoins",
        FACILITY_CLAIM.AMT_ENCOUNTER AS "amtenctr",
        SERVICE_PROVIDER.PROVIDER_CATEGORY_CODE AS "svc_catcode",
        FACILITY_CLAIM.AMT_DUPLICATE_CHARGE AS "amtdupchg",
        FACILITY_CLAIM.MEDICAL_CLAIMS_PROCESSOR_CODE AS "medclmproc",
        SERVICE_PROVIDER.MPIN AS "svc_mpin",
        FACILITY_CLAIM.AMT_COB_REDUCTION AS "amtcobreduc",
        FACILITY_CLAIM.AMT_COB_SAVINGS AS "amtcobsave",
        MEMBER_COVERAGE.INDIVIDUAL_ID AS "indv_id",
        HEADER_REASON.CODE AS "denycode",
        SERVICE_PROVIDER.SPECIALTY_CATEGORY_CODE AS "prvtype",
        SERVICE_REASON.CODE AS "rsn_code",
        SERVICE_PROVIDER.PRIMARY_TAX_ID AS "svc_fed_id",
        MEMBER_COVERAGE.FUNDING_PHI_IND AS "funding_phi_ind"
   FROM DIAGNOSIS HEADER_DIAGNOSIS_1,
        DIAGNOSIS HEADER_DIAGNOSIS_2,
        DIAGNOSIS HEADER_DIAGNOSIS_3,
        DIAGNOSIS HEADER_DIAGNOSIS_4,
        DIAGNOSIS HEADER_DIAGNOSIS_5,
        DIAGNOSIS HEADER_DIAGNOSIS_6,
        DIAGNOSIS HEADER_DIAGNOSIS_7,
        DIAGNOSIS HEADER_DIAGNOSIS_8,
        DIAGNOSIS HEADER_DIAGNOSIS_9,
        DRG DRG,
        FACILITY_CLAIM FACILITY_CLAIM,
        GEOGRAPHIC MEMBER_GEOGRAPHIC,
        MEMBER_COVERAGE MEMBER_COVERAGE,
        MEMBER_INDIVIDUAL_ID_16466_17 MEMBER_INDIVIDUAL_ID_LIS_17_1,
        PLACE_OF_SERVICE PLACE_OF_SERVICE,
        PROCEDURE HEADER_PROCEDURE_1,
        PROCEDURE HEADER_PROCEDURE_2,
        PROCEDURE HEADER_PROCEDURE_3,
        PROCEDURE HEADER_PROCEDURE_4,
        PROCEDURE HEADER_PROCEDURE_5,
        PROCEDURE HEADER_PROCEDURE_6,
        PROCEDURE SERVICE_PROCEDURE,
        PRODUCT PRODUCT,
        PROVIDER SERVICE_PROVIDER,
        REASON HEADER_REASON,
        REASON SERVICE_REASON,
        REVENUE REVENUE
  WHERE (    FACILITY_CLAIM.HEADER_EARLIEST_DATE BETWEEN '01-Jan-2006' AND '31-Dec-2006'
         AND MEMBER_COVERAGE.INDIVIDUAL_ID = MEMBER_INDIVIDUAL_ID_LIS_17_1.INDIVIDUAL_ID
         AND MEMBER_COVERAGE.PHARMACY_COVERAGE_IND = 'Y'
         AND MEMBER_COVERAGE.MEDICAL_COVERAGE_IND = 'Y'
         AND PRODUCT.BUSINESS_LINE_CODE IN ('COM')
         AND MEMBER_GEOGRAPHIC.CENSUS_DIVISION_CODE != 'UNK'
        )
    AND (    FACILITY_CLAIM.HEADER_DIAGNOSIS_1_KEY = HEADER_DIAGNOSIS_1.DIAGNOSIS_KEY
         AND FACILITY_CLAIM.HEADER_DIAGNOSIS_2_KEY = HEADER_DIAGNOSIS_2.DIAGNOSIS_KEY
         AND FACILITY_CLAIM.HEADER_DIAGNOSIS_3_KEY = HEADER_DIAGNOSIS_3.DIAGNOSIS_KEY
         AND FACILITY_CLAIM.HEADER_DIAGNOSIS_4_KEY = HEADER_DIAGNOSIS_4.DIAGNOSIS_KEY
         AND FACILITY_CLAIM.HEADER_DIAGNOSIS_5_KEY = HEADER_DIAGNOSIS_5.DIAGNOSIS_KEY
         AND FACILITY_CLAIM.HEADER_DIAGNOSIS_6_KEY = HEADER_DIAGNOSIS_6.DIAGNOSIS_KEY
         AND FACILITY_CLAIM.HEADER_DIAGNOSIS_7_KEY = HEADER_DIAGNOSIS_7.DIAGNOSIS_KEY
         AND FACILITY_CLAIM.HEADER_DIAGNOSIS_8_KEY = HEADER_DIAGNOSIS_8.DIAGNOSIS_KEY
         AND FACILITY_CLAIM.HEADER_DIAGNOSIS_9_KEY = HEADER_DIAGNOSIS_9.DIAGNOSIS_KEY
         AND FACILITY_CLAIM.DRG_KEY = DRG.DRG_KEY
         AND FACILITY_CLAIM.MEMBER_GEOGRAPHIC_KEY = MEMBER_GEOGRAPHIC.GEOGRAPHIC_KEY
         AND FACILITY_CLAIM.MEMBER_COVERAGE_KEY = MEMBER_COVERAGE.MEMBER_COVERAGE_KEY
         AND FACILITY_CLAIM.PLACE_OF_SERVICE_KEY = PLACE_OF_SERVICE.PLACE_OF_SERVICE_KEY
         AND FACILITY_CLAIM.HEADER_PROCEDURE_1_KEY = HEADER_PROCEDURE_1.PROCEDURE_KEY
         AND FACILITY_CLAIM.HEADER_PROCEDURE_2_KEY = HEADER_PROCEDURE_2.PROCEDURE_KEY
         AND FACILITY_CLAIM.HEADER_PROCEDURE_3_KEY = HEADER_PROCEDURE_3.PROCEDURE_KEY
         AND FACILITY_CLAIM.HEADER_PROCEDURE_4_KEY = HEADER_PROCEDURE_4.PROCEDURE_KEY
         AND FACILITY_CLAIM.HEADER_PROCEDURE_5_KEY = HEADER_PROCEDURE_5.PROCEDURE_KEY
         AND FACILITY_CLAIM.HEADER_PROCEDURE_6_KEY = HEADER_PROCEDURE_6.PROCEDURE_KEY
         AND FACILITY_CLAIM.SERVICE_PROCEDURE_KEY = SERVICE_PROCEDURE.PROCEDURE_KEY
         AND FACILITY_CLAIM.PRODUCT_KEY = PRODUCT.PRODUCT_KEY
         AND FACILITY_CLAIM.PROVIDER_KEY = SERVICE_PROVIDER.PROVIDER_KEY
         AND FACILITY_CLAIM.HEADER_REASON_KEY = HEADER_REASON.REASON_KEY
         AND FACILITY_CLAIM.SERVICE_REASON_KEY = SERVICE_REASON.REASON_KEY
         AND FACILITY_CLAIM.REVENUE_KEY = REVENUE.REVENUE_KEY
        )


Explain Plan for point 3:

| Id  | Operation                                         |  Name                          | Rows  | Bytes |TempSpc| Cost  |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                  |                                |    46G| 31T|          |    91M|
|*  1 |  HASH JOIN                                        |                                |    46G| 31T|  2347G|       91M|
|   2 |   TABLE ACCESS FULL                               | PROCEDURE                      |   160K|  2190K|       |    37 |
|*  3 |   HASH JOIN                                       |                                |    27G| 18T|  1364G|       53M|
|   4 |    TABLE ACCESS FULL                              | PROCEDURE                      |   160K|  2190K|       |    37 |
|*  5 |    HASH JOIN                                      |                                |    16G| 10T|   793G|       31M|
|   6 |     TABLE ACCESS FULL                             | PROCEDURE                      |   160K|  2190K|       |    37 |
|*  7 |     HASH JOIN                                     |                                |  9679M|  6238G|   460G|    18M|
|   8 |      TABLE ACCESS FULL                            | PROCEDURE                      |   160K|  2190K|       |    37 |
|*  9 |      HASH JOIN                                    |                                |  5738M|  3623G|   267G|    10M|
|  10 |       TABLE ACCESS FULL                           | PROCEDURE                      |   160K|  2190K|       |    37 |
|* 11 |       HASH JOIN                                   |                                |  3401M|  2103G|   155G|  6458K|
|  12 |        TABLE ACCESS FULL                          | PROCEDURE                      |   160K|  2190K|       |    37 |
|* 13 |        HASH JOIN                                  |                                |  2016M|  1220G|    89G|  3922K|
|  14 |         TABLE ACCESS FULL                         | PROCEDURE                      |   160K|  3128K|       |    37 |
|* 15 |         HASH JOIN                                 |                                |  1195M|   701G|    54G|  2464K|
|  16 |          TABLE ACCESS FULL                        | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 17 |          HASH JOIN                                |                                |   751M|   431G|    33G|  1568K|
|  18 |           TABLE ACCESS FULL                       | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 19 |           HASH JOIN                               |                                |   472M|   264G|    20G|  1017K|
|  20 |            TABLE ACCESS FULL                      | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 21 |            HASH JOIN                              |                                |   297M|   162G|    12G|   678K|
|  22 |             TABLE ACCESS FULL                     | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 23 |             HASH JOIN                             |                                |   186M| 99G|  8010M|   470K|
|  24 |              TABLE ACCESS FULL                    | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 25 |              HASH JOIN                            |                                |   117M| 61G|  4913M|   342K|
|  26 |               TABLE ACCESS FULL                   | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 27 |               HASH JOIN                           |                                |    73M| 37G|  3011M|   264K|
|  28 |                TABLE ACCESS FULL                  | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 29 |                HASH JOIN                          |                                |    46M| 23G|  1844M|   216K|
|  30 |                 TABLE ACCESS FULL                 | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 31 |                 HASH JOIN                         |                                |    29M| 14G|  1129M|   186K|
|  32 |                  TABLE ACCESS FULL                | DIAGNOSIS                      |   113K|  1554K|       |    33 |
|* 33 |                  HASH JOIN                        |                                |    18M|  8825M|   690M|   168K|
|* 34 |                   TABLE ACCESS FULL               | GEOGRAPHIC                     | 53327 |  1770K|       |    29 |
|* 35 |                   HASH JOIN                       |                                |    12M|  5388M|   667M|   157K|
|  36 |                    TABLE ACCESS FULL              | PLACE_OF_SERVICE               |    45 |   720 |       |     2 |
|* 37 |                    HASH JOIN                      |                                |    12M|  5205M|   649M|   147K|
|  38 |                     TABLE ACCESS FULL             | DRG                            |   668 |  8684 |       |     2 |
|* 39 |                     HASH JOIN                     |                                |    12M|  5056M|   626M|   136K|
|  40 |                      TABLE ACCESS FULL            | REASON                         |  2591 | 41456 |       |     4 |
|* 41 |                      HASH JOIN                    |                                |    12M|  4872M|   603M|   126K|
|  42 |                       TABLE ACCESS FULL           | REASON                         |  2591 | 41456 |       |     4 |
|* 43 |                       HASH JOIN                   |                                |    12M|  4689M|   584M|   117K|
|  44 |                        TABLE ACCESS FULL          | REVENUE                        | 10026 |   127K|       |     3 |
|* 45 |                        HASH JOIN                  |                                |    12M|  4540M|   518M|   107K|
|  46 |                         TABLE ACCESS FULL         | PROVIDER                       |  6504K|   285M|       |  9673 |
|* 47 |                         HASH JOIN                 |                                |    12M|  4012M|   484M| 89341 |
|* 48 |                          TABLE ACCESS FULL        | PRODUCT                        |   191 |  4584 |       |     2 |
|* 49 |                          HASH JOIN                |                                |    12M|  3741M|   106M| 81613 |
|* 50 |                           HASH JOIN               |                                |    15M|   666M|   704M| 24529 |
|  51 |                            TABLE ACCESS FULL      | MEMBER_INDIVIDUAL_ID_16466_17  | 24182 |   141K|       |     4 |
|* 52 |                            TABLE ACCESS FULL      | MEMBER_COVERAGE                |    73M|  2676M|       | 13303 |
|  53 |                           PARTITION RANGE ITERATOR|                                |       |    |          |       |
|* 54 |                            TABLE ACCESS FULL      | FACILITY_CLAIM                 |    54M| 14G|          | 25189 |
----------------------------------------------------------------------------------------------------------------------------

Execution Plans

Alex, August 23, 2007 - 11:25 am UTC

Hi Tom,

The explain plan is about one page, it just is too wide and it wraps on the page. Unfortunately, the time window for new questions is very seldom available and it was not available at the time I submitted the question.

I mentioned that I would post the query in the next message because of the limited on the size of a post. I posted the query 5 minutes later and it appears in the same thread.

I am not trying to debate that there are many ways to write a query. However, this query is generated by an application, which is in production. The changes to the application are not quick to test and implement. Moreover, the people who developed the application in the first place are long gone from this project.

I know that the question is long, but I wanted to provide you with as much detail as possible. Could you please comment my original questions when you have time?

The main problems in the explain plans are towards the bottom of each plan.

Thanks for your help,

Alex

Execution Plans

Alex, September 04, 2007 - 10:41 am UTC

Tom,

Could you please comment on my questions from a week ago?

Thanks

Tom Kyte
September 05, 2007 - 1:48 pm UTC

not really, I already commented on it. and I take vacations too.



it is very large (and this is a review followup - not "post your queries to have tom tune them")...

it self joins over and over - which is probably un-necessary, you can look into removing that.


rows_processed in v$sql

A reader, September 06, 2007 - 8:49 am UTC

We have one third party application (ODBC) that created a lot of load (LIOs or phys IOs and sorts) on the database.

The code which I can see in the v$sql is something like

select /*+ FIRST_ROWS */ (about 40 columns) from table a
where a=A1 and b=B1 order by c, d, e, f, g desc.

Table has about 500,000 rows and is about 300 MB. The predicates above are not selective at all. Actually 99,99% of rows in the column a has the value A1. The same is for b.

If I catch sql_text from v$sql and execute the query in my session it returns about 499,999 rows, but if I query v$sql I can see the numbers as (25, 50 or 75) as rows_processed !
I never saw something like 77 as row_processed for this module. It always X*25.

I think that application cuts only first 25/50/75 rows on the client side depends on client settings and I can see in the execution plan that the query tends to do either a full index scan of index on (c, d, e, f, g) and then table access by index rowid (with about 500,000 LIOs) or full table scan (after removing first rows hint)with 50,000 LIOs and about 30,000 phys I/Os, and then SORT the results which needs according to autotrace about 800 MB temp space.

My questions:

How it is possible that I always see 25/50/75 for rows_proceed in v$sql in this case and query returns about 500,000 rows if I execute it in other session (the text I catched in v$sql). If clients somehow cut the output, how Oracle knows about this?

Is there any possibility to improve performance of this query on the server side? Again both execution plans with or without hint are very bad - full index scan and rowid table access results in a lot of LIOs and Full table scan results in very expensive sort.

It is not only this query. Actually all queries from this ODBC application have first_rows hint, have order by, have an index exactly matching order by and a lot of queries seems to select a lot of rows and then cut the result.
Tom Kyte
September 11, 2007 - 8:06 am UTC

if the client fetches 25 rows at a time (array fetches), you would see 25 row multiples all of the time - sure. Oracle "knows" about this because Oracle is told to fetch N rows at a time.


A query that is executed A LOT and returns 1/2 million rows to a client application - I cannot imagine WHY YOU WOULD DO THAT.

We need to look at the application and ask the application "why the heck do you need to have fetched 1/2 MILLION ROWS - a lot????"

sometimes even Oracle recursive SQL can process 31M rows?

A reader, September 11, 2007 - 5:31 pm UTC

 1* select * from (select sql_text, rows_processed from v$sql order by rows_processed desc ) where rownum=1
14:23:14 SQL> /

SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ROWS_PROCESSED
--------------
select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0
      31348885


14:23:15 SQL> 

Tom Kyte
September 15, 2007 - 4:06 pm UTC

that is since the database has been started and is run in the background by SMON - sure, sometimes even Oracle can process lots of data.

that is looking for dictionary managed tablespace to perform free space coalescing on.


switch over the Locally managed and it'll be very different:

ops$tkyte%ORA10GR2> select sql_text, executions, rows_processed from v$sql where sql_text like 'select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t%';

SQL_TEXT
-------------------------------------------------------------------------------
EXECUTIONS ROWS_PROCESSED
---------- --------------
select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where t.ts#=f.ts#
and t.dflextpct!=0 and t.bitmapped=0
       338              0


one question

A reader, November 05, 2007 - 8:58 am UTC

Hi Tom,

if the explain plan of a query shows diffrent "card" than the card or rows shown in the tkprof of the same query then what should be done .

Thanks


Tom Kyte
November 06, 2007 - 9:14 am UTC

try to think about "why it might be so" and what you can do about it.

maybe you have a predicate like:

where month = 'Dec' and zodiac_sign = 'Pisces'

well, the database can figure out how many rows have Dec (say 1/12th do)
the database can figure out how many rows have Pisces (again, say 1/12th do)

but it cannot figure out how many Pisces are in December (answer = ZERO, we are all born in Feb or Mar)

so, in that case, dynamic sampling (search for that on this site for more information) might be what is needed.

could be you are missing histograms

could be you are missing/have stale statistics

in any case, it leads you to "where something is going wrong", and we use our knowledge to figure out "why"

why autotrace also some time differs the card

A reader, November 07, 2007 - 1:35 am UTC

Thanks Tom,

I have 2 more question -

1. why sometime even the values of cards shown in the expalin plan of autotrace defere with the rows shown in tkprof of the same query - beaause in the autottrace the query actually gets executed so should not it match with the tkprof results.

2. is it possible that rows in explain plan of tkprof would not match with the row source operations output of tkprof.I guess - it will not.

Thanks
Tom Kyte
November 07, 2007 - 5:11 pm UTC

1) because autotrace is just showing an explain plan, nothing more.

2) of course it is possible, not only possible but highly PROBABLE. Explain Plan = the guess as to what will happen. Row Source Operation = What ACTUALLY happened.

when reality diverges from the guess by large amounts, that is when you get "bad plans" and that is our flag as to "why"

why autotrace also some time differs the card

A reader, November 07, 2007 - 1:35 am UTC

Thanks Tom,

I have 2 more question -

1. why sometime even the values of cards shown in the expalin plan of autotrace defere with the rows shown in tkprof of the same query - beaause in the autottrace the query actually gets executed so should not it match with the tkprof results.

2. is it possible that rows in explain plan of tkprof would not match with the row source operations output of tkprof.I guess - it will not.

Thanks

This query

A reader, November 08, 2007 - 8:04 am UTC

Hi Tom,

I have been trying since 5 hours on this query ,it runs forever, i can see the tkprof that it is taking a wrong path , but don't know how to fix.tried many things did'nt work.can you please take a look.

here is the query -

select count(*)
from
(
SELECT
(porh.segment1 || '-' || '0' || '-' || porl.line_num
) order_num,
0 release_num, 0 revision_num,
SUBSTR (porh.type_lookup_code, 1, 1) order_type,
'internal' vendor_num, NULL vendor_site_code,
porl.line_num line_num, msi.segment1 item_num,
TO_CHAR (porl.need_by_date, 'yyyymmdd') need_by_date,
NULL promised_date, ool.order_quantity_uom uom,
( NVL (ool.ordered_quantity, 0)
- NVL (ool.cancelled_quantity, 0)
- NVL (ool.shipped_quantity, 0)
) quantity,
porl.destination_organization_id destination_organization_id
FROM po.po_requisition_headers_all porh,
po.po_requisition_lines_all porl,
inv.mtl_system_items_b msi,
ont.oe_order_headers_all ooh,
ont.oe_order_lines_all ool
WHERE porh.requisition_header_id = porl.requisition_header_id
AND porh.authorization_status = 'APPROVED'
AND porh.type_lookup_code = 'INTERNAL'
-- and nvl(porh.AUTHORIZATION_STATUS,'XXX') <> 'INCOMPLETE'
AND msi.inventory_item_id = porl.item_id
AND msi.organization_id = porl.destination_organization_id
AND msi.organization_id IN (select organization_id from
(SELECT DISTINCT loc.location_id, REF.organization_code,
REF.organization_id
FROM gems_inv.gems_inv_wr_cross_ref REF,
apps.hr_locations_all loc
WHERE REF.ship_to_location = loc.location_code
AND NVL (REF.enabled_flag, 'Y') = 'Y' and rownum < 5 )
)
AND ooh.source_document_id = porh.requisition_header_id
AND ooh.order_source_id = 10
AND ool.header_id = ooh.header_id
AND ooh.source_document_id = porh.requisition_header_id
AND ( NVL (ool.ordered_quantity, 0)
- NVL (ool.cancelled_quantity, 0)
- NVL (ool.shipped_quantity, 0)
) > 0
);
and below is the o/p from tkprof :

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 SORT (AGGREGATE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'OE_ORDER_LINES_ALL'
1 NESTED LOOPS
0 HASH JOIN (SEMI)
870174 NESTED LOOPS
870174 NESTED LOOPS
130034 HASH JOIN
153870 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'OE_ORDER_HEADERS_ALL'
153870 INDEX GOAL: ANALYZED (FULL SCAN) OF 'GEMS_OE_ORDER_HEADERS_ALL_N99' (NON-UNIQUE)
131922 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PO_REQUISITION_HEADERS_ALL'
870174 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'PO_REQUISITION_LINES_ALL'
870174 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PO_REQUISITION_LINES_U2' (UNIQUE)
870174 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'MTL_SYSTEM_ITEMS_B_U1' (UNIQUE)
1 VIEW OF 'VW_NSO_1'
1 VIEW
1 SORT (UNIQUE)
4 COUNT (STOPKEY)
4 HASH JOIN
3827 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'GEMS_INV_WR_CROSS_REF'
31209 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'HR_LOCATIONS_ALL'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'OE_ORDER_LINES_N1' (NON-UNIQUE)

Tom Kyte
November 08, 2007 - 10:12 am UTC

how do you see it took the wrong path.

should not it access the

A reader, November 08, 2007 - 10:18 am UTC

should not it access INDEX GOAL: ANALYZED (RANGE SCAN) OF 'OE_ORDER_LINES_N1' (NON-UNIQUE)
and so the OE_ORDER_LINES table first , this is what I thought.
I have asked this as a new question as well as I got an opprtunity to post a new question.

regards
Tom Kyte
November 09, 2007 - 11:48 am UTC

how could it do that. If the query was driven by that table, then that index would likely be useless - since you are not JOINING to that table anymore, but using it as the driver.

A reader, May 21, 2008 - 10:36 am UTC

Tom,

An application user complains about very bad resonse time of her query. I catch both sql statement and execution plan (which you can see below as the first plan) and saw that indeed her query did about 17 Mio LIO and needs about 20 Minutes to complete.

The very strange thing is that she is the only one user who has this problem. All other users working with this application did not complain about this problem. As I catch the sql code and ran it from my sqlplus session I got back the results in 2 seconds and optimizer chosen another execution plan (see the second plan). Application do not use binds and the "optimizer environment" is same (see select statement).

My questions:

1.What couse oracle to choose this wrong plan for one user only. And why oracle is so wrong with INDEX RANGE SCAN of SYS_C006324. Oracle has all statistics incusive histogramms. Optimizer estimated that this scan will return 1 (0?) rows. It returned 816 (the table itself is about 6000 rows).

2. In my session I can see a big differens in estimated and actual rows as well but the plan is not so bad, I got a result in few seconds. What could be the resons for optimizer to choose another execution plan. Again, optimizer env seems to be exactly the same for both sessions.

3. How can I avoid that the optimizer choosing the first very bad plan. This is pretty big issue for us - the user simple could not work with the application!




--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows Buffers |
--------------------------------------------------------------------------------------------------
| 1 | SORT ORDER BY | | 1 | 1 | 79 | 176M|
| 2 | HASH UNIQUE | | 1 | 1 | 79 | 176M|
|* 3 | FILTER | | 1 | | 252 | 176M|
| 4 | HASH GROUP BY | | 1 | 1 | 564 | 176M|
|* 5 | TABLE ACCESS BY INDEX ROWID | PERS | 1 | 1 | 1986 | 176M|
| 6 | NESTED LOOPS | | 1 | 1 | 173M| 176M|
| 7 | MERGE JOIN CARTESIAN | | 1 | 1 | 173M| 1524 |
| 8 | MERGE JOIN CARTESIAN | | 1 | 1 | 34398 | 1499 |
| 9 | MERGE JOIN CARTESIAN | | 1 | 1 | 702 | 1498 |
| 10 | INDEX FULL SCAN | SYS_C00367549 | 1 | 3 | 3 | 1 |
| 11 | BUFFER SORT | | 3 | 1 | 702 | 1497 |
|* 12 | TABLE ACCESS BY INDEX ROWID| NOAB | 1 | 1 | 234 | 1497 |
|* 13 | INDEX RANGE SCAN | SYS_C006324 | 1 | 1 | 816 | 895 |
| 14 | BUFFER SORT | | 702 | 49 | 34398 | 1 |
| 15 | INDEX FULL SCAN | SYS_C00367545 | 1 | 49 | 49 | 1 |
| 16 | BUFFER SORT | | 34398 | 4553 | 173M| 25 |
|* 17 | INDEX RANGE SCAN | SYS_C006365 | 1 | 4553 | 5031 | 25 |
|* 18 | INDEX RANGE SCAN | SYS_C006365 | 173M| 4 | 634K| 176M|
-------------------------------------------------------------------------------------------------

12 - filter("A"."IKORRKZ"=0)
13 - access("A"."SMAND"='010' AND "A"."SWERK"='0001' AND "A"."LDATUM">=149140 AND "A"."LDATUM"<=149170)
filter(("A"."LDATUM">=149140 AND "A"."LDATUM"<=149170))

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------------------------
| 1 | SORT ORDER BY | | 1 | 38 | 79 |
| 2 | HASH UNIQUE | | 1 | 38 | 79 |
|* 3 | FILTER | | 1 | | 252 |
| 4 | HASH GROUP BY | | 1 | 38 | 564 |
| 5 | CONCATENATION | | 1 | | 1986 |
|* 6 | HASH JOIN | | 1 | 12256 | 0 |
|* 7 | INDEX FAST FULL SCAN | SYS_C006365 | 1 | 4539 | 5031 |
| 8 | MERGE JOIN CARTESIAN | | 1 | 3983 | 0 |
| 9 | NESTED LOOPS | | 1 | 10 | 0 |
|* 10 | HASH JOIN | | 1 | 108 | 865 |
|* 11 | TABLE ACCESS FULL | NOAB | 1 | 27 | 233 |
|* 12 | TABLE ACCESS FULL | PERS | 1 | 4539 | 5031 |
|* 13 | INDEX UNIQUE SCAN | SYS_C00367549 | 865 | 1 | 0 |
| 15 | TABLE ACCESS FULL | ABE50K14915324599 | 0 | 409 | 0 |
| 16 | NESTED LOOPS | | 1 | 1172 | 1869 |
| 17 | NESTED LOOPS | | 1 | 3 | 623 |
| 18 | NESTED LOOPS | | 1 | 1 | 168 |
|* 19 | HASH JOIN | | 1 | 39 | 1013 |
| 20 | TABLE ACCESS FULL | ABE50K14915324599 | 1 | 409 | 49 |
|* 21 | TABLE ACCESS FULL | PERS | 1 | 4539 | 5031 |
|* 22 | TABLE ACCESS BY INDEX ROWID| NOAB | 1013 | 1 | 168 |
|* 23 | INDEX RANGE SCAN | SYS_C006324 | 1013 | 1 | 605 |
|* 24 | INDEX RANGE SCAN | SYS_C006365 | 168 | 3 | 623 |
|* 25 | TABLE ACCESS FULL | ABE50Z14915324599 | 623 | 409 | 1869 |
| 26 | NESTED LOOPS | | 1 | 1172 | 117 |
| 27 | NESTED LOOPS | | 1 | 3 | 39 |
| 28 | NESTED LOOPS | | 1 | 1 | 20 |
|* 29 | HASH JOIN | | 1 | 39 | 95 |
| 30 | TABLE ACCESS FULL | ABE50K14915324599 | 1 | 409 | 49 |
|* 31 | TABLE ACCESS FULL | PERS | 1 | 4539 | 5031 |
|* 32 | TABLE ACCESS BY INDEX ROWID| NOAB | 95 | 1 | 20 |
|* 33 | INDEX RANGE SCAN | SYS_C006324 | 95 | 1 | 114 |
|* 34 | INDEX RANGE SCAN | SYS_C006365 | 20 | 3 | 39 |
|* 35 | TABLE ACCESS FULL | ABE50Z14915324599 | 39 | 409 | 117 |
--------------------------------------------------------------------------------------------

select * from v$ses_optimizer_env where sid like '233';
      SID        ID NAME                                     ISD VALUE
---------- ---------- ---------------------------------------- --- -------------------------
       233          2 parallel_execution_enabled               YES false
       233          9 optimizer_features_enable                YES 10.2.0.3
       233         11 cpu_count                                YES 8
       233         12 active_instance_count                    YES 1
       233         13 parallel_threads_per_cpu                 YES 2
       233         14 hash_area_size                           YES 131072
       233         15 bitmap_merge_area_size                   YES 1048576
       233         16 sort_area_size                           YES 65536
       233         17 sort_area_retained_size                  YES 0
       233         24 pga_aggregate_target                     YES 524288 KB
       233         35 parallel_query_mode                      YES enabled
       233         36 parallel_dml_mode                        YES disabled
       233         37 parallel_ddl_mode                        YES enabled
       233         38 optimizer_mode                           YES all_rows
       233         48 cursor_sharing                           YES exact
       233         50 star_transformation_enabled              YES false
       233         66 optimizer_index_cost_adj                 NO  50


Tom Kyte
May 21, 2008 - 12:06 pm UTC

given i have no clue what tables are being accessed by the various filters and we don't actually see a query - well, no comment.

A reader, May 21, 2008 - 12:31 pm UTC

sorry....

select distinct A.SMAND,A.SWERK,A.SPERS,A.LDATUM*1.0 LDATUM,A.IZAEHLER*1.0 IZAEHLER,A.SBSL,A.LNORMABW*1.0 LNORMABW,A.IAZBZAEHLER*1.0 IAZBZAEHLER,A.IKORRKZ*1.0 IKORRKZ,A.STEXT STEXTNOAB,' ' STEXT,P.SMAND,P.SWERK,P.SPERS,P.LGUELTDAT*1.0 LGUELTDAT,P.SISTKSTMAND,P.SISTKSTWERK,
P.SISTABT,P.SISTKST,P.SISTARBPL,P.SSTAMMABT,P.SSTAMMKSTMAND,P.SSTAMMKSTWERK,P.SSTAMMKST,P.SSTAMMARBPL,P.SNAME,P.SVORNAME,' ' SGRP
FROM NOAB A, PERS P, ABE50K14915324599 K1 , ABE50Z14915324599 p1
where
( (P.SMAND='010'))
and
( (P.SWERK='0001'))
and
((A.LDATUM>=149140 AND A.LDATUM<=149170))
and
A.SMAND=P.SMAND AND A.SWERK=P.SWERK AND A.SPERS=P.SPERS AND A.IKORRKZ=0 AND
P.LGUELTDAT=(SELECT MAX(LGUELTDAT) FROM PERS Z
WHERE Z.LGUELTDAT<=149153
AND Z.SMAND=P.SMAND AND Z.SWERK=P.SWERK AND Z.SPERS=P.SPERS) AND (((P.SSTAMMKSTMAND= k1.smand007) AND (P.SSTAMMKSTWERK= k1.swerk007) AND (P.SSTAMMABT= k1.sabt007) AND (P.SSTAMMKST= k1.skst007)) OR ((P.SISTKSTMAND= k1.smand007) AND (P.SISTKSTWERK= k1.swerk007) AND (P.SISTABT= k1.s
abt007) AND (P.SISTKST= k1.skst007)) OR ((P.SMAND= p1.smand007) AND (P.SWERK= p1.swerk007) AND (P.SPERS= p1.spers007)))

ORDER BY 1 ,2 ,3 ,4

Predicate information for the first plan:

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("P"."LGUELTDAT"=MAX("LGUELTDAT"))
5 - filter((("P"."SSTAMMKSTMAND"="K1"."SMAND007" AND "P"."SSTAMMKSTWERK"="K1"."SWERK007" AND "P"."SSTAMMABT"="K1"."SABT007" AND "P"."SSTAMMKST"="K1"."SKST007") OR
("P"."SISTKSTMAND"="K1"."SMAND007" AND "P"."SISTKSTWERK"="K1"."SWERK007" AND "P"."SISTABT"="K1"."SABT007" AND "P"."SISTKST"="K1"."SKST007") OR ("P"."SMAND"="P1"."SMAND007"
AND "P"."SWERK"="P1"."SWERK007" AND "P"."SPERS"="P1"."SPERS007")))
12 - filter("A"."IKORRKZ"=0)
13 - access("A"."SMAND"='010' AND "A"."SWERK"='0001' AND "A"."LDATUM">=149140 AND "A"."LDATUM"<=149170)
filter(("A"."LDATUM">=149140 AND "A"."LDATUM"<=149170))
17 - access("Z"."SMAND"='010' AND "Z"."SWERK"='0001' AND "Z"."LGUELTDAT"<=149153)
filter("Z"."LGUELTDAT"<=149153)
18 - access("P"."SMAND"='010' AND "P"."SWERK"='0001' AND "Z"."SPERS"="P"."SPERS")
filter("A"."SPERS"="P"."SPERS")

predicate information for the second plan:

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("P"."LGUELTDAT"=MAX("LGUELTDAT"))
6 - access("Z"."SMAND"="P"."SMAND" AND "Z"."SWERK"="P"."SWERK" AND "Z"."SPERS"="P"."SPERS")
7 - filter(("Z"."SMAND"='010' AND "Z"."SWERK"='0001' AND "Z"."LGUELTDAT"<=149153))
10 - access("A"."SMAND"="P"."SMAND" AND "A"."SWERK"="P"."SWERK" AND "A"."SPERS"="P"."SPERS")
11 - filter(("A"."LDATUM">=149140 AND "A"."IKORRKZ"=0 AND "A"."SMAND"='010' AND "A"."SWERK"='0001' AND "A"."LDATUM"<=149170))
12 - filter(("P"."SMAND"='010' AND "P"."SWERK"='0001'))
13 - access("P"."SMAND"="P1"."SMAND007" AND "P"."SWERK"="P1"."SWERK007" AND "P"."SPERS"="P1"."SPERS007")
19 - access("P"."SISTKSTMAND"="K1"."SMAND007" AND "P"."SISTKSTWERK"="K1"."SWERK007" AND "P"."SISTABT"="K1"."SABT007" AND
"P"."SISTKST"="K1"."SKST007")
21 - filter(("P"."SMAND"='010' AND "P"."SWERK"='0001'))
22 - filter("A"."IKORRKZ"=0)
23 - access("A"."SMAND"='010' AND "A"."SWERK"='0001' AND "A"."SPERS"="P"."SPERS" AND "A"."LDATUM">=149140 AND "A"."LDATUM"<=149170)
24 - access("Z"."SMAND"='010' AND "Z"."SWERK"='0001' AND "Z"."SPERS"="P"."SPERS" AND "Z"."LGUELTDAT"<=149153)
25 - filter((LNNVL("P"."SMAND"="P1"."SMAND007") OR LNNVL("P"."SWERK"="P1"."SWERK007") OR LNNVL("P"."SPERS"="P1"."SPERS007")))
29 - access("P"."SSTAMMKSTMAND"="K1"."SMAND007" AND "P"."SSTAMMKSTWERK"="K1"."SWERK007" AND "P"."SSTAMMABT"="K1"."SABT007" AND
"P"."SSTAMMKST"="K1"."SKST007")
filter((LNNVL("P"."SISTKSTMAND"="K1"."SMAND007") OR LNNVL("P"."SISTKSTWERK"="K1"."SWERK007") OR
LNNVL("P"."SISTABT"="K1"."SABT007") OR LNNVL("P"."SISTKST"="K1"."SKST007")))
31 - filter(("P"."SMAND"='010' AND "P"."SWERK"='0001'))
32 - filter("A"."IKORRKZ"=0)
33 - access("A"."SMAND"='010' AND "A"."SWERK"='0001' AND "A"."SPERS"="P"."SPERS" AND "A"."LDATUM">=149140 AND "A"."LDATUM"<=149170)
34 - access("Z"."SMAND"='010' AND "Z"."SWERK"='0001' AND "Z"."SPERS"="P"."SPERS" AND "Z"."LGUELTDAT"<=149153)
35 - filter((LNNVL("P"."SMAND"="P1"."SMAND007") OR LNNVL("P"."SWERK"="P1"."SWERK007") OR LNNVL("P"."SPERS"="P1"."SPERS007")))







A reader, May 23, 2008 - 10:29 am UTC

Tom,

I would really appreciate if you can answer my question above or give me any idea what kind of solution I should looking for (I added the query code and predicates info for my original question - sorry again for not doing this as I asked the question for the first time).
This is really important issue for us (the user is not able to work with the application) and I already spent a lot of time trying to solve it but still have no solution.


Tom Kyte
May 23, 2008 - 6:17 pm UTC

frankly - no - i cannot - I don't even know what I'm looking at there - good query, bad query - what the tables are - and I don't think we can get from here to there. This is a rather "large" query. This section is for review followup. One would need to reverse engineer a rather large query against many tables with many more indexes.

Merge Join Cartesian but cant see why...

Jason Shannon, November 17, 2008 - 11:03 am UTC

Tom

The following query is used to generate a report. It has not changed in a number of years. Recently the optimizer has started choosing a plan with a merge join cartesian (see below). Stats are gathered with histograms every week against all tables in the schema. I have checked them and they are accurate. The query in its present state is ¿unusable¿ as after 1½ hours it fails having blown the temp tablespace (13GB) where previously it completed execution in 7 seconds. 

Other than using an /*+ ordered */ hint to force it down a different path (see below also). Are there any suggestions you have to remove the merge join cartesian? I am exploring the option of setting optimizer_index_caching = 80 and optimizer_index_cost_adj = 10 but am nervous as to what effect there may have on other queries and they amount of testing this would involve

The database is on 8.1.7.4 ¿ I know it¿s old but there is a project on going to replace the system with e-biz and until then my hands are tied.

SQL> set autotrace traceonly explain;
SQL> SELECT   os_version.osno, osnumber.organisation_id,
  2           apps_ra_customers.customer_name,
  3           moses.getstudyno_osno (os_version.osno) AS protocol,
  4           os_price_list.os_price_list_id,
  5           os_price_list.description AS price_list_description,
  6           os_deliverable.deliverable_value, os_deliverable.deliverable_comment
  7           os_deliverable.os_deliverable_id, os_deliverable.date_entered,
  8           os_deliverable.user_name,
  9           CASE
 10              WHEN osnumber.organisation_id = 2
 11                 THEN 'Pounds Sterling'
 12              ELSE 'US Dollars'
 13           END AS currency
 14      FROM moses.os_version,
 15           moses.os_deliverable,
 16           moses.os_price_list,
 17           moses.osnumber,
 18           moses.apps_ra_customers
 19     WHERE osnumber.osno = os_version.osno
 20       AND os_deliverable.os_version_id = os_version.os_version_id
 21       AND os_price_list.os_price_list_id = os_deliverable.os_price_list_id
 22       AND os_deliverable.invoice_number IS NULL
 23       AND os_price_list.nbti = 0
 24       AND apps_ra_customers.customer_id = osnumber.customer_id
 25  ORDER BY osnumber.osno;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=236 Card=34 Bytes=6868)
   1    0   SORT (ORDER BY) (Cost=236 Card=34 Bytes=6868)
   2    1     HASH JOIN (Cost=233 Card=34 Bytes=6868)
   3    2       HASH JOIN (Cost=157 Card=145 Bytes=18270)
   4    3         MERGE JOIN (CARTESIAN) (Cost=151 Card=145 Bytes=12470)
   5    4           HASH JOIN (Cost=129 Card=1 Bytes=19)
   6    5             INDEX (FAST FULL SCAN) OF 'IDX_OSNO_OSVERSION_ID' (UNIQUE) (Cost=8 Card=16103 Bytes=128824)
   7    5             TABLE ACCESS (FULL) OF 'OSNUMBER' (Cost=45 Card=15864 Bytes=174504)
   8    4           SORT (JOIN) (Cost=143 Card=145 Bytes=9715)
   9    8             TABLE ACCESS (FULL) OF 'OS_PRICE_LIST' (Cost=22Card=145 Bytes=9715)
  10    3         REMOTE* (Cost=5 Card=1370 Bytes=54800) LNK_FINANCE.CTS
  11    2       TABLE ACCESS (FULL) OF 'OS_DELIVERABLE' (Cost=74 Card=2363 Bytes=179588)
  10 SERIAL_FROM_REMOTE            SELECT "CUSTOMER_ID","CUSTOMER_NAME" FROM "BOLINF"."APPS_RA_CUSTOMERS" "APPS_RA_

SQL>


SQL> SELECT   /*+ordered */ os_version.osno, osnumber.organisation_id,
  2           apps_ra_customers.customer_name,
  3           moses.getstudyno_osno (os_version.osno) AS protocol,
  4           os_price_list.os_price_list_id,
  5           os_price_list.description AS price_list_description,
  6           os_deliverable.deliverable_value, os_deliverable.deliverable_comment,
  7           os_deliverable.os_deliverable_id, os_deliverable.date_entered,
  8           os_deliverable.user_name,
  9           CASE
 10              WHEN osnumber.organisation_id = 2
 11                 THEN 'Pounds Sterling'
 12              ELSE 'US Dollars'
 13           END AS currency
 14      FROM moses.os_version,
 15           moses.os_deliverable,
 16           moses.os_price_list,
 17           moses.osnumber,
 18           moses.apps_ra_customers
 19     WHERE osnumber.osno = os_version.osno
 20       AND os_deliverable.os_version_id = os_version.os_version_id
 21       AND os_price_list.os_price_list_id = os_deliverable.os_price_list_id
 22       AND os_deliverable.invoice_number IS NULL
 23       AND os_price_list.nbti = 0
 24       AND apps_ra_customers.customer_id = osnumber.customer_id
 25  ORDER BY osnumber.osno;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=14529 Card=35 Bytes=7070)
   1    0   SORT (ORDER BY) (Cost=14529 Card=35 Bytes=7070)
   2    1     HASH JOIN (Cost=14526 Card=35 Bytes=7070)
   3    2       HASH JOIN (Cost=14520 Card=35 Bytes=5670)
   4    3         TABLE ACCESS (FULL) OF 'OSNUMBER' (Cost=45 Card=15864 Bytes=174504)
   5    3         HASH JOIN (Cost=413 Card=551745 Bytes=83313495)
   6    5           TABLE ACCESS (FULL) OF 'OS_PRICE_LIST' (Cost=22 Card=145 Bytes=9715)
   7    5           HASH JOIN (Cost=136 Card=380514 Bytes=31963176)
   8    7             INDEX (FAST FULL SCAN) OF 'IDX_OSNO_OSVERSION_ID' (UNIQUE) (Cost=8 Card=16103 Bytes=128824)
   9    7             TABLE ACCESS (FULL) OF 'OS_DELIVERABLE' (Cost=74Card=2363 Bytes=179588)
  10    2       REMOTE* (Cost=5 Card=1370 Bytes=54800)   LNK_FINANCE.CTS
  10 SERIAL_FROM_REMOTE            SELECT "CUSTOMER_ID","CUSTOMER_NAME" FROM "BOLINF"."APPS_RA_CUSTOMERS" "APPS_RA_


SQL>

Tom Kyte
November 18, 2008 - 7:24 pm UTC

...
5 4 HASH JOIN (Cost=129 Card=1 Bytes=19)
...

it thinks "one row" - is that what actually happens?

this is WHY it uses a cartesian join (it thinks one row)

Now we have to understand why it thinks one row - look at ALL OF THE ESTIMATED card=values, are they accurate or near accurate or way off

Cardinality = 1

Jason Shannon, November 21, 2008 - 8:13 am UTC

Tom

I have changed the query to be just the 2 tables where the cardinality = 1.

SQL> set autotrace traceonly explain;
SQL> SELECT os_version.osno, osnumber.organisation_id,
  2         moses.getstudyno_osno (os_version.osno) AS protocol,
  3           CASE
  4              WHEN osnumber.organisation_id = 2
  5                 THEN 'Pounds Sterling'
  6              ELSE 'US Dollars'
  7           END AS currency
  8      FROM moses.os_version,
  9           moses.osnumber
 10     WHERE osnumber.osno = os_version.osno
 11  ORDER BY osnumber.osno;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=111 Card=1 Bytes=11)
   1    0   SORT (ORDER BY) (Cost=111 Card=1 Bytes=11)
   2    1     HASH JOIN (Cost=108 Card=1 Bytes=11)
   3    2       INDEX (FAST FULL SCAN) OF 'IDX_OSNO_OSVERSION_ID' (UNIQUE) (Cost=8 Card=16210 Bytes=64840)
   4    2       TABLE ACCESS (FULL) OF 'OSNUMBER' (Cost=45 Card=15970 Bytes=111790)


SQL> 

SQL> select count(*) from (
  2  SELECT os_version.osno, osnumber.organisation_id,
  3           moses.getstudyno_osno (os_version.osno) AS protocol,
  4           CASE
  5              WHEN osnumber.organisation_id = 2
  6                 THEN 'Pounds Sterling'
  7              ELSE 'US Dollars'
  8           END AS currency
  9      FROM moses.os_version,
 10            moses.osnumber
 11     WHERE osnumber.osno = os_version.osno
 12  ORDER BY osnumber.osno);

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=111 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     VIEW (Cost=111 Card=1)
   3    2       SORT (ORDER BY) (Cost=111 Card=1 Bytes=11)
   4    3         HASH JOIN (Cost=108 Card=1 Bytes=11)
   5    4           INDEX (FAST FULL SCAN) OF 'IDX_OSNO_OSVERSION_ID' (UNIQUE) (Cost=8 Card=16210 Bytes=64840)
   6    4           TABLE ACCESS (FULL) OF 'OSNUMBER' (Cost=45 Card=15970 Bytes=111790)


SQL> set autotrace off;
SQL> /

  COUNT(*)
----------
     16255

SQL>  

So the optimiser thinks there should be 1 row but there's 16255. I would expect 16255 to be returned. The statistics on the table were gathered last saturday and look correct...
 'execute dbms_stats.gather_schema_stats('MOSES',cascade=>true,method_opt=>'for all indexed columns size 254');'


Thanks for any advice you can give.


Tom Kyte
November 24, 2008 - 2:11 pm UTC

why for all indexed columns?


but what can you tell us about osno? can you think of a reason the optimizer might believe that the two sets are so "non-intersecting" as to return basically nothing when joined?

can we see a dbms_xplan output (with predicate information)

Recreated FBI and it removed the MJC

Jason Shannon, November 25, 2008 - 9:57 am UTC

Tom

I noticed that as well as the indexes being used by the query, OS_VERSION also had an FBI on it against the OSNO column.....

CREATE INDEX MOSES.OS_VERSION_OSNO_NU_NIDX ON MOSES.OS_VERSION
("OSNO" DESC)
NOLOGGING
TABLESPACE MOSESX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

When I removed this the plan changed to ......

SQL> set autotrace traceonly explain;
SQL> l
  1  SELECT os_version.osno, osnumber.organisation_id,
  2             moses.getstudyno_osno (os_version.osno) AS protocol,
  3              CASE
  4                 WHEN osnumber.organisation_id = 2
  5                    THEN 'Pounds Sterling'
  6                 ELSE 'US Dollars'
  7              END AS currency
  8         FROM moses.os_version,
  9              moses.osnumber
 10        WHERE osnumber.osno = os_version.osno
 11   --     AND osnumber.osno = 1
 12*   ORDER BY osnumber.osno
SQL> /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=139 Card=16217 Bytes
          =340557)

   1    0   SORT (ORDER BY) (Cost=139 Card=16217 bytes=340557)
   2    1     HASH JOIN (Cost=63 Card=16217 Bytes=340557)
   3    2       INDEX (FAST FULL SCAN) OF 'IDX_OSNO_OSVERSION_ID' (UNI
          QUE) (Cost=7 Card=16218 Bytes=64872)

   4    2       TABLE ACCESS (FULL) OF 'OSNUMBER' (Cost=48 Card=15978
          Bytes=271626)


SQL> 

I then recreated the FBI and then plan stayed the same i.e. the cardinality remained 16217.

I wasnt able to use dbms_xplan ..... didnt this first come in in 9.2?

Should I be gathering the stats for FBI's in a different way to other indexes?

Thanks

Jason

Tom Kyte
November 25, 2008 - 12:40 pm UTC

dbms_xplan was 9.2, I forgot you are on 8.1.7 (which is really sort of old, time to get on some software written this century, a *lot* has changed in the intervening 5 major releases of the software)

Stats Gathering

Jason Shannon, December 01, 2008 - 5:39 am UTC

Tom

Thanks for the advice (I will use it when trying to justify an upgrade to 9.2.0.8, prior to our e-biz implementation).

Just a couple of other things:

You said previously "why for all indexes", can you clarify? Should I not be gathering stats for indexes?

Also, should I be gathering the stats for FBI's differently (if at all)?
Tom Kyte
December 01, 2008 - 8:04 am UTC

I said "why for all INDEXED columns"

why just those columns?
are you sure you need those
are you sure you don't need unindexed ones

why are you using for all indexed columns. Those are many times the columns we don't need histograms on.


suppose your query is:

select *
from t1, t2
where t1.unindexed_column = 55
and t2.unindexed_column = 42
and t1.key = t2.key;


Now.... assume

t1 has 100,000,000 rows
t2 has 100,000,000 rows
unindexed column in both tables has millions of unique values however
t1 has 100 rows such that unindexed_column = 55
t2 has 10,000,000 rows such that unindex_column = 42

so in t2, that column is very skewed... might a histogram on that column be useful in order to help the optimizer know that it should full scan t1 - to find the 100 rows, hash them into memory and then hash join to t2?

without a histogram on that unindexed column - we'd get the estimated cardinality values wrong.

I'm pointing out that "for all indexed columns" is almost always "wrong" - except in examples...


What logic was behind you choosing for all indexed columns?

It pays to read EXACTLY what you write

Jason Shannon, December 01, 2008 - 9:55 am UTC

Sorry Tom,

It pays to read exactly what you write in your responses.

Totally understand your question and example.

Thanks for all your help.

Jason

Testing upgrade to avoid merge join cartesian

Jason Shannon, December 12, 2008 - 12:01 pm UTC

Tom

I'll apologise first - this is in regard to your point about upgrading to one of the 5 major releases since 8.1.7.4. not a merge join cartesian problem.

I have been given the go ahead to upgrade to 9.2.0.8 (and possibly beyond). I have basically cloned disks and presented them to a dev server and will do test upgrade from there. The database is still called CTSINT but I's like to reincarnate it to something like CTSUPG before bringing it on to our network to test.

NID came out in 9.2. but is there a quick way in 8.1.7.4 to rename the database rather recreating control files etc.?

Thanks

Jason
Tom Kyte
December 12, 2008 - 4:10 pm UTC

but the database name won't matter - just upgrade and then nid it if you want.

the global name might be relevant, but the dbid and name would only be 'rman' stuff. And if you nid it after you upgrade it - you are done.

11g Here we come

Jason Shannon, December 12, 2008 - 6:28 pm UTC

Thanks Tom.

Common sense.


What is solution

A, April 22, 2009 - 9:26 am UTC

Hello,
We are getting an error "ORA-01652: unable to extend temp segment by 128 in tablespace TEMP01".

TEMPO1 is a true temporary table and total size is around 8GB and the workarea_size_policy is set to AUTO. This is on Oracle 9i.

Without the ORDER BY clause it runs in 5 secs but with an ORDER BY clause it runs forever and finally throws the error ORA-01652.

The query fetches data over a remote db.

The same query runs (with the ORDER BY clause on LIVE box very well but hangs on test box).


Can you please tell me what could be the problem?

Following is the output of sql trace (of test box)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.10 0 0 1 0
Execute 1 0.01 0.30 0 0 0 0
Fetch 1 125.91 4520.39 3496 0 3005 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 125.95 4520.80 3496 0 3006 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21

Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY
0 HASH JOIN
30882885 MERGE JOIN CARTESIAN
2895 HASH JOIN
4545256 MERGE JOIN CARTESIAN
119612 REMOTE
4545256 BUFFER SORT
38 REMOTE
8 REMOTE
30882885 BUFFER SORT
10669 REMOTE
0 VIEW
0 SORT UNIQUE
0 UNION-ALL
0 REMOTE
0 REMOTE
0 REMOTE


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to dblink 841 0.00 0.00
SQL*Net message from dblink 841 0.30 2.72
SQL*Net message to client 1 0.00 0.00
SQL*Net more data from dblink 1879 0.01 1.63
direct path write 31982 12.44 4385.57
direct path read 233 0.00 0.00
local write wait 4512 0.98 58.18
db file sequential read 1 0.02 0.02
SQL*Net break/reset to client 4 506.46 506.46



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.03 0.10 0 0 1 0
Execute 4 0.01 0.30 0 0 0 0
Fetch 1 125.91 4520.39 3496 0 3005 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 125.95 4520.80 3496 0 3006 0

Misses in library cache during parse: 1

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 1 14.03 14.03
SQL*Net more data from client 1 0.00 0.00
single-task message 1 0.05 0.05
SQL*Net message to dblink 862 0.00 0.00
SQL*Net message from dblink 862 0.30 2.75
SQL*Net more data from dblink 1892 0.01 1.63
direct path write 31982 12.44 4385.57
direct path read 233 0.00 0.00
local write wait 4512 0.98 58.18
db file sequential read 1 0.02 0.02
SQL*Net break/reset to client 4 506.46 506.46
log file sync 1 0.00 0.00


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 6 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 0.00 0.00 0 6 0 1

Misses in library cache during parse: 0

4 user SQL statements in session.
3 internal SQL statements in session.
7 SQL statements in session.
********************************************************************************
Trace file: c:\arsdev01_ora_28463.trc
Trace file compatibility: 9.02.00
Sort options: default

1 session in tracefile.
4 user SQL statements in trace file.
3 internal SQL statements in trace file.
7 SQL statements in trace file.
7 unique SQL statements in trace file.
40501 lines in trace file.

Following is the output of sql trace (of live box) which is executing without any trouble

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.06 0.13 0 16 1 0
Execute 1 0.00 0.02 0 0 0 0
Fetch 1 40.66 79.72 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 40.72 79.89 0 16 1 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 21

Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY
0 HASH JOIN
0 MERGE JOIN CARTESIAN
0 HASH JOIN
11684729 MERGE JOIN CARTESIAN
307493 REMOTE
11684729 BUFFER SORT
38 REMOTE
0 REMOTE
0 BUFFER SORT
0 REMOTE
0 VIEW
0 SORT UNIQUE
0 UNION-ALL
0 REMOTE
0 REMOTE
0 REMOTE


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to dblink 2288 0.00 0.00
SQL*Net message from dblink 2288 0.02 4.40
SQL*Net message to client 1 0.00 0.00
SQL*Net more data from dblink 5552 0.02 3.04
direct path write 5484 0.00 0.06



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.07 0.13 0 16 1 0
Execute 4 0.00 0.02 0 0 0 0
Fetch 1 40.66 79.72 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 40.73 79.89 0 16 1 0

Misses in library cache during parse: 3
Misses in library cache during execute: 1

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 1 8.64 8.64
SQL*Net more data from client 1 0.00 0.00
single-task message 1 0.06 0.06
SQL*Net message to dblink 2309 0.00 0.00
SQL*Net message from dblink 2309 0.02 4.42
SQL*Net more data from dblink 5569 0.02 3.04
direct path write 5484 0.00 0.06
log file sync 1 0.00 0.00


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.01 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 9 0.00 0.00 0 16 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19 0.01 0.01 0 16 0 6

Misses in library cache during parse: 5

4 user SQL statements in session.
5 internal SQL statements in session.
9 SQL statements in session.
********************************************************************************
Trace file: c:\arsdp3_ora_13997.trc
Trace file compatibility: 9.02.00
Sort options: default

1 session in tracefile.
4 user SQL statements in trace file.
5 internal SQL statements in trace file.
9 SQL statements in trace file.
9 unique SQL statements in trace file.
15826 lines in trace file.



Tom Kyte
April 23, 2009 - 12:54 pm UTC

please compare an explain plan with your actuals above, I'll be guessing explain plan thinks "a lot less rows", the explain plan will show the merge join cartesian being "a good idea", the above shows "it is not a good idea"

so, look at that and ask yourself "why is explain plan so far off - missing statistics? invalid statistics? old statistics?"

MERGE JOIN CARTESIAN

Mayank, July 14, 2009 - 4:49 am UTC

Hi Tom,
Please help in Tuning below Query.
Only fault i find in Explain Plan is MERGE JOIN CARTESIAN.
But i dont understand how to remove it and also if you find any other flaws in Query please tell

SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT /*+ push_subq(@subq1,@subq2) */
  2         tab5.product_id, tab5.offer_id, tab5.offer_version_id, tab5.offer_code,
  3         tab5.offer_name, tab5.root_relation_id, tab6.relation_id,
  4         tab6.pcversion_id, tab6.pp_id, tab6.pp_code, tab6.pp_name
  5    FROM (SELECT /*+ no_unnest qb_name(subq1) push_subq(@subq3) */
  6                 rel1.child_id AS product_id,
  7                 rel1.relation_id AS root_relation_id, tab1.offer_id,
  8                 tab1.offer_version_id, tab1.offer_code, tab1.offer_name
  9            FROM (SELECT /*+ no_unnest qb_name(subq3) */
 10                         ct.cid AS offer_id, caption AS offer_code,
 11                         ct.pcversion_id AS offer_version_id,
 12                         offername.name_text AS offer_name
 13                    FROM tbcatalog_item ct, tbname offername
 14                  WHERE ct.item_type = 'OF'
 15                     AND ct.is_deleted = '0'
 16                     AND ct.pcversion_id =
 17                            (SELECT MAX (pcversion_id)
 18                               FROM tbcatalog_item
 19                              WHERE cid = ct.cid AND pcversion_id <= '80112001')
 20                     AND offername.cid = ct.cid
 21                     AND offername.LANGUAGE = 'EN'
 22                     AND offername.pcversion_id = ct.pcversion_id) tab1,
 23                 tbrelation rel1
 24           WHERE rel1.root_cid = tab1.offer_id
 25             AND rel1.child_item_type = 'PR'
 26             AND rel1.is_deleted = '0'
 27             AND rel1.pcversion_id = tab1.offer_version_id) tab5,
 28         (SELECT /*+ no_unnest qb_name(subq2) push_subq(@subq4) */
 29                 parent_id AS pr_co_id, relation_id, pcversion_id, tab1.pp_id,
               tab1.pp_code, tab1.pp_name
 30   31            FROM (SELECT /*+ no_unnest qb_name(subq4) */
 32                         ct.cid AS pp_id, caption AS pp_code,
 33                         ppname.name_text AS pp_name
 34                    FROM tbcatalog_item ct, tbname ppname
 35                   WHERE item_type = 'PP'
 36                     AND ct.is_deleted = '0'
 37                     AND ppname.cid = ct.cid
 38                     AND ppname.LANGUAGE = 'EN'
 39                     AND ppname.pcversion_id = ct.pcversion_id
 40                     AND ct.pcversion_id =
 41                            (SELECT MAX (pcversion_id)
 42                               FROM tbcatalog_item
 43                              WHERE cid = ct.cid AND pcversion_id <= '80112001')) tab1,
 44                 tbrelation rel2
 45           WHERE rel2.child_id = tab1.pp_id
 46             AND rel2.is_deleted = '0'
 47             AND rel2.parent_item_type IN ('PR', 'CO')
 48             AND rel2.pcversion_id =
 49                    (SELECT MAX (pcversion_id)
 50                       FROM tbcatalog_item
 51                      WHERE cid = rel2.parent_id AND pcversion_id <= '80112001')
 52             AND NOT EXISTS (
 53                    SELECT '1'
 54                      FROM tbrefined_relation
 55                     WHERE relation_id = rel2.relation_id
 56                       AND pcversion_id <= '80112001')) tab6
 57   WHERE EXISTS (
 58            SELECT '1'
 59              FROM tbrelation rel
 60             WHERE rel.root_cid = tab5.product_id
 61               AND rel.association_type = 'CN'
 62               AND rel.is_deleted = '0'
 63               AND (rel.child_item_type = 'CO' OR rel.child_item_type = 'PP')
 64               AND rel.pcversion_id =
 65                      (SELECT MAX (pcversion_id)
 66                         FROM tbcatalog_item
 67                        WHERE cid = rel.root_cid AND pcversion_id <= '80112001')
 68               AND (tab6.pr_co_id = rel.parent_id
 69                    OR tab6.pr_co_id = rel.child_id
 70                   ))
 71     AND NOT EXISTS (
 72            SELECT '1'
 73              FROM tbrefined_relation
 74             WHERE refined_relation_id = tab6.relation_id
 75               AND tbrefined_relation.root_relation_id = tab5.root_relation_id
 76               AND tbrefined_relation.pcversion_id = tab5.offer_version_id);

318 rows selected.

Elapsed: 00:05:55.22

Execution Plan
----------------------------------------------------------
Plan hash value: 1298544737

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                        |  4809 |  2080K|  4977   (1)| 00:01:00 |
|   1 |  NESTED LOOPS                          |                        |  2535 |  1096K|  1187   (1)| 00:00:15 |
|   2 |   NESTED LOOPS ANTI                    |                        |  3637 |  1236K|  1151   (1)| 00:00:14 |
|   3 |    NESTED LOOPS                        |                        |  3638 |  1158K|  1150   (1)| 00:00:14 |
|*  4 |     HASH JOIN                          |                        | 22275 |  6199K|   704   (1)| 00:00:09 |
|*  5 |      TABLE ACCESS BY INDEX ROWID       | TBCATALOG_ITEM         |  1783 | 65971 |     2   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN                 | TBCATALOG_ITEM_3IX     |  3566 |       |     1   (0)| 00:00:01 |
|   7 |      MERGE JOIN CARTESIAN              |                        |   311K|    73M|   700   (1)| 00:00:09 |
|   8 |       NESTED LOOPS ANTI                |                        |   132 | 29832 |   559   (1)| 00:00:07 |
|*  9 |        HASH JOIN                       |                        |  1367 |   283K|   545   (1)| 00:00:07 |
|  10 |         VIEW                           | VW_SQ_2                |  2368 | 52096 |     1   (0)| 00:00:01 |
|  11 |          HASH GROUP BY                 |                        |  2368 | 68672 |     1   (0)| 00:00:01 |
|* 12 |           INDEX FULL SCAN              | TBCATALOG_ITEM_PK      | 24963 |   706K|     1   (0)| 00:00:01 |
|* 13 |         TABLE ACCESS BY INDEX ROWID    | TBRELATION             |   162 |  6642 |     5   (0)| 00:00:01 |
|  14 |          NESTED LOOPS                  |                        | 19058 |  3536K|   543   (0)| 00:00:07 |
|  15 |           NESTED LOOPS                 |                        |   118 | 17582 |     5  (20)| 00:00:01 |
|* 16 |            HASH JOIN                   |                        |   169 |  9126 |     3   (0)| 00:00:01 |
|* 17 |             TABLE ACCESS BY INDEX ROWID| TBCATALOG_ITEM         |  1783 | 57056 |     2   (0)| 00:00:01 |
|* 18 |              INDEX RANGE SCAN          | TBCATALOG_ITEM_3IX     |  3566 |       |     1   (0)| 00:00:01 |
|  19 |             VIEW                       | VW_SQ_3                |  2368 | 52096 |     1   (0)| 00:00:01 |
|  20 |              HASH GROUP BY             |                        |  2368 | 68672 |     1   (0)| 00:00:01 |
|* 21 |               INDEX FULL SCAN          | TBCATALOG_ITEM_PK      | 24963 |   706K|     1   (0)| 00:00:01 |
|* 22 |            INDEX RANGE SCAN            | TBNAME_1IX             |     1 |    95 |     1   (0)| 00:00:01 |
|* 23 |           INDEX RANGE SCAN             | TBRELATION_2IX         |   485 |       |     1   (0)| 00:00:01 |
|* 24 |        INDEX RANGE SCAN                | TBREFINED_RELATION_PK  |   663K|  9067K|     1   (0)| 00:00:01 |
|  25 |       BUFFER SORT                      |                        |  2368 | 52096 |   699   (1)| 00:00:09 |
|  26 |        VIEW                            | VW_SQ_4                |  2368 | 52096 |     1   (0)| 00:00:01 |
|  27 |         HASH GROUP BY                  |                        |  2368 | 68672 |     1   (0)| 00:00:01 |
|* 28 |          INDEX FULL SCAN               | TBCATALOG_ITEM_PK      | 24963 |   706K|     1   (0)| 00:00:01 |
|  29 |     TABLE ACCESS BY INDEX ROWID        | TBRELATION             |     1 |    41 |     1   (0)| 00:00:01 |
|* 30 |      INDEX RANGE SCAN                  | TBRELATION_6IX         |     1 |       |     1   (0)| 00:00:01 |
|  31 |       NESTED LOOPS                     |                        |     1 |    58 |     2   (0)| 00:00:01 |
|  32 |        VIEW                            | VW_SQ_1                |    11 |   220 |     1   (0)| 00:00:01 |
|  33 |         SORT GROUP BY                  |                        |    11 |   209 |     1   (0)| 00:00:01 |
|* 34 |          INDEX RANGE SCAN              | TBCATALOG_ITEM_PK      |    11 |   209 |     1   (0)| 00:00:01 |
|  35 |        INLIST ITERATOR                 |                        |       |       |            |          |
|* 36 |         INDEX RANGE SCAN               | TBRELATION_6IX         |     1 |    38 |     1   (0)| 00:00:01 |
|* 37 |    INDEX RANGE SCAN                    | TBREFINED_RELATION_2IX |    91 |  2002 |     1   (0)| 00:00:01 |
|* 38 |   INDEX RANGE SCAN                     | TBNAME_1IX             |     1 |    95 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("CT"."PCVERSION_ID"="VW_COL_1" AND "CID"="CT"."CID")
   5 - filter("CT"."IS_DELETED"='0')
   6 - access("CT"."ITEM_TYPE"='OF')
   9 - access("REL2"."PCVERSION_ID"="VW_COL_1" AND "CID"="REL2"."PARENT_ID")
  12 - access("PCVERSION_ID"<=80112001)
       filter("PCVERSION_ID"<=80112001)
  13 - filter(("REL2"."PARENT_ITEM_TYPE"='CO' OR "REL2"."PARENT_ITEM_TYPE"='PR') AND
              "REL2"."IS_DELETED"='0')
  16 - access("CT"."PCVERSION_ID"="VW_COL_1" AND "CID"="CT"."CID")
  17 - filter("CT"."IS_DELETED"='0')
  18 - access("ITEM_TYPE"='PP')
  21 - access("PCVERSION_ID"<=80112001)
       filter("PCVERSION_ID"<=80112001)
  22 - access("PPNAME"."CID"="CT"."CID" AND "PPNAME"."PCVERSION_ID"="CT"."PCVERSION_ID" AND
              "PPNAME"."LANGUAGE"='EN')
  23 - access("REL2"."CHILD_ID"="CT"."CID")
  24 - access("RELATION_ID"="REL2"."RELATION_ID" AND "PCVERSION_ID"<=80112001)
  28 - access("PCVERSION_ID"<=80112001)
       filter("PCVERSION_ID"<=80112001)
  30 - access("REL1"."ROOT_CID"="CT"."CID" AND "REL1"."PCVERSION_ID"="CT"."PCVERSION_ID" AND
              "REL1"."CHILD_ITEM_TYPE"='PR' AND "REL1"."IS_DELETED"='0')
       filter( EXISTS (SELECT /*+ */ 0 FROM "IMP10PCOWN"."TBRELATION" "REL", (SELECT /*+ */
              MAX("PCVERSION_ID") "VW_COL_1","CID" "CID" FROM "IMP10PCOWN"."TBCATALOG_ITEM" "TBCATALOG_ITEM" WHERE
              "PCVERSION_ID"<=80112001 AND "CID"=:B1 GROUP BY "CID") "VW_SQ_1" WHERE "REL"."IS_DELETED"='0' AND
              ("REL"."CHILD_ITEM_TYPE"='CO' OR "REL"."CHILD_ITEM_TYPE"='PP') AND "REL"."PCVERSION_ID"="VW_COL_1" AND
              "CID"="REL"."ROOT_CID" AND "REL"."ROOT_CID"=:B2 AND "REL"."ASSOCIATION_TYPE"='CN' AND
              ("REL"."PARENT_ID"=:B3 OR "REL"."CHILD_ID"=:B4)))
  34 - access("CID"=:B1 AND "PCVERSION_ID"<=80112001)
  36 - access("CID"="REL"."ROOT_CID" AND "REL"."PCVERSION_ID"="VW_COL_1" AND
              ("REL"."CHILD_ITEM_TYPE"='CO' OR "REL"."CHILD_ITEM_TYPE"='PP') AND "REL"."IS_DELETED"='0' AND
              "REL"."ASSOCIATION_TYPE"='CN')
       filter("REL"."ROOT_CID"=:B1 AND "REL"."ASSOCIATION_TYPE"='CN' AND ("REL"."PARENT_ID"=:B2 OR
              "REL"."CHILD_ID"=:B3))
  37 - access("REFINED_RELATION_ID"="RELATION_ID" AND
              "TBREFINED_RELATION"."ROOT_RELATION_ID"="REL1"."RELATION_ID" AND
              "TBREFINED_RELATION"."PCVERSION_ID"="CT"."PCVERSION_ID")
  38 - access("OFFERNAME"."CID"="CT"."CID" AND "OFFERNAME"."PCVERSION_ID"="CT"."PCVERSION_ID" AND
              "OFFERNAME"."LANGUAGE"='EN')


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
    1942328  consistent gets
     811412  physical reads
          0  redo size
      38796  bytes sent via SQL*Net to client
       2336  bytes received via SQL*Net from client
         23  SQL*Net roundtrips to/from client
        510  sorts (memory)
          0  sorts (disk)
        318  rows processed

Thanks,
Mayank

Tom Kyte
July 15, 2009 - 11:06 am UTC

well, I see a lot of hints, my first step would be to remove all of them.


why do you blame the merge join cartesian for anything? What was the technical reason for pointing at that? (not saying it isn't, just wondering why you are jumping on it - it could be the best thing ever).


Erroneous results due to Merge Join Cartesian

Ravi, September 09, 2009 - 1:16 pm UTC

Tom,

It could be something to do with our Stats, but a Query when it uses Merge Join Cartesian outputs ONE row. But, if a EQUAL TO condition is changed to IN, it does NOT use Merge Join and produces the correct output of 8 rows.

Any views on what's causing the inconsistency in the results

Thanks

Ravi


UAT> l
1 SELECT null
2 FROM field_data_lines fdl, parcel_claim_adjust pca
3 WHERE pca_asa_id = 20058188
4 AND pca_lu_id = 122
5 AND fdl_fds_asa_id = pca_asa_id
6 AND fdl_field_id = (SELECT fdl2.fdl_field_id
7 FROM field_data_lines fdl2
8* WHERE fdl2.fdl_id IN pca_fdl_id)


UAT> /

N
-


1 row selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=9 Bytes=315)
1 0 FILTER
2 1 MERGE JOIN (CARTESIAN) (Cost=2 Card=9 Bytes=315)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'PARCEL_CLAIM_ADJUST'
(TABLE) (Cost=1 Card=1 Bytes=16)

4 3 INDEX (RANGE SCAN) OF 'PCA_FK01' (INDEX) (Cost=1 Car
d=31)

5 2 BUFFER (SORT) (Cost=1 Card=21 Bytes=399)
6 5 AND-EQUAL
7 6 INDEX (RANGE SCAN) OF 'FDL_ASA' (INDEX) (Cost=1 Ca
rd=21)

8 6 INDEX (RANGE SCAN) OF 'FDL_FDL_FIELD_ID_IDX' (INDE
X) (Cost=1 Card=12)

9 8 TABLE ACCESS (BY INDEX ROWID) OF 'FIELD_DATA_LIN
ES' (TABLE) (Cost=1 Card=1 Bytes=19)

10 9 INDEX (UNIQUE SCAN) OF 'FDL_PK' (INDEX (UNIQUE
)) (Cost=1 Card=1)

11 1 TABLE ACCESS (BY INDEX ROWID) OF 'FIELD_DATA_LINES' (TAB
LE) (Cost=1 Card=1 Bytes=19)

12 11 INDEX (UNIQUE SCAN) OF 'FDL_PK' (INDEX (UNIQUE)) (Cost
=1 Card=1)


1 SELECT null
2 FROM field_data_lines fdl, parcel_claim_adjust pca
3 WHERE pca_asa_id = 20058188
4 AND pca_lu_id = 122
5 AND fdl_fds_asa_id = pca_asa_id
6 AND fdl_field_id in (SELECT fdl2.fdl_field_id
7 FROM field_data_lines fdl2
8* WHERE fdl2.fdl_id IN pca_fdl_id)
UAT> /

N
-









8 rows selected.

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=54)
1 0 NESTED LOOPS (Cost=3 Card=1 Bytes=54)
2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=35)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'PARCEL_CLAIM_ADJUST'
(TABLE) (Cost=1 Card=1 Bytes=16)

4 3 INDEX (RANGE SCAN) OF 'PCA_FK01' (INDEX) (Cost=1 Car
d=31)

5 2 TABLE ACCESS (BY INDEX ROWID) OF 'FIELD_DATA_LINES' (T
ABLE) (Cost=1 Card=1 Bytes=19)

6 5 INDEX (UNIQUE SCAN) OF 'FDL_PK' (INDEX (UNIQUE)) (Co
st=1 Card=1)

7 1 AND-EQUAL
8 7 INDEX (RANGE SCAN) OF 'FDL_ASA' (INDEX) (Cost=1 Card=2
1)

9 7 INDEX (RANGE SCAN) OF 'FDL_FDL_FIELD_ID_IDX' (INDEX) (
Cost=1 Card=12)

Tom Kyte
September 14, 2009 - 10:12 am UTC

that would definitely be a bug - please utilize support.

A reader, September 15, 2009 - 11:13 am UTC

Hello Tom,


In the above post it's not that the subquery should return scaler value since = is used OR give error

and when the query is changed to "IN Clause" I think more rows satisfy the condition and result into 8 rows


Please correct me if I am wrong


Thnaks!


Tom Kyte
September 15, 2009 - 12:42 pm UTC



if the subquery returned more than one record, then the query with "=" would have failed. So, if you can use either IN or =, the subquery must return ONLY one row.



if you use

select ... from ... where .... AND col IN (subquery)

select ... from ... where .... AND col = (subquery)


and the second one does not fail, then subquery returns 0 or 1 records and both would return the same result set.

Is it possible?

Awktalk, February 26, 2010 - 4:36 pm UTC

To help with a very simple one? This takes 40 seconds to execute:

SELECT entitytable0.INSTANCEID AS "myid0",
entitytable0.ACTIVITY_ID AS "myid1", entitytable0.ID AS "myid2",
entitytable0.NAME AS "field4", entitytable0.START_DATETIME AS "field5"
FROM V_ACT_EXECUTION entitytable0
WHERE 1 = 1
AND ( ( entitytable0.INSTANCEID IN (
SELECT e.INSTANCEID
FROM EXEC_ACTIVITY_INSTANCE e, EB_BPLOANDATA ebo
WHERE entitytable0.ID = e.ID
AND entitytable0.ACTIVITY_ID = e.ACTIVITY_ID
AND e.INSTANCEID = ebo.INSTANCEID
AND e.ACTIVITY_ID = ebo.ACTIVITY_ID
AND attr0 <> ' ')
AND entitytable0.INSTANCEID IN (
SELECT e.INSTANCEID
FROM EXEC_ACTIVITY_INSTANCE e, EB_BPLOCATIONDATA ebo
WHERE entitytable0.ID = e.ID
AND entitytable0.ACTIVITY_ID = e.ACTIVITY_ID
AND e.INSTANCEID = ebo.INSTANCEID
AND e.ACTIVITY_ID = ebo.ACTIVITY_ID
AND attr16 <> ' ')
)
AND ( entitytable0.INSTANCEID IN (
SELECT e.INSTANCEID
FROM EXEC_ACTIVITY_INSTANCE e
WHERE entitytable0.ID = e.ID
AND entitytable0.INSTANCEID = e.INSTANCEID
AND entitytable0.ACTIVITY_ID = e.ACTIVITY_ID
AND e.NAME = 'Join before End')
AND entitytable0.INSTANCEID IN (
SELECT e.INSTANCEID
FROM exec_process_instance e
WHERE entitytable0.INSTANCEID = e.INSTANCEID
AND e.NAME = 'Rules')
)
)


PLAN_TABLE_OUTPUT
Plan hash value: 870980802

-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 75 | 18150 | 929 (1)| 00:00:12 |
| 1 | NESTED LOOPS SEMI | | 75 | 18150 | 929 (1)| 00:00:12 |
| 2 | NESTED LOOPS SEMI | | 75 | 17250 | 629 (1)| 00:00:08 |
| 3 | NESTED LOOPS SEMI | | 75 | 16350 | 329 (2)| 00:00:04 |
| 4 | NESTED LOOPS OUTER | | 75 | 15150 | 104 (4)| 00:00:02 |
| 5 | NESTED LOOPS | | 75 | 11325 | 103 (4)| 00:00:02 |
| 6 | SORT UNIQUE | | 4649 | 99K| 99 (2)| 00:00:02 |
|* 7 | TABLE ACCESS FULL | EXEC_PROCESS_INSTANCE | 4649 | 99K| 99 (2)| 00:00:02 |
| 8 | TABLE ACCESS BY INDEX ROWID | AP_ACTIVITY_INSTANCE_STARTED | 50 | 7550 | 1 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | PK_ACTINST_STARTED | 50 | | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PK_ACTINST_CMPLTED | 1 | 51 | 1 (0)| 00:00:01 |
| 11 | VIEW PUSHED PREDICATE | VW_SQ_3 | 1 | 16 | 3 (0)| 00:00:01 |
|* 12 | FILTER | | | | | |
| 13 | NESTED LOOPS OUTER | | 1 | 144 | 3 (0)| 00:00:01 |
| 14 | NESTED LOOPS OUTER | | 1 | 124 | 2 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID| AP_ACTIVITY_INSTANCE_STARTED | 1 | 73 | 1 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | PK_ACTINST_STARTED | 1 | | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PK_ACTINST_CMPLTED | 1 | 51 | 1 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | I_A_EVNT_ACTID_TYP | 1 | 20 | 1 (0)| 00:00:01 |
| 19 | VIEW PUSHED PREDICATE | VW_SQ_1 | 1 | 12 | 4 (0)| 00:00:01 |
| 20 | MERGE JOIN CARTESIAN | | 1 | 167 | 4 (0)| 00:00:01 |
| 21 | NESTED LOOPS OUTER | | 1 | 122 | 3 (0)| 00:00:01 |
| 22 | NESTED LOOPS OUTER | | 1 | 102 | 2 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | PK_ACTINST_STARTED | 1 | 51 | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | PK_ACTINST_CMPLTED | 1 | 51 | 1 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | I_A_EVNT_ACTID_TYP | 1 | 20 | 1 (0)| 00:00:01 |
| 26 | BUFFER SORT | | 1 | 45 | 3 (0)| 00:00:01 |
|* 27 | TABLE ACCESS BY INDEX ROWID | EB_BPLOANDATA | 1 | 45 | 1 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | IND_EB_BPLOANDATA | 1 | | 1 (0)| 00:00:01 |
| 29 | VIEW PUSHED PREDICATE | VW_SQ_2 | 1 | 12 | 4 (0)| 00:00:01 |
| 30 | MERGE JOIN CARTESIAN | | 1 | 165 | 4 (0)| 00:00:01 |
| 31 | NESTED LOOPS OUTER | | 1 | 122 | 3 (0)| 00:00:01 |
| 32 | NESTED LOOPS OUTER | | 1 | 102 | 2 (0)| 00:00:01 |
|* 33 | INDEX UNIQUE SCAN | PK_ACTINST_STARTED | 1 | 51 | 1 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | PK_ACTINST_CMPLTED | 1 | 51 | 1 (0)| 00:00:01 |
|* 35 | INDEX RANGE SCAN | I_A_EVNT_ACTID_TYP | 1 | 20 | 1 (0)| 00:00:01 |
| 36 | BUFFER SORT | | 1 | 43 | 3 (0)| 00:00:01 |
|* 37 | TABLE ACCESS BY INDEX ROWID | EB_BPLOCATIONDATA | 1 | 43 | 1 (0)| 00:00:01 |
|* 38 | INDEX RANGE SCAN | IND_EB_BPLOCATIONDATA | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - filter("E"."NAME"='Rules')
9 - access("STARTED"."INSTANCEID"="E"."INSTANCEID")
10 - access("STARTED"."INSTANCEID"="COMPLETED"."INSTANCEID"(+) AND
"STARTED"."ACTIVITY_ID"="COMPLETED"."ACTIVITY_ID"(+) AND "STARTED"."ID"="COMPLETED"."ID"(+))
12 - filter("STARTED"."INSTANCEID"="STARTED"."INSTANCEID")
15 - filter("STARTED"."NAME"='Join before End')
16 - access("STARTED"."INSTANCEID"="STARTED"."INSTANCEID" AND
"STARTED"."ACTIVITY_ID"="STARTED"."ACTIVITY_ID" AND "STARTED"."ID"="STARTED"."ID")
17 - access("COMPLETED"."INSTANCEID"(+)="STARTED"."INSTANCEID" AND
"COMPLETED"."ACTIVITY_ID"(+)="STARTED"."ACTIVITY_ID" AND "COMPLETED"."ID"(+)="STARTED"."ID")
filter("STARTED"."ID"="COMPLETED"."ID"(+) AND "STARTED"."ACTIVITY_ID"="COMPLETED"."ACTIVITY_ID"(+)
AND "STARTED"."INSTANCEID"="COMPLETED"."INSTANCEID"(+))
18 - access("COMPLETED"."ACTIVITY_ID"="EAE"."ACTIVITY_ID"(+) AND "EAE"."EVENT_TYPE"(+)=23)
23 - access("STARTED"."INSTANCEID"="STARTED"."INSTANCEID" AND
"STARTED"."ACTIVITY_ID"="STARTED"."ACTIVITY_ID" AND "STARTED"."ID"="STARTED"."ID")
24 - access("COMPLETED"."INSTANCEID"(+)="STARTED"."INSTANCEID" AND
"COMPLETED"."ACTIVITY_ID"(+)="STARTED"."ACTIVITY_ID" AND "COMPLETED"."ID"(+)="STARTED"."ID")
filter("STARTED"."ID"="COMPLETED"."ID"(+) AND "STARTED"."ACTIVITY_ID"="COMPLETED"."ACTIVITY_ID"(+)
AND "STARTED"."INSTANCEID"="COMPLETED"."INSTANCEID"(+))
25 - access("COMPLETED"."ACTIVITY_ID"="EAE"."ACTIVITY_ID"(+) AND "EAE"."EVENT_TYPE"(+)=23)
27 - filter("ATTR0"<>' ')
28 - access("EBO"."INSTANCEID"="STARTED"."INSTANCEID" AND "EBO"."ACTIVITY_ID"="STARTED"."ACTIVITY_ID")
33 - access("STARTED"."INSTANCEID"="STARTED"."INSTANCEID" AND
"STARTED"."ACTIVITY_ID"="STARTED"."ACTIVITY_ID" AND "STARTED"."ID"="STARTED"."ID")
34 - access("COMPLETED"."INSTANCEID"(+)="STARTED"."INSTANCEID" AND
"COMPLETED"."ACTIVITY_ID"(+)="STARTED"."ACTIVITY_ID" AND "COMPLETED"."ID"(+)="STARTED"."ID")
filter("STARTED"."ID"="COMPLETED"."ID"(+) AND "STARTED"."ACTIVITY_ID"="COMPLETED"."ACTIVITY_ID"(+)
AND "STARTED"."INSTANCEID"="COMPLETED"."INSTANCEID"(+))
35 - access("COMPLETED"."ACTIVITY_ID"="EAE"."ACTIVITY_ID"(+) AND "EAE"."EVENT_TYPE"(+)=23)
37 - filter("ATTR16"<>' ')
38 - access("EBO"."INSTANCEID"="STARTED"."INSTANCEID" AND "EBO"."ACTIVITY_ID"="STARTED"."ACTIVITY_ID")

Need suggestion

Deepak, July 22, 2014 - 9:33 am UTC

We are seeing Merge Join Cartesian for the following SQL and here we are checking for DUPLICATES. Could you please suggest how can we avoid this merge join cartesian.

SELECT cc_claim_head.ID col0
FROM gwcc.cc_claim cc_claim_head
INNER JOIN gwcc.cc_contact cc_contact_2
ON cc_claim_head.InsuredDenormID = cc_contact_2.ID
WHERE cc_claim_head.retired = 0
AND cc_claim_head.LossDate BETWEEN sysdate-5 AND sysdate
AND (cc_claim_head.PolicyID IN
(SELECT cc_policy_head.ID col0
FROM gwcc.cc_policy cc_policy_head
WHERE cc_policy_head.PolicyType = :1
AND cc_policy_head.PolicyNumber = :2
AND cc_policy_head.Retired = 0
))
AND cc_contact_2.NameDenorm = LOWER(:3)
AND cc_contact_2.Retired = 0;

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 95 | 9 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 95 | 9 (0)| 00:00:01 |
| 4 | MERGE JOIN CARTESIAN | | 1 | 65 | 7 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| CC_POLICY | 1 | 33 | 5 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | CC0000005KU2 | 2 | | 3 (0)| 00:00:01 |
| 7 | BUFFER SORT | | 4 | 128 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | CC0000001ZU3 | 4 | 128 | 2 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | CC000000J0U17 | 1 | | 1 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID | CC_CLAIM | 1 | 30 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_TIMESTAMP(:1)<=TO_TIMESTAMP(:2))
5 - filter("CC_POLICY_HEAD"."POLICYTYPE"=TO_NUMBER(:3))
6 - access("CC_POLICY_HEAD"."POLICYNUMBER"=:4 AND "CC_POLICY_HEAD"."RETIRED"=0)
8 - access("CC_CONTACT_2"."NAMEDENORM"=LOWER(:5) AND "CC_CONTACT_2"."RETIRED"=0)
filter("CC_CONTACT_2"."RETIRED"=0)
9 - access("CC_CLAIM_HEAD"."POLICYID"="CC_POLICY_HEAD"."ID" AND
"CC_CLAIM_HEAD"."RETIRED"=0)
10 - filter("CC_CLAIM_HEAD"."LOSSDATE">=TO_TIMESTAMP(:1) AND
"CC_CLAIM_HEAD"."LOSSDATE"<=TO_TIMESTAMP(:2) AND
"CC_CLAIM_HEAD"."INSUREDDENORMID"="CC_CONTACT_2"."ID")

Appreciate your help.-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 95 | 9 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 95 | 9 (0)| 00:00:01 |
| 4 | MERGE JOIN CARTESIAN | | 1 | 65 | 7 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| CC_POLICY | 1 | 33 | 5 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | CC0000005KU2 | 2 | | 3 (0)| 00:00:01 |
| 7 | BUFFER SORT | | 4 | 128 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | CC0000001ZU3 | 4 | 128 | 2 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | CC000000J0U17 | 1 | | 1 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID | CC_CLAIM | 1 | 30 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_TIMESTAMP(:1)<=TO_TIMESTAMP(:2))
5 - filter("CC_POLICY_HEAD"."POLICYTYPE"=TO_NUMBER(:3))
6 - access("CC_POLICY_HEAD"."POLICYNUMBER"=:4 AND "CC_POLICY_HEAD"."RETIRED"=0)
8 - access("CC_CONTACT_2"."NAMEDENORM"=LOWER(:5) AND "CC_CONTACT_2"."RETIRED"=0)
filter("CC_CONTACT_2"."RETIRED"=0)
9 - access("CC_CLAIM_HEAD"."POLICYID"="CC_POLICY_HEAD"."ID" AND
"CC_CLAIM_HEAD"."RETIRED"=0)
10 - filter("CC_CLAIM_HEAD"."LOSSDATE">=TO_TIMESTAMP(:1) AND
"CC_CLAIM_HEAD"."LOSSDATE"<=TO_TIMESTAMP(:2) AND
"CC_CLAIM_HEAD"."INSUREDDENORMID"="CC_CONTACT_2"."ID")

Thanks in advance.