ba6.us - Dave's Database Related Stuff

On a recent post about reducing the high water mark in a table by shrinking it someone asked if you could tell how much “space” there was in a table that could be recovered by compressing it.

Yes. As pseudo-code:

select number_of_rows*average_row_length. That is how much data there is.
Divided by the block size, to give how many blocks-worth of data you have.
Divided this by a “overhead” fudge factor to allow for wasted space (see later) of about 20%
Compare to the number of blocks used by the table.

Here is some code to do it, showing some extra data and how we calculate the effective percentage of space used (pct_spc_used). It runs against an example table ‘HWM’

select uts.blocks blks_used ,uts.avg_space ,uts.num_rows ,uts.avg_row_len ,uts.empty_blocks empty_blks ,usse.blocks alloc_blks ,greatest(uts.blocks,1)/greatest(usse.blocks,1) pct_hwm ,uts.num_rows*uts.avg_row_len data_in_bytes ,(uts.num_rows*uts.avg_row_len)/8192 data_in_blks ,((uts.num_rows*uts.avg_row_len)/8192)*1.25 mod_data_in_blks ,(((uts.num_rows*uts.avg_row_len)/8192)*1.25)/usse.blocks pct_spc_used from user_tab_statistics uts ,user_segments usse where uts.table_name='HWM' and uts.table_name=usse.segment_name / -- BLKS_USED AVG_SPACE NUM_ROWS AVG_ROW_LEN EMPTY_BLKS ALLOC_BLKS PCT_HWM ---------- ---------- ---------- ----------- ---------- ---------- -------- DATA_IN_BYTES DATA_IN_BLKS MOD_DATA_IN_BLKS PCT_SPC_USED ------------- ------------ ---------------- ------------ 1630 1221 50000 213 34 1664 .97957 10650000 1300.04883 1625.06104 .97659918

I am collecting the data from USER_TAB_STATISTICS and USER_SEGMENTS. For this code to work you must have reasonably good stats against the table.

I’d like to run through some of the columns I have selected:
First of all, columns AVG_SPACE and EMPTY_BLKS are not populated by dbms_stats.gather_table_stats.. They are populated by the deprecated ANALYZE command that you should NOT use to gather table stats since V10 came along. These columns are populated as I did an ANALYZE to get the data in there, as well as a dbms_stats.
Next, I collect BLOCKS_ALLOCATED from DBA_SEGMENTS {and for this demo I just ignored the potential for partitioned tables) and I compare this to the BLOCKS_USED to get the High Water Mark, as a percentage of the table. I do this as EMPTY_BLOCKS is set to zero if you have never used ANALYZE and, even if you did, unless you use this deprecated command all the time, the value will not change.
On the second line of output I calculate the DATA_IN_BYTES as a simple num_rows*avg_row_len, convert it into blocks {for simplicity I do not collect the block size, I know it is 8k}. I then apply my “Overhead” fudge factor. A block has a header, using around 100 bytes {I’ve not checked the exact figure for years}, pctfree can be varied but defaults to 10% and as only whole rows fit, then an average of half a row of space is empty in each “full” block. Thus I reduce the space available by 20-25%. In this case, 25% as my rows are large.
Finally, I compare this modified data volume to the used blocks to get the actual space

Below I run through creating some test data, looking at the stats and my calculated High Water Mark and pct_space_used and finally shrink my table to see if my guesstimate is a reasonable guesstimate:

populate table drop table hwm purge; prompt populate table set feed on create table hwm (id number(10) ,num1 number(2) ,vc1 varchar2(100) ,vc2 varchar2(100) ) / insert into hwm select rownum ,trunc(dbms_random.value(1,100)) ,lpad('A',100,'A') ,lpad('B',100,'B') from dual connect by level < 50001 / 50000 rows created. exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'HWM') --where is the HWM compared to total segment size BLKS_USED AVG_SPACE NUM_ROWS AVG_ROW_LEN EMPTY_BLKS ALLOC_BLKS PCT_HWM ---------- ---------- ---------- ----------- ---------- ---------- -------- DATA_IN_BYTES DATA_IN_BLKS MOD_DATA_IN_BLKS PCT_SPC_USED ------------- ------------ ---------------- ------------ 1630 0 50000 210 0 1664 .97957 10500000 1281.73828 1602.17285 .962844262 NB AVG_SPC and EMPTY_BLKS are NULL. The high water mark is 1630 blocks out of 1664 in the segment My calculated PCT_SPC_USED is 96%. That is probably close enough. {remember, the last used block will be only partly used, accounting for a bit of the difference} -- I will use ANALYZE to fill the missing columns analyze table hwm compute statistics; BLKS_USED AVG_SPACE NUM_ROWS AVG_ROW_LEN EMPTY_BLKS ALLOC_BLKS PCT_HWM ---------- ---------- ---------- ----------- ---------- ---------- -------- DATA_IN_BYTES DATA_IN_BLKS MOD_DATA_IN_BLKS PCT_SPC_USED ------------- ------------ ---------------- ------------ 1630 1150 50000 213 34 1664 .97957 10650000 1300.04883 1625.06104 .97659918 Now those two columns are populated. Not the slightly different AVG_ROW_LEN even though dbms_stats used 100% (as the table is so small) and ANALYZE was compute -- clear 90% of the data randomly 45461 rows deleted. BLKS_USED AVG_SPACE NUM_ROWS AVG_ROW_LEN EMPTY_BLKS ALLOC_BLKS PCT_HWM ---------- ---------- ---------- ----------- ---------- ---------- -------- DATA_IN_BYTES DATA_IN_BLKS MOD_DATA_IN_BLKS PCT_SPC_USED ------------- ------------ ---------------- ------------ 1630 1150 4539 210 34 1664 .97957 953190 116.356201 145.445251 .087407002 PCT_HWM is not altered of course but PCT_SPC_USED has dropped dramatically. The table is now only 8.7% used, according to my calculations (compared to 90% empty) The BLKS_USED does not change. The AVG_SPACE and EMPTY_BLOCKS are the same as I used dbms_stats to update the statistics and it DOES NOT ALTER the columns that it does not populate. Thus you have no idea how recent those columns are if you use a mixture of commands. -- clear some blocks completely by deleting a range 2181 rows deleted. BLKS_USED AVG_SPACE NUM_ROWS AVG_ROW_LEN EMPTY_BLKS ALLOC_BLKS PCT_HWM ---------- ---------- ---------- ----------- ---------- ---------- -------- DATA_IN_BYTES DATA_IN_BLKS MOD_DATA_IN_BLKS PCT_SPC_USED ------------- ------------ ---------------- ------------ 1630 1150 2358 210 34 1664 .97957 495180 60.4467773 75.5584717 .045407735 Now the PCT_SPC_USED is down to 4.5% -- has EMPTY_BLOCKS changed if I use ANALYZE? analyze table hwm compute statistics; BLKS_USED AVG_SPACE NUM_ROWS AVG_ROW_LEN EMPTY_BLKS ALLOC_BLKS PCT_HWM ---------- ---------- ---------- ----------- ---------- ---------- -------- DATA_IN_BYTES DATA_IN_BLKS MOD_DATA_IN_BLKS PCT_SPC_USED ------------- ------------ ---------------- ------------ 1630 7682 2358 213 34 1664 .97957 502254 61.3103027 76.6378784 .046056417 As you can see, if I use ANALYZE AVG_SPACE alters. But EMPTY_BLOCKS does not, even though I cleared a chunk of the table. So there are blocks that can be reused but not listed as empty. I'll just take a quick side-step and show a quick "oddity" about dbms_stats --deleting the stats (using dbms_stats) exec dbms_stats.delete_table_stats(ownname=>user,tabname=>'HWM') BLKS_USED AVG_SPACE NUM_ROWS AVG_ROW_LEN EMPTY_BLKS ALLOC_BLKS PCT_HWM ---------- ---------- ---------- ----------- ---------- ---------- -------- DATA_IN_BYTES DATA_IN_BLKS MOD_DATA_IN_BLKS PCT_SPC_USED ------------- ------------ ---------------- ------------ 1664 dbms_stats.delete_table_statistics clears ALL statistics, even the ones it does not populate --and now collect them via dbms_stats again BLKS_USED AVG_SPACE NUM_ROWS AVG_ROW_LEN EMPTY_BLKS ALLOC_BLKS PCT_HWM ---------- ---------- ---------- ----------- ---------- ---------- -------- DATA_IN_BYTES DATA_IN_BLKS MOD_DATA_IN_BLKS PCT_SPC_USED ------------- ------------ ---------------- ------------ 1630 0 2358 210 0 1664 .97957 495180 60.4467773 75.5584717 .045407735 --now to shrink the table alter table hwm enable row movement; alter table hwm shrink space; BLKS_USED AVG_SPACE NUM_ROWS AVG_ROW_LEN EMPTY_BLKS ALLOC_BLKS PCT_HWM ---------- ---------- ---------- ----------- ---------- ---------- -------- DATA_IN_BYTES DATA_IN_BLKS MOD_DATA_IN_BLKS PCT_SPC_USED ------------- ------------ ---------------- ------------ 72 0 2358 210 0 80 .90000 495180 60.4467773 75.5584717 .944480896 So I calculated that there was about 75 blocks of data in that table. having shrunk it, I was a little bit out.

Having run through those examples we can see that the accuracy of the PCT_SPC_USED is down to the fudge factor employed but is probably close enough at 25%. After all, you are only likely to shrink a table that very clearly would benefit from it.

I had a tricky performance problem to solve this week. Some SQL was running too slow to support the business need. I made the changes and additions I could see were needed to solve the problem and got the code running much faster – but it would not run faster consistently. It would run like a dream, then run slow, then run like a dream again 2 or 3 times and then run like a wounded donkey 3 or 4 times. It was very frustrating.

For many this would provoke the cry of “Why won’t it work!!!”. But I didn’t, I was crying “What don’t I understand???”. {I think I even did a bit of fist-pounding, but only quietly as my boss was sitting on the desk opposite me.}

I think I’ve always been a bit like that in respect of How Things Work”, but it has been enhanced within me by being blessed to work with or meet people for whom it is more important for them to understand why something is not working than fixing it.

I was reminded of this by a thoughtful comment in an email that one of the oaktable sent to the list. They made the comment that what they felt was common between members of the oaktable is “that we’re not interested (really) in what the solution is of most of our problems, but actually, what is the underlying issue that really causes the problem?”

It struck a real chord with me. Quite a few people I’ve come across seem to be fixated on wanting to know solutions – so that they can look knowledgeable and be “one of the best”. But that’s just stamp collecting really. It’s like one of those ‘games card’ fads that each generation of children has, where you want to collect the best cards so you can win. I never got it as a kid as there are few rules, tactics, ‘how it works’ to elucidate. What success is there in winning when it’s just down to the cards you have? {And being candid, I didn’t like them as partly as I never had the money to buy many cards and partly I was rubbish at trading them. No sales skills.}

I know the solve-it-don’t-just-fix-it position is a topic I have touched on before, but I think the attitude of trying to fix problems by understanding how it works is far more satisfying than doing so by knowing a set of solutions. You develop a deeper understanding to help solve new problems than any amount of solution-stamp-collecting ever will. However, another wise voice on the Oaktable discussion pointed out that you can be in a work environment where there is no time to investigate and you simply have to try your set of fixes and move on if you hit one that works. Your work environment can strongly influence how you work and, it some ways, the ways you think.

I bet some people are wondering what my problem at the start of this post actually was? Well, a nice technical blog about it may appear over the weekend, but the core reason for the toggling of working/not-working was partition swap. We have data coming into the system very fast. We build a new summary of the key data in one table and then swap it into active play via partition swap. On the live system, stats had not been gathered on the “swap” table we had introduced but had on the active table. So, each time the partition swapped, we went from good stats to “empty” stats or the other way around. The empty stats gave a quite, quite dreadful execution plan.

I’m not a die-hard “Queen’s English”, “thou shalt not split infinitives” type but I am sick of people miss-using the word Unique.

The word unique means being one of a kind, the only example, the singular occurrence, the absolute only one. One. Singular. Get it? Still don’t get it? Well it means….unique! As a word that has only one unequivocal meaning, “unique” pretty much bloody well is it, by it’s absolute definition. It’s a yes/no situation. If you are unique in some respect, it means you are the only one example.

Now we lot in the database world should be bang on certain about this, what with Unique Keys and the like, and you would expect that other group of pedantic types – scientist – would be sticklers for the word as well. But no, last week I had someone who I thought was a good, solid IT person ask me “how unique” a situation was, I’ve just seen a scientist on TV describe a rock formation as “quite unique”. You can’t BE “quite unique”. You can be unusual, you can be quite rare, you can be uncommon. They all mean one of a few or a bit more blagh than usual. Unique means…The One. I can’t even think of another word that means “unique” in the way that word means. “One” and “Only” and “Singular” are close, but they all indicate something is unique. You cannot have a situation that is “quite ‘the only one’”. It is the only one or it is not the only one. Tick or cross. If you claimed a situation was unique only for someone to point out that it had happened before they would say “aha! So, it is not unique”.

It would be less of a linguistic stupidity to ask “how dead is the parrot – a bit dead or a lot dead or quite dead”. The parrot is in a binary state, dead or not. {As a biologist you can actually argue about this, but most of us accept the yes/no state of dead}. It is NOT “quite dead”.

Is Usain Bolt’s 100 meters fastest time Unique? Yes. He’s the fastest, not one of the fastest, not “fairly world record holding”.

Would it make sense to say “I have the fairly only stamp of it’s kind in my possession”? No. If someone said “this set of events have approximately never happened before” you would think “huh?” and ask for clarification – maybe ask “do you mean it’s a unique set of circumstances?” and would expect a yes or no answer. Only no, I would half expect “fairly unique”. Arrrgghh!!!

You know how it goes. You get a call/mail/text with something along the lines of “I need to know all the details of customer orders placed on Tuesday 7th by customers based in Botswana – and I need it ASAP, by end of play today at the latest”. So you skip lunch, drop that task you have been trying to get around to doing all week and work out how to resolve the issue that has just been dropped on you. It takes a lot of effort and you finally get it sorted out around an hour after you told your girlfriend/boyfriend/cat you would be leaving the office that day – and mail it off to the requestor. You might even call them to let them know it is done, but oddly they don’t answer.

Next day, you see the guy who wanted this urgent request and ask if it was what they wanted “Oh, I have not looked at it yet – but thanks for doing it.”

NO! “Thanks” does not work in this situation. I’d have more respect for this guy if he laughed at me and said “got you again, sucker”. Many of you know what I mean don’t you – if you are in a support-type-role, this can be a big part of your life.

I had a job years back that seemed to consist 90% of such tasks. I was the development DBA team leader responsible for testing, validating and promoting code to production. Everyone’s changes were Urgency Level 1, to be done as an emergency release and many could not be put in place until after 5pm. I’d be sat there at 18:30 in a massive but virtually empty office, applying changes along with one or two of my guys. Everyone else had gone home. This was not once or twice a month, it was 4 or 5 times a week. What are you to do?

Well, I came up with one tactic that seemed to work pretty well.

Anyone who asked for an emergency change had to be there, on site, available when the change was done.
There were of course cries of protest and people stated it was ridiculous that they had to be there, they were not needed, the change had been tested thoroughly {oh how I laughed at that – a thoroughly tested “emergency” change huh?}. No, I replied, you had to be there in case it went wrong as it’s your system, your data and, frankly, your emergency. If it is not urgent enough for you – the guy wanting it to be done – to be inconvenienced, well it sure as hell is not urgent enough to inconvenience me. “You can call if there are problems” – What, after you have escaped the locality? Maybe turned off your phone? And if I get you , I have to wait for you to come back in? No no no. Urgent emergency now equates to presence in office. After all, I’ll be there.

I stuck to my rule. If the requester could not be bothered to stay, I downgraded the request to “Planned” and put it through the CAB process. If the requester dumped on one of their team and made them stay, I mentally marked them half a point down and factored it in next emergency.

The change was remarkable. I was no longer in the office on my own every evening. I was not there with someone else either. I was simply not there as, when you made the emergency a little bit inconvenient to the requester, it magically stopped being an emergency.

There was another change. Less cock-ups. Seeing as these changes now went through the CAB process and slightly more testing {like, some testing} the duff changes were more likely to be detected before they caused damage. My bosses went from regarding me as “not a team player” to “Not a team player – but we kind of get your point now”.

So my advice is, if someone wants to try and make something your emergency, find some way of making sure it remains inconvenient to them. If they are willing to put up with the inconvenience, then it is a real emergency and you need to crack on with it.

{This blog is about shrinking tables where the High Water Mark is higher than it needs to be. Prompted by one of the comments, I wrote a follow-up post on finding the High Water Mark and tables that consist mostly of empty space, which would be candidates for shrinking.}

This blog is about one of those things I do as almost an autonomous “not thinking about it” performance housekeeping task, one which I have been meaning to mention for ages.

There can be quite a lot to gain by checking out full scans on “small” tables and seeing if it is as efficient as it can be. Sometimes it is, sometimes it is not. Often it is “good enough”. Occasionally it is awful.

Recently I was just casting an eye over the “top 20″ SQL on a system for any code generating a lot of consistent gets. I came across the below:

FRST_LOAD_TIME SQL_ID PRSE EXCS BUFFS DISCS RWS -------------------- ------------- --------- ----------- -------------- ---------- ------------ CPU_MS ELAPSD_MS SORTS DIR_W OPT_COST -------------- -------------- ---------- ---------- -------------- SQL_TEXT ---------------------------------------------------------------------------------------------------- 2-03-14/18:00:10 bk9b5u6zyvy59 36,262 36,262 90,634,158 7 36,261 320,102.3 326,920.9 0 0 546 SELECT count(*) RUNNING_SESSIONS from SNP_SESSION WHERE SESS_STATUS = :1 and AGENT_NAME=:2

It caught my eye as it struck me as a lot of buffer gets for what I thought was quite a small table and it is executed quite often. In this case, a few thousand times a day. It takes 2500 buffer gets per count(*). All buffered, but the CPU has to chunk through it.

I check, it is a small table:

ABCD01> select count(*) from dev_ODI_XXXX.snp_session COUNT(*) ---------- 73 ABCD01> @tab_lst Enter value for tab_name: snp_session TABLE_NAME OWNER NUM_ROWS ------------------------------ ------------------------------ ------------- SNP_SESSION DEV_ODI_XXXX 49 -- and a quick check on those key columns OWNER COLUMN_NAME NUM_DISTINCT N_NULLS LOW_V HI_V BKTS AVG_L -------- -------------------- ------------ ---------- --------------- --------------- ---- ----- DEV_ODI_ SESS_STATUS 4 0 D W 4 2 DEV_ODI_ AGENT_NAME 4 6 AAA_ODI_AGENT BBB_ODI_AGENT 4 13

The table IS small, only 73 rows. It is also interesting that the stats are out by quite a percentage, but the table is still seen as small according to the stats. This indicates the table is quite volatile, but consistently small. Another thing to notice is that the number of distinct values for each column in the WHERE clause is only 4 and thus an index lookup would not be very specific. With such a small number of records in the table and not very specific index I “know” that an index lookup would not be of much benefit {BTW Everything that you “know” in this way – do check it in reality every now and again…}. I don’t look at adding an index, I look at why such a small table needs so many buffer gets.

So, why so many buffer gets?

First of all, I need to be careful. The original source of information I was looking as was V$SQL and that holds cumulative stats for all executions of the same SQL statement. Thus the buffer gets could have been very high a while ago (say, yesterday) and now are small, and the average is 2500 BGs per select but right now it is running fine. So I verify that the code is still doing a lot of work for such a small number of records:

set autotrace on SELECT count(*) RUNNING_SESSIONS from dev_ODI_XXXX.SNP_SESSION WHERE SESS_STATUS = 'D' --:1 and AGENT_NAME= 'DEV_ODI_AGENT'--:2 RUNNING_SESSIONS ---------------- 1 Elapsed: 00:00:00.99 Execution Plan ---------------------------------------------------------- Plan hash value: 1837310352 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 15 | 546 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 15 | | | |* 2 | TABLE ACCESS STORAGE FULL| SNP_SESSION | 1 | 15 | 546 (1)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - storage("SESS_STATUS"='D' AND "AGENT_NAME"='DEV_ODI_AGENT') filter("SESS_STATUS"='D' AND "AGENT_NAME"='DEV_ODI_AGENT') Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 2013 consistent gets 0 physical reads

That’s still a lot of consistent gets to scan all of 75 rows and do a count (which is the sort aggregate step). A second to process and 2013 consistent gets to look at 75 rows in a skinny table – just trust me it is a skinny table. And the optimiser cost the full scan step at 546 “equivalent single IOs”.
{Oh, if anyone is wondering what the “table access storage full” and predicate step “storage(“SESS_STATUS”=’D’ AND “AGENT_NAME”=’DEV_ODI_AGENT’)” it’s because this system is an exadata box. Just ignore this for now, it is not relevant to this issue. I’ll blog about it *next week*}.

What is the problem?

Well, let’s see how big the table is.

ABCD01> @seg_dets Enter value for seg_name: snp_session Enter value for owner: dev_odi% Any Key> OWNER SEG_NAME SEG TS_NAME BYTES_K BLOCKS exts INI_K NXT_K -------- --------------- --- -------- ---------- --------- ---- ------- ------- DEV_ODI_ SNP_SESSION TAB DEV_ODI_ 16,384 2,048 31 64 1024 REPO USER

The table is 2048 blocks in size, the consistent gets to look at the table is 2013. That’s close.

The problem is when you full scan a table, it scans up to the high water mark of that table, ie the highest block that Oracle knows there has been data in. As Oracle inserts new data into a table and extends it, it keeps a note of the highest block data has been put into, the High Water Mark (HWM). When you delete data from a table, Oracle marks individual blocks to be reused as they empty (drop below a certain percentage full). But Oracle does NOT check to see if many blocks are being fully emptied and it can reduce the high water mark. In fact, there could well be a few records in the first block of the table and then nothing until a few records in the last block that has been used in the table, the High Water Mark. Oracle just scans all blocks and counts relevant records up to the high water mark. This is actually a very long-standing performance tweek, you can allocate GBs of data to a table but Oracle knows the very highest block it has put data into for that segment and only scans up to the High Water Mark. Usually, the HWM is useful.

I happen to know that this table used to have several thousand records in it but is now used far less extensively and holds only a few records. They are constantly deleted and new ones created. I have seen {very regularly, at least once a year} some extreme situations where a table has had many millions of rows in it at one point, setting the HWM to several hundred thousand blocks into the table segment, but the table now only holds a few hundred records. But a Full Table Scan scans all the way up to the HWM, taking several minutes to access a few hundred records. Often that table is being inefficiently cleared with a “delete from table_x” statement and then re-populated.

What is the answer? Well, I could truncate the table and set the HWM to zero, but this table actually holds some real data. I can’t truncate it in this situation. I could MOVE the table, ie get Oracle to recreate the table and move the existing data into it. That would set the HWM of the new segment to block one and then raise it as it inserts the data neatly into the first few blocks. The third option, and the one I took, is to SHRINK the table. This basically tells oracle to shuffle the data down to the start of the table and reset the HWM. You can do this on the fly and I have done it on relatively active systems, but I must give the usual advice to test this yourself and you may have locking issues on a busy system hitting the table you shrink very hard. Also, you do need to enable row movement on the table for Shrink to work, as is demonstrated below:

ABCD01> alter table dev_ODI_XXXX.snp_session shrink space cascade; alter table dev_ODI_XXXX.snp_session shrink space cascade * ERROR at line 1: ORA-10636: ROW MOVEMENT is not enabled ABCD01> alter table dev_ODI_XXXX.snp_session enable row movement; Table altered. ABCD01> alter table dev_ODI_XXXX.snp_session shrink space cascade; Table altered. Elapsed: 00:00:01.98

So, how does my little select perform now?

set autotrace on SELECT count(*) RUNNING_SESSIONS from dev_ODI_XXXX.SNP_SESSION WHERE SESS_STATUS = 'D' --:1 and AGENT_NAME= 'DEV_ODI_AGENT'--:2 RUNNING_SESSIONS ---------------- 1 Elapsed: 00:00:00.04 Execution Plan ---------------------------------------------------------- Plan hash value: 1837310352 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 15 | 546 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 15 | | | |* 2 | TABLE ACCESS STORAGE FULL| SNP_SESSION | 1 | 15 | 546 (1)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - storage("SESS_STATUS"='D' AND "AGENT_NAME"='DEV_ODI_AGENT') filter("SESS_STATUS"='D' AND "AGENT_NAME"='DEV_ODI_AGENT') Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 8 consistent gets 0 physical reads

Fabulous. The run time is very short and the number of consistent gets is tiny. You might notice that the Explain Plan still thinks the cost of the full scan will be 546 (ie the equivalent of 546 single block reads) but that is as I have not re-gathered stats. I left the system to do this automatically that night.

I’ll just mention one more thing. Shrinking a table is a bit like the old trick of rebuilding indexes to compact them. People misunderstood what rebuilding indexes was achieving and why the indexes were so much larger than they “needed” to be and would spend CPU and IO rebuilding indexes overnight – only for them to quickly explode back to the original size (creating a lot of overhead as they did). Shrinking oversized tables can have a great benefit but keep in mind that if some process pushes the HWM right up every day by shoving lots of data into the table and then clearing it down later, shrinking the table is just a waste of time. It gives benefit when the table really has shrunk down in size for a while. Or it only occasionally explodes to a massive size. I have in the past written “regular shrink” scripts for tables I know explode once in a while (eg once a week) but are usually small, but you need to know what the pattern is. Don’t just blindly shrink tables.

For me in this situation, that code scanning that table dropped out of my “top 20 most demanding SQL statements” and has stayed out of it for weeks. The code now completed in considerably under 0.1 seconds as opposed to a whole second – but no one has noticed. I’d removed about 5% of the workload of the system – but no one noticed. But it took me 1/2 an hour to investigate and fix and I’d freed up a non-trivial chunk of processing resource. In my Performance Tuning heart I know I did a good job on that day.

You probably all recognise this situation:

Dave needs something doing that he can’t do himself – let’s say it is creating an API for the file management package. It isn’t your job to do but it is something you can do. Dave is blocked until the API is created.

So, being a nice person, you tell Dave you will see what you can do for him over the next couple of days.

So why is it that what Dave hears is “Dave, I love you more than life itself, I am dedicated to this task and I WILL complete it before the end of tomorrow. My other tasks, emergency production issues and the untimely demise of my cat are all secondary to this endeavour.”.

You see, 24 hours later, Dave is at your desk “When will this be done?! I’m blocked until I get this!!!”. If he’s the guy I had recently his next step is to slap his fist into his palm as he utters, almost shouts “I NEED this!”.

No. No you don’t need it. What you need is for that slap to be in your face, followed by “wake up! You don’t go shouting at the guy digging you out the hole!”.

I find this particularly unacceptable when the favour is to be fixing some mess that Dave created, or doing something Dave told his boss he had finished last week. Of course, those are the exactly situations where Dave is most likely to get upset, as he is in real trouble and most likely to commit that ultimate Favour sin:-

Dave to Boss “I Didn’t get my task done as Martin promised to create the API and he hasn’t. I’d be there now if I only did it myself”.

If you are thinking “Hmmm, I think I might have been ‘Dave’ recently” then Shame On You and go beg forgiveness. Of course, if you were ‘Dave’ you may well be the sort of sod who will twist the situation around in your head so it was not your fault anyway. Grrr, bad Dave.

For a while I gave up doing work favours as I got sick of the situation above playing out. Then I started doing favours again but being a bore about saying repeatedly, up front, that this was a favour, it was only if I had time, not to rely on me and, if it is that important, go ask someone else. Yeah, sounds really grumpy doesn’t it? That gave me a reputation for not being a Team Player (which is code for “mug”).

Now I have a rule system. As soon as someone starts getting demanding about the favour, I immediately challenge it. If they get shouty they lose their favour rights. No more favours for you until the requisite number of beers have been bought. It’s three.

Of course, you see this scene played out on help forums all the time. Initial message is nearly always in upper case text speak “PLS HLP ME, IS URGNT! CN U TELL ME HOW 2 DO MY JOB – THNX!!!” and soon degrades into helfull person asking for details of the exact person and Mr Shouty demanding more and more help. I don’t help. After all, this guy is never going to buy me a beer.

The last day of the three (and second on the ferry and of the conference proper) had a lot of talks I wanted to see, especially Dan Morgan talking about “Insanely large Databases”. It was a good talk, with an interesting interlude when a very loud announcement let us know we had docked at Kiel. Dan handled it with aplomb. Dan was talking about one specific experience he had suffered recently and he covered quite a few things I did and some I planned to but never got that far – but he had more technical details about the issues he had encountered, so all in all probably of more immediate use to the audience than my talk. It was a very good session. I have to confess, there were times I laughed out loud as memories flooded in, prompted by his wise words – I fear others may have interpreted differently but, honestly, I was laughing tears of shared pain.

I was also looking forward to seeing Uwe Hesse talk about Dataguard. I’d had the pleasure of spending a lot of time and a few beers chatting with Uwe over the last few days. His presentation was very well done (as it should be, he’s a professional trainer! He exceeded my expectations, though). And I loved the last bit, where he demonstrated how, under 11G R2 (R1 as well???), if you have a physical standby, a block corruption can be fixed “on the fly” and invisibly to the end user. I just love that feature and, though I knew about it already, seeing it demonstrated and the errors appearing in the alert log – though the user query runs fine – was sweet.

The rest of the sessions I saw were also good {Maria Colgan on preventing sub-optimal plans which was, mostly, about avoiding implicit data conversions, which I think all developers and designers should have drummed into their heads with rubber hammers; Doug Burns on those OEM performance graphs which continue to get better and better} – but I had to given in and go for a sleep. These anti-seasickness pills seem to work but make me dozy. I’d love it if those anti-travel-sickness pills were really placebos and I had a placebo side effect

The last day was rounded off with a nice meal and one or two (or three, or four) beers in a bar and some excellent times. I of course spent time with the Other Martins (we could not disband the cluster too easily), Doug, Holger, Maria, our Norwegian hosts and many more of the other people there. If only I had managed to fit in the other 10, 15 people I wanted to see but I’m getting old and I was very, very, very tired.

I have to say, it was one of the best conferences I have ever been to. OUGN 2013? Get yourself on that boat.

As I said yesterday, I am not one for blogging about conferences. So what the heck, I’ll do another post on this one .

You might have picked up on the fact that I am not very good on the sea and have a lot of nervousness about this idea of spending 2 days on an ocean-going liner. So today is the day we move over to being on the water, the OUGN conference proper. I’m delighted to say I am not the only one nervous about this boat lark, Marie Colgan {or, as I should be calling her, “The Optimizer Lady” – since Doug Burns christened her back at the UKOUG 2011 conference} feels the same. There is nothing better to reduce one’s apprehension about something than finding someone else who is just as if not more anxious about it. I suspect this is an evil way to think but I can’t help it.

The day went well, my overview of VLDBs could have maybe been a little more polished – I think I was trying to cover too many topics and not spending enough time on anything, apart from “why a small number of spindles is bad” which I spent too long on. But I did make two points that I think are major considerations with VLDBs and are actually not very technical. (1) If you are going to have to look after a massive database, try very, very, very hard to keep everything as simple, standardised and controlled as you can. (2) Find other people to talk to about VLDBs. Most companies that have them are reluctant to talk, either because of the industry type (Defense, Banks, Pharma) or a perceived giving away of corporate advantage (Banks, Pharma, Utilities and pretty much all companies really).

Anyhow, having done my talk I was now free for the next 2 days to enjoy other talks, socialise, relax – and keep internally checking if I felt nauseous or not. The sea has turned out to be very calm and the boat very stable. But I keep checking and, of course, that makes me feel a little anxious – and thus ill.

However, I have to say that the travel sickness pills I have been taking do seem to be very effective. They are effectively in making me feel woozy. But, and this is important, not ill. I’m having to rely on beer for the latter.

One thing I really, really like about this conference. Everyone is stuck on a boat, they can’t escape. Which means you get to see lots of people all day and it makes the whole thing have a nice sense of community.

Right, Maria is about to present again. I’m going to go and sit in the front row and sway lightly from side to side. Apparently it makes her feel even worse…

I’m not really one for blogging about conferences – I mean who cares what someone else saw being presented? But this is the first time I have stopped moving long enough (and been in contact with the internet world) to blog and my brain is too fried to do a technical one.

The journey here was very smooth and the train from the airport to central Oslo makes the ratty, tatty, confined coaches of the UK look as awful as they really are. So I arrive in the central station and decide I will need some local Kroner to pay the taxi to the hotel. So I find an ATM, put in the card and ask for money. Card refused. Huh? But I rang my bank at the weekend and let them know there would be transactions from a foreign country (after some issues a couple of years back my wife always lets the bank know when she will be away and she made me do the same). I tried another ATM from a different company. Refused. OK, damned bank, I’ll use my other card. Refused. Errrr…… In a foreign country, no idea really where I am, not got any local currency. Not looking good.

I wander into Oslo looking for a real, proper bank. Most Norwegians speak perfect English, maybe if I still have problems I can go in and ask. Find bank, go to ATM, about to ask for money….Notice the figures being suggested on this ATM are a lot smaller than the last place. Yes, I had my mental decimal place in the wrong location and I had been asking for almost £1,000 rather than £100. No wonder the request got refused (I keep my daily limit low, it stops be buying too much rubbish on any given day).

So, I head off to the event, get there for Lunch and have a great afternoon. My presentation on IOTS went well and, as I said yesterday, I think I’ll put it to sleep for a while now.

I saw Harald Van Breederode talk about Oracle Database Smart Flash Cache before I did my slot and I always like to hear Harald talk. It was good as ever, but I found myself not so much interested in the idea of using SSD-type storage as an extra “slow SGA” extension (as opposed to a “fast storage” extension) but more that in 3 or 4 years, memory-based storage will be the default and a whole swathe of my knowledge will once more become redundant. I mean, how important will it be to keep physical reads down via things like IOTs once physical reads are relatively cheap? You won’t really care much about expanding your SGA with a secondary cache when you have 4TB of main memory and 100,000 IPS (inputs per second, we will have to see how much faster Output can be made with memory-based storage).

This highlights one of the things I really like about conferences, meetings and chatting to fellow techies in the pub. The actual main topic or point might well be interesting but the secondary thoughts and ideas can be just as striking. I was talking to Uwe Hesse after the talks had finished and part of that was about learning new stuff and training courses. It made me realise that it is way too long since I ran any training courses. I love running training courses.

OK, I’ve had a rest, back to socialising with fellow OUGN 2012 attendees.

I’m just doing some last minute preparation for the Norwegian User Group Spring Seminar. This is quite a large conference with 5 concurrent streams and a very good line-up of presenters. You can see the agenda here.

What is also a little unusual about this conference is that it is mostly on a boat, or rather a ship. When I was first asked if I would put forward a talk or two I declined – as I am very prone to being sea-sick. Truls Bergersen, who helps organize the event, got in touch with me a few weeks later and said “we are doing a day on land first, do you want to present then?”. Sure! That would be great! So I am, I’ll be doing my talk on Index Organized Tables once more {and then I think I’ll put it to bed for a couple of years}.

Now, I am not exactly sure what made me agree to this, but then Truls asked if I would consider doing another talk I had suggested, on VLDBs (Very Large DataBases), the following day. Yes, why not?

Only, the next day we are on the ship. I’m a bit nervous about this. I don’t think that a good presentation should include the presenter going green and dashing for the door (though I did have a similar experience at Oracle Open World once, as a result of an all-you-can-eat Chinese meal the day before, but as it affected “the other end of things” you really don’t want details of that).

Thankfully, I’ve not been worrying about sea-sickness for the last couple of weeks. That will be because my laptop hard disk died and I lost everything on my still-new machine. It was still so new that I had not started backing it up properly – after all, there was nothing on there I could not replace easily.

It has been a real trial to replace all those things that I could easily replace. At least I had the presentations and some other critical bits on my temporary USB stick backup…

I’m kind of expecting to get a bit of a comment-kicking over this one…

I never much liked mobile phones – Yes they are incredibly useful, yes they allow countries that lack a ground-based telephony network to create a nationwide system, yes they allow communication all the time from almost anywhere. That last point is partly why I dislike them. {Actually, I don’t like normal phones much, or how some people {like my wife} will interrupt a conversation to dash across the room to answer it. It’s just a person on the phone, it will take a message if someone wants to say something significant. If someone calls your name out in a crowd, do you abandon the people you are talking to, dash across the room and listen to them exclusively? No, so what act that way over a phone?}.

However, I hold a special level of cynical dislike for “smart” phones. Why? Because people seem to be slaves to them and they seem to use them in a very antisocial way in social and even business situations. It is no longer just speaking or texting that people do, it’s checking and sending email, it’s twittering and blogging, it’s surfing the net and looking things up. I have no problem with any of this, I do all of these things on my desktop, laptop, netbook. But I don’t do them to the detriment of people who are there in the flesh – whilst supposedly in a conversation with mates at the pub or carrying out a transaction in a shop or using the coffee machine at work or, basically, standing in the bloody way staring at a little screen or rudely ignoring people who I am supposed to be interacting with.

The below is my phone. It makes calls, it sends texts, it might even be able to work as an alarm clock (I am not sure). It does not do anything else much and it was ten quid {actually the below might be the version up from the really cheap thing I have}:

I was pondering this rude (ab)use of Smart Phones in a meeting this week. It was a meeting to discuss a program of work, what needed doing and by whom. It was a meeting where everyone in the room was involved, each person’s opinion was important and we all had a vested interest in the outcome of the meeting. So why did over half of the people not only have their Smart Phone out but were tapping away, scrolling through stuff, looking at some asinine rubbish on Facebook {yes, I saw you}? One or two people in the room might have been able to argue that they needed to keep an eye out for important emails or calls – but really? Are things so incredibly important and only you can deal with them that you can’t just play your full part in a meeting for an hour? I was so annoyed by this that I missed half the meeting internally moaning about it…

I just see it as rude. It’s saying “while you people are talking, I can’t be bothered listening and I certainly don’t need to give you my full attention. And I don’t even care that I’m making it so obvious”. Or “I am buying this item from you and we need to deal with the transaction but you are so inconsequential I don’t even have to pause this conversation about which cafe to meet in next week. You do not deserve more than 15% of my attention”.

I supposed that is what really gets my blood slowly heating up, it’s that it has become accepted to be so rude. Just walk down the street, head down and eyes fixed on your glowing little screen, making no attempt to navigate with your fellow city dwellers. I made a decision 2 {correction, 3} years ago that, if you are walking along staring at your phone and you are going to collide with me, you ARE going to collide with me if you do not become aware of me and make allowances – and I am lower down than you, I braced my shoulder and I am going to win this one. If they are so fixated on that bl00dy screen that they do not heed any attention to others, people ping off me like they’ve been thumped by a tree stump. It now happens a lot and I always “win”. I’m surprised no one has punched me yet.

If I was a manager again I would introduce a simply rule. No Smart Phone in your hand unless you have a stated reason for doing so. There are many valid reasons, which will all be related to the meeting. Otherwise you are just being disrespectful. If you feel the meeting does not apply to you or this section is not relevant, fine. Sit still and listen anyway. You might actually find it useful to know what everyone else is doing. Stop playing bl00dy mental chickens or whatever or updating your status to “bored”.

I will hold strongly to these opinions. Right up until the minute I finally buy that iphone I’ve been considering getting. I really want to be able to check my twitter account during meetings, you see.

A colleague came to me a couple of days ago with a SQL problem. He had something like this:

@get_source NAME INPUT ------------- ----- GROUP_1 5 GROUP_2 3 GROUP_3 4 GROUP_4 7 GROUP_5 3

What he wanted to do was multiply all the inputs across the groups, to get the total number of possible permutations. ie 5*3*4*7*3. The product of all the INPUTS. This has to be in straight SQL. Easy! You just need to… Ahhh… Hmmm…

No, I just could not think of a way to do it that my colleague could use.

- There is no group-by function that gives a product of a column {that I know of}
- We could not use PL/SQL {for reasons I won’t go into}, so no cursor looping or passing in an array, which would make it simple
- Neither of us could think of an analytical function that could take the result of itself in the prior row (though I suspect there may be a way to do it).
- The number of groups could and would vary from a few to possibly a hundred, so the old tricks of converting rows to columns or nesting so many sql statements would not help.

So, I asked my friend – the queen of SQL Query, {Boneist} {Oh she of the trombone playing pastime}.

She came straight back with an answer. In case you want to try and work out an answer yourself before seeing the solution, below is a cat picture. The answer she came up with is below that:

The key to the solution is natural logs {ln}. I don’t know about you, but I learnt about using logs at school and have pretty much not used them since. In summary:

If x=3*5*9
then ln(x) = ln(3)+ln(5)+ln(9)
= 1.09861+1.60944+2.19722
= 4.90527

ie using log converts multiplication to addition. You then use EXP, the inverse of ln, to convert your added-up log value into your result.

exp(4.90527) = 135

{NB if you use my figures, exp(4.90527) realy equals 134.999355, as I have truncated the log values shown. Oracle does this far more accurately internally but be aware you might get some slight rounding errors).

So, what we can do is simply use the SQL GROUP function SUM to add together the natural logs of all the rows:

sum(ln(input))
{grouped by the whole statement, so no group by is needed in this case}

As an example:

-- show the expected result first select 3*7*4*5*1 from dual; 3*7*4*5*1 ---------- 420 select min(name),max(name),count(name) ,EXP (SUM (LN (gr_sum))) gr_prod from (select 'group_1' name, 3 gr_sum from dual union select 'group_2' name, 7 gr_sum from dual union select 'group_3' name, 4 gr_sum from dual union select 'group_4' name, 5 gr_sum from dual union select 'group_5' name, 1 gr_sum from dual ) / MIN(NAM MAX(NAM COUNT(NAME) GR_PROD ------- ------- ----------- ---------- group_1 group_5 5 420

As you can see, it works – even if when you first look at the formula your brains {if you are not a mathematician} try to leak out of your head. Just try and remember what your mathematics teacher said about log books and how, before calculators, they were used to speed up manual long multiplication tasks by converting the task into log addition.

If you want more information on logs, see this discussion about how they are actually about growth or wikipedia if you must .

Boneist actually pointed me to this very nice post about using logs in oracle by Anju Parashar, which I have borrowed from.

One issues to be aware of (which is highlighted in Anuj Parashar’s article) is that you can’t get a log of negative values, as a consequence Oracle will give you an ora-01428 error:

select ln(-3) from dual;
select ln(-3) from dual
*
ERROR at line 1:
ORA-01428: argument ‘-3′ is out of range

Anuj gives a version of code that works if all values are negative, below I have one that copes with any number of negatives. Basically, you convert all the values to be multiplied to positive values and then make it negative if the count of negative values is odd. Mathematically, the result of a multiplication can ONLY be negative if there are an odd number of negative values.

,EXP (SUM (LN (abs(gr_sum))))
*decode (mod(sum(decode(sign(gr_sum),0,0,1,0, 1)),2)
,0,1,-1) correct_gr_prod

I’m sure that the above expression could be simplified, but I have to go and do the day job.

Finally, here is a little set of test cases covering the above, so you can play with this.

mdw1123&gt; select 3*7*4*5*1 from dual; 3*7*4*5*1 ---------- 420 1 row selected. mdw1123&gt; -- mdw1123&gt; select 'group_1' name, 3 gr_sum from dual 2 union 3 select 'group_2' name, 7 gr_sum from dual 4 union 5 select 'group_3' name, 4 gr_sum from dual 6 union 7 select 'group_4' name, 5 gr_sum from dual 8 union 9 select 'group_5' name, 1 gr_sum from dual 10 / NAME GR_SUM ------- ---------- group_1 3 group_2 7 group_3 4 group_4 5 group_5 1 5 rows selected. mdw1123&gt; mdw1123&gt; select min(name),max(name),count(name) 2 ,EXP (SUM (LN (gr_sum))) gr_prod 3 from 4 (select 'group_1' name, 3 gr_sum from dual 5 union 6 select 'group_2' name, 7 gr_sum from dual 7 union 8 select 'group_3' name, 4 gr_sum from dual 9 union 10 select 'group_4' name, 5 gr_sum from dual 11 union 12 select 'group_5' name, 1 gr_sum from dual 13 ) 14 / MIN(NAM MAX(NAM COUNT(NAME) GR_PROD ------- ------- ----------- ---------- group_1 group_5 5 420 1 row selected. mdw1123&gt; -- mdw1123&gt; -- now with a negative mdw1123&gt; select 'group_1' name, 3 gr_sum from dual 2 union 3 select 'group_2' name, -7 gr_sum from dual 4 union 5 select 'group_3' name, 4 gr_sum from dual 6 union 7 select 'group_4' name, 5 gr_sum from dual 8 union 9 select 'group_5' name, 1 gr_sum from dual 10 / NAME GR_SUM ------- ---------- group_1 3 group_2 -7 group_3 4 group_4 5 group_5 1 5 rows selected. mdw1123&gt; -- and if the values contain negatives mdw1123&gt; select min(name),max(name),count(name) 2 ,EXP (SUM (LN (abs(gr_sum)))) gr_prod 3 ,mod(sum(decode(sign(gr_sum),0,0 4 ,1,0 5 , 1) 6 ),2) -- 0 if even number of negatives, else 1 7 modifier 8 ,EXP (SUM (LN (abs(gr_sum)))) 9 *decode (mod(sum(decode(sign(gr_sum),0,0,1,0, 1)),2) 10 ,0,1,-1) correct_gr_prod 11 from 12 (select 'group_1' name, 3 gr_sum from dual 13 union 14 select 'group_2' name, -7 gr_sum from dual 15 union 16 select 'group_3' name, 4 gr_sum from dual 17 union 18 select 'group_4' name, 5 gr_sum from dual 19 union 20 select 'group_5' name, 1 gr_sum from dual 21 ) 22 / MIN(NAM MAX(NAM COUNT(NAME) GR_PROD MODIFIER CORRECT_GR_PROD ------- ------- ----------- ---------- ---------- --------------- group_1 group_5 5 420 1 -420 1 row selected.

Sometimes I can become slightly annoyed by the silly way the media puts out total tosh and twaddle(*) that over-states the impact or drawbacks about technology (and science ( and especially medicine (and pretty much anything the media decides to talk about)))). Occasionally I get very vexed indeed.

My attention was drawn to some such thing about SSDs (solid State Discs) via a tweet by Gwen Shapira yesterday {I make no statement about her opinion in this in any way, I’m just thanking her for the tweet}. According to Computerworld

SSDs have a ‘bleak’ future, researchers say

So are SSDs somehow going to stop working or no longer be useful? No, absolutely not. Are SSDs not actually going to be more and more significant in computing over the next decade or so? No, they are and will continue to have a massive impact. What this is, is a case of a stupidly exaggerated title over not a lot. {I’m ignoring the fact that SSDs can’t have any sort of emotional future as they are not sentient and cannot perceive – the title should be something like “the future usefulness of SSDs looks bleak”}.

What the article is talking about is a reasonable little paper about how if NAND-based SSDS continue to use smaller die sizes, errors could increase and access times increase. That is, if the same technology is used in the same way and manufacturers continue to shrink die sizes. It’s something the memory technologists need to know about and perhaps find fixes for. Nothing more, nothing less.

The key argument is that by 2024 we will be using something like 6.4nm dies and at that size, the physics of it all means everything becomes a little more flaky. After all, Silicon atoms are around 0.28nm wide (most atoms of things solid at room temperature are between 0.2nm and 0.5nm wide), at that size we are building structures with things only an order of magnitude or so smaller. We have all heard of quantum effects and tunneling, which means that at such scales and below odd things can happen. So error correction becomes more significant.

But taking a reality check, is this really an issue:

• I look at my now 4-year-old 8GB micro-USB stick (90nm die?) and it is 2*12*30mm, including packaging. The 1 TB disc on my desk next to it is 24*98*145mm. I can get 470 of those chips in the same space as the disc, so that’s 3.8TB based on now-old technology.
• Even if the NAND materials stay the same and the SSD layout stays the same and the packaging design stays the same, we can expect about 10-50 times the current density before we hit any problems
• The alternative of spinning platers of metal oxides is pretty much a stagnant technology now, the seek time and per-spindle data transfer rate is hardly changing. We’ve even exceeded the interface bottleneck that was kind-of hiding the non-progress of spinning disk technology

The future of SSD technology is not bleak. There are some interesting challenges ahead, but things are certainly going to continue to improve in SSD technology between now and when I hang up my keyboard. I’m particularly interested to see how the technologists can improve write times and overall throughput to something closer to SDRAM speeds.

I’m willing to lay bets that a major change is going to be in form factor, for both processing chips and memory-based storage. We don’t need smaller dies, we need lower power consumption and a way to stack the silicon slices and package them (for processing chips we also need a way to make thousands of connections between the silicon slices too). What might also work is simply wider chips, though that scales less well. What we see as chips on a circuit board is mostly the plastic wrapper. If part of that plastic wrapper was either a porous honeycomb air could move through or a heat-conducting strip, the current technology used for SSD storage could be stacked on top of each other into blocks of storage, rather then the in-effect 2D sheets we have at present.

What could really be a cause of technical issues? The bl00dy journalists and marketing. Look at digital cameras. Do you really need 12, 16 mega-pixels in your compact point-and-shoot camera? No, you don’t, you really don’t, as the optics on the thing are probably not up to the level of clarity those megapixels can theoretically give you, the lens is almost certainly not clean any more and, most significantly, the chip is using smaller and smaller areas to collect photons (the sensor is not getting bigger with more mega-pixels you know – though the sensor size is larger in proper digital SLRs which is a large part of why they are better). This less-photons-per-pixel means less sensitivity and more artefacts. What we really need is maybe staying with 8MP and more light sensitivity. But the mega-pixel count is what is used to market the camera at you and I. As a result, most people go for the higher figures and buy something technically worse, so we are all sold something worse. No one really makes domestic-market cameras where the mega-pixel count stays enough and the rest of the camera improves.

And don’t forget. IT procurement managers are just like us idiots buying compact cameras.

(*) For any readers where UK English is not a first language, “twaddle” and “tosh” both mean statements or arguments that are silly, wrong, pointless or just asinine. oh, Asinine means talk like an ass {and I mean the four-legged animal, not one’s bottom, Mr Brooks}

My last post was on slow dictionary queries caused by having many, many thousands of extents in your database. This post is about a special case of this problem, which I encountered recently. It was on an Exadata box – Exadata is not particularly relevant to the issue, but I’m curious to see if mentioning Exadata will make this post more popular

I was querying the used and free space on a very small database on the client’s X2-2 1/4 rack Exadata machine. The query was really slow, taking about 30 seconds. This is a FAST box, what is going on?

I quickly realised that the problem was specific to one tablespace:

>@SPC_SUM Enter the tablespace (or leave null)&gt; DATA_01 TS_NAME ORD SUM_BLKS SUM_K MAX_CHNK_K NUM_CHNK -------------------- ----- ----------- ------------ ----------- -------- DATA_01 alloc 262,144 2,097,152 2,097,152 1 free 63,128 505,024 504,384 11 2 rows selected. Elapsed: 00:00:00.21 &gt; @SPC_SUM Enter the tablespace (or leave null)&gt; USERS TS_NAME ORD SUM_BLKS SUM_K MAX_CHNK_K NUM_CHNK -------------------- ----- ----------- ------------ ----------- -------- USERS alloc 748,320 5,986,560 5,372,160 2 free 127,904 1,023,232 6,144 3,058 2 rows selected. Elapsed: 00:00:26.05

We can see it takes 0.21 seconds for tablespace DATA_01, 26.05 seconds for the USERS. Yes, USERS is larger but not 150 times larger. What is going on? Well, as the title of this post suggests, it is down to dropping tables. The below shows my route to that conclusion.

What I could see was that for the USERS tablespace the maximum chunk of free space was relatively small compared to the sum of free space – 6MB out of 1GB, pretty much – and that there was a lot of individual pieces of free space, 3,058. This tablespace was shattered into a lot of bits. So, what sort of extent size management do we have? How big are the tables and indexes in this tablespace {NB I already knew we had no partitions so I did not have to worry about that}.

select tablespace_name,initial_extent,next_extent ,extent_management,min_extlen,allocation_type from dba_tablespaces where tablespace_name ='USERS' TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN MIN_EXTLEN ALLOCATIO ------------------------------ -------------- ----------- ---------- ---------- --------- USERS 65536 LOCAL 65536 SYSTEM select tablespace_name,blocks,count(*) from dba_extents where tablespace_name = 'USERS' group by tablespace_name,blocks having count(*) >1 order by blocks desc,tablespace_name TABLESPACE_NAME BLOCKS COUNT(*) ------------------------------ ---------- ---------- USERS 2560 2 USERS 2048 3 USERS 1536 7 USERS 1408 5 USERS 1280 2 USERS 1248 2 USERS 1152 2 USERS 1024 229 USERS 896 15 USERS 768 21 USERS 736 3 USERS 720 3 USERS 704 2 USERS 672 2 USERS 640 25 USERS 624 2 USERS 576 2 USERS 512 117 USERS 400 2 USERS 384 34 USERS 360 2 USERS 312 2 USERS 288 4 USERS 256 49 USERS 248 2 USERS 240 2 USERS 192 5 USERS 160 4 USERS 128 1165 USERS 8 1788 30 rows selected.

So we have system controlled extent allocation, this should be fine. I did a quick check of the table contents – 300 or so tables and 200 or so indexes. And, as you can see from the check of extents above, very few larger extents and a lot of small ones. {As a little aside, note the relatively large number of 8-block, 128-block, 256-block and 1024-block extents; These are the sizes that LMTs with system managed extents tend to use unless a similar sized chunk is found to be available, in which case it is used}.

I did some other checks on indexes and segment sizes and it all boiled down to one thing. There were not a lot of things in this tablespace, what was there was small and… The total of all those table and index blocks was way less than the used space in the tablespace.

That is the major indicator of what is going on here. The difference in space used in the tablespace and the total of the visible tables and indexes.

The last link in the chain is the recycle bin.

select owner,ts_name,count(*) from dba_recyclebin group by owner,ts_name OWNER TS_NAME COUNT(*) ------------------------------ ------------------------------ ---------- USER1 USERS 542356 USER1 2 WEGWEGWEG USERS 97 KKKUKUYLLX USERS 149 USOVFPKEKS USERS 3 .... ERHJTRTTTURT USERS 4 11 rows selected.

That’s 542,356 objects in the recyclebin for one user, in the one tablespace. My problem tablespace. The penny clicked, something I had already noticed and was a little uncomfortable about fell into place.

The client is using Oracle Data Integrator (ODI) to pull data together and put it into the database. The process they are using basically creates a table, uses it to load some data into and then pushes the data into the target tables. Then drops the table. The drop does not do a “DROP TABLE … PURGE;”. This is done many, many times per load cycle, which is intended to run several times a day.

Something you should always keep in mind with the recyclebin is that the tables, indexes and their extents that go into the recycle bin do not get reported in several of the key data dictionary views. I did mention this in a blog post way back but maybe a whole post on it is called for.

So, the tablespace was being shattered by the constant creating and dropping of small tables. It was hidden from easy view due to how the recyclebin is (not) exposed in data dictionary views.

It is not good practice to constantly create and drop lots of tables. As well as the admittedly rather odd impact that this posting is all about, there is a lot of internal work involved for the oracle database in creating and dropping tables. It is an overhead best avoided. The client had good reasons for this approach but now they are going to look at the alternatives.

I’ve not actually proven in this posting that all those dropped tables is the actual cause of the slow performance in querying the free space in that tablespace {though the corroborative evidence is very strong}. Neither have I said how we cleared up the mess. I’ll cover both of those in the next post on this mini-thread.

Are you finding queries to identify free space, the size of segments or details of extents in your database are very slow? You could have a database with a very, very large number of extents. If you are on version 9 or up, I would suggest you check the extent management settings for any tablespace holding large segments (see later).

{Caveat – I’m writing this posting based on what I remember of some issues I had on two V10.2 databases 2 and 3 years ago respectively, and those systems are no longer available to me. So please treat everything I say here with some caution – I’ve not checked and verified it to the level I normally would. But I wanted to move this information from another posting I am writing on a recent experience I will verify…}

First, what this article is NOT about. It has been known for a very long time that creating tables with a large number of extents can have a negative impact on SQL performance on that table. Except it never really did and it was a myth. Way back prior to Oracle 7.3 you could only create so many extents per segment, depending on block size, but that was another issue. It used to be argued that SQL select against such tables with many extents was slower. I did some tests and it was not – unless you were in the strange situation where your extent size was less than your multi-block read count, and even then the impact was not huge, it was a slowdown of a few percent to maybe 25%.

However, dropping such tables, truncating such tables and queries against the dictionary objects that deal with extents and free space could and still can become very slow. As we have progressed through the Oracle versions from 8 ,9 and 10 this problem has become less common and the impact has become less, mostly due to Locally Managed Tablespaces (LMTs) and Automatic Segment Space Management {though that is more in respect of concurrent DML than select}.

LMTs in particular have generally removed the issue. If you do not use LMTS and have no very,very pressing reason to not {like 3rd party support for applications}, then swap to LMTs. LMTs have been the default for user defined tablespaces since oracle 9 and have two options. Uniform (Fixed) extent sizes or Automatic, where oracle uses 8 block extents for a new segment to start, then 64 block extents, then 1024 block extents as the segment grows {I might be wrong on the exact size details but don’t worry about it, I certainly don’t}. You can check the settings for tablespaces as is demonstrated below. I create two tablespaces, one with uniform extent sizes and then one with automanaged extent sizes, and check the relevant information (this is on 11.2.0.3):

create tablespace mdw_uni_1m datafile 'C:\APP\MARTIN\ORADATA\MDW1123\MDW_UNI_1M' size 100m extent management local uniform size 1M; create tablespace mdw_auto datafile 'C:\APP\MARTIN\ORADATA\MDW1123\MDW_AUTO' size 100m extent management local autoallocate; select tablespace_name,initial_extent,next_extent ,extent_management,min_extlen,allocation_type from dba_tablespaces where tablespace_name like 'MDW%'; TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN MIN_EXTLEN ALLOCATIO ------------------ -------------- ----------- ---------- ---------- --------- MDW_UNI_1M 1048576 1048576 LOCAL 1048576 UNIFORM MDW_AUTO 65536 LOCAL 65536 SYSTEM

As you can see, tablespace MDW_UNI_1M uses uniform extents of 1M and MDW_AUTO has system managed allocation and starts with 64K extents – 8 blocks with my 8k block size.

As a quick demo, I’ll just create and populate two simple tables and see what extent sizes are created for them:

create table mdw_big tablespace mdw_auto as select rownum id,lpad('A',1000,'A') pad from dual connect by level <10000 create table mdw_big_uni tablespace mdw_uni_1m as select rownum id,lpad('A',1000,'A') pad from dual connect by level <10000 select owner,segment_name,blocks,count(*) from dba_extents where segment_name like 'MDW%' group by owner,segment_name,blocks OWNER SEGMENT_NAME BLOCKS COUNT(*) --------------- --------------- ---------- ---------- ERIC MDW_BIG_UNI 128 12 ERIC MDW_BIG 8 16 ERIC MDW_BIG 128 11 3 rows selected.

So, how do issues with large numbers of extents still arise with modern oracle systems? Well, the two situations I’ve seen on Oracle 10 had the same cause {and, as a teaser for later this week, I’ve seen a variation of this issue on a nice, shiny Exadata X2-2 Oracle 11.2.0.3 box You can readabout that here }. What alerted me was slow performance querying the data dictionary, in particular my scripts for checking free space, the size of segments and how many extents they consisted of.

If you create a tablespace with fixed extent sizes of eg 64K and then create a table in there that is 100GB in size, you will get an awful lot of extents. Now make it worse and have a partitioned table that ends up being several hundreds of GB in size with all those segments in that tablespace (or similarly defined tablespaces).

Since I hit the problem twice myself, I’ve chatted to others who have had the same issue. The above usually happens because of a mistake. The tablespace(s) in question are set up to hold small tables and then get used for large tables, either by a mistake in stating the exact tablespace to use or having the tablespace default to your default tablespace – which just happens to be a tablespace with fixed but small extent sizes.

The end result is a massive number of small extents in these tablespaces, usually with extents for different objects mixed in. Some dictionary queries slow down and, in particular, anything to do with looking at extents. For one site, I was trying to use my own code to gather statistics on tables that replaced the standard automated job. It’s fairly “smart” code and chooses a sample size based on the size of the segments. Only, the data dictionary was performing so slowly for the check on segment size that it was taking over 5 seconds to get the information – longer than some of the stats gathers.

You can logically understand why dropping or truncating the table is slow. Oracle has to sort out all those extents, remove the information from the data dictionary. This is not helped by the fact that part of the data dictionary is being slowed down due to all those pesky records…

You MAY be able to get some relief from this situation by gathering fixed object statistics. I did so at one site, where the queries against free_space and segment size sped up by around 80%. I have no demonstrated proof of this, it is just what I saw in one situation, so feel free to try it but don’t sue me if it does not help. Also, it took over 3 hours to gather the fixed object stats and you only do this sort of thing, untested, on a production system if you are already in a bad place.

{update – I just tested this on a private 11.2 db that was taking 10.3 seconds to count all extents, all 12,742 of them. Gathering fixed object stats made no difference at all.}

However, the real answer is to laboriously rebuild those segments in tablespaces with correctly specified uniform extent sizes. Which we did, over several weeks, and it made a difference.

If I was doing this task today, if I could get the outage to do it, I would create COPIES of those segments that were in the wrong tablespaces, re-name and re-apply any constraints and move the other other, smaller tables and indexes to a new tablespace – and then drop the tablespaces including contents. Why? As dropping a table with lots and lots of small extents seemed to take a very long time (many minutes per partition and we had thousands of them). Again, my proof is lost in the mists of time, but that’s what I would aim to do.

Who’s up for a beer or whiskey in Edinburgh on the evening of 28th Feb?

I’ve been promising myself I’d do the Scottish SIG for three or four years but life has always conspired to stop me. However, at last I am going to manage it this year.

The meeting is on the 29th February at the Oracle {was Sun} office in Linlithgow. You can see the schedule and details here. As ever, it is being chaired by Thomas Presslie, though I {and I suspect Mr Hasler} will be hoping he is not forcing drams of Whiskey on people before 10am in the morning, as he did at the last UKOUG conference…

I’m presenting on Index Organised Tables again, following up on the series of posts I did {and have still to finish}. As well as myself there is also Tony Hasler talking about stabilising statistics {one of the key things to stable and thus acceptable performance from a very knowledgeable man}, a presentation by Wayne Lewis on Unbreakable Enterprise Kernel 2 {which I understand is Oracle Linux with the extra bits Larry wants in there before they have gone through to the official Open Source release} and Harry Hall talking about all the new stuff on OEM 12C. If he says Cloud too often I might lob something heavy at him {nothing personal Harry, I’m just tired of the C word in connection with Oracle already}. Additionally, Gordon Wilkie will also be giving an Oracle Update.

Part of the reason I want to do the Scottish SIG is that I really like Edinburgh {and Scotland in general – wonderful geography}. My original intention was to take my wife up there and make the trip into a short break – but she has to go to the US that week and I have a new client that needs my time, so it will be a dash up there the evening before and back in the afternoon.

So, is anyone around in Edinburgh from late evening on the 28th of Feb and fancies showing me one or two nice pubs?

For those of us acquainted with the philosophical works of Douglas Adams we know that the the answer to everything is 42.

mdw1123> select all knowledge from everything 2 / KNOWLEDGE ---------- 42

This above is a real SQL statement (version 11.2.0.3, just in case you wanted to know ).

This was prompted by a silly discussion at lunch time about the answer to everything and databases and I wondered aloud how you could go about getting Oracle to respond with 42 when you “selected all from everything”. My colleagues looked at me like I was an idiot and said “create a table called everything with a column called all and select it”. Yeah, of course, and I laughed. So much for being an expert at Oracle huh?

Well, I tried. It did not work:

mdw1123> create table EVERYTHING (ALL number not null) 2 / create table EVERYTHING (ALL number not null) * ERROR at line 1: ORA-00904: : invalid identifier

Damn. It’s a reserved word. But for what? Off the top of my head I could not remember what ALL is used for in Oracle select syntax. Never mind, I could get around the issue to some degree by the use of quotes around the column name (and just for fun, I made the column name lowercase too – this is how you can create lowercase columns but you have to be careful with this, as you will see below):

mdw1123> create table everything ("all" number not null) 2 / mdw1123> desc everything Name Null? Type ----------------------------------------------------------------- -------- -------- all NOT NULL NUMBER mdw1123> insert into everything values (42) 2 / mdw1123> select "all" from everything 2 / all ---------- 42 -- but be careful of case mdw1123> select "ALL" from everything 2 / select "ALL" from everything * ERROR at line 1: ORA-00904: "ALL": invalid identifier

I was not happy with this though, I was having to put the quotes in my line and be careful about the syntax.

So, what is the word ALL used for? A quick check of the SQL reference manual:

It is the opposite of DISTINCT and the default, so we never have to put it in the statement.

With the above in mind I was able to quickly come up with something close, but not quite, what I originally asked for. See below for how.

mdw1123> create table everything (KNOWLEDGE NUMBER NOT NULL) 2 / mdw1123> insert into everything values (42) 2 / mdw1123> select all knowledge from everything 2 / KNOWLEDGE ---------- 42 mdw1123>

Having said it was not quite what I had originally set out to do, I actually prefer this version.

Of course, I cleaned up after myself. It feels odd typing in commands that have an English meaning that would not be what you want to do – I could not get over the nagging feeling that the below was going to cause a lot of data to disappear :

mdw1123> drop table everything purge;

If you alter table TEST drop partition Q1, does it go in the recycle bin?

That is the question I was asked today. “Of course it….” Pause. More pause.

No, I did not know. I knew I’d seen partitions in the recyclebin on Oracle but I thought there was no option to state PURGE when you dropped a partition.

So, a quick test was needed.

First I tried a drop of a partition I knew I could live without {NB this is on version 11.2.0.3, I am 99.8% sure this is the same on 10}

mdw1123> alter table person_call drop partition d_20111205 purge 2 / alter table person_call drop partition d_20111205 purge * ERROR at line 1: ORA-14048: a partition maintenance operation may not be combined with other operations mdw1123> alter table person_call drop partition d_20111205 2 / Table altered. mdw1123> select count(*) from dba_recyclebin 2 / Any Key> COUNT(*) ---------- 0 1 row selected.

That’s pretty conclusive then, Oracle does not like the PURGE option when dropping a partitions, which suggests it does not go into the recyclebin, and in fact my recyclebin remains empty when I correctly drop the partition. {NB I had purged the recyclebin before this test}.

However, I want to make sure that it is indeed just the partition operation that does not go to the recyclebin (you can of course turn off the recyclebin and I have not proven that I have not done this). So:

mdw1123> create table mdw (id number,vc1 varchar2(10)) 2 partition by range (id) 3 (partition p1 values less than (10) 4 ,partition p2 values less than (20) 5 ,partition p3 values less than (30) 6 ,partition pm values less than (maxvalue) 7 ) 8 mdw1123> / Table created. mdw1123> insert into mdw 2 select rownum,'AAAAAAAA' 3 from dual 4 connect by level <40 5 / 39 rows created. mdw1123> select * from dba_recyclebin 2 / no rows selected mdw1123> alter table mdw drop partition p3 2 / Table altered. mdw1123> select * from dba_recyclebin 2 / no rows selected mdw1123> drop table mdw 2 / Table dropped. mdw1123> select * from dba_recyclebin; Any Key> OWNER OBJECT_NAME ORIGINAL_NAME ------------------------------ ------------------------------ -------------------------------- OPERATION TYPE TS_NAME CREATETIME --------- ------------------------- ------------------------------ ------------------- DROPTIME DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT ------------------- ---------- -------------------------------- --- --- ---------- ----------- PURGE_OBJECT SPACE ------------ ---------- MDW BIN$DI/3ZwiMRneOxLrJ4jidyg==$0 MDW DROP Table Partition INDEX_01 2012-01-24:16:13:55 2012-01-24:16:15:33 2787392 NO NO 77672 77672 77672 1024 MDW BIN$DI/3ZwiMRneOxLrJ4jidyg==$0 MDW DROP Table Partition INDEX_01 2012-01-24:16:13:55 2012-01-24:16:15:33 2787392 NO NO 77672 77672 77672 1024 MDW BIN$DI/3ZwiMRneOxLrJ4jidyg==$0 MDW DROP Table Partition INDEX_01 2012-01-24:16:13:55 2012-01-24:16:15:33 2787392 NO NO 77672 77672 77672 1024 MDW BIN$DI/3ZwiMRneOxLrJ4jidyg==$0 MDW DROP TABLE 2012-01-24:16:13:55 2012-01-24:16:15:33 2787393 YES YES 77672 77672 77672 4 rows selected.

So, if you go and drop the wrong partition, you can’t go getting it back without some sort of restore or recovery (as there is nothing to get it back from) but you can if you drop the whole table. And I was not going mad, I had seen partitions in the Recyclebin (there is, after all, a PARTITION_NAME column in the table)

This seems a little odd to me, I would imagine that dropping the wrong partition is something you would want to use flashback query to fix (as you can with accidentally dropping a table) but there you go. You can’t.

Back to the day job….

I was involved in a discussion recently with Debra Lilley which version of Oracle to use. You can see her blog about it here (and she would love any further feedback from others). Oracle now has a policy that it will release the quarterly PSUs for a given point release for 12 months once that point release is superseded. ie once 11.2.0.3 came out, Oracle will only guarantee to provide PSUs for 11.2.0.2 for 12 months. See “My Oracle Support” note ID 742060.1. However, an older Terminal release such as 11.1.0.7 is not superseded and is supported until 2015 – and will get the quarterly PSU updates. This left the customer with an issue. Should they start doing their development on the latest and theoretically greatest version of Oracle and be forced to do a point upgrade “soon” to keep getting the PSUs, or use an older version of Oracle and avoid the need to upgrade?

This is in many ways a special case of the perennial issue of should you use the latest version of Oracle (or in fact any complex software solution) or go with the version you know and trust? Plus, should you patch up to the latest version which in theory gives you protection against bugs and vulnerabilities (along with the CPUs). Yes, they are two separate issues but people tend to sit on the same side of both points, for the same reasons.

The arguments to stay using an older version are that it is working, it is stable, you do not need the new features and upgrading is a lot of work and effort. Plus the new version will have new bugs that come along with the new features you do not need and things might be turned on by default that you could do without (like stats collecting or not creating the actual segments when a new table or partition is created). If you remain on your favourite version long enough, you get another issue which is that the latest version of Oracle might not be compatible with your ancient version of the OS or another package or programming language critical to your system (I got caught in a terrible web with old perl, old O/S and old DB that resulted in a need to upgrade all three together – ouch!).

The arguments to moving forward are that you get access to the latest features, that over all older features will have more bugs fixed in newer version, performance will be better {again, overall, exceptions allowing}. Also, if you do hit bugs and problems there are no issues in having to first upgrade to a fully supported version. Plus, fixes are made for current versions first and then back-ported to older ones. Those pack-ported fixes can cause real problems when you DO decide to upgrade.

The big sticking points are the effort involved in upgrading and living with the bugs that you find that Oracle Testing didn’t.

I’ve got a few of other considerations to throw into the pot.

Firstly, if you are developing something new, it is not a lot more effort to use the latest version. This allows you to learn the new version and eases the transition of older systems to it.

Secondly, Oracle like you if you use the latest version, especially if it is the latest-latest version or even beta. Yeah, the helpdesk will not have a clue about some of your issues but in my experience you get access to those really smart guys and gals in Oracle who do the third-line support or even the development work.

Thirdly, if you are on the latest version, if you do decide to freeze on that version for a while, for stability and a quiet life, you have a lot longer before your version (at least at a major level) drops out of support.

Fourthly, dynamic, inquisitive, flexible staff like new things. In my experience, environments that freeze on an old version have a higher percentage of staff who either like it dull and repetitive, or hate it being dull and repetitive – and itch to get out. If I’m in charge, I know which type of staff I like to have more of {NB there are some very good arguments for having some staff who like it dull and repetitive}.

As you can guess, I am in the “be on the latest version” side of the argument. I was ambivalent about it until a few years ago when I noticed a trend:

Sites that like to move forward tend to (a) do it in a controlled manner and (b) have the infrastructure to do proper regression testing.
Site that like to stay still lack the ability to do regression testing and move forward only when forced – and in a pressured, unplanned and frankly chaotic manner.

That was it, that was the real key thing for me. The further you lag behind the more likely you are to eventually be forced to upgrade and it won’t be a nice time doing it. I know, there are exceptions, systems still running Oracle 6 absolutely fine on an old DOS6.1 box. In the same way you also get the odd 95-year-old life-long smokers – and thousands of 45-year-old smokers with emphysema.

When I have any sway over the situation I now always strive to be on modern versions of Oracle {OS, language, whatever} and to patch small and regular. To support all this, have very good regression testing. I’ve only a couple of times been able to get the regression testing sorted out as well as I would like, but when you do the pain of patching and upgrading, as well as developing and integrating, is so much reduced that not patching seems madness.

So to sum up:

• If it is a new development, go for the very latest version, play with the latest features if potentially beneficial and see if you can get Oracle to be interested in your attempts. ie (B)lead.
• If you have good regression testing, plan and carry out patch and version upgrades as they come available and stay current. ie Lead
• If you have a complex solution in place and no/poor regression testing, do not move to a new major release, leave it a while for the worst new bugs to be found and fixed. Then move. ie Lag

If your system is old AND critical and all the guys and gals who implemented it are long gone, stay on that version for ever. ie stagnate.

Oh, and if that last one applies to many of your systems – dust off the CV and start reading technical manuals. One day you will need a new job in a hurry.

I recently bought myself a Kindle – the keyboard 3G version. Keyboard as I know I will want to add notes to things and the 3G version for no better reason than some vague idea of being able to download things when I am away from my WiFi.

So, how about getting Oracle documentation onto it? You can get the oracle manuals as PDF versions (as opposed to HTML) so I knew it was possible and that others have done so before. A quick web search will show a few people have done this already – one of the best posts is by Robin Moffat.

Anyway, this is my take on it.

1) Don’t download the PDF versions of the manuals and then just copy them onto your kindle. It will work, but is not ideal. PDF files are shown as a full page image in portrait mode and parts are unreadable. Swap to landscape mode and most text becomes legible and you can zoom in. In both modes there is no table of contents and none of the links work between sections. All you can do is step back and forth page by page and skip directly to pages, ie goto page 127. This is not so bad actually as quite often the manual states the page to go to for a particular figure or topic.

2) Do download the MOBI format of the manuals you want, if available. Oracle started producing it’s manuals in Mobi and Epub format last year. I understand that Apple’s .AZW format is based on .MOBI (Mobipocket) format. As such text re-flows to fit the screen of the Kindle. I’ve checked a few of the DBA_type manuals for V10 and V11 and Mobi files seem generally available, but not a couple I checked for 10.1. If there is no Mobi, you can still revert to downloading the PDF version.

3) You cannot download a set of manuals in this format and you won’t see an option to download an actual manual in MOBI format until you go into the HTML version of the document.

I can understand that it would be a task for someone in Oracle to go and create a new downloadable ZIP of all books in these formats or, better still, sets to cover a business function (like all DBA-type books and all developer-type books), but it would be convenient.
Anyhow, go to OTN’s documentation section, pick the version you want and navigate to the online version of the manual.

Here I go to the 11.2 version – note, I’m clicking on the online set of manuals, not the download option.

Select the HTML version of the document you want, in this case I am grabbing a copy of the performance tuning guide. As you can see, this is also where you can choose the PDF version of the manual

Once the first page comes up, you will see the options for PDF, Mobi and Epub versions at the top right of the screen (see below). I wonder how many people have not realised the manuals are now available in new ebook formats, with the option only there once you are in the manual itself?

I’ve already clicked the Mobi link and you can see at the bottom left of the screen-shot, it has already downloaded {I’m using Chrome, BTW}. Over my 4Mb slightly dodgy broadband connection it took a few seconds only.

4) I don’t like the fact that the files are called things like E25789-01.mobi. I rename them as I move them from my download directory to a dedicated directory. You then attach up your kindle to your computer and drag the files over to the kindle’s “documents” folder and, next time you go to the main menu on the kindle, they will appear with the correct title (irrespective of you renaming them or not)

5) If you download the PDFs I would strongly suggest you rename these files before you move them to the kindle as they will come up with that name. I have a booked called e26088 on my kindle now – which manual is that? {don’t tell me, I know}. I have not tried renaming the file on the kindle itself yet.

6) You don’t have to use a PC as an intermediate staging area, you can directly download the manuals to your kindle, if you have a WiFi connection. Go check out chapter 6 of the kindle user guide 4th edition for details, but you can surf the web on your kindle. Press HOME, then MENU and go down to EXPERIMENTAL. click on “Launch Browser” (if you don’t have wireless turned on, you should get prompted). I’d recommend you flick the kindle into landscape mode for this next bit and don’t expect lightning fast response. If it does not take you to the BOOKMARKS page, use the menu button to get there and I’d suggest you do a google search for OTN to get to the site. Once there navigate as described before. When you click on the .Mobi file it should be downloaded to your kindle in a few seconds. Don’t leave the page until it has downloaded as otherwise the download will fail.

There you go, you can build up whatever set of oracle manuals you like on your ebook or kindle and never be parted from them. Even on holiday…

I’ve obviously only just got going with my Kindle. I have to say, reading manuals on it is not my ideal way of reading such material. {story books I am fine with}. I find panning around tables and diagrams is a bit clunky and the Kindle is not recognising the existence of chapters in the Oracle Mobi manuals, or pages for that matter. However, the table of contents works, as do links, so it is reasonably easy to move around the manual. Up until now I’ve carried around a set of Oracle manuals as an unzipped copy of the html download save to a micro-USB stick but some sites do not allow foreign USB drives to be used. I think I prefer reading manuals on my netbook to the kindle, but the kindle is very light and convenient. If I ever get one of those modern smart-phone doo-dahs, I can see me dropping the netbook in favour of the smartphone and this kindle.

Of course, nothing beats a big desk and a load of manuals and reference books scattered across it, open at relevant places, plus maybe some more stuff on an LCD screen.