memory resize

[ams-jamali]

hi all,

(version oracle- 9.2)
I have to increase my pga_aggregate_value (if mutipass count is not zero),my db is running in pfile.

output of PGA_AGG_VALUE;

PROFILE COUNT PERCENT
---------------------------------------- ---------- ----------
workarea executions - optimal 42855515 100
workarea executions - onepass 407 0
workarea executions - multipass 4 0


NAME VALUE
---------------------------------------------------------------- ----------
aggregate PGA target parameter 209715200
aggregate PGA auto target 161344512
global memory bound 10485760
total PGA inuse 31879168
total PGA allocated 96164864
maximum PGA allocated 167884800
total freeable PGA memory 29753344
PGA memory freed back to OS 1.2168E+11
total PGA used for auto workareas 1436672
maximum PGA used for auto workareas 28165120
total PGA used for manual workareas 0

NAME VALUE
---------------------------------------------------------------- ----------
maximum PGA used for manual workareas 529408
over allocation count 0
bytes processed 3.4197E+11
extra bytes read/written 5.3596E+10
cache hit percentage 86.45

** currently we are doing purging process.cache hit already 97% now with in weeeks it came to 86%.

i have used in testDB.
alter system set pga_aggregate_size=32m scope=both;
but it shows me error like
ora-02065 illegal option for Alter System.

but i have used this command already in another productionDB it worked smoothly.

So, now what i want to know is will it works in DB that uses pfile,but size is enough i think so since it has 200m(without bouncing).

Thanks

[pareshjavkar]

hi all,

(version oracle- 9.2)
I have to increase my pga_aggregate_value,my db is running in pfile.

output of PGA_AGG_VALUE;

PROFILE COUNT PERCENT
---------------------------------------- ---------- ----------
workarea executions - optimal 42855515 100
workarea executions - onepass 407 0
workarea executions - multipass 4 0


NAME VALUE
---------------------------------------------------------------- ----------
aggregate PGA target parameter 209715200
aggregate PGA auto target 161344512
global memory bound 10485760
total PGA inuse 31879168
total PGA allocated 96164864
maximum PGA allocated 167884800
total freeable PGA memory 29753344
PGA memory freed back to OS 1.2168E+11
total PGA used for auto workareas 1436672
maximum PGA used for auto workareas 28165120
total PGA used for manual workareas 0

NAME VALUE
---------------------------------------------------------------- ----------
maximum PGA used for manual workareas 529408
over allocation count 0
bytes processed 3.4197E+11
extra bytes read/written 5.3596E+10
cache hit percentage 86.45

** currently we are doing purging process.cache hit already 97% now with in weeeks it came to 86%.
can i increase the size without bouncing my database.

i have used in testDB.
alter system set pga_aggregate_size=32m scope=both;
but it shows me error like
ora-02065 illegal option for Alter System.

but i have used this command already in another productionDB it worked smoothly.

So, now what i want to know is will it works in DB that uses pfile(without bouncing).

Thanks

SQL> alter system set pga_aggregate_target=64M;

[ams-jamali]

parvesh already am using 200m traget value.

1.will it works without rebouncing.

2.I have to check some other area.

[ams-jamali]

thanks parvesh for stating about that target,
instead of target i had given size..,
but
whether i have to concentrate on other area please state that.

[pareshjavkar]

thanks parvesh for stating about that target,
instead of target i had given size..,
but
whether i have to concentrate on other area please state that.

You just set the TARGET, it will automatically used as and when required/.

[ams-jamali]

Thanks paresh.,

After increasing the size,if i check Multipass will it comes to zero, immediately.,

Bore do u have any suggestion regarding this..,
Expecting your reply.,

[ams-jamali]

here is the advice.,

TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
25 73 8045
50 84 0
100 89 0
150 89 0
200 91 0
240 91 0
280 91 0
320 91 0
360 96 0
400 96 0
600 96 0
800 96 0
1200 97 0
1600 98 0

14 rows selected.

very low hit percentage may be due to purging process(i think so).
Any advice please..,

[malay_biswal]

It all depends on the amount of data being processed for sorting and the PGA_AGGREGATE_TARGET size. If after increasing this value your opeartion still needs disk, but it's completed in one pass the you will not need the multi pass.

[tamilselvan]

(version oracle- 9.2)
I have to increase my pga_aggregate_value (if mutipass count is not zero),my db is running in pfile.

output of PGA_AGG_VALUE;

PROFILE COUNT PERCENT
---------------------------------------- ---------- ----------
workarea executions - optimal 42855515 100
workarea executions - onepass 407 0
workarea executions - multipass 4 0

The total number of onepass + multipass is very low compared with optimal.
I do not think you need to increase PGA size.

[Bore]

Saw u r expecting my answer, but I have no much to say here :-) I do not feel such an expert exactly in PGA tuning.
Seems to me Tamil is right, it's hard to make multipass to 0,but as long as they are really low compared to optimal one and u do not suffer performance problems, just be happy

Regards
Boris