RE: Performance off "count(*)"
- From: "Marco Gralike" <Marco.Gralike@xxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 19 Jul 2008 21:39:06 +0200
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
