Tuesday, August 23, 2011

Manual RAC Database Creation

This document provides step by step instructions to create a RAC database manually. This procedure was tested on 11g, but should also work for other Oracle versions without or with some slight modifications. Following are the assumptions
  1. This is a 2 node RAC running on Solaris OS (All steps should also work on any other OS)
  2. Nodes names will be rac1 and rac2
  3. Oracle Clusterware (11.1.0.7) is installed and running
  4. Oracle Database Software (11.1.0.7) is installed
  5. Database name is saqdb and instances names will be saqdb1 and saqdb2
  6. Database will be same as option “custom database” selected during database creation using DBCA.
  7. ASM instances are up and running on both nodes with +DATA and +RCV (flash recovery area) disk group created.
  8. ORACLE_BASE= /u01/app/oracle
  9. ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1

Database Creation (all steps done by oracle user)
Create following directories on both nodes
mkdir -p /u01/app/oracle
mkdir -p /u01/app/oracle/admin/saqdb/adump
mkdir -p /u01/app/oracle/admin/saqdb/dpdump
mkdir -p /u01/app/oracle/admin/saqdb/hdump
mkdir -p /u01/app/oracle/admin/saqdb/pfile
mkdir -p /u01/app/oracle/cfgtoollogs/dbca/saqdb
On any node, set environment variable for ASM instance and create a directory “saqdb” on the disk group +DATA using ASMCMD
ASMCMD> cd DATAASMCMD> mkdir saqdb
ASMCMD> cd ..
ASMCMD> cd RCV
ASMCMD> mkdir saqdb
Add following entry in /var/opt/oracle/oratab file on both nodes. For other Unix bases systems, add this entry in /etc/oratab file.
saqdb:/u01/app/oracle/product/11.1.0/db_1:Y
Set environment variable for database home and create password file on both nodes
rac1
cd $ORACLE_HOME/dbs
orapwd file=orapwsaqdb1 password=mypassword
rac2
cd $ORACLE_HOME/dbs
orapwd file=orapwsaqdb2 password=mypassword
Create init file “initsaqdb1.ora” in $ORACLE_HOME/dbs directory with the following parameters on the rac1
   --------------------------------------------------------------------------------------------------

saqdb1.oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
saqdb2.oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.sga_target=1024m
*.audit_file_dest='/u01/app/oracle/admin/saqdb/adump'
*.audit_sys_operations=TRUE
*.audit_trail='OS'
*.cluster_database_instances=2
*.cluster_database=FALSE
*.compatible='11.1.0.0.0'
#*.control_files='+rcv/saqdb/controlfile/Current.3458.757175825'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+RCV'
*.db_domain=''
*.db_name='saqdb'
*.db_recovery_file_dest='+RCV'
*.db_recovery_file_dest_size=53687091200
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers=''
saqdb1.instance_number=1
saqdb2.instance_number=2
saqdb1.local_listener='LISTENER_SAQDB1'
saqdb2.local_listener='LISTENER_SAQDB2'
*.log_archive_dest_1='LOCATION=+RCV/'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=1000
*.pga_aggregate_target=125m
*.processes=1000
*.remote_listener='LISTENERS_SAQDB'
*.remote_login_passwordfile='exclusive'
*.sec_case_sensitive_logon=FALSE
*.sec_max_failed_login_attempts=3
*.sec_protocol_error_trace_action='ALERT'
*.sessions=335
saqdb2.thread=2
saqdb1.thread=1
saqdb1.undo_tablespace='UNDOTBS1'
saqdb2.undo_tablespace='UNDOTBS2'
 -------------------------------------------------------------------------------------------------- 


Note Following
1. control_files is commented out because this will be added later once controlfile will be created during database creation.
2. cluster_database is set to FALSE. It will set to TRUE in the last stages of this exercise.

Add following entries in TNS files of both nodes

LISTENER_SAQDB2 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = PLPORA02-VIP)(PORT = 1522))


LISTENER_SAQDB1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = PLPORA01-VIP)(PORT = 1522))

LISTENERS_SAQDB =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = PLPORA01-VIP)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = PLPORA02-VIP)(PORT = 1522))
  )

SAQDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = PLPORA01-VIP)(PORT = 1522))
    (CONNECT_DATA =
    (FAILOVER = true)
      (SERVER = DEDICATED)
      (SERVICE_NAME = COMFORT)
      (INSTANCE_NAME = COMFORT1)
      (failover_mode = (BACKUP=COMFORT2)(type=select)(method=basic)(RETRIES=20)(DELAY=3))
    )
  )

SAQDB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = PLPORA02-VIP)(PORT = 1522))
    (FAILOVER = true)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = COMFORT)
      (INSTANCE_NAME = COMFORT2)
      (failover_mode = (BACKUP=COMFORT1)(type=select)(method=basic)(RETRIES=20)(DELAY=3))
    )
  )

SAQDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = PLPORA01-VIP)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = PLPORA02-VIP)(PORT = 1522))
    (LOAD_BALANCE = yes)
    (FAILOVER = true)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = COMFORT)
      (failover_mode = (type=select)(method=basic)(RETRIES=20)(DELAY=3))
    )
  )

On rac1, invoke SQLPLUS and startup the instance in nomount state
$export ORACLE_SID=saqdb1
SQL>startup nomount
Execute following command to create the database.
CREATE DATABASE "saqdb" MAXINSTANCES 32MAXLOGFILES 192MAXLOGMEMBERS 3MAXDATAFILES 1024DATAFILE SIZE 300M AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITEDEXTENT MANAGEMENT LOCALSYSAUX DATAFILE SIZE 120M AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITEDSMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 20M AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITEDSMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE SIZE 200M AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITEDCHARACTER SET WE8MSWIN1252NATIONAL CHARACTER SET AL16UTF16LOGFILE GROUP 1  SIZE 51200K,GROUP 2  SIZE 51200K,GROUP 3  SIZE 51200K;
 
Now use “asmcmd” cmmand to find out the name of the control file and update it in the init.ora file you created previously.
[rac1]$ asmcmd
ASMCMD> ls
DATA/
RCV/
ASMCMD> cd rcv
ASMCMD> ls
SAQDB/
MYDB/
YOURDB/
ASMCMD> cd saqdb
ASMCMD> ls
CONTROLFILE/
ONLINELOG/
ASMCMD> cd controlfile
ASMCMD> ls
Current.3458.757175825
ASMCMD>

Shutdown and startup the instance to let control_files init parameter take effect.
shutdown immediate
startup
Log in as SYS and create USERS tablespace
CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE SIZE 5M AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO;
ALTER DATABASE DEFAULT TABLESPACE "USERS";

Log in as SYS and create run following scripts to create catalog, pl/sql support and to build other required structures.
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/catalog.sql;
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/catblock.sql;
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/catproc.sql;
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/catoctk.sql;
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/owminst.plb;
Connect as SYSTEM user and execute following script
@/u01/app/oracle/product/11.1.0/db_1/sqlplus/admin/pupbld.sql;
Exit from the session
Connect as SYSTEM user and execute following script
@/u01/app/oracle/product/11.1.0/db_1/sqlplus/admin/help/hlpbld.sql helpus.sql;
Connect as SYS and execute following scripts to add java support. @/u01/app/oracle/product/11.1.0/db_1/javavm/install/initjvm.sql;
@/u01/app/oracle/product/11.1.0/db_1/xdk/admin/initxml.sql;
@/u01/app/oracle/product/11.1.0/db_1/xdk/admin/xmlja.sql;
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/catjava.sql;
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/catexf.sql;
Connect as SYS and execute following scripts to add XML DB support.
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/catqm.sql change_on_install SYSAUX TEMP;
Exit from the session
Connect as SYS and execute following remaining scripts to add XML DB support.
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/catxdbj.sql;
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/catrul.sql;
Connect as SYS and execute following script to install ORDINST schema.
@/u01/app/oracle/product/11.1.0/db_1/ord/admin/ordinst.sql SYSAUX SYSAUX;


Connect as SYS and execute following script to install Intermedia.
@/u01/app/oracle/product/11.1.0/db_1/ord/im/admin/iminst.sql;


Connect as SYS and execute following script to install APEX.
@/u01/app/oracle/product/11.1.0/db_1/apex/catapx.sql change_on_install SYSAUX SYSAUX TEMP /i/ NONE;


Connect as SYS and execute following script to install Cluster Views.
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/catclust.sql;

Connect as SYS and execute following PL/SQL bock to lock all the users account except SYS and SYSTEM.
BEGIN
 FOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE USERNAME NOT IN (
'SYS','SYSTEM') )
 LOOP
  dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);
  execute immediate 'alter user ' || item.USERNAME || ' password expire account lock' ;
 END LOOP;
END;
/


Recompile all objects.
select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MI:SS') from dual;
execute utl_recomp.recomp_serial();
select 'utl_recomp_end: ' || to_char(sysdate, 'HH:MI:SS') from dual;


Run following to check whether any component is invalid and correct the problem before moving forward hereafter.
col comp_id format a15
col comp_name format a50
col version format a12
SELECT comp_id
      ,comp_name
      ,version
      ,status
  FROM dba_server_registry;

Create spfile on the ASM disk group
CREATE SPFILE='+DATA/saqdb/spfilesaqdb.ora' FROM PFILE;

Shutdown and then startup the database with spfile.
 
Now database is creation has been completed. Next steps will be to make it a RAC database and register with CRS.
Login using SQLPLUS as user SYS and turn on the cluster database parameter
ALTER SYSTEM SET cluster_database=true SCOPE=SPFILE;
Create undo tablespace for second instance
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE SIZE 200M AUTOEXTEND ON NEXT 10M;
Add thread 2 redo log files for the second instance.
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4 SIZE 50M, GROUP 5  SIZE 50M, GROUP 6 SIZE 50M;
 
Enable redo log thread 2
ALTER DATABASE ENABLE PUBLIC THREAD 2;
Now on rac2 node, set environment variables for instance saqdb2 and start it.
startup
From any node, use srvctl utility to register this new database and its instance with the CRSsrvctl add database -d saqdb -o $ORACLE_HOME
srvctl add instance -d saqdb -i saqdb1 -n rac1
srvctl add instance -d saqdb -i saqdb2 -n rac2
srvctl enable database -d saqdb
srvctl enable instance -d saqdb -i saqdb1
srvctl enable instance -d saqdb -i saqdb2


Cleanly shutdown the instance on both nodes using SQLPLUS and then start them using srvctl. Now you can see the status of this new database and instances using crs_stat command. After shutting down instances manually, execute following from any node.
srvctl start database -d saqdb
srvctl status database -d saqdb
Instance saqdb1 is running on node rac1
Instance saqdb2 is running on node rac2

Monday, March 28, 2011

Installing Oracle Database 11.2 on IBM AIX 6.1 (Includes HACMP)

SWAP space recommendation from Oracle corp. for Oracle 11g Release 2

If RAM is between 1 GB and 2 GB, SAWP should be 1.5 times the size of RAM
If RAM is between 2 GB and 16 GB, SWAP should be equal to the size of RAM
If RAM is more than 16 GB, SWAP should be 16 GB

Check RAM
# /usr/sbin/lsattr -E -l sys0 -a realmem

Check swap space
# /usr/sbin/lsps -a


Operating System Requirements
The following, or later versions, of the operating systems are required for Oracle Database 11g Release 2 (11.2):
- AIX 5L V5.3 TL 09 SP1 ("5300-09-01"), 64 bit kernel
- AIX 6.1 TL 02 SP1 ("6100-02-01), 64-bit kernel

The following operating system filesets are required for AIX 6.1:
- bos.adt.base
- bos.adt.lib
- bos.adt.libm
- bos.perf.libperfstat - 6.1.2.1 or later
- bos.perf.perfstat
- bos.perf.proctools
- rsct.basic.rte
- rsct.compat.clients.rte
- xlC.aix61.rte -10.1.0.0 or later

1. To determine the distribution and version of AIX installed, enter the following command:

# oslevel -s

For AIX 6.1: If the operating system version is lower than AIX 6.1 Technology Level 2 SP 1, then upgrade your operating system to this, or a later, level.

2. To determine whether the required filesets are installed and committed, enter a command similar to the following:
# lslpp -l bos.adt.base bos.adt.lib bos.adt.libm bos.perf.perfstat \
bos.perf.libperfstat bos.perf.proctools rsct.basic.rte rsct.compat.clients.rte \
xlC.aix61.rte

3. To determine the supported kernel mode, enter a command similar to the
following:
# getconf KERNEL_BITMODE
64


The following, or later, patches are required for Oracle Database 11g Release 2 (11.2) for AIX Based Systems:


Authorized Problem Analysis Reports (APARs) for AIX 6L:
- IZ41855
- IZ51456
- IZ52319


# /usr/sbin/instfix -i -k "IZ51456 IZ52319 IZ41855"
Note: IZ52319 and IZ41855 might not displayed because these are included in Isome other installed APARs


Create OS Level Groups and User
  1. Check all OS requirements are met.
  2. Add dba OS level groups using “smit security” command. For HACMP, use “smit hacmp” to add the shared group for the cluster
  3. Add oracle user using “smit security” command. Select dba as Primary group for this user and /home/oracle as home directory. For HACMP, use “smit hacmp” to add the shared user for the cluster. Home directory is recommended to be on the local storage of the node.
  4. Reset the password for oracle user. For HACMP, use “smit security” to reset password for both nodes in HACMP.

Configure Shell Limits (Copied from Oracle documentation)
  1. Run “smit chuser” command and select “oracle” user to set the limits as follows.

Shell Limit (As Shown in smit)                Recommended Value
Soft FILE size                                                    -1 (Unlimited)
Soft CPU time                                                   -1 (Unlimited) Note: This is the default value.
Soft DATA segment                                           -1 (Unlimited)
Soft STACK size                                                -1 (Unlimited)
Soft Real Memory size                                       -1 (Unlimited)
Processes (per user)                                          -1 (Unlimited)
Note: This limit is available only in AIX 6.1 or later.
Refer to “Configure System Configuration Parameters” step for information on configuration of processes per user limits.

Edit /etc/security/limits file to set “rss = -1” under user “oracle” to set the “Soft Real Memory Size”. This file should have entries for “oracle” user as follows
oracle:
        fsize = -1
        data = -1
        stack_hard = -1
        stack = -1
        rss = -1

Configure System Configuration Parameters
Parameter                                                     Recommended Value
maxuprocs                                                        16348
ncargs                                                              128

  1. Execute “smit chgsys” command and make sure that value in “Maximum number of PROCESSES is equal or greater to the value of “maxuprocs” mentioned above. Also make sure that value in “ARG/ENV list size in 4K byte blocks is greater or equal to the value of “ncarg” mentioned above.
  2. Login as user “oracle” and execute command “ulimit -a” and confirm that limits have been set mentioned under “Configure Shell Limits.”

Checking Asynchronous Input Output Processes(Copied from IBM Document)

On AIX 6, the AIO device drivers are enabled by default. Increase the number of aioserver processes from the default value. The recommended value for aio_maxreqs is 64k (65536). Confirm this value.
Confirm the aio_maxreqs value using the procedure for your release:
AIX 6.1:
# ioo –o aio_maxreqs
aio_maxreqs = 65536

When performing an asynchronous I/O to a file system, note that each asynchronous I/O operation is tied to an asynchronous I/O server. Thus, the number of asynchronous I/O servers limits the number of concurrent synchronous I/O operations in the system.

The initial number of servers that are started during a system restart is determined by the minservers parameter. As concurrent asynchronous I/O operations occur, additional asynchronous I/O servers are started, up to a maximum of the value set in the maxservers parameter.

In general, to set the number of asynchronous I/O servers, complete the following procedure:
1. Adjust the initial value of maxservers to 10 times the number of disks that are to be used concurrently but no more than 80.
2. Monitor the performance effects on the system during periods of high I/O activity. If all AIO server processes are started, then increase the maxservers value. Also, continue to monitor the system performance during peak I/O activity to determine if there was a benefit from the additional AIO servers. Too many
asynchronous I/O servers increase memory and processor overload of additional
processes, but this disadvantage is small.

To monitor the number of AIO server processes that have started, enter the following:
# ps -ek|grep -v grep|grep –v posix_aioserver|grep -c aioserver
Note: Value could be “0” if there is not activity going on to the server


Tuning the AIX for Oracle
  1. Execute following commands while logged in as root user, for more details about this, see document “Tuning IBM AIX 5.3 and AIX 6.1 for Oracle Database.pdf”
# vmo -L lgpg_size 
# vmo -L lgpg_regions
# vmo -p -o lgpg_size=16777216  -o lgpg_regions=256
# vmo -L lgpg_size 
# vmo -L lgpg_regions
# vmo -p -o lru_file_repage=0 -o minperm%=3 -o maxperm%=90 -o  maxclient%=90
# vmo -r -o page_steal_method=1  
# bosboot -ad /dev/ipldevice
  1. Reboot the server

Miscellaneous Settings
  1. Check /etc/hosts file. It should contain the host name and alias in it.
  2. /etc/resolv.conf file should contain following entry

    nameserver      168.232.220.220
    domain  mydomain.com
  3. Check /etc/netsvc.conf. This file should contain following entry to make sure that /etc/hosts file is used first for name resolution

    host = local, bind, nis
  4. Set the xntpd for network time synchronization.
    1. Make entry of time serve in the /etc/ntp.conf as follows
      server oa-10.utac.com.sg prefer
    2. Use “smit xntpd” to start xntpd service.


Installation of Oracle 11G Release 2

  1. Set the environment variables for user oracle in .profile which could be found in /home/oracle directory. For HACMP, do this on both nodes.
    ###################################################
    ORACLE_HOSTNAME=ibmaixha01
    ORACLE_BASE=/oracle;export ORACLE_BASE
ORACLE_HOME=/oracle/product/11.2.0.1/db_1;export ORACLE_HOME
ORACLE_SID=mydb;export ORACLE_SID
ORACLE_TERM=vt100; export ORACLE_TERM
NLS_LANG=american_america.UTF8; export NLS_LANG
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data; export ORA_NLS33
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:/lib:/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH
PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin
set -o vi###################################################
  1. Run the rootpre.sh script and root user. For HACMP, execute this script on both nodes of HACMP. This will also create a group hagsuser, add user oracle and root into this group on both nodes.
    Note: Do not run the rootpre.sh script if you have a later release
of the Oracle Database software already installed on this system.
Switch user to root:
$ su -
password:
#
Complete one of the following steps, depending on the location of the installation
files:
·        If the installation files are on disc, enter a command similar to the following, where directory_path is the disc mount point directory or the path of the db directory on the DVD:
# /directory_path/rootpre/rootpre.sh
·        If the installation files are on the hard disk, change directory to the Disk1 directory and enter the following command:
# ./rootpre.sh


  1. Set ORACLE_HOSTNAME environment variable appropriately if required.
  2. Login as oracle user and check if all environment variables are set correctly.
  3. Set DISPLAY environment variable if required.
  4. Run Oracle installer from the directory where software is placed.
    $ ./runInstaller
    Uncheck the box for security updates and click Next.
  5. Select “Install database software only”, and click Next.
  6. Select “Single database installation”, and click Next.
  7. Select appropriate option for installation and click Next.
  8. Enter ORACLE_BASE and ORACLE_HOME directories and click Next.
  9. Specify oracle inventory location as mentioned bellow and click Next.
  10. Click Next.
  11. Click on Finish to start installation
  12. Following screen shows the installation progress.
  13. Run “orainstRoot.sh” and then “root.sh” as root.
  14. Click Close to finish the installation.

Monday, March 7, 2011

Don't COMMIT Too Often

Log Writer or LGWR is Oracle instance’s background process which has the responsibility to read from the redo log buffer cache and write into the redo log files. LGWR writes to the redo log file
1) Every 3 seconds
2) When the redo log buffer is one-third full
3) Before DBWR (Database Writer) process writes the modified buffers to the disk.
4) When COMMIT is issued

Here my focus would be point 4. When a COMMIT is issued by a user, LWGR puts a commit record in the log buffer cache and then writes changes made by the transaction in the CURRENT redo log file (in all members of redo log group). Here the session issuing commit needs to wait for a small (yet ignorable) amount of time until LGWR confirms the corresponding server process that changes have been written to the redo log file and guarantees that the transactions is recoverable in case of a failure (instance/media). Remember that actual changed data blocks (buffers) may still be in the memory (buffer cache) and may be chosen to be written to the datafiles at some later point in time (checkpoint, incremental checkpoint etc.)

Point to note here is; a physical write on to the disk (redo log file) at each commit which means more physical writes and more burdens on the storage sub system. Although each single COMMIT by a session puts a very very small and ignorable burden on the storage, but if taken as a whole, if we have thousands of COMMITs being issues in a minute, certainly latency of storage may increase. This is the reason that it is not recommended by Oracle to use too many commits in your application code and you should try to COMMIT as less as possible. As explained above, committing data causes the data in the log buffer cache to be written to the redo log files which requires physical writes and along with it, a wait event "log file sync" occurs every time the data is written to the current redo log group. Although this is a background wait event, but still if you find in your top 5 wait event, you might need to look at your application code and do a discussion with your development team on these COMMIT statements embedded in your code. More commits, more “log file sync” wait event and eventually poisonous for the scalability of your system. Let’s have an example where we insert 100000 rows in a table with testing COMMIT after every insertion and then testing one COMMIT after all 100000 insertions and see the time difference. This time difference also proves that each commit further slows down the writings.

Enter user-name: scott@usgumsd
Enter password:

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

USGUMSD$SCOTT> create table test (id number);

Table created.

USGUMSD$SCOTT> set timing on

USGUMSD$SCOTT>   begin
  for i in 1..100000
  loop
  insert into test values(1);
  commit;
  end loop;
  end;
  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:16.73

USGUMSD$SCOTT> truncate table test;

Table truncated.

Elapsed: 00:00:00.09
USGUMSD$SCOTT> begin
  for i in 1..100000
  loop
  insert into test values(1);
  end loop;
  commit;
  end;
  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.12
USGUMSD$SCOTT>

Conclusion:
There is a difference of 4 seconds. If you have thousands of DMLs running simultaneously without taking care of too many commit, you can imagine how much burden it will have on your IO system and increase in "database time" in AWR


Sunday, February 20, 2011

CPU Percentage Vs. CPU Load Average for DBAs

Most of the people, specially, working on Windows platform don’t know much about Load Average of CPU and just take a look at the CPU usage %age. On Linux based platforms load average or run queue length is a very important indicator to check how much is the load on the CPUs of the system and whether current number of CPUs enough to support the workload pushed to the server. 

Technically speaking, CPU is either 100% utilized or 0% utilized (when working, status is 1 which means it is busy, and when not working, status is 0). So, in a normal environment when there are active processes, CPU keeps on processing something all the time which means it is 100% busy all the time, so, what is CPU % then? Well, at OS level, CPU %age is calculated by number of processes actively using CPU during a time unit (used for CPU % calculation) and CPU time utilized by each of these active processes.
Although CPU %age is not a bad thing to look at, but, Load Average is the key to calculate the CPU load on your system which will reveal to you the need to have more CPUs in your system. CPU run queue length or load average would show you how many processes are currently in the queue waiting for the CPU to process their request. In a 4 CPU system, Load Average 4 means that there are 4 processes on the CPU and no one is waiting for the CPU because each process has a CPU available to process its request, but, if the Load Average starts increasing from the value 4 and continuously above 4 means that 4 CPUs might not be enough to support all active processes because a few processes are waiting in the queue for CPU to be free because CPUs are busy in processing the requests of other processes. A load average of 6, in this scenario, will mean that there are 2 processes which are in wait for the CPUs.

So as a DBA, Load Average certainly exposes to the DBA if database is not doing good because of insufficient CPU resources. Application side could be tuned if possible to release CPU load, or more CPUs could be added.

If you generate the AWR report (AWR is available starting from 10g and needs diagnostic pack license), it would also list the CPU load in it and hence can help DBA to find out CPU load during the snapshots interval this AWR is based on.

If you see a high Load (more than number of CPUs), you can find out the top SQLs hogging the CPU. In my experience, I have seen SQLs running thousands of times within a time interval of 15 minutes (AWR was based on 15 minutes snapshot interval) and this was alarming because this kind of SQLs execution keep the CPU load at high level. In this scenario, you can work on Application side to find out why you have this many executions of a single SQL during a small period of time and tune your application. Otherwise, you might need to add more CPUs to the system.