Top 60 Oracle Blogs

Recent comments

May 2010

Skip Locked

You may already be familiar with the syntax: select ... for update skip locked; It appeared (internally) some time back in Oracle 8i to implement some features of advanced queueing, but was only legalised and documented in one of the more recent versions of Oracle. If, like me, you checked the manuals to understand what [...]


Here is a recent thread from OTN forums. The question raised is WTF? whether a hint in the examples section of DBMS_PARALLEL_EXECUTE documentation have to be used or not and why it is there. What do you think about it? Have a look in the documentation and mark your choice in a poll: PS. In [...]

Aanmelden AMIS Query: “An Evening with Doug Burns”

Doug BurnsOp donderdag 17 juni, vanaf 18:00 uur, zal Oracle Database Expert en Oracle ACE Director Doug Burns(Schotland), een kennisavond vullen met live demo’s op basis van de Oracle Enterprise Manager Diagnostic en Tuning Pack.

Deze bijzondere avond, geheel zonder slides, met de naam “How I Learned to Love Pictures – Oracle 10g/11g Performance Analysis Using OEM” zal alle in en outs van de Oracle Enterprise Manager Diagnostic en Tuning pack in geur en kleur, de valkuilen en de verstopte juweeltjes van de Oracle Enterprise Manager, demonstreren gebruik makend van onder andere Swingbench. Voor meer informatie, over Doug Burns of de inhoud van deze presentatie, zie de volgende blog posts en URL’s:

Wil je er bij zijn dan kun je je aanmelden voor deze AMIS Query, via de volgende URL:


Index Rebuilds

A question came up on OTN a little while ago about an oddity during an online index rebuild. The original question describes how a relatively small index (500MB) takes several minutes to build, with a few minutes where users who are trying to update the table seem to be waiting for locks on the table [...]

Applying the rules ...

Developing software has many things in common with aircraft development, depending of course, on how you look at it. In both cases, getting technical innovations to market as quickly as possible is key to success and while our users may not fall from the sky if our software fails, there are many software products that have enormous dollar and human cost when they don't work as they should. Even

Enabling and Disabling Database Options

One of those small items that is easy overlooked (at least I overlooked it...) and I think, arrived with Oracle database version 11.2, at least on Windows/Linux...

The Oracle 11gR2 Database Installation Guide for Windows described a new tool, at least for me, that enables or disables database features on Windows. In the manual it is described as follows...

When you install Oracle Database, certain options are enabled and others are disabled. If you must enable or disable a particular database feature for an Oracle home, then shut down the database and use the chopt tool. See Example 5-1.

The chopt tool is a command-line utility that is located in the ORACLE_HOME\bin directory. The syntax for chopt is as follows:

chopt [ enable | disable] db_option

The possible values for db_option described in the following table.

Kelly Johnson's 14 Rules of Management

Kelly Johnson's 14 Rules of Management, in their original form (highlighting added by me):--------------------------------------------------------1. The Skunk Works manager must be delegated practically complete control of his program in all aspects. He should report to a division president or higher. 2. Strong but small project offices must be provided both by the military and industry. 3

Identifying Execution Plan Problems

(original at
Based on the idea by Wolfgang Breitling that we can identify cost and cardinality errors of Oracle's optimizer by looking at the discrepancies between the ESTIMATED rows and the ACTUAL rows in each line of the execution plan.
I also added in a calculation to see how many IOs we were doing per row returned.
In order to get much out of this script you have to have run the query with the hint

/*+ gather_plan_statistics */
set statistics_level=all
sql_trace=true (only way on 9i other than an underscore parameter)

col cn format 99
col ratio format 99
col ratio1 format A6
--set pagesize 1000
set linesize 140
break on sql_id on cn
col lio_rw format 999
col "operation" format a60
col a_rows for 999,999,999
col e_rows for 999,999,999
col elapsed for 999,999,999

Def v_sql_id=&SQL_ID

-- sql_id,
childn cn,
--ptime, stime,
case when stime - nvl(ptime ,0) > 0 then
stime - nvl(ptime ,0)
else 0 end as elapsed,
nvl(trunc((lio-nvl(plio,0))/nullif(a_rows,0)),0) lio_rw,
--nvl(ratio,0) ratio,
' '||case when ratio > 0 then
rpad('+',ratio*-1 ,'+')
end as ratio1,
starts*cardinality e_rows,
--nvl(lio,0) lio, nvl(plio,0) parent_lio,

Oracle Video Demos

Just found this one

video demos of Oracle features - pretty cool.

SQLPLUS -prelim

There is a way to connect to Oracle even when sessions are maxed out or there is a hang etc:
I knew about this a while back but its just one of those things that's important to remember when it's needed! Thus, so I can always know where to find it , I'm reposting:

1) sqlplus -prelim / as sysdba

2) sqlplus /nolog
set _prelim on
conn / as sysdba

see more from Tanel Poder