This content has been marked as final. Show 8 replies
Check the metalink note :
Diagnosing and Resolving ORA-4030 errors - 233869.1
Refer to Metalink : Doc ID: 863562.1
It will help out for your trouble shooting the Issue.
- Pavan Kumar N
Thanks, I will pass that article along to my vendor and maybe they will get me a copy. I get all Oracle support through them so I don't have access to metalink. The reason I posted here was to try to get a jump start on what they might have me try, unfortunately it is a long process of going back and forth with the vendor checking metalink and then getting back to me to try something.
Have there been any noted problems with 11g and Automatic Memory Management?
1. Can you share the exact settings of yours?
- memory_target = ?
- memory_max_target = ?
- ksmggranule_size = ?
- pgamax_size = ?
2. Oracle 11g seems to have some minor problems with the granule size with automatic memory management and you might want consult to Oracle support for detail.
3. You can try traditional PGA heap dump with ORA-04030 error start upon that.
Dion Cho - Oracle Performance Storyteller
From my experience most database instances are too big memory wise. Administrators think "/3GB" means the SGA or MEMORY_TARGET can be set to 3 GB - but on Windows that's not true.
On Windows the whole database including all sessions is one process which consists of several threads. This process can allocate up to 3 GB memory. So the maximum size of everything SGA+PGA+UGA of all session must fit into this 3 GB. If you only have a few database connections you might use up to 2.5 GB for your SGA... but the more sessions you have the less memory is available for your SGA and the more memory is needed for your sessions.
Anyway i have also seen sessions which allocate a pl/sql table (stored in memory) and collect data for faster processing. Depending on the amount of data collected this table can be "huge" and cause your ORA-04040.
My blog: http://ronnyegner.wordpress.com
Thanks for the suggestions so far. This is what I got back from my vendor. It is possibly due to an unpublished Bug 7330813 that has to do with Function Based Indexes. This bug is fixed in 11.2.
-Unpublished Bug 7330813 deails
CBO may incur high hard parse times (as compared to 10g) for queries involving
Tables with functional indexes.
This Fix complements the fix for unpublished bug 6194582.
I have set the above parameter so we will see if it helps. Keep in mind that Oracle says not to use this Initialization parameter unless told to do so by Oracle Support.
This ended up being a problem on the Vendor's side. One of their procedures went crazy and started using up all available memory.
May I know how to resolve this issue? Because one of our production environment (in AIX) hitting the same error in 18.104.22.168 intermittently.
Is set the hidden parameter resolve your issue permanently?