Vincent.Chan‘s Blog

常用链接

统计

积分与排名

网站

最新评论

SQL优化

最近做项目发现很多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

posted on 2006-03-21 12:40 Vincent.Chen 阅读(209) 评论(0)  编辑  收藏 所属分类: Database


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


网站导航: