| Statspack [message #205292] |
Fri, 24 November 2006 03:19  |
seema.taunk
Messages: 96
Registered: October 2006
|
Member |
|
|
Hi friends,
I want ur expert advice on statspack report attached herewith.
I think problem is with sql statements not using bind variables.
Please suggest changes if any.
|
|
|
|
| Re: Statspack [message #205301 is a reply to message #205292] |
Fri, 24 November 2006 03:42  |
bwetkstr
Messages: 114
Registered: August 2005
|
Senior Member |
|
|
Hi,
I'm still a noob in tuning an Oracle database, but if I'm not mistaken your Library cache Hit ratio and your soft parse ratio is a little low no?
This normally indicates that that there is a parsing problem.
Kr
Karel.
|
|
|
|
| Re: Statspack [message #205361 is a reply to message #205301] |
Fri, 24 November 2006 06:08 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
This query here does an unholy amount of IO (2.5 million buffer gets a time, and it's being run once every 5 minutes)
SELECT call_req.open_date, call_req.id FROM call_req, ctct, loc,
site, z_zo, z_lho WHERE ( call_req.customer = ctct.id AND ctct
.c_l_id = loc.id AND loc.l_si_id = site.id AND site.z_si_zo_id
= z_zo.id AND z_zo.zo_lho_id = z_lho.id AND z_lho.lho_name L
IKE '%03972%' ) AND ( ( call_req.group_id != 14379066 ) and ( c
This query here looks a lot like it needs to be rewritten to use bind variables:
SELECT call_req.open_date, call_req.ref_num, call_req.template_n
ame, call_req.id FROM call_req WHERE ( call_req.problem = 'cr:31
906461' ) AND ( call_req.group_id IN ( SELECT id FROM ctct WHERE
id = 682960 OR id = 682961 OR id = 682962 OR id = 682953 OR id
= 695266 OR id = 695267 OR id = 695344 OR id = 695260 OR id = 69
About 50% of your parses are hard parses, which will cause a problem.
From a cursory inspection, I'd say you were right about the causes of any performance issue you're seeing.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
