Home » RDBMS Server » Performance Tuning » Merge join verse Filter. Same code, different database, different explain plan
Merge join verse Filter. Same code, different database, different explain plan [message #292149] Tue, 08 January 2008 01:02 Go to next message
bickers
Messages: 1
Registered: October 2006
Location: Australia
Junior Member
I have a view that performs differently on two separate databases and accordingly produces a different explain plan. The code is exactly the same on each database.

Database A is a test database with a relatively small amount of data

Oracle9i Release 9.2.0.7.0 - 64bit Production
JServer Release 9.2.0.7.0 - Production


Database B is a production database with a large amount of data

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 – Production


Without going into too much detail the view in question (I will call VIEW_A) is along the lines of:

SELECT various data
FROM various tables
WHERE various conditions
AND EXISTS (SELECT view_b.column_name
FROM view_b
WHERE view_b.column_name_a = table_name.column_name_a
AND view_b.column_name_b = table_name.column_name_b)
GROUP BY various_data

View_b is itself based on another view

When selecting a particular row from view_a the explain plan on the production database (B) shows a MERGE JOIN/SORT JOIN:

SORT (GROUP BY)
MERGE JOIN
SORT (JOIN)

And goes on to show a full table scan from a large table

The explain plan from the test database (A) shows

SORT GROUP BY
FILTER

and selects from the large table via an index


I have looked on the internet for sources that might tell me why the two databases are behaving differently (e.g. parameter settings) but have not been able to resolve this. Could someone please at least point me in the right direction as to what I should be looking for.

Thanks

Re: Merge join verse Filter. Same code, different database, different explain plan [message #292386 is a reply to message #292149] Tue, 08 January 2008 19:19 Go to previous messageGo to next message
jeffrey_hu
Messages: 2
Registered: December 2007
Junior Member
I think the root cause is the db parameters set. please check the difference between the two db. especially as following:

optimizer_index_caching
otimizer_index_cost_adj
optimizer_mode
Re: Merge join verse Filter. Same code, different database, different explain plan [message #292391 is a reply to message #292386] Tue, 08 January 2008 20:23 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Search for Cost Based Optimiser. Oracle evaluates each query not only based on the SQL syntax, the table/index structures, the database parameter settings, but also the DATA.

If the data in the two databases is different - or if Oracle THINKS it is different because statistics have not been recently calculated, then it can come up with a different plan.

Ross Leishman
Previous Topic: query plan and negative value (-1) in where clause
Next Topic: Bock Inefficiency 100%
Goto Forum:
  


Current Time: Sat Dec 10 05:02:37 CST 2016

Total time taken to generate the page: 0.22695 seconds