topic Re: CBO bug in Operating System - HP-UX

CBO bug

ericfjchen — Sun, 24 Jul 2005 19:51:08 GMT

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

Re: CBO bug

Jean-Luc Oudart — Mon, 25 Jul 2005 02:46:42 GMT

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

Re: CBO bug

Jean-Luc Oudart — Mon, 25 Jul 2005 02:50:36 GMT

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

Re: CBO bug

Yogeeraj_1 — Mon, 25 Jul 2005 03:46:07 GMT

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

Re: CBO bug

Patti Johnson — Mon, 25 Jul 2005 09:39:17 GMT

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

Re: CBO bug

ericfjchen — Mon, 01 Aug 2005 20:04:48 GMT

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

Thanks

Eric

Re: CBO bug

ericfjchen — Mon, 01 Aug 2005 20:06:00 GMT

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

Re: CBO bug

Indira Aramandla — Mon, 01 Aug 2005 21:20:33 GMT

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

Re: CBO bug

generic_1 — Mon, 01 Aug 2005 23:07:14 GMT

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 ;).

Re: CBO bug

ericfjchen — Fri, 19 Aug 2005 03:27:05 GMT

thanks