SQL Tuning学习杂记

Chp1  Oracle数据库体系结构

1、实例 + 数据库  构成了“Oracle数据库”

       （1）实例：Instance = Share Memory + Process（共享内存 + 守候进程）

       （2）数据库：Database = 数据文件 + 控制文件 + 重做日志文件

数据文件：

存放数据，占据最大的一块。

 

控制文件：

存放配置文件

成功启动和操作数据库所必需的小型二进制文件。包含：

a)数据文件的位置及大小

b)联机重做日志文件的位置及大小

c)数据库的创建时间

d)日志序列号

联机重做日志文件：

a)用于记录数据库缓冲区内数据更改（Select不会记录）的物理文件(更改前后都有)，目的是为了在实例失败时恢复尚未写入数据文件但已经提交的数据。

b)每个数据库至少包含两个重做日志组，并且这些重做日志组是循环使用的。(为了保证数据完备。组间切换（循环），组内备份（相互）)

Commit的时候，变化写盘（重做日志文件）成功之后才commit成功，为的就是保证”a)”，

所以，重做日志文件的IO性能最为关键（速度瓶颈）

提高归档的速度的方法：

（1）       增加重做日志组

（2）       归档的目录效率要高

口令文件：

用户存放有权限启动和关闭Oracle实例特权用户及其口令

归档日志文件：

a)是联机重做日志文件的脱机副本

b)数据库可以配置为archivelog或noarchivelog模式，只有在archivelog模式时才会生成归档日志文件。每次日志切换都会生成归档日志文件。

警告文件（alert）和跟踪文件：

Alert文件存放：警告信息和日志切换信息

跟踪文件分为：（1）由后台进程产生的；（2）服务器进程产生的

另外：

Pfile文件夹下为数据库初始化文件init.*

Bdump文件夹下存放：alert_*.log 和  后台跟踪文件*.trc

Udump文件夹下存放：user的进程产生的跟踪文件

Cdump文件夹下存放：core信息文件

Create文件夹下为数据库创建信息文件

Oracle数据库（服务器） = 实例 + 数据库

2、Server Process：

（1）个数：有限个、预先定义好

（2）状态：ready

这种模式称为“共享服务器模式”

好处：（1）服务个数有限，但用户数可以无限

        （2）服务器上的进程数有限，可控

应用：（1）网站——不要求实时响应

 

另一种模式：独占服务器模式：Server Process = User Process

缺点：（1）Server Process可能会很多

优点：（1）每个User Process都有一个Server Process对应，实现高效（用消耗资源换取）

 

3、系统全局区SGA

（1）由几种内存结构组成（必须的）：

● 共享池

存放SQL语句，计算单位（SHARED_POOL_SIZE）为Byte（字节），分为：

a. Library Cache库高速缓存：SQL、Procedure、Function、Package

b. Dictionary cache数据字典缓存：即row cache – 存放数据字典信息

上面两者统称为Shared pool（共享池）

●     数据库缓冲区高速缓存

Data cache（data buffer）：最小单位为“块”DB_BLOCK_SIZE ------ 8k

存放数据（表记录、索引等），每次最少读入8k

用LRU算法来管理 – 最近最少使用的丢弃

●     重做日志缓冲区

存放更新前后的值（新值 + 老值）

记录所有对数据库的数据块的更改信息（select不记录）

●     其他结构（如：锁管道、统计数据等）

（2）还有两种附加的内存结构也可以在SGA中进行配置（可选的内存区）：

●大型池（Large Pool）：用LRU算法来管理 – 最近最少使用的丢弃

大小由LARGE_POOL_SIZE定义

以下为两种常用的大型池（两类一定用到大型池的操作）：

a. 并行选项

b. rMan

●Java池

用Java语言（不是PL/SQL语句）编写数据库内部的函数、过程、包的时候才会用到Java池。大小由JAVA_POOL_SIZE参数定义。

●     排序区

用于排序数据的内存区，对于专用（独占）服务器连接，从PGA分配内存；对于共享服务器配置，从SGA配置。

如：ORDER BY, CREATE INDEX, GROUP BY

SQL语句的效率如果陡降，则很可能是用到了硬盘（硬盘排序—硬排序），因为内存（内存排序—软排序）读写很快。

程序全局区（PGA） --每个Server Process的私有内存

位于数据库服务器上

用于连接到Oracle数据库的用户进程的保留存储区

进程创建的时候分配，进程结束时回收。

仅仅被一个进程使用

a.任何时刻，只要PGA不退出，就保留它的最大值 – 老的方式（不用了）-- 手工的

b.动态变化 – 新的方式（合理） -- auto的 -- 尽量避免内存泄露

 

4、连接Oracle Server

（1）建立连接和会话

 

5、后台进程

必须启动的：

（1）       LGWR（重做日志文件写盘）

为了保证redo log file中的数据最新

以下情况LGWR写入：

●有提交

●重做日志缓冲区三分之一已满

●重做已超过1MB

●每三秒钟

●DBWR写入之前

（2）       DBWR（data buffer write）

下列情况DBWR进程写入：

●出现检查点CheckPoint（CKPT）

●灰数据缓冲区的数量达到阀值

●没有任何空闲缓冲区

●出现超时

●RAC发出ping请求

●表空间置于脱机

●表空间置于只读模式

●删除或截断表

●备份表空间

（3）       SMON -- 实例恢复，依据是redo log file

功能：

●例程恢复

    前滚重做日志中的更改

    打开数据库以便用户访问

    回滚未提交的事务

●合并空闲空间，碎片整理   不建议使用，将pctincrease设为0即可

●回收临时段 —— 排序的时候可能用到

（4）       PMON —— 回滚事务Process Monitor

进程失败后通过下列方法清理：

回滚事务

释放锁

释放其他资源

重启被异常挂起的进程

（5）       CKPT —— check point     保持数据库的一致性

功能：

在检查点时向DBWR进程发出信号

用检查点信息更新数据文件头

用检查点信息更新控制文件

（6）       ARCn —— 归档日志进程（可选后台进程）

（7）       一些相关的视图

V$instance V$database

V$sqltext   V$sqlarea

V$sgastat   V$sga

V$log   V$logfile

V$controlfile

Chp2 管理物理和逻辑数据库结构

所有的表都存在于表空间里面，数据库由若干个表空间组成，因此不同的表可以存在于不同的表空间里

表空间由一个或多个数据文件构成

表空间的扩大，由添加数据文件完成。一个数据文件仅能属于一个表空间

表名 = 段名segment

区 = 扩展 Extent   

（1）init   初始的时候，segment是初始扩展

以后的每一个扩展必须为连续的空间，并且Extent必须为db_block块的整数倍。

通过分配extent来满足表的日益增长的需要，每次扩展的大小可以人为设定。

每一个extent都仅能存在于一个datafile（数据文件）里面

一个OS Block = 512字节，所以extent必然为os block的倍数（因为db block是os block的倍数）

一张表如果没有定义extent分配原则，那么此表就采用它所存在的表空间的缺省表空间分配原则来分配空间。

    表空间的两种管理方式：

（1）数据字典管理的表空间 -- 存放于system表空间，有性能瓶颈

Init extent初始

Next extent下一个

Pctincrease增长率 —— 设为0，就不会产生“空间碎片”，即：分配的extent大小都一样大

一张表的扩展不要超过300个，如果某表的数据量太大，就可以加个历史表，作为备份，使得主表的数据量下降。（任何一张表里面的数据都有一个生命周期）

 

“空间碎片”——只要比需要申请的空间（连续的）小，都称为空间碎片，也可能本身很大，但都无法利用，因为不连续，都是离散分布的。

碎片的空间合并（必须是紧挨着的碎片才能合并），由SMON进程完成。不过效率很差。

（2）本地管理表空间 -- 存放于表内部

为了避免空间碎片：

a.uniform（统一的块大小）   相当于pctincrease设为0

    需要估算。适用于大数据量的表，并且每张表的数据量都差不多

b.自适应 —— 有多少就分配多少给申请者（块大小不规则）

    比较节省空间。适用于表空间内的表数据量差别很大，并且一张表中的数据时多时少。

表分区：

一个表可以分为多个分区，一个分区对应于一个段（分区 = 段）。

如：某表中，年份的字段，一年12个月，可以分为上半年、下半年两组，或者四个季度四组。

分区表：

表以“分区字段”的值的范围（上下半年或者四个季度等等）来分区。

1.普通表（没有分区的）的索引称为“普通索引”——即“全局索引”

       

    索引的“分区字段”：在索引（字段）字段之外，需要另一个字段来将原来的索引分成两个或多个索引。如：原来索引为身高字段，现用性别（男、女）分成两拨索引。则“性别”字段为“身高”索引的分区字段。这个索引就称为“分区索引”。

“有前缀的分区索引”：如：现在建立的索引为“性别”+“身高”，并且性别是索引的第一个字段（即第一个索引字段同时也是索引的分区字段），则这个索引称为有前缀的分区索引。否则称为“无前缀的分区索引”

2.分区表：

（1）普通索引 —— 全局索引

    同上，就是在某些字段上建索引。

(2)全局分区索引（全局 + 分区）

表分区字段 <> 索引的分区字段

以“身高”为索引字段，用“性别”作为索引的分区字段，那么每个年级的同学都可能在两个分区（男/女）中。

（1）       本地分区索引（Local）

表分区字段 = 索引的分区字段

   

    “分区索引”将大的索引分割为多个区，提高效率。

建议：

普通表使用普通索引

分区表使用本地分区索引（Local）

使用“分区索引”还是“普通索引”的原则：

Insert的时候分区索引效率高，但是Select的效率低（用普通索引）。

也就是：查询用普通索引，入库用分区索引。

按索引组织的表IOT（Index organized Table）：

没有数据段，只有索引段。

SELECT语句的执行过程：

1． Server Process（服务进程）接收到SQL语句后，首先到Library Cache中找，是否有可以共享的SQL语句（已使用过的一模一样的SQL），如果有，则转到2，否则到3

2． 如果DataBuffer中已经有相关数据，则直接从中取出数据，放入PGA排序，返回

3． 分析SQL语句，到Dictionary Cache中找是否存在，如果没有则到硬盘上读取（物理IO，DataFile，速度较慢），如果在数据字典缓存中已有，则直接从数据字典缓存中取。

4． 执行select语句，所有的数据放入DataBuffer，以共享给后来的进程，提取查询行

5． 也可能会从回滚段中取数据

6． 注意，所有的动作都是服务器进程完成的。

加粗的三个部分为可能用到的空间，并且最终必须放到DataBuffer中

 

SQL语句共享的条件（两者都要具备）：

1.     同一个数据库用户的SQL语句才能够被共享

2.     这两个语句在语法上必须一样，没有任何差别，包括大小写、空格等都必须一致

可以使用变量绑定的办法可以达到共享SQL

DELETE的过程：占用回滚段最多

a.     commit & rollback

“快速提交法” —— commit

Redo LogBuffer写到redoLogfile中。但新值暂未写到dataFile

b.     加锁：DataBuffer和 回滚段 上都要加锁

c.     提交后这两个地方都要释放锁，这个回滚段即可重复利用

UPDATE的过程：

1.老值放在回滚段，并申请锁，新值放到DataBuffer中。

2.写LogBuffer，前后的值都要写

Commit：

没有写盘的要写盘（Log file），DataBuffer中新值不用写盘

两个锁要释放（data buffer ， 回滚段）

Rollback：

取出回滚段中的老值，放到DataBuffer

Chp  4 索引的基本原理

索引 = 键值 + ROWID + 组织信息

数据是以“块”为单位存放的

Chp 5 基本的提示句法

目标：时间最短，代价最小

SQL的优化策略（2种）

1.基于代价的方式（cost） —— 两种：

（1）     希望SQL执行的结果第一页出来较快：first_rows(n行)

（2）     一次性返回所有行：all_rows

2.基于规则

（3）     按照规则（rule）

 

对于同一条SQL语句，进行算法比对

Choose模式：

首先，判断表中是否有分析数据？

1.如果有分析数据（或者用了sql hint），则Oracle选择cost方式，一般为first_rows

2.如果无分析数据，则（1）如果用到了“特殊技术”：包括分区、位图索引、函数索引（这两者都是基于代价的优化）、并行选项（和rMan一样用到Large Pool）等等，此时还是采用cost方式

                  （2）其他情况，采用rule方式

3.如果sql语句中加了sql hint，那么choose选项走的是cost方式

 

对于同一个业务逻辑的SQL的写法：

需要两个经验：（1）SQL经验 （2）业务经验

好的SQL语句的标准：

1.性能稳定（平稳） —— 对数据量、对环境的稳定（即不依赖于数据量和环境的变化）

2.速度可接受（不一定要求最快）

方法：

1.     要估算数据量，写对环境的要求不是很高的语句

2.     不要一味的求速度

 

核心的因素：表的数据量（表占用的空间，读的是db_block块）

比较同一业务逻辑的几个SQL语句的对硬盘、内存、回滚段的操作

 

对于一个业务而言，好的SQL语句需要如下的工作：

1.     好的业务逻辑（结构）—— 即：好的业务设计，这是第一要素

需要跟局方、设计人员沟通，使得在满足大部分需求（满足大概率事件）的前提下，消耗资源较少

2.     选择快速缩小数据量的路径

3.     慎重选择走索引还是全表扫描（目的：减少IO量）

 

6.3

一般的insert是写redo的，还有一种direct insert，不写redo（1.不可回滚，2.不使用原表中空间，每次都用全新的db_block“块”来使用）

1．单进程的direct insert，就是/*+ APPEND */

2．多进程的direct insert，就是/*+ PARALLEL */

/*+ CACHE(tab1) */ 表tab1种的数据一直驻留在cache中，适用于参数表

风险：有可能导致sql语句执行计划发生改变

 

/*+ … */ 建议Oracle使用hint。。。，并非强制

 

SQL：小数据量的情况下，用IN，否则用Exists代替In