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.