4、修正SQL执行计划
--如前面可知,由于索引不可用导致了SQL语句执行了全表扫描。 --事实上导致全表扫描的问题很多,若使用谓词列函数,谓词列数据类型转换,使用不等于,以及谓词列参与计算等,不一一列出 --针对上面的情形,我们应当收集统计信息以及重建索引 scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true); BEGIN dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true); END;
* ERROR at line 1: ORA-20000: index "SCOTT"."BIG_TABLE_PK" or partition of such index is in unusable state ORA-06512: at "SYS.DBMS_STATS", line 13182 ORA-06512: at "SYS.DBMS_STATS", line 13202 ORA-06512: at line 1
--上面再收集统计信息时,提示索引不可用,需要先rebulid
scott@SYBO2SZ> alter index i_big_tb_owner rebuild nologging;
scott@SYBO2SZ> alter index big_table_pk rebuild nologging;
scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);
--下面我们再次执行原SQL以及,由下可知,SQL已经使用了最优的执行计划 scott@SYBO2SZ> set autot trace exp; scott@SYBO2SZ> select count(*) from big_table where owner='GOEX_ADMIN';
Execution Plan ---------------------------------------------------------- Plan hash value: 334839806
------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 6 | 108 (1)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | INDEX RANGE SCAN| I_BIG_TB_OWNER | 44750 | 262K| 108 (1)| 00:00:02 | ------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("OWNER"='GOEX_ADMIN') |
5、后记
a、示例中创建的big_table脚本,请参考:Oracle 测试常用表BIG_TABLE
b、alter table move 方式用于实现段收缩,移动高水位,但不会释放申请的空间,以及导致索引失效
c、对于历史SQL语句,需要执行snapshot之后,才会被填充到DBA_HIST_SQL_PLAN、DBA_HIST_SQLSTAT、DBA_HIST_SNAPSHOT数据字典中
d、如果你的测试无法获得历史SQL语句及其执行计划,通常是由于awr阀值设置所致,可参考:Oracle AWR 阙值影响历史执行计划
e、历史SQL语句的执行计划也可以通过$ORACLE_HOME/rdbms/admin/awrsqrpt.sql来生成txt或html文件
f、引起同一SQL执行计划发生变化的情形很多,如统计信息的缺失,索引失效,不同级别的参数发生变化等
h、对于实例,会话,语句级别环境变化导致同一SQL执行计划发变异,也可以对此跟踪。参考:使用优化器性能视图获取SQL语句执行环境