This discussion is archived
9 Replies Latest reply: Jan 17, 2013 7:32 AM by newbiegal RSS

missing index and cursors

newbiegal Newbie
Currently Being Moderated
Hi friends,

We are in the midst of troubleshooting an application/db problem of a customer, at this point we are thinking it's cursors issue with the findings so far.. Database is on 10.2.0.4 on windows platform. It's a multithreaded application where there is only one session created on the database end for multiple user actions from the application. So the cursors get accumulated in this one session.
Open_cursors count is set to 3000 for the database. I periodically checked for the cursors count for this session, it goes up gradually and hits 3000 limit crashing the session. We found that one SQL statement creates over 1500 cursors and the other sql statements from the application uses about 2-10 cursors each. The sql statement that is using up excessive cursors is a simple select with 100 columns as below

SELECT ......... (100 columns)..... from table where field1 = :v1 order by field3

field1 is the PK in the table with unique index. field3 does not have an index, could this be an issue to create excessive cursors in the database?

The above select is being executed in a loop with some application logic. Each record is fetched in a loop.. There is a lot of other sql's similar to this executed in a loop but this statement seem to be creating too many cursors..

Please give me your valuable suggestions.. Thank you so much

Edited by: newbiegal on Jan 16, 2013 12:12 PM
  • 1. Re: missing index and cursors
    Centinul Guru
    Currently Being Moderated
    newbiegal wrote:
    SELECT ......... (100 columns)..... from table where field1 = :v1 order by field3

    field1 is the PK in the table with unique index. field3 does not have an index, could this be an issue to create excessive cursors in the database?
    What's the point of the ORDER BY if field1 is the primary key? This should only return one row by definition which negates the need for the order by.
    The above select is being executed in a loop with some application logic. Each record is fetched in a loop..
    That sounds like an application design issue. It is typically not good practice to execute the same statement over and over in a FOR LOOP. That's usually a sign that a JOIN can be used instead. With that said are the cursors being closed in the code?
  • 2. Re: missing index and cursors
    Justin Cave Oracle ACE
    Currently Being Moderated
    I'm not sure that I understand exactly what the problem that you're troubleshooting is.

    Are you saying that the application is getting an ORA-01000: maximum open cursors exceeded error? If so, that implies that the immediate problem is that your application has a cursor leak. This is most commonly the case when you have an application that doesn't clean up the resources that it requests from the database (i.e. not closing the ResultSet, Statement, and Connection objects in Java, for example, or closing the equivalent objects in .Net).

    The face that you seem to be calling the same SQL statement in a loop with a different primary key value, as has been mentioned, is probably a separate issue where executing a single SQL statement with a join would be more efficient. If you have a cursor leak, though, the specific SQL statement that you're using is irrelevant, the issue is that the application isn't cleaning up after itself.

    Justin
  • 3. Re: missing index and cursors
    newbiegal Newbie
    Currently Being Moderated
    Sorry I should have mentioned, there are 2 primary key fields in the table. So field1 is a part of primary key in the table..

    I asked the developers to check on that first whether or not the cursors are closed in the code. The developers claim that they don't open/close the cursors in the code and there is no code in the application to explicitly close the cursor only Oracle handles it. The select statement uses bind variable to fetch every record in a loop.. Once the cursor count hits the limit, application throws
    ORA-0100 error..

    Please share your thoughts..

    Edited by: newbiegal on Jan 16, 2013 12:55 PM
  • 4. Re: missing index and cursors
    newbiegal Newbie
    Currently Being Moderated
    Thanks for your replies.. Can you please tell me how we could make this sql statement efficient using a join instead of running it in a loop??? Thanks again
  • 5. Re: missing index and cursors
    Justin Cave Oracle ACE
    Currently Being Moderated
    newbiegal wrote:
    I asked the developers to check on that first whether or not the cursors are closed in the code. The developers claim that they don't open/close the cursors in the code and there is no code in the application to explicitly close the cursor only Oracle handles it. The select statement uses bind variable to fetch every record in a loop.. Once the cursor count hits the limit, application throws
    ORA-0100 error..
    Describe the application. Is this a Java application running on an app server? A PL/SQL application running on the database? Something else?

    Justin
  • 6. Re: missing index and cursors
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Justin Cave wrote:
    newbiegal wrote:
    I asked the developers to check on that first whether or not the cursors are closed in the code. The developers claim that they don't open/close the cursors in the code and there is no code in the application to explicitly close the cursor only Oracle handles it. The select statement uses bind variable to fetch every record in a loop.. Once the cursor count hits the limit, application throws
    ORA-0100 error..
    Describe the application. Is this a Java application running on an app server?
    Very important question - if this were (for example) being call from a loop written in java, with code that called a java class that was instantiated and discarded (possibly the wrong Java terms) every time around the loop, then each instantiation would create a new cursor which couldn't close because the server would think that the Java front end was still holding it open. It's an error I used to see relatively frequently about 10 years ago.

    Regards
    Jonathan Lewis
  • 7. Re: missing index and cursors
    961833 Newbie
    Currently Being Moderated
    Great explanation Jonathan.
    I have similar problem in one of my prod system.
    In such case (new cursor creation), How does Oracle expose it in v$sql_shared_cursor under mistmatch reason ?
  • 8. Re: missing index and cursors
    rp0428 Guru
    Currently Being Moderated
    HIJACKED THREAD!

    Please don't hijack another user's thread by asking your own question.
    >
    I have similar problem in one of my prod system.
    In such case (new cursor creation), How does Oracle expose it in v$sql_shared_cursor under mistmatch reason ?
    >
    Create your own thread and ask your question, providing all of the particulars like the 4 digit Oracle version.

    If you think your question is somehow related to this one then put a link to this thread in your new thread.
  • 9. Re: missing index and cursors
    newbiegal Newbie
    Currently Being Moderated
    Thanks everyone for the replies. We use C++ in the code.. The application guys have found 'some' cursors issue in the code so they are taking a look at it. We hope it will fix the problem.. Thanks again for all your help

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points