Your goal as Oracle administrator is not simply to create a database but to be on time, be on budget, and meet the availability and performance targets of the business. As with any goal, careful planning is the key to success. You have little control over a number of factors that affect the success of your database: for example, application design and testing. This chapter discusses three important issues that are definitely within your circle of influence and that you cannot afford to ignore: licensing, architecture, and sizing.

Licensing

Oracle provides a choice of licenses, ranging from free licenses to licenses for basic functionality or more advanced functionality, and a wide range of individually priced extra-cost options. You do not need licensing keys to unlock Oracle software; anyone may freely download software from the Oracle web site and begin using it. This is an unusual practice that separates Oracle from other software vendors and has probably contributed to Oracle’s success in the marketplace. It is, therefore, very easy to make the mistake of using software for which you do not have the required licenses. Make sure the licensing question is answered as early in the software development project as possible.

Practical Example

Suppose you are considering using a high-end configuration of four Sun UltraSPARC T2 servers—each with four four-core CPUs—for an e-commerce web site. For extra horsepower and reliability, the production database will be handled by two servers clustered together using Oracle Real Application Clusters (RAC) technology. For protection against a catastrophe such as fire or flood, the third server will be located in a separate data center and will handle a standby database, which will be kept synchronized with the production database using Oracle’s Data Guard technology; you can also use the standby database for reporting purposes and backups if the Active Data Guard option is purchased. The fourth server is needed to house multiple development and testing databases.

Table 4-1 shows the license and support fees I calculated for a high-end configuration using the pricing document on the Oracle web site when I wrote this chapter; the prices are subject to change, so you should always download the latest version of the pricing document. I’ve included typical options that you might consider licensing, such as table partitioning, Diagnostics Pack, and Tuning Pack. Note that because each CPU has multiple cores, you have to multiply the number of cores by a factor of 0.75 to compute the number of equivalent CPUs that must be licensed. You can find more information on the treatment of multicore CPUs in the pricing document listed at the end of this chapter; AMD and Intel CPUs are treated differently than Sun CPUs.

Table 4-2 shows the prices I calculated for a low-end configuration consisting of four Windows servers, each with two Intel Xeon four-core CPUs.

The calculations may lead you to select a different hardware configuration or to license only the options that you really need. However, licensing costs, support fees, and feature sets are not the only considerations when making technology choices; development costs, personnel costs, hardware compatibility, and performance benchmarks are some of the other considerations.

Free to Download, Free to Learn, Unlimited Evaluation

You can freely download Oracle database software from the Oracle web site and use it for self-education, evaluate its suitability for a project, or develop a prototype of an application. The following language is found at www.oracle.com/technology/software/index.html:

No license keys are required to unlock Oracle database software, there is no limit to the length of the evaluation period, there are no restrictions on the use of the product, and you don’t need to provide information about yourself or your company if you don’t want to do so. Oracle does not appear to be worried about the potential for illegal use of its software and stands alone in the software industry in allowing its product to be downloaded and used in this way; some of its commercial success can undoubtedly be traced to its liberal download and usage policies.

Database Editions

Just as Microsoft does with Windows Vista, Oracle packages its software into different versions. Those who need only basic database functionality can buy a less expensive edition than a large international corporation that needs high-end features. Here are some of the editions that you can choose from:

• Oracle Express Edition: Provides a significant subset of Oracle Database functionality—comparable to that of Standard Edition, described next—as a free starter edition. There are significant restrictions on its use (for example, the database size is restricted to 4GB) but Express Edition can be used without charge even in commercial settings (for example, a starter database bundled with a software product) and formal classroom settings. A significant attraction is its ease of deployment; it is provided as a self-extracting executable for Windows or an RPM package for Linux.
• Standard Edition: Includes a significant subset of Oracle Database functionality. There are no restrictions on the size of the database, but there is a restriction on the size of the server; Standard Edition can only be installed on servers with a maximum capacity of four CPUs. It is not suitable for the largest databases, because performance and management options such as partitioning are not included; they can only be licensed along with Enterprise Edition, described next. Management packs such as Configuration Management and Change Management are also not available with Standard Edition. However, in an effort to promote the use of RAC technology (a high-availability feature), Oracle permits its use with Standard Edition if there are no more than four CPUs in the cluster.
• Enterprise Edition: Includes performance and management features required by the largest and most demanding databases, such as parallel query, query-results caching, parallel backup and recovery, and so on. Enterprise Edition costs much more than Standard Edition: $47,500 per single-core CPU, at time of writing. However, a number of features are not included even with Enterprise Edition and cost even more, the most significant being RAC, partitioning (a management and performance feature needed when dealing with the largest data tables), and Advanced Security (for encryption of data as well as network traffic). Other important features that also require additional license fees are the management packs, such as Configuration Management Pack, Change Management Pack, Provisioning Pack (for release management), Diagnostic Pack, and Tuning Pack.

Oracle licenses can be purchased on the Web at http://store.oracle.com. However, it is conventional to contact an Oracle sales representative so that discounts can be negotiated on large purchases. If your organization already uses Oracle, you have an assigned sales representative. If your organization is using Oracle for the first time, you can get in touch with a sales representative by calling the toll-free number listed on the Oracle web site.

As already indicated, Oracle software can be freely downloaded from the Oracle web site. Media packs (CD sets) can also be ordered from the online store for a nominal price. Reference manuals can be downloaded in PDF format from the Oracle web site at no charge, regardless of whether you buy licenses for Oracle software. Of particular interest to you at this stage are the installation guides for various operating system platforms. Printed copies of the reference manuals can be purchased if necessary, but they are quite expensive.

Architectural Choices

Oracle provides a variety of architectural choices to suit every need. The choice of architecture is determined by factors such as performance, availability, and scalability. For example, if you require very high availability, you could consider Oracle’s Data Guard architecture. If you need to start small and scale to very high volumes, you could consider Oracle’s RAC architecture. The following sections explore the most common architectural choices for Oracle databases.

Dedicated Server

This is the simplest Oracle configuration as well as the most common. It requires that each connection to the database be handled by a dedicated Oracle process; if 100 users were connected to the database, then 100 Oracle processes are required to handle them. You use dedicated server architecture when creating a database on your laptop in Chapter 6.

Shared Server

The dedicated server architecture does not work well for large numbers of connections because the Oracle Database processes contend for the limited amount of RAM. Large numbers of database connections are typically observed in Online Transaction Processing (OLTP) situations, but most of the connections are typically idle most of the time.

For example, suppose that all the employees of a company use the same computer program on their individual workstations to do their daily work. They remain connected to the database throughout the day, but each of them usually makes a very reasonable number of requests for information. The total time required to process each user’s requests is generally a very small fraction of the time the user is connected.

In such a situation, shared Oracle Database processes can be used to conserve RAM. The program running on the user’s workstation communicates with an Oracle Database dispatcher process instead of a dedicated Oracle Database process. The dispatcher process places the request on a request queue. Any available shared process handles the request and places the results on a response queue. The dispatcher then communicates the results to the user. Any information that has to be preserved until the next request—that is, any state information—is preserved in the SGA.

Real Application Clusters

A computer can efficiently handle only a certain number of requests at a time; this number is greatly dependent on the number of CPUs and the amount of RAM. As the number of requests increases beyond a certain threshold, processes begin competing for scarce resources, and all of them suffer. The easiest solution is to increase the number of CPUs and the amount of RAM, but large and powerful computers are also very expensive.

RAC is a technology that combines the resources of more than one computer. Two or more Oracle Database instances share access to the same set of disks and coordinate with each other over a fast network. Additional instances can be added to the cluster as the workload increases. This allows you to start with cheap commodity hardware and scale out as the workload increases instead of scaling up to a more powerful (and more expensive) computer.

RAC also has implications for database availability. For example, any single instance can be shut down for hardware maintenance or OS patching without affecting the availability of the database. In some cases, it is even possible to apply Oracle patches in rolling upgrade fashion.

Standby Database

A standby database can improve application availability. In this scenario, redo information from the main database is shipped and applied to another database, called the standby (which started life as a clone of the main database). In the event of a primary-database outage, applications can use the standby database. Applications can also be switched to the standby database when hardware maintenance or operating system maintenance needs to be performed on the primary database; even the outages associated with Oracle patch sets and upgrades can be avoided. Note that maintenance of the standby database can be automated and simplified using an Oracle product called Data Guard, which is part of Enterprise Edition.

To save money, it is typical for the standby database to have less hardware resources (CPU and memory) than the primary database; it is also typical to use a non-RAC standby database for an RAC primary database. Another interesting option is to use an active-active configuration of two or more computers. A typical active-active configuration has two databases, each on a separate server; the standby database of the first database is placed on the second server, and the standby database of the second database is placed on the first server. Each server thus hosts a primary database as well as a standby database. If one server suffers an outage, the standby database on the other node can be activated; this is a very cost-effective way of using hardware resources.

Maximum Availability Architecture (MAA)

Maximum Availability Architecture (MAA) can be used when both availability and performance are important and budgets are generous. The typical standby configuration uses less hardware resources for the standby database because the probability of a switchover is low; that is, there will be some amount of degradation in performance if the applications are pointed to the standby database. The MAA configuration combines RAC technology with standby technology and uses identically configured primary and secondary sites. The primary and standby databases both have the same number of RAC nodes.

MAA can be extended to the application tier, too; the details are in the white paper listed at the end of this chapter. Of course, identically configured primary and secondary sites (both database and application tiers) can be extremely expensive.

Sizing

The hardware resources your database needs depend on a number of factors such as the characteristics of the application (for example, OLTP vs. OLAP), the expected amount of activity, and the performance targets (such as the response time of business-critical transactions). However, it is rare to get good information on the expected amount of activity; ask how many simultaneously active sessions can be expected on average, and you may get a blank stare in return. Even if you have good information, sizing can still be tricky. Here are some strategies you can use:

• Ask vendors for help. Both hardware and software vendors have a great deal of experience in hardware sizing. You might even consider including the question of hardware sizing in the request for proposal (RFP) process.
• Use the results obtained from volume testing during the development phase.
• Use information about similar systems in the enterprise; for example, an enterprise may own several brands, each of which has a similar e-commerce database.
• Early in the exercise, set the expectation that the hardware sizing is only a best guess and additional hardware may have to be procured once the initial results are in.
• Be very generous in your estimates, if you are not on a tight budget. You can also use this strategy if the application is critical to the enterprise; in such cases it is preferable to be oversized than undersized.
• Ensure that there is room for expandability; for example, ensure that the system can accommodate more CPUs and memory if the estimates are found to be inadequate.

Disk Sizing

It is easy to underestimate the amount of space you need for the database. Another common mistake is not to leave sufficient room for growth. The following sections describe the different types of files that you have to worry about in forecasting disk-space needs for a database.

Data Files

Data files store your tables and indexes, and therefore you need to have some idea of how much data will be stored in tables in the foreseeable future. To estimate the amount of space required for a table, you have to estimate the average size of each row of data and the number of rows in the table. A reasonable rule of thumb is to allow as much room for indexes as you allow for tables. There is a fair amount of overhead space and white space in tables and indexes, and it would not be unreasonable to estimate that only 50% of the allocated space is usable. To summarize: estimate the amount of space required for tables, then double the number to allow for indexes, and then double the number again to allow for overhead space and white space.

You also need to allow room for the SYSTEM and SYSAUX tablespaces used by Oracle. Oracle uses the SYSTEM tablespace to store the data dictionary and the SYSAUX tablespace for other kinds of management information: for example, Active Workload Repository (AWR).

If you do not have a license for the Diagnostics Pack (which is required in order to use AWR), you can and should use a free Oracle-provided alternative called Statspack to collect performance data. In this case, you should reserve a sufficient amount of space for Statspack data.

You must also allow a sufficient amount of undo and temporary space. Undo refers to a copy of a data block that is made before the block is changed; it is used to restore the block in case the transaction does not commit its work. Temporary space is used for sorting operations during SQL queries. Don’t forget to leave a margin of error, especially if your estimates are not very reliable.

Control Files and Online Redo Logs

The space required for control files and online redo log files is usually modest; 2GB should suffice for most databases. In a highly demanding OLTP environment, you should pay careful attention to the placement of these files because they can quickly become a performance bottleneck.

Archived Redo Logs

Redo logs contain the information necessary to redo the database; they hold a record of all changes to the database. When redo logs fill up, they have to be copied to the archive destination before they can be reused. The amount of space required for the storage of archived redo logs depends on the amount of activity in the database; it also depends on retention preferences. An amount of space equal to the size of the database is not unreasonable for a busy OLTP database.

Backups and Exports

Allocating an area twice the size of the database for backups is not an unreasonable thing to do, unless you plan to put backups directly onto tape.

Software Executables and Related Files

You must allocate some space for the Oracle software and for various categories of error logs such as the alert log, listener log, and trace files. A reasonable rule of thumb for most databases is 4GB of space.

Other Disk Considerations

There’s more to worry about when it comes to files than just the amount of space they take. Usable space, file placement, and disk speed are some of the issues to consider.

Usable Space

When specifying the amount of space required for data, be clear that you are talking about usable space. For data protection, most disks use some form of redundant array of inexpensive disks (RAID) layout, which reduces the amount of usable space. RAID 10 (mirroring plus striping) reduces the amount of usable space by 50%. RAID 5 has the most usable space but reduces write performance; the details are outside the scope of this book. RAID 10 is the best choice for databases.

File Placement

Some attention must be paid to the placement of different categories of files; for example, data files should not be placed on the same file system where software, archived redo log files, or backups are stored. Optimal Flexible Architecture (OFA) is a set of recommendations provided by Oracle for the placement of different categories of files. OFA is automatically used by Database Configuration Assistant (DBCA) when creating databases.

Disk Speed

Pay attention to your disk ratings, because disk I/O can be a big performance bottleneck. If it takes 10 milliseconds to retrieve 1 data block (typically 8KB in size), then an SQL query may not be able to read more than 100 data rows per second, because the required rows will probably be spread out over many data blocks.

Memory Sizing

Memory sizing is heavily dependent on the characteristics of your application. The amount of space you need to meet performance targets is very difficult to estimate unless you have good information, such as the results of volume testing. The cost of memory is a consideration, but memory becomes cheaper every year, and it is no longer unusual to find databases using many gigabytes of memory. Here are the main considerations when planning memory requirements:

• Buffer cache: You need to allow enough space for Oracle to cache data blocks in memory, because disk I/O can slow your application tremendously. Note that you must a use a 64-bit operating system and the 64-bit version of Oracle to create very large buffer caches, because the 32-bit versions cannot handle large amounts of memory.
• Shared pool: The shared pool contains the library cache and the dictionary cache. Query-execution plans are stored in the library cache, and information about database objects is stored in the dictionary cache. It is rare to find a database that needs more than 1GB of memory for the shared pool.
• Stack space: You need to provide stack space for each Oracle connection. A commonly used rule of thumb is 512KB for each connection: for example, 50MB for 100 connections.

CPU Sizing

The number of CPUs you need depends not only on the speed of the CPUs—the faster the CPUs, the fewer you need—but also on the application load. A good rule of thumb is as many CPUs as the average number of simultaneously active sessions during the critical period of the day; the database for a big e-commerce application may require 16 CPUs to meet performance targets.

Note that if you use more than four CPUs, you will need to purchase Oracle Enterprise Edition, which is far costlier than Oracle Standard Edition.

Network Sizing

Finally, you must give some consideration to network requirements. A database that services hundreds of connections may require multiple network cards to meet performance targets. Fast connections are required to the storage area network (SAN) and between the nodes in an RAC cluster.

Modern Options

Recent years have seen several innovations that reduce the complexity and cost of provisioning Oracle Database: engineered systems and various flavors of cloud computing.

Engineered Systems

Instead of designing and implementing your own hardware and software configuration, you can simply buy a complete hardware and software package—a.k.a. an engineered system—directly from Oracle Corporation. Two examples are Oracle Database Appliance (ODA) and Exadata.

Oracle Database Appliance

Deploying RAC is a complex endeavor. ODA is a two-node plug-and-play RAC cluster running Enterprise Edition; it reduces typical deployment times from weeks to less than a day. ODA eliminates or automates the following classes of complex and error-prone work involved in a typical RAC deployment:

• Hardware sizing and design
• Special networking requirements for RAC
• SAN or NAS storage sizing and provisioning (ODA uses locally attached storage)
• Operating system installation and configuration (the operating system comes preinstalled)
• Security requirements (ODA uses best practices for operating system and database security)
• Oracle Database installation (the installation is automated)
• System testing and validation
• Project management for these activities

ODA offers more advantages than simplified provisioning. Oracle licenses can be purchased using the “pay-as-you-grow” model; that is, you can license only the number of CPU cores you need and disable the rest. Also, software patching is automated for all tiers (BIOS, firmware, operating system, and database).

If you are planning to use RAC and Enterprise Edition, you should check whether ODA has enough capacity for you .in terms of CPU cores, storage, and IOPS. If so, there is simply no reason for you not to use ODA. Each new generation of ODA has more capacity than the previous generation; at the time of writing, each of the two nodes in ODA has two 12-core Intel Xeon processors and 256GB of memory, and the configuration includes 9TB of usable storage (doubly mirrored) and four 200GB solid-state disks for the online redo logs.

Exadata

Exadata is the big brother of ODA and should be considered if your organization has outgrown the capacity of ODA. It comes in four progressively more powerful configurations: eighth-rack (two RAC nodes), quarter-rack (two RAC nodes), half-rack (four RAC nodes), and full-rack (eight RAC nodes). Like ODA, each new generation of Exadata has more capacity at the previous generation. At the time of writing, each Exadata node has two 12-core Intel Xeon processors (in an eighth-rack configuration, only half the number of cores are enabled on each server) and up to 512GB of memory. A full-rack configuration also uses 14 storage servers each with 12 CPU cores, 12 × 1.2 TB 10,000 RPM disks (or 12 × 4 TB 7,200 RPM disks), and 3.2TB of flash cache.

Expansion capabilities are available if you have even higher storage needs. This makes Exadata suitable for today’s most demanding workloads and database consolidation on a massive scale.

Cloud Computing

Cloud computing is “the delivery of computing as a service instead of a product” (Wikipedia). Gone are the days when the only option was to purchase dedicated hardware and software when your computing needs changed or increased. In a large sense, cloud computing represents a return to the earliest days of the computing era when mainframe computers and time sharing were the norm. Cloud computing not only is more economical than owning hardware and software outright but also offers the key advantage of elasticity; that it, you can acquire more computing resources as your needs grow.

Platform as a Service (PaaS)

In the PaaS model, you have the ability to create tables, indexes, views, and other kinds of database objects in a dedicated database schema. This schema is a tenant in a multi-tenanted Oracle database containing many tenants. Of course, each tenant is completely isolated from the others. Key aspects of database administration such as backups and monitoring are automatically handled for you. The schema is accessed using web interfaces. Oracle Corporation offers PaaS with a choice of 5GB, 20GB, or 50GB of database storage.

Infrastructure as a Service (IaaS)

In the IaaS model, customers receive access not just to a database schema but to an entire server. This server is accessible over the Internet and is typically a virtual machine carved out of a very powerful server. In mere minutes, you can have complete access to a fully configured and operational Linux or Windows server containing a fully configured and operational Oracle Database instance. As your needs grow, you can add CPU cores, memory, or disk storage. You can either bring your own Oracle Database licenses (BYOL) or pay a per-minute charge for each minute the servers are running. At the time of writing, Microsoft Azure was charging $1.60 per hour for access to a four-core server with 7GB of RAM. Storage and network bandwidth are charged for separately. Such a pay-per-use model with no long-term contractual commitments is an irresistible attraction to businesses that wish to avoid heavy capital expenditures (CAPEX). In some service plans, key aspects of database administration such as backups and monitoring are automatically handled for you (IT as a service).

At the time of writing, the two major providers of Oracle Database servers are Amazon EC2 and Microsoft Azure, with Oracle making its first steps into the field. If your company is an Oracle shop, you will definitely want to check Oracle’s offering.

Summary

Here is a short summary of the concepts touched on in this chapter:

• Oracle offers a variety of licensing options. Standard Edition is attractively priced but can only be used on servers with a maximum capacity of four CPUs. Enterprise Edition is expensive but offers high-end features such as parallelism. Certain other features that are only available with Enterprise Edition require additional fees; the list includes features such as partitioning, and management packs such as Diagnostics Pack and Tuning Pack.
• Oracle software can be used free of charge for prototyping and self-education.
• The dedicated server configuration is the most common configuration; each user session is handled by a dedicated process.
• The shared server configuration conserves resources; a small set of shared processes and dispatchers handles all sessions.
• Real Application Clusters (RAC) involve multiple Oracle instances connecting to the same database. This allows you to use cheap commodity hardware and scale out as the workload increases instead of scaling up to a more powerful (and more expensive) computer. RAC databases can improve application availability; for example, hardware maintenance can be performed on one node of the RAC database without affecting the availability of the application.
• A standby database can improve application availability. In this scenario, redo information from the main database is shipped and applied to another database.
• Maximum Available Architecture (MAA) requires identically configured primary and standby sites; for example, if the primary database uses RAC, then the standby database also uses RAC. The redundancy is extended to the application tier. MAA maximizes availability but can be very expensive.
• Sizing is a difficult task involving a lot of best guesses. Strategies to use include asking vendors for help, using results obtained from volume testing, studying similar systems in the enterprise, setting expectations correctly, estimating generously, oversizing when possible, and ensuring that there is room for expansion.
• Disk sizing must take the following categories of files into account: data files, control files, online redo logs, archived redo logs, backups, exports, software, error logs, and trace files. The use of RAID layouts is standard practice but reduces the amount of usable space. Raid 10 is the best choice for databases. Certain categories of files must be separated from each other.
• The main categories of memory usage to consider are the buffer cache, the shared pool, and stack space for user sessions.
• Instead of designing and implementing your own hardware and software configuration, you can buy a complete hardware and software package—an engineered system—directly from Oracle Corporation. Oracle Database Appliance is a two-node plug-and-play RAC cluster running Enterprise Edition. Exadata is suitable for today’s most demanding workloads and database consolidation on a massive scale.
• Cloud computing is “the delivery of computing as a service instead of a product.” In the PaaS model, you can create tables, indexes, views, and other kinds of database objects in a dedicated database schema. In the IaaS model, customers receive access not just to a database schema but to a virtual machine carved out of a very powerful server.

Further Reading

• Oracle Database Licensing Information 12c Release 1. Describes the various bundles, extra-cost options, and licensing restrictions. http://docs.oracle.com/database/121/index.htm.
• Software Investment Guide. Provides a management overview of licensing options. www.oracle.com/us/corporate/pricing/index.html.
• US Oracle Technology Commercial Price List. Lists the prices of the various bundles and the extra cost options; it is updated whenever prices change. www.oracle.com/us/corporate/pricing/price-lists/index.html.