Re: High elapsed/cpu ratio

On Sep 12, 11:26 am, ebwri..._at_gmail.com wrote:
> 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

You might want to start here with the investigation of the db file sequential read wait events:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm#sthref945

Other than the article that you reference, I don't believe that I have ever seen a suggestion to increase the pga_aggregate_target in order to reduce the frequency of db file sequential read wait events. Any idea who the author of the article might be and the date of publication?

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed Sep 12 2007 - 20:34:04 CDT