| From: | Steve Adams |
| Date: | 26-Apr-2001 09:54 |
| Subject: | Reducing parsing with session_cached_cursors |
|
|
There are still a lot of soft parses there, so either the session cursor cache is smaller than your working set of reused sharable SQL, or your sessions are short-lived and do not reuse much SQL, or you have a very diverse range of sharable SQL. Try this query to get an indication of the maximum number of cacheable cursors that might be in use in any one session. If memory is abundant, you can just set session_cached_cursors to this value.
select
max(count(*)) max_cacheable_cursors
from
( select
p.kglobt18 schema# -- parsing schema number
from
sys.x$kglcursor p
where
p.kglobt12 > 2 -- enough parse_calls
union all
select
s.kglntsnm schema# -- authorized schema number
from
sys.x$kglcursor c,
sys.x$kglsn s
where
c.kglobt12 > 2 and
s.kglhdadr = c.kglhdadr
)
group by
schema#
/
I'll add this query to the 8.0 and 8.1 version of session_cursor_cache.sql shortly.
|
![]() |
The parsing in our production database has gotten extremely better but still remains high. I have gone as high as 100 for the session_cached_cursors parameter. I am not sure it will be beneficial to go any higher, so I set the value back to 50 since the results at 100 was not much better. Here is the stats at the value of 50 using the session_cursor_cache.sql script from your site. PARAMETER VALUE USAGE ----------------------------- ----- ----- session_cached_cursors 50 100% open_cursors 1200 4% CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES ----------------- ----------- ----------- 13.27% 73.16% 13.57%Any suggestions?
|