ORACLE将执行过的SQL语句存放在内存的共享池

  1. 云栖社区>
  2. 博客>
  3. 正文

ORACLE将执行过的SQL语句存放在内存的共享池

技术小甜 2017-11-09 21:04:00 浏览589
展开阅读全文
Oracle SQL性能优化深入浅出 
ORACLE将执行过的SQL语句存放在内存的共享池(shared buffer pool)中,可以被所有的数据库用户共享。当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同,
ORACLE就能很快获得已经被解析的语句以及最好的执行路径. 这个功能大大地提高了SQL的执行性能并节省了内存的使用。

为了不重复解析相同的SQL语句,在第一次解析之后,Oracle将SQL语句存放在内存中。这块位于系统全局区域SGA(systemglobal area)的共享池(shared buffer poo1)中的内存可以被所有的数据库用户共享。因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前执行过的语句完全相同,Oracle就能很快获得已经被解析的语句以及最好的执行方案。Oracle的这个功能大大地提高了SQL的执行性能并节省了内存的使用。
可惜的是,Oracle只对简单的表提供高速缓冲(cache bufferiIlg),这个功能并不适用于多表连接查询。数据库管理员必须在启动参数文件中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了。当向Oracle提交一个SQL语句时,Oracle会首先在这块内存中查找相同的语句。

SQL共享的三个条件:
1,当前被执行的语句和共享池中的语句必须完全相同 (包括大小写、空格、换行等)
2,两个语句所指的对象必须完全相同 (同义词与表是不同的对象)
3,两个SQL语句中必须使用相同的名字的绑定变量(bind variables) 

Oracle对两者采取的是一种严格匹配策略,要达成共享。SQL语句必须完全相同(包括空格、换行等)。能够使用共享的语句必须满足三个条件:

① 字符级的比较。当前被执行的语句和共享池中的语句必须完全相同。
例如: SELECT * FROM ATABLE;和下面每一个SQL语句都不同:
SELECT *from ATABLE
Select * From Atable;
② 语句所指对象必须完全相同。即两条SQL语句操作的数据库对象必须同一。
③语句中必须使用相同命名的绑定变量。如:第一组的两个SQL语句是相同的,可以共享;而第二组中两个语句不同,即使在运行时赋予不同的绑定变量以相同的值:
● 第一组 select pin,name from people where pin = :blk1.pin;
select pin,name from people where pin =:blk1.pin;
●第二组 select pin,name from people where pin =:blk1.ot_jnd;
select pin,name from people where pin = :blk1.ov_jnd;

659x414


832x520

SQL PARSE与共享SQL语句:

当一个Oracle实例接收一条sql后
1、Create a Cursor 创建游标
2、Parse the Statement 分析语句
3、Describe Results of a Query 描述查询的结果集
4、Define Output of a Query 定义查询的输出数据
5、Bind Any Variables 绑定变量
6、Parallelize the Statement 并行执行语句
7、Run the Statement 运行语句
8、Fetch Rows of a Query 取查询出来的行
9、Close the Cursor 关闭游标 


下面这个语句每执行一次就需要在SHARE POOL 硬解析一次,一百万用户就是一百万次,消耗CPU和内存,如果业务量大,很可能导致宕库……

如果绑定变量,则只需要硬解析一次,重复调用即可
select * from dConMsg 
where contract_no = 32013484095139

ORACLE 优化器模式:
Oracle的优化器共有3种模式:RULE (基于规则)、COST(基于成本)、CHOOSE(基于选择)。
设置缺省的优化器的方法,是在启动参数文件中针对OPTIMIZER_ MODE参数的各种声明进行选择,如RULE、COST、CHOOSE、ALL_ ROWS、FIRST_ ROWS。当然也可以在SQL语句级别或是会话级别对其进行覆盖。
为了使用基于成本的优化器(CBO,Cost—Based Optimizer),必须经常运行analyze命令,以增加数据库中的对象统计信息(object statistics)的准确性。如果数据库的优化器模式设置为基于选择,那么实际的优化器模式将和是否运行过analyze命令有关。如果数据表已经被analyze过,优化器模式将自动切换成CBO,反之,数据库将采用RULE形式的优化器。在缺省情况下,Oracle采用CHOOSE优化器。为避免那些不必要的全表扫描,必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器。

影响数据库系统性能的要素:
1,主机CPU,RAM,存储系统;
2,OS参数配置,ORACLE参数配置;
3,应用方面:数据库设计及SQL编程的质量
一个性能优秀的应用系统需要:
1,良好的硬件配置;
2,正确合理的数据库及中间件参数配置;
3,合理的数据库设计;
4,良好的sql编程;
5,运行期的性能优化

SQL Tunning 的重点:
SQL: insert, update, delete, select(主要关注的是select)
关注的是:如何用最小的硬件资源消耗、最少的响应时间定位数据位置

SQL优化的一般性原则:
1,目标:
减少服务器资源消耗(主要是磁盘IO);
2,设计方面:
尽量依赖oracle的优化器,并为其提供条件;
合适的索引,索引的双重效应,列的选择性;
3,编码方面:
利用索引,避免大表FULL TABLE SCAN;
合理使用临时表;
避免写过于复杂的sql,不一定非要一个sql解决问题;
在不影响业务的前提下减小事务的粒度;

优化概括:
● 创建表的时候。应尽量建立主键,尽量根据实际需要调整数据表的PCTFREE和PCTUSED参数;大数据表删除,用truncate table代替delete。

● 合理使用索引,在OLTP应用中一张表的索引不要太多。数据重复量大的列不要建立二叉树索引,可以采用位图索引;组合索引的列顺序尽量与查询条件列顺序保持一致;对于数据操作频繁的表,索引需要定期重建,以减少失效的索引和碎片。


● 查询尽量用确定的列名,少用*号。select count(key)from tab where key> 0性能优于select count(*)from tab;

当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*’ 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间;

尽量少嵌套子查询,这种查询会消耗大量的CPU资源;对于有比较多or运算的查询,建议分成多个查询,用union all联结起来;多表查询的查询语句中,选择最有效率的表名顺序。Oracle解析器对表解析从右到左,所以记录少的表放在右边。

● 尽量多用commit语句提交事务,可以及时释放资源、解锁、释放日志空间、减少管理花费;在频繁的、性能要求比较高的数据操作中,尽量避免远程访问,如数据库链等,访问频繁的表可以常驻内存:alter table...cache;

● 在Oracle中动态执行SQL,尽量用execute方式,不用dbms_sql包。













本文转自东方之子736651CTO博客,原文链接:http://blog.51cto.com/ecloud/1599319 ,如需转载请自行联系原作者




网友评论

登录后评论
0/500
评论
技术小甜
+ 关注