Re: Need advice on ORA-04030 and pga_aggregate_target parameter

On May 14, 9:23 pm, harvinde..._at_gmail.com wrote:
> Hi,
>
> Users are getting following errors on 1 of the database machine:
> "Oracle.DataAccess.Client.OracleException ORA-04030: out of process
> memory when trying to allocate 16396 bytes (koh-kghu call ,pmuccst:
> adt/record)"
>
> Configuration: Oracle 10.2.0.1 on RHAT Linux 4, 4GB RAM, SGA-1.2GB,
> PGA -600MB, 10 Shared servers
>
> Following is the info from the dynamic views:
>
> SELECT * FROM V$PGASTAT;
>
> NAME VALUE UNIT
> aggregate PGA target parameter 629145600 bytes
> aggregate PGA auto target 509561856 bytes
> global memory bound 104857600 bytes
> total PGA inuse 63959040 bytes
> total PGA allocated 119723008 bytes
> maximum PGA allocated 3512438784 bytes
> total freeable PGA memory 14090240 bytes
> process count 26
> max processes count 33
> PGA memory freed back to OS 1027295150080 bytes
> total PGA used for auto workareas 1264640 bytes
> maximum PGA used for auto workareas 13465600 bytes
> total PGA used for manual workareas 0 bytes
> maximum PGA used for manual workareas 536576 bytes
> over allocation count 2173
> bytes processed 400043052032 bytes
> extra bytes read/written 0 bytes
> cache hit percentage 100 percent
> recompute count (total) 491550
>
> select
> max(pga_used_mem) max_pga_used_mem
> , max(pga_alloc_mem) max_pga_alloc_mem
> , max(pga_max_mem) max_pga_max_mem
> from v$process
> /
> 19164289 40582953 3386319473
>
> SELECT PROGRAM, PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM,
> PGA_MAX_MEM
> FROM V$PROCESS;
> PROGRAM PGA_USED_MEM PGA_ALLOC_MEM
> PGA_FREEABLE_MEM PGA_MAX_MEM
>
> PSEUDO 0 0 0 0
> oracle_at_qalin1 (PMON) 213725 366221 0 366221
> oracle_at_qalin1 (PSP0) 212937 366221 0 366221
> oracle_at_qalin1 (MMAN) 220937 366221 0 366221
> oracle_at_qalin1 (DBW0) 19164289 40582953 131072
> 44318505
> oracle_at_qalin1 (LGWR) 10902229 23106685 196608
> 23565437
> oracle_at_qalin1 (CKPT) 308505 1657617 1114112
> 2771729
> oracle_at_qalin1 (SMON) 1319161 3118733
> 1507328 3577485
> oracle_at_qalin1 (RECO) 433237 1087117 65536 1087117
> oracle_at_qalin1 (CJQ0) 616505 1676941 786432 2070157
> oracle_at_qalin1 (MMON) 1220449 3249753
> 1638400 3446361
> oracle_at_qalin1 (MMNL) 218349 431757 0 431757
> oracle_at_qalin1 (D000) 651201 664177 0 1218189
> oracle_at_qalin1 (S000) 5319113 6496881 393216
> 739582577
> oracle_at_qalin1 (S001) 5378853 7217777
> 1048576 3380945521
> oracle_at_qalin1 (S002) 8128853 10035825 131072
> 3381273201
> oracle_at_qalin1 (S003) 4899257 6496881
> 1048576 3381600881
> oracle_at_qalin1 (S004) 615721 1909361 1179648
> 3386188401
> oracle_at_qalin1 (S005) 611497 1581681 851968 3386253937
> oracle_at_qalin1 (S006) 616837 1712753 851968 3386122865
> oracle_at_qalin1 (S007) 555533 1516145 851968 3386319473
> oracle_at_qalin1 (S008) 610401 1778289 1048576
> 3386253937
> oracle_at_qalin1 (S009) 609297 1974897 1114112
> 3386122865
> oracle_at_qalin1 (J000) 254085 1087117 0
> 1087117
> oracle_at_qalin1 (QMNC) 222077 366221 0 366221
> oracle_at_qalin1 (q000) 613877 1414797 262144 1414797
> oracle_at_qalin1 (q001) 307561 562829 0 562829
>
> PGA_MAX_MEM for shared servers is ~3GB sometimes, not sure what is
> causing that big consumption but the current allocated memory looks
> reasonable
>
> SELECT LOW_OPTIMAL_SIZE/1024 low_kb,
> (HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
> OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS
> FROM V$SQL_WORKAREA_HISTOGRAM
> WHERE TOTAL_EXECUTIONS != 0;
>
> LOW_KB HIGH_KB OPTIMAL_EXECUTIONS
> ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS
> 2 4 101146391 0 0
> 4 128 43179 0 0
> 128 256 50124 0 0
> 256 512 43867 0 0
> 512 1024 170969 0 0
> 1024 2048 10837 0 0
> 2048 4096 148 0 0
> 4096 8192 55 0 0
> 8192 16384 16 0 0
> 16384 32768 4 0 0
>
> No processing caused anything but optimal executions, there are no one
> pass or multi pass executions.
>
> SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
> ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
> ESTD_OVERALLOC_COUNT
> FROM V$PGA_TARGET_ADVICE;
>
> TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
> 75 98 4
> 150 98 4
> 300 98 4
> 450 98 4
> 600 100 4
> 720 100 4
> 840 100 4
> 960 100 4
> 1080 100 4
> 1200 100 3
> 1800 100 0
> 2400 100 0
> 3600 100 0
> 4800 100 0
>
> PGA hit is 100% at 600MB but overalloc count is not 0 until 1800MB.
>
> SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total))
> percentage
> FROM (SELECT name, value cnt, (sum(value) over ()) total
> FROM V$SYSSTAT
> WHERE name like 'workarea exec%');
>
> PROFILE CNT PERCENTAGE
> workarea executions - optimal 101481578 100
> workarea executions - onepass 0 0
> workarea executions - multipass 0 0
>
> What can be the possible reason for this error and how we determine
> what may have caused the maximum memory usage for process to spike to
> 3GB and since we don't have multiple pass executions what can be the
> other reasons?
>
> Thanks
> --Harvinder

Here's a thought, you seem to be using .Net technology to access an Oracle database via Shared Servers. Normally the use of shared servers indicates that you expect large numbers of clients. Quite often the use of the .Net clients indicates a 3 (or more) tier setup with some IIS application servers in the middle.

If this is the case I'd be thinking about doing my connection pooling on the apps tier (ODAC can do this for you in a nice configurable way) and not at the database level. I'd also be quite lary of shared servers in general, but that's another story.

cheers Received on Tue May 15 2007 - 14:20:59 CDT