Oracle 11g - Statisticly speaking!

OK, it's time for another Oracle Database 11g New Feature! Today some new statistics related features.

In Oracle database 11g you now have two new kinds of statistics that you can collect. Collectively these are known as extended statistics. The two kinds of extended statistics you can collect are:

1. Multi-column statistics
2. Expression statistics

Prior to Oracle Database 11g Oracle had no way of understanding the relationship of data within multiple columns of a where clause. Oracle Database 11g adds multicolumn statistics to the mix to try to solve this problem. Now the optimizer can generate more intelligent cost based plans when you have multiple columns in your where clause, based on the combined selectivity of both columns. Multi-column statistics are not generated automatically, when you generate statistics. You have to define the columns you want to generate the statistics on when analyzing the table. Here is an example of generating multi-column statistics on the table DUDE for columns DUDENO and DUDES_JOB. Note the "for columns" syntax that defines the columns to build the multi-column statistics on:

exec dbms_stats.gather_table_stats(null,'DUDE',
method_opt=>'for all columns size skewonly
for columns (DUDENO,DUDES_JOB)');

Expression statistics allow Oracle to collect selectivity information based on the application of a function on a column. This has direct relationship to the use of function based indexes. Again, you collect expression statistics with dbms_stats when you collect table statistics as seen in this example:

begin
dbms_stats.gather_table_stats(null, 'DUDE',
method_opt=>'for all columns size skewonly
for columns (lower(dude_name))');

Now the optimizer can rationally make execution plan choices with regards to the selectivity of the data in the dude_name with the lower function applied.

There is even more in my new book, Oracle Database 11g New Features. It's available for pre-sales on Amazon and should be out in November!

http://www.amazon.com/Oracle-Database-11g-New-Features/dp/0071496610/ref=sr_1_2/102-1704151-7971347?ie=UTF8&s=books&qid=1188535426&sr=1-2

There are more new statistics related features in 11g including publish/subscribe and restore of old statistics!! I'll talk about that in another post.

Oracle 11g Oops...

Been quite busy of late, so my blog has not had quite the number of updates that I'd like. One thing I thought I'd share with you is that I found my first Oracle 11g bug last week. Apparently when you are using the FRA with 11g it decides to archive redo logs to the FRA (as one would expect) AND to the default archivelog destination directory (which one does not expect). I've opened an SR with Oracle on this and we will see how it goes. I've heard a story or two of other bugs that have been discovered, but I've not heard details yet.

So, the moral of the story with 11g is, be careful out there!

I'm finishing up another chapter of my new book, Oracle Database 11g New Features. I'll have some more Oracle features to share with you in the next couple of days, so please standby and be patient.

Landing on Insturments...

So, more Oracle Database 11g in a couple of days. I'm wrapping up another chapter right now and I'll post a bit or two soon.

For this post though, I'd like to share with you that I'm back working on my instrument rating. For me, the best part of flying is cross-country trips. Some people like to get out and just putter around, but I love the experience of actually flying somewhere. Seeing new things, new airports and so on. Because of this, weather is a bit more of an issue for me.

I started my instrument training back in Chicago after I got N7598U. I logged about 15 hours of training or so. Now, I'm back at it! I took my written a few months ago, so thats out of the way. I've just started the flying back up this week and I've logged about 4 hours. Here is one of the approach plates that I use for doing an ILS into Ogden...



I find this stuff pretty cool. This is a precision approach which means it pretty much takes you down to the runway both vertically and horizontally.

I flew this approach and a couple of others on Tuesday, and flew down to Provo on Wed. Saturday I'm back up again and I hope to be done by October if I'm lucky.

More on this later, and more Oracle Database 11g!!!!

Oracle Support and 11g

There were some initial reports that Oracle Support had pushed back on providing SR support for Oracle Database 11g after it's initial release last week. I've not heard any reports in the last couple of days, so I hope this problem has been resolved.

However, this does bring up a good topic, dealing with Oracle support.

First and foremost, one has to realize that while Oracle support is a service organization, it is a big organization. As a result Oracle support sometimes lumbers slower than one would like, and sometimes you get a support analyst that is less than steller. All this is to be expected with a large organization. Because of this it's important to know how to move around such a beast.

First of all, when you open an SR and you need some form of response, make sure the SR is a priority 2 or better. Obviously if you don't want to work the thing 24/7 then you don't need it to be a priority 1, but I've seen priority 4's lag into forever before you get help. I've never seen a priority 3 I don't think (not that I've noticed), so I don't know if those even exist. How do you ensure that your SR is a priority 2? I've noticed that when you enter the SR, if you mark the last 3 questions as NO. These questions are:

Can you easily recover from, bypass or work around the problem?
Does your system or application continue normally after the problem occurs?
Are the standard features of the system or application still available; is the loss of service minor?

These will make you a priority 2. You can also always ask the person working your SR to escalate to a 2.

The next key is escalation. There was a very good post on ORACLE-L which references Chris Warticki's Blog with information about this very topic, so rather than rehash it, I'll just post a blog link here:

http://blogs.oracle.com/Support/2007/07/18#a18

The bottom line with Oracle support seems to be the squeaky SR gets the oil. You pay a lot for that support, so squeak my friends!

[Edit: added some clarity to a sentence]

Oracle Database 11g Finer Grained Dependencies

So, here is a promised new feature for Oracle Database 11g!! Have you ever had something like this happen:

We have a view, emp_view built on EMP as seen in this query:
set lines 132
column owner format a8
column view_name format a10
column text format a50

select dv.owner, dv.view_name, do.status, dv.text
from dba_views dv, dba_objects do
where view_name='EMP_VIEW'
and dv.view_name=do.object_name
and dv.owner=do.owner
and do.object_type='VIEW';

OWNER VIEW_NAME STATUS TEXT
-------- ---------- ------- ---------------------
SCOTT EMP_VIEW VALID select ename from emp

Now, we add a column to EMP and watch what happens to the view:

alter table emp add (new_column number);
select dv.owner, dv.view_name, do.status, dv.text
from dba_views dv, dba_objects do
where view_name='EMP_VIEW'
and dv.view_name=do.object_name
and dv.owner=do.owner
and do.object_type='VIEW';

OWNER VIEW_NAME STATUS TEXT
-------- ---------- ------- ---------------------
SCOTT EMP_VIEW INVALID select ename from emp


Now.... Oracle database 11g has improved dependency management. Let's look at this example in 11g:

select dv.owner, dv.view_name, do.status, dv.text
from dba_views dv, dba_objects do
where view_name='EMP_VIEW'
and dv.view_name=do.object_name
and dv.owner=do.owner
and do.object_type='VIEW';

OWNER VIEW_NAME STATUS TEXT
-------- ---------- ------- ---------------------
SCOTT EMP_VIEW VALID select ename from emp


alter table emp add (new_column number);
select dv.owner, dv.view_name, do.status, dv.text
from dba_views dv, dba_objects do
where view_name='EMP_VIEW'
and dv.view_name=do.object_name
and dv.owner=do.owner
and do.object_type='VIEW';

OWNER VIEW_NAME STATUS TEXT
-------- ---------- ------- ------------------------------
SCOTT EMP_VIEW VALID select ename from emp


Note in 11g that the update did not invalidate the view. If the change had been to the ename column, then it would have invalidated the view since there is a direct dependency between the ename column and the view. This same new dependency logic applies to things like PL/SQL code too.

More on 11g New Feature topics in my new book, Oracle Database 11g New Features from Oracle Press.

All along the watchtower

11g SOON... However I was sitting here and thinking about some of my other passions. I watch almost zero TV. For some reason though, I seem to like Sci-Fi. There are a few shows that have a fond spot in my heart... I'm not one of those guys that walks around in some uniform at some convention, but they do speak to me.

They are:

Battlestar Galactica (the new rendering).... If you didn't catch the season 3 finale, here it is... simply amazing I think... Season 4 will be it's final season, very sad!



Another show with a soft spot in my heart, Babylon 5... Seasons 2-3-4 were during some times of very good, and very tough times. There is a new CD out just now, Babylon 5: Lost Tales. It was just released a couple of weeks ago. The two stories on it are not the best of Babylon 5 but it's nice to see some B5 again. Here is a YouTube link to the trailer:



and another bit on YouTube from the series, the season 3 finale (I love YouTube):



and finally, a campy old show that takes me back to about age five, UFO...Here are the opening credits with what I think is one of the coolest opening theme songs of all time (it *is* CAMPY):




Ok.... so I've bored you with enough personal stuff. Tomorrow something 11g related...

[Edit: misspelling...]

My ode to Disney

My Ode to Disney...

Walt thank you for what you have done,
for Disneyland is really truly fun.
However for me, I must confess,
that Disneyworld is truly the best.

Disneyland was first, it is true.
It's an amazing experience for one who is new.
Disneyworld though, I must admit,
I think it better, by just a bit.

Both have Splash Mountain, a super ride,
but Disneyworlds is better, I must confide.
At Disneyland, it's one person per seat,
At Disneyworld, two together is neat.

The Haunted Mansion, oh what a thrill.
In both parks it's great, that is until,
you get to the dead center of the room,
and our host betrays a sense of doom.

Alas, Pirates of the Caribbean at Disneyland,
must take the prize well in hand.
It is a bit better than Disneyworld I think,
since there is also a place to eat and drink.

The castles simply can not compare,
at the two Disney properties fair.
Disneyworld's is a grand sight to see,
Disneyland's looks, well, quite wee.

For some reason I must admit candidly,
the Contemporary makes my Disney experience handily.
I love the look, I remember as a child,
wanting to stay there and just go wild.

In the end, both will do fine,
when I need to escape the adult confine.
Mickey and pals, are great to see,
and a Disney experience is wonderful for me.

If you came looking for Oracle, do not fear,
Another installment is very, very near.
Angst not, more is soon to come,
for sharing Oracle goodies is way to fun.

Copyright 2007 by Robert G. Freeman
All Rights Reserved
Like anyone really cares or would ever want to reprint this anyway... :-)

Oracle 11g is released and Invisible Indexes!!

I'm a bit tardy with a blog entry about this since it happened last week, but Oracle Database 11g 11.1.0.6 for Linux has been released!! I've got it up and running on my Linux VMWare machine right now!

For those of you interested in yet another new feature, how about invisible indexes? No, invisible indexes are not a character in a Wes Craven slasher picture, but rather a way to kind of turn on/off an index from the point of view of the optimizer. For example, we have a table called TEST with an index called ix_test. Look at how the execution plan changes if I make the ix_test index invisible:

-- first the index is visible
SQL> alter index ix_test visible;
Index altered.

SQL> select * from test where id=1;

ID
----------
1


Execution Plan
----------------------------------------------------------
Plan hash value: 416078300

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_TEST | 1 | 13 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------

-- Now, make it invisible
SQL> alter index ix_test invisible;

Index altered.

SQL> select * from test where id=1;

ID
----------
1


Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 24 (5)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 13 | 24 (5)| 00:00:01 |
--------------------------------------------------------------------------

One thing I noticed on the production cut is that you can not use a hint and have the index be used. So something like this does not seem to work:

select /*+ index(test ix_test) */ * from test where id=1;

The doc's I've seen so far seem to indicate this should work, but it might warrant some additional investigation before I call it a bug.

[Edited addition]
In looking at the production documentation now, all references to hints making the invisible index accessible by the optimizer are gone. So, it appears that hints, indeed, do not impact the optimizers ability to see an invisible index.

[/edit]

Robert goes to Mexico

So, I mentioned in an earlier post that I took a little vacation. My wife and I took a 3 day cruise to Ensenada, Mexico. We like to cruise, but we always find the end of the cruise very depressing. To avoid that depression, we decided to also spend a couple of days at Disneyland.

I'd never cruised before I got married, so I was not sure how much I would like it. I now find that it is my favorite thing to do on vacation along with Disney and riding trains. For me, a cruise is really the best of all worlds, you get to go places you have never been, you get to eat well, you can just be lazy and do nothing or you can get involved in a myriad of activities on the ship. My wife and I take different "kinds" of cruises. There are those that we take to actually goto the places and see the sights, for example we did an Alaska cruise which was incredible. Then there are our "don't get off the ship" cruises, which are usually to places we have already been. We take advantage of those cruises to sleep in, read, eat, swim and really enjoy the ship and having nothing to do for days on end.

Our cruise started with a flight from Salt Lake to Long Beach. After nearly missing our flight due to a long TSA line, we had a nice Jet Blue flight to Long Beach. Long Beach is an interesting airport. It's not quite modern, but just short of third world. I don't know if I've ever collected my bags, outside, at an US airport. No airstairs either, just a roll-up platform. After that we ate at the Airport Marriott (highly recommended!) and then headed to the ship.

One of the best things about cruising is the food. We were cruising on Royal Caribbean, and the food on this particular ship was above average for a cruise line.

One problem on the shorter cruises is that the clientele is younger, and a certain number of them are there, first and foremost, to get drunk. It was clear, that even before we left port, that the partying had begun. I don't mind it so much, except for the idiots that get rude and feel like they have to yell and scream at the top of their lungs. Other than that, I find drunken 21 year olds pretty funny.

I must admit, that I have a really hard time getting away from work, even when on the cruise. But there is nothing better than sitting on your balcony with the fresh sea air blowing in your face and the sound of the waves below as you cruise. It makes working so much less work.

On the way back from Ensenada, I was taking a bit of a restless nap in the late afternoon. I decided to get up and go out on the balcony. Lo and behold, WHALES!! Holly smokes, not just one but a whole pod of the things. It was amazing. I saw more whales on my 3 day quick cruise to Mexico than I saw on my 7 day Alaska cruise!! I yelled at my wife to come and see the whales and she was just so excited... I love it when she gets excited, it just makes my day to see her happy. It was amazing to watch them break the surface and see them exhale. Then you could see their big tails break as they headed back under the water. Amazing.

Every time I see whales though, I must admit.... I think of Star Trek IV and Mr. Scott's line, "Keptn', there be WHALES here!". I'm not a nerd, I'm not a nerd!

Disney was a great experience too, but I must admit that I prefer DisneyWorld to Disneyland. Thats just personal preference I am sure, and Disneyland is a perfectly wonderful place to go, but I miss seeing the Contemporary, for example. Plus, there is just so much more to do at DisneyWorld. I'll talk more about Disney in a later entry...

Well, enough goodies for now...


[Edited for spelling]

11g Security New Feature... A short one...

Two new security related features to Blog about this week. First, Oracle Database 11g comes with a new view called DBA_USERS_WITH_DEFPWD. What is for you ask? This view will show you the user accounts that are assigned default passwords, like scott/tiger. You can therefore change those passwords and be more secure. This view has one column, USERNAME.

Also, passwords in Oracle Database 11g will be case sensitive now! When you upgrade, your old passwords will not be case sensitive, but the first time you change the password of an account after an upgrade it will become case sensitive. New accounts will be case sensitive out of the box. The parameter sec_case_sensitive_logon will be provided to disable/enable this feature.

One other note. All the features I'm discussing in this Blog at this point are from Beta code. They can change between now and the time production code comes out, so be aware.

More on this and many other Oracle Database 11g topics in my new book from Oracle Press, Oracle Database 11g New Features. Check it out!

[Post edited for spelling error]

11g New Feature!! Revenge of Compression!!!

As promised, an 11g new feature for you. Today, Table compression.

While Oracle Database 10g offered limited table compression, Oracle Database 11g offers full blown table compression. Compression is supported in the following cases:

· Direct path SQL*Loader operations

· Create table as select commands

· Parallel inserts, or serial inserts with an append hint.

· Single row, or array insert and updates.

This allows you to use compression on any database table, any time. The result can be a much reduced storage requirement and potentially much better performance!

More on this and many other new features in my upcoming book Oracle Database 11g New Features.