Previous | Table of Contents | Next

Page 743

ANALYZE command) to pick the best way to access the data. Any selectivity that could have been gained through literals, which would help reduce the scale of the intermediate tables and final output returned, would be negated.

With OLTP, you want many relatively small rollback segments. With DSS and long-running queries, similar to long-running batch production systems, you want one or two large rollback segments. This is only to allow read-consistent, concurrent views of large amounts of rollback data. If the DSS were 100 percent read only, this would be a non-issue. In practice, I have found there are virtually no 100 percent read-only systems.

Other Considerations for both OLTP and DSS

Additional considerations are the Oracle block-level parameters INITRANS and PCTFREE for both OLTP and DSS. For OLTP, set PCTFREE relatively low (or just leave it at the default) because each block must have room to grow and change. With mostly read-only DSS, set PCTFREE high, because the data in the blocks will remain mostly static. For OLTP, you want to set INITRANS relatively high because a high number of concurrent users will otherwise trigger dynamic transaction header slot expansion, and consequentially block-level reorganizations, possibly escalating up to the extent levels.

With DSS, there's little chance of concurrent users having to wait due to locking because most transactions will be selects, not to mention the large amount of blocks a DSS stores. Hence, set INITRANS to some medium level if your DSS also supports a moderate level of insert, update, or delete. Otherwise, it can be left at the default.

Understanding Benchmarks

A benchmark is just a criterion that is representative of an application's real work. Chapter 3 mentions that quantitative measures as a result of transaction analysis can also be used as benchmarks. The performance of an application on its benchmark run is measured by some specifically chosen quantitative measures. With benchmark runs, you usually have preset conditions or configurations that you keep constant (the initial conditions, or dependent variables) while changing one variable (an independent variable) at a time between runs. This allows you to study the impact of these variables separately. Hence, benchmarking is just a variation on the scientific, or experimental, method. If done properly, you can assess cause and effect, or at the very least, correlation of effects. Your hypothesis is that changing the independent variable (for example, block size) will affect the dependent variables (for example, throughput).

Benchmarking can provide various benefits. Usually, benchmarking is associated with simulation. A simulation of a system is a functioning prototype that mimics the real work of that system to predict its performance or test its functionality. However, benchmarks also offer the capability to run baselines, which are the relevant quantitative measures you take right after you release a production system, or right before you begin your performance tuning effort. It gives the DBA a basis for comparison, to test whether his or her performance tuning changes have caused any improvement.

Page 744

The best known of the database system benchmarks are those prepared by the Transaction Processing Performance Council (TPC). This council has produced numerous benchmarks, but the first ever created were simply representative bank teller operations (TPC-A and TPC-B). For these benchmarks, a small database is created, including a bank table, a branch table, and an account table. The operations include updates to these tables and inserts to a history table. Performance is measured in terms of transactions per second (tps), a quantitative measure of throughput. Because TPC-A and TPC-B were released several years ago, the TPC has come out with newer benchmarks, TPC-C and TPC-D, aimed at benchmarking different kinds of applications.

TPC-A (1989) tests an OLTP environment with high concurrency and significant I/O, using one relatively simple, multitasked transaction. (A transaction is multitasked when it performs more than one single select, insert, update, or delete.) TPC uses the term transaction type to refer to a specific, multitasked transaction. In TPC-A, the transaction has multiple updates and an insert.

TPC-B (1990) is basically the same thing as TPC-A, except that it removes the high concurrency requirement. It is used as a database stress test, essentially just testing significant I/O. Yet this distinction is vague when using virtual terminals in simulation software, so often these two benchmarks are reported together or in lieu of one another.

TPC-C (1992) is an OLTP benchmark involving a mix of five different, more complex transaction types and nine database entities. By contrast, TPC-A and TPC-B use a single transaction type and four database entities. The business is now based on a supplier database with an order entry system, as opposed to a bank teller application.

TPC-D (1995) is a DSS benchmark that focuses especially on heavy, real-world transactions. It includes 17 complex queries against large amounts of data. This is the latest TPC benchmark offering. TPC-D is geared toward heavyweight queries that run for long periods of time, in contrast to the lighterweight TPC-A and TPC-B and the relatively middleweight TPC-C.

When performed in an unbiased, independent environment, these benchmarks provide a basis for comparison between RDBMS software vendors. The database systems can be compared both tuned and untuned. The application is the same, so if the networking is the same and the platform (hardware and OS) is very much the same, the only variable remaining is the RDBMS software. It's often the case that no two vendors will compete on the same platform with the same benchmark (at least within the same time period).

With good simulation software, such as LoadRunner, virtual users and processes can be made to simulate apparent load on a database system during the benchmark. The number of concurrent users can be increased incrementally, as can the number of transactions per user. Either way, the total number of transactions is increased, and you can effectively create and observe a database stress test. When the runs are validated and the statistics (tps) are gathered, you can explore the ROI strategy and use the performance tuning fundamentals as necessary to attack the general problem of tuning, even if done on a simulated system.

Previous | Table of Contents | Next