OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Bitmap Conversion (Oracle 10.2.0.3.0 on AIX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Bitmap Conversion [message #536391]

Tue, 20 December 2011 12:02

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?

Report message to a moderator

Re: Bitmap Conversion [message #536392 is a reply to message #536391]

Tue, 20 December 2011 12:15

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/

Report message to a moderator

Re: Bitmap Conversion [message #536404 is a reply to message #536391]

Tue, 20 December 2011 14:14

John Watson
Messages: 8596
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.

Report message to a moderator

Re: Bitmap Conversion [message #536410 is a reply to message #536404]

Tue, 20 December 2011 14:52

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.

Report message to a moderator

Re: Bitmap Conversion [message #536548 is a reply to message #536410]

Wed, 21 December 2011 07:28

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.

Report message to a moderator

Re: Bitmap Conversion [message #536553 is a reply to message #536548]

Wed, 21 December 2011 08:07

Michel Cadot
Messages: 67950
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Thank you for the feedback.

Regards
Michel

Report message to a moderator

Re: Bitmap Conversion [message #537350 is a reply to message #536553]

Tue, 27 December 2011 19:46

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

Report message to a moderator

Re: Bitmap Conversion [message #537448 is a reply to message #536391]

Wed, 28 December 2011 07:16

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

Report message to a moderator

Re: Bitmap Conversion [message #537463 is a reply to message #537448]

Wed, 28 December 2011 09:49

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Sep 26 01:13:14 CDT 2021