- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: CBO bug
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-24-2005 12:51 PM
тАО07-24-2005 12:51 PM
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-24-2005 07:46 PM
тАО07-24-2005 07:46 PM
Re: CBO bug
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-24-2005 07:50 PM
тАО07-24-2005 07:50 PM
Re: CBO bug
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-24-2005 08:46 PM
тАО07-24-2005 08:46 PM
Re: CBO bug
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-25-2005 02:39 AM
тАО07-25-2005 02:39 AM
Re: CBO bug
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-01-2005 01:04 PM
тАО08-01-2005 01:04 PM
Re: CBO bug
Thanks
Eric
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-01-2005 01:06 PM
тАО08-01-2005 01:06 PM
Re: CBO bug
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-01-2005 02:20 PM
тАО08-01-2005 02:20 PM
SolutionTo 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-01-2005 04:07 PM
тАО08-01-2005 04:07 PM
Re: CBO bug
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 ;).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-18-2005 08:27 PM
тАО08-18-2005 08:27 PM