Oracle10g优化

Oracle10g性能调整与优化

主要内容：收集性能数据、优化SQL语句和应用程序、调整共享池(Shared Pool)的性能、调整缓冲区高速缓存(Buffer Cache)的性能、调整重做有关的性能、共享(多线程)服务器（MTS）、调整磁盘I/O的性能、调整闩(latch)和锁定(lock)、调整操作系统。

第一章收集性能数据

1. 报警(Alert)日志文件

报警(Alert)日志文件在c:\oracle\product\10.2.0\admin\erp\bdump目录下：文件名为alert_erp.log。

注意报警(Alert)日志文件中的错误信息：

ORA-01652：在临时表空间中没有足够的空闲空间来进行排序操作。

ORA-01653：在表空间中没有足够的空闲空间来存放表。

ORA-01650：在回退段所在的表空间中没有足够的空闲空间来使回退段增长。

ORA-01631：表所占用的空间超过允许的最大值。

Checkpoint Not Complete。

Snapshot too old。

后台进程跟踪文件：也在c:\oracle\product\10.2.0\admin\erp\bdump目录下。文件名类似于erp_lgwr_2548.trc、erp_arc0_2620.trc、erp_dbw0_3012.trc。

用户跟踪文件：

udump目录下：erp_ora_788.trc文件。其中788为该会话所对应的Server Process的编号，可以通过V$process查得。

设置用户跟踪

实例级跟踪：init.ora参数SQL_TRACE=TRUE。这种方法会产生大量的系统开销。

用户级跟踪：

SQl>alter session set sql_trace=true;

SQl>alter session set sql_trace=false;

DBA跟踪：

SQL>exec sys.dbms_system.set_sql_trace_in_session(10,87,true);

SQL>exec sys.dbms_system.set_sql_trace_in_session(10,87,false);

10为会话编号，87为会话序列号。

限制用户跟踪文件的大小：init.ora参数MAX_DUMP_FILE_SIZE。

2. 性能优化视图

v$sysstat：数据库启动以来的统计数据。

v$system_event：系统中所有会话发生过的等待事件。

v$sesstat：所有当前会话的统计数据。

v$session: 所有当前的会话。

V$session_event：已经发生过的等待事件。

V$session_wait：正在发生的等待事件。

V$sgastat：SGA内存的统计数据。

V$waitstat：对自由列表的争用。

3. 收集性能数据的程序：STATSPACK。

STATSPACK工具的使用

创建一个单独的表空间存放性能数据。大小300M左右。

注意：STATSPACK收集的是默认数据库的数据。

用管理员SYS用户登录。

SQL> @ c:\oracle\product\10.2.0\db_1\rdbms\admin\spcreate.sql

用PERFSTAT用户登录。

SQL>execute STATSPACK.SNAP;

至少要有两个快照。

用PERFSTAT用户登录，生成报告文件：

SQL> @ c:\oracle\product\10.2.0\db_1\rdbms\admin\spreport.sql

4. 图形性能工具（WEB方式）

第二章优化SQL语句和应用程序

1. 测量SQL语句的性能

Tkprof(Trace Kernel Profile)工具的使用

功能：测量SQL语句的性能。

c:\> tkprof

c:\oracle\product\10.2.0\admin\ERP\udump\erp_ora_1436.trc c:\bao.txt sys=no sys=no的含义：不包含递归SQL语句(即访问数据字典的隐含语句)

SQL语句的处理要经过三个阶段：Parse、Execute、Fetch。

需要优化的SQL语句：

占用过多的CPU时间。

Parse、Execute、Fetch阶段的时间太长。

从磁盘读太多的数据块，而从内存中读很少的数据块。

访问许多数据块，但只返回几条数据。

Top SQL 的使用(WEB方式)

Top SQL用来代替Tkprof。

Top SQL可以找出哪些SQL语句的性能差，需要优化。

Top SQL 中的数据来源于V$SQL。

Top Sessions的使用(WEB方式)

Top Sessions可以找出哪些会话占用较多的资源。

2. SQL语句的解释计划(EXPLAIN PLAN)

通过解释计划，可以找出SQL语句性能低的原因。

用SQL ScratchPad来生成SQL语句的解释计划：

用命令来生成SQL语句的解释计划：

先检查sys用户下是否有plan_table表(9i中已经有了这个表)，如果没有，执行C:\oracle\product\10.2.0\db_1\rdbms\admin\utlxplan.sql脚本。

SQL>explain plan for SELECT e.empno, e.ename, d.deptno, d.loc

FROM scott.emp e, scott.dept d WHERE e.deptno = d.deptno;

注意要commit。

查询执行计划：

SQL>select lpad(' ',4*(level-2)) || operation || ' ' || options || ' ' || object_name "EXECUTION_PLAN" from plan_table start with id =0 connect by prior id = parent_id;

3. STATSPACK报告中的SQL语句性能

SQL ordered by Gets(按Gets排序的SQL语句)

SQL ordered by Reads(按Reads排序的SQL语句)

SQL ordered by Executions(按Executions排序的SQL语句)

SQL ordered by Parse Calls(按Parse Calls排序的SQL语句

4. Oracle优化方式

优化方式：基于rule和cost.

基于rule时的优化等级：根据语法和表结构优化

1 Single row by rowid

2 Single row by cluster join

3 Single row by hash cluster key with unique or primary key

4 Single row by unique or primary key

5 Cluster join

6 Hash cluster key

7 Indexed cluster key

8 Composite key

9 Single-column indexes

10 Bounded range search on indexed columns

11 Unbounded range search on indexed columns

12 Sort-merge join

13 MAX or MIN of indexed column

14 ORDER BY on indexed columns

15 Full table scan