dbms_stats ora-04030
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: dbms_stats ora-04030

Hybrid View

  1. #1
    Join Date
    Mar 2005
    Posts
    3

    dbms_stats ora-04030

    Hi, all:

    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:
    select
    sid,name,value
    from
    v$statname n,v$sesstat s
    where
    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 9.2.0.5. So, Anybody has any idea about what is going on with AIX. Here is the ulimit.

    time(seconds) unlimited
    file(blocks) unlimited
    data(kbytes) 131072
    stack(kbytes) 32768
    memory(kbytes) 32768
    coredump(blocks) 2097151
    nofiles(descriptors) 2000

    Thanks

    John Wang.

  2. #2
    Join Date
    Jul 2000
    Posts
    521
    Take a look at this : http://www.dbasupport.com/forums/sho...threadid=46634

    I faced similar issue - not with dbms_stats but, a session terminating due to ORA-04030 and nothing was looking out of place. I monitored the pga_used_mem from V$process for the sessions SPID and when that was reaching 1G, it was failing. After the bug-fix patch was applied (which is also part of 9206), the query completed successfully and I could also notice that the pga_used_mem did go beyond 1G.

    I know you have a different OS, but may be this is just 9205 issue. Give it a try...
    svk

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width