Oracle 11g数据库性能优化实战

需积分: 0 175 浏览量更新于2024-07-26 收藏 31.46MB PDF 举报

"Oracle 11g性能调优" 是一本由Sam Alapati编写的书籍，专注于在Oracle 11g数据库环境中提升系统性能的技巧和策略。这本书包括了15个章节，覆盖了从优化表性能、选择和优化索引到监控系统性能、SQL调优等多个关键领域。在第一章“优化表性能”中，作者讨论了影响数据存储和检索效率的数据库特性。这些特性通常在创建表之前就需要考虑，比如物理存储设置。物理存储的配置对表的性能有着直接影响，例如表空间的分配、段管理方式以及块大小的选择等。有效的物理设计可以提高I/O效率，减少数据访问的延迟。第二章“选择和优化索引”深入介绍了如何根据不同的查询模式来选取最适合的索引类型，包括B树索引、位图索引、函数索引等，并讲解了如何避免索引未被使用的情况，以提高查询速度。第三章“优化实例内存”探讨了Oracle 11g的内存结构，如SGA（系统全局区）和PGA（程序全局区）的配置，以及如何调整内存参数以适应工作负载，确保数据缓冲和查询缓存的有效性。第四章“监控系统性能”讲述了使用Oracle的性能监视工具，如AWR（自动工作区管理报告）和ASH（活动会话历史），来识别性能瓶颈并进行诊断。第五章“最小化系统冲突”涉及了减少锁和闩争用的策略，以降低并发环境中的性能问题。第六章“分析操作系统性能”强调了操作系统层面的性能监控，包括CPU、内存和磁盘I/O的分析，以找出可能影响数据库性能的系统因素。第七章“数据库故障排除”提供了定位和解决性能问题的方法，包括使用Oracle的诊断工具和日志分析。第八章“创建高效的SQL”和第九章“手动调优SQL”分别讲述了编写高性能SQL语句的技巧和通过分析、重构SQL来提升执行效率。第十章“跟踪SQL执行”和第十一章“自动化SQL调优”介绍了如何利用Oracle的SQL Trace和Automatic Workload Repository (AWR)进行SQL性能分析，并利用DBMS_SQLTUNE包自动化调优过程。第十二章“执行计划优化与一致性”关注了执行计划的稳定性和性能影响，包括CBO（成本基优化器）的配置和优化。第十三章“配置优化器”详细阐述了优化器参数的调整，以优化查询选择和执行路径。第十四章“实现查询提示”介绍了如何使用查询提示来引导优化器做出更优的选择。第十五章“并行执行SQL”探讨了并行查询的概念，以及如何利用Oracle的并行执行机制来加速大规模数据处理。本书通过丰富的案例和实践建议，为Oracle数据库管理员和开发者提供了一套全面的性能调优指南，旨在帮助他们提升Oracle 11g数据库系统的整体性能和响应速度。

CHAPTER 1 ■ OPTIMIZING TABLE PERFORMANCE

We realize that to take advantage of this feature the only “solution” is to upgrade to Oracle Database

11g R2, which is oftentimes not an option. However, we felt it was important to discuss this feature

because you’ll eventually encounter the aforementioned characteristics (when you start using the latest

release of Oracle).

You can disable the deferred segment creation feature by setting the database initialization

parameter DEFERRED_SEGMENT_CREATION to FALSE. The default for this parameter is TRUE.

You can also control the deferred segment creation behavior when you create the table. The CREATE

TABLE statement has two new clauses: SEGMENT CREATION IMMEDIATE and SEGMENT CREATION DEFERRED—for

example:

create table f_regs(

reg_id number

,reg_name varchar2(2000))

segment creation immediate;

■ Note The COMPATIBLE initialization parameter needs to be 11.2.0.0.0 or greater before using the SEGMENT

CREATION DEFERRED clause.

1-6. Maximizing Data Loading Speeds

Problem

You’re loading a large amount of data into a table and want to insert new records as quickly as possible.

Solution

Use a combination of the following two features to maximize the speed of insert statements:

• Set the table’s logging attribute to NOLOGGING; this minimizes the generation redo

for direct path operations (this feature has no effect on regular DML operations).

• Use a direct path loading feature, such as the following:

• INSERT /*+ APPEND */ on queries that use a subquery for determining which

records are inserted

• INSERT /*+ APPEND_VALUES */ on queries that use a VALUES clause

• CREATE TABLE…AS SELECT

Here’s an example to illustrate NOLOGGING and direct path loading. First, run the following query to

verify the logging status of a table. In this example, the table name is F_REGS:

CHAPTER 1 ■ OPTIMIZING TABLE PERFORMANCE

select

table_name

,logging

from user_tables

where table_name = 'F_REGS';

Here is some sample output:

TABLE_NAME LOG

------------------------------ ---

F_REGS YES

The prior output verifies that the table was created with LOGGING enabled (the default). To enable

NOLOGGING, use the ALTER TABLE statement as follows:

SQL> alter table f_regs nologging;

Now that NOLOGGING has been enabled, there should be a minimal amount of redo generated for

direct path operations. The following example uses a direct path INSERT statement to load data into the

table:

insert /*+APPEND */ into f_regs

select * from reg_master;

The prior statement is an efficient method for loading data because direct path operations such as

INSERT /*+APPEND */ combined with NOLOGGING generate a minimal amount of redo.

How It Works

Direct path inserts have two performance advantages over regular insert statements:

• If NOLOGGING is specified, then a minimal amount of redo is generated.

• The buffer cache is bypassed and data is loaded directly into the datafiles. This can

significantly improve the loading performance.

The NOLOGGING feature minimizes the generation of redo for direct path operations only. For direct

path inserts, the NOLOGGING option can significantly increase the loading speed. One perception is that

NOLOGGING eliminates redo generation for the table for all DML operations. That isn’t correct. The

NOLOGGING feature never affects redo generation for regular INSERT, UPDATE, MERGE, and DELETE statements.

One downside to reducing redo generation is that you can’t recover the data created via NOLOGGING

in the event a failure occurs after the data is loaded (and before you back up the table). If you can

tolerate some risk of data loss, then use NOLOGGING but back up the table soon after the data is loaded. If

your data is critical, then don’t use NOLOGGING. If your data can be easily re-created, then NOLOGGING is

desirable when you’re trying to improve performance of large data loads.

What happens if you have a media failure after you’ve populated a table in NOLOGGING mode (and

before you’ve made a backup of the table)? After a restore and recovery operation, it will appear that the

table has been restored:

CHAPTER 1 ■ OPTIMIZING TABLE PERFORMANCE

SQL> desc f_regs;

Name Null? Type

----------------------------------------- -------- ----------------------------

REG_ID NUMBER

REG_NAME VARCHAR2(2000)

However, when executing a query that scans every block in the table, an error is thrown.

SQL> select * from f_regs;

This indicates that there is logical corruption in the datafile:

ORA-01578: ORACLE data block corrupted (file # 10, block # 198)

ORA-01110: data file 10: '/ora01/dbfile/O11R2/users201.dbf'

ORA-26040: Data block was loaded using the NOLOGGING option

As the prior output indicates, the data in the table is unrecoverable. Use NOLOGGING only in situations

where the data isn’t critical or in scenarios where you can back up the data soon after it was created.

■ Tip If you’re using RMAN to back up your database, you can report on unrecoverable datafiles via the REPORT

UNRECOVERABLE command.

There are some quirks of NOLOGGING that need some explanation. You can specify logging

characteristics at the database, tablespace, and object levels. If your database has been enabled to force

logging, then this overrides any NOLOGGING specified for a table. If you specify a logging clause at the

tablespace level, it sets the default logging for any CREATE TABLE statements that don’t explicitly use a

logging clause.

You can verify the logging mode of the database as follows:

SQL> select name, log_mode, force_logging from v$database;

The next statement verifies the logging mode of a tablespace:

SQL> select tablespace_name, logging from dba_tablespaces;

And this example verifies the logging mode of a table:

SQL> select owner, table_name, logging from dba_tables where logging = 'NO';

How do you tell whether Oracle logged redo for an operation? One way is to measure the amount of

redo generated for an operation with logging enabled vs. operating in NOLOGGING mode. If you have a

development environment for testing, you can monitor how often the redo logs switch while the

transactions are taking place. Another simple test is to measure how long the operation takes with and

without logging. The operation performed in NOLOGGING mode should occur faster because a minimal

amount of redo is generated during the load.

CHAPTER 1 ■ OPTIMIZING TABLE PERFORMANCE

1-7. Efficiently Removing Table Data

Problem

You’re experiencing performance issues when deleting data from a table. You want to remove data as

efficiently as possible.

Solution

You can use either the TRUNCATE statement or the DELETE statement to remove records from a table.

TRUNCATE is usually more efficient but has some side effects that you must be aware of. For example,

TRUNCATE is a DDL statement. This means Oracle automatically commits the statement (and the current

transaction) after it runs, so there is no way to roll back a TRUNCATE statement. Because a TRUNCATE

statement is DDL, you can’t truncate two separate tables as one transaction.

This example uses a TRUNCATE statement to remove all data from the COMPUTER_SYSTEMS table:

SQL> truncate table computer_systems;

When truncating a table, by default all space is de-allocated for the table except the space defined by

the MINEXTENTS table-storage parameter. If you don’t want the TRUNCATE statement to de-allocate the

currently allocated extents, then use the REUSE STORAGE clause:

SQL> truncate table computer_systems reuse storage;

You can query the DBA/ALL/USER_EXTENTS views to verify if the extents have been de-allocated (or

not)—for example:

select count(*)

from user_extents where segment_name = 'COMPUTER_SYSTEMS';

How It Works

If you need the option of choosing to roll back (instead of committing) when removing data, then you

should use the DELETE statement. However, the DELETE statement has the disadvantage that it generates a

great deal of undo and redo information. Thus for large tables, a TRUNCATE statement is usually the most

efficient way to remove data.

Another characteristic of the TRUNCATE statement is that it sets the high-water mark of a table back to

zero. When you use a DELETE statement to remove data from a table, the high-water mark doesn’t

change. One advantage of using a TRUNCATE statement and resetting the high-water mark is that full table

scan queries search only for rows in blocks below the high-water mark. This can have significant

performance implications for queries that perform full table scans.

CHAPTER 1 ■ OPTIMIZING TABLE PERFORMANCE

Another side effect of the TRUNCATE statement is that you can’t truncate a parent table that has a

primary key defined that is referenced by an enabled foreign-key constraint in a child table—even if the

child table contains zero rows. In this scenario, Oracle will throw this error when attempting to truncate

the parent table:

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Oracle prevents you from truncating the parent table because in a multiuser system, there is a

possibility that another session can populate the child table with rows in between the time you truncate

the child table and the time you subsequently truncate the parent table. In this situation, you must

temporarily disable the child table–referenced foreign-key constraints, issue the TRUNCATE statement,

and then re-enable the constraints.

Compare the TRUNCATE behavior to that of the DELETE statement. Oracle does allow you to use the

DELETE statement to remove rows from a parent table while the constraints are enabled that reference a

child table (assuming there are zero rows in the child table). This is because DELETE generates undo, is

read-consistent, and can be rolled back. Table 1-4 summarizes the differences between DELETE and

TRUNCATE.

If you need to use a DELETE statement, you must issue either a COMMIT or a ROLLBACK to complete the

transaction. Committing a DELETE statement makes the data changes permanent:

SQL> delete from computer_systems;

SQL> commit;

■ Note Other (sometimes not so obvious) ways of committing a transaction include issuing a subsequent DDL

statement (which implicitly commits an active transaction for a session) or normally exiting out of the client tool

(such as SQL*Plus).

If you issue a ROLLBACK statement instead of COMMIT, the table contains data as it was before the

DELETE was issued.

When working with DML statements, you can confirm the details of a transaction by querying from

the V$TRANSACTION view. For example, say that you have just inserted data into a table; before you issue a

COMMIT or ROLLBACK, you can view active transaction information for the currently connected session as

follows:

SQL> insert into computer_systems(cs_id) values(1);

SQL> select xidusn, xidsqn from v$transaction;

XIDUSN XIDSQN

---------- ----------

3 12878

SQL> commit;

SQL> select xidusn, xidsqn from v$transaction;

no rows selected

剩余584页未读，继续阅读

呆瓜呆呆

粉丝: 34
资源: 81

Oracle 11g数据库性能优化实战

Oracle11g 自动维护任务配置与调优

oracle11g 性能优化

Oracle 系统 调优

oracle 11g 性能调优 官方PPT

Oracle 11g 性能调优（OCP版）超经典实战手册

Oracle 11g性能调优实战指南：快速解决数据库问题

Oracle 11g性能调优指南（E10822-04）PDF文档

oracle9i oracle11g oracle10g 性能调优 基础学习 视频地址

oracle 10g 性能调优 官方PPT

Oracle数据库11g性能调优基础

最新资源

Oracle 系统调优

oracle 11g 性能调优官方PPT

oracle9i oracle11g oracle10g 性能调优基础学习视频地址

oracle 10g 性能调优官方PPT