jump to navigation

Min and Max do not Mix May 27, 2009

Posted by mwidlake in performance.
Tags: , ,
10 comments

If you want to select the min and max value for an indexed column in a table, it may not perform as well as you like, despite some nice tricks Oracle can do with min and max. I’ll show you what I mean and a little trick, which might be the fastest way to get the min and max values in Oracle.

You can use this scipt here to replicate the below. It creates a test table of 99,999 rows. It was written on oracle 10 and should work on 11 unchanged {post a comment if you try and find anything is different} and you just need to remove the “purge” part of the drop command to get it to work on 9 {I think}.

This is the basic environment and setup.

NAME                           VALUE
------------------------------ --------------------
compatible                     10.2.0.1.0
cpu_count                      1
db_block_size                  8192
db_file_multiblock_read_count  16
optimizer_mode                 ALL_ROWS
sga_target                     612368384
sort_area_size                 65536

test102>desc test_1
 Name                             Null?    Type
 ------------------------------------------------------
 ID                               NOT NULL NUMBER
 NUM_1                                     NUMBER
 ID_2                                      VARCHAR2(41)
 NUM_2                                     NUMBER
 NUM_3                                     NUMBER
 NUM_4                                     NUMBER
 VC_1                                      VARCHAR2(50)
 VC_2                                      VARCHAR2(50)
 VC_3                                      VARCHAR2(250)

select count(*) from test_1;

  COUNT(*)
----------
     99999
Elapsed: 00:00:00.01

Execution Plan
-----------------------------------------------------
| Id  | Operation             | Name | Rows  | Cost (%CPU)|
-----------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 | 49   (5)|
|   1 |  SORT AGGREGATE       |      |     1 |         |
|   2 |   INDEX FAST FULL SCAN| T_PK | 99999 | 49   (5)|
-----------------------------------------------------

Statistics
-----------------------------------------------------
          0  recursive calls
          0  db block gets
        215  consistent gets
          0  physical reads

You can see that to count the 99,999 rows the CBO expects to fast full scan index T_PK for a cost of 49 {which equates roughly to the number of expected I/O operations} and the execution results in 215 buffer gets, which is the number of times a block was read from memory.

Let’s select the MAX(ID):

select max(id) from test_1;

   MAX(ID)
----------
     99999

Execution Plan
-----------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |    4 |2(0)|
|   1 |  SORT AGGREGATE            |      |     1 |    4 |    |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T_PK | 99999 |  390K|2(0)|
-----------------------------------------------------

Statistics
-----------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets

The plan shows that Oracle will use an INDEX FULL SCAN (MIN/MAX) to get the result. I find it a little misleading that it is called a “full scan” and it shows as processing 99,999 rows when in fact what Oracle does is nip down the ‘edge’ of the index to get the result. It reads the root block and one leaf block to get the result {Oracle does not need to visit the table, the data for column ID is in the index}.
If you have any doubts, look at the consistent gets – 2.
It’s fast and efficient.
It works with MIN too:-

select min(id) from test_1;

   MIN(ID)
----------
         1

Execution Plan
--------------------------------------------------
| Id  | Operation                  | Name | Rows  |Bytes| Cost (%CPU)|
--------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |   4 |2 (0)|
|   1 |  SORT AGGREGATE            |      |     1 |   4 |     |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T_PK | 99999 |390K|2 (0)|
---------------------------------------------------

Statistics
----------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets

It does not work for the other group functions as they need to gather all the data to, for example, calculate the average value for ID:

select avg(id) from test_1;

AVG(ID)
———-
50000

Execution Plan
——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
—————————————————–
| 0 | SELECT STATEMENT | | 1 | 4 | 49 (5)|
| 1 | SORT AGGREGATE | | 1 | 4 | |
| 2 | INDEX FAST FULL SCAN| T_PK | 99999 | 390K| 49 (5)|
—————————————————–

Statistics
—————————————————–
0 recursive calls
0 db block gets
215 consistent gets

Here Oracle returns to the index fast full scan and the same workload as count(*).

Now for the “gotcha” bit. What happens if you select MIN and MAX?

select max(id),min(id) from test_1;

   MAX(ID)    MIN(ID)
---------- ----------
     99999          1


Execution Plan
--------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes |Cost (%CPU)|
--------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |     4 |49   (5)|
|   1 |  SORT AGGREGATE       |      |     1 |     4 |        |
|   2 |   INDEX FAST FULL SCAN| T_PK | 99999 |   390K|49   (5)|
---------------------------------------------------


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

Damn! Oracle goes back to the index fast full scan and 215 consistent gets!

Way back when I was beta testing Oracle 10 I found this and reported it as a bug. Oracle Corp replied that it was not a bug, it was expected behaviour. It would scan the whole index even though, as a human, you could see a better way. The reason given, and I have some sympathy with this, is that spotting that only min and max supported by an index had been asked for and there was a more efficient way to do this would have to be added to the CBO.
Where we differed is they thought this would be a rare event, I thought it would be a more common event and worth their effort.

Never mind, you can do it yourself {I won’t line this up like the previous examples, they need too much editing to fit!}:

– now together
select max(id) from test_1
union
select min(id) from test_1;

MAX(ID)
———-
1
99999

Execution Plan
————————————————————–
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
————————————————————-
| 0 | SELECT STATEMENT | | 2 | 8 | | 601 (53)| 00:00:08 |
| 1 | SORT UNIQUE | | 2 | 8 | 4753K| 601 (53)| 00:00:08 |
| 2 | UNION-ALL | | | | | | |
| 3 | SORT AGGREGATE | | 1 | 4 | | 301 (5)| 00:00:04 |
| 4 | INDEX FULL SCAN (MIN/MAX)| T_PK | 99999 | 390K| | 2 (0)| 00:00:01 |
| 5 | SORT AGGREGATE | | 1 | 4 | | 301 (5)| 00:00:04 |
| 6 | INDEX FULL SCAN (MIN/MAX)| T_PK | 99999 | 390K| | 2 (0)| 00:00:01 |
————————————————————————-

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

There are times when a human beats the computer. 4 consistent gets. I’ve played with this a little and on my test system the select min(id),max(id) version takes around 4ms. The union method takes 0-1ms.
I see a couple of oddities.

- Firstly, the plan has 3 sort operations, but the statistics show only 1 sort. I am not fully sure what is going on, it might be that the plan is a little duff, it might be that at execution time Oracle realises it really does have only 1 record to sort and so does not. {I’ll buy the person who tells me the cause of the remaining sort and how to get rid of it a pint at the next UKOUG – if you do so before I blog about it}

- Secondly, if you look at the plan, the CBO estimates a lot of effort for the sorts and thus the total cost of the statement is high relatively high, 601. I need to look into that.

I’ve used this trick of splitting “select min,max” into two statements unioned together several times over the years and I find it very useful. Below I go into a few more oddities, but I know I ramble on so feel free to stop now.

In previous postings I have looked at the fastest way to select the number of records in a table. Well, if your tables have a surrogate primary key consisting of an ascending number (generated from a sequence and a very common occurrence) and you know there are few or no skipped values, you can use the above trick to get the MIN(ID) and MAX(ID) and thus the difference is the number of rows. You can do it in one statement, as shown below:

-- diff between min and max in one statement 
select a.result-b.result id_number
from (select max(id)+1 result,1 eric from test_1) a
   ,(select min(id) result,1 eric from test_1) b
where a.eric=b.eric
/

ID_NUMBER
-----------------
            99999


Execution Plan
-------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |    32 |     4   (0)|
|   1 |  NESTED LOOPS                |      |     1 |    32 |     4   (0)|
|   2 |   VIEW                       |      |     1 |    16 |     2   (0)|
|   3 |    SORT AGGREGATE            |      |     1 |     4 |            |
|   4 |     INDEX FULL SCAN (MIN/MAX)| T_PK | 99999 |   390K|     2   (0)|
|*  5 |   VIEW                       |      |     1 |    16 |     2   (0)|
|   6 |    SORT AGGREGATE            |      |     1 |     4 |            |
|   7 |     INDEX FULL SCAN (MIN/MAX)| T_PK | 99999 |   390K|     2   (0)|
------------------------------------------------------

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

With this version the CBO recognises that the statement cost is low (look, cost 4), the odd sorts are still there in the plan on a single row but the statement takes 4 consistent gets.

I used to think, wrongly, that Oracle would only use the INDEX FULL SCAN (MIN/MAX) for getting the absolute minimum and maximum values. I don’t know why I thought this but check out the next two examples:

select max(id) from test_1
where id7500;

MAX(ID)
———-
99999

Execution Plan
———————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
———————————————————-
| 0 | SELECT STATEMENT | | 1 | 4 | 2 (0)|
| 1 | SORT AGGREGATE | | 1 | 4 | |
| 2 | FIRST ROW | | 92500 | 361K| 2 (0)|
|* 3 | INDEX RANGE SCAN (MIN/MAX)| T_PK | 92500 | 361K| 2 (0)|
———————————————————-

Statistics
———————————————————-
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads

As you can see, Oracle knows it can get the Max (and min) value using the INDEX RANGE SCAN MIN/MAX efficiently, even for the second case where I am asking for the max value ABOVE a stated point.
I half expected oracle to do a range scan from the value I gave it. But it has the sense to know that, logically, the maximum value below X can be found by looking for X and then it is the record before that (or where it would be if it does not exist). The maximum above X is the maximum. In restrospect, I was asking a daft question there :-)

I could play with this longer, but enough for now.

Counting the Cost #4 – The speedy way May 26, 2009

Posted by mwidlake in performance.
Tags: , ,
7 comments

This is my fourth look at counting the number of rows from a table. Here I will look at even faster ways of finding the number of records in a table, which are particularly useful for tables with million and billions of rows. You can get a good estimate in much less than a second for a table of any size.

So far I have shown with this posting here that the myths and arguments about whether count(*), count(1), count(pk) etc are immaterial with modern version of Oracle – they all take the same effort by the CBO.
This next post looks at how indexes are used when you count a single column in a table and why it is not always a good idea.
The third post shows how Oracle will use the smallest index it can to select count(*) and in particular how a bitmap index is so useful for this.

To count the number of rows you have in your table for sure, you have little choice but to scan either the table or an index on a mandatory column (or columns). You may well not have bitmap indexes on your biggest tables (bitmap indexes do have some major drawbacks) so the best the CBO can usually do is scan the Primary Key index (it is usually the smallest index on mandatory columns). On a table with 10 million rows then this index could easily hold 20,000 leaf blocks (8k block size) and could take dozens of seconds to minutes to scan, depending on how powerful your system is and how much of the index is in the block buffer cache.

{This is my script for the below tests but it uses a table, PERSON that it does not create. You can check out the syntax of things though.}

This is select count(*) on our small, 20,000 row table.

test102>select count(*) from test_1;
COUNT(*)
———-
19999
Elapsed: 00:00:00.01

Execution Plan
———————————————————-
Plan hash value: 454320086
———————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
———————————————————————-
| 0 | SELECT STATEMENT | | 1 | 11 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| T_PK | 19999 | 11 (0)| 00:00:01 |
———————————————————————-

Statistics
———————————————————-
0 recursive calls
0 db block gets
44 consistent gets
0 physical reads

For a table with more than a few thousand records, selecting the number of rows (num_rows as held in the data dictionary against ALL_TABLES is quicker:

– a different way to get the info
select table_name,num_rows from all_tables
where owner=USER and table_name=’TEST_1′;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
TEST_1                              19999

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------
Plan hash value: 711593612

----------------------------------------------------
| Id  | Operation                             | Name| Rows |Bytes|Cost
----------------------------------------------------
|   0 | SELECT STATEMENT                 |       |  1 |  180 |    9
|*  1 |  FILTER                                    |       |     |         |
|*  2 |   HASH JOIN                             |       |  1 |  180 |    9
|   3 |    MERGE JOIN CARTESIAN         |       |  1 |  167 |    8
|   4 |     NESTED LOOPS OUTER          |       |  1 |  112 |    8
|   5 |      NESTED LOOPS OUTER         |       |  1 |  107 |    8
|   6 |       NESTED LOOPS OUTER        |       |  1 |  104 |    7
|   7 |        NESTED LOOPS OUTER       |       |  1 |   96 |    6
|   8 |         NESTED LOOPS                 |       |  1 |   85 |    5
|   9 |          NESTED LOOPS                |       |  1 |   82 |    4
|  10 |           NESTED LOOPS              |       |  1 |   50 |    3
|  11 |            TABLE ACCESS BY INDEX ROWID| USER$ |1|14 |1
|* 12 |             INDEX UNIQUE SCAN              | I_USER1|1|     |0
|* 13 |            TABLE ACCESS BY INDEX ROWID| OBJ$   |1| 36 |2
|* 14 |             INDEX RANGE SCAN                | I_OBJ2 |1|      |1
|* 15 |           TABLE ACCESS CLUSTER           | TAB$   |1|  32 |1
|* 30 |    INDEX RANGE SCAN             | I_OBJAUTH|  1 |   7 |   2
|* 31 |    FIXED TABLE FULL               | X$KZSRO  |  2 |  26 |  0
|* 32 |     FIXED TABLE FULL              | X$KZSPR  |  1 |  26 |   0
<snip>
Statistics
------------------------------------------------------
224  recursive calls
0     db block gets
60   consistent gets
0     physical reads
0     redo size
480  bytes sent via SQL*Net to client
381  bytes received via SQL*Net from client
2     SQL*Net roundtrips to/from client
8     sorts (memory)
0     sorts (disk)
1     rows processed

Now, have I lied? You can see from the above that it took 0.04 seconds to select the num_rows from the ALL_TABLES view. I trimmed the plan but you can see that it is looking at some very odd things, which are internal objects in the data dictionary (you may not see this if you run my script, I use a DBA privileged account and so I see the messy Oracle internals). I also trimmed the plan, which was 32 lines long. {It is very interesting to look at Oracle’s internals this way, but not now. }

This was the first execution and Oracle has to parse what looks like a simple statement but, because it is on a view(ALL_TABLES) which sits on top of many internal objects, the plan is quite complex. The parse effort is quite high. However, the predicted execution cost is low (9) and second run shows that the number of buffer gets is low:

– again without the extensive plan output
set autotrace on stat
select table_name,num_rows from all_tables
where owner=USER and table_name=’TEST_1′;

TABLE_NAME NUM_ROWS
—————————— ———-
TEST_1 19999

Elapsed: 00:00:00.00

Statistics
———————————————————-
0 recursive calls
0 db block gets
22 consistent gets
0 physical reads

So the number of consistent_gets goes down to 22 and the time it takes to run to below 1ms. That compares to a cost of 11 and consistent_gets of 44 for the primary key scan.
This cost of checking the data dictionary will be pretty much the same for any normal table, no matter how large.

Here I test another test table of mine (not created by the test script, I am afraid). I show the second run of selecting count(*) and also selecting num_rows from all_tables.

– Now to look at a bigger table
set autotrace on
select count(*) from person;

COUNT(*)
———-
82563
Elapsed: 00:00:00.01

Execution Plan
———————————————————-
Plan hash value: 1755611177
————————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 44 (5)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| PERS_ID | 82563 | 44 (5)| 00:00:01 |
————————————————————————-

Statistics
———————————————————-
0 recursive calls
0 db block gets
191 consistent gets
0 physical reads

select table_name,num_rows from all_tables
where owner=USER and table_name=’PERSON';

TABLE_NAME NUM_ROWS
—————————— ———-
PERSON 82563
Elapsed: 00:00:00.00

Execution Plan
———————————————————-
Plan hash value: 711593612

—————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost
—————————————————————–
| 0 | SELECT STATEMENT | | 1 | 180 | 9

Statistics
———————————————————-
0 recursive calls
0 db block gets
22 consistent gets
0 physical reads

Table PERSON is bigger, the CBO estimates the cost of a scan on the index as 44 and the run takes 191 consistent gets. The select from ALL_TABLES has a cost of 9 and 22 consistent gets – the same as for the smaller table.

Isn’t that great?

Well, maybe. The number of rows we are getting is the count of rows for that table the last time it was analysed. On version 9 of Oracle, that is probably when the DBA last scheduled an analyze or dbms_stats gather statement to run against the table {either directly or via a schema or whole database gather}. On Oracle 10 or 11 with the auto stats job, it should be within 10% as Oracle automatically re-gathers stats for any table changed by 10% or more since the last analyze, every night/over the weekend.
The accuracy of the count will be down to when it last ran, what the sample size was and how active the table is.

The question is, do you actually need to know the EXACT number of records, or just the number to a reasonable level of precision? Is the level of precision of the last gather OK? You can check the column LAST_ANALYZED on ALL_TABLES to see when that was. Sometimes it is accurate enough.

As a quick aside, if you have wide enough privileges (DBA or SELECT ANY DICTIONARY) and the time to play with the internal objects, you can construct a little script like the below which is even faster, as it only gets what you want and ignores table access issues {second iteration shown}:

set autotrace on
-- If you have the right account privs, you can look direct
-- into the data dictionary.
select o.name,o.owner#
      ,t.obj#,t.rowcnt
from sys.obj$ o
    ,sys.tab$	   t
    ,sys.user$    u
where o.name = 'PERSON'
and o.obj#   = t.obj#
and u.name   = 'TRAINER'
and u.user#  =o.owner#

NAME OWNER# OBJ# ROWCNT
—————————— ———- ———- ———-
PERSON 62 52732 82563

Elapsed: 00:00:00.00

Execution Plan
———————————————————-
Plan hash value: 3546182522
——————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————
| 0 | SELECT STATEMENT | | 1 | 55 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 55 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 47 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 14 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 33 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | I_OBJ2 | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS CLUSTER | TAB$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
—————————————————————

Statistics
———————————————————-
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads

So, looking at the data in the data dictionary is fast {and it should be, this is some of the basic information the CBO looks at to decide on its execution path and so Oracle Corp make it as fast as it reasonably can}. But it is potentially pretty inaccurate.

Do you remember me mentioning that Oracle 10 upwards {and Oracle 9 if your turn on monitoring on a table and call dbms_stats correctly} collects stats on a table when it changes by more than 10%?

How does oracle know?

Oracle keeps track of changes to tables. It seems to me to be pretty damned accurate in most circumstances {I’ll elaborate in a bit}.

The view to look at is ALL_TAB_MODIFCATIONS or DBA_TAB_MODIFICATIONS.

desc all_tab_modifications
 Name                                 Null?    Type
 ---------------- -------------------------------
 TABLE_OWNER                           VARCHAR2(30)
 TABLE_NAME                             VARCHAR2(30)
 PARTITION_NAME                        VARCHAR2(30)
 SUBPARTITION_NAME                     VARCHAR2(30)
 INSERTS                                   NUMBER
 UPDATES                                   NUMBER
 DELETES                                   NUMBER
 TIMESTAMP                               DATE
 TRUNCATED                               VARCHAR2(3)
 DROP_SEGMENTS                        NUMBER

As you can see, it shows the number of inserts, updates and deletes to the table. A few warnings:

- It does not record changes where you avoided the SQL layer, ie did direct path loads, used the +append hint or many other tricks to speed up processing by shoving a great chunk of data into the table quickly. All normal inserts/updates/deletes get caught though.

- The data is flushed from memory to the data dictionary “regularly”. Every 3 hours in Oracle 9, every 15 minutes in Oracle 10.1 and it seems only when a dbms_stats.gather statement is run on 10.2. I am afraid I have not yet checked on 11.

But you can {if you have the privs} flush the data down yourself, and it seems very quick:

test102>– You need to flush the stats down
test102>exec dbms_stats.flush_database_monitoring_info

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.23

And now a script for you to get the information. If you look {here} there is my sql*plus script I use all the time to do this. Feel free to steal and use it. I’d appreciate it if you left my initials in the top comment though:

select dbta.owner||'.'||dbta.table_name 	  tab_name
     ,dbta.num_rows			         anlyzd_rows
     ,to_char(dbta.last_analyzed,'yymmdd hh24:mi:ss')  last_anlzd
     ,nvl(dbta.num_rows,0)+nvl(dtm.inserts,0)
      -nvl(dtm.deletes,0) 		        tot_rows
  ,nvl(dtm.inserts,0)+nvl(dtm.deletes,0)+nvl(dtm.updates,0) chngs
  ,(nvl(dtm.inserts,0)+nvl(dtm.deletes,0)+nvl(dtm.updates,0))
    /greatest(nvl(dbta.num_rows,0),1)	   pct_c
  ,dtm.truncated		          trn
from	     dba_tables 	       dbta
-- replace below with all_tab_modifications if you need
left outer join sys.dba_tab_modifications dtm
   on  dbta.owner	      = dtm.table_owner
   and dbta.table_name    = dtm.table_name
   and dtm.partition_name is null
where dbta.table_name ='PERSON'
and dbta.owner	   ='TRAINER'

TAB_NAME ANLYZD_ROWS LAST_ANLZD TOT_ROWS CHNGS PCT_C
——————————————————————-
TRAINER.PERSON 82,563 090517 18:42:22 82,563 0 .000

1 row selected.

Elapsed: 00:00:00.06

Execution Plan
———————————————————-
Plan hash value: 1769260204

————————————————————
| Id | Operation | Name | Rows | Bytes | Cost
———————————————————–
0 | SELECT STATEMENT | | 3 | 396 | 22

Statistics
———————————————————-
224 recursive calls
0 db block gets
98 consistent gets
0 physical reads
0 redo size
796 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
1 rows processed

OK, 0 changes, not so informative? Well, it is, it means NUM_ROWS in the data dictionary is up to date and if you check back for the count(*) on that table, it is indeed 82563. But, just for you, I am going to damage my test PERSON table, just to show you.
I added about 27500 rows and deleted about 1250. I committed the changes and re-ran the script

@tab_count
exec dbms_stats.flush_database_monitoring_info

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

Enter value for tab_name: person
old 15: where dbta.table_name like upper(nvl(‘&Tab_name’,’WHOOPS’))
new 15: where dbta.table_name like upper(nvl(‘person’,’WHOOPS’))
Any Key>
TAB_NAME ANLYZD_ROWS LAST_ANLZD TOT_ROWS CHNGS PCT_C
————————————————————— —
TRAINER.PERSON 82,563 090517 18:42:22 108,795 28,730 .348

1 row selected.

Elapsed: 00:00:00.95

Execution Plan
———————————————————-
Plan hash value: 4225397431
——————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost
————————————————————————–
| 0 | SELECT STATEMENT | | 2 | 478 | 63

Statistics
———————————————————-
0 recursive calls
0 db block gets
71 consistent gets
0 physical reads

I forgot to turn off the pause, so the statement took almost a second! But the cost and consistent gets show it is a lightweight statement. How accurate is it?

select count(*) from person

COUNT(*)
———-
108795

1 row selected.

Elapsed: 00:00:00.56

Execution Plan
———————————————————-
Plan hash value: 1755611177
————————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 44 (5)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| PERS_ID | 82563 | 44 (5)| 00:00:01 |
————————————————————————-
Statistics
———————————————————-
0 recursive calls
0 db block gets
250 consistent gets
0 physical reads

The count(*) has a cost of 44 and talks 250 consistent gets. The “estimate” from using SYS.DBA_TAB_MODIFICATIONS is 108795, which matches the count(*).

I use SYS.DBA_TAB_MODIFICATIONS a lot now. After all, the fastest way to do something is to not do it, so I don’t count large tables, I get Oracle to tell me to the best of its knowledge with what it has already stored.

It will not work properly if you do direct inserts, but I have never really seen the information in DBA_TAB_MODIFICIATIONS be out by much at all if you are dealing with normal DML. There is no need to constantly gather stats to keep the information current, it is a lot, lot faster than select coun(*).

So, next time your boss wants to know how many records are in the ORDER_ITEMS table, don’t wait an hour for a count(*) to finish, look at ALL/DBA_TAB_MODIFICATONS. OK, it might be out by a few, but then depending on if you had your cup of tea before or after you kicked off the count(*), would that give a more “accurate” count to email to your boss?

Counting the Cost #3 May 22, 2009

Posted by mwidlake in performance.
Tags: , ,
2 comments

I’m sorry for the delay, I will now continue to look at the fastest way to count the rows in a table and what we can learn from it.

Today I will show you how you could potentially add a bitmap index to greatly speed up the count.

I’ve got a new test script that you can run that does most of the steps below. You can get it from here.

See this first posting for the details of setting up the table and showing that select count(*), count(1) and count(pk) all take the same amount of time and do the same work internally and it probably matters not one jot which you use.

See the second posting for what happens when you select count (column_name) from a table and why I think it is a poor idea to use it to count the number of rows in a table.

Previously I had shown that Oracle chooses to use primary key index to count the number of rows in a table – which has been the fastest way so far.

Oracle will actually use the smallest index it can to count the number of rows. Generally, the smallest index you can create is a bitmap index, as it is stored in such a compact manner. Just take my word on it.

OK, I suppose I better give some supporting evidence rather than just my word.
This is the table:

Name                             Null?    Type
 --------------------------------------- -------- 
 ID                             NOT NULL NUMBER
 NUM_1                                     NUMBER
 ID_2                                        VARCHAR2(41)
 NUM_2                                     NUMBER
 NUM_3                                     NUMBER
 NUM_4                                     NUMBER
 VC_1                                        VARCHAR2(50)
 VC_2                                        VARCHAR2(50)
 VC_3                                        VARCHAR2(250)

These are the sizes of the table and indexes on the table. The column used/leaf takes into account empty blocks in a table and just the leaf blocks in an index, which are the blocks scanned in a full scan:

                                            USED/ 
SEGMENT     TYPE     BLOCKS  LEAF  SIZE_K
--------- -------- ------- ----- ------
TEST_1       TABLE        1152 1079  9,216
T_PK           INDEX            48    41      384 Primary key on ID
T_UQ           INDEX            56    47      448 Unique idx on ID2
T_NONUQ     INDEX           48    39      384 nonunique on NUM
T_SPARSE     INDEX              8     4        64 on NUM4, 95% null

I am now going to create a simple bitmap index on NUM_3, a non-mandatory column but it does have a non-null value in every row.

Now to do something that can make counts faster, add a nice, compact bitmap index.

create bitmap index t_bm on test_1(num_3);
Index created.
Elapsed: 00:00:00.04

test102>analyze table test_1 compute statistics;
Table analyzed.
Elapsed: 00:00:00.29

I’ve gathered stats again so that the CBO knows about the index. Let’s start with a count of the column. We would expect that to use the new index.

– count on the indexed column
select count(num_3) from test_1;

COUNT(NUM_3)
————
19999

Execution Plan
———————————————————-
Plan hash value: 864032431
————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 2 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
| 2 | BITMAP CONVERSION TO ROWIDS | | 19999 | 39998 | 5 (0)| 00:00:01 |
| 3 | BITMAP INDEX FAST FULL SCAN| T_BM | | | | |
————————————————————————————–

Statistics
———————————————————-
1 recursive calls
0 db block gets
10 consistent gets
0 physical reads

As you can see, the CBO is using the new index and the cost has dropped down to 5 {please note, this is the estimated cost, but it is accurate in this case. I might do a blog entry on testing changes in very quick statements}. Consistent Gets is down to 10.

Let’s compare that to what was previously the fastest way to count the records, count(*) {or count(1) {or count(pk)}}, which had a cost of 11 and 46 consistent gets.

COUNT(*)
———-
19999

———————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
———————————————————————-
| 0 | SELECT STATEMENT | | 1 | 11 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| T_PK | 19999 | 11 (0)| 00:00:01 |
———————————————————————-

Statistics
———————————————————-
0 recursive calls
0 db block gets
46 consistent gets
0 physical reads

How big is the new index?

SEGMENT    TYPE      BLOCKS  LEAF  SIZE_K
---------- ------- ------- ----- --------
T_BM          INDEX           16         5       128
T_PK           INDEX           48       41       384 

I’ll run the count of NUM_3 a second time to get rid of any parse overhead.

select count(num_3) from test_1;

COUNT(NUM_3)
————
19999

Execution Plan
Plan hash value: 864032431
————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 2 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
| 2 | BITMAP CONVERSION TO ROWIDS | | 19999 | 39998 | 5 (0)| 00:00:01 |
| 3 | BITMAP INDEX FAST FULL SCAN| T_BM | | | | |
————————————————————————————–

Statistics
———————————————————-
0 recursive calls
0 db block gets
10 consistent gets

The bitmap index has 5 leaf blocks, a cost of 5 and there are 10 consistent gets.
The primary key has 41 leaf blocks, a cost of 11 and 46 consistent gets.

{Don’t worry too much about the relationship between blocks, costs and consistent gets, I’ll cover that in a later posting}

Bitmap indexes are unlike normal Oracle indexes in that they also index nulls. Thus a count (*) on the table can be answered by the CBO by using the smaller bitmap index. Here is the proof (second run only shown)

– traditional count(*)
select count(*) from test_1;

COUNT(*)
———-
19999

Execution Plan
———————————————————-
Plan hash value: 1066692852
——————————————————————————
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 1 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | BITMAP CONVERSION COUNT | | 19999 | 5 (0)| 00:00:01 |
| 3 | BITMAP INDEX FAST FULL SCAN| T_BM | | | |
——————————————————————————

Statistics
———————————————————-
0 recursive calls
0 db block gets
10 consistent gets

We see the same stats and cost, but look carefully and you will see that the Plan Hash Value is different and the plan is slightly different. The BITMAP CONVERSION TO ROWIDS has become a BITMAP CONVERSION COUNT. This is because, when we said count(NUM_3) we were asking for a count of NON-NULL values for column NUM_3, so the CBO had to handle that. Count(*) just wants to know how many rows there are.
{I am not sure exactly is being done by the conversion to rowids in this case as the CBO has no need to visit the table. It should be able to just ignore the bitmap for null values. Maybe it does so but this is how Explain Plan reports the activity. Maybe I should ask Jonathan Lewis :-)}

Here are just two quick proofs that select count (PK) and count (1) will both use this smaller index:

– count on pk column
select count(id) from test_1;

COUNT(ID)
———-
19999

Execution Plan
———————————————————-
Plan hash value: 1066692852
——————————————————————————
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 1 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | BITMAP CONVERSION COUNT | | 19999 | 5 (0)| 00:00:01 |
| 3 | BITMAP INDEX FAST FULL SCAN| T_BM | | | |
——————————————————————————

Statistics
———————————————————-
1 recursive calls
0 db block gets
10 consistent gets

– count on a static value
select count(1) from test_1;

COUNT(1)
———-
19999

Execution Plan
———————————————————-
Plan hash value: 1066692852
——————————————————————————
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 1 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | BITMAP CONVERSION COUNT | | 19999 | 5 (0)| 00:00:01 |
| 3 | BITMAP INDEX FAST FULL SCAN| T_BM | | | |
——————————————————————————

Statistics
———————————————————-
1 recursive calls
0 db block gets
10 consistent gets

Finally for this post (and I know, I do go on a bit) I will show that the CBO will use the bitmap to count the number of values in a column that it knows is mandatory – presumably someone in the Oracle Optimisation team recognised that such a count is counting the number of rows, so just count the number of rows the quickest way possible (which is to use the smallest index which is on a mandatory column or is a bitmap index, I suspect).

First, a count on a column that is NOT mandatory, which has a unique index on it. The CBO uses the supporting index:-

–count on a non-pk unique column
select count(id_2) from test_1;

COUNT(ID_2)
———–
19999

Execution Plan
———————————————————-
Plan hash value: 341132572
——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 1 | 6 | 12 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
| 2 | INDEX FAST FULL SCAN| T_UQ | 19999 | 117K| 12 (0)| 00:00:01 |
——————————————————————————

Statistics
———————————————————-
0 recursive calls
0 db block gets
52 consistent gets
0 physical reads

Cost of 12, 52 consistent gets. Now let us make the column mandatory, analyze the table and select count (NUM_2) again {I show the second run only, as the first run has the parse overhead}

alter table test_1
modify num_2 not null;

Table altered.

test102>analyze table test_1 compute statistics;

Table analyzed.

select count(num_2) from test_1;

COUNT(NUM_2)
————
19999

Execution Plan
———————————————————-
Plan hash value: 1066692852
——————————————————————————
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 1 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | BITMAP CONVERSION COUNT | | 19999 | 5 (0)| 00:00:01 |
| 3 | BITMAP INDEX FAST FULL SCAN| T_BM | | | |
——————————————————————————

Statistics
———————————————————-
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads

And there we go, a cost of 5, consistent gets of 10 and the same plan/plan hash value as count(*).
I still would not select count(column) as a general rule UNLESS I wanted to count non-null values of that column, but the CBO will step in and use the most efficient way it can to count the rows in a table, if it can identify that counting all the rows is what you really want to do.

I’ve managed to write a lot and learn a lot from a very, very simple statement!

My next post will be on even faster ways of {sort of} getting the number of rows in a table.

Counting the Cost #2 May 19, 2009

Posted by mwidlake in performance.
Tags: , ,
2 comments

Let’s continue where I left off. I had shown that select count(*), count(1), count(pk) were equivalent under Oracle 10 onwards (and I am pretty sure Oracle 9 and 11, but you would need to check).

So, looking at this simple select count(whatever) from a table, what else can we establish? Please look back at the prior posting to see the test table and indexes I created and basic environment info, but to summarise:-

select version,archiver,database_status from v$instance;

VERSION           ARCHIVE DATABASE_STATUS
----------------- ------- -----------------
10.2.0.1.0        STARTED ACTIVE

select substr(name,1,30) name,substr(value,1,20) value
from v$parameter
where name in ('db_block_size','compatible','cpu_count'
              ,'db_file_multiblock_read_count'
             ,'optimizer_mode','sga_target','sort_area_size')
order by name/

NAME                           VALUE
------------------------------ --------------------
compatible                     10.2.0.1.0
cpu_count                      1
db_block_size                  8192
db_file_multiblock_read_count  16
optimizer_mode                 ALL_ROWS
sga_target                     612368384
sort_area_size                 65536
 create table test_1
as select rownum  id
         ,mod(rownum,10)  num_1
         ,'A'||to_char(rownum+1000) id_2
         ,trunc(dbms_random.value(1,100)) num_2
         ,mod(rownum,5)  num_3
         ,decode(mod(rownum,10)
                 ,0,trunc(dbms_random.value(1,30))
                 ,null) num_4
        ,lpad('A',50,'A') vc_1
        ,lpad('B',50,'B') vc_2
        ,lpad('C',250,'C') vc_3
         from dba_objects
         where rownum < 20000;

desc test_1
 Name                                            Null?    Type
 ---------------------------- ------- --------------
 ID                                         NOT NULL NUMBER
 NUM_1                                               NUMBER
 ID_2                                                VARCHAR2(41)
 NUM_2                                               NUMBER
 NUM_3                                               NUMBER
 NUM_4                                               NUMBER
 VC_1                                                VARCHAR2(50)
 VC_2                                                VARCHAR2(50)
 VC_3                                                VARCHAR2(250)


So, count(*), count((pk_column) and count (1) had proved to be equivalent. This was the stable plan and stats from them (all of them):

Execution Plan
———————————————————-
Plan hash value: 454320086
———————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
———————————————————————-
| 0 | SELECT STATEMENT | | 1 | 11 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| T_PK | 19999 | 11 (0)| 00:00:01 |
———————————————————————-

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

Let us see what happens when we count a column that is indexed uniquely but is not set as a not null column:

– count on a none PK unique column
— (but with no unique constraint or set to not-null
select count(id_2) from test_1;

COUNT(ID_2)
———–
19999

Execution Plan
———————————————————-
Plan hash value: 341132572
——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 1 | 6 | 12 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
| 2 | INDEX FAST FULL SCAN| T_UQ | 19999 | 117K| 12 (0)| 00:00:01 |
——————————————————————————

Statistics
———————————————————-
1 recursive calls
0 db block gets
52 consistent gets
0 physical reads

The Execution plan has a new hash plan value and looks at a new index.
This is because we have told Oracle to count the new column. What we are actually asking is “give me a count of all rows with a value set for the column ID_2.
The column is not mandatory, ie set to not-null (check the tables description above), there could be many null values set for this column.
(Many null values do not break the UNIQUE constraint. Null, in Oracle terms, is a value that could be anything. Anything does not match anything. Thus you can have 0, 1 or many null values for a non-mandatory but unique column. A Primary key column, incidentally, MUST be mandatory.).
A count on the column will count all non-null values. So it uses the index on that column.
Let’s run it again, to get stats that do not include any parse overhead.:

select count(id_2) from test_1;

COUNT(ID_2)
———–
19999

Execution Plan
———————————————————-
Plan hash value: 341132572
——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 1 | 6 | 12 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
| 2 | INDEX FAST FULL SCAN| T_UQ | 19999 | 117K| 12 (0)| 00:00:01 |
——————————————————————————

Statistics
———————————————————-
0 recursive calls
0 db block gets
52 consistent gets

52 consistent gets, which is more than for the scans of index T_PK. The cost is also one higher, 12 as opposed to 11. This shows that the Cost Based Optimiser is correctly identifying that count(ID_2) will take more effort than count(*), and this is borne out by the number of consistent gets.
Index T_UQ is a larger index (I did check). Oracle can’t use the smaller primary key index T_PK as this index does not hold information on column ID_2.

Now, let us select count of a column that is not unique but indexed. Column NUM_2. Remember, what we are actually asking is “give me a count of all rows with a value set for the column NUM_2.
What we see is:

– count on a non-unique indexed column
select count(num_2) from test_1;

COUNT(NUM_2)
————
19999

Execution Plan
———————————————————-
Plan hash value: 225025118
———————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————
| 0 | SELECT STATEMENT | | 1 | 2 | 10 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
| 2 | INDEX FAST FULL SCAN| T_NONUQ | 19999 | 39998 | 10 (0)| 00:00:01 |
———————————————————————————

Statistics
———————————————————-
1 recursive calls
0 db block gets
44 consistent gets
0 physical reads

There is a new plan hash value and plan. Again, it needs to check for rows with the column specified set to a value, and there is an index to support this, T_NONUQ and it does a fast scan of it. Second run:

select count(num_2) from test_1;

COUNT(NUM_2)
————
19999

Execution Plan
———————————————————-
Plan hash value: 225025118
———————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————
| 0 | SELECT STATEMENT | | 1 | 2 | 10 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
| 2 | INDEX FAST FULL SCAN| T_NONUQ | 19999 | 39998 | 10 (0)| 00:00:01 |
———————————————————————————

Statistics
———————————————————-
0 recursive calls
0 db block gets
44 consistent gets
0 physical reads

44 consistent gets and a cost of 10. Hang on, that is less effort than count(*), count(1) or count(id). It is a smaller index. Why did the CBO not use this index to get the count of records in the table? Because, we just asked Oracle to count the rows with a value set for the NUM_2 column. The column is not mandatory, Oracle cannot guarantee there are no null values, so a count of the entries in the index would not be guaranteed to be the number of records in the table.

If you know a little more about Oracle, you may wonder if Oracle could check the column stats to see how many null values there are and, if there are none, us that smaller index for the count(*). But null values may have been created since the stats were gathered. If the CBO tried to use this trick, it could easily give the wrong answer, and then how would you feel about that licence fee?

As a further aside, I once came across a claim that counting a column with a unique index would be faster than counting a column with a non-unique index. They could have been right, depending on the size of the indexes, but they were not doing what they thought they were. They were counting the number of records with a value set for that column.

For this reason I would shy away from using count(column) to count the records in a table. If the column is mandatory, you will be counting the number of records, but your code is saying “count how often that column has a value”. As a pedant I feel it’s just wrong. As an experienced DBA who has seen mandatory columns made non-mandatory in live systems, I feel it is also slightly dangerous. Having also seen indexes, even unique indexes, be dropped from live systems, the performance impact could be significant, as you will soon see.

OK, I have been careful so far to count only columns with indexes on them. Let us count a column than has no index on it.

– count on an unindexed column
select count(vc_1) from test_1;

COUNT(VC_1)
———–
19999

Execution Plan
———————————————————-
Plan hash value: 479375906
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 50 | 240 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 50 | | |
| 2 | TABLE ACCESS FULL| TEST_1 | 19999 | 976K| 240 (1)| 00:00:03 |
—————————————————————————–

Statistics
———————————————————-
1 recursive calls
0 db block gets
1058 consistent gets
0 physical reads

There is a new plan hash value and plan. Now a full table scan is being used and the cost has shot up. The CBO has no index holding information on this column, so it has to scan the whole table. Let’s do the second run:

select count(vc_1) from test_1;

COUNT(VC_1)
———–
19999

Execution Plan
———————————————————-
Plan hash value: 479375906
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 50 | 240 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 50 | | |
| 2 | TABLE ACCESS FULL| TEST_1 | 19999 | 976K| 240 (1)| 00:00:03 |
—————————————————————————–

Statistics
———————————————————-
0 recursive calls
0 db block gets
1058 consistent gets
0 physical reads

A cost of 240 (the estimated effort) and buffer gets of 1058 (the actual effort). That is a lot more than scanning an index that is considerably smaller than the table. And now you can see why dropping an index that was on a column someone was counting (rightly or wrongly) can have an impact on performance.

Finally for now, let’s count a column that is sparsely populated, column NUM_4. Oh, I have only included the second run, so any parse overhead is gone:

– select on a sparse, indexed column
select count(num_4) from test_1;

COUNT(NUM_4)
————
1999

Execution Plan
———————————————————-
Plan hash value: 2410540583
———————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 2 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
| 2 | INDEX FAST FULL SCAN| T_SPARSE | 19999 | 39998 | 3 (0)| 00:00:01 |
———————————————————————————-

Statistics
———————————————————-
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads

A new plan hash value and a new plan. The CBO is using the index on the sparse column. The cost is 3 and the consistent gets only 7, a lot lower than we have seen before.. And the number of records is 1,999, not 19,999. We have counted the number of records with a value set for NUM_4. It needs so many fewer consistent gets as the index is a lot smaller.

This can be used as a performance trick, if you regularly want to count a small number of records in a large table, for example active orders in an orders table, which holds orders going back years and 99.9% of them are complete:

Have a column called ACTIVE, a CHAR(1) column.
Set it to a value, eg ‘Y’, when the record is created.
Set it to null when the order is complete.
Re-set it to ‘Y’ if the order ever becomes valid again.

A count on the column will use the very small index (helped by the fact the column is a CHAR(1)) to count the records. This little index will also allow the active records to be identified in other select queries also.

Don’t forget though, that indexes on the table has to be maintained by oracle and it does take up some space, so there is a cost to having it. Also, I have not mentioned what happens to that index over time… That might be for a later posting, but if you look at Richard Footes blog, you may well find something about such indexes growing over time.

My next post on this subject will consider if there is a way to count the records in a table even quicker than count(*).

Counting the Costs May 17, 2009

Posted by mwidlake in performance.
Tags: , ,
10 comments

Anyone who has been around the Oracle DBA or Developer world for a while is sure to have come across people debating(*) the fastest way of selecting the number of  rows from a table. Different suggestions have been that count(*) is not as fast as count(pk_column), count(rowid) or count(1).

(*) by debating I mean arguing, sometimes getting angry and, on one memorable occasion, pretty much asking each other to step outside to resolve this matter Man-to-Man (why do women not feel the need for this final step? Just more sensible I guess).

Bottom line, it makes no difference whether you select count(*), count(1), count(pk) on Oracle 10 (and, I am sure, Oracle 9). However, that is an unsupported claim and you should not believe me yet. Any web site or blog making a solid statement about how Oracle works needs to provide some proof, otherwise it is just opinion.

What I find more interesting is that some people seem happy to spend more time debating the question than doing a few tests to find out for sure. Also, proving the answer to such a simple question can actually lead to some more interesting insights.

I’ve followed the lead of my betters and I have created a script to run through all the below tests. If I can bend my very new blogging skills to it, the sql*plus script can be downloaded from this link.

Below follows my proof and some observations on the way.

First I better show what version of Oracle I am using and the basic set up as far as sql performance goes (I’ve trimmed the output to remove number of rows returned, timing etc where it is not significant)

test102>– basic setup info

test102>select version,archiver,database_status from v$instance; 

VERSION           ARCHIVE DATABASE_STATUS
—————– ——- —————–

  • 10.2.0.1.0        STARTED ACTIVE
  •  
    test102>select substr(name,1,30) name,substr(value,1,20) value
      2  from v$parameter
      3  where name in (‘db_block_size’,’compatible’,’cpu_count’,’db_file_multiblock_read_count’
      4              ,’optimizer_mode’,’sga_target’,’sort_area_size’)
      5  order by name
      6  /

    NAME                           VALUE
    —————————— ——————–
    compatible                     10.2.0.1.0
    cpu_count                      1
    db_block_size                  8192
    db_file_multiblock_read_count  16
    optimizer_mode                 ALL_ROWS
    sga_target                     612368384
    sort_area_size                 65536
     

    Now I create a reasonably sized table with a numeric column ID to be the primary key, column NUM_1 which is a small varying numeric, a unique column ID_2, NUM_2 holding random values between 0 and 99, another small number column NUM_3 and a final numeric column NUM_4 which is sparse, only 1 in 20 records will hold a value. Finally there are three columns VC_1 to VC_3 which just hold strings to pad out the table a little.

    test102>drop table test_1;
    Table dropped.

     Elapsed: 00:00:00.59

    test102>–
    test102>– create a table with 20k rows, an ascending numeric PK
    test102>– a unique VC
    test102>– a non unique numeric
    test102>– a sparsly populated numeric
    test102>– and some padding fields.
    test102>create table test_1
      2  as select rownum  id
      3  ,mod(rownum,10)  num_1
      4  ,’A’||to_char(rownum+1000) id_2
      5  ,trunc(dbms_random.value(1,100)) num_2
      6  ,mod(rownum,5)  num_3
      7  ,decode(mod(rownum,10),0,trunc(dbms_random.value(1,30))
      8         ,null) num_4
      9  ,lpad(‘A’,50,’A’) vc_1
     10  ,lpad(‘B’,50,’B’) vc_2
     11  ,lpad(‘C’,250,’C’) vc_3
     12  from dba_objects
     13  where rownum < 20000
     14  /

    test102>desc test_1

     Name                                            Null?    Type
     ———————————————– ——– ——————————–
     ID                                              NOT NULL NUMBER
     NUM_1                                                    NUMBER
     ID_2                                                     VARCHAR2(41)
     NUM_2                                                    NUMBER
     NUM_3                                                    NUMBER
     NUM_4                                                    NUMBER
     VC_1                                                     VARCHAR2(50)
     VC_2                                                     VARCHAR2(50)
     VC_3                                                     VARCHAR2(250)
     
    Now I add a primary key (a supporting unique index is created automatically), a unique index, a non-unique index and an index on the sparse column. Finally, I use the old ANALYZE command so compute statistics on the table and index, so that the Oracle Optimizer has information about the table and it’s indexes.
     
    test102>– now add a pK on the ID
    test102>alter table test_1
      2  add constraint t_pk primary key (id);
    Table altered.

    test102>– add a unique index on the unique varchar
    test102>create unique index t_uq on test_1(id_2);
    Index created.

    test102>– add a non-unique index on the non-unique numeric
    test102>create index t_nonuq on test_1(num_2);
    Index created.

    test102>– and finally an index on the sparse column
    test102>create index t_sparse on test_1(num_4);
    Index created.

    test102>– now add statistics
    test102>analyze table test_1 compute statistics;
    Table analyzed.
     
    That is the setup, now I turn on autotrace (which causes SQL*Plus to show information about the statements executed), timing and I’ll do my first test, the traditional select count(*).

    test102>set autotrace on
    test102>set timi on
    test102>set pause off
    test102>– Traditional count(*)
    test102>select count(*) from test_1;
     

      COUNT(*)
    ———-
         19999
    1 row selected. 

    Elapsed: 00:00:00.01

    Execution Plan
    ———————————————————-
    Plan hash value: 454320086

    ———————————————————————-
    | Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
    ———————————————————————-
    |   0 | SELECT STATEMENT      |      |     1 |    11   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |      |     1 |            |          |
    |   2 |   INDEX FAST FULL SCAN| T_PK | 19999 |    11   (0)| 00:00:01 |
    ———————————————————————-

    Statistics
    ———————————————————-
             44  recursive calls
              0  db block gets
             53  consistent gets
              0  physical reads
              0  redo size
            413  bytes sent via SQL*Net to client
            381  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
     
    What does this show? Well, there are 19,999 rows, it took 1 millisecond (ms) to do the count.

    The plan is simple, there was a fast full scan of the index T_PK, cost was 11 and it took 1ms

    The statistics show 44 recursive calls, 0 block reads (this is physical reads from disc, as far as Oracle is concerned) and 53 consistent gets, which is reads from memory. I’ll ignore the rest for now.

    However, I have done one test on one statement. I was taught to be a scientist at college, one isolated test is not enough, I do not know if the results are stable. I’ll do it again.

     
    test102>select count(*) from test_1;

      COUNT(*)
    ———-
         19999
    1 row selected.

    Elapsed: 00:00:00.00 

    Execution Plan
    ———————————————————-
    Plan hash value: 45432008
     
    ———————————————————————-
    | Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
    ———————————————————————-
    |   0 | SELECT STATEMENT      |      |     1 |    11   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |      |     1 |            |          |
    |   2 |   INDEX FAST FULL SCAN| T_PK | 19999 |    11   (0)| 00:00:01 |
    ———————————————————————-

    Statistics
    ———————————————————-
              0  recursive calls
              0  db block gets
             46  consistent gets
              0  physical reads
              0  redo size
            413  bytes sent via SQL*Net to client
            381  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
     
    Well the result and the plan are the same, but now the code takes less than 1ms, there are no recursive calls and consistent gets drop from 53 to 46. The recursive calls and the extra consistent gets in the first statement are due to Oracle doing some parsing work on the statement – it has checked the syntax, looked up the objects in the data dictionary and come up with an execution plan (I have to be honest, this is not quite true, I have run the statement several times already so some of that work was done before, thus the parse work in this example is a little light).
     
    The lesson here is, treat the first execution of a statement as a special case. The second and further executions give better stats on the execution of the statement. Run the select count(*) from test_1 half a dozen times yourself and you should see the stats are stable.

    Now,  select count(id) from test_1. Column ID is a primary key

    test102>– now count on the Primary key
    test102>select count(id) from test_1;
     
     COUNT(ID)
    ———-
         19999
    1 row selected.

    Elapsed: 00:00:00.00

    Execution Plan
    ———————————————————-
    Plan hash value: 454320086

    ———————————————————————-
    | Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
    ———————————————————————-
    |   0 | SELECT STATEMENT      |      |     1 |    11   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |      |     1 |            |          |
    |   2 |   INDEX FAST FULL SCAN| T_PK | 19999 |    11   (0)| 00:00:01 |
    ———————————————————————-
     
    Statistics
    ———————————————————-
              1  recursive calls
              0  db block gets
             46  consistent gets
              0  physical reads
              0  redo size
            414  bytes sent via SQL*Net to client
            381  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
     
    That is spookily like the second run of count(*). Remembering my comments on the first run, Let’s do a second run of the code.

    test102>select count(id) from test_1;
     COUNT(ID)
    ———-
         19999
    1 row selected.

    Elapsed: 00:00:00.00

    Execution Plan
    ———————————————————-
    Plan hash value: 454320086

    ———————————————————————-
    | Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
    ———————————————————————-
    |   0 | SELECT STATEMENT      |      |     1 |    11   (0)| 00:00:01
    |   1 |  SORT AGGREGATE       |      |     1 |            |          |
    |   2 |   INDEX FAST FULL SCAN| T_PK | 19999 |    11   (0)| 00:00:01 |
    ———————————————————————-

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

    Yep, exact same plan and exact same stats as for count(*).

    Why is there only a difference of 1 recursive call between the two runs? The Oracle kernel has spotted that the query can be satisfied with the same plan, so it just allocates it to the cursor (ie uses it for this statement). Note, the plan hash value is the same. 

    Now to try count(1). For brevity I am going to trim the output of things that do not change. You have the script to run yourself so you can check I am not deleting anything significant. 

    test102>– count on a static value
    test102>select count(1) from test_1;

      COUNT(1)
    ———-
         19999
     
    Execution Plan
    ———————————————————-
    Plan hash value: 454320086

    ———————————————————————-
    | Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
    ———————————————————————-
    |   0 | SELECT STATEMENT      |      |     1 |    11   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |      |     1 |            |          |
    |   2 |   INDEX FAST FULL SCAN| T_PK | 19999 |    11   (0)| 00:00:01 |
    ———————————————————————-
     
    Statistics
    ———————————————————-
              1  recursive calls
              0  db block gets
             46  consistent gets
     
    Exactly the same as the first iteration of select count(id). The plan hash value is the same too. Second run?

    test102>select count(1) from test_1;

      COUNT(1)
    ———-
         19999
     

    ———————————————————————-
    | Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
    ———————————————————————-
    |   0 | SELECT STATEMENT      |      |     1 |    11   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |      |     1 |            |          |
    |   2 |   INDEX FAST FULL SCAN| T_PK | 19999 |    11   (0)| 00:00:01 |
    ———————————————————————-

    Statistics
    ———————————————————-
              0  recursive calls
              0  db block gets
             46  consistent gets

    I feel that is pretty good supporting evidence that count(*), count(id) and count(1) are all being executed the same and are equally efficient. 

    As a final test for now, how about select count(‘any old text’)?
     
    test102>–count on a larger, text static value
    test102>select count(‘any old text’) from test_1;

    COUNT(‘ANYOLDTEXT’)
    ——————-
                  19999

    Execution Plan
    ———————————————————-
    Plan hash value: 454320086

    ———————————————————————-
    | Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
    ———————————————————————-
    |   0 | SELECT STATEMENT      |      |     1 |    11   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |      |     1 |            |          |
    |   2 |   INDEX FAST FULL SCAN| T_PK | 19999 |    11   (0)| 00:00:01 |
    ———————————————————————-

    Statistics
    ———————————————————-
              1  recursive calls
              0  db block gets
             46  consistent gets

    test102>select count(‘any old text’) from test_1;
     
    COUNT(‘ANYOLDTEXT’)
    ——————-
                  19999

    Execution Plan
    ———————————————————-
    Plan hash value: 454320086

    ———————————————————————-
    | Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
    ———————————————————————-
    |   0 | SELECT STATEMENT      |      |     1 |    11   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |      |     1 |            |          |
    |   2 |   INDEX FAST FULL SCAN| T_PK | 19999 |    11   (0)| 00:00:01 |
    ———————————————————————-

    Statistics
    ———————————————————-
              0  recursive calls
              0  db block gets
             46  consistent gets
     
    Tomorrow I will continue on the theme but that is enough for now.

    Follow

    Get every new post delivered to your Inbox.

    Join 159 other followers