[收藏]SQL优化

Posted on 2006-03-20 20:53 ikingqu 阅读(755) 评论(1)  编辑  收藏 所属分类: DataBase
最近做项目发现很多SQL没有优化: 现在总结几种优化方式.
 首先先了解一个SQL语句的执行过程分3步: 语法分析(parase)与编译,执行,取数据.
1: 在语法分析与编译时:oracle 使用哈希函数为SQL语句在库缓存中分配一个SQL区,
首先检查语句是否存在,若在,则查询数据库字典、检查必须的权限。
若无,需要语法分析与编译。所以SQL语句存在与内存中,将减少分析,编译时间。
SQL语句的分析与编译占整个语句运行过程的60%的时间,SQL优化的目标就是减少分析与编译的时间,共享代码。

查询SQL语句分析与编译的时间:
select * from v$sysstat
where name in ('parse time cpu','parse time elapsed','parse count (hard)')

一个SQL语句的响应时间(elapsed time )应该是服务时间+等待时间.
服务时间= CPU执行时间.
等待时间 可以从v$system_event
select total_waits, total_timeouts, time_waited, average_wait ,event
from v$system_event
where event='latch free'
所以解析一个SQL语句的平均等待时间是"等待时间/parse count" 这个值接近0
通过数据字典v$sqlare,可以查询到频繁被分析与编译的SQL语句.应该减少SQL语句的分析与编译的次数.

2: 将常用的实体驻留内存.
为了减少分析与编译时间,可以将常用的的实体如: 存储过程,包等,尽可能驻留在内存区域.
 1)预留内存空间. sql> show parameter shared_pool_reserved_size
       2)将频繁使用的实体驻留在内存中. 在使用DBMS_SHARED_POOL程序包前,必须首先运行系统提供的程序包: dbmspool.sql 和prvtpool.plb
 在加载这两个程序包后,自动生成所需的包.
      加载: sql> @/u01/app/oracle/product/8.17/rdbms/admin/dbmspool.sql
           sql> @/u01/app/oracle/product/8.17/rdbms/admin/prvtpool.sql
      包DBMS_SHARED_POOL包含以下存储过程.
      dbms_shared_pool.keep 用于将实体保存内存. dbms_shared_pool.keep(object in varchar2,[type in char default p]);
                             object 表示参数名, type 表示被驻留内存的实体类型;P 表示存储过程,C表示光标,R表示触发器,默认P
       
      dbms_shared_pool.unkeep 用于取消被设置进入内存的实体. dbms_shared_pool.unkeep(object in varchar2,[type in char default p]);
                             object 表示参数名, type 表示被驻留内存的实体类型;P 表示存储过程,C表示光标,R表示触发器,默认P
     
      dbms_shared_pool.size(minsize in number)

      select name ,type ,source_size+code_size+parsed_size+error_size "total bytes"
      from dba_object_size
      where owner='SCOTT'

3: 创建索引.
   select index_name,table_owner, table_name, tablespace_name from all_indexes
  
   select user_indexes.TABLE_NAME, user_indexes.INDEX_NAME,uniqueness, column_name
   from user_ind_columns ,user_indexes
   where user_ind_columns.INDEX_NAME=user_indexes.INDEX_NAME
   and user_ind_columns.TABLE_NAME=user_indexes.TABLE_NAME
   order by user_indexes.TABLE_TYPE,user_indexes.TABLE_NAME,user_indexes.INDEX_NAME,user_ind_columns.COLUMN_POSITION

4: 创建聚簇(cluster): 是一组存储在一起的有共同列或经常一起使用的表,被聚簇的两个表只有一个数据段.聚簇表在存储时,在物理层将子表合并到父表中,这样就少了表的连接时间.

5: 创建哈希索引.
 
6: SQL优化器: 基于成本的优化器CBO(cose_based)和基于规则RBO(rule_based)
   sql> show parameter OPTIMIZER_MODE
   可以修改参数文件: initSID.ora,增加: optimizer_Mode={CHOOSE| RULE| FIRST_ROWS|ALL_ROWS}
  all_rows , first_rows(n)基于成本; rule 基于规则,choose基于规则、成本。
  /*+ ordered*/
  /*+ rule */
  /*+ first_rows(50) */

  /*+ordered star*/
  写发: 
  alter system flush shared_pool;
select /*+ rule */ aa from visit

原文链接:http://www.blogjava.net/yanmin/archive/2006/03/20/36332.html

Feedback

# re: [转]SQL优化  回复  更多评论   

2006-03-20 23:41 by 風向逆轉 - Java无限
SQL优化 补充
作者blog:http://blog.csdn.net/baggio785/

Sql优化是一项复杂的工作,以下的一些基本原则是本人看书时所记录下来的,很明确且没什么废话:
1. 索引的使用:
(1).当插入的数据为数据表中的记录数量的10%以上,首先需要删除该表的索引来提高数据的插入效率,当数据插入后,再建立索引。
(2).避免在索引列上使用函数或计算,在where子句中,如果索引是函数的一部分,优化器将不再使用索引而使用全表扫描。如:
低效:select * from dept where sal*12 >2500;
高效:select * from dept where sal>2500/12;
(3).避免在索引列上使用not和 “!=”,索引只能告诉什么存在于表中,而不能告诉什么不存在于表中,当数据库遇到not 和 “!=”时,就会停止使用索引而去执行全表扫描。
(4).索引列上>=代替>
低效:select * from emp where deptno > 3
高效:select * from emp where deptno >=4
两者的区别在于,前者dbms将直接跳到第一个deptno等于4的记录,而后者将首先定位到deptno等于3的记录并且向前扫描到第一个deptno大于3的。
(5).非要对一个使用函数的列启用索引,基于函数的索引是一个较好的方案。
2. 游标的使用:
当在海量的数据表中进行数据的删除、更新、插入操作时,用游标处理的效率是最慢的,但是游标又是必不可少的,所以正确使用游标十分重要:
(1). 在数据抽取的源表中使用时间戳,这样每天的维表数据维护只针对更新日期为最新时间的数据来进行,大大减少需要维护的数据记录数。
(2). 在insert和update维表时都加上一个条件来过滤维表中已经存在的记录,例如:
insert into dim_customer select * from ods_customer where ods_customer.code not exists (dim_customer.code)
ods_customer为数据源表。dim_customer为维表。
(3). 使用显式的游标,因为隐式的游标将会执行两次操作,第一次检索记录,第二次检查too many rows这个exception,而显式游标不执行第二次操作。
3. 据抽取和上载时的sql优化:
(1). Where 子句中的连接顺序:
oracle采用自下而上的顺序解析where子句,根据这个原理,表之间的连接必须写在其他where条件之前,那些可以过滤掉大量记录的条件必须写在where子句的末尾。如:
低效:select * from emp e where sal>5000 and job = ‘manager’ and 25<(select count (*) from emp where mgr=e.empno);
高效:select * from emp e where 25<(select count(*) from emp where mgr=e.empno) and sal>5000 and job=’manager’;
(2). 删除全表时,用truncate 替代 delete,同时注意truncate只能在删除全表时适用,因为truncate是ddl而不是dml。
(3). 尽量多使用commit
只要有可能就在程序中对每个delete,insert,update操作尽量多使用commit,这样系统性能会因为commit所释放的资源而大大提高。
(4). 用exists替代in ,可以提高查询的效率。
(5). 用not exists 替代 not in
(6). 优化group by
提高group by语句的效率,可以将不需要的记录在group by之前过滤掉。如:
低效:select job, avg(sal) from emp group by job having job = ‘president’ or job=’manager’;
高效: select job, avg(sal) from emp having job=’president’ or job=’manager’ group by job;
(7). 有条件的使用union-all 替代 union:这样做排序就不必要了,效率会提高3到5倍。
(8). 分离表和索引
总是将你的表和索引建立在不同的表空间内,决不要将不属于oracle内部系统的对象存放到system表空间内。同时确保数据表空间和索引表空间置于不同的硬盘控制卡控制的硬盘上。



Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=623666


只有注册用户登录后才能发表评论。


网站导航:
 

posts - 4, comments - 5, trackbacks - 0, articles - 60

Copyright © ikingqu