-
Notifications
You must be signed in to change notification settings - Fork 4
/
Copy pathcursor.sql
135 lines (116 loc) · 3.8 KB
/
cursor.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
--==============================================================================
-- GPI - Gunther Pippèrr
-- Desc: Informations about cursor usage in the database
-- Date: 08.2013
--==============================================================================
set linesize 130 pagesize 300
column user_name format a25
ttitle left "Open Cursor used summary" skip 2
select inst_id
, user_name
, count(*)
from gv$open_cursor
where user_name is not null -- and user_name not in ( 'SYS' )
group by rollup (inst_id,user_name)
/
ttitle left "Open Cursor used by session" skip 2
select inst_id
, sid
, user_name
, count(*)
from gv$open_cursor
where user_name is not null -- and user_name not in ( 'SYS' )
group by inst_id,user_name,sid
order by 4,1,3
/
column name format a30 heading "Statistic|Name"
column value format 999G999G999G999 heading "Statistic|value"
ttitle left "Open Cursor used by session over the statistic" skip 2
select a.value
, s.username
, s.sid
, s.serial#
from v$sesstat a
, v$statname b
, v$session s
where a.statistic# = b.statistic#
and s.sid=a.sid
and b.name = 'opened cursors current'
and s.username is not null
/
ttitle left "Open Cursor Statistic " skip 2
column execute_count format 999G999G999G999G999 heading "SQL Execution"
column parse_count format 999G999G999G999G999 heading "Parse Count"
column cursor_hits format 999G999G999G999G999 heading "Cursor Hits"
column hit_percentage_parse format 99D990 heading "Parse| % Total"
column hit_percentage_cursor format 99D990 heading "Cursor Cache | % Total"
select inst_id
, execute_count
, parse_count
, round(parse_count/(execute_count/100),3) as hit_percentage_parse
, cursor_hits
, round(cursor_hits/(execute_count/100),3) as hit_percentage_cursor
from ( select name
, value
, inst_id
from gv$sysstat
where name in ('session cursor cache hits','parse count (total)','execute count')
)
pivot (
max (value)
FOR name
IN ( 'session cursor cache hits' AS cursor_hits
, 'parse count (total)' as parse_count
, 'execute count' as execute_count
)
)
/
prompt ... 11g Syntax if you hid an error on 10g!
prompt ... if Cursor Cache % Total is a relatively low percentage
prompt ... you should increate the DB Parameter session_cached_cursors
ttitle left "Cursor Settings init.ora " skip 2
show parameter cursor
ttitle left "Session cached Cursor Usage " skip 2
--
-- see also
-- SCRIPT - to Set the 'SESSION_CACHED_CURSORS' and 'OPEN_CURSORS' Parameters Based on Usage (Doc ID 208857.1)
--
select a.inst_id
, 'session_cached_cursors' parameter
, lpad(value, 5) value
, decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage
from
( select max(s.value) used , inst_id
from v$statname n
, gv$sesstat s
where n.name = 'session cursor cache count' and s.statistic# = n.statistic#
group by inst_id
) a,
( select value,inst_id
from gv$parameter
where name = 'session_cached_cursors'
) b
where a.inst_id=b.inst_id
union all
select c.inst_id
, 'open_cursors'
, lpad(value, 5)
, to_char(100 * used / value, '990') || '%'
from
( select s.inst_id , max((s.value)) used
from v$statname n
, gv$sesstat s
where n.name in ('opened cursors current')
and s.statistic# = n.statistic#
group by s.inst_id
) c,
( select value,inst_id
from gv$parameter
where name = 'open_cursors'
) d
where c.inst_id=d.inst_id
order by 1,2
/
ttitle off
prompt ... if usage percentage is a near 100%
prompt ... you should increate the DB Parameter session_cached_cursors