Re: CBO x RULE

Re: CBO x RULE

 

  

I would try reversing the FROM list and using a /*+ FIRST_ROWS ORDERED */
hint.

<<MrO>>

----- Original Message -----
From: "Parveen Kumar "
<oracledba-ezmlmshield-x155109.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Thursday, May 03, 2007 10:24 PM
Subject: Re: CBO x RULE


> With this much of data (5Mb and 40KB in the tables) ,I believe there is
> really something wrong with the statistics .5MB is not even a small
> table size now a days for oracle . Just reanalyze and check index stats
> too . It doesn't make a big diff with histogram on such a small table
> (even you can try a Bitmap index if you have very small distinct values
> in va.st_veiculo , but think about insert and update on table too)
>
> you need not to touch the parameters for these small tables
>
> Try removing optimizer_index_cost_adj ,and index_caching (BTW FYI it is
> advisable to set a lower value for cost_adj nearly 10-20 and higher
> value for index_caching ) ,look for the optimizer_compatible parameter
> too ,if you change the parameter you need to reanalyze the tables and
> indexes
>
> Just use
> exec dbms_stats.gather_schema_stats ('ABC',NULL,FALSE,'FOR ALL COLUMNS
> SIZE AUTO',5,'DEFAULT',TRUE,NULL,NULL,'GATHER','LIST');
> once and then change GATHER to GATHER AUTO for further analysis
>
> you should be fine with this
>
>
> Note that the columns used in where condition are indexed properly too
>
> Parveen
>
> Fernando Simonetti wrote:
> > 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
> >
> >
>
>
>
> ---------------------------------------------------------------------
> 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