[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
-
lfree
2021-09-28 09:40:06 -
Oracle
- 原创