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

--//估計生產系統修復這個問題。