Oracle Instance OptionsQuerying

Oracle Instance Options”Querying

The first key architectural issue the DBA must decide is how many Oracle instances will form the data warehouse for the purpose of supporting business intelligence queries? In essence, the DBA must decide how he or she will partition the data across instances. In fact, the answer to this one question alone will do more to define the available software and hardware architectural options open to the DBA than anything else.

For example, putting the entire data warehouse all in one instance will probably require a mainframe-like platform, whereas separating subject areas across instances will permit the DBA to use lots of smaller servers. Of course, it's really how the business users need access to the data that drives this decision. If your users must have access to all the subject areas, then separation may in fact make using the warehouse less simple.

Let's agree on some terminology to assist this discussion. If we use the term "data warehouse," or "DW," let's take that to mean the entire scope of all the subject areas. If we use the term "data mart," or "DM," let's take that to mean a subset of all the subject areas. Using these terms, let's examine our Oracle architecture options.

For those building an enterprise data warehouse, the options are (shown in Figure 2-2):

• Option 1 ” Entire DW in a single database, with a single instance, on a single server

• Option 2 ” Entire DW in a single database, with multiple instances, on a single server

• Option 3 ” Entire DW in a single database, with multiple instances, on multiple servers

Figure 2-2. Instance Options for Enterprise Data Warehouse

Note that the second option does not make much sense, unless you have a very large database server with an OS that supports partitioning of the hardware. Also note that both the second and third options require the use of OPS or RAC (OPS/RAC).

For those with separate and distinct data marts, the options are (shown in Figure 2-3):

• Option 1 ” All DMs in separate databases, with multiple instances, on a single server

• Option 2 ” All DMs in separate databases, with multiple instances, on multiple servers

Figure 2-3. Instance Options for Many Separate Data Marts

Note that the first option does not make much sense, unless you have a very large database server with an OS that supports partitioning of the hardware.

Of these database architectures, OPS/RAC is probably the least understood . In simple terms, OPS/RAC permits more than one instance (both the System Global Area [SGA] and processes) to connect to the same database (files). The instances can be on one or more heterogeneous servers; the only requirement is the ability to share one common file system.

OPS/RAC offers many potential advantages, including:

• Load balancing

• Fault tolerance

• Scalability

• Flexibility

However, these advantages come with some serious costs, including:

• Tougher to administer the OS

• Requires use of RAW devices

• Tougher to administer the database

• Tougher to diagnose/tune the database

• Tougher to backup/recover the database

• Generates more network traffic (i.e., inter-instance pinging)

• Limited maximum CPU power per DM or subject area

• Smaller pool of OPS/RAC qualified OS and DBA candidates