Telant培训材料Oracle数据库优化措施及其建议.docx
- 文档编号:25395511
- 上传时间:2023-06-08
- 格式:DOCX
- 页数:16
- 大小:34.96KB
Telant培训材料Oracle数据库优化措施及其建议.docx
《Telant培训材料Oracle数据库优化措施及其建议.docx》由会员分享,可在线阅读,更多相关《Telant培训材料Oracle数据库优化措施及其建议.docx(16页珍藏版)》请在冰豆网上搜索。
Telant培训材料Oracle数据库优化措施及其建议
Telant培训材料
Oracle数据库优化措施及其建议
RSG1-TD资源产品一部,应用开发部
修改记录
版本
作者
版本描述
日期
1.0
王晓燕
初稿
2009-11-04
目录
修改记录1
目录2
1问题的提出4
2SQL语句编写注意问题4
2.1ISNULL与ISNOTNULL4
2.2联接列4
2.3带通配符(%)的like语句5
2.4Orderby语句5
2.5NOT5
2.6IN和EXISTS6
2.7使用函数6
2.8比较不匹配的数据类型7
3索引7
3.1Oracle索引建立机制7
3.2Oracle位图索引7
3.3清除Oracle中无用索引8
4SQL语句的执行步骤8
5Oracle百万级别以上数据的分页查询和优化11
6用Oracle动态性能视图采集查询调优数11
7采用存储过程来实现功能13
7.1使用存储过程的场景13
7.2使用存储过程的优点13
1问题的提出
在应用系统开发初期,由于开发数据库数据比较少,对于查询SQL语句,复杂视图的编写等体会不出SQL语句各种写法的性能优劣,但是如果将应用系统提交实际应用后,随着数据库中数据的增加,系统的响应速度就成为目前系统需要解决的最主要的问题之一。
系统优化中一个很重要的方面就是SQL语句的优化。
对于海量数据,劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍,可见对于一个系统不是简单地能实现其功能就可,而是要写出高质量的SQL语句,提高系统的可用性。
在多数情况下,Oracle使用索引来更快地遍历表,优化器主要根据定义的索引来提高性能。
但是,如果在SQL语句的where子句中写的SQL代码不合理,就会造成优化器删去索引而使用全表扫描,一般就这种SQL语句就是所谓的劣质SQL语句。
在编写SQL语句时我们应清楚优化器根据何种原则来删除索引,这有助于写出高性能的SQL语句。
本文档给开发人员和DB做参考。
2SQL语句编写注意问题
下面就某些SQL语句的where子句编写中需要注意的问题作详细介绍。
在这些where子句中,即使某些列存在索引,但是由于编写了劣质的SQL,系统在运行该SQL语句时也不能使用该索引,而同样使用全表扫描,这就造成了响应速度的极大降低。
2.1ISNULL与ISNOTNULL
不能用null作索引,任何包含null值的列都将不会被包含在索引中。
即使索引有多列这样的情况下,只要这些列中有一列含有null,该列就会从索引中排除。
也就是说如果某列存在空值,即使对该列建索引也不会提高性能。
任何在where子句中使用isnull或isnotnull的语句优化器是不允许使用索引的。
2.2联接列
对于有联接的列,即使最后的联接值为一个静态值,优化器是不会使用索引的。
我们一起来看一个例子,假定有一个职工表(employee),对于一个职工的姓和名分成两列存放(FIRST_NAME和LAST_NAME),现在要查询一个叫比尔.克林顿(BillCliton)的职工。
下面是一个采用联接查询的SQL语句,
Select*FromEmployss
WhereFirst_Name||''||last_name='BillCliton';
上面这条语句完全可以查询出是否有BillCliton这个员工,但是这里需要注意,系统优化器对基于last_name创建的索引没有使用。
当采用下面这种SQL语句的编写,Oracle系统就可以采用基于last_name创建的索引。
Select*
FromEmployee
WhereFirst_Name='Bill'
AndLast_Name='Cliton';
遇到下面这种情况又如何处理呢?
如果一个变量(name)中存放着BillCliton这个员工的姓名,对于这种情况我们又如何避免全程遍历,使用索引呢?
可以使用一个函数,将变量name中的姓和名分开就可以了,但是有一点需要注意,这个函数是不能作用在索引列上。
下面是SQL查询脚本:
Select*
FromEmployee
WhereFirst_Name=Substr('&&name',1,Instr('&&name','')-1)
AndLast_Name=Substr('&&name',Instr('&&name','')+1)
2.3带通配符(%)的like语句
同样以上面的例子来看这种情况。
目前的需求是这样的,要求在职工表中查询名字中包含cliton的人。
可以采用如下的查询SQL语句:
Select*FromEmployeeWhereLast_NameLike'%cliton%'
这里由于通配符(%)在搜寻词首出现,所以Oracle系统不使用last_name的索引。
在很多情况下可能无法避免这种情况,但是一定要心中有底,通配符如此使用会降低查询速度。
然而当通配符出现在字符串其他位置时,优化器就能利用索引。
在下面的查询中索引得到了使用:
Select*FromEmployeeWhereLast_NameLike'c%'
2.4Orderby语句
ORDERBY语句决定了Oracle如何将返回的查询结果排序。
Orderby语句对要排序的列没有什么特别的限制,也可以将函数加入列中(像联接或者附加等)。
任何在Orderby语句的非索引项或者有计算表达式都将降低查询速度。
仔细检查orderby语句以找出非索引项或者表达式,它们会降低性能。
解决这个问题的办法就是重写orderby语句以使用索引,也可以为所使用的列建立另外一个索引,同时应绝对避免在orderby子句中使用表达式。
2.5NOT
我们在查询时经常在where子句使用一些逻辑表达式,如大于、小于、等于以及不等于等等,也可以使用and(与)、or(或)以及not(非)。
NOT可用来对任何逻辑运算符号取反。
下面是一个NOT子句的例子:
...WhereNot(Status='VALID')
如果要使用NOT,则应在取反的短语前面加上括号,并在短语前面加上NOT运算符。
NOT运算符包含在另外一个逻辑运算符中,这就是不等于(<>;)运算符。
换句话说,即使不在查询where子句中显式地加入NOT词,NOT仍在运算符中,见下例:
...WhereStatus<>'INVALID'
再看下面这个例子:
Select*FromEmployeeWhereSalary<>3000;
对这个查询,可以改写为不使用NOT:
Select*FromEmployeeWhereSalary<3000OrSalary>3000;
虽然这两种查询的结果一样,但是第二种查询方案会比第一种查询方案更快些。
第二种查询允许Oracle对salary列使用索引,而第一种查询则不能使用索引。
2.6IN和EXISTS
有时候会将一列和一系列值相比较。
最简单的办法就是在where子句中使用子查询。
在where子句中可以使用两种格式的子查询。
第一种格式是使用IN操作符:
...WhereColumnIn(Select*From...Where...);
第二种格式是使用EXIST操作符:
...WhereExists(Select1From...Where...);
我相信绝大多数人会使用第一种格式,因为它比较容易编写,而实际上第二种格式要远比第一种格式的效率高。
在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。
第二种格式中,子查询以select1开始。
运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句。
这样优化器就不必遍历整个表而仅根据索引就可完成工作(这里假定在where语句中使用的列存在索引)。
相对于IN子句来说,EXISTS使用相连子查询,构造起来要比IN子查询困难一些。
通过使用EXIST,Oracle系统会首先检查主查询,然后运行子查询直到它找到第一个匹配项,这就节省了时间。
Oracle系统在执行IN子查询时,首先执行子查询,并将获得的结果列表存放在在一个加了索引的临时表中。
在执行子查询之前,系统先将主查询挂起,待子查询执行完毕,存放在临时表中以后再执行主查询。
这也就是使用EXISTS比使用IN通常查询速度快的原因。
同时应尽可能使用NOTEXISTS来代替NOTIN,尽管二者都使用了NOT(不能使用索引而降低速度),NOTEXISTS要比NOTIN查询效率更高。
2.7使用函数
如果不使用基于函数的索引,那么在SQL语句的WHERE子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引。
下面的查询不会使用索引(只要它不是基于函数的索引)。
SelectEmpno,Ename,DeptnoFromEmp
Whereto_char(Hiredate,’yyyy-mm-dd’)='2009-03-05';
把上面的语句改成下面的语句,这样就可以通过索引进行查找。
SelectEmpno,Ename,DeptnoFromEmp
WhereHiredate AndHiredate>To_date('2009-03-04',’yyyy-mm-dd’); 2.8比较不匹配的数据类型 比较不匹配的数据类型也是比较难于发现的性能问题之一。 注意下面查询的例子,account_number是一个VARCHAR2类型,在account_number字段上有索引。 下面的语句将执行全表扫描。 SelectBank_Name,Address,City,State,Zip FromBanks WhereAccount_Number=990354; Oracle可以自动把where子句变成To_Number(Account_Number)=990354,这样就限制了索引的使用,改成下面的查询就可以使用索引: SelectBank_Name,Address,City,State,Zip FromBanks WhereAccount_Number='990354'; 特别注意: 不匹配的数据类型之间比较会让Oracle自动限制索引的使用,即便对这个查询执行ExplainPlan也不能让您明白为什么做了一次“全表扫描”。 3索引 Q: 为什么Oracle有时会用索引来查找数据? A: 在你运用SQL语言,向数据库发布一条查询语句时,Oracle将伴随产生一个“执行计划”,也就是该语句将通过何种数据搜索方案执行,是通过全表扫描、还是通过索引搜寻等其它方式。 搜索方案的选用与Oracle的优化器息息相关。 3.1Oracle索引建立机制 在建表初期,数据库开发人员就会对表的一些关键查询条件: 比如name,code,metecategory以及外键列上做索引。 但是很多跟实际业务使用场景相关的查询条件并不会考虑到很全面(比如函数索引),所以就要求开发人员给出需要建立索引的请求,由DB统一建立索引。 一般来说,不是很复杂的查询达到秒级以上就需要考虑是不是索引的问题了。 对于复杂的查询语句,也建议发给数据库开发人员进行脚本优化。 3.2Oracle位图索引 ✓适合于有大量重复数据的列(例如员工表的部门编号列,部门编号大量重复) ✓位图索引只能创建在非唯一性列上,唯一性unique索引只能使用B-tree索引且必须是全局global的或Localprefixed的 ✓位图索引不仅是在低基列上创建,在数据仓库环境里高基列上的位图索引性能也优于B-tree索引 ✓在数据仓库环境里,除非唯一性索引,创建位图索引是首选 ✓位图索引包含null值,而B-tree索引不包含null值 ✓分区表上只能创建本地local的位图索引,位图索引不能是全局global的 位图连接索引BitmapJoinIndexes: CreateBitmapIndexidx_t_xont(x); 3.3清除Oracle中无用索引 DML性能低下,其中最严重的原因之一是无用索引的存在。 所有SQL的插入、更新和删除操作在它们需要在每一行数据被改变时修改大量索引的时候会变得更慢。 许多Oracle管理人员只要看见在一个SQL查询的WHERE语句出现了一列的话就会为它分配索引。 虽然这个方法能够让SQL运行得更快速,但是基于功能的Oracle索引使得数据库管理人员有可能在数据表的行上过度分配索引。 过度分配索引会严重影响关键Oracle数据表的性能。 在Oracle9i出现以前,没有办法确定SQL查询没有使用的索引。 Oracle9i有一个工具能够让你使用ALTERINDEX命令监视索引的使用。 然后你可以查找这些没有使用的索引并从数据库里删除它们。 下面是一段脚本,它能够打开一个系统中所有索引的监视功能: SetPages999; SetHeadingOff; Spoolrun_Monitor.Sql Select 'alterindex'||Owner||'.'||Index_Name||'monitoringusage;' FromDba_Indexes WhereOwnerNotIn('SYS','SYSTEM','PERFSTAT'); SpoolOff; 你需要等待一段时间直到在数据库上运行了足够多的SQL语句以后,然后你就可以查询新的V$OBJECT_USAGE视图: Selectindex_name,table_name,monitoring,usedfromv$object_usage; 在V$OBJECT_USAGE有一列被称作USED,它的值是YES或者NO,它不会告诉你Oracle使用了这个索引多少次,但是这个工具对于找出没有使用的索引还是很有用的。 4SQL语句的执行步骤 一条SQL语句的处理过程要经过以下几个步骤: 1语法分析 分析语句的语法是否符合规范,衡量语句中各表达式的意义。 2语义分析 检查语句中涉及的所有数据库对象是否存在,且用户有相应的权限。 3视图转换 将涉及视图的查询语句转换为相应的对基表查询语句。 4表达式转换 将复杂的SQL表达式转换为较简单的等效连接表达式。 5选择优化器 不同的优化器一般产生不同的“执行计划”。 6选择连接方式 Oracle有三种连接方式,对多表连接Oracle可选择适当的连接方式。 7选择连接顺序 对多表连接Oracle选择哪一对表先连接,选择这两表中哪个表做为源数据表。 8选择数据的搜索路径 根据以上条件选择合适的数据搜索路径,比如选用全表搜索还是利用索引或是其他的方式。 9运行“执行计划” Oracle有两种优化器: 基于规则的优化器(RBO,RuleBasedOptimizer),和基于代价的优化器(CBO,CostBasedOptimizer)。 CBO自Oracle7版被引入,Oracle自7版以来采用的许多新技术都是基于CBO的,如星型连接排列查询,哈希连接查询,和并行查询等。 CBO计算各种可能“执行计划”的“代价”,即cost,从中选用cost最低的方案,作为实际运行方案。 各“执行计划”的cost的计算根据,依赖于数据表中数据的统计分布,Oracle数据库本身对该统计分布并不清楚,须要分析表和相关的索引,才能搜集到CBO所需的数据。 较典型的问题有: 有时,表明明建有索引,但查询过程显然没有用到相关的索引,导致查询过程耗时漫长,占用资源巨大,问题到底出在哪儿呢? 按照以下顺序查找,基本上能发现原因所在。 查找原因的步骤: 首先,检查表是否做过统计分析。 对数据经常有增、删、改的表最好定期对表和索引进行分析,我们提供了一个优化分析的过程,只要定期执行即可。 Oracle掌握了充分反映实际的统计数据,才有可能做出正确的选择。 除非在语句中用hint强制。 其次,检查被索引的列或组合索引的首列是否出现在PL/SQL语句的WHERE子句中,这是“执行计划”能用到相关索引的必要条件。 第三,看采用了哪种类型的连接方式。 Oracle共有SortMergeJoin(SMJ)、HashJoin(HJ)和NestedLoopJoin(NL)3种连接方式。 对于被连接的数据子集较小的情况,nestedloop连接是个较好的选择。 nestedloop就是扫描一个表,每读到一条记录,就根据索引去另一个表里面查找,没有索引一般就不会是nestedloops。 hashjoin是CBO做大数据集连接时的常用方式。 优化器扫描小表(或数据源),利用连接键(也就是根据连接字段计算hash值)在内存中建立hash表,然后扫描大表,每读到一条记录就来探测hash表一次,找出与hash表匹配的行。 sortmergejoin在9i开始已经很少出现了,因为其排序成本高,大多为hashjoin替代了。 通常情况下hashjoin的效果都比sortmergejoin要好。 第四,看连接顺序是否允许使用相关索引。 假设表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(AIND_COL1)*/*FromaWhereCol1=Xxx; 注意,注释符必须跟在SELECT之后,且注释中的“+”要紧跟着注释起始符“/*”或“--”,否则hint就被认为是一般注释,对PL/SQL语句的执行不产生任何影响。 5Oracle百万级别以上数据的分页查询和优化 一、使用2次查询来实现分页: 1.获取总记录数: SelectCount(*)FromtWhere… 2.利用Oracle的rownum获取指定页的数据 Select* From(SelectRow_.*,RownumRownum_ From(Select*FromtWhere? ? )Row_ WhereRownum<=? ) WhereRownum_>? (根据上一步得到的总记录数以及每页行数、当前页码计算出起始行号和结束行号,设置在上述SQL语句的“? ”处) 二、对于大表的一般性优化措施: 1.表结构设计原则: 数据类型尽量小(占用空间少,磁盘读入较快)、单独设置表空间 2.应用程序: 合理设计业务、SQL语句优化 3.合理使用索引: 只创建需要的索引,根据需要使用多列索引,存放于单独的表空间 4.数据库设置: 内存分配、查询优化器、分区、分表等 5.硬件环境: 硬盘、CPU、内存、网络带宽。 6用Oracle动态性能视图采集查询调优数 对于现在的一些发行版本,DBA(DatabaseAdministrator,数据库管理员)和开发员可以访问的已经有三种动态性能视图了,分别为V$SQL、V$SQLAREA,还有V$SQLTEXT。 这些视图可以用来采集有关SQL命令执行的统计信息。 在Oracle10g,Release2中,还增加了第四个动态性能视图,V$SQLSTATS,通过它能更方便地访问这类数据。 和静态数据字典视图(staticdictionaryview,也就是前缀为USER_、ALL_,或者DBA_的视图)不同,动态性能视图会随着系统的运行而不断更新。 这使得有可能在SQL语句执行之时监视其性能。 和静态视图一样的是,要使用它们你要先获得许可。 对于非数据库管理员用户(如典型的开发环境下)可赋予SELECT_CATALOG_ROLE权限,让他们可以从中做出选择。 下面是各个视图所提供功能的一些简要描述: V$SQL: 这个视图使用一个CLOB(characterlargeobject,字符型巨对象)column(栏,也就是视图中的属性字段),以提供SQL语句的完整文本,此外还有一列最多存放1000个VARCHAR2字符(存放SQL语句的前1000个字符)的对象,这方便了使用。 可访问的统计数据相当广泛: 包括解析语句(parse)和非法语句的数目、磁盘的读写次数、运行时间、等待时间,还有optimizer(优化器)数据。 你还可以从中知道创建语句的用
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Telant 培训 材料 Oracle 数据库 优化 措施 及其 建议