How to Tune the Database in Oracle 12c - dummies

How to Tune the Database in Oracle 12c

By Chris Ruel, Michael Wessler

Tuning Structured Query Language (SQL) is just one aspect of Oracle 12c performance management. The database itself can also be configured and tuned for better performance. It is typical that SQL tuning will give you the best performance advantages out of the gate, but it isn’t always possible.

For example, you may run a packaged vendor application in which you cannot change SQL. Although you may submit performance Service Requests to the vendor in order to receive new code, what can you do in the meantime to alleviate performance overhead?

To tune the database, you can employ various methods:

  • Oracle Instance Parameters: You can adjust these parameters to influence how the instance and the optimizer are configured to handle memory, parallelism, and execution plan generation.

  • Oracle Infrastructure Features: Different editions of the database come with features that can be implemented to overcome certain types of performance problems. Some examples of these features are partitioning, materialized views, object and system statistics, and SQL profiles.

  • Infrastructure Enhancements: These sorts of changes often require interaction with other professionals in your organization to make improvements to the underlying infrastructure that runs the database (for example, server upgrades, SAN improvements, or network transmission speeds).

One of the challenges you’ll encounter when coming up with methods to tune the database is of course identifying the problems. Oracle and other software vendors provide various tools that will holistically examine the database software and configuration to make recommendations.

Now, let’s walk through some examples of bare-bones, no-cost approaches from which you can start building your skills. When you’re comfortable with the fundamentals of tuning, you can better evaluate some of the more expensive options on the market.

Sometimes, no matter how experienced a database administrator is, a tool can do the job better and faster, which translates into money saved. However, before you go requisitioning purchases, you should understand how tuning the database works.

How to install Oracle 12c’s STATSPACK:

STATSPACK is an Oracle supplied group of programs that allows you to slice up the Oracle operating metrics into sections of time or periods for focused analysis. You then look at the operational statistics and SQL captured during these periods to identify bottlenecks and wait times. After you identify these problems, you can then begin to research methods for tuning them out of the database.

For some reason, as of Oracle 12c, Oracle no longer provides STATSPACK documentation as part of the main Oracle documentation website. However, Oracle still provides text-based documentation in the software install directory. You can find the Oracle STATSPACK documentation here:

$ORACLE_HOME/rdbms/admin/spcdoc.txt

To install STATSPACK on a Linux server running Oracle, follow these steps:

  1. From the server command prompt, start SQL*Plus and log in as SYSDBA by typing

    sqlplus / as sysdba

    You see something like this:

    SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 19 17:14:30 2013
    Copyright (c) 1982, 2013, Oracle. All rights reserved.
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
  2. Run the creation utility, which requires some inputs, by typing

    SQL> @?/rdbms/admin/spcreate

    You see something like this:

    Choose the PERFSTAT user's password
    -----------------------------------
    Not specifying a password will result in the installation FAILING
    Enter value for perfstat_password:

    For testing purposes, type the following password:

    Perf$tat

    You see something like the following:

    Choose the Default tablespace for the PERFSTAT user
    ---------------------------------------------------
    Below is the list of online tablespaces in this database which can
    store user data. Specifying the SYSTEM tablespace for the user's
    default tablespace will result in the installation FAILING, as
    using SYSTEM for performance data is not supported.
    Choose the PERFSTAT users's default tablespace. This is the tablespace
    in which the STATSPACK tables and indexes will be created.
    TABLESPACE_NAME        CONTENTS STATSPACK DEFAULT TABLESPACE
    ------------------------------ --------- ----------------------------
    MY_DATA            PERMANENT
    MY_INDEX            PERMANENT
    RMAN_DATA           PERMANENT
    SYSAUX             PERMANENT *
    USERS             PERMANENT
    Pressing <return> will result in STATSPACK's recommended default
    tablespace (identified by *) being used.
    Enter value for default_tablespace:

    Oracle recommends SYSAUX as the default tablespace for STATSPACK objects. Type

    SYSAUX

    You see something like this:

    Choose the Temporary tablespace for the PERFSTAT user
    -----------------------------------------------------
    Below is the list of online tablespaces in this database which can
    store temporary data (e.g. for sort workareas). Specifying the SYSTEM
    tablespace for the user's temporary tablespace will result in the
    installation FAILING, as using SYSTEM for workareas is not supported.
    Choose the PERFSTAT user's Temporary tablespace.
    TABLESPACE_NAME        CONTENTS DB DEFAULT TEMP TABLESPACE
    ------------------------------ --------- --------------------------
    TEMP              TEMPORARY *
    Pressing <return> will result in the database's default Temporary
    tablespace (identified by *) being used.
    Enter value for temporary_tablespace:
  3. Accept the default TEMP tablespace by pressing Enter.

    You see something like this:

    ... Creating PERFSTAT user
    ... Installing required packages
    ... Creating views
    ... Granting privileges
    ...output snipped...
    Creating Package STATSPACK...
    Package created.
    No errors.
    Creating Package Body STATSPACK...
    Package body created.
    No errors.
    NOTE:
    SPCPKG complete. Please check spcpkg.lis for any errors.

STATSPACK is very similar to a tool called the Automatic Workload Repository (AWR). However, the AWR requires a license to the Diagnostic pack.