Joel,
You are too kind.. (if only my CTO saw things that way ;-)
this thread made me curious if that param still exists
in 9i and works, and it does.
This plsql block below confirms the "commit closing cursors"
theory, and your statement about opening new cursors even
if statements are the same:
--------------------------------------------------
SQL> declare
2
3 x number;
4 cnt number;
5
6 procedure open25Curs
7 is
8 x number;
9 begin
10 select 1 into x from dual;
11 select 1 into x from dual;
12 select 1 into x from dual;
13 select 1 into x from dual;
14 select 1 into x from dual;
15 select 2 into x from dual;
16 select 2 into x from dual;
17 select 2 into x from dual;
18 select 2 into x from dual;
19 select 2 into x from dual;
20 select 3 into x from dual;
21 select 3 into x from dual;
22 select 3 into x from dual;
23 select 3 into x from dual;
24 select 3 into x from dual;
25 select 4 into x from dual;
26 select 4 into x from dual;
27 select 4 into x from dual;
28 select 4 into x from dual;
29 select 4 into x from dual;
30 select 5 into x from dual;
31 select 5 into x from dual;
32 select 5 into x from dual;
33 select 5 into x from dual;
34 select 5 into x from dual;
35 end open25Curs;
36
37 function get_openCurs
38 return number
39 is
40 cnt number := 0;
41 begin
42 select a.value into cnt
43 from v$statname b, v$mystat a
44 where a.statistic# = b.statistic#
45 and b.name = 'opened cursors current';
46 return cnt;
47 end get_openCurs;
48
49 procedure show_openCurs (p_cnt number)
50 is
51 begin
52 dbms_output.put_line(chr(9)||'open cursors: '||p_cnt);
53 end show_openCurs;
54
55 begin
56
57 dbms_output.put_line('start point');
58
59 cnt := get_openCurs();
60 show_openCurs(cnt);
61
62 execute immediate 'alter session set
"_close_cached_open_cursors" = true';
63
64 dbms_output.put_line('param changed to true');
65
66 open25Curs();
67
68 dbms_output.put_line('before commit');
69 cnt := get_openCurs();
70 show_openCurs(cnt);
71
72 commit;
73
74 dbms_output.put_line('after commit');
75 cnt := get_openCurs();
76 show_openCurs(cnt);
77
78 execute immediate 'alter session set
"_close_cached_open_cursors" = false';
79
80 dbms_output.put_line('param changed to false');
81
82 cnt := get_openCurs();
83 show_openCurs(cnt);
84 open25Curs();
85
86 dbms_output.put_line('before commit');
87 cnt := get_openCurs();
88 show_openCurs(cnt);
89
90 commit;
91
92 dbms_output.put_line('after commit');
93 cnt := get_openCurs();
94 show_openCurs(cnt);
95
96 execute immediate 'alter session set
"_close_cached_open_cursors" = true';
97
98 dbms_output.put_line('param changed to true');
99
100 commit;
101
102 dbms_output.put_line('after commit');
103 cnt := get_openCurs();
104 show_openCurs(cnt);
105
106 end;
107 /
start point
open cursors: 4
param changed to true
before commit
open cursors: 29
after commit
open cursors: 5
param changed to false
open cursors: 5
before commit
open cursors: 30
after commit
open cursors: 31
param changed to true
after commit
open cursors: 5
PL/SQL procedure successfully completed.
Notice that despite the identical sql, oracle still
Opened new cursors (as joel said) and the param
when set to true does infact cause cursors to be closed
on commit.
Wow, this was really geeky.
Speaking of geeky, did anyone see highlights
From the spelling bee contest?
Some kid quoted a line from napoleon dynamite.
One of the judges asked him to spell a word
(I believe it was "Chinook") and he asked for
a definition then immediately replied,
"do the chickens have large talons?"
hehe, I though that was funny...
yes, it is Friday.
- a
-----Original Message-----
From: Patterson Joel
[mailto:oracledba-ezmlmshield-x54819131.[Email address protected]
Sent: Friday, June 03, 2005 9:14 AM
To: LazyDBA Discussion
Subject: RE: Closing Cursors
Anthony as usual is right, but since I already wrote this once for
someone else I thought it might be useful to append it here.
(How many cursors do you 'really' have open):
When an application explicitly does a CLOSE, PL/SQL marks the cursor as
closed but does not close it on ORACLE side. This is to reuse the cursor
on re-execution of the statement. It's not aged out of memory. It will
be reused. A session opens cursors and closes them, but they remain
cached. If another cursor is needed, oracle doesn't check to see if one
is already open and cached, it simply opens a new one. This is done
until the limit specified by open_cursors is reached. When that limit is
reached, an identical query that requires a cursor will check for one
that is open and cached, and will reuse it.
The server caches cursors opened by the PL/SQL engine. Each time a
cursor is closed, it is really moved to a Least Recently Used (LRU) list
of open cursors and left open. This is done as a performance
improvement by saving a 'round_trip' from Client to Server each time a
cursor is opened. Note that no optimization occurs. In other words,
the cursor caching code does not check to see if a particular query
already has a cursor open; it simply creates another one. This is done
until OPEN_CURSORS is reached. If you have OPEN_CURSORS cached as open,
however, then if an identical query occurs, the server reuses the cached
cursor.
For performance reasons, pl/sql cursors are cached for future reuse, so
once you use them up they will be added to LRU list of cursor table.
Hence you continue to see them in v$open_cursor, although they have been
closed. But once you reach max_open_cursor limit ,these "used up"
cursor in LRU list will close and a new requested cursor will be opened.
Use the following query to find the 'really' open cursors.
declare
me table_types.tvc20;
begin
get_current_appversion(me);
dbms_output.put_line(me(1));
end;
/
select * from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and a.sid = &v_sid;
-----Original Message-----
From: Anthony Molinaro
[mailto:oracledba-ezmlmshield-x85755161.[Email address protected]
Sent: Thursday, June 02, 2005 8:42 AM
To: LazyDBA Discussion
Subject: RE: Closing Cursors
Rahul,
It's an optimization to keep them open, for possible reuse
(oracle does not do this by accident).
In anycase, in older versions of oracle, there
was an hidden param called _close_cached_open_cursors (I think)
and if set to true in your session, open cursors
would be closed after you commit (even implicit selects).
I don't know if this still holds true in 9i,
but was true in 8i and 8, never tested on 7.
You can easily test by checking open cursors current from
v$mystat in your session run some sql then commit then
check open cursors again.
If it doesn't change, then the cursors closing behavior
isn't effected by this hidden param (or maybe it's for
Explicit cursors now).
Might also be worth checking v$sysstat not only vb$mystat.
Good luck,
Anthony
-----Original Message-----
From: Rahul Mehta
[mailto:oracledba-ezmlmshield-x6149836.[Email address protected]
Sent: Thursday, June 02, 2005 8:08 AM
To: LazyDBA Discussion
Subject: Closing Cursors
Can implicit cursors opened by SELECT,INSERT etc on Oracle 9.2.0.1 be
closed without closing the connection to database .These cursors remain
open even after the Stored procedure from which the SELECT,INSERT,UPDATE
and othe DML statements are executed.
Does this require tuning the database.
Please reply at the earliest.
Its Urgent.
Regards
RAHUL
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page