Friday, December 14, 2007

I like sarcasm...

It is my favorite form of humor.  And the onion drips with it.

This article however, "Factual Error Found On Internet", is the Crème de la Crème.  Very funny, very very funny.  It is an old one, but funny still.

On the other hand, there is some factual information out there - I enjoyed this poster from Germany.  Thought provoking.

Thursday, December 13, 2007

A data guard education opportunity...

In the UK - in March.  See this link for details.  I know the instructor Carel-Jan Engel and he not only presents very well, he knows his stuff - especially regarding data guard.

So, if you want to be taught data guard by someone who uses it a lot and are near to the UK - this class is for you.

Tuesday, December 11, 2007

Ok, so what happened up there on stage...

I gave an 11g for DBA's "new feature" talk at UKOUG.  It was going pretty good - until...  Well, let me back up and start at the beginning.  I began my talk with:

You are going to see for the first time ever on screen a series of scripts.  I have not used these before in public - live.  Fingers crossed, let's hope for the best.

I shouldn't have done that - I jinxed myself :)

Everything was going great - until I got to some partitioning examples, interval partitioning in particular.  The script goes like this...

Ok, there is this new feature with partitioning called interval partitioning.  If you use this - then Oracle will automatically create new partitions based on your template, your description of how the partitions should be created.  This means you do not have to preallocate your partitions for a rolling window anymore.  For example, suppose you have an audit trail range partitioned by month - at the end of each month, you have to make sure you create the partition for NEXT month to avoid any errors.  With interval partitioning you can tell us to create a new partition on the fly as needed - without DBA intervention.  For example:

ops$tkyte%ORA11GR1> create table audit_trail
2 ( ts timestamp,
3 data varchar2(30)
4 )
5 partition by range(ts)
6 interval (numtodsinterval(1,'day'))
7 store in (users, example )
8 (partition p0 values less than
9 (to_date('23-feb-2007','dd-mon-yyyy'))
10 )
11 /
Table created.

 That creates a range partitioned table with everything having a TS attribute value less than 23-Feb-2007 stored in partition P0.  Further, we desire each new partition automatically added by Oracle to contain 1 days worth of data (numtodsinterval is a function that turns a number into a day to second interval). 


In order to demonstrate that Oracle does "logical things" here, I will insert a date in the future with regards to 23-Feb-2007, please note how Oracle did not create dozens of partitions for all of the interior values - it creates the MINIMAL set of partitions needed to contain our data:

ops$tkyte%ORA11GR1> insert into audit_trail (ts,data) values ( '02-jun-2007', 'xx' );
1 row created.

ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'AUDIT_TRAIL'
4 order by partition_position;

PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- -----------------------------------
P0 USERS TIMESTAMP' 2007-02-23 00:00:00'
SYS_P201 EXAMPLE TIMESTAMP' 2020-06-03 00:00:00'

 and then another date further out in the future, we'll use this to show that - even though it looks like all of the data between the 3rd of June and 15th of Sept will go into it - it won't:

ops$tkyte%ORA11GR1> insert into audit_trail (ts,data) values ( '16-sep-2007', 'xx' );
1 row created.

ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'AUDIT_TRAIL'
4 order by partition_position;

two PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- -----------------------------------
P0 USERS TIMESTAMP' 2007-02-23 00:00:00'
SYS_P201 EXAMPLE TIMESTAMP' 2020-06-03 00:00:00'
SYS_P202 EXAMPLE TIMESTAMP' 2020-09-17 00:00:00'

Right now, it might appear that anything between 3-jun and 16-sep would go into SYS_P202, but if we insert a date in between the existing dates:

ops$tkyte%ORA11GR1> insert into audit_trail (ts,data) values ( '11-sep-2007', 'xx' );
1 row created.

ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'AUDIT_TRAIL'
4 order by partition_position;

PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- -----------------------------------
P0 USERS TIMESTAMP' 2007-02-23 00:00:00'
SYS_P201 EXAMPLE TIMESTAMP' 2020-06-03 00:00:00'
SYS_P203 USERS TIMESTAMP' 2020-09-12 00:00:00'
SYS_P202 EXAMPLE TIMESTAMP' 2020-09-17 00:00:00'

We can see that Oracle created SYS_P203 - it will fill in the interior partitions as needed...


It was at this point I received a question from the audience.  It was simply:


Why are your range high values in the year 2020?


Indeed.  I had not noticed that.  I pointed out right there and then "Hey, this is the first time after all...", but I did not see immediately what I had done.  I knew what I changed in my scripts (I used to use SYSDATE in the example, but as time marches on - I'd prefer to have static dates so I can have a predicable experience).  But it was not immediately obvious - so I said "Watch my blog next week, I'll write about it"


Which brings us here - what happened?


A few people pointed it out for me after the session - it was the use of the default Oracle DATE format to insert a string into a TIMESTAMP!  On my system, the NLS settings were:

ops$tkyte%ORA11GR1> select * from nls_session_parameters;

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

17 rows selected.

 See that NLS_TIMESTAMP_FORMAT - when applied to 11-sep-2007 for example, it becomes:



  • 11 for the DAY
  • -
  • sep for the MONTH
  • -
  • 20 for the RR year - which is 2020
  • (space is ignored in the format, unless you use fx - for Format eXact)
  • 07 for the HOUR

We can see this easily:

ops$tkyte%ORA11GR1> create table t ( x timestamp, y timestamp );
Table created.

ops$tkyte%ORA11GR1> insert into t values ( '23-feb-2007', to_date( '23-feb-2007' ) );
1 row created.

ops$tkyte%ORA11GR1> select * from t;

X Y
----------------------------------- ------------------------------
23-FEB-20 07.00.00.000000 AM 23-FEB-07 12.00.00.000000 AM

 When we convert a string directly into the timestamp - we get 23-feb-2020, hour 7am.  If we use a DATE - which has a different default mask of dd-mon-rr - which works also with dd-mon-rrrr dates since the time is NOT included - we get the expected value.


So, the problem I encountered was an implicit conversion - introduced by me right before the session.  (Note to self: stop changing stuff before sessions, it is a bad idea).


The fix would be to use to_timestamp:

ops$tkyte%ORA11GR1> insert into audit_trail (ts,data) values ( to_timestamp('11-sep-2007','dd-mon-yyyy'), 'xx' );

1 row created.

ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'AUDIT_TRAIL'
4 order by partition_position;

PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- -----------------------------------
P0 USERS TIMESTAMP' 2007-02-23 00:00:00'
SYS_P204 USERS TIMESTAMP' 2007-06-03 00:00:00'
SYS_P206 EXAMPLE TIMESTAMP' 2007-09-12 00:00:00'
SYS_P205 USERS TIMESTAMP' 2007-09-17 00:00:00'

So, now the demo works as expected and no (evil) implicit conversions happen... 

Saturday, December 08, 2007

Well, this certainly has potential...

Alberto Dell'Era sent me an email pointing out an interesting blog entry on a new blog - the "Inside the Oracle Optimizer" blog.

The first technical article there is about the new "Intelligent Cursor Sharing" feature in Oracle Database 11g Release 1.  I wrote about this very briefly in an upcoming Oracle Magazine article - but this post is chock full of details - very clear.

I liked the first entry they posted describing what this blog will be about:

This blog will be our groups opportunity to set the record straight on the Oracle Cost Based Optimizer, and the statistics that feed it. It will also give you the opportunity to post questions and have them answered by the team.

So, it sounds like it is going to be monitored by people from the optimizer group itself.  I've added it to my list of RSS feeds for sure.

Definitely something to check out!