Implied Restriction on Number of GROUP BY Columns in Materialized View

pranavgovind hit this problem, as described on the DBA Support.com forum, wherein Oracle ’s attempt to index all the columns of an aggregating materialized view fails when more than 32 columns are included in the GROUP BY, and the error “ORA-01793: maximum number of index columns is 32″ is raised.

I added my own thoughts there, but this is symptomatic of some more general failings in the area of materialized views in which the creation and refresh codes do not take as much advantage of chances to optimise as a human could.

For example, consider a materialized view built against a fact table joined to a date hierarchy, so as to define the MV query like this. In such a case only the date_of_month column needs to be indexed — the inclusion of any other columns represents a waste of resources. In fact in this case the index ought to be declared to be UNIQUE, whether or not the Sys_Op_Map_NonNull() function is used on the column in order to be compatible with Oracle’s recursive SQL for refreshing materialized views. However Oracle would wastefully index all four of the GROUP BY columns.

Vacations for Young People

Tomorrow I go off to re-attempt my previously postponed break. In the time since I made my plans I have learned a couple of pertinent facts:

1. Gunnison, my first camping spot, lies in a valley that collects cold air and is apparantly one of the coldest places in the continental United States.
2. “Concordantly”, on Wednesday morning temperatures of 40°F/4°C are expected.

That’s some chilly camping and motorcycling weather, so now I have to take my electrically heated fleece, a liner for the sleeping bag, and put the brush guards back on the motorcycle (the true sign that winter has arrived in the Sponge household). However, I’m not looking for some glorified rest home for the over 40’s for my vacations yet — I’ve at least few months before I need to swap this in for this.

Since I’ll be down at the camping store, maybe I’d best pick up some bear spray as well — they’ll be getting peckish this time of year. Warning — fairly graphic imagery here. Not too bad but still, it makes you think.

Parameter Changes Between 9.2.0.1.0 and 10.2.0.2.0

I loaded the respective SYS.X$KSPPI data from 9.2.0.1.0 and 10.2.0.2.0 into regular user tables for the purpose of comparison, so here are some brief-but-fun statistics.

9.2.0.1.0 has 797 parameters, of which 540 are hidden and 257 are not.

10.2.0.2.0 has 1,410 parameters of which 1153 are hidden and 257 are not.

The two versions have 733 parameters exactly in common.

One hidden parameter from 9.2.0.1.0 was “promoted” to non-hidden in 10.2.0.2.0: _instance_type.

9.2.0.1.0 has two non-hidden parameters for which there are hidden counter-parts (ie. “parameter” and “_parameter” both exist):

• cpu_count
• db_block_buffers.

10.2.0.2.0 has six non-hidden parameters for which there are hidden counter-parts:

• db_block_buffers
• db_cache_size
• java_pool_size
• large_pool_size
• shared_pool_size
• streams_pool_size

10.2.0.2.0 has 647 hidden parameters that 9iR2 does not. Too many to list here, but the most popular first words of the parameter names are:

• 46 x optimizer
• 41 x xsolapi
• 36 x db
• 22 x olap
• 16 x gc
• 16 x disable
• 16 x flashback
• 15 x asm
• 14 x px
• 13 x lm
• 13 x log
• 12 x enable
• 11 x bct
• 10 x memory
• 10 x simulator
• 10 x olapi
• 8 x ash
• 8 x cursor
• 8 x kgl
• 8 x dump

A clear win for the optimizer, I think. Here is the full list.

And here is the full list of 10.2.0.2.0 parameters

Finally, the longest pparameter name in 10.2.0.2.0 is “_xsolapi_materialization_rowcache_min_rows_for_use”: an impressive 50 characters.

Upgrade Analysis and Virtualisation.

It seems that with every passing month I spend more and more of my time thinking about the cost-based optimizer, which makes the continual changes to it with different Oracle versions a rather bitter-sweet experience. I get the pleasure of learning new “stuff” and the downside of realising how much of what I know is going to be redundant.

My client is soon upgrading from 9iR2 to 10gR2, and that is keeping me hopping with plenty of analysis and planning. Today I had the pleasure of running some more tests to produce some formal analysis of the upgradebenefits. I based the work heavily on two Oracle white papers that each describe optimizations with the move from 9iR2 to 10gR1 and from 10gR1 to 10gR2. So the work was really a consolidation of the two, minus some features that I thought the client wouldn’t benefit from.

The major performance benefits that I expect from the upgrade are, in order of advantage:

1. Hash-based GROUP BY: This is a major benefit because GROUP BY is ubiquitous within the data warehousing environment. Wwe use it in generating summary data and in virtually every end-user query (except where the presence of a summary allows it to be optimised out of the execution plan).
2. In-memory sort optimisation: I measured some very impressive results in sorting large data sets. This is mostly an ETL benefit as the reports in the clients environment generally sort only small results sets, but where the ETL process does sort it is for the purpose of promoting physical row ordering of fact tables. The amount of data to be sorted is generally very large and the sort key is often many columns wide.
3. Pruning of disjunct-OR predicates: 10gR2 has some optimisations that improve the efficiency of pruning based on non-contiguous predicates placed on dimension tables. Many users will benefit from this for queries that require data between January 2006 and MArch 2006 OR between January 2005 and March 2005 — ie. year-to-year comparisons.

Aside from these transparent optimizer issues we are planning on eliminating our use of materialized view in favour of hand-coded summary table maintenance and use of DBMS_ADVANCED_REWRITE. In fact we already use hand-coded maintenance of the summaries, which yielded reductions in the update times from several hours down to several minutes, but not having the materialized view placed on them will make the management of them easier to comprehend for those .. um … at the back of the class.

Lastly there are some significant changes to initialisation parameters that affect partition pruning, so I’ll be having a close look at those as well.

So on to virtualisation.

Because of this increased focus on optimisation I find myself needing more and more specific versions of Oracle to test on, and because the maintenance of many versions on my regular system causes havoc with other applications such as Business Objects and Informatica I’ve gone virtual with the use of VMWare — a path that saner minds have been advocating for quite a while. You know who you are ;)

I’ve standardised myself on the use of CentOS 4 as an operating system, as it has not let me down before, but I’ve also got some OpenSuse 10.1 and Solaris 10 VMs running in case I need a diversion. The plan is to maintain Oracle versions as follows:

1. The same as the client’s production system (say, 10.2.0.2).
2. The “vanilla” version of the client release (10.2.0.1).
3. The “bleeding-edge” version of the client release (10.2.0.3).
4. Other significant releases, such as vanilla and bleeding-edge 10.1 and 9.2.

That ought to smooth the bumps on the road of analysis, anyway, and thanks to some help from HJR with networking I can access them all pretty seamlessly from the host machine (XP Pro with 1.5G of memory and 1Tb spread over five disks, by the way).

One handy trick I’ve found is that VMWare will mount the ISO files that most distributions provide, avoiding the need for burning an actual disc and allowing multiple VMs to have access to different ISO’s at the same time. If you have a capable burner — and my own choice is the clunky but reliable Cheetah — then you can also transmogrify the downloaded files from Oracle for installs and patchsets into ISO format and mount them as well, so you don’t have to burn them or ftp them or whatever. Handy stuff.

PL/SQL Exception Handling Performance

Tom Kyte wrote about the evils of WHEN OTHERS as an exception handling mechanism, and I thought I’d run a quick test to see if there were performance implications of different methods.

I used a function to test whether a string represents a number that is convertable by To_Number() in PL/SQL, and used three methods of handling the error.

1. WHEN OTHERS
2. Using the predefined VALUE_ERROR exception (not INVALID_NUMBER, which is the error that would be raised in SQL).
3. Using a user-associated named exception.

I ran each function 1,000,000 times for input values that were and were not convertable to numbers, and ran each test three times to check repeatability.

Test script

Script output

The script output shows that there was no appreciable difference between the three methods.  Hopefully that’s another nail in the coffin of WHEN OTHERS, if any more were needed.

Feedburner Update

Peter Scott accidentally reminds me that:

1. I have a Feedburner feed
2. I haven’t updated it to reflect the move to WordPress

Apologies to anyone using Feedburner who thinks that I must have been hit by a bus a few weeks ago. Go to http://oraclesponge.wordpress.com to catch up.

Mini-break Cancels Mini-break

As I was strapping sleeping bags and whatnot onto the bike yesterday evening, the gods intervened in my plans by toppling Number Two Son from his bicyle in a low-speed tumble. Result: “spiral fracture of the tibia“.

Ouch.

So he has a handsome short leg cast on the port side and will be of limited mobility for the next week.

Well at least it didn’t happen this evening, when I was a few hundred miles into the mountains and the wife was baby-sitting several other kids.

Whoops!

I just realised that I made a silly error in the Year-to-date transformation table demonstration scripts. It doesn’t affect the validity of the conclusions, thank goodness, but I’ll correct it shortly.

The error was that in the select statement I should have been grouping by the date_of_sale, not the month_of_sale, so the numbers are rather meaningless as they stand. Ironic really, as I’m often lecturing people on the topic of paying attention to the dimensionality and meaning of the result sets in such situations.

Tut!

Getting Away From It All

Tomorrow I leave for my own personal mini-break — two days of riding around the mountains of southern Colorado with nothing to think about but the scenery, the roads, maybe the occasional thunderstorm etc..

Leaving around noon on Friday I’ll be off to Gunnison, arriving in time to pitch tent and go off exploring the awkwardly named Black Canyon of the Gunnison National Park.We had a brief look around on the way back from the Grand Canyon a year or two ago, and it struck me as worthy of a few more hours of my time. Or I might just settle down with a good book instead.
I’ve also had a hankering for travelling the San Juan Skyway before I leave Colorado so that’ll be a solid day’s ride on Saturday, ending in Ouray, one of the most picturesque towns of the “”Little Switzerland” region.

Sunday brings a long ride home again across most of the breadth of the Rockies, hopefully in time for a shower before going out to dinner at a friend’s house. Yes, there will be a baby-sitter, so that should round off a pretty good weekend.

I shall bore you with blatant fabrications about my heroics — saving school bus from mountain lions etc — when I return.

Year-to-Date Query Improvement Through Transformation Tables

A quick comparison here between two methods of deriving year-to-date metric values from a data warehouse fact table.

Suppose we have a simple fact table of the following form: