要对sql语句进行分析,首先就是要去看sql语句的执行计划是否存在问题,Oracle在10g之后,默认采用CBO基于代价的优化器,sql语句的执行计划根据统计信息分析来决定,如果统计信息未收集,则采取动态采样的方式来决定最优的执行计划!
一: 获取sql语句的执行计划,在使用执行计划前,先以sys用户运行脚本$ORACLE_HOME/sqlplus/admin/plustrce.sql,该脚本创建了plustrace角色,并给该角色查询v$sessstat,v$statname,v$mystat三个动态性能视图的权限;最后将plustrace角色授予普通用户
1:创建测试表,填充数据,创建索引
SQL> create table t as select 1 id,object_name from dba_objects; Table created. SQL> select count(*) from t; COUNT(*) ---------- 77262 SQL> update t set id=99 where rownum=1; 1 row updated. SQL> commit; Commit complete. SQL> select id,count(*) from t group by id; ID COUNT(*) ---------- ---------- 1 77261 99 1 SQL> create index i_t_id on t(id); Index created.
2:获取sql语句的执行计划,‘dynamic sampling used for this statement (level=2)’表示采取级别2的动态采样;执行计划的步骤为靠右靠上先执行,而不是第一列的id顺序,在本例中先执行缩进最靠右的I_T_ID索引范围扫描,然后根据索引扫描出来的结果定位到T表相应行的rowid,谓词中的"2 - access("ID"=99)"表示where后条件id=99会对id为2的INDEX RANGE SCAN造成决定行的影响,这个也很好理解,在本例中如果where语句后面为id=1,则必然选择全表扫描才是最优的执行计划;rows则会返回的结果集行数,统计信息中对应select语句主要看物理读和一致性读的个数
SQL> set autot traceonly SQL> select * from t where id=99; Execution Plan ---------------------------------------------------------- Plan hash value: 4153437776 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time| -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 79 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 79 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | I_T_ID | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=99) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 10 recursive calls 0 db block gets 67 consistent gets 1 physical reads 0 redo size 491 bytes sent via SQL*Net to client 420 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed