jump to navigation

Cost of full table scans #2 June 30, 2009

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

This is the second post in a series on Full Table Scans. Sorry to anyone waiting for part two, I got distracted {I suspect very few people were actually waiting :-)} I make up for it by making posting this veeeery long.

Post One – I got several comments, mostly pre-empting what I planned to say next {I am not complaining as such, I like to get feedback and Randolf Geist in particular was being very helpful}, but I’m going to say what I wanted to say anyway, especially as I was being purposefully misleading in my first post…

I’m attempting to show three things in this series of posts.

  1. How table scans are costed by the CBO under 10g and 11g, as it is quite different from before.
  2. That simple test cases of what you knew once can fail to work and leave you confused and questioning your knowledge.
  3. How levels of “truth” help you understand more as you dig deeper.
  4. As a fourth I might touch on how Oracle’s calculated cost is not actually always reflecting how it executes sql, but I need to work on that a little.

If you thought that db_file_mutliblock_read_count is the number of blocks that oracle reads in one go when scanning tables and indexes and that this improves performance, that will help you understand what is going on inside your Oracle database.
If you think that increasing the value will make full table scans look more efficient to Oracle and thus it will favour them, you are again right and it may well help you tune a system.
However, you may know more – you may know that Oracle modifies the value of the MBRC to make it more realistic. As the MBRC gets larger, things like the size of the segment, hitting the end of individual extents and the physical time taken to read data off disk and chuck it across the network makes the efficiency gains of these larger values less significant. Each layer of knowledge helps a little more. Then the technology underneath shifts and you may not realise this as the older knowledge still kind of helps.

I’m not trying to be smart or “I know better” here, I’ve posted before that none of us knows this stuff until we are told and I certainly did not know all of what is in these posts when I started preparing them 3 or 4 weeks ago. Some of what I plan to say I am still not sure of. But I’ll postpone the philosphy now for a later blog post, though as I have a rough version already written, here it is if you want.

Back to the technology and the costing of full table scans.

Here is my test script again.

It creates a couple of tables, the one of interest is TEST_BIG, holding 40,000 rows. TEST_BIG is 2304 blocks big but only 2267 blocks are occupied. As nothing has been deleted from the table, this is also the high water mark, ie the highest point in the table into which data has been inserted. Oracle keeps track of this high water mark. The table is in a locally managed, autoallocate tablespace with automatic segment space management. All the defaults.

This is my environment:-
NAME VALUE
—————————— ——————–
compatible 11.1.0.0.0
cpu_count 2
db_block_size 8192
db_file_multiblock_read_count 128
optimizer_mode ALL_ROWS
sga_target 0
sort_area_size 65536

Just to re-itterate, I have not gathered system statistics -the system is “out of the box”.

From Oracle 10 onwards, there are some default system statistics set. You can see these by looking at the sys.aux_stats$ table (click on “view plain” to see it properly laid out):-

 select sname,pname,pval1,pval2
 from
 sys.aux_stats$
 order by sname,pname

SNAME           PNAME                PVAL1 PVAL2
-----------------------------------------------------
SYSSTATS_INFO   DSTART                     10-15-2007 11:32
SYSSTATS_INFO   DSTOP                      10-15-2007 11:32
SYSSTATS_INFO   FLAGS                    1
SYSSTATS_INFO   STATUS                     COMPLETED
SYSSTATS_MAIN   CPUSPEED
SYSSTATS_MAIN   CPUSPEEDNW      1845.90945
SYSSTATS_MAIN   IOSEEKTIM               10
SYSSTATS_MAIN   IOTFRSPEED            4096
SYSSTATS_MAIN   MAXTHR
SYSSTATS_MAIN   MBRC
SYSSTATS_MAIN   MREADTIM
SYSSTATS_MAIN   SLAVETHR
SYSSTATS_MAIN   SREADTIM

There are only 3 values set {on both version 11g and 10g},

  • CPUSPEEDNW – an estimate of the speed of the CPU. This value is different on different servers. Maybe. Both my old V10 windows test boxes have a value of 484, even though one is a truely old laptop, yet the linux servers I have access to all have different values, ranging from 713 to 1778.
  • IOSEEKTIME – This is the time for the IO subsystem to locate the start of an item. In both v10 and 11, this is 10 milliseconds
  • IOTFRSPEED – This is the volume of data that can be read from the io subsystem in 1 millisecond – 4096 bytes or 4k, again in V10 and 11

These default figures for IOSEEKTIME and IOTFRSPEED are fairly conservative, especially as they have not changed since V10, but then seek time has not really improved (especially on single discs).

In my experience, and looking around on the net, it seems that many sites on V10 upwards have not gathered system stats {BTW see the end of this post for some clarification on system statistics}. This seems to be due to one of several factors:

  • Some sites do not have a DBA or one who knows about system statistics {I’m not criticising, if you have been labelled a DBA and thrown the whole of the Oracle stack to look after, there is a lot to get your head around}.
  • Some DBAs think the system stats are gathered by the automatic stats job that {sort of} runs every night .
  • Changing to using system statistics would change execution plans and thus need full regression testing, some re-tuning, which there is no time or money for.
  • Hey, it works, why change things {this can also be viewed as  “I am fundamentally scared of change” :-) }.

I can understand all of the above, and as a result of them many of us benefit from understanding how the default system statistics work. I would suggest it is better to gather system statitics than not as you are giving the oracle CBO optimizer more accurate information about the actual speed of your cpu, your IO and the difference between single block reads and multi-block reads, but life is not perfect. {NB I’ve heard on the grapevine that you should not gather system stats if you run Oracle Apps 11i but I am no expert on Oracle Apps!}

So what does Oracle do with the “out of the box” information? It calculate values for SREADTIME {time to read a single block} and MREADTIM {the time taken to do a multi-block read. If you think about how discs work, to read something the head of the disc must locate where the data is and physically move to it – this is the seek time – and then read the data.
So for a single block (8k) the time taken is

IOSEEKTIME+(size of one block/IOTFRSPEED).
ie 10+(8192bytes/4096 bytes) = 12.

For a multi block read, then the disc head will seek once and then read db_file_multiblock_read_count number of blocks at once {just accept this for now, please}. In my case, 128*8k.

IOSEEKTIME+( (MBRC * Size of one block) / IOTFRSPEED)
ie 10+( (128*8192) / 4096)
= 10+(1048576/4096)
=10+(1024k/4k) =10+256 =236
I don’t know about you but I can’t divide 1048576 by 4096 just like that but 1024/4, yep easier.

The CBO can only cost something if it can treat all parts in the same units, so it converts those multiblock read counts to equivalent single block read counts by dividing the time for the multi block read by the time of the single read.
236/12 = 19.6667.
Oracle then rounds the figure to the next whole number, so 20.

{Incidentally, it does this with the CPU cost as well.
Oracle makes a guess at how much cpu effort an activity will take and then divides it by the CPU speed and the time for a single block read – I’m being a little simplistic here, but it will do for now}.

So, a single block read is cost 1
A multi block read of 128 is cost 20, and reads 128 times as much.

In previous versions of Oracle this was all different. In 8i, single block reads and multi block reads were costed the same. In 9i without cpu costing {no cpu costing being the default and I can’t remember seeing it changed on a live system I saw} a “fudge factor” was introduced where values for MBRC were reduced down to better match reality. And in my first post I misleadingly referred to a Fudge Factor and gave a table. Anyone who was used to this fudge factor may have noticed my numbers did not match, but then who of us remembers that the fudge factor for 8 as the multi block read count is 6.19? :-)

{That table is still useful, it shows an effective ratio for the MBRC, IF you have gatherd no stats AND you have an 8k block size. OK, maybe it is not so useful…}

Anyway, as you can now see from the above formulae, Oracle on 10g and 11g now uses a fairly sensible piece of logic to decide on the relative costs of single and multi block reads. The CBO formula is basically saying you save the cost of multiple seek times when reading multiple blocks, but the time to transfer data is directly related to the volumeof data read. This is probably reasonable on a system with a simple IO setup (like single disks or very basic RAID) but will break down with high-end storage which has read-ahead and SAN caches and the like.

OK, I have to address one more issue before I demonstrate that the formula is correct. And it is something that I did not know and confused the hell out of me when I decided to do a “simple test” of what I thought I knew, and prompted me to go on endlessly about how doing a simple test to prove what you have learned can go wrong and send you crazy with confusion. And thanks again to Randolf Geist for pointing me in the right direction.

At the end of the last post I had shown that my multiblock read count was 128 but acted like it was 8.
ie I got a cost of 617 for scanning TEST_BIG. When I manually set my MBRC to 8 I also got a cost of 617. When I manually set a value for MBRC of 128, it gave a cost of 395. Huh?!

Coskan alluded to this bit, but I missed it – I was ignorant about what he was saying.

On a newly created Oralce 11.1 test database, created as part of a simple instal on windows vista, you have db_file_multiblock_read_count set to:-

show parameter db_file_multiblock_read
NAME Value
————————————————————
db_file_multiblock_read_count 128

But there is a hidden parameter:-
NAME VALUE
————————————
DESCRIPTION
—————————- —– —–
_db_file_optimizer_read_count 8
multiblock read count for regular clients

{To see hidden parameters you need to be logged in as sys or use a trick like intermediate views – here is a script you can run as sys to see them. Warning, there are a LOT of them! I’ll do yet another post about hidden parameters sometime soon.}

How the hell are we supposed to know this???! Do Oracle do this on purposed to keep us awake at night worrying about this???.
*sigh*.

On Oracle 10, this hidden initialization parameter defaults to 8 and MBRC defaults to 8 {usually – I just recreated a new test 10.2.0.1 database and both are set to 16 “out of the box”}. If you change MBRC, the hidden parameter alters too, to the same value, so you do not notice it so much.
On 11, MBRC is set to 128 and the hidden parameter is set to 8 initially. Once you change MBRC, the hidden _db_file_optimizer_read_count is also changed to match.

So, as my first paragraph on the first posting said, you go on to a test database to check something and you get confused as the very first “simple” test does not give you the results you expected.

OK, does the formula work?
In the below table I list:

  • The MBRC that I set by altering my session
  • The cost as reported by Explain Plan
  • The calculated MREADTIM = IOSEEKTIM+ (MBRC*blocksize/IOTFRSPEED)
  • The calculated SREADTIM  = IOSEEKTIM+(blocksize/IOTFRSPEED)
  • MREATIME/SREADTIM { I’ll call it MT/ST}
  • Blocks in table/MBRC {I’ll call it blks/MBRC
  • (blks/MBRC)  * (MT/ST) to get a calculated cost in single block reads
MBRC Cost MRT SRT MT/ST blks/  Calc cost
                                       mbrc
2   1325  14    12  1.167   1133.5     1323
3   1010  16    12  1.333   755.667   1008
4    853   18    12  1.5       566.75     851
6    695   22    12  1.833   377.833   693
8    617   26    12  2.167   283.375   615
10  569   30    12  2.5       226.7       567
12  538   34    12  2.833   188.917   536
16  499   42    12  3.5       141.688   496
20  475   50    12  4.167   113.35     473
24  459   58    12  4.833   94.458     457
32  440   74    12  6.167   70.844     437
33  438   76    12  6.333   68.697     436
64  410   138  12  11.5    35.422     408
128 395  266  12  22.167  17.711   393

Yes :-)
The calculated cost is always 2 less than the cost given by the CBO. I know that under Oracle 10 1 is added to the cost for reading the table header. I am presuming that an additional 1 has been added for some reason under 11.

That will do for now.

Oh, as promised, some comments on system statistics

Oracle System statistics are NOT statistics collected on the tables and indexes owned by users SYS or SYSTEM. These would be “Dictionary Statistics”. These can be collected via specific calls to the DBMS_STATS.GATHER_DICTIONARY_STATISTICS and {if you have not turned off the automatics gathering of object statistics} are gathered automatically during weekday nights and over the weekend by the automated job, which gathers table and index stats when it feels they are needed. I’ll be posting about that {probably at inordinate length} in a month or so.
System Statistics are statistics about how the underlying computer system works, specifically cpu speed and IO subsystem performance.
Even Oracle Support can get confused over this. You might like to check out this unfortunate experience on the topic. I have to say, I have much sympathy with Piet as I had very similar issues trying to discuss automated stats gathering with Oracle a couple of years ago. Having said that, I in the end got good support on the topic.

A 100TB Database June 23, 2009

Posted by mwidlake in development, VLDB.
Tags: , ,
add a comment

Some of you may know of me as the guy who constantly presented on the “massive database of genetic information” he was creating. I started presenting about it around 2003 and I said it would be 7TB. As I built it, the data kept flooding in and by the time I had it up and running and fully populated, around 2005, it was getting scary – it had grown to over 25TB. Who am I kidding? It was beyond scary, it kept me awake at nights.

Well, it still exists and continues to grow. I saw it up to 45TB before I left the Sanger institute {where I built it} and it continues to grow towards the 100TB I designed it to scale to.

Why am I bragging about this? {” hey, have you seen the size of my database”?!}. Well, I am very proud of it. It was my pet project.

But pet project or not, I had a team of DBAs at the Sanger and of course, when I say “I built it” I should say “My team and I built it”. And they looked after it after I departed and it got even bigger.

Last week I got an email off one of them to invite me over for a small celebration this week. What are we celebrating? The first database on-site to hit 100TB. Am I proud? Hell yes, I am proud.

But not proud of what you probably think I am, given my careful preamble.

It is not my database that has broached the 100TB limit.

It is another one, a database the team put together after I left and that they have looked after without my input. What I am really proud about is that, with Shanthi Sivadasan who was holding the fort when I arrived at the Sanger {and remains there}, we put together a team of DBAs that is capable of creating and looking after such a large behemoth. It could not be done without excellent support from the Systems Administrators as well, but I feel particularly proud of the DBAs.

So congratulations to the DBAs at the Wellcome Trust Sanger Institue: Shanthi Sivadasan, Tony Webb, Andy Bryant, Aftab Ahmed, Kalyan Kallepally and Karen Ambrose. You went further with this than I did.

I hope that the cake to celebrate is very nice :-)

cost of full table scans June 17, 2009

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

As many sites point out, the cost as stated in explain plan is {being a little simplistic} a calculation by the CBO of the number of IO operations it thinks will be needed to complete that step of the plan. {It includes the cost of any previous step or steps that feed into it but many examples are nice and simple and have only a couple of steps in them}. 
However, it can be confusing when you decide to look at some simple examples and don’t see what you expect, as you start with simple table scans.

My test script  can be found here .

I set up a simple pair of test tables

-- create a table with 20k rows, an ascending numeric PK
-- a unique VC
-- a non unique numeric
-- a sparsly populated numeric
-- and some padding fields.
create table test_big
as select rownum  id
,mod(rownum,10)  num_1to10
,trunc(dbms_random.value(1,1000)) num_1k_vals
,'A'||to_char(rownum+1000) id_2
,trunc(dbms_random.value(1,100)) num_100_vals
,mod(rownum,5)  num_4
,decode(mod(rownum,10),0,trunc(dbms_random.value(1,30))
        ,null) num_sparse
,lpad('A',50,'A') vc_1
,lpad('B',50,'B') vc_2
,lpad('C',250,'C') vc_3
from dba_objects
where rownum < 40001
/
create table test_small
as select rownum  id
,mod(rownum,10)  num_1to10
,trunc(dbms_random.value(1,1000)) num_1k_vals
,'A'||to_char(rownum+1000) id_2
,trunc(dbms_random.value(1,100)) num_100_vals
,mod(rownum,5)  num_4
,decode(mod(rownum,10),0,trunc(dbms_random.value(1,30))
        ,null) num_sparse
,lpad('A',50,'A') vc_1
,lpad('B',50,'B') vc_2
,lpad('C',250,'C') vc_3
from dba_objects
where rownum < 101
/
-- now add statistics
-- NB each exec must be on one line
exec dbms_stats.gather_table_stats
(ownname=>user,tabname=>'TEST_BIG'
,estimate_percent=>100,cascade=>true)
exec dbms_stats.gather_table_stats
(ownname=>user,tabname=>'TEST_SMALL'
,estimate_percent=>100,cascade=>true)
--
desc test_BIG
DESC TEST_SMALL
--
set autotrace on
set timi on
set pause off
select count(*) from test_big;
select count(*) from test_small;

I’m running in Oracle 11.1.0.6 on a windows vista laptop, multi block read count is 128, no system statistics have been collected.

It creates one table with 40,000 records and one with 100 records and gather stats on them. I’ll now select count(*) from both and look at the plan.

select count(*) from test_big;
  COUNT(*)
----------
     40000

Execution Plan
-----------------------------------------------------
| Id  | Operation          | Name     | Rows  | Cost (%CPU)|
-----------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |   617   (1)|
|   1 |  SORT AGGREGATE    |          |     1 |            |
|   2 |   TABLE ACCESS FULL| TEST_BIG | 40000 |   617   (1)|
-----------------------------------------------------

select count(*) from test_small;

  COUNT(*)
----------
       100

Execution Plan
-----------------------------------------------------
| Id  | Operation          | Name       | Rows  | Cost (%CPU)|
-----------------------------------------------------
|   0 | SELECT STATEMENT   |            |     1 |     4   (0)|
|   1 |  SORT AGGREGATE    |            |     1 |            |
|   2 |   TABLE ACCESS FULL| TEST_SMALL |   100 |     4   (0)|
-----------------------------------------------------

The costs of full table scans are 617 for the large table and 4 for the small table. We have full stats on the tables and they are just simple tables, no indexes, we should be able to see where the numbers come from.

Any guesses? Well, it could be the number of blocks making up the objects:-

select table_name,num_rows,blocks,empty_blocks
from dba_tab_statistics
where owner=user
and table_name in ('TEST_SMALL','TEST_BIG');

TABLE_NAME   NUM_ROWS     BLOCKS EMPTY_BLOCKS
---------- ---------- ---------- ------------
TEST_BIG        40000       2267            0
TEST_SMALL        100          9            0

Err, no. 2267 blocks in TEST_BIG and 9 blocks in TEST_SMALL.
{btw the view DBA_TAB_STATISTICS holds a subset of the information on DBA_TABLES and I tend to use it if I just want stats info}.

It could be the number of blocks in the segment maybe?
run @seg_dets.sql .

Enter value for seg_name: test
Enter value for owner: t

SEG_NAME    SEG TS_NAME   BYTES_K  BLOCKS exts  INI_K NXT_K
------------------------------------------------------
TEST_BIG    TAB DATA_01      18,432     2,304   33      64
TEST_SMALL   TAB DATA_01         128        16    2      64

No, it isn’t that. 2304 is not very different from 2267, as you would expect as we simply created and populated the table.

Hands up anyone who is thinking about db_file_multiblock_read_count? MBRC is the number of blocks Oracle will try and select from a segment in one I/O operation if it is scanning it. Well, MBRC is set to 128 on this system {it’s “out of the box” and I question it being that high actually. On 10.2 on windows it is, I think, 8}.

That sounds incredibly sensible, we can check if it has scanned all those blocks in chunks as dictated by the MBRC. Well, divide 2304 {number of blocks used by the segment} by 617 {I/O cost} and you get…3.734. Nothing like the MBRC.

What’s going on?!!!


Well, oracle modifies the value of db_file_multiblock_read_count before using it in calculations. But it IS the source of the calculation. You can change MBRC at a session level to check:-

alter session set db_file_multiblock_read_count=4;
Session altered.

select count(*) from test_big;
  COUNT(*)
----------
     40000
Execution Plan
----------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Cost (%CPU)|
----------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |   853   (1)|
|   1 |  SORT AGGREGATE    |          |     1 |            |
|   2 |   TABLE ACCESS FULL| TEST_BIG | 40000 |   853   (1)|
----------------------------------------------------------

alter session set db_file_multiblock_read_count=8;
Session altered.

select count(*) from test_big;
  COUNT(*)
----------
     40000
Execution Plan
----------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Cost (%CPU)|
----------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |   617   (1)|
|   1 |  SORT AGGREGATE    |          |     1 |            |
|   2 |   TABLE ACCESS FULL| TEST_BIG | 40000 |   617   (1)|
----------------------------------------------------------

At an MBRC of 4 the CBO calculates a cost of 853 to scan the TEST_BIG table and at an MBRC of 8 the CBO calculates a cost of 617.

This script here will run through a set of values for db_file_multiblock_read_count and run the count(*) so you can see the values going down as MBRC goes up, but oracle discounts the effect of the parameter as it gets larger.

To save you the effort, these are the costs at each value of MBRC I checked and I calculate the modifed MBRC {based on 2267 blocks, 2267 is the best number to use as it is likely to be the high water mark or very close}.

MBRC          modified
Value  Cost    MBRC
2      1325    1.711
3      1010    2.245 -- not a Magic Number
4      853     2.658
6      695     3.262
8      617     3.674
10     569     3.984
12     538     4.214
16     499     4.543
20     475     4.773
24     459     4.939
32     440     5.152
33     438     5.176 -- not a Magic Number
64     410     5.529
128   395      5.729

What do I mean by the comment “it does not need to be a magic number”. Well, MBRC is normally set to 2, 4, 8, 16, 32,64… a factor of 2. All examples I can remember seeing used have been a Magic Number, so I thought I would check if Oracle does any rounding to such numbers or insists on even values. And it does not.
{quick comment – the table has changed since the first post, I stuck the segment size not the hwm into my spreadsheet}

A couple of final comments for today:

  • How in heck can you know about this fudge unless someone tells you? And yes, I know as someone told me.
  • The values I gained on 10.2 were very,very similar, consistently a cost 3 higher than on 11.1
  • Why does scanning 9 blocks have a cost of 4?
  • Why was I getting a cost of 617 with the default MBRC of 128 and yet when I set my session to have an MBRC of 128 the cost is calculated at 395?

I don’t know all the answers to the above yet.

 

The Sneaky WHAT Strategy!? June 15, 2009

Posted by mwidlake in biology, Perceptions.
Tags: , , ,
add a comment

OK, I can’t resist any more, I have to write a Blog about this. I apologise up front for any offence I cause anyone, it is not intended.

There has been a bit of a thread between my and Richard Foote’s blog about the Dunning-Kruger effect. This is his post on it. The Dunning Kruger effect (Jonathan Lewis told us what it was called) is where people have an over-inflated opinion of their own ability. Since the names of behavioral traits came up, I have been unable to get something out of my mind.

When I was at college I studied Zoology. In one lecture on animal behaviour we were told about the “Sneaky Fuck3r Strategy”. Yes, you read it right, that is what it is called. {I’ve stuck a ‘3’ in there as I’m concerned I’ll blow up some web filters}.

It was described in the context of Red Deer. A single dominant male has a harem of females during the breeding season. Other big, strong males will challenge the Dominant Stag and, if they win, will take over the harem. So, this one Stag has all the lady deer at his disposal, only challenged by similarly large, aggressive males.

Well, not quite. What sometimes happens is that, when the dominant stag is fighting off a challenge, one of the younger stags will sneak into the herd and mate with one of the females. Thus the term “Sneaky Fuck3r strategy”. Genetic testing shows that quite a few of the deer born are not fathered by the dominant male!

The one little twist added during my lecture was that it had been observed that one young male, male(A), would go and challenge the dominant stag whilst another young male(B) snuck into the herd. Then, a while later Male(B) would challenge the stag and Male(A) would have his turn. I don’t know if that was an unsubstantiated embellishment but is suggests smart as well as sneaky.

I really thought she was pulling our legs about the name, but the lecturer wasn’t. It is a real term, used by real zoologists, though mostly UK-based. You can google it but I won’t blame you if you want to wait until you are not at work to do so!

Many people lay the credit for the name to John Maynard Smith But this article with Tim Clutton-Brockhas an excellent description of the situation {click on “show Transcript” and I suggest you search for the word “sneaky”}. I am not clear if Tim did the original work on the subject though. For some reason I can’t fathom, wikipedia does not mention the strategy in it’s entries for either scientist…

How Much Knowledge is Enough? June 13, 2009

Posted by mwidlake in Blogging, Perceptions, performance.
Tags: , ,
8 comments

I’ve had a bee in my bonnet for a good few years now, which is this:

How do you learn enough about something new to be useful when you are working 40, 50, 60 hours a week?

Another bee is how much do you actually need to know to become useful? The bee following that one is if you do not have enough time to investigate something, how do you find the answer? Buzzing up behind is to fully understand how something works, you often need a staggering amount of back knowledge – how do you get it? Oh dear, it’s a hive in my head, not a single bee.

I am of course in this blog mostly thinking about Oracle and in particular Oracle performance. I think that these days it must be really very hard to get going with performance tuning as it has become such a broad topic. I don’t know if you have noticed but nearly all the performance experts are not in their teens. Or twenties. And precious few in their thirties. Forties are pretty much the norm.  We {and please excuse my audacity in putting myself in such an august group}  have been doing Oracle and performance for many years and have stacked up knowledge and understanding to help us.

For me this issue was thrown into sharp relief about 4 or 5 years ago. I had become a manager and, although I was learning lots of other skills and things, when it came to Oracle Technology I think I was forgetting more than I was learning. Oh, I was learning some new Oracle stuff but it was at a more infrastructure level. The real kick of reality was going to presentations on performance and Oracle internals. At the end of the 90’s I would go along and learn one or two new things but knew 90% of what was said. By the mid 2000’s I would go along and know 50% , the other 50% would be new. Then I went to one talk and found I was scribbling away as I knew precious little of what was being presented. More worryingly, I was struggling with “How does this fit in with what I already know?”.  I just didn’t know enough of the modern stuff.

That was a pivotal moment for me. It had the immediate effect of making me start reading blogs and books and manuals again. It’s not easy to find the time but I soon noticed the benefit. Even if I learnt only a little more one evening a week, I would invariably find that knowledge helping me the very next week or month. I was back on the road to being an expert. {Or so I thought}. Oh, it had a long term effect too. I changed job and went back to the technical, but that is for another day.

But hang on, during my decline I had not stopped being useful. I was still the Oracle performance expert where I worked and could still solve most of the performance issues I came across. It made me realise you do not need to know everything to be useful and you could solve a lot of problems without knowing every little detail of how something works. A good general knowledge of the Oracle environment and a logical approach to problem solving goes a long way.

I actually started to get annoyed by the “attitude” of experts who would bang on and on and on about how you should test everything and prove to yourself that your fix to a problem had fixed itas otherwise it was just being hopeful. I thought to myself “That is fine for you, oh exalted expert, as you have time for all this and don’t have 60 hours of day job to do every week. Give us a break and get real. Most of us have to get the problem solved, move on and get by with imperfect knowledge. Doing all that testing and proving, although nice in a perfect world, is not going to happen”.

Yep, I had an attitude problem :-). I was getting angry at what I now think is just a difference in perception. I’ll come back to that in a moment.

I don’t think I am going to go back on my opinion that for most people in a normal job, there simply is not time to do all the testing and proving and you have to move on, Making do with received knowledge. It just is not an ideal world. However, we need the experts to uncover that knowledge and we need experts who are willing to communicate that knowledge and we need experts we can rely on. I am very, very grateful to the experts I have learned from.  

All the time on blogs, forums and conversations the issue of “how do we know what sources we can trust” regularly comes up. Well, unfortunately I think that if you do not have time to do the testing and learning needed to become an expert yourself, you have to simple chose your experts, accept what they say but remain slightly skeptical about what they say. Everyone makes mistakes after all. I would advise you only accept someone as an expert and rely on their advice if they are willing to demonstrate why they believe what they believe. Everything else is just an unsubstantiated opinion. 

But I’ve come to some conclusions about most of the above questions I started with.

  1. If you are judicious in choosing your sources, you can learn more reliably and easily.
  2. Even a little bit of more knowledge helps and it often comes into use very quickly.
  3. The hard part? You have to make that time to learn, sorry.
  4. Although testing and proving is good, life is not perfect. If you did (1) you might get away without it. But don’t blame the expert if you get caught out.

But I’ve not addressed the point about needing all that back knowledge to fully understand how something works. Well, I think there is no short cut on that one. If you want to be an expert you need that background. And you need to be sure about that background. And that is where it all has fallen apart for me. I started a blog!

I already knew you learn a lot by teaching others, I’ve been running training courses on and off for a few years. But in writing a blog that is open to the whole community, I’ve realised I know less than I thought. A lot less. And if I want to be a source of knowledge, an expert, I have to fill a lot of those gaps. So I am going to have to read a lot, test things, makes sure that when I believe I know something I’ve checked into it and, when I fix something, I know why it is fixed {as best I can, that is} . All those things experts tell us we need to do. And that brings me back to my perception issue. 

Those who I think of as the best in this field all pretty much give the advice to test and prove. And they have to do this themselves all the time, to make sure what they say is right. And they are the best as what they say is nearly always right. It seems to be excellent advice.

However, I think it is only good advice, as it is advice you can’t always take, because there is too much else to do. I think sometimes experts forget that many people are just too pressured at work to do their own testing, not because they don’t want to test but because you can only live so long without sleeping. 

Anyway, I said something foolish about becoming an expert. I better go and check out some other blogs… start reading some manuals… try out a few ideas on my test database.  I’ll get back to you on how I’m progressing on that one in about, say, a year or two? All those gaps to fill….

Consistent Gets, db block gets and DML June 11, 2009

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

A few posts back I talked about consistent gets, db block gets and how I had realised I had got by for years without fully undertanding the terms. I then described how I went about learning more.

Well, I am about to learn yet more on the subject.

Jonathan Lewis posted a question about just this recently and I’m pretty sure I am the blog he was looking at. I won’t link to it as wordpress can automatically convert a link to a comment on other wordpress blogs and I don’t want to do that yet. You can find Jonathan’s post easily enough from my blogroll.

When I looked at consistent gets and db block gets I did so from a purely select point of view – and that is my downfall. Oracle does a lot more than select. DB block gets appear when data is being changed…

You can get a copy ofmy test script here and this is the full output.

Below is a printout of my test script. I simply create an empty copy of the EMP table, populate it, select from it and then delete from it. I do it twice so that the second time all code is parsed. All I am looking at right now is how much db_block_get activity there is.

set pause off timi on echo on
drop table emp2 purge;
spool dml_gets_test1.lst
set autotrace on
-- create an empty table from emp
create table emp2 as select 
* from emp where rownum < 1;
-- round one of tests, where parsing overhead exists
select empno,ename from emp2;
insert into emp2 select * from emp;
select empno,ename from emp2;
delete from emp2;
-- round two, just the statements
insert into emp2 select * from emp;
select empno,ename from emp2;
delete from emp2;
commit;
select empno,ename from emp2;
spool off
set autotrace off
drop table emp2 purge;

So, what do we see? {i’ve trimmed the output to show only what I think are the relevant bits}

Oh, this is on version 11.1.0.6 of oracle on windows vista, 8k block size, db_file_multiblock_read_count 128 {wow, more than I thought}, straight as created from the download and instal.

select empno,ename from emp2;
no rows selected

Execution Plan
———————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP2 | 1 | 20 | 2 (0)| 00:00:01 |
————————————————————————–

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

Just 8 consistent gets. No db_block gets, even though the statment is being parsed.

insert into emp2 select * from emp;
14 rows created.

Execution Plan
————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
————————————————————–
| 0 | INSERT STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01
| 1 | LOAD TABLE CONVENTIONAL | EMP2 | | | |
| 2 | TABLE ACCESS FULL | EMP | 14 | 1218 | 3 (0)| 00:00:01
————————————————————-

Statistics
———————————————————-
129 recursive calls
19 db block gets
107 consistent gets
0 physical reads
1672 redo size

As you can see, 129 recursive calls, 19 db block gets and 107 consistent gets. I believe a lot of that is due to parsing and maybe other things to do with the first time data is inserted into the table.

The second itteration gave the same plan but the statistics are very different:-

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

This helps show the overhead of parsing. however, we still see 3 db block gets

select empno,ename from emp2;

EMPNO ENAME
———- ———-
7369 SMITH
7499 ALLEN
{snip}
7934 MILLER

14 rows selected.

Execution Plan
———————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 14 | 280 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP2 | 14 | 280 | 3 (0)| 00:00:01 |
————————————————————————–

Statistics (run two)
———————————————————-
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads

No db block gets, 14 consistent gets as the data is read. Remember, this statement was parsed at the start of this script.

delete from emp2;
14 rows deleted.

Execution Plan
———————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————-
| 0 | DELETE STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | DELETE | EMP2 | | | |
| 2 | TABLE ACCESS FULL| EMP2 | 1 | 3 (0)| 00:00:01 |
——————————————————————-

First run:

Statistics
———————————————————-
4 recursive calls
15 db block gets
15 consistent gets
0 physical reads
4036 redo size

second run:
Statistics
———————————————————-
0 recursive calls
15 db block gets
7 consistent gets
0 physical reads
4036 redo size

So, as you can see, with delete there appears to be plenty of db block gets activity that is nothing to do with parsing.

I’m not going to analyse or investigate any further, I’ll wait to see what Jonathan says or I have more free time.

What I do know for sure is that I need to know more about the details of db block gets and my advice that it is internal processing that you can do little about may be very questionable advice indeed…

Hey, it’s not my fault I can’t spell. June 10, 2009

Posted by mwidlake in humour, Perceptions, Private Life.
Tags: , ,
1 comment so far

*Sigh*
I just got pinged by someone to let me know of some spelling mistakes in my blog. I know, I know, just leave me alone OK?!

Do you remember doing the “colour blind” test at school? {And, for our American cousins, “color blind”}. You know, you are shown a few images made up of dots, with numbers in them.

Most of the colour blindness images are far less obvious than this

Well, most people are shown 5 or 6 images and shout out “8”, “16” etc promptly five times and are then shown out – nothing more is said. Some people cry “7”, “34”, “dunno, give me a clue” and after 10 or 12 images get told they can’t distinguish blue & green or red & brown or something and so can’t drive trains or fly fighter planes… Me? I was in there for 5 minutes, coming up with what must have been very confusing answers. They even started showing me the same images again and I remember occasionally going something like “it’s 16 not 6, isn’t it”. Eventually they told me my colour vision was fine and threw me out as a time-waster. I wonder if I can fly fighter planes?

What they should probably have spotted (and a school teacher friend of mine got quite angry about this when I told her this story, as she thought they should have spotted this even back in the late 70’s) was that I could not read for toffee – as I have mild dyslexia. She had been taught how to identify dyslexia in children and one of the easiest ways was, she said, issues with the colour blindness test but without being colour blind.

When I read things I don’t do what a lot of people do, which is kind of pick up the start and end of long words and “see” it. I do it in little spirals. I do not know that I can explain better than that, but if I hit a long word (more than six letters) I start at the begining, flick to the back of the word and work back and if the two don’t meet I spiral in. I wonder if there is a cunning lexical trick I can sell to Oracle Text on that one?

It’s no where near as bad as many, heck I’ve managed to get by OK with it, but spell checkers have been a boon to me. The problem is, I don’t always remember to use them and, even if I do, a word spelt wrongly but is itself a correct word will not be picked up. I know, many packages now also have Grammer Checkers that could pick some of it up, but I find Grammer Checkers so infuriatingly useless, I turn them off.

So, sometimes my spelling is terrible. It’s because I have an IQ of 73, OK? The thing is, I probably got pinged in every exam I took because of it {except Maths, where in all honesty I got past the exams at age 16 and then it all stopped being logical. Sorry Mr Winters, I did my best as you know, but my brain could not do all that more advanced stuff}. I even got bollocked told off during my degree for carpals and carpels but heck, to me both read crapals.

I had particular fun a few years back when I introduced Oracle Partitioning to British Gas. No one had used it before but I had a quiet little application that I was passing over to the production DBAs to look after that did. So, I went over to Hinckley (oooh, thats a doozer to spell) where all the proddy DBAs lived and gave a presentation on Partitioning. Except I was doing it with white boards and OHP and every time I spelt Partitioning I wrote “rtit”, then went back and put in the “Pa” at the start and then tried to finish it off. Usually I managed. That was what prompted the chat with the teacher, I was telling her how that sort of thing happens to me and it’s annoying and she asked about if I had ever been tested for colour blindness.

So, there you go. It’s my excuse. Now you know that either I am right, or I have munchausens syndrome {just look it up, OK? Try this here}.

The odd thing? I can’t always spell “who” but I never get “Dyslexia” wrong.

Unhelpful “helpful” people June 9, 2009

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

I keep meaning to getting back to more technical blogs but I need to spend some time sorting it out first, and something has just bugged the hell out of me, so another “wordy” one today.

Richard Foote has gone back to explaining the basics of indexes and the CBO, which if you are new to CBO or indexes, hot-foote {sorry} it over there immediately and check it out. He is a brilliant teacher.

Near the start, he has a link to someone on one of the OTN forums who is stating the Cost Based Optimiser sucks. I won’t repeat the link. No, sod it, I WILL repeat the link. Here it is. It might elevate the page in google’s scoring but what the heck.

This person’s rather poor outlook on the CBO did not bother me, nor the fact that he is, in my opinion, wrong {he suggests using the rule hint on oracle 10, which is an option but is not, in my opinion, a mighty good idea as (a) who now knows the rules of the Rule Based Optimizer and (b) the hint will be ignored if you are using most new features added since 8, such as bitmap indexes or IOTs or partitioning. It might {and I have no proof for this} cause the feature not understood by the RBO to not be used, so maybe ignoring a nice bitmap index or function based index. Oh, and (c), if you have good stats the CBO usually wins.}.

What irritated me was his/her high-handed and abusive posting. That really annoys me. Then I thought “no, we all lose our temper sometimes and the person they are having a pop at did kind of ask for it”. But because I think that being abusive or condescending on forums is such bad behaviour, I dug a little into the other postings by this person.

Some were helpful. Many were simply links back to other pages or to some front end to Google the person’s question. And several, many, were abusive. Along the lines of “Why are you so stupid”; “If you can’t be bothered reading the manual you don’t deserve help”; “I would not employ you as you are a moron”. You get the idea?

It is a big problem with forums, and actually also in the work place (and occasionally, sadly, at meetings and conference). People being condescending, antagonistic and demeaning to others who do not know what they, the Mighty Brain, knows, who seem to Mighty Brain to not be trying quite as hard as they could or are seemingly asking something obvious.

OK, if it is obvious, give the answer. It might be that you, oh Mighty Brain, did not understand the question. OK, they maybe are not trying hard enough. Suggest to them where they could look, maybe this person has 3 managers breathing down their necks and they just really, really want an expert opinion now as they are not sure which of the seven opinions in google to trust. And Mighty Brain, unless you were born with your knowledge placed in your head by God, as a special force on this earth, you didn’t know what this “moron” does not know at one time. Someone told you or, vary rarely, you worked it out for yourself.

The truly, blood-boilingly, unjust thing about Mighty Brain? They are so sure of their own towering knowledge that they can’t see that they are often wrong.  I can’t think of any Oracle Expert who is widely accepted by their peers who is a “Mighty Brain”. In fact, a common trait of the very best practitioners and teachers (of any subject, not just IT) is that they are always willing to admit they do not know and to learn.

I did look for a way to ask for this particular Mighty Brain to be barred from the forum, but then I just decided to vent my spleen on my blog and have a glass of wine.

I hope that person trips over and really cracks their shins or something. Nothing permanent, just something incredibly painful. Grrrrrr.

Update – Jonanthan Lewis has commented to let me know that this “unskilled and unaware” {what a brilliant phrase} is the Dunning-Kruger effect. The link got filtered out by the comment mechanism, so I’ve posted it {well, maybe a similar one} here. Thanks Jonathan.

The Knowledge Curtain. June 8, 2009

Posted by mwidlake in development, Management, Perceptions.
Tags: , , ,
5 comments

I came across the concept of “The Knowledge Curtain” in the late 90’s, from a man called Lee Young, who I worked with for 2 or 3 years, along with a friend Mike Cox, who was key to showing me how to peek through the curtain.

The below is taken from a powerpoint presentation I sometimes give on project disasters (how to avoid, not how to encounter!):

When systems designers talk to end users, both parties usually end up not really understanding each other

When systems designers talk to end users, both parties usually end up not really understanding each other

The basic principle is that, the knowledge of your users/customers is partly hidden from you. When you ask people for whom you are designing a computer system about their job, they don’t tell you lots of stuff.

  • Things that they assume you know.
  • Things that it does not occur to them to mention.
  • Things that they do not want to admit to doing as it sounds silly or badly managed.
  • I’ve even had people not mention parts of their job as they don’t want their manager knowing they do it.

But that is OK, when you talk about information technology and computer systems to them, they have exactly the same problems with what you say :-).

Lee’s presentation, with N. Mehandjiev, predated the all-encompasing rise of the internet and this is one of the few references to it I can find. {Among the relatively few other hits on the topic, amongst the ones about knowing how to make curtains, are references to the “Knowledge Curtain” as the concept that the Web is not as available in other areas of the world. A related but very different issue}.

So, how do you peak beyond the knowledge curtain? Systems designers and developers with experience learn how to ask the questions “what are the exceptions to what  you have just told me” and “what else do you do” in many, many ways and without giving offence. After all, you have to keep asking these two questions and people naturally get irritated with that and some feel you are suggesting they are either stupid or lying. It can be tricky. 

I think that unless you have someone who is fully IT literate and has done the job the computer system is to assist with, you will inevitably only catch a part of the requirements.

For massive projects over many months or years, I think this lack of a clear understanding of what people do is a major factor to their failures. This is made even worse when the analysis is done by one group of people and the specifications are then shipped out to an external party for the application to be developed. With all that missing knowledge, it is no wonder some systems are initially so poor.

I only know of one method that reliably allows you really see beyond the knowledge curtain. That is prototyping and user feedback. Only when you show the people who are going to use the system what you have put together to help them will they know if it works. These sessions are incredibly valuable and only in development projects where they have been key to process have I seen the project deliver something truely useful.

I now have a general way of developing anything.

  • I ask the users for 3 or 4 major things that they want the system to do.
  • I develop those 3 or 4 features as quickly as possible and show them to the users.
    • One will be almost exactly what they need.
    • One or two will be close to what they need.
    • One will be utterly useless.
    • During the above, one or two critical new needs will come up.
  • Fix the close ones, dump or redo the useless one and add the new needs to the list.

Simply cycle around the above, asking for new features when you have got less than 4 features you  are actively working on. And only polish features (add all the nice screen touches and widgets) once is is exactly what they need or pretty damned close. {You hardly ever run out of features before you run out of time and money!} You end up with an excellent system that evolves to better help the customer as time goes on.

There are lots of development methodologies that have the above principle (or a variation of it) as their core, so I am certainly not the first person to find that this method works. Which is why I find it difficult to understand why so many projects don’t use it?

BTW, I thought I would just add that one of the factors in my starting a blog was a comment by Andrew Clarke on his, several years ago before Blogging really took off. It was for a presentation I did which included the Knoweldge Curtain concept. He was very nice about my presentation and I still appreciate it. This is the link, but as it is an archive you will have to search for my name.

Blogtastic June 7, 2009

Posted by mwidlake in Blogging.
Tags:
2 comments

I wonder how many blog entries world-wide have the title “blogtastic” or “blogging about blogs” or something similar.

I’ve been blogging properly for about 3 weeks now. Why did I start? 3 main reasons.

  • I forget stuff {I’m getting to that age} so I thought a blog was as good a place as any to stick stuff where I could find it.
  • I like to teach.  I know, it sounds a bit naff, but I honestly like explaining things and teching people stuff. If I was starting out on my career again, I would do more training.
  • Narcisism. There has to be an element of wanting to be noticed in anyone who blogs! I’d like to be a “C” list Oracle Name :-)

After a few weeks blogging, what have I learned?

  • I really like it when I get comments. It is less like talking to an empty room.
  • I am talking to an empty room! When I linked back to my blog from Jonathan Lewis’s blog my hits jumped from a half dozen to 80. They are heading back to a half dozen now.
  • Google does not pick up stuff just because it is on a blog. Which is maybe good as think how many spurious hits you would get for 99% of stuff and it is bad as, not only are people missing out on the great stuff I say, more worryinlgy, when I ask Google about some aspect of Oracle I know nothing about, how much great stuff am I missing?
  • It takes a lot of time to do rigorous explanations of oracle facts, which is what I have always demanded from my Oracle sources (and is why I use “-burleson” in my google searches).

I know my blogs are too long, I’ve been told. But then, they are supposed to be for my own benefit and I like to see why I decided what I think I know.

It takes a loooong time to say what you want to say. I’ve put a few basic techie things up and have not touched on my 2 other areas, VLDB and management. It is going to take me a long time to put down things I want to put down. I have a list of , ohhh, maybe 50 things to blog about already.

And last for now? It’s addictive. I want to put down everything now.I want people to find and read my blog now. I want my stats to be high.Why? Narcisism of course :-) But also because if I’m going to teach people there has to be people listening.

And really for last. Why do I want to teach? Well, the post on Consistent Gets says it all. When you teach people, you learn. The hardest questions often come from people who know the least about a topic.

Follow

Get every new post delivered to your Inbox.

Join 159 other followers