Home » RDBMS Server » Performance Tuning » count(*) anomaly (DB 12.1.0.2, Windows 10)
count(*) anomaly [message #651960] Sat, 28 May 2016 07:51 Go to next message
John Watson
Messages: 7266
Registered: January 2010
Location: Global Village
Senior Member
I have always worked on the assumption that the CBO doesn't care whether you count(*) or count('any constant you please'). I have encountered what seems to be an anomaly, simplified in this script:
alter session set workarea_size_policy=manual;
alter session set sort_area_size=1000000000;

drop table t1;
create table t1(c1 number);
insert into t1 select 1 from dual connect by level < 10000000;
create bitmap index bmi1 on t1(c1);

alter session set statistics_level=all;

select count(*) from t1;
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

select count(1) from t1;
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

select count(-1) from t1;
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
Here are the results:
orclz>
orclz> select count(*) from t1;

  COUNT(*)
----------
   9999999

orclz> select * from table(dbms_xplan.display_cursor(format=>'allstats last cost'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  5bc0v4my7dvr5, child number 1
-------------------------------------
select count(*) from t1

Plan hash value: 269127190

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      1 |        |   196 (100)|      1 |00:00:00.01 |     213 |
|   1 |  SORT AGGREGATE               |      |      1 |      1 |            |      1 |00:00:00.01 |     213 |
|   2 |   BITMAP CONVERSION COUNT     |      |      1 |   7005K|   196   (0)|    409 |00:00:00.01 |     213 |
|   3 |    BITMAP INDEX FAST FULL SCAN| BMI1 |      1 |        |            |    409 |00:00:00.01 |     213 |
-------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


19 rows selected.

orclz> select count(1) from t1;

  COUNT(1)
----------
   9999999

orclz> select * from table(dbms_xplan.display_cursor(format=>'allstats last cost'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  a2d8wpfzju8fr, child number 0
-------------------------------------
select count(1) from t1

Plan hash value: 269127190

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      1 |        |   196 (100)|      1 |00:00:00.01 |     213 |
|   1 |  SORT AGGREGATE               |      |      1 |      1 |            |      1 |00:00:00.01 |     213 |
|   2 |   BITMAP CONVERSION COUNT     |      |      1 |   7005K|   196   (0)|    409 |00:00:00.01 |     213 |
|   3 |    BITMAP INDEX FAST FULL SCAN| BMI1 |      1 |        |            |    409 |00:00:00.01 |     213 |
-------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


19 rows selected.

orclz> select count(-1) from t1;

 COUNT(-1)
----------
   9999999

orclz> select * from table(dbms_xplan.display_cursor(format=>'allstats last cost'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID  4uqf3hd15k6ny, child number 0
-------------------------------------
select count(-1) from t1

Plan hash value: 2124028488

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      1 |        |   196 (100)|      1 |00:00:01.68 |     213 |
|   1 |  SORT AGGREGATE               |      |      1 |      1 |            |      1 |00:00:01.68 |     213 |
|   2 |   BITMAP CONVERSION TO ROWIDS |      |      1 |   7005K|   196   (0)|   9999K|00:00:00.94 |     213 |
|   3 |    BITMAP INDEX FAST FULL SCAN| BMI1 |      1 |        |            |    409 |00:00:00.01 |     213 |
-------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


19 rows selected.

orclz>
If the constant is a string or a positive number (even zero) I get the BITMAP CONVERSION COUNT operation which is fast. Any negative number (even negative zero) gives me the much slower (though similarly costed) BITMAP CONVERSION TO ROWIDS. Any idea what is going on? Analyzing the table and index doesn't make any difference.

This question is of purely academic interest, but if anyone has any insight I'ld be interested to hear it.

ps - this is the sort of thing a dweebie person like me enjoys doing while sitting in the garden on a sunny holiday weekend Smile
Re: count(*) anomaly [message #651961 is a reply to message #651960] Sat, 28 May 2016 08:08 Go to previous messageGo to next message
Frank Naude
Messages: 4532
Registered: April 1998
Senior Member
Strange indeed!
I see Jonathan Lewis reported the same behaviour on his blog at
https://jonathanlewis.wordpress.com/2015/01/09/count-again/

Regards.
Frank

Re: count(*) anomaly [message #651963 is a reply to message #651960] Sat, 28 May 2016 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The answer is quite simple. Proud
There are 2 code paths for COUNT.
A special path for COUNT(*) and another one for any other expressions (constants, columns, anything).
But, in the early step of syntax transformation, if it is a positive constant, COUNT(positive constant) is converted to COUNT(*) (even with scientific notation like 1.12345E00). Note this is a syntax transformation, no semantic here, so if you use a positive constant expression like "1+1", it goes to the standard path.
Now the question is, why don't they include the negative case!
Also note if you use COUNT('Michel'), it is converted to COUNT(*). Maybe because in many programming languages, int('Michel') is 0. Laughing

Re: count(*) anomaly [message #651965 is a reply to message #651961] Sat, 28 May 2016 10:59 Go to previous messageGo to next message
John Watson
Messages: 7266
Registered: January 2010
Location: Global Village
Senior Member
I see the JL example picks up an important point: if you force cursor sharing, count('a constant') is always going be slower than count(*). I've been telling developers to use count(*) for years, but never had a proper reason. Now I do Smile
Thanks, guys.
Re: count(*) anomaly [message #651966 is a reply to message #651965] Sat, 28 May 2016 12:13 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I see the JL example picks up an important point: if you force cursor sharing, count('a constant') is always going be slower than count(*).


Yes, it seems logical as this turn the COUNT(1) to count(:"SYS_B_0"), a standard COUNT(expression) and so fools the optimizer.
Note that EXPLAIN PLAN which does not take care of cursor parameters still thinks COUNT(1) will be converted to COUNT(*) (at least in 11.2):
SQL> alter session set cursor_sharing = force;

Session altered.

SQL> alter system flush shared_pool;

System altered.

SQL> explain plan for  select count(1) from t1;

Explained.

SQL> select * from table(dbms_xplan.display('PLAN_TABLE',null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 269127190

------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |     1 |   196   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |      |     1 |            |          |
|   2 |   BITMAP CONVERSION COUNT     |      |  9197K|   196   (0)| 00:00:01 |
|   3 |    BITMAP INDEX FAST FULL SCAN| BMI1 |       |            |          |
------------------------------------------------------------------------------

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

   1 - SEL$1
   2 - SEL$1 / T1@SEL$1

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

   1 - (#keys=0) COUNT(*)[22]
   2 - COUNT(*)[22]
   3 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7924]

Note
-----
   - dynamic sampling used for this statement (level=2)

when
SQL> select count(1) from t1;
  COUNT(1)
----------
   9999999

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last cost'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID  6pbkyv5q90n6y, child number 0
-------------------------------------
select count(:"SYS_B_0") from t1

Plan hash value: 2124028488

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      1 |        |   196 (100)|      1 |00:00:45.63 |     217 |     25 |
|   1 |  SORT AGGREGATE               |      |      1 |      1 |            |      1 |00:00:45.63 |     217 |     25 |
|   2 |   BITMAP CONVERSION TO ROWIDS |      |      1 |   9197K|   196   (0)|   9999K|00:00:23.73 |     217 |     25 |
|   3 |    BITMAP INDEX FAST FULL SCAN| BMI1 |      1 |        |            |    409 |00:00:00.17 |     217 |     25 |
----------------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

Re: count(*) anomaly [message #651967 is a reply to message #651966] Sat, 28 May 2016 12:24 Go to previous message
John Watson
Messages: 7266
Registered: January 2010
Location: Global Village
Senior Member
Another case of "EXPLAIN PLAN tells lies"!

When I have time, I'ld like to gather up a collection of cases where EXPLAIN PLAN can be misleading. I began the process in this tutorial,
http://www.skillbuilders.com/webinars/webinar.cfm?id=136&w=Oracle-SQL-Tuning-with-Explain-Plan

Previous Topic: performance issue with oracle AQ while dequeueing the table
Next Topic: LEFT OUTER JOIN of Large Tables - Performance issue
Goto Forum:
  


Current Time: Sun Feb 25 14:23:18 CST 2018

Total time taken to generate the page: 0.02258 seconds