Home » RDBMS Server » Performance Tuning » Understand how optimizer works (Oracle 9i, AIX)
Understand how optimizer works [message #359559] Mon, 17 November 2008 04:57 Go to next message
psoftneto
Messages: 44
Registered: July 2008
Member
Hi all,

I have two databases with the same data and the same statistics.
I don't understand with the plan for the same request is not the same on the two databases.
There is one database on which there is MERGE JOIN CARTESIAN and not on the other database.
What are the parameters which can explain that ?

Thanks.
Re: Understand how optimizer works [message #359596 is a reply to message #359559] Mon, 17 November 2008 09:26 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>same data and the same statistics.
You mean, the statistics is just copied over?
Are the underlying physical architecture and hardware also the same?
What about Oracle init parameters?
Re: Understand how optimizer works [message #359742 is a reply to message #359596] Tue, 18 November 2008 02:08 Go to previous messageGo to next message
psoftneto
Messages: 44
Registered: July 2008
Member
Hi Mahesh,
Yes the statistics were copied from one database to the other one.
Init.ora parameters are not the same but hardware and architecture is the same.
Thanks.
Re: Understand how optimizer works [message #359800 is a reply to message #359742] Tue, 18 November 2008 04:15 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
certain init.ora parameters are vital in CBO.
If the database datafiles are not exactly carved (disk layout etc), statistics copied from one database to another, is of no help.
Re: Understand how optimizer works [message #359810 is a reply to message #359800] Tue, 18 November 2008 04:30 Go to previous messageGo to next message
psoftneto
Messages: 44
Registered: July 2008
Member
The following init.ora parameters are not the same or are not defined in the database with join cartesian:
java_pool_size
db_cache_size
db_file_multiblock_read_count
db_16K_cache_size
open_cursors
shared_pool_size
sga_max_size
log_checkpoint_interval
cursor_sharing
max_enabled_roles
parallel_max_servers
parallel_automatic_tuning
processes
job_queue_processes
fast_start_mttr_target
log_buffer
pga_aggregate_target
timed_statistics
max_dump_file_size

Database with join cartesian only:
hash_join_enabled=TRUE
query_rewrite_enabled=TRUE
star_transformation_enabled=FALSE
large_pool_size
sort_area_size

Can you tell me if some of those parameters should be aligned ?
Thanks
Re: Understand how optimizer works [message #359822 is a reply to message #359810] Tue, 18 November 2008 04:58 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
well,
pga_aggregate_target, hash_join_enabled are just enough to produce variation in plans.
In your case, it may even be the stats. If you can afford, just recollect the stats from scratch (instead of copying it).
>>Can you tell me if some of those parameters should be aligned ?
Without having any idea about your environment, that could be just as bad as it can get. Smile
Re: Understand how optimizer works [message #360584 is a reply to message #359822] Fri, 21 November 2008 08:27 Go to previous messageGo to next message
psoftneto
Messages: 44
Registered: July 2008
Member
After removing the folling parameters. It looks OK know.

hash_join_enabled
query_rewrite_enabled
star_transformation_enabled
Re: Understand how optimizer works [message #360600 is a reply to message #360584] Fri, 21 November 2008 10:04 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Its' weird - comparing this with your other thread its like you're only bothering to tell us half of the details on each thread.

If you honestly believe that making it difficult for us to understand what's going on will improve the quality of advice that you get, then I'd have to ask what colour the sky is on your planet.
Previous Topic: Oracle Tuning - Explain plan
Next Topic: Is this a 10g bug?
Goto Forum:
  


Current Time: Sun Dec 11 08:30:00 CST 2016

Total time taken to generate the page: 0.10710 seconds