tkprof is one of the most useful utilities available to Admins for diagnosing performance issues. It essentially formats a trace file into a more readable format for performance analysis. The DBA can then identify and resolve performance issues such as poor SQL, indexing, and wait events.

Why, When tkprof?

Use tkprof and session tracing when the database or a particular session is having performance problems. tkprof will generally be used infrequently, when researching a very particular performance issue. A user may complain that the response time for a session is abysmal compared to the prior week. Session tracing and tkprof can be used to see exactly what is happening on the database, enabling the DBA to take corrective action.

The utility can also be used to view SQL that is being executed for an application. In some situations, this will be the only mechanism a DBA will have to view SQL. These situations include the execution of encrypted PL/SQL code on the database or submission of SQL statements from third party applications.

Identifying the Siebel Server Task

To help diagnose problems that occur with Siebel server tasks it may be necessary to get an RDBMS trace. To perform such a trace on a server task on Oracle RDBMS, carry out the following:

1.First of all identify the process id of the Siebel server task. This can be done from:

Server Administration > Server Tasks (in version 5.x, 6.x)

Server Administration > Tasks (in version 7.x)

Administration – Server Management > Tasks (in version 8.0).

There is a PID column that identifies the process id for the server task. Make a note of the PID identified.

2.Log on to the Siebel database with SQL*Plus as a user with permissions to query the V$ tables for example DBA or SYSTEM and run the following SQL statement:

SELECT SERIAL#, SID
FROM V$SESSION
WHERE PROCESS LIKE 'PID:%'

Where PID is the PID identified in step 1. Make a note of the SID and SERIAL# pairs identified as there may be more than one Oracle session per process.

3.Then start SQL tracing for each SID and SERIAL# pair identified in step 2:

exec sys.dbms_system.set_sql_trace_in_session (SID, SERIAL#, true);

Tracing for this session is now on and Oracle writes to the user dump destination specified by the USER_DUMP_DEST initialization parameter. To turn the trace off:

exec sys.dbms_system.set_sql_trace_in_session (SID, SERIAL#, false);

4.Finally format the trace file with the TKPROF utility. TKPROF accepts as input a trace file produced by the SQL trace facility, and it produces a formatted output file. Run TKPROF on each individual trace file. For example:

TKPROF ora53269.trc ora53269.prf SORT = (EXECPU, FCHCPU)

The SORT value causes TKPROF to sort the SQL statements in order of the sum of the CPU time spent executing and the CPU time spent fetching rows before writing them to the output file.

In a nutshell a database index is an auxiliary data structure which allows for faster retrieval of data stored in the database. They are keyed off of a specific column so that queries like “Give me all people with a last name of ‘Sam’” are fast.

Indexes don’t come for free. What you gain for in retrieval speed you lose in insertion and deletion speed because every time you alter a table the indexes must be updated accordingly. If your table is updating frequently it’s possible that having indexes will cause overall performance of your database to suffer. There is also a space penalty, as the indexes take up space in memory or on disk. A single index is smaller than the table because it doesn’t contain all the data, only pointers to the data, but in general the larger the table the larger the index

(1) When to Index

- Columns frequently used in search or query expressions
- Columns used to join tables
- High-selectivity keys. The selectivity of an index is the percentage of rows in a table having the same value for the indexed key. An index's selectivity is optimal if few rows have the same value.
- Foreign keys

(2) When do not Index
- Columns and expressions with few distinct values
- Frequently updated columns

Consider indexing foreign keys of referential integrity constraints, since this column is commonly used to join both tables. Indexing foreign key columns helps avoid full table scans while searching for matching rows in the child when DML is performed on the parent.

When choosing to index a column (or group of columns), consider whether the performance gain for queries is worth the performance loss for massive INSERTs, UPDATEs, and DELETEs and the quantity of space required to store the index. You might want to experiment by comparing the processing times of the SQL statements with and without indexes. You can measure processing time with the SQL Trace facility.

Finally, the best way to know if you need to index or not is generating the execution plan for the query. Analyzing it you can determine if an index should be or not created.

In order to avoid trouble arising from corrupted siebns.dat files, we should create backup copies regularly and before each major modification of the Enterprise configuration.

The following command at the srvrmgr prompt allows us to create a backup copy of siebns.dat and specify a name for the new file. If the name is omitted, the file name will contain the current timestamp.

backup nameserver [filename]

Use DB2 load replace option when loading EIM tables and turn off table logging if possible.Use separate tablespaces for EIM tables and the base tables.Use large page sizes for the EIM and the larger base tables. A page size of 16KB or 32KB has been found to give good performance in practice. The larger page sizes allow more data to be fitted on a single page and also reduce the number of levels in the index B-tree structures.Similarly, use large extent sizes for both the EIM and the large base tables.

Consider using DMS containers for all Siebel tablespaces. Using raw devices or volumes will further help in improving performance.Ensure that the tablespace containers are equitably distributed across the logical and physical disks and the I/O controllers of the database server.Use separate bufferpools for EIM tables and the target base tables. Since initial EIM loads are quite large and there are usually no online users, it is recommended to allocate a significant amount of memory to the EIM and the base table bufferpools.Reorg tables if data on disk is fragmented. Use reorgchk utility with current statistics to find the fragmented table/index.

Periodically ensure that table and index statistics are collected. Do not use “RUNSTATS” with the “DETAILED” option.Use DB2 snapshot monitors to ensure performance is optimal and to detect and resolve any performance bottlenecks.Log retain can be turned "OFF" during the initial load. But remember to turn it back on before going live.For the EIM and the base tables involved, alter the table to set them to VOLATILE. This will ensure that indexes are preferred over table scans.

• DB2_CORRELATED_PREDICATES = YES
• DB2_HASH_JOIN = NO
• DB2_RR_TO_RS = YES
• DB2_PARALLEL_IO = “*”
• DB2_STRIPPED_CONTAINERS = ON (when using RAID devices for tablespace containers)

• INTRA_PARALLEL = NO (may be used during large index creation)
• MAX_QUERYDEGREE = 1 (may be increased during large index creation)
• SHEAPTHRES = 100,000 (depends upon available memory, SORTHEAP setting, and other factors)

• CATALOGCACHE_SZ = 6400
• DFT_QUERYOPT = 3
• LOCKLIST = 5000
• LOCKTIMEOUT = 120 (between 30 and 120)
• LOGBUFSZ = 512
• LOGFILESZ = 8000 or higher
• LOGPRIMARY = 20 or higher
• LOGRETAIN = NO (only during initial EIM loads)
• MAXLOCKS = 30
• MINCOMMIT = 1
• NUM_IOCLEANERS = Number of CPUs in the database server
• NUM_IOSERVERS = Number of disks containing DB2 containers
• SORTHEAP = 10240 (for initial EIM loads only, thereafter, during production run set it to between 64 and 256)
• STAT_HEAP_SZ = 8000

Prior to running EIM it is important to clean up fragmented objects, especially those that will be used during the EIM processing. The following SQL statement can be used to identify objects with greater than 10 extents:

SELECT segment_name,segment_type,tablespace_name,extents
FROM dba_segments
WHERE owner = (Siebel table_owner)
and extents > 9;

To fix fragmentation, the objects will need to be rebuilt with appropriate storage parameters. Always be careful when rebuilding objects because of defaults, triggers, etc. on the objects.

Optimizer Mode

Oracle optimization mode can also affect EIM performance. The Siebel application has been found to perform better under RULE based optimization under normal application usage. While there have been cases where it has been found that Cost based optimization has improved EIM performance this should only be attempted as a last resort and must be switched back to RULE for online usage. Be aware that Siebel only supports Rule-Based optimization. Optimization mode can be verified by running the following query:

SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = ‘OPTIMIZER_MODE’;

(must have DBA privilege)

Multiple EIM processes can be executed against an EIM table provided they all use different batches or batch ranges. The concern is that contention for locks on common objects may be experienced. To run multiple jobs in parallel against the same EIM table, check that the ‘FREELIST’ parameter is set appropriately for the tables and indexes used in the EIM processing. This would include the EIM tables and indexes as well as the base tables and indexes.

The value of this parameter specifies the number of block ids that will be stored in memory which are available for record insertion. As a rule of thumb, users should set this to at least half of the intended number of parallel jobs to be run against the same EIM table (example, a FREELIST setting of 10 should permit up to 20 parallel jobs against the same EIM table). This parameter is set at the time of object creation and the default for this parameter is 1. To check the value of this parameter for a particular object the following query can be used:

SELECT SEGMENT_NAME, SEGMENT_TYPE, FREELISTS
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME=’’;

To change this parameter the object must be rebuilt. Again, be careful when rebuilding objects because of defaults, triggers, etc. on the objects. To rebuild the object follow the steps below:

1. Export the data from the table with the grants.
2. Drop the table.
3. Re-create the table with the desired FREELIST parameter.
4. Import the data back into the table
5. Rebuild the indexes with the desired FREELIST parameter.