--//有时候上班业务突然出现异常，想快速了解昨天晚上那些表做了分析。
--//可以查看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
