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