Tuesday, April 26, 2011


Oracle RDBMS environment is too too big to know everything - as a result from time to time I just stumble at   some functionality that existed for years but I never heard of it! My today's finding is 'ASSOCIATE STATISTICS' command (more details here).

Briefly, it allows to hard-code some statistics (like cost or cardinalities) to different Oracle objects - columns, functions, packages, indexes etc. And this statistics will directly impact CBO's decisions - as any other statistics! Since in Dulcian we use a lot of PL/SQL, this feature could significantly simplify my tuning efforts in cases when we have functions inside of SQL queries.

Minor issues:
1. It looks like statistics cannot be associated with a separate function in the package - only standalone function. Too bad - will have to have logical wrappers!
2. Statistics can be associated with a package. Hmmm... Not sure that I understand that logic (and I was not able to find meaningful examples).

Will keep you posted on further discoveries - but it seems to be a nice add-on to my list of tricks (available in both 10g and 11g!)

No comments: