Home » SQL & PL/SQL » SQL & PL/SQL » Report hangs (possibly query related).. is this possible cause? (Oracle Reports 11g, Oracle 12c Database)
Report hangs (possibly query related).. is this possible cause? [message #664191] Thu, 06 July 2017 09:46 Go to next message
nait1234
Messages: 29
Registered: August 2013
Location: Edmonton
Junior Member
We have a report that is run probably about 1500 times a day. It now just hangs and won't complete. It just says this:
Job status: Report A Before Report trigger is running. is running.. Waiting
Wait while job is processed. Or, click on the link below to return to the job list

A quick look at the Before Report trigger itself... I don't think this is the cause of the hanging. The before report trigger is a couple of very quick queries hitting primary keys. They execute quickly when run outside of the report.


However, after poking around a bit, I wonder if I have found the culprit. There is a call to a function in a package. This function declares a cursor which just returns a count of rows matching criteria. This select is across a database link, and reading from a SQL Server database. The query itself can be run very quickly. However, if I call the package, it will hang.

The function body is pretty small.
FOR rec in cursor_name LOOP
  return rec.count_column;
END LOOP;

return null;


I'm wondering if Oracle (12c) will implicitly close the cursor when done this way.

I have attempted to change this to be structured better:
OPEN cursor_name;
FETCH cursor_name INTO v_count;
CLOSE cursor_name;

RETURN v_count; 

.. but that still hangs when I call the function via PL/SQL...

my_count_var := MY_FUNCTION(some_parameter);

However, oddly, if I just run the PL/SQL query statement itself, it returns a value almost immediately.


So my question is it possible that something broke (perhaps a bunch of open cursors or something) this from working properly from exiting within the for loop? Or is that a red herring?

Right now the suspicion from my DBA team is that there is something wrong with our gateway server between Oracle and SQL Server, but I wanted to be sure it isn't something in code causing the problem.

Re: Report hangs (possibly query related).. is this possible cause? [message #664193 is a reply to message #664191] Thu, 06 July 2017 09:55 Go to previous messageGo to next message
Michel Cadot
Messages: 65254
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I'm wondering if Oracle (12c) will implicitly close the cursor when done this way.

Yes.

Quote:
I have attempted to change this to be structured better:

It is sure a better way but you have to handle NO_DATA_FOUND and TOO_MANY_ROWS exceptions (of course if the query is just SELECT COUNT(*) it is not necessary, just good programming).

Quote:
Right now the suspicion from my DBA team is that there is something wrong with our gateway server between Oracle and SQL Server

Your DBA should then be able to see your session waiting for an event such as "SQL*Net message from dblink" or "SQL*Net more data from dblink"... but if you can execute the procedure with another tool than Reports, like SQL*Plus, then the problem is not there.

[Updated on: Thu, 06 July 2017 09:56]

Report message to a moderator

Re: Report hangs (possibly query related).. is this possible cause? [message #664194 is a reply to message #664193] Thu, 06 July 2017 09:59 Go to previous messageGo to next message
BlackSwan
Messages: 25748
Registered: January 2009
Location: SoCal
Senior Member
does package do any DML?
Re: Report hangs (possibly query related).. is this possible cause? [message #664195 is a reply to message #664194] Thu, 06 July 2017 10:50 Go to previous messageGo to next message
nait1234
Messages: 29
Registered: August 2013
Location: Edmonton
Junior Member
Hi guys, thanks for the replies..

First off, the function is 100% just based off of a select that joins a table across the database link (again, into SQL server), and a table on the DB im connected to. Primary keys are used, and it's quite a quick query when run on it's own (via TOAD). The query just does COUNT(0) in the select, so only one row expected here, of course.

Thanks for confirming the cursor would close, Michel.

I'll also suggest to them to check what you mentioned regarding waiting for an event.

The really odd thing to me, is this function honestly is just a select, and a simple for loop. More details on this below..


I'll actually paste the full thing below to show the simplicity of the function:

FUNCTION F_FUNCTION_NAME(p_param IN NUMBER) 
RETURN NUMBER IS
    
    CURSOR cursorname IS
    SELECT
       COUNT(*) COUNTS
    FROM
        dbo.remotename@remotelink rem,
        local_table_name ltn
      WHERE
          primary_key_column = p_param
          AND ltn.actual_date = rem."Date"
           AND ltn.div_division_number = rem."Garage_ID"
           AND ltn.block_number = rem."Run_Number"
           AND 'x' = (SELECT 'x' FROM dbo.remote_table_2@remotelink v where 
                v."Vehicle_Code" is not null and v.id = rem."Vehicle_ID");

BEGIN

    FOR rec IN cursorname LOOP
        RETURN rec.COUNTS ; 
    END LOOP;
    
    RETURN NULL;

END;

Obviously I've redacted some of the names above a bit, but that really is the whole thing.

I can take the entire select, paste it in TOAD and run it, and it works fine (read on though).

If I however try and run the function...
select PACKAGENAME.F_FUNCTION(54679179) FROM dual;

It will hang for forever.

I just tried this in SQL Plus, however, as suggested, and it ran perfectly fine (as the function above). So now I'm even a bit more confused. The database login credentials used for both are the same. Maybe this function isn't the problem?? Or is possible TOAD and Oracle Reports could have a problem with it, but SQL Plus not?

I'll see if the DBA can monitor a session to confirm where the hanging is occurring. That should have probably been done earlier, but they had seen some errors in Windows logs (for the gateway server that allows connections between SQL Server/Oracle) about the connection to the link not being established, and they were diagnosing that.

Re: Report hangs (possibly query related).. is this possible cause? [message #664196 is a reply to message #664195] Thu, 06 July 2017 11:06 Go to previous messageGo to next message
BlackSwan
Messages: 25748
Registered: January 2009
Location: SoCal
Senior Member
If such a situation happened to me, then I would do as below.

ALTER SESSION SET SQL_TRACE=TRUE;

then I would do " tail -f <trace_filename>
in order to see what was occurring when the report was seemingly hung.
Re: Report hangs (possibly query related).. is this possible cause? [message #664197 is a reply to message #664196] Thu, 06 July 2017 11:17 Go to previous messageGo to next message
nait1234
Messages: 29
Registered: August 2013
Location: Edmonton
Junior Member
Thanks for the suggestion, BlackSwan. The DBA team actually is trying one thing right now, and if it doesn't work, they mentioned they would bring a laptop over and do tracing with me while I run the offending code.

Also, just as a heads up... I was just looking in TOAD at the sessions, and I happened to notice that the "Current Statement" for a lot of these users actually shows this statement (the select I pasted above), so I think it actually is this statement that is hanging. I also noticed this in Total Waits:
SQL*Net message from client

Time waited: 189

[Updated on: Thu, 06 July 2017 11:19]

Report message to a moderator

Re: Report hangs (possibly query related).. is this possible cause? [message #664198 is a reply to message #664197] Thu, 06 July 2017 11:25 Go to previous messageGo to next message
Michel Cadot
Messages: 65254
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Can you create a procedure and query V$SESSION?
If so, then create T.Kyte's print_table function and post the result of:
exec print_table('select * from v$session where sid=<sid of your waiting session>');
(Don't forget to use code tags for the output, you eed to "set serveroutput on format wrap" to see the output of the function).

Re: Report hangs (possibly query related).. is this possible cause? [message #664199 is a reply to message #664198] Thu, 06 July 2017 12:13 Go to previous messageGo to next message
nait1234
Messages: 29
Registered: August 2013
Location: Edmonton
Junior Member
Hi guys, thanks for your help, but I think things may be resolved now. If not, I will try any of the suggestions mentioned recently. I appreciate all the help.

It looks like it was this gateway that was hung. What looked to be happening was that any sessions that were reading across the link just pretty much hung indefinitely. In turn, that caused a traffic jam on the reports server. Even if the problem was fixed (which I think it was about 2 hours ago now), the problem would still persist due to the hung sessions. I've killed off everything, and also our DBAs flipped everyone over to a different report server instance temporarily. Everything seems to be going through fine now. The load is pretty low right now, so it's a bit early to know, but I think this is a good sign. At any rate, I think it's clear that the gateway was the issue. The gateway problem was resolved, but the reports server was basically permanently broken, until those sessions were killed.

I've suggested to the DBA team that we set up a timeout on those print jobs, if possible (as for my business users, if they don't print within a few mins, they're basically useless).

Thanks for all the time and suggestions that you guys gave!

Re: Report hangs (possibly query related).. is this possible cause? [message #664200 is a reply to message #664199] Thu, 06 July 2017 12:16 Go to previous message
Michel Cadot
Messages: 65254
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thank you for your feedback and explanation.

Previous Topic: Accessing Sub directory under Oracle directory
Next Topic: Functional based index
Goto Forum:
  


Current Time: Thu Dec 14 17:04:55 CST 2017

Total time taken to generate the page: 0.01612 seconds