No question at this time
DBA Top 10
1 B. Vroman 14600
2 M. Cadot 11000
3 J. Schnackenberg 8200
4 T. Boles 7950
5 A. Kavsek 6200
6 M. Hidayathullah ... 2200
7 G. Lambregts 1100
8 T. P 1000
9 P. Wisse 900
10 B. Derous 500
10 . Lauri 500
10 R. Pattyn 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48286
Total active users1591
Act. users last 24h3
Act. users last hour0
Registered user hits last week242
Registered user hits last month1121
Go up

ORA-3113 in Select with order by
Next thread: Understanding bitmap indexes usage
Prev thread: getting a pdb in a shell script

Message Score Author Date
Hello, The below sql throws an ORA-03113 end of...... Peter Smeets Oct 31, 2018, 14:27
If you have a valid support contract you can check...... Gareth Graham Oct 31, 2018, 14:43
If you can truly search support.oracle.com then yo...... Tim Boles Oct 31, 2018, 16:10
Sorry we can't file an SR. Thank you.... Peter Smeets Oct 31, 2018, 16:53
Doc ID 1468556.1 covers Oracle 11g we have this is...... Peter Smeets Oct 31, 2018, 17:06
Do you have a licence?... Gareth Graham Nov 01, 2018, 11:42
Hi Peter, So far, I didn't work with Oracle RDB...... Lauri Nov 07, 2018, 07:59
Hello, we have Oracle licences but no support cont...... Peter Smeets Nov 07, 2018, 09:08
We've told you our idea! Open a Service Request. ...... Gareth Graham Nov 07, 2018, 09:54
Peter, You have added something interesting: "W...... Lauri Nov 07, 2018, 13:09
Hi Peter, just a small hint from my side....;),...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Ales Kavsek Nov 07, 2018, 14:10
Hi Lauri, My colleque debugged the program but...... Peter Smeets Nov 07, 2018, 14:56
Hello Ales, Thank you very much for the "small"...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Peter Smeets Nov 07, 2018, 16:25
Hi Peter, I'm glad that you found the "workarou...... Ales Kavsek Nov 07, 2018, 22:08
Hello Ales, In the past there was a case where ...... Peter Smeets Nov 08, 2018, 09:40

Follow up by mail Click here


Subject: ORA-3113 in Select with order by
Author: Peter Smeets, Belgium
Date: Oct 31, 2018, 14:27, 18 days ago
Os info: Micorsoft Windows 64bit
Oracle info: 12.2.0.1
Error info: Exception [type: ACCESS_VIOLATION, UNABLE_TO_WRITE] [ADDR:0x0] [PC:0x14CDB7287, __intel_memcpy()+1063]
Errors in file C:\ORA12CR2\diag\rdbms\gcs2\gcs2\trace\gcs2_ora_601272.trc (incident=40262):
ORA-07445: Exception aangetroffen: core-dump [_intel_memcpy()+1063] [ACCESS_VIOLATION] [ADDR:0x0] [PC:0x14CDB7287] [UNABLE_TO_WRITE] [].
Message: Hello,

The below sql throws an ORA-03113 end of file on communication channel in Oracle 12.2.0.1

It works fine in 12.1 and in 18.0.

If we leave out the order by it also works in 12.2.
(In fact if we only leave out the m2.searchname in the order by it also works...)

I cannot create a service request at Oracle and can't seem to find a solution on Oracle support...

Has anyone else experienced this in 12.2 and cares to share a possible solution?

Thank you very much,
Peter

SELECT M1.COMPANY AS COMPANY,
M1.DELIVERYCUSTOMER AS DELIVERYCUSTOMER,
M1.DELIVERYRETURN AS DELIVERYRETURN,
:language AS LANGUAGE,
:CUSTOMER AS CUSTOMER,
:CURRENCY AS CURRENCY,
1 AS CODEARTICLERAWMATERIAL,
m1.codediscount,
m1.discount,
MIN(M3.DELIVERYRETURN) AS CODERETURN,
M1.DELIVERYCUSTOMERSEQUENCE,
M1.MODEL,
M1.ARTICLEWITHMODEL,
M1.SPECIFICATION,
M1.ARTICLE,
M2.MODELARTICLEDESCRIPTION, m2.searchname,
:DATEDELIVERED as datedelivered,
:facextraaddressno as facextraaddressno,
M1.PACKAGE,
decode(m1.package, null, ' ', '**') as packageYN
FROM ALARTIC M2, AMDELCSD M1, AMDELCUS M3, amdelcss a, amdelcos b
WHERE M3.COMPANY = :COMPANY
AND M3.DELIVERYCUSTOMER = :DELIVERYCUSTOMER
AND M1.COMPANY = :COMPANY
AND M1.DELIVERYCUSTOMER = :DELIVERYCUSTOMER
AND M1.CODEARTICLERAWMATERIAL = 1
AND M2.COMPANY = M1.COMPANY
AND M2.ARTICLE = M1.ARTICLE
AND M2.LANGUAGE = :language
AND a.company = :company
AND a.deliveryreturn = m1.deliveryreturn
AND a.deliverycustomer = :deliverycustomer
AND a.deliverycustomersequence = m1.deliverycustomersequence
AND b.company(+) = :company
AND b.deliveryreturn(+) = a.deliveryreturn
AND b.deliverycustomer(+) = a.deliverycustomer
AND b.deliverycustomersequence(+) = a.deliverycustomersequence
AND b.sizeno(+) = a.sizeno

GROUP BY M1.COMPANY,
b.ordercustomertype, b.ordercustomer,b.ordercustomersequence,
M1.DELIVERYCUSTOMER,
M1.DELIVERYRETURN,
m1.codediscount,
m1.discount,
M1.DELIVERYCUSTOMERSEQUENCE,
M1.MODEL,
M1.ARTICLEWITHMODEL,
M1.SPECIFICATION,
M1.ARTICLE,
M2.MODELARTICLEDESCRIPTION,m2.searchname,
M1.Package
ORDER BY m2.searchname, b.ordercustomertype, b.ordercustomer, b.ordercustomersequence, m1.deliverycustomersequence
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-3113 in Select with order by
Author: Gareth Graham, United Kingdom
Date: Oct 31, 2018, 14:43, 18 days ago
Message: If you have a valid support contract you can check the ORA-600/ORA-7445/ORA-700 Error Look-up Tool (Doc ID 153788.1) here: https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=377134063736499&id=153788.1&_adf.ctrl-state=pa3aqcljf_67

Although as you say you can't log a SR I wonder if that is the case.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-3113 in Select with order by
Author: Tim Boles, United States
Date: Oct 31, 2018, 16:10, 18 days ago
Message: If you can truly search support.oracle.com then you should have seen several articles on this issue.

Search "ORA-7445 order by"

There are several database articles on this subject. None of them exactly match your situation but they all point towards a Bug in the software. There are several possible work arounds you can find under those articles.

ORA-07445 with Failing Function qkxrDo_Pvt from Query with positional ORDER BY (Doc ID 1468556.1)

ORA-07445: [subex1()+59] Executing Select With ORDER BY Clause (Doc ID 2102160.1)

Regards
Tim
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-3113 in Select with order by
Author: Peter Smeets, Belgium
Date: Oct 31, 2018, 16:53, 18 days ago
Message: Sorry we can't file an SR.
Thank you.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-3113 in Select with order by
Author: Peter Smeets, Belgium
Date: Oct 31, 2018, 17:06, 18 days ago
Message: Doc ID 1468556.1 covers Oracle 11g we have this issue in 12.2.

Doc ID 2102160.1 covers Oracle 12.1 as I said we have this issue in 12.2 while it works in 12.1.

Nevertheless I tried all suggested hidden parameters without success.

Applying 11g patches will not happen and not using the order by is not an option.

Thank you
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-3113 in Select with order by
Author: Gareth Graham, United Kingdom
Date: Nov 01, 2018, 11:42, 17 days ago
Message: Do you have a licence?
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-3113 in Select with order by
Author: Lauri, Netherlands
Date: Nov 07, 2018, 07:59, 11 days ago
Message: Hi Peter,

So far, I didn't work with Oracle RDBMS 12cR2.
This is typically the kind of error for which you would raise an SR.
Is there a reason why you cannot create a service request?
This is something essential for the DBA work.

Kind Regards
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-3113 in Select with order by
Author: Peter Smeets, Belgium
Date: Nov 07, 2018, 09:08, 11 days ago
Message: Hello, we have Oracle licences but no support contract therefor we cannot file an SR.

We recently noticed that if you execute the sql in sqldeveloper and fill in the parameters, the error doesn't occur?! It only occurs when executed from our Delphi program and then still it depends what from/to parameters the users keys in...
From :DELIVERYCUSTOMER To :DELIVERYCUSTOMER
47830 - 47835 = OK
47832 - 47836 = NOK Error occurs
47835 - 47837 = OK
47832 - 47837 = OK Error doesn't occur?!

We will contact our relation at Oracle and see what our options are. Meanwhile if anyone has some idea I'll be glad to try it out.

Kind regards,
Peter
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-3113 in Select with order by
Author: Gareth Graham, United Kingdom
Date: Nov 07, 2018, 09:54, 11 days ago
Message: We've told you our idea! Open a Service Request. Why/how are you paying for a licence but you get no support? Whose idea was that?
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-3113 in Select with order by
Author: Lauri, Netherlands
Date: Nov 07, 2018, 13:09, 11 days ago
Message: Peter,

You have added something interesting: "We recently noticed that if you execute the sql in sqldeveloper and fill in the parameters, the error doesn't occur?! It only occurs when executed from our Delphi program".
You probably need first to debug from Delphi. It's not because you get an Oracle error that the problem is located in the Oracle software.
Possibly, you need to consider the support you have with Delphi.

Kind Regards
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-3113 in Select with order by
Author: Ales Kavsek, Slovenia
Date: Nov 07, 2018, 14:10, 11 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi Peter,

just a small hint from my side....;), whenever I found out that some query is working fine in version x and y but not z I always try the failing query with "downgraded" CBO features. Can you try to add a hint at the beginning of your query:

SELECT /*+ optimizer_features_enable('12.1.0.2') */ M1.COMPANY AS COMPANY,
M1.DELIVERYCUSTOMER AS DELIVERYCUSTOMER,
....

Does it crash?

You obviously hit some Oracle bug, I don't believe it has anything to do with the Delphi. Without official support from Oracle your options are very limited. You'll have to spend a lot of time searching for a workaround. The chances that someone on this site hit the same bug (and is following the forum) as you, are close to nil :(.
Good luck!

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-3113 in Select with order by
Author: Peter Smeets, Belgium
Date: Nov 07, 2018, 14:56, 11 days ago
Message: Hi Lauri,

My colleque debugged the program but could not find a cause/error sadly.

Kind regards,
Peter
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-3113 in Select with order by
Author: Peter Smeets, Belgium
Date: Nov 07, 2018, 16:25, 11 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hello Ales,

Thank you very much for the "small" hint!-) I really appreciate your help!

This did the trick in combination with the parameter optimizer_adaptive_plans which was set to false and after I set it to true it worked.

Kind regards,
Peter
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-3113 in Select with order by
Author: Ales Kavsek, Slovenia
Date: Nov 07, 2018, 22:08, 11 days ago
Message: Hi Peter,

I'm glad that you found the "workaround".
Hmm...just want to clear something, as far as I know optimizer_adaptive_plans is by default True in 12.2.0.1, your probably at some point turned this parameter to False, and now, you turned this back to True, because you remembered what you changed and was trying to see if hint alone is enough. Am I right?

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: ORA-3113 in Select with order by
Author: Peter Smeets, Belgium
Date: Nov 08, 2018, 09:40, 10 days ago
Message: Hello Ales,

In the past there was a case where the performance was not so good. We had the impression that this was due to the optimizer and after different attempts we then set this parameter to false together with

DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'GENCOM', TABNAME => 'AMDELCUS', CASCADE => TRUE, method_opt => 'FOR ALL COLUMNS SIZE 1');

DBMS_STATS.SET_TABLE_PREFS (OWNNAME => 'GENCOM', TABNAME => 'AMDELCUS', pname => 'METHOD_OPT', pvalue => 'FOR ALL COLUMNS SIZE 1');

which improved performance.

Performance was still ok yesterday, hopefully this will still stay in the future.

Kind regards, Peter
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here