Top 60 Oracle Blogs

Recent comments

December 2010

Solaris AWK still broken after all these years

It’s amazing how time flies. I’ve got the above book sitting on my desk at work. It’s the only book on my desk at work. Its probably my favorite computer book for it’s terse clear style and simple power. I’ve keep it for about 20 years, and 20 years later, I’d expect any bugs in basic code examples to be worked out. For the most part on most platforms this is true, but not Solaris.
AWK is an awesome utility always available on UNIX and makes a perfect tool to for scripts that will run anywhere with no need for dependencies nor if’s, and’s or but’s. For that reason it’s all the more dissapointing that the default awk on Solaris is broken for syntaxes that were valid over 20 years ago. On the default awk on Solaris the following are broken
  1. awk -vVAR=value
  2. if ( $0 ~ “string” )
  3. gsub , sub

Probably the most useful thing to know is for point 1, passing in a shell variable, you can do it by setting a variable in the shell such as “VAR=10″ and accessing it in the awk script with:
Point 2, the if construct can be gotten around a bit less elegantly  with

 /string/   { ...

Point 3, gsub and sub are broken is just plain annoying and it takes writing your own routines with index and substr.
Which was too bad for me. I wanted to write a script to eliminate more than 2 redo logs from database creation for “scratch” databases. I don’t know if you can imagine a create script like:
STARTUP NOMOUNT pfile='/mnt/provision/redo2a/datafile/initredo2a.ora.provision'

) SIZE 52428800,

) SIZE 52428800,
) SIZE 52428800

I just wanted 2 redo logs and one member for group, so I wanted to pull out all that’s highlighted in orange. Simple with AWK:
cat ${MNTDIR}/ | \awk -vNREDO="$NREDO" 'BEGIN { output="on" members="off" nlogs=0 last="no"}{# membersif ( $0 ~ "GROUP"     ) { members="on" ; nlogs=0}# outputif ( $0 ~ NREDO       ) { last="yes" }if ( $0 ~ "DATAFILE"  ) { output="on"  }if ( $0 ~ "SIZE"      ) { members="off" ; nlogs=0}if ( output == "on"   ) {   nlogs++       gsub(/,/,"") } if ( nlogs < last ="=" last = "no" output="off"> /tmp/redo2.$$
The script works on AIX, HPUX, Redhat but not on Solaris. For Solaris I had a few choices. I could try and use nawk or gawk which would require me using some sort of “ifdef” and even then using nawk would require some changes. AFAIK gawk would have to be installed on the machines, thus ruling it out. For me, I wanted the safest option. The safest option for me was just to re-write the code in a way that worked around the bugs on Solaris default awk.
cat ${MNTDIR}/ | \awk  'BEGIN {output="on"members="off"nlogs=0last="no"size="no"NREDO="'"$NREDO"'"}/DATAFILE/               { output="on"  } # turn output back on/SIZE/                   { members="off" ; nlogs=0; size="yes"}  # end of group members/GROUP/                  { members="on"  ; nlogs=-1 }  # start counting group members{ if ( index($0,NREDO) )   last="yes"    } # reach the maximum  of redo logs to recreate{if ( output == "on"   ) {str=$0if ( members == "on"  ) { # we are in the log member section   nlogs++             # count members   i=index(str,",");   # get rid of comma at end of line   if ( i > 0 ) {       str=substr(str,0,i-1)   }}if ( nlogs < size ="=" last ="=" str="$0" i="index(str,"> 0 ) {                str=substr(str,0,i-1)            }            last = "no"            output="off"   # we reach max # of redo logs, turn output off till we see DATAFILE         }      }      print str   }}}{ size = "no" }'  > /tmp/redo2.$$

Index Join – 3

I’ve recently been writing about the index join mechanism and ways of emulating it. Those notes were originally inspired by an example of an index join that appeared on OTN a little while ago.

It was a plan that combined “bitmap/btree conversion” with the basic index join strategy so, with hindsight, it was an “obvious” and brilliant execution plan for a certain type of query. The query in the original posting was a simple select (with no predicates) against a huge table in a data warehouse – presumably extracting a small number of columns from a much wider row.


(I really hate reading SQL where the whole table name has been repeated as the alias all the way through the SQL – it makes the code so hard to read, especially when it’s all in upper case. It’s important to use aliases, of course, but 3 or 4 letters is a sensible length.)

Here’s the execution plan:

| Id  | Operation                          | Name                         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT                   |                              |  1799K|    42M|       | 51279   (1)| 00:10:16 |
|   1 |  HASH UNIQUE                       |                              |  1799K|    42M|   151M| 51279   (1)| 00:10:16 |
|   2 |   VIEW                             | index$_join$_001             |  1799K|    42M|       | 38227   (1)| 00:07:39 |
|*  3 |    HASH JOIN                       |                              |       |       |       |            |          |
|*  4 |     HASH JOIN                      |                              |       |       |       |            |          |
|*  5 |      HASH JOIN                     |                              |       |       |       |            |          |
|*  6 |       HASH JOIN                    |                              |       |       |       |            |          |
|*  7 |        HASH JOIN                   |                              |       |       |       |            |          |
|   8 |         BITMAP CONVERSION TO ROWIDS|                              |  1799K|    42M|       |   485   (1)| 00:00:06 |
|   9 |          BITMAP INDEX FULL SCAN    | ECP_ITEM_MASTER_DIM_IMPL_BMX |       |       |       |            |          |
|  10 |         BITMAP CONVERSION TO ROWIDS|                              |  1799K|    42M|       |   230   (0)| 00:00:03 |
|  11 |          BITMAP INDEX FULL SCAN    | ECP_ITEM_MASTER_DIM_IPF_BMX  |       |       |       |            |          |
|  12 |        BITMAP CONVERSION TO ROWIDS |                              |  1799K|    42M|       |   229   (0)| 00:00:03 |
|  13 |         BITMAP INDEX FULL SCAN     | ECP_ITEM_MASTER_DIM_IS3_BMX  |       |       |       |            |          |
|  14 |       BITMAP CONVERSION TO ROWIDS  |                              |  1799K|    42M|       |   228   (0)| 00:00:03 |
|  15 |        BITMAP INDEX FULL SCAN      | ECP_ITEM_MASTER_DIM_IS4_BMX  |       |       |       |            |          |
|  16 |      BITMAP CONVERSION TO ROWIDS   |                              |  1799K|    42M|       |   201   (0)| 00:00:03 |
|  17 |       BITMAP INDEX FULL SCAN       | ECP_ITEM_MASTER_DIM_IS5_BMX  |       |       |       |            |          |
|  18 |     BITMAP CONVERSION TO ROWIDS    |                              |  1799K|    42M|       |   207   (0)| 00:00:03 |
|  19 |      BITMAP INDEX FULL SCAN        | ECP_ITEM_MASTER_DIM_OI_BMX   |       |       |       |            |          |

Isn’t it brilliant! The optimizer has seen that all the required columns can be found in indexes (six of them) – but they happen to be bitmap indexes so the optimizer has done a “bitmap conversion to rowid” on all six indexes one after the other with five consecutive hash joins – carrying the column values with each conversion and hash join.

Unfortunately the owner of this plan wasn’t happy with the resulting plan because a full tablescan turned out to be faster – nevertheless, it’s a very clever concept as the size of the table was measured in Gigabytes while the indexes were only a few megabytes each, allowing for a significant saving in I/O time.

I was a little curious, though, about the final join strategy. It’s annoying that Oracle didn’t report any costs on the hash join lines themselves because that could be very revealing. It’s remarkable that the value in the Bytes column for the final view (which is six columns of data) is the same as the bytes column for each index conversion (and remember that the projection from each conversion is just one data column with an accompanying rowid) – there’s clearly something wrong with the arithmetic.

This may explain why the optimizer has decided to run the 6-way join using only two running hash joins (rather than first setting up five hash tables in memory than passing the last table through them). If you think about this, when Oracle gets to the last hash join (lines 3, 4 and 18) it has to build a hash table from the result of the previous four joins and (in this case) that’s going to need a similar amount of memory as five in-memory hash tables. With that thought in mind I was puzzled that Oracle hadn’t just built five in-memory hash tables then walked through each in turn with the sixth.

Still – it’s not my (or my client’s) problem; maybe one day I’ll need to look more closely at a similar case.

[Further reading on Index Joins]

Waiting for a Long Time – What is Going On?

December 7, 2010 I recently reviewed the book “Oracle Tuning the Definitive Reference Second Edition”, and did not provide an in-depth technical review of the entire book.  This blog article series will dig into some of the pages that were not specifically included in the review. Over the years I have seen several “Top 5 [...]

Optimizer Costing 4 – What is Wrong with this Quote?

December 7, 2010 (Back to the Previous Post in the Series) I recently reviewed the book “Oracle Tuning the Definitive Reference Second Edition”, and did not provide an in-depth technical review of the entire book.  This blog article series will dig into some of the pages that were not specifically included in the review.  What [...]

Server Bought for the 1 Grand Challenge

What seems like a couple of months ago I suggested the idea of The Fastest Oracle Server for a Grand. It turns out this was actually over 1/3 of a year ago! {such is the rapid passing of normal time}. Anyway, I’ve decided to give this a go.

The intention is that I am going to build a server based on PC technology which costs less than £1,000 and see how fast I can make it go. Of course “how fast” is a “piece of string” question – it depends on what you put into the Oracle database, how you want to use or manipulate the data and how business-ready the system is. I’m intending to build something that looks very, very un-business ready. That is, not a lot of redundancy. Before anyone wants to shoot me down for that (a) I am not running a bank or anything to do with finance (b) why are banks systems that only deal with cash so much more regulated and goverend than medical systems that are only relied on to keep you alive? (c) some of the biggest systems I know of are actually running on something close to PC kit.

I’m quietly confident I can build something that out-performs systems consisting 100 times as much. Now, that is a massive claim and I won’t be too sad if I fall short, but you can do a lot with modest kit. I worked for a charity for 6 years and boy did I see some clever stuff done on the sort of budget many organisation spend on office stationary.

So, what have I got so far? I confess I held off until I saw some new technology appear in a price band I could squeeze in. Namely USB3 and SATA3. There is always something just around the corner but I wanted those as I want to maximise the impact of solid state storage. So, my base server is:

  • Asus P7P55D-E motherboard supporting DDR3, USB3 and SATA3
  • Intel i5 760 2.8HHz chip
  • 8GB memory
  • 1TB samsung 7200rpm SATAII disk
  • AZCool Infinity 800W PSU
  • Coolmaster Elite RC-335 case

I chose the motherboard as it was getting good reviews and had the SATA3 and USB3 ports. I chose the case as it was large enough to take many hard drives, small enough to lug about and was a nice case. I stuck to 8GB RAM as RAM is expensive at the moment, but as it is in 2GB chunks I might regret that choice as all my slots are full. Many people forget the PSU but it’s like the tyers on your car. Those tyers keep you stuck to the road, a PSU keeps you powered. It might be utilitarian but they are vital and often overlooked. The hard disc is pretty good, but very likely to be swapped out (I don’t mind sticking it in another system). The CPU is a proper quad core CPU. I had plenty of scope to go bigger and better on the CPU but for grunt for cash, it seems presently to be the sweet spot.

The basic unit is not overclocked. I will increase the cooling and overclocking will be an option. It comes with 64 bit windows but linux is almost certainly going to be the faster option. No monitor is included but hey, it’s a database server, you don’t need fancy graphics. That old CRT in the corner will do! The server does have a rather nice nVidia GeForce GTX 460 in it but I am cutting out the cost of that. The server is currently the best gaming machine I have but that will end when I get time to start working on the Oracle side.

Total cost, £615 or so. That is like $615 seeing as we get so ripped off in the UK for IT kit. I can now go spend money on more fast hard discs, SSDs, even fast USB memory sticks. Any suggestions, I am happy to listen.

The biggest question is – When am I going to get time to work on this damn thing?

Optimizer Costing 3 – What is Wrong with this Quote?

December 6, 2010 (Back to the Previous Post in the Series) (Forward to the Next Post in the Series) I recently reviewed the book “Oracle Tuning the Definitive Reference Second Edition”, and did not provide an in-depth technical review of the entire book.  This blog article series will dig into some of the pages that were not [...]

UKOUG Presentation

I had a recent email enquiry after the slides from my talk at the technically excellent UKOUG Conference since problems with the website are preventing downloads currently. Reproduced below by way of a heads up is my email explaining that the slides are now available via the web, including to non-UKOUG members. Please feel free [...]

Optimizer Costing 2 – What is Wrong with this Quote?

December 6, 2010 (Back to the Previous Post in the Series) (Forward to the Next Post in the Series) I recently reviewed the book “Oracle Tuning the Definitive Reference Second Edition”, and did not provide an in-depth technical review of the entire book.  This blog article series will dig into some of the pages that were not [...]

UKOUG 2010 - My Personal Account

I'm not sure whether I was very lucky this past week or very unlucky.

It took me 14 hours to get from Edinburgh to Birmingham. That's almost a Hotsos Symposium trip!

My skin condition flared up which meant I was in a thoroughly miserable mood and had to miss quite a few presentations as I hunted down medical supplies and took care of it.

In a decision which seemed ridiculous even at the time, but was understandable, I decided that alcohol, bath-water and emollient oil were a good combination and so slipped and badly sprained my wrist which led to the rather unedifying spectacle of Alex Gorbachev tieing my shoelaces for me the following morning. It was a good job I managed the rest of my morning dressing routine myself but it was a close run thing! It seems to be getting a little better now.

I had my elbow twisted into making an utter fool of myself in front of many hundreds of people in Hall 1 as part of the evening Pantomime!

I only attended the first two days which meant, along with my unexpectedly late arrival on Sunday evening, I missed out on catching up with tons of people I planned to.

In the end, I managed to actually get to Birmingham, then to London and then back to Edinburgh. Others weren't so lucky.

Of the few presentations I managed to attend, most were excellent. More on those in a seperate post.

Thanks to many very generous sponsors and then the UKOUG's organisation of collection buckets and depositing the funds into my Movember account, my final fund-raising total was £1,202. Many thanks to all those who contributed, including those I'm unable to thank personally.

As always, the time I did spend in the company of old friends was well-spent.

At least, unlike UKOUG Chairman Ronan Miles, I did not agree to dress up as a naughty nurse. Hats off to him for joining in the fun though. I would praise Lisa Dobson but, as she pointed out, she's a Geordie and usually dresses up like that on a Friday night anyway ;-)

On balance, as usual, it was probably just normal life, not particularly lucky or unlucky ;-)

Optimizer Costing 1 – What is Wrong with this Quote?

December 5, 2010 (Modified December 6, 2010) (Forward to the Next Post in the Series) I recently reviewed the book “Oracle Tuning the Definitive Reference Second Edition”, and did not provide an in-depth technical review of the entire book.  As I stated in a comment in the earlier blog article, I would like to clarify that [...]