telant培训材料-oracle数据库优化措施及其建议

Telant培训材料Oracle数据库优化措施及其建议RSG1-TD 资源产品一部，应用开发部元数据维护和发布文档国信朗讯科技网络技术有限公司 1修改记录版本 作者 版本描述 日期1.0 王晓燕 初稿 2009-11-04国信朗讯科技网络技术有限公司 2目录修改记录 1目录 21 问题的提出 42 SQL 语句编写注意问题 .42.1 IS NULL 与 IS NOT NULL42.2 联接列 42.3 带通配符（%）的 like 语句 52.4 Order by 语句 52.5 NOT .52.6 IN 和 EXISTS62.7 使用函数 62.8 比较不匹配的数据类型 73 索引 73.1 Oracle 索引建立机制 73.2 Oracle 位图索引 73.3 清除 Oracle 中无用索引 .84 SQL 语句的执行步骤 .85 Oracle 百万级别以上数据的分页查询和优化 .116 用 Oracle 动态性能视图采集查询调优数 117 采用存储过程来实现功能 137.1 使用存储过程的场景 137.2 使用存储过程的优点 13国信朗讯科技网络技术有限公司 31 问题的提出在应用系统开发初期，由于开发数据库数据比较少，对于查询 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 创建的索引没有使用。 国信朗讯科技网络技术有限公司 4当采用下面这种 SQL 语句的编写，Oracle 系统就可以采用基于 last_name创建的索引。 Select *From EmployeeWhere First_Name = BillAnd Last_Name = Cliton; 遇到下面这种情况又如何处理呢？如果一个变量（name）中存放着 Bill Cliton 这个员工的姓名，对于这种情况我们又如何避免全程遍历，使用索引呢？可以使用一个函数，将变量 name 中的姓和名分开就可以了，但是有一点需要注意，这个函数是不能作用在索引列上。下面是 SQL 查询脚本： Select *From EmployeeWhere First_Name = Substr(）运算符。换句话说，即使不在查询 where 子句中显式地加入 NOT 词，NOT 仍在运算符中，见下例： . Where Status 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 查询效率更高。国信朗讯科技网络技术有限公司 62.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 统一建立索引。一般来说，不是很复杂的查询达到秒级以上就需要考虑是不是索引的问题了。对于复杂的查询语句，也建议发给数据库开发人员进行脚本优化。国信朗讯科技网络技术有限公司 73.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.SqlSelectalter index | Owner | . | Index_Name | monitoring usage;From Dba_IndexesWhere 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 使用了这个索引多少次，但是这个工具对于找出没有使用的索引还是很有用的。国信朗讯科技网络技术有限公司 84 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 强制。国信朗讯科技网络技术有限公司 9其次，检查被索引的列或组合索引的首列是否出现在 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 语国信朗讯科技网络技术有限公司 10句中那些需要返回 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、内存、网络带宽。国信朗讯科技网络技术有限公司 116 用 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 语句，