[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