Select query with views crashes with ORA-03113: end-of-file on communication channel

oracleoracle-11gview

I've installed an application developed on 11g XE in a 11g Enterprise Edition server (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production) and main SELECT query triggers this:

ORA-03113: end-of-file on communication channel 
Identificador de Proceso: 14040
Identificador de Sesión: 55 Número de Serie: 22227

I've simplified the actual query and I can still reproduce the crash with something as simple as this:

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;

… and it works as soon as I remove any view from the query. Involved tables are empty so far.

The DBA has found this in the alert log:

…ORA-07445: se ha encontrado una excepción: volcado de memoria
[kkqtutlSetViewCols()+250] [ACCESS_VIOLATION] [ADDR:0x8]
[PC:0x3DC175C] [UNABLE_TO_READ] []
Incident details in:
d:\oracle\diag\rdbms\audi\audi\incident\incdir_39166\audi_ora_5512_i39166.trc

And "audi_ora_5512_i39166.trc" contains this (actual query redacted for brevity):

========= 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

Do you have any hint about what could be going on?

Best Answer

As I already said, ORA-03113 is a generic error.

When that error occurs, the server process serving your session died unexpectedly because of some other error in the background. These errors are displayed in the database alert log.

In your case:

ORA-07445: [kkqtutlSetViewCols()+250] [ACCESS_VIOLATION] [ADDR:0x8] [PC:0x3DC175C] [UNABLE_TO_READ] []

You can then search support.oracle.com using this information.

This looks like

Bug 9577499 ORA-7445 [kkqtutlSetViewCols] when query fails to rewrite.

The suggested workaround is: alter session set query_rewrite_enabled=false;

This disables that feature at session level, another alternative would be disabling it only for your SQL statement that throws this error with a hint:

select /*+ norewrite */ ...

select /*+ opt_param('query_rewrite_enabled', 'false') */ ...

Related Solutions

How could sequence.nextval be null in Oracle

I'm pretty certain this will end up being an artifact of your code, or the .net driver you are using. I've knocked up a quick demo for you using pure SQL - PL/SQL and never get a lost sequence value. Incidentally the ref cursor you are using is probably unnecessary and likely impacts performance and readability of the code - my demo includes an insert_record2 procedure that consistently performs over 10% faster -in about 26s on my laptop vs 36 for the ref cursor version. I at least also think is easier to understand. You could obviously run a modified version against your test database complete with audit trigger.

/* 
demo for dbse 
assumes a user with create table, create sequence, create procedure pivs and quota. 

*/

drop table dbse13142 purge;

create table dbse13142(
    the_id number not null
,   name   varchar2(20)
,   userid number)
;

drop sequence x_seq;
CREATE SEQUENCE  X_SEQ NOCACHE  NOORDER  NOCYCLE ;

create or replace PROCEDURE Insert_Record
                (p_name    IN  VARCHAR2,                
                 p_userid  IN  INTEGER,
                 cur_out   OUT sys_refcursor)
    IS
        v_id NUMBER := 0;
    BEGIN
        -- Get id value from sequence
        SELECT x_seq.nextval
          INTO v_id
          FROM dual;

        -- Line below is X_PKG line 40
        INSERT INTO dbse13142
            (the_id,            
             name,                        
             userid)
          VALUES
            (v_id,
             p_name,                        
             p_userid);

        -- Return new id
        OPEN cur_out FOR
            SELECT v_id the_id
              FROM dual;
    END;
/


create or replace PROCEDURE Insert_Record2
                (p_name    IN  VARCHAR2,                
                 p_userid  IN  INTEGER,
                 p_theid   OUT dbse13142.the_id%type)
    IS
    BEGIN
        -- Get id value from sequence
        SELECT x_seq.nextval
          INTO p_theid
          FROM dual;

        -- Line below is X_PKG line 40
        INSERT INTO dbse13142
            (the_id,            
             name,                        
             userid)
          VALUES
            (p_theid,
             p_name,                        
             p_userid);
    END;
/

set timing on

declare
   c sys_refcursor;
begin   
for i in 1..100000 loop
   insert_record('User '||i,i,c);
   close c;
end loop;
commit;
end;
/

select count(*) from dbse13142;
truncate table dbse13142;

declare
  x number;
begin   
for i in 1..100000 loop
   insert_record2('User '||i,i,x);
end loop;
commit;
end;
/

select count(*) from dbse13142;
truncate table dbse13142;

Oracle startup error: “end-of-file on communication channel”

O/S-Error: (OS 665) The requested operation could
  not be completed due to a file system limitation

So the windows error 665 is the reason for the error an the error refers to file C:\ORADATAFILES\ORAPROD03\USERS03.DBF.What is the problem with the file? The following article discusses this error message: What factors can lead to Win32 error 665 (file system limitation)?. If the reason is file fragmentation a patch is available. I know it is to late but maybe a restore will not be necessary in this situation.

Best Answer

Related Solutions

How could sequence.nextval be null in Oracle

Oracle startup error: “end-of-file on communication channel”

Related Question