Oracle in子句過多的硬編碼引發的故障

某生產資料庫，不能登入，資料庫不能歸，生產環境為IBM小機，oracle為單例項的10.2.0.4。登入到資料庫伺服器可以看到磁碟空間被佔用完了,歸檔日誌儲存在yb_oradata目錄下，Oracle軟體安裝在oracle目錄，使用者不能登入是因為不能寫審計日誌，業務不能辦理是因為歸檔目錄沒有空間，這些是問題的症狀。，一般oracle所佔的大小不可能達到上百G的大小。那就需要檢查是什麼檔案佔用了空間,生產環境為IBM小機。

[IBMP740-1:root:/]#df -g
Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on
/dev/hd4           8.00      4.03   50%    11099     2% /
/dev/hd2           6.00      3.06   49%    57334     8% /usr
/dev/hd9var       16.00      6.87   58%    11305     1% /var
/dev/hd3          10.00      8.38   17%     2008     1% /tmp
/dev/fwdump        1.00      1.00    1%        5     1% /var/adm/ras/platform
/dev/hd1           2.00      2.00    1%        5     1% /home
/dev/hd11admin     10.00     10.00    1%        5     1% /admin
/proc                 -         -    -         -     -  /proc
/dev/hd10opt       2.00      1.65   18%    11518     3% /opt
/dev/livedump      2.00      2.00    1%        4     1% /var/adm/ras/livedump
/dev/oracle_lv    100.00      0.00  100%    66389    84% /oracle
/dev/bak_lv      999.00    520.64   48%       29     1% /bak
/dev/yboradata_lv   1399.00    0    100%      337     1% /yb_oradata

可以看到admin目錄佔用了79.16G

[IBMP740-1:root:/oracle]#du -sg *
0.00    Mail
79.16   admin
0.00    chapter10_01.sql
0.04    flash_recovery_area
3.06    inst
0.00    jd.log
0.00    lost+found
0.00    oraInventory
8.77    product
0.00    smit.log
0.00    smit.script
0.00    smit.transaction
0.00    sosi.txt
0.00    spcusr.lis
0.00    sqlhc
0.00    sqlnet.log
0.04    sqlt
0.00    sqlt.zip
0.01    sqlt_s10819
0.00    sqlt_s34882_log.zip
0.00    sqlt_s34883_log.zip
0.00    sqlt_s34884_xecute.zip
0.00    sqlt_s34885_xecute.zip
0.00    sqlt_s34886_xecute.zip
0.00    sqlt_s34887_xecute.zip
0.00    test_high_version.txt

可以看到RLZY目錄佔用了79.16

[IBMP740-1:root:/oracle/admin]#du -sg *
0.00    CAIWU
0.00    ORCL
79.16   RLZY
0.00    chdyl

可以看到cdump與udump目錄分別佔用了40.94G,38.22G

[IBMP740-1:root:/oracle/admin/RLZY]#du -sg *
0.05    adump
1.66    bdump
40.94   cdump
0.00    dpdump
0.00    pfile
0.00    scripts
38.22   udump

adump ：審計資訊
bdump ：後臺程式trace 和alert log ,就是說 alert_sid.log也存在這個目錄中
cdump ：一般放置一些核心的trace檔案，核心例項緩衝區產生的跟蹤檔案,除非資料庫出了問題 否則基本上不會有什麼資訊
dpdump：是存放一些登入資訊的
pfile ：初始化引數檔案 initSID
udump ：一般放置sql trace之後session的trace檔案，使用者伺服器程式產生的跟蹤檔案，常見的是sql問題

那麼我們先檢視一下alert_RZLY.log

[IBMP740-1:root:/oracle/admin/RLZY]#tail -f /oracle/admin/RLZY/bdump/alert_RLZY.log
ORA-07445: exception encountered: core dump [qcsAnalyzeBooleanExpr+0010] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFDFFFFF0] [] []
Wed Oct 26 19:22:35 2016
Thread 1 advanced to log sequence 47133 (LGWR switch)
  Current log# 2 seq# 47133 mem# 0: /yb_oradata/RLZY/RLZY/redo02.log
Wed Oct 26 19:32:51 2016
Errors in file /oracle/admin/RLZY/udump/rlzy_ora_43647120.trc:
ORA-07445: exception encountered: core dump [qcsAnalyzeBooleanExpr+0010] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFDFFFFF0] [] []
Wed Oct 26 19:42:40 2016
Errors in file /oracle/admin/RLZY/udump/rlzy_ora_13697930.trc:
ORA-07445: exception encountered: core dump [qcsAnalyzeBooleanExpr+0010] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFDFFFFF0] [] []

可以看到出現了ORA-07445: exception encountered: core dump [qcsAnalyzeBooleanExpr+0010] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFDFFFFF0] [] []錯誤，具體的錯誤資訊記錄到/oracle/admin/RLZY/udump/rlzy_ora_13697930.trc檔案中。如果檢視該檔案可以看到:

/oracle/admin/RLZY/udump/rlzy_ora_13697930.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/10.2.0/db_1
System name:	AIX
Node name:	IBMP740-1
Release:	1
Version:	6
Machine:	00F7AC3D4C00
Instance name: RLZY
Redo thread mounted by this instance: 1
Oracle process number: 73
Unix process pid: 13697930, image: oracle@IBMP740-1

*** 2016-10-26 19:42:40.925
*** ACTION NAME:() 2016-10-26 19:42:40.915
*** MODULE NAME:(JDBC Thin Client) 2016-10-26 19:42:40.915
*** SERVICE NAME:(SYS$USERS) 2016-10-26 19:42:40.915
*** SESSION ID:(1249.559) 2016-10-26 19:42:40.915
Exception signal: 11 (SIGSEGV), code: 50 (Address not mapped to object), addr: 0xffffffffdfffff0, PC: [0x100be5d90, qcsAnalyzeBooleanExpr+0010]
Registers:
iar: 0000000100be5d90, msr: a00000000000d032
 lr: 0000000100be5dd8,  cr: 0000000048222020
r00: 0000000100be5dd8, r01: 0ffffffffe000080, r02: 000000011022a6e8,
r03: 0ffffffffffee1a0, r04: 0000000110195798, r05: 00000001173f9a58,
r06: 0000000000000001, r07: 0ffffffffffee1a0, r08: 0000000110457778,
r09: 000000000000003f, r10: 0000000000000000, r11: 0000000000000000,
r12: 00000001008c5ce0, r13: 0000000110275b80, r14: 070000059a4fae08,
r15: 07000004dd304d00, r16: 0000000104d83ea8, r17: 07000004ada5ddd8,
r18: 000000000000000a, r19: 07000004dd304c08, r20: 0000000110000a70,
r21: 00000000000000d3, r22: 07000004dd3050e8, r23: 000003a1ecbd50bb,
r24: 0000000000000001, r25: 07000004ab1466f8, r26: 0ffffffffffef590,
r27: 0000000104fbef18, r28: 00000001104554a0, r29: 07000004ab1464f0,
r30: 0000000119360ff8, r31: 00000001173f9a58,
*** 2016-10-26 19:42:40.925
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [qcsAnalyzeBooleanExpr+0010] [SIGSEGV] [Address not mapped to object] [0xFFFFFFFFDFFFFF0] [] []
Current SQL statement for this session:
update lv_handle_mdiindiacc_detail ld set ld.push_flag= '1'  where    (  ld.indipayser  in  (485506300,485504469,481520527,481520481,
481203712,484953649,484953901,481203462,481382692,481382673,485061110,485060889,481631327,481628371,481714509,481714502,485422790,
485422743,485505625,485504046,485505138,485422775,483924474,485422767,481537563,481537508,485422795,485422751,485710571,482042800,
482042766,485710537,485233344,485233182,485233274,481470443,481469597,485422785,485422737,481352719,481352131,485422780,485422731,
486517983,483676463,483677515,486516952,486517971,486516920,483676426,483677504,486517984,483676467,483677516,486516956,485834372,
485834335,486517621,483677148,486515894,483675382,482155166,482155007,485341981,485341686,485148741,485148592,485233418,485233219,
485691049,485690926,482024499,482024622,481430441,481429048,485422809,485422738,485003015,485002902,481365287,485108845,485110024,
481364104,481576480,481575936,485345250,481593493,481593394,485345150,481293132,481292888,481870872,481870754,481631719,481628925,
481631720,481628926,484386401,484325919,484279761,481364990,481364170,485108911,485109731,481365343,481364180,485110080,485108921,
485461600,485461588,486518688,483678244,486516141,483675633,481203710,484953644,481203457,484953899,481859623,481858668,485301624,
485301274,485178242,485178159,485341976,485341674,485114334,485114120,481667238,481667062,481896047,485566958,485566922,481896012,
485492025,485491374,481383898,481383891,485525133,485524916,485823685,485823675,482155703,482155693,481919222,481918827,485506045,
485504050,481919226,481918831,481314218,481314176,481372925,481372665,485118141,485117847,482155169,482155010,479090023,479089896,
486519248,483676465,483678822,486516954,481951298,481951203,481342632,481341537,485422816,485422759,485414857,485414807,485114339,
485114125,485114340,485114126,485559817,485559793,481590100,481589780,485038170,485038149,483345446,483345323,481898367,481898066,
485133366,485131992,481514080,481514019,485546660,481868409,481868588,485546483,481631724,481628931,481631725,481628932,485801708,
485801650,486519198,483678762,483676384,486516881,481795700,481795657,481511439,481511400,481954867,481954816,481750064,481750061,
481264623,481264439,483444426,483443970,485121840,485122295,483444424,485121838,485122293,483443968,485801199,485801114,482124023,
482123938,481758852,485465193,485465374,481758679,484985855,481234905,484417829,484349508,484301809,481630369,484369496,484270030,
484316209,484422263,484354647,484306241,485505104,484424634,484359856,484308602,484369483,484316196,484270017,484380924,484277629,
484323787,484394588,484331396,484285322,484443543,484409895,484339940,484293866,484443489,484384989,484324507,484278349,484387781,
484327215,484281141,484422937,484306905,484356645,484377357,484322251,484276093,485365130,481610521,484419539,484303519,484351460,
482097063,485776434,484408090,484338888,484292814,484377349,484322243,484276085,484411456,484295427,484341501,484444428,485491523,
484363227,484309940,484263761,484408140,484292864,484338938,484363949,484264483,484310662,484387017,484326451,484280377,484391296,
484283118,484329192,484363284,484263818,484309997,484363963,484264497,484310676,484391310,484283132,484329206,484419515,484303495,
484351436,485491585,485107518,484372437,484319150,484272992,484364435,484311148,484264969,484363383,484263917,484310096,484372838,
484319551,484273393,484419460,484303440,484351381,484364855,484265389,484311568,484362965,484309678,484263499,484387603,484327037,
484280963,484363999,484264533,484310712,484393931,484284665,484330739,484419551,484303531,484351472,484415488,484299468,484346502,
484387757,484327191,484281117,484375344,484321766,484275608,484394990,484285418,484331492,484413841,484343964,484297806,485505354,
484385059,484278419,484324577,484399421,484287836,484333910,484371169,484271724,484443706,484317882,484419549,484351470,484303529,
485420565,481670219,484414622,484298587,484344785,484364069,484310782,484264603,484415790,484299770,484346804,484392213,484283511,
484329585,484413882,484297847,484344005,484372903,484273458,484319616,484391273,484329169,484283095,484408100,484292824,484338898,
484414128,484298093,484344251,485505040,481808388,485487553,485001985,484364106,484310819,484264640,484411657,484341702,484295628,
484444228,484367730,484314443,484268264,484403066,484289851,484335925,484419597,484351518,484303577,484372506,484319219,484273061,
484422684,484356396,484306656,484422685,484356397,484306657,484419642,484351563,484303622,484419468,484303448,484351389,484364631,
484311344,484265165,481499866,484368214,484314927,484268748,484395063,484331565,484285491,484371188,484443905,484317901,484271743,
484413836,484343959,484297801,484364176,484264710,484310889,484362932,484263466,484309645,484363103,484309816,484263637,484423596,
484307564,484358673,485806313,485806320,484416902,484348176,484300882,484364579,484311292,484265113,484410263,484294234,484340308,
484363437,484310150,484263971,484363289,484263823,484310002,484409819,484339864,484293790,484408046,484338844,484292770,484422080,
484354466,484306060)
...省略500多個 ld.indipayser in子句...
 or   ld.indipayser   in  (483435121,485491921,485491229,481690203,481689916,481520856,481520755,481898439,481898142,481430459,
 481429068,485829180,485828858,483585375,483583501,481898271,481897956,481898395,481898095,481429876,481428052,481430365,481428936,
 485622870,485622845,481953922,481953897,481342272,481341014,481342685,481341591,484412861,484296823,484342897,482228507,482226295,
 482059057,485829120,485828783,484410980,484294951,484341025,485341942,485341626,482104613,482103959,476024026,476023392,479354610,
 479355244,485506208,485504331,485506449,485504649,481630446,482161656,482161030,485414852,485414802,481407018,485139969,485140061,
 481406926,481292994,481292723,481595191,475647095,478940615,481595203,475647108,478940628,481430099,481428372,485506248,485504393,
 483585619,483583745,482202578,482201079,482013649,482013215,485133328,485131954,485524985,485524754,483585630,483583756,485008802,
 485008781,481267747,481267768,485546704,481868233,481868633,485546310,481406992,481406892,485140035,485139935,481599032,481598665,
 481406973,485139916,485140016,481406873,481459790,481459525,481815563,481815423,481459671,481459394,481742591,481742553,481363369,
 481363213,483584338,483582447,481271582,481271283,485012358,485012661,481430472,481429081,485432563,481703015,481702793,485432341,
 484938031,484937972,481576545,481576019,485104415,485104364,485506476,485504681,481659769,481659645,485506511,485504722,485837341,
 485837308,481281837,481281135,485519385,485520085,484914974,484913321,484915019,484913409,475697671,475697595,472375125,467064510,
 460778878,459403120,455152767,451292754,437811847,437811923,440579233,440579309,443323954,443324029,446196115,446196190,451292679,
 408059771,412145899,412146020,416223862,416223945,419135025,419135109,421090466,421090550,424835994,424836077,428415307,428415384,
 432137615,432137692,434943813,434943889,382221955,382222077,385744164,385744286,388759711,388759833,392484675,392484797,396668805,
 396668889,399208174,399208296,401551264,401551386,405030115,405030236,408059650,455152690,459403043,460778801,467064434,469745874,
 469745950,472375049,481823330,481823309,485061440,485060870,481293007,481292736,481791012,481790993,485055047,485054905,481264879,
 481264601,485182951,485181901,482161443,482160773,484915392,484913132,481459595,481459310,485061296,485060685,481632400,481629790,
 484367836,484268370,484314549,481965421,481964565,481333736,481333553,482062253,482055203,481342270,481341012,483444108,485121523,
 483443652,485121978,481282606,485519758,481281510,485520852,482228644,482225455,481742602,481742564,485753875,485750411,483438857,
 483435383,481386674,481386604,481459763,481459498,481238883,481238806,483585061,483583186,485465383,485465204,481459807,481459542,
 485820222,482151739,485820213,482151748,481506957,481506888,485829083,485828735,481553536,481553461,485290131,485290208,480813211,
 480811525,480809839,480802533,480803095,480804219,480804781,480805905,480806467,480807591,480808153,480809277,481195925,481196477,
 480800847,480801409,480810963,480812649,475662583,475662548,472347913,472347948,469363482,469363447,481807284,481807269,479116742,
 479116726,481157590,481157509,478569302,478569415,485505891,485503650,485797256,485797164,481925900,484370496,484271051,484317209,
 475662579,475662559,472347924,469363458,469363478,472347944,485366698,485366602,481459761,481459496,485829059,485828711,482104644,
 482104015,479355288,479354666,476023448,476024070,485061553,485061004,485061195,485060567,486518406,483677963,483675157,486515666,
 481342717,481012977,481012976,481341623,481215908,478529522,485628896,481960727,485432467,485432171,481702919,481702623,485752755,
 483437727,483434118,485749152,483345478,483345358,483345510,483345391,483345528,483345410,484400959,484334893,484288819,485506343,
 485504525,481742595,481742557,484938040,484937944,485071415,485071319,481859721,481858934,485567428,485567290,483585457,483583583,
 485133565,485132233,482161564,482160926,482104558,476024093,476023480,479354698,482103861,479355311,485829141,485828810,485190223,
 481453746,481453533,485190010,485368352,485368337,481613708,481613723,481632457,481629847,481407010,485140053,481406918,485139961,
 485829305,485828985,481856037,482097135,485776506,482096955,485776326,485831653,485831650,485492087,485491438,484945744,481408635,
 481407982,481408804,481408266,482085026,482084946,481383961,481383927,481207531,484958546,484958564,481207513,481342353,481341097,
 481815515,481815353,481519532,481519463,484945762,484945777,485824095,485824000,485116115,485116067,481370920,481370968,485829030,
 485828770,481632418,481629808,485829281,485828960,485829088,485828740,482202678,482201289,485432590,481702496,485432044,481703042,
 481753320,481753297,482104329,476023838,476023326,479354544,482104005,479355056,481467287,481467197,485506301,485504470,481430387,
 481428981,484915992,484914171,485492004,485491346,484398623,484333459,484287385,484413427,484297389,484343463,484390123,484282715,
 484328789,481365566,485110303,485109362,481364625,481576451,481575901,482017867,482017518)  )

最終這個update子句使用500多個ld.indipayser in子句，這些in子句之間使用or進行連線，等於in子句最終的引數個數有40多萬個。這就是udump目錄中佔用38.22G空間中消耗了34G空間的跟蹤檔案內容。

下面來檢視cdump目錄中的

[IBMP740-1:root:/oracle/admin/RLZY/cdump]#ls -lrt
...省略了大多數內容...
drwxr-x---    2 oracle   dba             256 Oct 26 14:29 core_28377398
drwxr-x---    2 oracle   dba             256 Oct 26 14:52 core_6685364
drwxr-x---    2 oracle   dba             256 Oct 26 15:12 core_63635536
drwxr-x---    2 oracle   dba             256 Oct 26 15:35 core_17301998
drwxr-x---    2 oracle   dba             256 Oct 26 16:04 core_47317412
drwxr-x---    2 oracle   dba             256 Oct 26 16:29 core_7144314
drwxr-x---    2 oracle   dba             256 Oct 26 16:54 core_33030282
drwxr-x---    2 oracle   dba             256 Oct 26 17:29 core_27918436
drwxr-x---    2 oracle   dba             256 Oct 26 17:39 core_12321324
drwxr-x---    2 oracle   dba             256 Oct 26 17:49 core_45089220
drwxr-x---    2 oracle   dba             256 Oct 26 18:15 core_12517416
drwxr-x---    2 oracle   dba             256 Oct 26 18:35 core_3539788
drwxr-x---    2 oracle   dba             256 Oct 26 19:00 core_14549222
drwxr-x---    2 oracle   dba             256 Oct 26 19:26 core_60817918
drwxr-x---    2 oracle   dba             256 Oct 26 19:42 core_43647120
drwxr-x---    2 oracle   dba             256 Oct 26 19:52 core_13697930

檢視最近生成的core_13697930目錄中生成的core檔案可以看到與/oracle/admin/RLZY/udump/rlzy_ora_13697930.trc檔案中一樣的SQL語句:

update lv_handle_mdiindiacc_detail ld set ld.push_flag= '1'  where    (  ld.indipayser  in  (485506300,485504469,481520527,481520481,
481203712,484953649,484953901,481203462,481382692,481382673,485061110,485060889,481631327,481628371,481714509,481714502,485422790,
485422743,485505625,485504046,485505138,485422775,483924474,485422767,481537563,481537508,485422795,485422751,485710571,482042800,
482042766,485710537,485233344,485233182,485233274,481470443,481469597,485422785,485422737,481352719,481352131,485422780,485422731,
486517983,483676463,483677515,486516952,486517971,486516920,483676426,483677504,486517984,483676467,483677516,486516956,485834372,
485834335,486517621,483677148,486515894,483675382,482155166,482155007,485341981,485341686,485148741,485148592,485233418,485233219,
485691049,485690926,482024499,482024622,481430441,481429048,485422809,485422738,485003015,485002902,481365287,485108845,485110024,
481364104,481576480,481575936,485345250,481593493,481593394,485345150,481293132,481292888,481870872,481870754,481631719,481628925,
481631720,481628926,484386401,484325919,484279761,481364990,481364170,485108911,485109731,481365343,481364180,485110080,485108921,
485461600,485461588,486518688,483678244,486516141,483675633,481203710,484953644,481203457,484953899,481859623,481858668,485301624,
485301274,485178242,485178159,485341976,485341674,485114334,485114120,481667238,481667062,481896047,485566958,485566922,481896012,
485492025,485491374,481383898,481383891,485525133,485524916,485823685,485823675,482155703,482155693,481919222,481918827,485506045,
485504050,481919226,481918831,481314218,481314176,481372925,481372665,485118141,485117847,482155169,482155010,479090023,479089896,
486519248,483676465,483678822,486516954,481951298,481951203,481342632,481341537,485422816,485422759,485414857,485414807,485114339,
485114125,485114340,485114126,485559817,485559793,481590100,481589780,485038170,485038149,483345446,483345323,481898367,481898066,
485133366,485131992,481514080,481514019,485546660,481868409,481868588,485546483,481631724,481628931,481631725,481628932,485801708,
485801650,486519198,483678762,483676384,486516881,481795700,481795657,481511439,481511400,481954867,481954816,481750064,481750061,
481264623,481264439,483444426,483443970,485121840,485122295,483444424,485121838,485122293,483443968,485801199,485801114,482124023,
482123938,481758852,485465193,485465374,481758679,484985855,481234905,484417829,484349508,484301809,481630369,484369496,484270030,
484316209,484422263,484354647,484306241,485505104,484424634,484359856,484308602,484369483,484316196,484270017,484380924,484277629,
484323787,484394588,484331396,484285322,484443543,484409895,484339940,484293866,484443489,484384989,484324507,484278349,484387781,
484327215,484281141,484422937,484306905,484356645,484377357,484322251,484276093,485365130,481610521,484419539,484303519,484351460,
482097063,485776434,484408090,484338888,484292814,484377349,484322243,484276085,484411456,484295427,484341501,484444428,485491523,
484363227,484309940,484263761,484408140,484292864,484338938,484363949,484264483,484310662,484387017,484326451,484280377,484391296,
484283118,484329192,484363284,484263818,484309997,484363963,484264497,484310676,484391310,484283132,484329206,484419515,484303495,
484351436,485491585,485107518,484372437,484319150,484272992,484364435,484311148,484264969,484363383,484263917,484310096,484372838,
484319551,484273393,484419460,484303440,484351381,484364855,484265389,484311568,484362965,484309678,484263499,484387603,484327037,
484280963,484363999,484264533,484310712,484393931,484284665,484330739,484419551,484303531,484351472,484415488,484299468,484346502,
484387757,484327191,484281117,484375344,484321766,484275608,484394990,484285418,484331492,484413841,484343964,484297806,485505354,
484385059,484278419,484324577,484399421,484287836,484333910,484371169,484271724,484443706,484317882,484419549,484351470,484303529,
485420565,481670219,484414622,484298587,484344785,484364069,484310782,484264603,484415790,484299770,484346804,484392213,484283511,
484329585,484413882,484297847,484344005,484372903,484273458,484319616,484391273,484329169,484283095,484408100,484292824,484338898,
484414128,484298093,484344251,485505040,481808388,485487553,485001985,484364106,484310819,484264640,484411657,484341702,484295628,
484444228,484367730,484314443,484268264,484403066,484289851,484335925,484419597,484351518,484303577,484372506,484319219,484273061,
484422684,484356396,484306656,484422685,484356397,484306657,484419642,484351563,484303622,484419468,484303448,484351389,484364631,
484311344,484265165,481499866,484368214,484314927,484268748,484395063,484331565,484285491,484371188,484443905,484317901,484271743,
484413836,484343959,484297801,484364176,484264710,484310889,484362932,484263466,484309645,484363103,484309816,484263637,484423596,
484307564,484358673,485806313,485806320,484416902,484348176,484300882,484364579,484311292,484265113,484410263,484294234,484340308,
484363437,484310150,484263971,484363289,484263823,484310002,484409819,484339864,484293790,484408046,484338844,484292770,484422080,
484354466,484306060)
...省略500多個 ld.indipayser in子句...
 or   ld.indipayser   in  (483435121,485491921,485491229,481690203,481689916,481520856,481520755,481898439,481898142,481430459,
 481429068,485829180,485828858,483585375,483583501,481898271,481897956,481898395,481898095,481429876,481428052,481430365,481428936,
 485622870,485622845,481953922,481953897,481342272,481341014,481342685,481341591,484412861,484296823,484342897,482228507,482226295,
 482059057,485829120,485828783,484410980,484294951,484341025,485341942,485341626,482104613,482103959,476024026,476023392,479354610,
 479355244,485506208,485504331,485506449,485504649,481630446,482161656,482161030,485414852,485414802,481407018,485139969,485140061,
 481406926,481292994,481292723,481595191,475647095,478940615,481595203,475647108,478940628,481430099,481428372,485506248,485504393,
 483585619,483583745,482202578,482201079,482013649,482013215,485133328,485131954,485524985,485524754,483585630,483583756,485008802,
 485008781,481267747,481267768,485546704,481868233,481868633,485546310,481406992,481406892,485140035,485139935,481599032,481598665,
 481406973,485139916,485140016,481406873,481459790,481459525,481815563,481815423,481459671,481459394,481742591,481742553,481363369,
 481363213,483584338,483582447,481271582,481271283,485012358,485012661,481430472,481429081,485432563,481703015,481702793,485432341,
 484938031,484937972,481576545,481576019,485104415,485104364,485506476,485504681,481659769,481659645,485506511,485504722,485837341,
 485837308,481281837,481281135,485519385,485520085,484914974,484913321,484915019,484913409,475697671,475697595,472375125,467064510,
 460778878,459403120,455152767,451292754,437811847,437811923,440579233,440579309,443323954,443324029,446196115,446196190,451292679,
 408059771,412145899,412146020,416223862,416223945,419135025,419135109,421090466,421090550,424835994,424836077,428415307,428415384,
 432137615,432137692,434943813,434943889,382221955,382222077,385744164,385744286,388759711,388759833,392484675,392484797,396668805,
 396668889,399208174,399208296,401551264,401551386,405030115,405030236,408059650,455152690,459403043,460778801,467064434,469745874,
 469745950,472375049,481823330,481823309,485061440,485060870,481293007,481292736,481791012,481790993,485055047,485054905,481264879,
 481264601,485182951,485181901,482161443,482160773,484915392,484913132,481459595,481459310,485061296,485060685,481632400,481629790,
 484367836,484268370,484314549,481965421,481964565,481333736,481333553,482062253,482055203,481342270,481341012,483444108,485121523,
 483443652,485121978,481282606,485519758,481281510,485520852,482228644,482225455,481742602,481742564,485753875,485750411,483438857,
 483435383,481386674,481386604,481459763,481459498,481238883,481238806,483585061,483583186,485465383,485465204,481459807,481459542,
 485820222,482151739,485820213,482151748,481506957,481506888,485829083,485828735,481553536,481553461,485290131,485290208,480813211,
 480811525,480809839,480802533,480803095,480804219,480804781,480805905,480806467,480807591,480808153,480809277,481195925,481196477,
 480800847,480801409,480810963,480812649,475662583,475662548,472347913,472347948,469363482,469363447,481807284,481807269,479116742,
 479116726,481157590,481157509,478569302,478569415,485505891,485503650,485797256,485797164,481925900,484370496,484271051,484317209,
 475662579,475662559,472347924,469363458,469363478,472347944,485366698,485366602,481459761,481459496,485829059,485828711,482104644,
 482104015,479355288,479354666,476023448,476024070,485061553,485061004,485061195,485060567,486518406,483677963,483675157,486515666,
 481342717,481012977,481012976,481341623,481215908,478529522,485628896,481960727,485432467,485432171,481702919,481702623,485752755,
 483437727,483434118,485749152,483345478,483345358,483345510,483345391,483345528,483345410,484400959,484334893,484288819,485506343,
 485504525,481742595,481742557,484938040,484937944,485071415,485071319,481859721,481858934,485567428,485567290,483585457,483583583,
 485133565,485132233,482161564,482160926,482104558,476024093,476023480,479354698,482103861,479355311,485829141,485828810,485190223,
 481453746,481453533,485190010,485368352,485368337,481613708,481613723,481632457,481629847,481407010,485140053,481406918,485139961,
 485829305,485828985,481856037,482097135,485776506,482096955,485776326,485831653,485831650,485492087,485491438,484945744,481408635,
 481407982,481408804,481408266,482085026,482084946,481383961,481383927,481207531,484958546,484958564,481207513,481342353,481341097,
 481815515,481815353,481519532,481519463,484945762,484945777,485824095,485824000,485116115,485116067,481370920,481370968,485829030,
 485828770,481632418,481629808,485829281,485828960,485829088,485828740,482202678,482201289,485432590,481702496,485432044,481703042,
 481753320,481753297,482104329,476023838,476023326,479354544,482104005,479355056,481467287,481467197,485506301,485504470,481430387,
 481428981,484915992,484914171,485492004,485491346,484398623,484333459,484287385,484413427,484297389,484343463,484390123,484282715,
 484328789,481365566,485110303,485109362,481364625,481576451,481575901,482017867,482017518)  )

所以應該是該語句中的in子句的硬編碼值過多引起的，in子句硬編碼值有40多萬個。這樣的語句解析將消耗的很多的shared_pool中的空間，從而導致系統異常。

處理方法:
應用改寫語句，將in子句中的這些值存放到某個臨時表中，將使用臨時表與要被更新的表進行關聯。