1

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?

  • 2
    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. You can find the alert log location with the following query: select value || '/alert_' || sys_context('userenv', 'instance_name') || '.log' from v$diag_info where name = 'Diag Trace';. Check the log, and post the errors from there. Most likely there will be some ORA-07445 errors. – Balazs Papp Jun 24 '16 at 12:42
  • @ Álvaro González Are these views point to remote tables? – JSapkota Jun 25 '16 at 1:19
  • @jsapkota No, they don't. Everything's local. – Álvaro González Jun 26 '16 at 8:25
  • Does it crash immediately? Or does it run for a bit and then fail? My first thought is a profile limit, limiting your query to a certain number of logical reads or CPU usage. Try select * from dba_profiles where profile = (select profile from dba_users where username='<user executing query>') – Nick S Jun 29 '16 at 15:35
  • @NickS It's an instant crash (but I still haven't loaded actual data, tables remain empty so far). I'll report back with my findings. – Álvaro González Jun 30 '16 at 7:21
1

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 */ ...

or

select /*+ opt_param('query_rewrite_enabled', 'false') */ ...
  • 1
    Just as an additional note, according to the same article, this is fixed in 11.2.0.2 – Andrew Brennan Jul 11 '16 at 15:37
  • If I understood correctly how query rewrite works, it's no improvement for me because I don't have any materialised view so disabling it is the way to go. – Álvaro González Jul 12 '16 at 6:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.