May 2010

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 [...]

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 [...]

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.

(original at http://sites.google.com/site/embtdbo/tuner/oracle-tcf-query-and-lios-per-row)
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 */
or
set statistics_level=all
or
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

select
       -- sql_id,
       --hv,
       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,
       --id,
       --parent_id,
       --starts,
       --nvl(ratio,0)                                    ratio,
       ' '||case when ratio > 0 then
   rpad('-',ratio,'-') 
       else 
   rpad('+',ratio*-1 ,'+') 
       end as                                           ratio1,
       starts*cardinality                              e_rows,
                                                       a_rows,
       --nvl(lio,0) lio, nvl(plio,0)                      parent_lio,