September 2010

Salary Survey Weirdness

Well now here’s an odd thing. In an otherwise frankly insulting article supposedly about visa fraud our old friend Don Burleson refers to Oracle Corporations salary survey for Oracle professionals which apparently shows US DBAs earning \$97k on average whilst DBA staff in the rest of the world were close to about half of that [...]

Which number takes more space in an Oracle row?

So, which number takes more bytes inside an Oracle row?

A: 123

B:  1000000000000000000000000000000000000

And the correct answer is … (drumroll) … A! The “big” number 1000000000000000000000000000000000000 actually takes less space than the “small” 123!

Let’s verify this:

```SQL> select vsize(123) A, vsize(1000000000000000000000000000000000000) B from dual;

A          B
---------- ----------
3          2```

WTF? Why does such a small number 123 take more space than  1000000000000000000000000000000000000 ?

Well, the answer lies in how Oracle stores numbers. Oracle NUMBER datatype doesn’t store numbers in their platform-native integer format. Oracle uses it’s own format which stores numbers in scientific notation, in exponent-mantissa form. More details about this here.

You can use the DUMP sql function to see the actual binary value of the number data stored:

```select dump(123) from dual;

DUMP(123)
---------------------
Typ=2 Len=3: 194,2,24

SQL> select dump(1000000000000000000000000000000000000) from dual;

DUMP(10000000000000
-------------------
Typ=2 Len=2: Typ=2 Len=2: 211,2```

So, although the number 1000000000000000000000000000000000000 is bigger than 123, when stored in base-10 exponent form, it really carries much less information in it than 123 (1 x 10^36 vs 123 x 10^0). Oracle doesn’t need many bits for keeping the precision of this large value as it happens to be a power of 10.

See what happens when I store a number only slightly bigger or smaller than the original large number, now the stored number requires much more storage for keeping the required precision:

```SQL> select dump(1000000000000000000000000000000000000+1) from dual;

DUMP(1000000000000000000000000000000000000+1)
-------------------------------------------------------
Typ=2 Len=20: 211,2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2

SQL> select dump(1000000000000000000000000000000000000-1) from dual;

DUMP(1000000000000000000000000000000000000-1)
-----------------------------------------------------------------------------------------
Typ=2 Len=19: 210,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100```

Which number takes more space in an Oracle row?

So, which number takes more bytes inside an Oracle row?
A: 123
B: 1000000000000000000000000000000000000
And the correct answer is … (drumroll) … A! The “big” number 1000000000000000000000000000000000000 actually takes less space than the “small” 123!
Let’s verify this:
SQL> select vsize(123) A, vsize(1000000000000000000000000000000000000) B from dual;
A B
---------- ----------
3 2 WTF? Why does such a small number 123 take more space than 1000000000000000000000000000000000000 ?
Well, the answer lies in how Oracle stores numbers.

Which number takes more space in an Oracle row?

So, which number takes more bytes inside an Oracle row?
A: 123
B: 1000000000000000000000000000000000000
And the correct answer is … (drumroll) … A! The “big” number 1000000000000000000000000000000000000 actually takes less space than the “small” 123!
Let’s verify this:
SQL> select vsize(123) A, vsize(1000000000000000000000000000000000000) B from dual;
A B
---------- ----------
3 2 WTF? Why does such a small number 123 take more space than 1000000000000000000000000000000000000 ?
Well, the answer lies in how Oracle stores numbers.

Which number takes more space in an Oracle row?

So, which number takes more bytes inside an Oracle row?
A: 123
B: 1000000000000000000000000000000000000
And the correct answer is … (drumroll) … A! The “big” number 1000000000000000000000000000000000000 actually takes less space than the “small” 123!
Let’s verify this:
SQL> select vsize(123) A, vsize(1000000000000000000000000000000000000) B from dual;
A B
---------- ----------
3 2 WTF? Why does such a small number 123 take more space than 1000000000000000000000000000000000000 ?
Well, the answer lies in how Oracle stores numbers.

Which number takes more space in an Oracle row?

So, which number takes more bytes inside an Oracle row?
A: 123
B: 1000000000000000000000000000000000000
And the correct answer is … (drumroll) … A! The “big” number 1000000000000000000000000000000000000 actually takes less space than the “small” 123!
Let’s verify this:
SQL> select vsize(123) A, vsize(1000000000000000000000000000000000000) B from dual;
A B
---------- ----------
3 2 WTF? Why does such a small number 123 take more space than 1000000000000000000000000000000000000 ?
Well, the answer lies in how Oracle stores numbers.

Oracle Security

A few things to report about Oracle Security after we have had a short break for familly holidays and also because of a lot of work being done over the last few months. It is nice to be busy in....[Read More]

Posted by Pete On 02/09/10 At 02:27 PM

Is it a bird? Is it a plane? No, it’s Captain Support…

I’m starting to think I’ve made a move into the PC support business, without wanting to or being paid for it…

Over the last couple of weeks my brothers laptop (one of my old ones) has been acting a bit strange. Nothing I could put my finder on, but something was not quite right. Yesterday my “Support Senses” started to tingle when I noticed some files and directories had become read-only for no apparent reason. A meer mortal might have considered trying to diagnose the problem, but this looked like a job for Captain Support. Quicker than a flash Captain Support did a fresh backup of all the photos and documents, then wasted that no good son of a… Dell.

Several hours and many Windows updates later there was a fresh copy of Windows Vista installed. Piece and harmony returned to the world. Captain Support even remembered to configure the VPN connection so his brother could connect to work.

Cheers

Tim…

Captain Support is the ongoing tale of a science nerd who was innocently sequencing sex-incompatibility genes in Brassica oleracea (wild cabbage) when he was spiked by the pins of a radioactive “i386 DX2/66″ chip, transforming him into a PC support superhero.

Public appearances 2010

Here’s the list of events where I’ll speak this year:

Michigan OakTable Symposium 2010
Ann Arbor, MI
16-17 September 2010

Considering the concentration of OakTable members there, this will be an awesome event!

I will be delivering my “Back to Basics: Choosing The Entry Point to Performance Troubleshooting Wisely” and “Understanding LGWR, log file sync waits and commit performance” sessions there.

Agenda & Registration:
http://michigan.oaktable.net/

Oracle Open #ff0000;">Closed World
San Francisco, CA
19-22. September

Note that I won’t be speaking at the official Oracle Open World conference, but I will be speaking at a secret underground event there, about some really fun stuff, like deep internals, hacking, kernel tracing and of course advanced troubleshooting ;-) And rest of the time I’ll be in some bar.

NYOUG Fall 2010 Training Session
Manhattan, NYC, NY
16 November 2010

This is a full day seminar organized by NYOUG. I will be delivering my “Scripts and Tools for Oracle Troubleshooting and Advanced Performance Analysis” session there. It’s an updated version of the material I delivered at the Hotsos Symposium Training Day this year.

Agenda & Registration:
http://www.nyoug.org/upcoming_events.htm#NYOUG_Training_Days

UKOUG Tech & EBS Conference (to be confirmed)
Birmingham, UK
29 November – 1 December 2010

I submitted four papers to UKOUG Tech&EBS conference, so if all goes well, I’ll be there in end of Nov/beginning of Dec too.

http://techandebs.ukoug.org/

Oracle Versions

Having discovered that it’s now easy to create polls, I find that it’s a little addictive.

There have been requests for help going all the way back to 7.3 fairly recently on the OTN database forum, so I thought I’d set up a poll to see which versions people had in production. If I’ve got it right you’ll be able to mark multiple choices from the list.

For reasons I don’t understand the order of the version selection is being randomised every time the screen refreshes. (The results show, as required, in the order “newest version first”).