PerformanceWiki [the blog]

How to collect jcc trace in WebSphere to troubleshoot SQL

November 29, 2009 – 10:37 pm
In WebSphere (should be similar in other app server), follow listed steps to enable jcc tracing:          1.    Select the data source that uses the Universal Driver from the WebSphere administrative console.           2.    Click Custom Properties on the data source configuration screen.           3.    Enter value -1 (TRACE_ALL) for the traceLevel property. This is recommended for most problem determination because it generates the maximum amount of trace data. If only specific trace data is  needed, it is possible to enable only certain types of tracing.           Here are the values to enter for the "traceLevel" (spelled exactly) property, based on the type of trace data that is needed:          Trace Level Desired    traceLevel Property          TRACE_NONE    0          TRACE_CONNECTION_CALLS    1          TRACE_STATEMENT_CALLS    2          TRACE_RESULT_SET_CALLS    4          TRACE_DRIVER_CONFIGURATION    16          TRACE_CONNECTS    32          TRACE_DRDA_FLOWS    64          TRACE_RESULT_SET_META_DATA    128          TRACE_PARAMETER_META_DATA    256          TRACE_DIAGNOSTICS    512          TRACE_SQLJ    1024          TRACE_ALL    -1         4. Enter a fully-qualified directory and file name for the trace output for ...

Posted in Uncategorized | No Comments »

Generate heap dumps for IBM WebSphere

November 20, 2009 – 4:39 pm
Generate heap dumps for IBM WebSphere You can always generate JVM heap dumps manually by setting the following environment variables in the shell in where a JVM starts. IBM_HEAPDUMP=true    heap dump in .phd format used for newer memory tools IBM_JAVA_HEAPDUMP_TEXT=true    classic heap dump format for older tools SIGQUIT (kill -3 on UNIX; Crtl-Break on Windows) to generate You can also set up WebSphere v6 to automate heap dump generation. This enables the best method to analyze memory leak problems on AIX, Linux, and Windows operating systems. Manually generating heap dumps at appropriate times might be difficult. To help you analyze memory leak problems when memory leak detection occurs, some automated heap dump generation support is available. This functionality is available only for IBM Software Development Kit on AIX, Linux, and Windows operating systems. Most memory leak analysis tools perform some forms of difference evaluation on ...

Posted in Uncategorized | No Comments »

Running iozone on AIX with CPU Affinity

November 16, 2009 – 2:42 pm
You can leverage more CPUs to run iozone on a SMP machine with thefollowing instructions:The -P option of iozone sets the processor that will be used for the test.The & at the end of the command detaches the process so all the commands will run at the sametime.The -f option is the name of a tmp file that is created during the test and must be unique.The -b option is the output Excel worksheet file. This should be unique and end in .wks The directory used must exist.The user running these command must have write permission to both directories (root or sudo is best).The Excel .wks output should be written to /home/ioz (change to your particular dir if desired):With 4 CPUs defined in the AIX system, use the following commands within a script -cd /db2/d2xx/data01iozone_AIX -Raz -p -P 1 -f ioz.tmp1 -b /home/ioz/db2_d2xx_data01.wks & cd /db2/d2xx/data02iozone_AIX -Raz -p -P 2 ...

Posted in Uncategorized | No Comments »

Getting iozone to work on AIX 6.1

November 10, 2009 – 6:13 pm
Take the following steps to make IOZone (IO test tool) to work on AIX 6.11) Install and setup the build treerpm -ivv iozone-3.291-1.src.rpm2) Soft link xlc, IBM xlc must be installed (use lslpp to check)ln -s /usr/vac/bin/cc /usr/bin/cc3) Execute the buildcd /opt/freeware/src/packages/SPECSrpm -ba iozone-3-291-1.spec4) Execute the newly created install rpm packagecd ../RPMS/ppcrpm -Uvv --force *5) Test iozoneiozone_AIX -aRunning it...root@pwhost06 # iozone_AIX -a        Iozone: Performance Test of File I/O                Version $Revision: 3.291 $                Compiled for 32 bit mode.                Build: AIX        Contributors:William Norcott, Don Capps, Isom Crawford, Kirby Collins                     Al Slater, Scott Rhine, Mike Wisner, Ken Goss                     Steve Landherr, Brad Smith, Mark Kelly, Dr. Alain CYR,                     Randy Dunlap, Mark Montague, Dan Million,                     Jean-Marc Zucconi, Jeff Blomberg, Benny Halevy,                     Erik Habbinga, Kris Strecker, Walter Wong, Joshua Root.        Run began: Tue Nov 10 14:13:59 2009        Auto Mode        Command line used: iozone_AIX -a        Output is in Kbytes/sec        Time Resolution = 0.000001 seconds.        Processor cache size ...

Posted in Uncategorized | No Comments »

DB2 XML index, import, export

March 31, 2009 – 5:34 pm
some tips on dealing with DB2 XML column and data: 1) create an index of a xml path for search If in a XMl column, you want to search, you can create a xml index and convert it into VARCHAR so the values in the index will be used rather than scanning the entire xml documents. To create a XML index, do the following: CREATE INDEX i_Product ON PRODUCT(SDOC) GENERATE KEY USING XMLPATTERN '/root_attr/child_attr/child_attr' AS SQL VARCHAR(1000) DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0614N The index or index extension "I_ADDDPCON" cannot be created or altered because the combined length of the specified columns is too long. SQLSTATE=54008>If you get the above error, try the same statement but with a smaller VARCHAR size: CREATE INDEX i_Product ON PRODUCT(SDOC) GENERATE KEY USING XMLPATTERN '/root_attr/child_attr/child_attr' AS SQL VARCHAR(500)2) ...

Posted in ibm db2 | No Comments »

IO Stack [PIC]

January 22, 2008 – 5:29 pm
I/O stack components Overview Typically the stack consists of the following • Database • Character or block device driver • Multipathing driver • SCSI driver • HBA • System Bus • Switch/iSCSI routers • Storage Array • Disks Example of vendors: ASM-Database IBM Oracle Character or block device driver Multipathing driver – PowerPath, Windows MPIO SCSI driver HBA – Host Bus Adapter, Qlogic (IBM), Emulex System Bus - PCI Switch/iSCSI routers – Brocade, McData Storage Array – IBM DS8100, EMC, HDS, HP Disks - Seagate The database processes makes I/O calls to the block device. The I/O scheduler in Linux forms the interface between the generic block layer and the low level device drivers. The block layer provides functions that are utilized by the file systems, databases (10gR2) and the virtual memory manager to submit I/O requests to block devices. These requests are transformed by the I/O scheduler and made available to the low-level device drivers. The device drivers consume the transformed requests and forward them (by using device specific protocols) to the ...

Posted in hardware setup, oracle, ibm db2, Back-of-the-Envelope, Database, Storage, Design, Fibre Channel, Capacity planning | 1 Comment »

Oracle Query to Determine IO Imposed by Reports or Users

January 22, 2008 – 5:10 pm
Don't forget to add extra IO imposed by users of the database. For example, to determine how much extra I/O will be imposed on the system when the new load is put into place. For example, if more users or reports will be added, determine how much I/O these reports (or a typical user) currently does, then extrapolate for the estimated growth. The query below will assist in getting some of this data. SELECT a.sid, a.statistic#, SUBSTR(b.name,1,40) name, a.value FROM v$sesstat a, v$statname b, v$session se WHERE se.audsid = (select userenv('sessionid') from dual) AND a.statistic# = b.statistic# AND se.sid = a.sid AND b.name in ('physical read total IO requests','physical read total bytes','physical write total IO requests','physical write total bytes') ORDER BY b.class, b.name

Posted in oracle, Back-of-the-Envelope, Storage, Design, Capacity planning | No Comments »

IO Characteristics of Your Application

January 22, 2008 – 5:01 pm
IO rate of 1+ Gigabytes/sec is rarely achievable, but hundreds of Megabytes/sec is achieved in lots of high-end production environment. Keep those numbers in mind when planning for what's possible. In Oracle, use AWR reports to determine current I/O metrics (Instance Activity Stats per sec). • IOPS = “physical reads total I/O requests” + “physical writes total I/O requests” • MBytes/s = “physical reads total bytes” + physical writes total bytes” • For RAC environments - aggregate IOPS or MBytes/s for all nodes Now the tricky part – estimating expected I/O throughput rates • Establish what is being expanded – more users, new reports • Pull I/O stats for a given session or transaction and extrapolate to the required number of users. • Ensure data points are representative of the new workload • Know when your Web users get on your system (e.g., peak hour transactions) For new or non-existing applications, use business rules or data model transaction profiles flow ...

Posted in oracle, Back-of-the-Envelope, Database, Design, Capacity planning | No Comments »

2 Gigabit Card - How fast is it?

January 22, 2008 – 4:40 pm
Common misunderstandings: “If I buy 2 Gigabit HBAs I will get 2 Gigabytes of throughput, that’s more than enough throughput for my 400 MB/s application” “I only need 2 disks to store my 1 TB database, now that we have 500GB disk drives are available!” The first misunderstanding is not just about bits vs bytes, its understanding the context of when and where the [unit of measurement] bits vs bytes is used. For example, in the world of networks (Ethernet, Fibre Channel, etc.) bits is a common unit of measurement, whereas bytes is used to describe throughput outside the network; i.e., disks or HBAs. Just be aware of the unit measurement and the context of its use. To convert 2Gbits into Mbytes : 2Gbits = (2* 1024 * 1024 * 1024) / (1024* 1024) / 8bits = 2147483648 / 1048576 = 2048 / 8 = 256Mbytes The second misunderstanding has to do with sizing for capacity instead of sizing for ...

Posted in Back-of-the-Envelope, Database, Storage, Design, Fibre Channel, Capacity planning | No Comments »

IOPS vs. Mbytes/sec

January 22, 2008 – 4:24 pm
IOPS • The standard unit of measurement for I/O operations per second. Includes all reads and writes. • This is how you rate a I/O stack components ability to process small block random I/O requests. • Used to describe I/O rate driven applications (OLTP, random I/O ) Mbytes/s • Mega”Bytes” per sec • Used to measure large block sequential transfer rates, with no response time characterization • Used to describe data rate driven applications (DSS, OLAP) • Transaction/s – its anything you claim it to be. Oracle query to gather this data from your database: set lines 250 set pages 9999 spool sysmetric_outp.log alter session set nls_date_format='dd-mm-yyyy hh24:mi'; select min(begin_time), max(end_time), sum(case metric_name when 'Physical Read Total Bytes Per Sec' then average end) Physical_Read_Total_Bps, sum(case metric_name when 'Physical Write Total Bytes Per Sec' then average end) Physical_Write_Total_Bps, sum(case metric_name when 'Redo Generated Per Sec' then average end) Redo_Bytes_per_sec, sum(case metric_name when 'Physical Read Total IO Requests Per Sec' then average end) Physical_Read_IOPS, sum(case metric_name when 'Physical Write Total IO Requests Per Sec' then average end) Physical_write_IOPS, sum(case metric_name ...

Posted in oracle, Back-of-the-Envelope, Database, Storage, Design | No Comments »