New Features in Performance Tuning Guide 11.2G
New Features in Performance Tuning Guide 11G
This chapter describes which new performance tuning features have been added to Oracle11g Release2 (11.2) and points to these additional information.
The features and enhancements described in this chapter include all aspects of optimizing database performance.
For a summary of all the new features of Oracle11gR2 , you can check the Oracle Database New Features Guide.
New features added in 11.2.0.2 (about performance tuning)
New and updated performance tuning features include:
Note: Resource Manager (resource manager, collectively referred to as the abbreviation RM in the text )
- Resource Manager on the enhancement of parallel statement queue
You can use RM to control the order of statements in a parallel statement queue. For example, you can ensure that high-priority statements spend less time in the queue (higher priority is executed first). Similarly, you can use an instruction to prevent a consumer group ( terms in RM , don’t understand yourself to go to baidu ) to occupy all parallel resources, and you can specify the maximum waiting time for parallel statements in the queue (which will start execution later).
For more information, please read “ Managing CPU Resources Using Oracle Database Resource Manager” and Oracle Database VLDB and Partitioning Guide.
2 Resource Manager Enhancement of CPU utilization restrictions
You can use the RM to each consumer group conducted CPU consumption limit, this feature limits the low priority session of CPU consumption, can help coordinate the effort (pressure equalization).
For more information, please read “ Managing CPU Resources Using Oracle Database Resource Manager”.
3 New package for automatic SQL adjustment
The DBMS_AUTO_SQLTUNE package is a new interface for managing automatic SQL tuning. And SQL tuning advice package DBMS_SQLTUNE package different,
DBMS_AUTO_SQLTUNE requires the DBA role, while DBMS_SQLTUNE only requires ADVISOR permissions.
For more information, please read “ Configuring Automatic SQL Tuning” ( 17.2.3 in Chapter 17 of this manual ).
4 Oracle Orion I/O test tool documentation
Oracle Orion tool can predict the performance of an Oracle database without installing Oracle or creating a database (sounds very high). Unlike other I/O evaluation tools, Oracle Orion is a tool that specifically simulates the I/O load of the Oracle database . It uses the same I/O software stack as Oracle software . Orion can also simulate the performance of ASM striping.
For more information, read “ the I / O Calibration with the Oracle at The Orion Calibration Tool.” (This manual 8 Section 8.4 )
New features added in 11.2.0.1 (about performance tuning)
New and updated performance tuning features include:
5 New AWR (Automatic Workload Repository) view
AWR supports some new historical transactions, including DBA_HIST_DB_CACHE_ADVICE and DBA_HIST_IOSTAT_DETAIL
For more information, read “ the Using Automatic Workload Repository Views” (this manual 5 Chapter 5.3.5)
5.1New AWR report
New AWR report and AWR cycle comparison report are added in Oracle RAC .
For more information, please read “ Generating Automatic Workload Repository Reports” ( 5.3.6) and “ Generating Automatic Workload Repository Compare Periods Reports” (5.3.7).
6 Client note cache supports table annotation information
Client result cache supports table annotation information
For more information, please read “ Using Result Cache Table Annotations”. ( 7.6.3.3 )
7 Enhanced PL/SQL comments in RESULT_CACHE
In 11.1 , the RELIES_ON clause is required to query the reference comment table in the PL/SQL function . This clause is no longer needed.
8 Statement level using hint to specify the degree of parallelism
Parallel degree can be specified in parallel hint .
For more information, please read “ Hints for Parallel Execution” (19.1.2.7)
9 Parallel execution in memory
When using parallel queries, you can configure the database to replace the previous direct path read ( PGA ) by using buffer cache .
When a database server has a large amount of memory, this setting may be appropriate. Similarly, a RAC database can aggregate the memory of all nodes, which can buffer larger objects and more queries.
For more information, please read “Using the Buffer Cache Effectively” ( 7.2.1 ) .
10 hint in online application upgrade
The online application upgrade hints suggested how to deal with conflicting INSERT and UPDATE operations when upgrading version-based online applications .
For more information, please read “ Hints for Online Application Upgrade” ( 19.1.2.6 )
11 SQL Tuning Advisor enhancements
The enhancements to SQL Tuning Advisor in this version include the following:
11.1 When adjusting a SQL statement, SQL Tuning Advisor will retrieve the actual execution time and historical execution data for an alternative execution plan of a statement. If an execution plan different from the original execution plan exists, then SQL Tuning Advisor will report that the replacement execution plan was found. See “Alternative Plan Analysis” ( 17.1.5).
11.2 You can transfer a SQL tuning set to any database ( 10Gr2 or later). This technique can be used by SQL performance analysts to adjust and roll back in the test database. See “Transporting a SQL Tuning Set” (17.4.5).
11.3 Sometimes, SQL Tuning Advisor may recommend to accept a profile , and this profile uses the automatic parallelism feature ( Auto DOP ). A parallel query profile is only recommended if the original execution plan is serial, and the execution time can be significantly reduced after parallel execution. See “SQL Profile Recommendations” ( 17.5.1.1 ) .
12 SQL plan baselines replace stored outlines
The Oracle database allows you to safely migrate from stored outlines to SQL plan baselines . After the migration, you can also stabilize the execution plan, and you can use more other advanced features provided by the SQL Plan Management framework. For more information, please read “ Migrating Stored Outlines to SQL Plan Baselines” (15.8).
Thanks for reading this post, you can follow me up to get latest update on this.
Thanks
Pankaj K.
