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