Mittwoch, 26. August 2009

-prelim and direct_access

I recently asked a question about the 'difference' between /prelim and direct_access on oracle-l.
Tanel Poder gave a great answer. Now it's nearly clear, at least for me:

-prelim allows sysdba to start a preliminary connection to db, which
means that a server process is started for you and it attaches to sga
segments, but it does not allocate anything inside sga (no process
state nor session state object and session structures are allocated
for example). This means tha no latch gets are needed in prelim mode
and if you have an instance hang due these latches your session wont
get stuck. You can do very limited things with prelim mode, mostly
just various dumps with oradebug.

Oradebug direct-access allows you to also query few x dollar tables
with sql-like syntax. thats fake sql though its oradebugs hack, not
real sql. The same is doable with oradebug dumptype as well.

Or, to summarise it (just for me):
  • -prelim allows me to start a sqlplus binary and connect it to the (correct ≈ right $ORACLE_HOME and $ORACLE_SID) SGA without creating anything within this SGA. So for me it's the cheapest way to get access to the shared memory of the specific instance.
  • this prelim state is of no really use in ordinary life. Just for some really rare occasions, it might be of any use. Most of these are oradebug variants. I have not tested any ALTER SYSTEM or ALTER SESSION statement yet. Some of them might be worth testing, but most will fail. Ordinary SQL will fail all the time (I'm sure).
  • oradebug direct-access is a nice gadget to query some x$ tables without really going through all the sql-parse-pin-heap-whatever engine ;-)
  • at last, -prelim is a kind of last resort to access an instance and at least get some informations, until some really hard direct SGA access methods must be considered

Dienstag, 18. August 2009

The truth about NULL

"Do you want something to eat?" - "NO"
"Do you want nothintg to eat?" - "NO"
"What do you want?" - "I don't know" (off she run crying)

That was a conversation between me and my 3 years old daughter.
As she run away, totally overstrained by the situation, her dad and her world over all, I had pity on her.
And one thought further, I recognised the true answer she gave me:
it's not "YES", it's not "NO" (regarding the 1st question), it's "NULL". Or translated into what I think are the thoughts of a 3 years old girl: "I don't know what to do now, so I'm against anything and run away!"
From now on, if any application designer argues with me, why any column can not be set NOT NULL, I will remember this picture of a overstrained, 3 years old girl and just feel pity.

Dienstag, 4. August 2009

cbo costing for pl/sql functions

I rarely create a blog entry just to refer to another article, but this time it's worth to do so:
pl/sql functions and cbo costing from Adrian Billington is worth reading and at the end a must know for anyone, who writes pl/sql functions and considers performance.