Home » SQL & PL/SQL » SQL & PL/SQL » Open cursor statement getting stuck (V.interesting issue) (Oracle 10G , Windows server)
Open cursor statement getting stuck (V.interesting issue) [message #582427] Thu, 18 April 2013 05:43 Go to next message
saket_kumar
Messages: 7
Registered: April 2013
Location: India
Junior Member
Hi,
I am Saket and before I post my query I would like to mention that I won't be able to give the data,specific information(like query or table names) about the issue as this concerns the privacy of some multinational company(where I work).

We have windows server and we are currently using Oracle 10g.

Issue-

We have a ProC, which has a SQL statement where it is trying to OPEN a cursor. The cursor query is huge and fetches data from 3 tables. 3 tables have 5k, 600k and 1.7 millions records respectively but the 'where' condition in cursor query makes the total count to 3k lines only.

Now the problem is , we have a schedule reboot of the server every Saturday night and the next day(Sunday night) when the program runs it gets stuck in the query --> OPEN <cursor> . We wait for 5 hours but this OPEN cursor query never executes or completes, that means our program gets stuck here.Eventually we have to kill the program.We have tried running it after killing as well but it again gets stuck.

Now the interesting part. On Monday business happens as usual but in the night when we again run this program it runs within minutes. The records in the tables are more or less same. Open cursor does not get stuck at all. It runs every night properly but every Sunday night it gets stuck.Please note that the program does not run on Saturday nights. So on Sunday night it runs for the first time after server reboot.

So there is something with Open cursor statement(with so much data) after server reboot. What exactly Open cursor does in memory or database.

I will be really grateful if someone can suggest something here.

Regards,
Saket.
Re: Open cursor statement getting stuck (V.interesting issue) [message #582429 is a reply to message #582427] Thu, 18 April 2013 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 58625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I won't be able to give the data,specific information(like query or table names) about the issue as this concerns the privacy of some multinational company(where I work).


You can nevertheless create a test case representing the issue you are trying to solve. We do NOT need actual tables and data, just ones that represent your problem.

Quote:
I will be really grateful if someone can suggest something here.

Quote:
, we have a schedule reboot of the server every Saturday night


Remove this and your problem will be solve.
Keep it and your problem will stay.

Regards
Michel
Re: Open cursor statement getting stuck (V.interesting issue) [message #582431 is a reply to message #582429] Thu, 18 April 2013 06:26 Go to previous messageGo to next message
saket_kumar
Messages: 7
Registered: April 2013
Location: India
Junior Member
All right let me develop a test case but I can't stop the reboot. Reboot is mandatory as the DB server is used quite rigorously, lot of data transaction happens over a week, it is advised by Oracle team to reboot this server once a week.


Can you suggest something about Open cursor statement(with so much data) . What exactly Open cursor does in memory or database. Why the same cursor gets stuck after server reboot, when it is run for the first time but runs fine after 24 hours(after market business hour is over).

Regards,
Saket.
Re: Open cursor statement getting stuck (V.interesting issue) [message #582433 is a reply to message #582431] Thu, 18 April 2013 06:44 Go to previous messageGo to next message
Littlefoot
Messages: 19335
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This won't help, I guess, but nevertheless - if you don't have any better option, you could try it next Saturday.

How about opening an "empty" cursor first, close it, and then open it normally. Something like this:
declare
  -- your huge cursor. "and 1 = par_what" will first evaluate to "1 = 2" and
  -- return nothing; then it'll evaluate to "1 = 1" and return the result set
  cursor c1 (par_what number) is 
    select d.dname,
           e.ename
    from emp e,
         dept d
    where e.deptno = d.deptno
      and 1 = par_what;
  c1r c1%rowtype;
begin
  open c1 (2);
  close c1;
  
  open c1 (1);
  loop
    fetch c1 into c1r;
    exit when c1%notfound;
    
    -- your processing goes here
    dbms_output.put_line(c1r.ename);
  end loop;
  close c1;
end;
Re: Open cursor statement getting stuck (V.interesting issue) [message #582434 is a reply to message #582431] Thu, 18 April 2013 06:51 Go to previous messageGo to next message
Michel Cadot
Messages: 58625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can you suggest something about Open cursor statement(with so much data) .


How much data it has to handle or to return is irrelevant to the issue.
Most likely the issue comes from the fact Oracle has to insert in memory all information needed to execute the cursor which table, column, view, synonyms definition as well as grants, statistics and so on.
This is why you must not stop the database, to avoid unnecessary work.

Regards
Michel
Re: Open cursor statement getting stuck (V.interesting issue) [message #582438 is a reply to message #582434] Thu, 18 April 2013 07:00 Go to previous messageGo to next message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel Cadot wrote on Thu, 18 April 2013 12:51

How much data it has to handle or to return is irrelevant to the issue.

Not necessairly. If it's a select for update then oracle has to lock all the records that match the cursor on the open statement.

[Updated on: Thu, 18 April 2013 07:01]

Report message to a moderator

Re: Open cursor statement getting stuck (V.interesting issue) [message #582439 is a reply to message #582438] Thu, 18 April 2013 07:01 Go to previous messageGo to next message
Michel Cadot
Messages: 58625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If it's a select for update


Good point. I forgot this case.

Regards
Michel
Re: Open cursor statement getting stuck (V.interesting issue) [message #582442 is a reply to message #582439] Thu, 18 April 2013 07:06 Go to previous messageGo to next message
saket_kumar
Messages: 7
Registered: April 2013
Location: India
Junior Member
Nope its not for select for update
Re: Open cursor statement getting stuck (V.interesting issue) [message #582443 is a reply to message #582431] Thu, 18 April 2013 07:08 Go to previous messageGo to next message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
saket_kumar wrote on Thu, 18 April 2013 12:26
it is advised by Oracle team to reboot this server once a week.


Most oracle experts advise not to do that, on the grounds that it almost always makes performance worse not better.
Re: Open cursor statement getting stuck (V.interesting issue) [message #582447 is a reply to message #582434] Thu, 18 April 2013 07:32 Go to previous messageGo to next message
saket_kumar
Messages: 7
Registered: April 2013
Location: India
Junior Member
I will try to build the test case but that will take some time as I am really caught up with lot of work plus I have to travel this weekend.

[quote title=Michel Cadot wrote on Thu, 18 April 2013 16:51]Quote:

How much data it has to handle or to return is irrelevant to the issue.
Most likely the issue comes from the fact Oracle has to insert in memory all information needed to execute the cursor which table, column, view, synonyms definition as well as grants, statistics and so on.



This sounds more convincing , I tried to run the cursor with no data in the tables hence cursor is fetching nothing, it completed with in seconds. After sometime I ran the whole program, again it got stuck in the open cursor statement but that particular day Open cursor statement executed but it took 4 and a half hours(which is way above average normal time, normally the cursor take 2-3 mins to execute).

Cursor fetches about 3000 records on an average, daily.

Also I would like to mention, we are running 100s of programs that day along with this, they also have cursors of equal magnitude. We have not seen any issue with them.

Regards
Saket
Re: Open cursor statement getting stuck (V.interesting issue) [message #582448 is a reply to message #582447] Thu, 18 April 2013 07:39 Go to previous messageGo to next message
saket_kumar
Messages: 7
Registered: April 2013
Location: India
Junior Member
Also can you suggest whether creating indexes will help here?? I don't think so but still.

Our database is in Windows server..hence reboot is mandatory as the OS is not that stable.


Regards,
Saket
Re: Open cursor statement getting stuck (V.interesting issue) [message #582450 is a reply to message #582448] Thu, 18 April 2013 07:48 Go to previous message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
If the code is hanging on the open and the cursor isn't select for update then indexes are irrelevant, but really we can't offer any real advice (other than stop rebooting the server) without more information.
A simple open statement should never take 5 hours, even after a reboot.
The obvious thing to do at this point is turn on database tracing next time the problem is going to occur and look at the resulting trace file to see where all the time is being spent.
Previous Topic: MONTHS_BETWEEN function clarification
Next Topic: tuning package
Goto Forum:
  


Current Time: Wed Jul 30 16:57:17 CDT 2014

Total time taken to generate the page: 0.05804 seconds