[20230906]显示最近统计分析的操作报表.txt

[20230906]显示最近统计分析的操作报表.txt --//有时候上班业务突然出现异常，想快速了解昨天晚上那些表做了分析。 --//可以查看DBA_OPTSTAT_OPERATIONS视图，了解做了那些操作。当然许多视图也能了解什么时间做了分析. --//oracle的DBMS_STATS.REPORT_STATS_OPERATIONS也可以实现类似功能测试看看。 1.环境： SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 2.分析表： --//顺便找两个表T1，T2分析看看。 3.测试DBMS_STATS.REPORT_STATS_OPERATIONS: --//然后执行如下： column my_report format a200 variable my_report clob; BEGIN   :my_report := DBMS_STATS.REPORT_STATS_OPERATIONS (      since        => SYSDATE-1 ,    until        => SYSDATE ,    detail_level => 'TYPICAL' ,    format       => 'TEXT' ); END; / SCOTT@test01p> print :my_report MY_REPORT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | CDB Id | Operation Id | Operation          | Target   | Start Time                 | End Time                   | Status    | Total Tasks | Successful Tasks | Failed Tasks | Active Tasks          | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 3      | 5484         | gather_table_stats | SCOTT.T2 | 2023-09-06 21:31:18.340000 | 2023-09-06 21:31:19.922000 | COMPLETED | 2           | 2                | 0            | 0                     | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 3      | 5464         | gather_table_stats | SCOTT.T1 | 2023-09-06 21:31:09.927000 | 2023-09-06 21:31:13.899000 | COMPLETED | 1           | 1                | 0            | 0                     | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 4.可以单独执行DBMS_STATS.REPORT_SINGLE_STATS_OPERATION函数了解一些细节： BEGIN   :my_report :=DBMS_STATS.REPORT_SINGLE_STATS_OPERATION (      OPID    => 5484 ,    FORMAT  => 'TEXT' ); END; / SCOTT@test01p> print my_report MY_REPORT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Operation Id | Operation          | Target   | Start Time                 | End Time                   | Status    | Total Tasks | Successful Tasks | Failed Tasks | Active Tasks                   | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 5484         | gather_table_stats | SCOTT.T2 | 2023-09-06 21:31:18.340000 | 2023-09-06 21:31:19.922000 | COMPLETED | 2           | 2                | 0            | 0                              | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |                                                                                                                                                                                                     | | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |                                                                                              T A S K S                                                                                              | | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    | |    | Target      | Type  | Start Time                 | End Time                   | Status                                                                                                    |    | |    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    | |    | SCOTT.T2    | TABLE | 2023-09-06 21:31:18.353000 | 2023-09-06 21:31:19.903000 | COMPLETED                                                                                                 |    | |    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    | |    | SCOTT.T2_I2 | INDEX | 2023-09-06 21:31:19.196000 | 2023-09-06 21:31:19.831000 | COMPLETED                                                                                                 |    | |    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    | |                                                                                                                                                                                                     | |                                                                                                                                                                                                     | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --//有点太花俏，估计很少查细节。也可以支持html格式。 BEGIN   :my_report :=DBMS_STATS.REPORT_SINGLE_STATS_OPERATION (      OPID    => 5484 ,    FORMAT  => 'HTML' ); END; / SCOTT@test01p> spool  myoutput.html SCOTT@test01p> print my_report ... SCOTT@test01p> spool  off SCOTT@test01p> host "E:\Progra~1\Mozill~1\firefox.exe"  d:\tmp\myoutput.html --//可以打开浏览器查看。不知道为什么仅仅支持"E:\Progra~1\Mozill~1\firefox.exe"这样写,如果有空格解析错误. 4.根据前面测试可以建立脚本如下： $ cat report_stats.sql column my_report format a200 variable my_report clob; BEGIN   :my_report := DBMS_STATS.REPORT_STATS_OPERATIONS (      since        => &1 ,    until        => &2 ,    detail_level => 'TYPICAL' ,    format       => 'TEXT' ); END; / prompt BEGIN prompt   :my_report :=DBMS_STATS.REPORT_SINGLE_STATS_OPERATION ( prompt      OPID    => &OPID prompt ,    FORMAT  => 'TEXT' prompt );; prompt END;; prompt / prompt prompt print :my_report print :my_report