Too Many Cursors?

[Helge Moulding]

I originally wrote this query to just run off the tables directly:

select a.val1
,b.val2
,b.val3
from a_table a
join b_table b on a.key = b.a_key
where a.val2 = parameter
and 0 = (
select count(*)
from b_table b2
where b.a_key = b2.a_key
and b2.val4 > b.val4 )

The trouble was that b_table is much much larger than a_table (and
the join is actually more complex and involves another table). It
ran much too slowly, so I thought to rewrite the entire mess as
follows:

create or replace function get_b_vals( in_a_key varchar2)
return refcur
is
rc refcur;
begin
open rc for
select b.val2
,b.val3
from b_table b
where b.a_key = in_a_key
and 0 = (
select count(*)
from b_key b2
where b2.a_key = in_a_key
and b2.val4 > b.val4 );
return rc;
end;
/
select a.val1
,get_b_vals(a.key)
from a_table a
where a.val2 = parameter;
/

It seemed to compile fine, but when I ran it I got a "too many cursors
open" error. Why is that? Can I safely close the cursor before
returning,
even though I'm returning the cursor?
--
Helge Moulding
hmoulding at gmail dot com Just another guy
http://hmoulding.cjb.net/ with a weird name

[Yuri Ivanov]

Helge Moulding пишет: I originally wrote this query to just run off the tables directly: select a.val1 ,b.val2 ,b.val3 from a_table a join b_table b on a.key = b.a_key where a.val2 = parameter and 0 = ( select count(*) from b_table b2 where b.a_key = b2.a_key and b2.val4 > b.val4 ) The trouble was that b_table is much much larger than a_table (and the join is actually more complex and involves another table). It ran much too slowly, so I thought to rewrite the entire mess as follows: create or replace function get_b_vals( in_a_key varchar2) return refcur is rc refcur; begin open rc for select b.val2 ,b.val3 from b_table b where b.a_key = in_a_key and 0 = ( select count(*) from b_key b2 where b2.a_key = in_a_key and b2.val4 > b.val4 ); return rc; end; / select a.val1 ,get_b_vals(a.key) from a_table a where a.val2 = parameter; / It seemed to compile fine, but when I ran it I got a "too many cursors open" error. Why is that? Can I safely close the cursor before returning, even though I'm returning the cursor? -- Helge Moulding hmoulding at gmail dot com Just another guy http://hmoulding.cjb.net/ with a weird name
May be you can increase OPEN_CURSORS parameter in your instance?


OPEN_CURSORS specifies the maximum number of open cursors (handles to
private

SQL areas) a session can have at once. You can use this parameter to
prevent
a

session from opening an excessive number of cursors. This parameter also

constrains the size of the PL/SQL cursor cache which PL/SQL uses to avoid
having

to reparse as statements are reexecuted by a user.

It is important to set the value of OPEN_CURSORS high enough to prevent your

application from running out of open cursors. The number will vary from one

application to another. Assuming that a session does not open the number of

cursors specified by OPEN_CURSORS, there is no added overhead to setting
this

value higher than actually needed.

Parameter type Integer

[Helge Moulding]

Yuri Ivanov wrote: May be you can increase OPEN_CURSORS parameter in your instance?

Thanks, Yuri. I did consider that. The trouble with that, of course,
is that it assumes that the function I wrote opens only a normal
number of cursors. It looks to me that there's a problem with that
assumption.
--
Helge Moulding
hmoulding at gmail dot com Just another guy
http://hmoulding.cjb.net/ with a weird name

[Yuri Ivanov]

Helge Moulding пишет: Yuri Ivanov wrote:May be you can increase OPEN_CURSORS parameter in your instance? Thanks, Yuri. I did consider that. The trouble with that, of course, is that it assumes that the function I wrote opens only a normal number of cursors. It looks to me that there's a problem with that assumption. -- Helge Moulding hmoulding at gmail dot com Just another guy http://hmoulding.cjb.net/ with a weird name
Now I catch what you want.
But IMHO, better to optimize your first query, than use any user-defined
function in the query.

Please, set timing ON
and show me a time for your both queryes and for one my :)

select a.val1
,b.val2
,b.val3
from a_table a, b_table b
where a.val2 = 'a'
and a.key = b.a_key
and NOT EXISTS (select *
from b_table b2
where b.a_key = b2.a_key
and b2.val4 > b.val4 );

Please :)
And talk me version of your RDBMS and information about indexes :)

[bernhard mutschlechner]

Helge Moulding schrieb: I originally wrote this query to just run off the tables directly: select a.val1 ,b.val2 ,b.val3 from a_table a join b_table b on a.key = b.a_key where a.val2 = parameter and 0 = ( select count(*) from b_table b2 where b.a_key = b2.a_key and b2.val4 > b.val4 ) The trouble was that b_table is much much larger than a_table (and the join is actually more complex and involves another table). It ran much too slowly, so I thought to rewrite the entire mess as follows: create or replace function get_b_vals( in_a_key varchar2) return refcur is rc refcur; begin open rc for select b.val2 ,b.val3 from b_table b where b.a_key = in_a_key and 0 = ( select count(*) from b_key b2 where b2.a_key = in_a_key and b2.val4 > b.val4 ); return rc; end; / select a.val1 ,get_b_vals(a.key) from a_table a where a.val2 = parameter; / It seemed to compile fine, but when I ran it I got a "too many cursors open" error. Why is that? Can I safely close the cursor before returning, even though I'm returning the cursor? -- Helge Moulding hmoulding at gmail dot com Just another guy http://hmoulding.cjb.net/ with a weird name
Helge,

As I understand, you are looking for the greatest/last row - concernig
val4 - of table b_table.
What about using a group function (ORACLE Optimzier does a good job
usually).

-- assuming, that val4 makes rows unique whithin a_key
select b.val2, b.val3
from b_table b
where (b.a_key, b.val4) in (
select b2.a_key, max( b2.val4)
from b_table b2
where b2.a_key = in_a_key
group by b2.a_key)

Increasing parameter open_cursor should not be necassary in most cases,
often it points to a programming error, e.g. cursors are not closed
after using, or a statement is not recognized as identical by the
optimizer (this seems to be the problem in your example - I use toad to
inspect open cursors for a session, of course there are Dictionary
tables too).

Hope that helps,

Bernhard

[Helge Moulding]

bernhard mutschlechner wrote: What about using a group function (ORACLE Optimzier does a good job usually). -- assuming, that val4 makes rows unique whithin a_key select b.val2, b.val3 from b_table b where (b.a_key, b.val4) in ( select b2.a_key, max( b2.val4) from b_table b2 where b2.a_key = in_a_key group by b2.a_key)

I've never tried that method before. I've used 0 = (select count
of greater rows) and not exists (select greater row). It seems to
me that not exists should be a little faster, since it only has
to check the table for a single row that beats the current value.
The other two methods have to find all rows, whether or not they
beat the current value. The difference in timing might be miniscule
if the table doesn't have a lot of entries for any given key.

At least that's the way I see that.

I ended up solving my problem by using a temporary table. That
gets me the right answer, and does so quickly enough.
--
Helge Moulding
hmoulding at gmail dot com Just another guy
http://hmoulding.cjb.net/ with a weird name

[Frank van Bortel]

Helge Moulding wrote:
[snip] create or replace function get_b_vals( in_a_key varchar2) return refcur is rc refcur; begin open rc for select b.val2 ,b.val3 from b_table b where b.a_key = in_a_key and 0 = ( select count(*) from b_key b2 where b2.a_key = in_a_key and b2.val4 > b.val4 ); return rc; end; / select a.val1 ,get_b_vals(a.key) from a_table a where a.val2 = parameter; / It seemed to compile fine, but when I ran it I got a "too many cursors open" error. Why is that? Can I safely close the cursor before returning, even though I'm returning the cursor? -- Helge Moulding hmoulding at gmail dot com Just another guy http://hmoulding.cjb.net/ with a weird name
For every a.val2, you call your cursor.
And no - you'll have to keep the cursor open until
the results are displayed. Nothing else to do than
up your open_cursors parameter (or recode...)

In general, you can query (using %ISOPEN) whether you
already opened this cursor. If so, this session has already
used this cursor - generally speaking the results have been
displayed, and you can now safely close the cursor, and open
for a new query.
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...