Long SQL statements

[Edcile]

I am having trouble with SQL statements that take an extended period of time to return a result set. Basically, I execute the SQL statement or Stored Procedure and I see the Session open up on Oracle and it apears to be processessing then after a long time the Session dissapears but nothing is ever returned to PL/SQL Developer.

When this happens on a regular SELECT command I can cancel the task and I receive the error ORA-03113: end-of-file on communication channel. Then ORA-03114: not connected to ORACLE. I

When this happens to a cursor that is returned from a Stored Procedure I can not cancel the action and I actually have to force a close of PL/SQL Developer.

I am a new user of PL/SQL Developer so maybe I am doing something wrong...

Thanks,

 

[Marco Kalter]

ORA-03113 usually means that the Oracle Server process for the current session has crashed. Can you check if a user trace dump file exist on the database server that corresponds to this ORA-03113 error? Usually you will see an ORA-00600 error here, with some error codes that may help us find the cause of the problem. If it does, can you send it to me?

 

[Edcile]

I am a novice at administering Oracle so you will have to give me a bit more information about where to look for the Trace Dump file.

I did do some more investigating and found that the combination of factors here is that it is a long SQL Execution and there is an error in the execution. I found this by running my SQL query on the server using SQL+ Worksheet and I received a SQL error. Once I corrected this things seemed to start working again in PL\SQL Developer but I would have assumed that the error should have been returned to me in PL\SQL Developer.

Thanks

 

[Marco Kalter]

How can there be a long-running query when there is an error? What exactly is the error?

 

[Edcile]

Sorry for not explaining further. The statement actually does a Join on two tables that have 2,000,000+ rows then groups by two columns and then does a SUM and ROUND on the response from a function. The issue that I was having was that the function was returning multiple rows and therefore and error was generated. However, it took so long to do the JOIN and Group By statements that the error was never returned to PL\SQL Developer.

To try to clarifiy what the statement does I will give you an example.

SELECT
column1,
column2,
SUM( column3),
SUM( ROUND( column4 * FN_Rate(column5, column6, 4353), 2),
SUM( ROUND( FN_Rate(column5, 4355, column6), 2 ) ),
SUM( ROUND( FN_Rate(column5, 305000, column6), 2 ) )
FROM
table1 INNER JOIN
table2 ON table1.column5 = table2.column5
GROUP BY (table1.column7, table1.column8);

Hopefully this clarifies what the issue is. I have now fixed the error and I still never get a response back once I have clicked on the button next to the Cursor Parameter. When I run it through SQL*Plus Worksheet it takes around 5 minutes to respond but it does retrieve the results I want.

 

[Edcile]

I looked even more into this and I think that I do not understand enough about the error/warning that is returned from the function that I am using.

When I execute the function FN_Rate it is supposed to return one value. However, becuase of some bad data it was returning multiple rows. Now when I run the function directly I receive an error that there are multiple rows (in both PL/SQL Developer and SQL Plus). But when I run the stored procedure that returns a cursor filled by my above select statment I receive no error and 300+ rows using SQL Plus and it crashes PL/SQL Developer.

I have added code to raise an applicaiton error and that seems to have made it so that if this error occurs it will always error but I would have expected it to error in the first place.

Can you shed any light on this issue?

Thanks

 

[Edcile]

I did not mention that adding the code to raise the applicaitno error did not seem to change the fact that PL/SQL Developer goes into a state of not responding after I attempt to execute the stored procedure in question.

Thanks again for all your assistance.

 

[Marco Kalter]

Does this still result in ORA-03113? If so, we may need the trace dump file to tackle this. Just search for an "udump" directory on the database server and check if there is a .trc file with a timestamp that matches the error. Like I said, you will probably see an ORA-00600 error in the trace file, with some error codes that may help us find the cause of the problem.

 

[Edcile]

I just did a search and found this folder but the most current file in this folder is from the 5th which is well before I started having this issue. I am not sure what this means but that is what I found

I am not sure what you mean by did it result in an ORA-03113. If you are asking if I get that error back in PL\SQL then the answer is no. There is absolutly no response. No error nothing. You simply click on the button next to the cursor in the test window and then the application goes into an unknown state. What I mean by this is that if you click on other open windows you can still run PL/SQL just fine however the window that is showing the results just sits there and does nothing and if you try to click back on the test window the entire applicaiton freezes and I have to end task the applicaiton. Would it help to see the "auto recovery file" from PL/SQL developer?

When I look on Oracle the session is still active and it appears to execute correctly but the applicaion still hangs.

Thanks for all of your help.

 

[Marco Kalter]

To obtain some more diagnostic information, can you modify the shortcut and add the debug parameter? For example:

"C:\Program Files\PLSQL Developer\plsqldev.exe" debug

Reproduce the problem and send me the debug.txt file that is generated in the PL/SQL Developer directory.