By uploading your problematic database's alert log file and trace files, the tool can automatically provide the issue identification and suggested solution based on its established guidelines. Actually, you can get to read those guidelines without really uploading anything. So, you can even use the guideline to troubleshoot the database by yourself.
Here is the example information taken from the guideline:
Fault: Insufficient
SGA Free Memory at Startup
|
|
Your instance SGA configuration has been flagged as having too little
memory available for the automatic memory manager to make adjustments in the
SGA component sizes.
This issue could occur if in the init.ora parameters of your Alert log, (shared_pool_size + large_pool_size + java_pool_size + db_keep_cache_size + streams_pool_size + db_cache_size) / sga_target is greater than 90%. With the introduction of automatic memory management (ASMM in 10g, AMM in 11g), i.e. when SGA_TARGET > 0 or MEMORY_TARGET > 0, memory granules can be moved from one SGA component to another, depending on which component has an immediate need to grow. The memory components are: shared pool, buffer cache, streams pool, large pool, java pool, log buffer. The most common sharing of memory granules occur between the shared pool and the buffer cache and so these components can increase and decrease in size. In order to limit how much any component will decrease in size, we set a minimum value by specifying a size in the parameter file. For example, when SGA_TARGET >0 or MEMORY_TARGET > 0, then SHARED_POOL_SIZE=nnnn and B_CACHE_SIZE=nnnn become minimum sizes of these components. The sum of all the minimum sizes determine the minimum size of the SGA at start up. If the SGA_TARGET (or MEMORY_TARGET) is only slightly larger than the sum of the minimum settings, there will be very little memory to do any resizing, and this can lead to a ORA-4031. |
|
Relevant Observation(s):
|
|
Resolution Action Plan
|
|
1) Reduce the minimum values for the dynamic SGA components to allow
memory manager to make changes as needed
For example, either set: shared_pool_size=new value in the database INIT.ORA file, or connect as SYSDBA and issue statements: alter system set shared_pool_size=new value scope=both; NOTE: Explicit settings (as minimums) can be changed while the database is online. and 2) Modify auto-tuned configuration 10g: Increase value of SGA_TARGET to provide more SGA memory Either set: sga_target=new value in the database INIT.ORA file, or connect as SYSDBA and issue statements: alter system set sga_target=new value scope=both; Note: It will be necessary to restart the instance. That will mean that associated database will have to schedule an outage as well. 11g: Increase value of MEMORY_TARGET if used. If SGA_MAX_SIZE has been set remove the explicit setting and allow MEMORY_TARGET to manage the SGA sizing. Either set: memory_target=new value (remove explicit setting if applicable) sga_max_size=value in the database INIT.ORA file, or connect as SYSDBA and issue statements: alter system set memory_target=new value scope=both; (if explicitly setting SGA_MAX_SIZE) alter system reset sga_max_size scope=spfile; Note: It will be necessary to restart the instance. That will mean that associated database will have to schedule an outage as well. or 3) both of the above. 4) See these notes for further information regarding automatic memory configuration. NOTE:781630.1 - How to configure ASMM (Auto Memory) on 10g and 11g - Best Practices NOTE:452512.1- 11g MEMORY_TARGET Parameter Dependency NOTE:443746.1- Automatic Memory Management(AMM) on 11g NOTE:295626.1- How To Use Automatic Shared Memory Management (ASMM) In Oracle10g |
|
Also, in the seminar, the presenter mentioned Oracle is also building the ORA-4300 troubleshooting tool. By promoting these tools, Oracle is not only helping DBA to do the work by themselves, but also saving money from support. In the end, Oracle can make more profit and get more customers.
