Miracle database forum day 1

We all had a good nights sleep, allbeit a short one and started off with Doug Burns about Dtrace. I had seen some posts by Doug about Dtrace but it did not catch on until today. He told enough about it to start to think about uses for tracing when the standard database tracing is not enough. Dtrace is an open source tool mainly found on Sun Solaris although there are some ports. It is a scripting language that can trace any part of a process running as long as you know the name of the module that you want to look to. And he showed some ways to get to know the names of the modules. Very light footprint so it is a tool you can even use in a production environment. Not something you do lightly, but enough interesting hooks to want to try that sometime.

Carel-Jan Engel had a novel use for his beloved data guard environment. When you specify a delay in the apply of redo log files on a standby database, you can use the standby as a means to recover from logical errors (user dropping a table, you know the kind). As long as the drop of the table has not arrived on the standby, you can temporarily stop applying redo logs, open the standby in read only mode, read the contents of the dropped table through a database link or export and use that to recover the table on the primary. Then you can restart the standby and start applying the redo logs again. With your dropped table in place. There are other ways to do this of course, some cheaper. But it is a nice example of thinking outside of the box.

Alex Gorbachev, a Russian now living in Canada showed us the results of his project in testing a hardware cluster solution for a data center and hammering it with a load of data. The hardware is called LiquidIQ The most interesting about the presentation to me, was that he used Hammerora to load the oracle database on the hardware with lots of data. Another tool to look into when I need to stress test a configure database. Now looking for some project that is able to sponsor that.

Joze Senegacnik from DbProf showed everything about the automatic sizing of work areas (for sorting, hash joins ….) that I wanted to know about a year ago when we were struggling to get a database and an optimizer to use as much hash joins as possible. I know why it was so difficult now but at least now I know it for a future use.

Michael Moller was asked by a customer to measure the benefits of buying the partitioning option for a table that was essential for the customer. Finally a customer that had enough sense to try to determine the effect of an option before believing the consultant that says it will be beneficial. Although I do not agree with his conclusions that partitioning is not to be used, it was refreshing to see the methodical way he approached the subject. A pity for the customer they did not use other partitioning schemes. I’m positive there is more to gain to get there.

Anjo Kolko has tought of lots of meanings for TBD, besides the obvious. His presentation was mainly an essay to start (DBA) people to think about what they are doing with the database. Tools are surprisingly simple: stats, rule, engine, advice. This goes for statspack, ASH, YAPP, even your own tool. Think about it.

Piet de Visser (Databases, Systems, Relationships (KISS)) held a plea for simplicity as opposed to complexity. He noticed there has been some consolidation of late resulting in reducing the number of databases. This lead to all kinds of different data into one database on one server. The process has been difficult due to requirements by vendors, suppliers and application owners. But it succeeded in many cases. And all this leads to simpler maintenance. The ideal situation would be one system; one database. Utopia of course, but it happens now and then. Currently ‘the one database’ is evolving into one database on many systems (a.k.a Grid). This adds a lot of complexity that is opposing the simplicity strived for in consolidation.

Julian Dyke (Flashback logging internals) dives another time into the deep dungeons of Oracle. Trying to get detailed knowledge of how Flashback logging is done. It is based on redo logging of course, but because of its nature it is different, very different. It looks a lot like before images of blocks are saved into the flashback area. Julian found out about a lot of undocumented commands that work on flashback logging and by their nature and names reveal many of the secrets of the flashback area. But a lot of detective work still has to be done.

Kurt van Meerbeek (Dude, where’s my data?) is the author of DUDE, Data Unloading by Data Extraction. Extract data from datafiles of a database that cannot be brought online any more due to loss of hardware, software or even the SYSTEM tablespace. Most people have seen this some time in their professional life; far beyond the ‘Oops’ moment, when you really start to panic. And of course not having a backup that vaguely usable. DUDE only reads data! It cannot change anything. It works on oracle data blocks and deduces the contents of a datafile, even if the system tablespace with the catalog is not available. Oracle (consultants) can do that, but this is very expensive and often not fast enough. DUDE is.

After all this we could do with some refreshment and some talking about other subjects. We could wander around Edinburgh Castle and see the Scottish crown jewels and the Stone of Destiny. A pleasant evening followed with a good meal, excellently topped off with a talk by Dave Ensor, former and now retired colleague of both Graham Wood and Jonathan Lewis. Some Gaelic music after that and anybody that was not tired yet could (and did) continue with some live, more modern music in Whistlebinkies.
Certainly a good day.

Miracle database forum day 0

Yesterday I arrived in Edinburgh. And a busy day it was. It started on the boat to England/Hull actually. I had decided to travel by motorbike to Edinburgh and after the conference make a little trip through Scotland before taking the boat home again. I checked in at about 7 pm and was immediately notified my passport had expired. Luckily it was just 1 week ago so there was not too much hassle, just the off chance that the English customs would not let me in. But they did, as seems clear, now that I am writing about Edinburgh.

I had some trouble finding a map for the northern part of England and ended up in a small seaside town before finding a shop that had one available. Just an extra 20 miles to ride for today. I was riding for fun, wasn’t I? The weather was not too bad, just some trickle now and then, but I had some dry roads. Nice. What I remember most from the trip are the hedges alongside the roads. They are all blooming currently and combined with the rain I could even smell that while driving by.

The trip along the coast was not too inspiring, but when I came into the North York National Park the scenery changed considerably. Much more rolling hills, well cultivated fields and lots of villages. And bends and blind summits in the roads. Those bends are one of the main reasons why I wanted to travel by bike in the first place, so they were nice. But I could do without the blind summits. Not knowing where the road is going is unnerving to me when driving at 80 km/h. Though it was not so much of a problem to the locals. They kept passing me with death-defying speed.

Anyway I survived the summits and had fun on the bends. When I entered Northumberland National Park the scenery changed again. Higher hills (mountains?) and summits with moor on top. Very beautiful. A pity the rain gained some strength there and the clouds where hanging rather low. But hey, that’s what they say about Scotland: “You don’t like the weather? Wait five minutes, it will have changed by then”. Not entirely true, but close. By the time I arrived in Edinburgh it was dry. Just in time to take a quick shower and then head quickly to the Whisky Heritage Center for registration.

The Scots do know how to make a show about a drink! Even a rollercoaster (at elderly people pace) to tell the history of the role of whisky in Scotland. And we got to drink a ‘wee dram’ of the stuff. Not too bad, not too bad

So much for the fun part. We had an introduction to the conference by Miracle Scotland President Thomas Presslie and a key note by Jonathan Lewis of which you can find more here and here. Something to do with an ironing board that was now improved upon. You can do the back of the shirt in half the time! But the marketing guys forgot to mention that there are 17 different steps to ironing a shirt and they improved upon only one of those. Does that ring a bell with the oracle features? Okay, the fun was not totally over, but there was a more serious tone in the evening.

Next we had Graham Wood talking about one of the features that is about to be included into the upcoming 11g version of Oracle (no garantuees it indeed will be included, of course). He promised that along the lines of sql trace and statspack there would be a possibility of tracing ‘contention’. This should give answers to questions like “Which process was holding what other process and what where they trying to do at the time?” Indeed one of the things that is not traceable right now and can be perceived as a performance problem. I think that for one, will be a usefull addition. And we got an open invitation to name this feature, just like YAPP, ADDM and ASH have been named by some of the people present in this conference.

Next, some more whisky tasting. And not just gulping it down! I believe we attended something of a University grade class in Whisky tasting, including a competition in ‘nosing’ and identifying some smells that can be present in whiskies. I did not do very well there, I recognized the smells, but could only name 4 of the 10. The whisky went down very well, by the way, thank you.

Last we had a representative from one of the sponsors of the conference. But since it was after the tasting and the representative did not catch on to the spirit of the conference, I only remember it was about IBM incorporating AMD processors in their hardware excellently. Oh well, they sponsored the drinks. I’ll not say a bad word about them.

Encyclopedia spine in a DWH/BI context

One of the blogs I read by Dominic Delmolini had an interesting question that seemed an nice exercise in the use of sql and probably analytic sql. It turns out that an elegant solution (by the questioner himself) uses the 10g addition of reg_exp_replace as well. I have been using regular expressions a lot in unix scripting and have grown fond of them, although they are terrible to read if you haven’t made them yourself.

The solution presented by Dominic made me think about uses for a query of this kind in the current day and age. Who still has a need for an encyclopedia after all, these days? Other than that it looks so nice on your bookshelves?

The thing that I came up with would be something along the lines of a materialized view for the contents of, for example, an enormous parts database with millions of parts, bolt and nut like parts that is. With this query (or materialized view based on it) you could present someone who is looking for a part but does not know what it is called exactly, with a quick way to find what he/she is looking for.  You could also use this in a datawarehouse to present an overview of the number of people selected for a mailing of some kind per part of the city – or street names the selected people live in. I think that would be a rather intuitive way of showing results that many people are used to, but, up to now, was not so easy to create.

Another thing learned.

OWB Monitoring

Ever have had to look at the result of jobs in an OWB environment? The most complete way to do that is to first start the OWB Browser Listener, which incidently takes about 1 minute on my 1GB XP VMWare virtual machine. Then start the OWB repository browser, another 30 seconds and login (typing 10 seconds, waiting for the first screen to appear; 15 seconds). Then you have to click on the Execution Schedule Report, wich the first time takes about 10 seconds as well. From there on the Repository browser is by far the best way to determine what transpired during the jobs that have run.

But if you want to quickly have a look how everything is running, and not wait 2′05″,  it’s much easier to login to the database and run a query against the runtime repository (i.e. if you have a user that has the privileges to select from the runtime repository). I know a lot of people will have made something along the same lines, but I have made my own. Anybody that is interested is invited to copy the query below and improve upon it.

SELECT    e.execution_name, e.return_result, e.creation_date,
e.last_update_date, e.elapse,
TO_CHAR (TRUNC (SYSDATE, 'DD') + e.elapse / (24 * 3600), 'HH24:MI:SS') AS elapse_format,
wb_rt_constants.to_string (e.audit_status) AS audit_status_symbol, e.audit_status,
DECODE (x.err, NULL, 0, x.err) AS error#,
DECODE (x.sel, NULL, 0, x.sel) AS selected,
DECODE (x.ins, NULL, 0, x.ins) AS inserted,
DECODE (x.upd, NULL, 0, x.upd) AS updated,
DECODE (x.del, NULL, 0, x.del) AS deleted,
DECODE (x.dis, NULL, 0, x.dis) AS discarded,
DECODE (x.mer, NULL, 0, x.mer) AS merged,
DECODE (x.cor, NULL, 0, x.cor) AS corrected,
wb_rt_constants.to_string (m.severity) AS severity_symbol, m.severity, l.plain_text,
p.parameter_name, p.value,
m.creation_date, e.task_object_name, e.task_object_store_name,
e.task_name, e.audit_status,
e.top_level_audit_execution_id, e.audit_execution_id,
e.parent_audit_execution_id
FROM      wb_rtv_audit_executions e
LEFT JOIN wb_rtv_audit_messages m
ON        e.audit_execution_id = m.audit_execution_id
LEFT JOIN wb_rtv_audit_message_lines l
ON        l.audit_message_id = m.audit_message_id
LEFT JOIN
(SELECT   e.audit_execution_id, SUM (a.rta_errors) AS err,
SUM (a.rta_select) AS sel, SUM (a.rta_insert) AS ins,
          SUM (a.rta_update) AS upd, SUM (a.rta_delete) AS del,
          SUM (a.rta_discarded) AS dis, SUM (a.rta_merge) AS mer,
          SUM (a.rta_corrections) AS cor
        FROM      wb_rt_audit_executions e
        LEFT JOIN wb_rt_audit a
        ON        e.audit_execution_id = a.rte_id(+)
        GROUP BY  e.audit_execution_id) x
ON        e.audit_execution_id = x.audit_execution_id
ORDER BY  last_update_date DESC;

Bitmap image of a “De Stijl” bicycle.

Several years ago, when windows 3.11 was the latest and the greatest I thought MS-Paint was a nice piece of software. And I fumbled around with it a lot. One of the few things that survived from that period is the image you see above. I still use it now and then. And I thought it would be nice to use it for my blog as well. Now for trying to get the image on every page. Html editing was not my thing with windows 3.11 and it isn’t right now.

Start OWB mapping from the Design client and provide parameter(s)

We recently have found the method to start a mapping from the OWB Repository Browser. And we did that all by ourselves . The beauty of it, we thought, was that you could provide a value for any parameters defined in the mapping. Something we could not do from the Design Client. Luckily the guys and gals from OWB development are emerging from the dark and are actively blogging about the things you can and can’t do with OWB. There is a post on orablogs now that describes how to start your mapping (or process flow) from the Design Client and provide parameter values as well.

The posts I have recently seen on the OWB blog suggest that it is advisable for anyone working with OWB to keep a tab on the blog. Lots of interesting things to do with OWB and even some promises of things to come in OWB 10.2.0.3.

Window dressing

Ever since I started with Windows 3.1 I have been a sucker for fancy icons. I have been wondering for quite some time now how those small icons for web pages were included in the pages. And today I found one way to do that. I’m sure there are other (better) ways. But I wanted to note this one, at least for myself.

Securing a database

Security is becoming more and more of an issue. And that includes the data in an OLTP database or a data warehouse. The ultimate source for security related Oracle issues is Pete Finnigan, of course. He was asked recently if Oracle had created a good entry point for securing a database. Mr. Finnigan was not very convinced an Oracle security newbie would be introduced properly by the presented material. He suggested however two other places he liked very much. I wanted to note them here for anybody that needs a thorough security introduction. And I include myself in the list of interested (newbie) readers when I will be securing the next database I encounter. My current environment has no extensive security requirements, but I am sure I will see these environments very soon, from close by.

Look for it here: Oracle security white papers page and here: Arup Nanda’s Project Lockdown, or go to the post mentioned above.

Formatting elapsed seconds

I guess we all have had that awkward moment when you want to know how many hours and minutes 11311 seconds is. I don’t understand why I can’t find such a function in Oracle, seems strange. Today was one of those moments so I created a query that does just that. I leave it to others to create a function from this.

This is what I started with:

SELECT TO_CHAR ( TO_DATE ('1900-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + 1 / (24 * 3600),'HH24:MI:SS')
FROM DUAL;
A bit verbose so I changed it to:

SELECT TO_CHAR ( TRUNC (SYSDATE, 'DD') + 1 / (24 * 3600),'HH24:MI:SS')
FROM DUAL;

Both give the following result:

|formatted|
-----------
|00:00:01 |

That’s the principle, now for some test data

-- Create a table to hold some test data
CREATE TABLE father_time (seconds NUMBER);
-- Generate seconds as a fraction of a day into the table
-- I personally like the use of the pseudo column LEVEL in this statement

INSERT INTO father_time
WITH x AS
(SELECT (LEVEL - 1) / (24 * 3600)
FROM DUAL
CONNECT BY 1 = 1 AND LEVEL < 24 * 3600 + 3) -- slightly more than a day of seconds
SELECT *
FROM x;

-- Format the elapsed time into hours, minutes and seconds
-- if there are more than 24 hours of seconds the days are 'truncated'
SELECT seconds,
TO_CHAR (TRUNC (SYSDATE, 'DD') + seconds, 'HH24:MI:SS') AS formatted
FROM father_time
ORDER BY seconds DESC;

DROP TABLE father_time;

And here are some interesting parts of the output:

|seconds |formatted|
|-----------|---------|
|1,000011574| 0:00:01|
| 1| 0:00:00|
|0,999988426| 23:59:59|
|0,999976852| 23:59:58|
.
.
|0,99931713 | 23:59:01|
|0,999305556| 23:59:00|
|0,999293981| 23:58:59|
|0,999282407| 23:58:58|
|0,999270833| 23:58:57|
|0,999259259| 23:58:56|

You can imagine the rest I guess.

New is better (maybe not)

As others already have mentioned; Oracle 10.2.0.3 is available in Windows Vista flavor here. And the new version of VMWare is out of beta as well.

Had no problems whatsoever with the 5.5 version up to now, so I will probably wait with installing VMWare 6 until the 6.1 (and until the money comes in, $189 is not cheap).

I have downloaded Oracle for Vista but have not installed it yet. More fun to follow.