Index statistics on complete refreshed materialized views. What is going on?

A few days ago I discovered some (to my feeling) buggy behavior during a COMPLETE refresh of a -partitioned- materialized view. It was an unfortunate situation, because it was discovered the day after going into production. It was not noticed during the weeks of development and pre-production execution. Seems like we have some improvement to do there. It had to do with the index statistics on GLOBAL indexes of a partitioned materialized view. The first time our ETL ran with some new materialized views implemented on them, the ETL did its job and COMPLETE refreshed a  new MV. So far, so good, that was the intention. However, countries started complaining about huge performance problems with some specific reports and another ETL part (only run on Production, which is one of those things I did not know and we need to improve a next time we test a new implementation) was taking hours and hours where it normally would take a few minutes.

Oracle 11 upgrade challenges

PLS-00593: default value of parameter “x” must match that of spec

At one customer, we are upgrading from Oracle 8.1.7.4 to Oracle 11g. It was about time. So far, there are not many challenges. The few problems so far have to do with migrating from a Single Byte character set to a Multibyte character set (on purpose) and some PL/SQL mismatches. Today I was looking into some package bodies that did not compile in 11g. The error was a PLS-00593: default value of parameter “x” must match that of spec.  So we seemed to have a few packages with one or more functions in them in which the parameter default values did not match. So I had to straighten that and I wondered: what is the one it must be if I do not want the risk of changing the application logic that may rely on the default values. The one in the specification, or the one in the body?

Eliminating workload from the system using materialized views

The last months I have been looking into a customer’s ETL process. They have a Siebel OLTP and Analytics (now OBI 10) system (Data warehouse) and every night an ETL process runs in order to maintain the Data warehouse. When I arrived at this customer, the ETL was not a big problem: it ran within the window although at first sight it did look like there were some inefficiencies. But since it was not the highest priority performance problem, I did not watch it very closely. In a Siebel environment, there are always several performance issues to look at, so I got busy enough. However, at one time the volume of the data in this fast growing system became a problem for the ETL: with more frequency, the ETL ran into the morning.

I had a great two weeks listening to Cary Millsap, Jeff Holt and Tom Kyte

The past two weeks were nice! I had the opportunity to attend two classes presented by three of the most experienced and talented Oracle specialists in the world. January 19-21 it started with “The Masterclass 2009”, organized by Miracle Benelux and hosting Cary Millsap and Jeff Holt. This was a unique opportunity. I believe it was years and years ago that they visited Europe and they never did a presentation before in The Netherlands.

One of the main reasons for this Masterclass was to honour Lex de Haan.

A week later, on January 25-26, Tom Kyte came to Oracle The Netherlands in De Meern. I think nearly 100 people, mostly Dutch but at least some people from abroad (of which a few familiar ones from the week before) could listen to a lot of information on Oracle 11, storage techniques, rebuilds and good comments on binding.

Take advantage of a Function based index in First_rows mode with an ORDER BY on NULLable columns

One of the things I encounter a lot in a Siebel application I support, is the fact that there are lots of queries with ORDER BY’s that take too long to return the first screen full of records OR are executed just to open a Business Component without any filtering. A typical OLTP application mostly wants the first screen full of records to return as fast as possible, so a FIRST_ROWS optimization is the default way. In Siebel, this FIRST_ROWS optimization mode is hard-coded in the DLL, so no way to even circumvent it.

However especially with the default ORDER BY defined on business components (Sort specification) or the ability for users to define their own ORDER BY, you run into situations where this ORDER BY makes the first records to return too slowly, since the whole result set will mostly be materialized, sorted and then the first rows can be returned, but…

Hello everyone

This is another blog on Oracle. For some time I wanted an easy way of sharing my knowledge, experience and findings with everyone interested. I write articles sometimes (in Dutch Oracle magazines), but those have a somewhat large interval. A blog enables me to post potentially interesting things immediately and get feedback as well. I can be right, I can be wrong and in either case I would love to know. I hope some or many people will enjoy it!