Oracle 11g top features are as follows :
1. Database Replay
2. Flashback Data Archive
3. Automatic Diagnostic Repository – ADR
4. Automatic Memory Tuning
5. Case sensitive password
6. Virtual columns and indexes
7. Interval Partition and System Partition
8. The Query Result Cache
9. ADDM RAC Enhancements
10. SQL Plan Management and SQL Plan Baselines
11. SQL Access Advisor & Partition Advisor
12. SQL Query Repair Advisor
13. SQL Performance Analyzer (SPA)
14. DBMS_STATS Enhancements
15. The PL/SQL Result Cache
1. Database Reply:
This allows the total database workload to be captured, transferred to a test database create from a backup or standby database, then replayed to test the affects of an upgrade or system change. Currently, these are working to a capture performance overhead of 5%, so this will capture real production workloads
The new Database Replay tool works like a DVR inside the database. Using a unique approach, it faithfully captures all database activity beneath the level of SQL in a binary format and then replays it either in the same database or in a different one (which is exactly what you would want to do prior to making a database change). You can also customize the capture process to include certain types of activity, or perhaps exclude some.
Database Replay delivers half of what Oracle calls Oracle Database 11g\’s \”Real Application Testing\” option; the other half is provided by another tool, SQL Performance Analyzer. The main difference between these two tools is the scope involved: whereas Database Replay applies to the capture and replay of all (subject to some filtering) activities in a database, SQL Performance Analyzer allows you to capture specific SQL statements and replay them. (You can\’t see or access specific SQLs captured in Database Replay, while in SQL Performance Analyzer you can.) The latter offers a significant advantage for SQL tuning because you can tweak the SQL statement issued by an application and assess its impact. (SQL Performance Analyzer is covered in a forthcoming installment in this series.)
2. Flashback Data Archive /Total Recall
A new database object, a flashback data archive is a logical container for storing historical information. It is stored in one or more tablespaces and tracks the history for one or more tables.
You specify retention duration for each flashback data archive (could be # of years).
SQL> create flashback archive fla1 tablespace tbs1 retention 2 year;
With the \”Oracle Total Recall\” option, Oracle database 11g has been specifically enhanced to track history with minimal performance impact and to store historical data in compressed form to minimize storage requirements, completely transparent to applications, easy to setup . This efficiency cannot be duplicated by your own triggers, which also cost time and effort to set up and maintain.
To satisfy long-retention requirements that exceed the undo retention, create a flashback data archive
3. ADR – Automatic Diagnostics Repository
All trace files, core files, and the alert files are now organized into a directory structure comprising the Automatic Diagnostic Repository (ADR).
The ADR is a file-based repository for database diagnostic data. It has a unified directory structure across multiple instances and multiple products.
Beginning with Release 11g, the database, Automatic Storage Management (ASM), Cluster Ready Services (CRS), and other Oracle products or components store all diagnostic data in the ADR. Each instance of each product stores diagnostic data underneath its own ADR home directory.
For example, in an Oracle Real Application Clusters environment with shared storage and ASM, each database instance and each ASM instance has a home directory within the ADR. ADR\’s unified directory structure, consistent diagnostic data formats across products and instances, and a unified set of tools enable customers and Oracle Support to correlate and analyze diagnostic data across multiple instances.
The location of the ADR is controlled by the Oracle \”diagnostic_dest\” parameter.
Path specified in the \’diagnostic_dest\’ parameter defines the ADR root directory,ADR BASE.
The first subdirectory inside an ADR (under the directory) is always named \”diag\”
For example, if the \’diagnostic_dest\’ and thus the is specified as \”$ORACLE_HOME/log\”, then expect to find the subdirectory \”$ORACLE_HOME/log/diag\”. Below this will be .
Any number of instances/components can share same ADR BASE. Under ADR BASE there will be individual ADR HOMES.
Under ADR BASE ,the address of an will be similar to :
diag///.
Inside each ADR home, you can find several subdirectories, each for storing a specific type of diagnostic data. Among the subdirectories, you should be able to find TRACE, ALERT, INCIDENT, CDUMP etc
4. Automatic Memory Tuning
Automatic PGA tuning was introduced in Oracle 9i. Automatic SGA tuning was already
introduced in Oracle 10g. But In 11g, all memory can be tuned automatically by setting one parameter. We can literally tell Oracle how much memory it has and it determines how much to use for PGA, SGA and OS Processes. Maximum and minimum thresholds can be set.
Automatic Memory Management is being introduced in 11g. This enables automatic tuning of PGA and SGA with use of two new parameters named MEMORY_MAX_TARGET and MEMORY_TARGET.
You have to use RESULT_CACHE_MODE initialization parameter which determines the SQL query result cache behavior. The possible initialization parameter values are MANUAL and FORCE
RESULT_CACHE_MAX_SIZE is new component which has been introduced as part of 11g Memory architecture. The result cache is composed of the SQL query result cache and PL/SQL function result cache, which share the same infrastructure. Results of queries and query fragments can be cached in memory in the SQL query result cache. The database can then use cached results to answer future executions of these queries and query fragments. Similarly PL/SQL Function Result can also be cached.
5.Case sensitive password :
Passwords are expected to also become case sensitive This and other changes should result in better protection against password guessing scenarios. For example, in addition to limiting the number of failed login attempts to 10 (default configuration in 10gR2), Oracle 11g beta’s planned default settings should expire passwords every 180 days, and limit to seven the number of times a user can login with an expired password before disabling access.
6.Virtual columns/indexes :
User can create Virtual index on table. This Virtual index is not visible to optimizer, so it will not affect performance, Developer can user HINT and see is Index is useful or not.Invisible Indexes prevent premature use of newly created indexes
When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The syntax for defining a virtual column is listed below.
column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]
If the datatype is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only.
A virtual index is created in a slightly different manner than the normal indexes. A virtual index has no segment pegged to it, i.e., the DBA_SEGMENTS view will not show an entry for this. Oracle handles such indexes internally and few required dictionary tables are updated so that the optimizer can be made aware of its presence and generate an execution plan considering such indexes.
7. Interval Partition and System Partition
Interval partitioning is an extension of range partitioning, where the system is able to create new partitions as they are required. The PARTITION BY RANGE clause is used in the normal way to identify the transition point for the partition, then the new INTERVAL clause used to calculate the range for new partitions when the values go beyond the existing transition point.
The following code shows an example of a table using interval partitioning.
CREATE TABLE interval_PART (
EMPid NUMBER,
DEPTcode VARCHAR2(10),
INTERVAL_descr VARCHAR2(50),
created DATE
)
PARTITION BY RANGE (created)
INTERVAL (NUMTOYMINTERVAL(1,’MONTH’))
(
PARTITION part_01 values LESS THAN (TO_DATE(‘01-NOV-2007’,’DD-MON-YYYY’))
);
System Partition
System partitioning allows large tables to be broken down into smaller partitions, but unlike other partitioning schemes, the database has no control over the placement of rows during insert operations. The following example shows the creation of a system partitioned table.
CREATE TABLE part_system (
empid NUMBER,
deptcode VARCHAR2(10),
description VARCHAR2(50),
created DATE
)
PARTITION BY SYSTEM
(
PARTITION part_1,
PARTITION part_2
);
8. Query Result Cache
A separate shared memory pool is now used for storing and retrieving cached results. Query retrieval from the query result cache is faster than rerunning the query. Frequently executed queries will see performance improvements when using the query result cache.
The new query result cache enables explicit caching of results in database memory. Subsequent queries using the cached results will experience significant performance improvements.
Enable Result Caching :
You can set the RESULT_CACHE_MODE initialization parameter to control whether the SQL query result cache is used for all queries (when possible).
This parameter can be set at the system, session, or table level to the following values:
MANUAL – The results of a query can only be stored in the result cache by using the result_cache hint.
FORCE – All results are stored in the result cache.
Example:
If the RESULT_CACHE_MODE parameter is set to MANUAL then you must explicitly use the /*+ result_cache */ hint in your query to store the results of a query in the result cache.
SELECT /*+ result_cache */ deptno, avg(sal)
FROM emp
GROUP BY deptno;
If the RESULT_CACHE_MODE parameter is set to FORCE, and you do not wish to include the result of the query in the result cache, then you must use the /*+ no_result_cache */ hint in your query.
SELECT /*+ no_result_cache */ deptno, avg(sal)
FROM emp
GROUP BY deptno;
The database automatically invalidates a cached result whenever a transaction modifies the data or metadata of any of the database objects used to construct that cached result.
9. ADDM RAC Enhancements
Oracle Database 11g now offers what it calls database ADDM, or the ability to run ADDM reports at the overall clustered database level as well as at the individual database instance level. Whenever a new Automatic Workload Repository (AWR) snapshot is requested, or whenever an ADDM analysis has been executed against a set of existing AWR snapshots, ADDM first analyzes each individual RAC instance’s performance (aka instance ADDM) and then immediately performs an ADDM database analysis.
10. SQL Plan Management and SQL Plan Baselines
The SQL statement is hard parsed, the cost based optimizer produces several execution plans and selects the one with the lowest cost. If a SQL plan baseline is present, the optimizer tries to find a matching plan in the SQL plan baseline that is flagged as accepted. If a matching plan is found, the optimizer will uses the plan. If the SQL plan baseline doesn\’t contain a matching plan, the optimizer evaluates the accepted plans in the SQL plan baseline and uses the one with the lowest cost. If the execution plan originally produced by the optimizer has a lower cost than those present in the SQL plan baseline, it is added to the baseline as a not-accepted plan, so it is not used until it is verified not to cause a reduction in performance. If a system change affects all existing accepted plans, the optimizer will use the original plan with the lowest cost and mark this as accepted.
Oracle call this a \”conservative plan selection strategy\”, as the optimizer preferentially uses a tried an tested execution plan, even if a new plan looks like it might perform better. Only when the newer plan is proved to perform well will it be accepted for use.
The use of SQL plan baselines is controlled by the OPTIMIZER_USE_SQL_PLAN_BASELINES parameter, which is set to TRUE by default.
SQL Management Base
The SQL management base resides in the SYSAUX tablespace and stores SQL plan baselines, as well as statement logs, plan histories and SQL profiles. Space usage is controlled by altering two name-value attributes using the CONFIGURE procedure of the DBMS_SPM package.
· space_budget_percent (default 10) : Maximum size as a percentage of SYSAUX space. Allowable values 1-50.
· plan_retention_weeks (default 53) : Number of weeks unused plans are retained before being purged. Allowable values 5-523 weeks.
11. SQL Access Advisor & Partition Advisor
SQL Access Advisor was introduced in Oracle 10g to make suggestions about additional indexes and materialized views which might improve system performance. Oracle 11g has made two significant changes to the SQL Access Advisor:
The advisor now includes advice on partitioning schemes that may improve performance.
The original workload manipulation has been deprecated and replaced by SQL tuning sets.
Partition Advisor
The SQL Access Advisor, originally introduced in Oracle 10g, has been updated to include advice on partitioning existing tables, materialized views and indexes. Partitioning exiting tables can be quite a lengthy process, so don\’t expect this advice to provide a quick fix.
Enhanced Statistics Collection for Partitioned Objects
Oracle 11g includes improvements to statistics collection for partitioned objects so untouched partitions are not rescanned. This significantly increases the speed of statistics collection on large tables where some of the partitions contain static data. Where partition exchange load (PEL) is used to add data to the a table, only the newly added partition must be scanned to update the global statistics.
12.SQL Query Repair Advisor
SQL Repair Advisor after a SQL statement fails with a critical error. The advisor analyzes the statement and in many cases recommends a patch to repair the statement. If you implement the recommendation, the applied SQL patch circumvents the failure by causing the query optimizer to choose an alternate execution plan for future executions.
13. SQL Performance Analyzer
SQL Performance Analyzer (SPA), a key feature of the Real Application Testing option introduced in Oracle Database 11g, can predict and prevent SQL execution performance problems caused by system changes.
SQL Performance Analyzer provides a granular view of the impact of changes on SQL execution plans and execution statistics by running the SQL statements in isolation before and after a change. SQL Performance Analyzer compares the SQL execution result, before and after the change, and generates a report outlining the net benefit on the workload due to the changes as well as the set of regressed SQL statements. For regressed SQL statements, appropriate executions plan details along with recommendations to remedy them are provided. SQL Performance Analyzer is well integrated with existing SQL Tuning Set (STS), SQL Tuning Advisor and SQL Plan Management functionalities. SQL Performance Analyzer completely automates and simplifies the manual and time-consuming process of assessing the impact of change on extremely large SQL workloads (thousands of SQL statements).
14.DBMS_STATS Enhancements
11g there have been some enhancements made to the DBMS_STATS package. Overall the GATHER_* processes run faster but what stands out to me is the speed and accuracy that DBMS_STATS.AUTO_SAMPLE_SIZE now gives. As a performance person, I often times make reference to letting the numbers tell the story, so lets dive into a comparison between 10.2.0.3 and 11.1.0.5.
15.The PL/SQL Result Cache
PL/SQL function result cache provides a simple way to boost the performance of PL/SQL functions by saving the results of function calls for specific combinations of input parameters in the SGA. These results can be reused by any session calling the same function with the same parameters. This can result in a significant performance boost when functions are called for each row in an SQL query, or within a loop in PL/SQL. This article describes the usage and administration of the function result cache.
