RE: Performance off "count(*)"
Date: Sat, 19 Jul 2008 21:39:06 +0200
Message-ID: <3FA1E6332C73EB4391F729E4EDB7CED90142F07C@amisnt30.AMIS.local>
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_at_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_at_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_at_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_at_jlcomp.demon.co.uk]
Sent: zaterdag 19 juli 2008 10:34
To: oracle-l_at_freelists.org
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_at_AMIS.nl> To: "Gints Plivna" <gints.plivna_at_gmail.com>; "Riyaj Shamsudeen" <riyaj.shamsudeen_at_gmail.com> Cc: <oracle-l_at_freelists.org> 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
Received on Sat Jul 19 2008 - 14:39:06 CDT