jump to navigation

Single Table Hash Selects Investigated May 30, 2009

Posted by mwidlake in performance.
Tags: ,
2 comments

I think I mentioned in an earlier post that Piet de Visser had posted a nice example of the fastest way to select a single row, via a single table hash cluster. Well, I somehow missed his followup until now (I think because he has two blogs and I was checking the other one).

This is the follow up and he once again provides scripts to repeat his tests. What a nice chap. He shows how care in setting up the hash cluster is a good idea.

Here’s looking forward to his next long train trip so he can add to this thread. I might even spot it sooner next time!

Fear of Databases May 29, 2009

Posted by mwidlake in Management, Perceptions.
Tags: , ,
4 comments

“It’s all in the database!”

I’m sure most of you (if you are in the UK, I must remember that the web is a world spanning medium) have seen the adverts by the wonderful TV Licencing authority or the DVLA. If not they go something like:

“We keep records” {background music}
“We know if you have paid…or not.” {Music become more sinister}
We will find you, you cannot hide” {more affirmative music}
“It’s all in the database” {doom-laden musical flourish}

OK, maybe I lay it on a bit with the music.

Now, as a database professional, I see “it’s all in the database” as a good thing. With luck it will be a well designed database with referrential integrity and all nicely validated.

Nearly all news media stories about actual or perceived threats to electronic privacy also site “The Database” as the core.
“They {who?} will hold all your web searches in a vast Database”.
” A laptop holding a Database of 1 million double glazing customers has been stolen”. I bet it was actually 10 thousand and in a spreadsheet.

It’s getting to the point where I don’t feel comfortable telling people I meet outside of the IT world that I am a database expert. Databases are hardly ever now seen in a good light, they seem to be linked only to things bad and Orwellian.

The Database is also often cited when companies get things wrong for their customers. You ring up to complain about some aspect of non-service and are often told “Oh, it doesn’t agree with you in the Database” or “the Database has got it wrong”. No it hasn’t, the person putting the information in the database got it wrong. I’ve been in the unusual situation of being told a lie where the database was given as the cause but I had access to that database. So I checked and the database was fine. It was being used as a convenient and much maligned excuse.

Very little is mentioned of the beneficial uses of databases.
For most of us our salary is processed via databases and it is a lot cheaper and more reliable than having half a hundred pay clerks doing it manually in pen and ink.
Databases are used to hold or index much of that vast quantity of stuff that you can search for on the net. Even the useful stuff on Klingons.
I for one would welcome a UK-wide database holding my basic medical details so that when I go to my GP or hospital, they do not need my memory (and in fact my consciousness) to tell them my medical past. If I have an allergy to a common drug I damned well want all medical people to know that before they put 10cc of the stuff into my veins.

And to wrap up my bad-tempered tirad, I now find it particularly tricky to talk about what I still feel is my most significant achievement in IT, namely an 80TB Database of genetic information. Without getting into the topic of Bioethics, which is beyond the scope of this blog, Genetics and a lot of biological stuff is now painted grey, if not deep, murkey, scary Red by the media. I tell John down the pub that I created a huge genetics database, he is sure I am either working on a secret government project to know all about his inner workings or some evil company combining tomatoes and monkeys into some awful, new thing that {and he has seen the movies to prove this} in all likelihood will turn into a zombie killer, escape and do for mankind.

Maybe I’ll just tell people I shoplift for a living, it might be more socially acceptable than being involved in Databases or Genetics.

Management And Infrastructure SIG May 28, 2009

Posted by mwidlake in Management, Meeting notes.
Tags: , ,
add a comment

It is the next Mangement and Infrastructure Special Interest Group meeting next week (MI SIG), on Wednesday 3rd June.

I currently chair the MI SIG and, just as Andrew Clark says about the Development and Engineering SIG, “it is not as sexy as it sounds” {The chairing, I mean, the SIG itself is incredibly sexy and wonderful}. It basically means I spend a few days 2 or 3 times a year helping organise the event, strongly supported by the UK Oracle User Group (and in particular by Michelle Ericsson) and by the deputy chairs, Gordon D. Brown, Neil Chandler and Tony Clements, our “Oracle buddy”. I then chair the meeting itself.

Chairing may not be sexy, but it is rewarding, especially when we get a good line up of talks as for this one and the registered delegate numbers are healthy. SIGs have been suffering poorer attendances of late and a high number of delegates just not turning up, which is vexing (and, I’m sorry to be blunt, bloody rude to the speakers and committee who do this for free).

I’m presenting on “Being an IT Manager” and I am trying something different. I am ditching Powerpoint and I am just going to talk. It could be a disaster.

I’ll of course let you know how it went. Alternatively, if there are still spaces, come along and witness for yourself.

Why Tune? May 27, 2009

Posted by mwidlake in Management, performance.
Tags: ,
add a comment

I’m just wrapping up a couple of changes to a presentation I am putting on the UKOUG web site. Right at the start is a slide that sums up my attitude to tuning. I might make it my tag line but for now, this simple post will do (before my brain drops the information, as it has a talent for doing)

If you are not tuning something in order to help your business (or client) achieve something faster that they need to achieve faster, then you have to question why you are doing it.

If it is to learn HOW to tune, that’s good.

If it is just to see if you can get it faster, for the crack as it were, then if there is anything as or more important than making a cup of tea to be done, you should be doing that.

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?

The fastest way to get a single record May 22, 2009

Posted by mwidlake in performance.
Tags:
1 comment so far

Piet de Visser and I have had the odd chat about SQL performance over the last couple of months and he recently posted this interesting article and test script showing how to get a single record out of a large table with only one logical IO. It uses hash clusters, which are a very underutilised feature.

I’ve a couple of thoughts myself on how to use them, but for now I’ll leave you to look at Piet’s posting.

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.

UNIX SIG May 2009 May 20, 2009

Posted by mwidlake in Uncategorized.
2 comments

This is just a quick post today, I was going to continue on my theme of select count(*) but it’s been a long, tiring day. I’ll do it tomorrow.

I was off over to Wolverhampton to present at the oracle user group UNIX SIG (special interest group) today. I felt there was a need for more introductory presentations as they tend to be expert ones these days, people talking about the latest, coolest things they had found out. Great though such talks are, for many users, these can be a little beyond where they are and discussions I have had suggest that a lack of easily accessible talks can turn people off SIGs. So I offered an introductory talk on tuning, how to get going with it. The talk was graceously accepted by Patrick Hurley, currently running that SIG.

The day did not start well. OK, the night before did not start well, I was suffering from trying to put too much into the talk and I was up until 1am stripping things out. I finally got it into a state I felt I could present but it lacked a few diagrams I wanted. It would have to do.

I set of OK in the morning, just past 7am. However, I got stuck on the way, a crash on the A14 had bloked the road and I missed my chance to escape. Thus I was late. I managed to call ahead but several presenters were having trouble today.

I arrived during the morning coffee break and I was up next, so it was a quick job to set up the laptop, grab a coffee and get into the right frame of mind.

The talk went OK I think, could have been a little more clear, a little more polished, but we will see what the feedback says.  I was trying to get what I spend at least a whole day (and preferrably 2 or 3 days) running a course on into just under an hour.

After that and on top of the 4 hours in the car I was bushed. I hung around for lunch, had a couple of chats with fellow OUG people, including the deputy chair of the Mangement & Infrastructure SIG, Gordon Brown, which is good as we have our SIG in 2 weeks. However, I had seen Pete Finnigan’s talk at the Northern SIG, did not have much interest in the talk on Oracle Certification (sorry Joel, just not my bag) and so I did what I dislike other people doing, I left early.

A better journey back was had but then I had to get going on my latest assignment, some project management for an established client.

Hmm, this is all a bit “what I did today”. Not very interesting to other people I guess. Maybe I’ll edit it tomorrow.

More testing code layout May 19, 2009

Posted by mwidlake in Blogging.
Tags:
2 comments

Well, I pinged an email to the nice support guys at WordPress.com about whether I had missed something about turning off the gutter (line numbering) from the sourcode view, but there is no flag I missed and no mention was made of it coming in the future. I also am a little concerned that using sourcecode slows down the rendering of the web page. So I might save it for special code.

I also found out that if your code sample is more than about 60 characters long, you exceed the width of the text area and get scroll bars to the bottom and side (not of course helped by not being able to suppres the line numbering):

select count(*) "Invalid count:" from dba_objects where status != 'VALID';
select substr(owner,1,14) ownr
,substr(object_name||' - '|| object_type,1,36) obj_name
,to_char(created,'DD-MON-YY') CREATED
,to_char(last_ddl_time,'ddmmyy hh24:mi') last_ddl
from dba_objects where status !='VALID'
order by 1,2
/

compared to:

select count(*) "Invalid count:"
from dba_objects where status != 'VALID';
select substr(owner,1,14) ownr
,substr(object_name||' - '|| object_type,1,36) obj_name
,to_char(created,'DD-MON-YY') CREATED
,to_char(last_ddl_time,'ddmmyy hh24:mi') last_ddl
from dba_objects where status !='VALID'
order by 1,2
/

But the nice support desk person did suggest I try pre tags;

select count(*) "Invalid count:"
from dba_objects where status != 'VALID';
select substr(owner,1,14) ownr
,substr(object_name||' - '|| object_type,1,36) obj_name
,to_char(created,'DD-MON-YY') CREATED
,to_char(last_ddl_time,'ddmmyy hh24:mi') last_ddl
from dba_objects where status !='VALID'
order by 1,2
/

Hmmm, nope, not really better than code.
I am starting to think I duffed slightly in chosing this style, Regulus. I suspect it does not respond to many formatting tags.

On the plus side, the nice helpdesk person did agree to putting the issue of access to the flags for sourcecode on the list of requested enhancements. Nice people.

Follow

Get every new post delivered to your Inbox.

Join 152 other followers