Mandalika's scratchpad

Solaris Tip: How-To Identify Memory Mapped Files

2012-01-17T18:46:00.000-08:00

A memory mapped (mmap'd) file is a shared memory object, or a file with some portion or the whole file was mapped to virtual memory segments in the address space of an OS process. Here is one way to figure out if a given object (file or shared memory object) was memory mapped in a process or not.

find the file system inode number of the object
look for that inode number in the address space of a given process

And here is an example. We are about to check a log file and a shared memory segment in a Siebel object manager's process address space.


# pfiles 8251
8251:   siebmtshmw /siebel/siebsrvr/admin/Siebel81.isve02.s
..
   1: S_IFREG mode:0744 dev:256,65539 ino:246660 uid:1234 gid:30 size:0
      O_WRONLY|O_APPEND|O_CREAT
      /siebel/siebsrvr/enterprises/Siebel81/isve02/log/StdErrOut/stderrout_8251_23311913.log
...
   9: S_IFREG mode:0700 dev:256,65539 ino:246640 uid:1234 gid:30 size:6889472
      O_RDWR|O_CREAT|O_EXCL
      /siebel/siebsrvr/admin/Siebel81.isve02.shm
..

# pmap -sx 8251 | grep 246660    
#               <== stderrout_8251_23311913.log file was not a memory mapped file

# pmap -sx 8251 | grep 246640
F6400000      64      64       -       -   8K r--s-  dev:256,65539 ino:246640   
F6410000     136     136       -       -    - r--s-  dev:256,65539 ino:246640
F6432000     128     128       -       -   8K r--s-  dev:256,65539 ino:246640
...
                <== Siebel81.isve02.shm was a memory mapped object

Unwanted Software Installers

2012-01-04T23:43:00.001-08:00

After all these years of software evolution, it is odd to see not much improvement in the area of software installation. Customers do not seem to mind dealing with different, complex installers. Nevertheless this whole process can be simplified to save time, effort and energy.

In an ideal world, a software installer is supposed to have just one function - copying the software bits to a designated location and nothing else. However today we interact with different installers that does variety of things -- some install the pre-compiled binaries, few come in ready-to-extract zip archives, few others compile the binary on-the-fly and install the binary. Most of the enterprise software installers configure the software as part of the installation process where as few installers install the software and simply quit leaving the configuration step for the experts. Some of the installers hard-code the hostname, IP address, absolute paths of certain files etc., into some of the files on target system, which makes it hard to re-use the software home directory on a different server. Few installers do sensible job by not tying anything to the host system where the software is being installed.

Here is my partial wish list of features for a software installer. I think it is enough to make a point.

— Idempotent installations : install the software once, run anywhere. Customers should be able to move the resulting home directories from one host to any location on another host without worrying about the underlying changes to the location of the home directory, hostname, IP address etc., One example is the Oracle RDBMS installation. Once installed, the ORACLE_HOME can be zipped up, moved to another host, extracted and used right away. ORACLE_HOME usually contains the binaries. Installation specific configuration is stored outside of ORACLE_HOME. Optional Oracle Grid Control configuration appears to be saved under ORACLE_HOME, which is an aberration though it can be easily reconfigured once moved to another host.

— Simplicity : providing the entire directory structure in an extractable compressed archive file will remove one or more layers of dependency that the software installer has. For example, some of the installers require Java run-time to show the graphical interface for the installer. I recently encountered an installer executable that has private/unsupported symbols statically linked to it. When those private interfaces were removed in a later version of the operating environment, installer crashed and failed to make any progress. Had the software been provided in an extractable archive, software would have been readily available in the latter case. It appears that Oracle Corporation is moving in the right direction by releasing WebLogic 12c software as a zip file.

— De-couple software installation from configuration : there should be clear separation between the installation and configuration. Once the software is in place, relevant folks can always configure the software as directed and needed. The customer just needs a simple tool or script to configure the software.

=> Off-topic: providing a web interface is even better. It gives the flexibility to configure the software from anywhere in the same network.

— Contain everything in a single top-level directory : it makes patching easier even if the top-level directory was moved to a different location or host. No point in spreading the pieces of software into multiple locations anyway. Going back to the example of ORACLE_HOME, one shortcoming in Oracle RDBMS installation is that few directories/files such as oraInventory reside outside of ORACLE_HOME - so, when moving ORACLE_HOME to another host, it is necessary to move all relevant files that are outside of ORACLE_HOME as well for successful database software patching.

With careful planning/design, Release Engineering can be as creative and innovative as the rest of the teams in delivering a software product out of the door --- but I guess it is up to the customers to demand that attitude.

PS:
This blog post can be improved a lot. However since it is mostly about an opinion and a wish list, there is not much motivation to put more effort into it. And of course it is a generic discussion - nothing specific to a particular software or corporation.

Oracle Application Testing Suite (OATS): Few Tips & Tricks

2011-12-27T15:14:00.001-08:00

OATS is a suite of applications that can be used for performance and scalability testing, functional and regression testing. It is a thin client application that runs within a web browser - so, it is easy to use the tool from anywhere as long as the web server running on the host node is accessible. Hopefully the following tips and tricks will benefit some of the users of the Oracle Application Testing Suite.

Few technical details first - OATS is a 32-bit Java application that runs in a WebLogic container (WLS) with Oracle XE database being the backend store for test session data.

[Trick] Issue : OATS software fails to install on 64-bit Windows systems

Resolution:
Download and install 64-bit .NET framework manually before installing the OATS software. Look for .NET framework on Microsoft's downloads website.

[Trick] Issue : OATS software fails to install on systems with large number of [virtual] CPUs

Resolution:
On systems with many cores/vCPUs, Oracle database in general requires large amounts of memory to be configured for SGA - so, one solution would be to allocate as much memory as required. However Oracle XE limits the memory utilization within the database to 1 GB. Besides, XE uses only one CPU even if there are multiple CPUs available on a system. Hence one workaround is to limit the number of vCPUs that the system exposes during the installation of OATS software. The steps are shown below.

Start button -> Run -> type "msconfig"
Click on Boot tab -> Advanced Options
Check "Number of processors" and set appropriate value (I believe we can go up to 16)
Reboot Windows
Uninstall failed OATS installation and try installing again
Undo the above made changes after the successful installation of OATS
Reboot Windows one final time

Thanks to my colleague Bao Doan for providing this workaround.

[Trick] Issue : During runtime, OATS drive the load and executes the test as expected but fails to collect runtime statistics

Resolution:
This is another limitation of Oracle XE database. Until 10g, XE limits the maximum amount of user data in the database to 4 GB. This limit was raised to 11 GB in release Oracle 11g XE. OATS 9.x releases bundle Oracle 10g XE. To take advantage of the larger limit for data, install Oracle 11g XE manually before installing OATS software. OATS installer gives the option to use an existing installation of Oracle XE. Besides, it is not possible to have multiple Oracle XE installations on a single box anyway (that's another XE limitation).

For existing installations, one workaround is to remove old and unwanted sessions to make room for new sessions in the database. Listed below are the steps.

Connect to the Oracle Load Testing (OLT) tool
Click on "Manage" top-level menu (upper right corner) -> Sessions
Click on any unwanted session and press "Delete" button (I recommend deleting one session at a time)

[Trick] Issue : Under load, there are many network timeouts with ton of sockets in TIME_WAIT state on OATS agent systems including the OATS Controller node

Resolution:
Tune TCP/IP parameters on Windows as shown below.

Launch Windows registry
Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\TcpIP\parameters
Configure the following two parameters. If not found, create those parameters by selecting Edit -> New -> DWORD Value from the menu bar. Select "Decimal" under Base.
Reboot Windows

Thanks to my colleagues Dino and Vishnu for sharing this workaround.

[Trick] Issue : OATS Controller does not show any graphs or analysis reports

Resolution:
Install Adobe Flash Plugin and try again.

[Trick] Issue : Under load, OATS Controller stops collecting runtime statistics at some random point

Resolution:
Check Oracle database alert log for some clue(s). If there is an error message such as "ORA-12516: TNS:listener could not find available handler with matching protocol stack", connect to the database, query v$resource_limit view and compare the values reported under CURRENT_UTILIZATION and MAX_UTILIZATION for the resource "processes". If the current utilization is pretty close to the configured maximum value, raise the value for processes parameter in [S]PFILE.

[Tip] Balancing the load among multiple OATS agent systems

One simple way is to create a VU Agent System Group based on the available agent systems. Steps listed below.

Connect to the Oracle Load Testing (OLT) tool
Click on "Manage" top-level menu (upper right corner) -> Systems
Click on "VU Agent System Group" in the left hand side
On the right hand side, click on "New" option
Select all the agent systems that you want to be part of the "VU Agent System Group"
Finally name the newly created system group and save

Note that it is not possible to attach weights to the agent systems - so, it is suggested to have agent systems with similar hardware configurations in the VU Agent System Group.

[Tip] Balancing the load among multiple web servers using OATS Controller

If there are multiple web server instances running in a enterprise application deployment; and OATS software is being used to test the performance and scalability of the application, parameterizing the web server hostname and port number in OATS test script will take care of the web server load balancing problem. Of course there are many alternatives to this approach such as using a hardware load balancer, using web server Reverse Proxy etc.,

[Added on 01/19/2012]

[Tip] How-To check the available space in USERS tablespace?

Run the following on OATS Controller node:

Start -> All Programs -> Oracle Database XX Express Edition -> Run SQL Command Line


SQL> connect / as sysdba

SQL> SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;

Copy/paste the above SQL code in a text file with sql extension and execute that SQL statement by calling the SQL script from SQL> command prompt. eg., assuming the above code was saved in a plain text file called chktblspcusg.sql under C:\ drive, execute the SQL script as shown below:

SQL> @C:\chktblspcusg.sql

Solaris Tip: Resolving "statd: cannot talk to statd at , RPC: Timed out(5)"

2011-12-13T19:45:00.000-08:00

Symptom:

System log shows a bunch of RPC timed out messages such as the following:



Dec 13 09:23:23 gil08 last message repeated 1 time
Dec 13 09:29:14 gil08 statd[19858]: [ID 766906 daemon.warning] statd: cannot talk to statd at ssc23, RPC: Timed out(5)
Dec 13 09:35:05 gil08 last message repeated 1 time
Dec 13 09:40:56 gil08 statd[19858]: [ID 766906 daemon.warning] statd: cannot talk to statd at ssc23, RPC: Timed out(5)
..

Those messages are the result of an apparent communication failure between the status daemons (statd) of both local and remote hosts using RPC calls.

Workaround/Solution:

If the target_host is reachable, execute the following to stop the system from generating those warning messages --- stop the network status monitor, remove the target host entry from /var/statmon/sm.bak file and start the network status monitor process. Removing the target host entry from sm.bak file keeps that machine from being aware that it may have to participate in locking recovery.

eg.,


# ps -eaf | fgrep statd 
  daemon 14304 19622   0 09:47:16 ?           0:00 /usr/lib/nfs/statd
    root 14314 14297   0 09:48:03 pts/15      0:00 fgrep statd

# svcs -a | grep "nfs/status"
online          9:52:41 svc:/network/nfs/status:default

# svcadm -v disable nfs/status
svc:/network/nfs/status:default disabled.

# ls /var/statmon/sm.bak
ssc23

# rm /var/statmon/sm.bak/ssc23

# svcadm -v enable nfs/status
svc:/network/nfs/status:default enabled.

Siebel Troubleshooting : An ODBC error occurred; SBL-GEN-03006: Error calling function: DICFindTable m_pReqTbl

2011-11-18T15:37:00.001-08:00

Symptom:

A newly installed Siebel application server fails to start despite successful ODBC connectivity to the database. SRProc process logs ODBC error messages similar to the following:


Message: GEN-13,
 Additional Message: dict-ERR-1109: 
       Unable to read value from export file (Data length (32) > Column definition (3)).

Message: GEN-13,
 Additional Message: dict-ERR-1107: Unable to read row 0 from export file (UTLDataValRead pBuf, col 4 ).

GenericLog  GenericError  1     0002157..  11-11-18 13:28  Message: Generated SQL statement:,
 Additional Message: SQLFetch:
   SELECT RDOBJ.DOCK_ID, RDOBJ.RELATED_DOCK_ID, RDOBJ.SQL_STATEMENT, RDOBJ.CHECK_VISIBILITY,
          'N', RDOBJ.COMMENTS, RDOBJ.ACTIVE, RDOBJ.SEQUENCE, RDOBJ.VIS_STRENGTH,
          RDOBJ.REL_VIS_STRENGTH, RDOBJ.VIS_EVT_COLS
     FROM ORAPERF.S_DOCK_REL_DOBJ RDOBJ, ORAPERF.S_DOCK_OBJECT DOBJ
    WHERE RDOBJ.REPOSITORY_ID = (SELECT ROW_ID FROM ORAPERF.S_REPOSITORY WHERE NAME = ?)
      AND DOBJ.ROW_ID = RDOBJ.DOCK_ID
      AND (DOBJ.INACTIVE_FLG = 'N' OR DOBJ.INACTIVE_FLG IS NULL)
      AND (RDOBJ.INACTIVE_FLG = 'N' OR RDOBJ.INACTIVE_FLG IS NULL)

Message: Error: An ODBC error occurred,
 Additional Message: Function: DICGetRDObjects; ODBC operation: SQLFetch

Message: GEN-13,
 Additional Message: dict-ERR-1109: Unable to read value from export file (UTLCompressFRead (fseek)).

Message: GEN-13,
 Additional Message: dict-ERR-1107: Unable to read row 0 from export file (UTLDataValRead pBuf, col 0 ).

Message: GEN-10,
 Additional Message: Calling Function: DICLoadDObjectInfo; Called Function: Calling DICGetRDObjects

Message: GEN-10,
 Additional Message: Calling Function: DICLoadDict; Called Function: DICLoadDObjectInfo

GenericError
(srpdb.cpp (860) err=3006 sys=2) SBL-GEN-03006: Error calling function: DICFindTable m_pReqTbl
(srpsmech.cpp (74) err=3006 sys=0) SBL-GEN-03006: Error calling function: DICFindTable m_pReqTbl
(srpmtsrv.cpp (107) err=3006 sys=0) SBL-GEN-03006: Error calling function: DICFindTable m_pReqTbl
(smimtsrv.cpp (1203) err=3006 sys=0) SBL-GEN-03006: Error calling function: DICFindTable m_pReqTbl
SmiLayerLog Error       Terminate process due to unrecoverable error: 3006. (Main Thread)

An inconsistent or corrupted dictionary file "diccache.dat" is likely the cause.

Solution:

Stop the application server and manually kill the remaining Siebel application specific processes
eg.,
```
stop_server all

pkill siebmtsh
pkill siebproc
..
```
Remove $SIEBEL_HOME/bin/diccache.dat file. It will be re-generated during the application server startup
Start the application server
```
start_server all
```

Oracle Database on NFS : Resolving "ORA-27086: unable to lock file - already in use" Error

2011-10-10T17:45:00.000-07:00

Some Context

Oracle database was hosted on ZFS Storage Appliance (NAS). The database files are accessible from the database server node via NFS mounted filesystems. Solaris 10 is the operating system on DB node.

Someone forgets to shutdown the database instance and unmount the remote filesystems before rebooting the database server node. After the system boots up, Oracle RDBMS fails to bring up the database due to locked-out data files.

eg.,


SQL> startup
ORACLE instance started.

Total System Global Area 1.7108E+10 bytes
Fixed Size      2165208 bytes
Variable Size   9965671976 bytes
Database Buffers  6845104128 bytes
Redo Buffers    295329792 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/orclvol4/entDB/system01.dbf'

======================
Extract from alert log:
======================

...
ALTER DATABASE OPEN
Fri Aug 05 21:30:54 2011
Errors in file /oracle112/diag/rdbms/entdb/entDB/trace/entDB_dbw0_7235.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/orclvol4/entDB/system01.dbf'
ORA-27086: unable to lock file - already in use
SVR4 Error: 11: Resource temporarily unavailable
Additional information: 8
Additional information: 21364
Errors in file /oracle112/diag/rdbms/entdb/entDB/trace/entDB_dbw0_7235.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/orclvol4/entDB/sysaux01.dbf'
ORA-27086: unable to lock file - already in use
SVR4 Error: 11: Resource temporarily unavailable
Additional information: 8
Additional information: 21364
...

Reason for the lock failure:

Because of the sudden ungraceful shutdown of the database, file locks on data files were not released by the NFS server (ZFS SA in this case). NFS server held on to the file locks even after the NFS client (DB server node in this example) was restarted. Due to this, Oracle RDBMS is not able to lock those data files residing on NFS server (ZFS SA). As a result, database instance was failed to start up in exclusive mode.

Workaround

Manually clear the NFS locks as outlined below.

On NFS Client (database server node):

Shutdown the mounted database
Unmount remote (NFS) filesystems

Execute: clear_locks -s <nfs_server_host>

eg.,


# clear_locks -s sup16
Clearing locks held for NFS client ipsedb1 on server sup16
clear of locks held for ipsedb1 on sup16 returned success

On NFS Server (ZFS SA):
(this step may not be necessary but wouldn't hurt to perform)

Execute: clear_locks <nfs_client_host>

eg.,


sup16# clear_locks 10.129.207.93
Clearing locks held for NFS client 10.129.207.93 on server sup16
clear of locks held for 10.129.207.93 on sup16 returned success

Again back on NFS Client (database server node):

Restart NFS client
(this step may not be necessary but wouldn't hurt to perform)
```
# svcadm -v disable nfs/client
# svcadm -v enable nfs/client
```
Mount remote/NFS filesystems
Finally start the database

Also see:
Listing file locks on Solaris 10

Siebel Connection Broker Load Balancing Algorithm

2011-10-06T12:38:00.000-07:00

Siebel server architecture supports spawning multiple application object manager processes. The Siebel Connection Broker, SCBroker, tries to balance the load (incoming requests) across different object manager processes running in a single Siebel server.

Least Loaded or Round Robin?

By default, SCBroker forwards the incoming request to any object manager process that is least loaded - meaning the process with the least number of running tasks. In Siebel terminology, this behavior is referred as "least-loaded" or "LL" connection forwarding algorithm. While the default LL algorithm provides the optimal behavior in the best case scenarios, it may lead to serious availability problems if one of several object manager prcesses running in a Siebel server stops responding in a timely fashion [for some reason]. Such an object manager may still accept requests though it may timeout. At some point, the unresponsive/hung or erroneous object manager will have the least number of tasks that may prompt SCBroker component to forward new incoming requests to that object manager process - which in turn leads to a stalemate. To avoid such situations, it is recommended to configure "round-robin" or "RR" algorithm in SCBroker component. When round-robin algorithm is configured, SCBroker ignores the number of running tasks per object manager process and routes all requests to all object managers in a round robin fashion.

While both algorithms have their strengths and weaknesses, customers must weigh both options and choose the one that fits best in their deployment.

eg.,

Find the current load balancing algorithm:


srvrmgr>  list advanced param ConnForwardAlgorithm for comp SCBroker show PA_ALIAS, PA_VALUE, PA_NAME

PA_ALIAS              PA_VALUE  PA_NAME                                    
--------------------  --------  -----------------------------------------  
ConnForwardAlgorithm  LL        Connection Forward algorithm for SCBroker

Configure SCBroker to use round-robin algorithm:


srvrmgr> change param ConnForwardAlgorithm=RR for comp SCBroker server SERVER_NAME
Command completed successfully.

srvrmgr> list advanced param ConnForwardAlgorithm for comp SCBroker show PA_ALIAS, PA_VALUE, PA_NAME

PA_ALIAS              PA_VALUE  PA_NAME                                    
--------------------  --------  -----------------------------------------  
ConnForwardAlgorithm  RR        Connection Forward algorithm for SCBroker

Other SCBroker parameters of interest: ConnForwardTimeout and ConnRequestTimeout

Oracle RDBMS : Generic Large Object (LOB) Performance Guidelines

2011-09-10T22:11:00.001-07:00

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 strongly 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 off 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

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

2011-08-28T03:42:00.001-07:00

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

Gene Kelly, the Man Who Can Dance

2011-08-09T21:19:00.000-07:00

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).

Singin' In The Rain (1952) - Moses Supposes
Anchors Aweigh (1944) - The Worry Song featuring Jerry Mouse
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.

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

2011-07-02T00:53:00.000-07:00

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 consolidation 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 environments. 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 simpleprocess 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 plans 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

PeopleSoft Application Server : Binding JSL Port to Multiple IP Addresses

2011-05-28T01:11:00.000-07:00

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

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

2011-05-24T00:52:00.000-07:00

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.

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

2011-04-12T00:40:00.001-07:00

[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

Sri Satyanarayana Vratamu in Telugu Script

2011-03-10T23:27:00.000-08:00

శ్రీ సత్యనారాయణ వ్రతము తెలుగులో.

Part 1 : Vratakalpamu (వ్రతకల్పము)
Part 2 : Kadha, Vishnu Sahasra Naamaavali (కధ, విష్ణు సహస్ర నామావళి)

Download original documents and rotate counterclockwise for better readability.

Courtesy/Publisher: గొల్లపూడి వీర సామీ సన్, రాజమండ్రి

See Also:
Hanuman Chalisa (హనుమాన్ చాలీసా) and Sandhya Vandanam (సంధ్యా వందనము) in Telugu Script

PeopleSoft Financials 9.0 (Day-in-the-Life) Benchmark on Oracle Sun

2011-02-01T20:43:00.000-08:00

It is very rare to see any vendor publishing a benchmark on competing products of their own let alone products that are 100% compatible with each other. Well, it happened at Oracle|Sun. M-series and T-series hardware was the subject of two similar / comparable benchmarks; and PeopleSoft Financials 9.0 DIL was the benchmarked workload.

Benchmark report URLs

	PeopleSoft Financials 9.0 on Oracle's SPARC T3-1 Server
	PeopleSoft Financials 9.0 on Oracle's Sun SPARC Enterprise M4000 Server

Brief description of workload

The benchmark workload simulated Financial Control and Reporting business processes that a customer typically performs when closing their books at period end. "Closing the books" generally involves Journal generation, editing & posting; General Ledger allocations, summary & consolidations and reporting in GL. The applications that were involved in this process are: General Ledger, Asset Management, Cash Management, Expenses, Payables and Receivables.

The benchmark execution simulated the processing required for closing the books (background batch processes) along with some online concurrent transaction activity by 1000 end users.

Summary of Benchmark Test Results

The following table summarizes the test results of the "close the books" business processes. For the online transaction response times, check the benchmark reports (too many transactions to summarize here). Feel free to draw your own conclusions.

As of this writing no other vendor published any benchmark result with PeopleSoft Financials 9.0 workload.

(If the following table is illegible, click here for cleaner copy of test results.)

Hardware Configuration

Elapsed Time

Journal Lines per Hour

Ledger Lines per Hour

Batch only

Batch + 1K users

Batch only

Batch + 1K users

Batch only

Batch + 1K users

DB + Proc Sched	1 x Sun SPARC Enterprise M5000 Server 8 x 2.53 GHz QC SPARC64 VII processors, 128 GB RAM
App + Web	1 x SPARC T3-1 Server 1 x 1.65 GHz 16-Core SPARC T3 processor, 128 GB RAM

24.15m
Reporting: 11.67m

25.03m
Reporting: 11.98m

6,355,093

6,141,258

6,209,682

5,991,382

DB + Proc Sched	1 x Sun SPARC Enterprise M5000 Server 8 x 2.66 GHz QC SPARC64 VII+ processors, 128 GB RAM
App + Web	1 x Sun SPARC Enterprise M4000 Server 4 x 2.66 GHz QC SPARC64 VII+ processors, 128 GB RAM

21.74m
Reporting: 11.35m

23.30m
Reporting: 11.42m

7,059,591

6,597,239

6,898,060

6,436,236

Software Versions

Oracle’s PeopleSoft Enterprise Financials/SCM 9.00.00.331
Oracle’s PeopleSoft Enterprise (PeopleTools) 8.49.23 64-bit
Oracle’s PeopleSoft Enterprise (PeopleTools) 8.49.23 32-bit on Windows Server 2003 SP2 for generating reports using nVision
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 64-bit + RDBMS patch 9699654
Oracle Tuxedo 9.1 RP36 Jolt 9.1 64-bit
Oracle WebLogic Server 9.2 MP3 64-bit (Java version "1.5.0_12")
MicroFocus Server Express 4.0 SP4 64-bit
Oracle Solaris 10 10/09 and 09/10

Acknowledgments

It is one of the complex and stressful benchmarks that I have ever been involved in. It is a collaborative effort from different teams within Oracle Corporation. A sincere thanks to the PeopleSoft benchmark team for providing adequate support throughout the execution of the benchmark and for the swift validation of benchmark results numerous times (yes, "numerous" - it is not a typo.)

(Original post is at:
http://blogs.sun.com/mandalika/entry/peoplesoft_financials_9_0_day)

Oracle 11g : Poor Performance Accessing V$SESSION_FIX_CONTROL

2011-01-10T19:07:00.001-08:00

PeopleSoft HCM, Financials/SCM 9.x customers may have to patch their Oracle database server with RDBMS patch 9699654. Rest of the Oracle customers: read the symptoms and decide.

In couple of PeopleSoft deployments it is observed that the following SQL is the top query when all queries are sorted by elapsed time or CPU time. 11.2.0.1.0 is the Oracle database server version.



SELECT VALUE FROM V$SESSION_FIX_CONTROL WHERE BUGNO = :B1 AND SESSION_ID = USERENV('SID')

The target query is being executed thousands of times. The poor performance is due to the lack of a proper index. Here is the explain plan that exhibits the performance issue.


-------------------------------------------------------------------------------
| Id  | Operation  | Name       | Starts | E-Rows | A-Rows |   A-Time   |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |      1 | |      1 |00:00:00.02 |
|*  1 |  FIXED TABLE FULL| X$QKSBGSES |      1 |      1 |      1 |00:00:00.02 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("BUGNO_QKSBGSEROW"=:B1 AND
       "SID_QKSBGSEROW"=USERENV('SID') AND "INST_ID"=USERENV('INSTANCE')))

20 rows selected.

Oracle Corporation accepted this behavior as a bug and agreed to fix in Oracle RDBMS 12.1. Meanwhile an RDBMS patch was made available to the customers running 11.2.0.1 or later. 9699654 is the bug# (Bad performance of V$SESSION_FIX_CONTROL query) - so, Solaris SPARC customers can download the RDBMS patch 9699654 directly from the support web site. Customers on other platforms: please search the bug database and support web site with appropriate keywords.

After applying the RDBMS patch 9699654, the optimizer was using an index and the query performance was improved as expected. Also the target SQL query was no longer the top SQL - in fact, no references to this particular query were found in the AWR report. The new explain plan is shown below.


----------------------------------------------------------------------------------------------
| Id  | Operation               | Name               | Starts | E-Rows | A-Rows |   A-Time   |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                    |      1 |        |      1 |00:00:00.01 |
|*  1 |  FIXED TABLE FIXED INDEX| X$QKSBGSES (ind:1) |      1 |      1 |      1 |00:00:00.01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("BUGNO_QKSBGSEROW"=:B1 AND "SID_QKSBGSEROW"=USERENV('SID') AND
              "INST_ID"=USERENV('INSTANCE')))

20 rows selected.

Oracle's Optimized Solution for Siebel CRM 8.1.1

2010-12-11T00:25:00.001-08:00

A brief explanation of what an optimized solution is and what it is not can be found in the previous blog entry Oracle's Optimized Solution for PeopleSoft HCM 9.0. We went through a similar exercise to publish another optimized solution around Siebel CRM 8.1.1.

The Siebel solution implements Oracle Siebel CRM using a unique combination of SPARC servers, Sun storage, Solaris OS virtualization, Oracle application middleware and Oracle database products.

URLs to the Siebel CRM white papers:

White you are at it, do not forget to check the 13,000 user Siebel CRM benchmark on the latest SPARC T3 platform.

Oracle's Optimized Solution for PeopleSoft HCM 9.0

2010-12-11T00:24:00.000-08:00

According to Oracle Corporation: Oracle's optimized solutions are applications-to-disk solutions that are comprised of Oracle's Sun servers, storage, and networking components, Oracle Solaris, Oracle Enterprise Linux, Oracle Database, Oracle Fusion Middleware and Oracle Applications.

To be clear, an optimized/optimal solution is neither a software package nor a hardware system bundled with pre-tuned software. It is simply a set of recommendations based on some testing performed in labs. The recommendations typically provide sizing guidelines for small, medium and large configurations, best practices, tuning tips and some performance data. Customers can refer to these guidelines when deploying enterprise applications on Oracle hardware to achieve optimal configuration for better TCO and ROI.

The PeopleSoft solution implements two modules in Oracle PeopleSoft Enterprise Human Capital Management (HCM) 9.0 to demonstrate how Oracleʼs servers, disk storage and advanced flash based storage technology can be used to accelerate database transactions to achieve unprecedented application performance. Workload consolidation is achieved through server consolidation while maintaining the appropriate balance of performance, availability, cost and expected future capacity requirements.

The optimized solution technical white paper can be accessed from the following URL:

Oracleʼs Optimized Solution for PeopleSoft Human Capital Management Consolidation using M-series servers, Flash and Enterprise Storage

A corresponding solution brief targeting less patient is available at:

Oracle's Optimized Solution for PeopleSoft HCM - A Business White paper

(Original blogpost location:
http://blogs.sun.com/mandalika/entry/oracle_s_optimized_solution_for)

Instructions to Turn ON/OFF Hardware Prefetch on SPARC64 Systems

2010-12-02T00:58:00.000-08:00

The hardware prefetch is ON by default on M-series servers such as M8000/M9000, M4000/M5000, M3000

The following excerpt is from a SPARC64 document:

Hardware speculatively issues the prefetch operation based on the prediction that there is high possibility to access to the following continuous address in the future, if there have been load accesses for a consecutive address.

Although this feature is designed to improve the performance of various workloads, due to the speculative nature, not all workloads may benefit with the default behavior. For example, in our experiments, we noticed 10+% improvement in CPU utilization while running some of the PeopleSoft workloads on M-series hardware with hardware prefetch turned off. Hence irrespective of the application/workload, the recommended approach is to conduct few experiments by running representative customer workloads on target M-series hardware with and without the hardware prefetch turned on.

Instructions to Turn On/Off Hardware Prefetch:

Connect to the system Service Processor (XSCF)
```
% ssh -l <userid> <host>
```
Check the current prefetch mode by running the following command at XSCF> prompt
```
XSCF> showprefetchmode
```
Find the domain id of all mounted system boards (or skip to next step)
```
XSCF> showboards -a
```
Power-off all configured domains
```
XSCF> poweroff -d <domainid> [OR]
XSCF> poweroff -a
```
From my experience, on larger systems with multiple domains configured, all domains must be powered off before the SP lets changing the prefetch mode. If someone has a correction to this information or better instruction that minimizes disruption, please let me know. I'd be happy to update these instructions.
Wait until the domain(s) are completely powered off. Check the status by running showlogs command
```
XSCF> showlogs power
```
Change the prefetch mode to the desired value
```
XSCF> setprefetchmode -s [on|off]
```
Verify the prefetch mode
```
XSCF> showprefetchmode
```

Finally power-on all configured domains


XSCF> poweron -d <domainid> [OR]
XSCF> poweron -a

Disconnect from SP, and wait for the OS to boot up

Note to Sun-Oracle customers:

If the default value of hardware prefetch is changed, please make sure to mention this in any service requests, bug reports, etc., that you may file with Oracle Corporation. Unfortunately none of the standard commands on Solaris report the status of hardware prefetch - so, providing this additional piece of information beforehand will help the person who is analyzing/diagnosing the case.

+Original blog post URL

Instructions to Restore Files from a Windows Filesystem using Ubuntu Live CD

2010-11-30T03:21:00.000-08:00

Windows users who have not interacted with any kind of UNIX or Linux distribution before are the target audience of this HOW-TO blog post.

Download the latest version of Ubuntu 32-bit ISO image from the following location:
http://www.ubuntu.com/getubuntu/download
Burn the ISO image onto a CD or DVD so you can try running Ubuntu Linux from the CD (Live CD option)
On the target system (running Windows or any other OS but does not recognize the disk drive(s) containing Windows FS) boot Linux from the CD or DVD that you burned
Once the OS boots up: launch the partition editor
Look for NTFS, FAT or FAT32 filesystems and note down the corresponding partition names.
In the example, /dev/sda1 and /dev/sda2 are the Windows filesystems.
Launch system terminal (command line interface)
Mount the Windows filesystem using the "mount" command.
Finally navigate to the mounted filesystem using the graphical user interface.
Insert an USB flash device
Ubuntu recognizes the USB drive and mounts it automatically. Type "df -h" on the command line terminal and look for "NO NAME". "NO NAME" filesystem is the USB file system.
Backup your files from the Windows filesystem to the USB filesystem

SPARC T3 reiterates Siebel CRM's Supremacy on T-series Hardware

2010-10-31T00:17:00.001-07:00

It's been mentioned and proved several times that Sun/Oracle's T-series hardware is the best fit to deploy and run Siebel CRM. Feel free to browse through the list of Siebel benchmarks that Sun published in the past on T-series:

2004-2010 : A Look Back at Sun Published Oracle Benchmarks

Oracle Corporation announced the availability of SPARC T3 servers in Oracle OpenWorld 2010, and sure enough there is a Siebel CRM benchmark on SPARC T3-1 server to support the server launch event. Check the following web page for high level details of the benchmark.

SPARC T3-1 Server Posts a High Score on New Siebel CRM 8.1.1 Benchmark

I intend to provide the missing pieces of information in this blog post.

First of all, it is not a "Platform Sizing and Performance Program" (PSPP) benchmark. Siebel 8.1.1 was used to run the benchmark, and there is no Siebel PSPP benchmark kit available as of today for v8.1.1. Hence the test results from this benchmark exercise are not directly comparable to the Siebel 8.0 PSPP benchmark results.

Workload

The benchmark workload consists of a mix of Siebel Financial Services Call Center and Siebel Web Services / EAI transactions. The FINS Call Center transactions create a bunch of Opportunities, Quotes and Orders, where as the Web Services / EAI transactions submit new Service Requests (SR), search for and update existing SRs. The transaction mix is 40% FINS Call Center transactions and 60% Web Services / EAI transactions.

Software Versions

Siebel CRM 8.1.1
Oracle RDBMS 11g R2 (11.2.0.1), 64-bit
iPlanet Web Server 7.0 Update 8, 32-bit
Solaris 10 09/10 in the application-tier and
Solaris 10 10/09 in the web- and database-tiers

Hardware Configuration

Application Server : 1 x SPARC T3-1 Server (2 RU system)
- One socket 16-Core 1.65 GHz SPARC T3 processor, 128 hardware threads, 6 MB L2 Cache, 64 GB RAM
Web Server + Database Server : 1 x Sun SPARC Enterprise T5240 Server (2 RU system)
- Two socket 16-Core 1.165 GHz UltraSPARC T2 Plus processors, 128 hardware threads, 4 MB L2 Cache, 64 GB RAM

Virtualization Technology

iPlanet Web Server and the Oracle 11g Database Server were configured on a single Sun SPARC Enterprise T5240 Server. Those software layers were isolated from each other with the help of Oracle Solaris Containers virtualization technology. Resource allocations are shown below.

Tier	#vCPU	Memory (GB)
Database	96	48
Web	32	16

Test Results

#vUsers	Avg Trx Resp Time (sec)		Business Trx Throughput/HR		Avg CPU Utilization (%)			Avg Memory Footprint (GB)
#vUsers	FINS	EAI	FINS	EAI	App	DB	Web	App	DB + Web
13,000	0.43	0.2	48,409	116,449	58	42	37	52	35

Why stop at 13K users?

Notice that the average CPU utilization on the application server node (SPARC T3-1) is only ~58%. The application server node has room to accommodate more online vusers - however, there is not enough free memory left on the server to scale beyond 13,000 concurrent users. That is the main reason to stop at 13,000 user count in this benchmark.

Siebel Best Practices

Check the following presentation:

Siebel on Oracle Solaris : Best Practices, Tuning Tips

Acknowledgments

Credit to all our peers at Oracle Corporation who helped us with the hardware, workload, verification and validation etc., in a timely manner. Also Jenny deserves special credit for spending enormous amount of time running the benchmark with patience.

Original blog post URL:
http://blogs.sun.com/mandalika/entry/sparc_t3_reiterates_siebel_s

Is it really Solaris Versus Windows & Linux?

2010-10-08T23:09:00.001-07:00

(Even though the title explicitly states "Solaris Versus .. ", this blog entry is equally applicable to all the operating systems in the world with few changes.)

Lately I have seen quite a few e-mails and heard few customer representatives talking about the performance of their application(s) on Solaris, Windows and Linux. Typically they go like the following with a bunch of supporting data (all numbers) and no hardware configuration specified whatsoever.

"Transaction X is nearly twice as slow on Solaris compared to the same transaction running on Windows or Linux"
"Transaction X runs much faster on my Windows laptop than on a Solaris box"

Lack of awareness and taking the hardware completely out of the discussions and context are the biggest problems with complaints like these. Those claims make sense only when the underlying hardware is the same in all test cases. For example, comparing a single user, single threaded transaction running on Windows, Linux and Solaris on x86 hardware is appropriate (as long as the type and speed of the processor are identical), but not against Solaris running on SPARC hardware. This is mainly because the processor architecture is completely different for x86 and SPARC platforms.

Besides, these days Oracle offers two types of SPARC hardware - 1. T-series and 2. M-series, which serve different purposes though they are compatible with each other. It is hard to compare and analyze the performance discrimination between different SPARC offerings (T- and M-series) too with no proper understanding of the characteristics of the CPUs in use. Choosing the right hardware for the right job is the key.

It is improper to compare the business transactions running on x86 with SPARC systems or even between different types of SPARC systems, and to incorrectly attribute the hardware strength or weakness to the operating system that runs on top of the bare metal. If there is so much of discrepancy among different operating environments, it is recommended to spend some time understanding the nuances in testing hardware before spending enormous amounts of time trying to tune the application and the operating system.

The bottomline: in addition to the software (application + OS), hardware plays an important role in the performance and scalability of an application - so, unless the testing hardware is the same for all test cases on different operating systems, don't you just focus on the operating system alone and make hasty decisions to switch to other operating platforms. Carefully choose appropriate hardware for the task in hand.

OOW 2010 : Accelerate and Bullet-Proof Your Siebel CRM Deployment with Oracle's Sun Servers

2010-09-23T20:48:00.001-07:00

The best practices slides from today's OpenWorld presentation can be downloaded from the following location.

Siebel on Oracle Solaris : Best Practices, Tuning Tips

The entire presentation with proper disclaimers and Oracle Solaris Cluster specific slides will be posted on Oracle's web site soon. Stay tuned.

Identifying Ideal Oracle Database Objects for Flash Storage and Accelerators

2010-08-09T03:04:00.000-07:00

(Originally posted on blogs.sun.com at:
http://blogs.sun.com/mandalika/entry/identifying_ideal_oracle_database_objects)

The Sun Storage F5100 Flash Array and Sun Flash Accelerator F20 PCIe Card help accelerate I/O bound applications such as databases. The following are some of the guidelines to identify Oracle database objects that can benefit by using the flash storage. Even though the title explicitly states "Oracle", some of these guidelines are applicable to other databases and non-database products. Exercise discretion, evaluate and experiment before implementing these recommendations as they are.

Heavily used database tables and indexes are ideal for flash storage
- - The database workloads with no I/O bottlenecks may not show significant performance gains
- - The database workloads with severe I/O bottlenecks can fully realize the benefits of flash devices
  - Top 5 Timed Foreground Events section in any AWR report that was collected on the target database system is useful in finding whether disk I/O is a bottleneck
    - Large number of Waits and the large amount of time in DB spent waiting for some blocked resource under User I/O Wait Class is an indication of I/O contention on the system
Identify the I/O intensive tables and indexes in a database with the help of Oracle Enterprise Manager Database Control, a web-based tool for managing Oracle database(s)
- - The "Performance" page in OEM Database Control helps you quickly identify and analyze performance problems
- - Historical and the real-time database activity can be viewed from the "performance" page.
  - The same page also provides information about the top resource consuming database objects
An alternate way to identify the I/O intensive objects in a database is to analyze the AWR reports that are generated over a period of time especially when the database is busy
- - Scan through the SQL ordered by .. tables in each AWR report
- - Look for the top INSERT & UPDATE statements with more elapsed and DB times
  - The database tables that are updated frequently & repeatedly, along with the indexes created on such tables are good candidates for the flash devices
- - SQL ordered by Reads is useful in identifying the database tables with large number of physical reads
  - The database table(s) from which large amounts of data is read/fetched from physical disk(s) are also good candidates for the flash devices
    - To identify I/O intensive indexes, look through the explain plans of the top SQLs that are sorted by Physical Reads
Examine the File IO Stats section in any AWR report that was collected on the target database system
- - Consider moving the database files with heavy reads, writes and relatively high average buffer wait time to flash volumes
Examine Segments by Physical Reads, Segments by Physical Writes and Segments by Buffer Busy Waits sections in AWR report
- - The database tables and indexes with large number of physical reads, physical writes and buffer busy waits may benefit from the flash acceleration
Sun flash storage may not be ideal for storing Oracle redo logs
- - Sun Flash Modules (FMOD) in F5100 array and F20 Flash Accelerator Card are optimized for 4K sector size
- - In general, Oracle redo log files default to a block size that is equal to the physical sector size of the disk, which is typically 512 bytes
  - Majority of the recent Oracle Database platforms detect the 4K sector size on Sun flash devices
  - Oracle database automatically creates redo log files with a 4K block size on file systems created on Sun flash devices
    - However with a block size of 4K for the redo logs, there will be significant increase in redo wastage that may offset expected performance gains

F5100 Flash Storage and F20 PCIe Flash Accelerator Card as Oracle Database Smart Flash Cache

In addition to the I/O intensive database objects, customers running Oracle 11g Release 2 or later versions have the flexibility of using flash devices to turn on the "Database Smart Flash Cache" feature to reduce physical disk I/O. The Database Smart Flash Cache is a transparent extension of the database buffer cache using flash storage technology. The flash storage acts as a Level 2 cache to the (Level 1) SGA. Database Smart Flash Cache can significantly improve the performance of Oracle databases by reducing the amount of disk I/O at a much lower cost than adding an equivalent amount of RAM.

F20 Flash Accelerator offers an additional benefit - since it is a PCIe card, the I/O operations bypass disk controller overhead.

The database flash cache can be enabled by setting appropriate values to the following Oracle database parameters.


 db_flash_cache_file
 db_flash_cache_size

Check Oracle Database Administrator's Guide 11g Release 2 (11.2) : Configuring Database Smart Flash Cache documentation for the step-by-step instructions to configure Database Smart Flash Cache on flash devices.

PeopleSoft NA Payroll 500K EE Benchmark on Solaris : The Saga Continues ..

2010-07-07T19:41:00.000-07:00

(Original post is at:
http://blogs.sun.com/mandalika/entry/peoplesoft_na_payroll_500k_ee)

Few clarifications before we start.

Difference between 240K and 500K EE PeopleSoft NA Payroll benchmarks

Not too long ago Sun published PeopleSoft NA Payroll 240K EE benchmark results with 16 job streams and 8 job streams. First of all, I want to make sure everyone understands the fact that PeopleSoft NA Payroll 240K and 500K EE benchmarks are two completely different benchmarks. The 240K database model represents a large sized organization where as 500K database model represents an extra-large sized organization. Vendors [who benchmark] have the flexibility of configuring 8, 16, 24 or 32 parallel job streams (or threads) in those two benchmarks to parallellize the work being done.

Now that the clarifications are out of the way, here is the direct URL for the 500K Payroll benchmark results that Oracle|Sun published last week. (document will be updated shortly to fix the branding issues)

PeopleSoft Enterprise Payroll 9.0 using Oracle for Solaris on a Sun SPARC Enterprise M5000 (500K EE 32 job streams)

What's changed at Sun & Oracle?

The 500K payroll benchmark work was started few months ago when Sun is an independent entity. By the time the benchmark work is complete, Sun was part of Oracle Corporation. However it has no impact whatsoever on the way we have been operating and interacting with the PeopleSoft benchmark team for the past few years. We (Sun) still have to package all the benchmark results and submit for validation just like any other vendor. It is still the same laborious process that we have to go through from both ends of Oracle (that is, PeopleSoft & Sun). I just mentioned this to highlight Oracle's non-compromising nature on anything at any level in publishing quality benchmarks.

SUMMARY OF 500K NA PAYROLL BENCHMARK RESULTS

The following bar chart summarizes all the published benchmark results by different vendors. Each 3D bar on X-axis represent one vendor, and the Y-axis shows the throughput (#payments/hour) achieved by corresponding vendor. Actual throughput and the vendor name is also shown in each of the 3D bar for clarity. Common sense dictates that higher the throughput, the better it is.

The numbers in the following table were extracted from the very first page of the benchmark results white papers where Oracle|PeopleSoft highlights the significance of the results and the actual numbers that are of interest to the customers. The results in the following table are sorted by the hourly throughput (payments/hour) in the descending order. The goal of this benchmark is to achieve as much hourly throughput as possible. Click on the link that is underneath the hourly throughput values to open corresponding benchmark result.

Oracle PeopleSoft North American Payroll 9.0 - Number of employees: **500,480** & Number of payments: **750,720**
Vendor	OS	Hardware Config	#Job Streams	Elapsed Time (min)	Hourly Throughput Payments per Hour
Sun	Solaris 10 10/09	1x Sun SPARC Enterprise M5000 with 8 x 2.53 GHz SPARC64 VII Quad-Core CPUs & 64G RAM 1 x Sun Storage F5100 Flash Array with 40 Flash Modules for data, indexes. Capacity: 960 GB 1 x Sun Storage 2510 Array for redo logs. Capacity: 272 GB. Total storage capacity: 1.2 TB	32	50.11	898,886
IBM	z/OS 1.10	1 x IBM System z10 Enterprise Class Model 2097-709 with 8 x 4.4 GHz IBM System z10 Gen1 CPUs & 32G RAM 1 x IBM TotalStorage DS8300. Total capacity: 9 TB	8^*	58.96	763,962
HP	HP-UX B.11.31	1 x HP Integrity rx7640 with 8 x 1.6 GHz Intel Itanium2 9150 Dual-Core CPUs & 64G RAM 1 x HP StorageWorks EVA 8100. Total capacity: 8 TB	32	96.17	468,370

This is all public information. Feel free to compare the hardware configurations and the data presented in all three rows and draw your own conclusions. Since all vendors used the same benchmark toolkit, comparisons should be pretty straight forward.

Sun Storage F5100 Flash Array, the differentiator

Of all these benchmark results, clearly the F5100 storage array is the key differentiator. The payroll workload is I/O intensive, and requires low latency storage for better throughput (it is implicit that less latency means less I/O waits).

There is a lot of noise from some of the outside blog readers (I do not know who those readers are or who they work for) when Sun published the very first NA Payroll 240K EE benchmark with eight job streams using an F5100 array that has 40 flash modules (FMOD). Few people thought it is necessary to have those many flash modules to get that kind of performance that Sun demonstrated in the benchmark. Now that we have the 500K benchmark result as well, I want to highlight another fact that it is the same F5100 that was used in all the three NA Payroll benchmarks that Sun published in the last 10 months. Even though other vendors increased the number of disk drives when moved from 240K to 500K EE benchmark environment, Sun hasn't increased the flash modules in F5100 -- the number of FMODs remained at 40 even in 500K EE benchmark. This fact implicitly suggests at least two things -- 1. F5100 array is resilient, scales and performs consistently even with increased load. 2. May be 40 flash modules are not needed in 240K EE Payroll benchmark. Hopefully this will silence those naysayers and critics now.

While we are on the same topic, the storage capacity in the other array that was used to store the redo logs was in fact reduced from 5.3 TB in a J4200 array that was used in 240K EE/16 job stream benchmark to 272 GB in a 2510 array that was used in 500K EE/32 job stream benchmark. Of course, in both cases, the redo logs consumed only 20 GB on disk - but since the arrays were connected to the database server, we have to report the total capacity of the array(s) whether it is being used or not.

Notes on CPU utilization and IBM's #job streams

Even though I highlighted the I/O factor in the above paragraph, it is hard to ignore the fact that the NA Payroll workload is CPU intensive too. Even when multiple job streams are configured, each stream runs as a single-thread process -- hence it is vital to have a server with powerful processors for better [overall] performance.

Observant readers might have noticed couple of interesting things.

The maximum average CPU usage that Sun reported in 500K EE benchmark in any scenario by any process is only 43.99% (less than half of the total processing capacity)
The reason is simple. The SUT, M5000, has eight quad-core processors and each core is capable of running two hardware threads in parallel. Hence there are 64 virtual CPUs on the system, and since we ran only 32 job streams, only half of the total available CPU power was in use.
Customers in a similar situation have the flexibility to consolidate another workload onto the same system to take advantage of the available/remaining CPU cycles.
IBM's 500K EE benchmark result is only with 8 job streams
I do not know the exact reason - but if I have to speculate, it is as good as anyone's guess. Based on the benchmark results white paper, it appears that the z10 system (mainframe) has eight single core processors, and perhaps that is why they ran the whole benchmark with only eight job streams.

Also See:

Benchmark Results White Papers

Book Review: Oracle Database 11g – Underground Advice for Database Administrators

2010-06-15T21:20:00.000-07:00

(06/15/2010: This blog post will be edited multiple times to add reviews for the remaining chapters in the book.)

Author: April C. Sims
Publisher: Packt
Target Audience: Oracle Database Administrators

Chapter #1 "When to step away from the keyboard" starts off with an interesting example, cautions the DBAs to be self-restraint but encourages to do the right thing at the end of the day. I liked the idea of listing out a whole bunch of graphical and command line Oracle tools [with brief descriptions] that an Oracle DBA may need in performing some of the day-to-day activities. Also couple of pages were dedicated to list out various tasks performed by Oracle DBAs on a daily, weekly, monthly, quarterly & yearly basis. It was interesting. And finally the chapter concludes with a bunch of useful tips for the administrators to avoid making unwanted errors.

The only thing that probably didn't fit in this chapter is the very brief discussion on staying away from dinosaurs. In my opinion, it is completely off-topic.

Chapter #2 "Maintaining Oracle Standards" is available for download. Get it from this location and read it yourself. You be the judge.

Chapter #3 "Tracking the Bits and Bytes". The first half of the chapter talks about Oracle Data Block and the methods to view the data at the block level, the finest level of granularity that contains the actual data. The author tried and succeeded with a decent follow up that briefly explains how transaction integrity is maintained in Oracle database. The hands on exercise makes the reader sweat a little, but may help understand the material that was presented earlier, better. The key is to focus and try to understand what is happening when running all those scripts and commands. I would like a much simpler example though. Admittedly this is not something that Oracle administrators do everyday, but it does not hurt to gain some insight into Oracle internal workings and to be prepared to leverage this knowledge when disaster strikes.

The second half of the chapter was dedicated for Log Miner, a PL/SQL package utility that can be used to extract the database transactions that have been executed over a period of time. April did a nice job briefly explaining why protecting the [physical] redo, undo and the archive log files is very important -- to keep the data & database transactions from falling into the wrong hands. An example using "Flashback Transaction Blackout" method was shown to demonstrate how to use log miner utility to retrieve the changes that were done to the database few minutes ago.

I am not impressed with the example in page 92 in section Identifying data in undo segments by flashing back to timestamp. There are a bunch of SQL statements in the example with no output from a test environment. I strongly believe that showing the actual output keeps the material interesting and easy to follow.

Also I did not like the idea of pointing to blogs and random web sites, as they may disappear any time without a warning.

To be continued ..
________________
Technorati Tags:
Oracle | Database | RDBMS | DBA | Book | Review | Packt

Oracle RDBMS : Flushing a Single SQL Statement out of the Object Library Cache

2010-05-31T20:27:00.000-07:00

It is well known that the entire shared pool can be flushed with a simple ALTER SYSTEM statement.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

What if the execution plan of a single SQL statement has to be invalidated or flushed out of the shared pool so the subsequent query execution forces a hard parse on that SQL statement. Oracle 11g introduced a new procedure called PURGE in the DBMS_SHARED_POOL package to flush a specific object such as a cursor, package, sequence, trigger, .. out of the object library cache.

The syntax for the PURGE procedure is shown below.

procedure PURGE (
        name VARCHAR2, 
        flag CHAR DEFAULT 'P', 
        heaps NUMBER DEFAULT 1)

Explanation for each of the arguments is documented in detail in $ORACLE_HOME/rdbms/admin/dbmspool.sql file.

If a single SQL statement has to be flushed out of the object library cache, the first step is to find the address of the handle and the hash value of the cursor that has to go away. Name of the object [to be purged] is the concatenation of the ADDRESS and HASH_VALUE columns from the V$SQLAREA view. Here is an example:

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';

ADDRESS   HASH_VALUE
---------------- ----------
000000085FD77CF0  808321886

SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77CF0, 808321886', 'C');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';

no rows selected

Note to Oracle 10g R2 Customers

The enhanced DBMS_SHARED_POOL package with the PURGE procedure is included in the 10.2.0.4 patchset release.

10.2.0.2 and 10.2.0.3 customers can download and install RDBMS patch 5614566 to get access to these enhancements in DBMS_SHARED_POOL package.

Also see:

Oracle Support Document ID 457309.1 "How To Flush an Object out the Library Cache [SGA]"
Oracle Support Document ID 751876.1 "DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4"
DBMS_SHARED_POOL.PURGE() procedure documentation

(Original post is at:
http://blogs.sun.com/mandalika/entry/oracle_rdbms_flushing_a_single)

Oracle Database: Say No Thanks! to a New Index

2010-05-23T11:02:00.000-07:00

(Original post is at blogs.sun.com at:
http://blogs.sun.com/mandalika/entry/oracle_database_say_i_no)

.. unless you are working with a database that is largely read-only or if the new index is supposed to be fixing a critical performance issue with no side effect(s).

Two topics covered in this blog entry with plenty of simple examples:

Why creating new indexes on a heavily modified table may not be a good idea? and
How to identify unused indexes?

Read on.

Indexes are double-edged swords that may improve the performance of targeted queries, but in some cases they may accidentally degrade the performance of other queries that are not targeted. In any case, exercise caution while adding a new index to the database. After adding a new index, monitor the overall performance of the database, not just the targeted query.

If DML statements that modify data (INSERT, UPDATE, or DELETE) are being executed large number of times on a table, make sure that the addition of a new index on the same table does not negatively affect the performance of those DML operations. Usually this is not a problem if the SQLs being executed are simply retrieving but not adding or modifying the existing data. In all other cases, there is some performance overhead induced by the addition of each new index. For example, if there are 10 indexes created on a table DUMMY, adding a new row of data to the table DUMMY may require updating all 10 indexes behind the scenes by the database management system.

Here is an example demonstrating the performance overhead of a new index on a table.

SQL> CREATE TABLE VIDEO
  2  (BARCODE VARCHAR(10) NOT NULL,
  3  TITLE VARCHAR2(25) NOT NULL,
  4  FORMAT VARCHAR2(10),
  5  PRICE NUMBER,
  6  DATA_OF_RELEASE DATE)
  7  /

Table created.

SQL> insert into VIDEO values ('9301224321', 'AVATAR', 'BLU-RAY', 19.99, '22-APR-2010');

1 row created.

..

SQL> insert into VIDEO values ('3782460017', 'THE SIMPSONS - SEASON 20', 'BLU-RAY', 29.99, '04-JUL-2009');

1 row created.

SQL> select * from VIDEO;

BARCODE  TITLE        FORMAT   PRICE DATA_OF_RELEASE
--------------- ----------------------------------- --------------- ---------- ---------------
9301224321 AVATAR        BLU-RAY   19.99 22-APR-10
7619203043 BEN-HUR        VHS     9.79 12-MAR-63
7305832093 THE MATRIX       DVD    12.29 03-DEC-99
4810218795 MEMENTO        DVD     8.49 02-FEB-02
3782460017 THE SIMPSONS - SEASON 20     BLU-RAY   29.99 04-JUL-09

SQL> select * from USER_INDEXES where TABLE_NAME = 'VIDEO';

no rows selected

SQL> alter session set events '10046 trace name context forever, level 8';

Session altered.

SQL> select * from VIDEO where FORMAT = 'BLU-RAY';

BARCODE  TITLE        FORMAT   PRICE DATA_OF_RELEASE
--------------- ----------------------------------- --------------- ---------- ---------------
9301224321 AVATAR        BLU-RAY   19.99 22-APR-10
3782460017 THE SIMPSONS - SEASON 20     BLU-RAY   29.99 04-JUL-09

SQL> alter session set events '10046 trace name context off';

Session altered.

SQL trace file has the following contents.

SQL ID: 0pu5s70nsdnzv
Plan Hash: 3846322456
SELECT * 
FROM
 VIDEO WHERE FORMAT = :"SYS_B_0"


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         16          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         16          0           2

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 28  

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  TABLE ACCESS FULL VIDEO (cr=16 pr=0 pw=0 time=3 us cost=4 size=100 card=2)

Let's create an index and see what happens.

SQL> create index VIDEO_IDX1 on VIDEO (FORMAT);

Index created.

SQL>  alter session set events '10046 trace name context forever, level 8';

Session altered.

SQL> select * from VIDEO where FORMAT = 'BLU-RAY';

BARCODE  TITLE        FORMAT   PRICE DATA_OF_RELEASE
--------------- ----------------------------------- --------------- ---------- ---------------
9301224321 AVATAR        BLU-RAY   19.99 22-APR-10
3782460017 THE SIMPSONS - SEASON 20     BLU-RAY   29.99 04-JUL-09

SQL> alter session set events '10046 trace name context off';

Session altered.

The latest contents of the trace file are as follows. Notice the reduction in buffer gets from 16 to 4. That is, the new index improved the query performance by 75%.

SQL ID: 0pu5s70nsdnzv
Plan Hash: 2773508764
SELECT * 
FROM
 VIDEO WHERE FORMAT = :"SYS_B_0"


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          4          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          4          0           2

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 28  (CS90)

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  TABLE ACCESS BY INDEX ROWID VIDEO (cr=4 pr=0 pw=0 time=12 us cost=2 size=100 card=2)
      2   INDEX RANGE SCAN VIDEO_IDX1 (cr=2 pr=0 pw=0 time=10 us cost=1 size=0 card=2)(object id 76899)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      2   TABLE ACCESS (BY INDEX ROWID) OF 'VIDEO' (TABLE)
      2    INDEX   MODE: ANALYZED (RANGE SCAN) OF 'VIDEO_IDX1' (INDEX)

So far so good. Let's add a new row of data and examine the trace file one more time. From hereafter, keep an eye on the "current" column (logical IOs performed due to an INSERT, UPDATE or DELETE) and notice how it changes with different actions -- adding and removing: indexes, new row(s) of data etc.,

SQL ID: dnb2d8cpdj56p
Plan Hash: 0
INSERT INTO VIDEO 
VALUES
 (:"SYS_B_0", :"SYS_B_1", :"SYS_B_2", :"SYS_B_3", :"SYS_B_4")


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          1          7           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          1          7           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 28  (CS90)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=0 us)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  INSERT STATEMENT   MODE: ALL_ROWS
      0   LOAD TABLE CONVENTIONAL OF 'VIDEO'

Now drop the index, re-insert the last row and get the tracing data again.

SQL> drop index VIDEO_IDX1;

Index dropped.

SQL> delete from VIDEO where BARCODE ='4457332907';

1 row deleted.

SQL> commit;

Commit complete.

SQL> alter session set events '10046 trace name context forever, level 8';

Session altered.

SQL> insert into VIDEO values ('4457332907', 'KING OF THE HILL - ALL', 'DVD', 90.00, '01-JAN-2011');

1 row created.

SQL> alter session set events '10046 trace name context off';

Session altered.

The contents of the latest trace file are shown below.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          0          2          5          1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.00          0          2          5           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 28  (CS90)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=0 us)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  INSERT STATEMENT   MODE: ALL_ROWS
      0   LOAD TABLE CONVENTIONAL OF 'VIDEO'

This time create two indexes and see what happens.

SQL> CREATE INDEX VIDEO_IDX1 ON VIDEO (FORMAT);

Index created.

SQL> CREATE INDEX VIDEO_IDX2 ON VIDEO (TITLE);

Index created.

Trace file contents:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          1          9           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          1          9           1

Notice the two additional logical IOs (look under "current" column). Those additional logical input/output operations are the result of the new indexes. The number goes up as we add more indexes and data to the table VIDEO.

SQL> delete from VIDEO where BARCODE ='4457332907';

1 row deleted.

SQL> commit;

Commit complete.

SQL> create index VIDEO_IDX3 on VIDEO (PRICE, DATA_OF_RELEASE);

Index created.

SQL>  alter session set events '10046 trace name context forever, level 8';

Session altered.

SQL> insert into VIDEO values ('4457332907', 'KING OF THE HILL - ALL', 'DVD', 90.00, '01-JAN-2011');

1 row created.

SQL> alter session set events '10046 trace name context off';

Session altered.


SQL trace:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          1         11           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          1         11           1

You can try other operations such as UPDATE, DELETE on your own.

Since there are only few rows of data in the table VIDEO, it is hard to notice the real performance impact in these examples. If you really want to see the negative performance impact due to the large number of indexes on a heavily updated table, try adding thousands or millions of rows of data and few more indexes.

Moral of the story: Indexes aren't always cheap - they may have some overhead associated with them. Be aware of those overheads and ensure that the index maintenance overhead do not offset the performance gains resulting from the indexes created on a particular table.

Monitoring Index Usage

Now we know the possible disadvantage of having too many indexes on a heavily updated table. One way to reduce the index maintenance overhead is to instrument the indexes so we can monitor their usage from time to time and remove the unused indexes. To start monitoring the index usage, alter the index by specifying the keywords MONITORING USAGE.

SQL> select index_name from user_indexes where table_name = 'VIDEO';

INDEX_NAME
--------------------------------------------------------------------------------
VIDEO_IDX3
VIDEO_IDX1
VIDEO_IDX2

SQL> alter index VIDEO_IDX1 MONITORING USAGE;

Index altered.

SQL> alter index VIDEO_IDX2 MONITORING USAGE;

Index altered.

SQL> alter index VIDEO_IDX3 MONITORING USAGE;

Index altered.

Once the indexes are instrumented, query the V$OBJECT_USAGE view occasionally to see if the instrumented indexes are being used in executing SQL queries.

SQL> select * from VIDEO where BARCODE LIKE '%22%';

BARCODE  TITLE        FORMAT   PRICE DATA_OF_RELEASE
--------------- ----------------------------------- --------------- ---------- ---------------
9301224321 AVATAR        BLU-RAY   19.99 22-APR-10

SQL> select * from VIDEO where FORMAT = 'VHS';

BARCODE  TITLE        FORMAT   PRICE DATA_OF_RELEASE
--------------- ----------------------------------- --------------- ---------- ---------------
7619203043 BEN-HUR        VHS     9.79 12-MAR-63

SQL> select * from VIDEO where PRICE < 20;

BARCODE  TITLE        FORMAT   PRICE DATA_OF_RELEASE
--------------- ----------------------------------- --------------- ---------- ---------------
4810218795 MEMENTO        DVD     8.49 02-FEB-02
7619203043 BEN-HUR        VHS     9.79 12-MAR-63
7305832093 THE MATRIX       DVD    12.29 03-DEC-99
9301224321 AVATAR        BLU-RAY   19.99 22-APR-10

SQL> select * from VIDEO where FORMAT = 'BLU-RAY' AND DATA_OF_RELEASE < '01-JAN-2010';

BARCODE  TITLE        FORMAT   PRICE DATA_OF_RELEASE
--------------- ----------------------------------- --------------- ---------- ---------------
3782460017 THE SIMPSONS - SEASON 20     BLU-RAY   29.99 04-JUL-09


SQL> column INDEX_NAME format A25
SQL> column START_MONITORING format A30

SQL> select INDEX_NAME, USED, START_MONITORING 
  2  from V$OBJECT_USAGE 
  3  where INDEX_NAME LIKE 'VIDEO_IDX%'
  4  /

INDEX_NAME    USED     START_MONITORING
------------------------- --------- ------------------------------
VIDEO_IDX1    YES     04/27/2010 01:10:20
VIDEO_IDX2    NO     04/27/2010 01:10:25
VIDEO_IDX3    YES     04/27/2010 01:10:31

In the above example, the index VIDEO_IDX2 was not in use during the period of index monitoring. If we are convinced that the queries that will be executed are similar to the ones that were executed during the index monitoring period, we can go ahead and remove the index VIDEO_IDX2 to reduce the performance overhead during updates on table VIDEO.

To stop monitoring the index usage, alter the index with the keywords NOMONITORING USAGE.

SQL> alter index VIDEO_IDX1 NOMONITORING USAGE;

Index altered.

Music : Few Mixed Tunes with GarageBand

2010-05-10T02:07:00.000-07:00

(Originally posted on 12/09/2009. This blog posted will be updated as new compositions come along.)

For the past couple of weeks I have had fun playing with Apple's GarageBand. It is a nice piece of software with intuitive user interface and tons of free & ready-to-use music loops. It only took a couple of tries and about 6 hours to produce my first ever mixed tune with software of any kind. The second one just took two hours as my main focus was on only two loops. I'm happy with the output and decided to share it with my family and friends. Hence I uploaded those two tracks to iCompositions, an internet community web site that facilitates sharing each individuals' creative work with the rest of the community at free of cost. Click on the following music player images to listen to those instrumental tracks.

[12/09/09] Track #1 Hokum

[12/09/09] Track #2 Thrum

[12/18/09] Track #3 Kabuki Dance

[01/05/10] Track #4 Phantasm

[05/10/10] Track #5 Transgression

Now if only I knew how to play a real instrument ..
________________
Technorati Tags:
Music | GarageBand

Oracle Database 11g – Underground Advice for Database Administrators

2010-05-07T22:24:00.000-07:00

.. review coming soon ..

Meanwhile feel free to explore the Table of Contents and check the freely download-able chapter Chapter 2: Maintaining Oracle Standards

Oracle 11g R1: Poor Data Pump Performance when Exporting a Partitioned Table

2010-05-03T00:12:00.000-07:00

(Originally posted on blogs.sun.com at http://blogs.sun.com/mandalika/entry/oracle_11g_r1_poor_data)

Symptom(s)

Data Pump Export utility, expdp, performs well with non-partitioned tables, but exhibits extreme poor performance when exporting objects from a partitioned table of similar size. In some cases the degradation can be as high as 3X or worse.

SQL traces may show that much of the time is being spent in a SQL statement that is similar to:

UPDATE "schema"."TABLE" mtu 
SET mtu.base_process_order = NVL((SELECT mts1.process_order FROM "schema"."TABLE" mts1 
WHERE ..

Here is an example data export session:

Export: Release 11.1.0.7.0 - 64bit Production on Wednesday, 31 March, 2010 6:56:50

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;; 
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCHMA"."SYS_EXPORT_TABLE_01":  SCHMA/******** DIRECTORY=exp_dir DUMPFILE=SOME_DUMMY_PART_FULL.DMP TABLES=SOME_DUMMY_PART
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 20.56 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCHMA"."SOME_DUMMY_PART":"DUMMY_PART_P01"  1.143 GB 13788224 rows
. . exported "SCHMA"."SOME_DUMMY_PART":"DUMMY_PART_P02"  1.143 GB 13788224 rows
. . exported "SCHMA"."SOME_DUMMY_PART":"DUMMY_PART_P03"  1.143 GB 13788224 rows
...
. . exported "SCHMA"."SOME_DUMMY_PART":"DUMMY_PART_P32"  151.1 MB 1789216 rows
. . exported "SCHMA"."SOME_DUMMY_PART":"DUMMY_PART_P33"  11.37 MB  136046 rows
. . exported "SCHMA"."SOME_DUMMY_PART":"DUMMY_PART_P00"      0 KB       0 rows
Master table "SCHMA"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCHMA.SYS_EXPORT_TABLE_01 is:
  /DBDUMP/SOME_DUMMY_PART_FULL.DMP
Job "SCHMA"."SYS_EXPORT_TABLE_01" successfully completed at 11:22:36

Solution(s) / Workaround

This is a known issue (that is, a bug) and a solution is readily available. Try any of the following to resolve the issue:

Apply the 11g database patch 8845859
Upgrade to 11.2.0.2 patchset when it is available, or
Specify "VERSION=10.2.0.3" expdp option as a workaround

I ran into this issue and I chose the workaround to make some quick progress. With the string "VERSION=10.2.0.3" appended, export time went down from 265 minutes to 60+ minutes.

2004-2010 : A Look Back at Sun Published Oracle Benchmarks

2010-04-21T01:25:00.000-07:00

(Originally published on blogs.sun.com at:
http://blogs.sun.com/mandalika/entry/2004_2010_a_look_back)

Since Sun Microsystems became a legacy, I got this idea of a reminiscent [farewell] blog post for the company that gave me the much needed break when I was a graduate student back in 2002. As I spend more than 50% of my time benchmarking different Oracle products on Sun hardware, it'd be fitting to fill this blog entry with a recollection of the benchmarks I was actively involved in over the past 6+ years. Without further ado, the list follows.

2004

1. 10,000 user Siebel 7.5.2 PSPP benchmark on a combination of SunFire v440, v890 and E2900 servers. Database: Oracle 9i

Benchmark Report
Blog: Sun achieves winning Siebel benchmark
Blog: Sun and Siebel Kick Some Benchmark Butt
Blog: When Good Benchmarks Go Bad

2005

2. 8,000 user Siebel 7.7 PSPP benchmark on a combination of SunFire v490, v890, T2000 and E2900 servers. Database: Oracle 9i

Benchmark Report

3. 12,500 user Siebel 7.7 PSPP benchmark on a combination of SunFire v490, v890, T2000 and E2900 servers. Database: Oracle 9i

Benchmark Report

2006

4. 10,000 user Siebel Analytics 7.8.4 benchmark on multiple SunFire T2000 servers. Database: Oracle 10g

Benchmark Report

2007

5. 10,000 user Siebel 8.0 PSPP benchmark on two T5220 servers. Database: Oracle 10g R2

2008

6. Oracle E-Business Suite 11i Payroll benchmark for 5,000 employees. Database: Oracle 10g R1

White Paper (didn't qualify as a benchmark since we configured more than 4 payroll threads)
Blog: Running Batch Workloads on Sun's CMT Servers

7. 14,000 user Siebel 8.0 PSPP benchmark on a single T5440 server. Database: Oracle 10g R2

8. 10,000 user Siebel 8.0 PSPP benchmark on a single T5240 server. Database: Oracle 10g R2

2009

9. 4,000 user PeopleSoft HR Self-Service 8.9 benchmark on a combination of M3000 and T5120 servers. Database: Oracle 10g R2

10. 28,000 user Oracle Business Intelligence Enterprise Edition (OBIEE) 10.1.3.4 benchmark on a single T5440 server. Database: Oracle 11g R1

11. 50,000 user Oracle Business Intelligence Enterprise Edition (OBIEE) 10.1.3.4 benchmark on two T5440 servers. Database: Oracle 11g R1

12. PeopleSoft North American Payroll 9.0 240K EE 8-stream benchmark on a single M4000 server with F5100 Flash Array storage. Database: Oracle 11g R1

2010

13. PeopleSoft North American Payroll 9.0 240K EE 16-stream benchmark on a single M4000 server with F5100 Flash Array storage. Database: Oracle 11g R1

14. 6,000 user PeopleSoft Campus Solutions 9.0 benchmark on a combination of X6270 blades and M4000 server. Database: Oracle 11g R1

Although challenging and exhilarating, benchmarks aren't always pleasant to work on, and really not for people with weak hearts. While running most of these benchmarks, my blood pressure shot up several times leaving me wonder why do I keep working on time sensitive and/or politically, strategically incorrect benchmarks (apparently not every benchmark finds a home somewhere on the public network). Nevertheless in the best interest of my employer, the showdown must go on.

PeopleSoft Campus Solutions 9.0 benchmark on Sun SPARC Enterprise M4000 and X6270 blades

2010-03-01T00:54:00.000-08:00

Oracle|Sun published PeopleSoft Campus Solutions 9.0 benchmark results on February 18, 2010. Here is the direct URL to the benchmark results white paper:

PeopleSoft Enterprise Campus Solutions 9.0 using Oracle 11g on a Sun SPARC Enterprise M4000 & Sun Blade X6270 Modules

Sun published three PeopleSoft benchmarks on SPARC platform over the last 12 month period -- one OLTP and two batch benchmarks^[1]. The latest benchmark is somewhat special for at least couple of reasons:

Campus Solutions 9.0 workload has both online transactions and batch processes, and
This is the very first time ever Sun published a PeopleSoft benchmark on x64 hardware running Oracle Enterprise Linux

The summary of the benchmark test results is shown below. These numbers were extracted from the very first page of the benchmark results white papers where Oracle|PeopleSoft highlights the significance of the test results and the actual numbers that are of interest to the customers. Test results are sorted by the hourly throughput (invoices & transcripts per hour) in the descending order. Click on the link that is underneath the vendor name to open corresponding benchmark result.

While analyzing these test results, remember that the higher the throughput, the better. In the case of online transactions, it is desirable to keep the response times as low as possible.

Oracle PeopleSoft Campus Solutions 9.0 Benchmark Test Results
Vendor	Hardware Configuration		OS	Resource Utilization		Response/Elapsed Times at Peak Load (6,000 users)
				Resource Utilization		Online Transactions: Avg Response Times (sec)				Batch Throughput/hr
				CPU%	Mem (GB)	Logon	LSC	Page Load	Page Save	Invoice	Transcripts
Sun	DB	1 x M4000 with 2 x 2.53GHz SPARC64 VII QC processors, 32GB RAM 1 x Sun Storage Flash Accelerator F20 with 4 x 24GB FMODs 1 x ST2540 array with 11 × 136.7GB SAS 15K RPM drives	Solaris 10	37.29	20.94	0.64	0.78	0.82	1.57	31,797	36,652
	APP	2 x X6270 blades with 2 x 2.93GHz Xeon 5570 QC processors, 24GB RAM	OEL4 U8	41.69^*	4.99^*
	WEB+PS	1 x X6270 blade with 2 x 2.8GHz Xeon 5560 QC processors, 24GB RAM	OEL4 U8	33.08	6.03

HP	DB	1 x Integrity rx6600 with 4 x 1.6GHz Itanium 9050 DC procs, 32G RAM 1 x HP StorageWorks EVA8100 array with 58 x 146GB drives	HP-UX 11iv3	61	30	0.71	0.91	0.83	1.63	22,753	30,257
	APP	2 x BL460c blade with 2 x 3.16GHz Xeon 5460 QC procs, 16GB RAM	RHEL4U5	61.81	3.6
	WEB	1 x BL460c blade with 2 x 3GHz Xeon 5160 DC procs, 8GB RAM	RHEL4U5	44.36	3.77
	PS	1 x BL460c blade with 2 x 3GHz Xeon 5160 DC procs, 8GB RAM	RHEL4U5	21.90	1.48

HP	DB	1 x ProLiant DL580 G4 w/ 4 x 3.4GHz Xeon 7140M DC procs, 32G RAM 1 x HP StorageWorks XP128 array with 28 x 73GB drives	Win2003R2	70.37	21.26	0.72	1.17	0.94	1.80	17,621	25,423
	APP	4 x BL480c G1 blades with 2 x 3GHz Xeon 5160 DC procs, 12GB RAM	Win2003R2	65.61	2.17
	WEB	1 x BL460c G1 blades with 2 x 3GHz Xeon 5160 DC procs, 12GB RAM	Win2003R2	54.11	3.13
	PS	1 x BL460c G1 blades with 2 x 3GHz Xeon 5160 DC procs, 12GB RAM	Win2003R2	32.44	1.40

This is all public information. Feel free to compare the hardware configurations & the data presented in the table and draw your own conclusions. Since both Sun and HP used the same benchmark workload, toolkit and ran the benchmark with the same number of concurrent users and job streams for the batch processes, comparison should be pretty straight forward.

Hopefully the following paragraphs will provide relevant insights into the benchmark and the application.

Caution in interpreting the Online Transaction Response Times

Average response times for the online transactions were measured using HP's QuickTest Pro (QTP) tool. This is a benchmark requirement. QTP test scripts have a dependency on the web browser (IE in particular) -- hence it is extremely sensitive to the web browser latencies, remote desktop/VNC latencies and other latencies induced by the operating system. Be aware that all these latencies will be factored into the transaction response times and due to this, the final average transaction response times might be skewed a little. In other words, the reported average transaction response times may not necessarily be very accurate. In most of the cases we might be looking at the approximate values and the actual values might be far better than the ones reported in the benchmark report. (I really wish Oracle|PeopleSoft would throw away some of the skewed samples to make the data more accurate and reliable.). Please keep this in mind when looking at the response times of the online transactions.

Quick note about Consolidation

In our benchmark environment, we had the PeopleSoft Process Scheduler (batch server) set up on the same node as that of the web server node. In general, Oracle recommends setting up the process scheduler either on the database server node or on a dedicated system. However in the benchmark environment, we chose not to run the process scheduler on the database server node as it would hurt the performance of the online transactions. At the same time, we noticed plenty of idle CPU cycles on the web server node even at the peak load of 6,000 concurrent users, so we decided to run the PS on the web server node. In case if customers are not comfortable with this kind of setup, they can use any supported virtualization technology (eg., Logical Domains, Containers on Solaris, Oracle VM on OEL) to separate the process scheduler from the web server by allocating the system resources as they like. It is just a matter of choice.

PeopleSoft Load Balancing

PeopleSoft has load balancing mechanism built into the web server to forward the incoming requests to appropriate application server in the enterprise, and within the application server to send the request to an appropriate application server process, PSAPPSRV. (I'm not 100% sure but I think application server balances the load among application server processes in a round robin fashion on *nix platforms whereas on Windows, it forwards all the requests to a single application server process until it reaches the configured limit before moving on to the next available application server process.). However this in-built load balancing is not perfect. Most of the times, the number of requests processed by each of the identically configured application server processes [running on different application server nodes in the enterprise] may not be even. This minor shortcoming could lead to uneven resource usage across different nodes in the PeopleSoft deployment. You can notice this in the CPU and memory usage reported for the two app server nodes in the benchmark environment (check the benchmark results white paper).

Sun Flash Accelerator F20 PCIe Card

To reduce I/O latency, hot tables and hot indexes were placed on a Sun Flash Accelerator F20 PCIe Card in this benchmark. The F20 card has a total capacity of 96 GB with 4 x 24GB Flash Modules (FMODs). Although this workload is moderately I/O intensive, the batch processes in this benchmark generate a lot of I/O for few minutes in the steady state of the benchmark. The flash accelerator handled the burst of I/O activity pretty well, and as a result the performance of the batch processesing was improved.

Check the white paper Best Practices for Oracle PeopleSoft Enterprise Payroll for North America using the Sun Storage F5100 Flash Array or Sun Flash Accelerator F20 PCIe Card to know more about the top flash products offered by Oracle|Sun and how they can be deployed in a PeopleSoft environment for maximum benefit.

Solaris specific Tuning

Almost on all versions of Solaris 10, the kernel uses 4M as the maximum page size despite the fact that the underlying hardware supports as high as 256M pages. However large pages may improve the performance of some of the memory intensive workloads such as Oracle database by reducing the number of virtual <=> physical translations there by reducing the expensive dTLB/iTLB misses. In the benchmark environment, the following values were set in the /etc/system configuration file of the database server node to enable 256MB pages for the process heap and ISM.


* 256M pages for process heap
set max_uheap_lpsize=0x10000000

* 256M pages for ISM
set mmu_ism_pagesize=0x10000000

While we are on the same topic, Linux configuration is out-of-the-box. No OS tuning was performed in this benchmark.

Tuning Tip for Solaris Customers

Even though we did not set up the middle-tier on a Solaris box in this benchmark, this particular tuning tip is still valid and may help all those customers running the application server on Solaris. Consider lowering the shell limit for the file descriptors to a value of 512 or less if it was set to any value greater than 512. As of today (until the release of PeopleTools 8.50), there are certain parts of code in PeopleSoft calls the file control routine, fcntl(), and the file close routine, fclose(), in a loop "ulimit -n" number of times to close a bunch of files which were opened to perform a specific task. In general, PeopleSoft processes won't open hundreds of files. Hence the above mentioned behavior results in ton of dummy calls that error out. Besides, those system calls are not cheap -- they consume CPU cycles. It gets worse when there are a number of PeopleSoft processes that exhibit this kind of behavior simultaneously. (high system CPU% is one of the symptoms that helps identifying this behavior). Oracle|PeopleSoft is currently trying to address this performance issue. Meanwhile customers can lower the file descriptors shell limit to reduce its intensity and impact.

We have not observed this behavior on OEL when running the benchmark. But be sure to trace the system calls and figure out if the shell limit for the file descriptors need be lowered even on Linux or other supported platforms.

______________________________________

Footnotes:

1. PeopleSoft benchmarks on Sun platform in year 2009-2010

PeopleSoft HRMS 8.9 SELF-SERVICE Using ORACLE on Sun SPARC Enterprise M3000 and Enterprise T5120 Servers -- online transactions (OLTP)
PeopleSoft Enterprise Payroll 9.0 using Oracle for Solaris on a Sun SPARC Enterprise M4000 (8 streams) -- batch workload
PeopleSoft Enterprise Payroll 9.0 using Oracle for Solaris on a Sun SPARC Enterprise M4000 (16 streams) -- batch workload

2. ^*HP's benchmark results white paper did not show the CPU and memory breakdown numbers separately for each of the application server nodes. It only shows the average of average CPU and memory utilization for all app server nodes under "App Servers". Sun's average CPU, memory numbers [shown in the above table] were calculated in the same way for consistency.

(Copied from the original post at Oracle|Sun blogs @
http://blogs.sun.com/mandalika/entry/peoplesoft_campus_solutions_9_0)

Extracting DDL Statements from a PeopleSoft Data Mover exported DAT file

2010-02-11T01:07:00.000-08:00

Case in hand: Given a PeopleSoft Data Mover exported data file (db or dat file), how to extract the DDL statements [from that data file] which gets executed as part of the Data Mover's data import process?

Here is a quick way to do it:

Insert the SET EXTRACT statements in the Data Mover script (DMS) before the IMPORT .. statement.

eg.,
```
% cat /tmp/retrieveddl.dms

..
SET EXTRACT OUTPUT /tmp/ddl_stmts.log;
SET EXTRACT DDL;
..

IMPORT *;
```
It is mandatory that the SET EXTRACT OUPUT statement must appear before any SET EXTRACT statements.
Run the Data Mover utility with the modified DMS script as an argument.

eg., OS: Solaris
```
% psdmtx -CT ORACLE -CD NAP11 -CO NAP11 -CP NAP11 -CI people -CW peop1e -FP /tmp/retrieveddl.dms
```
On successful completion, you will find the DDL statements in /tmp/retrieveddl.dms file.

Check chapter #2 "Using PeopleSoft Data Mover" in Enterprise PeopleTools x.xx PeopleBook: Data Management document for more ideas.

______
(Originally posted on blogs.sun.com at:
http://blogs.sun.com/mandalika/entry/extracting_ddl_statements_from_a)

PeopleSoft NA Payroll 240K EE Benchmark with 16 Job Streams : Another Home Run for Sun

2010-01-31T20:44:00.000-08:00

(Originally posted on blogs.sun.com at:
http://blogs.sun.com/mandalika/entry/peoplesoft_na_payroll_240k_ee)

Poor Steve A.^[1] ... This entry is not about Steve A. though. It is about the new PeopleSoft NA Payroll benchmark result that Sun published today.

First things first. Here is the direct URL to our latest benchmark results:

PeopleSoft Enterprise Payroll 9.0 using Oracle for Solaris on a Sun SPARC Enterprise M4000 (16 job streams^[2] -- simply referred as 'stream' hereonwards)

The summary of the benchmark test results is shown below only for the 16 stream benchmarks. These numbers were extracted from the very first page of the benchmark results white papers where Oracle|PeopleSoft highlights the significance of the results and the actual numbers that are of interest to the customers. The results in the following table are sorted by the hourly throughput (payments/hour) in the descending order. The goal is to achieve as much hourly throughput as possible. Click on the link that is underneath the hourly throughput values to open corresponding benchmark result.

Oracle PeopleSoft North American Payroll 9.0 - Number of employees: **240,000** & Number of payments: **360,000**
Vendor	OS	Hardware Config	#Job Streams	Elapsed Time (min)	Hourly Throughput Payments per Hour
Sun	Solaris 10 5/09	1x Sun SPARC Enterprise M4000 with 4 x 2.53 GHz SPARC64-VII Quad-Core processors and 32 GB memory 1 x Sun Storage F5100 Flash Array with 40 Flash Modules for data, indexes 1 x Sun Storage J4200 Array for redo logs	16	43.78	493,376
HP	HP-UX	1 x HP Integrity rx6600 with 4 x 1.6 GHz Intel Itanium2 9000 Dual-Core processors and 32 GB memory 1 x HP StorageWorks EVA 8100	16	68.07	317,320

This is all public information. Feel free to compare the hardware configurations and the data presented in both of the rows and draw your own conclusions. Since both Sun and HP used the same benchmark toolkit, workload and ran the benchmark with the same number of job streams, comparison should be pretty straight forward.

If you want to compare the 8 stream results, check the other blog entry: PeopleSoft North American Payroll on Sun Solaris with F5100 Flash Array : A blog Reprise. Sun used the same hardware to run both benchmark tests with 8 and 16 streams respectively. We could have gotten away with 20+ Flash Modules (FMODs), but we want to keep the benchmark environment consistent with our prior benchmark effort around the same benchmark workload with 8 job streams. Due to the same hardware setup, now we can easily demonstrate the advantage of parallelism (simply by comparing the test results from 8 and 16 stream benchmarks) and how resilient and scalable the F5100 Flash array is.

Our benchmarks showed an improvement of ~55% in overall throughput when the number of job streams were increased from 8 to 16. Also our 16 stream results showed ~55% improvement in overall throughput over HP's published results with the same number of streams at a maximum average CPU utilization of 45% compared to HP's maximum average CPU utilization of 89%. The half populated Sun Storage F5100 Flash Array played the key role in both of those benchmark efforts by demonstrating superior I/O performance over the traditional disk based arrays.

Before concluding, I would like to highlight a few known facts (just for the benefit of those people who may fall for the PR trickery):

8 job streams != 16 job streams. In other words, the results from an 8 stream effort is not comparable to that of a 16 stream result.
The throughput should go up with increased number of job streams [ only up to some extent -- do not forget that there will be a saturation point for everything ]. For example, the throughput with 16 streams might be higher compared to the 8 stream throughput.
The Law of Diminishing Returns applies to the software world too, not just for the economics. So, there is no guarantee that the throughput will be much better with 24 or 32 job streams.

Other blog posts and documents of interest:

Notes:

[1] Steve A. tried so hard and his best to make everyone else believe that HP's 16 job stream NA Payroll 240K EE benchmark results are on par with Sun's 8 stream benchmark results. Apparently Steve A. failed and gave up after we showed the world a few screenshots from a published and eventually withdrawn benchmark [ by HP ]. You can read all his arguments, comparisons etc., in the comments section of my other blog entry PeopleSoft North American Payroll on Sun Solaris with F5100 Flash Array : A blog Reprise as well as in Joerg Moellenkamp's blog entries around the same topic.

[2] In PeopleSoft terminology, a job stream is something that is equivalent to a thread.

Accessing MySQL Database(s) from StarOffice / OpenOffice.org Suite of Applications

2009-12-30T08:37:00.000-08:00

This blog post is organized into two major sections and several sub-sections. The major sections focus on the tasks to be performed at the MySQL server and the *Office client while the sub-sections talk about the steps to be performed in detail.

To show the examples in this exercise, we will be creating a new MySQL database user with user ID SOUSER. This new user will be granted read-only access to couple of tables in a MySQL database called ISVe. The database can be accessed from any host in the network. ben10.sfbay is the hostname of the MySQL server.

Tasks to be Performed at the MySQL Server

This section is intended only for the MySQL Server Administrators. If you are an end-user, skip ahead to Tasks to be Performed at the Client side.

Create a new MySQL user and grant required privileges.

eg.,


% mysql -u root -p
Enter password: *****
Server version: 5.1.25-rc-standard Source distribution
..

mysql> CREATE USER SOUSER IDENTIFIED BY 'SOUSER';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for SOUSER;
+-------------------------------------------------------------------------------------------------------+
| Grants for SOUSER@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'SOUSER'@'%' IDENTIFIED BY PASSWORD '*8370607DA2602E52F463FF3B2FFEA53E81B9314C' | 
+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> USE ISVe;
Database changed

mysql> show tables;
+--------------------------+
| Tables_in_ISVe           |
+--------------------------+
| CustomReport             | 
| CustomSQL                | 
| ISVeOldProjects          | 
| ISVeOrg                  | 
| ISVeProject              | 
| ISVeProjectExecution     | 
| ISVeProjectGoalAlignment | 
| ISVeProjectMiscInfo      | 
| ISVeProjectScoping       | 
| ISVeProjectStatus        | 
| ISVeProjects             | 
| ISVeProjectsVW           | 
| ISVeSearchLog            | 
| LastRefreshed            | 
+--------------------------+
14 rows in set (0.00 sec)

mysql> GRANT SELECT ON ISVe.ISVeOldProjects TO 'SOUSER'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON ISVe.ISVeProjects TO 'SOUSER'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for SOUSER;
+-------------------------------------------------------------------------------------------------------+
| Grants for SOUSER@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'SOUSER'@'%' IDENTIFIED BY PASSWORD '*8370607DA2602E52F463FF3B2FFEA53E81B9314C' | 
| GRANT SELECT ON `ISVe`.`ISVeOldProjects` TO 'SOUSER'@'%'                                              | 
| GRANT SELECT ON `ISVe`.`ISVeProjects` TO 'SOUSER'@'%'                                                 | 
+-------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> quit
Bye

Check the database connectivity and the accessibility from a remote location.


% mysql -h ben10.sfbay -D ISVe -u SOUSER -pSOUSER
Server version: 5.1.25-rc-standard Source distribution

mysql> show tables;
+-----------------+
| Tables_in_ISVe  |
+-----------------+
| ISVeOldProjects |
| ISVeProjects    |
+-----------------+
2 rows in set (0.03 sec)

mysql> select count(*) from ISVeOldProjects;
+----------+
| count(*) |
+----------+
|     2880 |
+----------+
1 row in set (0.04 sec)

mysql> select count(*) from ISVeProjects;
+----------+
| count(*) |
+----------+
|     4967 |
+----------+
1 row in set (0.33 sec)

mysql> delete from ISVeOldProjects;
ERROR 1142 (42000): DELETE command denied to user 'SOUSER'@'vpn-192-155-222-19.SFBay.Sun.COM' for table 'ISVeOldProjects'

mysql> delete from ISVeProjects;
ERROR 1142 (42000): DELETE command denied to user 'SOUSER'@'vpn-192-155-222-19.SFBay.Sun.COM' for table 'ISVeProjects'

mysql> quit
Bye

Tasks to be Performed at the Client side (End-User's Workstation)

StarOffice and OpenOffice suite of applications can access the MySQL Server using JDBC or native drivers.

MySQL Connector/J is a platform independent JDBC Type 4 driver that is developed specifically to connect to a MySQL database. Using Connector/J, it is possible to connect to almost any version of MySQL Server from any version of StarOffice or OpenOffice.org

Sun|MySQL recently developed a native MySQL driver to facilitate connecting from StarOffice / OpenOffice.org suite of applications to a MySQL database. The new native driver is called MySQL Connector/OpenOffice.org. However the current version of the MySQL Connector for OO.o is compatible only with OpenOffice 3.1, StarOffice 9.1 or newer and it can connect only to MySQL Server 5.1 or later versions. This native connector is supposed to be faster in comparison with the Java connector.

We will explore both MySQL connectors in this section.

Note:

As an end user, you need not be concerned about the internal workings of these MySQL connectors. You just need to worry about installing and configuring the drivers so the *Office applications can connect to the MySQL database in a seamless fashion.

I. Connector/J approach

Installation steps for MySQL Connector/J

Using the following navigation, find the location of the JRE that is being used by StarOffice / OpenOffice.org
- Launch StarOffice / OpenOffice.org
- Tools Menu -> Options
- In the 'Options' window, StarOffice / OpenOffice.org -> Java
Here is a sample screen capture from a Mac running StarOffice 9.

In the above example, /System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home is the location of the JRE. Here onwards, this location will be referred as JRE_HOME.
1. Download the connector from the following web page:
  http://dev.mysql.com/downloads/connector/j/
  
  As of this writing, 5.1.10 is the current version for Connector/J
2. Extract the driver and the rest of the files from the compressed [downloaded] archive
  
  eg.,
```
% gunzip -c mysql-connector-java-5.1.10.tar.gz | tar -xvf -
```
3. Locate the jar file that contains the driver --- mysql-connector-java-5.1.10-bin.jar, and copy it into the <JRE_HOME>/lib/ext directory with 'root' privileges.
  
  eg.,
```
% sudo cp mysql-connector-java-5.1.10-bin.jar /System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home/lib/ext

% ls -l /System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home/lib/ext/*connector*jar
/System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home/lib/ext/mysql-connector-java-5.1.10-bin.jar
```
4. Restart StarOffice / OpenOffice.org
This concludes the installation of MySQL Connector/J.

2. Configuration steps for Connector/J
1. Launch StarOffice / OpenOffice.org
2. In the Welcome screen, click on "Database". A database wizard pops up to help us create, open or connect to an existing database from StarOffice / OpenOffice.org.
3. Since our main interest is only to connect to an existing database in this exercise, click on "Connect to an existing database" radio button and select "MySQL" from the drop-down menu that is underneath the selected radio button.
  
  Click on "Next >>" button
4. In the next screen, select JDBC by clicking on "Connect using JDBC (Java Database Connectivity)" radio button
  
  Click on "Next >>" button
5. In "Set up connection to a MySQL database using JDBC" screen, provide the name of the database, hostname or IP address of the MySQL database server (server URL) that you want to connect to along with the port# on which the MySQL server is actively listening for new database connections.
  
  MySQL JDBC driver class text field will be automatically filled with the string com.mysql.jdbc.Driver. Leave that string intact, and click on "Test Class" button to make sure that the relevant class can be loaded with no issues. Unless the driver class is loaded successfully, you will not be able to connect to the MySQL database. In case of unsuccessful class loading, double check the installation steps for MySQL Connector/J.
  
  Click on "Next >>" button
  
  Note:
  
  In the above screenshot, notice that the "Name of the database" was filled with ISVe?zeroDateTimeBehavior=convertToNull (It is not completely visible in the above screen capture, but you just have to believe me). In this example, ISVe is the database name and zeroDateTimeBehavior is the configuration property which was set to a value of convertToNull. Without this configuration property, Connector/J throws an exception when it encounters date values such as 0000-00-00. In such cases, the error message will be something similar to java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date.
  
  Configuration properties define how Connector/J will make a connection to a MySQL server. The list of Connector/J configuration properties are documented in the following web page:
  
  http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html
  
  If you have more than one configuration property, you can define all of those properties in the "Name of the database" field. The syntax would be:
  <MySQL_DB_Name>?<Config_Property1=Value>&<Config_Property2=Value>&..&<Config_Propertyn=Value>
6. Provide the database user name and the password details in "Set up the user authentication" screen. Click on "Password required" check box if there is a password setup for the database user.
  
  Click on "Test Connection" button to ensure a successful connection to the MySQL database using the credentials provided in this window.
  
  Click on "Next >>" button
7. In the final screen, simply accept the default values and click on 'Finish' button.
  
  "Yes, register the database for me" and "Open the database for edition" are the defaults selected in this screen.
  
  When you click on the 'Finish' button, you will be prompted to provide a name to the database to save it as a file on your local machine. The saved file contains information about the database including the queries, reports and forms that are linked to the MySQL database. The actual data remain in the MySQL database. Hence you need not worry about the size of the file that is being saved on your local machine. It will be small in size.
8. Ultimately the Database area of the Base main window appears as shown in the following screen capture.
  
  Notice the RDBMS name, Type of Connectivity, MySQL Database Name along with the configuration properties, Database user name and the Database server hostname at the bottom of the window.
  
  You will be able to query the database, create new forms/reports etc., from this window. Unfortunately discussion around those topics is beyond the scope of this blog post - so, we will stop here.

II Connector/OpenOffice.org approach

MySQL Connector for OpenOffice.org is a MySQL driver for OpenOffice suite of applications. Even though it appears to be a native driver, MySQL Connector/OpenOffice.org has no implementation for the MySQL Client Server protocol. It is in reality a proxy on the top of MySQL Connector for C++ aka MySQL Connector/C++.

Unlike MySQL Connector/J, Connector/OpenOffice.org has no dependency on JRE, and it can easily be installed using the OpenOffice.org Extension Manager. Due to the underlying native code, Connector/OpenOffice.org may outperform Connector/J in performance.

1. Installation steps for MySQL Connector/OpenOffice.org

Before installing the connector, make sure that you have OpenOffice.org 3.1 [or later] -OR- StarOffice 9.1 [or later] suite installed, and the version of the MySQL server on which the database is hosted is at least 5.1. If any of these requirements are not met, skip this entire section and check the I. Connector/J approach for the instructions that may work with your current versions of StarOffice / OpenOffice and MySQL server.

Download the connector for your platform from the following location:
http://extensions.services.openoffice.org/project/mysql_connector
Launch StarOffice / OpenOffice.org
Bring up the "Extension Manager" by clicking on Tools Menu -> Extension Manager ...
Click on "Add" button, then locate the OpenOffice connector that you downloaded in step #1 (see two steps above). Click on "Open" button. The name of the connector will be something similar to mysql-connector-ooo-....oxt.
Choose appropriate response to the question "For whom do you want to install the extension?". In this example, I chose the option "Only for me".
Read the "Extension Software License Agreement" and accept the agreement to install the Connector/OpenOffice.org as an extension to StarOffice / OpenOffice.org
Restart StarOffice / OpenOffice.org to complete the installation.

2. Configuration steps for MySQL Connector/OpenOffice.org

Launch StarOffice / OpenOffice.org
In the Welcome screen, click on "Database". A database wizard pops up to help us create, open or connect to an existing database from StarOffice / OpenOffice.org.
Since our main interest is only to connect to an existing database in this exercise, click on "Connect to an existing database" radio button and select "MySQL" from the drop-down menu that is underneath the selected radio button.

Click on "Next >>" button
In the next screen, select "Connect native" radio button

Click on "Next >>" button
In "Set up connection to a MySQL database" screen, provide the name of the database, hostname or IP address of the MySQL database server (server URL) that you want to connect to along with the port# on which the MySQL server is actively listening for new database connections. If the MySQL Server is running on the same machine as that of the StarOffice / OpenOffice.org application, you can provide the location of the socket under "Socket" field. If not, leave it blank.

Click on "Next >>" button
Provide the database user name and the password details in "Set up the user authentication" screen. Click on "Password required" check box if there is a password setup for the database user.

Click on "Test Connection" button to ensure a successful connection to the MySQL database using the credentials provided in this window.

Click on "Next >>" button
In the final screen, simply accept the default values and click on 'Finish' button.

"Yes, register the database for me" and "Open the database for edition" are the defaults selected in this screen.

When you click on the 'Finish' button, you will be prompted to provide a name to the database to save it as a file on your local machine. The saved file contains information about the database including the queries, reports and forms that are linked to the MySQL database. The actual data remain in the MySQL database. Hence you need not worry about the size of the file that is being saved on your local machine. It will be small in size.
Ultimately the Database area of the Base main window appears as shown in the following screen capture.

Notice the RDBMS name, Type of Connectivity, MySQL Database Name along with the configuration properties, Database user name and the Database server hostname at the bottom of the window.

You will be able to query the database, create new forms/reports etc., from this window. Unfortunately discussion around those topics is beyond the scope of this blog post - so, we will stop here.

That is all there is to it in installing and configuring the MySQL connectors for *Office suite of applications. Now enjoy the flexibility of fetching the data from your favorite office productivity software.

(Original blog post is at the following location:
http://blogs.sun.com/mandalika/entry/setting_up_staroffice_openoffice_org)

PeopleSoft North American Payroll on Sun Solaris with F5100 Flash Array : A blog Reprise

2009-11-29T22:59:00.001-08:00

(Copied from my other blog at blogs.sun.com. Original post is at: http://blogs.sun.com/mandalika/entry/peoplesoft_north_american_payroll_on)

During the "Sun day" keynote at OOW 09, John Fowler stated that we are #1 in PeopleSoft North American Payroll performance. Later Vince Carbone from our Performance Technologies group went on comparing our benchmark numbers with HP's and IBM's in BestPerf's group blog at Oracle PeopleSoft Payroll (NA) Sun SPARC Enterprise M4000 and Sun Storage F5100 World Record Performance. Meanwhile Jeorg Moellenkamp had been clarifying few things in his blog at App benchmarks, incorrect conclusions and the Sun Storage F5100. Interestingly it all happened while we have no concrete evidence in our hands to show to the outside world. We got our benchmark results validated right before the Oracle OpenWorld, which gave us the ability to speak about it publicly [ and we used it to the extent we could use ]. However Oracle folks were busy with their scheduled tasks for OOW 09 and couldn't work on the benchmark results white paper until now. Finally the white paper with the NA Payroll benchmark results is available on Oracle Applications benchmark web site. Here is the URL:

PeopleSoft Enterprise Payroll 9.0 using Oracle for Solaris on a Sun SPARC Enterprise M4000

Once again the summary of results is shown below but in a slightly different format. These numbers were extracted from the very first page of the benchmark results white papers where PeopleSoft usually highlights the significance of the results and the actual numbers that they are interested in. The results are sorted by the hourly throughput (payments/hour) in the descending order. The goal is to achieve as much hourly throughput as possible. Since there is one 16 stream result as well in the following table, exercise caution when comparing 8 stream results with 16 stream results. In general, 16 parallel job streams are supposed to yield better throughput when compared to 8 parallel job streams. Hence comparing a 16 stream number with an 8 stream number is not an exact apple-to-apple comparison. It is more like comparing an apple to another apple that is half in size. Click on the link that is underneath the hourly throughput values to open corresponding benchmark result.

Oracle PeopleSoft North American Payroll 9.0 - Number of employees: **240,000** & Number of payments: **360,000**
Vendor	OS	Hardware Config	#Job Streams	Elapsed Time (min)	Hourly Throughput Payments per Hour
Sun	Solaris 10 5/09	1x Sun SPARC Enterprise M4000 with 4 x 2.53 GHz SPARC64-VII Quad-Core processors and 32 GB memory 1 x Sun Storage F5100 Flash Array with 40 Flash Modules for data, indexes 1 x Sun Storage J4200 Array for redo logs	8	67.85	318,349
HP	HP-UX	1 x HP Integrity rx6600 with 4 x 1.6 GHz Intel Itanium2 9000 Dual-Core processors and 32 GB memory 1 x HP StorageWorks EVA 8100	16	68.07	317,320
HP	HP-UX	1 x HP Integrity rx6600 with 4 x 1.6 GHz Intel Itanium2 9000 Dual-Core processors and 32 GB memory 1 x HP StorageWorks EVA 8100	8	89.77	240,615^*
IBM	z/OS	1 x IBM zSeries 990 model 2084-B16 with 313 Feature with 6 x IBM z990 Gen1 processors (populated: 13, used: 6) and 32 GB memory 1 x IBM TotalStorage DS8300 with dual 4-way processors	8	91.7	235,551

This is all public information -- so, feel free to draw your own conclusions. ^*At this time of writing, HP's 8 stream results were pulled out of Oracle Applications benchmark web site for some reason I do not know why. Hopefully it will show up again on the same web site soon. If it doesn't re-appear even after a month, probably we can simply assume that the result is withdrawn.

As these benchmark results were already discussed by different people in different blogs, I have nothing much to add. The only thing that I want to highlight is that this particular workload is moderately CPU intensive, but very I/O bound. Hence the better the I/O sub-system, the better the performance. Vince provided an insight on Why Sun Storage F5100 is a good option for this workload, while Jignesh Shah from our ISV-Engineering organization focused on the performance of this benchmark workload with F20 PCIe Card.

Also when dealing with NA Payroll, it is very unlikely to achieve a nice out-of-the-box performance. It requires a lot of database tuning too. As the data sets are very large, we partitioned the data in some of the very hot objects and it showed good improvement in query response times. So if you are a PeopleSoft customer running Payroll application with millions of rows of non-partitioned data, consider partitioning the data. We are currently working on a best practices blueprint document for PeopleSoft North American Payroll that presents a variety of tuning tips like these in addition to the recommended practices for F5100 flash array and flash accelerator F20 PCIe card. Stay tuned ..

Related Blog Post:

PeopleSoft HRMS 8.9 Self-Service Benchmark on M3000 & T5120 Servers

Java Code to Convert TWiki URLs to HTML Links

2009-11-09T01:50:00.000-08:00

Few days ago I was looking for some code or tool with the ability to convert TWiki URLs to equivalent HTML URLs. (For the uninitiated, TWiki supports wide variety of URL formats in addition to the standard HTML format.). I spent almost 3 hours searching the web and trying out different code, Java classes etc., But at the end, none of the things that I found elsewhere seems to have the ability to convert almost all the TWiki URL formats that I'm trying to convert [to HTML]. So, I gave up looking for some readymade code and quickly wrote some Java code to resolve the issue that I'm trying to solve.

Now that I have the code, thought publishing it in my blog would help other people who are looking for something similar to this. So here it is. Download the source code from the following location.

TwikiToHTML.java

The code assumes that the TWiki system is deployed on http://www.mandalika.com (a fake web site), and is capable of handling the following TWiki URL formats:


[[http://twiki.etclabs.com/pub/MDE/IsveSeeameRomanIvanovMeraJanuaryTechEval/MeraJanuaryScoping.pdf][MeraJanuaryScoping]]

[[Main.JNetX#April_22nd_2008_Whats_new_at_Sun][report]]

[[Main.RomanIvanovLDomsCheatSheet][Executiontrackingpage]]

[[MDE.IsveBravoJeffTaylorProgressApamaSolarisx64Pott][Link]]

[[IsveSeeameOrgadKimchiIGTvLabProcedures][docs]]

[[http://scweb.france.com/~fparient/pub/europa08solaris.odp][solarisprezo]]<br>[[http://scweb.france.com/~fparient/pub/europa08fx.pdf][fxbenchmark]]

[[OmalleyDataProject]]

[[http://twiki.etclabs.com/bin/view/Main/MySQL64NvPlan][Plan]],[[http://twiki.etclabs.com/bin/view/Main/IntegrateSFW][Detail]]

MDE.IsveAlphaBogdanVasiliuSynopsysVCSPerformanceOptimization

http://sidc.israel.com/~mk147459/Reports/ana.html

SofCheck

<a href="http://twiki.org">twiki</a>

Sample inputs and outputs:


% java TwikiToHTML



TWiki URL: NotSoPl@inY0uKnow

HTTP equivalent:



TWiki URL: ItIsJustplain

HTTP equivalent: <a href="http://www.mandalika.com/bin/view/Downloads/ItIsJustplain" target="_new">ItIsJustplain</a>



TWiki URL: GiriK@lyanaChakravarth!

HTTP equivalent:



TWiki URL: &ersand

HTTP equivalent:



TWiki URL: Harisha

HTTP equivalent:



TWiki URL: Janaki Lakshmi

HTTP equivalent:



TWiki URL: JanakiLakshmi

HTTP equivalent:



TWiki URL: [[OmalleyDataProject]]

HTTP equivalent: <a href="http://www.mandalika.com/bin/view/Downloads/OmalleyDataProject" target="_new">OmalleyDataProject</a>



TWiki URL: [[Giri][Mandalika]]

HTTP equivalent: <a href="http://www.mandalika.com/bin/view/Downloads/Giri" target="_new">Mandalika</a>



TWiki URL: [[Giri][Mandalika]], [[Kalyana][Chakravarthy]]

HTTP equivalent: <a href="http://www.mandalika.com/bin/view/Downloads/Giri" target="_new">Mandalika</a><BR><a href="http://www.mandalika.com/bin/view/Downloads/Kalyana" target="_new">Chakravarthy</a>



TWiki URL: [[Giri][Mandalika]], [[Kalyana][Chakravarthy]], [[IsveSeeameOrgadKimchiIGTvLabProcedures][docs]]

HTTP equivalent: <a href="http://www.mandalika.com/bin/view/Downloads/Giri" target="_new">Mandalika</a><BR><a href="http://www.mandalika.com/bin/view/Downloads/Kalyana" target="_new">Chakravarthy</a><BR><a href="http://www.mandalika.com/bin/view/Downloads/IsveSeeameOrgadKimchiIGTvLabProcedures" target="_new">docs</a>



TWiki URL: [[Giri][Mandalika]], [[Kalyana][Chakravarthy]], [[IsveSeeameOrgadKimchiIGTvLabProcedures][docs]], [[OmalleyDataProject][Omalley]]

HTTP equivalent: <a href="http://www.mandalika.com/bin/view/Downloads/Giri" target="_new">Mandalika</a><BR><a href="http://www.mandalika.com/bin/view/Downloads/Kalyana" target="_new">Chakravarthy</a><BR><a href="http://www.mandalika.com/bin/view/Downloads/IsveSeeameOrgadKimchiIGTvLabProcedures" target="_new">docs</a><BR><a href="http://www.mandalika.com/bin/view/Downloads/OmalleyDataProject" target="_new">Omalley</a>



TWiki URL: http://sidc.israel.etclabs.com/~mk147459/Reports/ana.html

HTTP equivalent: <a href="http://sidc.israel.etclabs.com/~mk147459/Reports/ana.html" target="_new">ana.html</a>



TWiki URL: http://sidc.israel.etclabs.com/~mk147459/Reports/ana.html,http://technopark02.blogspot.com

HTTP equivalent: <a href="http://sidc.israel.etclabs.com/~mk147459/Reports/ana.html" target="_new">ana.html</a><BR><a href="http://technopark02.blogspot.com" target="_new">technopark02.blogspot.com</a>



TWiki URL: [[http://www.mandalika.com/pub/MDE/IsveSeeameRomanIvanovMeraJanuaryTechEval/MeraJanuaryScoping.pdf][MeraJanuaryScoping]]

HTTP equivalent: <a href="http://www.mandalika.com/pub/MDE/IsveSeeameRomanIvanovMeraJanuaryTechEval/MeraJanuaryScoping.pdf" target="_new">MeraJanuaryScoping</a>



TWiki URL: [[http://www.mandalika.com/bin/view/Main/RomanIvanovRStyleSoftlabRSBankV6][execution details]] <BR> [[http://www.mandalika.com/bin/view/Main/OracleRAConLDom][OracleRAConLDom]]

HTTP equivalent: <a href="http://www.mandalika.com/bin/view/Main/RomanIvanovRStyleSoftlabRSBankV6" target="_new">execution details</a><BR><a href="http://www.mandalika.com/bin/view/Main/OracleRAConLDom" target="_new">OracleRAConLDom</a>



TWiki URL: [[http://www.mandalika.com/pub/ISVeProjects/ISVeProject20080213161851/Project20080213161851scoping.odt][scoping document]] , [[http://www.mandalika.com/pub/MDE/IsveSeeameRomanIvanovMeraJanuaryTechEval/MeraJanuaryScoping.pdf][MeraJanuaryScoping]] <BR> [[http://sidc.israel.etclabs.com/~ok134283/techeval/snooggie.html][techeval]] , [[http://sidc.israel.etclabs.com/~ok134283/techeval/servision.html][techeval]]

HTTP equivalent: <a href="http://www.mandalika.com/pub/ISVeProjects/ISVeProject20080213161851/Project20080213161851scoping.odt" target="_new">scoping document</a><BR><a href="http://www.mandalika.com/pub/MDE/IsveSeeameRomanIvanovMeraJanuaryTechEval/MeraJanuaryScoping.pdf" target="_new">MeraJanuaryScoping</a><BR><a href="http://sidc.israel.etclabs.com/~ok134283/techeval/snooggie.html" target="_new">techeval</a><BR><a href="http://sidc.israel.etclabs.com/~ok134283/techeval/servision.html" target="_new">techeval</a>



TWiki URL: [[Main.RomanIvanovLDomsCheatSheet][Execution tracking page]]

HTTP equivalent: <a href="http://www.mandalika.com/bin/view/Main/RomanIvanovLDomsCheatSheet" target="_new">Execution tracking page</a>



TWiki URL: [[MDE.IsveBravoJeffTaylorProgressApamaSolarisx64Pott][Link]]

HTTP equivalent: <a href="http://www.mandalika.com/bin/view/MDE/IsveBravoJeffTaylorProgressApamaSolarisx64Pott" target="_new">Link</a>



TWiki URL: [[Main.JNetX#April_22nd_2008_Whats_new_at_Sun][report]]

HTTP equivalent: <a href="http://www.mandalika.com/bin/view/Main/JNetX#April_22nd_2008_Whats_new_at_Sun" target="_new">report</a>



TWiki URL: [[Main.RomanIvanovLDomsCheatSheet][Execution tracking page]], [[MDE.IsveBravoJeffTaylorProgressApamaSolarisx64Pott][Link]]   <BR>  [[Main.JNetX#April_22nd_2008_Whats_new_at_Sun][report]]

HTTP equivalent: <a href="http://www.mandalika.com/bin/view/Main/RomanIvanovLDomsCheatSheet" target="_new">Execution tracking page</a><BR><a href="http://www.mandalika.com/bin/view/MDE/IsveBravoJeffTaylorProgressApamaSolarisx64Pott" target="_new">Link</a><BR><a href="http://www.mandalika.com/bin/view/Main/JNetX#April_22nd_2008_Whats_new_at_Sun" target="_new">report</a>



TWiki URL: [[http://www.mandalika.com/bin/view/MDE/IsveKylinPDSChinaVolumeISVWorldonline][World-Online]]

HTTP equivalent: <a href="http://www.mandalika.com/bin/view/MDE/IsveKylinPDSChinaVolumeISVWorldonline" target="_new">World-Online</a>



TWiki URL: [[ISVeProjects.IsveIndividualSummaryCarylTakvorian][Caryl Takvorian]] Tom Duell

HTTP equivalent: <a href="http://www.mandalika.com/bin/view/ISVeProjects/IsveIndividualSummaryCarylTakvorian" target="_new">Caryl Takvorian</a>



TWiki URL: [[http://www.mandalika.com/bin/view/MDE/IsveGANEIndividualSummaryAndrewWalton][Andrew Walton]]

HTTP equivalent: <a href="http://www.mandalika.com/bin/view/MDE/IsveGANEIndividualSummaryAndrewWalton" target="_new">Andrew Walton</a>



TWiki URL: [[http://etclabswebcollab.east.etclabs.com/gm/folder-1.11.1890618][Project Folder]]

HTTP equivalent: <a href="http://etclabswebcollab.east.etclabs.com/gm/folder-1.11.1890618" target="_new">Project Folder</a>



TWiki URL: [[http://etclabswebcollab.east.etclabs.com/gm/document-1.9.3262994/SUN%25E4%25B8%25BB%25E6%259C%25BAOCS%25E6%2580%25A7%25E8%2583%25BD%25E6%25B5%258B%25E8%25AF%2595%25E6%258A%25A5%25E5%2591%258A.doc][Cust. Report]], [[http://etclabswebcollab.east.etclabs.com/gm/document-1.9.3270163/ocs_perftune_t2000.odt][ISVE Report]]

HTTP equivalent: <a href="http://etclabswebcollab.east.etclabs.com/gm/document-1.9.3262994/SUN%25E4%25B8%25BB%25E6%259C%25BAOCS%25E6%2580%25A7%25E8%2583%25BD%25E6%25B5%258B%25E8%25AF%2595%25E6%258A%25A5%25E5%2591%258A.doc" target="_new">Cust. Report</a><BR><a href="http://etclabswebcollab.east.etclabs.com/gm/document-1.9.3270163/ocs_perftune_t2000.odt" target="_new">ISVE Report</a>



TWiki URL: MDE.IsveAlphaBogdanVasiliuSynopsysVCSPerformanceOptimization

HTTP equivalent: <a href="http://www.mandalika.com/bin/view/MDE/IsveAlphaBogdanVasiliuSynopsysVCSPerformanceOptimization" target="_new">IsveAlphaBogdanVasiliuSynopsysVCSPerformanceOptimization</a>



TWiki URL: [[Main.RomanIvanovLDomsCheatSheet]]

HTTP equivalent: <a href="http://www.mandalika.com/bin/view/Main/RomanIvanovLDomsCheatSheet" target="_new">RomanIvanovLDomsCheatSheet</a>



TWiki URL: MDE.PostgreSQLAutoTune <BR> [[Main.RomanIvanovLDomsCheatSheet]] , MDE.IsveAlphaBogdanVasiliuSynopsysVCSPerformanceOptimization

HTTP equivalent: <a href="http://www.mandalika.com/bin/view/Main/RomanIvanovLDomsCheatSheet" target="_new">RomanIvanovLDomsCheatSheet</a><BR><a href="http://www.mandalika.com/bin/view/MDE/PostgreSQLAutoTune" target="_new">PostgreSQLAutoTune</a><BR><a href="http://www.mandalika.com/bin/view/MDE/IsveAlphaBogdanVasiliuSynopsysVCSPerformanceOptimization" target="_new">IsveAlphaBogdanVasiliuSynopsysVCSPerformanceOptimization</a>



TWiki URL: [[http://www.mandalika.com/bin/view/ISVeProjects/IsveIndividualSummaryMohammedYousuf][Mohammed Yousuf]], Thiagarajan Chandrasekaran

HTTP equivalent: <a href="http://www.mandalika.com/bin/view/ISVeProjects/IsveIndividualSummaryMohammedYousuf" target="_new">Mohammed Yousuf</a>



TWiki URL: MDE.IsveDabuboirProsDamienCookeMyStaffMyStaffPostgreSQLAdoption  [[http://twiki.etclabs/pub/MDE/IsveDabuboirProsDamienCookeMyStaffMyStaffPostgreSQLAdoption/MyStaff.html][Project Status]]

HTTP equivalent: <a href="http://twiki.etclabs/pub/MDE/IsveDabuboirProsDamienCookeMyStaffMyStaffPostgreSQLAdoption/MyStaff.html" target="_new">Project Status</a><BR><a href="http://www.mandalika.com/bin/view/MDE/IsveDabuboirProsDamienCookeMyStaffMyStaffPostgreSQLAdoption" target="_new">IsveDabuboirProsDamienCookeMyStaffMyStaffPostgreSQLAdoption</a>



TWiki URL: [[http://www.xxx.yyy]], <a href="http://www.xyz.com">xyz</a>, http://www.yahoo.com , <a href="www.giril.com">giri</a> , [[http://www.harisha.com][harisha]], <a href="www.etclabs.com">etclabs</a>

HTTP equivalent: <a href="http://www.xyz.com">xyz</a><BR><a href="www.giril.com">giri</a><BR><a href="www.etclabs.com">etclabs</a><BR><a href="http://www.xxx.yyy" target="_new">www.xxx.yyy<BR><a href="http://www.harisha.com" target="_new">harisha</a><BR><a href="http://www.yahoo.com" target="_new">www.yahoo.com</a>



TWiki URL: New link YousufCertificationofOracleRAConSolaris10u6Containers ,  old link http://blogs.etclabs.com/rac

HTTP equivalent: <a href="http://blogs.etclabs.com/rac" target="_new">rac</a>



TWiki URL: attached email.

HTTP equivalent: 



TWiki URL: [[http://www.mandalika.com/bin/view/MDE/Caucho Caucho]] [[http://www.mandalika.com/bin/view/MDE/ISVeProject20070812164315 20070812164315]]

HTTP equivalent: <a href="http://www.mandalika.com/bin/view/MDE/Caucho" target="_new">Caucho</a><BR><a href="http://www.mandalika.com/bin/view/MDE/ISVeProject20070812164315" target="_new">20070812164315</a>



TWiki URL: http://download.sap.com/download.epd?context=40E2D9D5E00EEF7CB82813BD3F95797BAEB80527B36EC026E03386D659E4DE48

HTTP equivalent: <a href="http://download.sap.com/download.epd?context=40E2D9D5E00EEF7CB82813BD3F95797BAEB80527B36EC026E03386D659E4DE48" target="_new">download.epd?context=40E2D9D5E00E ... BAEB80527B36EC026E03386D659E4DE48</a>

Disclaimer:
This code is poorly documented, not perfect and most importantly may not even meet the minimum Java coding standards. However it does the intended job with minor modifications. Feel free to use, modify, reproduce or redistribute - but please do not come back complaining. I have no intention of fixing or improving this code.

Sun achieves the Magic Number 50,000 on T5440 with Oracle Business Intelligence EE 10.1.3.4

2009-10-14T00:49:00.000-07:00

Less than two months ago, Sun Microsystems published an Oracle Business Intelligence benchmark with the best single system performance of 28,000 concurrent BI EE users at ~75% CPU utilization. Sun and Oracle Corporation announced another Oracle Business Intelligence benchmark result today with two identical T5440 servers in the Oracle BI Cluster serving 50,000 concurrent BI EE users.

An Oracle white paper with Sun's 50,000 user benchmark results can be accessed from Oracle's Business Intelligence web.

The hardware specifications for each of the T5440s are similar to the hardware that was used in the prior benchmark effort on a single T5440 server. However this time the Presentation Catalog (also frequently referred as the Web Catalog) was moved to a T5220 server where the NFS server was running. Besides this the only other change from the earlier 28,000 user benchmark exercise is the addition of another T5440 to the test rig.

The following graph shows the scalability of the application from one node to four nodes to eight nodes running on T5440 servers.

Without further ado, here is the summary of the benchmark results along with their significance and some interesting facts:

One of the major goals of this benchmark effort is to show the horizontal and vertical scalability of the application (OBIEE) by highlighting the superior performance and the resilience of the underlying hardware (T5440) and the operating system (Solaris). Needless to say the goal has been met.
Another goal of this benchmark is to show decent number of concurrent BI EE users executing transactions with good response times. Since we already showed the maximum load that can be achieved on a single BI instance (7500 users) and on a single T5440 server running multiple BI instances (28,000 users), this time we did not attempt to get the peak number that can be achieved from the two T5440 servers in the benchmark environment. Now that there is an additional server in the test setup that is taking care of the Presentation Catalog and the database server, 2 * 28000 = 56,000 BI EE users would have been an achievable target -- but we opted to stop at the "magic" and the "respectable" number 50,000 instead.
The entire benchmark run lasted for about 9 hours 45 minutes, and out of which 8 hours were the rampup hours where the 50,000 BI virtual users were logging into the application few users at a time. LoadRunner tool reported only 4 errors for the entire duration of the run; and there are zero errors in the 60 minute steady state period during which the statistics reported in the document were collected.
Two Sun SPARC Enterprise T5440 servers each with 4 x 8-Core 1.6 GHz UltraSPARC T2 Plus processors delivered the best performance of 50,000 concurrent BI EE users at around 63% CPU utilization.
The BI EE Cluster was deployed on two T5440 servers running Solaris 10 5/09 operating system. All the nodes in the BI Cluster were consolidated onto two T5440 servers using the free and efficient Solaris Containers virtualization technology.
The Presentation Catalog was hosted on ZFS powered file system that was created on top of four internal Solid State Drive (SSD) disks. The Catalog was shared among all eight BI nodes in the cluster as an NFS share. One 8-Core 1.2 GHz UltraSPARC T2 processor powered T5220 server was used to run the NFS server. Due to the minimal activity of the database, Oracle 11g database was also hosted on the same server. Solaris 10 5/09 is the operating system.
Solid State Drive (SSD) disks with ZFS file system showed significant I/O performance improvement over traditional disks for the Presentation Catalog activity. In addition, ZFS helped get past the UFS limitation of 32767 sub-directories in a Presentation Catalog directory.
Caching was turned ON at the application server, which led to minimal database activity on the server. Note hat the caching mechanism was turned ON even in the prior benchmark exercise.
The low end CoolThreads CMT Server T5220 and the mid-range T5440 server once again proved to be ideal candidates to deploy and run multi-thread workloads by exhibiting resilient performance when handling large number of simultaneous requests from 50,000 BI EE virtual users. T5220 handled large number of concurrent asynchronous read/write requests from eight different NFS clients.
NFS v3 was configured at the NFS Server as well as at the NFS Client nodes. NFS version 4 is the default on Solaris 10, and it might have worked as expected. However a handful of bug reports prompted us to go with the more matured and less buggy version 3.
3283 watts is the average power consumption when all the 50,000 concurrent BI users are in the steady state of the benchmark test. That is, in the case of similarly configured workloads, the T5440 server supports 15.2 users per watt of energy consumed and supports 5,000 users per rack unit.

A summary of the results with system-wide averages of CPU and memory utilization is shown below. The latest results are highlighted in blue color.

#Vusers	Clustered	#BI Nodes	#CPU	#Core	RAM	CPU	Memory	Avg Trx Response Time	#Trx/sec
7,500	No	1	1	8	32 GB	72.85%	18.11 GB	0.22 sec	155
28,000	Yes	4	4	32	128 GB	75.04%	76.16 GB	0.25 sec	580
50,000	Yes	8	8	64	256 GB	63.32%	172.21 GB	0.28 sec	1031

TOPOLOGY DIAGRAM

The topology diagram in the benchmark results white paper is almost illegible. Here is the original topology diagram that was inserted into the white paper.

Quite frankly I'm not very proud of this drawing -- but that's the best that I could come up with in a short span. Rather than showing the flow of communication between each and every component in the benchmark setup, I simplified the drawing by introducing a "black box" sort of thing - "private network" - in the middle, which protected the drawing from getting messy.

CPU USAGE GRAPH

The following two-dimensional graph shows the CPU utilization patterns at all 3 nodes in the benchmark setup for the 60 minute steady state of the benchmark run. This graph was generated using the free GNUplot tool with sar data as the inputs.

COMPETITIVE LANDSCAPE

And finally here is a quick summary of all the results that are published by different vendors so far with similar benchmark kit. Feel free to draw your own conclusions. All this is public information. Check the corresponding benchmark reports by clicking on the URLs under the "#Users" column.

Server	Processors					#Users	OS
Server	Chips	Cores	Threads	GHz	Type	#Users	OS
2 x Sun SPARC Enterprise T5440 (APP) 1 x Sun SPARC Enterprise T5220 (NFS,DB)	8 1	64 8	512 64	1.6 1.2	UltraSPARC T2 Plus UltraSPARC T2	50,000	Solaris 10 5/09
1 x Sun SPARC Enterprise T5440	4	32	256	1.6	UltraSPARC T2 Plus	28,000	Solaris 10 5/09
5 x Sun Fire T2000	1	8	32	1.2	UltraSPARC T1	10,000	Solaris 10 11/06
3 x HP DL380 G4	2	4	4	2.8	Intel Xeon	5,800	OEL
1 x IBM x3755	4	8	8	2.8	AMD Opteron	4,000	RHEL4

Before you go, do not forget to check the best practices for configuring / deploying Oracle Business Intelligence on top of Solaris 10 running on Sun CMT hardware.

Related Blog Posts:
T5440 Rocks [again] with Oracle Business Intelligence Enterprise Edition Workload

(Originally posted on blogs.sun.com at:
http://blogs.sun.com/mandalika/entry/oracle_business_intelligence_10_1)

Hindi Music - V.Man's Recommendations

2009-09-18T21:52:00.000-07:00

*****
1. Race - Race is On My Mind
2. Bluff Master - Right Here Right Now
3. Rang De Basanti - Pathshala
4. Bhool Bhulaiya - Hare Rama Hare Krishna

****
1. Pyar Tu Ne Kya Kiya - Kambakth Ishq
2. Bluff Master - Bure Bure
3. Dus - Deedar De
4. Cash - Title Song
5. Dhoom 2 - Dhoom Again
6. Dus - Dus Bahane
7. Delhi 6 - Masakali
8. Cash - Mind Blowing Mahiya
9. Tashan - Chaliya Chaliya
10. Shootout at Lokhandwala - Ganapat
11. Blue - Chiggy Wiggy

***
1. What's Your Rashee - Chehre Jo Dekhe Hain
2. Taxi No 9211 - Meter Down
3. Main Aur Mrs Khanna - Happening
4. 13B - Oh Crazy Mama
5. Kaminey - Rat ke Dhai Baje
6. Tashan - Dil Hara
7. Luck - Jee Le

**
1. Aksar - Jhalak Dikhlaja
2. Slumdog Millionaire - Jai Ho

*
Rest of them ...

T5440 Rocks [again] with Oracle Business Intelligence Enterprise Edition Workload

2009-08-23T02:28:00.000-07:00

A while ago, I blogged about how we scaled Siebel 8.0 up to 14,000 concurrent users by consolidating the entire Siebel stack on a single Sun SPARC^® Enterprise T5440 server with 4 x 1.4 GHz eight-core UltraSPARC^® T2 Plus Processors. OLTP workload was used in that performance benchmark effort.

We repeated a similar effort by collaborating with Oracle Corporation, but with an OLAP workload this time around. Today Sun and Oracle announced the 28,000 user Oracle Business Intelligence Enterprise Edition (OBIEE) 10.1.3.4 benchmark results on a single Sun SPARC Enterprise T5440 server with 4 x 1.6 GHz eight-core UltraSPARC T2 Plus Processors running Solaris 10 5/09 operating system. An Oracle white paper with Sun's 28,000 user benchmark results is available on Oracle's benchmark web site.

Some of the notes and key take away's from this benchmark are as follows:

Key specifications for the Sun SPARC Enterprise T5440 system under test are: 4 x UltraSPARC T2 Plus processors, 32 cores, 256 compute threads and 128 GB of memory in a 4RU space.
The entire OBIEE solution was deployed on a single Sun SPARC Enterprise T5440 server using Oracle BI Cluster software.
The BI Cluster was configured with 4 x BI nodes. Each of those BI nodes were configured to run inside a Solaris Container.
1. Each Solaris Container was configured with one physical processor (that is, 8 cores or 64 virtual cpus), and 32 GB physical memory.
2. Each BI node was configured to run BI Server, Presentation Server and OC4J Web Server
3. Two of the BI nodes have the BI Cluster Controller running (primary & secondary)
4. One out of four Containers was sharing CPU and memory resources with Oracle 11g RDBMS and the host operating system that are running in the global zone
Caching was turned ON at the application server, which led to minimal database activity on the server.
1. In other words, one can use these results only to size the hardware requirements for a complete BI EE deployment excluding the database server.
2. All the OBIEE benchmark results published so far are with the caching turned ON. This fact was not explicitly mentioned in some of the benchmark results white papers. Check the competitive Landscape for the pointers to different benchmark results published by different vendors.
From our experiments with the OBIEE benchmark workload, it appears that a BI deployment with a single non-cluster BI node could reasonably scale well up to 7,500 active users on a T5440 server. To scale beyond 7,500 concurrent users, you might need another instance of BI. Of course, your mileage may vary.
BI EE exhibited excellent horizontal scalability when multiple BI nodes were clustered using BI Cluster software. Four BI nodes in the Cluster were able to handle 28,000 concurrent users with minimal impact on the overall average transaction response times.
The underlying hardware must behave well in order for the application to scale and perform well -- so, credit goes to UltraSPARC T2 Plus powered Sun SPARC Enterprise T5440 server as well. In other words, it is fair to say the combination of (T5440 + OBIEE) performs and scales well on Solaris.
A summary of the results with system-wide averages of CPU and memory utilization is shown below.

#Vusers Clustered #BI Nodes #CPU #Core RAM CPU Memory Avg Trx Response Time #Trx/sec
7,500 No 1 1 8 32 GB 72.85% 18.11 GB 0.22 sec 155
28,000 Yes 4 4 32 128 GB 75.04% 76.16 GB 0.25 sec 580
Internal Solid State Drive (SSD) with ZFS file system showed significant I/O performance improvement over traditional disk for the BI catalog activity. In addition, ZFS helped get past the UFS limitation of 32,767 sub-directories in a BI catalog directory.
The benchmark demonstrated that 64-bit BI EE platform is immune to the 4 GB virtual memory limitation of the 32-bit BI EE platform -- hence can potentially support even more users and have larger caches as long as the hardware resources are available.
2,107 watts is the average power consumption when all the 28,000 concurrent users are in the steady state of the benchmark test. That is, in the case of similarly configured workloads, T5440 supports 13.2 users per watt of the power consumed; and supports 7,000 users per rack unit.

TOPOLOGY DIAGRAM:

A picture is worth a thousand words. The following topology diagram(s) says it all about the configuration.

1. Single Node BI Non-Cluster Configuration : 7,500 Concurrent Users

Even though the Solaris Container was shown in a cloud like graphical form, it has nothing to do with the "Cloud Computing". It is just a side effect of fancy drawing.

2. Four Node BI Cluster Configuration : 28,000 Concurrent Users

COMPETITIVE LANDSCAPE

Here is a quick summary of all the results that are published by different vendors. Feel free to draw your own conclusions. All this is public information. Check the corresponding benchmark reports by clicking on the URLs under the "#Users" column.

Server	Processors					#Users	OS
Server	Chips	Cores	Threads	GHz	Type	#Users	OS
1 x Sun SPARC Enterprise T5440	4	32	256	1.6	UltraSPARC T2 Plus	28,000	Solaris 10 5/09
5 x Sun Fire T2000	1	8	32	1.2	UltraSPARC T1	10,000	Solaris 10 11/06
3 x HP DL380 G4	2	4	4	2.8	Intel Xeon	5,800	OEL
1 x IBM x3755	4	8	8	2.8	AMD Opteron	4,000	RHEL4

CAUTION

Although T5440 possesses a ton of great qualities, it might not be suitable for deploying workloads with heavy single-threaded dependencies. The T5440 is an excellent hardware platform for multi-threaded, and moderately single-threaded/multi-process workloads. When in doubt, it is a good idea to leverage Sun Microsystems' Try & Buy program to try the workloads on the T5440 server before making the final call.

Check the second part of this blog post for the best practices for configuring / deploying Oracle Business Intelligence on top of Solaris 10 running on Sun CMT hardware.

Related Blog Posts:

Oracle Business Intelligence on Sun : Few Best Practices

2009-08-23T02:14:00.000-07:00

The following suggested best practices are applicable to all Oracle BI EE deployments on Sun hardware (CMT and M-class) running Solaris 10 or later. These recommendations are based on our observations from the 28,000 user benchmark on Sun SPARC Enterprise T5440. It is not the complete list, and your mileage may vary.

Ensure that the system's firmware is up-to-date.
Upgrade to the latest update release of Solaris 10.
Solaris runs in 64-bit mode by default on SPARC platform. Consider running 64-bit BI EE on Solaris.
Enable 256M large pages on all nodes. By default, the latest update of Solaris 10 will use a maximum of 4M pages even when 256M pages are a good fit.
On larger systems with more CPUs or CPU cores, try not to deploy Oracle BI EE in the global zone.
If the BI catalog is hosted on a local file system, create a ZFS file system to store the catalog.
Ensure that all the BI components in the cluster are configured in a many-to-many fashion
For proper load balancing, configure all BI nodes to be almost identical in the BI Cluster
When planning to add an identically configured new node to the BI Cluster, simply clone an existing well-configured BI node running in a non-global zone.
Increase the file descriptors limit. Edit SAROOTDIR/setup/systunesrv.sh to increase the value from 1024 to any other value of your choice. In addition you must increase the shell limit using the ulimit -n command
Configure 256M large pages for the JVM heap of Chart server and OC4J web server (this recommendation is equally applicable to other web servers such as WebLogic or Sun Java system Web Server). Also use parallel GC, and restrict the number of parallel GC threads to 1/8th of the number of virtual CPUs.

eg.,
```
-XX:LargePageSizeInBytes=256M -XX:+UseParallelGC -XX:ParallelGCThreads=8
```
The Oracle BI Presentation Server keeps the access information of all the users in the Web Catalog. When there are large number of unique BI users, it can take a significant amount of time to look up a user if all the users reside in one single directory. To avoid this, hash the user directories. It can be achieved by having the following entry in SADATADIR/web/config/instanceconfig.xml

eg.,
```
<Catalog>
<HashUserHomeDirectories>2</HashUserHomeDirectories>
</Catalog>
```
HashUserHomeDirectories specifies the number of characters to use to hash user names into sub directories. When this element is turned on, for example, the default name for user Steve's home directory would become /users/st/steve.
BI Server and BI Presentation Server processes create many temporary files while rendering reports and dashboards for a user. This can result in significant I/O activity on the system. The I/O waits can be minimized by pointing the temporary directories to a memory resident file system such as /tmp on Solaris OS. To achieve this, add the following line to the instanceconfig.xml configuration file.

eg.,
```
<TempDir>/tmp/OracleBISAW</TempDir>
```
Similarly the Temporary directory (SATEMPDIR) can be pointed to a memory resident file system such as /tmp to minimize the I/O waits.

Instructions to Create a Facebook Application

2009-07-27T11:04:00.000-07:00

Here is a quick HOW-To document with step-by-step instructions to create a simple Facebook application.

Create an account with Facebook at facebook.com
To create an application in Facebook, go to Facebook Developers web site
Click on Set Up New Application.
Name the Appliaction and click on save changes.
Enter a brief description for the application.
On the left side of the page click on Canvas.
Fill the application name in Canvas Page URL field.
Fill the Canvas Callback URL field.

Canvas Callback URL is the address of the website where the appliaction is hosted.
For example if the appliaction is hosted on a website www.xyz.com, then the Canvas Callback URL address will be http://www.xyz.com/your_application.

Click on save changes.
A summary of the application is shown at this point.
Sample code is shown at the bottom of this page. Click on the sample code.
Download and unzip the tar.gz file.

In the footprints folder, you will find a sample Facebook application with the file name index.php

Facebook appliaction can be developed in JavaScript, PHP or HTML.

For more details about the langauges in which the application can be developed, check Facebook Developers web site.

Create your Facebook application and host it on any web site of your choice. Be aware that Facebook does not host the actual application - so you are on your own in finding a hosting site to deploy your Facebook application.

You can test the application from the Canvas Page URL address.

The url will be in the format http://apps.facebook.com/appliaction_name/

Few Caveats:

If the application is developed in JavaScript, note that all the functions may not work as expected in Facebook. For example, to set the value of 5 to a text box, the following is the code in JavaScript:

var k = 5; document.getElementById("someid").value = k;

The above code may not work in Facebook application. The following modified code works in Facebook:

var k = 5; document.getElementById("someid").setValue(k);

To see all the Facebook equivalent functions for the JavaScript functions, check the FBJS wiki page.

If you encounter the error, FBML Error : illegal tag "body" under "fb:canvas", remove the <body> tag from the HTML application. When Facebook parses the appliaction, it addes the <body> tag automatically.

The code shown below is ready to be used as an example Facebook application. This a very basic calculator with limited functionality.

To test how this example code works, visit the following URL:
http://apps.facebook.com/basic_calc

Sample Facebook Application : Simple Calculator


<html>

<head>
<style type = "text/css">
input {
 width : 30px;
 height: 30px;
 font-size: 1.2em;
 color: red;
       }
</style>

<script language="JavaScript">
var num2 = 0 , num1=0;

function Assign(myvalue)  
{
 var button_value , Key = 0; 
 button_value = myvalue.getValue();

 switch(button_value)
 {
  case '+':
  case '-':
  case '*':
  case '/':
  case '%':
  {
   key = button_value;
   num1 = num2;
   num2 = 0;
   break;
  }

  case '=':
  {
   if (key == '+')
   {
    Sum(num1,num2);
   }

   if (key == '-')
   {
    Subtraction(num1,num2);
   }

   if (key == '*')
   {
    Product(num1,num2);
   }

   if (key == '/')
   {
    Reminder(num1,num2);
   }

   if (key == '%')
   {
    Modulo(num1,num2);
   }
   break;
  }

  case '+/-':
  {
   num2 = num2 * (-1);
   document.getElementById("output").setValue(num2);
   break;
  }

  default:
  {
   button_value = parseInt(button_value);
   combine(button_value);
   break;
  }
 }
}

function combine(input) 
{
 num2 = (num2 * 10) + input;
 document.getElementById("output").setValue(num2);
}

function Cancel() 
{
 num2 = 0;
 num1 = 0;
 document.getElementById("output").setValue(num2);
}

function Sum(val1,val2) 
{
 var result;
 result = val1+val2;
 document.getElementById("output").setValue(result);
 num2 = 0;
 num1 = 0;
}

function Subtraction(val1,val2) 
{
 var result;
 result = val1-val2;
 document.getElementById("output").setValue(result);
 num2 = 0;
 num1 = 0;
}

function Product(val1,val2) 
{
 var result;
 result = val1*val2;
 document.getElementById("output").setValue(result);
 num2 = 0;
 num1 = 0; 
}

function Reminder(val1,val2) 
{
 var result;
 result = val1/val2;
 document.getElementById("output").setValue(result);
 num2 = 0;
 num1 = 0;
}

function Modulo(val1,val2) 
{
 var result;
 result = val1%val2;
 document.getElementById("output").setValue(result);
 num2 = 0;
 num1 = 0;
}

</script>

<title>Very Basic Calculator</title>
</head>

<form>

<table border="1" bgcolor = "#CDCDCD">

<tr>
<td colspan="6"><input id ="output" type="text" name="result" value="0"  style="width: 169px; height: 30px; color:blue;" readonly = "readonly"/></td>
</tr>

<tr>
<td><input type="button"  name="one" value="1" onclick = "Assign(this)"/></td>
<td><input type="button"  name="two" value="2" onclick = "Assign(this)"/></td>
<td><input type="button"  name="three" value="3" onclick = "Assign(this)"/>
<td><input type="button"  name="add" value="+" onclick = "Assign(this)"/></td>
<td rowspan="2"><input type="button"  name="clear" style = "height : 64px;" value="C" onclick = "Cancel()"/></td>
</tr>

<tr>
<td><input type="button"  name="four" value="4" onclick = "Assign(this)"/></td>
<td><input type="button"  name="five" value="5" onclick = "Assign(this)"/></td>
<td><input type="button"  name="six" value="6" onclick = "Assign(this)"/></td>
<td><input type="button"  name="subtract" value="-" onclick = "Assign(this)"/></td>
</tr>

<tr>
<td><input type="button"  name="seven" value="7" onclick = "Assign(this)"/></td>
<td><input type="button"  name="eight" value="8" onclick = "Assign(this)"/></td>
<td><input type="button"  name="nine" value="9" onclick = "Assign(this)"/></td>
<td><input type="button"  name="multiply" value="*" onclick = "Assign(this)"/></td>
<td rowspan="2"><input type="button"  name="equals" style = "height : 64px;" value="=" onclick = "Assign(this)"/></td>
</tr>

<tr>
<td><input type="button"  name="zero" value="0" onclick = "Assign(this)"/></td>
<td><input type="button"  name="negative" value="+/-" onclick = "Assign(this)"/></td>
<td><input type="button"  name="modulus" value="%" onclick = "Assign(this)"/></td>
<td><input type="button"  name="divide" value="/" onclick = "Assign(this)"/></td>
</tr>

</table>

</form>

</html>

________________
Technorati Tags:
Facebook

Oracle Business Intelligence : Workaround / Solution to "[46036] Internal Assertion" Error

2009-07-12T23:49:00.000-07:00

Symptom:

When checking in changes to Oracle BI repository (RPD), Admintool fails with an error message:


    [46036] Internal Assertion: Condition FALSE, file server/Utility/Generic/NQThreads/SUGThread.cpp, line 515

Solution / Workaround:

Edit <BI_HOME>/server/Config/NQSConfig.INI configuration file to increase the value of SERVER_THREAD_STACK_SIZE parameter. Replace the line SERVER_THREAD_STACK_SIZE = 0; with SERVER_THREAD_STACK_SIZE = 512 KB; and restart the Analytics server (SAS)

________________
Technorati Tags:
Oracle | Business Intelligence | Siebel Analytics

Mac OS X 10.5 Tip: Outgoing Only sendmail

2009-06-05T02:14:00.000-07:00

By default, sendmail is disabled on Mac OS X 10.5 (Leopard). If you just want to enable/disable sending outgoing mail, run the following highlighted commands.


% sudo postfix start
Password:
postfix/postfix-script: starting the Postfix mail system

% netstat -an | grep LIST | grep 25
tcp4       0      0  127.0.0.1.25           *.*                    LISTEN

% sudo postfix stop
postfix/postfix-script: stopping the Postfix mail system

% netstat -an | grep LIST | grep 25
%

One of several possible error messages [that might have lead you to this page] is pasted below. The above solution should fix it.


javax.mail.MessagingException: Could not connect to SMTP host: localhost, port: 25;
  nested exception is:
 java.net.ConnectException: Connection refused
 at com.sun.mail.smtp.SMTPTransport.openServer(SMTPTransport.java:1391)
 at com.sun.mail.smtp.SMTPTransport.protocolConnect(SMTPTransport.java:412)
 at javax.mail.Service.connect(Service.java:288)
 at javax.mail.Service.connect(Service.java:169)
 at javax.mail.Service.connect(Service.java:118)
 at javax.mail.Transport.send0(Transport.java:188)
 at javax.mail.Transport.send(Transport.java:118)
        ...
        ...
Caused by: java.net.ConnectException: Connection refused
 at java.net.PlainSocketImpl.socketConnect(Native Method)
 at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)
 at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)
 at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)
 at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:432)
 at java.net.Socket.connect(Socket.java:520)
 at java.net.Socket.connect(Socket.java:470)
 at com.sun.mail.util.SocketFetcher.createSocket(SocketFetcher.java:233)
 at com.sun.mail.util.SocketFetcher.getSocket(SocketFetcher.java:189)
 at com.sun.mail.smtp.SMTPTransport.openServer(SMTPTransport.java:1359)
 ... 9 more

Installing Siebel Web Extension (SWE) on top of Sun Java System Web Server 7.0

2009-05-23T01:16:00.000-07:00

As of today, Sun Java System Web Server 7.0 is not a certified platform to deploy Siebel 8.0 enterprise on. We are working with Oracle Corporation to make this certification happen so our customers can take advantage of the performance optimizations that went into the web server release 7.0.

Meanwhile those who want to give it a try can do so with little effort. In release SJSWS 7.0, the start/stop/restart/.. scripts were appropriately relocated to bin directory under the virtual web server instance. The installer for Siebel 8.0 Web Server Extension looks for the start script [of the web server] under the home directory of the virtual web server instance. (because it was the default location until the release of SJSWS 7.0). The installation fails if the installer cannot find the start script in the location it is expecting it to be.

Due to the relocation mentioned above, installation of the Siebel Web Server Extension fails at the very last step where it tries to modify the start script with a bunch of LD_PRELOADs so the Siebel Web Extension loads up and runs on the Sun Java System Web Server. To get around this failure, all you have to do is to create a symbolic link in the home directory of the virtual web server instance pointing to the startserv script residing in the bin directory.

The following example shows the necessary steps.


% pwd
/export/pspp/SJWS7U5/https-siebel-pspp

% ln -s bin/startserv start

% ls -l start
lrwxrwxrwx   1 pspp     dba           13 May 17 17:01 start -> bin/startserv

Install Siebel Web Extension in the normal way. No other changes are required.

AFTER SWE INSTALLATION:


% ls -l start*
-rwxr-xr-x   1 pspp     dba         4157 May 17 17:38 start
-rwxr-xr-x   1 pspp     dba         3456 May 17 17:38 start_.bak

% mv bin/startserv bin/startserv.orig
% mv start bin/startserv

Notice that the Siebel installer actually made two copies of the startup script from the symbolic link. The original bin/startserv remained intact after the SWE installation.

Finally start the Web Server instance by running the startserv script. It should start with no issues.


% pwd
/export/pspp/SJWS7U5/https-siebel-pspp/bin

% ./startserv
Sun Java System Web Server 7.0U5 B03/10/2009 16:38
info: swe_init reports: SWE plug-in log file
info: CORE5076: Using [Java HotSpot(TM) Server VM, Version 1.5.0_15] from [Sun Microsystems Inc.]
info: HTTP3072: http-listener-1: http://siebel-pspp:8000 ready to accept requests
info: CORE3274: successful server startup

Before we conclude, do not forget the fact that Sun Java System Web Server 7.0 is not yet certified with Siebel 8.0 release. Use the instructions mentioned in this blog post at your own risk. However if you do like to take that risk, consider installing the latest release of Sun Java System Web Server, which is SJSWS 7.0 Update 5 as of this writing.

Stay tuned for the certification news though.

(Originally posted on:
http://blogs.sun.com/mandalika/entry/installing_siebel_web_extension_swe)
_______________
Technorati Tags:
Oracle | Siebel | CRM | Solaris | Sun Java System Web Server | iPlanet

Oracle E-Business Suite R12 Database Tier on Solaris 10 x64

2009-05-01T00:32:00.000-07:00

The long wait for this certification is over. Oracle Corporation finally announced support for Solaris 10 x64 as the certified E-Business Suite platform for the database tier. Oracle E-Business Suite Release 12 (12.0.4) is now certified with Oracle Database Server 10g R2 version 10.2.0.4 on the 'database tier only' (previously known as 'split configuration' in 11i) platform Sun Solaris x86/64.

The following Metalink documents might be of interest to Sun-Oracle customers who are waiting for this certification to happen.

_______________
Technorati Tags:
Oracle | E-Business Suite | Oracle Applications | Solaris

Developing MySQL Database Applications With PHP

2009-04-24T00:50:00.000-07:00

A four part tutorial that explains the PHP / MySQL extensions - mysql, mysqli, and pdo_mysql - with simple examples is now accessible from Sun Developer Network. While most of the information presented in the tutorial is available elsewhere in bits and pieces, the real value-add is in the introduction of the MySQL native driver for PHP, mysqlnd.

Here is a brief description of all four parts in the series:

Part 1: Using the MySQL Improved Extension, mysqli -- describes mysqli, the MySQL Improved Extension that is the successor to the mysql extension
Part 2: Using the MySQL Extension, mysql -- describes the original extension designed to develop PHP applications that interact with a MySQL database
Part 3: Using the PDO Extension With MySQL Driver, pdo_mysql -- describes the pdo_mysql driver, which implements the PHP Data Objects (PDO) interface to access the MySQL Server from PHP
Part 4: Using the MySQL Native Driver for PHP, mysqlnd -- describes the MySQL native driver for PHP, mysqlnd, which is an alternative to the MySQL client library, libmysql, for connecting from PHP 5 and PHP 6 to MySQL Server 4.1 or later

Exploring the Features of MySQL Connector/C++

2009-04-15T21:02:00.000-07:00

With the introduction of MySQL Connector/C++, now C++ application developers have one additional option to choose from, to connect to MySQL Server 5.1 or later from a C++ application. Admittedly, as of today, there isn't enough documentation with examples to show the capabilities [and gotchas] of MySQL Connector/C++. I tried to fill that gap with the technical article, Developing Database Applications Using MySQL Connector/C++. Hopefully it serves as a starting point for the C++ developers while waiting for the MySQL Connectors documentation team to publish the official documentation on MySQL Developer Zone.

While you are at it, don't forget to check the supplement document, Installing MySQL Connector/C++ from Source, in case if you want to build the driver on your own from the source code.

________________
Technorati Tags:
MySQL | Connector/C++

Controlling [Virtual] Network Interfaces in a Non-Global Solaris Zone

2009-04-07T21:23:00.000-07:00

In the software world, some tools like SAP NetWeaver's Adaptive Computing Controller (ACC) require full control over a network interface, so they can bring up/down the NICs at their will to fulfill their responsibilities. Those tools may function normally on Solaris 10 [and later] as long as they are run in the global zone. However there might be some trouble when those tools are attempted to run in a non-global zone, especially on machines with only one physical network interface installed, and when the non-global zones are created with the default configuration. This blog post attempts to suggest few solutions to get around those issues, so the tools can function the way they normally do in the global zone.

If the machine has only one NIC installed, there are at least two issues that will prevent tools like ACC from working in a non-global zone.

Since there is only one network interface on the system, it is not possible to dedicate that interface to the non-global zone where ACC is supposed to run. Hence all the zones, including the global zone, must share the physical network interface.

When the physical network interface is being shared across multiple zones, it is not possible to plumb/unplumb the network interface from a Shared-IP Non-Global Zone. Only the root users in the global zone can plumb/unplumb the lone physical network interface.

When a non-global zone is created with the default configuration, Shared-IP zone is created by default. Shared-IP zones have separate IP addresses, but share the IP routing configuration with the global zone.

Fortunately, Solaris 10 has a solution to the aforementioned issues in the form of Network Virtualization. Crossbow is the code name for network virtualization in Solaris. Crossbow provides the necessary building blocks to virtualize a single physical network interface into multiple virtual network interfaces (VNICs) - so the solution to the issue at hand is to create a virtual network interface, and then to create an Exclusive-IP Non-Global Zone using the virtual NIC. Rest of the blog post demonstrates the simple steps to create a VNIC, and to configure a non-global zone as Exclusive-IP Zone.

Create a Virtual Network Interface using Crossbow

Make sure the OS has Crossbow functionality
```
global# cat /etc/release
                 Solaris Express Community Edition snv_111 SPARC
           Copyright 2009 Sun Microsystems, Inc.  All Rights Reserved.
                        Use is subject to license terms.
                             Assembled 23 March 2009
```
Crossbow has been integrated into Solaris Express Community Edition (Nevada) build 105 - hence all Nevada builds starting with build 105 will have the Crossbow functionality. OpenSolaris 2009.06 and the next major update to Solaris 10 are expected to have the support for network virtualization out-of-the-box.

Check the existing zones and the available physical and virtual network interfaces.



global# zoneadm list -cv
  ID NAME             STATUS     PATH                           BRAND    IP    
   0 global           running    /                              native   shared

global# dladm show-link
LINK        CLASS    MTU    STATE    OVER
e1000g0     phys     1500   up       --

In this example, there is only one NIC, e1000g0, on the server; and there are no non-global zones installed.

Create a virtual network interface based on device e1000g0 with an automatically generated MAC address. If the NIC has factory MAC addresses available, one of them will be used. Otherwise, a random address is selected. The auto mode is the default action if none is specified.
```
global# dladm create-vnic -l e1000g0 vnic1
```

Check the available network interfaces one more time. Now you should be able to see the newly created virtual NIC in addition to the existing physical network interface. It is also possible to list only the virtual NICs.



global# dladm show-link
LINK        CLASS    MTU    STATE    OVER
e1000g0     phys     1500   up       --
vnic1       vnic     1500   up       e1000g0

global# dladm show-vnic
LINK         OVER         SPEED  MACADDRESS           MACADDRTYPE         VID
vnic1        e1000g0      1000   2:8:20:32:9:10       random              0

Create a Non-Global Zone with the VNIC

Create an Exclusive-IP Non-Global Zone with the newly created VNIC being the primary network interface.



global # mkdir -p /export/zones/sapacc
global # chmod 700 /export/zones/sapacc

global # zonecfg -z sapacc
sapacc: No such zone configured
Use 'create' to begin configuring a new zone.
zonecfg:sapacc> create
zonecfg:sapacc> set zonepath=/export/zones/sapacc
zonecfg:sapacc> set autoboot=false
zonecfg:sapacc> set ip-type=exclusive
zonecfg:sapacc> add net
zonecfg:sapacc:net> set physical=vnic1
zonecfg:sapacc:net> end
zonecfg:sapacc> verify
zonecfg:sapacc> commit
zonecfg:sapacc> exit

global # zoneadm -z sapacc install

global # zoneadm -z sapacc boot

global #  zoneadm list -cv
  ID NAME             STATUS     PATH                           BRAND    IP    
   0 global           running    /                              native   shared
   1 sapacc           running    /export/zones/sapacc         native   excl

Configure the new non-global zone including the IP address and the network services



global # zlogin -C -e [ sapacc
...

  > Confirm the following information.  If it is correct, press F2;             
    to change any information, press F4.                                        
                                                                                
                                                                                
                  Host name: sap-zone2
                 IP address: 10.6.227.134                                       
    System part of a subnet: Yes                                                
                    Netmask: 255.255.255.0                                      
                Enable IPv6: No                                                 
              Default Route: Detect one upon reboot

Inside the non-global zone, check the status of the VNIC and the status of the network service



local# hostname
sap-zone2

local# zonename
sapacc

local# ifconfig -a
lo0: flags=2001000849 mtu 8232 index 1
        inet 127.0.0.1 netmask ff000000 
vnic1: flags=1000843 mtu 1500 index 2
        inet 10.6.227.134 netmask ffffff00 broadcast 10.6.227.255
        ether 2:8:20:32:9:10 
lo0: flags=2002000849 mtu 8252 index 1
        inet6 ::1/128 

local# svcs svc:/network/physical
STATE          STIME    FMRI
disabled       13:02:18 svc:/network/physical:nwam
online         13:02:24 svc:/network/physical:default

Check the network connectivity.

From inside the non-global zone to the outside world:



local# ping -s sap29
PING sap29: 56 data bytes
64 bytes from sap29 (10.6.227.177): icmp_seq=0. time=0.680 ms
64 bytes from sap29 (10.6.227.177): icmp_seq=1. time=0.452 ms
64 bytes from sap29 (10.6.227.177): icmp_seq=2. time=0.561 ms
64 bytes from sap29 (10.6.227.177): icmp_seq=3. time=0.616 ms
^C
----sap29 PING Statistics----
4 packets transmitted, 4 packets received, 0% packet loss
round-trip (ms)  min/avg/max/stddev = 0.452/0.577/0.680/0.097

From the outside world to the non-global zone:



remotehostonWAN# telnet sap-zone2
Trying 10.6.227.134...
Connected to sap-zone2.sun.com.
Escape character is '^]'.
login: test
Password: 
Sun Microsystems Inc.   SunOS 5.11      snv_111 November 2008

-bash-3.2$ /usr/sbin/ifconfig -a
lo0: flags=2001000849 mtu 8232 index 1
        inet 127.0.0.1 netmask ff000000 
vnic1: flags=1000843 mtu 1500 index 2
        inet 10.6.227.134 netmask ffffff00 broadcast 10.6.227.255
lo0: flags=2002000849 mtu 8252 index 1
        inet6 ::1/128 
-bash-3.2$ exit
logout
Connection to sap-zone2 closed.

Dynamic [Re]Configuration of the [Virtual] Network Interface in a Non-Global Zone

Finally try plumbing down/up the virtual network interface inside the Exclusive-IP Non-Global Zone



global # zlogin -C -e [ sapacc
[Connected to zone 'sapacc' console]
..

zoneconsole# ifconfig vnic1 unplumb

zoneconsole# /usr/sbin/ifconfig -a
lo0: flags=2001000849 mtu 8232 index 1
        inet 127.0.0.1 netmask ff000000

zoneconsole# ifconfig vnic1 plumb

zoneconsole# ifconfig vnic1 10.6.227.134 netmask 255.255.255.0 up

zoneconsole# /usr/sbin/ifconfig -a
lo0: flags=2001000849 mtu 8232 index 1
        inet 127.0.0.1 netmask ff000000
vnic1: flags=1000843 mtu 1500 index 2
        inet 10.6.227.134 netmask ffffff00 broadcast 10.6.227.255
lo0: flags=2002000849 mtu 8252 index 1
        inet6 ::1/128

As simple as that! Before we conclude, be informed that prior to Crossbow, Solaris system administrators were required to use Virtual Local Area Networks (VLAN) to achieve similar outcomes.

Check Zones and Containers FAQ, if you are stuck with a strange situation or if you need some interesting ideas around virtualization on Solaris.
________________
Technorati Tags:
Solaris | Virtualization | Crossbow | SAP | NetWeaver

Adobe [Acrobat] Reader 9.1.x on [Open]Solaris

2009-03-29T13:41:00.000-07:00

Finally Adobe Reader is available for OpenSolaris. It took 10 years (nearly 4 years, to be fair) for Adobe to upgrade the Adobe [acrobat] reader from version 4.0.x to 9.1.x for Solaris x86. In any case, better late than never. You can download the latest copy of the Adobe reader (previously known as Acrobat reader) from Adobe's ftp site: ftp.adobe.com
_______________
Technorati Tags:
OpenSolaris | Solaris | Adobe | acroread | acrobat | Adobe Reader

Accessing MySQL Database(s) with JDBC

2009-02-26T22:55:00.000-08:00

A new technical article entitled "Using MySQL With Java Technology", has been posted on java.sun.com at:

http://java.sun.com/developer/technicalArticles/mysql_java/index.html

This article explains the essential steps involved in manipulating the data in a MySQL database from a Java application. MySQL Connector/J JDBC driver was used in the example code to show the database connectivity, data manipulation steps. Application developers who are new to Java programming language [but not to MySQL database] are the target audience of this article.

Stay tuned for the next article in this series "Using MySQL with PHP" ..

_______________
Technorati Tags:
Java | JDBC | MySQL | Connector/J

Sun Blueprint : MySQL in Solaris Containers

2009-02-24T00:12:00.000-08:00

While the costs of managing a data center are becoming a major concern with the increased number of under-utilized servers, customers are actively looking for solutions to consolidate their workloads to:

improve server utilization
improve data center space utilization
reduce power and cooling requirements
lower capital and operating expenditures
reduce carbon footprint, ..

To cater those customers, Sun offers several virtualization technologies such as Logical Domains, Solaris Containers, xVM at free of cost for SPARC and x86/x64 platforms.

In order to help our customers who are planning for the consolidation of their MySQL databases on systems running Solaris 10, we put together a document with a bunch of installation steps and the best practices to run MySQL inside a Solaris Container. Although the document was focused on the Solaris Containers technology, majority of the tuning tips including the ZFS tips are applicable to all MySQL instances running [on Solaris] under different virtualization technologies.

You can access the blueprint document at the following location:

Running MySQL Database in Solaris Containers

The blueprint document briefly explains the MySQL server & Solaris Containers technology, introduces different options to install MySQL server on Solaris 10, shows the steps involved in installing and running Solaris Zones & MySQL, and finally provides few best practices to run MySQL optimally inside a Solaris Container.

Feel free to leave a comment if you notice any incorrect information, or if you have generic suggestions to improve documents like these.

Acknowledgments

Many thanks to Prashant Srinivasan, John David Duncan and Margaret B. for their help in different phases of this blueprint.

_______________
Technorati Tags:
Sun | Solaris | OpenSolaris | MySql | Virtualization | Containers | Zones | Best Practices | Consolidation

PeopleSoft HRMS 8.9 Self-Service Benchmark on M3000 & T5120 Servers

2009-02-22T01:31:00.001-08:00

Sun published the PeopleSoft HRMS 8.9 Self-Service benchmark results today. The benchmark was conducted on 3 x Sun SPARC Enterprise M3000 and 1 x Sun SPARC Enterprise T5120 servers. Click on the following link for the full report with the benchmark results.

PeopleSoft HRMS 8.9 SELF-SERVICE Using ORACLE on Sun SPARC Enterprise M3000 and Enterprise T5120 Servers

Admittedly it is Sun's first PeopleSoft benchmark after a hiatus of over five years. However I am glad that we came up with a very nice cost effective solution in our comeback effort to the PeopleSoft applications' benchmarking.

Some of the notes and highlights from this competitive benchmark are as follows.

The benchmark measured the average search and save transaction response times at a peak load of 4,000 concurrent users.
4,000 users is the limitation of the benchmark kit. All vendors using this benchmark kit are bound to this limitation. Hence it is easy to compare the performance as the throughput achieved by each vendor will be the same. In comparing the benchmark results from workloads like these, lower average [transaction response times, CPU, memory utilizations] and the hardware in use (lesser the better), usually indicate better performance.
IBM and Sun are the only vendors who published benchmark results with PeopleSoft HRMS 8.9 Self-Service benchmark kit.

Sun's benchmark results are superior relative to IBM's best published result on a combination of z990 2084-C24 and eServer pSeries p690 servers. While I leave the price comparisons to the reader¹, I'd like to show the performance numbers extracted from the benchmark reports published by Sun and IBM. All the following data/information is available in the benchmark reports. Feel free to draw your own conclusions.

Average Transaction Response Times

Vendor	Single User Search (sec)	4,000 Users Search (sec)	Single User Save (sec)	4,000 Users Save (sec)
Sun	0.78	0.77	0.71	0.74
IBM	0.78	1.35	0.65	1.01

Average CPU Utilizations

Vendor	Web Server CPU%	App Server1 CPU%	App Server2 CPU%	DB Server CPU%
Sun	23.10	66.92	67.85	27.45
IBM	45.81	59.70	N/A	40.66

Average Memory Utilizations

Vendor	Web Server GB	App Server1 GB	App Server2 GB	DB Server GB
Sun	4.15	3.67	3.72	5.54
IBM	5.00	15.70	N/A	0.3 (Huh!?)

Hardware Configuration

Vendor: Sun Microsystems

Topology Diagram

Tier	Server Model	Server Count	Processor	Processor Speed	Processor Count	#Cores per Processor	Memory
Web	T5120	1	UltraSPARC-T2	1.2 GHz	1	4	8 GB
App	M3000	2	SPARC64-VII	2.52 GHz	1	4	8 GB
DB	M3000	1	SPARC64-VII	2.52 GHz	1	4	8 GB

2 x Sun Storage J4200 arrays were used to host the database. Total disk space: ~1.34 Terabytes. Consumed only 120 GB disk space -- 115 GB for data on one array; and 5 GB for redo logs on the other array.

Vendor: IBM

Tier	Server Model	Server Count	Processor	Processor Speed	Processor Count	#Cores per Processor	Memory
Web	p690 (7040-681)	1	POWER4	1.9 GHz	4	NA (?)	12 GB
App	p690 (7040-681)	1	POWER4	1.9 GHz	12	NA (?)	32 GB
DB	zSeries 990, model 2084-C24	1	z990 Gen1	???	6	NA (?)	32 GB

1 x IBM TotalStorage DS8300 Enterprise Storage Server, 2107-922 ws used to host the database. Total disk space: ~9 Terabytes.

The combination of Sun SPARC Enterprise M3000 and T5120 servers consumed 1030 Watts on the average in a 7RU space in achieving 4,000 concurrent users. That is, in the case of similarly configured workloads, M3000/T5120 support 3.88 users per watt of the power consumed; and 571 users per rack unit.

Just like our prior Siebel and Oracle E-Business Suite Payroll 11i benchmarks, Sun collaborated with Oracle Corporation in executing this benchmark. And we sincerely thank our peers at Oracle Corporation for all their help and support over the past few months in executing this benchmark.
___________

I'm planning to post some of the tuning tips to run PeopleSoft optimally on Solaris 10. Stay tuned ..

(Originally posted on blogs.sun.com at:
http://blogs.sun.com/mandalika/entry/peoplesoft_hrms_8_9_self)

________________
Technorati Tags:
Solaris | sun | oracle | peoplesoft | hrms | benchmark | m3000 | t5120

¹: It is relatively hard to obtain IBM's server list prices. On the other hand, it is very easy to find the list prices of Sun servers' from http://store.sun.com

Mounting Windows' NTFS on [Open]Solaris x86/x64

2009-02-11T01:47:00.000-08:00

The steps outlined in this blog post are derived from the Miscellaneous filesystem support for OpenSolaris on x86 web page. I just added few examples to illustrate the steps to mount a partition with NTFS filesystem that exists on the external hard drive (in this case, it is a Seagate FreeAgent external hard drive).

Step-by-Step instructions to mount NTFS filesystem on [Open]Solaris

Install the packages : FSWpart and FSWfsmisc.
- Download http://www.belenix.org/binfiles/FSWpart.tar.gz and http://www.belenix.org/binfiles/FSWfsmisc.tar.gz
- Uncompress and install the packages with 'root' privileges.
```
% gunzip -c FSWpart.tar.gz | tar xvf -
# pkgadd -d . FSWpart

% gunzip -c FSWfsmisc.tar.gz | tar xvf -
# pkgadd -d . FSWfsmisc
```

Find the logical device name for the NTFS partition. -l option of the rmformat command lists all removable devices along with their device names.



# rmformat -l   
Looking for devices...
     1. Logical Node: /dev/rdsk/c1t0d0p0
        Physical Node: /pci@0,0/pci-ide@1f,1/ide@1/sd@0,0
        Connected Device: MATSHITA UJDA750 DVD/CDRW 1.60
        Device Type: DVD Reader
 Bus: IDE
 Size: 
 Label: 
 Access permissions: 
     2. Logical Node: /dev/rdsk/c2t0d0p0
        Physical Node: /pci@0,0/pci1179,1@1d,7/storage@1/disk@0,0
        Connected Device: Seagate  FreeAgentDesktop 100F
        Device Type: Removable
 Bus: USB
 Size: 953.9 GB
 Label: 
 Access permissions:

Identify the NTFS partition on the external disk with the help of fdisk



# fdisk /dev/rdsk/c2t0d0p0
             Total disk size is 60800 cylinders
             Cylinder size is 32130 (512 byte) blocks

                                               Cylinders
      Partition   Status    Type          Start   End   Length    %
      =========   ======    ============  =====   ===   ======   ===
          1                 IFS: NTFS         0  60800    60801    100

SELECT ONE OF THE FOLLOWING:
   1. Create a partition
   2. Specify the active partition
   3. Delete a partition
   4. Change between Solaris and Solaris2 Partition IDs
   5. Exit (update disk configuration and exit)
   6. Cancel (exit without updating disk configuration)
Enter Selection: 6
#

In this example, partition #1 i.e., c2t0d0p1 has the NTFS filesystem.

Mount the NTFS partition just like mounting an UFS filesystem using the mount command. Use the argument ntfs to the command line option -F. Since the filesystem was mounted in a slightly different way than the conventional way, use /usr/bin/xlsmounts to see the detailed mount table information.
```
# mount -F ntfs /dev/dsk/c2t0d0p1 /mnt

# /usr/bin/xlsmounts
  PHYSICAL DEVICE                 LOGICAL DEVICE      FS    PID         ADDR Mounted on
/dev/dsk/c2t0d0p1              /dev/dsk/c2t0d0p1    ntfs   6755  127.0.0.1:/ /mnt

# ls /mnt
expForSun.dmp             MySQL5.1                   RECYCLER
medium-64-bit             $RECYCLE.BIN               System Volume Information
```
Notice the 127.0.0.1:/ under ADDR column in the output of xlsmounts. NTFS mount uses userland NFSv2 server to access the filesystems on raw partitions. That is why the mount was shown as NFS client mounted from 127.0.0.1:/
To unmount the NTFS filesystem, use /usr/bin/xumount. Solaris standard umount command unmounts the filesystem but does not terminate the background NFS server process.
```
# /usr/bin/xumount /mnt

         - OR -

# /usr/bin/xumount /dev/dsk/c2t0d0p1
```

Check the Miscellaneous filesystem support for OpenSolaris on x86 page and Moinak Ghosh's blog post Mount and Access NTFS and Ext2FS from Solaris x86 for the rest of the fine details.

(Originally posted on blogs.sun.com at:
http://blogs.sun.com/mandalika/entry/mounting_windows_ntfs_on_open)
_______________
Technorati Tags:
Solaris | OpenSolaris | NTFS

Workaround to "eject of cdrom /dev/dsk/cxtxdxsx failed" Error on SXCE b106

2009-02-08T00:12:00.000-08:00

Symptom

On Solaris Express Community Edition build 106, eject(1) fails intermittently (esp. when inserted the blank media) with the error: eject of cdrom /dev/dsk/cxtxdxsx failed.


# cat /etc/release
                  Solaris Express Community Edition snv_106 X86
           Copyright 2009 Sun Microsystems, Inc.  All Rights Reserved.
                        Use is subject to license terms.
                            Assembled 13 January 2009

# eject
eject of cdrom /dev/dsk/c1t0d0s2 failed: A security policy in place prevents this sender 
from sending this message to this recipient, see message bus configuration file (rejected 
message had interface "org.freedesktop.Hal.Device.Storage" member "Eject" error name 
"(unset)" destination "org.freedesktop.Hal")

Solution

Upgrade to snv_107. Meanwhile check the bug #6791982.

Workaround

Edit /etc/dbus-1/system.d/hal.conf.

Add the following line to the <policy context="default"> section.

<allow send_interface="org.freedesktop.Hal.Device.Storage"/>
Restart the D-Bus message bus system service.

svcadm restart svc:/system/dbus:default

On a totally unrelated note, to see all the existing SMF services with a simple description, run:

svcs -o FMRI,DESC
Finally try to eject the CD/DVD disk by typing the eject command.

(Originally posted on blogs.sun.com at:
http://blogs.sun.com/mandalika/entry/sxce_snv_106_workaround_to)
_______________
Technorati Tags:
Solaris | OpenSolaris | troubleshooting

PHP: Memory savings with mysqlnd

2009-01-31T19:41:00.000-08:00

mysqlnd may save memory. In the best cases, it may consume only 50% memory as that of libmysql esp. when the client application does not modify the data in the result set after executing a query. Keep in mind that the client must use ext/mysqli and treat the data returned by the query as read-only in order to fully realize mysqlnd's memory gains. If the client application modifies any of the data, mysqlnd behaves just like libmysql.

Let's have a quick look at the memory consumption in both the cases (mysqlnd and libmysql) with an example before delving into the internals. For easy comparison, the sample PHP script does not modify any of the arrays returned from the fetch method. The following example uses DTrace on Sun Solaris to monitor the calls to malloc() and prints the requested bytes of memory on the standard output.

Source code for the script: PHPmysqliClient.php. MySQL table structure and the sample data are shown in the other blog post: Demonstrating the Features of MySQL Native Driver for PHP, mysqlnd.


bash# cat monitormalloc.d

#!/usr/sbin/dtrace -s

pid$1:libc:malloc:entry
{
  printf("\t\tSize : %d Bytes", arg0);
 ustack();
 @malloc[probefunc] = quantize(arg0);
}

CASE 1: ext/mysqli with libmysql

In one terminal window:

bash# /opt/coolstack/php5/bin/php PHPmysqliClient.php
In another terminal window, run monitormalloc.d `pgrep php` and press any key to continue ..

Retrieved 3 row(s).

CityName
--------
Hyderabad, India
San Francisco, USA
Sydney, Australia

In another terminal window:

bash# ./monitormalloc.d `pgrep php`
dtrace: script './monitormalloc.d' matched 1 probe
CPU ID   FUNCTION:NAME
  0  80920   malloc:entry  Size : 964 Bytes
              libc.so.1`malloc
              libmysqlclient.so.16.0.0`0xce2173c1
              libmysqlclient.so.16.0.0`0xce23bb47
              mysqli.so`0xce11d292

  0  80920   malloc:entry  Size : 20 Bytes
              libc.so.1`malloc
              libmysqlclient.so.16.0.0`0xce2173c1
              libmysqlclient.so.16.0.0`0xce23da60
              mysqli.so`0xce11dc72

  0  80920   malloc:entry  Size : 20 Bytes
              libc.so.1`malloc
              libmysqlclient.so.16.0.0`0xce2173c1
              libmysqlclient.so.16.0.0`0xce21d991
              libmysqlclient.so.16.0.0`0xce21d9ce
              libmysqlclient.so.16.0.0`0xce23da72
              mysqli.so`0xce11dc72

  0  80920   malloc:entry  Size : 17 Bytes
  ... elided stack traces for brevity ...
  0  80920   malloc:entry  Size : 152 Bytes
  0  80920   malloc:entry  Size : 16384 Bytes
  0  80920   malloc:entry  Size : 8199 Bytes
  0  80920   malloc:entry  Size : 7 Bytes
  0  80920   malloc:entry  Size : 4088 Bytes
  0  80920   malloc:entry  Size : 4088 Bytes
  0  80920   malloc:entry  Size : 4088 Bytes
  0  80920   malloc:entry  Size : 4088 Bytes
  0  80920   malloc:entry  Size : 4088 Bytes
  0  80920   malloc:entry  Size : 4088 Bytes
  0  80920   malloc:entry  Size : 4088 Bytes
  0  80920   malloc:entry  Size : 4088 Bytes
  0  80920   malloc:entry  Size : 4088 Bytes
  0  80920   malloc:entry  Size : 18261 Bytes
  0  80920   malloc:entry  Size : 58 Bytes
  0  80920   malloc:entry  Size : 4088 Bytes
  0  80920   malloc:entry  Size : 4088 Bytes
  0  80920   malloc:entry  Size : 120 Bytes
  0  80920   malloc:entry  Size : 5 Bytes
  0  80920   malloc:entry  Size : 6 Bytes
  0  80920   malloc:entry  Size : 5 Bytes
  0  80920   malloc:entry  Size : 56 Bytes
  0  80920   malloc:entry  Size : 8164 Bytes
  0  80920   malloc:entry  Size : 8164 Bytes
  0  80920   malloc:entry  Size : 92 Bytes
  0  80920   malloc:entry  Size : 56 Bytes
  0  80920   malloc:entry  Size : 8164 Bytes
              libc.so.1`malloc
              libmysqlclient.so.16.0.0`0xce2173c1
              libmysqlclient.so.16.0.0`0xce21a27b
              libmysqlclient.so.16.0.0`0xce23b8a4
              libmysqlclient.so.16.0.0`0xce23d4fa
              mysqli.so`0xce11fe56

  0  80920   malloc:entry  Size : 262144 Bytes
              libc.so.1`malloc
              php`0x856fb98

^C

  malloc  
           value  ------------- Distribution ------------- count
               2 |                                         0
               4 |@@@@@                                    4
               8 |                                         0
              16 |@@@@                                     3
              32 |@@@@                                     3
              64 |@@@                                      2
             128 |@                                        1
             256 |                                         0
             512 |@                                        1
            1024 |                                         0
            2048 |@@@@@@@@@@@@@@      11
            4096 |@@@@                                     3
            8192 |@                                        1
           16384 |@@@                                      2
           32768 |                                         0
           65536 |                                         0
          131072 |                                         0
          262144 |@                                        1
          524288 |                                         0

CASE 2: ext/mysqli with mysqlnd

In one terminal window:

bash# /export/home/php53/bin/php PHPmysqliClient.php
In another terminal window, run monitormalloc.d `pgrep php` and press any key to continue ..

Retrieved 3 row(s).

CityName
--------
Hyderabad, India
San Francisco, USA
Sydney, Australia

In another terminal window:

bash# ./monitormalloc.d `pgrep php`
dtrace: script './monitormalloc.d' matched 1 probe
CPU ID   FUNCTION:NAME
  0  80920   malloc:entry  Size : 262144 Bytes
              libc.so.1`malloc
              php`0x82f702b
              php`0x82f80ab
              php`0x82f841f
              php`0x82f98c4
              php`0x82c7668
              php`0x83c30ae
              php`0x80c059c

^C

  malloc  
           value  ------------- Distribution ------------- count
          131072 |                                         0
          262144 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 1
          524288 |                                         0

In the case of ext/mysqli with libmysql, there are more than 25 calls to malloc() accounting to a total size around 367KB, where as in the case of ext/mysqli with mysqlnd, there is only one call to malloc() with a size of 256KB. In other words, mysqlnd is consuming 30% less memory relative to libmysql to do similar database operations (in reality, it is incorrect to treat every byte allocated as the memory consumed unless there exists a corresponding memory mapping -- however for the sake of this discussion, let's just assume that all the allocated bytes are eventually consumed).

The memory savings in the above example are the result of mysqlnd's ability to hold the results only once in the memory. On the other hand, as libmysql is not a part of PHP, some of the results fetched by libmysql will be copied into memory twice. When libmysql fetches the data from the MySQL Server, it puts the data into its own buffers. Then the data gets copied from the libmysql buffers into respective ext/mysqli data structures (often referred as zvals) before it is made available to the PHP clients to consume. So with ext/mysqli and libmysql, there might be two copies of the data in the main memory - one copy inside the libmysql buffers and the other inside zvals. With mysqlnd, there might be only one copy of the data in the memory. mysqlnd also uses buffers but links the zval structures directly to the read buffers, wherever possible. Therefore in majority of the instances, mysqlnd consumes less memory relative to libmysql. In the worst case, it may consume as much memory as that of libmysql. The total memory savings depend on the size of the buffered result set.

Shown below is the simplified behind-the-scenes actions of ext/mysqli with libmysql and ext/mysqli with mysqlnd when mysqli sends a query:

ext/mysqli with libmysql

mysqli sends a query
result set gets fetched into libmysql buffers
mysqli allocates zvals, then new buffers
mysqli copies data from libmysql to its own buffers
mysqli calls mysql_free_result() and deallocates libmysql buffers

ext/mysqli with mysqlnd

mysqli sends a query
result set gets fetched row by row -- every row is a different buffer
mysqlnd creates a result set of zvals pointing to the buffers
mysqli calls mysqlnd_free_result() and deallocates the row buffers

In short, ext/mysqli with libmysql does:

one extra allocation for mysqli buffers
one extra data copy
one extra zval allocation (which can be saved with the zval cache)

when compared to ext/mysqli with mysqlnd.

Related Blog Posts:

Acknowledgments
Andrey Hristov & Ulf Wendel, Sun-MySQL AB

(Originally posted on blogs.sun.com at:
http://blogs.sun.com/mandalika/entry/php_memory_savings_with_mysqlnd)
_______________
Technorati Tags:
PHP | MySQL | mysqlnd

Demonstrating the Features of MySQL Native Driver for PHP, mysqlnd

2009-01-26T01:06:00.000-08:00

Support for Persistent Connections

ext/mysqli does not support persistent connections when built with libmysql. However ext/mysqli does support persistent connections when built with mysqlnd. To establish a persistent connection with the MySQL server using ext/mysqli and mysqlnd, prepend the database host with the string "p:" (p stands for persistent) as shown below.


$host="p:localhost";
$port=3306;
$socket="/tmp/mysql.sock";
$user="root";
$password="admin";
$dbname="test";

$cxn = new mysqli($host, $user, $password, $dbname, $port, $socket)
 or die ('Could not connect to the database server' . mysqli_connect_error());

ext/mysql, ext/mysqli and PDO_MySQL support persistent connections when built with mysqlnd.

The new API call mysqli_fetch_all()

mysqlnd extends the ext/mysqli API with one brand new method, mysqli_fetch_all().

mysqli_fetch_all() fetches all result rows and return the result set as an associative array, a numeric array, or both. The method signature is shown below for both procedural as well as object oriented style of programming.

Procedural style:

 mixed mysqli_fetch_all (mysqli_result $result [, int $resulttype])

Object oriented style:

 mixed mysqli_result::fetch_all ([int $resulttype])

where: $result is a result set identifier returned by mysqli_query(), mysqli_store_result() or mysqli_use_result(), and $resulttype is an optional constant indicating what type of array should be produced from the current row data. The possible values for this parameter are the constants MYSQLI_ASSOC, MYSQLI_NUM, or MYSQLI_BOTH. Defaults to MYSQLI_NUM.

Because mysqli_fetch_all() returns all the rows as an array in a single step, it may consume more memory than some of its counterparts like mysqli_fetch_array(). mysqli_fetch_array() returns one row at a time from the result set, hence consumes less memory relative to mysqli_fetch_array(). Besides, if you need to iterate over the result set, you may need a foreach() loop and this approach might be little slower compared to the result set retrieval using mysqli_fetch_array(). Hence consider using mysqli_fetch_all() only in those situations where the fetched result set will be sent to another layer for post processing. If you have to process the fetched result set in the same layer with the help of iterators, then the benefit of using the mysqli_fetch_all() method might be minimal, if there is any.

Statistical Data Collection

mysqlnd collects a lot of statistics which you can use to tune your application. mysqlnd enhances ext/mysqli API with three mysqli_get_XX_stats() methods for easy monitoring and to simplify the bottleneck analysis. For example, using a combination of mysqli_get_XX_stats() methods, one can easily identify a PHP client script that is opening more database connections than it needs or selecting more rows than it consumes.

Accessing Client Statistics:

To access per process client statistics, simply call mysqli_get_client_stats() with no arguments. Similarly to access client statistics per connection, call mysqli_get_connection_stats() with the database connection handle as the argument. Both of these methods return an associated array with the name of the statistic parameter as the key and the corresponding data as the value.

Alternatively per process client statistics can be accessed by calling the phpinfo() method.

The above methods return statistics like bytes_sent, bytes_received to represent the number of bytes sent to and received from the MySQL server, result_set_queries to show the number of queries which generated a result set, buffered_sets, unbuffered_sets to show the number of buffered and unbuffered result sets for the queries generating a result set but not run as a prepared statement. rows_fetched_from_server_normal shows the number of rows that have been fetched from the server using buffered and unbuffered result sets. rows_buffered_from_client_normal shows the number of rows fetched from the server and buffered on the client-side, and rows_skipped_normal shows the number of rows generated by the server but not read from the client.

Accessing Zval Cache Statistics:

mysqlnd collects statistics from its internal zval cache, that you can access by calling mysqli_get_cache_stat() method. This method returns an associative array with the name of the statistic as the key and the corresponding data as the value. The zval cache statistics might be useful to tweak zval cahe related php.ini settings for better performance.

Sample PHP Script Demonstrating mysqlnd's Features

The following sample PHP script demonstrates how to:

establish persistent connections
use mysqli_fetch_all() to fetch and display the result set
access client, connection and zval cache statistics using mysqli_get_client_stats(), mysqli_get_connection_stats() and mysqli_get_cache_stat() methods

The code sample in this tutorial try to retrieve the data from the City table in the MySQL test database. The table structure and the data from the City table are shown below by using the mysql client. MySQL server is running on the default port 3306.


bash# mysql -u root -p
Enter password: admin
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.24-rc-standard Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE test;
Database changed

mysql> DESCRIBE City;
+----------+-------------+------+-----+---------+-------+
| Field    | Type  | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| CityName | varchar(30) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
1 row in set (0.07 sec)

mysql> SELECT * FROM City;
+--------------------+
| CityName  |
+--------------------+
| Hyderabad, India   |
| San Francisco, USA |
| Sydney, Australia  |
+--------------------+
3 rows in set (0.17 sec)

The main purpose of the following example is only to illustrate the syntactical use of the new features of mysqlnd. The sample code does not represent any real world scenarios.


bash# cat PHPmysqliClientmysqlnd.php

<?php

 /* create a persistent connection to the MySQL server */
 $cxn = new mysqli("p:localhost", "root", "admin", "test", 3306, "/tmp/mysql.sock")
  or die ('Could not connect to the database server' . mysqli_connect_error());

 $query = "SELECT * FROM City";
 
 /* execute the query */
 if ($cxn->real_query ($query)) {

  /* initiate the result set retrieval */
  if ($result = $cxn->store_result()) {

   /* find the number of rows in the result set */
   $nrows = $result->num_rows;

   echo "\nRetrieved $nrows row(s).\n\n";
   echo "CityName\n--------\n";

   $all_rows = $result->fetch_all(MYSQLI_ASSOC);

   for($i = 0; $i < count($all_rows); $i++) {
    echo $all_rows[$i][CityName] . "\n";
   }
  }

  /* close the result set */
  $result->close();
 }

 echo "\n\nClient Statistics After One Query\n---------------------------------";
 $client_stats = mysqli_get_client_stats();
 #var_dump($client_stats);
 foreach ($client_stats as $key=>$value) {
  if ($value > 0) {
                        echo "\n$key : $value";
  }
 }

 echo "\n\nStatistics for Connection #1\n----------------------------";
 $conn_stats = mysqli_get_connection_stats($cxn);
 #var_dump($conn_stats);
 foreach ($conn_stats as $key=>$value) {
  if ($value > 0) {
                        echo "\n$key : $value";
  }
 }

 echo "\n\nCache Statistics After One Query\n--------------------------------";
 $cache_stats = mysqli_get_cache_stats();
 #var_dump($cache_stats);
 foreach ($cache_stats as $key=>$value) {
  if ($value > 0) {
                        echo "\n$key : $value";
  }
 }

 echo "\n\n=================================\n\n";
 echo "\nEstablishing connection #2 to the MySQL server ..\n\n";

 /* create a non-persistent connection to the MySQL server */
 $cxn2 = new mysqli("localhost", "root", "admin", "mysql", 3306, "/tmp/mysql.sock")
  or die ('Could not connect to the database server' . mysqli_connect_error());

 $query = "SELECT Host, User FROM user";

 /* execute the query */
 if ($cxn2->real_query ($query)) {

  /* initiate the result set retrieval */
  if ($result = $cxn2->store_result()) {

   /* find the number of rows in the result set */
   $nrows = $result->num_rows;
   echo "\nRetrieved $nrows row(s).\n\n";

   echo "Host\t\tUser\n----\t\t----\n";

   $all_rows = $result->fetch_all(MYSQLI_ASSOC);

   for($i = 0; $i < count($all_rows); $i++) {
    echo $all_rows[$i][Host] . "\t" . $all_rows[$i][User] . "\n";
   }
  }

                /* close the result set */
  $result->close();
 }

 echo "\n\nClient Statistics After Two Queries\n-----------------------------------";
 $client_stats = mysqli_get_client_stats();
 #var_dump($client_stats);
 foreach ($client_stats as $key=>$value) {
  if ($value > 0) {
   echo "\n$key : $value";
  }
 }

 echo "\n\nStatistics for Connection #2\n----------------------------";
 $conn_stats = mysqli_get_connection_stats($cxn2);
 #var_dump($conn_stats);
 foreach ($conn_stats as $key=>$value) {
  if ($value > 0) {
   echo "\n$key : $value";
  }
 }

 echo "\n\nCache Statistics After Two Queries\n----------------------------------";
 $cache_stats = mysqli_get_cache_stats();
 #var_dump($cache_stats);
 foreach ($cache_stats as $key=>$value) {
  if ($value > 0) {
   echo "\n$key : $value";
  }
 }

 echo "\n";

 //phpinfo();

 /* close the database connections */
 $cxn->close();
 $cxn2->close();

?>

bash# /export/home/php53/bin/php PHPmysqliClientmysqlnd.php

Retrieved 3 row(s).

CityName
--------
Hyderabad, India
San Francisco, USA
Sydney, Australia


Client Statistics After One Query
---------------------------------
bytes_sent : 90
bytes_received : 222
packets_sent : 2
packets_received : 9
protocol_overhead_in : 36
protocol_overhead_out : 8
bytes_received_ok_packet : 11
bytes_received_eof_packet : 9
bytes_received_rset_header_packet : 5
bytes_received_rset_field_meta_packet : 54
bytes_received_rset_row_packet : 70
packets_sent_command : 1
packets_received_ok : 1
packets_received_eof : 1
packets_received_rset_header : 1
packets_received_rset_field_meta : 1
packets_received_rset_row : 4
result_set_queries : 1
buffered_sets : 1
rows_fetched_from_server_normal : 3
rows_buffered_from_client_normal : 3
rows_fetched_from_client_normal_buffered : 3
rows_skipped_normal : 3
copy_on_write_performed : 3
connect_success : 1
active_connections : 1
active_persistent_connections : 1
explicit_free_result : 1
mem_erealloc_count : 1
mem_efree_count : 2
mem_realloc_count : 1
proto_text_fetched_string : 3

Statistics for Connection #1
----------------------------
bytes_sent : 90
bytes_received : 222
packets_sent : 2
packets_received : 9
protocol_overhead_in : 36
protocol_overhead_out : 8
bytes_received_ok_packet : 11
bytes_received_eof_packet : 9
bytes_received_rset_header_packet : 5
bytes_received_rset_field_meta_packet : 54
bytes_received_rset_row_packet : 70
packets_sent_command : 1
packets_received_ok : 1
packets_received_eof : 1
packets_received_rset_header : 1
packets_received_rset_field_meta : 1
packets_received_rset_row : 4
result_set_queries : 1
buffered_sets : 1
rows_fetched_from_server_normal : 3
rows_buffered_from_client_normal : 3
rows_skipped_normal : 3
connect_success : 1
active_connections : 1
active_persistent_connections : 1
explicit_free_result : 1
proto_text_fetched_string : 3

Cache Statistics After One Query
--------------------------------
put_misses : 3
get_hits : 3
size : 2000
free_items : 1997
references : 3

=================================


Establishing connection #2 to the MySQL server ..


Retrieved 5 row(s).

Host  User
----  ----
127.0.0.1 root
localhost
localhost root
unknown
unknown root


Client Statistics After Two Queries
-----------------------------------
bytes_sent : 190
bytes_received : 501
packets_sent : 4
packets_received : 21
protocol_overhead_in : 84
protocol_overhead_out : 16
bytes_received_ok_packet : 22
bytes_received_eof_packet : 18
bytes_received_rset_header_packet : 10
bytes_received_rset_field_meta_packet : 148
bytes_received_rset_row_packet : 157
packets_sent_command : 2
packets_received_ok : 2
packets_received_eof : 2
packets_received_rset_header : 2
packets_received_rset_field_meta : 3
packets_received_rset_row : 10
result_set_queries : 2
buffered_sets : 2
rows_fetched_from_server_normal : 8
rows_buffered_from_client_normal : 8
rows_fetched_from_client_normal_buffered : 8
rows_skipped_normal : 8
copy_on_write_performed : 13
connect_success : 2
active_connections : 2
active_persistent_connections : 1
explicit_free_result : 2
mem_erealloc_count : 1
mem_efree_count : 2
mem_realloc_count : 4
proto_text_fetched_string : 13
Statistics for Connection #2
----------------------------
bytes_sent : 100
bytes_received : 279
packets_sent : 2
packets_received : 12
protocol_overhead_in : 48
protocol_overhead_out : 8
bytes_received_ok_packet : 11
bytes_received_eof_packet : 9
bytes_received_rset_header_packet : 5
bytes_received_rset_field_meta_packet : 94
bytes_received_rset_row_packet : 87
packets_sent_command : 1
packets_received_ok : 1
packets_received_eof : 1
packets_received_rset_header : 1
packets_received_rset_field_meta : 2
packets_received_rset_row : 6
result_set_queries : 1
buffered_sets : 1
rows_fetched_from_server_normal : 5
rows_buffered_from_client_normal : 5
rows_skipped_normal : 5
connect_success : 1
active_connections : 1
explicit_free_result : 1
proto_text_fetched_string : 10

Cache Statistics After Two Queries
----------------------------------
put_misses : 13
get_hits : 13
size : 2000
free_items : 1987
references : 4

Before concluding, be adviced that some of the experimental functions that are available with ext/mysqli and libmysql are not available with ext/mysqli and mysqlnd. eg., mysqli_embedded_*(), mysqli_*rpl*_()

Related Blog Post:
MySQL Native Driver for PHP, mysqlnd

Acknowledgments
Andrey Hristov & Ulf Wendel, Sun-MySQL AB

(Originally posted on blogs.sun.com at:
http://blogs.sun.com/mandalika/entry/demonstrating_the_features_of_mysql)
_______________
Technorati Tags:
PHP | MySQL | mysqlnd

MySQL Native Driver for PHP, mysqlnd

2009-01-19T00:19:00.000-08:00

In order to communicate with the MySQL database server from a PHP application, ext/mysql, ext/mysqli and the PDO MYSQL driver rely on the MySQL client library, libmysql - that has the required implementation for the client-server protocol. The MySQL native driver for PHP (will simply be referred as mysqlnd from this point), is an additional, alternative way to connect from PHP 5 and PHP 6 to the MySQL Server 4.1 or later versions. mysqlnd is a replacement for the MySQL client library, libmysql; and it is tightly integrated into PHP starting with the release of PHP 5.3. That is, from PHP 5.3 onwards the developers can choose between libmysql and mysqlnd when using mysql, mysqli or PDO_MySQL extensions to connect to the MySQL server 4.1 or newer. Due to the tight integration into PHP 5.3 (and later), mysqlnd eliminates the dependency on the MySQL client programming support when the database extension(s) and the database driver are built with the support for mysqlnd.

mysqlnd is not another PHP extension like mysqli nor it has an exposed API to the userland. It is a library that provides almost similar functionality as that of the MySQL client library, libmysql. mysqlnd and libmysql libraries implement the MySQL communication protocol - hence both of those libraries can be used to connect to the MySQL Server.

Since mysqlnd is neither a new extension nor a programming API, but just an alternative to libmysql to connect from PHP to the MySQL Server, there is no need to make changes to the existing PHP scripts. The existing scripts which were running properly with the mysql, mysqli and PDO_MySQL extensions built with libmysql support, continue to run with the exact same behavior even when the mysql, mysqli and PDO_MySQL extensions are built with the mysqlnd support.

From the performance perspective, mysqlnd might be as fast as libmysql; and may even outperform libmysql in some cases. The generic recommendation is to try mysqlnd with your PHP application and to decide based on the performance results.

Some of the advantages of using mysqlnd are listed below:

Easy to compile: no linking against libmysql, and no dependency on the MySQL client programming support.
may outperform libmysql in certain cases
persistent connections for ext/mysqli
uses PHP memory management, supports PHP memory limit
reduced memory footprint -- keeps every row only once in memory, where as with libmysql you have it twice in memory
keeps a long list of performance related statistics for bottle-neck analysis

Installing PHP with the MySQL native driver, mysqlnd

As most of the pre-packaged PHP binary installations may not have the support for mysqlnd enabled by default, it is recommended to build PHP and the required database extensions with mysqlnd from the source code. The rest of this section focuses on the installation of PHP 5.3 from the source code. Check the php-mysqlnd web page at dev.mysql.com for the installation instructions for the prior versions of PHP with mysqlnd support.

Get the source code for PHP 5.3 or later versions from http://www.php.net or from http://snaps.php.net/.
Make sure that Autoconf 2.13 or later and GNU M4 1.4.7 or later are installed on the machine. Adjust the PATH environment variable to include the path to the autoconf and m4 tools.

eg.,
```
bash# ls /usr/local/bin/autoconf ; ls /usr/local/bin/m4
/usr/local/bin/autoconf
/usr/local/bin/m4

bash# export PATH=.:/usr/local/bin:$PATH
```
On Sun Solaris, create a soft link to gmake in the source directory.
```
bash# ln -s /usr/bin/gmake make
```

Navigate to the source directory and run buildconf.

eg.,



bash# cd php5.3-200811132130
bash# ./buildconf --force

For building PHP 5.3 or later with mysqlnd support on UNIX/Linux systems, you can decide for all three MySQL extensions (ext/mysql, ext/mysqli, PDO_mysql) whether they should be built using mysqlnd or libmysql. When choosing mysqlnd, use "mysqlnd" as path to the mysql client library. If you don't specify "mysqlnd" as library location, by default, PHP tries to use libmysql. It is possible to build one extension with one library, and another extension with another library. For example, you can build mysqli extension with mysqlnd support, and PDO_MYSQL with libmysql.

The configure option shown below builds all the three extensions with mysqlnd support.

eg.,
```
bash# ./configure --prefix=/export/home/php53 --enable-mysqlnd \
       --with-mysqli=shared,mysqlnd --with-mysql=shared,mysqlnd \
 --with-pdo-mysql=shared,mysqlnd --with-zlib=shared [other options]
```
configure script in PHP 6.0 and some builds of PHP 5.3 may not recognize the --enable-mysqlnd option, so check the configure options by running ./configure --help before specifying --enable-mysqlnd in the list of configure options when building PHP.

On Windows platform, mysqli extension uses the MySQL Native Driver by default in PHP versions 5.3 and newer. Hence you don't need to configure libmysql.dll
On Sun Solaris, pass the -z muldefs option to the linker to pro-actively defend against linker errors like ld: fatal: symbol `<symbol>' is multiply-defined.
```
bash# export LDFLAGS="-z muldefs"
```
Build PHP.
```
bash# make
```
Install PHP in the destination location.
```
bash# make install
```

Enable the required database extensions in php.ini

eg.,



bash# grep extension php.ini | grep -v \;
extension_dir=/export/home/php53/lib/php/extensions/no-debug-non-zts-20071006
extension="mysql.so"
extension="mysqli.so"
extension="pdo_mysql.so"

Finally verify the new PHP installation by checking the list of PHP modules.

eg.,



bash# cd /export/home/php53/bin

bash# ./php -m | grep mysql
mysql
mysqli
mysqlnd
pdo_mysql

bash# ./php -i | grep -i mysql
..
mysql
MySQL Support => enabled
Client API version => mysqlnd 5.0.5-dev - 081106 - $Revision: 1.3.2.18 $
..
mysqli
MysqlI Support => enabled
Client API library version => mysqlnd 5.0.1-beta - 070402 - $Revision: 321 $
..
mysqlnd
mysqlnd => enabled
Version => mysqlnd 5.0.5-dev - 081106 - $Revision: 1.3.2.18 $
..
pdo_mysql
PDO Driver for MySQL => enabled
Client API version => mysqlnd 5.0.5-dev - 081106 - $Revision: 1.3.2.18 $
..

Acknowledgments
Andrey Hristov & Ulf Wendel, Sun-MySQL

(Originally posted on blogs.sun.com at:
http://blogs.sun.com/mandalika/entry/mysql_native_driver_for_php)
_______________
Technorati Tags:
PHP | MySQL | mysqlnd

Xorg Screen Resolution Woes on SXCE

2009-01-10T00:36:00.000-08:00

Symptom(s)

A fresh install of Solaris Express Community Edition (SXCE) build 104 or an upgrade from an earlier SXCE build say 95 on a Toshiba Tecra M2 laptop results in a successful operating environment, but fails to bring up the graphical GNOME environment mainly because Xserver fails to start with errors like :0.0 refused by server.

Few ideas to get around the Xserver issue: (apparently I'm not an Xorg expert -- so spare me the criticism in case if you notice any unusual/incoherent terminology)

run kdmconfig and select Xorg
run /usr/X11/bin/xorgconfig -configure, and save the resulting xorg.conf in /etc/X11 directory. Finally start the GNOME Display Manager by typing gdm (run gdm-stop to stop the display manager).

If 800x600 is the best resolution you can get, and if nvidia-settings throws an error with message:


You do not appear to be using the NVIDIA X driver. Please edit your X configuration file 
(just run `nvidia-xconfig` as root), and restart the X server

.. try running nvidia-xconfig as root user. It will replace the existing /etc/X11/xorg.conf file. In case if the GNOME Display Manager fails to come up after this step, check the /var/log/Xorg.0.log file for any clue. If you notice something similar to the following lines in your Xorg.0.log, you may have to downgrade the NVIDIA driver to the latest versions of the supported legacy driver for your graphics processing unit (GPU).


...
...
(II) NVIDIA dlloader X Driver  177.82  Tue Nov  4 13:39:01 PST 2008
(II) NVIDIA Unified Driver for all Supported NVIDIA GPUs
(II) Primary Device is: PCI 01:00:0
(WW) NVIDIA(0): The NVIDIA GeForce FX Go5200 32M/64M GPU installed in this
(WW) NVIDIA(0):     system is supported through the NVIDIA 173.14.xx Legacy
(WW) NVIDIA(0):     drivers. Please visit
(WW) NVIDIA(0):     http://www.nvidia.com/object/unix.html for more
(WW) NVIDIA(0):     information.  The 177.82 NVIDIA driver will ignore this
(WW) NVIDIA(0):     GPU.  Continuing probe... 
(EE) No devices detected.

Fatal server error:
no screens found

Solution

The following instructions outline the steps to resolve the above issue on a Tecra M2 laptop running SXCE build 104.

Download the latest legacy GPU version (173.14.xx series) of the display driver from NVIDIA's web site
http://www.nvidia.com/object/solaris_display_173.14.12.html
Download the driver file
http://us.download.nvidia.com/solaris/173.14.12/NVIDIA-Solaris-x86-173.14.12.run
Install the driver
sh NVIDIA-Solaris-x86-173.14.12.run

This step removes the existing nvidia driver before installing the legacy one.
Reboot the system
reboot -- -r
Remove the Xorg configuration file if exists.
rm /etc/X11/xorg.conf
Generate a new Xorg configuration file
nvidia-xconfig
Logout and log back in.

The screen resolution issue should have been fixed automagically after the last step. Good Luck.

The above steps are based on NVIDIA's documentation:
http://www.nvidia.com/object/solaris_display_173.14.12.html

(Originally posted on blogs.sun.com at:
http://blogs.sun.com/mandalika/entry/fix_to_xorg_screen_resolution)

_____________
Technorati Tags:
Xorg | Troubleshooting | Solaris | nVidia

Quick Fix to the Linker Error - ld: fatal: library -lgcc_s: not found

2008-12-17T01:12:00.001-08:00

Symptom(s):

When building some of the open source applications like MySQL Cluster Carrier Grade Edition with the GCC compiler, the linking step may fail with the fatal error message library -lgcc_s: not found despite the fact that the gcc_s library is available on the system and is in the linker search path -- for example, LD_LIBRARY_PATH on Solaris.

Quick Fix / Workaround:

Add the absolute path to the gcc_s library on the link line, OR
(Recommended) If the library's filename has any major and minor version numbers appended to it, simply create a soft link to the library in the same location without the major and minor version numbers in the target filename.

eg.,

On a Solaris 10 system, by default, the gcc_s library might be available under /usr/sfw/lib (32-bit SPARC and x86 copy), /usr/sfw/lib/sparcv9 (64-bit SPARC copy) and /usr/sfw/lib/amd64 (64-bit x64 copy). If all you can find is the gcc_s library with the version number(s) in the filename like libgcc_s.so.1, simply run the command 'ln -s libgcc_s.so.1 libgcc_s.so' in the same directory where you found the libgcc_s.so.1.

Note:

Probably this particular error message may show up on any OS platform with any compiler, and the workaround mentioned in this post may work on all those failed cases. I cannot guarantee that, but I know for sure that it works on Solaris (both SPARC and x86/x64 versions).

_______________
Technorati Tags:
Linker | Linking | Troubleshooting

Mandalika's scratchpad

Solaris Tip: How-To Identify Memory Mapped Files

Unwanted Software Installers

Oracle Application Testing Suite (OATS): Few Tips & Tricks

Solaris Tip: Resolving "statd: cannot talk to statd at , RPC: Timed out(5)"

Siebel Troubleshooting : An ODBC error occurred; SBL-GEN-03006: Error calling function: DICFindTable m_pReqTbl

Oracle Database on NFS : Resolving "ORA-27086: unable to lock file - already in use" Error

Siebel Connection Broker Load Balancing Algorithm

Oracle RDBMS : Generic Large Object (LOB) Performance Guidelines

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

Gene Kelly, the Man Who Can Dance

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

PeopleSoft Application Server : Binding JSL Port to Multiple IP Addresses

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

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

Sri Satyanarayana Vratamu in Telugu Script

PeopleSoft Financials 9.0 (Day-in-the-Life) Benchmark on Oracle Sun

Oracle 11g : Poor Performance Accessing V$SESSION_FIX_CONTROL

Oracle's Optimized Solution for Siebel CRM 8.1.1

Oracle's Optimized Solution for PeopleSoft HCM 9.0

Instructions to Turn ON/OFF Hardware Prefetch on SPARC64 Systems

Instructions to Restore Files from a Windows Filesystem using Ubuntu Live CD

SPARC T3 reiterates Siebel CRM's Supremacy on T-series Hardware

Is it really Solaris Versus Windows & Linux?

OOW 2010 : Accelerate and Bullet-Proof Your Siebel CRM Deployment with Oracle's Sun Servers

Identifying Ideal Oracle Database Objects for Flash Storage and Accelerators

F5100 Flash Storage and F20 PCIe Flash Accelerator Card as Oracle Database Smart Flash Cache

PeopleSoft NA Payroll 500K EE Benchmark on Solaris : The Saga Continues ..

Book Review: Oracle Database 11g – Underground Advice for Database Administrators

Oracle RDBMS : Flushing a Single SQL Statement out of the Object Library Cache

Oracle Database: Say No Thanks! to a New Index

Monitoring Index Usage

Music : Few Mixed Tunes with GarageBand

Oracle Database 11g – Underground Advice for Database Administrators

Oracle 11g R1: Poor Data Pump Performance when Exporting a Partitioned Table

2004-2010 : A Look Back at Sun Published Oracle Benchmarks

2004

2005

2006

2007

2008

2009

2010

PeopleSoft Campus Solutions 9.0 benchmark on Sun SPARC Enterprise M4000 and X6270 blades

Oracle PeopleSoft Campus Solutions 9.0 Benchmark Test Results

Footnotes:

Extracting DDL Statements from a PeopleSoft Data Mover exported DAT file

PeopleSoft NA Payroll 240K EE Benchmark with 16 Job Streams : Another Home Run for Sun

Accessing MySQL Database(s) from StarOffice / OpenOffice.org Suite of Applications

Tasks to be Performed at the MySQL Server

Tasks to be Performed at the Client side (End-User's Workstation)

I. Connector/J approach

II Connector/OpenOffice.org approach

PeopleSoft North American Payroll on Sun Solaris with F5100 Flash Array : A blog Reprise

Java Code to Convert TWiki URLs to HTML Links

Sun achieves the Magic Number 50,000 on T5440 with Oracle Business Intelligence EE 10.1.3.4

Hindi Music - V.Man's Recommendations

T5440 Rocks [again] with Oracle Business Intelligence Enterprise Edition Workload

Oracle Business Intelligence on Sun : Few Best Practices

Instructions to Create a Facebook Application

Oracle Business Intelligence : Workaround / Solution to "[46036] Internal Assertion" Error

Mac OS X 10.5 Tip: Outgoing Only sendmail

Installing Siebel Web Extension (SWE) on top of Sun Java System Web Server 7.0

Oracle E-Business Suite R12 Database Tier on Solaris 10 x64

Developing MySQL Database Applications With PHP

Exploring the Features of MySQL Connector/C++

Controlling [Virtual] Network Interfaces in a Non-Global Solaris Zone

Create a Virtual Network Interface using Crossbow

Create a Non-Global Zone with the VNIC

Dynamic [Re]Configuration of the [Virtual] Network Interface in a Non-Global Zone

Adobe [Acrobat] Reader 9.1.x on [Open]Solaris

Accessing MySQL Database(s) with JDBC

Sun Blueprint : MySQL in Solaris Containers

PeopleSoft HRMS 8.9 Self-Service Benchmark on M3000 & T5120 Servers

Mounting Windows' NTFS on [Open]Solaris x86/x64

Workaround to "eject of cdrom /dev/dsk/cxtxdxsx failed" Error on SXCE b106

PHP: Memory savings with mysqlnd

Demonstrating the Features of MySQL Native Driver for PHP, mysqlnd

MySQL Native Driver for PHP, mysqlnd

Xorg Screen Resolution Woes on SXCE