SQL语句优化.docx

资源描述

SQL语句优化.docx

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

SQL语句优化.docx

SQL语句优化

我们说过一个设计良好的应用程序是不需要优化的，这里的“良好”很大程度上指良好的SQL语句设计，在Oracle数据库应用系统中几乎有80%的性能问题是有糟糕的SQL语句引起的。

☐那么如何写出“良好”的SQL语句呢，我们给出一个条款式的说明，并作简单地讨论。

☐对于对于规模较小的表，如果SQL语句的WHERE子句有GROUPBY、DISTINCT或GROUPBY则对设计的列建立明确的索引。

☐如果SQL语句的WHERE子句在使用索引时比执行全表扫描还要耗时，则不使用索引。

☐在应用程序的SQL语句中不要使用相关子查询，因为随着子查询和主查询中表中行记录的增长，这种查询将极大的消耗CPU资源而尽量使用联机视图重新编写。

☐在SQL语句的where子句中用notexists来代替notin。

☐使用like运算符代替substr函数，因为如’scot%’这样的like运算符将使用索引，而substr函数则使索引无效。

☐如果SQL语句中频繁使用基于某种计算规则的查询并且知道所涉及的列，则创建基于函数的索引。

☐如果查询总是基于主从表关系的行，则对外键建立索引。

☐尽量减少建立索引的时间，可以将sort_area_size设置的足够大，使得建立索引的排序行为都在内存中发生，或者在系统不繁忙时创建大表的索引。

☐上述条款式说明的都基于减少I/O和更有效的内存使用，通过减少系统I/O和有效利用内存和CPU的使用减少系统压力，增加系统对用户的响应时间。

了解了这些基本规则后，我们介绍如何使用工具获得SQL的执行计划。

☐获得SQL语句的执行计划。

SQL语句执行计划就是解释SQL语句的执行步骤，在Oracle中使用explainplanfor指令来获得SQL语句的执行计划，也可以使用autotrace执行获得SQL语句的执行过程和相关的统计信息，如物理读的数据量、磁盘内排序的数据量等。

☐使用EXPLAINPLANFOR命令

在使用该指令时，必须先使用一个脚本文件utlxplan.sql来创建plan_table表来存储使用explainplanfor语句获得的SQL语句的分析结果。

☐执行脚本文件utlxplan.sql。

SQL>@F:

\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxplan.sql

☐查看表PLAN_TABLE的结构

SQL>descPLAN_TABLE

☐通过explainplanfor指令分析SQL语句的执行计划。

SQL>explainplanfor

2selectcount（*）fromscott.emp;

☐2TABLEACCESSFULLEMP1

☐查看表PLAN_TABLE中的SQL语句执行计划信息。

SQL>selectid,operation,options,object_name,position

2fromplan_table;

IDOPERATIONOPTIONSOBJECT_NAMEPOSITION

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

0SELECTSTATEMENT3

1SORTAGGREGATE1

2TABLEACCESSFULLEMP1

☐从上例的输出中可以看出SQL语句的执行过程，我们分析最后一行，ID说明步骤标识，OPERATION为TABLEACCESS说明该步骤的行为是访问表，OPTIONS为FULL说明，使用全表扫描访问表，OBJECT_NAME说明行为的对象为表EMP。

在分析一个SQL语句时，经常使用该指令判断是否使用适当的索引完成表的访问，从而适当的修改索引或创建索引来优化SQL语句的执行。

☐使用AUTOTRACE指令可以跟踪SQL语句并分析其执行。

☐使用AUTOTRACE指令可以跟踪SQL语句并分析其执行步骤，统计信息如物理读数据量，磁盘和内存排序数据量。

但是要执行该指令需要设置几个参数。

☐SQL_TRACE：

该参数说明是否启动对SQL语句的追踪。

默认该参数为FALSE，要启用AUTOTRACE功能需要将参数SQL_TRACE设置为TRUE，该参数可以动态改变。

注意，在不需要追踪SQL语句时，最好将该参数设置为FALSE，因为它会造成跟踪所有执行的SQL语句，这样会产生大量的TRC文件，对磁盘空间有一定的冲击

☐USER_DUMP_DEST：

该参数说明SQL语句追踪文件的记录位置，在笔者的计算机上其默认目录为f:

\app\administrator\diag\rdbms\orcl\orcl\trace。

☐TIMED_STATISTICS：

该参数可以使用ALTERSYSTEM或ALTERSESSION动态设置。

默认参数值为TRUE。

☐所以，我们只需要设置参数SQL_TRACE来启动对SQL语句执行的追踪。

如下例所示。

☐设置参数SQL_TRACE启动SQL语句追踪。

SQL>altersystemsetsql_trace=true;

☐使用AUTOTRACE追踪SQL语句执行计划。

☐SQL>setautotracetraceonly;

☐SQL>selectcount（*）fromscott.emp;

☐执行计划

☐----------------------------------------------------------

☐Planhashvalue:

2083865914

☐-------------------------------------------------------------------

☐-------------------------------------------------------------------

☐|0|SELECTSTATEMENT||1|3（0）|00:

00:

01|

☐|1|SORTAGGREGATE||1|||

☐|2|TABLEACCESSFULL|EMP|56|3（0）|00:

00:

01|

☐-------------------------------------------------------------------

☐统计信息

☐----------------------------------------------------------

☐224recursivecalls

☐0dbblockgets

☐28consistentgets

☐5physicalreads

☐0redosize

☐408bytessentviaSQL*Nettoclient

☐385bytesreceivedviaSQL*Netfromclient

☐2SQL*Netroundtripsto/fromclient

☐4sorts（memory）

☐0sorts（disk）

☐1rowsprocessed

☐AUTOTRACE结果分两部分，一部分是SQL语句的执行计划，一部分是统计信息。

从执行计划可以清楚看出SQL语句的执行步骤，访问的对象以及消耗的CPU，如果是表，还记录访问的表的行数。

统计信息显示了更具体的数据访问和磁盘访问的细节，如物理读数据量，重做数据量，迭代访问传送到客户端的数据量，以及客户端传递给数据库服务器的数据量，内存排序的数据量，以及磁盘排序的数据量，其实如果此处出现磁盘排序，即sorts（disk）的值不为0，或许需要调整PGA中SORT_AREA_SIZE的尺寸（排序区自动管理的情况下），或调整PGA_AGGREGATE_TARGET参数以增减PGA的尺寸，使得排序行为尽可能在内存完成。

☐下面我们详细介绍统计信息中每一行的含义。

☐recursivecalls：

递归调用的次数。

☐dbblockgets：

读数据块的数量。

☐consistentgets：

总的逻辑I/O。

☐physicalreads：

物理I/O。

☐redosize：

重做数量。

☐bytessentviaSQL*Nettoclient：

SQL*Net通信。

☐bytesreceivedviaSQL*Netfromclient。

☐SQL*Netroundtripsto/fromclient。

☐sorts（memory）：

内存排序统计。

☐sorts（disk）：

磁盘排序统计。

☐rowsprocessed：

被检索的行数。

☐在不需要使用AUTOTRACE时，将该功能关闭，不然所有接下来执行的SQL语句都会被追踪分析。

如下所示，关闭AUTOTRACE功能。

例子:

关闭AUTOTRACE。

SQL>setautotraceoff;

☐对于用户经常使用的查询，尤其是大表的查询，在程序设计时应该对这些问题做出预测，并要求对这样的查询尽量使用索引，这样通常可以加快查询速度，减少系统I/O，但是并不是所有的系统都经过“精细”的需求分析，如果遇到这样的情况，发现某个SQL语句总是使用全变扫描实现用户的查询，则需要通过建立索引，加快查询速度，这是DBA可以介入的优化SQL语句的方法，我们使用EXPLAINPLANFOR分析语句的执行中的全表扫描行为。

☐找到造成等待的SQL语句。

☐使用explainplanfor分析SQL语句，判断是否使用索引。

☐如果没有索引，则根据调优需求建立索引。

☐继续使用explainplanfor分析SQL语句，判断是否使用索引。

☐Oracle的SGA是指系统全局区，它是数据库运行期间使用的一段公有内存，即所有使用数据库的用户都可以访问这部分内存，它包括共享池、重做日志缓冲区、数据库缓存高速缓冲区、java池、大池以及流池组成。

其实优化SGA就是调整这些数据库组件的参数，提高系统的运行效率，比如提高用户查询的响应事件。

☐Lock_sga的含义及优化

☐该参数的作用是将SGA锁定（lock）在物理内存内，这样就不会发生SGA使用虚拟内存的发生，显然这样可以提高数据的读取速度，记住磁盘I/O操作永远是尽量避免或减少的。

该参数的默认值为FALSE，即不将SGA锁定在内存中。

下面，我们修改参数lock_sga为true。

如下所示。

☐例子28-39设置参数LOCK_SGA为TRUE。

☐SQL>altersystemsetlock_sga=truescope=spfile;

☐系统已更改。

该参数是静态参数，需要重启数据库才可生效。

☐Pre_page_sga的含义及优化。

☐该参数的作用是启动数据库实例时，将整个SGA读入物理内存，对于内存充足的系统而言，这样显然可以提高系统运行效率。

我们修改该参数为TRUE。

如下例所示。

☐例子28-40设置参数PRE_PAGE_SGA为TRUE。

☐SQL>altersystemsetpre_page_sga=truescope=spfile;

☐sga_target的含义及优化。

☐在Oracle10g以及以上的版本中，提供了内存的自动管理，这样Oracle可以根据业务需要和服务器自身的软硬件环境自动调整一些内存参数。

参数SGA_TARGET就是决定是否使用SGA自动管理，该参数的默认值和系统的SGA_MAX_SIZE一样大，当该参数值不为0时，则启动SGA的自动管理，该参数可以动态修改，下面我们修改该参数的值为700M。

如下所示。

☐例子28-43修改参数SGA_TARGET的值。

☐SQL>altersystemsetsga_target=700M;

☐系统已更改。

☐读者可以如例子28-718所示自行查看修改结果，这里不再给出查询结果，

☐既然SGA可以自动管理，但不是所有的内存组件都可以自动调整，那么那些SGA的内存是可以自动调整呢，下面是SGA可以自动调整的内存组件。

☐共享池。

☐java池。

☐大池。

☐数据库缓冲区。

☐流池。

☐将程序常驻内存

在Oracle数据库中有一个软件包dbms_shared_pool，它提供过程keep和unkeep将用户经常使用的程序如存储过程、触发器、序列号、游标以及JAVASOURCE等数据库对象长期保存在一个内存结构中，这个内存区就是共享池（sharedpool），对于用户频繁使用的这些数据库对象而言，将其常驻内存可以减少磁盘I/0从而减少用户的响应时间

☐将程序常驻内存

创建软件包DBMS_SHARED_POOL

SQL>F:

\app\Administrator\product\11.1.0\db_1\RDBMS\ADMIN;

☐将程序常驻内存的过程

☐查看用户HR拥有的存储过程。

SQL>selectobject_name,object_type

2fromdba_objects

3whereobject_type='PROCEDURE'

4andowner='HR';

OBJECT_NAMEOBJECT_TYPE

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

SECURE_DMLPROCEDURE

ADD_JOB_HISTORYPROCEDURE

☐将过程secure_dml常驻内存

SQL>EXECUTEdbms_shared_pool.keep（'HR.SECURE_DML'）;

☐用户HR的过程SECURE_DML清除出内存。

SQL>EXECUTEdbms_shared_pool.unkeep（'HR.SECURE_DML'）;

☐将数据常驻内存

在生产数据库中，为了提高用户的访问速度对于经常使用的表，可以使其常驻内存中，这样避免了对该表访问时产生频繁的磁盘I/O行为，这样可以提高用户的访问响应时间，虽然造成一定的内存占用，但是使用内存访问确实提高了访问的响应时间，在某种程度上是有效的。

而当不需要频繁访问该表时，DBA可以将其从内存中清除。

本节我们再次学习Oralce的各种数据块的缓存池，通过分析了解将数据常驻内存的必要性，和可行性。

然后给出一个具体的例子将SCOTT用户的EMP表和一个索引常驻内存

☐将数据常驻内存

再论数据块缓存池

☐在Oracle数据库体系结构的介绍中，读者已经知道在数据库块写到磁盘文件之前，或者从磁盘文件读取数据之后，首先需要将数据块缓存在数据库高速缓存中，所以需要适当设置该缓冲区的大小以满足用户需求。

在Oracle8之后的版本中，用户可以把SGA中段的已缓存块放在3个缓冲池中。

☐默认池（defaultpool）：

所有的段都放在这个池中，即原先的缓冲区池，如果没有指定数据的缓存位置，默认将数据缓存在这个池中。

☐保持池（keeppool）：

对于用户频繁访问的数据如表或索引等数据库对象的数据块可以放置在这个候选的缓冲区池中。

放在默认池中的数据块，虽然可以频繁访问，但是这些段数据会老化而退出默认池，所以最好放置在保持池中，使得数据可以长久保存。

回收池recyclepool：

对于随机访问的大段可以放在这个缓冲区池中，因为大的数据段会很快老化退出缓冲池，导致缓冲区的频繁刷新输出，所以需要将随机访问的大段放置这个缓冲区池中。

☐将数据常驻内存的过程

我们假设将用户SCOTT的SALGRADE表以及表EMP中建立的基于函数的索引SCOTT_EMP_INCOME_IDX常驻保持池中，我们给出操作步骤：

1、查看表SALGRADE和索引SCOTT_EMP_INCOME_IDX的块大小

SQL>selectsegment_name,segment_type,blocks

2fromdba_segments

3whereowner='SCOTT'

4*andsegment_namein（'SALGRADE','SCOTT_EMP_INCOME_IDX'）

2、数据字典dba_segments是静态数据字典，如果需要获得最新的段统计信息，需要使用ANALYZE指令收集统计信息如下所示

SQL>analyzeindexscott.SCOTT_EMP_INCOME_IDXcomputestatistics;

SQL>analyzetablescott.salgradecomputestatistics;

3、查询的数据库块大小。

SQL>showparameterdb_block_size;

NAMETYPEVALUE

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

db_block_sizeinteger8192

4、计算要保存的表和索引的大小。

SQL>select（8+8）*8Kbytesfromdual;

KBYTES

----------

128

设置保持池的大小。

SQL>connectsystem/oracle@orclassysdba

已连接。

SQL>altersystemsetdb_keep_cache_size=10M;

6、将表SALGRADE设置为常驻内存。

SQL>altertablesalgrade

2storage（buffer_poolkeep）;

7、将索引scott_emp_income_idx设置为常驻内存。

SQL>alterindexscott_emp_income_idx

2storage（buffer_poolkeep）;

☐计算机的输入输出即I/O是很耗时的系统行为，I/O优化就是通过一定的措施减少I/O消耗的时间，从本质上讲优化I/O的方法无非两种，一是最大化地减少I/O操作从而减少操作的数据量，二是平衡I/O，如平衡数据库中各种文件的磁盘分布，它也同时减少了对统一磁盘文件的操作，减少了I/O量。

本章讲述的I/O优化就是基于以上两点，只是针对不同粒度的数据库组件优化的具体方法不同。

☐在处理I/O优化问题之前，我们首先需要确认优化的对象是什么，那么如何确认这个对象呢？

答案是使用数据字典视图，所以在面临Oracle的I/O相关的性能问题时，应该使用视图v$system_event，v$session_event，v$session_wait视图来总和事件信息、会话信息以及等待事件相关的信息提供有关和I/O相关的性能瓶颈。

这里再次强调读者在实施Oracle数据库优化时，应该对常用的等待事件比较熟悉，这样对于等待事件就可以初步判断导致等待的原因，采取迭代的方式实现优化。

读者应注意一个问题，就是磁盘I/O的竞争是不可避免的，问题是这些磁盘I/O争用发生的频率，以及对于系统运行造成的影响，只有严重的影响数据库系统性能的I/O竞争才采取I/O优化措施。

下面介绍不同的数据库组件的优化原则或具体方法。

☐表空间I/O优化

☐在安装Oracle数据库时，所有表空间的数据文件都存放在相同的目录下，显然也都位于同一个物理磁盘上。

显然这样的设置是不合理的，需要从I/O的角度进行优化，如将不同的表空间数据文件分布到不同的磁盘等。

☐Oracle数据库在逻辑上划分成多个表空间，而表空间中包含物理的数据文件，这些文件是Oracle格式的操作系统文件，通过设计不同的表空间从而存放和管理不同的数据库文件。

下面分类介绍这些表空间、表空间作用以及相关的优化问题

☐表空间I/O优化

☐系统表空间

☐系统表空间主要用于存放数据库字典信息，但是在默认情况下用户数据也存储在系统表空间中，如果将用户数据和数据字典存放在相同的表空间显然存在磁盘I/O竞争，所以系统表空间中只存放和管理数据字典信息，对于用户数据可以创建用户表空间来单独存储。

☐表空间I/O优化

☐还原表空间

☐还原表空间主要用户回滚用户更改的数据，如对于用户删除一个大表时（DROP），此时会产生大量的还原数据，对还原表空间造成一定得维护压力，而且对于OLTP系统，用户会频繁的更改表数据，所以还原表空间会不断的填充数据和释放磁盘空间，这样就比较容易产生磁盘碎片，所以还原表空间应该只用于还原数据，而不要用作其他用途。

☐表空间I/O优化

☐临时表空间

临时表空间完成数据的磁盘排序行为，显然排序行为是数据库操作中比较频繁的一个数据库行为，这样就容易造成临时段的频繁的分配和释放，也容易造成磁盘碎片，所以临时表空间不能存放永久数据，也不要存放如索引等其他数据库对象。

☐表空间I/O优化

☐用户表空间

实际的数据库系统肯定需要存储用户数据，主要是表数据，而表数据是应用程序或用户频繁操作的数据，所以需要单独建立一个表空间，关键是表空间中的数据文件要存储在单独的磁盘上，如果系统硬件资源不足，可以存储在I/O操作不频繁的磁盘上以减少I/O竞争产生的I/O等待。

除了用户表空间，对于大型的表还需要建立索引，而索引和用户表最好放在不同的磁盘驱动器上，显然使用表的索引与搜索表数据同样存在竞争，所以最好创建索引表空间。

即用户表空间我们分为用户数据表空间和用户索引表空间，将二者的数据文件存储在不同的磁盘上，可以有效地减少磁盘I/O从而优化I/O行为。

☐表空间I/O优化

☐大对象表空间

☐大对象表空间是拥有大的数据库对象的表空间，该表空间的尺寸根据数据库块尺寸的不同分布范围为8Tb~128Tb。

Oracle数据库在处理数据时都需要将表数据从数据文件所在磁盘读取到数据库高速缓冲区中，这个过程是机械行为，所以相对于内存行为要消耗更多的时间，所以对于大对象表空间尤其需要考虑I/O优化，对于大对象表空间应该使用更大的块尺寸，使得每次读取数据时可以获得比小的块尺寸更多的数据，减少了磁盘I/O。

☐数据文件I/O优化

☐数据文件优化：

存放用户实际使用的表数据或索引等数据库对象，尤其对于大型的数据

展开阅读全文