1351-1352
Page 1351
Tuning Data Warehouses in Oracle
Tuning an Oracle application and database instance is a bit of an art. So many components are interacting with one another that it is hard to separate which component is the dominant factor. Also, changes to one tuning parameter can cause problems to occur with another parameter. (For more information on this topic, see Chapter 23, "Performance Tuning and Optimization.") Data warehouses are one of the few forms of Oracle database that almost always require some tuning. The vast majority of Oracle databases I have worked on require little or no tuning. The data warehouses I have worked on from 20GB to 135GB, however, have needed a lot of tuning. Worse yet, you sometimes have to use tuning parameters not found in common Oracle documentation to get the most out of your systems. Because these parameters vary from operating system to operating system, I do not discuss them in detail here. Typically, you only use these parameters if Oracle tells you to use them.
It is important that you balance database tuning with application tuning. The first reaction of users or developers to a long-running query is that there is some grotesque problem with the database (and perhaps the DBA). They go to their management, who goes to their management, and eventually it becomes a crisis that is dumped onto the DBA. In my experience as a DBA, if the DBA has performed routine tuning and checking of the database itself, the problem almost always is in the way the application's query was written; or, it is something that cannot be fixed without more hardware, memory, or disk drives . One group I worked with had a dedicated application tuner who routinely turned queries that were terminated after 50 minutes of waiting into queries that executed in less than 30 seconds. The key is to use Oracle's tracing feature to study the execution plans used for the query. When Oracle chooses poorly, you have to give it hints or rewrite the SQL statement a little bit to make things go faster. Some of this need to tune SQL statements is reduced in later versions of Oracle (7.3.2.3 and later), because the cost-based optimizer is becoming much better at choosing the best query plan. You still have to remember to gather the statistics the cost-based optimizer needs to make the correct decisions, however. With the size of data warehouses, you usually wind up doing percentage samples of the fact tables instead of complete analyses; these samples usually provide very good results.
Oracle Data Marts
One of the products I have worked with recently is Oracle Data Marts. The reason behind this product is simple enough. A lot of people are building data warehouses. These projects often cost millions of dollars (hardware, software, and labor) and take years to complete. If someone could create a more efficient means of building data warehouses, there would be a good market for this product.
The concept of a data mart is that of a smaller, subject-oriented collection of data. A data warehouse is larger and contains many subjects (marketing, purchasing, and so on) on a single database. The data mart's subject-oriented approach simplifies the process, because you do not
Page 1352
have to worry about integrating the needs of multiple user communities. You also do not have to deal with the fact that various groups usually want to see the same data but in slightly different formats.
With the basic components of the Oracle Data Marts product, Oracle took some computer-aided software technology (from its Designer/2000 product) and used it to help you capture the schema of your existing OLTP systems. You also can use this tool to build a schema for your target data mart. You still have to design the target schema manually, but this tool can help you check for data and linkage problems.
The next tool in the Data Mart product is the product that lets you build the transformations between the old data stores and the new data mart. This is a simple GUI-based tool that enables you to select a source, link it to a target (selecting the fields from a listbox), and then add transformations (summations and subtraction, for example). Transformations are added from a standard list that then brings up a fill-in-the-blanks window instead of making the user type in the SQL formulas. This environment enables you to quickly build the conversion routines in a graphical format users can understand. These conversions can be extended to more complex routines using Microsoft COM objects (a Visual Basic program, for example).
The best part about the Data Mart Builder product is that the transformations you build can be run through a series of data extraction and conversion services that Oracle provides. These routines can use SQL*Net to move data from one set of tables to another. These services can be set up to run at predefined intervals to make the process of moving data highly automated.
The final major component of the Data Mart suite is the Discoverer/2000 product. This is a tool that allows end users to access data with ad-hoc or predefined queries. You can do a lot of setup work to make the schema really easy for the users to understand. Users do not have to understand table and column names in the database, for example. You can program in aliases that describe the data in terms with which the users are familiar.
The Data Mart suite also includes a copy of the Oracle Enterprise Server and Web Server products. These are the standard products that you can buy elsewhere, but wrapping them into this bundle makes things easier on the acquisition side of the house. You should seriously consider using a Web interface to your databases when building new products. This interface may mean a little bit more in terms of server resources, but it saves you from having to perform complex setup work on your user workstations. This can cost quite a bit of time if you have thousands of users. In the Web-based world, all users need is a network connection, an operating system such as UNIX, Windows 95 or Windows NT, and a Web browser.
| NOTE |
You will soon be able to buy off-the-shelf products that use the Data Mart suite of products to extract information from a standard source (Oracle's Financial suite, for example) and |
EAN: 2147483647
Pages: 391