Oraclesql

前言

sql_trace 是我在工作中经常要用到的调优工具 相比较statspack 我更愿意用这个工具

因为数据库慢原因的 %以上是由于sql问题造成的 statspack没有sql的执行计划 显示没有它直观 方便 对想要针对性不强

介绍数据库调优需要经常会用到的工具 可以很精确地跟抓取相关session正在运行的sql 再通过tkprof分析出来sql的执行计划等相关信息 从而判断那些sql语句存在问题

统计如下信息（摘字官方文档）

Parse execute and fetch counts

CPU and elapsed times

Physical reads and logical reads

Number of rows processed

Misses on the library cache

Username under which each parse occurred

Each mit and rollback

使用

使用前需要注意的地方

初始化参数timed_statistics=true   允许sql trace 和其他的一些动态性能视图收集与时间（cpu elapsed）有关的参数 一定要打开 不然相关信息不会被收集 这是一个动态的参数 也可以在session级别设置

SQL>alter session set titimed_statistics=true

MAX_DUMP_FILE_SIZE跟踪文件的大小的限制 如果跟踪信息较多可以设置成unlimited 可以是KB MB单位 I开始默认为unlimited这是一个动态的参数 也可以在session级别设置

SQL>alter system set max_dump_file_size=

SQL>alter system set max_dump_file_size=unlimited

USER_DUMP_DEST指定跟踪文件的路径 默认路径实在$ORACLE_BASE/admin/ORA_SID/udump这是一个动态的参数 也可以在session级别设置

SQL>alter system set user_dump_dest=/oracle/trace

数据库级别

设置slq_trace参数为true会对整个实例进行跟踪 包括所有进程 用户进程和后台进程 会造成比较严重的性能问题 生产环境一定要慎用

SQL>alter system set sql_trace=true;

Session级别

当前会话

SQL>alter session set sql_trace=true;

SQL>alter session set sql_trace=false;

其他会话

通过oracle提供的系统包 DBMS_SYSTEM SET_SQL_TRACE_IN_SESSION来实现

SQL>execute dbms_system set_sql_trace_in_session(sid serial# true);

SQL>execute dbms_system set_sql_trace_in_session(sid serial# false);

注

sid serial#从v$session视图中获得

DBMS_SYSTEM包里还可以对其他用户的参数（如 timed_statistics max_dump_file）进行设置 在这不做介绍了 很少用到 想了解dbms_system里的程序包可以desc dbms_system看一下

得到trace文件后我们要用tkprof他进行格式化 通过sql语句快速定位到相应的trace文件

Tkprof

tkprof的目的是将sql trace生成的跟踪文件转换成用户可以理解的格式

格式

tkprof tracefile outputfile [optional | parameters ]

参数和选项（这里只介绍最常用的 也是最实用的）

explain=user/password执行explain命令将结果放在SQL trace的输出文件中

sys=[yes/no]确定系统是否列出由sys用户产生或重调的sql语句

sort=sort_option按照指定的方法对sql trace的输出文件进行降序排序

sort_option选项

prscnt按解析次数排序

prscpu按解析所花cpu时间排序

prsela按解析所经历的时间排序

prsdsk按解析时物理的读 *** 作的次数排序

prsqry按解析时以一致模式读取数据块的次数排序

prscu按解析时以当前读取数据块的次数进行排序

execnt按执行次数排序

execpu按执行时花的cpu时间排序

exeela按执行所经历的时间排序

exedsk按执行时物理读 *** 作的次数排序

exeqry按执行时以一致模式读取数据块的次数排序

execu按执行时以当前模式读取数据块的次数排序

exerow按执行时处理的记录的次数进行排序

exemis按执行时库缓冲区的错误排序

fchcnt按返回数据的次数进行排序

fchcpu按返回数据cpu所花时间排序

fchela按返回数据所经历的时间排序

fchdsk按返回数据时的物理读 *** 作的次数排序

fchqry按返回数据时一致模式读取数据块的次数排序

fchcu按返回数据时当前模式读取数据块的次数排序

fchrow按返回数据时处理的数据数量排序

注

这些排序中我经常用到的是fchdsk fckchela fchqry 因为有问题的sql一般都是大的查询造成的 当然更新 插入 删除时也会存在全表扫描 这就需要:exedsk exeqry exeela等选项 根据具体情况具体分析

Cpu时间和Elapsed时间都是以秒为单位 而且两个值基本上一样 但我比较常用elapsed 他是反映的用户相应时间 从运行sql到用户得到结果的时间 会更实际些

tkprof输出文件各列的含义 （理解下面的含义对我们快速定位问题很有帮助）

parse:

将sql语句转换成执行计划 包括检查是否有正确的授权 需要到得表 列及其他引用到得对象是否存在 这些信息分别存在v$librarycache v$rowcache

execute

oracle实际执行的语句 如 insert update delete 这些会修改数据 对于select *** 作 这部只是确定选择的行数

fetch

返回查询获得的行数 只有执行select会被收集

Count

这个语句被parse execute fetch的次数的统计

Cpu

这个语句所有的parse execute fetch所用的cpu总的时间 以秒为单位 如果TIMED_STATISTICS 关闭的话 值为

Elapsed

这个语句所有的parse execute fetch所消耗的总的时间 以秒为单位 如果TIMED_STATISTICS 关闭的话 值为

Disk

这个语句所有的parse execute fetch从磁盘上的数据文件中读取的数据块的数量

Query

在一致性读的模式下 这个语句所有的parse execute fetch所获取的buffer数量（这部分是从内存读取的也就是逻辑读取的 相当于执行计划里的consistent gets）

Current

在current模式下 这个语句所有的parse execute fetch所获取的buffer数量 一般是current模式下发生的delect insert update的 *** 作都会获取buffer

Rows

语句返回的行数 不包括子查询中返回的记录数目 对于select语句 返回在fetch这步 对于insert delete update *** 作 返回记录是在execute这步

分析

我一般的思路步骤是

先找磁盘多的sq l(sort= fchdsk ) 意味着全表扫描 找运行时间长的(sort= fchela) 意味着sql可能写的不好或磁盘 逻辑读较多 找出一致性读较多的(sort= fchqry) 当表不是很大的时候（可能全部缓存住了） 没有发生磁盘读 但不意味着不需要建立索引 或者sql需要优化 找出当前模式从缓冲区获得数据的数量(sort=exedsk exeela exeqry) 这些主要集中在dml语句里的 *** 作 看是否有必要优化sql或建立索引之所以排序是为了在sql很多的时候快速定位sql 如果sql比较少的话就没必要排序了 但我们要有分析问题的思路

举例

我自己建立了一个表

create table t (id int);

begin

for v in loop

insert into t values(v );

end loop

mit;

end;

下面是sql_trace所抓到得sql

不正常状态

select

from t

where id=

call count cpu elapsed disk query current rows

     

Parse                                                                   Execute                                                                 Fetch                                                        

     

total                                                        

Misses in library cache during parse:

Optimizer goal: CHOOSE

Parsing user id: (WH)

Rows Row Source Operation

  

TABLE ACCESS FULL T

Rows Execution Plan

  

SELECT STATEMENT GOAL: CHOOSE

TABLE ACCESS (FULL) OF T

首先这是一个select语句 它走了全部扫描

磁盘读（ ）和逻辑读（ ）都很多

运行了 次（Execute） 分析了 次（Parse） 一共用了将近 秒（elapsed）

我只是选择表的一行的数据的结果 就发生这么大的成本 很显然是全表扫描的结果造成的

正常状态

在做跟踪前我为这个表建立了一个索引

Create index t on t (id);

select

from t

where id=

call count cpu elapsed disk query current rows

     

Parse                                                                 Execute                                                                 Fetch                                                                

     

total                                                            

Misses in library cache during parse:

Optimizer goal: CHOOSE

Parsing user id: (WH)

Rows Row Source Operation

  

INDEX RANGE SCAN T (object id )

Rows Execution Plan

  

SELECT STATEMENT GOAL: CHOOSE

INDEX (RANGE SCAN) OF T (NON UNIQUE)

同样的语句

它走了索引 物理读 这个 其实是开始读索引时需要第一次读入的 以后运行就没有了

逻辑读 （平均这个sql一次 个逻辑读）

同样运行了 次（Execute）

分析了 次（Parse） 运行次数越多 分析次数越少越好一共只用了 秒（elapsed）

lishixinzhi/Article/program/Oracle/201311/17866

假定在程序效率和关键过程相当且不计入缓存等措施的条件下，读写任何类型的数据都没有直接 *** 作文件来的快，不论MSYQL过程如何，最后都要到磁盘上去读这个“文件”（记录存储区等效），所以当然这一切的前提是只读 内容，无关任何排序或查找 *** 作。

动态网站一般都是用数据库来存储信息，如果信息的及时性要求不高 可以加入缓存来减少频繁读写数据库。

两种方式一般都支持，但是绕过 *** 作系统直接 *** 作磁盘的性能较高，而且安全性也较高，数据库系中的磁盘性能一直都是瓶颈，大型数据库一般基于unix

系统，当然win下也有，不常用应为win的不可靠性，unix下，用的是裸设备raw设备，就是没有加工过的设备（unix下的磁盘分区属于特殊设备，

以文件形式统一管理），由dbms直接管理，不通过 *** 作系统，效率很高，可靠性也高，因为磁盘，cache和内存都是自己管理的，大型数据库系统

db2，oracal，informix（不太流行了），mssql算不上大型数据库系统。

1、直接读文件相比数据库查询效率更胜一筹，而且文中还没算上连接和断开的时间。

2、一次读取的内容越大，直接读文件的优势会越明

显（读文件时间都是小幅增长，这跟文件存储的连续性和簇大小等有关系），这个结果恰恰跟书生预料的相反，说明MYSQL对更大文件读取可能又附加了某些 ***

作（两次时间增长了近30%），如果只是单纯的赋值转换应该是差异偏小才对。

3、写文件和INSERT几乎不用测试就可以推测出，数据库效率只会更差。

4、很小的配置文件如果不需要使用到数据库特性，更加适合放到独立文件里存取，无需单独创建数据表或记录，很大的文件比如、音乐等采用文件存储更为方便，只把路径或缩略图等索引信息放到数据库里更合理一些。

5、PHP上如果只是读文件，file_get_contents比fopen、fclose更有效率，不包括判断存在这个函数时间会少3秒左右。

6、fetch_row和fetch_object应该是从fetch_array转换而来的，书生没看过PHP的源码，单从执行上就可以说明fetch_array效率更高，这跟网上的说法似乎相反。

磁盘读写与数据库的关系：

一 磁盘物理结构

(1) 盘片：硬盘的盘体由多个盘片叠在一起构成。

在硬盘出厂时，由硬盘生产商完成了低级格式化(物理格式化)，作用是将空白的盘片(Platter)划分为一个个同圆心、不同半径的磁道

(Track)，还将磁道划分为若干个扇区(Sector)，每个扇区可存储128×2的N次方（N=0123）字节信息，默认每个扇区的大小为

512字节。通常使用者无需再进行低级格式化 *** 作。

(2) 磁头：每张盘片的正反两面各有一个磁头。

(3) 主轴：所有磁片都由主轴电机带动旋转。

(4) 控制集成电路板：复杂！上面还有ROM（内有软件系统）、Cache等。

二 磁盘如何完成单次IO *** 作

(1) 寻道

当控制器对磁盘发出一个IO *** 作命令的时候，磁盘的驱动臂(Actuator

Arm)带动磁头(Head)离开着陆区(Landing

Zone，位于内圈没有数据的区域)，移动到要 *** 作的初始数据块所在的磁道(Track)的正上方，这个过程被称为寻道(Seeking)，对应消耗的时

间被称为寻道时间(Seek Time)；

(2) 旋转延迟

找到对应磁道还不能马上读取数据，这时候磁头要等到磁盘盘片(Platter)旋转到初始数据块所在的扇区(Sector)落在读写磁头正下方之后才能开始读取数据，在这个等待盘片旋转到可 *** 作扇区的过程中消耗的时间称为旋转延时(Rotational Delay)；

(3) 数据传送

接下来就随着盘片的旋转，磁头不断的读/写相应的数据块，直到完成这次IO所需要 *** 作的全部数据，这个过程称为数据传送(Data Transfer)，对应的时间称为传送时间(Transfer Time)。完成这三个步骤之后单次IO *** 作也就完成了。

根据磁盘单次IO *** 作的过程，可以发现：

单次IO时间 = 寻道时间 + 旋转延迟 + 传送时间

进而推算IOPS(IO per second)的公式为：

IOPS = 1000ms/单次IO时间

三 磁盘IOPS计算

不同磁盘，它的寻道时间，旋转延迟，数据传送所需的时间各是多少？

1 寻道时间

考虑到被读写的数据可能在磁盘的任意一个磁道，既有可能在磁盘的最内圈（寻道时间最短），也可能在磁盘的最外圈（寻道时间最长），所以在计算中我们只考虑平均寻道时间。

在购买磁盘时，该参数都有标明，目前的SATA/SAS磁盘，按转速不同，寻道时间不同，不过通常都在10ms以下：

3 传送时间2 旋转延时

和寻道一样，当磁头定位到磁道之后有可能正好在要读写扇区之上，这时候是不需要额外的延时就可以立刻读写到数据，但是最坏的情况确实要磁盘旋转整整

一圈之后磁头才能读取到数据，所以这里也考虑的是平均旋转延时，对于15000rpm的磁盘就是(60s/15000)(1/2) = 2ms。

(1) 磁盘传输速率

磁盘传输速率分两种：内部传输速率(Internal Transfer Rate)，外部传输速率(External Transfer Rate)。

内部传输速率(Internal Transfer Rate)，是指磁头与硬盘缓存之间的数据传输速率，简单的说就是硬盘磁头将数据从盘片上读取出来，然后存储在缓存内的速度。

理想的内部传输速率不存在寻道，旋转延时，就一直在同一个磁道上读数据并传到缓存，显然这是不可能的，因为单个磁道的存储空间是有限的；

实际的内部传输速率包含了寻道和旋转延时，目前家用磁盘，稳定的内部传输速率一般在30MB/s到45MB/s之间(服务器磁盘，应该会更高)。

外部传输速率(External Transfer Rate)，是指硬盘缓存和系统总线之间的数据传输速率，也就是计算机通过硬盘接口从缓存中将数据读出交给相应的硬盘控制器的速率。

硬盘厂商在硬盘参数中，通常也会给出一个最大传输速率，比如现在SATA30的6Gbit/s，换算一下就是61024/8，768MB/s，通常指的是硬盘接口对外的最大传输速率，当然实际使用中是达不到这个值的。

这里计算IOPS，保守选择实际内部传输速率，以40M/s为例。

(2) 单次IO *** 作的大小

有了传送速率，还要知道单次IO *** 作的大小(IO Chunk Size)，才可以算出单次IO的传送时间。那么磁盘单次IO的大小是多少？答案是：不确定。

*** 作系统为了提高 IO的性能而引入了文件系统缓存(File System Cache)，系统会根据请求数据的情况将多个来自IO的请求先放在缓存里面，然后再一次性的提交给磁盘，也就是说对于数据库发出的多个8K数据块的读 *** 作有可能放在一个磁盘读IO里就处理了。

还有，有些存储系统也是提供了缓存（Cache），接收到 *** 作系统的IO请求之后也是会将多个 *** 作系统的 IO请求合并成一个来处理。

不管是 *** 作系统层面的缓存还是磁盘控制器层面的缓存，目的都只有一个，提高数据读写的效率。因此每次单独的IO *** 作大小都是不一样的，它主要取决于系统对于数据读写效率的判断。这里以SQL Server数据库的数据页大小为例：8K。

(3) 传送时间

传送时间 = IO Chunk Size/Internal Transfer Rate = 8k/40M/s = 02ms

可以发现：

(31) 如果IO Chunk Size大的话，传送时间会变大，从而导致IOPS变小；

(32) 机械磁盘的主要读写成本，都花在了寻址时间上，即：寻道时间 + 旋转延迟，也就是磁盘臂的摆动，和磁盘的旋转延迟。

(33) 如果粗略的计算IOPS，可以忽略传送时间，1000ms/(寻道时间 + 旋转延迟)即可。

4 IOPS计算示例

以15000rpm为例：

(1) 单次IO时间

单次IO时间 = 寻道时间 + 旋转延迟 + 传送时间 = 3ms + 2ms + 02 ms = 52 ms

(2) IOPS

IOPS = 1000ms/单次IO时间 = 1000ms/52ms = 192 (次)

这里计算的是单块磁盘的随机访问IOPS。

考虑一种极端的情况，如果磁盘全部为顺序访问，那么就可以忽略：寻道时间 + 旋转延迟 的时长，IOPS的计算公式就变为：IOPS = 1000ms/传送时间

IOPS = 1000ms/传送时间= 1000ms/02ms = 5000 (次)

显然这种极端的情况太过理想，毕竟每个磁道的空间是有限的，寻道时间 + 旋转延迟 时长确实可以减少，不过是无法完全避免的。

四 数据库中的磁盘读写

1 随机访问和连续访问

(1) 随机访问(Random Access)

指的是本次IO所给出的扇区地址和上次IO给出扇区地址相差比较大，这样的话磁头在两次IO *** 作之间需要作比较大的移动动作才能重新开始读/写数据。

(2) 连续访问(Sequential Access)

相反的，如果当次IO给出的扇区地址与上次IO结束的扇区地址一致或者是接近的话，那磁头就能很快的开始这次IO *** 作，这样的多个IO *** 作称为连续访问。

(3) 以SQL Server数据库为例

数据文件，SQL Server统一区上的对象，是以extent(88k)为单位进行空间分配的，数据存放是很随机的，哪个数据页有空间，就写在哪里，除非通过文件组给每个表预分配足够大的、单独使用的文件，否则不能保证数据的连续性，通常为随机访问。

另外哪怕聚集索引表，也只是逻辑上的连续，并不是物理上。

日志文件，由于有VLF的存在，日志的读写理论上为连续访问，但如果日志文件设置为自动增长，且增量不大，VLF就会很多很小，那么就也并不是严格的连续访问了。

2 顺序IO和并发IO

(1) 顺序IO模式(Queue Mode)

磁盘控制器可能会一次对磁盘组发出一连串的IO命令，如果磁盘组一次只能执行一个IO命令，称为顺序IO；

(2) 并发IO模式(Burst Mode)

当磁盘组能同时执行多个IO命令时，称为并发IO。并发IO只能发生在由多个磁盘组成的磁盘组上，单块磁盘只能一次处理一个IO命令。

(3) 以SQL Server数据库为例

有的时候，尽管磁盘的IOPS(Disk Transfers/sec)还没有太大，但是发现数据库出现IO等待，为什么？通常是因为有了磁盘请求队列，有过多的IO请求堆积。

磁盘的请求队列和繁忙程度，通过以下性能计数器查看：

LogicalDisk/AvgDisk Queue Length

LogicalDisk/Current Disk Queue Length

LogicalDisk/%Disk Time

这种情况下，可以做的是：

(1) 简化业务逻辑，减少IO请求数；

(2) 同一个实例下，多个数据库迁移的不同实例下；

(3) 同一个数据库的日志，数据文件分离到不同的存储单元；

(4) 借助HA策略，做读写 *** 作的分离。

3 IOPS和吞吐量(throughput)

(1) IOPS

IOPS即每秒进行读写（I/O） *** 作的次数。在计算传送时间时，有提到，如果IO Chunk Size大的话，那么IOPS会变小，假设以100M为单位读写数据，那么IOPS就会很小。

(2) 吞吐量(throughput)

吞吐量指每秒可以读写的字节数。同样假设以100M为单位读写数据，尽管IOPS很小，但是每秒读写了N100M的数据，吞吐量并不小。

(3) 以SQL Server数据库为例

对于OLTP的系统，经常读写小块数据，多为随机访问，用IOPS来衡量读写性能；

对于数据仓库，日志文件，经常读写大块数据，多为顺序访问，用吞吐量来衡量读写性能。

磁盘当前的IOPS，通过以下性能计数器查看：

LogicalDisk/Disk Transfers/sec

LogicalDisk/Disk Reads/sec

LogicalDisk/Disk Writes/sec

磁盘当前的吞吐量，通过以下性能计数器查看：

LogicalDisk/Disk Bytes/sec

LogicalDisk/Disk Read Bytes/sec

LogicalDisk/Disk Write Bytes/sec

数据库建索引主要是用于查询时进行排序的,当然某一字段没有建索引，SQL里也可以它为关键字进行排序，但性能远远低于有索引的情况，记录比较多的时候对比更明显。至于性能，如果在记录比较多的情况，上万条记录，你又建了很多索引，数据库本身维护这些索引会付出很大的代价。这个时候你只要在几个常用的字段上加索引，删掉不用的索引，就会解决性能的问题。

以上就是关于Oraclesql全部的内容，包括:Oraclesql、磁盘读写和数据库读写哪个效率更高、数据库建索引如何影响性能，影响性能的真正原因是什么、等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！