经常见到有人在QQ群和微信群发SQL代码和(或)简单执行计划的截屏咨询优化问题,如果是比较简单的SQL,也没问题; 如果是稍微复杂一点的SQL, 特别是一些根因分析类问题, 建议收集尽可能详细的信息.
如果你在oracle 的技术支持网站MOS(My Oracle Support)提交SQL诊断的SR(service request), 提供服务的工程师非常有可能要求上传sql的sqlhc信息,或者是SQLT(比sqlhc更全面的信息采集, 一般情况下不需要)信息.
sqlhc采集,需要:
sqlplus客户端工具
sqlhc.sql脚本(MOS doc: 1366133.1 可以下载),
DBA权限数据库用户
sqlplus user/passwd@xxx (直接在数据库服务器上执行也可以)
SQL>@e:\scripts\sqlhc.sql T
两个参数,第一个参数T,第二个参数是你要采集的sql_id(下面红色部分换成你的sql_id),如果sqlhc.sql就在当前目录,可以简写成:
SQL>@sqlhc T aykvshm7zsabd
几分钟后,会在当前目录下生成一个sqlhc开头的zip文件,就是采集到的sqlhc信息.
如果zip文件里面包含一个sqlhc_xxxxxxxx_5_sql_monitor.zip, 那么这个信息就算是收集完整了.
如果生成的sqlhc_xxx.zip文件里面没有包含sql_monitor.zip文件, 或者你的数据库不是11g及以上版本, 还要再采集sql执行过程的真实信息:
方法1(10g版本也适用):
sqlplus appuser/passwd@xxx
SQL>alter session set statistics_level=all;
SQL>执行你的业务sql
说明:
如果sql使用了绑定变量,最好是先定义绑定变量,再赋值执行,如:
SQL>var b1 number
SQL>exec :b1:=100
SQL>select count(*) from t1 where object_id=:b1;
或者用一组常量直接替换绑定变量;
SQL>select count(*) from t1 where object_id=100;
注意:
sqlplus里面不能使用:1 , :2 这样的绑定变量
sqlplus里面不能使用date/timestamp等绑定变量类型
遇到这种情况,可以到pl/sql developer里面执行,详见下文
如果返回的结果集比较大,建议在sql外面再套一层,对其中某几个字段做sum或count
如果是dml语句, 执行完后再rollback;
SQL>set linesize 200 pagesize 300
SQL>spool plan.log
SQL>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
SQL>spool off
生成了plan.log 文件, 包含了sql执行过程中的真实信息.
方法2:(类似方法1)
把 alter session set statistics_level=all; 步骤, 替换成在业务sql中使用hint
SQL>select /*+ gather_plan_statistics */count(*) from t1 where object_id=100;
后面步骤同方法1.
方法2的hint容易写错,而且调试多个sql时每个sql都有使用hint, 不如方法1简单.
下图就是用上面方法获取的执行计划信息的一个实例:
两个大红框之外的信息,对sql优化的诊断和调试,非常重要.
方法3: 需要11g及以上版本(active格式需要11gR2及以上版本)
业务sql增加monitor的hint, 生成sql monitor文件:
SQL> select /*+ monitor tag001 */ count(*) from t1;
如果sql执行时间不长, 可以等sql结束后,用下面代码保存sql monitor文件(不需要sqlid信息,默认采集刚刚执行过的sqlid):
set linesize 10000 pages 6000
set longchunksize 20000000 long 20000000
set trimout on trims on head off
spool sqlmon.html
select
DBMS_SQLTUNE.REPORT_SQL_MONITOR(
report_level=>'ALL',
type=>'active') as report
from dual;
spool off
执行完后,就在当前目录下生成了sqlmon.html 文件,即为所需sql monitor文件.
其中active可以改成text, 可以不借助浏览器查看; 复杂sql推荐使用active.
如果你有本人的ora私家工具, 可以用ora monlist 获取sql的sqlid,再用ora monsave sql_id保存sql monitor文件.
如果sql执行时间很长, 可以不需要等待sql执行结束,在sql执行一段时间后即可保存sql monitor文件:
需要先查到业务sql对应的sqlid信息(业务sql的hint里面加tag001的意义就是为了方便查找sqlid):
select sql_id , to_char(substr(sql_text,1,200)) as sql_text
from gv$sqlarea
where upper(SQL_TEXT) like upper('%tag001%')
and SQL_TEXT not like '%SQL_TEXT%';
得到sqlid后,就可以用下面脚本保存sql monitor文件了:
set linesize 10000 pages 6000
set longchunksize 20000000 long 20000000
set trimout on trims on head off
spool sqlmon.html
select
DBMS_SQLTUNE.REPORT_SQL_MONITOR(
sql_id=>'&sql_id',
report_level=>'ALL',
type=>'active') as report
from dual;
--先copy上面代码,根据提示输入sqlid,再执行:
spool off
也可以把上面代码保存成getmon.sql
SQL>@getmon
然后根据提示输入sqlid,同样能保存sql monitor文件
sql monitor用浏览器打开后的样子:
sql monitor也可以通过oracle 的em 实时查看和保存.
sqlhc信息一定要在sqlplus下收集.
补充信息的收集,可以在sqlplus下完成,也可以在pl/sql developer下实现:
pl/sql developer 工具使用F5得到的执行计划信息,适合分析简单的SQL,不适合分析复杂SQL.
使用pl/sql developer 得到与前面方法1和方法2一样sql真实执行计划及详细信息:
在sql window下执行(其中b1对应的是绑定变量)
declare
b1 date;
begin
execute immediate 'alter session set statistics_level = ALL';
b1:=sysdate-1;
for test in
(
-- 用你的业务sql替换下面的示例sql,后面不要加 ";"):
select /*+ monitor tag001 */count(*) from t1 where created>b1
)
loop
null;
end loop;
for x in (
select p.plan_table_output
from table(dbms_xplan.display_cursor(null,null,' allstats last')) p
)
loop
dbms_output.put_line(x.plan_table_output);
end loop;
rollback;
end;
/
然后可以在"output"页面得到所需真实执行计划信息.
用pl/sql developer 保存sql monitor:
看图吧:
(完)
4418
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
