Archive for October 2010

Grid Infrastructure 11.2.0.2 – Multicast Patch News

The addition of multicast with 11.2.0.2 caused a lot of troubles and confusion. See the following articles regarding that:

I just saw that Oracle has published a note on October 25th, which says that there will be a patch 9974223 (not yet public), which changes the multicast address from 230.0.1.0 to 224.0.0.251.

See: How to Validate Network and Name Resolution Setup for the Clusterware and RAC [ID 1054902.1]

I found it very interesting that in our current environment, the test script mcast1 fails for 230.0.1.0, but succeeds for 224.0.0.251. Other than that, the suspected host route for multicast is not even necessary at all.

So, the suggested method from Blog Article http://www.ora-solutions.net/web/2010/09/29/upgradeinstall-of-grid-infrastructure-to-11-2-0-2-is-failing/ is not necessary. It simply turned out that the Java test script is not appropriate for testing, but mcast1.c is.

Update – 10.11.2010

Patch 9974223 is officially available for Linux x86-64.



Grid Control 11.1 – Encountered Issues

I am currently working with Grid Control 11.1 (plus PSU October 2010) on Linux x86-64 and came across multiple issues:

  • For provisioning, you need 3 specific patches as explained in MOS Note 427577.1.
  • For one of those patches, you need to fix OMS_HOME/bin/PARTool.pm (MOS Note 1145125.1)
  • If you are using HP-UX Itanium Agents, you need to apply agent patch Patch 9756966 (MOS Note 1214754.1)
  • If you are managing 11.2 RAC databases with grid control you need Patch 10015330 (MOS Note 1183783.1)
  • Some of the target databases have database time zone set differently. This results in future timestamp values in MGMT_TARGETS_LOAD_TIMES.LAST_LOAD_TIME and this in turn results in “Status Pending” for targets under specific circumstances. We did not observe this behaviour with 10.2.0.5 agents.

I am sure there will be more to come ….



suboptimal prorated density calculation with DESC index – cardinality feedback feature

At a customer site, I came across a problem of the Oracle cost based optimizer with selectivity calculation with a composite index with one column sorted descending. The following testcase describes the issue. Currently Oracle development is working on the issue. During testing on 11.2.0.1, I realized that the new cardinality feedback issue takes care of the problem and subsequent executions are using the correct plan.

-- drop table if exists
DROP TABLE ADMDECKER.DUMMY purge;
 
-- create table
CREATE TABLE ADMDECKER.DUMMY (
      a NUMBER NOT NULL,
      B NUMBER NOT NULL,
      C NUMBER NOT NULL,
      D NUMBER NOT NULL,
      E NUMBER ) NOLOGGING;
 
-- fill table
INSERT /*+ APPEND */  INTO ADMDECKER.DUMMY
SELECT  rownum AS a,
        MOD(rownum,100) AS B,
        MOD(rownum,10000) AS C,
        1 AS D,
        234 AS E
        FROM (
               SELECT rownum r
               FROM
                       (SELECT rownum r FROM dual CONNECT BY rownum <= 100) a,
                       (SELECT rownum r FROM dual CONNECT BY rownum <= 100) b,
                       (SELECT rownum r FROM dual CONNECT BY rownum <= 100) c
               WHERE rownum <= 1000000
       );
 
commit;
 
-- create indexes
CREATE INDEX ADMDECKER.DUMMY_IDX1 ON ADMDECKER.DUMMY(C,B) nologging;
CREATE INDEX ADMDECKER.DUMMY_IDX2 ON ADMDECKER.DUMMY(B DESC,a) nologging;
CREATE INDEX ADMDECKER.DUMMY_IDX3 ON ADMDECKER.DUMMY(C,B,D) nologging;
CREATE INDEX ADMDECKER.DUMMY_IDX4 ON ADMDECKER.DUMMY(B,a) nologging;
 
-- gather statistics (including tab stats for virtual hidden column for "B desc" column.
BEGIN DBMS_STATS.GATHER_TABLE_STATS(
      OWNNAME=>'ADMDECKER',
      TABNAME=>'DUMMY',
      METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',
      cascade=>TRUE,
      ESTIMATE_PERCENT=>100);
END;
/
 
SELECT * FROM DBA_TAB_STATISTICS WHERE TABLE_NAME = 'DUMMY';
SELECT * FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME = 'DUMMY';
 
-- note min/max/density values for system generated virtual hidden column
SELECT * FROM DBA_TAB_COLS WHERE TABLE_NAME = 'DUMMY';
SELECT * FROM dba_ind_statistics  WHERE TABLE_NAME = 'DUMMY';

I enable 10053 tracing and execute the query. Afterwards I display the execution plan.

SQL&gt; ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
 
SESSION altered.
 
SELECT /*+ GATHER_PLAN_STATISTICS RUN2 */ COUNT(E) FROM ADMDECKER.DUMMY
WHERE C = 9592 AND B >= 93 AND D=1;
 
  COUNT(E)
----------
         0
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +COST'));
 
PLAN_TABLE_OUTPUT
---------------------
SQL_ID  f5mkaprk7fzt8, child NUMBER 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS RUN2 */ COUNT(E) FROM ADMDECKER.DUMMY
WHERE C = 9592 AND B >= 93 AND D=1
 
Plan hash VALUE: 1903509519
 
------------------------------------------------------------------------------------------------
| Id |Operation                    |Name      |E-ROWS| Cost (%CPU)|A-ROWS|   A-TIME   | Buffers |
------------------------------------------------------------------------------------------------
|   0|SELECT STATEMENT             |          |      |     4 (100)|     1|00:00:00.07 |   23437 |
|   1| SORT AGGREGATE              |          |     1|            |     1|00:00:00.07 |   23437 |
|*  2|  TABLE ACCESS BY INDEX ROWID|DUMMY     |     7|     4   (0)|     0|00:00:00.07 |   23437 |
|*  3|   INDEX RANGE SCAN          |DUMMY_IDX2|     1|     3   (0)| 70000|00:00:00.03 |     197 |
------------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - FILTER(("C"=9592 AND "D"=1))
   3 - access("DUMMY"."SYS_NC00006$"<=HEXTORAW('3EA1FF') )
       FILTER(SYS_OP_UNDESCEND("DUMMY"."SYS_NC00006$")>=93)

The complete 10053 can be viewed here: 10053 Trace of bad plan.

It can be seen that the execution plan contains the following statement and uses a very, very low density.

  Using prorated density: 0.000002 of col #6 as selectvity of out-of-range/non-existent value pred

So, instead of using Index DUMMY_IDX3, which contains all three predicate columns, especially more selective equality predicate “C = 9592”, it uses quite unselective index DUMMY_IDX2, only because density is calculated wrongly.

If the statement is executed a second time, the right plan is used. Cardinality feedback feature kicks in here. Please note the higher costs and higher cardinality for index range scan at row source operation id 3.

CONNECT ADmdecker
Enter password:
Connected.
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
 
SESSION altered.
 
SELECT /*+ GATHER_PLAN_STATISTICS RUN2 */ COUNT(E) FROM ADMDECKER.DUMMY
WHERE C = 9592 AND B >= 93 AND D=1;
 
  COUNT(E)
----------
         0
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +COST'));
 
PLAN_TABLE_OUTPUT
----------------------
SQL_ID  f5mkaprk7fzt8, child NUMBER 1
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS RUN2 */ COUNT(E) FROM MDECKER.DUMMY
WHERE C = 9592 AND B >= 93 AND D=1
 
Plan hash VALUE: 1171978535
 
-------------------------------------------------------------------------------------------------
| Id |Operation                    |Name       |E-ROWS| Cost (%CPU)|A-ROWS |   A-TIME   |Buffers |
-------------------------------------------------------------------------------------------------
|   0|SELECT STATEMENT             |           |      |    11 (100)|     1 |00:00:00.01 |      3 |
|   1| SORT AGGREGATE              |           |    1 |            |     1 |00:00:00.01 |      3 |
|   2|  TABLE ACCESS BY INDEX ROWID|DUMMY      |    7 |    11   (0)|     0 |00:00:00.01 |      3 |
|*  3|   INDEX RANGE SCAN          |DUMMY_IDX3 |    7 |     3   (0)|     0 |00:00:00.01 |      3 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   3 - access("C"=9592 AND "B">=93 AND "D"=1)
       FILTER("D"=1)
 
Note
-----
 - cardinality feedback used FOR this statement

The complete 10053 of the new plan (using cardinality feedback feature) can be viewed here: 10053 Trace of good plan using cardinality feedback. More information about this little documented new feature can be found at Dion Cho´s blog and from this Oracle paper.

Update: 06.10.2011

Oracle Support has provided Patch 11072246, which fixes a bug in DBMS_STATS on how min/max values are set for hidden desc columns. After installing the patch, the bug is gone and the execution plan is optimal.



Oracle Index Internals Seminar with Richard Foote

I just got back home from an outstanding two-day Seminar of Index Guru Richard Foote. He was coming all the way from Canberra, Australia for this seminar in Munich and next week´s in Prague. I can highly recommend this seminar to any Oracle professional interested in Performance Analysis in general and indexes in particular. He has the rare gift of explaining quite complex operations very simply, sometimes even entertaining. He finished the ~ 700 slides exactly as planned at 5 p.m. the second day, on the minute. The seminar was very well worth the money. Although he starts from the basics in regards of indexes, in my opinion the seminar is quite advanced. What is very special about the seminar is that it is unique and there is no other training material or documentation around, which has such a strong focus on index internals.

I have learned quite a lot about index internals (and even some David Bowie albums) during the 2 days and understood almost everything. However, I am still puzzled about the meaning of some Ozzie phrases like “diggysquat”, “doggledigook”, “dadada” or “presto” (although this one sounds like italian…).

If you have a chance to attend to a seminar of Richard or even a presentation on one of the major conferences – don´t miss it. BTW, Richard also blogs at http://richardfoote.wordpress.com. You can see the seminars agenda at http://richardfoote.wordpress.com/oracle-index-internals-seminar/ and you can get a taste of it in http://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth-ii.pdf

I might even check out amazon.com for one of those Bowie albums now…



Oracle 11g Certified Masters …

I was quite surprised when I received a mail from Oracle that my OCM 11g profile is now online. It turned out that I am one of only 6 people with an 11g OCM profile! Although there might be some others who succeeded in certification and didn´t publish a profile, I am still quite surprised that there are so few. Maybe the run will begin as soon as the full exam is publicly available as currently only upgrades are possible.

http://education.oracle.com/education/otn/
http://education.oracle.com/education/otn/ocm-11g.html