I am having problems with dbms_stats.gather_schema_stats with cascade option on Aix. The exact command is "exec dbms_stats.gather_schema_stats(ownname=>null,estimate_percent=>dbms_stats.auto_sample_size,cascade=>true);"
I tried the above command on two AIX machines with AIX 5.2. Both failed with ora-04030. One machine has 4GB of ram with 8GB paging space. The other machine has 8GB ram with 16 GB paging space. I used topas to monitor the memory usage for the session. When the paging space for the session went up to around 120mb, then, it failed. The two databases I used are one of those ERP databases with tons of tables and indexes. The PGA_AGGREGATE_TARGET does not seem to have any thing to do with the failure. I have tried 50M and 300M. Each time it failed at 120M. I also ran this query:
v$statname n,v$sesstat s
n.STATISTIC# = s.STATISTIC# and
name like 'session%memory%'
order by 3 desc
session pga max is right round 120m.
For some reason, Oracle just won't allocate more memory any more even though both machine have enough paging space.(one machine still has 50% paging space left, the other has 33% left).
Then, I ran the same command on a win2k server. It worked. The PGA went up to around 260MB. the oracle.exe is around 410mb, that is SGA+PGA.
Oracle version is 220.127.116.11. So, Anybody has any idea about what is going on with AIX. Here is the ulimit.
Click Here to Expand Forum to Full Width