Oracle 11g管理员新功能概览：关键特性与实用技巧

需积分: 9 49 浏览量更新于2024-07-22 收藏 736KB PDF 举报

Oracle 11g新特性概述 Oracle 11g是Oracle数据库的第11代重要版本，它在许多方面增强了性能、安全性和管理效率。针对数据库管理员，Oracle 11g引入了一系列显著的新特性和改进，使得系统管理和维护更为便捷。以下是一些关键的新功能和变化： 1. **增强的安全性**： - Oracle 11g引入了新的身份验证方法，如Oracle Advanced Security Option (ASO)，加强了用户访问控制，提高了安全性。 - 数据库透明数据加密(TDE)也被提升到核心，确保敏感数据在传输和存储时得到加密。 2. **性能优化**： - 新的内存管理技术，如Advanced Queuing (AQ)和Automatic Workload Repository (AWR)改进，帮助管理员更有效地利用内存资源，提高查询性能。 - 实体化视图(Oracle Materialized Views)的更新，允许更快的数据刷新和查询响应。 3. **易用性与管理工具**： - SQL Developer等工具得到了升级，提供直观的图形界面和自动化任务，简化了日常运维工作。 - Database Configuration Assistant (DBCA) 和 Enterprise Manager Console 的改进，使得系统配置和监控更加直观和高效。 4. **网格计算和云集成**： - Oracle WebLogic Server 11g与Oracle Fusion Middleware的集成加强，支持云计算环境下的应用部署和管理。 - 自动化任务调度和资源池管理，如Oracle Scheduler和Oracle Resource Manager，有助于更好地协调和优化资源分配。 5. **SQL语言增强**： - 新的PL/SQL特性，如递归子查询和异常处理改进，提高了编程灵活性和代码可读性。 - SQL窗口函数和分析表达式也扩展了数据分析和报告能力。 6. **诊断和故障恢复**： - 更强大的诊断工具，如Automatic Database Diagnostic Monitor (ADDM)和Automatic Workload Repository (AWR)，有助于快速定位问题并优化性能。 - 数据库恢复选项的优化，如闪回数据集和闪回事务，降低了数据丢失的风险。 7. **开放标准和兼容性**： - Oracle 11g与Open Standards的整合加深，支持XML、JSON等标准数据格式，以及与Java和Web Services的良好集成。 8. **版本更新和维护**： - 随着文档版本1.1和1.15的发布，Oracle 11g新特性不断迭代和完善，包括修复错误、添加新功能，并持续回应用户反馈进行调整。这些新特性使得Oracle 11g成为企业级数据库的基石，适应了日益增长的业务需求和技术发展趋势。然而，使用这些特性时必须注意版权条款，遵循非商业用途且未经编辑者书面许可的规定，并理解使用过程中可能存在的潜在风险。通过合理利用Oracle 11g的功能，数据库管理员可以提升组织的IT基础设施效能和竞争力。

Page 16

Oracle 11g New Features for Administrators - Summary Sheets

Replaying a Database Workload Using Enterprise Manager

To replay a database workload using Enterprise Manager, follow the links: Software and Support page> Real

Application Testing> Database Replay> in the Go to Task column, click the Replay Workload task

icon.

Using OEM, you can also monitor an active workload replay and view a completed workload replay.

The SQL Performance Analyzer

The SQL Performance Analyzer (SPA) aims at measuring the impact of applying any change on the database on

the performance of the SQL statements execution. If it finds out performance degradation in one or more SQL

statements, it provides you recommendations on how to improve their performance.

This is very useful for a DBA to analyze how a change on the database (including database upgrade) may affect

the execution efficiency of SQL statements. Using this tool is explained here because you may consider using it

to study the effect of upgrading an Oracle database 10g release 2 to 11g.

SPA can be invoked from OEM or using DBMS_SQLPA package.

To use SPA, you perform the following phases:

1. Capture the SQL workload.

2. Measure the performance of the workload before the change.

3. Make a change.

4. Measure the performance of the workload after the change.

5. Compare performance.

Steps that should be followed to perform those phases are explained in the following sections.

It is recommended to make an identical copy of the production database, apply the change on the test database

then use SPA on the test database to measure the influence of the applied changes.

Note

If you plan to use SPA on a test database, it is highly recommended to make the test database

resemble the production database as closely as possible. You can use the RMAN duplicate command

for this purpose.

Capturing the Production System SQL Workload

You capture the SQL Workload that you intend to analyze and store its statements into a SQL tuning set (STS).

Following are the steps to do so:

1. Create STS using the following code:

begin

DBMS_SQLTUNE.CREATE_SQLSET(

SQLSET_NAME => 'upgrade_sts',

DESCRIPTION =>'To test upgrade to 11g',

SQLSET_OWNER =>'SYS'); -- current user is the default

end;

2. You can load SQL statements into a STS from different sources, including the cursor cache, Automatic

Workload Repository (AWR), and existing SQL Tuning Sets. STS contains information about the captured

statements such as execution context, SQL text and execution frequency.

The following example illustrates how to load UPGRADE_STS from an AWR baseline called peak baseline. The

data has been filtered to select only the top 30 SQL statements ordered by elapsed time.

declare

baseline_cur DBMS_SQLTUNE.SQLSET_CURSOR;

begin

-- a ref cursor is opened to select from the specified baseline

OPEN baseline_cur FOR

SELECT VALUE(p)

FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(

'peak baseline',NULL, NULL, 'elapsed_time', NULL, NULL, NULL,30 )) p;

-- Next the statements and their statistics are loaded into the STS

DBMS_SQLTUNE.LOAD_SQLSET( SQLSET_NAME=>'UPGRADE_STS', POPULATE_CURSOR=>baseline_cur);

end;

Page 17

Oracle 11g New Features for Administrators - Summary Sheets

The following example loads UPGRADE_STS with SQL statements that are not owned by SYS and their elapsed

time is greater than 20,000 seconds.

declare

sql_cur DBMS_SQLTUNE.SQLSET_CURSOR;

begin

-- a ref cursor is opened to select the required SQL statments

OPEN sql_cur FOR

SELECT VALUE(p)

FROM TABLE (DBMS_SQLTUNE.LOAD_SQLSET(

'parsing_schema_name <> ''SYS'' and elapsed_time > 2000000',NULL, NULL, NULL, NULL,1,

NULL, 'ALL')) p;

-- the statements are loaded into the STS

DBMS_SQLTUNE.LOAD_SQLSET( SQLSET_NAME=>'UPGRADE_STS', POPULATE_CURSOR=>sql_cur);

end;

3. If you are using a test database, you should transport the STS created in the previous step to the test

system. This can be done by performing the following steps:

3.1. Create a staging table to hold the STS from the production database. Following is an example code

to do that:

exec DBMS_SQLTUNE.CREATE_STGTAB_SQLSET( TABLE_NAME =>'sts_table');

3.2. Export STS data into the staging table as shown in the following code:

begin

DBMS_SQLTUNE.PACK_STGTAB_SQLSET( SQLSET_NAME => 'UPGRADE_STS',

STAGING_TABLE_NAME =>'sts_table');

end;

3.3. Export the staging table from the production database and import it to the test database using Data

Pump export and import utilities.

3.4. Import the STS data from the staging table to the test database as shown in the example below:

begin

DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET( SQLSET_NAME => '%',

REPLACE => TRUE,

STAGING_TABLE_NAME =>'sts_table');

end;

4. Create a tuning task for the created STS as show in the example code below:

declare

v_task varchar2(100);

begin

v_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( SQLSET_NAME => 'UPGRADE_STS',

TASK_NAME =>'spa_upgrade_task');

end;

Note

We are using SPA in this scenario to analyze SQL involved in a STS. Actually, you can also use

it to

analyze a given single SQL statement,

a statement in the cursor cache or SQL statements in a

workload repository in a given a range of snapshot identifiers

5. If you are using SPA to examine the influence of upgrading a 10g database to 11g, set the parameter

OPTIMIZER_FEATURES_ENABLE in the test database to 10.2.0. This enables you to make SPA generate

statistics for 10g database.

ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE ='10.2.0'

Page 18

Oracle 11g New Features for Administrators - Summary Sheets

Measuring the Performance Before the Change

Start analyzing the SQL workload before applying the change. This is done by using the procedure

EXECUTE_ANALYSIS_TASK. This procedure has the parameter EXECUTION_TYPE which should take one of three

possible values: TEST EXECUTE, EXPLAIN PLAN or COMPARE PERFORMANCE. The first value will lead to execute all

the SQL Statements in the STS. The second value will generate explain plans for the SQL Statements without

executing the statements. The third value is only used to compare two versions of SQL Performance data.

begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK (

TASK_NAME => 'spa_upgrade_task',

EXECUTION_TYPE=> 'TEST EXECUTE', -- or EXPLAIN PLAN

EXECUTION_NAME =>'before_change');

end;

After the code above is successfully executed, apply the change on the database.

Measuring the Performance After the Change

If the change was upgrading the database to 11g, do not forget to set the parameter

OPTIMIZER_FEATURES_ENABLE to the 11g value after the upgrade finishes:

ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE ='11.1.0.6'

After applying the change, you collect SQL performance data by running EXECUTE_ANALYSIS_TASK again.

begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK (

TASK_NAME => 'spa_upgrade_task',

EXECUTION_TYPE=> 'TEST EXECUTE', -- or EXPLAIN PLAN

EXECUTION_NAME =>'after_change');

end;

Comparing the SQL Performance

To compare the SQL performance data before and after the change, you still use the procedure

EXECUTE_ANALYSIS_TASK but this time you set the parameter EXECUTION_TYPE to COMPARE PERFORMANCE.

The following code compares the SQL performance data analyzed by the SPA before and after the change for the

task spa_upgrade_task. By setting the parameter COMPARISION_METRIC to DISK_READS, it will measure the

performance impact of the change on the disk reads. Other possible values include: ELAPSED_TIME,

OPTIMIZER_COST, DIRECT_WRITE, PARSE_TIME, and BUFFER_GETS.

begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK (

TASK_NAME => 'spa_upgrade_task',

EXECUTION_TYPE => 'COMPARE PERFORMANCE',

EXECUTION_PARAMS => 'DBMS_ADVISOR.ARGLIST('COMPARISION_METRIC','DISK_READS'));

end;

By default, the procedure EXECUTE_ANALYSIS_TASK compares the last two task executions. You can set the

names of the executions before and after the system change was made as follows:

begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK (

TASK_NAME => 'spa_upgrade_task',

EXECUTION_TYPE => 'COMPARE PERFORMANCE',

EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST('EXECUTION_NAME1', 'before_change',

'EXECUTION_NAME2', 'after_change',

'COMPARISON_METRIC','DISK_READS'));

end;

Page 19

Oracle 11g New Features for Administrators - Summary Sheets

Then you can generate a report to show the result of performance comparison. This can be done using

REPORT_ANALYSIS_TASK function as shown in the code below:

declare

report clob;

begin

report := DBMS_SQLPA.REPORT_ANALYSIS_TASK (

TASK_NAME => 'spa_upgrade_task',

TYPE => 'TEXT', -- or HTML, XML

LEVEL =>'TYPICAL', -- BASIC,ALL,IMPROVED,REGRESSED,CHANGED,

-- UNCHANGED,CHANGED_PLANS,UNCHANGED_PLANS,ERRORS

SECTION =>'SUMMARY'); -- or SECTION_ALL

end;

SQL Performance Analyzer Report

The report is divided into the sections: General Information, Result Summary and Result Details. Following table

illustrates description of the report's section and subsections.

Section/ Subsection Description

General Information

It contains basic information and metadata about the SQL Performance Analyzer

task, the SQL Tuning Set used, and the pre-change and post-change executions.

Result Summary

It summarizes the results of the SQL Performance Analyzer task.

Overall Performance

Statistics

It displays statistics about the overall performance of the entire SQL workload.

Use this subsection to determine whether the workload performance will improve

or degrade after making the system change.

Performance

Statistics of SQL

Statements

It highlights the SQL statements that are the most impacted by the system

change.

Errors

It reports all errors that occurred during an execution.

Result Details

It represents a drill-down into the performance of SQL statements that appears

in the Result Summary section of the report.

SQL Statement

Details

It summarizes the SQL statement, listing its information and execution details.

Single Execution

Statistics

It compares execution statistics of the SQL statement from the pre-change and

post-change executions and then summarizes the findings.

Execution Plans

It displays the pre-change and post-change execution plans for the SQL

statement.

SQL Performance Analyzer Views

You can use the following queries to monitor SQL Performance Analyzer and view its analysis results:

-- to display descriptive information about the created SQL Performance Analyzer tasks

SELECT TASK_ID, TASK_NAME, STATUS, PCT_COMPLETION_TIME, PROGRESS_METRIC,

RECOMMENDATION_COUNT

FROM DBA_ADVISOR_TASKS

WHERE ADVISOR_NAME='SQL Performance Analyzer';

-- to display information about task executions

SELECT TASK_NAME, EXECUTION_NAME, EXECUTION_TYPE, STATUS

FROM DBA_ADVISOR_EXECUTIONS

WHERE ADVISOR_NAME='SQL Performance Analyzer';

-- to display the SQL Performance Analyzer findings

-- TYPE possible values: PROBLEM, SYMPTOM, ERROR, and INFORMATION

SELECT TASK_NAME, EXECUTION_NAME, FINDING_NAME, TYPE,

IMPACT_TYPE, IMPACT "Impact Value", MESSAGE, MORE_INFO

FROM DBA_ADVISOR_FINDINGS

WHERE upper(TASK_NAME)= upper ('spa_test');

剩余111页未读，继续阅读

枫Ss

粉丝: 0
资源: 6

Oracle 11g管理员新功能概览：关键特性与实用技巧

ORACLE11G新特性

Oracle11g新特性

oracle11g 新特性

oracle 11g 新特性

oracle11g新特性

oracle11g新特性导读

ORACLE11g新特性及实施

Oracle 11g 新特性.doc

Oracle11g新特性交流.pptx

wxh oracle 11g 新特性 .rar

最新资源