[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