Oracle SQL性能诊断与调试信息采集方法

    经常见到有人在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:scriptssqlhc.sql   T  <sql_id>    

两个参数,第一个参数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: