elant培训材料-Oracle数据库优化措施及其建议.doc

Telant培训材料Oracle数据库优化措施及其建议RSG1-TD 资源产品一部，应用开发部修改记录版本作者版本描述日期1.0王晓燕初稿2009-11-04目录修改记录1目录21问题的提出42SQL语句编写注意问题42.1IS NULL 与 IS NOT NULL42.2联接列42.3带通配符（%）的like语句52.4Order by语句52.5NOT52.6IN和EXISTS62.7使用函数62.8比较不匹配的数据类型73索引73.1Oracle索引建立机制73.2Oracle位图索引73.3清除Oracle中无用索引84SQL语句的执行步骤85Oracle百万级别以上数据的分页查询和优化116用Oracle动态性能视图采集查询调优数117采用存储过程来实现功能137.1使用存储过程的场景137.2使用存储过程的优点131 问题的提出在应用系统开发初期，由于开发数据库数据比较少，对于查询SQL语句，复杂视图的编写等体会不出SQL语句各种写法的性能优劣，但是如果将应用系统提交实际应用后，随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于海量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍，可见对于一个系统不是简单地能实现其功能就可，而是要写出高质量的SQL语句，提高系统的可用性。 在多数情况下，Oracle使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。但是，如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器删去索引而使用全表扫描，一般就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种原则来删除索引，这有助于写出高性能的SQL语句。本文档给开发人员和DB做参考。2 SQL语句编写注意问题下面就某些SQL语句的where子句编写中需要注意的问题作详细介绍。在这些where子句中，即使某些列存在索引，但是由于编写了劣质的SQL，系统在运行该SQL语句时也不能使用该索引，而同样使用全表扫描，这就造成了响应速度的极大降低。2.1 IS NULL 与 IS NOT NULL 不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。 任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。 2.2 联接列 对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。我们一起来看一个例子，假定有一个职工表（employee），对于一个职工的姓和名分成两列存放（FIRST_NAME和LAST_NAME），现在要查询一个叫比尔.克林顿（Bill Cliton）的职工。 下面是一个采用联接查询的SQL语句， Select * From Employss Where First_Name| |last_name =Bill Cliton;上面这条语句 完全可以查询出是否有Bill Cliton这个员工，但是这里需要注意，系统优化器对基于last_name创建的索引没有使用。 当采用下面这种SQL语句的编写，Oracle系统就可以采用基于last_name创建的索引。 Select *From Employee Where First_Name = Bill And Last_Name = Cliton; 遇到下面这种情况又如何处理呢？如果一个变量（name）中存放着Bill Cliton这个员工的姓名，对于这种情况我们又如何避免全程遍历，使用索引呢？可以使用一个函数，将变量name中的姓和名分开就可以了，但是有一点需要注意，这个函数是不能作用在索引列上。下面是SQL查询脚本： Select *From Employee Where First_Name = Substr(&name,1,Instr(&name, ) - 1) And Last_Name = Substr(&name,Instr(&name, ) + 1)2.3 带通配符（%）的like语句 同样以上面的例子来看这种情况。目前的需求是这样的，要求在职工表中查询名字中包含cliton的人。可以采用如下的查询SQL语句： Select * From Employee Where Last_Name Like %cliton% 这里由于通配符（%）在搜寻词首出现，所以Oracle系统不使用last_name的索引。在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用： Select * From Employee Where Last_Name Like c% 2.4 Order by语句ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中（像联接或者附加等）。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。 仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。 2.5 NOT我们在查询时经常在where子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用and（与）、or（或）以及not（非）。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子： . . Where Not(Status = VALID)如果要使用NOT，则应在取反的短语前面加上括号，并在短语前面加上NOT运算符。NOT运算符包含在另外一个逻辑运算符中，这就是不等于（;）运算符。换句话说，即使不在查询where子句中显式地加入NOT词，NOT仍在运算符中，见下例： . . Where Status INVALID 再看下面这个例子： Select * From Employee Where Salary 3000;对这个查询，可以改写为不使用NOT： Select * From Employee Where Salary 3000; 虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。 2.6 IN和EXISTS 有时候会将一列和一系列值相比较。最简单的办法就是在where子句中使用子查询。在where子句中可以使用两种格式的子查询。 第一种格式是使用IN操作符： . . Where Column In (Select * From . . Where . .); 第二种格式是使用EXIST操作符： . . Where Exists(Select 1 From . .Where . .);我相信绝大多数人会使用第一种格式，因为它比较容易编写，而实际上第二种格式要远比第一种格式的效率高。在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。 第二种格式中，子查询以select 1开始。运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作（这里假定在where语句中使用的列存在索引）。相对于IN子句来说，EXISTS使用相连子查询，构造起来要比IN子查询困难一些。 通过使用EXIST，Oracle系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间。Oracle系统在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因。 同时应尽可能使用NOT EXISTS来代替NOT IN，尽管二者都使用了NOT（不能使用索引而降低速度），NOT EXISTS要比NOT IN查询效率更高。2.7 使用函数如果不使用基于函数的索引，那么在SQL语句的WHERE子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。 下面的查询不会使用索引（只要它不是基于函数的索引）。 Select Empno, Ename, Deptno From Emp Where to_char(Hiredate,yyyy-mm-dd) = 2009-03-05;把上面的语句改成下面的语句，这样就可以通过索引进行查找。Select Empno, Ename, Deptno From Emp Where Hiredate To_date(2009-03-04,yyyy-mm-dd);2.8 比较不匹配的数据类型比较不匹配的数据类型也是比较难于发现的性能问题之一。注意下面查询的例子，account_number是一个VARCHAR2类型，在account_number字段上有索引。下面的语句将执行全表扫描。Select Bank_Name, Address, City, State, Zip From Banks Where Account_Number = 990354;Oracle可以自动把where子句变成To_Number(Account_Number) = 990354，这样就限制了索引的使用,改成下面的查询就可以使用索引：Select Bank_Name, Address, City, State, Zip From Banks Where Account_Number = 990354;特别注意：不匹配的数据类型之间比较会让Oracle自动限制索引的使用，即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描”。3 索引Q：为什么Oracle有时会用索引来查找数据？A：在你运用SQL语言，向数据库发布一条查询语句时，Oracle将伴随产生一个“执行计划”，也就是该语句将通过何种数据搜索方案执行，是通过全表扫描、还是通过索引搜寻等其它方式。搜索方案的选用与Oracle的优化器息息相关。3.1 Oracle索引建立机制 在建表初期，数据库开发人员就会对表的一些关键查询条件：比如name，code，metecategory以及外键列上做索引。但是很多跟实际业务使用场景相关的查询条件并不会考虑到很全面（比如函数索引），所以就要求开发人员给出需要建立索引的请求，由DB统一建立索引。一般来说，不是很复杂的查询达到秒级以上就需要考虑是不是索引的问题了。对于复杂的查询语句，也建议发给数据库开发人员进行脚本优化。3.2 Oracle位图索引 适合于有大量重复数据的列（例如员工表的部门编号列，部门编号大量重复） 位图索引只能创建在非唯一性列上，唯一性unique索引只能使用B-tree索引且必须是全局global的或Local prefixed的 位图索引不仅是在低基列上创建，在数据仓库环境里高基列上的位图索引性能也优于B-tree索引 在数据仓库环境里，除非唯一性索引，创建位图索引是首选 位图索引包含null值，而B-tree索引不包含null值 分区表上只能创建本地local的位图索引，位图索引不能是全局global的位图连接索引Bitmap Join Indexes：Create Bitmap Index idx_t_x on t(x);3.3 清除Oracle中无用索引DML性能低下，其中最严重的原因之一是无用索引的存在。所有SQL的插入、更新和删除操作在它们需要在每一行数据被改变时修改大量索引的时候会变得更慢。 许多Oracle管理人员只要看见在一个SQL 查询的WHERE语句出现了一列的话就会为它分配索引。虽然这个方法能够让SQL运行得更快速，但是基于功能的Oracle索引使得数据库管理人员有可能在数据表的行上过度分配索引。过度分配索引会严重影响关键Oracle数据表的性能。 在Oracle9i出现以前，没有办法确定SQL查询没有使用的索引。Oracle9i有一个工具能够让你使用ALTER INDEX命令监视索引的使用。然后你可以查找这些没有使用的索引并从数据库里删除它们。 下面是一段脚本，它能够打开一个系统中所有索引的监视功能： Set Pages 999;Set Heading Off;Spool run_Monitor.SqlSelect alter index | Owner | . | Index_Name | monitoring usage; From Dba_Indexes Where Owner Not In (SYS, SYSTEM, PERFSTAT);Spool Off;你需要等待一段时间直到在数据库上运行了足够多的SQL语句以后，然后你就可以查询新的V$OBJECT_USAGE视图: Select index_name,table_name,monitoring,used from v$object_usage;在V$OBJECT_USAGE有一列被称作USED，它的值是YES或者NO，它不会告诉你Oracle使用了这个索引多少次，但是这个工具对于找出没有使用的索引还是很有用的。4 SQL语句的执行步骤一条SQL语句的处理过程要经过以下几个步骤：1 语法分析 分析语句的语法是否符合规范，衡量语句中各表达式的意义。 2 语义分析 检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限。 3 视图转换 将涉及视图的查询语句转换为相应的对基表查询语句。 4 表达式转换 将复杂的SQL表达式转换为较简单的等效连接表达式。 5 选择优化器 不同的优化器一般产生不同的“执行计划”。6 选择连接方式 Oracle有三种连接方式，对多表连接Oracle可选择适当的连接方式。7 选择连接顺序 对多表连接Oracle选择哪一对表先连接，选择这两表中哪个表做为源数据表。 8 选择数据的搜索路径 根据以上条件选择合适的数据搜索路径，比如选用全表搜索还是利用索引或是其他的方式。 9 运行“执行计划” Oracle有两种优化器：基于规则的优化器（RBO， Rule Based Optimizer），和基于代价的优化器（CBO， Cost Based Optimizer）。 CBO自Oracle 7版被引入，Oracle自7版以来采用的许多新技术都是基于CBO的，如星型连接排列查询，哈希连接查询，和并行查询等。CBO计算各种可能“执行计划”的“代价”，即cost，从中选用cost最低的方案，作为实际运行方案。各“执行计划”的cost的计算根据，依赖于数据表中数据的统计分布，Oracle数据库本身对该统计分布并不清楚，须要分析表和相关的索引，才能搜集到CBO所需的数据。 较典型的问题有：有时，表明明建有索引，但查询过程显然没有用到相关的索引，导致查询过程耗时漫长，占用资源巨大，问题到底出在哪儿呢？按照以下顺序查找，基本上能发现原因所在。查找原因的步骤： 首先，检查表是否做过统计分析。对数据经常有增、删、改的表最好定期对表和索引进行分析，我们提供了一个优化分析的过程，只要定期执行即可。Oracle掌握了充分反映实际的统计数据，才有可能做出正确的选择。除非在语句中用hint强制。其次，检查被索引的列或组合索引的首列是否出现在PL/SQL语句的WHERE子句中，这是“执行计划”能用到相关索引的必要条件。 第三，看采用了哪种类型的连接方式。Oracle共有Sort Merge Join（SMJ）、Hash Join（HJ）和Nested Loop Join（NL）3种连接方式。对于被连接的数据子集较小的情况，nested loop连接是个较好的选择。nested loop就是扫描一个表，每读到一条记录，就根据索引去另一个表里面查找，没有索引一般就不会是 nested loops。hash join是CBO 做大数据集连接时的常用方式。优化器扫描小表（或数据源），利用连接键（也就是根据连接字段计算hash 值）在内存中建立hash表，然后扫描大表，每读到一条记录就来探测hash表一次，找出与hash表匹配的行。sort merge join在9i开始已经很少出现了，因为其排序成本高，大多为hash join替代了。 通常情况下hash join的效果都比sort merge join要好。第四，看连接顺序是否允许使用相关索引。假设表emp的deptno列上有索引，表dept的列deptno上无索引，Where语句有emp.deptno=dept.deptno条件。在做NL连接时，emp做为外表，先被访问，由于连接机制原因，外表的数据访问方式是全表扫描，emp.deptno上的索引显然是用不上，最多在其上做索引全扫描或索引快速全扫描。第五，是否用到系统数据字典表或视图。由于系统数据字典表都未被分析过，可能导致极差的“执行计划”。但是不要擅自对数据字典表做分析，否则可能导致死锁，或系统性能下降。 第六，索引列是否函数的参数。如是，索引在查询时用不上。 第七，是否存在潜在的数据类型转换。如将字符型数据与数值型数据比较，Oracle会自动将字符型用to_number()函数进行转换，从而导致第六种现象的发生。 第八，索引列的选择性不高。 我们假设典型情况，有表emp，共有一百万行数据，但其中的emp.deptno列，数据只有4种不同的值，如10、20、30、40。虽然emp数据行有很多，Oracle缺省认定表中列的值是在所有数据行均匀分布的，也就是说每种deptno值各有25万数据行与之对应。假设SQL搜索条件DEPTNO=10，利用deptno列上的索引进行数据搜索效率，往往不比全表扫描的高，ORACLE理所当然对索引“视而不见”，认为该索引的选择性不高。 但我们考虑另一种情况，如果一百万数据行实际不是在4种deptno值间平均分配，其中有99万行对应着值10，5000行对应值20，3000行对应值30，2000行对应值40。在这种数据分布图案中对除值为10外的其它deptno值搜索时，毫无疑问，如果索引能被应用，那么效率会高出很多。第九，索引列值是否可为空（NULL）。如果索引列值可以是空值，在SQL语句中那些需要返回NULL值的操作，将不会用到索引，如COUNT（*），而是用全表扫描。这是因为索引中存储值不能为全空。 第十，看是否有用到并行查询（PQO）。并行查询将不会用到索引。不过并行查询并不适合OLTP系统，因为会占用大量的CPU，数据仓库抽取数据的时候可以考虑。 第十一，看PL/SQL语句中是否有用到bind变量。由于数据库不知道bind变量具体是什么值，在做非相等连接时，如“”,“like”等，Oracle将引用缺省值，在某些情况下会对执行计划造成影响。如果从以上几个方面都查不出原因的话，我们只好用采用在语句中加hint的方式强制Oracle使用最优的“执行计划”。 hint采用注释的方式，有行注释和段注释两种方式。如我们想要用到A表的IND_COL1索引的话，可采用以下方式： Select /*+ INDEX(A IND_COL1)*/ * From a Where Col1 = Xxx;注意，注释符必须跟在SELECT之后，且注释中的“+”要紧跟着注释起始符“/*”或“-”，否则hint就被认为是一般注释，对PL/SQL语句的执行不产生任何影响。5 Oracle百万级别以上数据的分页查询和优化一、使用2次查询来实现分页：1. 获取总记录数: Select Count(*) From t Where 2. 利用Oracle的rownum获取指定页的数据Select *From (Select Row_. *, Rownum Rownum_ From (Select * From t Where ? ?) Row_ Where Rownum ?（根据上一步得到的总记录数以及每页行数、当前页码计算出起始行号和结束行号，设置在上述SQL语句的“？”处）二、对于大表的一般性优化措施：1. 表结构设计原则：数据类型尽量小（占用空间少，磁盘读入较快）、单独设置表空间2. 应用程序：合理设计业务、SQL语句优化3. 合理使用索引：只创建需要的索引，根据需要使用多列索引，存放于单独的表空间4. 数据库设置：内存分配、查询优化器、分区、分表等5. 硬件环境：硬盘、CPU、内存、网络带宽。6 用Oracle动态性能视图采集查询调优数对于现在的一些发行版本，DBA(Database Administrator，数据库管理员)和开发员可以访问的已经有三种动态性能视图了，分别为V$SQL、V$SQLAREA，还有V$SQLTEXT。这些视图可以用来采集有关SQL命令执行的统计信息。在Oracle 10g, Release 2 中，还增加了第四个动态性能视图，V$SQLSTATS，通过它能更方便地访问这类数据。和静态数据字典视图（static dictionary view，也就是前缀为USER_、ALL_，或者DBA_的视图）不同，动态性能视图会随着系统的运行而不断更新。这使得有可能在SQL语句执行之时监视其性能。和静态视图一样的是，要使用它们你要先获得许可。对于非数据库管理员用户（如典型的开发环境下）可赋予SELECT_CATALOG_ROLE权限，让他们可以从中做出选择。下面是各个视图所提供功能的一些简要描述: V$SQL: 这个视图使用一个CLOB(character large object，字符型巨对象)column（栏，也就是视图中的属性字段），以提供SQL语句的完整文本，此外还有一列最多存放1000个 VARCHAR2字符（存放SQL语句的前1000个字符）的对象，这方便了使用。可访问的统计数据相当广泛：包括解析语句（parse）和非法语句的数 目、磁盘的读写次数、运行时间、等待时间，还有optimizer（优化器）数据。你还可以从中知道创建语句的用户和schema(部署对象)，以及当前 有多少用户正在执行它。V$SQLAREA: 这个视图包含许多和V$SQL相同的统计信息。可是，V$SQL对每条初始SQL语句及其子语句（child）都要包含一行统计信息，而这个视图只对实际输入的SQL字串产生一条统计信息。V$SQLSTATS:这是10gR2版中新加入的视图，这个视图比V$SQL和V $SQLAREA更快更方便，它只包含其它视图column的子集。一般来说，它不连接用户信息。它的每一行对应一条SQL语句及其执行计划的hash value(杂凑值)。它的额外的优点是，这个视图中的纪录入口比其它视图更为持久。因此，虽然某一条语句已经在共享池中过期失效（这导致它从V$ SQLAREA中消失），但你仍然可以通过该视图找到它。V$SQLTEXT: 有时候你会想得到分开的每一行SQL语句，而不是V$SQL中给出的一个巨对象；那 么V$SQL可以让你如愿。你可以通过COMMAND_TYPE栏中的代号选择特定的某种类型的语句（比如，选择INSERT语句，或者SELECT语 句）。SQL_TEXT column中存放的是单个语句，而PIECE column则给语句编上号码，以便通过 ORDER BY来排序。另外有一个叫V$SQLTEXT_WITH_NELINES的变量会保留原来的换行符以改善可读性（V$SQLTEXT会用空格替代换行 符）。V$SQLTEXT和V$SQLTEXT_WITH_NEWLINES都不会告诉你是谁执行了这条语句，或者是谁在提供给你这些统计信息。如果要获 得那类信息，你还得通过V$SQL和V$SQLAREA视图中的ADDRESS和HASH_VALUE column实现。这四个视图工具合到一起，共同为你提供了原始资料，帮助你检测你的SQL数据库执行状况如何。7 采用存储过程来实现功能存储过程就是已经编译好的、优化过的放在数据库服务器中的一些SQL语句，可供应用程序直接调用。综合资源管理系统实现省集中后，对系统的的性能和代码的稳定性要求更高，因此需要开发的代码能够高效的处理业务需求，并能够灵活的根据业务需求做修改。7.1 使用存储过程的场景1、 业务逻辑复杂的功能2、 功能比较独立的模块3、 批处理功能4、 。具体的案例包括拓扑图连线的生成、电路文本路由的查找等等。7.2 使用存储过程的优点1、执行速度比普通的SQL语句快 在运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。 由于执行SQL语句的大部分工作已经完成，所以存储过程能以极快的速度执行。2、便于集中控制 当规则变化时，只需要在数据库的服务器中修改相应的存储过程，而不需要逐个的在应用程序中修改，应用程序保持不变即可，这样就省去了修改应用程序工作量。3、可以降低网络的通信量4、保证数据库的安全性和完整性 通过存储过程不仅可以使没有权限的用户在控制之下间接地存取数据库，保证数据的安全；而且可以使相关的动作在一起发生，从而可以维护数据库的完整性。5、灵活性 存储过程可以用流控制语句编写，具有很强的灵活性，可以完成复杂的判断和运算，可以根据条件执行不同SQL语句。