Shulou(Shulou.com)06/01 Report--

[Enmo College] take stock of the 10 performance impacts brought about by the new features in Oracle 11g (part two). Do you remember the top 10 performance impacts of Oracle 11g that we checked a few days ago?

Any new version of Oracle will always bring a large number of eye-catching new features, but often at the beginning of the introduction of these new features, the first cause some trouble, because of the lack of understanding of heart technology, because of the inadaptability to the old environment, from Oracle products to technical service operation and maintenance, it always has to go through a long-term process of running-in.

So this time, we will continue to share with you the new troubles brought about by those new features, which can be used as a reference for those users who are ready or have just stepped into this new version.

6. _ optimizer_use_feedback-cardinality feedback of optimizer

Cardinality Feedback-cardinality feedback is a new feature introduced in Oracle 11.2.This new feature uses the information collection during the execution of SQL to dynamically adjust the execution plan to solve the problems of outdated statistical information, no histogram or inaccurate calculation based on histogram.

Oracle hopes to improve the accuracy of the execution plan as a result, but in some cases, we may encounter situations where SQL performs best for the first time and then deteriorates after running it.

The initialization parameter _ optimizer_use_feedback controls whether this feature is enabled, which is turned off by setting it to False:

Alter system set "_ optimizer_use_feedback" = false

7. Deferred_segment_creation-create delay segment

In Oracle 11.2, when we create an empty table or an empty partition, to speed up the creation, Oracle does not immediately allocate the initial segment and space, and the actual table segment Table Segement is delayed until the first row of data is inserted.

This feature is enabled through the DEFERRED_SEGMENT_CREATION parameter, which defaults to TRUE. The creation of delayed segment can save space and speed up the initialization process, which is an optimization for performance and resources.

One problem with this new feature is that these empty tables are not included when using exp / imp for export and import, which may result in missing objects.

If you think this feature is bothering you, you can turn it off by changing the parameter:

Alter system set deferred_segment_creation=flase sscope=spfile

8. _ resource_manager_always_on-Resource Manager

In 11g, Oracle's resource manager is enabled by default, often plays a role, and may lead to competition.

You may see something similar in the TOP 5 event:

There are two parameters that can be set together to completely turn off this implicit control when you don't need the explorer:

SQL > alter system set "_ resource_manager_always_off" = true scope=spfile

SQL > alter system set "_ resource_manager_always_on" = false scope=spfile

9. _ gc_policy_time-DRM management in RAC cluster

DRM is short for Dynamic Resource Management, which means dynamic resource management. In Oracle RAC, all Data block are managed by an instance as the primary instance, called Master,Master, which is responsible for taking care of the state of the data block under its jurisdiction, including locking, and authorizing cross-instance access.

If the master node of the data block can be dynamically modified frequently with the access of the data block, then the grant message of the corresponding GC will be greatly reduced. Based on the above considerations, the DRM feature came into being. But early DRM has a short-term performance impact in the process of re-master, which is intolerable in many important environments.

If you want to turn off the DRM feature, you can combine setting _ gc_policy_time with _ gc_undo_affinity:

Alter system set "_ gc_policy_time" = 0 scope=spfile

Alter system set "_ gc_undo_affinity" = false scope=spfile

10. Cleaning and adjustment of _ cleanup_rollback_entries, _ undo_autotune-UNDO

In the management of UNDO, how to set the retention time, clean up the rollback segment entries and free up UNDO space is very important in the database with high transaction rate.

_ cleanup_rollback_entries-specify the number of ENTRIES per rollback during rollback. Default is 100. you can set a higher rollback speed.

_ undo_autotune-used to automatically adjust the undo retention time. Set _ undo_autotune=true, then undo_retention is no longer applicable and Oracle will decide the tuned_undo_retention on its own.

The following settings adjust these properties as needed:

Alter system set "_ undo_autotune" = false scope=spfile

Alter system set "_ cleanup_rollback_entries" = 1000 scope=spfile

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.