Basic Tools to Tune Your Oracle 12c Database - dummies

Basic Tools to Tune Your Oracle 12c Database

By Chris Ruel, Michael Wessler

Knowing what tools are available and how to use them will greatly improve your tuning proficiency with Oracle 12c databases. Have you ever tried to turn a screw with a pair of pliers? Using the wrong tool or not knowing the proper tool for the job can make any task difficult or impossible. You have a variety of tools at your disposal when tuning an Oracle database.

Oracle 12c documentation

Oracle provides a vast and comprehensive set of documentation for the database. If you visit the Oracle documentation website, you see documentation for versions going back to 8.1.7. This documentation, organized into books and sections according to topic, is free to anyone on the Internet. You also see an entire book dedicated to tuning. Some sections of the documentation are dedicated to helping you use specific Oracle-provided tools.

Oracle 12c support

Oracle Support isn’t an anonymous group that meets Tuesday nights in a local church basement. Rather, it’s a website that contains a vast amount of resources. Knowing how to efficiently search through all the information available is half the battle.

The search bar allows you to input search strings just like any website. You can type descriptions of specific problems, the names of features, error messages, and so on. The list is lengthy.

Articles and documents written (and for the most part tested) by the Oracle technical staff are available. Because there are so many Oracle software users in this world, you’ll rarely encounter a problem that hasn’t already been encountered by someone else.

Oracle 12c user groups

User groups are Oracle clubs. Depending on your locality, they may be large or small. Some user groups are regional; they might be associated with a city, a town, or a particular part of the country. You can join national and international groups.

There are even virtual groups (such as forums on the web) that can be valuable resources if you’re a member. Some user groups are free, some are cheap, and some may cost hundreds of dollars to join.

Beyond hobnobbing with your Oracle buddies, often these user groups have regularly scheduled technical sessions. These sessions can range from how to use a new feature to how to solve a particular performance problem. The best example is Oracle’s annual bash, Oracle Open World. You can sit in on literally thousands of sessions, many delivered by the foremost experts in the industry.

If you need to pay to become a member of a particular user group, ask your supervisor at work whether the cost is covered under training expenses.

Oracle 12c training classes

Although not always cheap, training classes can be some of the most valuable methods to gain proficiency in database tuning.

Training classes can be free if provided by your local user group. However, they may not be as comprehensive and hands-on as attending a full, multi-day course. Sometimes these can seem expensive up front.

The average cost is probably in the $2,000–$4,000 range, and you may need to cover travel expenses as well if the class is not local. However, dollar for dollar, getting your hands dirty with actual classroom exercises and labs is often the most bang for your buck when you’re a new database administrator.

Oracle 12c licensed tools

Oracle offers a wide variety of licensable tools that you can use to tune your database. Of the available tuning tools, foremost are the Diagnostic and Tuning packs for Oracle Enterprise Manager. These packs contain all kinds of devices and advisories for tuning your database.

Most of the licensed tools are built around data that is readily available in the database, and a very experienced database administrator can make quick use of the metadata to gather the information for tuning a particular problem.

However, that isn’t necessarily the quickest approach to solving every pending issue. Although Oracle internally provides most, if not all, of the information to help you fix a performance problem, compiling and analyzing the information can take a considerable amount of time — even for the most seasoned database administrators.

Here are some of the tools included with the Oracle Diagnostic and Tuning packs for Oracle 12c Enterprise Manager Cloud Control.

Name Pack Description
Automatic Database Diagnostics Monitor (ADDM) Diagnostic This tool provides focused analysis of activities the database
is spending most time on to determine the root causes of
Automatic Workload Repository (AWR) Diagnostic At regular intervals, the database takes a snapshot of its
workload information and stores it for analysis of past
Active Session History (ASH) Diagnostic Similar to AWR, this tool stores information specific to
sessions, such as past SQL executed and performance metric
Monitoring and Alerting Diagnostic You get a comprehensive set of monitoring and notification
Real Time Monitoring Tuning This tool enables graphical analysis of real-time performance
database activity within Enterprise Manager. (A picture really is
worth a thousand words.)
SQL Tuning Advisor Tuning This advisor automates the SQL tuning process by
comprehensively exploring all the possible ways of tuning SQL
statements. This tool also provides action plans to remediate
SQL Access Advisor Tuning This advisor recommends design and parameter changes to
emphasize more throughput and better performance.

Although they come with the database, you are not allowed to use the tools without a license. If you do, you run the risk of being in violation of your license agreement with Oracle.

You can talk to your Oracle Sales rep about getting licensed to use these tools. Licensed tools allow you to reduce the time it takes to find and solve problems. They are pieces of software, just like the database, therefore, Oracle charges you for them.