| Help - Different Plans on Different Versions [message #181862] |
Wed, 12 July 2006 00:11 |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi ,
I have collected latest statistics but i got no difference in execution plan although i got negligible difference in the COST
The two databases differs from each other for following parameters only
db_block_checksum FALSE TRUE
db_writer_processes 2 1
fast_start_mttr_target 0 300
java_pool_size 117440512 83886080
mts_circuits 0 170
mts_servers 0 1
mts_sessions 0 165
parallel_max_servers 4 5
pga_aggregate_target 20971520 12582912
query_rewrite_enabled TRUE FALSE
remote_os_authent TRUE FALSE
shared_pool_size 218103808 167772160
shared_server_sessions 0 165
shared_servers 0 1
Rest all other parameters are the same.
Also since db2 was using indexes for com_param_user_m i tried to put Hints in the query fired on db1,
however it gave even worst results.
Similarly using db2 was using Nested Loop Joins while db1 was using Hash Joins, so i tried to use Hint for Nested Loop
Join in db1, which too gave worst results.
Also in the attached file we can see that for db1 there is Parallel to Serial conversion in the query.
I have checked all the table and indexes for db1 and db2 for structure they are exactly the same (including parallel option degree)
also another queries having tables, indexes created without option are also giving drastic difference in db1 and db2
Please suggest
Also please suggest can i disable all these "parallel" options without restarting the instance.
Thanks and Regards,
Pratap
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
