Home » RDBMS Server » Performance Tuning » Statspack
Statspack [message #205292] Fri, 24 November 2006 03:19 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Statspack [message #205630 is a reply to message #205361] Sun, 26 November 2006 22:24 Go to previous message
seema.taunk
Messages: 96
Registered: October 2006
Member
Hi friends,

Thanx a lot.....


Previous Topic: Any Recommendation for Statspack Reprot
Next Topic: Query Tuning (Performance)
Goto Forum:
  


Current Time: Tue Dec 06 13:54:27 CST 2016

Total time taken to generate the page: 0.05377 seconds