Hi Fernando
Most CBO parameters have changed their default values between 8i and 9i.
One of them is unnest_subquery which you probably is hitting with your statement.
I assume that you are collecting your optimizer statistics with dbms_stats and not
with analyze, and that you do this also for histograms for all columns. (not only the indexed ones)
Try the following hint in your statement like this:
SELECT va.nr_placa_veiculo
FROM
caminhao_movimento cm,
veiculo_aciaria va
WHERE cm.nr_placa_veiculo (+)= va.nr_placa_veiculo
AND cm.sq_movimento = (select /*+ no_unnest */ max(sq_movimento)
from caminhao_movimento cm
where cm.nr_placa_veiculo = va.nr_placa_veiculo
and sq_protocolo is not null)
AND va.st_veiculo in ('CA', 'VA', 'DC');
This behaviour is solved in release 10g since the "bad" experiences made the CBO default
parameters to be changed back.
Måns
________________________________
From: Fernando Simonetti [mailto:oracledba-ezmlmshield-x18754110.[Email address protected]
Sent: Thu 5/3/2007 21:09
To: LazyDBA Discussion
Subject: CBO x RULE
I'm migrating from Oracle 8i to Oracle 9.2 and from Rule to CBO optimizer
and I have having some performances problems.
Considere the size of following TABLES
Tables
caminhao_movimento = 5MB DATA
veiculo_aciaria = 40KB Data
Considere the following SELECT
SELECT va.nr_placa_veiculo
FROM
caminhao_movimento cm,
veiculo_aciaria va
WHERE cm.nr_placa_veiculo (+)= va.nr_placa_veiculo
AND cm.sq_movimento = (select max(sq_movimento)
from caminhao_movimento cm
where cm.nr_placa_veiculo = va.nr_placa_veiculo
and sq_protocolo is not null)
AND va.st_veiculo in ('CA', 'VA', 'DC')
optimizer=CBO (all_rows and first_rows -> almost de same result)
This execution plan is terrible. About 3 minutes of execution to return
data.
OPERATIONS OPTIONS OBJECT
------------------------------ ------------------------------
------------------------
--SELECT STATEMENTCOST = 12468
----FILTER
------SORT GROUP BY
--------NESTED LOOPS
----------HASH JOIN
------------INLIST ITERATOR
--------------TABLE ACCESS BY INDEX ROWID
VEICULO_ACIARIA
----------------INDEX RANGE SCAN
IX_VEICULO_ACIARIA_02
------------TABLE ACCESS FULL
CAMINHAO_MOVIMENTO
----------INDEX RANGE SCAN
IX_CAMINHAO_MOVIMENTO_01
Under the same select without the clausule: -> and va.st_veiculo in ('CA',
'VA', 'DC')
Look the the diference of STATEMENTCOST
This is fast. Obviously have diferent results. About 5 seconds.
OPERATIONS OPTIONS OBJECT
------------------------------ ------------------------------
------------------------------
--SELECT STATEMENTCOST = 129
----NESTED LOOPS
------NESTED LOOPS
--------VIEW VW_SQ_1
----------SORT GROUP BY
------------TABLE ACCESS FULL
CAMINHAO_MOVIMENTO
--------INDEX UNIQUE SCAN
IX_VEICULO_ACIARIA_01
------INDEX UNIQUE SCAN
IX_CAMINHAO_MOVIMENTO_01
The original Select but with the /*+ RULE */ hint
This is the faster. About 1 second.
OPERATIONS OPTIONS OBJECT
------------------------------ ------------------------------
------------------------------
--SELECT STATEMENTCOST =
----FILTER
------NESTED LOOPS OUTER
--------TABLE ACCESS FULL
VEICULO_ACIARIA
--------INDEX RANGE SCAN
IX_CAMINHAO_MOVIMENTO_01
------SORT AGGREGATE
--------TABLE ACCESS BY INDEX ROWID
CAMINHAO_MOVIMENTO
----------INDEX RANGE SCAN
IX_CAMINHAO_MOVIMENTO_01
I tried tunning the database with:
optimizer_index_cost_adj = 3 (Starting with 100 I decrease it after each
test)
optimizer_index_caching = 95 (Starting with 0 I increase it after each
test)
optimizer_mode=FIRST_ROWS (tried others too)
With this changes the execution plan get better but still worse a lot them
RULE optimizer.
I analyzed the tables and I made histogram on va.st_veiculo.
I have hundreds querys with this kind of construction but much more complex,
and I don´t want to rewrite all of theirs.
I read that CBO have limitations with subquerys and IN clausula. Anyone
knows if are there any configuration for CBO to resolve this kind of select?
I´m giving use of CBO up.
----------------------------------------------------------
Esta mensagem e seus anexos podem conter informações confidenciais ou
privilegiadas. Se você não é o destinatário dos mesmos você não está
autorizado a utilizar o material para qualquer fim. Solicitamos que você
apague a mensagem e avise imediatamente ao remetente. O conteúdo desta
mensagem e seus anexos não representam necessariamente a opinião e a
intenção da empresa, não implicando em qualquer obrigação ou
responsabilidade por parte da mesma.
This message may contain confidential and/or privileged information. If you
are not the addressee or authorized to receive this for the addressee, you
must not use, copy, disclose or take any action based on this message or any
information herein. If you have received this message in error, please
advise the sender immediately by reply e-mail and delete this message. The
contents of this message and its attachments do not necessarily express the
opinion or the intention of the company, and do not implies any legal
obligation or responsabilities from this company.
----------------------------------------------------------
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Oracle LazyDBA home page