Archives of the TeradataForum

Message Posted: Wed, 27 Jun 2012 @ 16:26:51 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

q) I noticed your comment about cost profiles and was wondering if you have more detail on the impact of differences. We have just upgraded our Production system from a 5500H running 13.01 to a 6650H running 13.10 and I did notice that they have a different value in the cost profile setting but was not sure if it needed to match or not. We seem to be experiencing a lot more skewing issues on the new system and while our benchmark tests ran better on the new system, we are seeing some queries run significantly worse.

a) Mark:

w.r.t to cost profiles

cost profiles are used to represent platform characteristics and there is an old but informative orange book(customer viewable) on OCES type 3 costing. I am sorry but not aware of a detail document of what each field represents.

It's more of an internal as far as I know & do not have any idea on document to my knowledge. If you do not have multiple cost profiles on the system - and you are using default one(which was provided with TD version) this may be easier to zero-out .Note that , parameters in cost profiles could impact the optimizer join planning/costing when the various co-efficient' s considered. If it is lacking to/cannot leverage new or advanced equipment(CPU / node power) and then that's a worry.

I am quite sure TD have never left a shortcoming out there.

If there are multiple profiles you can zero out which is worst effected from historical data in DBQL / w.r.t user. See if anything can be done at user cost profile.

skewing

Can start analyzing the query behaviors one by one which have worst effected and start analyzing the ones which are missing SLAs (or) worst effecting in terms of skew as in this case. Seeing that the stats are current and still you see the degradation. Look in to need for any new stats/multi-column stats / indexes which might help. We have come across scenarios where these recommendations helped many queries after an upgrade.

Running an explain through HELP STATS diag is always good and highly recommended. If you have not done already.

Additional note:

Sorry for sidetracking but I just want to put my thoughts together to explore other reasons as well.

I would start to think it from configuration point of view. Regarding the AMPS and PEs(were there any changes) or increased ? OR change in schema ? co-existent ? And are they balanced ?

Believing they are on a similar configuration w.r.t AMP/PEs and you have only upgraded hardware , version and your data is stable and stats are current/refreshed.

Since you were on an older release - you are certainly going to see some difference in behavior until they are fine tuned.

Step 1 -

I would think of looking/analyzing it at from Workload they are going on to[TASM](live), ResUsage and DBQL(historical) point of view as well, if the hit of perf is at system level.

Step 2-

Keep an eye on changing data.

Last resort is to get in touch with support person to have someone to look it in detail :).

Hope it helps.

Thank you,

Asif
TD gsc

	<Prev	Next>		<<First	<Prev	Next>	Last>>