SQL语句性能调整.docx

SQL语句性能调整.docx

《SQL语句性能调整.docx》由会员分享，可在线阅读，更多相关《SQL语句性能调整.docx（16页珍藏版）》请在冰豆网上搜索。

SQL语句性能调整

SQL语句性能调整

SQL语句性能调整的目标：

1．去掉不必要的大表全表扫描

2．检查并优化索引的使用

3．检查子查询，尽量用简单的连接的方式实现含有子查询的语句；

4．调整PCTFREE和PCTUSED等存储参数优化插入、更新或者删除等操作；

5．根据数据库设定的优化器书写SQL语句，原则上应该按基于规则的优化要求书写；

6．在需要对库表全表扫描并有多个CPU的情况下考虑并行方式查询；

一、数据库索引使用的问题

1．索引用还是不用？

需要考虑库表的数据量而定。

是全表扫描还是索引范围扫描主要考虑SQL的查询速度问题。

这里主要关心读取的记录的数目。

根据ORACLE官方的说法,使用索引范围扫描的原则是：

对于数据有原始排序的表，读取少于表记录数40%的查询应该使用索引范围扫描。

对读取多于表记录数40%的查询应全表扫描。

对于未排序的表，读取少于表记录数7%的查询应该使用索引范围扫描,反之，对读取多于表记录数7%的查询应全表扫描。

这里我们定个规则：

在表数据量小于8K（一个数据库大小）时，不建立索引。

2．如果字段列上有建索引，什么SQL查询是有用索引的？

什么SQL查询又是没有用索引的呢？

存在下面情况的SQL，是不会用到索引的：

（1）、存在数据类型隐形转换的,如：

select*frombas_facswherefacid=’1120000000418’；

（2）、列上有数学运算的，如：

select*fromemployeewheresalary*2<10000;

（3）、使用不等于（<>）运算的，如：

select*frombas_facswherefacid<>1120000000418;

（4）、使用substr字符串函数的，如：

select*frombas_facswheresubstr（facid,1,3）=’112’;

（4）、’%’通配符在第一个字符的，如:

select*frombas_facswherefaccodelike‘%/PX’;

（5）、字符串连接（||）的，如：

select*frombas_facswherefaccode||’/PX’=’AHZAKNXI/J027/PX’;

3．含有函数的索引

日期类型也是很容易用到的，而且在SQL语句中会使用to_char函数以查询具体的的范围日期。

如：

select*fromemployeewhereto_char（birth_day,’YYYY’）=’1976’;

我们可以建立基于函数的索引如：

createindexInd_emp_birthonemployee（to_char（（birth_day,’YYYY’））;

二、优化含有排序子句的SQL语句

1．ORACLE数据库发生排序的情况：

SQL中包含groupby子句;

SQL中包含orderby子句;

SQL中包含distinct子句;

SQL中包含minus或union操作;

尽量少用以上列出排序子句，除非必须；另外用unionall替代union。

2．尽量保证排序在内存中进行而非在磁盘。

在内存执行的排序速度要比在磁盘执行的排序速度快14000倍。

如果是专用连接，排序内存根据INIT.ORA的sort_area_size进行分配，如果是多线程服务连接，排序内存根据large_pool_size进行分配。

sort_area_size的增大可以减少磁盘排序，但是过大将使ORACLE性能降低，因为所用的连接会话都会分配到一个sort_area_size大小的内存，所以，为了提高有限的查询速度，可能会浪费大量的内存。

增加sort_multiblock_read_count的值使每次读取更多的内容，减少运行次数，提高性能。

三、SQL子查询的调整

　　1、理解关联子查询和非关联子查询。

下面是一个非关联子查询：

　　select*frombas_facswherefacidin（selectclientidfrombas_client）

而下面是一个关联子查询：

select*frombas_facsfwherefacidin

（selectclientidfrombas_clientwhereclientid=f.facid）;

　　以上返回的结果集是相同的，可是它们的执行开销是不同的：

　　非关联查询的开销——非关联查询时子查询只会执行一次，而且结果是排序好的，并保存在一个ORACLE的临时段中，其中的每一个记录在返回时都会被父查询所引用。

在子查询返回大量的记录的情况下，将这些结果集排序，以及将临时数据段进行排序会增加大量的系统开销。

关联查询的开销——对返回到父查询的的记录来说，子查询会每行执行一次。

因此，我们必须保证任何可能的时候子查询用到索引。

　　2、EXISTS子句和IN子句

带IN的关联子查询是多余的，因为IN子句和子查询中相关的操作的功能是一样的,如：

select*frombas_facsfwherefacidin

（selectclientidfrombas_clientwhereclientid=f.facid）;

为非关联子查询指定EXISTS子句是不适当的，因为这样会产生笛卡乘积。

如：

　　select*frombas_facswhereExists（selectclientidfrombas_client）

尽量不要使用NOTIN子句。

使用MINUS子句都比NOTIN子句快，虽然使用MINUS子句要进行两次查询：

select*frombas_facswherefacidin（selectfacidfrombas_facsMINUSselectfacidfrombas_stationwherestcodelike‘A01%’）;

3、在任何可能的时候，用标准连接或内嵌视图改写子查询。

四、SQL语句尽量可重用并采用绑定变量方式书写

书写SQL语句时尽量遵循如下原则：

1、遵循重用原则。

如下两个SQL语句，在ORACLE共享池是作为两个语句缓存的，ORACLE对它们分别进行了分析和编译，只有这两条语句完全相同，ORACLE才会作为一条语句缓存在共享池中。

select*fromOPT_SEGMENT

SELECT*FROMOPT_SEGMENT

2、绑定变量。

如下的两个SQL语句，在ORACLE共享池是作为两个语句缓存的，要想在共享池中缓存成一条，只有使用变量（9i后修改系统参数，数据库就可以自动绑定变量，但增加了分析SQL语句的负荷）

selectfaccode,facnamefrombas_facswherefacid='1460000001637'

selectfaccode,facnamefrombas_facswherefacid='1460000001639'

使用变量后的SQL语句如下：

selectfaccode,facnamefrombas_facswherefacid=:

facid

五、复杂关联查询语句简化处理。

在ORACLE中，当有超过5个表关联的查询发生时，性能开销可能会增加很多。

此种情况下，可以先将某几个表按优化原则关联建成视图，其它表再与视图关联查询，往往会提高性能。

（例子比较复杂）

六、更新、插入、以及删除等DML语句的调整

　　1、DML语句是指用来执行更新、插入、以及删除等操作类型的语句。

这些语句在结构上是很简单的，可调整的余地较小。

性能低下的情况有：

插入缓慢并占有过多的I/O资源－－这种情况主要是空闲列表中的数据块的空间过小，仅容的下较少的记录。

更新缓慢——这种情况主要是UPDATE操作扩展了一个VARCHAR2类型的列，而ORACLE被强制将内容迁移到其他数据块时。

删除缓慢——这种情况主要是记录被删除，ORACLE必须将数据块重新放置到空闲列表时。

　　因此，对DML进行调整，主要时利用对象存储参数和SQL之间的关系进行调整。

　　2、CTFREE存储参数

　　PCTFREE存储参数告诉ORACLE什么时候应该将数据块从对象的空闲列表中移出。

ORACLE的默认参数是PCTFREE=10；也就是说，一旦一个INSERT操作使得数据块的90%被使用，这个数据块就从空闲列表中移出。

　　PCTUSED存储参数

　　PCTUSED存储参数告诉ORACLE什么时候将以前满的数据块加到空闲列表中。

当记录从数据表中删除时，数据库的数据块就有空间接受新的记录，但只有当填充的空间降到PCTUSED值以下时，该数据块才被连接到空闲列表中，才可以往其中插入数据。

PCTUSED的默认值是PCTUSED=40。

　　存储参数规则小结

（1）PCTUSED较高意味着相对较满的数据块会被放置到空闲列表中，从而有效的重复使用数据块的空间，但会导致I/O消耗。

PCTUSED低意味着在一个数据块快空的时候才被放置到空闲列表中，数据块一次能接受很多的记录，因此可以减少I/O消耗，提高性能。

（2）PCTFREE的值较大意味着数据块没有被利用多少就从空闲列表中断开连接，不利于数据块的充分使用。

PCTFREE过小的结果是，在更新时可能会出现数据记录迁移的情况。

（注：

数据记录迁移是指记录在是UPDATE操作扩展了一个VARCHAR2类型的列或BLOB列后,PCTFREE参数所指定的空间不够扩展，从而记录被ORACLE强制迁移到新的数据块，发生这种情况将较严重的影响ORACLE的性能，出现更新缓慢）。

　　（3）在批量的插入、删除或者更新操作之前，先删除该表上的索引，在操作完毕之后在重新建立，这样有助于提高批量操作的整体速度，并且保证B树索引在操作之后有良好的性能。

　　3、ORACLE优化器的调整与选择；

　　基于成本优化器（CBO）：

（1）ORACLE8i以上版本更多地使用成本优化器，因为它更加智能；

（2）通过optimizer_mode=all_rows或first_rows来选择CBO；通过altersessionsetoptimizer_goal=all_rows或first_rows来选择CBO;通过添加hint来选择CBO;

　　（3）使用基于成本优化的一个关键是：

存在表和索引的统计资料。

通过analyzetable获得表的统计资料；通过analyzeindex获得索引的统计资料。

　　（4）对于超过5个表的连接的查询，建议不要使用成本优化器，而是在SQL语句中通过添加/*+rule*/提示或者通过指定的执行计划来避免可能会在20分钟以上的SQL解析时间。

　　基于规则优化器（RBO）：

（1）ORACLE8i以及ORACLE的以前版本主要用（RBO）,并且比较有效；

（2）通过optimizer_mode=rule来选择RBO；通过altersessionsetoptimizer_goal=rule来选择RBO;通过添加/*+rule*/来选择RBO;

　　（3）在RBO中，from子句的表的顺序决定表的连接顺序。

From子句的最后一个表是驱动表，这个表应该是最小的表。

　　（4）限定最强的布尔表达式放在最底层。

　　4、跟踪、优化SQL语句的方法

（1）保证在实例级将TIMED_STATISTICS设置为TRUE（在INIT.ORA中永久的设置它或执行ALTERSYSTEM命令临时设置它）；

（2）保证将MAX_DUMP_FILE_SIZE设置的较高。

此参数控制跟踪文件的大小。

　　（3）决定USER_DUMP_DEST所指向的位置，并保证有足够的磁盘空间。

这是放置跟踪文件的位置。

　　（4）在应用系统运行时，打开所怀疑的会话的SQL_TRACE.（在INIT.ORA中通过SQL_TRACE=TRUE永久的设置对所有的回话进行跟踪或通过使用系统包DBMS_SYSTEM.set_sql_trace_in_session（sid，serial,true）;命令临时设置它）

　　（5）执行业务相关操作；

　　（6）设置跟踪结束（DBMS_SYSTEM.set_sql_trace_in_session（sid,serial,false），如果不执行该步骤，可能跟踪文件中的信息不全，因为可能有一部分还在缓存中）；

　　（7）定位跟踪文件；

　　（8）对步骤（6）的跟踪文件进行TKPROF，生成报告文件；

　　（9）研究此报告文件，可以看到CPU、DISK、QUERY、COUNT等参数和executionplan（执行计划），优化开销最大的SQL；

重复执行步骤（4）~（9）直到达到所需的性能目标；

7、在有多个CPU的服务器上改变数据库系统设置使其运行在并行方式下

一、oracle高水位线详解

一、什么是水线（HighWaterMark）?

所有的oracle段（segments，在此，为了理解方便，建议把segment作为表的一个同义词）都有一个在段内容纳数据的上限，我们把这个上限称为"highwatermark"或HWM。

这个HWM是一个标记，用来说明已经有多少没有使用的数据块分配给这个segment。

HWM通常增长的幅度为一次5个数据块，原则上HWM只会增大，不会缩小，即使将表中的数据全部删除，HWM还是为原值，由于这个特点，使HWM很象一个水库的历史最高水位，这也就是HWM的原始含义，当然不能说一个水库没水了，就说该水库的历史最高水位为0。

但是如果我们在表上使用了truncate命令，则该表的HWM会被重新置为0。

二、HWM数据库的操作有如下影响：

a）全表扫描通常要读出直到HWM标记的所有的属于该表数据库块，即使该表中没有任何数据。

b）即使HWM以下有空闲的数据库块，键入在插入数据时使用了append关键字，则在插入时使用HWM以上的数据块，此时HWM会自动增大。

三、如何知道一个表的HWM？

a）首先对表进行分析:

ANALYZETABLEESTIMATE/COMPUTESTATISTICS;

b）SELECTblocks,empty_blocks,num_rows

FROMuser_tables

WHEREtable_name=;

说明：

BLOCKS列代表该表中曾经使用过得数据库块的数目，即水线。

EMPTY_BLOCKS代表分配给该表，但是在水线以上的数据库块，即从来没有使用的数据块。

让我们以一个有28672行的BIG_EMP1表为例进行说明：

1）SQL>SELECTsegment_name,segment_type,blocks

FROMdba_segments

WHEREsegment_name='BIG_EMP1';

SEGMENT_NAMESEGMENT_TYPEBLOCKS

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

BIG_EMP1TABLE1024

1rowselected.

2）SQL>ANALYZETABLEbig_emp1ESTIMATESTATISTICS;

Statementprocessed.

3）SQL>SELECTtable_name,num_rows,blocks,empty_blocks

FROMuser_tables

WHEREtable_name='BIG_EMP1';

TABLE_NAMENUM_ROWSBLOCKSEMPTY_BLOCKS

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

BIG_EMP128672700323

1rowselected.

注意：

BLOCKS+EMPTY_BLOCKS（700+323=1023）比DBA_SEGMENTS.BLOCKS少1个数据库块，这是因为有一个数据库块被保留用作segmentheader。

DBA_SEGMENTS.BLOCKS表示分配给这个表的所有的数据库块的数目。

USER_TABLES.BLOCKS表示已经使用过的数据库块的数目。

4）SQL>SELECTCOUNT（DISTINCT

DBMS_ROWID.ROWID_BLOCK_NUMBER（rowid）||

DBMS_ROWID.ROWID_RELATIVE_FNO（rowid））"Used"

FROMbig_emp1;

Used

----------

700

1rowselected.

5）SQL>deletefrombig_emp1;

28672rowsprocessed.

6）SQL>commit;

Statementprocessed.

7）SQL>ANALYZETABLEbig_emp1ESTIMATESTATISTICS;

Statementprocessed.

8）SQL>SELECTtable_name,num_rows,blocks,empty_blocks

FROMuser_tables

WHEREtable_name='BIG_EMP1';

TABLE_NAMENUM_ROWSBLOCKSEMPTY_BLOCKS

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

BIG_EMP10700323

1rowselected.

9）SQL>SELECTCOUNT（DISTINCT

DBMS_ROWID.ROWID_BLOCK_NUMBER（rowid）||

DBMS_ROWID.ROWID_RELATIVE_FNO（rowid））"Used"

FROMbig_emp1;

Used

----------

0----这表名没有任何数据库块容纳数据，即表中无数据

1rowselected.

10）SQL>TRUNCATETABLEbig_emp1;

Statementprocessed.

11）SQL>ANALYZETABLEbig_emp1ESTIMATESTATISTICS;

Statementprocessed.

12）SQL>SELECTtable_name,num_rows,blocks,empty_blocks

2>FROMuser_tables

3>WHEREtable_name='BIG_EMP1';

TABLE_NAMENUM_ROWSBLOCKSEMPTY_BLOCKS

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

BIG_EMP100511

1rowselected.

13）SQL>SELECTsegment_name,segment_type,blocks

FROMdba_segments

WHEREsegment_name='BIG_EMP1';

SEGMENT_NAMESEGMENT_TYPEBLOCKS

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

BIG_EMP1TABLE512

1rowselected.

注意:

TRUNCATE命令回收了由delete命令产生的空闲空间，注意该表分配的空间由原先的1024块降为512块。

为了保留由delete命令产生的空闲空间，可以使用TRUNCATETABLEbig_emp1REUSESTORAGE.

用此命令后，该表还会是原先的1024块。

四、Oracle表段中的高水位线HWM

在Oracle数据的存储中，可以把存储空间想象为一个水库，数据想象为水库中的水。

水库中的水的位置有一条线叫做水位线，在Oracle中，这条线被称为高水位线（High-wartermark,HWM）。

在数据库表刚建立的时候，由于没有任何数据，所以这个时候水位线是空的，也就是说HWM为最低值。

当插入了数据以后，高水位线就会上涨，但是这里也有一个特性，就是如果你采用delete语句删除数据的话，数据虽然被删除了，但是高水位线却没有降低，还是你刚才删除数据以前那么高的水位。

也就是说，这条高水位线在日常的增删操作中只会上涨，不会下跌。

下面我们来谈一下Oracle中Select语句的特性。

Select语句会对表中的数据进行一次扫描，但是究竟扫描多少数据存储块呢，这个并不是说数据库中有多少数据，Oracle就扫描这么大的数据块，而是Oracle会扫描高水位线以下的数据块。

现在来想象一下，如果刚才是一张刚刚建立的空表，你进行了一次Select操作，那么由于高水位线HWM在最低的0位置上，所以没有数据块需要被扫描，扫描时间会极短。

而如果这个时候你首先插入了一千万条数据，然后再用delete语句删除这一千万条数据。

由于插入了一千万条数据，所以这个时候的高水位线就在一千万条数据这里。

后来删除这一千万条数据的时候，由于delete语句不影响高水位线，所以高水位线依然在一千万条数据这里。

这个时候再一次用select语句进行扫描，虽然这个时候表中没有数据，但是由于扫描是按照高水位线来的，所以需要把一千万条数据的存储空间都要扫描一次，也就是说这次扫描所需要的时间和扫描一千万条数据所需要的时间是一样多的。

所以有时候有人总是经常说，怎么我的表中没有几条数据，但是还是这么慢呢，这个时候其实奥秘就是这里的高水位线了。

那有没有办法让高水位线下降呢，其实有一种比较简单的方法，那就是采用TRUNCATE语句进行删除数据。

采用TRUNCATE语句删除一个表的数据的时候，类似于重新建立了表，不仅把数据都删除了，还把HWM给清空恢复为0。

所以如果需要把表清空，在有可能利用TRUNCATE语句来删除数据的时候就利用TRUNCATE语句来删除表，特别是那种数据量有可能很大的临时存储表。

在手动段空间管理（ManualSegmentSpaceManagement）中，段中只有一个HWM，但是在Oracle9iRelease1才添加的自动段空间管理（AutomaticSegmentSpaceManagement）中，又有了一个低HWM的概念出来。

为什么有了HWM还又有一个低HWM呢，这个是因为自动段空间管理的特性造成的。

在手段段空间管理中，当数据插入以后，如果是插入到新的数据块中，数据块就会被自动格式化等待数据访问。

而在自动段空间管理中，数据插入到新的数据块以后，数据块并没有被格式化，而是在第一次在第一次访问这个数据块的时候才格式化这个块。

所以我们又需要一条水位线，用来标示已经被格式化的块。

这条水位线就叫做低HWM。

一般来说，低HWM肯定是低于等于HWM的。

五、修正ORACLE表的高水位线