Oracle RDBMS : Generic Large Object (LOB) Performance Guidelines

This blog post is generic in nature and based on my recent experience with a content management system where securefile BLOBs are critical in storing and retrieving the checked in content. It is stro ngly suggested to check the official documentation in addition to these brief guidelines. In general, Oracle Database SecureFiles and Large Objects Developer's Guide 11g Release 2 (11.2) is a good starting point when creating tables involving SecureFiles and LOBs.

Guidelines

• Tablespace: create the LOB in a different tablespace isolated from the rest of the database
• Block size: consider larger block size (default 8 KB) if the expected size of the LOB is big
• Chunk size: consider larger chunk size (default 8 KB) if larger LOBs are expected to be stored and retrieved
• Inline or Out-of-line: choose "DISABLE STORAGE IN ROW" (out-of-line) if the average LOB size is expected to be > 4 KB. The default inlining is fine for smaller LOBs
• CACHE or NOCACHE: consider bypassing the database buffer cache (NOCACHE) if large number of LOBs are stored and not expected to be retrieved frequently
• COMPRESS or NOCOMPRESS: choose COMPRESS option if storage capacity is a concern and a constraint. It saves disk space at the expense of some performance overhead. In a RAC database environment, it is recommended to compress the LOBs to reduce the interconnect traffic
• De-duplication: by default, duplicate LOBs are stored as a separate copy in the database. Choosing DEDUPLICATE option enables sharing the same data blocks for similar files thus reducing storage overhead and simplifying storage management
• Partitioning: consider partitioning the parent table to maximize application performance. Hash partitioning is one of the options if there is no potential partition key in the table
• Zero-Copy I/O protocol: turned on by default. Turning it off in a RAC database environment could be beneficial. Set the initialization parameter _use_zero_copy_io=FALSE to turn o ff the Zero-Copy I/O protocol
• Shared I/O pool: database uses the shared I/O pool to perform large I/O operations on securefile LOBs. The shared I/O pool uses shared memory segments. If this pool is not large enough or if there is not enough memory available in this pool for a securefile LOB I/O operation, Oracle uses a portion of PGA until there is sufficient memory available in the shared I/O pool. Hence it is recommended to size the shared I/O pool appropriately by monitoring the database during the peak activity. Relevant initialization parameters: _shared_io_pool_size and _shared_iop_max_size

Also see:
Oracle Database Documentation : LOB Performance Guidelines

Read More

Oracle 11g: Travel back in time with the Database Flashback

Error recovery, historical reporting, trend analysis, data forensics and fraud detection are just some of the business problems that can be solved by using the Flashback Data Archive feature in Oracle 11g database. The Flashback option can be enabled for the entire database or for a selected set of tables. It can be enabled in the database with no application changes.

At work I usually run performance tests by starting with a clean copy of the database. I analyze the test results at the end of the test, determine the next course of action (tuning), restore the clean copy of the database from a backup, apply the tuning and re-run the performance test. It goes on in a cycle until I'm happy with the overall test result. In some cases especially with large data sets, restoring the database from a backup becomes one of the time consuming tasks. In such situations, using the database flashback to go back to a previously saved restore point saves quite a bit of time. Rest of this blog post demonstrates how to enable database flashback and to go back to a specified restore point. Check Oracle Total Recall with Oracle Database 11g Release 2 white paper for more information on Flashback Data Archive (FDA).

Objective

Revert the entire database to a previously saved state at will

Steps to perform

• Configure the following initialization parameters: db_recovery_file_dest & db_recovery_file_dest_size
  
• Enable Archive Log mode
  
• Enable database Flashback option
  
• Create a restore point. Decide whether to create a normal or a guaranteed restore point
  --------------------------------------------------------------------------------------------------------
  
• Finally flashback database to the created restore point when required

Be aware that there will be some performance and storage overhead in using the database flashback. Evaluate all your options carefully before configuring database flashback.

Example

The following example uses guaranteed restore point to flashback the database in a two-node RAC environment. Most of the example is self-explanatory.

% srvctl status database -d DEMO
Instance DEMO1 is running on node racnode01
Instance DEMO2 is running on node racnode02

/* stop all the database instances except one (anyone) in RAC config */

% srvctl stop instance -d DEMO -i DEMO2

% export ORACLE_SID=DEMO1

/* put one of the instances in non-cluster mode */

% sqlplus / as sysdba
SQL> alter system set cluster_database=false scope=spfile;

% srvctl stop instance -d DEMO -i DEMO1

% sqlplus / as sysdba
SQL> startup mount

/* enable archive log mode */

SQL> alter database archivelog;

SQL> archive log list
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence        4

SQL> show parameter db_recovery_file

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest       string  +FRA
db_recovery_file_dest_size      big integer 512G

/* enable flashback option */

SQL> alter database flashback on;

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

/* put the instance back in cluster mode and restart the database */

SQL> alter system set cluster_database=true scope=spfile;

SQL> alter database open;

% srvctl stop instance -d DEMO -i DEMO1

% srvctl start database -d DEMO

/* create a guaranteed restore point */

% sqlplus / as sysdba
SQL> create restore point demo_clean_before_test guarantee flashback database;

Restore point created.

SQL> column NAME format A25
SQL> column TIME format A40
SQL> set lines 120
SQL> select NAME, SCN, TIME, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE 
  2  from V$RESTORE_POINT where GUARANTEE_FLASHBACK_DATABASE='YES';

NAME     SCN TIME          GUA STORAGE_SIZE
------------------------- ---------- ---------------------------------------- --- ------------
DEMO_CLEAN_BEFORE_TEST     17460960 21-AUG-11 01.01.20.000000000 AM       YES     67125248

/* flashback database to the saved restore point */

% srvctl stop database -d DEMO

% export ORACLE_SID=DEMO1

% rman TARGET /

RMAN> STARTUP MOUNT;
RMAN> FLASHBACK DATABASE TO RESTORE POINT 'DEMO_CLEAN_BEFORE_TEST';

Starting flashback at 21-AUG-11
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:25

Finished flashback at 21-AUG-11

RMAN> ALTER DATABASE OPEN RESETLOGS;

database opened

RMAN> SHUTDOWN IMMEDIATE;

% srvctl start database -d DEMO

/* ============================================================================== */

/* alternatively run the following RMAN script as shown below */

% cat restore.rman
RUN {
        STARTUP MOUNT;
        FLASHBACK DATABASE TO RESTORE POINT 'DEMO_CLEAN_BEFORE_TEST';
        ALTER DATABASE OPEN RESETLOGS;
        SHUTDOWN IMMEDIATE;
}

EXIT

% rman TARGET / cmdfile=restore.rman

Note:
It is not mandatory to enable flashback database in order to create and use restore points. The requirement in such a case is to put the database in ARCHIVELOG mode and creating the first guaranteed restore point when the database is in mounted state.

Read More

Gene Kelly, the Man Who Can Dance

I'm not a fan of musicals, and it is very unlikely I will ever be. However recently I developed interest in some of the oldies especially the ones starring Gene Kelly just for the sheer amount of entertainment value he brings to the screen. Michael Jackson is my only favorite entertainer all these years. Now Gene Kelly joins that élite with his graceful and magical dancing skills that majority of the current generation is unaware of.

Here are my top 3 picks so far from Gene's performances. Sit back, relax and enjoy! (video courtesy: Google|YouTube).

1. Singin' In The Rain (1952) - Moses Supposes

   
   
   
   

2. Anchors Aweigh (1944) - The Worry Song featuring Jerry Mouse

   
   
   
   

3. Summer Stock (1950) - Newspaper Dance

   
   
   

My recommendation to everyone who loves to watch a nice dance routine - get hold of Gene Kelly's musical films, watch all the dance performances involving Gene and skip the rest of the movie if you don't like to sit around watching a musical.

~

Read More

Be Eco-Responsible. Use Resource Management features in Solaris, Oracle Database and Go Green

This blog post is about a white paper that introduces the resource management features in Oracle Solaris and Oracle Database.

Who needs more hardware when under-utilized systems are sitting idle in datacenters consuming valuable space, energy and polluting environment with carbon emissions. These days virtualization and con solidation are more popular than ever before. And then there is Oracle Corporation with great products consisting rich features for resource manageability in consolidated, virtualized and isolated envir onments. Put together all these pieces what do we get? - a complete green solution that helps make this planet a better place.

In an attempt to increase the awareness of the resource management features available in Oracle Solaris and Oracle Database, we just published a four-part white paper surrounding Oracle Solaris Resource Manager and Oracle Database Resource Manager. Those resource managers are not really a product, process or an add-on to Solaris and Oracle Database. "Resource Manager" is the abstract term for the set of software modules that are integrated into the Solaris operating system and Oracle database management system to facilitate resource management from the OS and RDBMS perspective.

The first part of the series introduces: the concept of resource management, Solaris Resource Manager and Oracle Database Resource Manager; compares and contrasts both of those resource managers andends the discussion with a brief overview of resource management in high-availability (HA) environments. Access this paper from the following URL:

        Part 1: Introduction to Resource Management in Oracle Solaris and Oracle Database

The second part is all about the available resource management features in Oracle Solaris. This includes resource management in virtualized environments too. The range of topics vary from the simple process binding to the dynamic reconfiguration of CPU and memory resources in a logical domain (LDom). The value of this paper lies in the volume of examples that follow each of the introduced feature. Access the second part from the following URL:

        Part 2: Effective Resource Management Using Oracle Solaris Resource Manager

Similar to the second part, the third part talks about the available resource management features in Oracle Database Management System. Majority of the discussion revolves around creating resource pl ans in a Oracle database. This part also contains plenty of examples. Access the third part from the following URL:

        Part 3: Effective Resource Management Using Oracle Database Resource Manager

The final part briefly introduces various hardware/software products that makes Oracle Corporation the ideal consolidation platform. Also an imaginary case study was presented to demonstrate how to consolidate multiple applications and databases onto a single server using Oracle virtualization technologies and the resource management features found in Oracle Solaris and Oracle Database. Description of the consolidation plan and the implementation of the plan takes up major portion of this final part. Access the fourth and final part of this series from the following URL:

        Part 4: Resource Management Case Study for Mixed Workloads and Server Sharing

Happy reading.

Original blog post is at:
http://blogs.oracle.com/mandalika/entry/be_eco_responsible_use_resource

Read More

PeopleSoft Application Server : Binding JSL Port to Multiple IP Addresses

For the impatient:

On any multi-homed¹ host, replace %PS_MACH% variable in "Jolt Listener" section of the application server domains' psappsrv.cfg file wih the special IP address "0.0.0.0" to get the desired effect. It enables TCP/IP stack to listen on all available network interfaces on the system. In other words, if JSL is listening on 0.0.0.0 on a multi-homed system, PIA traffic can flow using any of the IP addresses assigned to that system.

For the rest:

A little background first.

PeopleSoft application server relies on Jolt, a companion product that co-exists with Tuxedo, to handle all web requests. That is, Jolt is the bridge between PeopleSoft application server and the web server (any supported one) that facilitates web communication. Tuxedo helps schedule PeopleSoft application server processes to perform the actual transactions. When the application server is booted up, Jolt server listener (JSL) is bound to a pre-configured port number and is actively monitored for incoming web requests. On the other hand, web server instance(s) are made aware of the existence of all Jolt listeners in a PeopleSoft Enterprise by configuring the hostname:port# pairs in each of the web domain's configuration.properties file.

By default the variable %PS_MACH% in each of the application server domain configuration file, psappsrv.cfg, gets resolved to the hostname of the system during application server boot-up time. The following example demonstrates that.

eg.,

/* Application server configuration file */
% cat psappsrv.cfg
..
[JOLT Listener]
Address=%PS_MACH%
Port=9000
..

/* Boot up the application server domain */
% psadmin -c boot -d HRHX
..
exec JSL -A -- -d /dev/tcp -n //ben01:9000 -m 100 -M 100 -I 5 -j ANY -x 20 -S 10 -c 1000000 -w JSH :
        process id=20077 ... Started.
..

% hostname
ben01

% netstat -a | grep 9000
ben01.9000                 *.*                0      0 49152      0 LISTEN

% netstat -an | grep 9000
17.16.221.106.9000          *.*                0      0 49152      0 LISTEN

% ifconfig -a
lo0: flags=2001000849 mtu 8232 index 1
 inet 127.0.0.1 netmask ff000000 
bge0: flags=1000843 mtu 1500 index 2
 inet 17.16.221.106 netmask ffffff00 broadcast 17.16.221.255
bge1: flags=1000843 mtu 1500 index 3
 inet 18.1.1.1 netmask ffffff00 broadcast 18.1.1.255
e1000g0: flags=1000843 mtu 1500 index 4
 inet 18.1.1.201 netmask ffffff00 broadcast 18.1.1.255

% telnet 17.16.221.106 9000
Trying 17.16.221.106...
Connected to 17.16.221.106.
Escape character is '^]'.

% telnet 18.1.1.1 9000
Trying 18.1.1.1...
telnet: Unable to connect to remote host: Connection refused

% telnet 18.1.1.201 9000
Trying 18.1.1.201...
telnet: Unable to connect to remote host: Connection refused

Notice that %PS_MACH% was replaced by the actual hostname and the Jolt listener created the server socket using the IP address 17.16.221.106 and port number 9000. From the outputs of netstat, ifconfig and telnet, it is apparent that "bge0" is the only network interface that is being used by the Jolt listener. It means web server can communicate to JSL using the IP address 17.16.221.106 over port 9000 but not using any of the other two IP addresses 18.1.1.1 or 18.1.1.201. This is the default behavior.

However some customers may wish to have the ability to connect to the application services from different/multiple networks. This is possible in case of multi-homed systems -- servers with multiple network interfaces that are connected to a single or multiple networks. For example, such a host could be part of a public network, a private network where only those clients that can communicate over private links can connect or an InfiniBand network, a low latency high throughput network. The default behavior of JSL can be changed by using a special IP address "0.0.0.0" in place of the variable %PS_MACH% in application server domains' configuration file. The IP address 0.0.0.0 hints the Jolt listener (JSL) to listen on all available IPv4 network interfaces on the system. (I read somewhere that "::0" is the equivalent for IPv6 interfaces. Didn't get a chance to test it out yet). The following example demonstrates how the default behavior changes with the IP address 0.0.0.0.

% cat psappsrv.cfg
..
[JOLT Listener]
Address=0.0.0.0
Port=9000
..

/* Update the binary configuration by reloading the config file */
% psadmin -c configure -d HRHX

% psadmin -c boot -d HRHX
..
exec JSL -A -- -d /dev/tcp -n //0.0.0.0:9000 -m 100 -M 100 -I 5 -j ANY -x 20 -S 10 -c 1000000 -w JSH :
 process id=20874 ... Started.

% netstat -a | grep 9000
      *.9000               *.*                0      0 49152      0 LISTEN

% telnet 17.16.221.106 9000
Trying 17.16.221.106...
Connected to 17.16.221.106.
..

% telnet 18.1.1.1 9000
Trying 18.1.1.1...
Connected to 18.1.1.1.
..

% telnet 18.1.1.201 9000
Trying 18.1.1.201...
Connected to 18.1.1.201.

Footnote:
[1] Any system with more than one interface is considered a multi-homed host

Read More

Oracle Database: How to Figure Out if a Tablespace is Empty

It is not uncommon for Oracle DBAs to create, drop tablespaces for various reasons throughout the life of a database management system. It is a good practice to double check whether a tablespace is really empty before dropping it. One way is to visually check the "Tablespaces" section in Oracle Enterprise Manager (OEM) database console. However sometimes the graphical interface may return false positives. Another way is to rely on data dictionary views to obtain relevant information as accurately as possible. In the latter case, DBA_SEGMENTS / USER_SEGMENTS views are useful in mapping segments such as table, index, table/index partition etc., to a tablespace.

eg.,

The following example queries USER_SEGMENTS view to list the segments and their types stored in a tablespace called "TS_SALES_DATA32K".

SQL> COLUMN SEGMENT_NAME FORMAT A30
SQL> COLUMN SEGMENT_TYPE FORMAT A30
SQL> 
SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE
  2  FROM USER_SEGMENTS
  3  WHERE TABLESPACE_NAME = 'TS_SALES_DATA32K';

SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------------------
SALES_DATA                     TABLE

The tablespace "TS_SALES_DATA32K" is holding one table called "SALES_DATA". The following query returns no rows meaning tablespace "TS_SALES_DATA" is empty - hence it can be dropped with no hesitation.

SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE         
  2  FROM USER_SEGMENTS       
  3  WHERE TABLESPACE_NAME = 'TS_SALES_DATA';

no rows selected

Here is another query that lists out all the tablespaces in a database along with the number of segments/objects stored in each of those tablespaces. Note that it is possible to extract similar information in different ways using more efficient queries.

SQL> COLUMN TABLESPACE FORMAT A40
SQL> 
SQL> SELECT UT.TABLESPACE_NAME "TABLESPACE", COUNT (US.SEGMENT_NAME) "NUM SEGMENTS"
  2  FROM USER_TABLESPACES UT, USER_SEGMENTS US
  3  WHERE UT.TABLESPACE_NAME = US.TABLESPACE_NAME
  4  GROUP BY (UT.TABLESPACE_NAME)
  5  ORDER BY COUNT (US.SEGMENT_NAME) DESC;

TABLESPACE                               NUM SEGMENTS
---------------------------------------- -----------
TS_DP                                         114989
TS_DP_X                                          306
..
TS_SALES_DATA32K                                   1
TS_SALES_DATA                                      0

13 rows selected.

SEE ALSO:
Oracle Database Concepts : Data Blocks, Extents, and Segments

Read More

Oracle Solaris: Show Me the CPU, vCPU, Core Counts and the Socket-Core-vCPU Mapping

[Replaced old code with new code on 10/03/11]

It should be easy to find this information just by running an OS command. However for some reason it ain't the case as of today. The user must know few details about the underlying hardware and run multiple commands to figure out the exact number of physical processors, cores etc.,

For the benefit of our customers, here is a simple shell script that displays the number of physical processors, cores, virtual processors, cores per physical processor, number of hardware threads (vCPUs) per core and the virtual CPU mapping for all physical processors and cores on a Solaris system (SPARC or x86/x64). The script showed valid output on recent T-series, M-series hardware as well as on some older hardware - Sun Fire 4800, x4600. Due to the changes in the output of cpu_info over the years, it is possible that the script may return incorrect information in some cases. Since it is just a shell script, tweak the code as you like. The script can be executed as any OS user.

Download the script: showcpucount

% cat showcpucount

--------------------------------------- CUT HERE -------------------------------------------

#!/bin/bash

/usr/bin/kstat -m cpu_info | egrep "chip_id|core_id|module: cpu_info" > /var/tmp/cpu_info.log

nproc=`(grep chip_id /var/tmp/cpu_info.log | awk '{ print $2 }' | sort -u | wc -l | tr -d ' ')`
ncore=`(grep core_id /var/tmp/cpu_info.log | awk '{ print $2 }' | sort -u | wc -l | tr -d ' ')`
vproc=`(grep 'module: cpu_info' /var/tmp/cpu_info.log | awk '{ print $4 }' | sort -u | wc -l | tr -d ' ')`

nstrandspercore=$(($vproc/$ncore))
ncoresperproc=$(($ncore/$nproc))

speedinmhz=`(/usr/bin/kstat -m cpu_info | grep clock_MHz | awk '{ print $2 }' | sort -u)`
speedinghz=`echo "scale=2; $speedinmhz/1000" | bc`

echo "Total number of physical processors: $nproc"
echo "Number of virtual processors: $vproc"
echo "Total number of cores: $ncore"
echo "Number of cores per physical processor: $ncoresperproc"
echo "Number of hardware threads (strands or vCPUs) per core: $nstrandspercore"
echo "Processor speed: $speedinmhz MHz ($speedinghz GHz)"

# now derive the vcpu-to-core mapping based on above information #

echo -e "\n** Socket-Core-vCPU mapping **"
let linenum=2

for ((i = 1; i <= ${nproc}; ++i ))
do
        chipid=`sed -n ${linenum}p /var/tmp/cpu_info.log | awk '{ print $2 }'`
        echo -e "\nPhysical Processor $i (chip id: $chipid):"

        for ((j = 1; j <= ${ncoresperproc}; ++j ))
        do
                let linenum=($linenum + 1)
                coreid=`sed -n ${linenum}p /var/tmp/cpu_info.log | awk '{ print $2 }'`
                echo -e "\tCore $j (core id: $coreid):"

                let linenum=($linenum - 2)
                vcpustart=`sed -n ${linenum}p /var/tmp/cpu_info.log | awk '{ print $4 }'`

                let linenum=(3 * $nstrandspercore + $linenum - 3)
                vcpuend=`sed -n ${linenum}p /var/tmp/cpu_info.log | awk '{ print $4 }'`

                echo -e "\t\tvCPU ids: $vcpustart - $vcpuend"
                let linenum=($linenum + 4)
        done
done

rm /var/tmp/cpu_info.log
--------------------------------------- CUT HERE -------------------------------------------

# prtdiag | head -1
System Configuration:  Sun Microsystems  sun4u SPARC Enterprise M4000 Server

# ./showcpucount
Total number of physical processors: 4
Number of virtual processors: 32
Total number of cores: 16
Number of cores per physical processor: 4
Number of hardware threads (strands or vCPUs) per core: 2
Processor speed: 2660 MHz (2.66 GHz)

** Socket-Core-vCPU mapping **

Physical Processor 1 (chip id: 1024):
        Core 1 (core id: 0):
                vCPU ids: 0 - 1
        Core 2 (core id: 2):
                vCPU ids: 2 - 3
        Core 3 (core id: 4):
                vCPU ids: 4 - 5
        Core 4 (core id: 6):
                vCPU ids: 6 - 7

Physical Processor 2 (chip id: 1032):
        Core 1 (core id: 8):
                vCPU ids: 8 - 9
        Core 2 (core id: 10):
                vCPU ids: 10 - 11
        Core 3 (core id: 12):
                vCPU ids: 12 - 13
        Core 4 (core id: 14):
                vCPU ids: 14 - 15

Physical Processor 3 (chip id: 1040):
        Core 1 (core id: 16):
                vCPU ids: 16 - 17
        Core 2 (core id: 18):
                vCPU ids: 18 - 19
        Core 3 (core id: 20):
                vCPU ids: 20 - 21
        Core 4 (core id: 22):
                vCPU ids: 22 - 23

Physical Processor 4 (chip id: 1048):
        Core 1 (core id: 24):
                vCPU ids: 24 - 25
        Core 2 (core id: 26):
                vCPU ids: 26 - 27
        Core 3 (core id: 28):
                vCPU ids: 28 - 29
        Core 4 (core id: 30):
                vCPU ids: 30 - 31

Read More