DBA_HIST_SQLSTAT等系统字典可以用来分析sql的历史执行情况,直接查看并不方便,oracle推出工具可以较为清晰的分析。
1、sql health check 工具-sqlhc
(1)得到某sql的sql_id
(2)解压sqlhc.zip到oracle用户下
(3)执行sqlhc.sql
sqlplus / as sysdba
SQL> START sqlhc.sql "T" 1477v1yvnvx5s
(4)得到 sqlhc_20211229_1351_1477v1yvnvx5s.zip
上面截图的html文件可以看到此sql所有的执行计划及plan_hash_value,我们可以通过baseline绑定plan_hash_value, 做执行计划的绑定:
问题sql创建基线:
declare
joyce_pls number;
begin
joyce_pls := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => 'aaqjvsum9qvcy',
PLAN_HASH_VALUE=>735879891, ---问题执行计划的plan_hash_value
enabled => 'NO'
);
end;
/
查看新创建基线的sql_handle:
select sql_handle,plan_name, ENABLED, ACCEPTED, FIXED, EXECUTIONS,sql_text from dba_sql_plan_baselines ORDER BY CREATED;
绑定正常的PLAN_HASH_VALUE
declare
joyce_pls number;
begin
joyce_pls := DBMS_SPM.load_plans_from_cursor_cache(sql_id => 'g654zn2992dus',
PLAN_HASH_VALUE=>4122059633, ---正常执行计划的plan_hash_value
sql_handle=>'SQL_c45811fe991ba192' );
end;
/
2、sqlt
(1)sqlt简介
sqlt是sql分析比较详细的工具,一般sql分析的顺序是:实际执行计划 -> sql monitor -> sqlhc -> sqlt
(2)安装sqlt
$ unzip sqlt.zip
$ cd sqlt/install
$ sqlplus / as sysdba
SQL> START sqcreate.sql
Password for user SQLTXPLAIN: <password of using sqlt>
Default tablespace [UNKNOWN]:USERS
Temporary tablespace [UNKNOWN]: TEMP
Main application user of SQLT: <application user>
Oracle Pack license [T]:T
(3)使用sqlt生成某sql的报告
$ cd sqlt
$ sqlplus <application user>/<password>
SQL> start run/sqltxtract.sql 1477v1yvnvx5s
Password: <password>
(4)得到sqlt_20211229_1406_1477v1yvnvx5s_S.zip
![](https://img-blog.csdnimg.cn/img_convert/d992bef0809cc82add84cf86143c2a0f.png)
参考文档(mos)
sqlt和sqlhc软件包下载