[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. --//估计生产系统修复这个问题。