The following major performance goals for both DSS and OLTP clients can be achieved in part by fragmenting tables appropriately:
For best performance, plan table fragmentation to help keep all CPU VPs on all coservers evenly busy.
For OLTP clients, transactions should be initiated evenly across coservers and each transaction for a session should access data only on one coserver, if possible. For DSS clients, distribute table fragments across coservers and disks so that queries can take maximum advantage of parallel scans.
The database server creates one scan thread for each fragment that is read to satisfy a query. The CPU VPs manage 95 percent of all threads, and scan threads are included in this category. The optimal number of fragments per CPU VP is a major factor in performance tuning. If the entire table is usually scanned and no fragments can be eliminated, plan for one or two fragments for each CPU VP. If fragment elimination can often occur, you might be able to specify more fragments for each table.
I/O contention occurs both between queries and within a query, when two table fragments on the same disk are joined.
To eliminate I/O contention, place each table fragment in its own dbspace. Place the dbspaces on separate disks, if possible. If your system uses a disk array in which the physical disks are not visible to the operating system, ask Information Services staff how the virtual disks are mapped to physical disks at the logical unit level. Also place fragments of commonly joined tables on separate disks.
For information about creating spaces for table fragments, see Planning Storage Spaces to Support Fragmentation Schemes. For information about RAID and other disk arrays, see I/O Resources.
The fragmentation schemes that you implement to achieve these performance goals might be different for an OLTP client than for a DSS client. DSS and OLTP clients have some additional specific requirements, described in the following sections.
Data contention occurs between queries or transactions when one query has locked a data page that another query requires.
To reduce data contention if queries access only a few table rows at random in any table, consider fragmentation schemes that increase the number of fragments. If the table is divided into a larger number of table fragments, multiple transactions are less likely to require the same fragment.
OLTP transactions perform best when they are executed as serial plans. The database server usually creates a serial plan when all data required by the transaction is on local disks on a single coserver.
When you design a fragmentation strategy for the entire database, use the table-access information that you created. Make sure that fragments of tables are grouped together appropriately for joins on the same coserver, if possible. Create duplicate copies of small or unfragmented tables to facilitate serial plans.
The database server scans a table faster when it scans many table fragments in parallel instead of scanning a single large table fragment.
If most queries scan entire tables, create a test fragmentation scheme with two table fragments per CPU VP. Monitor table scans to make sure that the CPU VPs are consistently busy. If CPU time is still available, you might increase the number of fragments.
Overall execution time can be reduced if the fragmentation scheme allows the database server to identify table fragments that are not required to satisfy a query. Fragments that are not needed are eliminated from the query plan.
If most queries can eliminate table fragments, you might fragment the table with more than two fragments per CPU VP.
If you load data from external files into a data warehouse or data mart, if possible fragment the base tables so that you can attach a new table fragment and detach an old table fragment. Attaching and detaching table fragments is faster and simpler than appending or inserting rows and deleting rows.
A collocated join occurs when the data to be joined for a query resides in table fragments on the same coserver. The joined data is shipped to the other coservers for further processing only after the join is complete.
Analyze your table access information and determine whether you can fragment tables that are often joined so that collocated joins can occur. For information about creating dbslices that facilitate collocated joins, see Creating Dbslices for Collocated Joins.