Home » RDBMS Server » Performance Tuning » Explain Plan
Explain Plan [message #271181] Sun, 30 September 2007 09:52 Go to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

All,
There are two environments E1 and E2. I fired a query involving 12 tables in both the environment. I got different explain plan. Difference was in E2, two tables doesn’t uses index and there appears to be full table scan.

Query is running fast in E1 than in E2. I compared the constraint and indexes on both environment and didn’t find any difference.

I'm confused why optimizer it not picking the indexes in E2 but it does in E1.

Your suggestions would be highly appreciated.

Thanks,
Naveen

[Updated on: Sun, 30 September 2007 09:54]

Report message to a moderator

Re: Explain Plan [message #271182 is a reply to message #271181] Sun, 30 September 2007 09:59 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Your suggestions would be highly appreciated.
Your post is devoid of meaningful content.
We are not standing behind you & don't know what you know about either E1 or E2.

What are the same between E1 & E2?
What are the differences between E1 & E2?

The differences are what result in the different behavior.

Re: Explain Plan [message #271183 is a reply to message #271182] Sun, 30 September 2007 10:15 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

sorry about that..

E1 and E2 are oracle 9i databases..

-> What are the same between E1 & E2?
I checked the indexes and contraints for all the tables involved in the query. They are same.

Regards,
Naveen
Re: Explain Plan [message #271184 is a reply to message #271181] Sun, 30 September 2007 10:20 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I checked the indexes and contraints for all the tables involved in the query. They are same.
What about the underlying data? Does the same data reside in both DBs?
What about statistics on both the tables & indexes? Are they the same?
Re: Explain Plan [message #271186 is a reply to message #271183] Sun, 30 September 2007 10:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about the server?
What about the OS?
What about their patch levels?
What about their memory?
What about their configuration?
What about their disks?
What about their controllers?
What about their workload?
What about Oracle configurations?
What about their patch levels?
What about their *size* parameters?
What about their *target parameters?
What about...

Regards
Michel
Re: Explain Plan [message #271518 is a reply to message #271184] Mon, 01 October 2007 16:54 Go to previous message
OracleDisected
Messages: 25
Registered: September 2007
Location: Mexico
Junior Member
Both environments are running under Cost Based Optimizer?
... in order to use statistics

If data (row count level) is substantially different, then it will affect statistics (even if they are up-to-date on both environments).

If row count for every table is the same, or scaled by an almost equal factor, there is no impact. Just check if your statistics are up-to-date.

Regards
Ignacio

[Edit MC: url to poster blog removed. We don't care about your blog in this post. If you have an answer on your site, post a link to this answer. If you don't, post your blog url in Marketplace]

[Updated on: Thu, 11 October 2007 12:40] by Moderator

Report message to a moderator

Previous Topic: row_locking
Next Topic: Rollback per transaction % too high and a lot db file sequential reads
Goto Forum:
  


Current Time: Thu Dec 08 20:12:32 CST 2016

Total time taken to generate the page: 0.13522 seconds