[20210926]使用dbms_workload_repository.add_colored_sql.txt

来源：这里教程网时间：2026-03-03 17:01:28 作者：

[20210926]使用dbms_workload_repository.add_colored_sql.txt --//生产系统一条ql语句感觉有一些怪异,awr收到的信息以及查询DBA_HIST_SQLSTAT视图都很奇怪,使用 --//dbms_workload_repository.add_colored_sql标注观察看看,做一个记录: 1.环境: SYS@127.0.0.1:xxxx/zzzz> @ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production --//sql_id = 7ksrtc8rzpawc SELECT a.object_name, c.sid, CASE TO_CHAR(b.locked_mode) WHEN '0' THEN 'NONE' WHEN '1' THEN 'NULL' WHEN '2' THEN 'ROW-S (RS)' WHEN '3' THEN 'ROW-X (RX)' WHEN '4' THEN 'SHARE (S)' WHEN '5' THEN 'S/ROW-X (SRX)' WHEN '6' THEN 'Exclusive (X)' ELSE TO_CHAR(b.locked_mode) END locked_mode, c.SERIAL#, b.process, c.program, c.SQL_ADDRESS FROM all_objects a, sys.gv_$locked_object b, sys.GV_$SESSION C WHERE a.object_id = b.object_id AND b.process = c.process ORDER BY a.object_name --//后记:仔细查看才发现华为的研发写错了,应该写成如下: SELECT a.object_name, c.sid, CASE TO_CHAR(b.locked_mode) WHEN '0' THEN 'NONE' WHEN '1' THEN 'NULL' WHEN '2' THEN 'ROW-S (RS)' WHEN '3' THEN 'ROW-X (RX)' WHEN '4' THEN 'SHARE (S)' WHEN '5' THEN 'S/ROW-X (SRX)' WHEN '6' THEN 'Exclusive (X)' ELSE TO_CHAR(b.locked_mode) END locked_mode, c.SERIAL#, b.process, c.program, c.SQL_ADDRESS FROM all_objects a, sys.gv_$locked_object b, sys.GV_$SESSION C WHERE a.object_id = b.object_id AND b.process = c.process AND b.INST_ID = c.INST_ID ~~~~~~~~~~~~~~~~~~~~~~~~~ and b.SESSION_ID =c.SID ~~~~~~~~~~~~~~~~~~~~~~~~~~ ORDER BY a.object_name --//b.process = c.process 连接条件加上与不加上应该不影响查询结果. --//华为研发真应该给自己打脸,太丢人了,估计拿着单机版本的数据库做的测试.无语!!想当然以为process字段唯一的. --//在我看来给客户发现研发的错误是非常丢脸的事情. SELECT COUNT (*) FROM (SELECT process FROM gv$session WHERE INST_ID = 1 INTERSECT SELECT process FROM gv$session WHERE INST_ID = 2) COUNT(*) ---------- 1741 --//虽然windwos的process看起来类似1084:1080这样,但是如果反查: select * from gv$session where process='1084:1080'; --//结果我不贴出了. --//可以发现我们的程序非常变态,1个程序要打开4个连接,其中有1个连接到另外的实例. 2.测试: SYS@127.0.0.1:xxxx/zzzz> select sysdate from dual ; SYSDATE ------------------- 2021-09-26 08:49:06 SYS@127.0.0.1:xxxx/zzzz> exec dbms_workload_repository.add_colored_sql('7ksrtc8rzpawc'); PL/SQL procedure successfully completed. SYS@127.0.0.1:xxxx/zzzz> select * from DBA_HIST_COLORED_SQL ; DBID SQL_ID CREATE_TIME ---------- ------------- ------------------- 2417323702 7ksrtc8rzpawc 2021-09-26 08:49:14 SYS@127.0.0.1:xxxx/zzzz> select * from sys.wrm$_colored_sql; DBID SQL_ID OWNER CREATE_TIME ---------- ------------- --------- ------------------- 2417323702 7ksrtc8rzpawc 1 2021-09-26 08:49:14 2.等一段时间看看awr报表: --//我看了9-10点的awr报表,仅仅出现在SQL ordered by Sharable Memory,SQL ordered by Version Count SQL ordered by Sharable Memory Only Statements with Sharable Memory greater than 1048576 are displayed Sharable Mem (b) Executions % Total SQL Id SQL Module SQL Text 194,528,928 1.39 7ksrtc8rzpawc SELECT a.object_name, c.sid, C... 104,135,664 0.74 5r14h528vkacs select to_char(min(start_time --//明天继续观察,观察时间2021/09/28 09/12/30 $ rlsql -s -l sys/Password_106@127.0.0.1:xxxx/zzzz as sysdba <<< "@ sqlh 7ksrtc8rzpawc 60550 " | awk '$10>=0 || $10='0'{ print $0}' SNAP_ID INSTANCE_NUMBER PLAN_HASH_VALUE BEGIN_INTERVAL_TIME END_INTERVAL_TIME ELAPSED_TIME_DELTA CPU_TIME_DELTA EXECUTIONS_DELTA EXECUTIONS_TOTAL ROWS_PROCESSED_DELTA BUFFER_GETS_DELTA LOADED_VERSIONS 60550 1 3030673966 2021-09-24 13:00:31.390 2021-09-24 14:00:39.168 2171154 2163667 0 0 0 202 35 60550 1 4164392588 2021-09-24 13:00:31.390 2021-09-24 14:00:39.168 8964592 8696676 11 12145 0 2596341 37 60551 1 3030673966 2021-09-24 14:00:39.168 2021-09-24 15:00:47.185 2537991 2519622 0 0 0 226 47 60551 1 4164392588 2021-09-24 14:00:39.168 2021-09-24 15:00:47.185 4595819 4449324 12 12157 0 480638 47 60552 1 3030673966 2021-09-24 15:00:47.185 2021-09-24 16:00:54.814 2663995 2575604 0 0 0 281 57 60552 1 4164392588 2021-09-24 15:00:47.185 2021-09-24 16:00:54.814 18936715 17793290 12 12169 0 3859239 59 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 60553 1 3030673966 2021-09-24 16:00:54.814 2021-09-24 17:00:02.131 2930805 2794577 0 0 0 289 67 60553 1 4164392588 2021-09-24 16:00:54.814 2021-09-24 17:00:02.131 17185326 15880588 11 12180 0 3006125 69 60554 1 3030673966 2021-09-24 17:00:02.131 2021-09-24 18:00:08.949 2845827 2772578 0 0 0 310 72 60554 1 4164392588 2021-09-24 17:00:02.131 2021-09-24 18:00:08.949 17543958 16821439 12 12192 0 3788169 74 60555 1 3030673966 2021-09-24 18:00:08.949 2021-09-24 19:00:16.374 2472227 2459627 0 0 0 306 80 60555 1 4164392588 2021-09-24 18:00:08.949 2021-09-24 19:00:16.374 11726661 11607235 12 12204 0 3911169 80 60556 1 3030673966 2021-09-24 19:00:16.374 2021-09-24 20:00:23.301 2107047 2098680 0 0 0 263 91 60556 1 4164392588 2021-09-24 19:00:16.374 2021-09-24 20:00:23.301 7760131 7699825 13 12217 0 1743354 92 60602 1 3030673966 2021-09-26 17:00:40.432 2021-09-26 18:00:47.512 2933909 2744578 0 0 0 291 74 60602 1 4164392588 2021-09-26 17:00:40.432 2021-09-26 18:00:47.512 15417212 14824749 12 12761 0 2436171 76 60603 1 3030673966 2021-09-26 18:00:47.512 2021-09-26 19:00:54.580 2476196 2464624 0 0 0 241 51 60603 1 4164392588 2021-09-26 18:00:47.512 2021-09-26 19:00:54.580 6304023 6193061 12 12773 0 836437 52 60604 1 3030673966 2021-09-26 19:00:54.580 2021-09-26 20:00:01.259 2329792 2296649 0 0 0 223 38 60604 1 4164392588 2021-09-26 19:00:54.580 2021-09-26 20:00:01.259 4467477 4316343 12 12785 0 480755 39 60605 1 3030673966 2021-09-26 20:00:01.259 2021-09-26 21:00:08.801 2292893 2283651 0 0 0 250 46 60605 1 4164392588 2021-09-26 20:00:01.259 2021-09-26 21:00:08.801 8601945 8548701 12 12797 0 2383295 47 60606 1 3030673966 2021-09-26 21:00:08.801 2021-09-26 22:00:16.185 2315537 2311647 0 0 0 238 58 60606 1 4164392588 2021-09-26 21:00:08.801 2021-09-26 22:00:16.185 6663848 6607996 12 12809 0 1547417 59 60607 1 3030673966 2021-09-26 22:00:16.185 2021-09-26 23:00:23.674 2277466 2272657 0 0 0 237 69 60607 1 4164392588 2021-09-26 22:00:16.185 2021-09-26 23:00:23.674 6983427 6936945 12 12821 0 1760850 70 60608 1 3030673966 2021-09-26 23:00:23.674 2021-09-27 00:00:30.047 2346573 2332640 0 0 0 222 81 60608 1 4164392588 2021-09-26 23:00:23.674 2021-09-27 00:00:30.047 4585585 4536311 12 12833 0 693810 82 60609 1 3030673966 2021-09-27 00:00:30.047 2021-09-27 01:00:36.974 2326362 2311648 0 0 0 228 91 60609 1 4164392588 2021-09-27 00:00:30.047 2021-09-27 01:00:36.974 5337363 5278198 12 12845 0 836276 93 --//18936715/10^6/12 = 1.578秒,有点慢.不是太慢.总共16秒不到. --//查询awr snap_id=60551,6055报表,也仅仅出现在SQL ordered by Sharable Memory部分. SQL ordered by Sharable Memory Only Statements with Sharable Memory greater than 1048576 are displayed Sharable Mem (b) Executions % Total SQL Id SQL Module SQL Text 33,993,403 12 0.24 7ksrtc8rzpawc JDBC Thin Client SELECT a.object_name, c.sid, C... 31,845,595 12 0.22 7ksrtc8rzpawc JDBC Thin Client SELECT a.object_name, c.sid, C... 18,671,197 24 0.13 5r14h528vkacs JDBC Thin Client select to_char(min(start_time)... 17,779,445 24 0.12 5r14h528vkacs JDBC Thin Client select to_char(min(start_time)... --//sql_id=5r14h528vkacs也在SQL ordered by Elapsed Time部分.它的执行时间有点长. SQL ordered by Elapsed Time Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100 %Total - Elapsed Time as a percentage of Total DB time %CPU - CPU Time as a percentage of Elapsed Time %IO - User I/O Time as a percentage of Elapsed Time Captured SQL account for 66.7% of Total DB Time (s): 46,970 Captured PL/SQL account for 6.1% of Total DB Time (s): 46,970 Elapsed Time (s) Executions Elapsed Time per Exec (s) %Total %CPU %IO SQL Id SQL Module SQL Text ... 1,218.97 24 50.79 2.60 36.48 63.65 5r14h528vkacs JDBC Thin Client select to_char(min(start_time)... ... --//我以为标注后会出现在SQL ordered by Elapsed Time部分,实际上使用dbms_workload_repository.add_colored_sql仅仅保证出现在awr报表(我估计). 3.收尾： SYS@127.0.0.1:xxxx/zzzz> exec dbms_workload_repository.remove_colored_sql('7ksrtc8rzpawc'); PL/SQL procedure successfully completed. SYS@127.0.0.1:xxxx/zzzz> select * from sys.wrm$_colored_sql; no rows selected

编辑推荐：

返回列表

[20210926]使用dbms_workload_repository.add_colored_sql.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推