Home » RDBMS Server » Performance Tuning » Oracle generates different execution plans for test and prod environments (Oracle Database 11g)
Oracle generates different execution plans for test and prod environments [message #584063] Wed, 08 May 2013 06:40 Go to next message
httr
Messages: 2
Registered: May 2013
Junior Member
Hi everybody,

This is my first post in this forum. I hope to post more topics in future.

I'm new in performance tuning also and confused very much because of wide scope of performance tuning.

I have a problem that i explained below and two questions:

There is a table with 50 million rows in both test and prod environments. I have created a bitmap index on a column ( FPOLK ) of this table in both environments. The column has 133 distinct values. Then I got statistics of table in both environments again. I wrote the query below and view the execution plan :

SELECT FPOLNO FROM PLM WHERE FPOLK = 374 GROUP BY FPOLNO

There is 313 rows in the table where fpolk = 374. So we expect execution plan use the bitmap index created. Test env. uses but prod env. does not use bitmap index. So my questions :

1. Why prod does not use index while test uses under same conditions?

2. In test env. cardinality does not change in execution plans that uses index even i change the value of fpolk in query.

SELECT FPOLNO FROM PLM WHERE FPOLK = 374 GROUP BY FPOLNO
SELECT FPOLNO FROM PLM WHERE FPOLK = 930 GROUP BY FPOLNO

Cardinality is the same for the queries above.

Thanks in advance
Re: Oracle generates different execution plans for test and prod environments [message #584064 is a reply to message #584063] Wed, 08 May 2013 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 58934
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.


For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Also, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: Oracle generates different execution plans for test and prod environments [message #584102 is a reply to message #584064] Wed, 08 May 2013 13:16 Go to previous messageGo to next message
Flyby
Messages: 144
Registered: March 2011
Location: Belgium
Senior Member
The index in prod is in a usable state? As Michel wrote above, post a testcase. Is it a regular bitmap index or a a bitmap join index ...
Re: Oracle generates different execution plans for test and prod environments [message #584245 is a reply to message #584102] Fri, 10 May 2013 08:42 Go to previous messageGo to next message
gazzag
Messages: 298
Registered: November 2010
Location: Bristol, UK
Senior Member
In addition to Michel's post, check that the initialisation parameters are the same on both databases.
Re: Oracle generates different execution plans for test and prod environments [message #584246 is a reply to message #584245] Fri, 10 May 2013 08:56 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
WHY MY INDEX IS NOT BEING USED
http://communities.bmc.com/communities/docs/DOC-10031

http://searchoracle.techtarget.com/tip/Why-isn-t-my-index-getting-used

http://www.orafaq.com/tuningguide/not%20using%20index.html
Re: Oracle generates different execution plans for test and prod environments [message #584253 is a reply to message #584246] Fri, 10 May 2013 09:45 Go to previous messageGo to next message
Roachcoach
Messages: 1198
Registered: May 2010
Location: UK
Senior Member
What happens if you port PRD stats into test?
Re: Oracle generates different execution plans for test and prod environments [message #584279 is a reply to message #584063] Fri, 10 May 2013 16:28 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
I usually run stats on 100% of the table and its indexes with the following command because it cuts down on the optimizer discrepancies between production and stage. The "cascade=>TRUE" will run stats on the table's indexes.
SQL > execute dbms_stats.gather_table_stats('ECSWEBD','ALAN',estimate_percent=>100,cascade=>TRUE);

PL/SQL procedure successfully completed.
Re: Oracle generates different execution plans for test and prod environments [message #584342 is a reply to message #584279] Mon, 13 May 2013 03:42 Go to previous messageGo to next message
httr
Messages: 2
Registered: May 2013
Junior Member
Thanks for all replies,

I solved the problem with a friend's suggestion. I was gathering statistics without deleting old statistics. Also I was gathering statistics with analyze table command. He told me that he experienced the same problem before. Firstly I deleted current statistics with DBMS_STATS.DELETE_TABLE_STATS and then gathered the statistics. Now Execution Plan uses the index.
Re: Oracle generates different execution plans for test and prod environments [message #584543 is a reply to message #584342] Wed, 15 May 2013 04:43 Go to previous messageGo to next message
pointers
Messages: 350
Registered: May 2008
Senior Member
Thank you for the feedback.

Regards,
Pointers
Re: Oracle generates different execution plans for test and prod environments [message #584574 is a reply to message #584543] Wed, 15 May 2013 12:54 Go to previous messageGo to next message
Michel Cadot
Messages: 58934
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@Pointers,

Yes unlike you he gives feedback.

Regards
Michel
Re: Oracle generates different execution plans for test and prod environments [message #584576 is a reply to message #584063] Wed, 15 May 2013 14:05 Go to previous messageGo to next message
pointers
Messages: 350
Registered: May 2008
Senior Member
@Micheal
This is unfair, I am big fan of you ...
However, I say full table scan is faster than index scan, ok show me ????
I say, show me the proof Smile ...
likewise, just show me where i dint give you feedback.

Regards,
Pointers
Re: Oracle generates different execution plans for test and prod environments [message #584577 is a reply to message #584574] Wed, 15 May 2013 14:07 Go to previous message
pointers
Messages: 350
Registered: May 2008
Senior Member
I always feel in encouraging some one or thanking some one (for someone passion) is also sort of encouraging, if I miss, I think it was not deliberate

Regards,
Pointers

[Updated on: Wed, 15 May 2013 14:11]

Report message to a moderator

Previous Topic: Is there any way to have the DB articulate why PDML is blocked
Next Topic: db cache advice 10g
Goto Forum:
  


Current Time: Fri Aug 29 09:17:04 CDT 2014

Total time taken to generate the page: 0.10259 seconds