選擇帶有視圖的查詢因 ORA-03113 崩潰:通信通道上的文件結束
我在 11g 企業版伺服器(Oracle 數據庫 11g 企業版版本 11.2.0.1.0 - 64 位生產)中安裝了一個在 11g XE 上開發的應用程序,主 SELECT 查詢觸發了這個:
ORA-03113: end-of-file on communication channel Identificador de Proceso: 14040 Identificador de Sesión: 55 Número de Serie: 22227
我已經簡化了實際的查詢,我仍然可以用這樣簡單的方法重現崩潰:
SELECT table_1.table_1_id FROM table_1 LEFT JOIN view_1 ON table_1.table_1_id=view_1.table_1_id LEFT JOIN view_2 ON view_1.table_2_id=view_2.table_2_id LEFT JOIN table_2 ON table_1.table_1_id=table_2.table_1_id AND view_1.table_2_id=table_2.table_2_id LEFT JOIN view_3 ON table_2.table_2_id=view_3.table_2_id;
…一旦我從查詢中刪除任何視圖,它就會起作用。到目前為止,涉及的表是空的。
DBA 在警報日誌中發現了這一點:
…ORA-07445:遇到異常:記憶體轉儲
$$ kkqtutlSetViewCols()+250 $$ $$ ACCESS_VIOLATION $$ $$ ADDR:0x8 $$ $$ PC:0x3DC175C $$ $$ UNABLE_TO_READ $$ $$ $$
事件詳細資訊位於:d:\oracle\diag\rdbms\audi\audi\incident\incdir_39166\audi_ora_5512_i39166.trc並且“audi_ora_5512_i39166.trc”包含以下內容(為簡潔起見,實際查詢已編輯):
========= Dump for incident 40848 (ORA 7445 [kkqtutlSetViewCols()+250]) ======== ----- Beginning of Customized Incident Dump(s) ----- Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x8] [PC:0x3DC175C, kkqtutlSetViewCols()+250] Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production Process Id: 0x00000620 Thread Id : 0x00001a08 Time : Mon Jul 11 11:46:08 Excp. Code: 0xc0000005 Excp. Type: ACCESS_VIO Flags: 0x00000000 ------------------- Registers ---------------------------- ip=0000000003DC175C sp=000000001A3A4DB0 rp=0000000044038058 r1=0000000000000000 r2=0000000000000004 r3=0000000038450640 r4=0000000000000000 r5=000000001A3A4DB0 r6=0000000044038058 r7=0000000044033140 r8=0000000000000000 r9=0000000000000004 r10=0000000000000004 r11=0000000000000003 r12=0000000000000001 r13=0000000000020B78 r14=0000000044037B18 r15=0000000038450080 ------------------- End of Registers --------------------- *** 2016-07-11 11:46:08.499 dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0) ----- Current SQL Statement for this session (sql_id=3dt50z5hh7gg9) ----- SELECT table_1.table_1_id FROM table_1 LEFT JOIN view_1 ON table_1.table_1_id=view_1.table_1_id LEFT JOIN view_2 ON view_1.table_2_id=view_2.table_2_id LEFT JOIN table_2 ON table_1.table_1_id=table_2.table_1_id AND view_1.table_2_id=table_2.table_2_id LEFT JOIN view_3 ON table_2.table_2_id=view_3.table_2_id; ----- Call Stack Trace ----- calling call entry argument values in hex location type point (? means dubious value) -------------------- -------- -------------------- ---------------------------- kkqtutlSetViewCols( 0000000000000000 49204E4F20534154 )+250 49434154524F504D 524F504D492E4E4F 495F4E4F49434154 kokqpoqb()+1319 CALL??? kkqtutlSetViewCols( 04400C070 044078D40 044021FE0 )+69 000020B78 kokqpqb()+196 CALL??? kokqpoqb() 0440229B0 0031412FD 000001F68 000000000 kokqpqb()+318 CALL??? kokqpqb() 7FF365B8188 000010400 000000000 004FF4D30 kokqpqb()+64 CALL??? kokqpqb() 7FF365B7F48 000000000 000000000 000000000 kokqpqb()+318 CALL??? kokqpqb() 000000000 038450640 000000000 000000000 kokqpqb()+318 CALL??? kokqpqb() 000000000 000000000 000000000 000000000 kokqpqb()+64 CALL??? kokqpqb() 038450080 038450080 01A3A52E0 0440229B0 kokqpqb()+64 CALL??? kokqpqb() 000020B78 038450080 01A3A52E0 044078CB0 kokqpqb()+318 CALL??? kokqpqb() 044061FD0 0059F3106 000000000 0078E8984 kokqpqb()+318 CALL??? kokqpqb() 000000000 0078E8950 044065D78 000000000 kokqpqbc2()+166 CALL??? kokqpqb() 044065D78 0059F3025 000000000 0000221E0 kokqpqbc_i()+99 CALL??? kokqpqbc2() 000020B78 004C956C4 000000000 004C9559B kokqpqbc()+17 CALL??? kokqpqbc_i() 0000221E0 000020B78 000020B78 038450080 kkqcttcalo()+1017 CALL??? kokqpqbc() 01A40EB60 009263551 000000002 000000001 kkqctdrvJPPD()+1176 CALL??? kkqcttcalo() 038450080 044002110 044002110 043FF23C8
你對可能發生的事情有任何暗示嗎?
正如我已經說過的,ORA-03113 是一個通用錯誤。
發生該錯誤時,為您的會話提供服務的伺服器程序由於後台的其他錯誤而意外死亡。這些錯誤顯示在數據庫警報日誌中。
在你的情況下:
ORA-07445: [kkqtutlSetViewCols()+250] [ACCESS_VIOLATION] [ADDR:0x8] [PC:0x3DC175C] [UNABLE_TO_READ] []
然後,您可以使用此資訊搜尋support.oracle.com 。
這看起來像
錯誤 9577499 ORA-7445$$ kkqtutlSetViewCols $$當查詢無法重寫時。
建議的解決方法是:
alter session set query_rewrite_enabled=false;
這會在會話級別禁用該功能,另一種選擇是僅對拋出此錯誤並提示的 SQL 語句禁用它:
select /*+ norewrite */ ...
要麼
select /*+ opt_param('query_rewrite_enabled', 'false') */ ...