wrong cardinality

From: Stalin <stalinsk_at_gmail.com>
Date: Mon, 13 Feb 2006 16:22:55 -0800
Message-ID: <c5363d3a0602131622y1ca12fe6n15fcbb5a54a41b68@mail.gmail.com>

Hi All,

Can anyone explain why the cardinality is way off from the expected 2Mil mark. Below query should return a count of 2million rows. However both index and full scan give 133k.

Query with index range scan takes 6mins to complete however, full scan completes in 35secs. If i set OIC and OICA to defaults 0 and 100 respectively, then i get full scan without forcing via hint.

I tried collecting system stats to avoid setting OIC/OICA and let oracle to choose the right fastest path, but oracle chose index range plan, the slower 6mins.

Interesting to see adjusted MBRC to be 16 as set by dbfmrc.

MBRC=16,
MREADTIM=3.247,
SREADTIM=1.821 Current Optimizer settings

o_i_c = 75, o_i_c_a = 50; dbfmrc=16
cpu_costing=off, 9.2.0.6

1 explain plan for SELECT count(*)
2 FROM OBJECTS
3 WHERE OBJECTS.DOMAINID IN

('tK001bHUV2UNYwZJoNYz57S1Z68A','3330000000000000000000000000')

4 AND OBJECTS.TYPE IN ('cpe');

Explained.

> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

---

---

| Id  | Operation                    |  Name                         |

Rows | Bytes | Cost |

---

|   0 | SELECT STATEMENT             |                               |
    1 |    31 |  7508 |
|   1 |  SORT AGGREGATE              |                               |
    1 |    31 |       |
|*  2 |   TABLE ACCESS BY INDEX ROWID| OBJECTS                       |
  133K|  4036K|  7508 |
|*  3 |    INDEX RANGE SCAN          | IX_OBJECTS_TYPE_LASTMODIFIED  |
  266K|       |   863 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

2 - filter("OBJECTS"."DOMAINID"='3330000000000000000000000000' OR
      "OBJECTS"."DOMAINID"='tK001bHUV2UNYwZJoNYz57S1Z68A')

3 - access("OBJECTS"."TYPE"='cpe')

Forcing Full Scan:

1 explain plan for SELECT /*+ full(objects) */ count(*) 2 FROM OBJECTS
3 WHERE OBJECTS.DOMAINID IN

('tK001bHUV2UNYwZJoNYz57S1Z68A','3330000000000000000000000000')

4 AND OBJECTS.TYPE IN ('cpe')
5 ;

Explained.

Elapsed: 00:00:00.01
> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

---

---

| Id | Operation | Name | Rows | Bytes | Cost |

---

|   0 | SELECT STATEMENT     |             |     1 |    31 | 14517 |
|   1 |  SORT AGGREGATE      |             |     1 |    31 |       |
|*  2 |   TABLE ACCESS FULL  | OBJECTS     |   133K|  4036K| 14517 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):

---

2 - filter(("OBJECTS"."DOMAINID"='3330000000000000000000000000'
      OR "OBJECTS"."DOMAINID"='tK001bHUV2UNYwZJoNYz57S1Z68A') AND
      "OBJECTS"."TYPE"='cpe')

Note: cpu costing is off

FYI... NUM_ROWS BLOCKS

---------------------- ----------------------
4000137                150933

INDEX_NAME                     BLEVEL                 LEAF_BLOCKS     
      AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
CLUSTERING_FACTOR      NUM_ROWS
------------------------------ ----------------------
---------------------- ----------------------- -----------------------
---------------------- ----------------------
IX_OBJECTS_DOMAINID_NAME       3                      31037           
      1                       1                       458812          
      4000137
IX_OBJECTS_TYPE_LASTMODIFIED   2                      12906           
      1                       1                       212281          
      4000137

COLUMN_NAME                    NUM_DISTINCT           DENSITY
------------------------------ ---------------------- ----------------------
DOMAINID                       4                      .25
TYPE                           15                     .0666666666666667

Thanks,
Stalin

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Feb 13 2006 - 18:22:55 CST