Careful analysis of your client applications helps you configure the database server to fit your unique uses and requirements. Once you have determined how the database server will be used, you can more easily determine how it might be configured.
As you perform the analysis, consider more than one way of setting up the database server and its databases. To decide on the most appropriate configuration, use a test system and try sample uses of the applications with an appropriately scaled user load. As you test possible configurations, however, remember that not all aspects of the database server scale at the same rate.
Traditionally, database applications fall into one of two categories: decision support (DSS) and online transaction processing (OLTP). Your database server might host operational data store (ODS) application, which is a kind of hybrid between OLTP and DSS. Extended Parallel Server is designed for the best performance of DSS and ODS applications.
These categories can be broken down still further, as described in the following sections. Consider the categories into which your applications fit, and decide what requirements are generally most important.
For DSS applications, a specially designed database called a data warehouse is created from extracted and consolidated corporate data originally stored in OLTP or other detailed-record databases. A data warehouse can provide historical information for decision-support queries of all kinds.
A specialized data warehouse, called a data mart, usually draws selected data designed to answer specific types of questions or to support a specific function within an organization.
Queries against a data warehouse or data mart perform more complex compute-intensive and memory-intensive tasks than OLTP, often including scans of entire tables, manipulation of large amounts of data, joins of multiple tables, and the creation of temporary tables. Such operations require large amounts of memory. Because of their complexity, DSS queries might not execute quickly and users do not expect instantaneous response.
When you configure a database server for DSS applications, pay special attention to planned table schemas and disk location of table fragments for the following primary purposes:
In addition, DSS applications require careful management of memory. The kind of DSS applications your database server supports determine how you manage memory allocation for queries. You might decide to allow the database server to allocate memory for queries automatically based on query complexity and data-distribution statistics or you might specify the range within which memory should be granted to each query. These considerations are discussed in the following sections.
For information about table schemas and deployment, see Planning Table and Index Fragmentation. For disk storage guidelines, see Working With Available Hardware Resources. For information about memory management, as described in Tuning Memory-Management Parameters.
DSS queries fall into two categories, planned and ad hoc. Many DSS database systems support a mixed workload of both planned and ad hoc queries. For information about features that help you support a mixed workload, see Adjusting Resource Use by Specific Sessions.
In the past, data warehouse applications consisted mainly of carefully tuned queries designed to answer complicated questions that are asked repeatedly, usually to track and predict business changes, such as in the customer base, sales, or profits.
If users run only such repeated queries, you can use techniques described inAnalyze Complex Query Plans to tune each query carefully for the best performance and most efficient use of database server resources, using. Although such DSS queries might require huge amounts of memory and CPU power, planned queries can be scheduled to run at specific times in some DSS environments to help balance the load on the database server and can be assigned a PDQPRIORITY value that limits the amount of memory allocated for each query. For information about setting PDQPRIORITY and related parameters, see Allocating Memory for DSS Queries. For information about allowing the database server to allocate appropriate memory automatically, see Enabling Optimizer-Determined Memory Allocation.
The major effort in a planned-query DSS application is the initial tuning of the queries, using information output about the query plan during the first few times that queries run. Appropriate monitoring of the database server, as described in Tuning Configuration for Resource Usage, identifies query-processing hot spots and helps you adjust the database server or the query statements.
Users have increasing access to tools that allow them to construct ad hoc queries against the database. Such queries can range from simple statements that require little memory and processing power to complex queries that require much memory and processing power as well as large amounts of temporary space.
Because users create these queries spontaneously at unscheduled times, the load on the database server can easily become unbalanced and response times become unpredictable. Even users who run planned queries at unplanned times can unbalance the server and degrade performance.
Such workloads can be managed by a combination of tools, such as I-Spy, and database server features, as described in Managing Resource Use for Specific Sessions.
OLTP applications are used to capture new data or update existing data. These operations typically involve quick, indexed access to a small number of rows. OLTP applications are often multiuser applications with acceptable response times in fractions of seconds.
OLTP applications have the following characteristics:
When you configure a database server for OLTP applications, pay special attention to planning use of the following database server features:
An operational data store is a hybrid database application midway between an OLTP and a DSS application. It has the following characteristics:
Such a database application requires inter-query instead of intra-query parallelism, with serial plans and index-based data access instead of parallel plans and table scans. The fragmentation goals are different from DSS applications. For information, see Evaluating Fragmentation Factors for Both DSS and OLTP Clients.
Both OLTP and DSS applications require updates of data.
Tables that are updated by batch jobs might be fragmented so that updates occur by loading data into a new fragment, attaching the new fragment, and detaching fragments no longer needed. If the batch job refreshes existing data in the table, take advantage of the parallel processing and update capabilities of the database server, as described in Improving Large Data Updates.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]