FAQ
hi
here are the specs of my db:
version 8.1.7 on windows,open_cursors=500
i am facing a certain issue. we have a few sessions
running out of cursor(ora-1000) and i used 1000 event
to dump trace on these sessions. i found a certain
statement
select .nextval from dual;
being repeated more than 350 times of the available
500 cursors.
the dev say nothing has changed nor has there been a
increase in the load.

can you please advise me whther this could be a issue
or am i missing something here

thanks
sai
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Sai Selvaganesan
INET: ssaisundar_at_sbcglobal.net

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

Search Discussions

  • Mladen Gogala at Dec 9, 2003 at 7:39 pm
    What tool are you using? HAve you considered putting select from the
    sequence in an explicit cursor, open it, fetch it and close it again?
    What Have in mind is something like this:

    declare
    cursor csr is
    select sai.nextval from dual;
    num integer :=0;
    ind integer :=10;
    begin
    while (ind>=0) loop
    open csr;
    fetch csr into num;
    close csr;
    dbms_output.put_line('Sai is:'||num);
    ind:=ind-1;
    end loop;
    end;
    /

    Here is the output:

    QL> /

    Sai is:13
    Sai is:14
    Sai is:15
    Sai is:16
    Sai is:17
    Sai is:18
    Sai is:19
    Sai is:20
    Sai is:21
    Sai is:22
    Sai is:23

    PL/SQL procedure successfully completed.
    On 12/09/2003 01:39:25 PM, Sai Selvaganesan wrote:
    hi
    here are the specs of my db:
    version 8.1.7 on windows,open_cursors=500
    i am facing a certain issue. we have a few sessions
    running out of cursor(ora-1000) and i used 1000 event
    to dump trace on these sessions. i found a certain
    statement
    select .nextval from dual;
    being repeated more than 350 times of the available
    500 cursors.
    the dev say nothing has changed nor has there been a
    increase in the load.

    can you please advise me whther this could be a issue
    or am i missing something here

    thanks
    sai
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Sai Selvaganesan
    INET: ssaisundar_at_sbcglobal.net

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    Mladen Gogala
    Oracle DBA

    Note:
    This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks.
    Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Mladen Gogala
    INET: mladen_at_wangtrading.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Sai Selvaganesan at Dec 9, 2003 at 10:59 pm
    thanks mlade..i will surely give this a shot.

    can you please tell me whether a sequence creates such
    issues. as mentioned earlier, the developers claim
    that no code has changed. im am not able to give any
    kind of reason for this though the trace shows this
    statement being called more than 350 times.

    thanks
    sai
    --- Mladen Gogala wrote:
    What tool are you using? HAve you considered putting
    select from the
    sequence in an explicit cursor, open it, fetch it
    and close it again?
    What Have in mind is something like this:

    declare
    cursor csr is
    select sai.nextval from dual;
    num integer :=0;
    ind integer :=10;
    begin
    while (ind>=0) loop
    open csr;
    fetch csr into num;
    close csr;
    dbms_output.put_line('Sai is:'||num);
    ind:=ind-1;
    end loop;
    end;
    /


    Here is the output:

    QL> /
    Sai is:13
    Sai is:14
    Sai is:15
    Sai is:16
    Sai is:17
    Sai is:18
    Sai is:19
    Sai is:20
    Sai is:21
    Sai is:22
    Sai is:23

    PL/SQL procedure successfully completed.
    On 12/09/2003 01:39:25 PM, Sai Selvaganesan wrote:
    hi
    here are the specs of my db:
    version 8.1.7 on windows,open_cursors=500
    i am facing a certain issue. we have a few sessions
    running out of cursor(ora-1000) and i used 1000 event
    to dump trace on these sessions. i found a certain
    statement
    select .nextval from dual;
    being repeated more than 350 times of the available
    500 cursors.
    the dev say nothing has changed nor has there been a
    increase in the load.

    can you please advise me whther this could be a issue
    or am i missing something here

    thanks
    sai
    --
    Please see the official ORACLE-L FAQ:
    http://www.orafaq.net
    --
    Author: Sai Selvaganesan
    INET: ssaisundar_at_sbcglobal.net

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and
    web hosting services

    To REMOVE yourself from this mailing list, send an
    E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be
    removed from). You may
    also send the HELP command for other information
    (like subscribing).
    Mladen Gogala
    Oracle DBA



    Note:
    This message is for the named person's use only. It
    may contain confidential, proprietary or legally
    privileged information. No confidentiality or
    privilege is waived or lost by any mistransmission.
    If you receive this message in error, please
    immediately delete it and all copies of it from your
    system, destroy any hard copies of it and notify the
    sender. You must not, directly or indirectly, use,
    disclose, distribute, print, or copy any part of
    this message if you are not the intended recipient.
    Wang Trading LLC and any of its subsidiaries each
    reserve the right to monitor all e-mail
    communications through its networks.
    Any views expressed in this message are those of the
    individual sender, except where the message states
    otherwise and the sender is authorized to state them
    to be the views of any such entity.

    --
    Please see the official ORACLE-L FAQ:
    http://www.orafaq.net
    --
    Author: Mladen Gogala
    INET: mladen_at_wangtrading.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    To REMOVE yourself from this mailing list, send an
    E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in
    the message BODY, include a line containing: UNSUB
    ORACLE-L
    (or the name of mailing list you want to be removed
    from). You may
    also send the HELP command for other information
    (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Sai Selvaganesan
    INET: ssaisundar_at_sbcglobal.net

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Sai Selvaganesan at Dec 9, 2003 at 10:59 pm
    thanks mladen.

    will give this a shot...again thanks a bunch

    sai
    --- Mladen Gogala wrote:
    What tool are you using? HAve you considered putting
    select from the
    sequence in an explicit cursor, open it, fetch it
    and close it again?
    What Have in mind is something like this:

    declare
    cursor csr is
    select sai.nextval from dual;
    num integer :=0;
    ind integer :=10;
    begin
    while (ind>=0) loop
    open csr;
    fetch csr into num;
    close csr;
    dbms_output.put_line('Sai is:'||num);
    ind:=ind-1;
    end loop;
    end;
    /


    Here is the output:

    QL> /
    Sai is:13
    Sai is:14
    Sai is:15
    Sai is:16
    Sai is:17
    Sai is:18
    Sai is:19
    Sai is:20
    Sai is:21
    Sai is:22
    Sai is:23

    PL/SQL procedure successfully completed.
    On 12/09/2003 01:39:25 PM, Sai Selvaganesan wrote:
    hi
    here are the specs of my db:
    version 8.1.7 on windows,open_cursors=500
    i am facing a certain issue. we have a few sessions
    running out of cursor(ora-1000) and i used 1000 event
    to dump trace on these sessions. i found a certain
    statement
    select .nextval from dual;
    being repeated more than 350 times of the available
    500 cursors.
    the dev say nothing has changed nor has there been a
    increase in the load.

    can you please advise me whther this could be a issue
    or am i missing something here

    thanks
    sai
    --
    Please see the official ORACLE-L FAQ:
    http://www.orafaq.net
    --
    Author: Sai Selvaganesan
    INET: ssaisundar_at_sbcglobal.net

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and
    web hosting services

    To REMOVE yourself from this mailing list, send an
    E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be
    removed from). You may
    also send the HELP command for other information
    (like subscribing).
    Mladen Gogala
    Oracle DBA



    Note:
    This message is for the named person's use only. It
    may contain confidential, proprietary or legally
    privileged information. No confidentiality or
    privilege is waived or lost by any mistransmission.
    If you receive this message in error, please
    immediately delete it and all copies of it from your
    system, destroy any hard copies of it and notify the
    sender. You must not, directly or indirectly, use,
    disclose, distribute, print, or copy any part of
    this message if you are not the intended recipient.
    Wang Trading LLC and any of its subsidiaries each
    reserve the right to monitor all e-mail
    communications through its networks.
    Any views expressed in this message are those of the
    individual sender, except where the message states
    otherwise and the sender is authorized to state them
    to be the views of any such entity.

    --
    Please see the official ORACLE-L FAQ:
    http://www.orafaq.net
    --
    Author: Mladen Gogala
    INET: mladen_at_wangtrading.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web
    hosting services
    To REMOVE yourself from this mailing list, send an
    E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of
    'ListGuru') and in
    the message BODY, include a line containing: UNSUB
    ORACLE-L
    (or the name of mailing list you want to be removed
    from). You may
    also send the HELP command for other information
    (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Sai Selvaganesan
    INET: ssaisundar_at_sbcglobal.net

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Jonathan Lewis at Dec 11, 2003 at 9:54 am
    That won't help, as the cursor would still
    be held open in the pl/sql cursor cache -
    despite the explicit close.

    It's also more efficient to use the implicit
    cursor in pl/sql for a single row fetch in
    the user's version of Oracle.

    Regards

    Jonathan Lewis
    http://www.jlcomp.demon.co.uk

    The educated person is not the person
    who can answer the questions, but the
    person who can question the answers -- T. Schick Jr

    One-day tutorials:
    http://www.jlcomp.demon.co.uk/tutorial.html

    Three-day seminar:
    see http://www.jlcomp.demon.co.uk/seminar.html
    ____UK___November

    The Co-operative Oracle Users' FAQ
    http://www.jlcomp.demon.co.uk/faq/ind_faq.html

    Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, December 09, 2003 7:39 PM
    What tool are you using? HAve you considered putting select from the
    sequence in an explicit cursor, open it, fetch it and close it again?
    What Have in mind is something like this:

    declare
    cursor csr is
    select sai.nextval from dual;
    num integer :=0;
    ind integer :=10;
    begin
    while (ind>=0) loop
    open csr;
    fetch csr into num;
    close csr;
    dbms_output.put_line('Sai is:'||num);
    ind:=ind-1;
    end loop;
    end;
    /


    Here is the output:

    QL> /
    Sai is:13
    Sai is:14
    Sai is:15
    Sai is:16
    Sai is:17
    Sai is:18
    Sai is:19
    Sai is:20
    Sai is:21
    Sai is:22
    Sai is:23

    PL/SQL procedure successfully completed.
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Jonathan Lewis
    INET: jonathan_at_jlcomp.demon.co.uk

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Mladen Gogala at Dec 11, 2003 at 2:09 pm
    It might be held in the cursor cache, it may even be
    held in session cursors cache but it will not be counted
    as an open cursor. My suggestion had diagnostic purpose only.
    The problem is, probably, with the tool which explicitly closes
    cursors too frequently and insufficiently sized shared pool
    which throws cursors out soon after they're closed.
    On 12/11/2003 04:54:25 AM, Jonathan Lewis wrote:

    That won't help, as the cursor would still
    be held open in the pl/sql cursor cache -
    despite the explicit close.

    It's also more efficient to use the implicit
    cursor in pl/sql for a single row fetch in
    the user's version of Oracle.


    Regards

    Jonathan Lewis
    http://www.jlcomp.demon.co.uk

    The educated person is not the person
    who can answer the questions, but the
    person who can question the answers -- T. Schick Jr


    One-day tutorials:
    http://www.jlcomp.demon.co.uk/tutorial.html


    Three-day seminar:
    see http://www.jlcomp.demon.co.uk/seminar.html
    ____UK___November


    The Co-operative Oracle Users' FAQ
    http://www.jlcomp.demon.co.uk/faq/ind_faq.html


    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Tuesday, December 09, 2003 7:39 PM

    What tool are you using? HAve you considered putting select from the
    sequence in an explicit cursor, open it, fetch it and close it again?
    What Have in mind is something like this:

    declare
    cursor csr is
    select sai.nextval from dual;
    num integer :=0;
    ind integer :=10;
    begin
    while (ind>=0) loop
    open csr;
    fetch csr into num;
    close csr;
    dbms_output.put_line('Sai is:'||num);
    ind:=ind-1;
    end loop;
    end;
    /


    Here is the output:

    QL> /
    Sai is:13
    Sai is:14
    Sai is:15
    Sai is:16
    Sai is:17
    Sai is:18
    Sai is:19
    Sai is:20
    Sai is:21
    Sai is:22
    Sai is:23

    PL/SQL procedure successfully completed.
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Jonathan Lewis
    INET: jonathan_at_jlcomp.demon.co.uk

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    Mladen Gogala
    Oracle DBA

    Note:
    This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks.
    Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Mladen Gogala
    INET: mladen_at_wangtrading.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Jonathan Lewis at Dec 11, 2003 at 3:34 pm
    Run your test case, and check the contents
    of v$open_cursor. Unless my memory has
    got it backwards,

    the pl/sql cursor cache is counted towards
    max_open_cursors, but the cursors that have
    been held open by the 'dirty tricks department'
    are closed as required if the limit is reached:
    (so should not be responsible for ORA-01000
    anyway).

    cursors held open as session_cache'd cursors
    are counted independently of max_open_cursors -
    so should not cause an ORA-01000

    Regards

    Jonathan Lewis
    http://www.jlcomp.demon.co.uk

    The educated person is not the person
    who can answer the questions, but the
    person who can question the answers -- T. Schick Jr

    One-day tutorials:
    http://www.jlcomp.demon.co.uk/tutorial.html

    Three-day seminar:
    see http://www.jlcomp.demon.co.uk/seminar.html
    ____UK___November

    The Co-operative Oracle Users' FAQ
    http://www.jlcomp.demon.co.uk/faq/ind_faq.html

    Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Thursday, December 11, 2003 2:09 PM
    It might be held in the cursor cache, it may even be
    held in session cursors cache but it will not be counted
    as an open cursor. My suggestion had diagnostic purpose only.
    The problem is, probably, with the tool which explicitly closes
    cursors too frequently and insufficiently sized shared pool
    which throws cursors out soon after they're closed.
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Jonathan Lewis
    INET: jonathan_at_jlcomp.demon.co.uk

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 9, '03 at 6:39p
activeDec 11, '03 at 3:34p
posts7
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase