July 2011

Whilst investigating the latest of our many library cache contention problems on 11.2, I made the fatal mistake of relying on my previous experience combined with a standard Oracle Support note describing how to diagnose such problems.

When the system was apparently hung (although the reality was that one session was holding a library cache mutex or latch that other sessions were waiting for - hopefully more on that later), I decided a Systemstate dump would be a good idea. (On a side-note, I often prefer Hanganalyze but I suspect that's more to do with me finding the resulting trace file easier to read.)

What a mistake. Particularly as this was a Production system and I'd managed to talk one of the clients good DBAs into helping me out! As soon as the statement was run, the system went into a much worse state and, to cut a medium but too exciting story short, we ended up having to restart everything.

To make matters worse, another good colleague reminded me that he had sent out a warning to people that he'd hit the same experience earlier, due to bug 11800959 - "A SYSTEMSTATE dump with level >= 10 in RAC env dumps unnecessary huge BUSY GLOBAL CACHE ELEMENTS". In the usual email flurry, I'd put that whole conversation to one side.

So please don't make the same mistake as I did and ignore the warning - tread carefully before you start taking Systemstate dumps on 11.2!

For organizations that just procured an Exadata machine, one of the big questions is bound to be about the group supporting it. Who should it be - the DBAs, Sys Admins, Network Admins, or some blend of multiple teams?

Dates are a surprising pain for correlating data an at the same time being understandable to the human reader.

For a generic form we can take seconds since 1970, on UNIX, with

$ date '+%s'
1311372938

but then how to we convert it back? There is a neat trick for that, “date -d @”

$ date -d @1311372938  +%H:%M:%S
22:15:38

so in scripts we can load up the date into seconds, that can be subtracted to get deltas and at the same time converted easily to any date format :

date_secs=` date '+%s'`
date -d @$date_secs +%H:%M:%S
22:23:34

AWK

AWK takes a bit of a trick to convert seconds to human format:

date_secs=1311372938
# setup the UNIX command
d="date -d @"date_secs" +%H:%M:%S"
# run the command to getline and put it into "date_human_format"
d | getline date_human_format
# be sure and close the open descriptor
close(d)

Dtrace
Dtrace will output nano seconds since 1970 in “walltimestamp” function, so just lop off the nanoseconds and you can convert it to date

date_secs=walltimestamp/1000000;

Oracle

Oracle  has the nice feature of returning the days along with fractional days when subtracting two dates, so it seems like just subtracting off 1970 should work and then multiplying by the seconds in a day

SELECT (SYSDATE - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) from dual;

But since Oracle can already subtract dates there is little need to do the conversion unless extracting the data to be used by an external consumer.

If Oracle is reading data from an external source in seconds since 1970 we can just reverse the process

select to_char((TO_DATE('01-JAN-1970','DD-MON-YYYY') + seconds_since_1970 / 86400 ),'DD-MON-YYYY') from dual;

example

SQL> select to_char((TO_DATE('01-JAN-1970','DD-MON-YYYY') +1311372938  / 86400 ),'DD-MON-YYYY') from dual;

TO_CHAR((TO
-----------
22-JUL-2011

SQL> SELECT (SYSDATE - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) from dual;

(SYSDATE-TO_DATE('01-JAN-1970','DD-MON-YYYY'))*(86400)
------------------------------------------------------
                                            1311424000

Oracle TIMESTAMP

One issue on Oracle though, the variable type timestamp can be a bit frustrating to work with, but it can be cast into a date, for example:

select cast(begin_interval_time as date ) from DBA_HIST_SNAPSHOT;

UPDATE: just found this link which is pretty good at covering a wide range of languages and databases:

http://www.epochconverter.com/

though the didn’t include AWK :)  – who uses AWK these days anyway?

At the risk of turning my Blog into some sort of half-way-house tweet update thing (correct, I’ve never logged into twitter), as a couple of people asked about the outcome with the broken £1,000 server, I’m happy to report it came back this week. The motherboard had died. I’d convinced myself it was the PSU when I trawled the net as it seems to be one of those things that is most likely to die having fired up in the first place, but no, the motherboard. I guess some solder “dried” or the pc pixies just don’t like me. One month turnaround is not very impressive…

They had another motherboard exactly the same in stock so I got a like-for-like swap. I was kind of hoping for a different one with more SATA3 and USB3 headers

Now I’m trying to download the latest oracle 11 for 64 bit windows. I live out in the wilds of North Essex (for non-UK people, this is all of 62 Kilometers North-Northeast of London as the crow flies, so not exactly in an obscure and remote part of the UK! For those who DO know the UK, it is nothing like “the only way is Essex” out here. We have trees, fields, wildlife and a lack of youth culture.) As such, my broadband connect is sloooow. The connection keeps breaking and I lose the download. *tsch*. I’m sure I had a download manager somewhere which got around these issues…