[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;