整理Oracle数据库中数据查询优化的一些关键点

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

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

Oracle查询优化第一方面:Oracle数据查询命中率的提高

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

1)字典表活动

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

2)临时段的活动

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

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

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

3)回滚段的活动

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

4)表扫描

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

Oracle查询优化第二方面:多表查询的优化

在进行多表联合查询时,数据库可能会采取MERGEJOINS、NESTED LOOP、HASH JOIN。其中,不论什么时候哈希联结要比另两种联结开销要小。

我们可以使用哈希联结代替MERGEJOINS、NESTED LOOP联结、因此,在应用中,可添加一些设置使得数据库在有多大联合查询发生时使用哈希联结。其方法是:以 oracle用户身份登录数据库服务器,在initosid.ora文件中添加:

HASH_JOIN_ENABLED=TRUE

HASJ_AREA_SIZE=26000

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

Oracle查询优化第三方面:大表查询优化

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

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;

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;

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

Oracle查询优化第四方面:SQL优化

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

SQL优化的主要途径是:

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

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

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

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

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

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

时间: 2016-01-10

ORACLE 查询被锁住的对象,并结束其会话的方法

使用Oracle时,发现有表被锁,又不知道是谁(或者哪个程序)锁的,怎么办 ? 两步走: 1.查找出被锁对象的会话ID和序列号 执行如下SQL: -- 查询出被锁对象,并提供 kill 脚本 SELECT S.SID, S.MACHINE, O.OBJECT_NAME, L.ORACLE_USERNAME, L.LOCKED_MODE, S.OSUSESR, 'ALTER SYSTEM KILL SESSION '''|| S.SID || ', '|| S.SERIAL#||''';' AS

详解SQLServer和Oracle的分页查询

不管是DRP中的分页查询代码的实现还是面试题中看到的关于分页查询的考察,都给我一个提示:分页查询是重要的.当数据量大的时候是必须考虑的.之前一直没有花时间停下来好好总结这里.现在又将Oracle视频中关于分页查询的内容看了一遍,发现很容易就懂了. 1.分页算法     最开始我在网上查找资料的时候,看到很多分页内容,感觉很多很乱.其实不是这样.网上那些资料大同小异.问题出在了我自己这里.我没搞明白进行分页的前提是什么?我们都知道只要有分页都会涉及这些变量:每页又多少条记录(pageSize).当

oracle 9i使用闪回查询恢复数据库误删问题

如果用户误删/更新了数据后,作为用户并没有什么直接的方法来进行恢复,他们必须求助DBA来对数据库进行恢复,到了Oracle9i,这一个难堪局面有所改善.Oracle 9i中提供了一项新的技术手段--闪回查询,用户使用闪回查询可以及时取得误操作前的数据,并可以针对错误进行相应的恢复措施,而这一切都无需DBA干预. 因为一时手贱,生产上的数据被我给delete掉了. 用的是delete语句,然后很迅速的还给commit了 下面这两个语句: ALTER TABLE tablename ENABLE r

Oracle数据库中基本的查询优化与子查询优化讲解

1. 查询条件合理排序 Oracle采用自下而上的顺序解析WHERE字据,从优化性能角度考虑,建议将那些可以过滤掉大量记录行的条件写在WHERE子句的末尾,而将表 之间的连接条件置于其他WHERE子句之前,即对易排查的条件先做判断处理,这样在过滤掉尽可能多的记录后再进行等值连接,可以提高检索效率. 例如: SELECT empno, ename, job, sal, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno

简单说明Oracle数据库中对死锁的查询及解决方法

死锁的原理 当对于数据库某个表的某一列做更新或删除等操作,执行完毕后该条语句不提 交,另一条对于这一列数据做更新操作的语句在执行的时候就会处于等待状态, 此时的现象是这条语句一直在执行,但一直没有执行成功,也没有报错.    死锁的定位方法 通过检查数据库表,能够检查出是哪一条语句被死锁,产生死锁的机器是哪一台.   1)用dba用户执行以下语句 select username,lockwait,status,machine,program from v$session where sid in

Oracle的数据字典技术简析

正在看的ORACLE教程是:Oracle的数据字典技术简析.数据字典是Oracle存放有关数据库信息的地方,其用途是用来描述数据的.比如一个表的创建者信息,创建时间信息,所属表空间信息,用户访问权限信息等.当用户在对数据库中的数据进行操作时遇到困难就可以访问数据字典来查看详细的信息. Oracle中的数据字典有静态和动态之分.静态数据字典主要是在用户访问数据字典时不会发生改变的,但动态数据字典是依赖数据库运行的性能的,反映数据库运行的一些内在信息,所以在访问这类数据字典时往往不是一成不变的.以下

讲解Oracle数据库中的数据字典及相关SQL查询用法

Oracle数据字典概述    数据库是数据的集合,数据库维护和管理这用户的数据,那么这些用户数据表都存在哪里,用户的信息是怎样的,存储这些用户的数据的路径在哪里,这些信息不属于用户的信息,却是数据库维护和管理用户数据的核心,这些信息就是数据库的数据字典来维护的,数据库的数据字典就汇集了这些数据库运行所需要的基础信息叻.每个数据库都提供了各自的数据字典的方案,虽然形式不同,但是目的和作用是一样的,比如在mysql里数据字典是在information_schema 里表现的,sqlserver则是

一些Oracle数据库中的查询优化建议综合

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引.   2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: select id from t where num=0 3.应尽量避免在 where 子句中使用!=或<>操作符,否则将引

Linux下为Node.js程序配置MySQL或Oracle数据库的方法

mysql使用 安装mysql 模块: 在安装根目录 cmd命令行执行命令 npm install mysql 安装成功后. mysql数据库表 已存在的情况下. 在nodejs根目录 新建mysql.js: var sys = require('util'); var mysql=require('mysql'); console.log('正在连接MySQL...'); var http = require("http"); var server=http.createServer

Node.js中使用mongoose操作mongodb数据库的方法

如何利用mongoose将数据写入mongodb数据库? 1.利用npm下载安装mongoose; npm install mongoose 2.建立js文件,引入mongoose var mongoose = require('mongoose'); 3.mongoose.connect连接数据库 //连服务器 mongoose.connect('mongodb://127.0.0.1:27017/test'); //数据库的名字 var connection = mongoose.conne

为Node.js程序配置使用Nginx服务器的简明教程

Node.js是一个基于Chrome JavaScript运行时建立的平台, 用于方便地搭建响应速度快.易于扩展的网络应用.Node.js 使用事件驱动, 非阻塞I/O 模型而得以轻量和高效,非常适合在分布式设备上运行的数据密集型的实时应用,如实时聊天等等.然而对于gzip编码,静态文件,HTTP缓存,SSL处理,负载平衡和反向代理等,都可以通过nginx来完成,从而减小node.js的负载,并通过nginx强大的缓存来节省网站的流量从而提高网站的加载速度. 流程图 nginx配置如下: htt

Linux下安装Python3和django并配置mysql作为django默认服务器方法

我的操作系统为centos6.5 1  首先选择django要使用什么数据库.django1.10默认数据库为sqlite3,本人想使用mysql数据库,但为了测试方便顺便要安装一下sqlite开发包. yum install mysql mysql-devel #为了测试方便,我们需要安装sqlite-devel包 yum install sqlite-devel 2  接下来需要安装Python了,因为Python3已经成为主流,所以接下来我们要安装Python3,到官网去下载Python3

windows系统下node.js环境配置与安装教程图文详解(详细版)

国内目前关注最高,维护最好的一个关于nodejs的网站应该是http://www.cnodejs.org/ windows系统下简单nodejs环境配置. 第一步:下载安装文件 下载地址:官网https://nodejs.org/en/download/ 这里用的是 第二步:安装nodejs 下载完成之后,双击 node-v6.9.1-x64.msi,开始安装nodejs,默认是安装在C:\Program Files\nodejs下面 第三步:安装相关环境 打开C:\Program Files\

TypeScript开发Node.js程序的方法

当我第一次发现 TypeScript 时,就把它用到了自己的 JavaScript 程序中.使用 TypeScript 有很多好处,现在你要让我在用原生 JavaScript 写任何东西的话,需要给我一个令人信服的理由. 在本文中,我将向你展示如何设置一个简单的开发环境,以便使用 TypeScript 编写 Node.js 应用程序. 首先在 TypeScript 中可能有一千种或更多种不同的方法去创建 Node.js 程序.我只是想展示自己喜欢的方式. 另外你可以在此处找到我的入门项目:htt

linux环境安装node.js开发环境搭建图文教程

Node.js现在有多火,有多重要,就不多说了,这里介绍一下他的开发环境安装,通常情况下,我们都是在window环境进行开发,或者是在mac环境下进行开发,但是我们的项目实际运行的环境一般都是在linux上的,所以我们让我们的开发环境和生产环境能够尽量统一,且能够方便我们开发,一般我们将必要环境安装在服务器上,然后在window上通过xshell,SecureCRTPortable等一些工具连接上去,在window上使用方便IDE软件开发,然后通过ftp将文件同步上去,所以这里就讲一些如何在服务

充分发挥Node.js程序性能的一些方法介绍

一个Node.JS 的进程只会运行在单个的物理核心上,就是因为这一点,在开发可扩展的服务器的时候就需要格外的注意. 因为有一系列稳定的API,加上原生扩展的开发来管理进程,所以有很多不同的方法来设计一个可以并行的Node.JS运用.在这篇博文里,我们就来比较下这些可能的架构. 这篇文章同时也介绍compute-cluster 模块:一个小型的Node.JS库,可以用来很方便的管理进程,从来二线分布式计算. 遇到的问题 我们在Mozilla Persona的项目中需要可以处理大量不同特征的请求,所

Node.js+Express配置入门教程详解

Node.js是一个Javascript运行环境(runtime).实际上它是对Google V8引擎进行了封装.V8引 擎执行Javascript的速度非常快,性能非常好.Node.js对一些特殊用例进行了优化,提供了替代的API,使得V8在非浏览器环境下运行得更好.Node.js是一个基于Chrome JavaScript运行时建立的平台, 用于方便地搭建响应速度快.易于扩展的网络应用.Node.js 使用事件驱动, 非阻塞I/O 模型而得以轻量和高效,非常适合在分布式设备上运行的数据密集型

如何在Linux上安装Node.js

Node.js 是建立在谷歌的 V8 JavaScript 引擎服务器端的软件平台上.在构建高性能的服务器端应用程序上,Node.js 在 JavaScript 中已是首选方案.是什么让使用 Node.js 库和应用程序的庞大生态系统来开发服务器后台变得如此流行.Node.js 自带一个被称为 npm 的命令行工具可以让你轻松地安装它,进行版本控制并使用 npm 的在线仓库来管理 Node.js 库和应用程序的依赖关系. 在本教程中,我将介绍 如何在主流 Linux 发行版上安装 Node.js