Memory Tuning Tips

Oracle 10gR2

You can check the current size of components in sga using v$ views like:
V$SGA
V$SGASTAT
V$SGA_DYNAMIC_COMPONENTS

If you enabled ASMM, memory statistics will be calculated and indication of memory tuning can be found in the following v$ views:
V$SGA_TARGET_ADVICE
V$PGA_TARGET_ADVICE
V$DB_CACHE_ADVICE
V$SHARED_POOL_ADVICE
V$JAVA_POOL_ADVICE
V$STREAM_POOL_ADVICE

Another interactive way to tune memory is through 'Memory Advisor' interface which can be found in 'Advisor Central' from Grid Control.

SGA_TARGET vs. SGA_MAX_SIZE
SGA_MAX_SIZE is static parameter, while SGA_TARGET is dynamic. The size of SGA_MAX_SIZE is allocated from system memory after system start. On some platforms, the difference (SGA_MAX_SIZE-SGA_TARGET) is in virtual memory, which onlys taks space from disk. While on other platforms (Windows and Linux), it takes memory from system, which means much more SGA_MAX_SIZE than SGA_TARGET can be resouce waste on production system.

ORA-04031 and SGA settings

Oracle 10.2.0.2
OS RHEL 4

Recently, I got error message from Grid Control for one of our production databases.

Failed to connect to database instance: ORA-04031: unable to allocate 4120 bytes of shared memory ("large pool","unknown object","session heap","kzctxhugi1") (DBD ERROR: OCISessionBegin).

The message is pretty obvious: the large pool is not large enough for new allocations. Since it's 10g and we are using ASMM, the SGA is self-adjusted. We put 1.5G to sga_target and didn't specify a value for large_pool_size. Oracle suggests give a minimum value to large_pool_size so that Oracle won't squeeze large pool too small.

Another question I have during the research is that what's point of having both sga_target and sga_max_size.

Well, since sga_max_size is static and sga_target is dynamic, having set a larger value for sga_max_size than sga_target gives you a tuning margin for sga_target later without restarting the database. One thing we should pay attention is that on some OS, such as Windows and Linux, memory is allocated the same size as sga_max_size when the instance is started, but on some other OS, such as Sun Solaris, memory is allocated the same size as sga_target, and the other free memory (sga_max_size - sga_target) is ready for other use.

Grid Control Agent Crash with 'too many open files' Error

Grid Control Agent 10.2.0.4
Database: 10.2.0.2
OS Platform: RHEL Release 4 64bit

Agent crashes a lot, intermittently with 'too many open files' error inside Grid Control. Trace file 'emagent.trc' gives 'health check' error lik this:

2008-03-24 15:24:52 Thread-4124650400 ERROR fetchlets.healthCheck: GIM-00105: file not found
2008-03-24 15:24:52 Thread-4124650400 ERROR engine: [oracle_database,,health_check] : nmeegd_GetMetricData failed : Instance Health Check initialization failed due to one of the following causes: the owner of the EM agent process is not same as the owner of the Oracle instance processes; the owner of the EM agent process is not part of the dba group; or the database version is not 10g (10.1.0.2) and above.
2008-03-24 15:24:52 Thread-4124650400 WARN collector: Error exit. Error message:Instance Health Check initialization failed due to one of the following causes: the owner of the EM agent process is not same as the owner of the Oracle instance processes; the owner of the EM agent process is not part of the dba group; or the database version is not 10g (10.1.0.2) and above

Cause: Bug 5872000 - Healthcheck Error Occurs fror 32Bit Database on 64Bit OS Due to Bug4526916 Fix.
The Healthcheck file, namely $ORACLE_HOME/dbs/hc_.cat file differs in size from the memory structure used by the Agent to read it. This file is created by the database on startup time, if not present.

This happens when the database is e.g. 10.2.0.4 and the agent is 10.2.0.3 and vice versa.

Possible solutions:

1. Apply Patch 5872000 to databases on 64-bit machine.
This needs to be applied on top of 10.1 -> 10.2.0.3, and 11.1.0.6 databases. THe file $ORACLE_HOME/dbs/hc_.dat may need to be removed before starting up the database after patch application. This file is created on database start up if not present. The agent uses this file for the Healthcheck metric. By recreating the file on start up after the patch application, the file is the correct one needed by the agent.
2.Disable the healthcheck metric per database in Grid Control.
Check 379423.1 in Metalink on 'How to edit or disable the Health Check Metric Collection in Grid Control 10.2'.

Note: If the second workaround is applied, then you have to redo it everytime you add new database target into Grid Control.

Reference Doc in Metalink:
564617.1
566607.1
379423.1
469227.1

Open File Limit on Linux

Oracle 10.2.0.2
3-node RAC
RHEL AS release 4

Our application experienced performance downgrade today and user can not get response from application for around an hour. Not long after that, when we tried to log into the system, a message 'Too many open files' showed up and we were unable to login.

Searching a little bit, I found that Linux limits the number of open file handles. I recently installed Oracle Grid Control Agent on our RAC and created new database. The open file handles was increased up to the limit, and the system was unstable.

The file /proc/sys/fs/file-nr lists the number of current allocated file handles, available file handles in the allocated file handles and maximum file handles that can be opend for the whole system.
Oracle recommends that the file handles for the entire system be set to at lease 65536.
You can alter the default setting for the maximum number of file handles without rebooting the machine by making the changes directly to the /proc file system (/proc/sys/fs/file-max) using the following:

# sysctl -w fs.file-max=65536

You should then make this change permanent by inserting the kernel parameter in the /etc/sysctl.conf startup file:

# echo "fs.file-max=65536" >> /etc/sysctl.conf

The soft limit and hard limit of file handles for oracle can be configued inside /etc/security/limits.conf file.

cat >> /etc/security/limits.conf <<EOF
oracle soft mnproc 2047
oracle hard mnproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

nofile is the number of open file handles.
mnproc is hte number of processes available to a single user

Text Index Synchronization and Optimization

Oracle Text is a core component of iFS, Ultra Search, and XML DB. The indexing and searching abilities of Oracle Text are full-text retrieval against virtually any datatype (including all LOB types). They are not restricted to data stored in the database. It can index and search documents stored on the file system and index more than 150 document types, including a search on widget and find documents that contain the work gadget.

How to apply Oracle Text and Text Index can be found here:
http://www.oracle.com/technology/oramag/oracle/04-sep/o54text.html

The Oracle Text indexes must be periodically synchronized so that new data is included in the indexes. This process can be scheduled to run on a periodic basis for Oracle 9i. The Procedure 'CTX_DDL.SYNC_INDEX' is used to synchronize text index. Oracle 10g supports an improved method of index synchronization over Oracle 9i. Oracle 10g has the ability to synchronize the Text indexes as data is saved. This method does not require background jobs to be configured.

In systems with frequent record modification or creation, the Text indexes will become fragmented over time. This fragmentation will lead to slow performance during index synchronization, saving data, and searching. The Procedure 'CTX_DDL.OPTIMIZE_INDEX' should be used to optimize the Text indexes periodically.

Text index types: CONTEXT, CTXCAT, or CTXRULE.

To make sure which indexes are Text indexes, log into user account and issue the following command:
SELECT idx_name
FROM ctxsys.ctx_indexes
WHERE idx_owner='';

Text Packages and associated information can be found in the Oracle official document 'Oracle Text Reference'.

Sql Tuning Advisor

Sql Tuning Advisor (STA) is a new feature introduced in Oracle 10g. This automates the entire SQL tuning process.

Three ways to utilize STA:
Enterprise Manager Grid Control or Database Control
DBMS_SQLTUNE package
sqltrpt.sql script

1. STA Through EM
The user must have been granted the SELECT_CATALOG_ROLE role.
STA interface can be found through Performance Page > Advisor Central (Related Links) > SQL Tuning Advisor.
Through 'Top Activity' or 'Historical SQL (AWR)', you can choose Hot SQL that you want to tune. And then you can create tune sets and schedule sql tuning.

2. DBMS_SQLTUNE package
To use the APIs the user must have been granted the DBA role and the ADVISOR privilege.
Running SQL Tuning Advisor using DBMS_SQLTUNE package is a two-step process:
1) Create a SQL tuning task
2) Execute a SQL tuning task
Example can be found in metalink Doc 262687.1.

3. sqltrpt.sql script
Starting with Oracle 10.2 there is a script ORACLE_HOME/rdbms/admin/sqltrpt.sql which can be used for usage of SQL Tuning Advisor from the command line.

Tuning 'log file sync' Event

What is a 'log file sync' Wait

At commit time, a process creates a redo record (containing commit opcodes) and copies that redo record into the log buffer. Then, that process signals LGWR to write the contents of log buffer. LGWR writes from the log buffer to the log file and signals user process back completing a commit. A commit is considered successful after the LGWR write is successful. In a nutshell, after posting LGWR to write, user or background processes wait for LGWR to signal back with a 1-second timeout. The user process charges this wait time as a 'log file sync' event.

The Root Causes of 'log file sync' Waits

1.LGWR is unable to complete writes fast enough for one of the following reasons:
a.Disk I/O performance to log files is not good enough.
b.LGWR is starving for CPU resource.
c.Due to memory starvation issues, LGWR can be paged out.
d.LGWR is unable to complete writes fast enough due to file system or Unix buffer cache limitations.
2.LGWR is unable to post the processes fast enough, due to excessive commits.
3.IMU undo/redo threads. With private strands, a process can generate few Megabytes of redo before commiting. LGWR must write the generated redo so far, and processes must wait for 'log file sync' waits, even if the redo generated from other processes is small enough.
4.LGWR is suffering from other database contention such as enqueue waits or latch contention.
5.Various bugs.

Identify the Root Cause

1.First make sure the 'log file sync' event is indeed a major wait event. Compare 'log file sync' wait time with CPU time.
2.Identify and break down LGWR wait events, and query wait events for LGWR.

Find sid for LGWR process.

SQL> SELECT sid 
SQL> FROM v$session
SQL> WHERE type='BACKGROUND' and program like '%LGWR%';

Find wait events for LGWR.

SQL> SELECT event, time_waited, time_waited_micro
SQL> FROM v$session_event
SQL> WHERE sid=
SQL> ORDER BY time_waited;

When LGWR is waiting for posts from the user sessions, that wait time is accounted as an 'rdbms ipc message' event. Normally, this event can be ignored.
It is worth noting that v$session_wait is a cummulative counter from the instance startup, and hence, can be misleading.