Re: High elapsed/cpu ratio

ebwright_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?

I personally prefer to drop user perfstat cascade because the information is almost useless.
Unless you are completely sure, nothing else ran in the time between the two statspack snapshots, you may see only indications of normal work in the database.
The information you are looking for is in the tracefile of the single statement to tune.

Jan Received on Fri Sep 14 2007 - 01:01:09 CDT