How to Tune the Oracle 12c Database Before Something Goes Wrong
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.
|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 hard part: Hardware and tuning your Oracle 12c database
As you can probably imagine, the hardware is an important part of any software installation. Everyone has different backgrounds and familiarity with computer hardware. Your experience may be simply using your computer at home or your laptop at work. Or, you may have a background in system administration.
Because the hardware selection is such an important part of the configuration, be sure to assess your ability to make hardware recommendations. If you aren’t up to speed on the latest hardware trends or how to appropriately size a server or system, make sure you have someone available who is.
There is no shame in asking for help with hardware selection. Many companies these days have experts on staff whose job it is to help 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 again.
There is no shortage of vendors wanting to sell hardware to your company. In our experience, most hardware vendors also have experts on staff who are well-versed in helping spec out an Oracle system. These experts can talk to you about the features and differences of the gear they can provide.
Make sure that you understand the budget as well. Most often, Oracle licensing is calculated by CPU socket/core. Although a hardware vendor may want to set you up with 64 CPUs of processing power, make sure you can afford to run Oracle on that configuration. With that said, don’t let budget alone determine your hardware. That’s a sure-fire way to head down the road to failure.
Server and storage vendors want to spend your money. However, they also want you to be happy with the product you’re getting. Talk to a technical expert from the vendor (or someone within your company) about the purpose of the system.
Aspects of the hardware selection may gravitate from one configuration to another depending on whether you’re supporting something like a data warehouse or a system that needs to process transactions quickly. Configurations can affect CPU, disk, memory, and network resources.
Also discuss high availability, scalability, and redundancy. To get answers to all these questions, you also may have to coordinate with the project owners and application experts. The sooner you, as a database administrator, get included in the project planning the better off you’ll be in the long run.