Re: High elapsed/cpu ratio

From: <ebwright_at_gmail.com>
Date: Wed, 12 Sep 2007 08:26:09 -0700
Message-ID: <1189610769.667770.203780@r34g2000hsd.googlegroups.com>

We ran statspack against this program last night. Below is top 5 timed events section:

Event                                               Waits    Time (s)

% Total Ela Time

-------------------------------------------- ------------ -----------
---------------
db file sequential read                         6,446,038

2,856 43.21
CPU time
1,765 26.71

PL/SQL lock timer                                     180
1,756    26.57
db file scattered read                             58,205
116     1.75
log file parallel write                            13,434
53      .80

http://www.akadia.com/services/ora_statspack_survival_guide.html indicates that large numbers of db file sequential read could be addressed by tuning pga_aggregate_target. This system has 32 GB, with sga_max_size = 4 GB and pga_aggregate_target = 2GB. Would increasing pga_aggregate_target be advisable? If so, to what value? What about the hidden _pga_max_size (200MB) parameter? Wouldn't this still be a limiting factor how much PGA is available to this session?

Thanks in advance,
Eric Received on Wed Sep 12 2007 - 10:26:09 CDT