Oracle Sponge -- Now Moved To Wordpress

Moving to WordPress

2006-06-12T15:31:00.000-06:00

Blogger irritates me. Let's see if Wordpress makes me happy.

http://oraclesponge.wordpress.com/

I've used the supplied import functionality to move over posts and comments, and it seems to have gone pretty well. Internal links need to be resolved to the new UR, and some text formatting has gone astray, but I can work my way through that.

The category functionality is great, and the interface is much more snappy, so I'll probably stick with it.

Modelling of Condominiums

2006-06-02T13:45:00.000-06:00

A condominium in this case being a geographical area of which two of more sovereignties agree to share dominium, but that title ought to do wonders for my statistics.

Anyway, I was thinking about the mapping between cities and zip codes today, which is a many-to-many relationship, and the thought popped into my head, "I wonder if there are any cities which belong to multiple countries?" In other words, are there any cities which are part of a condominium?

So I went looking for one, not expecting to find any, and I was right -- there appear to be none. Probably a good job too.

In fact the only decent example of a condominium currently in effect seems to be that of Pheasant Island, which is a condominium of France and Spain. It apparantly is shared on a six-monthly basis between the two nations, which leads to an interesting modelling problem -- a cyclic time-dependent relationship between geography and sovereignty. In other words, it's a time-share! (Stats again).

Anyway, I'll leave that as "an exercise for the reader".

It also raises anther question: back when one needed a passport to cross between the two countries, was a crime commited if a passport-less person was present on the island at the time that sovereignty changed? Perhaps none -- perhaps the condominium agreement allowed people of either nationality to be present on the island at any time, regardless of which state currently was in charge of it. But if the agreement did not allow this then could you charge a person with crossing a border if they were stationary at the time the offence occured?

These thoughts usually turn-up on Fridays, oddly enough. Must be something to do with blood sugar.

The Prime Number Few

2006-05-22T15:08:00.000-06:00

I was chasing links through Wikipedia the other day, which always leads somewhere interesting.

In this case I fell into the deep waters of linguistics and learned about garden path sentences. They caught my eye in particular because the article includes one of my favourite jokes, albeit with a slightly different construction to my usual one.

"If time flies like an arrow, do fruit flies like a banana?"

Now that I've read a deconstruction of the joke, it seems a little less funny though.

The Three Pillars of Oracle Data Warehousing

2006-05-18T07:00:00.000-06:00

Introduction

This is a basic topic for Oracle data warehousing beginners, based on some ideas that I'm hoping will stop buzzing round in my head if I commit them to virtual paper.

There are three Oracle features that provide a foundation for successful data warehousing:

Partitioning
Parallelism
Psummary Tables (the "p" is silent)

Here are the benefits that they bring to the system.

Partitioning

In a data warehouse we are interested in accessing large numbers of rows that are identified through some common attribute. Very often the same small numbers of attributes are used over and over again to filter the data. The most common is some form of date filter ("give me sales for this January"), and there are often others that are used very commonly ("give me inventory levels for repairable items", "give me backorders for California").

In an OLTP system we would use an index or a full tablescan to get this subset of data, but that can be inefficient if we are selecting a large number of rows representing a subset of the total table data because of the high number of logical i/o's required and the single block reads that are used to access the table.

In a data warehouse we can partition according to attributes commonly used as filters by the users, or according to logical child attributes of them (eg. users commonly filter by month but we partition by day). This achieves two important aims.

Rows of data that are commonly used together are physically co-located.
The optimizer can treat each partition as a table and can perform fast scans of multiple partitions to access the data ("partition pruning").

We can also use multicolumn or composite partitioning to partition by multiple attributes, so that we can partition both by date and by location and get partition pruning on either or both.

There are also benefits to manipulating bulk data -- we can use partition-based DDL operations to load and unload data from a table (a partition exchange) or to delete old data (partition drop or truncate).

Parallelism

In a parallel query operation the physical locations in which the required rows are held are subdivided into a number of ranges, each of which is then scanned by a different process
(a parallel query slave). The processes that read the data then pass it on to either a single query coordinator process or to another set of slaves which themselves pass the results to the query coordinator.

Insert, update and delete operations can also be parallelized to provide faster bulk data changes.

One of the key differences between serial and parallel queries are that the parallel queries read data directly from disk instead of checking for the required blocks in the SGAs block buffer area, and pass the result set directly to the user's PGA, thus completely bypassing the SGA. Read consistency is maintained through the query coordinator requesting a checkpoint to have commited dirty buffers of the scanned object written to disk before the PQ slaves start reading.

The coordination required in creating and controlling query slaves leads to an overall increase in resource usage which can easily overload a system, but when correctly implemented spare system resources can be used to improve the response time of queries that access or manipulate a large amount of data.

You probably need a lower degree of parallelism than you expect.

Psummary tables

OK, "summaries" then.

The static nature of data warehouse data allows the results of some frequently executed queries to be pre-computed and cached as summary tables. These are often created through the Oracle materialized view object type, although the use of an MV is not mandatory.

The query rewrite function of the cost-based optimizer can use the metadata stored in materialized view definitions or through declarations of query equivalence made through the DBMS_ADVANCED_REWRITE package (10g+ only) to redirect queries against large base tables (typically data warehouse fact tables) to the smaller summary tables. In most cases the summary table does not have to be an exact match for the query.

The major challenge in using summary tables is in finding an efficient mechanism for maintaining consistency between the summary and its base table(s).

The major benefit is that almost any end-user query can be executed extremely quickly with the correct use of summary tables.

PSummary

Partitioning allows efficient access to relatively large subsets of data and efficient bulk manipulation of data.
Parallelism allows the work of a single user process to be shared among multiple slave processes, leading to faster completion time but higher resource usage.
Psummaries provide extremely fast performance, but their maintenance and monitoring can require non-trivial efforts.

Now, hopefully these thoughts will leave my head alone.

Strange Thing To Carry

2006-05-18T06:00:00.000-06:00

My kids (3, 4 & 6) intercepted and detained a pedestrian passing our house the other evening on the grounds that he was in Public Possession of Dogs, and they needed to subject him to the usual line of questioning: "Names?", "Ages?", "Boys or Girls?", "Do they bite?" etc..

Strangely none of them noticed what I saw immediately, which was the holstered handgun on his hip. I'm sure he had a license to do so, but what he was expecting might happen to him in our Nice Suburban Neighborhood is anyone's guess.

On the other hand one of my wife's co-workers reported a sighting of a mountain lion about two miles southeast of us a couple of years ago. I'm inclined to wonder whether *ahem* alcohol was a factor in that case because we're definitely on the wrong side of the city for that kind of wildlife -- antelope, deer, foxes and the occasional coyote maybe, but how a lion would pass unnoticed around Colorado Springs from the mountains on the west to the plains on the eastern side is a mystery to me.

However the mountains to the west of us aparantly have the highest density of mountain lions throughout the Rockies, which leads to some basic precautions when hiking with the kids -- don't let them straggle or walk too far in front is all it really amounts to. (That link contains other interesting information on bubonic plague, hanta virus, avalanches etc. by the way). Sightings and other less fortunate encounters seem to be pretty common up around Boulder though.

Here's a prime example of the kind of plump, tender morsel that a lion would enjoy most.

It's amazing how fast those legs will carry him if he's told that there's a lion behind him!

Getting the (Sub)Partition Name for a Row

2006-05-11T11:14:00.000-06:00

Adapted from a response to a question posted on Oracle-l, here are three methods for finding out what partition or subpartition a table row is stored in, or which partition or subpartition a row will be stored in.

Logical Inference

For range-based or list-based partitioning or for range-list composite partitioning it is often feasible to infer the partition or subpartition name from the values of the partition and subpartition key columns, as long as a sensible partition naming convention has been chosen.

For example, for a range partitioned table defined in part by ...

Create table T (col1 date ...)
Partition By Range (col1)
(
Partition Y2006_M01 values less than (date '2006-02-01'),
Partition Y2006_M02 values less than (date '2006-03-01'),
Partition Y2006_M03 values less than (date '2006-04-01'),
...
)

...we can use a SQL function to provide the partition name:

To_Char(col1,'"Y"YYYY"_M"MM')

Note the use of double-quotes in the above formula to identify literal strings in the date format.

This is a fast and simple method, although an historically varying granularity on the partitions or a complex partitioning scheme could make it difficult to maintain.

DBMS_MView.PMarker

In cases where it is difficult or impossible to do perform the logical inference (hash partitioning, for example, or with complex multicolumn range/list partitioning) there are a couple of other techniques that you can use.

In the DBMS_MVIEW package there is a PMARKER function that returns the data_object_id for the object in which the row resides, and you can join to user/dba/all_objects using that.

Here's a funky example script using variable numbers of hash subpartitions ...

drop table t
/

create table t (col1 , col2 , col3 )
partition by range (col1)
subpartition by hash (col2)
(
partition p1 values less than (2) subpartitions 2,
partition p2 values less than (3) subpartitions 4,
partition p3 values less than (4) subpartitions 8
)
as
select mod(rownum,3)+1,
      floor(dbms_random.value(1,256)),
      floor(dbms_random.value(1,10))
from   all_objects
where rownum < 101
/

with   obj as
      (select --+ materialize
              data_object_id,
              subobject_name
       from   user_objects
       where  object_name = 'T' and
              object_type = 'TABLE SUBPARTITION')
select subobject_name,
      col3
from   T,
      obj
where  data_object_id = DBMS_MView.PMarker(t.rowid)
order by 1
/

My unsubstantiated guess is that this uses the file#/block# of the rowid to perform a look-up on the extent that contains the row. It therefore also ought to be possible to "hand-roll" a similar method based on extracting the file# and block# from the rowid's and joining to the dba/user/all_extents view (or a materialized subset of it, for performance reasons) to get the segment properties. I don't know if I'd care to go that route myself.

TBL$OR$IDX$PART$NUM()

The TBL$OR$IDX$PART$NUM() function gives you the appropriate partition number for a table value or set of values. It's an undocumented (except through metalink, if that counts) function with a couple of magic numbers in it, but the general format to use is ...

TBL$OR$IDX$PART$NUM("PARTITIONED_TABLE_NAME", 0, d#, p#, "COLUMN_NAME")

The easiest way to get the appropriate format and magic numbers to use for this function is to run a trace on a "with validation" partition exchange against the table of interest, and you'll pretty much get the complete SQL that you need.

So you might end up with something similar to ...

with   utp as
      (select --+ materialize
              partition_position,
              partition_name
       from   user_tab_partitions
       where  table_name = 'MY_TABLE')
select utp.partition_name,
      last_name,
      first_name
from   my_table,
      utp
where  utp.partition_position = TBL$OR$IDX$PART$NUM("MY_TABLE", 0, 0,
65535, "PART_COL")
/

This method has the advantage that you can just supply it with arbitrary values for the partition key column(s) and you will get the appropriate partition number/name. Therefore you can use it to answer the question "Which partitions will these rows go into?". If you use partition exchanges "without validation" then you can also use it to run periodic checks on whether your ETL process has been putting rows in the wrong partitions.

The disadvantage is that it is undocumented and has those pesky magic numbers, or course.

Summary

My preference is to use the method of logical inference, if possible.

The other two are evidently based on very different methodologies -- one being based on the physical location of the row and the other on values of the partition key column(s), so they can be applied to different situations. The TBL$OR$IDX$PART$NUM method is rather more amenable to performance tuning by pre-aggregating the table data based on distinct partition keys prior to applying the function call.

Machine Music

2006-05-03T09:28:00.000-06:00

Oooh, this is nice.

http://video.google.com/videoplay?docid=-5503582578132361295

A Day Without Immigrants

2006-05-01T06:39:00.000-06:00

So today is apparantly "A Day Without Immigrants" and I just realised that this means me as well, technically speaking. Does it not seem strange that so immigrants should be held to be the source of so many problems in America, of all countries? And people in the southern border lands appear to be particularly prone to that finger-pointing, when they are living on land that was originally part of a Mexico too weakened by its own war for independence to be able to defend it from the young states to the north.

I'm coming up to the eighth anniversary of my "stepping off the boat" as it were -- it's easy to work out as I just add on one year and a couple of days to my wedding anniversary, which is how long it took for my permanent resident visa to come through, plus a couple of weeks to get the last things packed and get a flight. I missed my first wedding anniversary by just five days I think. From the visa (and later job) interview questions I was asked I was apparantly suspected of being an economic migrant, which made the enormous paycut that I took to move from London, UK to Dayton, Ohio rather ironic.

On the other hand, I don't really think of myself as an immigrant. I'm think I'm really someone who is just hanging out here for a while ... seeing the sights, doing a bit of shopping, being married, having kids etc.. On the other hand I've only been back to England once in all that time, for my brother's wedding in Cambridge last year. With my parents living in Spain and my parents-in-law living near Rome we tend to pay fleeting visits to Heathrow every now and then just to see if they've finished building it yet (which they haven't) and to stock-up on the few goodies that are available there and not from our local Fine Imported Goods emporium -- ie. the commissary at Peterson AFB. They sell Flakes, Crunchies, Milk Chocolate Hobnobs etc, although they fly off the shelves because everyone panic-buys them apparantly.

So anyway I get itchy feet everytime I see Oracle blogsters writing of their travels, and we're regularly overcome by an urge to move back to Europe. Any part of it at all will do. We were recently thwarted in an attempted move to Slovenia or Romania, and now we're trying for Germany. My brother tells me we should be moving to Denmark -- in fact a friend of a friend ended up there after some kind of incident involving rowing to Iceland in a replica of a Viking longboat, during which voyage he apparantly had an affair with the wife of the group leader which must have involved either extraordinary levels of discretion or some extraordinarily tense mealtimes -- but frankly it's probably Hobson's choice where we land. If the USAF doesn't have positions for a Developmental Engineer at least available then it's not on the list.

So where was I?

Ah yes, the immigrant day thing. Well it turns out that most of my work colleagues will be in a Customer Acceptance Test for most of the day so they wouldn't notice me not being here (here being 1,200 miles from them) anyway. However, I applaud the priniciple and I may go down the Monica's Taco Shop for a breakfast burrito to show support for the cause.

Welcome

2006-04-29T08:10:00.000-06:00

Welcome to the Dark Side, Mark!

Whoops, security breach.

2006-04-27T22:47:00.000-06:00

Well it turns out that thanks to "Homeland Security" your complete driver's license information may be on the Internet ... although the good news is that you can get it taken off. It seemed like such a good idea that I've taken mine off.

Go here, search for it, and go through the "Hide my Records" procedure if it's there. What is wrong with these people?

I already checked for George Bush of Texas, but I suppose there's a few of them and the "real" one isn't listed. If you're quick though (ie. quicker than him) you can get yourself a copy of the license of Mr Thomas Kyte of Leesburg, Virginia before he gets the records removed ... that's quite the photo!

A Dose of Stupidity

2006-04-26T10:28:00.000-06:00

Natural selection is a fine thing, but when a person's stupidity and arrogance takes someone else's life we can only hope that there will be some serious repurcussions.

http://www.kcsg.com/story.aspx?id=682

No helmet, no protective gear, and 85mph in a 35mph zone on a new machine? At the very least I hope this guy got skinned well enough that he'll need a helmet to get women to look at him in future.

More Security For You Sir.

2006-04-26T07:44:00.000-06:00

http://www.computerworld.com/securitytopics/security/story/0,10801,110880,00.html

Looks like some kind of security administrator role being introduced. $20,000 per CPU or $400 per user seems a littlesteep to me though.

Soon Time to Move Again

2006-04-19T08:00:00.000-06:00

It's that time again, when the wife and I have to fill in forms and wishlists to try and influence our next posting. Last time we got pretty lucky - Colorado Springs wasn't on our list but it turned out pretty well.

The eternal problem is that wherever there are Air Force bases, all commerce other than the defence industry tends to flee. It's a trend that is continuing, accelerating even, with the consolidation of Air Force units into the larger bases with plenty of room for growth -- ie. in the middle of a God-forsaken wilderness with a 60 mile commute to the nearest habitation.

That isn't to say that they aren't good places to live -- Eglin AFB in Florida is next to one of the best beaches in the world, and Vandenburg AFB is nicely positioned on the California coast. Still they are not exactly major centres of commercial high-tech activity. In Colorado Springs I landed on my feet with a work-at-home contract with a longterm client, but that sort of thing wears thin after a while. Pretty quickly actually, especially in conjunction with a move to a new town.

The current hot prospect looks like Hanscom AFB, just outside Boston, or maybe the Pentagon. I think I like the idea of Boston more. Actually I like the idea of Europe even more than that, with some interesting vacancies at Ramstein to the south-west of Frankfurt -- I'll have to brush up on the non-existant German that I didn't learn from either the Turks I worked with in Munich or the girlfriend from Manchester I was living with at the time.

Early days yet though, and the whole process just epitomises the story of the best-laid plans of mice and men often going astray. If the worst comes to the worst and we do end up in the middle of nowhere then I'll be hoping for at least some nearby reasonable-sized airport, and I'll do the road-warrior thing for a few years. Or we'll just find the wife a new employer who doesn't unreasonably insist on moving it's employees every three years or so, regardless of the diverse other opportunities in the area. Tut! Maybe we'll move to New Zealand instead.

Space Requirement for Compressed Row Updates

2006-04-18T12:21:00.000-06:00

It seems that when you update rows within compressed blocks the data is temporarily decompressed then recompressed, possibly causing the table to grow.

Here I create an uncompressed copy of DBA_OBJECTS, which takes 788 blocks to store. The compressed copy is then shown to take 256 blocks.

On updating all of the rows the table has grown to 1038 blocks, approximately 256+788, yet as the DBMS_ROWID-based queries show the rows have not moved. It seems that an additional approximately-788 blocks of space have been added to move the table's high water mark.

Curioser and curioser.

SQL> drop table t1
 2 

Table dropped.

SQL>
SQL> create table t1
 2  nocompress
 3  pctfree 0
 4  as
 5  select * from dba_objects
 6  /

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T1')

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks,empty_blocks from user_tables
 2  where table_name ='T1'
 3  /

   BLOCKS EMPTY_BLOCKS
---------- ------------
      788            0

SQL>
SQL> select min(dbms_rowid.rowid_block_number(rowid)) min_block,
 2         max(dbms_rowid.rowid_block_number(rowid)) max_block,
 3         count(distinct dbms_rowid.rowid_block_number(rowid)) num_blocks
 4  from t1
 5  /

MIN_BLOCK  MAX_BLOCK NUM_BLOCKS
---------- ---------- ----------
  1610346    1611133        788

SQL>
SQL> update t1 set owner = rtrim(owner)
 2  /

60120 rows updated.

SQL> commit
 2  /

Commit complete.

SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T1')

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks,empty_blocks from user_tables
 2  where table_name ='T1'
 3  /

   BLOCKS EMPTY_BLOCKS
---------- ------------
      788            0

SQL> select min(dbms_rowid.rowid_block_number(rowid)) min_block,
 2         max(dbms_rowid.rowid_block_number(rowid)) max_block,
 3         count(distinct dbms_rowid.rowid_block_number(rowid)) num_blocks
 4  from t1
 5  /

MIN_BLOCK  MAX_BLOCK NUM_BLOCKS
---------- ---------- ----------
  1610346    1611133        788

SQL>
SQL> drop table t1
 2  /

Table dropped.

SQL>
SQL> create table t1
 2  compress
 3  pctfree 0
 4  as
 5  select * from dba_objects
 6  /

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T1')

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks,empty_blocks from user_tables
 2  where table_name ='T1'
 3  /

   BLOCKS EMPTY_BLOCKS
---------- ------------
      256            0

SQL> select min(dbms_rowid.rowid_block_number(rowid)) min_block,
 2         max(dbms_rowid.rowid_block_number(rowid)) max_block,
 3         count(distinct dbms_rowid.rowid_block_number(rowid)) num_blocks
 4  from t1
 5  /

MIN_BLOCK  MAX_BLOCK NUM_BLOCKS
---------- ---------- ----------
  1611146    1611401        256

SQL>
SQL> update t1 set owner = rtrim(owner)
 2  /

60121 rows updated.

SQL> commit
 2  /

Commit complete.

SQL>
SQL> exec dbms_stats.gather_table_stats(user,'T1')

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks,empty_blocks from user_tables
 2  where table_name ='T1'
 3  /

   BLOCKS EMPTY_BLOCKS
---------- ------------
     1038            0

SQL> select min(dbms_rowid.rowid_block_number(rowid)) min_block,
 2         max(dbms_rowid.rowid_block_number(rowid)) max_block,
 3         count(distinct dbms_rowid.rowid_block_number(rowid)) num_blocks
 4  from t1
 5  /

MIN_BLOCK  MAX_BLOCK NUM_BLOCKS
---------- ---------- ----------
  1611146    1611401        256

SQL> update t1 set owner = lower(owner)
 2  /

60121 rows updated.

SQL> commit
 2  /

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'T1')

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks,empty_blocks from user_tables
 2  where table_name ='T1'
 3  /

   BLOCKS EMPTY_BLOCKS
---------- ------------
     1038            0

SQL> select min(dbms_rowid.rowid_block_number(rowid)) min_block,
 2         max(dbms_rowid.rowid_block_number(rowid)) max_block,
 3         count(distinct dbms_rowid.rowid_block_number(rowid)) num_blocks
 4  from t1
 5  /

MIN_BLOCK  MAX_BLOCK NUM_BLOCKS
---------- ---------- ----------
  1611146    1611401        256

Introspection time

2006-04-18T08:38:00.000-06:00

I wandered over to Similar Minds to try their Jung personality test.

ISTP - "Engineer". Values freedom of action and following interests and impulses. Independent, concise in speech, master of tools. 5.4% of total population.

Free Jung Personality Test (similar to Myers-Briggs/MBTI)

Apparantly it means this. some of those observations seem to be pretty spot-on -- ie. the one's that reflect well on me. Just ignore all the others, they're not true. Also the "athletic" thing, pay no attention to that.

Careerwise I seem to be doing OK. Here are some suggestions from the website:

Police and Detective Work
Forensic Pathologists
Computer Programmers, System Analysts and Computer Specialists
Engineers
Carpenters
Mechanics
Pilots, Drivers, Motorcyclists
Athletes
Entrepreneurs

Well, #3 there is obviously on target ... I also built a very nice fence and gate, and next I'm putting up some trellises so that's #5 ... I have a Masters degree in Aeronautical Engineering and an HNC in Marine Engineering, so that's #4 ... I ride a 2005 Triumph Tiger, #7. I really ought to work harder on #8 though.

OK, back to work people.

Bad Form

2006-04-16T19:12:00.000-06:00

I just finished filling in the thirteen-pages of forms required to transfer Number Three Son to a new day care. Here are some statistics on the number of times the forms required particular information

His name: Seven
His date of birth: Four
His current age: Two
His doctor's name: Three
My address: Seven
My phone number: Ten
My signature: Eighteen

Things I have now lost as a result:

Use of my writing hand
Memory of my social security number
Last trace of sympathy for de-normalisation for performance

And my will to live is fading fast ... there must be a beer around here somewhere ...

In Praise of ISO 8601

2006-04-13T07:00:00.000-06:00

As a UK ex-patriate and US resident I'm very sensitive to date formats. Moving from the DD/MM/YYY format to the MM/DD/YYYY format was a traumatic experience, and after eight years I still get a nervous twitch when I need to place the month before the day. What kind of a twisted mind thought that up?

Probably the same one who decided that in my first job in the US I'd be dealing with a system for which the preferred date display formats were to be YYDDD and YYYYDDD. There can't be many people who are instinctively aware that the first of October is day 274 of a non-leap year, but if you find someone who is then I can tell you who they work for. Furthermore, they may belive that this data format is known as the Julian Date, and they're wrong about that of course. Further-furthermore it's surprising how many database designers believe that a requirement to display a date in YYYYDDD format means that it is a great idea to store it as a NUMBER(7), leading to it being displayed by default in many tools as "2,006,274.00". Actually it's not surprising, it's just irritating for the hopefully-obvious reasons.

The other exception to the adherence to the MM/DD/YYYY format was the Immigration Service, who seemed to prefer DD/MM/YYYY on their forms. Thus I was lulled into a false sense of security through the whole visa process, and only ambushed with the MM/DD/YYYY abomination when I was fully commited to the process.

This is why I have such a liking for ISO 8601, which specifies the standard date format to be YYYY-MM-DD, with a reduced precision option of YYYY-MM and an optional time component of what we Oracleers would represent as hh24:mi:ss. I could lay out a complete set of reasons why this makes more sense but it is done very well here, and the display of week numbers is also nicely handled.

Now then, do you suppose that if the massed hordes of database developers and administrators rose up against the MM/DD/YYYY and the less-objectionable DD/MM/YYYY and DD-Mon-YYYY formats in favour of YYYY-MM-DD then we might make a meaningful impact on this scourge? I'd hope so. If we all started a "Movement Against Date Display Stupidity" (MADDS) by claiming that there was a fatal bug in the system when displaying anything other than YYYY-MM-DD then we might deceive our way to virtue, and all mankind would benefit.

It'd do me a power of good, anyway.

A Quick Materialized View Performance Note

2006-04-12T07:00:00.000-06:00

I was asked in an email this morning about how to improve materialized view refresh performance, and it's something that appears in Google hits for the blog quite frequently. So I thought I'd write a quick-and-dirty posting to summarise some of the research (and the resulting practical applications) that I've been fiddling with recently.

Here are some bullet points:

The materialized view fast refresh mechanism is a one-size-fits-all solution, and is probably not efficient for 99% of summary table maintenance operations.
The join of the aggregated change data to the MV is function-based, as the columns of both relations are wrapped in the Sys_Op_Map_NonNull() function that allows "null = null" joins. I think that it is extremely unlikely that anyone has nullable attribute columns in their fact or summary tables, so this (and the composite function-based index required to support it) are a waste of resources.
Because of the nature of the join it seems to be extremely unlikely that partition pruning of the summary table could take place.
The join mechanism promotes nested loop joins, where a hash join is probably more efficient (that's technically an outer join in the merge, of course).
The refresh mechanism assumes that a merge will be required, when sometimes an insert is not only possible but is very much more efficient.

If performance (and robustness, IMHO) are an issue for you then I would advise that you do the following:

Use materialized views only for enabling query rewrite (which means creating them on a prebuilt summary table, and unless you are loading to the summary by partition exchange then you have to drop the MV, refresh the summary table, and recreate the MV). In 10g it is much more easy to use the DBMS_Advanced_Rewrite package instead of MV's.
Write your own refresh code, based on the usual principles of writing good SQL. If you don't need a merge then don't use it. If you don't need to join to dimension tables to get higher attributes then don't do it.
Leverage different levels of aggregation to help produce higher levels. For a series of hierarchical summaries, multi-level aggregations can be extremely beneficial.
Consider storing the refresh and MV definition SQL in CLOB columns of a summary management table, so they can be tuned and edited without needing to open up package code to do so.
Consider using a complete refresh, either through MV's or manually, for higher aggregation levels, particularly when you can reference another summary table to do so.

My practical experience of taking this path delivered a reduction in the refresh time of a set of seven materialized views based on a single fact table, from a couple of hours down to six minutes. The publication of this is an example of what my client's technical lead calls "polishing the crown" :D

Anyway, this is all a sort-of abstract from a whitepaper that I'm working on right now, which will include all sorts of proofs and demonstrations of the above, plus some process and risk management advice, but it doesn't look like it'll be ready very soon. Feel free to comment or ask questions on the points raised above though -- I don't want anyone to be kept in suspenders 'till the paper is finished.

My Dishwasher is List Partitioned

2006-04-07T15:37:00.000-06:00

My dishwasher has two levels in it. The bottom level is for plates and Large Things, the top level is for cups and Small Things, and there is a removable cutlery partition attached to the door.

If I had a spare cutlery partition then I could keep one of them in use by the dishwashing system while the other one is treated as a regular cutlery holder, and it could be filled with dirty cutlery as the items become available. When it was full I could perform a cutlery partition exchange between the one with clean items and the one now full of dirty items.

If I had spare levels then they could be treated in a similar manner. I believe that commercial dishwashers have exactly that configuration, thus they operate with lower downtime because of this exchange mechanism, although the overall configuration requires more space.

Within the cutlery partition there are six subpartitions. I like to fill each one with a single type of cutlery -- one for knives, two for spoons (they don't stack as well), a couple for forks, and one for other items. Although it is more work to separate the items into these subpartitions it has the advantage of physically clustering all the spoons together and I can access all items of one type without having to scan the complete cutlery partition.

For the upper and lower levels similar principles apply, although they are not really subpartitioned in the same way. Instead the large plates are clustered in a single contiguous range -- the small plates, the glasses and the mugs each have their own place. Again it is more work to insert the items like this, but the advantage of faster retrieval is similar because I don't have to scan the complete level to pick similar items out from dissimilar ones.

That is all.

The State of The Sponge

2006-04-06T11:53:00.000-06:00

I notice that the blog just passed it's 80,000 page load since I started using Statcounter to monitor it, and that's just one day short of its first anniversary so it's time for stats.

Looking at the statistics for the last 1000 visits, one-third of them come from Google searches -- most seem to be to do with materialized views, oddly enough.

186 www.google.com   
77  tkyte.blogspot.com   
23  www.google.co.uk   
23  www.bloglines.com   
20  oracledoug.blogspot.com   
19  www.google.co.in   
14  www.google.com.au   
10  www.google.ca   
9   www.google.fr   
8   thinkoracle.blogspot.com   
7   blogs.oracle.com   
7   orawin.info   
6   www.google.de   
6   oradot.com   
6   www.rittman.net   
5   www.google.es   
5   www.blogger.com   
5   www.petefinnigan.com   
4   my5.statcounter.com   
4   www.google.be
4   www.google.it   
4   search.blogger.com   
4   www.newsgator.co.uk   
4   www.netvibes.com   
3   www.google.com.tr   
3   www.google.fi   
3   uk.my.yahoo.com   
3   www.edpadgett.com   
3   ora-dev.blogspot.com   
3   search.msn.com   
3   www.google.se   
2   www.google.com.sg   
2   www.google.co.il   
2   www.oracle-base.com   
2   www.google.ru   
2   www.google.ro   
2   www.google.ch   
2   search.yahoo.com   
2   pjs-random.blogspot.com   
2   www.google.ie
2   bloglines.com   
2   www.google.com.ph   
2   www.google.nl   
2   oracle-base.com   
2   discuss.joelonsoftware.com   
2   www.google.com.sg   
1   blogsearch.google.com   
1   www.dbasupport.com   
1   my2.statcounter.com   
1   oramossoracle.blogspot.com   
1   www.nomad8.com   
1   www.google.co.za   
1   my.yahoo.com   
1   www.google.com.tw   
1   www.google.com.hk   
1   www.google.pl   
1   www.google.co.kr   
1   tkyte-test.blogspot.com   
1   www.google.is   
1   www.google.cl
1   idyller.blogspot.com   
1   www.google.com.ar   
1   www.google.co.jp   
1   peec2006.blogspot.com   
1   www.google.com.br   
1   www.google.gr   
1   portal.umbrialistens.com   
1   www.google.co.ve   
1   kdsatplay.blogspot.com   
1   www.google.dk   
1   www.google.com.eg   
1   oraqa.com   
1   www.google.com.co   
1   ambigramania.blogspot.com   
1   www.google.pt   
1   www.surfwax.com   
1   www.google.com.mx   
1   www.alltheweb.com

Fifty-two countries are represented, if we ignore the unknowns ...

402 41.66%   United States
132 13.68%   United Kingdom
80   8.29%   Canada
60   6.22%   Lithuania
39   4.04%   Netherlands
37   3.83%   India
26   2.69%   Australia
20   2.07%   Germany
17   1.76%   France
15   1.55%   Switzerland
13   1.35%   Spain
11   1.14%   Unknown   -
9    0.93%   Russian Federation
8    0.83%   Belgium
7    0.73%   Turkey
7    0.73%   Japan
6    0.62%   Norway
6    0.62%   Denmark
5    0.52%   Portugal
4    0.41%   United Arab Emirates
4    0.41%   Italy
4    0.41%   Sweden
4    0.41%   Israel
3    0.31%   Colombia
3    0.31%   Greece
3    0.31%   Singapore
2    0.21%   Romania
2    0.21%   Philippines
2    0.21%   Poland
2    0.21%   Austria
2    0.21%   Ireland
2    0.21%   Serbia And Montenegro
2    0.21%   Finland
2    0.21%   Latvia
2    0.21%   Bolivia
2    0.21%   Uruguay
2    0.21%   Argentina
2    0.21%   South Africa
2    0.21%   Puerto Rico
1    0.10%   Pakistan
1    0.10%   Bulgaria
1    0.10%   Estonia
1    0.10%   Kenya
1    0.10%   Chile
1    0.10%   Andorra
1    0.10%   Ukraine
1    0.10%   Iceland
1    0.10%   New Zealand
1    0.10%   Egypt
1    0.10%   Brazil
1    0.10%   Slovenia
1    0.10%   Korea, Republic Of
1    0.10%   Hong Kong

Good to see the Netherlands up there this time! Readership seems to be up to 13 Dutch people, unless some of them are cheating and using multiple computers.

The most frequently occuring city for Sponge readers is London, UK, then Calgary, Ontario, Arlington, Nieuwegein ...

The most popular article is "Writing Good SQL". It's a perennial favourite with the Google crowd.

That's all -- back to work people!

A Story of Database FUD

2006-04-06T08:12:00.000-06:00

The sorry tale here ... http://discuss.joelonsoftware.com/default.asp?joel.3.328948.11

Bad Advice On Materialized View Deferrable Constraints

2006-04-05T15:30:00.000-06:00

An article by Jonathan Lewis on constraint problems with materialized views set off a little bell in my head about a metalink document on the subject, Note 284101.1.

The note protests the use of unique, foreign key and primary key constraints on materialized views, and includes the following interesting phrases ...

"It is not a good idea to create unique (unique indexes ) constraints , primary key or Foreign key constraints on the snapshot base table ... There should not be any Unique/PK constraints at Mview base table ... Drop the Unique/PK constriants on Mview Base Tables."

Then one of the workarounds is "You can have Deferred Constraints". The document doesn't identify why having deferred constraints might be a bad idea, or why not having integrity constraints on the table at all is preferable to having them deferred .. I can't think of a reason why that might be so, and I can think of several reasons why it might not be so (query optimization being the prime one).

All this misdirection and ambiguity, combined with the abbreviation of an error code from "ORA-00001" to "ORA-1" in the title of a help document (does that make sense to anyone?) make this a pretty poor piece of work. It's not the Most Useless Metalink Article Ever, but it's close.

By the way, if you read the title and the first few words of this article, and thought "Dave's going to say that Jonathan Lewis gave bad advice!" before realising I wasn't ... the effect was intentional :D If you didn't think that then my plan failed.

What's Wrong With Opera?

2006-04-05T07:16:00.000-06:00

Not the musical form, obviously there's nothing wrong with that at all.

The browser is very nice, but I can't read the Oracle forums with it. If I go here then clicking on any link takes me here. Opera also doesn't display the little images that are scattered all around the page, and I get a bunch of rectangles with text in them. Internet Explorer doesn't have this problem, nor the issues that I wrote about in Firefox the other day (heresy, I know).

Why is reading HTML and following links so difficult? You'd imagine that they'd have it close to working by now. Well, maybe there's some configuration thing that needs to be done to the browser, but I'm not sure why I should have to spend my time messing about with this kind of crap.

OK, I promise to write something later this week about a technology that I do like. Um ....

Development and Tuning

2006-04-04T19:15:00.000-06:00

I had a pithy thought earlier today.

1. Development is the business of making the database do work
2. Tuning is the business of reducing the amount of work the database does.

Therefore, tuning is the opposite of development. It is "anti-development"!

What's Wrong With Firefox?

2006-04-03T08:49:00.000-06:00

A number of things.

On one of my machines it consistently refuses to open particular web sites. http://www.dbdebunk.com/index.html is one of them -- IE has no problem, Opera has no problem. No error message, nothing. It just seems to pretend that I haven't asked it do do anything.
It's a resource hog. Memory usage often tops 150Mb, and can only be reduced by stopping and restarting it. If my machine startys running slowly then I know where to look -- Firefox is sitting and chewing up CPU cycles to no apparant effect.
It's unstable. I generally get four or five crashes a week, and they're not associated with having a lot of windows or tabs open either.

It didn't used to be this bad, and it seems to be getting worse. AdBlock has been the only reason to keep using it, but that's not enough anymore. I'd actually rather have adverts on the page than put up with the all of Firefox's crap .

URL Masking at DBASupport.com forums

2006-03-30T14:03:00.000-07:00

http://www.dbasupport.com/forums/showthread.php?p=226665

Rather sinister, I think. I wonder what's behind it?

(Busy day for posts today, huh?)

Why Space Travel Is A Stupid Idea

2006-03-30T13:19:00.000-07:00

It seems to me that all other arguments aside, when a lamp falling onto the outside of a space ship and causing "... five small indentations, with the largest about the size of a stick of gum, and one 6-inch (15-centimeter) to 7-inch (17-centimeter) long scratch" makes it onto the news, then it's time to be rethinking whether this is a goal worth pursuing. Doubtless at this very moment a ten-man committee is being formed to write a review of the "Procedures for Luminescent Output Device Restraint In Shuttle Proximity".

Bonus opinion: I don't believe that the spokesman for the space shuttle's manufacturer, Lockheed Martin, is called Marion LaNasa. I think they made that up.

Unfortunate Timing

2006-03-30T10:52:00.000-07:00

My plans to attend UKOUG in November, and maybe even submit a paper, have been thoroughly sunk. Following the collapse of our plans to get posted to Eastern Europe, the wife is scheduled for a deployment to Sicily from October to December and I'll be playing the part of Mr. Mom to our herd of offspring for three months.

Speaking of which, what's the collective noun for small boys? A wrestle of boys? A destruction? A puddle? Any of those would seem to fit.

Things I Need: Part I

2006-03-29T15:06:00.000-07:00

Now that my Poweredge is up and running, I'm casting about for a New Shiny Thing that I absolutely must have.

I'm unimpressed with mere items of technological clothing.

What I need is a JL421 Badonkadonk. Am I right fellas?

A List Of Weekend Successes

2006-03-26T21:26:00.000-07:00

A list of things we did this weekend

Recommissioned sprinkler system, watered plant, soaked lawn, decommissioned sprinkler system
Cut kids' hair -- no fatal wounds inflicted.
Cleared basement
Got rid of four old tires
Made chicken and rice soup
Mended kite -- flew kite -- broke kite again
Boxed 10 cu.ft. of kids clothes, to go to Afghanistan via USAF apparantly.
Got Poweredge 6400 running on Centos 4, moved it to the basement & started Oracle 10.1 install.

OK, about item 8.

The tricky part of the business was the boot. I had grub stage2read errors coming out the wazoo, and eventually gave up on trying "conventional" fixes. Booting with a floppy drive turned out to be the answer, as long as it referenced an initrd. Quite the learning experience.

Note to self: make backup of floppy disk! Maybe two backups.

Poweredge 6400 -- the Great o/s Debate

2006-03-17T07:45:00.000-07:00

First, some boring details.

The service tag for this machine is H6MGN01, and the recorded configuration seems to be pretty accurate. The critical part is this: "ASSEMBLY, CARD (CIRCUIT), POWEREDGE EXPANDABLE RAID CONTROLLER NUMBER, 3-DC, 128"

Maybe it's a truism that the tricky part of working with these systems is getting the drivers for the array loaded before the install, but I seem to have worked that out. There was a nervous moment of array errors before I realized that the controller card was slightly unseated during shipping, but that seems to be a non-issue now.

Dell appear to have pretty good support for Linux, on the whole. I've been through the information at http://linux.dell.com/storage.shtml and deduced the following.

The PERC 3/DC requires a MegaRAID driver "megaraid_mbox"
The required driver is present in RHEL4 kernel 2.6.9-5.EL and higher -- sounds good for a Whitbox or Centos install!
SLES9 ought to work, but has not been tested on the PERC3 series by Dell
The PERC3/DC firmware update must be extracted to floppy on a Windows machine
There are Solaris drivers for the Adaptec card, but not for the PERC3. Boooh.

Point 1: no problem. Actually SuSE 8 appears to have the right drivers also. I tried a quick install yesterday evening but it choked on reboot with a "GRUB loading stage2Read error" -- maybe something needs tweaking there.

Point 3: Rats. I'm going to have to get creative there, with some major surgery to move a floppy over to my Windows machine.

Anyway, I'm downloading Centos ISO's right now, so we'll see how they perform. Do you think that driver has to be specifically loaded before the install? I guess we'll find out ...

Poweredge 6400 -- the Great o/s Debate

2006-03-17T07:12:00.000-07:00

The PERC 3/DC requires a MegaRAID driver "megaraid_mbox"
The required driver is present in RHEL4 kernel 2.6.9-5.EL and higher -- sounds good for a Whitbox or Centos install!
SLES9 ought to work, but has not been tested on the PERC3 series by Dell
The PERC3/DC firmware update must be extracted to floppy on a Windows machine
There are Solaris drivers for the Adaptec card, but not for the PERC3. Boooh.

A New Arrival

2006-03-16T12:43:00.000-07:00

My new (used) Poweredge 6400 arrived today -- a new dawn of technical complexity, electrical power consumption and noise pollution has arrived in the Sponge household.

The first technical challenge is what name to give to the machine? In accordance with my own conventions it has to be that of a London train station, and because it is short, wide, and dressed in black I immediately thought it looks like a Victoria. Unfortunately I already have one of those, so I think it'll have to be "Waterloo".

It'll be a fine chance to give a brief history lesson on the Empire's Days of Glory to my mob of ungrateful foreign colonial children.

Predicate Pushing And Analytic Functions

2006-03-15T16:00:00.000-07:00

I'm sure that there must be a fair number of Oracle professionals who carry around in their heads a little score card of some of their best tuning results ever ... hopefully, at least, 'cos otherwise I'm a weirdo. Anyway, today I improved the performance of a set of decision support queries and achieved my best result ever - improving query speed by a factor of 180,000, from an original run time of one hour down to a new time of 0.02 seconds.

The wise monkeys in the audience will immediately be thinking "partition pruning!" or "materialized view!", and in fact if you thought the former then you'd be right. Here's how it worked.

I had defined a view against a large fact table so that it included several analytic functions. The details really don't matter, but the intention was to allow a column to contribute to a metric value only once per transaction_id, so the metric definition was something like:


Decode(Row_Number() Over (Partition By Transaction_ID Order By 1),1,Qty,0)

Queries that access this view had predicates on a number of columns, including some very selective indexed columns (Item_ID) and a partition key column (Fiscal_Month). Unfortunately there is nothing in the database to tell the optimizer that each unique value of Transaction_ID had but a single value Item_ID and Fiscal_Month, so logically the predicates could not be applied until after the analytic function had been calculated. Hence there was no predicate pushing on the fiscal_month and item_id, and neither partition pruning nor index access was considered. The query was actually scanning about one quarter of the table (it looks like a combination of subpartition pruning and partition pruning was taking place, but this table is multicolumn range + list composite partitioned, and pruning at the partition level was only taking place on the leading column of the partition key).

However, we included the two predicate columns in the analytic functions' partition clause like so:


Decode(Row_Number() Over (Partition By Transaction_ID, Item_Id, Fiscal_Month Order By 1),1,Qty,0)

Now this doesn't change the result because the Item_Id and Fiscal_Month entries are actually logically redundant, but it did allow both 9i and 10g to push the predicates and give full partition and subpartition pruning and index-based access.

Quite a nice result.

Here's a script that I used to demonstrate that it would work.


drop table t1;
create table t1
   (
   txid   ,
   month  ,
   item_cd,
   qty  
   )
as
select
   floor(rownum/5),
   floor(rownum/20),
   floor(rownum/10),
   floor(rownum/5)
from
   dual
connect by
   level < 100
/

explain plan for
select txid,
       qty
from   (
       select txid,
              item_cd,
              month,
              qty,
              decode(row_number() 
              Over (Partition By txid Order By 1),1,qty,0)
                 qty_fix
       from   t1
       ) v
where  item_cd = 0
/
select * from table(dbms_xplan.display)
/

explain plan for
select txid,
       qty_fix
from   (
       select txid,
              item_cd,
              month,
              qty,
              decode(row_number()
              Over (Partition By txid Order By 1),1,qty,0)
                 qty_fix
       from   t1
       ) v
where  item_cd = 0
/
select * from table(dbms_xplan.display)
/

explain plan for
select txid,
       qty_fix
from   (
       select txid,
              item_cd,
              month,
              qty,
              decode(row_number()
              Over (Partition By txid,item_cd Order By 1),1,qty,0)
                 qty_fix
       from   t1
       ) v
where  item_cd = 0
/
select * from table(dbms_xplan.display)
/

In 10g the explain plan with predicate pushing was very straightforward:


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 2273146475

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     9 |   252 |     3  (34)| 00:00:01 |
|   1 |  VIEW               |      |     9 |   252 |     3  (34)| 00:00:01 |
|   2 |   WINDOW SORT       |      |     9 |   351 |     3  (34)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| T1   |     9 |   351 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("ITEM_CD"=0)

Note the filter being applied to line 3. Where the predicate was not pushed it was applied to line 1.

The 9i explain plan was rather ... funky. But it worked.

Creating Built-in Help For PL/SQL Packages

2006-02-24T09:44:00.000-07:00

This is a trivial application of basic techniques, but it seemed to me to be an idea worthwhile of sharing.

Anyway, I sometimes have a need to code up some PL/SQL to support ETL operations, and such procedures are often executed from the command line in SQL*Plus by operators. Currently I'm working on a generic package to support manual refresh of some troublesome materialized views.

This is not necessarily the sort of thing that the operators will run every day, and hence it seems worthwhile to put the documentation for the procedures literally at their fingertips by providing a built-in help function. I've done this by adding to the package a definition of a pipelined function called HELP, which simply spools out descriptions of procedures and parameters, and here's how it works.

Start with an object type definition such as:

create or replace TYPE HELP_TEXT_TYPE
AS OBJECT ( help_text varchar2(80) );
/

Then add a table type definition:

create or replace TYPE HELP_TEXT_TYPE_TABLE
AS TABLE OF HELP_TEXT_TYPE;
/

In your package you'll need a pipelined function specification:

Create Package help_demo
as
   function help                     
   return help_text_type_table
   pipelined;
End;
/

Then the package body contains the function definition, such as:

Create or replace Package Body help_demo
as
   function help
   return   help_text_type_table
   pipelined
   is
   begin
      Pipe Row(help_text_type('IF THIS IS DIFFICULT TO READ THEN USE THE COMMAND ...'));
      Pipe Row(help_text_type('set heading off pagesize 1000'));
      Pipe Row(help_text_type(''));
      Pipe Row(help_text_type('HELP for package HELP_DEMO'));
      Pipe Row(help_text_type('=========================='));
      Pipe Row(help_text_type(''));
      Pipe Row(help_text_type('Procedures'));
      Pipe Row(help_text_type('----------'));
      Pipe Row(help_text_type(''));
      Pipe Row(help_text_type(' None'));
      Pipe Row(help_text_type(''));
      Pipe Row(help_text_type('Functions'));
      Pipe Row(help_text_type('---------'));
      Pipe Row(help_text_type(''));
      Pipe Row(help_text_type('help:'));
      Pipe Row(help_text_type(' You''re reading it now. This is a demonstration of how'));
      Pipe Row(help_text_type(' to provide a built-in help functionality for a PL/SQL package'));
      Pipe Row(help_text_type(' that is accessible through SQL*Plus to user of the package'));
      Pipe Row(help_text_type(''));
      Pipe Row(help_text_type('Parameters'));
      Pipe Row(help_text_type('----------'));
      Pipe Row(help_text_type(''));
      Pipe Row(help_text_type(' There are none of those either, but if there were then they''d be'));
      Pipe Row(help_text_type(' listed and described here, like the following examples'));
      Pipe Row(help_text_type(''));
      Pipe Row(help_text_type(' autocommit; (Y/N) Default N'));
      Pipe Row(help_text_type('   Whether or not to commit each process as it completes'));
      Pipe Row(help_text_type('   A commit will be issued at the end anyway'));
      Pipe Row(help_text_type(''));
      Pipe Row(help_text_type('That Is All'));
      Pipe Row(help_text_type('==========='));
      Pipe Row(help_text_type(''));
      Pipe Row(help_text_type(' Get back to work'));
   End;
End;
/

When you issue the query;

Select * from table(help_demo.help)
/

... you get output such as ...

IF THIS IS DIFFICULT TO READ THEN USE THE COMMAND ...
set heading off pagesize 1000

HELP for package HELP_DEMO
==========================

Procedures
----------

 None

Functions
---------

help:
 You're reading it now. This is a demonstration of how
 to provide a built-in help functionality for a PL/SQL package
 that is accessible through SQL*Plus to user of the package

Parameters
----------

 There are none of those either, but if there were then they'd be
 listed and described here, like the following examples

 autocommit; (Y/N) Default N
   Whether or not to commit each process as it completes
   A commit will be issued at the end anyway

That Is All
===========

 Get back to work

33 rows selected.

You might choose to wrap up the help functioanlity in a view, such as:

create view help_demo
as
select * from table(help_demo.help)
/

... to make is easier to access, or even use DBMS_OUTPUT to send the help text to the screen. I like this though as it's accessible directly through SQL, the Universal Language.

Partition Change Tracking Fast Refresh Workaround?

2006-02-21T14:04:00.000-07:00

I wrote a while ago that I didn't think that Partition Change Tracking (PCT) fast refresh was worth using, because eventually something will cause the refresh to fail and then, for various silly reasons, you can only get PCT FR back by completely rebuilding your MV in one shot. This is not a practical endeavour in many cases, hence PCT is not worth so much.

However, it has now occured to me that if one kept a backup copy of the data in the PCT-based MV, even if it is only as a regular table, then in the event of disaster such a table could be brought up to dat with respect to the master tables(s) and with the correct MV definition placed over it temporarily the complete rebuild of the PCT-based MV could be rewritten to effectively be a full table scan of this backup. that ought to be more achievable I suppose.

It still seems like a lot of trouble though.

Oracle Wiki at Dizwell

2006-01-24T22:30:00.000-07:00

Howard's Wiki at Dizwell is up and running, and seems to be off to a very good start considering that this is a pretty new thing for many of us. I hope that everyone will be contributing -- these things only survive if we give it the oxygen of our participation, and I know that everyone has something to share.

The text formatting takes a little getting used to but at the bottom of the edit pages there are brief tips that will get you through the experience pretty painlessly.

Are you still here? Go, go ....

Communication Skills

2006-01-20T13:40:00.000-07:00

Public speaking often being in part of the job, here's the help you may like.

http://www.tmous.com/media/MOUS_falipornia.html

Why We Fight

2006-01-20T12:57:00.000-07:00

http://www.sonyclassics.com/whywefight/

An Odd Form Of Flattery

2006-01-17T23:35:00.000-07:00

I think that if someone is going to copy your work to their own blog then the least they might do is provide a link to the original source ... oh wait, no the least they might do is spell your name right.

The Law Is An Ass

2006-01-10T12:22:00.000-07:00

So it's now illegal to anonymously annoy someone on the internet. Sounds ridiculous, don't it? Well, not according to the Violence Against Women and Department of Justice Reauthorization Act, which was cunningly attached to a must-pass funding bill for the Justice Department (a lovely example of the dysfunctionality of the US legislative system).

Read the whole stupid story here.

boy, I for one am glad that I never annoy people on the internet ... anonymously, that is.

RMOUG Training Days 2006

2006-01-06T10:58:00.000-07:00

The RMOUG Training Days 2006 are set for February 15-16 at the Colorado Convention Center. I always end up missing these events due to last minute emergencies, but this year I'm pretty determined to make it.

Hey, if nothing else it'll be worth a few blog entries, and that's nothing to be sniffed at.

Anyone planning on going?

(Almost) Error Free Code

2006-01-05T22:52:00.000-07:00

Picked up from Slashdot, an article at IEEE about a British software house delivering code with less than one error per ten thousand SLOCs, albeit at a premium of up to 50% higher costs than regular development. Their "secret" is to return to the roots of software engineering by describing the application in terms of a a formal specification language Z ("zed") based on set theory and predicate logic before subjecting it to mathematical analysis to identify ambiguities and inconsistencies, and only then converting it to code (which itself becomes a relatively trivial task). Incidentally, they code in Spark, a descendant of Ada, and if you follow that link to the Wikipedia article you'll see an example of some excellent commenting techniques under "Annotation Examples".

Accurate and complete requirements gathering is emphasised, as is prototyping as a means of requirements feedback to the customer. In an example development described in the article the developed software showed just four faults in the first year of operation out of 100,000 SLOC.

I found the IEEE article rather inspiring, although at the end the comments on the willingness of programmers to use formal methods and logic are depressingly familiar.

There are more details in a presentation by Praxis here.

"The Economist" Premium Content Free

2005-12-27T10:23:00.000-07:00

OK, you have to watch a short commercial, but that's capitalism folks. And it's a small price of entry for the world's best world affairs and business coverage.

http://www.economist.com

The Tranquility of Materialized Views

2005-12-20T09:44:00.000-07:00

Yesterday evening I searched the Oracle 10g documentation for the string "materialized views", and was browsing through the 378 matching entries in "Error Messages". At about the point where I got to ORA-12055: materialized view definition contains cyclic dependencies with existing materialized views I was overcome with a great sense of tranquility and understanding of materialized views.

Shortly after that I realised that I was probably delusional from lack of sleep or something, so I snapped out of it and went to bed. Is it still only Tuesday today?

Optimizing Materialized Views Part V: HOLAP Query Performance

2005-12-17T16:39:00.000-07:00

HOLAP Performance With Different Aggregation Levels

I'm going to construct a slightly different test data set for this experiment, involving the aggregation of 10,000,000 by three different columns each having around 1000 distinct values.

drop table master;

create table master
  (
  col1    not null,
  col2    not null,
  col3    not null,
  tx_qty  not null
  )
pctfree 0
nologging
as
select
  trunc(dbms_random.value(1,1000)),
  trunc(dbms_random.value(1,1000)),
  trunc(dbms_random.value(1,1000)),
  trunc(dbms_random.value(1,100),2)
from
  dual
connect by
  1=1 and
   level <= 1000000;

The performance test queries will take the form of a query such as the following:

clear screen
alter session set workarea_size_policy = manual;
alter session set sort_area_size = 1000000000;
alter session set sort_area_retained_size = 1000000;
set timing on autotrace on feedback off

-- loop: the following executed four times
alter system flush buffer_cache;
select sum(s_tx_qty) from
(Select
  sum(tx_qty) s_tx_qty,
  {grouped column list}
From master group by grouping sets
  ({grouping sets});
-- end of loop

In the four executions of the query the first invokes a hard parse and the autotrace statistics are discarded. The autotrace results of the following three results are averaged.

We will try aggregations at a number of different levels. Some of these will involve hierarchies and some will not, but the aim is to expose as full a range of optimizations as we can. For the sake of my time, my sanity, and blog length I'm going to run these to get simple wall clock times and autotrace statistics for now. The host machine and database will be as quiescent as possible.

We'll start with simple, conventional aggregations to get benchmarks for familiar operations.

	Benchmark 1	Benchmark 2	Benchmark 3	Benchmark 4
Group By	None	col1	col1,col2	Col, col2, col3
rows	1	999	997,955	9,949,574
consistent gets	25,074	25,074	25,074	25,074
physical reads	25,062	25,062	25,062	25,062
sorts (memory)	0	1	1	1
Elapsed Time	8.87	18.95	59.70	92.42

In the following table we present the results of the various grouping set tests, with the “Y” indicating that the grouping set was included as part of that test.

Grouping Set(s) Used	Test 1	Test 2	Test 3	Test 4	Test 5	Test 6	Test 7	Test 8
()	Y		Y	Y	Y	Y	Y	Y
(COL1)		Y	Y	Y	Y	Y	Y	Y
(COL2)				Y	Y	Y		Y
(COL3)						Y		Y
(COL1,COL2)					Y	Y	Y	Y
(COL1,COL3)						Y		Y
(COL2,COL3)						Y		Y
(COL1,COL2,COL3)							Y	Y
rows	1	999	1,000	1,999	999,954	2,996,849	10,948,529	12,946,423
recursive calls	0	0	0	185	25	299	270	319
db block gets	0	0	0	23,575	2803	38,219	34745	40,872
consistent gets	25,074	25,074	25074	71,498	33286	122,911	62313	130,765
physical reads	25,062	25,062	25062	71,434	27799	122,776	62242	130,622
redo size	0	0	0	1,929	2,147	3525.33	1513	3,080
sorts (memory)	0	1	1	2	3	4	2	4
sorts (disk)	0	0	0	0	0	0	0	0
Elapsed Time	12.33	16.34	17.84	63.57	71.49	230.23	173.08	266.69

Test 1 is a straight aggregation to find the sum of tx_qty, but expressed as a grouping set of () rather than the usual “Select Sum(my_col) From my_table” syntax. The autotrace statistics are also very conventional and show physical reads very close to consistent gets, with no db block gets or recursive SQL.

This test is logically the same as Benchmark 1, but required an elapsed time 50% longer. The execution plans are in fact different, with the SORT AGGREGATE of the conventional method being replaced with a SORT GROUP BY NOSORT in the grouping sets method.

Test 2 is similarly conventional, finding the sum of tx_qty grouped by col1. Again this is expressed as a grouping set (col1) rather than the regular syntax and the autotrace statistics are very similar to Test 1 with the addition of an in-memory sort. The performance is very similar to that of Benchmark 2, to which it is logically equivalent, and both queries are using a SORT GROUP BY operation.

Test 3 gives our first hierarchical result set, combining the results of Test 1 and Test 2. In fact the result set for Test 3 is logically equivalent to (Benchmark1 UNION ALL Benchmark 2), and the benefits of the HOLAP cube begin to show themselves. The average elapsed time of 17.84 seconds is only 62% of the elapsed times for Tests 1 and 2 combined. The execution plan shows a SORT (GROUP BY ROLLUP) operation which is evidently an optimization specifically for hierarchical cube queries. It appears either that the result for the (COL1) grouping set is being used as the basis for the () grouping set calculation, or that the results for both sets are being computed in the same operation. As we will shortly see, the latter appears to be the more probable scenario.

Test 4 adds into Test 3 a grouping set of (Col2), giving two sets at the same level with no more detailed level included. This result ought to be logically similar to (Test 2 UNION ALL Test 3) -- with the appropriate columns being used -- or (Benchmark 1 UNION ALL Benchmark 2 UNION ALL Benchmark 3). The result is surprising because this time the performance is well in excess of those comparisons, and gives the first hint at a possible contra-indication for HOLAP cube MV's. The elapsed time of 63.57 seconds is 86% higher than the sum of the elapsed times for Tests 2 and 3, of 34.18 seconds.

The explain plan show the creation of two temporary tables, although when the statement is executed these temporary table names are of course different.

------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                             |     1 |       |   170  (85)| 00:00:02 |
|   1 |  SORT AGGREGATE              |                             |     1 |       |            |          |
|   2 |   VIEW                       |                             |   707K|       |   170  (85)| 00:00:02 |
|   3 |    TEMP TABLE TRANSFORMATION |                             |       |       |            |          |
|   4 |     LOAD AS SELECT           |                             |       |       |            |          |
|   5 |      TABLE ACCESS FULL       | MASTER                      |    10M|   114M|   162  (88)| 00:00:02 |
|   6 |     LOAD AS SELECT           |                             |       |       |            |          |
|   7 |      SORT GROUP BY           |                             |     1 |    26 |     3  (34)| 00:00:01 |
|   8 |       TABLE ACCESS FULL      | SYS_TEMP_0FD9D6665_1214756C |     1 |    26 |     2   (0)| 00:00:01 |
|   9 |     LOAD AS SELECT           |                             |       |       |            |          |
|  10 |      SORT GROUP BY ROLLUP    |                             |     1 |    26 |     3  (34)| 00:00:01 |
|  11 |       TABLE ACCESS FULL      | SYS_TEMP_0FD9D6665_1214756C |     1 |    26 |     2   (0)| 00:00:01 |
|  12 |     VIEW                     |                             |     1 |    39 |     2   (0)| 00:00:01 |
|  13 |      TABLE ACCESS FULL       | SYS_TEMP_0FD9D6666_1214756C |     1 |    39 |     2   (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------

Here are the STAT lines from a 10046 trace file:

STAT #6 id=1 cnt=1999 pid=0 pos=1 obj=0 op='TEMP TABLE TRANSFORMATION  (cr=71632 pr=66687 pw=23191 time=58587487 us)'
STAT #6 id=10 cnt=1999 pid=1 pos=1 obj=0 op='VIEW  (cr=144 pr=6 pw=0 time=27785 us)'
STAT #6 id=11 cnt=1999 pid=10 pos=1 obj=-40016278 op='TABLE ACCESS FULL SYS_TEMP_4254951018 (cr=144 pr=6 pw=0 time=19771 us)'
STAT #6 id=12 cnt=1 pid=1 pos=2 obj=0 op='LOAD AS SELECT  (cr=25074 pr=20328 pw=23185 time=19896368 us)'
STAT #6 id=13 cnt=10000000 pid=2 pos=1 obj=260950 op='TABLE ACCESS FULL OBJ#(260950) (cr=25074 pr=20328 pw=0 time=30000104 us)'
STAT #6 id=14 cnt=1 pid=1 pos=3 obj=0 op='LOAD AS SELECT  (cr=23208 pr=23185 pw=3 time=18510637 us)'
STAT #6 id=15 cnt=999 pid=4 pos=1 obj=0 op='SORT GROUP BY (cr=23208 pr=23185 pw=0 time=18508219 us)'
STAT #6 id=16 cnt=10000000 pid=5 pos=1 obj=-40016279 op='TABLE ACCESS FULL SYS_TEMP_4254951017 (cr=23208 pr=23185 pw=0 time=30022328 us)'
STAT #6 id=17 cnt=1 pid=1 pos=4 obj=0 op='LOAD AS SELECT  (cr=23206 pr=23168 pw=3 time=20142290 us)'
STAT #6 id=18 cnt=1000 pid=7 pos=1 obj=0 op='SORT GROUP BY ROLLUP (cr=23206 pr=23168 pw=0 time=20140880 us)'
STAT #6 id=19 cnt=10000000 pid=8 pos=1 obj=-40016279 op='TABLE ACCESS FULL SYS_TEMP_4254951017 (cr=23206 pr=23168 pw=0 time=30018947 us)'

Cleaned up a little, this reads more easily as:

TEMP TABLE TRANSFORMATION
VIEW
 TABLE ACCESS FULL SYS_TEMP_4254951018
LOAD AS SELECT
 TABLE ACCESS FULL OBJ#(260950)
LOAD AS SELECT
 SORT GROUP BY
  TABLE ACCESS FULL SYS_TEMP_4254951017
LOAD AS SELECT
 SORT GROUP BY ROLLUP
  TABLE ACCESS FULL SYS_TEMP_4254951017

The temporary table definitions are as follows:

CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6669_1214756C"
  ("C0" NUMBER,
   "C1" NUMBER,
   "A0" NUMBER )
IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254951017 ) NOPARALLEL;

CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D666A_1214756C"
  ("C0" NUMBER,
   "C1" NUMBER,
   "D0" NUMBER,
   "A0" NUMBER )
IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254951018 ) NOPARALLEL

You'll note that the global temporary tables' OBJNO clause correlate to part of the temporary table name referenced in the STAT lines – a bit unintuitive that, if you ask me.

So how do we interpret this? Fortunately, a 10046 trace file contains some interesting clues.

Although it shows the creation of the temporary tables it does not reveal any other recursive SQL against them. However it does show wait events of course, and where there is a 'db file scattered read' there are also p1 and p2 parameters which tell you the file number and block number respectively for the object being read. Now the only objects involved here ought to be our MASTER table and our two temporary tables, and we know the file and block numbers for the MASTER table are available from DBA_EXTENTS.

So I'm going to break off there for this entry, and present the analysis of the execution plan, and more, next time.

An International Community, Apparantly

2005-12-15T10:12:00.000-07:00

I recently increased my StatCounter log size to 1,000 -- more from boredom than any real need, admittedly -- and have found it fascinating to see the range of countries that the Sponge gets visits from. Here is my current list:


Country                   Visits
===============           ======
United States:            209
United Kingdom:           72
Australia:                26
Switzerland:              21
Canada:                   20
Sweden:                   12
Unknown:                  12
India:                    11
Greece:                   9
Germany:                  9
Netherlands:              6
Turkey:                   6
Luxembourg:               5
Singapore:                5
Slovenia:                 4
France:                   4
Denmark:                  4
Spain:                    4
Estonia:                  3
Hong Kong:                2
Romania:                  2
Belgium:                  2
Ukraine:                  2
Serbia And Montenegro:    1
Saudi Arabia:             1
Egypt:                    1
Lebanon:                  1
Japan:                    1
Czech Republic:           1
Lithuania:                1
Austria:                  1
Finland                   1

As you see, Septics lead the way, followed closely by other English speaking countries.

Being the well-travelled International Citizen that I am, I'm a little embarrassed that I sometimes get goggly at the thought of all these far-away places, and also that I use non-words like "goggly" and "septic" to an international audience. I catch myself thinking dumb things like, "Wow! They have databases in Lithuania!?!?". Yes dumbass, they also have cars and washing machines as well, I expect.

Anyway, a question: what do people use for support in the smaller countries of the world? Do Oracle have national support staff speaking the local language(s), or do you rely on a regional system where the major regional languages are spoken, or do you have to fall back on knowledge of English and contact Oracle's global support people? I was trying to find out a while back whether Oracle published their documentation in any languages other than English, but pretty much drew a blank on it.

Reunited With Old Technology

2005-12-13T22:34:00.000-07:00

While standing on a chair in my basement storage room to make tweaks to my Shelf Of Technology, I was distracted by an aluminium case that I'd been carrying from abode to abode for about fifteen years without opening it as far as I could recall. Nestled snugly inside the blue foam padding within was my first proper camera, a Pentax ME Super.

It's been a while since I've handled it, and it felt heavy, solid, and just a delight to hold. After fifteen years, did it still work?

Away to a local store for a pair of LR44 batteries and the smallest roll of film available. Straightaway more memories came back. The batteries go in "+" side down, and they always try and flip themselves over. The little white button that has to be pressed to change the shutter action is fiddly. Little LEDs in the view finder tell you the shutter speed it will choose on the Auto setting. And it still feels heavy, especially after a few years with my Sony Cybershot digital.

The first roll of file flew by. It was really too dark for an ISO100 so I had to find kids at their most stationary, which any parent can tell you means that they were just sitting mostly still but vibrating slightly with surplus energy -- on average, they were not moving. The light was artificial so it had a warm cast to it that the flash on the digital camera would have obscured.

So here's the results so far.

The photo CD does not give great resolution and the 35mm aspect ratio is a little different, but so far it's encouraging enough for an investment in three rolls of faster black and white film. Some of the reviews I've read of this model mention its strength with black and white in particular, so now I need a chance to get up into the mountains to try it out so my creative urges have outlets other than endless images of the cat, the kids and the strata of detritus on my desk.

Playing With System Statistics

2005-12-13T11:24:00.000-07:00

Updated 14-Dec-2005 at the end of the article

I was just musing on what we can deduce directly from system statistics, and thought of the following.

If the system recorded the average MBRC and the average MREADTIM over the period of statistics collection, then does that mean that we can learn something directly about the performance of the i/o system during that time? I ran an experiment:

drop table t
/

create table t
 nologging
 pctfree 99
 pctused 0
 tablespace REG_UA4MB_MSSM
as
select rpad(rownum,100) whatever
from   dual
connect by 1=1 and
     level < estimate_percent=""> 1,block_sample => true)

select blocks*
     (select block_size
      from   user_tablespaces
      where  tablespace_name = 'REG_UA4MB_MSSM')
     /1024/1024 table_size_Mb
from   user_tables
where table_name = 'T'
/

alter  system flush buffer_cache;

exec   dbms_stats.gather_system_stats('START')

select count(*) from t;

exec   dbms_stats.gather_system_stats('STOP')

select pname,
     pval1
from   sys.aux_stats$
where  sname = 'SYSSTATS_MAIN'
/

select (select pval1
      from   sys.aux_stats$
      where  sname = 'SYSSTATS_MAIN' and
             pname = 'MBRC') *
     (select to_number(value) block_size
      from   v$parameter
      where  name = 'db_block_size') /
     (select pval1
      from   sys.aux_stats$
      where  sname = 'SYSSTATS_MAIN' and
             pname = 'MREADTIM')
     /1024 sys_stat_mread_speed
from dual
/

What I'm doing there is creating a table of around 512Mb (because I'm aiming for one row per block, and each block is 8kb), flushing the buffer cache, starting the collection of system statistics, timing a select of count(*) for the 512Mb table, and comparing the read speed according to table_size/duration against MBRC*block_size/MREADTIM.

In fact the correlation is pretty good. Here's my own execution of the above script with timing on:


SQL> drop table t
2  /

Table dropped.

Elapsed: 00:00:00.37
SQL>
SQL> create table t
2     nologging
3     pctfree 99
4     pctused 0
5     tablespace REG_UA4MB_MSSM
6  as
7  select rpad(rownum,100) whatever
8  from   dual
9  connect by 1=1 and
10         level <>
SQL> exec dbms_stats.gather_table_stats(user,'T',estimate_percent=> 1,block_samp
le => true)

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.29
SQL>
SQL> select blocks*
2         (select block_size
3          from   user_tablespaces
4          where  tablespace_name = 'REG_UA4MB_MSSM')
5         /1024/1024 table_size_Mb
6  from   user_tables
7  where table_name = 'T'
8  /

TABLE_SIZE_MB
-------------
 511.992188

Elapsed: 00:00:00.06
SQL>
SQL> alter  system flush buffer_cache;

System altered.

Elapsed: 00:00:00.01
SQL>
SQL> exec   dbms_stats.gather_system_stats('START')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.28
SQL>
SQL> select count(*) from t;

COUNT(*)
----------
   65535

Elapsed: 00:00:13.12
SQL>
SQL> exec   dbms_stats.gather_system_stats('STOP')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
SQL>
SQL> select pname,
2         pval1
3  from   sys.aux_stats$
4  where  sname = 'SYSSTATS_MAIN'
5  /

PNAME                               PVAL1
------------------------------ ----------
CPUSPEEDNW                       1132.397
IOSEEKTIM                              10
IOTFRSPEED                           4096
SREADTIM                           11.039
MREADTIM                           11.886
CPUSPEED                             1084
MBRC                                   63
MAXTHR                           36093952
SLAVETHR                          2065408

9 rows selected.

Elapsed: 00:00:00.00
SQL>
SQL> select (select pval1
2          from   sys.aux_stats$
3          where  sname = 'SYSSTATS_MAIN' and
4                 pname = 'MBRC') *
5         (select to_number(value) block_size
6          from   v$parameter
7          where  name = 'db_block_size') /
8         (select pval1
9          from   sys.aux_stats$
10          where  sname = 'SYSSTATS_MAIN' and
11                 pname = 'MREADTIM')
12         /1024 sys_stat_mread_speed
13  from dual
14  /

SYS_STAT_MREAD_SPEED
--------------------
        42.4028269

Elapsed: 00:00:00.00

So the system statistics suggest a multiblock read performance of 42.4Mb/sec, and the Count(*) timing of 13.12sec and table size of 512Mb suggest 39Mb/sec.

This is just a single-threaded multiblock read on an otherwise quiescent system and so probably represents the upper limit on what this system could achieve, rather than what it would achieve in a multiuser environment, but the method seems useful as some kind of benchmark. Shouldn't it be possible to perform some kind of real-world comparison on this basis, if the system stats were collected during a period of multiuser activity?

I intuitively feel that this calculation would represent an approximation of how many Mb/sec you could expect from each thread of a parallel query, providing that neither the CPUs nor the disk subsystem are choking performance. So if you knew that you could run a degree 8 scan of a table without any part of the system choking, this ought to tell you hw long such a scan would take.

I think.

Update 14-Dec-2005

Following comments by Jonathan Lewis I ran a slightly modified version of the script to start a 10046 level 8 trace before the start of system statistics gathering. Here's a summary in which I've just included truncated SQL statements and wait events (and summarized those where appropriate):


BEGIN dbms_stats.gather_system_stats('START'); END;                             
SELECT /*+ rule */ VALUE FROM V$PARAMETER WHERE UPPER(NAME) = 'STATISTICS_LEVEL'
SELECT S.SNAME, S.PNAME, S.PVAL1, S.PVAL2 FROM AUX_STATS$ S WHERE S.SNAME LIKE 'S
WAIT #2: nam='db file sequential read' ela= 230 p1=1 p2=4434 p3=1
WAIT #2: nam='db file sequential read' ela= 139 p1=1 p2=4426 p3=1
SELECT SUM(KCFIOSBR) SBLKRDS, SUM(KCFIOSBT)*10 SBLKRDTIM, SUM(KCFIOMBR) MBLKRDS,
SELECT S1.VALUE - S2.VALUE MBREADS FROM V$SYSSTAT S1, V$SYSSTAT S2 WHERE S1.NAME
delete from  sys.cache_stats_0$
WAIT #20: nam='db file sequential read' ela= 8581 p1=1 p2=5425 p3=1
WAIT #20: nam='db file scattered read' ela= 1069 p1=1 p2=5426 p3=7
WAIT #20: nam='db file scattered read' ela= 10004 p1=1 p2=51393 p3=8
WAIT #20: nam='db file scattered read' ela= 13039 p1=1 p2=64049 p3=8
WAIT #20: nam='db file scattered read' ela= 3618 p1=1 p2=64073 p3=8
WAIT #20: nam='db file scattered read' ela= 10195 p1=1 p2=80513 p3=8
WAIT #20: nam='db file scattered read' ela= 12877 p1=1 p2=88233 p3=8
WAIT #20: nam='db file sequential read' ela= 4380 p1=2 p2=57 p3=1
WAIT #20: nam='db file sequential read' ela= 498 p1=1 p2=5434 p3=1
WAIT #20: nam='db file sequential read' ela= 142 p1=1 p2=5435 p3=1
WAIT #20: nam='db file sequential read' ela= 175 p1=1 p2=88257 p3=1
SELECT NVL(VALUE,'8.0') FROM V$PARAMETER WHERE NAME = 'compatible'
select sysdate, SYSDATE + 10/24/60 from dual
DECLARE job BINARY_INTEGER := 1; broken BOOLEAN := TRUE; next_date DATE := SYSDAT
SELECT NAME FROM SYS.USER$ WHERE USER# = :B1
WAIT #22: nam='db file sequential read' ela= 11775 p1=1 p2=98 p3=1
WAIT #22: nam='db file sequential read' ela= 150 p1=1 p2=91 p3=1
SELECT NAME FROM SYS.USER$ WHERE USER# = UID
SELECT JOBSEQ.NEXTVAL FROM DUAL
INSERT INTO SYS.JOB$ (JOB, LOWNER, POWNER, COWNER, NEXT_DATE, INTERVAL#, FLAG, WH
WAIT #25: nam='db file sequential read' ela= 2854 p1=1 p2=1473 p3=1
WAIT #25: nam='db file sequential read' ela= 263 p1=1 p2=1475 p3=1
WAIT #25: nam='db file sequential read' ela= 1069 p1=1 p2=1482 p3=1
WAIT #25: nam='db file sequential read' ela= 1132 p1=1 p2=1490 p3=1
UPDATE SYS.SNAP$ SET AUTO_FUN = :B2 , AUTO_DATE = :B1 WHERE (SOWNER,VNAME) IN (SE
WAIT #26: nam='db file sequential read' ela= 577 p1=1 p2=1497 p3=1
WAIT #26: nam='db file scattered read' ela= 260 p1=1 p2=1498 p3=2
UPDATE SYS.JOB$ SET FIELD1 = :B2 WHERE SYS.JOB$.JOB = :B1
update sys.job$ set cowner=:2, nlsenv=:3, env=:4 where job=:1
DELETE FROM SYS.AUX_STATS$ WHERE SNAME = 'SYSSTATS_INFO'
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_INFO', 'STATUS', NULL, :B1 )
WAIT #29: nam='db file sequential read' ela= 1898 p1=1 p2=4425 p3=1
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_INFO', 'DSTART', NULL, :B1 )
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_INFO', 'DSTOP', NULL, :B1 )
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_INFO', 'FLAGS', :B1 , NULL)
insert into sys.wri$_optstat_aux_history (savtime, sname, pname, pval1, pval2) se
WAIT #33: nam='db file sequential read' ela= 179 p1=3 p2=1028 p3=1
WAIT #33: nam='db file sequential read' ela= 27820 p1=3 p2=1019 p3=1
WAIT #33: nam='db file sequential read' ela= 170 p1=3 p2=1018 p3=1
WAIT #33: nam='db file sequential read' ela= 138 p1=3 p2=1017 p3=1
WAIT #33: nam='db file sequential read' ela= 228 p1=3 p2=1024 p3=1
WAIT #33: nam='db file sequential read' ela= 3783 p1=2 p2=9 p3=1
WAIT #33: nam='db file sequential read' ela= 5176 p1=2 p2=7095 p3=1
DELETE FROM SYS.AUX_STATS$ WHERE SNAME = 'SYSSTATS_MAIN'
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_MAIN', 'CPUSPEEDNW', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_MAIN', 'IOSEEKTIM', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_MAIN', 'IOTFRSPEED', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_MAIN', 'SREADTIM', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_MAIN', 'MREADTIM', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_MAIN', 'CPUSPEED', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_MAIN', 'MBRC', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_MAIN', 'MAXTHR', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_MAIN', 'SLAVETHR', :B1 , NULL)
DELETE FROM SYS.AUX_STATS$ WHERE SNAME = 'SYSSTATS_TEMP'
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_TEMP', 'SBLKRDS', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_TEMP', 'SBLKRDTIM', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_TEMP', 'MBLKRDS', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_TEMP', 'MBLKRDTIM', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_TEMP', 'CPUCYCLES', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_TEMP', 'CPUTIM', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_TEMP', 'JOB', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_TEMP', 'CACHE_JOB', :B1 , NULL)
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_TEMP', 'MBRTOTAL', :B1 , NULL)
insert into sys.wri$_optstat_opr (operation, target, start_time,  end_time) value
WAIT #3: nam='db file sequential read' ela= 181 p1=3 p2=1035 p3=1
WAIT #3: nam='db file sequential read' ela= 126 p1=3 p2=1034 p3=1
WAIT #3: nam='db file sequential read' ela= 124 p1=3 p2=1033 p3=1
WAIT #3: nam='db file sequential read' ela= 123 p1=3 p2=1040 p3=1
WAIT #3: nam='db file sequential read' ela= 4169 p1=2 p2=25 p3=1
WAIT #3: nam='db file sequential read' ela= 665 p1=2 p2=71 p3=1
WAIT #3: nam='db file sequential read' ela= 127 p1=3 p2=1044 p3=1
WAIT #16: nam='log file sync' ela= 201 p1=342 p2=0 p3=0
WAIT #16: nam='SQL*Net message to client' ela= 4 p1=1111838976 p2=1 p3=0
WAIT #16: nam='SQL*Net message from client' ela= 2175 p1=1111838976 p2=1 p3=0
=====================
PARSING IN CURSOR #3 len=44 dep=0 uid=40 oct=3 lid=40 tim=125426516619 hv=2242715
select /*+ noparallel (t) */ count(*) from t
END OF STMT
PARSE #3:c=0,e=3497,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=125426516613
EXEC #3:c=0,e=90,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=125426516827
WAIT #3: nam='SQL*Net message to client' ela= 5 p1=1111838976 p2=1 p3=0

(start of summary)
1024*db file scattered read, ela total = 11752972 (avg 11478)
  3*db file sequential read, ela total = 625
(end of summary)

(start of stat lines for count(*) from t)
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=65590 pr=65537 pw=0 t
STAT #3 id=2 cnt=65535 pid=1 pos=1 obj=260948 op='TABLE ACCESS FULL T (cr=65590 p
(end of stat lines()

WAIT #3: nam='SQL*Net message from client' ela= 230 p1=1111838976 p2=1 p3=0
WAIT #3: nam='SQL*Net message to client' ela= 2 p1=1111838976 p2=1 p3=0
WAIT #3: nam='SQL*Net message from client' ela= 2549 p1=1111838976 p2=1 p3=0
BEGIN dbms_stats.gather_system_stats('STOP'); END;
SELECT 1 FROM SYS.JOB$ WHERE JOB = :B2 AND POWNER = :B1 FOR UPDATE
DELETE FROM JOB$ WHERE JOB = :B1
UPDATE SYS.SNAP$ SET AUTO_FUN = 'null' WHERE (SOWNER,VNAME) IN (SELECT RC.OWNER,
insert into sys.wri$_optstat_aux_history (savtime, sname, pname, pval1, pval2) se
WAIT #6: nam='db file sequential read' ela= 10237 p1=2 p2=41 p3=1
WAIT #6: nam='db file sequential read' ela= 7822 p1=2 p2=2145 p3=1
DELETE FROM SYS.AUX_STATS$ WHERE SNAME = 'SYSSTATS_TEMP'
insert into sys.wri$_optstat_opr (operation, target, start_time,  end_time) value
WAIT #16: nam='log file sync' ela= 195 p1=388 p2=0 p3=0
WAIT #16: nam='SQL*Net message to client' ela= 4 p1=1111838976 p2=1 p3=0
WAIT #16: nam='SQL*Net message from client' ela= 3981 p1=1111838976 p2=1 p3=0
select pname,
      pval1
from   sys.aux_stats$
where  sname = 'SYSSTATS_MAIN'
WAIT #5: nam='SQL*Net message to client' ela= 3 p1=1111838976 p2=1 p3=0
WAIT #5: nam='SQL*Net message from client' ela= 217 p1=1111838976 p2=1 p3=0
WAIT #5: nam='SQL*Net message to client' ela= 2 p1=1111838976 p2=1 p3=0
WAIT #5: nam='SQL*Net message from client' ela= 8720 p1=1111838976 p2=1 p3=0
select (select pval1
       from   sys.aux_stats$
       where  sname = 'SYSSTATS_MAIN' and
              pname = 'MBRC') *
      (select to_number(value) block_size
       from   v$parameter
       where  name = 'db_block_size') /
      (select pval1
       from   sys.aux_stats$
       where  sname = 'SYSSTATS_MAIN' and
              pname = 'MREADTIM')
      /1024 sys_stat_mread_speed
from dual
WAIT #16: nam='SQL*Net message to client' ela= 3 p1=1111838976 p2=1 p3=0
WAIT #16: nam='SQL*Net message from client' ela= 838 p1=1111838976 p2=1 p3=0
WAIT #16: nam='SQL*Net message to client' ela= 3 p1=1111838976 p2=1 p3=0
WAIT #16: nam='SQL*Net message from client' ela= 2492 p1=1111838976 p2=1 p3=0

So it does look to me like the sequential reads are mostly generated by recursive SQL, although there are a few associated with the count(*) from t.

Not a clue on the thread statistics though.

New Index Section

2005-12-13T10:53:00.000-07:00

I added a new index section (is that the right term?) for articles about materialized views, since I seem to be writing so much about them.

Yesterday evening I felt nauseous while writing my next MV-related article, and I thought it was just an overdose of trace files or an allergy to consistent gets, but thankfully it turned out that rebooting my computer had led to a smell of burning insulation filling the room.

So, nothing to worry about there then.

System Statistics

2005-12-13T10:33:00.000-07:00

In 9i onwards we can use DBSMS_STATS.GATHER_SYSTEM_STATS to get some becnchmarks for system performance that are then usable by the cost-based optimizer, and it seems to me that these statistics ought to be a pretty good way of comparing different hardware (cutting through all the CPU GHz, disk speed, controller caching comparisons that are so detailed that it's virtually impossible to get useful information from them).

There don't seem to be many reference values for system statistics available anywhere, so over at the Dizwell Forum I started a thread to collect them -- if you have a moment then I'd appreciate it if you could post a very brief description of your hardware and the results of:

select pname,pval1 from sys.aux_stats$
where sname = 'SYSSTATS_MAIN'
/

Hopefully something analytical will come out of it.

If you don't want to register at Dizwell then feel free to reply below: thanks.

A Little Culture

2005-12-12T07:00:00.000-07:00

Almost unbelievably we managed to get away for a whole night, and went to Denver for a bit of culture with the Colorado Symphony Orchestra and this young whipper-snapper playing Chopin. Twenty-three years old, and twenty years experience as a piano player. Strewth.

Anyway, I feel like a crucial decision point coming up -- keep with the CD collection or start investing in SACD (and at some point the equipment to play them properly). I hate these technical decisions.

MV Articles

2005-12-09T13:13:00.000-07:00

I think I'm going to go back and change some article names: "Reducing The Work Of Refreshing Multiple Materialized Views" is not proving to be a good description, so I'll switch over to "Optimizing Materialized Views" as a stub.

Optimizing Materialized Views Part IV: Introduction to HOLAP Cubes

2005-12-08T23:01:00.000-07:00

A Quick Recap

In part II of this series I described the mechanism by which the direct path enhancement to materialized view fast refresh, and the benefits it bestows.

In part III I showed how we can identify circumstances in which we can entirely bypass Oracle's internal mechanism for fast refresh and instead use our own insert statement to maintain the materialized view. Tricky stuff, and not entirely conventional, but powerful nonetheless.

In this part IV we return to the straight and narrow of accepted practice, albeit somewhat at the cutting edge, by looking at a type of materialized view. More accurately we will look at a type of query which we can materialize in an MV and which specifically addresses the subject of multiple MV refreshes.

Hierarchical OLAP Cubes

One of the items of work that is carried out in the refresh of a materialized view is the scan of the changed data in the master table. When you are refreshing multiple materialized views based directly on the same fact table then that change data gets scanned multiple times. We might spend time looking at how we can reduce the work of these multiple scans, but how much more satisfactory it is to find a way of eliminating all but one of the scans entirely. We will do this by taking advantage of the ability of Oracle to aggregate to multiple levels in a single SQL statement.

You'll find this documented in various places under the subject of Hierarchical Cubes, most usefully in the Data Warehousing Guide, and the principle is fairly simple. In just the same way as a regular materialized view holds a single level of aggregation (for example at the level of "location code" and "transaction month") a hierarchical cube can hold a number of different aggregation levels in a single materialized view. Don't be misled or intimidated by the the word “cube” by the way. Other than a simple change in the syntax of the MV query and an additional concept or two there is nothing here that is much of a paradigm shift for us humble databasers.

So, a hierarchical cube hold multiple aggregation levels in a single table. We have been using as an example a fact table with a logical structure as follows:

create table master
 (
 location_cd  number not null,
 tx_timestamp date   not null,
 product_cd   number not null,
 tx_qty       number not null,
 tx_cst       number not null
 )
/

Based on such a table we have a great many choices of aggregation level that we might find use for. In general we might expect to use the following:

location_cd and trunc(tx_timestamp,'D')
product_cd and trunc(tx_timestamp,'D')
trunc(tx_timestamp,'D')
trunc(tx_timestamp,'MM')
location_cd and product_cd and trunc(tx_timestamp,'D')
location_cd and product_cd and tx_timestamp (if not unique in the master table, and even then see “The Mysterious Benefit Of Completely Redundant Materialized Views”)

Many of these materialized views are going to be very small, and it seems like a shame to be scanning many millions of new rows multiple times on their account. Maybe we might modify our strategy to base some of the higher level MV's on other lower level MV's, and that's an option that we'll look at later on, but for now let us see what an hierarchical cube can do for us.

Syntax

There are a number of slightly different syntaxes through which multilevel aggregation can be enabled, but the one that I prefer myself is the "Grouping Sets" method. This is because it gives me exact control over which aggregation levels I want to be "materialized" with the minimum of brainwork.

For example, consider the following syntax:

Select
   product_cd,
   location_cd,
   trunc(tx_timestamp,'MM') tx_month,
   sum(tx_qty) s_tx_qty,
   sum(tx_cst) s_tx_cst,
   count(*)    c_star
from
   master
group by
   grouping sets
      ((trunc(tx_timestamp,'MM'),product_cd ),
       (trunc(tx_timestamp,'MM'),location_cd));

Those last three lines are what makes this SQL “special”, by producing multiple levels of aggregation from a single SQL statement. In this case we have a single statement generating the aggregations at the month/product level and the month/location level.

Once you have grasped this it is of course easy to extend the syntax to more levels, and here is an example with six of them:

Select
   product_cd,
   location_cd,
   trunc(tx_timestamp,'MM') tx_month,
   sum(tx_qty) s_tx_qty,
   sum(tx_cst) s_tx_cst,
   count(*)    c_star
from
   master
group by
   grouping sets
      ((trunc(tx_timestamp,'MM'),product_cd),
       (trunc(tx_timestamp,'MM'),location_cd),
       (trunc(tx_timestamp,'MM')),
       (product_cd,location_cd),
       (product_cd),
       (location_cd));

Naturally when we compute multiple aggregation levels in a single query result we need a reliable method of distinguishing between the aggregation levels. We cannot just select a Sum() of a metric column of a cube like this without inflating the required result by some integer factor (six, in this last example), and it follows that every single query that gets rewritten against such a multilevel aggregation needs to select the rows of only a single aggregation level, and exclude all others.

To enable this we use a "Grouping" function, and the most convenient form for our purposes is GROUPING_ID. This identifies in a single value the aggregation (or “grouping” if you wish) level of a row by returning a bit vector of ... well just read about it here. But long-story-short, if you are going to define a materialized view with multiple aggregation levels then you include the GROUPING_ID function based on all the dimensions included as grouping levels. Thus we would write:

Select
   grouping_id
    (trunc(tx_timestamp,'MM'),
     product_cd,
     location_cd) gid,
   product_cd,
   location_cd,
   trunc(tx_timestamp,'MM') tx_month,
   sum(tx_qty) s_tx_qty,
   sum(tx_cst) s_tx_cst,
   count(*)    c_star
from
   master
group by
   grouping sets
   ((trunc(tx_timestamp,'MM'),product_cd),
    (trunc(tx_timestamp,'MM'),location_cd));

or:

select
   grouping_id
    (trunc(tx_timestamp,'MM'),
     product_cd,
     location_cd) gid,
   product_cd,
   location_cd,
   trunc(tx_timestamp,'MM') tx_month,
   sum(tx_qty) s_tx_qty,
   sum(tx_cst) s_tx_cst,
   count(*)    c_star
from
   master
group by
   grouping sets
      ((trunc(tx_timestamp,'MM'),product_cd),
       (trunc(tx_timestamp,'MM'),location_cd),
       (product_cd,location_cd),
       (trunc(tx_timestamp,'MM')),
       (product_cd),
       (location_cd));

Notice that we just include each dimension column once there, irrespective of how many times it is included in different grouping sets.

This seems like an appropriate time to look at the execution of such queries:

drop table master
/

create table master
   (
   location_cd  not null,
   tx_timestamp not null,
   product_cd   not null,
   tx_qty       not null,
   tx_cst       not null
   )
pctfree 0
nologging
as
select
   trunc(dbms_random.value(1,10)),
   to_date('01-jan-2005','DD-Mon-YYYY')
      + dbms_random.value(0,30),
   trunc(dbms_random.value(1,10000)),
   trunc(dbms_random.value(1,10)),
   trunc(dbms_random.value(1,100),2)
from
   dual
connect by
   1=1 and
   level < 1000001;

begin
   dbms_stats.gather_table_stats
     (ownname    => user,
      tabname    => 'MASTER',
      method_opt => 'for all columns size 254');
end;
/

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 4085247581

----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |  1000K|    82M|   667   (2)| 00:00:08 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |
|   2 |   LOAD AS SELECT           |                             |       |       |            |          |
|   3 |    TABLE ACCESS FULL       | MASTER                      |  1000K|    20M|   659   (2)| 00:00:08 |
|   4 |   LOAD AS SELECT           |                             |       |       |            |          |
|   5 |    SORT GROUP BY           |                             |     1 |    48 |     3  (34)| 00:00:01 |
|   6 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6607_12037633 |     1 |    48 |     2   (0)| 00:00:01 |
|   7 |   LOAD AS SELECT           |                             |       |       |            |          |
|   8 |    SORT GROUP BY           |                             |     1 |    48 |     3  (34)| 00:00:01 |
|   9 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6607_12037633 |     1 |    48 |     2   (0)| 00:00:01 |
|  10 |   VIEW                     |                             |     1 |    87 |     2   (0)| 00:00:01 |
|  11 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D6608_12037633 |     1 |    87 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Isn't that interesting? It's pretty esoteric stuff, although later on we'll see some real eye-bulgers. There is some kind of temporary structure being used there, but what is its nature? To answer questions of this sort we can run a SQL trace on the query, but I'm going to leave the results of that for another posting because otherwise I'm going to set another new record for length of blog entry. However it is clear that some internal temporary structures are being created to help with the aggregation, so some optimizations are at work here that we will explore in detail another time. For now let us look at some performance comparisions.

There are three types of comparison of interest here.

Firstly, how is the performance of a HOLAP query affected by the addition and nature of further aggregation levels? My instinct going into this exercise is that there is a certain overhead involved in using a HOLAP query, based on the existence of all those temporary structures that need to be created. Also I suspect that there could be optimizations that allow lower levels of aggregation to be leveraged in the calculations of higher levels – for example it could be that an aggregation to the (product_cd,location_cd) level can be used as an intermediate result in calculating an aggregation at the (product_cd) and the (location_cd) levels. It could also be that such optimizations are highly dependent on Oracle version.

Secondly, how does the performance of a HOLAP query compare with running multiple single-level aggregations?

Thirdly, how do the different forms of syntax – RollUp(), Grouping Sets() and Cube() -- compare performance-wise? Again, this may be very version dependent, and if the optimizations are in place we can expect there to actually be little difference.

Fourthly, how is all this affected by the new hash-based grouping algorithm of version 10gR2? Without the need for a SORT GROUP BY operation it seems that this might make a real difference to the internal optimizations of the aggregations.

HOLAP Performance With Different Aggregation Levels

First a hypothesis, or maybe just speculation.

A HOLAP query potentially contains enough information for the optimizer to tell that there are result sets generated that can then be leveraged to produce further result sets. For example if you are going to aggregate at 10,000,000 row table and generate a 1,000,000 row result set for the grouping (col1, col2) then if the 1,000 row groupings (col1) and (col2) are also required then it makes sense to use the (col1, col2) result set to do so. On the other hand if two result sets (col1, col2) and (col2, col3) are required then there might be an advantage in calculating (col1, col2, col3) as an intermediate step, but it would depend on the sophistication of the optimization and the determination of whether such an intermediate step was worthwhile. If no intermediate set is produced then the original set of rows would be scanned twice, and as long as we can keep our sorts in memory we ought to be able to detect this.

When thinking about the possibilities for aggregation it makes sense to think of a tree structure of potential intermediate result sets, such as the following

That one shows promise for optimization by leveraging the intermediate result sets. However the following one does not:

I'm going to construct a slightly different test data set for this experiment, involving the aggregation of 10,000,000 by three different columns each having around 1000 distinct values. We will try aggregations at a number of different levels and combinations of levels, such as: ((COL1)), ((COL1),(COL2)), ((COL1),(COL1,COL2)), ((col1),(col2),(col3),(col1,col2,col3)), etc. with the intent of exposing us to almost a full range of potential optimizations.

The methodology for these tests is to execute each query four times, flushing the buffer cache before each one. The average statistics of the last three executions will be taken (with the intent that the recursive calls due to the hard parse of the first execution are thus excluded). Workarea size policy will be set to manual and sufficient sort area allocated to eliminate disk sorts. The host machine and database will be as quiescent as possible, which is not difficult to achieve in this environment.

.... And with that I'm going to break off this entry to let you absorb these ideas, comment back if you wish, and give me time to complete the tests.

ORA-12034: materialized view log younger than last refresh

2005-12-08T19:00:00.000-07:00

Dabblers in the art of the materialisation of views may be used to seeing this error message, and it's often a portent of doom. Metalink Note 204127.1 tackles it head on with a list of possible causes, but is remiss in advising that a complete refresh is required.

And thank goodness for that. A multi-gigabyte materialized view based on a multi-multi-gigabyte master table is no joke to perform a complete refresh on, and anything we can do to avoid it is welcome.

As far as data warehousing is concerned we can trim the fat out of the Metalink note to shortlist our potential causes:

Previous refresh failed
Modifying the master table so that changes don't go to the materialized view log (truncate, alter partitions ... drop / truncate ... etc)
Master table reorganization

And here is another prime cause that the note doesn't identify:

Issuing an MV log purge.

All of these potential causes appear to be easily fixable through the simple mechanism of dropping and recreating the materialized view. But I'm not talking about dropping the data here. One of the requirements for this method to be successful is that the materialized view was created on a prebuilt table. If this is the case, and it is possible to find out if it is or not based on the BUILD_MODE column of USER_MVIEWS, then in the event of a refresh failure we can:

Drop the materialized view.
Do whatever is required (if anything) to bring the master table and the MV back into synchronization (delete data from master, or add data to MV).
Recreate the MV, again using the ON PREBUILT TABLE clause obviously.
Erm ...
That's it.

Doubtless you script hounds will be wanting to see a test of this, huh?


create table m (col1 number);

create materialized view log on m
with rowid (col1)
including new values;

create table
     m_mv_cstar
as
select count(*) c_star
from   m;

create materialized view m_mv_cstar
 on prebuilt table
 refresh fast
 on commit
as
select count(*) c_star
from   m;

insert into m values (1);

commit;

That gets us a successfully fast refreshing materialized view. Now we break it.


drop materialized view log on m;

create materialized view log on m
with rowid (col1)
including new values;

insert into m values (1);

commit;

That ought to get you the ORA-12034 error, and this is the time to go and check whether any other materialized views has successfully refreshed and whether the change to the master table is still intact or whether that has been rolled back. In this case the change to m ought to have been rolled back.

So now we fix the problem of the ORA-12034 without a complete refresh of the materialized view.


drop  materialized view m_mv;

create materialized view m_mv
  on prebuilt table
  refresh fast
  on commit
as
select count(*) c_star
from   m;

If by some chance you had mislaid the definition of your materialized view then use the following to retrieve it.


Set Long 100000 Pagesize 0

Select  DBMS_Metadata.Get_DDL('MATERIALIZED_VIEW','M_MV_CSTAR') From Dual;

Note the underscore in the MATERIALIZED_VIEW there. Forget that and you'll receive a baffling:


 1* Select  DBMS_Metadata.Get_DDL('MATERIALIZED VIEW','M_MV_CSTAR') From Dual
SQL> /
ERROR:
ORA-31600: invalid input value MATERIALIZED VIEW for parameter OBJECT_TYPE in
function GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 2576
ORA-06512: at "SYS.DBMS_METADATA", line 2627
ORA-06512: at "SYS.DBMS_METADATA", line 4220
ORA-06512: at line 1

So have we successfully recovered from the error?


insert into m values (1);

commit;

select * from m;

select * from m_mv_cstar;

Hopefully that demonstrates that we have.

So, is there a downside to this? Well if you have never run a complete refresh on your materialized view, or created it with a "build immediate" clause, then you will not get the comfort of a STALENESS of "FRESH" and will instead be stuck with the disturbing "UNKNOWN". Relax -- it's just that although you know that the materialized view is fresh, Oracle does not trust you. But then there are so many events that can break the "FRESH" staleness of your materialized view that you should treat FRESH MV's as a rare treat and not get used to them.

Yahoo Instant Messenger Out, Trillian In

2005-12-08T09:00:00.000-07:00

Working remotely from my client as I do, I make a lot of use of instant messaging. I made the serious error of upgrading to the latest version and have watched in amusement as it's memory usage often goes northwards of hundreds of megabytes. The record was 1.2Gb ... nice!

Even when freshly started it squats there hogging 15Mb, and usually hovers between 30Mb and 50Mb, which is inexcusable for software that basically lets me type stuff to other people.

So Yahoo Instant Messenger is officially Bad Software, and I've replaced it with Trillian Basic 3.1 ... I'll be watching it very closely, but so far it hasn't done anything unpleasant and uses a fraction of the memory.

Brrrr ...

2005-12-07T08:24:00.000-07:00

A little chilly this morning ... -2F/-19C

Oh, and guess who forgot to put the car in the garage last night?

Update: Guess who wisely decided to not put the car in the garage for fear of the garage door freezing immovably to the ground? That's right, me. I'm a genius.

Peterson AFB was on a two hour delay this morning, and the school district upgraded their two hour delay to a complete closure so I may be getting the company of a stir-crazy six year old boy. That'll be ... uh ... great.

On a positive note though, when it's this cold you don't find your vehicle encased in a solid sheet of ice in the morning. The snow just brushes straight off, sometimes into the top of an artfully placed boot.

It looks like it'll be hanging around a while as it's not due to get above freezing for a couple of days. We usually rely on sun + altitude + gravel to get the roads cleared, and it's pretty successful if you ignore the broken windshields and scratched paint. After our recent few days of cold our local roads are a sheet of inch-thick ice though, so that old advice that most accidents happen within five minutes of home is very true -- often five minutes can be only a hundred yards or so.

The Mysterious Benefit Of Completely Redundant Materialized Views

2005-12-06T19:00:00.000-07:00

A Scenario

You've just started working on a new data warehouse project, and you're familiarizing yourself with the database schema. You are looking at a simple table that stores transactional information and which is defined as follows:

create table master
(
transaction_cd number not null,
location_cd    number not null,
tx_timestamp   date   not null,
product_cd     number not null,
tx_qty         number not null,
tx_cst         number not null
)
/
alter table master
add constraint xpkmaster primary key (transaction_cd)
/

Plus various foreign keys, indexes and other stuff of course.

Built upon this table are a number of materialized views, one of which has the following definition:

create materialized view
master_mv1
...
Enable Query Rewrite
As
Select
transaction_cd,
location_cd,
product_cd,
tx_timestamp,
sum(tx_qty) s_tx_qty,
sum(tx_cst) s_tx_cst,
count(*)    c_star
from
master
group by
transaction_cd,
location_cd,
product_cd,
tx_timestamp
/

If you've spotted that all of the attribute columns of the master table are included in the materialized view, and one of those attributes is defined as the primary key of the master table, then you will correctly conclude the following: the materialized view is completely redundant. It stores exactly the same data as the master table.

So, simple question. Why go to the trouble of creating such an abominable waste of resources?

Answer below ...

Answer 1

It's because of a combination of problems.

Firstly, there are front-end tools that will not optimize their SQL to take account of situations where an aggregation of a metric is not required. A metric called "Transaction Qty" will commonly be defined as SUM(MASTER.TX_QTY) and will always be included in queries in the following way:

select
transaction_cd,
sum(tx_qty)
from
master
where
tx_timestamp between to_date('01-jan-2005')
                 and to_date('02-jan-2005') and
product_cd   = 3
group by
transaction_cd
/

The front end tool will not recognise that when the primary key of the MASTER fact table is also selected then it is sufficient (and more efficient) to:

select
transaction_cd,
tx_qty
from
master
where
tx_timestamp between to_date('01-jan-2005')
                 and to_date('02-jan-2005') and
product_cd   = 3
/

Now theoretically there is an opportunity for the Oracle optimizer to step in here and rescue us, but unfortunately it does not.

Here's a full demonstration script:

drop table master;
drop materialized view master_mv1;

create table master
(
transaction_cd not null,
location_cd    not null,
tx_timestamp   not null,
product_cd     not null,
tx_qty         not null,
tx_cst         not null
)
pctfree 0
nologging
as
select
rownum,
trunc(dbms_random.value(1,10)),
to_date('01-jan-2005','DD-Mon-YYYY')
   + dbms_random.value(0,30),
trunc(dbms_random.value(1,10000)),
trunc(dbms_random.value(1,10)),
trunc(dbms_random.value(1,100),2)
from
dual
connect by
1=1 and
level < ownname     =""> user,
   tabname     => 'master',
   granularity => 'ALL');
end;
/

create materialized view master_mv1
nologging
Using No Index
Never Refresh
Enable Query Rewrite
As
Select
transaction_cd,
location_cd,
product_cd,
tx_timestamp,
sum(tx_qty) s_tx_qty,
sum(tx_cst) s_tx_cst
from
master
group by
transaction_cd,
location_cd,
product_cd,
tx_timestamp
/

begin
dbms_stats.gather_table_stats(
   ownname     => user,
   tabname     => 'master_mv1',
   granularity => 'ALL');
end;
/

We can now compare the original and the rewritten queries and their execution plans:

explain plan for
select /*+ norewrite */
    transaction_cd,
    sum(tx_qty)
from   master
where  product_cd   = 3
group by transaction_cd;

select * from table(dbms_xplan.display());
-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |   102 |  1224 |   792   (3)| 00:00:10 |
|   1 |  SORT GROUP BY     |        |   102 |  1224 |   792   (3)| 00:00:10 |
|*  2 |   TABLE ACCESS FULL| MASTER |   102 |  1224 |   791   (3)| 00:00:10 |
-----------------------------------------------------------------------------
explain plan for
select transaction_cd,
    sum(tx_qty)
from   master
where  product_cd   = 3
group by transaction_cd;
select * from table(dbms_xplan.display());
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |   101 |  1212 |   874   (3)| 00:00:11 |
|*  1 |  MAT_VIEW REWRITE ACCESS FULL| MASTER_MV1 |   101 |  1212 |   874   (3)| 00:00:11 |
-------------------------------------------------------------------------------------------

explain plan for
select transaction_cd,
    sum(tx_qty)
from   master
where  location_cd   = 3
group by transaction_cd;

select * from table(dbms_xplan.display());
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |   111K|  1193K|   873   (3)| 00:00:11 |
|*  1 |  MAT_VIEW REWRITE ACCESS FULL| MASTER_MV1 |   111K|  1193K|   873   (3)| 00:00:11 |
-------------------------------------------------------------------------------------------

Notice also that if we place a tempting index on the transaction_cd column of master_mv1 then we can get further benefits:

alter table master_mv1 add constraint xpk_master_mv1 primary key (transaction_cd)
/

explain plan for
select /*+ norewrite */
    transaction_cd,
    sum(tx_qty)
from   master
where  transaction_cd   in (1,2,123456)
group by transaction_cd;

select * from table(dbms_xplan.display());
--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |     3 |    24 |     4   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT         |            |     3 |    24 |     4   (0)| 00:00:01 |
|   2 |   INLIST ITERATOR             |            |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| MASTER     |     3 |    24 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | XPK_MASTER |     3 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

explain plan for
select transaction_cd,
    sum(tx_qty)
from   master
where  transaction_cd   in (1,2,123456)
group by transaction_cd;

select * from table(dbms_xplan.display());
---------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                |     3 |    24 |     4   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR                        |                |       |       |            |          |
|   2 |   MAT_VIEW REWRITE ACCESS BY INDEX ROWID| MASTER_MV1     |     3 |    24 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                     | XPK_MASTER_MV1 |     3 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Again, the sort group by phase is eliminated.

Answer 2:

Different groups of users may have different reporting priorities. Store managers will be applying filters such as "location_cd = 5", while product managers will be applying "product_cd = 123". Both of them would benefit from the physical clustering of similar values for the columns they usually apply filters to, through partitioning (and hence partition pruning) or through an insert of rows sorted by their favourite column, or maybe though clustering.

My bet would be on the master and the MV tables having different partitioning keys.

Now that's not the only way to "skin this cat" -- of course you might go down the path of composite partitioning. However that will give you a limited number of columns on which partition pruning is possible. For example with a multicolumn-range/list composite partitioning scheme on (A,B,C)/(D) you can get partition pruning for queries on the following combinations of predicated columns:

A
AB
ABC
ABCD
AD
ABD

The following combinations do not benefit from partition pruning:

It's actually possible though to benefit from partition pruning in those latter cases if a logically redundant predicate is placed on other columns ... for example if A had only distinct values of 'X','Y', and 'Z' then by including a predicate of:

A In ('X','Y','Z')

...then it is possible to extend the range of partition pruning ... erm ... well maybe I'll write another blog entry on that, but as you can see it's a little bit tricky and still requires some upfront planning.

So does the query rewrite costing take account of differences in partitioning pruning between a master table and it's materialized views?

drop table master;
drop materialized view master_mv1;

create table master
(
transaction_cd not null,
location_cd    not null,
tx_timestamp   not null,
product_cd     not null,
tx_qty         not null,
tx_cst         not null
)
partition by list (location_cd)
(
partition p01 values (1),
partition p02 values (2),
partition p03 values (3),
partition p04 values (4),
partition p05 values (5),
partition p06 values (6),
partition p07 values (7),
partition p08 values (8),
partition p09 values (9),
partition p10 values (10)
)
pctfree 0
nologging
as
select
rownum,
trunc(dbms_random.value(1,10)),
to_date('01-jan-2005','DD-Mon-YYYY')
  + dbms_random.value(0,30),
trunc(dbms_random.value(1,10000)),
trunc(dbms_random.value(1,10)),
trunc(dbms_random.value(1,100),2)
from
dual
connect by
1=1 and
level < ownname     =""> user,
  tabname     => 'master',
  granularity => 'ALL');
end;
/

create materialized view master_mv1
partition by hash (product_cd)
partitions 256
compress nologging
Using No Index
Never Refresh
Enable Query Rewrite
As
Select
transaction_cd,
location_cd,
product_cd,
tx_timestamp,
sum(tx_qty) s_tx_qty,
sum(tx_cst) s_tx_cst
from
master
group by
transaction_cd,
location_cd,
product_cd,
tx_timestamp
/

begin
dbms_stats.gather_table_stats(
  ownname     => user,
  tabname     => 'master_mv1',
  granularity => 'ALL');
end;
/

explain plan for
select   transaction_cd,
      sum(tx_qty)
from     master
where    product_cd   = 3
group by transaction_cd;

select * from table(dbms_xplan.display());

explain plan for
select   transaction_cd,
      sum(tx_qty)
from     master
where    location_cd   = 3
group by transaction_cd;

select * from table(dbms_xplan.display());

Here are the execution plans:

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |            |     9 |   108 |     4  (25)| 00:00:01 |       |       |
|   1 |  SORT GROUP BY                 |            |     9 |   108 |     4  (25)| 00:00:01 |       |       |
|   2 |   PARTITION HASH SINGLE        |            |     9 |   108 |     3   (0)| 00:00:01 |    36 |    36 |
|*  3 |    MAT_VIEW REWRITE ACCESS FULL| MASTER_MV1 |     9 |   108 |     3   (0)| 00:00:01 |    36 |    36 |
-------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        | 11040 |   118K|       |    63   (4)| 00:00:01 |       |       |
|   1 |  SORT GROUP BY         |        | 11040 |   118K|   536K|    63   (4)| 00:00:01 |       |       |
|   2 |   PARTITION LIST SINGLE|        | 11040 |   118K|       |    11   (0)| 00:00:01 |     3 |     3 |
|   3 |    TABLE ACCESS FULL   | MASTER | 11040 |   118K|       |    11   (0)| 00:00:01 |     3 |     3 |
---------------------------------------------------------------------------------------------------------

We see here that the query is rewritten to take advantage of the different partitioning scheme of the materialized view. This simple case would have been a good candidate for a composite partitioning scheme of course.

The Cost Of Maintaining Redundant Materialized Views

So what is the cost to the system of providing this materialized view? Well if we're smart about how we maintain it then there is very little cost indeed. After all, we know that we don't have to aggregate the master data in order to populate the materialized view's table so I don't see why we would choose to do so.

In this previous blog I showed how there can be benefits to maintaining a materialized view manually, rather than through the internal refresh procedure. This redundant MV is an excellent example of a situation that benefits from such an approach, because the MV data is exactly the same as the master data. So instead of inserting the master table data and then refreshing the MV we can insert into both tables at the same time. As I noted before, the MV data can be maintained manually through either the partition exchange mechanism or by dropping the MV (if it is built on a predefined table) and directly inserting into it. In the latter case we could even use the multitable insert syntax that Oracle so thoughtfully provides to insert into both the master and the MV tables simultaneously, giving us the benefit of a single transaction.

Summary

So there we have it. Materialized views that are redundant in terms of data but which can provide interesting benefits in the report query phase.

Would I do such a thing myself? Maybe, maybe not. I don't believe that there ought to be qualms about the extra storage requirement because by the time you have used up half the capacity of todays hard drives you have already run out of bandwidth. I haven't worked in an environment where I have felt comfortable in implementing such a scheme, but I could see that I might. I can imagine a related situation where I would create a materialized view on a subset of the master table's columns in order to provide a skinny table (possibly with different partitioning scheme), in order to get the benefits of reduced i/o for scans of large number of rows and/or to leverage the optimizer's abandonment of the SORT GROUP BY phase when reporting on a primary key column.

Regardless of the practicality, I think that it serves as a nice demonstration of the power of query rewrite.

Dimensions Without Dimension Tables

2005-12-05T21:43:00.000-07:00

Mike Ault has blogged on the subject here.

Oracle Documentation Slow?

2005-12-01T11:16:00.000-07:00

Is it just me or is the Oracle documentation site very slow right now? I keep getting timeouts on searching.

I feel like someone is stepping on my oxygen pipe when that happens.

Antivirus and Firewall Software Change

2005-12-01T08:09:00.000-07:00

Norton is out, and AVS is in.

Norton has been killing my internet connection for a little while apparantly, but I only just found out that it was responsible when I noticed ccproxy.exe consuming 1.5Gb of memory and growing ... it's the kind of thing you tend to notice on a machine with 1.5Gb of physical memory plus Oracle EE 10gR1 running on it. I had to keep restarting the Symantec Netwrok Proxy service to restore the connection. "Bye-bye!"

So it turns out that not only does AVS have all the required credentials but it's user interface is also not nearly as funky as Norton's. It appears to do all the same stuff, plus it registers itself with Windows XP Security Center (which Norton never managed to do). The firewall part does everything I need as well.

Oh, and it's a darned sight cheaper as well -- the anti-virus part is free for home users, and AVG + Firewall Edition is a snip at $48.95 for a two year license.

Here you go: http://www.grisoft.com

(Their website is also much easier to use than Symantec's as well)

Optimizing Materialized Views Part III: Manual Refresh Mechanisms

2005-11-23T06:00:00.000-07:00

So, back on the subject of fast-refreshing multiple materialized views.

Reviewing my previous comments on this, I think it's time for some clarification. I wrote before that I prefer to consider that fast refresh is based on the materialized view log rather than the master table itself, but as I also wrote in a followup this is not actually the case when you load a master table using direct path inserts. In fact the materialized view log is not touched in this circumstance and instead the logging takes place in the ALL_SUM_DELTA system view. So from the view point of the refresh process itself you can consider the materialized view log to be redundant. This doesn't mean that you can do away with it, however. There is no mechanism in Oracle for saying that you want a materialized view to be fast refreshable based only on direct path inserts and that the MV log is never going to be used.

In fact you might want to go so far as to ensure that it really is redundant by preventing inserts into it, since these would be an indication that conventional path insert is being used. A couple of mechanisms for this spring to mind, such as placing the log in a readonly tablespace, or placing an error-raising insert trigger on it. This would thereby prevent anything other than a direct path insert into the master table, including deletes and updates -- considering the performance disparity between conventional and direct path change logging and subsequent fast refresh I would be inclined to go ahead with such a plan.

Refresh Mechanism Following Direct-Path Insert

So given that you have inserted data to the master table in direct path mode, what is the refresh mechanism for the materialized views? Looking at a SQL trace file tells us that a MERGE statement is used. Here is a prettified example derived from the script used here.

Merge Into DAVE.MASTER_MV1 SNA$
Using
(
Select  /*+  Opt_Estimate(Query_Block Max=1000)   */
DLT$0.LOCATION_CD                          GB0,
Trunc(DLT$0.TX_TIMESTAMP,'MM')             GB1,
Sum(1)                                     D0,
Nvl(Sum(1*(DLT$0.TX_CST)), 0)              D1,
Sum(1* Decode((DLT$0.TX_CST), Null, 0, 1)) H1,
Nvl(Sum(1*(DLT$0.TX_QTY)), 0)              D2,
Sum(1* Decode((DLT$0.TX_QTY), Null, 0, 1)) H2
From     (
Select  /*+ Cardinality(16800) No_Merge No_Merge(LL$) Rowid(MAS$)
          Ordered Use_Nl(MAS$) No_Index(MAS$) PQ_Distribute(MAS$,Random,None) */
     MAS$.ROWID RID$  ,
     MAS$.TX_TIMESTAMP,
     MAS$.LOCATION_CD ,
     MAS$.TX_CST      ,
     MAS$.TX_QTY
From   ALL_SUMDELTA LL$,
     DAVE.MASTER MAS$
Where  LL$.TABLEOBJ# = 256646 And
     LL$.TIMESTAMP > :1     And
     MAS$.ROWID    Between LL$.LOWROWID
                       And LL$.HIGHROWID
) As Of Snapshot (:2)  DLT$0
Group By DLT$0.LOCATION_CD,
Trunc(DLT$0.TX_TIMESTAMP,'MM')
)AV$
On (
Sys_Op_Map_NonNull(SNA$.LOCATION_CD) = Sys_Op_Map_NonNull(AV$.GB0) And
Sys_Op_Map_NonNull(SNA$.TX_MONTH)    = Sys_Op_Map_NonNull(AV$.GB1)
)
When Matched Then
Update  Set SNA$.C_STAR   = SNA$.C_STAR+AV$.D0,
 SNA$.S_TX_CST = DECODE(SNA$.S_TX_CST,
                        Null, Decode(AV$.H1, 0, Null, AV$.D1),
                              (SNA$.S_TX_CST + AV$.D1)),
 SNA$.S_TX_QTY = Decode(SNA$.S_TX_QTY,
                        Null, Decode(AV$.H2,0,Null,AV$.D2),
                              (SNA$.S_TX_QTY + AV$.D2))
When Not Matched Then
Insert (SNA$.LOCATION_CD,
SNA$.TX_MONTH,
SNA$.C_STAR,
SNA$.S_TX_CST,
SNA$.S_TX_QTY)
Values (AV$.GB0,
AV$.GB1,
AV$.D0,
Decode(AV$.H1,0,Null,AV$.D1),
Decode(AV$.H2,0,Null,AV$.D2))

One of the characteristics to note is the absence of an APPEND hint in the merge statement, and to me this implies the following:

New rows will be inserted through the conventional path. No direct path for you!
Data segment compression on the MV base table will not be leveraged.
If a fast refreshable MV is referencing MV1 as its master table then the change logging will be performed in the MV1 materialized view log, not through the SUM_DELTA mechanism

Optimizing MV Refresh By Avoiding MERGE

Another issue to note is simply that a single merge statement is used instead of an insert and an update. It is not inconcievable that you have a situation where the new data added to a fact table does not require modification of existing rows in one or more materialized views. If the materialized view groups by "Column A" and each load cycle on the fact table contains values of "Column A" that never appear in any other load cycle then this would be the case, and this might be either implicit or explicit in your data loading application.

The distinction here between implicit and explicit refers to whether the uniqueness of "Column A" to a particular load cycle is a deliberate design feature or whether it is more by happenstance or business requirements. An example of an explicit situation would be where you use a unique "load cycle code" as part of your ETL procedure (not a bad idea in itself), assigning a new value to each load and pushing this into one or more MV's. An example of an implicit situation might be where each load cycle operates on a granularity of fact table data that is repeated in the materialized view, such as where you load a complete day of transactions represented by a contiguous range of "TRANSACTION_TIME" (midnight-to-midnight) and the materialized view contains an expression such as:

Trunc(TRANSACTION_TIME,'MI')
Trunc(TRANSACTION_TIME,'HH')
Trunc(TRANSACTION_TIME,'D')

In such a circumstance it is conceivable that it would be more efficient to manage the materialized view aggregation ourself than to leave it to the RDBMS, because using an insert allows us the following potential advantages:

We avoid a potentially expensive join to the MV.
A direct path write allows us to minimize logging.
A direct path write allows us to use data segment compression.
We can enforce a sort order in the materialized view data, potentially improving report query performance

I predict that this last issue will become more important in 10gR2 as the new hash-based aggregation algorithm replaces the current sort-based method, and the likelihood that GROUP BY returns a value-clustered result set is reduced.

However the tricky part of such an operation is in modifying the materialized view's data, which is not allowed through DML.

SQL> insert into master_mv1
2  select
3     location_cd,
4     trunc(tx_timestamp,'MM') tx_month,
5     sum(tx_qty) s_tx_qty,
6     sum(tx_cst) s_tx_cst,
7     count(*)    c_star
8  from
9     master
10  group by
11     location_cd,
12     trunc(tx_timestamp,'MM')
13  /
insert into master_mv1
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

For the experienced data warehouse developer an obvious method for bypassing such a limitation is to use a partition exchange operation, so let us construct a test case.

In this script we load a fact table upon which are based two materialized views, one of which is refresh on demand and the other of which is never refresh. The latter will be manually refreshed using a direct path insert into a non-partitioned table followed by a partition exchange without validation. To avoid either of the refresh processes benefiting from buffer caching by the other, the buffer cache will be flushed.

drop table master;
drop table master_test_data;
drop table master_mv1_exch;
drop materialized view master_mv1;
drop materialized view master_mv2;

create table master
(
location_cd  number not null,
tx_timestamp date   not null,
product_cd   number not null,
tx_qty       number not null,
tx_cst       number not null
)
pctfree 0
nologging
/

create materialized view log on master
with rowid
(
location_cd,
tx_timestamp,
product_cd,
tx_qty,
tx_cst
)
including new values
/

create materialized view master_mv1
compress nologging
Partition By List (TX_MONTH)
(
Partition Y2005M01 Values (to_date('01-jan-2005'))
)
Using No Index
Never Refresh
Enable Query Rewrite
As
Select
product_cd,
trunc(tx_timestamp,'MM') tx_month,
sum(tx_qty) s_tx_qty,
sum(tx_cst) s_tx_cst,
count(*)    c_star
from
master
group by
product_cd,
trunc(tx_timestamp,'MM')
/

create table master_mv1_exch
compress nologging
as
select *
from   master_mv1
where  1=0
/

create materialized view master_mv2
compress nologging
Partition By List (TX_MONTH)
(
Partition Y2005M01 Values (to_date('01-jan-2005'))
)
Using No Index
Refresh Fast On Demand
Enable Query Rewrite
As
Select
product_cd,
trunc(tx_timestamp,'MM') tx_month,
sum(tx_qty) s_tx_qty,
sum(tx_cst) s_tx_cst,
count(*)    c_star
from
master
group by
product_cd,
trunc(tx_timestamp,'MM')
/

create table master_test_data
(
location_cd  not null,
tx_timestamp not null,
product_cd   not null,
tx_qty       not null,
tx_cst       not null
)
pctfree 0
nologging
as
select
trunc(dbms_random.value(1,10)),
to_date('01-jan-2005','DD-Mon-YYYY')
+ dbms_random.value(0,30),
trunc(dbms_random.value(1,1000)),
trunc(dbms_random.value(1,10)),
trunc(dbms_random.value(1,100),2)
from
dual
connect by
1=1 and
level < list      =""> 'MASTER_MV2',
method    => 'F');
end;
/
exit
/

From the wall-clock timing of the queries the manual refresh is superior with its three stages of insert, partition exchange and "consider fresh" completing in 5.08 seconds (4.75+0.15+0.18), representing 66% of the merge-based MV refresh time of 7.65 seconds.

Tkprof extracted the following details from the trace file

********************************************************************************

Insert /*+ append */ Into master_mv1_exch
Select
product_cd,
trunc(tx_timestamp,'MM') tx_month,
sum(tx_qty) s_tx_qty,
sum(tx_cst) s_tx_cst,
count(*)    c_star
from
master
group by
product_cd,
trunc(tx_timestamp,'MM')

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.05          2        344          3           0
Execute      1      2.89       3.78       1927       3404        110        9999
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      2.92       3.83       1929       3748        113        9999

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 40

Rows     Row Source Operation
-------  ---------------------------------------------------
1  LOAD AS SELECT  (cr=3404 pr=1927 pw=27 time=3785583 us)
9999   SORT GROUP BY (cr=3365 pr=1914 pw=0 time=3652049 us)
1000000    TABLE ACCESS FULL MASTER (cr=3365 pr=1914 pw=0 time=4008780 us)


Elapsed times include waiting on following events:
Event waited on                             Times   Max. Wait  Total Waited
----------------------------------------   Waited  ----------  ------------
db file sequential read                        11        0.01          0.05
control file sequential read                    4        0.00          0.00
db file scattered read                         70        0.18          0.73
direct path write                               5        0.00          0.00
SQL*Net message to client                       1        0.00          0.00
SQL*Net message from client                     1        0.00          0.00
********************************************************************************

...
********************************************************************************

MERGE INTO "DAVE"."MASTER_MV2" "SNA$" USING (SELECT   /*+
OPT_ESTIMATE(QUERY_BLOCK MAX=1000)   */ "DLT$0"."PRODUCT_CD" "GB0",
TRUNC("DLT$0"."TX_TIMESTAMP",'MM') "GB1", SUM(1) "D0", NVL(SUM(1*
("DLT$0"."TX_CST")), 0) "D1", SUM(1* DECODE(("DLT$0"."TX_CST"), NULL, 0, 1))
"H1", NVL(SUM(1* ("DLT$0"."TX_QTY")), 0) "D2", SUM(1*
DECODE(("DLT$0"."TX_QTY"), NULL, 0, 1)) "H2" FROM (SELECT  /*+ CARDINALITY(
335800) NO_MERGE NO_MERGE(LL$) ROWID(MAS$) ORDERED USE_NL(MAS$)
NO_INDEX(MAS$) PQ_DISTRIBUTE(MAS$,RANDOM,NONE) */  "MAS$"."ROWID" RID$  ,
"MAS$"."TX_TIMESTAMP", "MAS$"."PRODUCT_CD", "MAS$"."TX_CST",
"MAS$"."TX_QTY"  FROM ALL_SUMDELTA "LL$", "DAVE"."MASTER" "MAS$" WHERE
LL$.TABLEOBJ# = 257149 AND "LL$".TIMESTAMP > :1 AND  "MAS$".ROWID BETWEEN
"LL$".LOWROWID AND  "LL$".HIGHROWID ) AS OF SNAPSHOT (:2)  "DLT$0"  GROUP
BY "DLT$0"."PRODUCT_CD",TRUNC("DLT$0"."TX_TIMESTAMP",'MM'))"AV$" ON
(SYS_OP_MAP_NONNULL("SNA$"."PRODUCT_CD")=SYS_OP_MAP_NONNULL("AV$"."GB0")
AND SYS_OP_MAP_NONNULL("SNA$"."TX_MONTH")=SYS_OP_MAP_NONNULL("AV$"."GB1"))
WHEN MATCHED THEN UPDATE  SET "SNA$"."C_STAR"="SNA$"."C_STAR"+"AV$"."D0",
"SNA$"."S_TX_CST"=DECODE("SNA$"."S_TX_CST", NULL, DECODE("AV$"."H1", 0,
NULL, "AV$"."D1"), ("SNA$"."S_TX_CST" + "AV$"."D1")), "SNA$"."S_TX_QTY"=
DECODE("SNA$"."S_TX_QTY", NULL, DECODE("AV$"."H2", 0, NULL, "AV$"."D2"),
("SNA$"."S_TX_QTY" + "AV$"."D2")) WHEN NOT MATCHED THEN INSERT
("SNA$"."PRODUCT_CD", "SNA$"."TX_MONTH", "SNA$"."C_STAR", "SNA$"."S_TX_CST",
"SNA$"."S_TX_QTY") VALUES ( "AV$"."GB0", "AV$"."GB1", "AV$"."D0", DECODE
("AV$"."H1", 0, NULL, "AV$"."D1"), DECODE ("AV$"."H2", 0, NULL, "AV$"."D2"))



call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          4           0
Execute      1      6.04       6.66       1922       3760      10640        9999
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      6.06       6.68       1922       3760      10644        9999

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 40     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
2  MERGE  (cr=3811 pr=1925 pw=0 time=6662264 us)
9999   VIEW  (cr=3682 pr=1914 pw=0 time=6556192 us)
9999    HASH JOIN RIGHT OUTER (cr=3682 pr=1914 pw=0 time=6516189 us)
0     PARTITION LIST SINGLE PARTITION: 1 1 (cr=3 pr=0 pw=0 time=92 us)
0      MAT_VIEW ACCESS FULL MASTER_MV2 PARTITION: 1 1 (cr=3 pr=0 pw=0 time=73 us)
9999     VIEW  (cr=3679 pr=1914 pw=0 time=6415840 us)
9999      SORT GROUP BY (cr=3679 pr=1914 pw=0 time=6365838 us)
1000000       VIEW  (cr=3679 pr=1914 pw=0 time=14006210 us)
1000000        NESTED LOOPS  (cr=3679 pr=1914 pw=0 time=8006206 us)
35         VIEW  (cr=76 pr=0 pw=0 time=4235 us)
35          FILTER  (cr=76 pr=0 pw=0 time=4055 us)
35           NESTED LOOPS  (cr=76 pr=0 pw=0 time=3802 us)
35            NESTED LOOPS  (cr=4 pr=0 pw=0 time=755 us)
1             TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=75 us)
1              INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=32 us)(object id 36)
35             INDEX RANGE SCAN I_SUMDELTA$ (cr=1 pr=0 pw=0 time=472 us)(object id 161)
35            TABLE ACCESS CLUSTER USER$ (cr=72 pr=0 pw=0 time=2556 us)
35             INDEX UNIQUE SCAN I_USER# (cr=2 pr=0 pw=0 time=798 us)(object id 11)
0           NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
0            INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us)(object id 107)
0            FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us)
0           FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
1000000         TABLE ACCESS FULL MASTER (cr=3603 pr=1914 pw=0 time=3222833 us)


Elapsed times include waiting on following events:
Event waited on                             Times   Max. Wait  Total Waited
----------------------------------------   Waited  ----------  ------------
db file sequential read                        10        0.00          0.03
db file scattered read                         70        0.02          0.48
log buffer space                               13        0.00          0.03
********************************************************************************

The difference in elapsed time between the two methods is almost entirely accounted for in CPU time, probably due to the joins required by the merge statement. You might also notice that I took the opportunity to make the tables NOLOGGING and COMPRESS.

The methodology presented here for the manual refresh of the materialized view through a partition exchange is not the only one available, of course. Another choice might be to define the materialized view upon a prebuilt table, which allows the MV to be temporarily dropped so as to allow DML on the underlying table. The recreation of the MV definition is then a trivial issue in terms of database work required, and the only effect would be the temporary loss of the ability to rewrite queries against that MV. It would have the further advantage that indexes (or partitions thereof) on the materialized view table could be temporarily rendered unusable during the insert process so as to rebuild them in a nologging mode, without users being inconvenienced by "unusable index" errors.

It would also be possible to split the manual refresh into more stages to reduce the period for which query rewrite is unavailable:

Aggregate master table to temporary table
Drop materialized view
Insert into materialized view table from temp table (no aggregation required)
Recreate materialized view

However this would sacrifice many of the performance benefits of the single stage manual insert.

Summary

If there is an alternative mechnism for avoiding the MERGE then I'd be very glad to hear it, but this method may stand on its own if the alternatives do not allow direct path operations on the MV table or the sorting of the table data.

There are also some clear benefits to this mechanism when you consider the refresh of multiple materialized views, but they will wait until after thanksgiving (if I escape both the long term and immediate threats to my health of deep-frying a turkey tomorrow).

Last Minute Addendum

It occured to me just after posting this that another benefit of using a manual refresh is that you can avoid the overhead of calculating the extra columns required by the fast refresh mechanism, in this case the COUNT(*). I'll do a test on the benefits of that also.

Choosing Partitioning Keys: For ETL or Reporting?

2005-11-14T07:00:00.000-07:00

This is one of the recursive thought processes that has been spun off from considering ways of optimizing the fast refresh of multiple materialized views: I must now be at a recursive depth well into double figures because I cannot recall how I got here at all. Anyway ...

I was just browsing the 10gR2 documentation, and noticed the following comments here:

"The partitioning scheme of the data warehouse is often crucial in determining the efficiency of refresh operations in the data warehouse load process. In fact, the load process is often the primary consideration in choosing the partitioning scheme of data warehouse tables and indexes.

The partitioning scheme of the largest data warehouse tables (for example, the fact table in a star schema) should be based upon the loading paradigm of the data warehouse."

I have no argument with "The partitioning scheme ... is often crucial in determining the efficiency of refresh operations ...", nor with "... the load process is often the primary consideration in choosing the partitioning scheme ...": the former statement is self-evidently true and I'm sure that the latter is also true. However I do have a problem with, "The partitioning scheme ... should be based upon the loading paradigm ...". This strikes me as being not a very well thought-out piece of advice that depends more on an appeal to historical precedent or coincidence than it does to a rational design process. And it neglects the critical point that the partitioning scheme is always crucial in determining the efficiency of reporting operations.

I recently wrote something that touched on this subject in a roundabout way by mentioning that I was migrating a fact table from a partitioning scheme that aligned with the ETL process to a new scheme that aligns with the reporting requirements. Throughout the migration I keep thinking of events Quite Some Time Ago in which the administrators of an OLTP database objected to the addition of new indexes on a couple of columns. Their rationale for this was superficially sound: that it would have slowed down both the insert of new rows and the subsequent update of those columns. While they were not discounting the benefits of the indexes for select operations they were treating the pros and the cons as if they were on a level footing. In fact at the time that the row was inserted the pain was already half over - a few days after the rows were inserted the newly indexed column's value would change once and from there on there were only benefits to be gained from the new index. Substantial benefits they were too: two-orders of magnitude improvement in the performance of some key reporting queries.

Now that isn't the be all and end all of the considerations. To be completely fair we should also consider the sensitivity of the system (or the business) to the effects of the changes at the time that they are experienced. Taking the example above, if the loading of new data or the subsequent change to the indexed value was a critical process on which there were particular time restrictions, while the select statements that benefited were part of a batch operation with no current performance problems (in terms of whether the business was impacted by it running for longer than it could have with the index in place) then the technical benefits of the additional indexes might have been outweighed by operational issues.

So to appreciate the impact of a design feature on a system you not only have to consider whether those features represent potential benefits and detriments, but you have to weight them by a couple of other factors:

How often the benefit or detriment occurs
How sensitive the system's operations are to the benefits and detriments occuring when they do.

So back to the matter of the partitioning.

Hopefully the parallels between the situations are pretty clear. We have design choices (to index or not to index ... to partition according to the convenience of either the ETL procees or the reporting queries) that impose costs and benefits on different operational stages in the data handling.

Now let us look at the costs and benefits of taking different decisions in the partitioning, and we will start here by stating the obvious: that there is only a difference between the two partitioning schemes when the ETL and the reporting processes use different logical bases for identifying record sets of interest. If your data arrives in sets that correspond to the reporting requirements then this is not an issue.

I'll not rehash on the situation that caused the migration from an ETL-based partitioning scheme to a report-based partitioning scheme, but in brief the users are interested in reports based on the date of a financial transaction, but financial transactions often arrive very "late". For example, transactions for March 2005 could be transmitted in September 2005.

Let us suppose then that we follow the advice given in the documentation. We partition our fact table by some natural or "synthetic" date (synthetic in the sense that it does not represent a business object) that represents the arrival period of the data, say "DAY_OF_FILE_ARRIVAL". This is a great convenience to the ETL programmer, who can transform the new data set into a table having the same structure as the fact table (without partitioning, or partitioned if the fact table is composite partitioned), create or rebuild indexes on it, analyze it, handle new or changed dimensional values, then perform a partition exchange with the fact table to make the new data available to the user community.

However the users are then querying by "DATE_OF_TRANSACTION". For example they specify a range-based predicate of DATE_OF_TRANSACTION between '01-jan-2005' and '31-Jan-2005'. As this represents a small fraction of the data in the fact table, and as the rows are fairly well clustered, an index-based access method is used to retrieve the rows. In fact until the number of fact table blocks to be touched starts to exceed some threshold like 10% of the total, an index-based access method can be expected, with the undesirably high number of logical reads and small physical read size that you'd expect.

So in this scenario partition pruning for the reporting queries has been lost and with it has gone arguably the single most effective means of enhancing query performance against a large fact table, short of slapping materialized views all over it or using parallel query (which I take rather for granted in a data warehousing environment).

If we adopted the alternative partitioning scheme based on DATE_OF_TRANSACTION then partition pruning sanity would return to user reports, but at what cost to the ETL process?

First, let's consider what alternatives there might be to a simple partition exchange-based loading method. In practice there is just one -- the direct path insert, either serial or parallel. While a full quantitative comparison of the partition exchange and direct path inserts is not not going to be presented right here and now (although maybe I'll add it to my list), I feel that direct path inserts are not actually such a bad method for fact table loading. I'd consider the major disadvantage to be that they generate more redo because you are modifying enabled indexes for which the nologging attribute is not respected. In terms of manageability the pros and cons are mixed. On the one hand, you no longer need to worry about identifying which partition the data has to be exchanged with (if you ever did worry about that).

As far as statistics are concerned you do need to either allow monitoring and DBMS_Stats to handle the identification and collection of modified table statistics, or you need to start worrying about partitions again and implementing your own mechnaism for refreshing partition-level statistics. I have a feeling that while the latter approach may be more trouble by several orders of magnitude, it might still be preferable to the monitoring option. As the documentation here states, "If a monitored table has been modified more than 10%, then these statistics are considered stale and gathered again." While I am no fanatic about making sure that statistics are right up to date you would certainly want to think about whether this threshold is low enough, particularly in regard to histograms - it doesn't take a 10% change in the number of rows for a histogram to be significantly altered, particularly if business conditions and data distributions change with time.

I can't believe that I just set myself up for another blog topic. * sigh * Another recursive layer -- I need a vacation.

Another consideration is how well the batches of new records correlate with the partitioning scheme. Maybe your batch of new records is scattered all over a bunch of partitions, or maybe nearly all of the data is going to a single partition with a few rows scattered over a handful of others. In fact I prefer to think about this the other way around -- instead of thinking about this from the view point of how the new data is going to be split I'd rather consider what is ging to happen to the fact table partitions. Here are a few different scenarios:

One empty partition to be populated, one other partition to gain around 1,000 rows (0.01% of current total)
One empty partition to be populated, six others to gain between one and three million rows (1-3% of total), five others to gain between 10 and 1000 (0.001-0.1% of current total)
All partitions to gain around one million rows (1% of total)

How you treat these different scenarios, and how confortable you are with them, depends on where the breakeven point lies for you in your tolerance of ETL complexity and performance, and probably therefore on your ETL tool also.

When a partition starts out with data we have a couple of options.

Copy the data into a new/empty table in nologging mode, insert the new data, rebuild indexes, then partition exchange with the original.
Direct path load into the original partition, again with the option of disabling indexes where possible.

Which one of these approaches works best for you depends on a few factors. The former approach is certainly more challenging to program, while the latter will incur higher overhead on logging (although the overall i/o subsystem load may well be smaller).

So there you have it. I don't think that there is a clear cut case for partitioning according to ETL requirements, and the benefits of partitioning by reporting requirements are certainly enough to compel a close examination of the alternative to loading exclusively by partition exchange. Does this seem like an issue worthy of being rasied as a documentation bug? I'm not sure, but if people think it important then I'd be willing to raise one.

Oracle Patent on Load-Dependent Query Optimizer Costing

2005-11-12T15:13:00.000-07:00

As if we don't have enough factors to consider when scratching our heads over execution plans, we'll soon have to consider server load as a variable if Oracle implements the technology described in Patent 6,957,211.

When will the madness stop? "When", I ask you?

Choosing an Ordering Method for Best Table Compression

2005-11-10T12:56:00.000-07:00

Some comments here: http://www.phpbbserver.com/phpbb/viewtopic.php?t=614&mforum=dizwellforum

Oracle XE Registration

2005-11-04T10:53:00.000-07:00

Has anyone successfully registered this yet? I've submitted my registration three times so far without getting an acknowledgement. E-Mail details are correct and everything.

Anyone got any clues on this?

Dynamic Sampling at AskTom

2005-10-27T11:55:00.000-06:00

I saw an interesting question on dynamic sampling over at Ask Tom and ended up pretty much dumping a half-finished blog posting in reply. I'll save myself the elctrons of publishing it here by just linking to it. Enjoy.

Testing the Trace Loader

2005-10-06T07:00:00.000-06:00

As I said yesterday, I've been fiddling around with loading trace files into the database for analysis, and today I was looking for a good test case to see how the numbers are coming out. The obvious example to choose was the direct path enhancement to materialized view refresh that I also wrote about recently.

Here is my test script:


drop table master;
drop table master_test_data;
drop materialized view master_mv1;

create table master
   (
   location_cd  number not null,
   tx_timestamp date   not null,
   product_cd   number not null,
   tx_qty       number not null,
   tx_cst       number not null
   )
pctfree 0
nologging
/

create materialized view log on master
with rowid
   (
   location_cd,
   tx_timestamp,
   product_cd,
   tx_qty,
   tx_cst
   )
including new values
/

create materialized view master_mv1
Using No Index
Refresh Fast On Commit
Enable Query Rewrite
As
Select
   location_cd,
   trunc(tx_timestamp,'MM') tx_month,
   sum(tx_qty) s_tx_qty,
   sum(tx_cst) s_tx_cst,
   count(*)    c_star
from
   master
group by
   location_cd,
   trunc(tx_timestamp,'MM')
/

create materialized view master_mv2
Using No Index
Refresh Fast On Commit
Enable Query Rewrite
As
Select
   product_cd,
   trunc(tx_timestamp,'MM') tx_month,
   sum(tx_qty) sum_tx_qty,
   sum(tx_cst) sum_tx_cst,
   count(*)    c_star
from
   master
group by
   product_cd,
   trunc(tx_timestamp,'MM')
/

create table master_test_data
   (
   location_cd  not null,
   tx_timestamp not null,
   product_cd   not null,
   tx_qty       not null,
   tx_cst       not null
   )
pctfree 0
nologging
as
select
   trunc(dbms_random.value(1,10)),
   to_date('01-jan-2005','DD-Mon-YYYY')
      + dbms_random.value(1,31),
   trunc(dbms_random.value(1,1000)),
   trunc(dbms_random.value(1,10)),
   trunc(dbms_random.value(1,100),2)
from
   dual
connect by
   1=1 and
   level < 1000001
/
commit
/
select count(*) from master_test_data
/
alter session set timed_statistics = true;
alter session set max_dump_file_size=1000000000;
alter session set events '10046 trace name context forever, level 8';

insert into master
select *
from   master_test_data
where  rownum < 100001
/
commit
/
exit

Rather straightforward stuff. The "select count(*)" of rows in the master test data table is just to eliminate the possiblity of the test results getting skewed by delayed block cleanout, by the way. The example above is for the conventional path insert. The only change required for the direct path enhancement to kick-in is to use an APPEND hint in the last insert statement.

So, having identified the trace files I issue the following command:

begin
 trace.purge; -- purges all data from the trace file repository

   trace.load_file
   (directory   =>'UDUMP',
    file_name   => 'slimdb_ora_4412.trc',
    identify_as => 'direct path',
    debug       => 0,
    log_extents => true,
    log_files   => true);

   trace.load_file
   (directory   =>'UDUMP',
    file_name   => 'slimdb_ora_4600.trc',
    identify_as => 'conv path',
    debug       => 0,
    log_extents => true,
    log_files   => true);
end;
/

This allows me to query the trace file information directly, giving me such output as:

SQL> select
2     substr(identify_as,1,12),
3     op_name,
4     sum(op_c),
5     sum(op_e)/1000000 sec,
6     count(*)
7  from
8  (
9  select
10     identify_as,
11     cur_dep,
12     f.file#,
13     f.file_name,
14     c.cursor#,
15     o.op_name,
16     OP_C  ,
17     OP_E  ,
18     OP_P  ,
19     OP_CR ,
20     OP_CU ,
21     OP_TIM
22  from
23     trace_ops o,
24     trace_cursors c,
25     trace_files f
26  where
27     f.file#   = c.file# and
28     c.cursor# = o.cursor#
29     )
30  group by
31     substr(identify_as,1,12),
32     op_name
33  order by
34     1,2,4 desc
35  /

SUBSTR(IDENT OP_NAME     SUM(OP_C)        SEC   COUNT(*)
------------ ---------- ---------- ---------- ----------
conv path    EXEC         20875000  33.358519     100342
conv path    FETCH         2296875   3.026768        304
conv path    PARSE          156250    .661319        333
direct path  EXEC          2890625   3.476836        350
direct path  FETCH          140625    .406403        402
direct path  PARSE          859375   1.302354        224

6 rows selected.

Of course all the interesting work is going on behind the scenes in the form of recursive SQL:

SQL> select
2     substr(identify_as,1,12),
3     cur_dep,
4     op_name,
5     sum(op_c),
6     sum(op_e)/1000000 sec,
7     count(*)
8  from
9  (
10  select
11     identify_as,
12     cur_dep,
13     f.file#,
14     f.file_name,
15     c.cursor#,
16     o.op_name,
17     OP_C  ,
18     OP_E  ,
19     OP_P  ,
20     OP_CR ,
21     OP_CU ,
22     OP_TIM
23  from
24     trace_ops o,
25     trace_cursors c,
26     trace_files f
27  where
28     f.file#   = c.file# and
29     c.cursor# = o.cursor#
30     )
31  group by
32     substr(identify_as,1,12),
33     cur_dep,
34     op_name
35  order by
36     1,2,3,5 desc
37  /

SUBSTR(IDENT    CUR_DEP OP_NAME     SUM(OP_C)        SEC   COUNT(*)
------------ ---------- ---------- ---------- ---------- ----------
conv path             0 EXEC         14484375  22.637736         18
conv path             0 FETCH         2125000   2.342986         15
conv path             0 PARSE           93750     .56999         16
conv path             1 EXEC          6343750  10.694057     100188
conv path             1 FETCH          109375    .619292        147
conv path             1 PARSE           62500    .079386        187
conv path             2 EXEC            46875    .026135        128
conv path             2 FETCH           62500    .062152        134
conv path             2 PARSE               0    .009992        128
conv path             3 EXEC                0     .00006          1
conv path             3 FETCH               0    .002111          1
conv path             3 PARSE               0    .001875          1
conv path             4 EXEC                0    .000531          7
conv path             4 FETCH               0    .000227          7
conv path             4 PARSE               0    .000076          1
direct path           0 EXEC          1718750   2.218695          3
direct path           0 PARSE           31250     .17893          2
direct path           1 EXEC           750000    .841392        138
direct path           1 FETCH           15625    .128181         91
direct path           1 PARSE          343750     .47748        133
direct path           2 EXEC           140625     .14998        124
direct path           2 FETCH          109375    .168264        124
direct path           2 PARSE          421875     .52747         27
direct path           3 EXEC           281250    .260751         78
direct path           3 FETCH           15625    .068693        180
direct path           3 PARSE           46875    .109109         61
direct path           4 EXEC                0    .006018          7
direct path           4 FETCH               0    .041265          7
direct path           4 PARSE           15625    .009365          1

29 rows selected.

So what leaps out at us here (or it would do if Blogger didn't trim leading and multiple white spaces) is an enormous number of executions at a recursive depth (cur_dep) of 1 in the conventional path refresh -- coincidentally (well, actually not at all coincidentally) slightly more than the number of rows being inserted into the master table.

This of course, is the maintenance of the materialized view log being performed in an inefficient row-by-row manner:

SQL> select CUR_SQL_TEXT
2  from   trace_cursors
3  where  cursor# =
4     (
5     select cursor#
6     from
7     (
8     Select cursor#,count(*)
9     from   trace_ops
10     where  op_name = 'EXEC'
11     group by cursor#
12     order by 2 desc
13     )
14     where rownum = 1
15     )
16  /

CUR_SQL_TEXT
--------------------------------------------------------------------------------

INSERT /*+ IDX(0) */ INTO "DAVE"."MLOG$_MASTER" (dmltype$$,old_new$$,snaptime$$,

change_vector$,m_row$,"LOCATION_CD","TX_TIMESTAMP","PRODUCT_CD","TX_QTY","TX_C

ST") VALUES (:d,:o,to_date('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),:c,:m,

:1,:2,:3,:4,:5)

Hey, at least it's using bind variables!

Reporting Wait Events For Data Warehouse Queries

2005-10-05T06:30:00.000-06:00

Funny how easy it is to get side-tracked. Well not really side-tracked, but just more and more involved in one particular thing, so that you end up burrowing so deeply into it that it loses all relevance to the real world. Just as a simple SQL statement generates deeper and deeper levels of more and more obscure recursive statements, so it can be with the most simple of tasks.

Those of you still awake may recall that I was looking at materialized view refresh, and in the course of getting to grips with the optimum refresh methodology for fast refreshing multiple MV's I've been picking the bones out of some lovely warm trace files. My guidance has come from two books that are probably familiar to many readers, and which have become indispensible to me: Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning; Shee, Deshpande and Gopalakrishnan; Oracle Press, and Optimizing Oracle Performance; Millsap and Holt; O'Reilly. And let me first say that they are a beautiful compliment to each other. While the former goes into just the right level of detail on how to understand and mitigate the wait events themselves, the latter presents a methodology for data capture and event interpretation backed up with a strong background in queueing theory.

Now the usual challenge is to translate an approach aimed at OLTP databases into something that we can use in data warehousing, and fortunately this is not too tricky in the case of wait event capture and analysis. One of the major challenges associated with wait event analysis on OLTP databases is simply in capturing the correct data in the first place (a topic addressed in depth by the Millsap/Holt book) but this is relatively easy in "the other world": a problematic query that we are interested in tuning is generally well-known and easily isolated, whether it is part of the ETL routine or part of a report.

Issues relating to use of shared servers, which can scatter a single session's wait events among multiple trace files practically at random, are also probably rare. Shared server configurations are inherently problematic in an environment where system load is dominated by fewer, longer running queries than an OLTP system would experience, and so are rarely used.

On the other hand we do have the challenge of parallel query, in which the most meaningful wait events get pushed to different trace files, one for each PQ process. Without identifying these files, capturing their data, and consolidating them into a single entity for analysis there is little that can usefully be done.

And now a word about recursive SQL. Recursive SQL statements are issued by Oracle in support of the execution of the SQL statements submitted to the database. They are used to parse the query, to maintain the data dictionary (for example to add extents to table and index segments), to perform dynamic sampling, to retrieve information on segments for the optimizer to use, etc.. So we might be able to deduce immediately that recursive SQL plays a very large role in the execution of data warehouse queries for a number of reasons, the foremost of which are the high parse ratio and the complexity of the objects that the optimizer has to consider.

The high parse ratio is caused by our desire for the optimizer to be sensitive to literal values in the predicates (histogram usage being particularly important in this environment), and where an OLTP system could be unscalable due to the workload imposed by a high hard parse rate, the longer duration of the data warehouse queries renders this overhead less significant. Not wholly insignificant, but not so significant that it is worth eliminating entirely.

Now there is also the issue of object complexity. I venture to suggest that in data warehousing we have a much higher probability that partitioning is being used, and also that we will be leveraging partition pruning. So despite the apprant simplicity of many queries against star schemas there are actualy a disproportionate number of data and index segments for the optimizer to be aware of. There is also a high chance that we will be using query rewrite, and hence that there are a great many more objects to be considered even if they are never going to be touched by the optimized query.

Now I rather glossed over the issue of how easy it is in a data warehouse to identify the SQL for which we want to the wait events. However if you read much of the literature on wait event analysis you will find that this is actually a very large part of the formal methodologies generally presented. Do you want to identify the queries with the longest duration, or the most logical reads, or the most physical I/O, or what other considerations are there? When data warehousing relieves us of that issue we are left with two other major issues:

What makes our query run for so long?
What can we do about it?

And these are by no means trivial tasks. However I would suggest that the first of these issues really ought to be more trivial than it currently is. Consider the steps we might follow in a wait event analysis for a hypothetical data warehouse query.

Extract the query, along with representative predicate literals
Execute the query with the 10046 event set at level 8 ("8" rhymes with "wait", which is how I remember that level 8 gives us wait events and level 4 gives us binds :) ... and level 12 gives us both of course).
Find the trace files (parallel query giving us multiple files, of course)
Run tkprof on the trace files to turn them into something readable
Coalesce the results
Analyze what events are causing us pain (generally I/O-related ones and sometimes high CPU usage, I find).
For I/O related events examine what object they are occuring on, how many there are, their average duration, whether there are I/O hot spots on our devices etc..

For me the tricky part starts at step 4: I can extract queries, set events, find trace files without much problem, but the rest leaves me a little cold.

Let's start with tkprof. A fine program in general, but remembering the options for sorting the queries is beyond me, and having listed my major wait events I then have to go probing through the raw file again plus querying the data dictionary to find out what segment and file the events occured on. And remember that we may have upwards of a dozen file to contend with. Unless I'm missing out on something, that means a dozen executions of tkprof then a manual process to merge and examine all those raw files anyway.

Merging, sorting and reporting: these are the bread and butter tasks of a database, not a command-line programs. So while staring at the ceiling in the dark one night I had the following thoughts:

"Load the raw files into the database then report on them from there."
"That means I can link the I/O-related wait events directly to the data dictionary to get object and file names automatically".
"That means we'd have a history of the the exact wait events and the real execution plan associated with a query within the database itself"
"That's so obvious that it must have been done before."
"Even if it has, I bet I could do it easily and customize it for my own purposes."

So that's what I've been up to for the past couple of weeks, in the little time I have between my real work and my family, instead of going through the materialized view refresh analysis that I actually promised. But it's a step in the right direction anyway -- should make that process more easy.

How do you go about desinging and implementing such a methodology then? Here are the aims that I started out with.

Load into database tables ther significant data from multiple trace files

For me the data of interest are the wait events, and in order to put them into a usable context I also need to associate them with SQL statements (cursors, in the trace file), the file name, operations such as PARSE, EXEC and FETCH. They all have to be stored in a set of dedicated tables.

Exactly which data would we load for each of these types of trace information? As much as possible, and for the most part that means all of it. We can get timings, recursive depth (very useful!), user id's, just about anything we need.

Associate significant system data with the trace file data

In our world of data warehousing we regularly add, truncate, split, merge and drop partitions, and when we do so we change the object id to which the wait event parameters refers. It seems prudent for this reason and for performance purposes (data dictionary views being notoriously cranky when it comes to joining to other data) to provide the option of storing the appropriate data at the time that the trace file is loaded.

Associate multiple trace files with a single report

This is of particular use in coalescing the results of multiple files generated by parallel query for a single submitted SQL statement. So we'd want to load multiple trace files and say "these belong together".

Provide predefined mechanisms for reporting on the events

A set of views on the database tables was going to be required, to allow more simple custom queries to be defined on the trace data. Analagous to the data dictionary views provided in the database in fact. In just the same way as "select * from table(dbms_xplan.display())" reports on the most recently generated explain plan, we can provide a mechanism for reporting on the most recently loaded trace file(s) for example.

Provide some administrative tools for purging old data

Purge all the data, or all the data prior to some date, and some options like that.

The progress so far has been encouraging. Loading in a trace file with a known name is now a trivial task, and basic reporting on the events is a doddle. For example I'm currently using the following command to load a single file:


begin
   trace.load_file
     (directory   =>'UDUMP',                -- 1
      file_name   => 'slimdb_ora_4680.trc', -- 2
      debug       => 0,                     -- 3
      log_extents => true,                 -- 4
      log_files   => true);                 -- 5
end;
/

The parameters are pretty intuitive:

The name of the directory object that points to the location of the file to be loaded.
The name of the file to be loaded (duh).
The level of debugging message required (spat out through DBMS_OUTPUT).
Whether to take a snapshot of the relevant entities in the dba_extents system view, to enhance later reporting (default "false" due to relatively high overhead).
Whether to take a snapshot of the relevant entities in the dba_data files system view, to enhance later reporting (default "true").

With the data loaded I can then report in the following manner:


SQL> select * from all_trace_files;

     FILE# FILE_NAME                      READ_DATE
---------- ------------------------------ ---------
    132440 slimdb_ora_4680.trc            05-OCT-05

SQL> column SUM_WT_ELA format 999,999,990
SQL> column AVG_WT_ELA format 999,999,990
SQL> column waits format 999,990
SQL> break on report
SQL> compute sum of SUM_WT_ELA on report
SQL> select substr(WT_NAME,1,25) wt_name,
  2  sum(WT_ELA) sum_wt_ela,
  3  avg(wt_ela) avg_wt_ela,
  4  count(*) waits
  5  from last_trace_waits
  6  group by substr(WT_NAME,1,25)
  7  /

WT_NAME                     SUM_WT_ELA   AVG_WT_ELA    WAITS
------------------------- ------------ ------------ --------
SQL*Net message from clie   27,372,317    9,124,106        3
SQL*Net message to client           14            5        3
db file scattered read       4,771,858       18,640      256
db file sequential read      1,490,384        3,644      409
                          ------------
sum                         33,634,573

SQL> select   CUR_DEP,
  2           count(*)
  3  from     last_trace_cursors
  4  group by cur_dep
  5  order by 1
  6  /

   CUR_DEP   COUNT(*)
---------- ----------
         0          2
         1        391
         2       2189
         3         92
         4          2
         5          1

6 rows selected.

SQL> set pagesize 100
SQL> select substr(WT_NAME,1,23) wt_name,
  2  substr(segment_name,1,20) segment_name,
  3  sum(WT_ELA) sum_wt_ela,
  4  avg(wt_ela) avg_wt_ela,
  5  count(*) waits
  6  from last_trace_obj_waits
  7  group by substr(WT_NAME,1,23),substr(segment_name,1,20)
  8  order by 3 desc
  9  /

WT_NAME                 SEGMENT_NAME           SUM_WT_ELA   AVG_WT_ELA    WAITS
----------------------- -------------------- ------------ ------------ --------
db file scattered read   FCT_FSA_1               4,698,204       19,908      236
db file sequential read INDSUBPART$             3,963,720        1,149    3,450
db file sequential read C_OBJ#_INTCOL#          2,355,677        4,403      535
db file sequential read C_FILE#_BLOCK#          2,129,218        1,342    1,587
db file sequential read I_OBJ2                    676,269        1,375      492
db file sequential read I_FILE#_BLOCK#            470,275        4,237      111
db file sequential read FCT_FSA_1                 417,323        5,962       70
db file sequential read TABSUBPART$               170,888        4,069       42
db file scattered read  INDCOMPART$               130,647        6,221       21
db file sequential read HIST_HEAD$                 62,906        7,863        8
db file sequential read I_INDSUBPART$              61,507        1,922       32
db file scattered read  TABCOMPART$                57,753        6,417        9
db file sequential read I_CDEF2                    34,272        4,284        8
db file sequential read SNAP$                      30,162        7,541        4
db file sequential read PARTOBJ$                   29,690        7,423        4
db file sequential read SUMAGG$                    24,404        6,101        4
db file sequential read INDCOMPART$                19,534        9,767        2
db file sequential read SUMKEY$                    19,116        4,779        4
db file sequential read DIMJOINKEY$                17,296        4,324        4
db file sequential read PARTCOL$                   15,068        3,767        4
db file sequential read TABCOMPART$                14,988        7,494        2
db file sequential read SUMDELTA$                  12,143       12,143        1
db file sequential read I_TABSUBPART$              11,146        5,573        2
db file sequential read I_CDEF3                    10,627       10,627        1
db file sequential read I_SUBCOLTYPE1              10,131       10,131        1
db file sequential read I_NTAB2                     9,696        9,696        1
db file sequential read I_CCOL1                     9,366        4,683        2
db file sequential read C_COBJ#                     9,322        9,322        1
db file sequential read SUM$                        8,722        4,361        2
db file sequential read I_OBJ#_INTCOL#              7,496        7,496        1
db file sequential read I_SUBPARTCOL$               7,059        7,059        1
db file sequential read I_DIMATTR$_2                6,601        6,601        1
db file sequential read DEPENDENCY$                 6,432        3,216        2
db file sequential read I_HH_OBJ#_INTCOL#           6,267        6,267        1
db file sequential read I_DEFSUBPART$               5,595        5,595        1
db file sequential read VIEW$                       5,231        5,231        1
db file sequential read DEFSUBPART$                 4,795        4,795        1
db file sequential read I_DEPENDENCY1               4,649        4,649        1
db file sequential read I_REFCON2                   3,544        3,544        1
db file sequential read I_PARTCOL$                  2,705        2,705        1
db file scattered read  SUM$                          742          371        2
db file sequential read I_OPQTYPE1                    195          195        1
db file sequential read SUBPARTCOL$                   138          138        1
db file sequential read I_DIMLEVELKEY$_2              136          136        1
                                             ------------
sum                                            15,541,655

The last two are pretty interesting, for a trace file based on a single star schema query, huh? Sorry about the crappy formating though.

There's still work to do, and at the top of my list are the following:

Allow grouping of trace files as a single logical entity
Add more reporting views, focusing on filtering out recursive statements and on a set of "MY_%" views
Associating recursive queries with their parents to enable reporting views based on CONNECT BY queries
Better handling of files
Creating a load script for the TRACELDR schema under which all this runs
Creating a Trace Loader role to allow other users to access the package and views.

Based on that you can probably expect to hear from me again around Christmas.

Nah, not really. Soon.

Vacation Time

2005-09-15T15:04:00.000-06:00

But it's just Dayton, OH, to cheer on the wife's running of the Air Force (half) marathon.

See you next week.

Optimizing Materialized Views Part II: The Direct Path Insert Enhancement

2005-09-15T11:00:00.000-06:00

This follows on from some recent thoughts about the need to find ways of optimizing fast refresh against multiple materialized views.

In the documentation for both 9i and 10g you will find references to the optimization of fast refresh materialized views when direct-path insert is applied to the master table. For example here it states that "Fast refresh can perform significant optimizations if it finds that only direct loads have occurred...". As far as I can tell the documentation gives no further information, but a couple of tests on the internal refresh mechanism are very revealing.

Here is how conventional, non-Partition Change Tracking fast refresh works with conventional insert (two uses for the word "conventional" -- that won't be confusing at all):

Bulk conventional insert into master_table (Insert Into master_table ... select ... or other methods such as SQL*Loader conventional path)
Recursive SQL maintains MV log through multiple Insert Into MLOG$... VALUES ()
Commit
MV maintained by Merge Into MV Using (Select ... From MLOG$...)...

Here is the direct path enhancement:

Direct-path insert into master_table (Insert /*+ APPEND */ Into master_table ... select ... or other methods such as SQL*Loader direct path)
Commit
MV maintained by Merge Into MV Using (Select ... From ALL_SUMDELTA, Master_Table...)...

The Difference

Here are the highlights of the differences:

Although some recursive SQL is performed on an empty MLOG, there is no insert activity into the MLOG table at all.
The MV's are not updated from the MLOG. Instead, the ALL_SUMDELTA view identifies the ranges of rowid's in the master_table that were just inserted, and a join between ALL_SUMDELTA and master_table is all that is needed to supply the change vectors required for the MV fast refresh. Recall that direct path insert creates contiguous blocks of records above the table's high water mark, and thus the new records are not interspersed with old records.

The loss of MLOG activity is probably the major reason for the performance boost (I'll pick the bones out of the trace file hopefully to confirm this next time). The multiple Insert Into ... Values ... statements are an enormous overhead (measurable with SQL tracing), and they both prevent the insert into the MLOG from being a nologging operation and they also lose the benefits of a single bulk operation over multiple single operations. The lack of rows in the table for subsequent recursive SQL against MLOG also makes those SQL's an insignificant use of resources.

The identification of the change vectors for the refresh process may also show improvement. In particular, where the MLOG represents a substantial proportion of the master table's columns and the master table is compressed (not uncommon in fact tables) then the size of the change vectors stored in the MLOG is likely to exceed that of the new records in the table. On the other hand, the MLOG blocks are possibly still in the buffer cache at the time that the MV refresh takes place, which would not be the case with the new rows in the master table (which bypass the buffer cache).

Further Thoughts

One inference that I'm drawing from all this is that the advantages of Partition Change Tracking fast refresh over conventional fast refresh are eroded enormously, which is a relief because I'm not a PCTFR fan at all for reasons that I think I've mentioned here before. If not, let me know.

And here's another thought: if your intention is that MV refresh must never be based on conventional path insert because of the enormous performance loss, then could you place a trigger on the MLOG table to raise an error before insert?

There's a little more work needed there to iron out the exact costs and benefits on the MV refresh process, but the performance increase on the lack of MLOG maintenance alone is an enormous boost.

So to summarize:

"The best way to improve the performance of a task is not to do it at all"

"Bulk operations beat single-row operations"

Some Results

Now then, I don't want to suggest that the following results are formal and repeatable, but on my own test hardware I see the conventional method coming in at 17 minutes for the insert and 12 minutes for a refresh, in contrast to the direct method's 1 second for the insert and 4 seconds for the refresh. These number appear to be an improvement of such magnitude that I'm almost loath to publish them, so let's place a big "YOUR RESULTS MAY VARY" label on them for now.

Next Time

Anyway, that's all for now. I'm going to have a dig through the trace files for more detailed information.

Oh by the way, this extremely powerful optimization is patented by the Oracle Corporation, so hands off it ;)

The Test Script

drop table master;
drop table master_test_data;
drop materialized view master_mv1;
drop materialized view master_mv2;

create table master
(
location_cd number not null,
tx_timestamp date not null,
product_cd number not null,
tx_qty number not null,
tx_cst number not null
)
pctfree 0
nologging
/

create materialized view log on master
with rowid
(
location_cd,
tx_timestamp,
product_cd,
tx_qty,
tx_cst
)
including new values
/

create materialized view master_mv1
Using No Index
Refresh Fast On Commit
Enable Query Rewrite
As
Select
location_cd,
trunc(tx_timestamp,'MM') tx_month,
sum(tx_qty) s_tx_qty,
sum(tx_cst) s_tx_cst,
count(*) c_star
from
master
group by
location_cd,
trunc(tx_timestamp,'MM')
/

create materialized view master_mv2
Using No Index
Refresh Fast On Commit
Enable Query Rewrite
As
Select
product_cd,
trunc(tx_timestamp,'MM') tx_month,
sum(tx_qty) sum_tx_qty,
sum(tx_cst) sum_tx_cst,
count(*) c_star
from
master
group by
product_cd,
trunc(tx_timestamp,'MM')
/

create table master_test_data
(
location_cd not null,
tx_timestamp not null,
product_cd not null,
tx_qty not null,
tx_cst not null
)
pctfree 0
nologging
as
select
trunc(dbms_random.value(1,10)),
to_date('01-jan-2005','DD-Mon-YYYY')
+ dbms_random.value(1,31),
trunc(dbms_random.value(1,1000)),
trunc(dbms_random.value(1,10)),
trunc(dbms_random.value(1,100),2)
from
dual
connect by
1=1 and
level <= 100000 / commit
/
select count(*) from master_test_data
/

alter session set events '10046 trace name context forever, level 8'
/
set timing on

insert /*+ append */ into master select * from master_test_data
where rownum <= 50000 / commit
/
insert /*+ append */ into master select * from master_test_data
/
commit
/
exit

From the "I'm no expert, but..." file

2005-09-14T07:30:00.000-06:00

* Ahem *

It would take a blind man, or one with work/hobbies to get on with instead, to avoid noticing all the talk about legal liability that folks might incur as the "owner"/"publisher" of a blog or forum. Serious stuff really, when some people say that just by allowing comments on your blog you are potentialy opening yourself up to potential liability for actionable defamation and such things.

My policy has been to allow comments by anyone and so far the only one's I've removed have been spam. I must have a well-behaved readership, or something. :D

Anyhoo, here's a resource that gives some pretty good news on the subject: the Electronic Frontier Foundation's Legal Guide For Bloggers. The best news is that even if you don't have a blog then it's an interesting read all the same. For example, you can learn why a person included on a list of "Top Ten Dumbasses" lost their case for libel. More seriously the page on Section 230 is an essential read.

Like I say though, I'm no expert. But these people certainly seem to know what they're talking about.

Webpage Guru

2005-09-08T00:05:00.000-06:00

I don't want to speak too soon but I seem to have managed to alter the blog template to make the text area wider, through a technical process of mucking about with likely looking parameters (in many ways similar to network administration). Best of all nothing seems to be broken.

If you have problems viewing this then let me know and I'll re-tweak it.

Next challenge -- alter the background colour to an eye-watering bath sponge yellow.

Optimizing Materialized Views Part I: Principles For Analysis

2005-09-07T20:00:00.000-06:00

Some Background

For a variety of reasons that are too painful to relate, query rewrite against some largish fact tables has been unsuccessful in the past, and manual builds of summary tables have been combined with the aggregate awareness of Business Objects to produce the same effect.

The major drawback to this was the extraordinary number of hoops that had to be jumped through to make a change. The creation of a new summary table required coordinated efforts by a database designer, a DBA, a Business Objects designer and an ETL designer. As we all know the difficulty of coordinating such effort is related to something like the 7th power* of the number of people involved, so in other words by the time you have four people involved the job will take forever to get done.

In other other-words, new summary tables were never added.

However now that we have an apparantly robust and automatic method for maintaining the integrity of the materialized view refresh process and the resultant query rewrite ability, we find ourselves with a different choke-point on the creation of new materialized view-based summaries: the duration of the daily fast refresh process that accompanies the arrival of new fact table data. So here is a thought experiment that I'm going through at the moment.

"What techniques do we have for optimizing the refresh of materialized views?"

For my own selfish reasons I'm thinking about conventional fast refresh of materialized views based on a single table. Any resemblance between this scenario and others is purely coincidental, but may exist all the same.

The Scenario

So let us examine a scenario where we have multiple fast-refreshable materialized views based on a single table. Personally when thinking about fast-refreshable MV's I prefer to consider that they are based on a materialized view log, not the table itself. It is fairly trivial to demonstrate that a fast refresh of a materialized view is based entirely on the MV log without the requirement of access to the master table -- I did it for Oracle Support once by creating a fast refresh-on demand MV, and taking the tablespace with the master table offline before successfully refreshing the MV.

The MV log table serves as a change-capture mechanism for its master table Some of them represent conventional summaries of the fact table. Some of them represent lists of distinct values of particular attribute columns in the table (such as Date_Of_Day, for example) in order to support fast retrieval of such values for the user interface. Some of these materialized views could logically be based on others, for example a list of Date_Of_Day values could be based on a summary table that stored metrics at a dimensionality of (Date_Of_Day, Location_Cd, Product_Cd).

I suppose that we had also best define what we mean by "minimizing work". Again for my own selfish reasons I'm talking here about minimizing disk reads and writes, and in making those that occur as efficient as possible.

The issue here is in the multiple scans of a materialized view log that are required in order to support the maintenance of materialized views. If you have seven materialized views based directly on a fact table then I don't see that Oracle can avoid seven consecutive scans of the log in order to maintain them.

Reducing The Workoad

In approaching this problem I'm dividing the approaches up into a number of categories.

More efficient modifications to the materialized view log.
More efficient reads of the materialized view log.
Fewer reads of the materialized view log.
More efficient update of the materialized views.
Fewer materialized views.
Erm ...
That's it.

Now it seems to me that any one of these categories with the possible exception of the last two holds promise for more efficient MV maintenance -- when they are combined together we ought to be able to do something worthwhile.

Methodology

I think that I can say without any fear of contradiction that if you want to make a process more efficient then you have to start by fully understanding it, and to my knowledge there are few tools more powerful for rummaging in the internals of an obscure Oracle process than a SQL trace.

So here's the plan.

Set up a representative scenario involving conventional fast refresh of multiple materialized views against a single master table, in the form of a SQL script.
Execute the script with SQL tracing turned on.
Extract from the trace file any statement that touches the materialized view log or the materialized views.
Look for methodologies for optimizing each statement.
Look for opportunities for reducing the number of statements executed.
Measure.
Change.
Re-execute
Re-measure.
Compare benefits
Et cetera

Well, as I mentioned previously these blogs have a habit of growing out-of-control, and personally I have the attention span of a goldfish so I'm going to split this up into a number of blogs. Hopefully this will also give anyone who spots an error or bad assumption the chance to leap in before I plow on down the Path of Wrongness.

So in the next blog I'll set up the representative scenario, run a SQL trace on it, and carve out the most tender parts of the internals for our later consumption.

continued here ...

* This rule is based on a mixed bag of Democrat, Republican, and Anarcho-syndicalist technicians with varying number of children, wives, previous work commitments and deadlines. Your mileage may vary.

An Apology (no, not from me)

2005-09-06T15:37:00.000-06:00

Just dabbling over at the website of TriCare, and came across what I thought was an extraordinary statement.

The registration functionality will continue to be available until further notice. We will provide an update as soon as a new date is established for disabling the registration functionality. We apologize for any inconvenience this may cause.

Um ... they appear to be apologising for their continued inability to prevent online registration. Shurely shome mishtake?

What I'm Thinking About At The Moment

2005-09-06T14:34:00.000-06:00

... is how to improve the performance of multiple materialized view fast refreshes based on a single master table.

Hmmm.

One of the values of this blogging thing is that you start with a problem, and then develop a plan to tackle it a certain way, and then you snap your fingers and think, "Best blog this right away!". Then as you write your thoughts down you realise, "What if someone suggests .... or what if they ask ... best write something about that option as well then!"

Hence a brief note about your plan of action could turn into a near-total examination of the subject, some of which is not relevant to your situation anyway. In some cases you never see an end in sight -- in order to do justice to the topic (even one as narrowly focused as my current concern) in any rigorous sense you can see a twenty thousand word task ahead of you complete with scripts to justify every assumption and display every facet of the argument. This turns out to be so overwhelming that the blog collapses half-built under the pressure of actually having to do something in a hurry.

It's a hard life, eh? Well, I'm picking the bones out of my materialized view refresh issue before is gets turned into a problem, so hopefully I'll get to do justice to the topic.

Trivia for the day: the History Of The Ampersand

2005-09-01T14:07:00.000-06:00

http://www.artlebedev.com/mandership/112/

Partitions With And Without Rows

2005-08-31T06:30:00.000-06:00

Just a quick note on a SQL technique I stumbled on that I thought I'd share.

Having a partitioned fact table, with some partitions populated and others not, I have a series of materialized views that have not necessarily been entirely maintained. In this situation the materialized views are all partitioned with exactly the same scheme as the fact table but some of the partitions that have rows in the fact table do not have rows in the materialized views.

I needed a query to rapidly identify those cases so that the offending MV partitions could be maintained, and I came up with a requirement as follows: Identify all the partitions of the fact table that have at least one row, and for which there are no rows in the equivalent partition of a particular materialized view.

Because I had a usable naming convention for my partitions I was able to translate a partition name into a predicate very easily. for example given a partition name "Y2005M01" I can translate this into '01-jan-2005' using the simple method to_date(partition_name,'"Y"YYYY"M"MM'). You can try this yourself with:
select to_char(sysdate,'"Y"YYYY"M"MM') from dual;
...and then ...
select to_date(to_char(sysdate,'"Y"YYYY"M"MM'),'"Y"YYYY"M"MM') from dual;

So this allowed a query of the form:

Select partition_name
From user_tab_partitions utp
Where table_name = 'MY_FCT_TABLE' And
Exists
(Select /*+ norewrite */ 1
From MY_FCT_TABLE
Where date_of_month =
to_date(utp.partition_name,
'"Y"YYYY"M"MM')) And
Not Exists
(Select /*+ norewrite */ 1
From MY_FCT_TABLE_MV
Where date_of_month =
to_date(utp.partition_name,
'"Y"YYYY"M"MM'));

For a table having 175 partitions this identified the half-dozen or so problem partitions in around 3 seconds. Not too shabby, I thought.

An Easier Explanation

2005-08-30T09:57:00.000-06:00

The DBMS_XPLAN supplied package gives results that are so much more usable than the old "set autotrace traceonly explain", or any manual method of reading the plan_table, that the only thing holding me back from using it more often was the lengthy syntax.

No more: I have created views to save my aching fingers. Feel free to use them, modify them, whatever for your own purposes -- just don't blame me if they break something ;)

create or replace view xplan_basic
as
select * from table(dbms_xplan.display('plan_table',null,'BASIC'));*

create or replace view xplan_typical
as
select * from table(dbms_xplan.display('plan_table',null,'TYPICAL'));

create or replace view xplan_parallel
as
select * from table(dbms_xplan.display('plan_table',null,'ALL'));

create or replace view xplan_serial
as
select * from table(dbms_xplan.display('plan_table',null,'SERIAL'));

I am now a mere "select * from xplan_parallel" away from my execution plans.

* see comment from Pete ... I missed the "BASIC" parameter value in the original post.

Partition Pruning and Dimension Tables

2005-08-30T07:00:00.000-06:00

As a side note to a previous blog, Peter Scott kindly humoured me by checking on a partition pruning scenario that I had been unable to simulate. Specifically, the scenario is of partition pruning in a fact table based on a predicate that references a higher dimensional level through a joined dimension table.

Well, I found the metalink note that describes this method of optimization, and it's number 209070.1, with the generously proportioned title: "Partition Pruning based on Joins to Partitioning Criteria Stored in Dimension Tables". In contrast to the Most Useless Metalink Article Ever, this one is actually extremely useful, and aside from skipping details of a couple of hidden parameters it is very detailed in its description of the internal mechanism by which the optimizer implements "subquery pruning". Maybe I'll name it "Most Useful Metalink Article Ever". Nah, maybe not.

What also aroused my interest was the article's mention of the TBL$OR$IDX$PART$NUM() function, which I'd previously only encountered in trace files for partition exchanges and an article by Jonathan Lewis. I guess that this is about as documented as this function gets.

Another feature, interesting by it's omission, is that foreign keys do not get a mention either in this article or the associated 179518.1: "Partition Pruning and Joins". I'll have to see if my tests require a foreign key or not -- Peter's own test used foreign key constraints in the disabled/rely state, and I'm guessing that they're not considered.

Also worthy of note are the conditions under which subquery pruning will be invoked: "The cost of the recursive subquery must not exceed 5% of the cost of accessing all data in the partitioned fact table and the predicates on the dimension table must select less than 50% of the data in the dimension table." The former appears to be controlled by the hidden parameter _subquery_pruning_cost_factor and the latter by _subquery_pruning_reduction, which have default values of 20 (1/0.05 of course) and 50 respectively on my 10.1.0.3.0 database. Rather circumstantial evidence, but supported by the note's advice:
"As long as there is a valid join on the partition key then pruning can be forced by setting :
_subquery_pruning_cost_factor =1
and
_subquery_pruning_reduction =100.
This effectively overrides the default choice made by the optimizer."

The recursive subquery referred to above appears to be the one by which the required partitions are identified: that involving the TBL$OR$IDX$PART$NUM() function. Since this is a query against a dimension table it is difficult toimagine that it could reach 5% of the total query cost under real world conditions, but it's another issue to bear in mind. I speculatively wonder whether it would be possible to create a materialized view to help return this result faster, or whether the nature of the function or the use of recursive SQL would prohibit it's creation or use? Another thing to think about later.

Anyway, a little more work could nail these issues down ... alas, tempus fugit etc.

Finally there is another hidden parameter (revealed through the use of JL's script here) to consider: _subquery_pruning_mv_enabled, with a default value of "false" and a description of "enable the use of subquery predicates with MVs to perform pruning". This seems to me to be a critical parameter. Why it is set to false by default is not immediately clear to me but I'll be keeping this parameter in mind as it appears that it could relate to query rewrite which is an important part of the database design I am working on. Worse case scenario: raising an iTar to get the thumbs up from Oracle Support to make a change to a hidden parameter. Not a bad thing in itself, as they generally like you to jump through a few hoops in justifying such a course of action.

Well, it's been an interesting and educational week so far, that's for sure.

For another resource on this issue see this thread at AskTom.

Doings With Dimensions

2005-08-26T10:43:00.000-06:00

An interesting blog over at Peter Scott's place -- BYOB.

Is there anyone out there not on vacation or travel?

2005-08-26T10:05:00.000-06:00

So, Howard Rogers is in New Zealand, Tom Kyte is in Iceland (and heading to the rest of Europe), Jonathan Lewis was in Norway yesterday and is in Lithuania next week, and everyone from Burleson Consulting is in an undisclosed location -- I don't know where it is but it sure ain't North Carolina.

I get two nights with the family in a travel trailer up in Buena Vista (pronounced "Byoona Vista" by the locals, it seems). Oh, I'll be in Dayton, Ohio for the Air Force Marathon next month (no, not me -- the wife is running the half-marathon).

What exactly am I doing wrong?

The Linux Experience: What's Missing

2005-08-26T09:23:00.000-06:00

It's been a week or two since I dedicated my laptop entirely to Whitebox Linux, and so far I have few complaints.

There are some applications that I rely on that are either not available on Linux, or for which I don't have a spare $100,000 for the license. In the former category is UltraEdit. It's one of those applications that seem pretty simple to start with, and then you find one key feature that you can't do without. For me that feature is column-mode editing.

Long-story-short, column-mode editing allows you to select, copy and paste a rectangular block of text. It also allows you to select a single column, start typing, and have whatever you type echoed on every line selected.

So for example if you want to create a view over a table with fifty columns and you want to wrap every column in an expression such as:
Decode(col1,' ','N/A',col1) col1,
...then you;

DESC the table
paste the result into UltraEdit
use column mode to select and copy the rectangle that holds the column names
paste the result over to the right, twice

... and this gives you your list of column names three times, looking something like;

col1 col1 col1
col2 col2 col2
col3 col2 col3

You then use column mode to select the gap to the left of the leftmost column of column names (nothing confusing about this example!) and type "Decode(". This gives you:

Decode(col1 col1 col1
Decode(col2 col2 col2
Decode(col3 col3 col3

Then you select the gap to the left of the second column of column names, and type ",' ','N/A'," giving you:

Decode(col1,' ','N/A',col1 col1
Decode(col2,' ','N/A',col2 col2
Decode(col3,' ','N/A',col3 col3

Well, you see how this works anyway. Once you get into the swing of things this is very fast and pretty soon you have:

Decode(col1,' ','N/A',col1) col1,
Decode(col2,' ','N/A',col2) col2,
Decode(col3,' ','N/A',col3) col3,

Now you could do the same thing by querying user_tab_columns, but with a complex or lengthy expression I much prefer this way. For example in:

Decode(LTrim(col1,'0'),'}',0,'A',1,'B',2,zoned_to_number(col1)) col1,

... I just can't keep track of all of those single quotes that have to be doubled-up.

It also will search and replace on special characters such as carriage returns very easily, so you can remove occurances of blank lines by replacing "^p^p" with "^p" until they're all gone for example, or get a line break after each comma by replacing commas with ",^p".

Not ... available ... on ... Linux.

Ouch.

Still, if you're on Windows then I recommend it. For $40 I'd give a copy to every DBA and developer (only if they're on Windows, of course. It'd be a bit of a waste otherwise.)

Word Verification for Comments

2005-08-23T06:57:00.000-06:00

Following a tip by Gabe over at TK's blog, I've turned on word verification for comments to stop the spambots.

I don't know, I'll kind of miss them in a way ...

Execution Plans for "Partition Not-Quite-Pruning"

2005-08-22T10:48:00.000-06:00

This is a followup to this blog, which itself was a followup to this one.

Table Structure
The table here is called FCT_FSA_MSD_GSD and is composite partitioned on file_load_month and fund_cd. There are about 60 partitions on file_load_month and three subpartitions per partition, of which one holds only fund_cd values of '6C'. A fiscal month is not directly represented in the table, and is defined with a range of values for the date_of_day column.

Indexes
xie06fct_fsa_msd_gsd: single column bitmap index on FILE_LOAD_MONTH
xie15fct_fsa_msd_gsd: single column bitmap index on DATE_OF_DAY
xie28fct_fsa_msd_gsd: single column bitmap index on FUND_CD

Explain Plans
I've edited the plans just to take out the parallelism references that were mucking up the format, and joined lines together where they broke.

Here's the old form of the query.

SQL> select count(*), sum(fiscal_amt) from fct_fsa_msd_gsd
2  where date_of_day between '01-jan-2004' and '31-jan-2004' and
3  fund_cd = '6C'
4  /

COUNT(*) SUM(FISCAL_AMT)
---------- ---------------
6469727      3521311527

Elapsed: 00:00:41.01

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=22964 Card=1 Bytes=16)
1    0   SORT (AGGREGATE)
2    1     SORT* (AGGREGATE)
3    2       PARTITION RANGE* (ALL)
4    3         TABLE ACCESS* (BY LOCAL INDEX ROWID) OF 'FCT_FSA_MSD_GSD' (Cost=22964 Card=940684 Bytes=15050944)
5    4           BITMAP CONVERSION* (TO ROWIDS)
6    5             BITMAP AND*
7    6               BITMAP MERGE*
8    7                 BITMAP INDEX* (RANGE SCAN) OF 'XIE15FCT_FSA_MSD_GSD'
9    6               BITMAP INDEX* (SINGLE VALUE) OF 'XIE28FCT_FSA_MSD_GSD'


Statistics
----------------------------------------------------------
     20  recursive calls
      3  db block gets
  36489  consistent gets
  36360  physical reads
   1016  redo size
    258  bytes sent via SQL*Net to client
    274  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
     70  sorts (memory)
      0  sorts (disk)

      1  rows processed

Commentary on above: A bitmap merge combines
the fund code and date of day predicates,
then every partition of the table is accessed by
rowid.

Here's the first part of the improved query

SQL> select count(*), sum(fiscal_amt) from fct_fsa_msd_gsd
2  where date_of_day between '01-jan-2004' and '31-jan-2004' and
3  fund_cd = '6C' and
4  file_load_month = '01-jan-2004'
5  /

COUNT(*) SUM(FISCAL_AMT)
---------- ---------------
6469717      3521307863

Elapsed: 00:00:12.02

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=197 Card=1 Bytes=25)
1    0   SORT (AGGREGATE)
2    1     SORT* (AGGREGATE)
3    2       TABLE ACCESS* (FULL) OF 'FCT_FSA_MSD_GSD' (Cost=197 Card=3375101 Bytes=84377525)

Statistics
----------------------------------------------------------
    149  recursive calls
      3  db block gets
  37043  consistent gets
  36651  physical reads
    936  redo size
    244  bytes sent via SQL*Net to client
    275  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      3  sorts (memory)
      0  sorts (disk)
      1  rows processed

Commentary on above: This is 9.2.0.6 and the explain plan is not
showing that partition pruning has taken place.
However from the consistent gets and physical reads is evidently has.
Looks like the subpartition stats are off as well. Tut.


Here's the second part of the improved query

SQL> select count(*),sum(fiscal_amt) from fct_fsa_msd_gsd
2  where date_of_day between '01-jan-2004' and '31-jan-2004' and
3  fund_cd = '6C' and
4  file_load_month != '01-jan-2004'
5  /

COUNT(*) SUM(FISCAL_AMT)
---------- ---------------
    10          3663.2

Elapsed: 00:00:04.00

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=22432 Card=1 Bytes=24)
1    0   SORT (AGGREGATE)
2    1     SORT* (AGGREGATE)
3    2       PARTITION RANGE* (ALL)
4    3         TABLE ACCESS* (BY LOCAL INDEX ROWID) OF 'FCT_FSA_MSD_GSD' (Cost=22432 Card=919682 Bytes=22072368)
5    4           BITMAP CONVERSION* (TO ROWIDS)
6    5             BITMAP AND*
7    6               BITMAP MINUS*
8    7                 BITMAP MERGE*
9    8                   BITMAP INDEX* (RANGE SCAN) OF 'XIE15FCT_FSA_MSD_GSD'
10    7                 BITMAP INDEX* (SINGLE VALUE) OF 'XIE06FCT_FSA_MSD_GSD'
11    6               BITMAP INDEX* (SINGLE VALUE) OF 'XIE28FCT_FSA_MSD_GSD'

Statistics
----------------------------------------------------------
     20  recursive calls
      3  db block gets
    725  consistent gets
    591  physical reads
   1008  redo size
    253  bytes sent via SQL*Net to client
    275  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
     70  sorts (memory)
      0  sorts (disk)
      1  rows processed

Commentary on above. Bitmap indexes on file_load_month is minus'd
from that on date_of_day, then the result is merged with that on
fund cd, and the entire table is accessed by rowid.


Here's the combined parts of the improved query

SQL> select sum(rc),sum(fiscal_amt) from
2  (
3  select count(*) rc, sum(fiscal_amt) fiscal_amt from fct_fsa_msd_gsd
4  where date_of_day between '01-jan-2004' and '31-jan-2004' and
5  fund_cd = '6C' and
6  file_load_month = '01-jan-2004'
7  union all
8  select count(*),sum(fiscal_amt) from fct_fsa_msd_gsd
9  where date_of_day between '01-jan-2004' and '31-jan-2004' and
10  fund_cd = '6C' and
11  file_load_month != '01-jan-2004'
12  )
13  /

SUM(RC) SUM(FISCAL_AMT)
---------- ---------------
6469727      3521311527

Elapsed: 00:00:37.03

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=22629 Card=1 Bytes=26)
1    0   SORT (AGGREGATE)
2    1     VIEW (Cost=22629 Card=2 Bytes=52)
3    2       UNION-ALL
4    3         SORT (AGGREGATE)
5    4           SORT* (AGGREGATE)
6    5             TABLE ACCESS* (FULL) OF 'FCT_FSA_MSD_GSD' (Cost=197 Card=3375101 Bytes=84377525)
7    3         SORT (AGGREGATE)
8    7           SORT* (AGGREGATE)
9    8             PARTITION RANGE* (ALL)
10    9               TABLE ACCESS* (BY LOCAL INDEX ROWID) OF 'FCT_FSA_MSD_GSD' (Cost=22432 Card=919682 Bytes=22072368)
11   10                 BITMAP CONVERSION* (TO ROWIDS)
12   11                   BITMAP AND*
13   12                     BITMAP MINUS*
14   13                       BITMAP MERGE*
15   14                         BITMAP INDEX* (RANGE SCAN) OF 'XIE15FCT_FSA_MSD_GSD'
16   13                       BITMAP INDEX* (SINGLE VALUE) OF 'XIE06FCT_FSA_MSD_GSD'
17   12                     BITMAP INDEX* (SINGLE VALUE) OF 'XIE28FCT_FSA_MSD_GSD'

Commentary on above: Here we go, all working nicely together. the timing
for this combined query is high, but this is a working production
machine with a bunch of other stuff going on.

Other Comments
My guesstimate on the row proportions was off. In fact there were 6.5 million rows involved in the result set, and only 10 of them were not in the Jan 2005 partition (so that's 0.000154%).

More on Partition Not-Quite-Pruning

2005-08-21T05:17:00.000-06:00

(Updated below)

Here's a follow up to the previous post on this subject, which ought to be read first. This is just to give more background on why I think this technique works.

We have a scenario where we want to read all of the rows where fiscal_month is Jan-2005 (around one million rows) from a fact table of sixty partitions (based on file_load_month). 99.5% of these rows (995,000 rows) are contained in a single partition (file_load_month=Jan-2005) which itself holds around one million rows. The other 5,000 rows are scattered across other partitions of the table. In fact they will probably be found in around ten-twenty of the remaining fifty-nine partitions.

If you just query for rows where "fiscal_month = Jan-2005" then no partition pruning is possible (remember that the table is partitioned on file_load_month) and therefore the optimizer has two choices on how to execute this

Access by index -- find several million rows by reading a bitmap index on fiscal_month and then reading the relevant blocks through single block io.
Access by full table scan -- read all sixty partitions of the table in multiblock io.

Neither of these are satisfactory, so a different query was employed where two queries were UNION ALL'd together.

Selects all of the required rows from the file_load_month Jan-2005 partition
Select all of the required rows from all of the other partitions.

The first query uses partition pruning to read 995,000 rows of the one million rows in the file_load_month Jan-2005 partition. Multiblock io is used.

The second query accesses the table through a BITMAP MINUS operation on two bitmap indexes (on fiscal_month minus file_load_month) to read around 5,000 rows that are probably scattered throughout the remaining fifty-nine partitions using single block io.

Now if you consider how many blocks get read for the old technique and the new technique, then the two methods are practically the same. In fact the new method probably reads slightly more blocks than the old method. The difference between them comes from the ability of the new methodology to read nearly all of the required table blocks in multiblock mode.

It is interesting to note that the optimizer actually has all of the information that it needs in order to perform this optimization internally. Partition-level histograms on the fiscal_month show that nearly all of the required rows are contained in a single partition, and almost none of them are contained in any other.

I'm going to get some explain plans extracted that are more instructive than those I currently have, and I'll get back to you with them.

Update

Jonathan Lewis has written to point out the similarity between this approach and the practically-defunct feature of "Partition Views" (PV), in which similar tables are UNION ALL'ed together in a view that can be queried to produce an effect similar to pruning in a partitioned table (PT).

He also points out one of the strengths of PV -- that the optimizer could not only decide not to touch a partition (a form of optimization we know as "pruning" of course) but could also generate a different form of execution plan for each partition that the query would touch. This is a benefit of PV that was lost with PT (except for the "do not touch this partition" pruning optimization) and which is resurrected in the technique I'm describing here. Exactly the same principle applies, of breaking up a result set into multiple subsets with UNION ALL to merge them together.

I'm inclined to wonder whether in this particular example an approach of defining a partition view over a partitioned table, in the form of ...

Select fiscal_month, file_load_month, ...
from my_table
where fiscal_month = file_load_month
Union All
Select fiscal_month, file_load_month, ...
from my_table
where fiscal_month != file_load_month

... would be productive. I wonder whether the optimizer transitivity would be robust enough to handle this situation.

Jonathan's article comparing PV and PT is in Microsoft Word format here -- for the MS-averse Google has a passable HTML conversion here.

Partition Not-Quite-Pruning

2005-08-20T09:21:00.000-06:00

I'm in the middle of a migration of a fact table from one structure to another. The key difference is in the partitioning key -- the old structure was partitioned according to the month in which the data arrived, wheras the new structure is partitioned according to the fiscal month of the transaction. It's a very slight but very important difference, because it is the fiscal month that the users will query by, hence partition pruning is very much more likely to occur in the new structure.

The data is being migrated one new-table partition at a time, to allow MV maintenance-without-tears. Now a partition of the new table sources data almost entirely from one partition of the old table, but not quite. Probably around 99.5% of the new partition's data will come from a single partition of the old table (and will represent about 99.5% of that old partition's data). The other 0.5% can come from pretty much any other partition of the old table.

So, here's the problem -- does the optimizer scan the entire old table in lovely efficient multiblock reads (direct, reads, because of the query parallelism), or does it use single block reads and the bitmap index on fiscal_month? Either one of these is unsatisfactory, so here's the solution I came up with.

Instead of the straightforward statement:

Select *
From old_table
Where fiscal_month = '01-jan-2005'

... we instead can use:

Select *
From old_table
Where fiscal_month = '01-jan-2005' and
file_load_month = '01-jan-2005'
Union All
Select *
From old_table
Where fiscal_month = '01-jan-2005' and
file_load_month != '01-jan-2005'

The first result set selects the 99.5% of the required rows that represent 99.5% of the old tables file_load_month-based partition for '01-jan-2004', and it does this with an efficient (multiblock) full scan of a single partition.

The second result set selects the 0.5% of the required rows that are in any partition other than that for file_load_month '01-jan-2004', and it does this with single block reads through the combination of the bitmap indexes on fiscal_month and file_load_month.

So in this case we got the best of both worlds. The query time dropped from around 56 seconds to 17 seconds.

Now, what should we call this? "Partition Not-Quite-Pruning"? "Partition Semipruning?" ... ?

Continued here ...

A List: Moving Data Between Databases

2005-08-18T04:30:00.000-06:00

A simple one, this. But there's a lot of ways out there.

Spool to Flat File, Load with SQL*Loader or External Tables: SQL*Plus does a reasonable job of unloading. Tom Kyte has a C utility for unloading data also.
SQL*Plus COPY command: Sadly deprecated, and without support for all data types, but a handy tool for basic data transfers. Allows arbitrary SQL on the data selection.
Database Links: Direct movement from one database to another. Aside from the network latency this is basically the same as reading data from your local database. If you combine database links and materialized views, then you can replicate remote data to your local database through the databae link.
DBMS_PIPE: I don't recall reading of anyone using this, but I expect that it's a theoretical possibility to send data from one instance to another through a pipe. scratch this one: see TK's comment
Export/Import: Sometimes mistaken for part of a backup and recovery strategy!
Transportable Tablespaces: Closely related to Export/Import, allowing movement of entire tablespaces without the need for unloading and reloading data.
Data Pump: Export/Import for the new millenium :) and with many new features.
Oracle Streams: Powerful functionality here. Non-trivial, but probably the modern tool of choice for the regular propagation of data around your corporate network.

Finally, here is an introduction to the sharing of information between database systems.

Physics Review

2005-08-17T07:27:00.000-06:00

Not an Oracle topic, but here is a news story of interest to the scientifically inclined reader. Science is truly on the defense.

http://www.theonion.com/news/index.php?issue=4133&n=2

A List: Things That Materialized Views Can Be (Philisophically) Similar To

2005-08-15T17:00:00.000-06:00

When is a materialized view like a ... ?:

Constraint: When you use it to enforce a multirow constraint, such as "SUM(allocation_pct) per Site = 100", which is not supported through regular constraints. Better then triggers because MV's refresh on commit, not on DDLoperations themselves.
Index: When you use it to speed a full scan of a subset of a table's columns .
Partition: When a materialized view selects a subset of a table's rows, thus making the subset accessible through multiblock reads instead of single block index-based access, and without requiring the whole of the original table to be scanned.
New Set Of Unbelievably Fast Disks: When a materialized view with query rewrite allows a large data set to be pre-aggregated and subsequently queried in almost no time at all.
Trigger: When you stop trying to enforce multirow constraints with triggers and start using materialized views instead.
Synonym: When it allows you to give an alternative name to a table.
Time Machine: When a stale materialized view shows you the previous state of a table's data. A materliazed view log then gives you an audit trail of sorts for changes since the last refresh*

1, 3 and 4 seem to be the most interesting ones there. I don't think I'd use 2, unless it was on a single column with nulls, and a bitmap index was not appropriate, and the query had to consider nulls. 6 seems like a real stretch.

* Thanks Gary

Training and Demonstrations

2005-08-15T00:00:00.000-06:00

A few years ago some collegues of mine introduced themselves to a Vice-President of Sales for a Well Known Business Intelligence corporation at a local trade fare, and explained that they were familiar with his product thorugh a "cook-off" that our group had held between his product and that of a major competitior. "Ah ha", cries he, "is that British bastard here as well then?"

Heh heh heh. It had been an interesting exercise all right. It had culminated in a demonstration to users of the two product suites, with my own demonstration of Product A facing off against the corporation's pre-sales consultant's demonstration of Product B, followed by a round of discussion and voting by the users and project managers. Product A swept the board and was duly annointed the successor to our then-current tool. Apparantly there then followed much gnashing and grinding of teeth at the WKBI corporation, and a belief that the competition had been unfairly skewed in some way.

So why the overwhelming vote for Product A? The users believed that the product was more intuitive, easier to use, with a more simple interface. They believed that it would just be easier to get their jobs done with that choice. Whether they were right or not, I'm not sure. The difference between theusability of the two products is probably pretty slight, but the difference between the demonstations was very clear and is very well illustrated by the article at ComputerWorld that prompted today's stream of consciousness. I'll just pause while you absorb it.

...

The two demonstrations were almost exactly like those example methodologies. While the pre-sales consultant for Product B described every feature available to the users, your humble correspondant showed them how to login to Product A, how to run one of the standard reports, how to create and modify a personal report (once at demonstrate-and-explain-every-mouse-click speed, then at normal user speed), and how to send it to other users. It was a very *ahem* dynamic presentation with a great deal of audience interaction. I suppose that the essence of the matter was that a sales demonstration was turned into a training session of the type described in the article.

So the end result was that the audience saw that Product A did what they want, but that Product B did a lot of stuff that they didn't fully comprehend. From there the voting was practically a formality.

I wonder whether the Well Known Business Intelligence corporation's consultant ever realised why the bid was lost to them? The VP of Sales certainly had a strong opinions on the matter. The big baby.

How Not To Ask A Question

2005-08-11T15:02:00.000-06:00

Do you think it's possible that this questioner is being just a little broad in the advice he/she is requesting? Maybe just a shade optimistic?

Lesson from the Shark Tank

2005-08-11T13:28:00.000-06:00

Here are two learning experiences in one -- how databases get corrupted, and how consultants get ... um ... corrupted as well.

Waiting For A Delivery

2005-08-11T08:23:00.000-06:00

If there's anything more exciting than waiting for the delivery of a shiny new piece of technology, then I'd like to know what it is.

Today I'm waiting for a replacement hard drive for my two year old laptop, which I'm going to store in the basement as a little database and HTML DB server, on my "shelf of technology" (cable modem, wireless router, NSLU2 and attached hard drive). The new drive is a speedy little Hitachi Travelstar 7K60, which seems to be about the fastest I can get. A bit tricky to come by, but I found one here.

So I'm really changing the drive because if it all goes pear-shaped and I want to get my old WinXP/Suse9.1 dual-boot laptop back then I can just swap the disks over again. That, and it's a shiny new thing that I get delivered, and sometimes you just have to find excitement where you can.

A List: Ways to Scan a Table Faster

2005-08-10T08:00:00.000-06:00

So you need to read an entire table? Here's some features and techniques that may help.

Employ Parallelism: Ah, the sweetest technique of all. Brute force. If you have the resources, then let Oracle divide the table into chunks and use all your system resources to scan it. Unfriendly in an OLTP environment, mind you.
Compress Data Segments: Make the table smaller! Block level compression can shrink that data like a plunge into cold water. Very OLTP unfriendly.
Reduce Free Space Percent: PCTFREE = very small => more rows per block => smaller table. And potentially a higher chance of row migration, of course.
Increase Percent Used: PCTUSED = large => less likely that blocks retain free space following deletes => more rows per block => smaller table.
Use a Larger Block Size: For significantly long rows you may get reduced empty space, thus a smaller table to scan.
Reorder The Columns: If you are commonly interested in just a subset of columns (for example metrics in a fact table) then consider making them the first columns of the table definition - for tables with lots of columns there is measurable overhead in finding the end columns (I'm not talking about row chaining here). Hmmm, maybe I'll post something else about this.
Index Columns of Interest: An index can be treated as a skinny table, and your query might be satisfied by a fast ful or full index scan. The usual comments about NULL values in indexes apply here. Don't neglect consideraton of index size either - index key compression and use of bitmap indexes provide smaller structures to scan.
Materialized Views: Is there anything they're not good for? This could be a genuine skinny table, or an aggregation of selected columns.
Ensure Table Is Spread Over Available Devices: With consequent reduced likelihood of encountering an i/o choke point.

There is not a single feature listed here that carries with it no disadvantages, and depending on your circumstances the severity of the disadvantages may range from the insignificant to the devastating.

Do not implement any of these without understanding how the feature works, the mechanism by which it provides the advantage, how it brings disadvantages, and how these all interact in your particular situation.

Handy Hardware, Or Not ...

2005-08-09T11:03:00.000-06:00

Here's an AnandTech review that caught my eye -- 4Gb of memory with a battery backup. Could this be an economical way of getting performance for non-sequential storage access?

I wasn't particularly impressed with the way that the performance was reported, particularly for the random access test, because no indication was given on whether the device was the bottleneck on the test or not -- hey, maybe the CPU's were maxed out? It's a mystery. There's an indication here that random reads and writes would really benefit.

So what do you think? I'm no shill for solid-state drives, but for a small-but-busy server, would you put your redo logs on this? Temp space? How about the system tablespace? ;)

A List: SQL Features You've Probably Never Used In Production

2005-08-08T23:59:00.000-06:00

By popular demand (one anonymous person -- hey, it's all relative), another list.

OK, you may have played with them, found a hypothetical circumstance for them, but you've probably never found a real situation in which to use them, or if you have you've probably backed out at the last minute. Or the DBA won't let you use them. Or you found there was a bug that spoiled it.

More likely you will not even recognize them -- unless you're one of those losers who enjoys browsing documentation of course. * ahem *

The Subquery Factoring Clause: OK, a bit of a softball to start with. I bet that someone has used this. Not first time, but as part of a tuning exercise. And to show off.
The Partitioning Clause of a Query Table Expression: Actually hardcoding a partition name in a query? Wellll, OK maybe.
The Model Clause: This is great! What's it for?
Multiset Operators: Or nested tables in general
Submultiset: OK, that's cheating. Let's just say "nested tables"
The CORR_K() Function: Oh wait, there was that time when ... nah not really. Never used it.
PowerMultiset_By_Cardinality: "...takes as input a nested table and a cardinality and returns a nested table of nested tables containing all nonempty subsets (called submultisets) of the nested table of the specified cardinality". * wipes-eyes * OK seriously, that's the last time I'm going to mention nested tables. Or nested tables of nested tables.
Alter Sequence: Admit it ... you thought you were going to be able to change the current value with it, but then found you just had to drop the sequence and recreate it, so that's what you did.
Associate/Dissociate Statistics: What?

Well, I pride myself on the high education level of my readership, and also it's high regard for risk-taking. If you have actually used one of these features in a production system, and it has actually made it past the DBA/watchdog, then please reply under the heading "I have an unusual affinity for Shiny New Things, and have successfully used the following features in a production system:"

A List: Accessing Part Of A Table Quickly

2005-08-08T11:57:00.000-06:00

I like lists. In particular, I like to make little lists of Oracle features that have some commonality in the way they work, or in what they do.

Today's list is Oracle Features For Accessing Part Of A Table Quickly. Quicker than scanning the whole table anyway, and assuming that the table is not of trivial size. Here's what I have so far:

Creating an Index: obviously.
Making the Table Index-Organized: Is that too similar to Option 1 to justify a new heading, do you think?
Placing The Table In A Cluster: Hash or indexed.
Partitioning The Table: Partition pruning being the key feature here, or through using the PARTITION or SUBPARTITION clauses of the query table expression.
Creating a Materialized View: By precomputing the result set for a query that requires only part of the full table. Query rewrite optional, I suppose.

Off the top of my head, that's all I can think of right now. Other suggestions welcome.

Trouble Down Under

2005-08-08T09:38:00.000-06:00

I see that DKB and HJR are having at it again.

DKB posted some inflammatory comments here, and HJR posted responses here, here, and here.

So apparantly DKB's secret location from which he is broadcasting is Australia. I'm sure he knows his own business, but it seems to me that his blog entry is very derogatory towards the people paying the bill for his consultancy ... "Offshoring (the use of grossly under-trained and unskilled IT professionals)" ... "if you are an IT manager stupid enough to entrust a mission-critical computer system to a foreign “offshore” developer firm".

I think that it's nice that Burleson Consulting now has a legal presence in Australia .. DKB and HJR can get on with their legal business without bothering the rest of us. On a side note, while OraBlogs is getting notices that they are rendering copyrighted images, Don's own forum makes extensive use of copyrighted images. Like I say though, doubtless he knows his own business ...

Daylight Savings Time

2005-08-08T07:00:00.000-06:00

Update

Looks like the journos have figured out the downside ... http://www.cnn.com/2005/TECH/08/08/daylight.saving.ap/index.html

The good news though: "Missiles won't be launching". I don't know how they expect the unscrupulous to make a profit out of something if reputable news media aren't going to scream hysterically about it for months in advance, in the style of The Great Y2K Fiasco. Ah, how fondly I recall the sweet satisfaction of waking up on January 1st 2000, having taken not a single precaution against the promised apocalypse, and finding everything working completely normally.

Anyway, this is a serious business in its own way: I recall being asked a few years ago to opine on a bid for a state system that was being converted to Oracle. The system defined funding for various state entities, and virtually the entire business logic was in effect defined by the state legislature. As such the funding model was vulnerable to change at any time, even retrospectively, and the only limit to the scope of change was the imagination of the state representatives and their advisors. For a fixed price contract and with existing staff already reeling from a series of recent changes it would have been a risk management nightmare. "Thumbs down" for that one.

Original Article

Heads-up people!

CNN are reporting on imminent changes to daylight savings time.

"House and Senate negotiators on an energy bill agreed to begin daylight-saving time three weeks earlier, on the second Sunday in March, and extend it by one week to the first Sunday in November.
...
Lawmakers said they hoped to complete the energy legislation next week."

I wonder how much bespoke code this is going to affect? I'm supposing that there will be some Oracle patches to deal with the required changes to TIMESTAMP WITH TIMEZONE, etc.

Here's a search on the 10g documentation for "daylight savings time", by the way.