Oracle笔记（十二）：性能优化篇

比眉伴天荒 2022-04-23 06:00 263阅读 0赞

**目录**

一、概述

二、修改系统全局区

三、修改进程全局区

四、优化查询

分析执行计划

使用索引

优化子查询

五、优化数据库结构

拆分表

增加中间

优化插入记录速度---禁用索引

禁用唯一性检查

使用批量插入

六、优化服务器

优化Oracle参数

优化硬件

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

## 一、概述 ##

Oracle优化，一方面找出系统的瓶颈，提高整体性能；另一方面需要合理的结构设计和参数调整，提高操作相应速度；同时尽可能节省系统资源。

Oracle优化可以是多方面的，例如：

*  优化文件系统，提高 I / O的读写速度；
 *  优化操作系统调度策略，提高Oracle在高负荷负载能力；
 *  优化表结构、索引、查询语句，加快相应

## 二、修改系统全局区 ##

Oracle中的内存主要分为2部分，系统全局区和进程全局区。

系统全局区，简称SGA（System Global Area）是共享的内存机构，亦称为共享全局区。

SGA主要包括：

*  共享池
 *  Java池
 *  大型池
 *  缓冲区
 *  日志缓冲区

**查看当前数据库的SGA状态 **

SQL> SHOW PARAMETER SGA;
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    lock_sga                             boolean     FALSE
    pre_page_sga                         boolean     FALSE
    sga_max_size                         big integer 1536M
    sga_target                           big integer 0

** 注意2个内容：**

*  sga\_max\_size        SGA分配的最大内存   
 *  sga\_target               数据库可管理的最大内存（0表示关闭了共享内存区）

**视图：V$SGASTAT 查看具体SGA分配情况（Oracle管理员）**

SQL> select * from v$sgastat;
    
    POOL         NAME                            BYTES
    ------------ -------------------------- ----------
                 fixed_sga                     2176168
                 buffer_cache                520093696
                 log_buffer                    7397376
    shared pool  kgqbt_alloc_block                3072
    shared pool  kkj jobq  wor                    4128
    shared pool  dpslut_kfdsg                      512
    shared pool  hot latch diagnostics             160
    shared pool  vips_package_file                1320
    shared pool  ENQUEUE STATS                   20952
    shared pool  vem_user_actlog                   664
    shared pool  transaction                    681560
    
    POOL         NAME                            BYTES
    ------------ -------------------------- ----------
    shared pool  Wait History Array                  8
    shared pool  vproblem_bucket                   848
    shared pool  vnot_exist_incident              3200
    shared pool  KCB buffer wait statistic        3352
    shared pool  invalid low rba queue            2048
    shared pool  KQF optimizer stats table        1832
    shared pool  KCB tablespace encryption         952
    shared pool  bt_qentry                       28512
    shared pool  ksunfy: system-global sta        4840
    shared pool  vips_file_metadata                944
    shared pool  vips_file_copy_log               1696
    
    ...内容太多了...

**下面是修改SGA内存大小**

ALTER SYSTEM SET sga_max_size=2048M scope=spfile;

scope=spfile 表示设置作用到数据库启动文件中，一经重启，该参数立即重启。

**再修改参数sga\_target **

ALTER SYSTEM SET sga_target = 2048M scope=spfile;

最后重启数据库。

## 三、修改进程全局区 ##

进程全局区简称PGA。

**查看PGA状态**

**SQL> SHOW PARAMETER PGA;**

**NAME                                 TYPE        VALUE  
\------------------------------------ ----------- ------------------------------  
pga\_aggregate\_target                 big integer 0**

**修改PGA大小**

pga\_aggregate\_target  大于0时，系统自动管理PGA。

**SQL> ALTER SYSTEM SET pga\_aggregate\_target = 500M scope=both;**

**系统已更改。**

## 四、优化查询 ##

### 分析执行计划 ###

**首先从cmd 登陆SQLPlus**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FkaWRhczc0ODkxNDk2_size_16_color_FFFFFF_t_70][]

**连接上数据库后，设置AUTOTRACE查看执行计划**

**SQL> SET AUTOTRACE ON;  
SQL> SELECT \* FROM FRUITS;**

-- 表信息省略了
    已选择16行。
    
    
    执行计划
    ----------------------------------------------------------
    Plan hash value: 1063410116
    
    ----------------------------------------------------------------------------
    | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |        |    16 |   288 |     3   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| FRUITS |    16 |   288 |     3   (0)| 00:00:01 |
    ----------------------------------------------------------------------------
    
    
    统计信息
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              9  consistent gets
              0  physical reads
              0  redo size
           1278  bytes sent via SQL*Net to client
            530  bytes received via SQL*Net from client
              3  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
             16  rows processed

查询结果中

*  ID表示序号，但不是执行的先后顺序（根据缩进判断先后）
 *  Operation表示当前操作内容

### 使用索引 ###

俩方法：

CREATE INDEX index_name 
        ON tb_name(col_name);

ALTER TBALE tb_name 
      ADD INDEX index_name(col_name);

### 优化子查询 ###

用Join连接代替子查询：

子查询虽然灵活，但影响查询效率。内层查询出结果后放入临时表，查询完毕后才撤销。

连接查询不需要建立临时表，其速度比子查询更快，若查询中使用索引，效果会更好。Oracle不需要在内存中增加开销来完成查询工作。

## 五、优化数据库结构 ##

### 拆分表 ###

把不常用的字段分解出来，添加新表，查询时候 Join 联合查询。

假设会员表存储会员登录认证信息，该表中有很多字段，如id、姓名、密码、地址、电话、个人描述字段。其中地址、电话、个人描述等字段并不常用。可以将这些不常用字段分解出另外一个表。将这个表取名叫 members\_detail。表中有member\_id、address、telephone、description 等字段。其中，member\_id是会员编号，address字段存储地址信息，telephone字段存储电话信息，description字段存储会员个人描述信息。这样就把会员表分成两个表，分别为members表和members\_detail表。

CREATE TABLE members (
      Id  number(11) NOT NULL,
      username varchar2(255) DEFAULT NULL ,
      password varchar2(255) DEFAULT NULL ,
      last_login_time date DEFAULT NULL ,
      last_login_ip varchar2(255) DEFAULT NULL ,
      PRIMARY KEY (id)
    ) ;
    CREATE TABLE members_detail (
      member_id number (11) DEFAULT 0,
      address varchar2(255) DEFAULT NULL ,
      telephone varchar2(16) DEFAULT NULL ,
      description  varchar2(255)
    ) ;
    
    --表结构如下：
    
    SQL> desc members;
    名称              空值         类型            
    ---------------        --------        ------------- 
    ID              NOT NULL     NUMBER(11)    
    USERNAME                   VARCHAR2(255) 
    PASSWORD                   VARCHAR2(255) 
    LAST_LOGIN_TIME            DATE          
    LAST_LOGIN_IP              VARCHAR2(255)
    
    
    SQL> DESC members_detail;
    名称          空值 类型            
    -----------      --    ------------- 
    MEMBER_ID      NUMBER(11)    
    ADDRESS        VARCHAR2(255) 
    TELEPHONE      VARCHAR2(16)  
    DESCRIPTION    VARCHAR2(255)

如果需要查询会员的详细信息，可以用会员的id来查询。

如果需要将会员的基本信息和详细信息同时显示，可以将 members 表和 members\_detail 表进行联合查询，

SELECT * FROM members mbs
      LEFT JOIN members_detail mdt
        ON mbs.id=mdt.member_id;

### 增加中间表 ###

对于需要经常联合查询的表，可建立中间表提高查询效率。通过建立中间表，把需要经常联合查询的数据插入中间表中，而后将原来的联合查询改为对中间表的查询，因此提高效率。

**会员信息表和会员组信息表的SQL语句如下：**

CREATE TABLE vip(
      id number(11) NOT NULL,
      username varchar2(255) DEFAULT NULL,
      password varchar2(255) DEFAULT NULL,
      groupId number (11) DEFAULT 0,
      PRIMARY KEY (Id)
    ) ;
    CREATE TABLE vip_group (
      Id  number(11) NOT NULL,
      name varchar2(255) DEFAULT NULL,
      remark varchar2(255) DEFAULT NULL,
      PRIMARY KEY (Id)
    ) ;
    
    -- DESC会员信息表和会员组信息表。
    SQL> DESC vip;
    名称       空值       类型            
    --------      --------     ------------- 
    ID       NOT NULL   NUMBER(11)    
    USERNAME          VARCHAR2(255) 
    PASSWORD          VARCHAR2(255) 
    GROUPID            NUMBER(11)   
    
    SQL> DESC vip_group;
    名称     空值       类型            
    ------     --------     ------------- 
    ID     NOT NULL  NUMBER(11)    
    NAME             VARCHAR2(255) 
    REMARK          VARCHAR2(255)

已知现在有一个模块需要经常查询带有会员组名称、会员组备注（remark）、会员用户名信息的会员信息。根据这种情况可以创建一个temp\_vip表。temp\_vip表中存储用户名（user\_name），会员组名称（group\_name）和会员组备注（group\_remark）信息。

CREATE TABLE temp_vip (
      id number (11) NOT NULL,
      user_name varchar2(255) DEFAULT NULL,
      group_name varchar2(255) DEFAULT NULL,
      group_remark varchar2(255) DEFAULT NULL,
      PRIMARY KEY (Id)
    );

**        接下来，从会员信息表和会员组表中查询相关信息存储到临时表中：**

INSERT INTO temp_vip(user_name, group_name, group_remark)
    SELECT v.username, g.name, g.remark 
      FROM vip v, vip_group g
     WHERE v.groupId =g.Id;

以后，可以直接从temp\_vip表中查询会员名、会员组名称和会员组备注，而不用每次都进行联合查询。这样可以提高数据库的查询速度。

## ##

### 优化插入记录速度-禁用索引 ###

对于非空表，插入记录时，Oracle会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入记录的速度。为了解决这种情况，可以在插入记录之前禁用索引，数据插入完毕后再开启索引。禁用索引的语句如下：

-- 禁用索引
    ALTER index index_name unusable;
    
    -- 重新开启
    ALTER index index_name  usable;

### 禁用唯一性检查 ###

插入数据时，Oracle会对插入的记录进行唯一性校验。这种唯一性校验也会降低插入记录的速度。为了降低这种情况对查询速度的影响，可以在插入记录之前禁用唯一性检查，等到记录插入完毕后再开启。禁用唯一性检查的语句如下：

-- 禁用
    ALTER TABLE table_name
    DISABLE CONSTRAINT constraint_name;
    
    -- 开启
    ALTER TABLE table_name 
    ENABLE CONSTRAINT constraint_name;

### 使用批量插入 ###

INSERT INTO fruits VALUES
    SELECT ‘x1’, ‘101 ’, ‘mongo2 ’, ‘5.6’ from dual
    Union all 
    SELECT ‘x2’, ‘101 ’, ‘mongo3 ’, ‘5.6’ from dual
    Union all 
    SELECT ‘x3’, ‘101 ’, ‘mongo4 ’, ‘5.6’ from dual;

## 六、优化服务器 ##

### 优化Oracle参数 ###

*  DB\_BLOCK\_BUFFERS：数据库缓冲区大小
 *  SHARED\_POOL\_SIZE：作用是缓存已经被解析的SQL语句，使其能重用使用【建议150~500M，每增加1·G内存增加100M】
 *  Sort\_area\_size：查询需要排序时，使用该部分内存，内存不足时，增加临时表空间做排序。【建议不超过3M】
 *  Sort\_area\_retained\_size：当排序完成后，至少给Session继续保留的排序内存最小值，该值最大可等于Sort\_area\_size【建议设为Sort\_area\_size的10%，甚至为0】
 *  Log\_buffer：重做日志缓冲区【建议设为1M】
 *  Session\_cached\_cursor：指定高速缓存的会话游标的数量【建议设为50】
 *  re\_page\_sga：把所有SGA装载到内存中，虽然对启动速度有影响，但在内存充足的系统上能降低缺页故障率【建议设为true】
 *  ML\_LOCKS：所有用户获取锁的 最大数量，过小会引起死锁问题【建议设为600以上】
 *  DB\_FILE\_MULTIBLOCK\_READ\_COUNT：与全表扫描有关，在Oracle请求大量连续数据块的时候，该参数控制控制块的读入速率。【建议设为8】
 *  OPEN\_CURSOR：指定一次会话可以打开游标的最大数量，限制游标高速缓存的大小。【建议设高，250~300】

### 优化硬件 ###

*  扩容内存，增加缓冲区容量
 *  高速磁盘，合理分布 I/O
 *  多处理器，多线程工作

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FkaWRhczc0ODkxNDk2_size_16_color_FFFFFF_t_70]: /images/20220323/47255edb2694487fa9e60c47a6a54903.png