Home » RDBMS Server » Performance Tuning » Soft Close Cursor (9i and 10g)
Soft Close Cursor [message #327808] Tue, 17 June 2008 13:51 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi,
I'm currently reading a book that tells about soft-closed cursor, i tried checking my recently executed select statement in the v$open_cursor. Tried some of this statements:


select 'openA' from dual;

SELECT sql_id,
       sql_text,
       parse_calls,
       executions
FROM v$sql
WHERE INSTR(UPPER(sql_text),'OPENA')  > 0
  AND INSTR(UPPER(sql_text),'SQL_TEXT') = 0
  AND command_type = 3;

select sql_text
from v$open_cursor
where 1=1 
  --and sql_id = 'ajut3t2v0m7sc'
  and sql_text like '%openA%';



Still cant find my recent query on that cursor. Please advise if there's anything I miss or did not understand in the steps.

Also related to that soft-close cursor, I tried setting my open_cursors parameter to lower like 5 or 10, then issued a sequence of impicit cursors inside a block, but it did not return the error maximum cursors allowed. The code is like this:


declare
  v_t varchar2(10);
  cursor a is
  select 'a' from dual; 
  cursor b is
  select 'b' from dual; 
beg
 open a; 
 fetch a into v_t;
 --not closing it
 open b; 
 fetch b into v_t;
 ...and so  on....
end;



Thank you so much for your help.

Regards,
Rhani

[Updated on: Tue, 17 June 2008 13:54]

Report message to a moderator

Re: Soft Close Cursor [message #328209 is a reply to message #327808] Thu, 19 June 2008 06:43 Go to previous messageGo to next message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
Hi,

v$open_cursor view lists cursors that each user session
currently has opened and parsed (from Applications???)

Regards,

MSMallya
Re: Soft Close Cursor [message #328598 is a reply to message #328209] Fri, 20 June 2008 11:02 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi,
Thank you very much for the reply. If that's the case, which parameter really does affect the maximum cursors allowed to get that ora-error. Thanks.

Regards,
Rhani
Re: Soft Close Cursor [message #329017 is a reply to message #327808] Mon, 23 June 2008 13:50 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I have heard of soft parse, but not soft close. I guess there could be such a thing. Can you tell us what soft close means?

Thanks, Kevin
Previous Topic: Explain Plan
Next Topic: Capturing top 10 longest running SQLs
Goto Forum:
  


Current Time: Tue Nov 26 17:30:08 CST 2024