jump to navigation

Regular Index vs. Partitions (The Best Of Both Worlds) August 28, 2008

Posted by Richard Foote in Oracle Cost Based Optimizer, Oracle Indexes, Partitioning.
17 comments

When asking for requests and suggestions on what I could cover in the blog, Brian Tkatch asked “When is a partition better than just a regular index ?”

As it’s a very good question, I thought I might spend a few moments attempting to provide some kind of meaningful answer.

The first point I would make however, is that indexes and partitions are not at all mutually exclusive. A table of course could very well benefit from being both partitioned and indexed. Most partitioned tables have indexes and indeed indexes can in turn be partitioned.

The second point I would make is that there are two key advantages or reasons why it might be beneficial to partition a segment. They are:

  1. Better Manageability
  2. Better Performance

This post can be viewed as being but an introduction to the potential performance benefits of partitioned segments. I will leave the topic of better manageability for another time.

The point of Brian’s question is when can the performance benefits available to partitioned segments out perform and be a better option than that of a possible associated index.

As I’ve discussed a number of times, there are no magic numbers or ratios as to when an index is the most appropriate and cost effective access path. If fact, the link shows a theoretical example of when a Full Table Scan (FTS) is by far the cheaper option when selecting just 1% of data. Generally speaking though, the higher the percentage of returned rows, the more likely a FTS is going to be the more efficient option.

However, a Full Table Scan is just that, a FULL Table Scan. Oracle needs to read the entire table, each and every block below the segment High Water Mark. So if a query needed to retrieve just 5% of the data, it’s quite likely it would perform a FTS because it’s possibly a cheaper and less costly alternative than using an associated index (the above link explains why) but it would need to access 100% of the table to do so. That means it would need to effectively access 95% of the data and simply just throw away the rows so it can access the 5% of data it actually requires in a more efficient manner than possible using an index access path.

That’s potentially (say) 95% of the work and resources for no real reason other than Oracle has no choice but to access all the data when performing a FTS. That’s potentially 95% of wasted resources, 95% of wasted effort. Yes, a TFS might be more efficient than using an index but accessing unnecessarily say 95% of data sounds rather costly nonetheless.

Wouldn’t it be nice if somehow we could get the benefits of performing a FTS (multiblock reads, reading a specific block just the once, etc.) but only read the say 5% of the data that we were actually interested in (or something significantly less than the 100%)? Somehow combine the benefits of a FTS with the benefits provided by an index, that being only needing to access data that is of direct interest.

Enter Partitioning.

Partitioning provides Oracle with another level of granularity (or levels of granularity with composite partitions) when accessing a table. It’s no longer an question of using an index or reading the entire table. Oracle can now either use an index, access the entire table or with partitioning access just those partitions (or bits) of a table that can only possibly contain data of interest.

By partitioning a table, Oracle knows that data belonging to the partitioning keys must belong in a specific partition. The logical table is effectively broken down into multiple physical segments with each partition segment containing only data associated with the partitioning keys or columns.

If the say 5% of data that’s of interest only logically resides in the one or whatever number of partitions, then there’s no need for Oracle to access the data in all the other partitions, containing the other say 95% of unwanted data. The Cost based Optimizer (CBO) can automatically take advantage of “partition pruning” and only needs to access the partition or those partitions that only can contain the data of interest.

Oracle can now potentially perform a so-called “FTS” of the table, but only actually accesses and reads the 5% (or significantly less than the 100%) of data that’s potentially of direct interest by accessing just those partitions it needs to.

At a simplistic level, indexes are best when accessing a relatively “small” amount of data. A FTS is best when accessing a relatively “large” amount of the data and partitions can be extremely useful and beneficial when accessing an amount of data somewhere between the two. As such, partitioning is more likely to be beneficial in a Data Warehouse or Reporting environment where accessing a relatively high percentage of data is common.

This demo of indexes vs. a FTS vs. partition pruning shows how a FTS outperforms an index when accessing approximately 1/8 of data in a table but an appropriately partitioned table out performs both options.

More on partitioning in the future.

100,000 Hits, Olympic Games and OOW (Golden Years) August 26, 2008

Posted by Richard Foote in Olympics, OOW, Oracle Blog.
8 comments

Time for a bit of a catchup on things.

It doesn’t seem that long ago when I was celebrating 10,000 hits. Well over the last day or so, I’ve hit the next significant milestone, 100,000 hits. Considering the somewhat limited appeal of my humble writings (Oracle Indexes doesn’t generally generate the same interest as say Madonna’s new world tour or the world’s best apple pie recipe), it’s a nice little number to reach nonetheless. So a big thank-you to everyone who has contributed over the past 8 months or so and I hope some of those 100,000 hits were worth the effort.

Over the past two weeks, I’ve been stuck in front of the TV watching the spectacle that is the Beijing 2008 Olympic Games. I’m an Olympic Games tragic and just love watching the best athletes in the world compete and amaze us with their talents while of course watching Australia win medals. One of the happiest and most exciting periods of life was during the whole Sydney 2000 Olympics experience and for example being there poolside as Ian Thorpe won the 400M freestyle gold medal. Magical times indeed.

For the BeiJing 2008 Olympics, my highlights and lowlights were:

Australian Highlight: Toss up between Sally McLennan’s celebration when she won silver in the 100M hurdles and Matthew Mitcham’s amazing last dive to take gold in the 10M platform. To finish 6th overall was another impressive performance for a country of only 20 odd million although beating the poms in 2012 must surely be a national priority :)

Overall Highlight: Michael Phelps winning 8 gold medals was an incredible achievement that will likely never been repeated but for me the highlight was the performance of Usain Bolt who managed to crush the best in the world and beat world records while playing with the crowd. He would be booked for speeding if he ran that fast in a school zone !!

Biggest Disappointment: The TV coverage dished out by Channel 7 in Australia, it was simply appalling. Featuring more ads than actual coverage, what coverage we had was often delayed and featured repeat after repeat after repeat of the same things. Many sports were simply not shown, including many blue ribbon events like many of the athletic field events. SBS, a second TV station with secondary rights managed to salvage some respectability with their coverage but overall, it was a huge disappointment. Thankfully, Channel 7 no longer have the TV rights for the Olympics, hopefully lessons have been learnt and things will improve in time for London 2012.

I’ve had a number of emails regarding where the hell I’m listed for this year’s Oracle OpenWorld schedule after my presentation was one of the winners in the Oracle Mix “competition”. Firstly a big thank-you to everyone who voted, my “Indexing Secrets” presentation ended up finishing 6th and was selected. Unfortunately, times are somewhat tight at work and I wasn’t able to get my travel costs approved and so will not be able to attend OOW this year. Sorry to all those who were hoping to see me action, maybe next year.

Finally, in the next day or two, I’ll finally get around to answering Brian Tkatch’s question on when partitioning is a better option than just an index. So stayed tuned !!

Indexes and NOT Equal (Not Now John) August 13, 2008

Posted by Richard Foote in Index Access Path, NOT Equal, Oracle Cost Based Optimizer, Oracle Indexes, Performance Tuning.
25 comments

The Cost Based Optimizer (CBO) is a rather complex piece of code that has to deal with countless different possible scenarios when trying to determine what the most optimal execution plan might be. It’s also a vitally important piece of code because not only do the decisions need to be reasonably accurate so that it doesn’t generate inefficient execution plans but it needs to make these decisions in a reasonably efficient manner else it wastes resources and most importantly wastes time while it performs its calculations.

So there’s a trade-off between ensuring the CBO makes reasonable decisions while ensuring it makes its decisions in a timely and resource efficient manner. Database performance could be directly impacted if these trade-offs are not managed effectively.

Therefore, there are all sorts of short cuts and assumptions that are coded into the CBO to make its life a little easier. However, these short cuts can sometimes be problematic if they’re not recognised and handled appropriately.

One of these little short cuts worth noting is how the CBO deals with NOT EQUAL (and NOT IN) conditions …

Typically when we have a condition where we just say NOT EQUAL, we’re basically suggesting we’re interested in the vast majority of possible values with the exception of the value specified in the NOT EQUAL condition. We want most values but not if it’s this particular value.

For example, a condition where we state something such as:

WHERE TEXT <> ‘BOWIE’

means we want all the other possible values of TEXT, just not those with the specific value of ‘BOWIE’. In other words, we’re typically interested in the vast majority of possible values when we specify a NOT EQUAL condition.

However, we all know that typically, Oracle will not use an index if generally a relatively “high” percentage of rows are to be selected. It would generally be more efficient and less costly to simply perform a Full Table Scan if most rows are going to be returned anyways.

Therefore the CBO simply ignores indexes when costing a NOT EQUAL condition. Why bother going to all the overhead of calculating the cost of using an index to retrieve the vast majority of rows when a Full Table Scan is going to be the cheaper alternative in the vast majority of such cases. So the CBO doesn’t even bother trying and ignores all indexes that could potentially be used to retrieve the rows based on the NOT EQUAL condition.

But what if the data isn’t evenly distributed and the NOT EQUAL condition actually retrieves only a relatively small proportion of the rows. What if most rows actually have the value specified in the NOT EQUAL condition and the remaining rows constitute a relatively small proportion of the remaining rows ?

When the CBO ignores indexes, it ignores indexes in all cases. Even if 99.99% of rows match the value in the NOT EQUAL condition and there’s only a handful of remaining rows to actually be retrieved, the code path in the CBO is still followed and indexes are ignored regardless. The reason possibly being such queries could be re-written to avoid the use of the NOT EQUAL condition and so its use is still suggesting a large selectivity.

The refusal of the CBO to consider an index with a NOT EQUAL condition can easily be illustrated.

First, let’s create a table and populate a TEXT column with the same value, ‘BOWIE’:

SQL> create table bowie as select rownum id, ‘BOWIE’ text from dual connect by level <= 1000000;

Table created.

Let’s make the TEXT column NOT NULL so the CBO knows all rows have a value for this column:

SQL> alter table bowie modify text not null;

Table altered.

Let’s now add a new row, one that has a different value for the TEXT column:

SQL> insert into bowie values (1000001, ‘ZIGGY’);

1 row created.

Commit complete.

So all rows have a TEXT value of ‘BOWIE’, except for just the one row which has a value of ‘ZIGGY’.

OK, let’s now create an index on this column:

SQL> create index bowie_i on bowie(text);

Index created.

Let’s now collect some statistics on this table, including a histogram on the TEXT column so that the CBO knows the data is not even distributed and that the vast number of values of TEXT are ‘BOWIE’:

SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> ‘BOWIE’, cascade=> true, estimate_percent=> null, method_opt=> ‘FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> ‘BOWIE’, cascade=> true, estimate_percent=> null, method_opt=> ‘FOR COLUMNS TEXT SIZE 5′);

PL/SQL procedure successfully completed.

So only one row has a value that is NOT a ‘BOWIE’ which means an index to retrieve this one and only row would be an efficient and appropriate execution path, right ?

Well, let’s see what the CBO decides to do. First, let’s set a 10053 trace so we can see how the CBO has costed it’s possible options.

SQL> alter session set events ’10053 trace name context forever’;

Session altered.

Let’s now execute this simple and innocent looking statement:

SQL> select * from bowie where text <> ‘BOWIE’;

        ID TEXT
---------- -----
   1000001 ZIGGY

---------------------------------
| Id| Operation         | Name  | 
---------------------------------
|  0| SELECT STATEMENT  |       |
|* 1|  TABLE ACCESS FULL| BOWIE | 
---------------------------------

We note that Oracle has decided to not use the index but use a FTS instead.  If we look at the relevant parts of the 10053 trace, we note that the CBO did not even cost or consider using the index. The index was basically ignored and not considered at all:

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: BOWIE  Alias: BOWIE
    #Rows: 1000001  #Blks:  2214  AvgRowLen:  10.00
Index Stats::
  Index: BOWIE_I  Col#: 2
    LVLS: 2  #LB: 2370  #DK: 2  LB/K: 1185.00  DB/K: 1105.00  CLUF: 2210.00
Access path analysis for BOWIE
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for BOWIE[BOWIE]
  Column (#2):
    NewDensity:0.000000, OldDensity:0.000000 BktCnt:1000001, PopBktCnt:1000000, PopValCnt:1, NDV:2
  Table: BOWIE  Alias: BOWIE
    Card: Original: 1000001.000000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
  Access Path: TableScan
    Cost:  620.67  Resp: 620.67  Degree: 0
      Cost_io: 601.00  Cost_cpu: 435767288
      Resp_io: 601.00  Resp_cpu: 435767288
  Best:: AccessPath: TableScan
         Cost: 620.67  Degree: 1  Resp: 620.67  Card: 1.00  Bytes: 0

You can try to hint the query but the CBO will still ignore any RANGE SCAN operation because the CBO can’t know what all other possible potential values that are not ‘BOWIE’ might be (remembering the statistics may not necessarily be accurate). It can perform a FULL INDEX SCAN but this means reading all the leaf nodes that contain all the unwanted ‘BOWIE’ values and so it still an inefficient option:

SQL> select /*+ index (bowie bowie_i) */ * from bowie where text <> ‘BOWIE’;

-----------------------------------
| Id| Operation                   |
-----------------------------------
|  0| SELECT STATEMENT            |
|  1|  TABLE ACCESS BY INDEX ROWID|
|* 2|   INDEX FULL SCAN           |
-----------------------------------

The INDEX RANGE SCAN is simply not an option …

What is an option of course is to simply rewrite the query. One can just write the query in the “positive” sense and the index is now considered and used:

SQL> select * from bowie where text = ‘ZIGGY’;

-----------------------------------
| Id| Operation                   |
-----------------------------------
|  0| SELECT STATEMENT            |
|  1|  TABLE ACCESS BY INDEX ROWID|
|* 2|   INDEX RANGE SCAN          |
-----------------------------------

Or, if there a many different distinct values that are not ‘BOWIE’ but which in total still constitute a relatively small percentage of the total rows, then it could be re-written as follows which can make use of the index in an effective manner by concatenating two separate index range scans:

SQL> select * from bowie where text < ‘BOWIE’ or text > ‘BOWIE’;

        ID TEXT
---------- -----
   1000001 ZIGGY
------------------------------------
| Id| Operation                    |
------------------------------------
|  0| SELECT STATEMENT             |
|  1|  CONCATENATION               |
|  2|   TABLE ACCESS BY INDEX ROWID|
|* 3|    INDEX RANGE SCAN          |
|  4|   TABLE ACCESS BY INDEX ROWID|
|* 5|    INDEX RANGE SCAN          |
------------------------------------

Note this same issue applies to NOT IN conditions.

Be very careful when using NOT EQUAL conditions and be mindful of the impact they may have with your indexes.

Non-Unique Indexes and Direct-Path Inserts (What In The World) August 6, 2008

Posted by Richard Foote in Direct-Path Inserts, Non-Unique Indexes, Oracle Indexes, Performance Tuning.
6 comments

The OTN Database Forum has had some really good threads lately and something that came up was the question of indexes and Direct-Inserts which I thought might be worth a mention here.

I’ve previously discussed the differences between Unique and Non-Unique Indexes and my general preference for using Unique Indexes where possible and appropriate:

http://richardfoote.wordpress.com/2007/12/18/differences-between-unique-and-non-unique-indexes-part-i/

http://richardfoote.wordpress.com/2007/12/21/differences-between-unique-and-non-unique-indexes-part-ii/

http://richardfoote.wordpress.com/2007/12/30/differences-between-unique-and-non-unique-indexes-part-iii/

A Direct-Path Insert is a special mechanism used by Oracle to more quickly and efficiently insert data into a table. Rather than utilising the conventional method of using the table freelists or ASSM bitmaps to find an available free block that’s subsequently loaded into the buffer cache and processed, Oracle instead builds the necessary blocks in session memory and writes them directly to disk, “appending” them above the table High Water Mark (HWM) and hence by-passing the buffer cache entirely.

When inserting a large amount of data, a Direct-Path Insert can hence be substantially faster and has the added flexibility of being a NOLOGGING operation if required.

However, if you have a Primary Key (or Unique Key) policed via a Non-Unique index, then Oracle will automatically disable Direct-Path inserts behind the scene. This restriction has only been lifted since 11g.

A simple demo.

First, using a 10.2.0.3 database, create a table and populate it with a few rows:

SQL> CREATE TABLE ZIGGY (id NUMBER, text VARCHAR2(20));

Table created.

SQL> INSERT INTO ziggy SELECT rownum , ‘Ziggy’ FROM dual CONNECT BY LEVEL <= 10000;

10000 rows created.

SQL> COMMIT;

Commit complete.

Next, let’s add a PK that’s policed by a Non-Unique Index:

SQL> ALTER TABLE ziggy ADD CONSTRAINT ziggy_pk PRIMARY KEY(id) USING INDEX(CREATE INDEX ziggy_pk ON ziggy(id));

Table altered.

Now, let’s see how many blocks below the HWM we have allocated to the table:

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’ZIGGY’, estimate_percent=>null, cascade=> true, method_opt=>’FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

SQL> SELECT blocks, empty_blocks FROM dba_tables WHERE table_name = ‘ZIGGY’;

BLOCKS EMPTY_BLOCKS
------ ------------
    25            0

Let’s see how many physical direct write operations we have currently performed:

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = ‘physical writes direct’;

NAME                   VALUE
---------------------- -----
physical writes direct    21

Let’s now attempt to perform a Direct-Path Insert operation:

SQL> INSERT /*+ APPEND */ INTO ziggy SELECT 10001, ‘NEW’ FROM dual;

1 row created.

SQL> COMMIT;

Commit complete.

But have we actually performed any physical writes direct operations ?

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = ‘physical writes direct’;

NAME                   VALUE
---------------------- -----
physical writes direct    21

NO !! The number of such operations has not changed. Has the HWM been incremented ?

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’ZIGGY’, estimate_percent=>null, cascade=> true, method_opt=>’FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

SQL> SELECT blocks, empty_blocks FROM dba_tables WHERE table_name = ‘ZIGGY’;

BLOCKS EMPTY_BLOCKS
------ ------------
    25            0 

NO !! The number of blocks has also remained the same. Clearly then, the Direct-Path Insert has not actually worked and Oracle has simply performed a conventional insert operation instead.

Let’s see if the situation changes if we replace the index policing the PK with a Unique Index instead …

SQL> ALTER TABLE ziggy DROP PRIMARY KEY;

Table altered.

SQL> DROP INDEX ZIGGY_PK;

Index dropped.

SQL> ALTER TABLE ziggy ADD CONSTRAINT ziggy_pk PRIMARY KEY(id);

Table altered.

OK, let’s see the current number of physical writes direct operations:

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = ‘physical writes direct’;

NAME                   VALUE
---------------------- -----
physical writes direct    41 

Let’s try another Direct-Path Insert …

SQL> INSERT /*+ APPEND */ INTO ziggy SELECT 10002, ‘NEW’ FROM dual;

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = ‘physical writes direct’;

NAME                   VALUE
---------------------- -----
physical writes direct    42 

And the number of physical writes direct has now increased. What about the HWM ?

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’ZIGGY’, estimate_percent=>null, cascade=> true, method_opt=>’FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

SQL> SELECT blocks, empty_blocks FROM dba_tables WHERE table_name = ‘ZIGGY’;

BLOCKS EMPTY_BLOCKS
------ ------------
    26            0 

Yep, that’s gone up by one as well. Let’s just repeat the process to be sure …

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = ‘physical writes direct’;

NAME                   VALUE
---------------------- -----
physical writes direct    42 

SQL> INSERT /*+ APPEND */ INTO ziggy SELECT 10003, ‘NEW’ FROM dual;

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = ‘physical writes direct’;

NAME                   VALUE
---------------------- -----
physical writes direct    43 

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’ZIGGY’, estimate_percent=>null, cascade=> true, method_opt=>’FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

SQL> SELECT blocks, empty_blocks FROM dba_tables WHERE table_name = ‘ZIGGY’;

BLOCKS EMPTY_BLOCKS
------ ------------
    27            0 

Yes indeed, Direct-Path Inserts are definitely now occurring now we have a Unique Index policing our PK constraint.

Same thing now with a Non-Unique Index policing our PK again but this time on an 11g database …

SQL> CREATE TABLE ZIGGY (id NUMBER, text VARCHAR2(20));

Table created.

SQL> INSERT INTO ziggy SELECT rownum , ‘Ziggy’ FROM dual CONNECT BY LEVEL <= 10000;

10000 rows created.

SQL> COMMIT;

Commit complete.

SQL> ALTER TABLE ziggy ADD CONSTRAINT ziggy_pk PRIMARY KEY(id) USING INDEX(CREATE INDEX ziggy_pk ON ziggy(id));

Table altered.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’ZIGGY’, estimate_percent=>null, cascade=> true, method_opt=>’FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

SQL> select blocks, empty_blocks from dba_tables where table_name = ‘ZIGGY’;

BLOCKS EMPTY_BLOCKS
------ ------------
    25            0 

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = ‘physical writes direct’;

NAME                   VALUE
---------------------- -----
physical writes direct    23 

SQL> INSERT /*+ APPEND */ INTO ziggy SELECT 10001, ‘NEW’ FROM dual;

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = ‘physical writes direct’;

NAME                   VALUE
---------------------- -----
physical writes direct    24 

Now, even though we have a Non-Unique index, the number of physical writes direct operations has indeed gone up by one.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’ZIGGY’, estimate_percent=>null, cascade=> true, method_opt=>’FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

SQL> select blocks, empty_blocks from dba_tables where table_name = ‘ZIGGY’;

BLOCKS EMPTY_BLOCKS
------ ------------
    26            0 

And indeed, the HWM has increased as well showing that indeed a Direct-Path Insert works as expected in 11g even if our PK constraint is policed by a Non-Unique Index. 

Yet another example of a difference between a Unique and a Non-Unique index which might be worth some consideration.

Seattle (Smells Like Teen Spirit) August 1, 2008

Posted by Richard Foote in Travel.
add a comment

A couple of weeks ago, I had the great pleasure of presenting at the OracleDays 2008 Conference as organised by the Puget Sound Oracle User Group and so visited the beautiful city of Seattle for the first time.  Unfortunately, my body clock takes a while to get re-adjusted and I literally spent most of my time “Sleepless In Seattle” however I had a great time regardless.

July is just the perfect time to visit with the weather nothing but superb with lots of sun and the temperatures in the mid to high 20s Celsius. Not a drop of rain in sight (I’m beginning to forget what rain looks like). I’m an Australian and I still managed to get a touch of sunburn during my visit !!

Seattle is a very scenic city, with the waters of Elliott Bay and the Puget Sound providing a lovely setting. However it was the impressive presence of Mount Rainier on the horizon that kept taking my attention. At 4,392m, it’s just about 2 times as high as Australia mainland’s highest mountain so looking at 2 Kosciusko mountains on top each other was just an awesome sight. I just loved the little clouds that were forever hovering over it.

During free time, I tried to fit as much tourist activities as I could cram in. Naturally, I had to visit the Space Needle, the futuristic looking tower that features so prominently on the Seattle skyline. Built for the 1962 World Fair, it’s a great way to see the lovely views of Seattle and the surrounding area

As a music lover, I popped in afterwards into the nearby Experience Music Project (EMP), a celebration of American popular music which has lots of really interesting things to see. Jimi Hendrix, one of the inspirations for David Bowie’s Ziggy Stardust character, is heavily featured with a great collection of Hendrix memorabilia and documentaries. The “tower” of guitars is something to see as is the building itself, which from various angles looks like some kind of huge alien arse. Yes, I have a warped view on things …

I also strongly recommend a cruise of the harbour for a different view of the city, either via a ride on a state ferry or one of the many cruise ships. I hopped on board an Argosy Cruise for an hour cruise with commentary of the harbour and enjoyed the sun, a nice beer and the beautiful views from the top deck.

The waterfront offers lots of things to do. The “Ye Olde Curiosity Shop” is worth a visit, full of weird and wacky things including shrunken human skulls. For a great lunch, I highly recommend Elliot’s Oyster House Seafood restaurant for a great selection of, you guessed it, oysters. I love US salads and I had a great house salad (and beer of course) here.

The Seattle Aquarium is certainly well worth a visit. Lot’s to see and do although for me seeing the largest octopus I’ve ever seen was certainly the highlight. Lots of tanks with lots fish, many unique to that part of the world. The huge underwater dome was really impressive with all sorts of amazing fish swimming all round (and over) you. The Sea Otters were damn cute as well !!

Another must do in Seattle is the famous “Underground Tour” where you get yet another unique view of the city, this time from one floor below the current level of the city. Seattle has an amazing history, originally built using the plentiful wood materials on what was basically swamplands, the city was rebuild after the great fire of 1889 finally using (fire-proof) bricks and a full level higher to help solve all the drainage issues. Raising an entire city to be a floor higher is an amazing feat and the underground tour gives an entertaining view of what life used to be like in Seattle 100 or so years ago.

If you love books and bookshops as much as I do, then the Elliot Bay Book Shop is a must. Located in the older Pioneer Square area of town, it’s a rare book shop that has both character and of course lots and lots of great books. My excess luggage costs increased significantly due directly to my visit !! For example, it’s not often I find a book on David Bowie I don’t already own (I have over 50).

In all, Seattle is well worth a visit if you get the chance, although after speaking to many of the locals, summer when Seattle is at its “rainless” best, is certainly the time to go.

Many many thanks go to Daniel and Helen for being such fantastic hosts.

Follow

Get every new post delivered to your Inbox.

Join 1,703 other followers