`
wlh269
  • 浏览: 458056 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

基于ORACLE数据库查询优化方法

阅读更多
随着数据库技术的发展 ,其应用越来越广泛,已逐渐成为现代计算机信息系统和计算机应用系统的基础和核心。基于数据库的联机事务处理(OLTP)以及联机分析处理(OLAP)银行、企业、 政府等部门最为重要的计算机应用之一,并作为电子政务、门户网站等应用系统底层数据管理的工具。   

数据库最基本的任务是存储、管理数据,而终端用户唯一能看到的数据库特性就是其性能:数据库以何速度处理某一指定查询的结果,并且将结果返回到用户所用的工具和应用程序。从大多数系统的应用实例来看,查询操作在各种数据库操作中所占据的比重最大、查阅新闻、 查看文件、 查询统计信息等。因此,数据库查询操作的效率是影响一个应用系统响应时间的关键因素。随着一个应用系统中数据的动态增长,数据量变大,数据库查询效率就会有所降低,应用系统的响应速度也随之减慢,尤其对于海量数据的管理和查询问题就更加突出,如何提高动态增长的数据库的查询效率就显得尤为重要。   

目前通用的数据库产品有很多种,其中ORACLE数据库以其支持大数据库、多用户的高性能事务处理, 对业界各项工业标准的支持,完整的安全和完整性控制,支持分布式数据库利分布处理 具有可移植性、可兼容性和可连接性等突出优点倍受用户喜爱,应用较为广泛,在互联网数据库平台上处于领先地位、其Spatial技术能更加有效地管理地理信息,实现海量空间信息的存储和管理。本文结合ORACLE数据库应用经验,从命中率提高、多表查询优化、大表查询优化和SQL优化等四个方面阐述ORACLE数据库查询效率提高的经验和方法。

1.ORACLE数据查询命中率的提高   
“命中率(HITRATIO) 是指直接从内存中取得数据而不从磁盘中取得数据的比率,也就是查询请求的数据块已经在内存中次数的百分比”。影响命中率的因素有四种:字典表活动、临时段活动、回滚段活动、表扫描, 应用DBA可以对这四种因素进行分析,找出数据库命中率低的症结所在。

1.1 字典表活动  

当一个SQL 语句第一次到达ORACLE内核时数据库对SQL语句进行分析,包含在查询中的数据字典对象被分解,产生SQL执行路径。如果SQL语句指向一个不在SGA中的对象??表或视图,ORACLE执行SQL语句到数据典中查询有关对象的信息。数据块从数据字典表被读取到SGA的数据缓存中。由于每个数据字典都很小,因此,我们可缓存这些表以提高对这些表的命中率。但是由于数据字典表的数据块在SGA中占据空间,当增加全部的命中率时,它们会降低表数据块的可用空间, 所以若查询所需的时间字典信息已经在SGA缓存中,那么就没有必要递归调用。

1.2临时段的活动    当用户执行一个需要排序的查询时,ORACLE设法对内存中排序区内的所有行进行排序,排序区的大小由数据库的init.ora文件的数确定。如果排序区域不够大,数据库就会在排序操作期间开辟临时段。临时段会人为地降低 OLTP(online transaction processing)应用命中率,也会降低查询进行排序的性能。如果能在内存中完成全部排序操作,就可以消除向临时段写数据的开销。所以应将SORT_AREA_SIZE设置得足够大,以避免对临时段的需要。这个参数的具体调整方法是: 查询相关数据,以确定这个参数的调整。  

select * from v$sysstat where name='sorts(disk)'or name='sorts(memory);

例如在某数据库上进行这个查询操作, 结果将为

STATISTIC#  NAME                CLASS         VALUE

174        sorts(disk)          64             2280

173        sorts(memory)        64           15600804

可见,大部分排序是在内存中进行的,但还有小部分发生在临时段, 需要调整 值,查看init.ora文件的 SORT_AREA_SIZE值,参数为:SORT_AREA_SIZE=65536将其调整到SORT_AREA_SIZE=131072、这个值调整后,重启ORACLE数据库即可生效。

1.3 回滚段的活动   

回滚段活动分为回滚活动和回滚段头活动。对回滚段头块的访问会降低应用的命中率, 对OLTP系统命中率的影响最大。为确认是否因为回滚段影响了命中率,可以查看监控输出报表中的“数据块相容性读一重写记录应用” 的统计值,这些统计值是用来确定用户从回滚段中访问数据的发生次数。

1.4 表扫描   

通过大扫描读得的块在数据块缓存中不会保持很长时间, 因此表扫描会降低命中率。为了避免不必要的全表扫描,首先是根据需要建立索引,合理的索引设计要建立人对各种查询的分析和预测上,笔者会在SQL优化中详细谈及;其次是将经常用到的表放在内存中,以降低磁盘读写次数。例如 Alter table your_table_name cathe。

2多表查询的优化
在进行多表联合查询时,数据库可能会采取MERGEJOINS、NESTED LOOP、HASH JOIN。其中,不论什么时候哈希联结要比另两种联结开销要小。我们可以使用哈希联结代替MERGEJOINS、NESTED LOOP联结、因此,在应用中,可添加一些设置使得数据库在有多大联合查询发生时使用哈希联结。其方法是:    以 oracle用户身份登录数据库服务器,在initosid.ora文件中添加 :

HASH_JOIN_ENABLED=TRUE

HASJ_AREA_SIZE=26000  

修改完后,重新启动数据库,使这些参数值生效。

3大表查询的优化  
数据库中有些表是增长非常快的,记录量很大,对这种表进行访问时,索引的好处就微乎其微了,通常采用两种办法来进行大表访问的优化。

3.1大表建立在哈希簇中

create cluster TRADE_CLUSTER(vuserid integer)

storage(initial 50M next 50M)

hash is vuserid

size 60 hashkeys 10000000;/*hashkeys指定了在哈希表里的所期望的行数。*/ create table

trade_detail_new as select * from trade_detail cluster

TRADE_CLUSTER(userid);

drop table trade_detail;

rename trade_detail_new to trade_detail;

3.2 建分区表  

将一个大表分开放置在几个逻辑分区中或者是将一个大表分成了几张小表 ,即可以单独对这些小表进行查询,也可以union all一 起查询。   

例如:将 一个记录交易详情的表拆分:

create trade_detail_1 as select * from trade_detail

where trade_time between to_date('mm-dd','01-01')and to_date('mm-dd','03-31');

alter table trade_detail_1 add constraint check_trade_detail_1

check (trade_time between to_date('mm-dd','01-01')and to_date('mm-dd','03-31'));

同样,建立起另几张按交易发生的季度而划分的表。然后创建执行四个表联合的视图;

create view trade_detail as select * from trade_detail_1

union all select * from trade_detail_2

union all select * from trade_detail_3

union all select * from trade_detail_4;

这样在查询某段时间内的数据时只访问小表就可以了,需要时也可进行联合查询。

4 SQL优化
应用程序的执行最终将归结为数据库中的SQL 语句执行,SQL语句消耗了70%-90%的数据库资源。因此SQL 语句的执行效率最终决定了ORACLE数据库的性能。许多程序员认为查询优化是DBMS(数据库管理系统)的任务,与程序员所编写的SQL语句关系不大,这是错误的。一个好的查询计划往往可以使程序性能提高数十倍。另外,SQL语句独立于程序设计逻辑,相对于对程序源代码的优化,对SQL语句的优化在时间成本和风险上的代价都很低。SQL优化的主要途径是:   

(l)有效索引的建立。在经常进行连接,但是没有指定为外键的列上建立索引;在频繁进行排序或分组 (即进行group by 或 order by 操作)的列上建立索引;在条件表达式中经常用到的不同值较多的列上建立检索,在不同值少的列上不要建立索引;如果待排序的列有多个,可以在这些列上建立复合索引(compound index)。  

为了降低I/O竟争, 索引要建在与用户表空间不在同一磁盘上的索引空间里。索引分为:分区索引、完全索引、唯一索引、位图索引等几种类型,在建立索引前,应该测量这个索引的选择性,索引的选择性是指索引列里不同值的数目与表中记录数的比。    

测绘索引的选择性的语句为:

select count(distinct userid||'%'||username||'%'||password)from userinfo;

COUNT(DISTINCT USERDI'%'||USERNAME||'%'||PASSWORD)

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

                                        10400

select count(*)from userinfo;

COUNT

-------

10400

可见这个索引选择性很好,已经达到了1.0

(2)在有大量重复值并且经常有范围查询(例如 between,>,<>=,<=)的列,或是用到order by、group by的列,可考虑建立群集索引;   

(3)要经常同时存取多列,目每列都含有重复值可考虑建立组合索引   

(4)优化表达式,在能使用范围查询时尽可能使用范围索引, 而少用“like“,因为”LIKE“关键字支持的通配符匹配特别耗费时间。  

(5)使用ORACLE语句优化器(oracle optimizer)和行锁管理器( row-level manager)来调整优化SQL语句。

5结 论
数据库的优化是个细致的工作,提高查询效率只是其中的一个部分。亦如何提高查询效率这个问题上,本文作者通过对ORACLE数据库应用的实践经验 ,总结了四个方面来优化查询:监测命中率,当其降低时,进行调整;多表联合查询时,使用哈希联接;将大表进行优化,以缩短查询时间;对SQL语句进行优化,以提高查询效率。通过这些方法 可更好地发挥ORACLE数据库的作用。
分享到:
评论

相关推荐

    基于ORACLE数据库查询优化方法研究.pdf

    因此,研究并实施有效的Oracle数据库查询优化方法,对于提高企业信息系统性能具有重要的实际意义。 查询命中率是衡量数据库性能的一个重要指标,它反映了查询操作中数据从内存中获取的频率。高命中率意味着较少的...

    Oracle数据库查询优化的方法

    Oracle数据库作为企业级信息管理系统的重要支撑平台,其查询优化方法尤为关键。本文重点分析Oracle数据库索引及临时表在查询中的应用,并探讨了基于索引使用SQL语句进行数据库效率优化的几种实现方法。 在Oracle...

    Oracle数据库查询优化方法研究.pdf

    【Oracle数据库查询优化方法研究】 Oracle数据库是当前广泛应用的关系型数据库之一,因其强大的数据存储能力和多用户高并发的优秀性能而备受大型企业青睐。优化Oracle数据库的查询效率对于提升整体系统性能至关重要...

    基于Oracle数据库查询优化策略的研究.pdf

    本文主要探讨了在Oracle数据库中进行查询优化的策略,以提高数据处理速度和查询效率。 首先,Oracle数据库的优化器是关键组件,它负责确定SQL语句的最佳执行路径。优化器有两种类型:基于规则的优化器(RBO)和基于...

    oracle数据库性能优化.pdf

    Oracle数据库性能优化是确保系统高效运行的关键环节,尤其是在大数据量和高并发的环境中。Oracle数据库因其先进、完整和集成的特性,在市场中占据主导地位,因此深入理解和掌握Oracle的优化技术至关重要。 首先,...

    Oracle数据库的查询优化

    Oracle数据库内置了一个基于成本的优化器,用于确定执行查询的最佳策略。优化器的工作原理如下: 1. **评估不同执行路径的成本**:优化器会分析SQL语句,并评估不同执行路径(如表扫描与索引扫描)的成本。 2. **...

    基于Oracle数据库的SQL语句优化

    ### 基于Oracle数据库的SQL语句优化 #### 引言 Oracle数据库是由甲骨文公司开发的一款关系型数据库管理系统,在当前的数据库市场中占据着重要的地位。它广泛应用于商业、政府等领域,具备强大的数据处理能力和高效...

    基于Oracle数据库性能优化技术研究.pdf

    基于Oracle数据库性能优化技术研究.pdf

    基于Oracle数据库的SQL优化原则和方法.pdf

    【SQL优化方法】 1. **使用索引**:创建合适的索引可以加速数据检索。应针对常用查询列和连接条件建立索引,但也要注意过多的索引会增加插入和更新操作的开销。 2. **避免全表扫描**:通过限制查询范围,如使用...

    基于oracle数据库的超市管理系统

    《基于Oracle数据库的超市管理系统详解》 在信息技术日益发达的今天,各类管理系统已经渗透到各行各业,其中超市管理系统作为零售业的重要组成部分,对于提升运营效率、优化管理流程具有至关重要的作用。本文将深入...

    基于Oracle数据库性能优化技术探析.pdf

    Oracle数据库性能优化技术是数据库管理员和IT专业人士关注的重要领域,因为高效的数据库性能直接影响到企业的业务运行效率和用户体验。Oracle数据库作为一款广泛应用的关系型数据库,以其出色的安全性、兼容性、扩展...

    基于Oracle数据库性能优化的研究.pdf

    本文主要探讨了Oracle数据库性能优化的方法和工具,特别强调了智能优化器在提升性能中的作用。 首先,性能调整的目标是解决系统吞吐量下降和用户响应时间变慢的问题。优化的基本原则是减少磁盘访问以获取所需数据。...

    基于Oracle数据库性能优化及监控研究.pdf

    本文主要探讨了Oracle数据库性能的评价指标,分析了影响性能的主要因素,并提出了优化和监控的方法。 首先,Oracle数据库性能的评价指标包括以下几个方面: 1. **数据库吞吐量**:衡量数据库在单位时间内处理SQL...

    基于Oracle数据库电信CRM经营分析系统的性能优化.pdf

    基于Oracle数据库电信CRM经营分析系统的性能优化 Oracle 数据库是当前使用最为广泛的大型数据库管理系统,对电信行业 CR M 应用的性能优化方法至关重要。作为关系型数据库,Oracle 数据库在电信 CRM 经营分析系统...

    基于Oracle数据库的查询优化技术.pdf

    【Oracle数据库查询优化技术】 Oracle数据库作为一款广泛应用于各行业的关系型数据库系统,其查询优化对于提升系统性能至关重要。查询优化主要包括三个方面:应用适当的索引、SQL语句优化和其他优化手段。 1. 应用...

    基于Oracle数据库海量数据的查询优化研究.pdf

    "基于Oracle数据库海量数据的查询优化研究" 本文主要探讨了 Oracle 数据库海量数据的查询优化研究,通过对 Oracle 数据库的分析,讨论了分页查询技术、SQL 语句优化、索引技术等查询优化方法,并对 Oracle 数据库的...

    Oracle数据库优化详解

    Oracle数据库优化是一门深入的技术领域,它关系到数据库性能的提升和资源的高效利用。本文针对Oracle数据库的优化提供了详尽的指导和案例分析,尤其强调了在CBO(Cost-Based Optimizer,基于成本的优化器)模式下的...

    基于ORACLE数据库的SQL优化研究.pdf

    3. ORACLE数据库中SQL查询优化 在ORACLE数据库中,SQL优化的处理过程涉及三个主要阶段:解析数据、执行数据和提取数据。解析阶段涉及对SQL语句的语法和数据进行整体解析,以及将相关数据放入共享应用中。在执行阶段...

    基于Oracle数据库的几种常见SQL优化策略.pdf

    本文档主要讨论了基于Oracle数据库的几种常见SQL优化策略,以提高数据库的处理性能和查询效率。在当前大数据和物联网发展的背景下,数据库的性能优化变得越来越重要。Oracle数据库作为当前使用最为广泛的数据库管理...

    大型Oracle数据库迁移优化方法.pdf

    5. **实验与结论**:通过仿真实验,对比传统的Oracle数据库迁移方法与提出的优化方法,结果显示,优化方法在数据完整性、对接效率等方面表现更优。 6. **适用性与未来展望**:大型Oracle数据库迁移优化方法的设计...

Global site tag (gtag.js) - Google Analytics