My top 10 Oracle 11g New Features Part 1

I have had the chance to test Oracle 11g database (beta 5 at the moment).
I want to start a series of 10 posts here which give overviews over the (to my mind best) top new features in Oracle database 11g:

 I will first describe the new Features in CHANGE MANAGEMENT because I think that these are really revolutionary:

 1.  SQL Performance Analyzer (sql replay):
              With this feature we can predict the impact of system changes on a workload. Using this we can forecast changes in response times for SQL after a change. This can be very handy in cases like parameter changes, schema changes, hardware changes, os changes, db upgrades…Any change that influence sql plans is a good candidate for thîs feature.

How does it work?
 We use so called SQL TUNING SETS (STS) to hold workload information such as sql text, binds, execution plans plus statistics on executions. The STS is populated from the cursor cache over a certain periode of time. We capture teh increments for all these informations periodically over time. Only the desired statements are captured the rest of the workload is filtered out. After we have created a staging table which we can ship to a test system with the changed configuration using datapump, we can capture the desired workload and load it into the staging table. There we can unpack the STS from the staging table on the test system and run the workload there. Then we can compare the sql exection statistics of the runs for the workload on the two different systems.

We can also use the SQL Performance Analyzer to run the same workload on the same system multiple times wit different parameter settings, for instance with different optimzer versions enabled, in order to find out differences in excution plans after upgrades.

Sources for the workload to capture can, aside the cursor cache,  also be AWR snapshots and AWR baselines. 

The technology behind this feature is a new package called DBMS_SQLPA. Here is an overview for the procedures of this package: 

CANCEL_ANALYSIS_TASK Procedure Cancels the currently executing task analysis of one or more SQL statements
CREATE_ANALYSIS_TASK Function Creates an advisor task to process and analyze one or more SQL statements
DROP_ANALYSIS_TASK Procedure  Drops a SQL analysis task
EXECUTE_ANALYSIS_TASK Function & Procedure Executes a previously created analysis task
INTERRUPT_ANALYSIS_TASK Procedure Interrupts the currently executing analysis task
REPORT_ANALYSIS_TASK Function Displays the results of an analysis task
RESET_ANALYSIS_TASK Procedure Resets the currently executing analysis task to its initial state
RESUME_ANALYSIS_TASK Procedure Resumes a previously interrupted analysis task that was created to process a SQL tuning set.
SET_ANALYSIS_TASK_PARAMETER Procedures Sets the SQL analysis task parameter value
SET_ANALYSIS_DEFAULT_PARAMETER Procedure Sets the SQL analysis task parameter default value

There is an interface to the SQL Performance Analyzer in the enterprise manager in the Advisor Central. 
A number of new to DBA_ADVISOR_* views have been added in 11g which we can use to display information from the SQL Performance Advisor. Also a number of DBA_ADVISOR_* views which were alredy available in 10g have new columns, such as DBA_ADVISOR_TASKS and DBA_ADVISOR_FINDINGS

=;-)

Hope to see you in one of my 11g seminars

congratulations for 20 year of SOUG

Yesterday I was in Luzern at the Verkehrshaus where the celebration party for the 20th. aniversary of the foundation of the Swiss Oracle Usergroup SOUG.

A number of interesting speakers were scheduled for laudatio speeches such as Nenad Rozic, Sr. Director EMEA Server Technologies & Global DBA support. I had a very interesting chat with him about quality Oracle’s support and the management cultur within the company.

Also very interesting was the speech of Gunter Dueck, an top manager of IBM who talked about innovation processe in the industry and showed in a very easy to understand way what the blocking factors for innovation within large companies are. He is very impressing personality and has very interesting ideas about the cultur in large companies. I think that I will some of his books since I have the impression that I understand what he wants to say and that it is important to change something about the culture in management.

The most interesting speaker from the technical point of view was
Guenter Stuerner, the head of Oracle pre-sales in Germany.

He talked about Oracle 10g Time Ten database and 11g New Features.
and also showed some interesting old photographs from the first days of Oracle,
this one here shows

Larry Ellison, Bruce Scott (the master of a cat named TIGER), Bob Miner, Ed Oates even before Oracle was named Oracle.

Also I met some of my customers

whome I hope to see again in one of my new seminars and trainings.

Oracle University Switzerland announces brand new Oracle 11g courses

Today I received this below mesage from Oracle University Switzerland
and I am happy to post it here just slightly adjusted:

Oracle University is pleased to bring you
Oracle Database 11g New Features with Lutz Hartmann – Oracle ACE
Oracle Database 11g: New Features Overview Seminar (1 day)

Date	Location	Price	Enrolment
27 August 2007	Baden	SFR 850	Enrol Now
29 August 2007	Digicomp Bern	SFR 850	Enrol Now
31 August 2007	Digicomp Geneva	SFR 850	Enrol Now

In this seminar, you are introduced to the planned key new capabilities of Oracle Database 11g Release 1 and gain an understanding of how to take advantage of them. Topic areas covered include new features for
· Managing storage
· Ensuring high availability
· Improving scalability and performance
· Strengthening security
· Managing data warehouses The instructor lecture is supported by recorded product demonstrations.
By attending this seminar, you can more effectively plan your use of Oracle Database 11g Release 1. or for more indepth insight plus hands-on
Oracle Database 11g: New Features for Administrators (5 days)

Date	Location	Price	Enrolment
24-28 September 2007	Geneva	SFR 4250	Enrol Now
05-09 November 2007	Zürich	SFR 4250	Enrol Now
12-16 November 2007	Geneva	SFR 4250	Enrol Now
17-21 December 2007	Baden	SFR 4250	Enrol Now

This course gives you the opportunity to learn about – and practice with -
the planned new change-management features and other key enhancements in Oracle Database 11g Release 1.You will examine the benefits and use of
· Planned new features in managing change
· Diagnosing and recovering from problems
· Ensuring high availability
· Improving scalability and performance
· Strengthening security
· Other areas that concern database administrators The hands-on practice sessions help reinforce your understanding of these planned new capabilities in Oracle Database 11g Release 1 so that you will be ready to apply them when you return to your office. » connect Lutz Hartmann as sysdba

San Francisco by Limo

On the last day of the Oracle 11g New Features pilot in San Francisco we all took a tour round the City in a very fancy car

I had always wanted to ride in a Stretch Limo 
   and it was lots of fun!

We went to the Golden Gate Bridge …to the Coit tower  from where you get a beautiful view over the city  .. we dashed through
China Town walked Lombard St. ..

and at the end of the tour we went to OLDEST ITALIAN RESTAURANT OF AMERICA the Fior d’Italia which is a must for every lover of Italian cusine who stays in San Francisco! Altough there are some Italians who say that it is impossible to eat pasta outside Italy. I would recommend them to try it. I had my best Saltimbocca ever there. It was just devine!

After dinner we all went downtown again with the obligatory   cable car. It was so much fun and I realy miss some of these people aready.

How to crack Oracle passwords within seconds?

Yesterday The Hackers’ Choice organization published a paper that shows how to break into an Oracle 10g database very easily.

And there is not only the paper, but “…THC further releases practical tools to sniff and crack the password of an oracle database within seconds…” all you need to know is how to use them.

Oracle in old Chinese

 Again it is a greate course with Jean-Francois Verrier teaching. It must be very difficult to teach a course which has never been taught before by anybody at all.
In the pilot class for Oracle database 11g New Features for administrators in San Francisco there are instructors from all around the world who want to deliver the new course soon in their countries.
Amoung other countries they are comming from Denmark, Germany, Korea, China, the U.K., India, Italy, myself from Switzerland and I think from some more countries as well. This makes the class especially interesting: you get to know a lot people from abroad.

 Yesterday Hansen an Oracle instructor from Shanghai
explained the
Chinese  Oracle logo   Jia Gu Wen
to Jean-Francois.
He really gave his best to get the message through to him  …but

I am not sure if everything really got through to J.F.  ..he seemed to be a little puzzled to me!

How to adjust the threshold for statistics to become stale in Oracle 11g

I was asked recently in one of my seminars if it is possible to adjust the threshold value for optimizer statistics to be considered being stale in Oracle database 10g. 

 As you all know there is an auto task job which is automatically created with every Oracle 10g database called gather_stats_job which automatically collects statistics every night and on the weekend by default for all object for which more of 10% of the values have changed. This behaviour is enabled by default in 10g and is implemented by TABLE MONITORING which is enabled for all tables by default.

In Oracle 10g the 10% threshold is a hard coded value and cannot be adjusted. This is a particularly painful with columns with skewed data in large tables.  Here you would have to lock the statistics and gather them manually when needed with an adjusted sample size of probably more than 10%, because otherwise you would get terrible execution plans possibly. 

 In Oracle database 11g there is a new procedure in the DBMS_STATS package we can use to even set individual thresholds on segment level.
Here is  how it works:

lutz@ora11gBETA5 _SQL > exec dbms_stats.set_table_prefs(‘HR’, EMPS’, ‘STALE_PERCENT’, ‘15′)

The previous statement adjusts the threshold for the table EMPS owned by HR to 15%.  

All togehter there are three new parameters in DBMS_STATS:
- STALE_PERCENT which overwrites the default threshold of 10%
- INCREMENTAL for statistics on partitions incrementally gathered
- PUBLISH in order to populate the data dictionary with the newly gathered statistics or not, whcih can conme in handy if you want to test with the new statistics on another system before really making them effective on production.

I think that this is exactely what a lot of people have been waiting for since a long time

Oracle 11g to be launched in July

On July 11th. Oracle 11g will abviousely be launched officially by  Oracle President Charles Phillips and Oracle Senior Vice President Andy Mendelsohn.
At least this is what Eddy Awad just posted on his blog.

Arrived in San Francisco once again

Saturday night I arrived in San Francisco.

On Sunday I took a day off:
no burea, no Oracle, no telefone conferences

… and walked along the bay  …where I found these sea lions relaxing on pontons in the harbour. I found out that they first apeard after directly the earth quake. Their populatin has grown up to about 300 int the summer and about 600 during the winter time.

This week I will take part here in the first
Oracle 11 New Features for Administrators course ever