Best Performance Tuning Book for Oracle Database

One of the best books I have read on any Oracle technology. Must read if you need to know practical performance tuning. Link below

 

Oracle Performance Survival Guide 

Avoid out of memory error on Solaris 10 for Oracle Database

Out of Memory Problems on Oracle 10 / Solaris 10

Many kernel parameters have been replaced by so called resource controls in Solaris 10. It is possible to change resource controls using the prctl command. All shared memory and semaphore settings are now handled via resource controls, so any entries regarding shared memory or semaphores (shm & sem) in /etc/system will be ignored.

Here is the procedure we followed to modify the kernel parameters on Solaris 10 / Oracle 10.2.0.2.

Unlike earlier releases of Solaris, most of the system parameters needed to run Oracle are already set properly, so the only one you need is the maximum shared memory parameter. In earlier versions this was called SHMMAX and was set by editing the /etc/system file and rebooting. With Solaris 10 you set this by modifying a «Resource Control Value». You can do this temporarily by using prctl, but that is lost at reboot so you will need to add the command to the oracle user’s .profile.

The other option is to create a default project for the oracle user.

# projadd -U oracle -K \
“project.max-shm-memory=(priv,4096MB,deny)” user.oracle

What this does:

Makes a project named “user.oracle” in /etc/project with the user oracle as it’s only member.

# cat /etc/project

system:0::::
user.root:1::::
noproject:2::::
default:3::::
group.staff:10::::
user.oracle:100::oracle::project.max-shm-memory
=(priv,4294967296,deny)

Because the name was of the form “user.username” it becomes the oracle user’s default project.

The value of the maximum shared memory is set to 4GB, you might want to use a larger value here if you have more memory and swap.

No reboot is needed, the user will get the new value
at their next login.

Now you can also modify the max-sem-ids Parameter:

# projmod -s -K “project.max-sem-ids=(priv,256,deny)” \
user.oracle

Check the Paramters as User oracle

$ prctl -i project user.oracle

project: 100: user.oracle
NAME PRIVILEGE VALUE FLAG ACTION RECIPIENT
project.max-contracts
privileged 10.0K – deny –
system 2.15G max deny –
project.max-device-locked-memory
privileged 125MB – deny –
system 16.0EB max deny –
project.max-port-ids
privileged 8.19K – deny –
system 65.5K max deny –
project.max-shm-memory
privileged 4.00GB – deny –
system 16.0EB max deny –
project.max-shm-ids
privileged 128 – deny –
system 16.8M max deny –
project.max-msg-ids
privileged 128 – deny –
system 16.8M max deny –
project.max-sem-ids
privileged 256 – deny –
system 16.8M max deny –
project.max-crypto-memory
privileged 498MB – deny –
system 16.0EB max deny –
project.max-tasks
system 2.15G max deny –
project.max-lwps
system 2.15G max deny –
project.cpu-shares
privileged 1 – none –
system 65.5K max none –
zone.max-lwps
system 2.15G max deny –
zone.cpu-shares
privileged 1 – none –

ORA-27300 ORA-27301 ORA-27302

ORA-27300: OS system dependent operation:fork failed with status: 12
ORA-27301: OS failure message: Not enough space
ORA-27302: failure occurred at: skgpspawn3
Sun Mar 27 08:45:52 2011

This problem is most likely due to swap space being less. Ask your system administrator to increase the swap space. The rule of thumb is swap space should be twice that of physical memory. We faced this issue on AIX 5L box running Oracle Database 10gR2 10.2.0.1

Alternately you can run the HCVE script which is now part of RDA to do health check of database and confirm this.

1. Download RDA package for your OS from metalink.

2. Unzip the RDA package.

3. Go to the RDA directory and type below command to run. For an AIX system you will see options. Select your database version.

$ ./rda.sh -T hcve

Processing HCVE tests …
Available Pre-Installation Rule Sets:
1. Oracle Database 10g R1 (10.1.0) Preinstall (AIX)
2. Oracle Database 10g R2 (10.2.0) Preinstall (AIX)
3. Oracle Database 11g R1 (11.1.0) Preinstall (AIX)
4. Oracle Database 11g R2 (11.2.0) Preinstall (AIX)
5. Oracle Application Server 10g (9.0.4) Preinstall (AIX)
6. Oracle Fusion Middleware 11g R1 (11.1.1) Preinstall (AIX)
7. Oracle Portal Preinstall (Generic)
8. Oracle Identity Management 10g (10.1.4) Preinstall (AIX)
9. Oracle E-Business Suite Release 11i (11.5.10) Preinstall (AIX)
10. Oracle E-Business Suite Release 12 (12.1.1) Preinstall (AIX)
Available Post-Installation Rule Sets:
11. Oracle Portal Postinstall (generic)
12. Data Guard Postinstall (Generic)
Enter the HCVE rule set number
Hit ‘Return’ to accept the default (1)
> 2

Enter value for < Planned ORACLE_HOME location >
Hit ‘Return’ to accept the default
(/oradb/u01/app/oracle/oracle/product/10.2.0/db_1)

4. Once you select a test the step above you will see the test results on the screen. The test results are also written to a HTML file that is located in the RDA output directory. You can review the test results by using a Web Browser to open the following file located in:

Test “Oracle Database 10g R1 (10.1.0)  Preinstall (AIX)” executed at 28-Mar-2011 14:33:03

Test Results
~~~~~~~~~~~~

ID     NAME                 RESULT  VALUE
====== ==================== ======= ==========================================
A00010 OS Certified?        PASSED  Certified with Oracle Database 10g
A00020 User in “/etc/passw PASSED  userOK
A00030 Group in “/etc/grou PASSED  GroupOK
A00040 Enter ORACLE_HOME    RECORD  /oradb/u01/app/oracle/oracle/product/..>
A00050 ORACLE_HOME Valid?   PASSED  OHexists
A00060 O_H Permissions OK?  PASSED  CorrectPerms
A00070 Umask Set to 022?    PASSED  UmaskOK
A00080 LDLIBRARYPATH Unset? PASSED  UnSet
A00090 LIBPATH Unset?       PASSED  UnSet
A00100 Other O_Hs in PATH?  FAILED  OratabEntryInPath
A00110 oraInventory Permiss PASSED  oraInventoryOK
A00120 /tmp Adequate?       PASSED  TempSpaceOK
A00130 Swap (in MB)         RECORD  5632
A00140 RAM (in MB)          PASSED  7712
A00150 SwapToRAM OK?        FAILED  SwapLessThanRAM
A00160 Disk Space OK?       PASSED  DiskSpaceOK
A00170 AIXTHREAD_SCOPE=S?   PASSED  AIXTHREAD_SCOPEOK
A00175 LINK_CNTRL is Unset? PASSED  LINK_CNTRLunset
A00180 Got Software Tools?  PASSED  ld_nm_ar_make_found
A00190 ulimits OK?          PASSED  ulimitOK
A00200 Got OS Packages?     PASSED  All required OS packages are installed
A00210 Got OS Patches?      PASSED  PatchesFound
A00220 Other OUI Up?        PASSED  NoOtherOUI
Result file: /oradb/u01/app/oracle/rda/output/RDA_HCVE_A200DB10R1_aix_res.htm

Note: There are NO tests currently available for the Windows Platform.

Change size of Redo Log files and drop the old files.

How to change the Size of the Redo Log files to improve the log switch frequency in Oracle database.

select * from v$logfile;
select * from v$log;

alter database add logfile group 4 (‘/u1/oradata/TEST/redo4a.log’,’/u1/oradata/TEST/redo4b.log’) size 100M reuse;

alter database add logfile group 5 (‘/u2/oradata/TEST/redo5a.log’,’/u2/oradata/TEST/redo5b.log’) size 100M reuse;

alter database add logfile group 6 (‘/u3/oradata/TEST/redo6a.log’,’/u3/oradata/TEST/redo6b.log’) size 100M reuse;

Alter system switch logfile;
Alter system switch logfile;
Alter system switch logfile;

Alter system checkpoint;

Before proceeding to drop the old log files. Just do a “select * from v$log” to be sure that the old redo log group have their status as “inactive”.

Alter database drop logfile group 1;
Alter database drop logfile group 2;
Alter database drop logfile group 3;

#Now go to the physical location and delete the files from there.