[20210926]使用dbms_workload_repository.add_colored_sql.txt
[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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2794113/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210926]關於pam_tally2模組.txt
- [20210926]並行執行計劃疑問.txt並行
- [20210926]DBA_HIST_SQLSTAT.ELAPSED_TIME_DEL他的單位.txtSQL
- laravel使用EasyWeChat 使用Laravel
- 如何使用使用 HAVING 與 ORDER BY？
- winscp使用教程多使用者，winscp使用教程多使用者，教程詳情
- 配置vsftpd匿名使用服務，個人使用者使用以及虛擬使用者使用配置細節！FTP
- 使用 CSS 追蹤使用者CSS
- RecyclerView使用指南（四）—— 使用ItemDecorationView
- RecyclerView使用指南（一）—— 基本使用View
- mongodb使用者與角色使用MongoDB
- Urllib庫的使用一---基本使用
- Vivado使用技巧（19）：使用Vivado Simulator
- TestContainer使用者使用經驗AI
- ImageJ使用教程（一）：開始使用
- Laravel passport 多端使用者使用LaravelPassport
- 使用dwebsocket在Django中使用WebsocketWebDjango
- 使用者及使用者組管理使用的練習
- jumpserver 使用者,系統使用者和管理使用者 普通使用者和特權使用者 區別Server
- 使用Bootstrap tab頁切換的使用boot
- PyCharm使用技巧（六）：Regullar Expressions的使用PyCharmExpress
- 使用普通使用者執行 dockerDocker
- Scrapy框架的使用之Scrapyrt的使用框架
- 在C#中使用OpenCV（使用OpenCVSharp）C#OpenCV
- 使用者組和使用者
- Docker 使用者操作使用說明Docker
- 使用PyCharm引入需要使用的包PyCharm
- 使用jquery和使用框架的區別jQuery框架
- 儘量使用 useReducer，不要使用 useStateuseReducer
- 24. 使用MySQL之使用遊標MySql
- 22. 使用MySQL之使用檢視MySql
- 使用者授權，策略的使用
- 使用 JWT 認證使用者身份JWT
- 2、Spring使用+ioc容器配置使用1Spring
- (四)Linux之使用者管理（使用者和使用者組）Linux
- RxPermission使用
- crontab 使用
- 使用 Anaconda