Before you jump into creating KPIs for your Oracle database, clearly state and test your assumptions right up front. That has to come first. Otherwise, you’ll go down a blind alley many times trying to achieve better performance that will never happen because your assumptions were wrong in the first place. Be prepared to fail as often as necessary to discover which assumptions have to go. It will also help to have a more general grounding in how Oracle came about and where Oracle excels now.

The Origin of Oracle

Oracle started off as Software Development Laboratories, founded by Larry Ellison, Bob Miner and Ed Oates in 1977. They wrote Oracle Version 1 in assembly language, but that version was never released.

By 1979, they had Oracle Version 2 on the market, billed as the first commercially available SQL relational database management system (RDBMS). The founders changed the name to Relational Software Inc. (RSI), but shortly afterward it was rebranded again in honor of the company’s star performer: the Oracle database. The unofficial story is that it was RSI’s first major client, the CIA, who chose the more imaginative name Oracle.

Oracle Now

The latest release, as of the date of this article, is the Oracle Database 12c, which came out in 2013. The 12c was specially designed to be more cloud-friendly. It introduced a multitenant architecture so that a company with many databases dispersed geographically could bring them together rapidly and manage them in the cloud from a central location. Many sys admins have been pushing for in-memory data processing as a part of their analytics requirements and 12c finally gave them that capability also. There are five levels of complexity that businesses can choose from: Express, Standard One, Standard, Enterprise, and Personal.

• Express is the stripped down, entry-level database for small companies.

• Standard One is the next step up for small businesses.

• Standard is for larger organizations that require application clustering and have particular types of hardware.

• Enterprise is for high-volume online transactions, data warehouses with intense query requirements and Internet-based applications with the most strict uptime parameters.

• Personal is the same as Enterprise except that it is for single-user development with no clustering. Because it is for database developers, features like tuning and diagnostics are not available.

Other than the Express, which is a separate item, all of these editions are in the same download, so you can choose one at the time of deployment rather than purchase.

What Oracle Does Best

Databases are the backbone of most mission-critical applications today. Hundreds of thousands of businesses are using Oracle for on-premise data centers as well as cloud-based and hybrid architectures. Databases run everything from back office applications to reporting on daily business intelligence statistics to strategic analysis and forecasting for the C-suite.

Oracle has been optimized for dealing with massive data sets common to business challenges involving big data. Solutions that depend on Oracle are frequently targeted at enterprise architects searching for an IT infrastructure robust enough to handle changing business needs. Also, Oracle is often chosen by developers who need a reliable database solution for their applications. Database administrators and sysadmins often turn to Oracle when they are looking for quick provisioning and high performance. IT execs regularly cite Oracle as a flexible and well-supported database option that has a widely established reputation across industries.

While your Oracle database is designed for high-volume usage, you’ll need to monitor carefully performance levels to see if further resources are required to support the deployment. You do not want to find out that you need massive configuration changes or many extra servers with additional CPUS after your database crashes right in the middle of the business week. Proactively keep an eye on KPIs of overall database health, potential bottlenecks and indications that the system is not working up to its potential.

Any active monitoring operation requires you to stay on your toes and strive to measure things you have not thought of before. Here are 10 well-known metrics that you will need to use at some point.

1. Under-allocated RAM regions – Most of the time you can rely on the automatic memory management processes recommended by Oracle. Sometimes, you can observe significant speed improvements by increasing RAM instead of using the much slower disk access. When you do not allocate sufficient RAM for shared_pool_size, pga_aggregate_target, and db_cache_size, you database will chug along at the rate of physical I/O. Use Memory Advisor whenever possible in making manual adjustments.

2. In Memory Sort Ratio – When your database is slow, this can be an important piece of the puzzle because disk sorts must be handled in the tablespace, which is vastly slower than sorting in RAM.

3. Parse to Execute Ratio – The first time they execute, SQL must be parsed, which includes a syntax check, a semantic check, a decision tree and an execution plan to run it with maximum efficiency. Execution plans are then stored in the library cache to save time on the next execution. Parses can be hard or soft, but you want to reduce both. A hard parse should be only the initial run when SQL must parse everything. A soft parse means only the variable are parsed. For a better parse to execute ratio, increase session cache cursors from the default at 50 to find your best performance somewhere between 100 and 1000.

4. Excessive nested loop joins – Iterative loops are going to be slow. There’s no way around it. You’ll need to dig into the code to find faster solutions than a nested loop joins wherever possible. If you have no better options, 64-bit Oracle systems are meant for you because they have gigabytes for RAM sorts and hash joins. Be sure you have enough RAM to allow the CBO to choose hash joins by setting the pga_aggregate_target parameters for faster turnaround.

5. Page Cleaning Ratio – This has become a more important metric for online publishers and e-commerce pages. Page cleaners write old pages to the disk asynchronously so new pages can be read into the buffer pool. A great page cleaning ratio is around 95 percent.

6. Average Buffer Pool I/O Response Time – This is something that end users will be very interested in finding out. People can sense I/O response bottlenecks quickly and tend to complain loudly. Look for an average buffer pool read/write time in the neighborhood of 10 milliseconds.

7. Long Full Table Scans – If you are consistently seeing full table scans, something has gone terribly wrong. Online transactions and high-volume operations need to work faster. Look at your transaction design again. Search for deadly indexing and fully optimize your SQL. If your full table scan brings back under 20 percent of table rows, it is likely there are missing indexes.

8. Transaction Log Response Time – Latency in transactions can be a huge problem when payments are involved and log response times can have a big influence over latency. Look for your log response to be no more than 10 milliseconds, just like your buffer pool I/O.

9. Rows Read/Row Selected Ratio – This can save you hours of research. It will answer how many rows of the database were read before the specified rows were returned. If you are dealing with a ratio higher than 20, you may have a problem with creating indexes. From there you can go deeper and investigate cases where the number of rows read in much larger than the total executions.

10. Human Misfeasance – Yes, it is true. The biggest problem with your database may be the DBA. Misfeasance is better than malfeasance, however. It means you did not intend to do anything wrong. Misfeasance means you may have neglected to monitor their database (STATSPACK/AWR), for example. Maybe you forgot to set-up custom exception reporting alerts on the OEM performance screen. This is where you’ll find instance efficiency, wait bottlenecks, slow SQL response time or wasted space in the shared global area. A wise man once said, “Man is the measure of all things.” Don’t neglect to measure yourself.

Keys to Improving Performance

There are two areas you need to investigate before you measure for any of these KPIs.

1. Ask your users what they need.

Are processes running too slow? Do they have to be executed within a particular time window, as before the boss arrives on Monday morning? What drove people crazy in the past? What are their biggest frustrations now? The answer will guide you on which KPIs apply to you most urgently.

2. Diagnose problems.

Whenever things get buggy, the Web page will not refresh or searches just hang there, get a snapshot of all the vital info, such OS and processes running. You should also take note when everything is working great so you can make a precise comparison. Oracle’s Automatic Database Diagnostic Monitor can help you diagnose the most prevalent problems.

The Database And the Total System

Approach your performance improvements as a recursive and iterative process. Even after you make adjustments to the database, you may not see the improvements you expect because your assumptions were wrong. You’ll need to experiment to find the bottlenecks, and each one may reveal another series of bottlenecks somewhere else in the system.

Your total system performance is also affected by other system resources that interface with the database. For example, CPU cycles are often the culprit when SQL drags. Make sure you have enough CPUs for your run-queue and that there is a large amount of unused RAM. Data buffers and in-memory sorts all depend on your available RAM. The sure sign of a beginner system administrator is someone who tries to tune the database before checking out the stressors on the external environment.

When you are sure that the external environment is stable, it is time to start planning out your metrics in association with what the users need. Keep in mind as you develop your toolbox of metrics that having too many metrics will just slow you down in the end. Make sure your metrics are easy to collect, so you are not becoming part of the problem.

KPIs Are Personal

Remember that nothing is written in stone, and every business has its challenges. There are no specific numbers you are trying to hit. For example, 100 percent uptime is ideal, and 0 percent is uniformly bad, but there’s a great deal of room in the middle for establishing your individual KPI goals.

Go over your KPIs a thousand times to make sure you precisely measure what you think you are. Remember that if the metric is fluctuating drastically, there is probably something wrong in the measurement itself. Enjoy your Oracle database and never stop looking for ways to improve it.