5 Managing the Oracle Instance
This chapter provides background information about the Oracle instance and instructions for managing the instance.
This chapter contains the following sections:
5.1 Overview of the Oracle Instance and Instance Management
An Oracle database system consists of an Oracle database and an Oracle instance (in an Oracle Real Application Clusters environment, there can be more than one instance).
A database consists of a set of disk files that store user data and metadata. Metadata, or "data about the data," consists of structural, configuration, and control information about the database.
An Oracle instance (also known as a database instance) contains the set of Oracle Database background processes that operate on the stored data and the shared allocated memory that those processes use to do their work.
Each instance has an instance ID, also known as a system ID (SID). Because there can be multiple Oracle databases on a host computer, each with its own set of data files, you must identify the instance to which you want to connect. For a local connection, you identify the instance by setting operating system environment variables ORACLE_SID
and ORACLE_HOME
. For a remote connection, you identify the instance by specifying a network address and a database service name.
An Oracle instance must be started to read and write information to the database. The Oracle instance creates the database upon receipt of instructions from the Oracle Database Configuration Assistant (DBCA) utility or the CREATE
DATABASE
SQL statement.
When the Oracle instance is not available, your data is safe in the database, but it cannot be accessed by any user or application.
The properties of an Oracle instance are specified using instance initialization parameters. When the instance is started, an initialization parameter file is read, and the instance is configured accordingly.
This section presents some concepts of an Oracle instance and its management. It contains the following topics:
See Also:
Oracle Database Concepts for an overview of the Oracle database instance
5.1.1 About Initialization Parameters
During installation, when you select a preconfigured database workload available in Database Configuration Assistant (DBCA), the initialization parameters are optimized for typical use in the environment that you specified. As the number of database users increases and the workload increases, you might have to alter some initialization parameters. You can make these changes using the Initialization Parameter page in Oracle Enterprise Manager Database Express (EM Express), or by using an advisor provided by Oracle Database, such as the Memory Advisor. See "Optimizing Memory Usage with the Memory Advisors" for more information.
After being read from a file, initialization parameters are retained in memory, where the values for many of them can be changed dynamically. There are two types of parameter files. The type of file used to start the instance determines if dynamic initialization parameter changes persist across database shutdown and startup. The parameter file types are:
-
Server parameter file
The server parameter file, commonly known as the SPFILE, is the preferred form of initialization parameter file, and is a binary file that can be written to and read by the database. It must not be edited manually. It is stored on the host computer on which Oracle Database is running. Changes are made when you use EM Express or SQL*Plus to modify one or more initialization parameters, or when Oracle Database itself makes changes for self-tuning purposes. Any changes to it persist across database shutdown and startup operations.
Note:
When changing an initialization parameter in the server parameter file, you can also specify that the in-memory value be changed, so that your change is reflected immediately in the current instance. If you do not change the in-memory value, then the change does not take effect until you shut down and restart the database.
-
Text initialization parameter file
A text initialization parameter file is a text file that can be read by the Oracle instance, but it is not written to by the instance. You can change a text initialization parameter file with a text editor, but changes do not take effect until you restart the Oracle instance. When you start the instance with this type of file, you can still change many initialization parameters dynamically with EM Express, but only for the current instance. Unless you also edit the text initialization parameter file and make the same change, the change is lost when you restart the database instance.
You can use SQL statements to create the following:
-
A server parameter file from a text initialization file
-
A server parameter file from the current (in-memory) values of all initialization parameters
-
A text initialization parameter file from a server parameter file
When you create the database with DBCA, a server parameter file is created. This file is then used each time the database is started.
See Also:
-
Oracle Database Administrator’s Guide for information about the default name and location of the server parameter file, and for commands to create a server parameter file or text initialization parameter file
-
Oracle Database Concepts for an overview of parameter files
5.1.2 About Background Processes
The background processes that are present depend on the features that are being used in the database. Some fundamental background processes are described in Table 5-1.
Table 5-1 Oracle Database Background Processes
Background Process | Description |
---|---|
The database writer writes modified blocks from the database buffer cache to the files on a disk. Oracle Database allows a maximum of 100 database writer processes. |
|
The log writer process writes redo log entries to disk. Redo log entries are generated in the redo log buffer of the System Global Area (SGA) and the log writer process writes the redo log entries sequentially into an online redo log file. |
|
At specific times, all modified database buffers in the SGA are written to the data files by a database writer process (DBWn). This event is called a checkpoint. The checkpoint process signals DBWn, updates the data files and control files of the database, and records the time of this update. |
|
The system monitor performs instance recovery when a failed instance is restarted. |
|
The process monitor performs a recovery when a user process fails. It cleans up the cache and frees resources that the failed process was using. |
|
Archiver processes copy the online redo log files to archival storage when the log files are full or a log switch occurs. The database must be in archive log mode to run archive processes. For more information, see "Performing Backup and Recovery". |
|
This process performs various management-related background tasks, for example:
|
|
Job Queue Processes (CJQ0 and Jnnn) |
Job queue processes run user jobs, often in batch mode. A job is a user-defined task scheduled to run one or more times. |
See Also:
-
Oracle Database Reference for a complete list of Oracle Database background processes
-
Oracle Database Concepts for an overview of background processes
5.1.3 About Server and Client Processes
-
A client program acting on behalf of the user, such as Oracle Enterprise Manager (Enterprise Manager), SQL*Plus, or an application
-
A server process that handles the connection to the database on behalf of the client program, and that performs much of the work for the client program, such as parsing and running SQL statements, and retrieving and returning results to the client program
Server processes can be either dedicated or shared. When server processes are dedicated, Oracle Database is running in dedicated server mode. When server processes are shared, Oracle Database is running in shared server mode. In dedicated server mode, each client process has its own server process. Although a dedicated server process is good for long-running queries and administrative tasks, an idle process or too many dedicated processes can result in an inefficient use of resources.
Using shared server mode eliminates the need for a dedicated server process for each user connection, requires less memory for each user connection, and enables more users to access the database. Shared server mode is more efficient at supporting multiple client programs making frequent short-running queries.
5.1.4 About Instance Memory Structures
The sizes of the instance memory structures affect database performance and are controlled by initialization parameters.
Upon installation, you can let the database manage memory for you automatically, or you can choose to manually configure the instance memory structures. If you choose manual memory management, then Oracle Database provides advisors to help you determine appropriate values for memory parameters.
The following sections describe the two important memory areas that you can monitor and size:
See Also:
-
Oracle Database Concepts and Oracle Database Administrator’s Guide for more information about Oracle Database memory architecture
5.1.4.1 System Global Area
Table 5-2 lists the components of the SGA.
Table 5-2 SGA Components
Component | Description |
---|---|
Database buffer cache |
Before data stored in the database can be queried or modified, it must be read from a disk and stored in the buffer cache. All user processes connected to the database share access to the buffer cache. For optimal performance, the buffer cache should be large enough to avoid frequent disk I/O operations. |
Shared pool |
The shared pool caches information that is shared among users:
|
Redo log buffer |
This buffer improves performance by caching redo information until it can be written to the physical online redo log files stored on disk. Redo information and online redo log files are discussed in "About Online Redo Log Files". |
Large pool |
This optional area is used to buffer large I/O requests for various server processes. |
In-Memory Area |
This optional component contains the In-Memory Column Store (IM column store). The IM column store contains copies of tables, partitions, and materialized views in a columnar format optimized for rapid scans. The IM column store supplements the database buffer cache, which stores data in traditional row format. |
Memoptimize Pool |
This optional component contains the buffers for use with the MEMOPTIMIZE FOR READ feature for fast lookup. |
Java pool |
The Java pool is an area of memory that is used for all session-specific Java code and data within the Java Virtual Machine (JVM). |
Streams pool |
The Streams pool is an area of memory that is used by the Oracle Replication feature. |
Result cache |
The result cache buffers query results. If a query is run for which the results are stored in the result cache, then the database returns the query results from the result cache instead of rerunning the query. This SGA component speeds the execution of frequently run queries. |
See Also:
-
Oracle Database Concepts for more information about the SGA
-
Oracle Database In-Memory Guide for more information about the In-Memory Area
-
Oracle Database Performance Tuning Guide for more information about the Memoptimize Pool and the MEMOPTIMIZE FOR READ feature for fast lookup
5.1.4.2 Program Global Area
The amount of PGA memory used and the contents of the PGA depend on whether the instance is running in dedicated server or shared server mode.
The PGA is used to process SQL statements and to hold logon and other session information. A large part of the PGA is dedicated to SQL work areas, which are working memory areas for sorts and other SQL operations.
See Also:
-
"About Server and Client Processes" for more information about dedicated server and shared server modes
-
Oracle Database Concepts for more information about the PGA
5.1.5 About Instance Startup and Shutdown
The phrases "starting up and shutting down the Oracle instance" are often used interchangeably with "starting up and shutting down the database."
This section contains the following topics:
5.1.5.1 About Administration Privileges for Startup and Shutdown
SYSDBA
for fully empowered database administrators and SYSOPER
for users who start and shut down the database, but have no privileges to access user objects.
When you create an Oracle database, there are two primary administrative user accounts that are automatically created: SYS
and SYSTEM
. Both of these users have full database administration privileges, but initially, only user SYS
or SYSTEM
can connect with the SYSOPER
privilege. Therefore, until you grant the SYSOPER
privilege to other users, you must connect to the Oracle instance as user SYS
or SYSTEM
to start and shut down the instance. When connecting (logging in) as user SYS
, you must always specify that you are connecting AS
SYSDBA
.
See Also:
-
"About Administrative Accounts and Privileges" for more information about the
SYS
andSYSTEM
users, and theSYSDBA
privilege -
"SYS and SYSTEM Users" for information about the recommended alternative to using the
SYSTEM
account for day-to-day administrative tasks
5.1.5.2 About Instance Startup
OPEN
and ready for user connections. However, there are situations in which you may want to start the instance with the database in the MOUNTED
state, but not open. An instance can also be started without the database either mounted or open. Thus, there are three stages to starting an instance:
-
You start the instance using one of the following methods:
-
Using the SQL*Plus
STARTUP
command. See "Shutting Down and Starting Up Using SQL*Plus". -
On Microsoft Windows, using the Services program in Control Panel to start the Oracle Database services. See "Shutting Down and Starting Up Using the Windows Services Program".
The instance reads the initialization parameter file, allocates System Global Area (SGA) memory, and starts the background processes.
-
-
If you mount the database, then the Oracle instance opens the control file for the database, but does not open the data files. The database is now considered to be in the
MOUNT
state. This state enables you to perform certain administrative functions that cannot be performed when other users are accessing the database. An example of such a function is enabling or disabling the archiving of online redo log files. See "About Archived Redo Log Files" for information about online redo log file archiving. -
If you open the database, then, after reading the parameter file and control file, the online redo log files and data files for the database are also opened. The state of the database is now
OPEN
and user access to the data is available.
The default startup mode for the database (OPEN) completes the three stages in sequence. Unless you explicitly specify otherwise, the instance is started, the database is mounted, and then the database is opened.
5.1.5.3 About Instance Shutdown
NORMAL
shutdown, which means users are not allowed to create new connections to the database, but the shutdown process waits for all currently connected users to exit their sessions. After all the users have disconnected, then the committed transactions are written to disk, the database files are closed, and the instance is stopped. However, there are situations in which you may not want to wait for users to disconnect on their own (IMMEDIATE
mode), or you want to let the current transactions for each user complete before they are disconnected (TRANSACTIONAL
mode). In emergency situations you can even shut down the database without waiting for the committed transactions to be written to disk (ABORT
mode).
Shutting down an instance goes through the following stages:
-
After all the users have exited from their sessions, or been disconnected, Oracle Database writes data in the System Global Area (SGA) to the data files and online redo log files. A checkpoint is performed on the data files and their headers are marked current as of the time of the instance shutdown. The data files and online redo log files are then closed and the state of the database is changed to
CLOSED
. The control file remains open to the instance. -
The Oracle instance dismounts the database and updates relevant entries in the control file to record a clean shutdown. The control file is closed. The database is now closed and dismounted. The instance is in the
NOMOUNT
state. -
The Oracle instance stops the background processes and deallocates the shared memory used by the SGA.
If a SHUTDOWN ABORT
or abnormal termination occurs, then the instance of an open database closes and shuts down the database instantaneously. Oracle Database does not write data in the buffers of the SGA to the data files and redo log files. The subsequent reopening of the database requires instance recovery, which Oracle Database performs automatically.
5.2 Shutting Down and Starting Up the Oracle Instance
This section provides instructions about two methods you can use to start or shut down the Oracle instance.
See Also:
5.2.1 Shutting Down and Starting Up Using SQL*Plus
You can shut down and start the Oracle instance using SQL*Plus.
To shut down and start the Oracle instance using SQL*Plus:
5.2.2 Shutting Down and Starting Up Using the Windows Services Program
-
OracleService
SID
, which is your Oracle instance. -
Oracle
ORACLE_HOME
TNSListener
, which is your listener. The listener is required for clients to connect to your database.
In the preceding service names, SID
refers to the system identifier for the instance and ORACLE_HOME
refers to the Oracle home name.
To start or stop Oracle Database services:
-
Click Start and then select Control Panel.
The Control Panel window opens.
-
Double-click the Administrative Tools icon, and then double-click the Services icon.
The Services window opens, displaying all Windows services that are available on your system.
-
Locate the Oracle Database services listed at the beginning of this section. For example, if your SID is
orcl
and Oracle home name isOraDb11g_home1
, then locate the following services:-
OracleServiceORCL
-
OracleOraDb11g_home1TNSListener
-
-
Start or stop the services, using the following steps for each service:
-
Select the service name.
-
In the Action menu, click Start or Stop.
-
5.3 Viewing and Modifying Initialization Parameters
-
Until the instance is shut down: The new values for the initialization parameters are applied to the currently running instance, but, when the database is restarted, the initialization parameter values revert to their previous settings.
-
From now until the initialization parameter is changed again: The changes are applied to the currently running instance and are also stored in the server parameter file. The changes made to the initialization parameters persist when the database is restarted.
-
When the database is restarted: The new values for the initialization parameters are recorded in server parameter file, but are not applied to the currently running instance. The changes take effect only when the database is restarted.
Note:
These three scenarios correspond to using the SCOPE=MEMORY
, SCOPE=BOTH
, and SCOPE=SPFILE
clauses of the ALTER
SYSTEM
SQL statement, respectively, when you use the ALTER
SYSTEM
statement to change initialization parameters.
To view or modify initialization parameters:
-
In Oracle Enterprise Manager Database Express (EM Express), from the Configuration menu, select Initialization Parameters.
The Initialization Parameters page appears.
The Initialization Parameters page has two tabs:
-
Current—This tab (the default) displays all initialization parameter values that are currently active (in memory) for the Oracle instance.
-
SPFile—This tab displays initialization parameter settings in the server parameter file (SPFILE). This tab is present only when the current instance started up with a server parameter file. The file location is displayed at the top of the tab.
Note:
In a pluggable database (PDB), the Initialization Parameters page includes the PDB Modifiable column. Each initialization parameter that can be modified at the PDB level has a check mark in the PDB Modifiable column.
Any initialization parameter in a PDB that does not have a check mark in the PDB Modifiable column can be set and modified only in the root, and the value set in the root applies to the individual PDBs in the multitenant container database (CDB).
For more information on individual initialization parameters, see Oracle Database Reference.
-
-
(Optional) On either tab, reduce the number of initialization parameters displayed by doing one or both of the following:
-
In the search field, enter text.
-
Select either the Modified or Basic option next to the search field to limit the display to either modified or basic initialization parameters.
For example, to view only initialization parameters that have the text
DEST
anywhere in the parameter name, enterdest
in the Search field. EM Express then restricts the list of initialization parameters accordingly. -
-
To modify an initialization parameter for the currently running instance only (the modifications will not persist when the instance is restarted), complete the following steps:
-
On the Current tab, select the initialization parameter whose value you want to modify.
Note:
If the Set button does not become available when you select the parameter, then the parameter is not dynamic—that is, it cannot be changed for the current instance.
-
Click the Set button.
The Set Initialization Parameter page appears.
-
In the Value column, enter a new value for the initialization parameter.
-
For the Scope field, ensure that Memory is selected. The value you set will not persist when the instance is restarted.
-
(Optional) In the Comments column, enter text explaining the reasons for the changes.
-
Click OK.
A confirmation message appears.
-
-
To modify an initialization parameter for the currently running instance, and also record the modifications in the server parameter file that will persist when the database instance is restarted, complete the following steps:
-
On the Current tab, select the initialization parameter whose value you want to modify.
Note:
If the Set button does not become available when you select the parameter, then the parameter is not dynamic—that is, it cannot be changed for the current instance.
-
Click the Set button.
The Set Initialization Parameter page appears.
-
For the Scope field, ensure that both Memory and SPFile are selected. The value you set will persist when the database instance is restarted.
Note:
If the SPFile option is not available, then the database instance does not have an SPFILE, and changes made to the instance will not persist when the instance is restarted.
-
(Optional) In the Comments column, enter text explaining the reasons for the changes.
-
Click OK.
A confirmation message appears. The message includes a Show SQL button. Click the Show SQL button to see the SQL statement that was executed.
-
-
To modify an initialization parameter in the server parameter file only, such that the current instance is not affected and changes take effect only when the database is next restarted, complete the following steps:
-
Click SPFile to view the SPFile tab.
-
Select the initialization parameter whose value you want to modify. If the initialization parameter does not appear on the SPFile tab, then select the initialization parameter on the Current tab, instead.
-
Click the Set button.
The Set Initialization Parameter page appears.
-
In the Scope field, choose SPFile.
For an initialization parameter that cannot be reset without restarting the database, the Scope field defaults to SPFile, and the Memory option does not appear.
-
In the Value column, enter a new value for the initialization parameter.
-
(Optional) In the Comments column, enter text explaining the reasons for the changes.
-
Click Apply.
A confirmation message appears.
-
Note:
Changes to initialization parameters are recorded in the alert log as ALTER
SYSTEM
statements. See Oracle Database Administrator’s Guide for information about the alert log.
See Also:
5.4 Managing Memory
See Also:
5.4.1 About Memory Management
Beginning with Oracle Database 11g Release 1 (11.1), you can let the database manage the SGA memory and instance PGA memory completely. You designate only the total memory size to be used by the instance, and Oracle Database dynamically exchanges memory between the SGA and the instance PGA as needed to meet processing demands. This capability is referred to as automatic memory management. In this memory management mode, the database also dynamically tunes the sizes of the individual SGA components and the instance PGA.
To have more direct control over the sizes of the SGA and instance PGA, you can disable automatic memory management and enable automatic shared memory management.
-
With automatic shared memory management, you set target and maximum sizes for the SGA. Oracle Database then tunes the total size of the SGA to your designated target, and dynamically tunes the sizes of all SGA components.
-
When you enable automatic shared memory management, you can also enable automatic PGA memory management. With automatic PGA memory management, Oracle Database automatically performs memory management of instance PGA. Optionally, you can set a target size for the instance PGA, and the database then tunes the size of the instance PGA to your target, and dynamically tunes the sizes of individual PGAs.
If you want complete control of individual SGA component sizes, you can disable both automatic memory management and automatic shared memory management. This is called manual shared memory management. In this mode, you set the sizes of several individual SGA components, thereby determining the overall SGA size. You then manually tune these individual SGA components on an ongoing basis.
Manual shared memory management mode is intended for experienced DBAs only. Note that in this mode, automatic PGA memory management remains enabled.
Note:
Although it is possible to disable automatic PGA memory management, it is not recommended, and is not described in this manual.
Table 5-3 summarizes the various memory management modes that you can set for your database instance.
Table 5-3 Oracle Database Memory Management Modes
Memory Management Mode | You Set | Oracle Database Automatically Tunes |
---|---|---|
Automatic memory management |
|
|
Automatic shared memory management and automatic PGA memory management (Automatic memory management disabled) |
|
|
Manual shared memory management and automatic PGA memory management (Automatic memory management and automatic shared memory management disabled) |
|
|
Note:
Automatic Memory Management is not available on all platforms. See Oracle Database Administrator’s Guide for more information about supported platforms.
If you choose the basic installation option when you install the database, then automatic memory management is enabled. If you choose advanced installation, then Database Configuration Assistant (DBCA) enables you to select from the three memory management modes. Oracle recommends that you enable automatic memory management.
Whichever memory management mode you choose, you may have occasion to adjust memory settings as demands on the database or on its host computer change. Reasons why you adjust memory settings include the following:
-
You receive a memory-related alert or error message.
-
You receive a memory-related recommendation from Automatic Database Diagnostic Monitor (ADDM).
-
You want to change the amount of memory allocated to accommodate future growth in memory demand.
You can use a memory advisor to help you adjust memory sizes. See "Modifying Memory Settings – Automatic Shared Memory Management" for an example of using a memory advisor.
Note:
The initialization parameters that are used to manage memory are set in the root of a multitenant container database (CDB), and the values set for those parameters are applied to all of the pluggable databases (PDBs) in the CDB.
5.4.2 Enabling Automatic Memory Management
To enable automatic memory management:
-
Start SQL*Plus and connect to the database as
SYSDBA
. -
Calculate the minimum value for
MEMORY_TARGET
as follows:-
Determine the current sizes of
SGA_TARGET
andPGA_AGGREGATE_TARGET
by entering the following SQL*Plus command:SHOW PARAMETER TARGET
SQL*Plus displays the values of all initialization parameters with the string TARGET in the parameter name.
NAME TYPE VALUE ------------------------------ ----------- ---------------- archive_lag_target integer 0 db_flashback_retention_target integer 1440 fast_start_io_target integer 0 fast_start_mttr_target integer 0 memory_max_target big integer 0 memory_target big integer 0 parallel_servers_target integer 32 pga_aggregate_target big integer 29M sga_target big integer 356M
Or, on the Initialization Parameters page in Oracle Enterprise Manager Database Express (EM Express), you can enter "TARGET" in the Search field to display the values of all the initialization parameters with the string TARGET in the parameter name, as described in "Viewing and Modifying Initialization Parameters."
-
Run the following query to determine the maximum instance Program Global Area (PGA) allocated since the database was started:
SQL> select value from v$pgastat where name='maximum PGA allocated'; VALUE ---------- 246844416
246844416 bytes is approximately 235M.
-
Compute the maximum value between the query result from step 2.b and
PGA_AGGREGATE_TARGET
. AddSGA_TARGET
to this value.memory_target = sga_target + max(pga_aggregate_target, maximum PGA allocated)
For example, if
SGA_TARGET
is 356M andPGA_AGGREGATE_TARGET
is 29M as shown above, and if the maximum PGA allocated is determined to be 235M, thenMEMORY_TARGET
should be at least 591M (356M + 235M).
-
-
Choose the value for
MEMORY_TARGET
that you want to use.This can be the minimum value that you computed in step 2, or you can choose to use a larger value if you have enough physical memory available.
-
For the
MEMORY_MAX_TARGET
initialization parameter, decide on a maximum amount of memory that you would want to allocate to the database for the foreseeable future. That is, determine the maximum value for the sum of the System Global Area (SGA) and instance PGA sizes. This number can be larger than or the same as theMEMORY_TARGET
value that you chose in the previous step. -
Do one of the following:
-
If you started your Oracle Database instance with a server parameter file, which is the default if you created the database with the Database Configuration Assistant (DBCA), enter the following SQL*Plus command:
ALTER SYSTEM SET MEMORY_MAX_TARGET =
n
M SCOPE = SPFILE;where n is the value that you computed in step 4.
The
SCOPE = SPFILE
clause sets the value only in the server parameter file, and not for the running instance. You must include thisSCOPE
clause becauseMEMORY_MAX_TARGET
is not a dynamic initialization parameter.Or, you can also select the
MEMORY_MAX_TARGET
initialization parameter on the Initialization Parameters page in EM Express, click Set, specify a Scope of SPFile, and set a new value, as described in "Viewing and Modifying Initialization Parameters." -
If you started your instance with a text initialization parameter file, manually edit the file so that it contains the following statements:
memory_max_target =
n
M (650M for this example) memory_target =m
M (591M for this example)where n is the value that you determined in step 4, and m is the value that you determined in step 3.
Note:
In a text initialization parameter file, if you omit the line for
MEMORY_MAX_TARGET
and include a value forMEMORY_TARGET
, the database automatically setsMEMORY_MAX_TARGET
to the value ofMEMORY_TARGET
. If you omit the line forMEMORY_TARGET
and include a value forMEMORY_MAX_TARGET
, theMEMORY_TARGET
parameter defaults to zero. After startup, you can then dynamically changeMEMORY_TARGET
to a nonzero value, provided that it does not exceed the value ofMEMORY_MAX_TARGET
.
-
-
Shut down and restart the database.
-
In EM Express, from the Configuration menu, select Memory.
The Memory Management page appears. In the Memory Settings section, the Memory Management value is Auto. This indicates that Automatic Memory Management is enabled for the database. The initialization parameter values shown on this page are the ones that have been specified in addition to
MEMORY_MAX_TARGET
. -
If you started your Oracle Database instance with a server parameter file, make these changes to the following initialization parameter values:
MEMORY_TARGET =
n
M; (591M for this example) SGA_TARGET = 0; PGA_AGGREGATE_TARGET = 0;You can also set these initialization parameter values using the Initialization Parameters page in EM Express, specifying a scope of SPFile. See "Viewing and Modifying Initialization Parameters"for more information.
Note:
The preceding steps instruct you to set
SGA_TARGET
andPGA_AGGREGATE_TARGET
to zero so that the sizes of the SGA and instance PGA are tuned up and down as required, without restrictions. You can omit the statements that set these parameter values to zero and leave either or both of the values as positive numbers. In this case, the values act as minimum values for the sizes of the SGA or instance PGA.
See Also:
5.4.3 Modifying Memory Settings – Automatic Memory Management
To predict the percentage of time saved for different target memory sizes:
-
In EM Express, from the Configuration menu, select Memory.
The Memory Management page appears. In the Memory Settings section, the Memory Management value is Auto. This indicates that Automatic Memory Management is enabled for the database.
-
Use the Memory Advisor graph (which appears to the right of the Memory Settings section) to predict the percentage of time saved for potential target memory sizes.
In the Memory Advisor graph:
-
Potential values for the
MEMORY_TARGET
initialization parameter (in MB) are represented on the horizontal axis of the graph. The current setting of theMEMORY_TARGET
initialization parameter is indicated by a blue dot. -
The corresponding values of time saved are represented on the vertical axis of the graph. The plotted values are expressed as a percentage relative to the current setting of the
MEMORY_TARGET
initialization parameter.Negative values represent the percentage of an increase in time consumed (when the memory allotted to Oracle is smaller than the current setting), while positive values represent the percentage of decrease in time consumed (when the memory alloted to Oracle is larger than the current setting.
An orange line on the graph plots different values that can be specified for the
MEMORY_TARGET
initialization parameter. Click any dot on the orange line to see a prediction of the decrease in time consumed for theMEMORY_TARGET
value represented by that dot.In this figure, the Memory Advisor graph indicates that increasing the current value of the
MEMORY_TARGET
initialization parameter will not decrease the percentage of time saved. -
-
To change the value of the
MEMORY_TARGET
initialization parameter:-
Click Configure Memory on the Memory Management page.
The Initialization Parameter page appears.
-
Select the
MEMORY_TARGET
initialization parameter and click Set.The Set Initialization Parameter page appears.
-
In the Scope field, enter the scope for this change.
See "Viewing and Modifying Initialization Parameters" for more information about specifying a scope of Memory, or SPFile, or both.
-
In the Value field, enter the new value for the
MEMORY_TARGET
initialization parameter. -
Click OK.
A confirmation message appears.
-
See Also:
-
Oracle Database Performance Tuning Guide for more information about memory management methods
5.4.4 Enabling Automatic Shared Memory Management
This section describes how to change to automatic shared memory management if either automatic memory management or manual shared memory management is currently enabled for your database instance.
To change to automatic shared memory management if automatic memory management is currently enabled:
If automatic memory management is currently enabled, but you would like to have more direct control over the sizes of the System Global Area (SGA) and instance Program Global Area (PGA), you can disable automatic memory management and enable automatic shared memory management. Follow these steps:
-
In Oracle Enterprise Manager Database Express (EM Express), from the Configuration menu, select Initialization Parameters.
The Initialization Parameters page appears, with the Current tab displayed.
-
In the Search field, enter
MEMORY_TARGET
. -
Select
MEMORY_TARGET
, and then click Set.The Set Initialization Parameter page appears.
-
In the Value field, enter 0, specify a Scope of Memory, and then click OK.
A confirmation message appears.
Note:
This step changes automatic memory management to automatic shared memory management for the current session. To change automatic memory management to automatic shared memory management and keep it in effect after the database is restarted:
-
If your database uses a server parameter file, specify a Scope of SPFile as well as a Scope of Memory on the Set Initialization Parameter page.
-
If your database uses a text initialization parameter file, manually set the value of
MEMORY_TARGET
to 0 in that file.
-
-
From the Configuration menu, select Memory.
Note that in the SGA Memory subsection of the Memory Settings section, the Management Mode value is now Auto. This indicates that automatic shared memory management is enabled. The initialization parameter values shown on this page are the ones that have been specified in addition to
MEMORY_TARGET
.
To change to automatic shared memory management if manual shared memory management is currently enabled:
If manual shared memory management is currently enabled, but you would like Oracle Database to help you determine optimal sizes of the SGA and instance PGA, you can disable manual shared memory management and enable automatic shared memory management. Follow these steps:
5.4.5 Modifying Memory Settings – Automatic Shared Memory Management
Before you modify memory settings for automatic shared memory management, use the SGA Advisor graph in Oracle Enterprise Manager Database Express (EM Express) to predict the percentage of time saved by using a different total System Global Area (SGA) size. This section assumes that automatic memory management is disabled, and that automatic shared memory management is enabled.
To predict the percentage of time saved for different SGA memory sizes:
-
In EM Express, from the Configuration menu, select Memory.
The Memory Management page appears. In the Memory Settings section, the Management Mode value is Auto in the SGA Memory subsection.This indicates that Automatic Shared Memory Management is enabled for the database.
-
Use the SGA Advisor graph (which appears to the right of the Memory Settings section) to predict the percentage of time saved for potential SGA memory sizes.
In the SGA Advisor graph:
-
Potential values for the
SGA_TARGET
initialization parameter (in MB) are represented on the horizontal axis of the graph. The current setting of theSGA_TARGET
initialization parameter is indicated by a blue dot. -
The corresponding values of time saved are represented on the vertical axis of the graph. The plotted values are expressed as a percentage relative to the current setting of the
SGA_TARGET
initialization parameter.Negative values represent the percentage of an increase in time consumed (when the memory allotted to Oracle is smaller than the current setting), while positive values represent the percentage of decrease in time consumed (when the memory alloted to Oracle is larger than the current setting.
An orange line on the graph plots different values that can be specified for the
SGA_TARGET
initialization parameter. Click any dot on the orange line to see a prediction of the decrease in time consumed for theSGA_TARGET
value represented by that dot.In this figure, the SGA Advisor graph indicates that increasing the current value of the
SGA_TARGET
initialization parameter will not decrease the percentage of time saved. -
-
To change the value of the
SGA_TARGET
initialization parameter:-
Click Configure Memory on the Memory Management page.
The Initialization Parameter page appears.
-
Select the
SGA_TARGET
initialization parameter and click Set.The Set Initialization Parameter page appears.
-
In the Scope field, enter the scope for this change.
See "Viewing and Modifying Initialization Parameters" for more information about setting a scope of Memory, or SPFile, or both.
-
In the Value field, enter the new value for the
SGA_TARGET
initialization parameter. -
Click OK.
A confirmation message appears.
-
See Also:
-
Oracle Database Performance Tuning Guide for more information about memory management modes
5.4.6 Enabling Manual Shared Memory Management
This section describes how to enabled manual shared memory management.
Follow these steps to enable manual shared memory management:
5.4.7 Modifying Memory Settings - Manual Shared Memory Management
Before you modify memory settings for manual shared memory management, use the Buffer Cache Advisor graph in Oracle Enterprise Manager Database Express (EM Express) to predict the estimated percentage of reads saved by using a different database cache size. This section assumes that automatic memory management is disabled, and that manual shared memory management is enabled.
To predict the percentage of reads saved for different database cache sizes:
-
In EM Express, from the Configuration menu, select Memory.
The Memory Management page appears. In the Memory Settings section, the Management Mode value is Manual in the SGA Memory subsection.This indicates that Manual Shared Memory Management is enabled for the database.
-
Use the Buffer Cache Advisor graph (which appears to the right of the Memory Settings section) to predict the percentage of reads saved for potential database cache sizes.
In the Buffer Cache Advisor graph:
-
Potential values for the
DB_CACHE_SIZE
initialization parameter (in MB) are represented on the horizontal axis of the graph. The current setting of theDB_CACHE_SIZE
initialization parameter is indicated by a blue dot. -
The corresponding values of reads saved are represented on the vertical axis of the graph. The plotted values are expressed as a percentage relative to the current setting of the
DB_CACHE_SIZE
initialization parameter.Negative values represent the percentage of an increase in reads (when the memory allotted to Oracle is smaller than the current setting), while positive values represent the percentage of decrease in reads (when the memory alloted to Oracle is larger than the current setting.
An orange line on the graph plots different values that can be specified for the
DB_CACHE_SIZE
initialization parameter. Click any dot on the orange line to see a prediction of the percentage of reads saved for theDB_CACHE_SIZE
value represented by that dot.In this figure, the Buffer Cache Advisor graph indicates that increasing the current value of the
DB_CACHE_SIZE
initialization parameter will not increase the percentage of reads saved. -
-
To change the value of the
DB_CACHE_SIZE
initialization parameter:-
Click Configure Memory on the Memory Management page.
The Initialization Parameter page appears.
-
Select the
DB_CACHE_SIZE
initialization parameter and click Set.The Set Initialization Parameter page appears.
-
In the Scope field, enter the scope for this change.
See "Viewing and Modifying Initialization Parameters" for more information about specifying a scope of MEMORY, or SPFile, or both.
-
In the Value field, enter the new value for the
DB_CACHE_SIZE
initialization parameter. -
Click OK.
A confirmation message appears.
-
5.4.8 Modifying Memory Settings – Automatic PGA Memory Management
Modifying memory settings for automatic Program Global Area (PGA) memory management involves using the PGA Advisor graph in Oracle Enterprise Manager Database Express (EM Express) to modify the instance PGA size. This section assumes that automatic memory management is disabled, and that automatic PGA memory management is enabled.
Note:
When you disable automatic memory management, automatic PGA memory management remains enabled by default.
To modify the instance PGA size:
-
In EM Express, from the Configuration menu, select Memory.
The Memory Management page appears. If the database has either automatic shared memory management or manual shared memory management enabled, then the PGA Advisor Graph appears as the second chart to the right of the Memory Settings section.
-
Use the PGA Advisor graph to predict the estimated cache hit percentage for potential database cache sizes.
In the PGA Advisor graph:
-
Potential values for the
PGA_AGGREGATE_TARGET
initialization parameter are represented on the horizontal axis of the graph. The unit size (for example,MB
orGB
) is also indicated on the horizontal axis. The current setting of thePGA_AGGREGATE_TARGET
initialization parameter is indicated by a blue dot. -
The corresponding estimated cache hit percentage values are represented on the vertical axis of the graph. The plotted values are expressed as a percentage relative to the current setting of the
PGA_AGGREGATE_TARGET
initialization parameter.
An orange line on the graph plots different values that can be specified for the
PGA_AGGREGATE_TARGET
initialization parameter. Click any dot on the orange line to see an estimate of the percentage of cache hits for thePGA_AGGREGATE_TARGET
value represented by that dot.In this figure, the PGA Advisor graph indicates that increasing the current value of the
PGA_AGGREGATE_TARGET
initialization parameter will not increase the percentage of cache hit. -
-
To change the value of the
PGA_AGGREGATE_TARGET
initialization parameter:-
Click Configure Memory on the Memory Management page.
The Initialization Parameters page appears.
-
Select the
PGA_AGGREGATE_TARGET
initialization parameter and click Set.The Set Initialization Parameter page appears.
-
In the Scope field, enter the scope for this change.
See "Viewing and Modifying Initialization Parameters" for more information about specifying a scope of Memory, or SPFile, or both.
-
In the Value field, enter the new value for the
PGA_AGGREGATE_TARGET
initialization parameter. -
Click OK.
A confirmation message appears.
-
See Also:
-
Oracle Database Performance Tuning Guide for more information about memory management modes
5.5 Managing the Oracle Instance: Oracle By Example Series
Oracle By Example (OBE) has a series on the Oracle Database 2 Day DBA guide. This OBE series steps you through the tasks in this chapter, and includes annotated screenshots.
-
Start up and Shut down the Oracle Database Instance
-
View and Modify the Initialization Parameters
-
To see a clickable list of the above tutorials, go to Manage the Oracle Instance Series.
-
For seamless navigation through the tutorial series, access the following link:
You can navigate across the tutorials by clicking the > button at the bottom of the pane.