Universal Logging System

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
===== watch_oracle =====

This is a monitoring script for Oracle 9i, 10g, 11g and 12c database instances. 
It only does SELECTs on system tables, definitely no changes to anything!
No *_HIST_* tables are used.

"watch_oracle" may not cover everything that **YOU** are interested in, 
especially not any RAC specifics (currently), but it gives quite 
an overview of e.g.:

  * the instance status, SGA, PGA and tablespaces.
  * the shared pool and the buffer pools,
  * the dictionary and library caches,
  * sessions, processes and database system activity,
  * errors in the alert file
  * and more...

The script does especially **not** give any information about:

  * session specific content or performance of SQL commands, but you may have a look at [[:uls:agents:oracle_tools:ora_statspack]]
  * any defined schemas
  * any assigned privileges

"watch_oracle" consists of:

  * the Perl script containing the source of the monitoring script: "watch_oracle.pl"
  * the bash script to start the "watch_oracle.pl": "watch_oracle"

It uses sections of the
  * general configuration file for the [[uls:agents:oracle_tools]]: "oracle_tools.conf"

Typically, the bash script "watch_oracle" is invoked 
regularly thru a cron job (or task on Wind*ws), which sets the 
correct environment before executing the Perl script "watch_oracle.pl".

The "watch_oracle.pl" generates a log file and several work files during execution 
to keep its output and work values for the next run(s). 
The directory defined by WORKING_DIR in the [GENERAL] section 
is used as the destination directory for those files, 
the default is "<oracle_tools_dir>/var".

-----

==== Configuration ====

The configuration of "watch_oracle" is done by editing the file "oracle_tools.conf", 

<code ini oracle_tools.conf>

# -------------------------------------------------------------------
# oracle_tools.conf
# -------------------------------------------------------------------
#
# This is the single configuration file for the ORACLE_TOOLS.
# All definitions are made here for all scripts.

[GENERAL]

WORKING_DIR = `pwd`/var

TEST_BEFORE_RUN = /usr/local/sccl/bin/sccl_test_res oracle_%%ORACLE_SID%%

[WATCH_ORACLE]

IDENTIFIER = _watch_oracle

OPTIONS = SCHEDULER
</code>

You may want to change the above parameters something different, e.g.:

<code ini oracle_tools.conf>
WORKING_DIR = /var/log/watch_oracle

</code>

-----

==== Usage ====

=== Manual Execution ===

You can start the "watch_oracle" manually as user "oracle":

<code bash>
$ cd <oracle_tools_dir>/
$ ./watch_oracle
</code>

The log and work value files are placed in directory which is 
defined for WORKING_DIR in the "oracle_tools.conf", 
the default is "<oracle_tools_dir>/var/".

-----

=== Regular Execution ===

The execution in regular time intervals is controlled by 
an entry in a crontab, similar to:

<file - /etc/cron.d/oracle_tools_orcl>
  
# -----
# Monitoring script for Oracle
1,11,21,31,41,51 * * * * oracle /oracle/admin/<orcl>/oracle_tools/watch_oracle

</file>

-----

==== Output Results ====

The script gathers the following metrics (in alphabetical order)
of watch_oracle.pl, version 0.55:

-----

=== Alert Log ===

The alert.log of the Oracle database instance is checked for occurences of 
the expressions: 

  * ORA- (any Oracle error)
  * Errors in file (try to read first line from the given trace file)
  * Corrupt block (is produced by RMAN in case of corrupt blocks)
  * Process .. died (when a process died unexpectedly)

The search always starts at the last checked position 
within the alert file. The output will be restricted 
to a reasonable number of lines. You must check the alert log for 
the full error message(s).

{|
! error entry:
| Only present, if errors have been found. It contains the lines of the alert.log containing the matched patterns.
|-
! first lines of trace file:
| If a trace file is referenced in the alert.log, like 
 "Errors in file .../xyz_ora_12345.trc", then the first lines 
 of that trace file are extracted. That should give a clue
 about the problem.
|}

:!: Teststep "alert.log" is **only** present in interactive analysis, 
**if** error entries were found in the **selected** time period!

-----

=== Buffer Cache ===

Oracle uses the buffer cache to store blocks read from disk. By default, there is only one buffer cache defined for the default block size. Other default buffer caches using different block sizes may be defined, also a keep and a recycle buffer cache.

For more information, see the [[uls:misc:references#Oracle® Database Performance Tuning Guide]].

{|
! size:
| Current size of the buffer cache.
|-
! used:
| Number of currently used buffer blocks multiplied by the block size. This is normally just a bit smaller than 'size' due to dynamic sizing.
|-
! hit ratio:
| The buffer cache hit ratio represents how often a requested block has
 been found in the buffer cache without requiring disk access:
<code oracle11>
SELECT physical_reads, db_block_gets + consistent_gets 
  FROM V$BUFFER_POOL_STATISTICS;
</code>
 Using the above results for each buffer cache, its hit ratio is calculated with the following formula:
\(hit~ratio = 1 - (physical~reads / (db~block~gets + consistent~gets)) * 100 \)
|-
! objects: (optional)
| This report shows the top 20 objects which occupy the most space in the buffer cache.
|}

-----

=== Dictionary Cache ===

The dictionary cache is part of the shared pool. Information stored in the data dictionary cache includes usernames, segment information, profile data, tablespace information, and sequence numbers. The dictionary cache also stores descriptive information, or metadata, about schema objects. Oracle uses this metadata when parsing SQL cursors or during the compilation of PL/SQL programs.

Misses on the data dictionary cache are to be expected in some cases. On instance startup, the data dictionary cache contains no data. Therefore, any SQL statement issued is likely to result in cache misses. As more data is read into the cache, the likelihood of cache misses decreases. Eventually, the database reaches a steady state, in which the most frequently used dictionary data is in the cache. At this point, very few cache misses occur.

For more information, see the [[uls:misc:references#Oracle® Database Performance Tuning Guide]].

{|
! gets:
| Shows the total number of requests for information on the corresponding item.
|-
! getmisses:
| Shows the number of data requests which were not satisfied by the cache, requiring an I/O.
|-
! fixed:
| Number of fixed entries in the cache.
|-
! modifications:
| Shows the number of times data in the dictionary cache was updated.
|-
! overall hit ratio:
| It is also possible to calculate an overall dictionary cache hit ratio
 using the following formula; however, summing up the data over all the
 caches will lose the finer granularity of data:
<code oracle11>
 select (sum(gets - getmisses - fixed)) / sum(gets) from v$rowcache;
</code>
Examine cache activity by monitoring the GETS and GETMISSES columns. For frequently accessed dictionary caches, the ratio of total GETMISSES to total  GETS should be less than 10% or 15%, depending on the application.

Increase the amount of memory available to the data dictionary cache by increasing the value of the initialization parameter SHARED_POOL_SIZE (if not automatic memory management is enabled).
|}

-----

=== Detailed Dictionary Cache ===

Typically, if the shared pool is adequately sized for the library cache, it will also be adequate for the dictionary cache data.

Misses on the data dictionary cache are to be expected in some cases. On instance startup, the data dictionary cache contains no data. Therefore, any SQL statement issued is likely to result in cache misses. As more data is read into the cache, the likelihood of cache misses decreases. Eventually, the database reaches a steady state, in which the most frequently used dictionary data is in the cache. At this point, very few cache misses occur.

This shows the statistics for a particular data dictionary item.

{|
! gets:
| Shows the total number of requests for information on the corresponding item.
|-
! getmisses:
| Shows the number of data requests which were not satisfied by the cache, requiring an I/O.
|-
! fixed:
| Number of fixed entries in the cache.
|-
! modifications:
| Shows the number of times data in the dictionary cache was updated.
|-
! hit ratio:
| Item-specific hit ratio.
|}

-----

=== Fast Recovery Area ===

Starting in Oracle 11g release 2, Oracle has re-named the flash recovery area to be the fast recovery area FRA.

The FRA is a unified storage location for all Oracle Database files related to recovery. When you set the parameters DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE, RMAN backups, archive logs, control file automatic backups, and database copies can be written to the FRA. RMAN automatically manages files in the FRA by deleting obsolete backups and archive files no longer required for recovery.

Use [[uls:agents:oracle_tools:ora_dbinfo]] to find the parameter settings.

{|
! size:
| Maximum amount of disk space (in MB) that the database can use for the FRA. This is the value specified in the DB_RECOVERY_FILE_DEST_SIZE initialization parameter.
|-
! used:
| Amount of disk space (in MB) used within the FRA.
|-
! %used:
| Percentage of used disk space in the FRA.
|-
! reclaimable:
| Total amount of disk space (in MB) that can be freed by deleting obsolete, redundant, and other low priority files from the FRA.
|-
! number of files:
| Number of files in the FRA.
|}

-----

=== Flashback Recovery Area ===

"Flashback database" is a feature new in Oracle 10g that allows a DBA to revert an entire database back to an earlier point in time. Depending upon the length of time of the required flashback, it is often significantly faster and easier to flashback the database (recover backwards) than perform a point-in-time recovery (forward from last full backup).

{|
! flashback feature enabled:
| YES if the flashback area is enabled.
|-
! recovery_file_dest:
| Path of the flashback area.
|-
! size:
| Size of the flashback area.
|-
! used:
| Used space in the flashback area.
|-
! %used:
| Percentage of used space in the flashback area.
|-
! oldest flashback timestamp:
| The earliest time the database can be flashed back to.
|-
! retention target:
| Planned number of minutes to keep the flashback data.
|}

-----

=== Info ===

General info about the state and version of the Oracle database.

{|
! database status:
| Should be "OPEN" for normal operation.
|-
! oracle version:
| Is the version of this instance.
|-
! hostname:
| The hostname of the server where the instance is running.
|-
! instance name:
| Should match to the environment variable ORACLE_SID.
|-
! database log mode:
| Is 'ARCHIVELOG' or 'NOARCHIVELOG'. You only can perform online backups, if the instance is in 'ARCHIVELOG' mode.
|-
! instance startup at:
| Is the ISO timestamp of when this instance has been started.
|}

-----

=== Jobs ===

You can schedule routines (jobs) to be run periodically using the job queue. To schedule a job you submit it to the job queue, using the Oracle supplied DBMS_JOB package, and specify the frequency at which the job is to be run. Additional functionality enables you to alter, disable, or delete a job that you previously submitted.

Job queue (Jnnn) processes execute jobs in the job queue. For each instance, these job queue processes are dynamically spawned by a coordinator job queue (CJQ0) background process.

The JOB_QUEUE_PROCESSES initialization parameter controls whether a coordinator job queue process is started by an instance. If this parameter is set to 0, no coordinator job queue process is started at database startup, and consequently no job queue jobs are executed.

Jobs are deprecated since Oracle 10.2, use now DBMS_SCHEDULER.

:!: If the execution interval of a job is smaller than the execution 
interval of this monitoring script, not all job executions may be 
reported and some figures may be wrong. Check 'last execution before' 
to find out if all job executions are catched.

All jobs that have not been executed for more than 32 days are ignored.

{|
! broken:
| Oracle has failed to successfully execute the job after
 16 attempts. Or you have marked the job as broken, using
 the procedure DBMS_JOB.BROKEN. Once a job has been marked
 as broken, Oracle will not attempt to execute the job until
 it is either marked not broken, or forced to be execute by
 calling the DBMS_JOB.RUN.
|-
! failures:
| Number of times the job has started and failed since its last success.
|-
! last execution before:
| The time in hours that has elapsed since the last execution of the job.
|-
! runtime:
| The time that the job needed to execute at its last execution.
|-
! start-stop:
| The start and stop time tupel for the last execution of the job.
|-
! what:
| The executed command.
|}

-----

=== Latches ===

Oracle collects statistics for the activity of all latches and stores
these in this view. Values are only gathered if gets or misses are
greater than 10 since the last script run.

{|
! gets:
| Is the number of successful willing to wait requests for a latch.
|-
! misses:
| Is how many times a process didn't successfully request a latch.
|-
! successful:
| Is the percentage how often a request missed a latch.
|}

-----

=== Library Cache ===

The library cache holds executable forms of SQL cursors, PL/SQL programs, and Java classes.

The library cache is nothing more than an area in memory, specifically one of three parts inside the shared pool. The library cache is composed of shared SQL areas, PL/SQL packages and procedures, various locks and handles, and in the case of a shared server configuration, stores private SQL areas.

Whenever an application wants to execute SQL or PL/SQL (collectively called code), that code must first reside inside Oracle's library cache. When applications run and reference code, Oracle will first search the library cache to see if that code already exists in memory. If the code already exists in memory then Oracle can reuse that existing code (also known as a soft parse). If the code does not exist, Oracle must then load the code into memory (also known as a hard parse, or library cache miss). There are various criteria as to whether code being requested actually matches code already in the library cache.

Be aware that a configured library cache area, since it is allocated a specific amount of memory, can actively only hold so much code before it must age out some to make room for code that is required by applications. This is not necessarily a bad thing but we must be aware of the size of our library cache as well as how many misses or hard parses that are occurring. If there are too many, we may need to increase the amount of memory allocated to the library cache.

For more information, see the [[uls:misc:references#Oracle® Database Performance Tuning Guide]].

{|
! gets:
| The number of lookups for code in the library cache. When a statement
 needs to be executed, the library cache is checked for a previous
 instance of it. If found, it is one get.
|-
! gethits:
| The number of successful library cache lookups.
|-
! gethitratio:
| The ratio of gethits to gets.
|-
! pins:
| The number of executions for code in the library cache.
|-
! pinhits:
|
|-
! pinhitratio:
| The ratio of pinhits to pins.
|-
! reloads:
| The number of attempts to execute code but it was not found in the library cache.
|-
! invalidations:
| The number of times that statements have become invalid for some reason, typically through a DDL operation, and a reparse is required.
|}

-----

=== Open Cursors ===

Open cursors take up space in the shared pool, in the library cache. To keep a renegade session from filling up the library cache, or clogging the CPU with millions of parse requests, the parameter OPEN_CURSORS is set.

OPEN_CURSORS sets the maximum number of cursors each session can have open. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time. If a single session has OPEN_CURSORS of cursors open, it will get an ora-1000 error when it tries to open one more cursor.

Raise the OPEN_CURSORS parameter if sessions reach the current limit. If a session continuously gets an ORA-1000, it may indicate a leak in the application code.

See also: [[uls:misc:references#Monitoring Open and Cached Cursors]].

{|
! open_cursors:
| The specified parameter.
|-
! count:
| Number of all opened cursors for all sessions.
|-
! max:
| The maximum number of opened cursors for one session.
|-
! avg:
| The average number of opened cursors for all sessions.
|}

-----

== Session Cached Cursors ==

SESSION_CACHED_CURSORS sets the number of cached closed cursors each session can have. If SESSION_CACHED_CURSORS is not set, it defaults to 0 and no cursors will be cached for your session. (Your cursors will still be cached in the shared pool, but your session will have to find them there.) If it is set, then when a parse request is issued, Oracle checks the library cache to see whether more than 3 parse requests have been issued for that statement. If so, Oracle moves the session cursor associated with that statement into the session cursor cache. Subsequent parse requests for that statement by the same session are then filled from the session cursor cache, thus avoiding even a soft parse.

The obvious advantage to caching cursors by session is reduced parse times, which leads to faster overall execution times. This is especially so for applications like Oracle Forms applications, where switching from one form to another will close all the session cursors opened for the first form. Switching back then opens identical cursors. So caching cursors by session really cuts down on reparsing.

{|
! session_cached_cursors:
| The specified parameter.
|-
! sessions hit limit:
| Percentage of sessions having reached the number of session cached cursors as specified as parameter. (Some sessions may never reach the specified session_cached_cursors)
|-
! avg:
| Average number of session cached cursors for all sessions.
|}

-----

=== PGA ===

The Program Global Area (PGA) is a private memory region containing 
data and control information for a server process. Access to it is 
exclusive to that server process and is read and written only by the 
Oracle code acting on behalf of it.

For more information, see the [[uls:misc:references#Oracle® Database Performance Tuning Guide]].

The automatic PGA Memory Management is enabled, if the PGA_AGGREGATE_TARGET 
initialization parameter is set to a non-zero value. 
It cannot be used for shared server connections!

{|
! aggregate PGA target parameter:
| This is the current value of the initialization parameter
 PGA_AGGREGATE_TARGET. If you do not set this parameter, its value
 is 0 and automatic management of the PGA memory is disabled.
|-
! aggregate PGA auto target:
| This gives the amount of PGA memory Oracle can use for work areas
 running in automatic mode. This amount is dynamically derived from
 the value of the parameter PGA_AGGREGATE_TARGET and the current work
 area workload. Hence, it is continuously adjusted by Oracle. If this
 value is small compared to the value of PGA_AGGREGATE_TARGET, then a
 lot of PGA memory is used by other components of the system (for
 example, PL/SQL or Java memory) and little is left for sort work areas.
 You must ensure that enough PGA memory is left for work areas running
 in automatic mode.
|-
! global memory bound:
| This gives the maximum size of a work area executed in AUTO mode. This
 value is continuously adjusted by Oracle to reflect the current state
 of the work area workload. The global memory bound generally decreases
 when the number of active work areas is increasing in the system. As a
 rule of thumb, the value of the global bound should not decrease to less
 than one megabyte. If it does, then the value of PGA_AGGREGATE_TARGET
 should probably be increased.
|-
! total PGA allocated:
| This gives the current amount of PGA memory allocated by the instance.
 Oracle tries to keep this number less than the value of PGA_AGGREGATE_TARGET.
 However, it is possible for the PGA allocated to exceed that value by a
 small percentage and for a short period of time, when the work area workload
 is increasing very rapidly or when the initialization parameter
 PGA_AGGREGATE_TARGET is set to a too small value.
|-
! total PGA used for auto workareas:
| This indicates how much PGA memory is currently consumed by work areas
 running under automatic memory management mode. This number can be used
 to determine how much memory is consumed by other consumers of the PGA
 memory (for example, PL/SQL or Java):
 \[ PGA~other = total~PGA~allocated - total~PGA~used~for~auto~workareas \]
|-
! maximum PGA allocated:
| This is the maximum amount of PGA memory allocated since instance start.
|-
! over allocation count:
| Over-allocating PGA memory can happen if the value of PGA_AGGREGATE_TARGET
 is too small to accommodate the PGA other component in the previous equation
 plus the minimum memory required to execute the work area workload.
 When this happens, Oracle cannot honor the initialization parameter
 PGA_AGGREGATE_TARGET, and extra PGA memory needs to be allocated. If
 over-allocation occurs, you should increase the value of
 PGA_AGGREGATE_TARGET (=> V$PGA_TARGET_ADVICE).
|-
! total bytes processed:
| This is the number of bytes processed by memory-intensive SQL operators.
 For example, the number of byte processed is the input size for a sort
 operation.
|-
! extra bytes read/written:
| When a work area cannot run optimally, one or more extra passes is
 performed over the input data. extra bytes read/written represents the
 number of bytes processed during these extra passes since instance
 start-up. This number is also used to compute the cache hit percentage.
|-
! cache hit ratio:
| This metric is computed by Oracle to reflect the performance of the PGA
 memory component. A value of 100% means that all work areas executed by
 the system have used an optimal amount of PGA memory. Some work areas
 run one-pass or even multi-pass, depending on the overall size of the PGA
 memory (=> system statistics). When a work area cannot run optimally, one
 or more extra passes is performed over the input data. This reduces the
 cache hit percentage in proportion to the size of the input data and the
 number of extra passes performed:
 \[ cache~hit~ratio = \frac{total~bytes~processed * 100}{total~bytes~processed + extra~bytes~read~written} \]
|}

<!--
                      total bytes processed * 100
    cache hit ratio = ------------------------------------------------
                      total bytes processed + extra bytes read/written
-->

=== Redo Logs ===

The most crucial structure for recovery operations is the redo log, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.

For more information, see the |AdminGuide|.

{|
! redo buffer allocation retries:
| Total number of retries a user process must wait to allocate space in the
 redo buffer. Retries are needed either because the redo writer has fallen
 behind or because an event such as a log switch is occurring.
 
 The value of redo buffer allocation retries should be near zero over an
 interval. If this value increments consistently, then processes have had to
 wait for space in the redo log buffer. The wait can be caused by the log
 buffer being too small or by checkpointing. Increase the size of the redo
 log buffer, if necessary, by changing the value of the initialization
 parameter LOG_BUFFER. The value of this parameter is expressed in bytes.
 Alternatively, improve the checkpointing or archiving process.
 
 Another data source is to check whether the log buffer space wait event is
 not a significant factor in the wait time for the instance; if not, the
 log buffer size is most likely adequate.
|-
! redo entries:
| Number of times a redo entry is copied into the redo log buffer.
|-
! redo log space requests:
| This reflects the number of times a user process waits for space in the
 redo log buffer. The LGWR writes redo entries from the redo log buffer to
 a redo log file.  Once LGWR copies the entries to the redo log file the
 user process can over write these entries.
|-
! redo log space wait time:
| Total elapsed waiting time for 'redo log space requests'.
|-
! redo size:
| Amount of redo generated.
|-
! redo synch writes:
| Number of times a change being applied to the log buffer must be written
 out to disk due to a commit. The log buffer is a circular buffer that
 LGWR periodically flushes. Usually, redo that is generated and copied into
 the log buffer need not be flushed out to disk immediately.
|-
! redo write time:
| Total elapsed time of the write from the redo log buffer to the current redo log file.
|-
! redo writes:
| Total number of writes by LGWR to the redo log files.
|-
! summary status all members:
| select member, status from v$logfile;
 
 If the status for any one member is not blank (which is ok), then 'ERROR' is delivered, else 'OK'.
|-
! member status info:
| This is only delivered in case of an error. It shows a list of those redo log file members that have a bad state in the form:
<file>
<member> = <status>

<member>
  The path and filename of the redo log member.

<status>:
  INVALID: File is inaccessible
  STALE  : File's contents are incomplete
  DELETED: File is no longer used
</file>
|-
! redo log switches:
| The number of redo log switches that have occurred since the last run.
|}

-----

=== Scheduler ===

The Oracle Scheduler enables users to schedule jobs running inside the database such as PL/SQL procedures or PL/SQL blocks as well as jobs running outside the database such as shell scripts.

{|
! scheduler log:
| Lists the log entries of the scheduled jobs.
|}

-----

=== Sessions and Processes ===

The number of sessions and processes. Processes include the background processes of the Oracle instance. You may check if you are running out of processes if you compare the number of current processes against the number of defined (in init.ora/spfile) number of processes.

{|
! processes:
| The number of all processes.
|-
! sessions:
| The number of sessions.
|-
! max processes:
| The defined max number of processes.
|}

-----

=== Shared Pool ===

Contains the most recently used SQL statements and parse trees along with PL/SQL blocks.

{|
! size:
| The current size of the shared pool.
|-
! used:
| The currently used space in the shared pool.
|-
! free:
| The currently free space in the shared pool.
|-
! %used:
| Ratio of used to size.
|}

Increasing the amount of memory for the shared pool increases the amount of memory available to both the library cache and the dictionary cache.

To ensure that shared SQL areas remain in the cache after their SQL statements are parsed, increase the amount of memory available to the library cache until the V$LIBRARYCACHE.RELOADS value is near zero. To increase the amount of memory available to the library cache, increase the value of the initialization parameter SHARED_POOL_SIZE.

The Reserved Pool is 5% of the shared pool by default, that limits used to 95%, except SHARED_POOL_RESERVED_SIZE is otherwise configured manually.

You may change the shared pool size, if there is still free memory in the sga by issueing e.g.:
<code oracle11>
  alter system set shared_pool_size = 200M;
</code>

In newer Oracle databases, the parameter SGA_TARGET or MEMORY_MAX_TARGET (dynamically manage both the SGA and PGA) is set and Oracle manages all pools and the PGA by itself (Automatic Memory Management).

-----

=== SGA ===

Because the purpose of the SGA is to store data in memory for fast access, the SGA should be within main memory. If pages of the SGA are swapped to disk, then the data is no longer quickly accessible. On most operating systems, the disadvantage of paging significantly outweighs the advantage of a large SGA.

{|
! overall size:
| The current size of the SGA.
|-
! free memory:
| The current free memory within the SGA.
|}

You may change buffer cache, shared pool and large pool without bouncing the instance, as long as there is free memory available.

-----

=== System Statistics ===

{|
! consistent changes:
| The number of times a database block has applied rollback entries to perform a consistent read on the block.
|-
! db block changes:
| This counts the total number of changes that were made to all blocks in the SGA that were part of an update or delete operation.
|-
! DBWR checkpoint buffers written:
| The number of buffers that were written for checkpoints.
|-
! execute count:
| Sum of all sql calls, including recursives. In an OLTP system the execute count should be remarkable higher than the parse count.
|-
! parse count (total):
| Count of sql calls (of execute count) that needed to be parsed. An sql call should if possible only be parsed once but executed multiple times. (If not, check if application uses bind variables).
|-
! physical reads:
| Number of blocks that had to be read from disk.
|-
! physical writes:
| Number of blocks that had to be written to disk.
|-
! recursive calls:
| A high figure of recursive calls (compared to total calls) may indicate any of the following:
  * Dynamic extension of tables due to poor sizing
  * Growing and shrinking of rollback segments due to unsuitable OPTIMAL settings
  * Large amounts of sort to disk resulting in creation and deletion of temporary segments
  * Data dictionary misses
  * Complex triggers, integrity constraints, procedures, functions and/or packages
 
|-
! table fetch continued row:
| When a row spans more than one block during a fetch then this figure is incremented.
 Retrieving rows that span more than one block increases the logical I/O
 by a factor that corresponds to the number of blocks than need to be accessed.
 Exporting and re-importing may eliminate this problem. Taking a closer look
 at the STORAGE parameters PCT_FREE and PCT_USED. This problem cannot be fixed
 if rows are larger than database blocks (for example, if the LONG datatype is
 used and the rows are extremely large).
|-
! table scan rows gotten:
| The number of rows processed during scan operations.
|-
! table scans (short tables):
| Short tables may be fully scanned by Oracle when this is quicker than using an index. 
|-
! table scans (long tables):
| Full table scans of long tables is generally bad for overall
 performance. High figures for this may indicate lack of indexes on large
 tables or poorly written SQL which fails to use existing indexes or
 is returning a large percentage of the table.
|-
! sorts (disk):
| The number of sort operations that needed disk writes. Sorts that require
 I/O to disk are quite resource intensive. Try increasing the size of the
 initialization parameter SORT_AREA_SIZE.
|-
! sorts (memory):
| The number of sorts in memory (no disk writes). This is more an indication
 of sorting activity in the application work load. You cannot do much better
 than memory sorts, except maybe no sorts at all. Sorting is usually caused
 by selection criteria specifications within table join SQL operations.
|-
! user calls:
| This is incremented each time Oracle allocates resources for a user (log in, parse, execute).
|-
! user commits:
| The number of committed user transactions.
|}

-----

=== Tablespace Info ===

(this is obsolete: all this info has moved to [[uls:agents:oracle_tools:ora_dbinfo]])

A tablespace is a logical storage unit within an Oracle database. It is logical because a tablespace is not visible in the file system of the machine on which the database resides. A tablespace, in turn, consists of at least one datafile which, in turn, are physically located in the file system of the server. Btw, a datafile belongs to exactly one tablespace.
Each table, index and so on that is stored in an Oracle database belongs to a tablespace. The tablespace builds the bridge between the Oracle database and the filesystem in which the table's or index' data is stored.
There are three types of tablespaces in Oracle:

  * permanent tablespaces
  * undo tablespaces
  * temporary tablespaces

For more information, see the |AdminGuide|.

To create a new tablespace, use the SQL statement CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE.

Prior to Oracle8i, all tablespaces were created as dictionary-managed. Dictionary-managed tablespaces rely on data dictionary tables to track space utilization. Beginning with Oracle8i, you were able to create locally managed tablespaces, which use bitmaps (instead of data dictionary tables) to track used and free space. These locally managed tablespaces provide better performance and greater ease of management.

You can also create a special type of tablespace called an undo tablespace. This tablespace is specifically designed to contain undo records. These are records generated by Oracle that are used to roll back, or undo, changes to the database for recovery, read consistency, or as requested by a ROLLBACK statement.

Locally managed tablespaces track all extent information in the tablespace itself, using bitmaps. All tablespaces, including the SYSTEM tablespace, can be locally managed.

See the DBMS_SPACE_ADMIN package for maintenance procedures for locally 
managed tablespaces (this may differ over Oracle versions):

  CREATE TABLESPACE xyz DATAFILE '/oracle/admin/<sid>/oradata/xyz01.dbf' SIZE 2048M
    EXTENT MANAGEMENT LOCAL
    - AUTOALLOCATE       (default, preferred)
    - UNIFORM SIZE 128K  (only if necessary for best control of object growth)
    SEGMENT SPACE MANAGEMENT
    - MANUAL   (default!)
    - AUTO     (preferred)

See also: CREATE TEMPORARY TABLESPACE

Use the preferred options if possible.

{|
! status:
| ONLINE | OFFLINE | READ ONLY
|-
! contents:
| PERMANENT | TEMPORARY | UNDO
|-
! logging:
| LOGGING | NOLOGGING
|-
! extent management:
| Indicates how the extents of this tablespace are managed { DICTIONARY | LOCAL }
|-
! allocation type:
| Type of extent allocation in effect for this tablespace { SYSTEM | UNIFORM | USER }
|-
! segment space management:
| Indicates how the used and free space in the segments in this tablespace are managed { MANUAL | AUTO }.
|-
! autoextensible datafiles:
| The number of autoextensible datafiles for the tablespace. If greater than zero, the tablespace may grow on demand if disk space is available.
|}

-----

=== Tablespace Usage ===

Usage information about all tablespaces.

{|
! size:
| Size of the tablespace.
|-
! used:
| Space used within the tablespace. For temporary tablespaces: the sum of the size of all currently existing user objects in the tablespace.
|-
! free:
| Free space within the tablespace.
|-
! %used:
| Percentage of usage (used/size*100).
|-
! used (lazy), free (lazy), %used (lazy):
| These figures reflect the "normal" space usage of the temporary
 tablespace. But the space is only reused on demand (lazy) after
 all the free space has been used up. (For temporary tablespaces only)
|}

-----

=== Wait Events Classes ===

Wait events are statistics that are incremented by a server process or 
thread to indicate that it had to wait for an event to complete before 
being able to continue processing. Wait events are grouped into classes.

When a database is up and running, every connected process is either 
busy performing work or waiting to do so. The 'idle' and 'other' wait classes are ignored.

Search for "Classes of Wait Events" in Oracle's Database Reference.

{|
! total waits:
| Number of waits occurred for the specific wait class.
|-
! time waited:
| Sum of time waited for the specific wait class.
|-
! average time waited:
| Average wait time for each wait. Ratio of 'time waited' to 'total waits'.
|-
! ratio to sum total waits:
| Percentage of the wait class 'total waits' to the sum of 'total waits' for all wait classes.
|-
! ratio to sum time waited:
| Percentage of the wait class 'time waited' to the sum of 'time waited' for all wait classes.
|}

-----

=== Wait Events ===

Wait events are statistics that are incremented by a server process or thread to indicate that it had to wai
t for an event to complete before being able to continue processing. Wait event data reveals various symptom
s of problems that might be impacting performance, such as latch contention, buffer contention, and I/O cont
ention. Remember that these are only symptoms of problems, not the actual causes.

{|
! SQL*Net Events:
| If these wait events constitute a significant portion of the wait
 time on the system or for a user experiencing response time issues,
 then the network or the middle-tier could be a bottleneck.
|-
! buffer busy waits:
| This wait indicates that there are some buffers in the buffer cache that multiple processes are attempting to access concurrently.
|-
! db file scattered read:
| This event signifies that the user process is reading buffers into the
 SGA buffer cache and is waiting for a physical I/O call to return.
 A db file scattered read issues a scattered read to read the data into
 multiple discontinuous memory locations. A scattered read is usually a
 multiblock read. It can occur for a fast full scan (of an index)
 in addition to a full table scan.
|-
! db file sequential read:
| This event signifies that the user process is reading a buffer into the
 SGA buffer cache and is waiting for a physical I/O call to return.
 A sequential read is a single-block read.
 
 Single block I/Os are usually the result of using indexes. Rarely,
 full table scan calls could get truncated to a single block call due
 to extent boundaries, or buffers already present in the buffer cache.
|-
! enqueue waits (enq):
| Enqueues are locks that coordinate access to database resources.
 This event indicates that the session is waiting for a lock that is
 held by another session.
|-
! free buffer waits:
| This wait event indicates that a server process was unable to find a
 free buffer and has posted the database writer to make free buffers by
 writing out dirty buffers. A dirty buffer is a buffer whose contents
 have been modified. Dirty buffers are freed for reuse when DBWR has
 written the blocks to disk.
|-
! latch events (latch):
| A latch is a low-level internal lock used by Oracle to protect memory
 structures. The latch free event is updated when a server process
 attempts to get a latch, and the latch is unavailable on the first attempt.
|-
! total waits:
| Total number of waits for the event.
|-
! total timeouts:
| Total number of timeouts for the event.
|-
! time waited:
| Total amount of time waited for the event.
|-
! average wait:
| Average amount of time waited for the event.
|}

Generally, the analysis of wait events is very sophisticated, 
good documentation is found for the different Oracle versions in 
the [[uls:misc:references#Oracle® Database Performance Tuning Guide]].

For some wait events, Oracle Note 223117.1 and 34558.1 may also be of interest.
TIMED_STATISTICS must be set to TRUE, which is not the default for some operating systems.

-----

==== Auto-Monitoring Settings ====

These are the recommended auto-monitoring settings for the ORACLE_TOOLS.
These include the recommended aggregations (or compressions) for several 
gathered metrics. 

Use "main menu --> administration --> auto-monitoring --> <domain>"
and paste the following definitions into the text box after having replaced the
placeholder <notification destination> by your actual notification 
destination:

<file ini auto_settings.conf>
# ===================================================
#
# AutoLimits, ORACLE
#
# ===================================================

# ===================================================
# Limits

[limits]

server=
section=Oracle DB*
teststep=alert.log
detail=error entry
unit=
function=last
over=1
relation=!==
infolevel=
warnlevel=_NO_ERROR_
errorlevel=
notifymode=S10
message=Error in alert.log: $VALUE
destination1=<notification destination>
dest1levels=IWF
destination2=
dest2levels=IWF
remark=

server=
section=Oracle DB*
teststep=fast recovery area
detail=%used
unit=
function=avg
over=3
relation=>
infolevel=89
warnlevel=90
errorlevel=
notifymode=24
message=The FRA is used for more than $VALUE $UNIT!
destination1=<notification destination>
dest1levels=WF
destination2=
dest2levels=WF
remark=

server=
section=Oracle DB*
teststep=info
detail=database status
unit=
function=last
over=1
relation=!==
infolevel=OPEN
warnlevel=OPEN
errorlevel=
notifymode=24
message=The database is not "$LIMIT"!
destination1=<notification destination>
dest1levels=WF
destination2=
dest2levels=WF
remark=

server=
section=Oracle DB*
teststep=redo logs
detail=status report
unit=
function=last
over=1
relation=!==
infolevel=
warnlevel=_NO_ERROR_
errorlevel=
notifymode=24
message=Incorrect state of at least one online redo log file:$VALUE
destination1=<notification destination>
dest1levels=IWF
destination2=
dest2levels=IWF
remark=

server=
section=Oracle DB*
teststep=tablespace usage:*
detail=%used
unit=
function=avg
over=3
relation=>
infolevel=89
warnlevel=90
errorlevel=
notifymode=24
message=This tablespace is used for more than $VALUE $UNIT!
destination1=<notification destination>
dest1levels=WF
destination2=
dest2levels=WF
remark=

server=
section=Oracle DB*
teststep=_*
detail=message
unit=
function=last
over=1
relation=!==
infolevel=OK
warnlevel=OK
errorlevel=
notifymode=24
message=Error when executing script for teststep "$TESTSTEP".
destination1=<notification destination>
dest1levels=WF
destination2=
dest2levels=IWF
remark=


# ===================================================
# isAlive

[isalive]

server=
section=Oracle DB*
teststep=_backup*
detail=message
unit=
period=30
periodunit=hour
notifymode=24
message=Script $TESTSTEP has not been executed as expected.
destination1=<notification destination>
destination2=
remark=
access=all


server=
section=Oracle DB*
teststep=_rman*
detail=message
unit=
period=30
periodunit=hour
notifymode=24
message=Script $TESTSTEP has not been executed as expected.
destination1=<notification destination>
destination2=
remark=
access=all


server=
section=Oracle DB*
teststep=_watch*
detail=message
unit=
period=60
periodunit=min
notifymode=24
message=Script $TESTSTEP has not been executed as expected.
destination1=<notification destination>
destination2=
remark=
access=all


# ===================================================
# Combined Limits

[combilimits]
server=
section=Oracle DB*
teststep=sessions and processes
condition=$VALUE_OF(processes) >= $VALUE_OF(max processes) * 0.9
level=WARN
notifymode=24
message=The number of $VALUE_OF(processes) Oracle processes is getting close to the defined parameter 'processes' ($VALUE_OF(max processes)).
destination1=<notification destination>
destination2=
remark= 


# ===================================================
# Compression

[compression] 

server=
section=oracle db*
teststep=buffer cache:*,buffer cache
detail=hit ratio,size,used
unit=
hourly=
daily=avg,min,max
weekly=avg,min,max
monthly=
access=all

server=
section=oracle db*
teststep=dictionary cache
detail=overall hit ratio
unit=
hourly=
daily=avg,min,max
weekly=avg,min,max
monthly=
access=all

server=
section=oracle db*
teststep=fast recovery area
detail=size
unit=
hourly=
daily=max
weekly=max
monthly=
access=all

server=
section=oracle db*
teststep=fast recovery area
detail=used
unit=
hourly=
daily=max,max-min
weekly=max,max-min
monthly=
access=all

server=
section=oracle db*
teststep=library cache
detail=*ratio
unit=
hourly=
daily=avg,min,max
weekly=avg,min,max
monthly=
access=all

server=
section=oracle db*
teststep=pga
detail=aggregate PGA target parameter
unit=
hourly=
daily=max
weekly=max
monthly=
access=all

server=
section=oracle db*
teststep=pga
detail=total PGA allocated
unit=
hourly=
daily=avg,min,max
weekly=avg,min,max
monthly=
access=all

server=
section=oracle db*
teststep=redo logs
detail=redo size
unit=
hourly=
daily=avg,sum
weekly=avg,sum
monthly=
access=all

server=
section=oracle db*
teststep=sessions and processes
detail=sessions
unit=
hourly=
daily=avg,min,max
weekly=avg,min,max
monthly=
access=all

server=
section=oracle db*
teststep=sga
detail=overall size,free memory
unit=
hourly=
daily=avg,min,max
weekly=avg,min,max
monthly=
access=all

server=
section=oracle db*
teststep=shared pool
detail=size
unit=
hourly=
daily=max
weekly=max
monthly=
access=all

server=
section=oracle db*
teststep=shared pool
detail=used
unit=
hourly=
daily=avg,min,max
weekly=avg,min,max
monthly=
access=all

server=
section=oracle db*
teststep=system statistics
detail=user calls,user commits
unit=
hourly=
daily=avg,sum
weekly=avg,sum
monthly=
access=all

server=
section=oracle db*
teststep=tablespace usage
detail=size of all
unit=
hourly=
daily=max
weekly=max
monthly=
access=all

server=
section=oracle db*
teststep=tablespace usage:*
detail=size
unit=
hourly=
daily=max
weekly=max
monthly=
access=all

server=
section=oracle db*
teststep=tablespace usage:*
detail=used
unit=
hourly=
daily=max,max-min
weekly=max,max-min
monthly=
access=all
</file>
Universal Logging System

User Tools

Site Tools

Page Tools
