New features of Oracle 11g

Source: Internet
Author: User
Tags continue dba file system range regular expression resource knowledge base oracle database

Oracle 11g on July 11, 2007 11 O'Clock (Beijing time 11th 22 o'clock) officially released, 11g is Oracle 30 years to release the most important version of the database, according to the needs of users to achieve information lifecycle management (information Lifecycle Management) and many other innovations.

I. Outline of new features

1. Database Management Section

Database Replay (DB replay)

This feature captures the load of the entire data and passes it on to a test database created from a backup or standby database, then repeats itself to test the effect of the system tuning.

SQL Replay (SQL Replay)

Similar to the previous feature. But just capture the portion of the SQL payload, not the full load.

Planning Management (Plan Management)

This feature allows you to fix the query plan for a particular statement, regardless of whether the data changes or the database version changes will not change her query plan.

Automatic diagnostic Knowledge Base (Automatic diagnostic Repository ADR)

When Oracle detects an important error, it automatically creates an event (incident), captures the information associated with the event, and automatically checks the database for health and notifies the DBA. In addition, this information can be packaged and sent to the Oracle support team.

Event Packaging Services (Incident packaging service)

If you need to test further or retain relevant information, this feature can package information related to an event. And you can also send packaged information to the Oracle support team.

Patch based on features (Feature Based patching)

This feature makes it easy to distinguish between the features in a patch package that you are using and that you have to play. Enterprise Manager (EM) enables you to subscribe to a feature-based patch service, so Enterprise Manager can automatically scan those features you are using with patches that can be hit.

Automatic SQL optimization (Auto SQL tuning)

The 10g automatic optimizer can write tuning recommendations in SQL profile. In 11g, you can have Oracle automatically apply a profile with 3 times times the original performance to the SQL statement. Performance comparisons are performed by a new administrative task in the Maintenance window.

Access Counsel (Access Advisor)

11G's access advice can give zoning advice, including recommendations for new interval partitions (interval partitioning). An interval partition is equivalent to an automated version of a range partition (range partitioning), and she can automatically create a partition of the same size if necessary. range partitions and interval partitions can exist in a single table, and the range partition can be converted to an interval partition.

Automatic memory optimization (Auto Memory tuning)

In 9i, automatic PGA optimization was introduced, and automatic SGA optimization was introduced in 10g. To 11g, all memory can be automatically optimized by setting only one parameter for the entire table. As long as you tell Oracle how much memory is available, she can automatically specify how much memory is allocated to the PGA, how much memory is allocated to the SGA, and how much memory is allocated to the operating system process. Of course, you can set the maximum and minimum threshold values.

Resource Manager (Resource Manager)

The 11G resource Manager can manage not only the CPU but also IO. You can set the priority, file type, and ASM disk group for specific files.


ADDM was introduced in 10g. In 11g, ADDM can recommend not only a single instance, but also the entire RAC (that is, the database level). In addition, some instructions (Directive) can be added to the ADDM to ignore information that you don't care about.

AWR baseline (AWR baselines)

The AWR baseline has been expanded. You can automatically create baselines for some other features that you use. The Dian Cun Zhang Line is created by default.

2, Plsql part

Result set cache (results set Caching)

This feature can greatly improve the performance of many programs. In some MIS systems or OLAP systems, you need to use many queries such as SELECT COUNT (*). Before, if we wanted to improve the performance of such queries, we might need to use materialized views or query rewriting techniques. In 11g, we just need to add a/*+result_cache*/hint to cache the result set, which can greatly improve query performance. Of course, in this case, we may have another problem: completeness. Because the integrity of the data is guaranteed through consistent reading in Oracle. Obviously, under this new feature, in order to improve performance, the data is read from the result set in the cache without reading the data from the rollback segment. The answer to this question is complete assurance of completeness. Because the result set is cached independently, any other DML statements will not affect the contents of the result set during the query, thus guaranteeing the integrity of the data.

Object Dependency Improvement

Before 11g, if a function or view depended on a table, once the table had a structural change, the function or view would become invalid, whether it involved a function or a view-dependent property. In 11g, this situation is adjusted: if the properties of the table change are independent of the related function or view, the related object state does not change.

An improvement of regular expressions

In 10g, regular expressions are introduced. This feature greatly facilitates the developer. 11g,oracle has again improved this feature. Among them, a function called Regexp_count is added. In addition, other regular expression functions have been improved.

New SQL Syntax =>

When we call a function, we can specify the data for a particular function parameter by =>. In the 11g, this syntax can also appear in the SQL statement. For example, you can write a statement like this:

Select F (x=>6) from dual;

For TCP packets (UTL_TCP, utl_smtp ...) ) supports FGAC (Fine grained Access control) security controls

Added read-only table (read-only table)

Previously, we implemented read-only control of a table through triggers or constraints. 11g does not need this much trouble, you can directly specify the table as a read-only table.

Trigger execution efficiency improved

Internal unit Inline (Intra-unit inlining)

In C, you can use the inline function (inline) or macro implementation to make some small, frequently called functions inline, after compiling, the call to the part of the inline function will be compiled into the function body of the inline function, thus improving the function efficiency. In the 11g plsql, such inline functions can also be implemented.

Set Trigger Order

There may be multiple triggers on a single table. In 11g, you can specify the order in which they are triggered without having to worry about the chaos of the data in order.

Mixed triggers (compound trigger)

This is a new trigger that appears in 11g. She can have you in the same trigger with the Declarations section, the Before Process section, after the each row process part and after process part.

Create an invalid trigger (Disabled Trigger)

11g, developers can be free to create a invalid trigger and compile her when needed.

Using sequences in non-DML statements (sequence)

In previous versions, if you wanted to assign a sequence value to a variable, you would need to implement it by using a statement similar to the following:

Select Seq_x.next_val into v_x from dual;

In 11g, the following statement can be implemented without such a hassle:

V_x: = Seq_x.next_val;


11g, you can set plsql_warning=enable all to send a warning if there is no error in "when others".

The inheritable nature of Plsql

Inheritance can be implemented in Oracle object types through Super (and Java-like) keywords.

Higher compilation speed

Because the external C compiler is not being used, the compilation speed is increased.

Improved Dbms_sql Package

One of the improvements is that Dbms_sql can receive more than 32k of CLOB. You can also support user-defined types and bulk operations.

Added the Continue keyword

You can use the Continue keyword in a plsql loop statement (functionality is the same as the Continue keyword in other advanced languages).

New Plsql data Type--simple_integer

This is an integer data type that is more efficient than Pls_integer.

3. Other parts

Enhanced compression technology

You can compress up to 2/3 of the space.

High speed propulsion technology

Can greatly improve the data read speed of the file system.

Enhanced Data Guard

You can create snapshots of the standby database for testing purposes. Combined with the database replay technology, we can realize the pressure test of the simulation generation system load.

Online Application Upgrades

Hot Patches--installation upgrades or patches do not need to restart the database.

Database Repair Recommendations

The DBA can be guided during error diagnosis and solution implementation.

Logical Object Partitioning

Logical objects can be partitioned, and partitions can be created automatically to facilitate the management of large databases (Very Large Databases VLDBs).

New High-performance LOB Infrastructure

The new PHP driver

Second, Detailed introduction

1, zoning

Partition (partition) has always been a proud Oracle database technology, it is the presence of the partition so that Oracle efficient processing of massive data is possible, in Oracle 11g, partitioning technology in the ease of use and scalability of the enhanced.

1.1. Interval Partitioning

In one of my projects, because of the huge amount of data, the table was designed to be a partition every one hours, and a repetitive and tedious task that the database administrator would have to do every other day was to generate a new 24 partitions each other to store the data for the second day. And in 11g this work can be done by Oracle automatically, based on range and list of interval partitioning partition type debut.

CREATE TABLE Tb_interval


INTERVAL (Numtoyminterval (1, ' month '))

(PARTITION P0 VALUES less THAN (to_date (' 1-1-2010 ', ' dd-mm-yyyy '));

Specifies the interval between which Oracle should automatically create partitions, the example above is 1 months, and then at least one basic partition is created, the example of which is that all data before 2010-1-1 is in the P0 partition, and each month's data is stored in a new partition that is automatically created by Oracle.

1.2. System Partitioning

System partition, in this new type, we do not need to specify any partition keys, the data will enter which partition is entirely determined by the application, in fact, is the decision by SQL, finally, we can in the INSERT statement to specify which partition to insert.

More Wonderful content:

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.