Benjamin Franklin thought an ounce of prevention was worth a pound of cure. This saying can be applied to tuning your Oracle 12c database. At today’s prices, a pound of cure comes with a pretty hefty price tag, so preparation and planning are paramount to any software installation.
A properly executed Oracle database installation and configuration lay the groundwork for an environment that is void of underlying systemic issues that can contribute to performance-related problems.
Pre-installation plans and preparation
Planning the installation of your database software or a new database is an important step in performance tuning. Poor planning can lead to problems that can’t be surmounted easily by the tools at your disposal.
For example, if you’re going to have a database that has a very high IO requirement, you need a solid storage foundation underneath the database that can handle the workload. If you don’t, no amount of SQL tuning can overcome the physical shortage of resources needed.
This is a short list of details to consider before beginning an Oracle installation.
Item Considerations
Number of users Concurrency, CPU/cores, memory
High transaction workload type CPU, memory, database parameters, recovery time
High read workload type Parallelism, SSDs, SAN, spindles, file organization
High availability Clustering, redundancy
Ad hoc querying Reporting tools, materializing data, indexing, ETL/batch jobs
The answers you gain by understanding what the application and database are going to support can help you design a system capable of handling your workload. These concepts are not new. Apply what you’ve experienced in any major undertaking to the Oracle-related task at hand.
How to select software for your Oracle 12c database
Another area that can help you put your best foot forward when preparing and planning an Oracle installation is careful selection of the software version. The best guideline is to go with the latest version available. However, here are a couple of caveats to consider:
Latest is not always greatest. Some people say you should never buy the first year of a new model of car. As with anything super-new, you can sometimes fall victim to undiscovered glitches and bugs.
With Oracle, our approach is simply be wary of using brand-new versions of Oracle without at least one patch set update (PSU). PSUs from Oracle come out every three months. Therefore, you won’t have to wait long before fixes become available.
If you have third-party packaged software, make sure you’re using an approved or certified version of Oracle. Even though Oracle 12c patchset “X” might be available, make sure that if your database is going to support another software vendor’s packaged application, it’s certified by the vendor to work with Oracle.
Unfortunately, it is common for software vendors’ certification to fall behind the latest software version of Oracle. This can be frustrating. However, more frustrating is when you’re trying to make a software package work on one version of the database when it has been tuned for another.
After you settle on a version of the Oracle software to use, make sure you have the latest maintenance packs, Patch Set Updates (PSU), or Security Patch Updates (SPU) applied. Each update can contain fixes that improve the performance of the database engine.
The tricky part: hardware and tuning Oracle 12c database
As you can probably imagine, hardware is an important part of any software installation. Everyone has different backgrounds and knowledge of computers. Your experience may simply be using your computer at home or your laptop at work. Or you may have a background in system administration.
Since selecting hardware is such an important part of configuration, be sure to evaluate your ability to make hardware recommendations. If you’re not familiar with the latest hardware trends or how to adequately size a server or system, make sure you have someone available.
There is no shame in asking for help choosing hardware. Many companies these days have employee experts tasked with helping make these decisions. It’s better to do the job right the first time by asking for help than having to go back and do it all over again.
There is no shortage of vendors willing to sell hardware to your company. In our experience, most hardware vendors also have staff experts who are well versed in helping define an Oracle system. These experts can talk to you about the features and differences in equipment they can provide.
Make sure you understand the budget, too. Most of the time, the Oracle license is calculated by the CPU socket/core. Although your hardware vendor may want to set up 64 CPUs of processing power, make sure you can run Oracle on this configuration. However, don’t let budget alone determine your hardware. This is a surefire way to progress down the path to failure.
Server and storage vendors are willing to spend your money. However, they also want you to be happy with the product you are getting. Talk to a technical expert from the vendor (or someone within your company) about the purpose of the system.
Aspects of hardware selection may gravitate from one configuration to another depending on whether you support something like a data warehouse or a system that needs to process transactions quickly. Configurations can affect the CPU, disk, memory, and network resources.
Also discuss high availability, scalability, and redundancy. To get answers to all of these questions, you may also have to coordinate with project owners and application experts. The sooner you, as a database administrator, get involved in project planning, the better off you will be in the long run.