Funny thing I had today. Well funny, not when you want to make progress in your work, but still interesting what I discovered when I tried to isolate the problem…I found a way to make the same mistake and have Oracle return three different errors, dependent on the order of columns in an Insert statement. I cannot explain why, so any feedback I am very willing to receive. Continue reading
I never had so much trouble installing an Oracle version on a Windows OS as I had with installation of Oracle 11R2 on Windows 7 (64-bit). The issues I faced were twofold:
- First I got messages (during installation of the software) that files could not be found in the target oracle folder
- Second, after finding the solution for the first annoyance, I spent two hours finding out why everything installed correctly, I could create a database, but could not start a Listener. I am an Administrator on my laptop. The errors I got at starting a listener were ORA-12560 and ORA-12541.
I regret not being able to write new things more often, but in fact it only means I am OK: very busy with work, doing private stuff (which is important since life is not all about working), enjoying the three young kids and trying to achieve some good results in track and field. And for that last goal, I can say I did. After a few months of struggling to find a tiny piece of shape comparable to last years shape, just in time I found it. I won the gold medal in the Dutch Championships 400 metres hurdles. OK, be honest, for the ‘older’ men (40+). Still, my time was certainly not bad and about 2 seconds ahead of all other competitors of 35 years and older. And it brought me back in the top 30 of all Dutch 400 metres hurdles runners (Juniors and Seniors) in 2010. Nice. Just as nice was that I met Alex Nuijten (Amis) who volunteered there and even wrote about it on this nice blog. Thanks Alex. Continue reading
At last a new post from my hand after months of silence. Well, silence in writing then: in the mean time we got our third child and I participated in the World Championships Double Decathlon in Delft. Those two things were enough for me to handle next to regular work 😉
In this post I want to share good experience I had in the use of DBMS_REDEFINITION to speed up the Siebel OLTP application I am responsible for (at least, for the performance part of it). It is about clustering of data and rebuilding tables as ONLINE as can be and how I used a trick with a stored outline to overcome a performance problem during rebuild of one table. Continue reading
Recently I was sent a data warehouse SQL query that performed badly. It took more than an hour and timed out. It was the first time that query was run with a particular parameter. Here I will show that to solve this, I created an index to give the optimizer better information, even though it did not actually use it to execute the query. Continue reading
Today was the first day of the second Miracle Masterclass this year: Tanel Põder has come to The Netherlands, saw a room filled with a lot of people (he called it the best seminar even before he started, just because of the amount of attendants) and made me feel I have an awful lot to explore yet. However, that is in fact a good thing and it made me get through the “trains nightmare” today with a smile: I had trouble in the morning since I left home far too late, had to cycle as fast as I could and jumped completely exhausted with sour legs into an already late train packed with people, so I had to stand. Good start of the day. And on the way back tonight all trains around Utrecht had trouble because of some “logistic problems”. Eventually I was not unlucky: only a half hour delay, although standing again (great thing a First class ticket to Utrecht, I could really use it 😦 …NOT!). But hey, I said I went through it with a smile. Because this first seminar day was already worth the money. The more new things you hear may make you feel exhausted and even a little frustrated, but at the same time satisfied because the net pay-off is awesome, right? Continue reading
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. Continue reading
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. Continue reading
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.
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… Continue reading