1748128 Members
3491 Online
108758 Solutions
New Discussion юеВ

Re: CBO bug

 
SOLVED
Go to solution
ericfjchen
Regular Advisor

CBO bug

This is Oracle 9.0.1.4. We have 2 environments. One SQL execution plan is fine
on the TEST environment, but it is very bad on the Production. Both database
are CBO. We analyze the realted tables on the Production but still bad. If we
add hint /*+ rule */, it becomes good. Besides hint, how can we do? Can we use
SQL profile to fix the execution plan? Or export the related tables' statistic
from the TEST and import to the PRODUCTION?

Thanks

Eric
9 REPLIES 9
Jean-Luc Oudart
Honored Contributor

Re: CBO bug

Hi Eric

2 questions :

1) is your test environment a recent copy of your production data ?

2) have you any differences in init.ora parameters (that can influence performance) ?

Regards
Jean-Luc
fiat lux
Jean-Luc Oudart
Honored Contributor

Re: CBO bug

Hi again,

please find attached Metalink doc :35934.1 "Cost Based Optimizer - Common Misconceptions and Issues"

This may help to understand the behaviour of the query in your production database.

Regards
Jean-Luc
fiat lux
Yogeeraj_1
Honored Contributor

Re: CBO bug

hi,

if you are sure you want to do it..

dbms_stats is the way to do it!

if you need any further guidance, let us know.

regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Patti Johnson
Respected Contributor

Re: CBO bug

Eric,
If you can not find an init.ora parameter that accounts for the difference in execution plans then you could use dbms_stats to export the table statistics from test and import them into the production database.

I've done this a couple of times when minor differences in the number of records caused execution plans in my production database to change. Importing the test statistics did solve the problem. You just need to remember to re-import the old statistics if you regularly run gather schema statistics in production.

Stored Outlines are designed for this type of issue but I have not had any luck with them when the code is from sqlforms applications.

Patti
ericfjchen
Regular Advisor

Re: CBO bug

How to use dbms_stats to export the table statistics from testing to production?

Thanks

Eric
ericfjchen
Regular Advisor

Re: CBO bug

2 questions :

1) is your test environment a recent copy of your production data ?

--> Yes

2) have you any differences in init.ora parameters (that can influence performance) ?

--> No
Indira Aramandla
Honored Contributor
Solution

Re: CBO bug

Hi Eric,

To copy the statistcis from one database to another you need to

1. Create the holding table using DBMS_STATS
SQL> exec dbms_stats.create_stat_table('user1','STATS');
This command creates the holding table for statistics. The table that is created is owned by user1 and called STATS (user1.STATS).

2. Move the statistics to the STATS holding table
exec dbms_stats.export_table_stats('user1','EMP',NULL,'STATS',NULL,TRUE);
This populates the holding table user1.STATS with statistics gathered on the user1.EMP table, and includes any indexes and places them in the user1.STATS table.

3. Then Export the data in the STATS table using exp on the source database
%exp user1/password tables=STATS file=expstat.dmp

4. Then Import on the new database, run import:
%imp user1/password file=expstat.dmp full=y log=implog.txt

5. Populate the data dictionary in the new database.
SQL> exec dbms_stats.import_table_stats('user1','EMP',NULL,'STATS',NULL,TRUE);

Attached is Metalink Note:117203.1 which describes the steps to copy the statistics from one database to another database using DBMS_STATS

IA

Never give up, Keep Trying
generic_1
Respected Contributor

Re: CBO bug

Check and make sure your kernel parameters and, swap memory, cpus are the same or better in production.

Also, I see people supposedly test stuff allot of times and then they put it in production with allot more users and a much larger database and wonder why its slow. Hopefully this is not your case ;).
ericfjchen
Regular Advisor

Re: CBO bug

thanks