RE: Performance off "count(*)"

Jonathan,

you were on the right track, see outcome. It took a time to setup and
execute.
If the hidden OID column of the binary xml table gets a null contraint,
the CBO drives
the count via the only index instead of a full table scan.


09:09:57 SQL> explain plan for
09:10:00   2  select count(*) from wiki_stage;

Explained.

Elapsed: 00:00:00.05
09:11:51 SQL> SELECT * FROM
TABLE(dbms_xplan.display('PLAN_TABLE',NULL,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
------------------
Plan hash value: 3207986117

------------------------------------------------------------------------
-
| Id  | Operation          | Name       | Rows  | Cost (%CPU)| Time
|
------------------------------------------------------------------------
-
|   0 | SELECT STATEMENT   |            |     1 |   275K  (1)| 00:55:03
|
|   1 |  SORT AGGREGATE    |            |     1 |            |
|
|   2 |   TABLE ACCESS FULL| WIKI_STAGE |  6996K|   275K  (1)| 00:55:03
|
------------------------------------------------------------------------
-

Query Block Name / Object Alias (identified by operation id):

PLAN_TABLE_OUTPUT
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / WIKI_STAGE@SEL$1

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]

20 rows selected.

Elapsed: 00:00:00.14

09:11:59 SQL> explain plan for
09:12:31   2  select count(SYS_NC_OID$) from wiki_stage;

Explained.

Elapsed: 00:00:00.02

09:12:33 SQL> SELECT * FROM
TABLE(dbms_xplan.display('PLAN_TABLE',NULL,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
------------------
Plan hash value: 1000365655

------------------------------------------------------------------------
-------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost
(%CPU)| Time     |
------------------------------------------------------------------------
-------------
|   0 | SELECT STATEMENT      |             |     1 |    17 | 11564
(1)| 00:02:19 |
|   1 |  SORT AGGREGATE       |             |     1 |    17 |
|          |
|   2 |   INDEX FAST FULL SCAN| SYS_C009650 |  6996K|   113M| 11564
(1)| 00:02:19 |
------------------------------------------------------------------------
-------------

Query Block Name / Object Alias (identified by operation id):

PLAN_TABLE_OUTPUT
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / WIKI_STAGE@SEL$1

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT("SYS_NC_OID$")[22]
   2 - "SYS_NC_OID$"[RAW,16]

21 rows selected.

Elapsed: 00:00:00.04

09:12:35 SQL> set autotrace on
09:15:54 SQL>  select count(SYS_NC_OID$) from wiki_stage;

COUNT(SYS_NC_OID$)
------------------
           6996002

Elapsed: 00:00:21.34

Execution Plan
----------------------------------------------------------
Plan hash value: 1000365655

------------------------------------------------------------------------
-------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost
(%CPU)| Time     |
------------------------------------------------------------------------
-------------
|   0 | SELECT STATEMENT      |             |     1 |    17 | 11564
(1)| 00:02:19 |
|   1 |  SORT AGGREGATE       |             |     1 |    17 |
|          |
|   2 |   INDEX FAST FULL SCAN| SYS_C009650 |  6996K|   113M| 11564
(1)| 00:02:19 |
------------------------------------------------------------------------
-------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      42373  consistent gets
       5207  physical reads
          0  redo size
        431  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


22:57:16 SQL> alter table WIKI_STAGE
11:38:42   2  modify sys_nc_oid$ not null;

Table altered.

Elapsed: 01:07:28.80
12:46:11 SQL>

12:46:11 SQL> exec dbms_stats.gather_schema_stats('WIKI',
cascade=>TRUE);

PL/SQL procedure successfully completed.

Elapsed: 01:06:37.50
17:19:20 SQL>

21:27:44 SQL> explain plan for
21:27:50   2  select count(*) from wiki_stage;

Explained.

Elapsed: 00:00:00.18
21:27:56 SQL> SELECT * FROM
TABLE(dbms_xplan.display('PLAN_TABLE',NULL,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
------------------
Plan hash value: 1000365655

------------------------------------------------------------------------
-----
| Id  | Operation             | Name        | Rows  | Cost (%CPU)| Time
|
------------------------------------------------------------------------
-----
|   0 | SELECT STATEMENT      |             |     1 | 11108   (1)|
00:02:14 |
|   1 |  SORT AGGREGATE       |             |     1 |            |
|
|   2 |   INDEX FAST FULL SCAN| SYS_C009650 |  6996K| 11108   (1)|
00:02:14 |
------------------------------------------------------------------------
-----

Query Block Name / Object Alias (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
------------------

   1 - SEL$1
   2 - SEL$1 / WIKI_STAGE@SEL$1

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]

20 rows selected.

Elapsed: 00:00:00.32


21:30:09 SQL> select count(*) from wiki_stage;

  COUNT(*)
----------
   6996002

Elapsed: 00:00:48.35

Execution Plan
----------------------------------------------------------
Plan hash value: 1000365655

------------------------------------------------------------------------
-----
| Id  | Operation             | Name        | Rows  | Cost (%CPU)| Time
|
------------------------------------------------------------------------
-----
|   0 | SELECT STATEMENT      |             |     1 | 11108   (1)|
00:02:14 |
|   1 |  SORT AGGREGATE       |             |     1 |            |
|
|   2 |   INDEX FAST FULL SCAN| SYS_C009650 |  6996K| 11108   (1)|
00:02:14 |
------------------------------------------------------------------------
-----


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      42373  consistent gets
      14470  physical reads
          0  redo size
        421  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 

-----Original Message-----
From: Jonathan Lewis [mailto:jonathan@xxxxxxxxxxxxxxxxxx] 
Sent: zaterdag 19 juli 2008 10:34
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Performance off "count(*)"



Marco,

XMLType table are a version of object tables, which means
they have a hidden OID column called sys_nc_oid$.

The problem seems to be that the sys_nc_oid$ column
(the objects unique object id) does not get declared with
a not null constraint.  So the unique index on this column
cannot be used for the count.

I can't think of a mechanism that could (legally) ever allow
the OID to be null, so it seems that you should be safe
issuing
    alter table XXX modify sys_nc_oid$ not null;

If you do, then Oracle will automatically use the index
in the count(*).

I'd raise an SR to get confirmation that this is safe before
doing it on live data though.


Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


----- Original Message ----- 
From: "Marco Gralike" <Marco.Gralike@xxxxxxx>
To: "Gints Plivna" <gints.plivna@xxxxxxxxx>; "Riyaj Shamsudeen" 
<riyaj.shamsudeen@xxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, July 18, 2008 8:25 PM
Subject: RE: Performance off "count(*)"


Thanks all for the examples. A lot (re-)think and I guess I will have to
do some 
further testing.

In case you wonder, I am working on a:  XMLType table, based on Binary
XML 
Securefile storage with has the compression parameter set to high.


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


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

Other related posts: