Search

OakieTags

Who's online

There are currently 0 users and 28 guests online.

Recent comments

Oakies Blog Aggregator

Understanding Linux Load Average – Part 1

A frequently asked question in my classroom is “What is the meaning of load average and when is it too high?”. This may sound like an easy question, and I really thought it was, but recently I discovered that things aren’t always that easy as they seem. In this first of a three-part post I […]

NVL2()

There are many little bits and pieces lurking in the Oracle code set that would be very useful if only you had had time to notice them. Here’s one that seems to be virtually unknown, yet does a wonderful job of eliminating calls to decode().

The nvl2() function takes three parameters, returning the third if the first is null and returning the second if the first is not null. This is  convenient for all sorts of example where you might otherwise use an expression involving  case or decode(), but most particularly it’s a nice little option if you want to create a function-based index that indexes only those rows where a column is null.

Here’s a code fragment to demonstrate the effect:

select nvl2(1,2,3) from dual;

select nvl2(null,2,3) from dual;

select nvl2(1,null,3) from dual;

select nvl2(null,null,3) from dual;

And here’s the resulting output – conveniently the function call is also the column heading in the output:

NVL2(1,2,3)
-----------
          2

NVL2(NULL,2,3)
--------------
             3

NVL2(1,NULL,3)
--------------

NVL2(NULL,NULL,3)
-----------------
                3

Note, particularly, from the last two that a non-null input (first parameter) turns into the null second parameter, and the null input turns into the non-null third parameter. To create a function-based index on rows where columnX is null, and be able to access them by index, you need only do the following:

create index t1_f1 on t1(nvl2(columnX,null,1));

select * from t1 where nvl2(columnX,null,1) = 1;

(Don’t forget, of course, that you will need to gather stats on the hidden column underpinning the function-based index before you can expect the optimizer to use it in the correct cases.)

You don’t need an app for that… (browser stats)…

I’m getting a bit sick of reading about how not having a mobile presence is a big fail. Once again the marketing people take a one-size-fits-all approach and assume that if you are on the net, then people must want to use your services from mobile devices. This is utter nonsense. In reality, the need for a mobile presence depends very much on what services you are offering and who you are offering them to.

For example, look at the OS breakdown for my website over the last month.

So 98.6% of visitors are using non-mobile devices. This is not a surprise since my website focuses on Oracle DBAs and developers, who are unlikely to be working from mobile devices. Would you suggest a real company with this focus and these sort of web stats should invest in a mobile presence?

Just out of interest I checked the browser stats.

Since I last checked in November, Chrome has bitten even more market share from IE and Firefox. We’re fast approaching a 3-way tie. Looks like Microsoft really do need all those IE adverts after all. :)

Cheers

Tim…


You don’t need an app for that… (browser stats)… was first posted on April 23, 2012 at 2:15 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.




IOUG Collaborate Deep Dive slides — Deploying Oracle Database 11gR2 for High Availability by Alex Gorbachev

Quick blog post from Collaborate 2012 in Vegas. I’m only doing one session this year but it’s a very long session — I’ve just done a deel dive on deploying Oracle Database 11gR2 for High Availability. It’s a broad topic and my plan was to focus a lot on basic concepts and how they are [...]

Rant – Unique means UNIQUE! Argh!

I’m not a die-hard “Queen’s English”, “thou shalt not split infinitives” type but I am sick of people miss-using the word Unique.

The word unique means being one of a kind, the only example, the singular occurrence, the absolute only one. One. Singular. Get it? Still don’t get it? Well it means….unique! As a word that has only one unequivocal meaning, “unique” pretty much bloody well is it, by it’s absolute definition. It’s a yes/no situation. If you are unique in some respect, it means you are the only one example.

Now we lot in the database world should be bang on certain about this, what with Unique Keys and the like, and you would expect that other group of pedantic types – scientist – would be sticklers for the word as well. But no, last week I had someone who I thought was a good, solid IT person ask me “how unique” a situation was, I’ve just seen a scientist on TV describe a rock formation as “quite unique”. You can’t BE “quite unique”. You can be unusual, you can be quite rare, you can be uncommon. They all mean one of a few or a bit more blagh than usual. Unique means…The One. I can’t even think of another word that means “unique” in the way that word means. “One” and “Only” and “Singular” are close, but they all indicate something is unique. You cannot have a situation that is “quite ‘the only one’”. It is the only one or it is not the only one. Tick or cross. If you claimed a situation was unique only for someone to point out that it had happened before they would say “aha! So, it is not unique”.

It would be less of a linguistic stupidity to ask “how dead is the parrot – a bit dead or a lot dead or quite dead”. The parrot is in a binary state, dead or not. {As a biologist you can actually argue about this, but most of us accept the yes/no state of dead}. It is NOT “quite dead”.

Is Usain Bolt’s 100 meters fastest time Unique? Yes. He’s the fastest, not one of the fastest, not “fairly world record holding”.

Would it make sense to say “I have the fairly only stamp of it’s kind in my possession”? No. If someone said “this set of events have approximately never happened before” you would think “huh?” and ask for clarification – maybe ask “do you mean it’s a unique set of circumstances?” and would expect a yes or no answer. Only no, I would half expect “fairly unique”. Arrrgghh!!!

Temporary Tablespace Storage Parameters – What is Wrong with this Quote

April 22, 2012 (Modified April 24, 2012) I had intended to finish assembling the second half of the “Oracle Database 11gR2 Performance Tuning Cookbook” book review, however my free time that may be dedicated to book reviews has been a bit limited lately (I have a review of another book started, but left untouched for [...]

Where is LOB data stored?

There was a question in Oracle-L about where is the LOB data actually stored (in the row or the LOB segments) and what are the exact conditions when a switch from one to another may happen. The documentation isn’t fully clear about this and the “4000 bytes” number may mislead people to think that you can store 4000 bytes of your data in a LOB item before it must move out-of-line.

I clarified this in my post in Oracle-L, but will post it here too. First check this diagram:
 

 
If you create the LOB column with DISABLE STORAGE IN ROW, then the LOB data is always stored out-of-line. LOB index is always used. Only the LOB ID is stored inline, and the ID is looked up in LOB index, where you’ll get the pointers to actual LOB chunk blocks.
 
If you create the LOB column with ENABLE STORAGE IN ROW, then the LOB data may be stored in-line or out-of-line.
 
If the total LOB data + overhead <= 4000 bytes, then the LOB item will be stored in-line. No LOB index is used, even if you modify the LOB later on as everything is stored in-line with the row and versioning/rollback is achieved with undo data.
 
If the total LOB data + overhead > 4000 bytes, then the LOB item will be stored out-of-line. If the LOB fits into 12 x LOB_chunk_size, then no LOB index entries are created, because the in-line LOB locator can store up to 12 pointers to the LOB chunk blocks for each lob item. So if your LOB chunk size is 8kB, you can store LOB items up to 96kB in size without inserting anything to LOB index. However if the LOB item is bigger, then no pointers are stored in-row and all pointers will be put to the LOB index.
 
Note that once you modify an existing LOB item (which is bigger than 4000 bytes with its overhead), but smaller than 12 x chunk_size, then LOB index will still be used after the first LOB change operation as pointers to the old LOB chunk versions have to be stored in it (LOB segments don’t rely on undo for rollback & consistency, but just use LOB chunk versioning managed by LOB index).
 
The “overhead” of an in-line LOB item is 36 bytes, so the actual LOB data must be 4000 – 36 = 3964 bytes or less in order to fully fit in-row. And note that we are talking about bytes here, not characters. With multibyte character sets a character in a CLOB may take multiple bytes.
 
I wrote a presentation about LOB internals quite some time ago, back when Oracle 10.1 came out I think – check it here:
 

 
Enjoy! :-)
 
P.S. The next Advanced Oracle Troubleshooting v2.0 class will start in 3 weeks! (I just added the online payment option back too for convenience:)
 

WordPress 3.3.2…

WordPress 3.3.2 is out. It’s a maintenance release, so you can see the changelog for the fixes.

WordPress 3.4 is knocking at the door, so you can expect another upgrade to follow soon when it moves out of beta.

Cheers

Tim…


WordPress 3.3.2… was first posted on April 20, 2012 at 5:54 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.




Friday Philosophy – Identifying and Nullifying Fake Urgency

You know how it goes. You get a call/mail/text with something along the lines of “I need to know all the details of customer orders placed on Tuesday 7th by customers based in Botswana – and I need it ASAP, by end of play today at the latest”. So you skip lunch, drop that task you have been trying to get around to doing all week and work out how to resolve the issue that has just been dropped on you. It takes a lot of effort and you finally get it sorted out around an hour after you told your girlfriend/boyfriend/cat you would be leaving the office that day – and mail it off to the requestor. You might even call them to let them know it is done, but oddly they don’t answer.

Next day, you see the guy who wanted this urgent request and ask if it was what they wanted “Oh, I have not looked at it yet – but thanks for doing it.”

NO! “Thanks” does not work in this situation. I’d have more respect for this guy if he laughed at me and said “got you again, sucker”. Many of you know what I mean don’t you – if you are in a support-type-role, this can be a big part of your life.

I had a job years back that seemed to consist 90% of such tasks. I was the development DBA team leader responsible for testing, validating and promoting code to production. Everyone’s changes were Urgency Level 1, to be done as an emergency release and many could not be put in place until after 5pm. I’d be sat there at 18:30 in a massive but virtually empty office, applying changes along with one or two of my guys. Everyone else had gone home. This was not once or twice a month, it was 4 or 5 times a week. What are you to do?

Well, I came up with one tactic that seemed to work pretty well.

Anyone who asked for an emergency change had to be there, on site, available when the change was done.
There were of course cries of protest and people stated it was ridiculous that they had to be there, they were not needed, the change had been tested thoroughly {oh how I laughed at that – a thoroughly tested “emergency” change huh?}. No, I replied, you had to be there in case it went wrong as it’s your system, your data and, frankly, your emergency. If it is not urgent enough for you – the guy wanting it to be done – to be inconvenienced, well it sure as hell is not urgent enough to inconvenience me. “You can call if there are problems” – What, after you have escaped the locality? Maybe turned off your phone? And if I get you , I have to wait for you to come back in? No no no. Urgent emergency now equates to presence in office. After all, I’ll be there.

I stuck to my rule. If the requester could not be bothered to stay, I downgraded the request to “Planned” and put it through the CAB process. If the requester dumped on one of their team and made them stay, I mentally marked them half a point down and factored it in next emergency.

The change was remarkable. I was no longer in the office on my own every evening. I was not there with someone else either. I was simply not there as, when you made the emergency a little bit inconvenient to the requester, it magically stopped being an emergency.

There was another change. Less cock-ups. Seeing as these changes now went through the CAB process and slightly more testing {like, some testing} the duff changes were more likely to be detected before they caused damage. My bosses went from regarding me as “not a team player” to “Not a team player – but we kind of get your point now”.

So my advice is, if someone wants to try and make something your emergency, find some way of making sure it remains inconvenient to them. If they are willing to put up with the inconvenience, then it is a real emergency and you need to crack on with it.

Lockout…

If you took Escape from New York and gave it a Sci-Fi touch-up you would probably end up with Lockdown. That’s not a bad thing, since I really enjoyed EfNY when I saw it as a kid. I thought Lockdown was a fun action film. It doesn’t push the boundaries, but what it does, it does pretty well.

Guy Pierce‘s character (Snow) is actually pretty similar to Kurt Russell‘s character (Snake Plissken). Mouthy and cool. Nice also to see Joseph Gilgun from the This is England film and TV spin-offs.

Overall I would say lightweight, but quite enjoyable.

I’ve been playing catch-up this week. Of the four films I’ve seen I think I enjoyed this one the most.

Cheers

Tim…


Lockout… was first posted on April 20, 2012 at 2:37 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.