Topic: Performance & Tuning >> hard parses
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| vhansa |
Posted: Dec 26, 2002 12:45:00 PM |
Total Post: 2
Joined: Jan, 2003
|
Hi All,
We have a lot of hard parses going on in our system.
parse count (hard) 3612511
parse count (total) 58418025
Can we have any sql which can Identify the sqls which are doing hard
parses.
Thanks,
Rahul
|
|
|
 tonny_2003 |
| Posted: Dec 26, 2002 01:00:00 PM |  |
|
Total Post: 6
Joined: Jan, 2003
|
Are you sure you have problem?
Looks like soft parse are 93%.
Thanks,
Vimal
|
|
|
|
|
| William_Jones |
| Posted: Dec 26, 2002 02:01:00 PM | |
|
Total Post: 2
Joined: Jan, 2003
|
Run this Query and check if U have SESSION_CURSORS_CACHED set up in your
init.ora
----------------------------CUT-----------------CUT--------------------
column parameter format a29
column value format a5
column usage format a5
select
'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
from
sys.v_$statname n,
sys.v_$sesstat s
where
n.name = 'session cursor cache count' and
s.statistic# = n.statistic#
),
( select
value
from
sys.v_$parameter
where
name = 'session_cached_cursors'
)
union all
select
'open_cursors',
lpad(value, 5),
to_char(100 * used / value, '990') || '%'
from
( select
max(sum(s.value)) used
from
sys.v_$statname n,
sys.v_$sesstat s
where
n.name in ('opened cursors current', 'session cursor cache count') and
s.statistic# = n.statistic#
group by
s.sid
),
( select
value
from
sys.v_$parameter
where
name = 'open_cursors'
)
/
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 814 |
| Mohammed Taj | 704 |
| Jayanta Sur | 479 |
| positive fanatic | 416 |
| Vigyan Kaushik | 386 |
| Gitesh Trivedi | 322 |
| Gopu Gopi | 242 |
| neeraj sharma | 231 |
| Ramesh Jois | 226 |
| Bishal Khetan | 196 |
|
|
