[20231017]使用dbms_workload_repository.add_colored_sql之2.txt

[20231017]使用dbms_workload_repository.add_colored_sql之2.txt

--//生產系統有一條語句存在效能問題。

1.環境:
SYS@192.168.100.235:1521/orcl> @ pr
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.問題:

SYS@192.168.100.235:1521/orcl> @ ashtop sql_id,module1 1=1 &day
    Total                                                                                             Distinct Distinct
  Seconds     AAS %This   SQL_ID        MODULE1              FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps
--------- ------- ------- ------------- -------------------- ------------------- ------------------- ---------- --------
    13275      .2   15% |                                    2023-10-09 11:13:29 2023-10-10 11:13:26          1     9935
    11624      .1   13% |               backup incr datafile 2023-10-09 23:19:33 2023-10-10 00:14:01          1     3260
    10858      .1   12% |               w3wp.exe             2023-10-09 11:13:29 2023-10-10 11:13:22          6     7621
     7246      .1    8% | 675pg97p5artg w3wp.exe             2023-10-09 11:13:29 2023-10-10 09:33:52          4     3624
     5977      .1    7% |               jdbc thin client     2023-10-09 11:17:57 2023-10-10 11:04:31          1     5977
     5376      .1    6% | 63f20n958qv99 w3wp.exe             2023-10-09 11:18:56 2023-10-10 11:08:43       4296     2888
     3480      .0    4% | cyqv7gknyf7bh w3wp.exe             2023-10-10 01:00:03 2023-10-10 04:26:51       2307     3480
      923      .0    1% | g1v8dts358gq5 w3wp.exe             2023-10-09 11:21:51 2023-10-10 11:13:23        259      780
....
30 rows selected.
--//sql_id=675pg97p5artg,一天的時間內僅僅執行4次.每次7246/4 = 1811.5,差不多每次30分鐘。
--//建立索引最佳化後，我想觀察最佳化效果，發現awr不再記錄該語句執行情況。平時執行太少，我查詢v$sqlarea也沒有結果，
--//也就是該語句根本不在共享池裡面。
--//為了記錄該語句的執行情況想到了dbms_workload_repository.add_colored_sql。

SYS@192.168.100.235:1521/orcl> exec dbms_workload_repository.add_colored_sql('675pg97p5artg');
PL/SQL procedure successfully completed.

SYS@192.168.100.235:1521/orcl> select * from DBA_HIST_COLORED_SQL ;
      DBID SQL_ID        CREATE_TIME             CON_ID
---------- ------------- ------------------- ----------
1585360079 675pg97p5artg 2023-10-18 08:33:50          0

SYS@192.168.100.235:1521/orcl> column owner format 99999999
SYS@192.168.100.235:1521/orcl> select * from sys.wrm$_colored_sql;
      DBID SQL_ID            OWNER CREATE_TIME
---------- ------------- --------- -------------------
1585360079 675pg97p5artg         1 2023-10-18 08:33:50
1 row selected.

--//我有一種感覺執行次數太少，也許在一個小時之內已經不再共享池，有可能抓取不到相關執行資訊。還是等待一定的時間觀察看看。

3.等待時間觀察。

SYS@192.168.100.235:1521/orcl> @ sqlhh 675pg97p5artg 5
time unit : millisecond

BEGIN_INTERVAL_TIME    INST_ID SQL_ID        PLAN_HASH_VALUE EXECUTIONS ELA_MS_PER_EXEC CPU_MS_PER_EXEC ROWS_PER_EXEC LIOS_PER_EXEC BLKRD_PER_EXEC IOW_MS_PER_EXEC  AVG_IOW_MS CLW_MS_PER_EXEC APW_MS_PER_EXEC CCW_MS_PER_EXEC
------------------- ---------- ------------- --------------- ---------- --------------- --------------- ------------- ------------- -------------- --------------- ----------- --------------- --------------- ---------------
2023-10-16 11:00:22          1 675pg97p5artg      3495262713          5           76679            1725           0.0        257389          13647           75734         5.5               0               0               3
2023-10-16 12:00:25          1 675pg97p5artg      3495262713          1         6509723          117415           0.0      17008226         617391         6430447        10.4               0               0            1240
2023-10-16 17:00:38          1 675pg97p5artg      3255412359          1          438355           27841           0.0       1549877        1549299          433246        35.7               0              54               0
2023-10-16 17:00:38          1 675pg97p5artg      3495262713          2            6031            2828           0.0        959377            669            3237         4.8               0               0               0
2023-10-18 15:00:39          1 675pg97p5artg      1475918621          3               6               3           0.0             5              0               3         9.4               0               0               0
2023-10-19 10:00:30          1 675pg97p5artg      1475918621          1               9               1           0.0             3              1               8         8.1               0               0               0
6 rows selected.
--//現在選擇的執行計劃很好,返回行數居然是0.

4.收尾：
--//exec dbms_workload_repository.remove_colored_sql('675pg97p5artg');
--//select * from sys.wrm$_colored_sql;