Search

Top 60 Oracle Blogs

Recent comments

July 2011

Homecoming

I’ve been on holiday, walking in the Lake District, for a few days, so you haven’t seen much from me for a while (apart from a couple of post-dated items).

Now that I’m back I’ve got several hundred email messages, a couple of jobs, and a load of writing to catch up on, so you’re still not going to see much output. However, I have a of odds and ends that I’ve filed for later use, and I started tidying this list up a few weeks ago, so I thought I’d share the tidy bit with you – just to make sure you have some interesting reading over the next few days.

It’s published as a WordPress “page” rather than a post, I’ll probably post a new temporary link to it from time to time so that people can see when it gets updated. I’ve also added it to the “Special Links” list in the right-hand column.

Difference between Select Any Dictionary and Select_Catalog_Role

When you want to give a user the privilege to select from data dictionary and dynamic performance views such as V$DATAFILE, you have two options:

grant select any dictionary to ;
grant select_catalog_role to ;

Did you ever wonder why there are two options for accomplishing the same objective? Is one of them redundant? Won't it make sense for Oracle to have just one privilege? And, most important, do these two privileges produce the same result?

Systemstate Dump warning

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!

OT: Norway

Having had one of my best conference experiences ever this year in the company of many good Norwegians, this weeks news saddened me and it must have come as a hell of a shock to the country as a whole, not just those involved. Their horror is difficult to imagine.

I really don't have adequate words, frankly.

At the same time, it inspired me to hear ...

"We must – and will – meet terror with more democracy, not less. We must not lock up Norwegian society. That would be piling tragedy upon tragedy."

My heart goes out to those involved and I look forward to going back to Norway next year ...

Who Manages the Exadata Machine?

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?

Toolbar Buttons Revisited…

My 9 year old nephew was making a Powerpoint slideshow today. I watched him regularly saving his slideshow using the menu and asked why he was not using the Save button, to which he replied, “Which one is it?”.

I looked at the toolbar and saw a button with a picture of a floppy disk. I don’t think he has ever seen a floppy disk in his life. I’m not surprised he didn’t associate this button with saving his slideshow.

I’ve seen articles suggesting that buttons with icons are not good for new users. This is especially true if the icons reference old technology they have never encountered.

Cheers

Tim…

 

 

 




date conversions

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?

Pythian Tools: Method R Profiler, MR Tools & MR Trace

Working with 100 talented database engineers is fun and there are lots going on — lots of exciting (and not so much) projects ongoing, huge amount of problems solved, mistakes made (and learned from), many unique (as well as routine) customer needs satisfied, many new (and old) methods applied, many good (and less so) tools [...]

Fastest £1,000 Server – back from supplier

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…


Which Linux do you pick for Oracle Installations?

There was an interesting thread on the OakTable mailing list the other day regarding the choice of Linux distros for Oracle installations. It was started by one member (the name has been withheld to protect the innocent :) ) who said,

“I cannot imagine (but want to understand) why anyone would pick RHEL5.6 for Oracle as opposed to the vastly superior OEL with the UEK.”

I must admit I’ve kinda forgotten that any distro apart from Oracle Linux (OL) exists as far as production installations of Oracle software are concerned.

Some of the reasons cited for people not to pick OL include:

  • The customer has a long relationship with Red Hat and doesn’t want to jump ship.
  • RHEL is the market leading enterprise Linux distro, so why switch to Oracle?
  • The customer doesn’t want to be too dependent on Oracle.
  • The customer has lots of non-Oracle servers running RHEL and doesn’t want a mix of RHEL and OL as it would complicate administration.
  • The customer uses some software that is certified against RHEL, but not OL.
  • The customer prefers Red Hat support over Oracle support. Wait. Red Hat and support in the same sentence. Give me a minute to stop laughing…
  • The customer is using VMware for Virtualization and the Unbreakable Enterprise Kernel (UEK) is not supported on VMware.

I guess every company and individual will have differing justifications for their choice of distro.

So why would you pick OL and Unbreakable Enterprise Kernel (UEK) for Oracle installations?

  • You can run it for free if you don’t want OS support. Using OL without support doesn’t affect the support status of the products (DB, App Servers etc.) running on top of it.
  • It’s what Oracle use to write the Linux version of the products.
  • It’s what Exadata uses.
  • Oracle products are now certified against the OL + UEK before they are certified against the RHEL kernel.
  • UEK is typically a much more up to date version of the kernel than that shipped by RHEL and includes all the patches vital for optimum Oracle performance.
  • Single vendor, so no finger pointing over support issues (from Google+ comment).
  • It is the only enterprise Linux distro that supports kernel patching without reboots thanks to Oracle’s newly aquired Ksplice.

For more information you might want to read this whitepaper or watch this webcast.

If you are looking at things from a purely technical perspective, I guess you are going to pick OL and UEK. Of course, many of us don’t work in a world where technology is picked purely on its merits. :)

Cheers

Tim…

Update: Check out this post by Jay Weinshenker for a different angle on this issue.