Home » RDBMS Server » Performance Tuning » Bitmap Conversion (Oracle 10.2.0.3.0 on AIX)
Bitmap Conversion [message #536391] Tue, 20 December 2011 12:02 Go to next message
chuckstoker
Messages: 10
Registered: January 2011
Junior Member
PROD system and DEV system.
Exact same data (both downstream from the same GoldenGate replication)

Exact same schema stats (exported from PROD and imported into DEV)

Same SYSTEM Stats and init parms

Production explain plan simply uses an INDEX UNIQUE SCAN
DEV explain plan does a BITMAP CONVERSION and runs MUCH longer.

Of course I can use a parm and turn conversion off, but I'm at a loss to figure out why I'm getting two different plans. What am I missing?
Re: Bitmap Conversion [message #536392 is a reply to message #536391] Tue, 20 December 2011 12:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
exact, exact, exact produces same results
different result occur from something being different.
We can't say anything more since we have no details

alter session set events '10053 trace name context forever, level 1';

http://jonathanlewis.wordpress.com/2010/04/30/10053-viewer/

Re: Bitmap Conversion [message #536404 is a reply to message #536391] Tue, 20 December 2011 14:14 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Goldengate is executing DML: each database will have executed this according to the local circumstances at the time, so there will be divergence. The physical structures and row placement will not be the same. You will quite likely get different exec plans (irrespective of the stats) if only because of optimizer dynamic sampling.
Probably the only solution is to remove DEV, and clone it from PROD.
Re: Bitmap Conversion [message #536410 is a reply to message #536404] Tue, 20 December 2011 14:52 Go to previous messageGo to next message
chuckstoker
Messages: 10
Registered: January 2011
Junior Member
Thank you. That is kind of the answer that I was looking for.., although exec plans do match a vast majority of the time and I can usually account for the reasons behind the differences. This is a vendor database, so I'm extremely limited in what I can accomplish.

PROD started to return rows..., DEV just sat but I was able to get the optimization information.
This is a 6tb database and the table in question is one of the largest.

One thing that immediately stood out is that there are no workload stats in DEV. I will change that to see if it helps.
The trace files are over 17mb each, otherwise I would attach them. The 10053 trace and viewer are VERY informative. Thanks for that.

Re: Bitmap Conversion [message #536548 is a reply to message #536410] Wed, 21 December 2011 07:28 Go to previous messageGo to next message
chuckstoker
Messages: 10
Registered: January 2011
Junior Member
Just to follow up.
I re-gathered workload statistics and it resolved the bitmap conversion issue.
Thank you both.
Re: Bitmap Conversion [message #536553 is a reply to message #536548] Wed, 21 December 2011 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thank you for the feedback.

Regards
Michel
Re: Bitmap Conversion [message #537350 is a reply to message #536553] Tue, 27 December 2011 19:46 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
This is interesting. May I inquire for a little more detail when you say:

Quote:
I re-gathered workload statistics


What does this actually mean?

Kevin
Re: Bitmap Conversion [message #537448 is a reply to message #536391] Wed, 28 December 2011 07:16 Go to previous messageGo to next message
chuckstoker
Messages: 10
Registered: January 2011
Junior Member
I had originally manually updated the system stats to match the production environment:

select * from sys.aux_stats$;

SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ----------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 11-16-2011 02:00
SYSSTATS_INFO DSTOP 11-16-2011 08:00
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 1944.76987
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM 6.558
SYSSTATS_MAIN MREADTIM 2.143
SYSSTATS_MAIN CPUSPEED 421
SYSSTATS_MAIN MBRC 16
SYSSTATS_MAIN MAXTHR 11873280
SYSSTATS_MAIN SLAVETHR

These obviously did not represent the true workload or the server stats. I gathered system stats during an actual workload and the bitmap conversions were eliminated.

-- cron to start at 2am
execute dbms_stats.gather_system_stats('Start');

-- cron to stop at 3am
execute dbms_stats.gather_system_stats('Stop');


SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ----------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 12-21-2011 02:30
SYSSTATS_INFO DSTOP 12-21-2011 03:30
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 1233.01
SYSSTATS_MAIN IOSEEKTIM 17.792
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM 5.714
SYSSTATS_MAIN MREADTIM 14.316
SYSSTATS_MAIN CPUSPEED 1264
SYSSTATS_MAIN MBRC 85
SYSSTATS_MAIN MAXTHR 9688064
SYSSTATS_MAIN
SLAVETHR
Re: Bitmap Conversion [message #537463 is a reply to message #537448] Wed, 28 December 2011 09:49 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks, excellent reply. This is what I thought you meant but I needed to make sure.

Kevin
Previous Topic: Query running long time
Next Topic: Tuning a small but complicated query
Goto Forum:
  


Current Time: Fri Mar 29 04:13:44 CDT 2024