[20200422]跟蹤特定sql語句以及v$open_cursor檢視(補充).txt
[20200422]跟蹤特定sql語句以及v$open_cursor檢視(補充).txt
--//上個星期的測試,連結如下:
http://blog.itpub.net/267265/viewspace-2686681/=> [20200417]跟蹤特定sql語句以及v$open_cursor檢視2.txt
http://blog.itpub.net/267265/viewspace-2686680/=> [20200417]跟蹤特定sql語句以及v$open_cursor檢視.txt
--//可是我跟蹤生產系統特定sql語句,發現:
$ ls -ltr *.trc | grep "2020-04-17 23:" | awk '{print $8}' | xargs -IQ grep -l 0pv15fhky1mbp Q
xxxx1_ora_66907.trc
xxxx1_ora_89016.trc
xxxx1_ora_89269.trc
xxxx1_ora_27151.trc
xxxx1_ora_49176.trc
--//注意我ls定義的別名如下,不然無法顯示全日期格式,grep過濾無效.
$ alias ls
alias ls='ls --color=auto --time-style=+"%Y-%m-%d %H:%M:%S"'
$ ls -ltr *.trc | grep "2020-04-17 23:" | awk '{print $8}' | xargs -IQ grep -l 0pv15fhky1mbp Q > /tmp/bb1.txt
# cat /tmp/bb1.txt | xargs -IQ bash -c "echo Q;grep 0pv15fhky1mbp Q| awk '{print \$4}'| uniq -c"
xxxx1_ora_66907.trc
45 #140080227777816
xxxx1_ora_89016.trc
1 #139810917052128
3 #139810914124832
20 #139810914330520
xxxx1_ora_89269.trc
4 #140446340344624
196 #140446337461080
xxxx1_ora_27151.trc
232 #140521932868888
xxxx1_ora_49176.trc
1 #139947945946848
3 #139947943056416
43 #139947943262104
--//並沒有看到這麼多的PARSING IN CURSOR #NNNNNNNN的情況。僅僅變化幾個。估計生產系統exadata還打了其它補丁。
--//我的測試環境:
$ /u01/app/oracle/product/11.2.0.4/dbhome_1/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0.4/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0.4/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.4
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/opatch/opatch2020-04-22_08-29-44AM_1.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2020-04-22_08-29-44AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.
--//生產系統:
$ /u01/app/oracle/product/11.2.0.4/dbhome_1/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0.4/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0.4/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.6
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/opatch/opatch2020-04-22_08-30-27AM_1.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2020-04-22_08-30-27AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 11g 11.2.0.4.0
There are 1 product(s) installed in this Oracle Home.
Interim patches (2) :
Patch 18522515 : applied on Fri Oct 10 21:18:53 CST 2014
Unique Patch ID: 17929068
Patch description: "OCW Patch Set Update : 11.2.0.4.3 (18522515)"
Created on 08 Aug 2014, 08:05:07 hrs PST8PDT
Bugs fixed:
18024089, 18428146, 18328800, 18187697, 14525998, 18352845, 17391726
17750548, 17387214, 18414137, 17001914, 17927970, 16346413, 17551223
15832129, 17305100, 18272135, 18180541, 17985714, 17292250, 17378618
16206997, 17500165, 16876500, 16429265, 17065496, 18343490, 18848125
18336452, 13991403, 16613232, 17955615, 14693336, 17273003, 17273020
17238586, 17089344, 12928658, 18226143, 17531342, 17172091, 18229842
17155238, 17159489, 16543190, 17039197, 17483479, 17947785, 16317771
10052729, 17481314, 18199185, 17405302, 18399991
Patch 19307276 : applied on Fri Oct 10 21:18:14 CST 2014
Unique Patch ID: 17929068
Patch description: "DATABASE PATCH FOR EXADATA (SEP2014 - 11.2.0.4.11) : (19307276)"
Created on 8 Aug 2014, 01:38:14 hrs PST8PDT
Sub-patch 19061859; "DATABASE PATCH FOR EXADATA (AUG2014 - 11.2.0.4.10) : (19061859)"
Sub-patch 18825509; "DATABASE PATCH FOR EXADATA (JUL2014 - 11.2.0.4.9) : (18825509)"
Sub-patch 18642122; "DATABASE PATCH FOR EXADATA (JUN2014 - 11.2.0.4.8) : (18642122)"
Sub-patch 18552960; "DATABASE PATCH FOR EXADATA (MAY2014 - 11.2.0.4.7) : (18552960)"
Sub-patch 18293775; "DATABASE PATCH FOR EXADATA (APR2014 - 11.2.0.4.6) : (18293775)"
Sub-patch 18136151; "DATABASE PATCH FOR EXADATA (MAR2014 - 11.2.0.4.5) : (18136151)"
Sub-patch 18006299; "DATABASE PATCH FOR EXADATA (FEB2014 - 11.2.0.4.4) : (18006299)"
Sub-patch 17943261; "DATABASE PATCH FOR EXADATA (JAN2014 - 11.2.0.4.3) : (17943261)"
Sub-patch 17741631; "DATABASE PATCH FOR EXADATA (DEC 2013 - 11.2.0.4.2) : (17741631)"
Sub-patch 17628006; "DATABASE PATCH FOR EXADATA (NOV 2013 - 11.2.0.4.1) : (17628006)"
Bugs fixed:
17288409, 16188701, 16930924, 17205719, 17811429, 17501296, 17922254
17754782, 17726838, 13364795, 17311728, 15834345, 18418934, 16809786
17284817, 17441661, 16477664, 13645875, 16992075, 14193240, 18199537
16542886, 17446237, 17889549, 14015842, 14565184, 18324129, 18317132
17071721, 17610798, 17449815, 17375354, 17397545, 17265093, 18230522
13866822, 17235750, 16360112, 17982555, 17478514, 12905058, 14338435
18961101, 13944971, 17811789, 16929165, 12747740, 18867023, 17230905
17546973, 18851298, 14054676, 17088068, 16885125, 18264060, 17346091
18317074, 18780342, 17016369, 17042658, 14602788, 18686405, 19240264
17158214, 14657740, 17332800, 17775506, 16199543, 13951456, 18483595
16315398, 17186905, 18744139, 16850630, 17437634, 17561405, 18767554
19049453, 17883081, 17836820, 17296856, 18674024, 14333054, 17006183
18277454, 17232014, 17249711, 16855292, 10136473, 17705023, 17179434
17997507, 17865671, 18554871, 17635021, 18331944, 18966843, 19146626
18879319, 17551709, 17588480, 18304997, 17344412, 17842825, 18681862
17390160, 13955826, 16306373, 13609098, 18139690, 17501491, 17239687
17752121, 17299889, 17602269, 18316692, 17313525, 18818847, 18293054
17242746, 18025431, 17600719, 18191164, 17571306, 17951233, 18094246
18419770, 17040527, 17165204, 17011832, 18098207, 16785708, 17465741
16180763, 17477958, 17174582, 18522509, 12982566, 16971958, 17323222
16875449, 16524926, 17237521, 16980342, 14822091, 17596908, 13982287
18665986, 17811438, 17811447, 18031668, 16912439, 16494615, 14373152
18077632, 18061914, 17540469, 18760484, 13512939, 17545847, 17082359
17614134, 17341326, 14034426, 17891946, 18339044, 17716305, 18133214
18074623, 17496884, 17752995, 16392068, 17209410, 17736165, 17767676
12608451, 17205005, 18384391, 17614227, 17040764, 17381384, 18973907
15913355, 17389192, 14084247, 17612828, 17006570, 18663279, 13853126
17721717, 17390431, 17570240, 18203837, 14245531, 18456874, 16043574
16863422, 17877323, 18325460, 17402822, 14486653, 17468141, 14313306
17786518, 17912217, 14460384, 18226122, 17037130, 18244962, 18765602
18203838, 16198143, 16956380, 17478145, 17394950, 14829250, 17835627
18619917, 17027426, 14000767, 16268425, 19257578, 18247991, 17839474
14458214, 12716670, 18436307, 16618055, 17265217, 13498382, 16692232
17786278, 17227277, 17734862, 16042673, 16314254, 17443671, 17952061
16228604, 18154779, 18061271, 18783969, 16837842, 17393683, 13498243
18247351, 17787259, 18025214, 18009564, 13073613, 15861775, 14764840
18135678, 18614015, 18134680, 16399083, 18191542, 18192858, 17082612
18018515, 17835048, 16472716, 18830412, 18508266, 17050888, 17936109
14010183, 17325413, 16466530, 17799428, 18832544, 17080436, 16613964
17036973, 17761775, 16721594, 16579084, 18280813, 15979965, 13651346
18203835, 17297939, 16731148, 17811456, 18205490, 14133975, 17385178
16830594, 17586955, 16450169, 9756271, 17655634, 17357979, 17892268
18160822, 17648596, 16220077, 16069901, 11733603, 16285691, 17587063
18180390, 18485835, 17393915, 17348614, 18096714, 13816053, 17238511
17824637, 14285317, 13877071, 14309884, 14764829, 18328509, 17622427
16943711, 17346671, 14852021, 18996843, 17783588, 16618694, 18674047
17672719, 17851160, 17546761, 17799716
Rac system comprising of multiple nodes
Local node = dm01dbadm01
Remote node = dm01dbadm02
--------------------------------------------------------------------------------
OPatch succeeded.
--//估計生產系統修復這個問題。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2687519/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20200424]跟蹤特定sql語句以及v$open_cursor檢視(再補充).txtSQL
- [20200417]跟蹤特定sql語句以及v$open_cursor檢視.txtSQL
- [20200417]跟蹤特定sql語句以及v$open_cursor檢視2.txtSQL
- [20130226]跟蹤特定IP的sql語句.txtSQL
- phalcon:跟蹤sql語句SQL
- [20150527]跟蹤單個sql語句.txtSQL
- sqlplus 跟蹤sql語句SQL
- [20160704]從跟蹤檔案抽取sql語句.txtSQL
- 使用10046跟蹤sql語句SQL
- Oracle執行語句跟蹤 使用sql trace實現語句追蹤OracleSQL
- mysql如何跟蹤執行的sql語句MySql
- 【MySQL】如何對SQL語句進行跟蹤(trace)?MySql
- 依據錯誤號來跟蹤sql語句SQL
- SQL效能的度量 - 語句級別的SQL跟蹤autotraceSQL
- [20131031]從跟蹤檔案中抽取sql語句.txtSQL
- [20190329]探究sql語句相關mutexes補充2.txtSQLMutex
- 檢視低效的SQL語句SQL
- 使用oracle的10046事件跟蹤SQL語句Oracle事件SQL
- 檢視v$sql_shared_cursor檢視獲取sql語句為什麼不能共享?SQL
- [20200326]dbms_monitor跟蹤與SQL語句分析.txtSQL
- oracle 使用者跟蹤 需要的sql語句總結OracleSQL
- [20160720]檢視了解oracle跟蹤事件.txtOracle事件
- [20220120]超長sql語句補充4.txtSQL
- 20140321]檢視大量消耗資源的sql語句.txtSQL
- [ZT]v$sqlarea,v$sql,v$sqltext這三個檢視提供的sql語句有什麼區別SQL
- [20220119]超長sql語句補充3.txtSQL
- 跟蹤oracle特定報錯 errorstackOracleError
- [20161230]檢視父遊標中sql語句.txtSQL
- 使用TKPROF檢視跟蹤檔案
- sql 語句網路除錯和 sql 語句低層傳輸檢視SQL除錯
- 【檢視】使用V$SQL_PLAN檢視獲取曾經執行過的SQL語句執行計劃SQL
- 檢視mysql正在執行的SQL語句MySql
- SQL語句大全—檢視錶空間(二)SQL
- SQL語句大全—檢視錶空間(一)SQL
- 透過ORADEBUG運用10046事件跟蹤SQL語句事件SQL
- [20120809]學習物化檢視(補充).txt
- [20210112]ashtop查詢特定表的SQL語句.txtSQL
- Oracle 檢視佔用undo大的sql語句OracleSQL