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
>
>
Oracle LazyDBA home page