ORA 7445: Query causing Oracle 12c to crash
I'm currently testing my application on Oracle 12 after all tests passed OK on Oracle 11. During testing the application kept losing the connection to Oracle when running a certain query. Here is a simplified example which replicates the problem exactly:
--create an example table
CREATE TABLE ERROR_TABLE(
PKID INT,
STRING_COLUMN VARCHAR2(255 CHAR)
);
--and run the offending query
SELECT
T.*,
ROW_NUMBER() OVER(
ORDER BY TO_NUMBER(REGEXP_SUBSTR(STRING_COLUMN,'^[0-9]*[.]*[0-9]*')) ASC,
STRING_COLUMN ASC,
PKID ASC
)
FROM
(
SELECT
PKID,
(SELECT MIN(STRING_COLUMN) FROM ERROR_TABLE T1 WHERE T1.PKID = T2.PKID) AS STRING_COLUMN
FROM ERROR_TABLE T2
)T;
When I run this query the connection to Oracle is dropped. There is an 'incident' recorded in the Oracle alert log, but it doesn't say why it happened. It seems to be the combination of the TO_NUMBER(... and the SELECT(MIN... part. If I replace either of those the problem goes away. However, I'm reluctant to do this as it's been tested and validated, and this technique appears in multiple places throughout the program.
Has anyone else encountered anything like this? What is causing it?
I'm running Oracle 12.1.0.1.0. The server is 64-bit and the client is 32-bit.
UPDATE
Here is the relevant excerpt from the alert log:
<msg time='2014-09-29T14:57:30.310+01:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='ADMIN-PC'
host_addr='fe80::5183:eb5:fdd6:8fce%10' module='SQL Developer' pid='2084'>
<txt>Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x0] [PC:0x7FEF691FB5A, qcsogolz()+208]
</txt>
</msg>
<msg time='2014-09-29T14:57:30.435+01:00' org_id='oracle' comp_id='rdbms'
msg_id='1305664044' type='INCIDENT_ERROR' group='Access Violation'
level='1' host_id='ADMIN-PC' host_addr='fe80::5183:eb5:fdd6:8fce%10'
prob_key='ORA 7445 [qcsogolz]' errid='40818' detail_path='C:APPORACLEUSERdiagrdbmsorclorcltraceorcl_ora_2084.trc'>
<txt>Errors in file C:APPORACLEUSERdiagrdbmsorclorcltraceorcl_ora_2084.trc (incident=40818):
ORA-07445: exception encountered: core dump [qcsogolz()+208] [ACCESS_VIOLATION] [ADDR:0x0] [PC:0x7FEF691FB5A] [UNABLE_TO_READ] []
</txt>
</msg>
<msg time='2014-09-29T14:57:30.451+01:00' org_id='oracle' comp_id='rdbms'
type='UNKNOWN' level='16' host_id='ADMIN-PC'
host_addr='fe80::5183:eb5:fdd6:8fce%10' module='SQL Developer' pid='2084'>
<txt>Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
</txt>
</msg>
<msg time='2014-09-29T14:57:32.760+01:00' org_id='oracle' comp_id='rdbms'
msg_id='dbgripsto_sweep_staged_obj:15783:70631439' type='ERROR' group='ami_comp'
level='8' host_id='ADMIN-PC' host_addr='fe80::5183:eb5:fdd6:8fce%10'>
<txt>Sweep [inc][40818]: completed
</txt>
</msg>
And here is a copy of the resulting trace file:
Trace file C:APPORACLEUSERdiagrdbmsorclorcltraceorcl_ora_2084.trc
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU : 2 - type 8664, 2 Physical Cores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:882M/2038M, Ph+PgF:1864M/4076M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 50
Windows thread id: 2084, image: ORACLE.EXE (SHAD)
*** 2014-09-29 14:57:30.310
*** SESSION ID:(18.63977) 2014-09-29 14:57:30.310
*** CLIENT ID:() 2014-09-29 14:57:30.310
*** SERVICE NAME:(pdborcl) 2014-09-29 14:57:30.310
*** MODULE NAME:(SQL Developer) 2014-09-29 14:57:30.310
*** ACTION NAME:() 2014-09-29 14:57:30.310
*** CONTAINER ID:(3) 2014-09-29 14:57:30.310
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x0] [PC:0x7FEF691FB5A, qcsogolz()+208]
DDE: Problem Key 'ORA 7445 [qcsogolz]' was flood controlled (0x4) (incident: 40818)
ORA-07445: exception encountered: core dump [qcsogolz()+208] [ACCESS_VIOLATION] [ADDR:0x0] [PC:0x7FEF691FB5A] [UNABLE_TO_READ] []
Dump file c:apporacleuserdiagrdbmsorclorcltraceorcl_ora_2084.trc
Mon Sep 29 14:57:30 2014
ORACLE V12.1.0.1.0 - 64bit Production vsnsta=0
vsnsql=16 vsnxtr=3
Dumping diagnostics for abrupt exit from ksedmp
ksedmp exception at address PC:0x0
ksedmp exception at 0000000000000000
Dumping initial exception call-stack
------------------- Call Stack Trace ---------------------
Frameptr RetAddr Param#1 Param#2 Param#3 Param#4 Function Name
000007FEF691FB5A 0000000000000000 0000000000630072 0000000000000000 0000000000000000 0000000000000000 qcsogolz()+208
000007FEF692BD90 000007FEF691FA8A 000000001969AE00 000007FE00000000 0000000000035490 000007FE00000000 qctcopn()+1904
000007FEF692B7B8 000007FEF692B620 0000000019696BC0 0000000022C90F80 000000014A2062A0 000000014657990E qctcopn()+408
000007FEF692B7B8 000007FEF692B620 000000014A2062A0 00000000196954C0 00000000196954C0 000007FF1A0EC938 qctcopn()+408
000007FEF692B7B8 000007FEF692B620 00000000196964E0 000007FEF65DBEE3 000000001522B400 0000000022C90F80 qctcopn()+408
000007FEF692B7B8 000007FEF692B620 0000000000000000 000000001522A7A8 000000001522A2E8 0000000000000000 qctcopn()+408
000007FEF6928BA3 000007FEF692B620 0000000022C91540 000007FEF65DBC6D 0000000000000000 000000001522A9B8 qctcpqb()+291
000007FEF6928A53 000007FEF6928A80 0000000019695C20 0000000022C90F80 0000000000035490 00000001433D61D6 qctcpqbl()+51
000000014561599A 000007FEF6928A20 00000001460088E0 000000002DDB5570 0000000000000001 000000001522A9B8 xtydrv()+138
0000000145FE901F 0000000145615910 0000000015227AC8 0000000000000000 0000000022C91540 000007FEF65DBFCC kkqcttcalo()+383
0000000145FF4C5D 0000000145FE8EA0 0000000000000000 0000000000000000 0000000000008000 0000000002000000 kkqctdrvCVM()+1501
00000001462F36EF 0000000145FF4A37 0000000000000000 0000000000000433 0000000022C91540 000007FEF61F6186 kkqvmTrMrg()+3087
00000001462F2148 00000001462F2AE0 0000000000000000 00000001422DEED1 0000000000000001 0000000000037038 kkqvmdrv2()+872
00000001460059B9 00000001462F1DE0 0000000000000000 000007FEF65E1153 0000000000000000 00000001460D7EAE kkqctdrvTD()+809
0000000145F213E1 0000000146005690 0000000000000000 000000002DDB5ED0 0000000000000002 00000000000000A1 kkqdrv()+6977
000000014600504C 0000000145F1F8A0 0000000015222578 000007FF00000000 000000000003AF58 000007FF1A0ECB80 kkqctdrvIT()+828
0000000145EE8B5A 0000000146004D10 0000000000000000 000000001522FF48 0000000000000038 000000001522FF48 apadrv()+4010
0000000143250A15 0000000145EE7BB0 0000000000000000 0000000000000433 0000000000000000 0000000000000000 opitca()+2565
000000014025C3A6 0000000143250010 00000000196EEB60 000007FF1A0ECB80 000000002DDB8CE8 0000000100000002 kksLoadChild()+8886
0000000140DA2D9E 000000014025A0F0 0000000022C91540 000007FF1839B920 000007FF1CE6F730 000007FF1839B920 kxsGetRuntimeLock()+2414
0000000140BE5A2A 0000000140DA2430 0000000022C91540 00000000196EEB60 000000002DDB8110 000000000000012C kksfbc()+15626
0000000140BDCB6E 0000000140BE1FD0 00000000196EEB60 0000000000000003 000007FF00000108 000000002DDBB020 kkspsc0()+2526
0000000140BDE812 0000000140BDC43E 0000000015242330 000000002DDBB020 0000000000000137 0000000000000003 kksParseCursor()+130
000000014630AC0C 0000000140BDE790 00000000151C6AF8 00000000151C9B98 0000000000000000 000007FEF7C21726 opiosq0()+3004
00000001442E760A 000000014630A050 000007FF00000003 000000014915B0C0 0000000000000000 00000000000000A4 kpooprx()+410
00000001442E3A52 00000001442E7470 0000000022C962AC 0000000000000001 0000000022C962D0 0000000000000001 kpoal8()+994
0000000140E685C1 00000001442E3670 0000000000000001 000000002DDBB2BC 00000000169711A0 000000002DDBB295 opiodr()+1601
000007FEF75CD7A7 0000000140E67F80 000007FF0000005E 000007FF0000001F 000000002DDBD8A0 000007FE00000000 ttcpip()+1223
0000000140E64E10 000007FEF75CD2E0 0000000022CB1140 0000000000000000 0000000000000000 0000000000000000 opitsk()+2160
0000000144768CD7 0000000140E645A0 0000000000000000 0000000000000000 000000002DDBF080 000000002DDBE7F0 opiino()+1079
0000000140E685C1 00000001447688A0 000000000000003C 0000000000000000 000000002DDBF2F0 0000000000000000 opiodr()+1601
000000013FE2F68A 0000000140E67F80 000000000000003C 0000000000000004 000000002DDBF2F0 0000000000000000 opidrv()+842
000000013FE3074E 000000013FE2F3DC 000000010000003C 000007FE00000004 000000002DDBF2F0 0000000015106A28 sou2o()+94
000000013FD612E4 000000013FE306F0 01CFDBED484830FD 0000000014A364D0 000E001D000907DE 0001015900120039 opimai_real()+276
000000013FD610BA 000000013FD611D0 0000000000000000 0000D6736DC2EA78 0000000014A364D0 000000002DDBF4A8 opimai()+170
000000013FD62239 000000013FD61010 0000000000000000 0000000149041F90 0000000000000050 000000000000196C OracleThreadStart()+713
0000000076CC59ED 000000013FD61F70 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000076CC59ED
0000000076EFC541 0000000076CC59E0 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000076EFC541
---------------- End of Call Stack Trace -----------------
This is a bug in 12.1.0.1 (Bug 17633803). It will be fixed in 12.2 and patched in 12.1.0.2.
There is a workaround which is to run
ALTER SESSION SET "_optimizer_unnest_scalar_sq"=false;
I've tried running your sample code above and the workaround does fix the issue in 12.1.0.1
UPDATE
Tested as fixed and working (without the workaround) in 12.1.0.2
链接地址: http://www.djcxy.com/p/81726.html