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.

• Oracle 12c Memory Settings
• Oracle 12c Amm

# Set the default memory options for the Java VM which apply to both 32 and 64-bit VM's. # These values can be overridden in the user.conf file, see the comment at the top of this file. AddVMOption -Xms2048M. Oracle 12c gives you three ways to manage the memory in your instance: Automatically by letting Oracle do all the work. Manually by tuning individual parameters for the different memory areas. Combination of automatic and manual by using your knowledge of how things operate, employing Oracle’s. Re: Memory Parameter Tuning in Oracle 12c John Thorton Dec 20, 2018 2:12 PM (in response to 817202) 817202 wrote: Hi Guru's, DB Version:12.1.2.0. OS Version:- RHEL. Kindly advice over the tunning on memory parameters. As currently don't have access to EM Express. By storing database objects in memory, Oracle Database can perform scans, queries, joins, and aggregates much faster than on disk. The In-Memory Column Store can drastically improve performance when: Scanning a large number of rows and applying filters, such as, =, and IN. About Automatic Memory Management. The simplest way to manage instance memory is to allow the Oracle Database instance to automatically manage and tune it for you. To do so (on most platforms), you set only a target memory size initialization parameter (MEMORYTARGET) and optionally a maximum memory size initialization parameter (MEMORYMAXTARGET). The instance then tunes to the target memory size, redistributing memory.

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.

Oracle 12c Memory Settings

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

   You see something like this:

2. Run the creation utility, which requires some inputs, by typing

   You see something like this:

   For testing purposes, type the following password:

   You see something like the following:

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

   You see something like this:

3. Accept the default TEMP tablespace by pressing Enter.

   You see something like this:

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

Memory management is an important component of working with instances in your database. Oracle 12c gives you three ways to manage the memory in your instance:

• Automatically by letting Oracle do all the work

• Manually by tuning individual parameters for the different memory areas

• Combination of automatic and manual by using your knowledge of how things operate, employing Oracle’s advice infrastructure, and letting Oracle take over some areas

A quick note on Oracle automation. Through the last several releases of Oracle, the database has become more automated in areas that were previously manual and even tedious at times. This isn’t to say that soon it will take no special skill to manage an Oracle database. Exactly the opposite: When more mundane operations are automated, it frees you up as the DBA to focus on the more advanced features.

We’ve had great success implementing automated features for clients. It frees up our resources to focus on things such as high availability and security, areas that require near full-time attention. Thank goodness you don’t have to spend hours watching what SQL statements are aging out of the shared pool prematurely, resulting in performance problems.

We recommend that you manage memory automatically in Oracle 12c.

When you create your database, you can set one new parameter that takes nearly all memory tuning out of your hands: MEMORY_TARGET. By setting this parameter, all the memory areas are automatically sized and managed. After you type show parameter memory_target in SQL*Plus (the SQL command-line interface available in Oracle), you see this output on the screen:

Automatic memory management lets you take hold of the amount of memory on the system and then decide how much you want to use for the database.

Oracle 12c Amm

It’s never obvious what value you should choose as a starting point. Answer these questions to help set the value:

• How much memory is available?

• How many databases will ultimately be on the machine?

• How many users will be on the machine? (If many, you allocate 4MB per user for process overhead.)

• What other applications are running on the machine?

Before the users get on the machine, consider taking no more than 40 percent of the memory for Oracle databases. Use this formula:

(GB of memory × .40) / Number of Eventual Databases = GB for MEMORY_TARGET per database

For example, if your machine had 8GB of memory and will ultimately house two databases similar in nature and only 100 users each, you would have this equation: (8 × .40) / 2 = 1.6GB for MEMORY_TARGET per database.

To help determine whether you have enough memory, Oracle gives you some pointers if you know where to look. It’s called the Memory Target Advisor. Find it from the command line in the form of the view V$MEMORY_TARGET_ADVICE. You can find it in the Database Control home page by clicking Advisor Central>Memory Advisors>Advice.

Whatever you choose for the MEMORY_TARGET setting isn’t all the memory Oracle uses. That’s why you should have an idea of how many sessions there will be before you make the final determination.

For instance, this parameter covers only memory used by the SGA and PGA. Every single session that connects to the database requires memory associated with its OS or server process. This memory requirement adds up. One of our clients has nearly 3,000 simultaneous connections eating up about 16GB of memory outside the SGA and PGA. The client’s machine has 64GB of memory, and the MEMORY_TARGET is set at 16GB.