Chapter 25 – Diagnosing Oracle Database Issues

Intro

• There are two distinct sources for alert info in Oracle
  • Background processes – these create trace files and add data to alert logs. This cannot be configured.
  • Database alert system – runs from within the database and is completely configurable

The Alert Log

• As soon as an instance starts, critical messages are written to the alert log (and trace files) in the BACKGROUND_DUMP_DEST for which the OS directory must exist and the oracle user must have write permissions.
  • The format of the file is alert_<SID>.log
  • The directory can’t be the root directory
• Messages included in alert log
  • Startup and shutdown – moves from nomount to mount to open, etc.
  • All changes to physical structure of db – creating, dropping, renaming datafiles, logfiles, resizing & taking datafiles offline or online
  • Tablespace operations like DROP and CREATE; putting tablespaces into and out of hot backup mode for user backups.
  • Log switches and archives
  • Nondefault instance parameters used to start instance; alter system commands
  • There are no DML or DDL commands in the alert log.
• Common messages:
  • Checkpoint incomplete – when a checkpoint occurs and a previous checkpoint has not yet completed.
  • Unable to open file – when a problem exists with a controlfile or controlfile copy (-> MOUNT) or a problem exists with a redo log or datafile (-> OPEN)
  • Corrupt block – address of a corrupt datafile block
  • Problems with archiving – archivelog destination full
  • Deadlocks – sessions are blocking each other

Background Trace Files

• Background processes will write out data to the BACKGROUND_DUMP_DEST when they encounter problems.
• The size of the trace file is limited by MAX_DUMP_FILE_SIZE.
• All trace files write out their own file name, the version of Oracle running on the server and operating system information to the beginning of the file before writing out the trace info.

Server Generated Alerts

• Completely configurable alert system that monitors db, instance, user sessions and issues warnings when limits are reached and events occur.
• MMON (Manageablility Monitor) and MMNL (Manageablility Monitor Light) send out alerts based on configured metrics.
• Different metrics are calculated with different frequency.
  • Disk space is calculated every 10 mins
  • CPU usage is calculated every second

Alert Types

• threshold / stateful – alerts that build up over time and persist until cleared (disk space)
• nonthreshold / stateless – real-time alerts that don’t persist (snapshot too old)

The Alert System Architecture

• The instance accumulates statistics used by the alert system and writes them to disk (AWR) every hour.
• MMON issues alerts to the ALERT_QUE which is visible by many sessions via subscription.
  • The main subscriber to the ALERT_QUE is the DB EM Daemon which displays the alerts and can send out emails, if configured.
• The alert log file is completely separate from the Alert System

Viewing Alert Information

• Alert information can be viewed by querying:
  • DBA_OUTSTANDING_ALERTS
  • DBA_ALERT_HISTORY

Setting Up Notification Rules

• Can be configured through DB Control

Editing Thresholds

• Can be done via DB Control in the Manage Metrics link from the Related Links section or the dbms_server_alert package.
• Examples:
  • dbms_server_alert.tablespace_pct_full
  • dbms_server_alert.operator_ge – greater or equal
  • dbms_server_alert.object_type_tablespace

User Trace Files

• Background trace files are created automatically when a problem occurs while user trace files are created on demand.
• User tracing can be used to detect what SQL a user is running for situations where his session may be running slow.
• Shared servers and web applications may cause tracing problems because sessions are the same for many users. However, it is still possible to trace a session in Shared Server Mode by tracing the SID and SERIAL# that uniquely identify the session.

Instance Level SQL Tracing

• Can be enabled by setting the parameter SQL_TRACE to true and will be output in the USER_DUMP_DEST.
• The files will be named: <SID>_ora_<process_id>.trc
• To find the process id / username of the session that produced a trace file, you can join the V$SESSION & V$PROCESS views.
• SQL> alter system set sql_trace=true;

Session Level SQL Tracing

• Output in USER_DUMP_DEST.
• SQL> execute dbms_monitor.session_trace_enable(session_id=>162,serial_num=>14);
  • To get session_id:
    • SQL> select sid, serial# from v$session where username=’JOHN’;
• SQL> alter session set sql_trace=true; // only works if user is in SQL*Plus

Tracing with DB Control

• From the db control home page, Performance tab -> Top Consumers. You will see activity by session, module, service name, web client ID, or PL/SQL procedure.

Parameters

• BACKGROUND_DUMP_DEST [dyn] – location of the alert log
• USER_DUMP_DEST [dyn] – location of user trace files
• MAX_DUMP_FILE_SIZE [dyn] – defaults to UNLIMITED and is the max size for a dump file (does not apply to alert log)
• SQL_TRACE [dyn] – defaults to FALSE

Tables & Views

• DBA_OUTSTANDING_ALERTS – alerts yet to be cleared
• DBA_ALERT_HISTORY – alerts that have been cleared or were stateless
• DBA_EXTENTS – helps in determining block corruption
• V$ALERT_TYPES – configurable alerts
• V$SESSION – session info that can be used for determining who created user trace files
• V$PROCESS – session info that can be used for determining who created user trace files

Chapter 24 – Using RMAN to Back Up Databases

Intro

• There are four interfaces for using RMAN:
  • DB Control / Enterprise Manager
  • Batch mode / cron
  • Pipe interface
  • RMAN executable

RMAN in Batch Mode

• run {
• allocate channel t1 type sbt_tape;
• …
• backup copy of database delete input;}

The RMAN Pipe Interface

• Uses the PL/SQL package DBMS_PIPE which allows one database session to communicate with another. It does this by creating a data structure called a pipe in the SGA. One session will queue messages in the Pipe while the other will pick up these messages for execution. Output will be redirected to the controlling session.
• To create PIPEs:
  • rman PIPE rmpipe target sys/pass@ocp10g
  • Two pipes will be created:
    • ORA$RMAN_RMPIPE_IN – user input
    • ORA$RMAN_RMPIPE_OUT – user output
  • dbms_pipe.pack & dbms_pipe.send_message will be used by the controlling process.
  • dbms_pipe.unpack & dbms_pipe.receive_message will be used to retrieve output.

Backing Up with the RMAN Executable

Stand-Alone and Job/Command Block Commands

• run {} braces are used for commands that only make sense when run with others
  • run {
  • allocate channel d1 type disk;
  • backup database;
  • }
• Stand alone commands:
  • Connect

  Creating Backup Sets

  • RMAN can back up objects in two ways:
    • Image copy – identical binary copy of file
    • Backupset – format that can only be manipulated by Oracle
      • Controlfile
      • Spfile
      • Datafiles
      • Archive logs
      • Image copies
      • Other backupsets
  • It is quicker to restore from image copies but image copies are bigger and can only be written to disk (not tape).
  • Backup sets can make incremental backups and are the only option when backing up to tape.
  • Backup database creates a backupset with the datafiles, controlfile and spfile.
  • BACKUP DATABASE PLUS ARCHIVELOG causes the following actions to occur:
    • ALTER SYSTEM ARCHIVE LOG CURRENT
    • BACKUP ARCHIVELOG ALL
    • BACKUP WHOLE DB
    • ALTER SYSTEM ARCHIVE LOG CURRENT
    • Backup archive logs generated in steps 3 and 4.
  • Script to backup all archive logs older than 7 days
    • run {
    • allocate channel t1 type sbt;
    • backup as backupset archivelog until time ’sysdate -7′;}
  • Offline backups must be conducted in MOUNT mode. They also will fail if the database crashed or was shutdown aborted.
  • If the archive logs are multiplexed then the following commands will behave differently:
    • RMAN> backup as backupset archivelog all DELETE INPUT;
      • This will delete only archivelog copies in primary destination.
    • RMAN> backup as backupset archivelog all DELETE ALL INPUT;
      • This will delete all multiplexed archivelog copies.

  Making Image Copies

  • Image copies are exact binary copies, equivalent to an OS copy and paste. They cannot be backed up to tape.
  • Image copies can be made of:
    • Datafiles
    • Controlfile
    • Archive logs
    • NOT the spfile
  • Restores from image copies are faster then backupsets because they do not have to be extracted / decompressed.
  • The entire db can be backed up as an image copy:
    • RMAN> backup as copy database;

  Using Tags for Backup Sets and Image Copies

  • A tag is an assigned name to a backupset or a set of image copies.
  • RMAN> backup [as backupset] database tag=weekly;
  • RMAN> delete backupset tag weekly;

  Incremental Backups

  • Incremental backups are very dependent on SCN. Whenever a backup is taken, it is marked in the repository as having been taken at a particular SCN.
  • To determine what needs to be backed up for incrementals, rman only has to compare the scn of the last backup with the current scn of the db to see if data has been added.
  • Incremental backups only apply to datafiles because they rely on SCN.
  • Reasons to use incrementals:
    • Time – incrementals run quicker than a full backup every night with block change tracking enabled
      • If block change tracking is not enabled, then incrementals are not faster during the read but are writing less data to disk which is often the slowest portion (so in that way, it’s faster).
        
    • Space – disk space and tape backup space can be a problem if a full backup occurs every night
    • Impact on end users – the I/O needed for a full backup and disk contention can impact end users
  • Incremental backup levels:
    • Level 0 – full backup that can be used for subsequent incrementals
      • RMAN> backup incremental level 0 database;
    • Differential Level 1 – only the blocks that have changed since the last backup (whether it is level 0 or level 1)
      • RMAN> backup incremental level 1 differential database;
    • Cumulative Level 1 – all changes since the last level 0 incremental backup
      • RMAN> backup incremental level 1 cumulative database;
  • A full backup cannot be used for a cumulative level 1 backup. A cumulative level 1 backup must be done on top of an incremental level 0 backup.

  Combining Incremental Backups and Image Copies

  • It is possible to apply incremental backups to image copies (but not backupsets).
  • One technique would be an image copy once a week and incrementals, nightly.
  • Image copy:
    • run { backup as copy incremental level 0 database tag db_img_copy;}
  • Make incremental, apply it to copy of database, delete incremental:
    • run {
    • backup incremental level 1 for recover of copy with tag db_img_copy database tag db_incr_copy;
    • recover copy of database with tag db_img_copy;
    • delete backupset tag db_incr_copy;}

  Block Change Tracking

  • Incrementals will scan the entire database being backed up in order to detect changes, unless block change tracking is enabled.
  • Block change tracking enables an additional background process called CTWR (Change Tracking WRiter). This process records the address of each block that has been changed so that incrementals will no longer have to scan entire datafiles to find changed blocks.
    • By default, the change tracking file will go to the DB_CREATE_FILE_DEST. It is initially 10M and grows by increments of 10M.
    • SQL> alter database enable block change tracking using file ‘/home/oracle/ctwr.out’;

  Managing and Monitoring RMAN Backups

  LIST

  • RMAN> list backup; // backup sets
  • RMAN> list copy; // image copies
  • RMAN> list backup of database; // whole database backup sets, full or incremental
  • RMAN> list backup of datafile 1;
  • RMAN> list backup of tablespace users;
  • RMAN> list backup of archivelog all;
  • RMAN> list copy of archivelog from time=’sysdate – 2′;
  • RMAN> list backup of archivelog from sequence 1000 until sequence 1050;

  REPORT

  • RMAN> report schema; // “schema” is confusing. this just shows datafiles and has nothing to do with user objects.
  • RMAN> report need backup;
  • RMAN> report need backup days 3;
  • RMAN> report need backup redundancy 3;
  • RMAN> report obsolete;
  • RMAN> report obsolete redundancy 2; // shows backups that are older than two others

  DELETE

  • RMAN> delete obsolete;
  • RMAN> delete obsolete redundancy 2; // delete backups that are older than two others

  Views & Tables

  • These views and data are contained in the control file only (not recovery catalog).
  • V$BLOCK_CHANGE_TRACKING – to monitor block change tracking
  • V$BACKUP_FILES
  • V$BACKUP_SET
  • V$BACKUP_PIECE
  • V$BACKUP_REDOLOG – each archived log that has been backed up
  • V$BACKUP_SPFILE – each spfile that has been backed up
  • V$BACKUP_DEVICE – names of sbt devices
  • V$RMAN_CONFIGURATION

  Parameters

  • DB_CREATE_FILE_DEST – default location for the change tracking file

How to Create a Recovery Catalog in 10g

Create a database with dbca called rcat

C:\> sqlplus SYS/pass@rcat AS SYSDBA

SQL> CREATE SMALLFILE TABLESPACE catalog_tbs DATAFILE ‘F:\ORACLE\..\cat_tbs.dbf’ SIZE 20M AUTOEXTEND ON NEXT 5M UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

SQL> CREATE USER rcat IDENTIFIED BY rcat DEFAULT TABLESPACE catalog_tbs QUOTA UNLIMITED ON catalog_tbs;

SQL> GRANT recovery_catalog_owner TO rcat;

SQL> Grant connect, resource to rcat;

SQL> exit

C:\> rcat catalog rcat/rcat@rcat

RMAN> create catalog;

RMAN> exit

C:\>rman target orcl catalog rcat/rcat@rcat

Recovery Manager: Release 10.2.0.1.0 – Production on Tue Sep 12 21:21:27 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: RCAT (DBID=373135761)
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> report schema;

Chapter 23 – Getting Started with Oracle Recovery Manager

Recovery Manager Features

• Backup of database plus archive logs made since the backup will be able to survive any data loss as long as redo log groups, exist.
• RMAN can backup:
  • Whole databases
  • Tablespaces
  • Datafiles
  • Controlfile
  • Spfile (static pfile cannot be backed up)
  • Archive logs
• Online redo logs are only protected by multiplexing and cannot be backed up.
• RMAN can create incremental backups, image copies or backupsets. Image copies cannot be written to tape.
• RMAN validates the contents of blocks as it scans them which will detect block corruption if it occurs.
• RMAN can be integrated with a tape backup library.

Recovery Manager Components

The RMAN Executable

• Can be found in $ORACLE_HOME/bin and is named rman
• RMAN connects to a db via a listener and server process, prompts for user commands and sends the commands to the instance to be executed.
• RMAN can be run remotely so long as the versions of the RMAN executable and target database, match.
• RMAN is implemented with the PL/SQL package DBMS_BACKUP_RESTORE. Since this package is kernelized, or part of the instance (stored in the SGA), you don’t need an open database to use RMAN. Other PL/SQL procedures are stored in the Data Dictionary, which means that you need an open database to use them.

The Target Database

• The target database is the db that you intend to backup, or restore and recover and RMAN can only connect to one target at a time.
• The controlfile of the target db stores information needed by RMAN:
  • Location of the datafiles
  • Backups that have been made in the past
  • RMAN’s persistent config settings
• RMAN also writes to the controlfile of the target database to tell where the backups are written.
• Still, if the target db is totally destroyed, including all copies of the controlfile, it will still be possible for RMAN to restore it, provided that RMAN has been configured correctly.

Server Processes and Channels

• Whenever a backup or restore operation is initiated, then at least one “channel” is launched. Multiple channels will allow for parallelism.
  A channel is a server process that does the work of copying files.
  • A channel can be launched manually via ALLOCATE CHANNEL or RMAN can launch channels automatically by using configured settings.
• Two types of channel processes exist:
  • Disk channels – backup or restore to disk
  • SBT channels – backup or restore to tape
• Another server process is the polling process. This process monitors the progress of the backup or restore operation.

The RMAN Repository

• The repository is always stored in the controlfile of the target database. There is also the option to store it in the Recovery Catalog database, as well.
• The repository in the controlfile / Recovery Catalog tells:
  • Location of the datafiles
  • Backups that have been made in the past
  • RMAN’s persistent config settings
• If the repository is lost, then it may be possible to rebuild it if the proper precautions have been taken.

The Recovery Catalog

• The RMAN repository is always written to the target db’s controlfile, but it can also be written out to a separate Oracle database, called the Recovery Catalog.
• Benefits of a Recovery Catalog
  • Enhances functionality
  • You are no longer dependent on the controlfile of the target db. What if controlfile is damaged / lost?
  • The Recovery Catalog can store RMAN scripts.
  • One Recovery Catalog can be used to store metadata for multiple databases.
  • There is no time limit to how long a Recovery Catalog can store metadata. The controlfile relies on the parameter: CONTROL_FILE_RECORD_KEEP_TIME.

Media Management Library

• To use a tape library, the instance must be linked to a device driver supplied by the tape library vendor.
• Once the driver is linked, it will be possible to launch channels of type SBT_TAPE.
• The Oracle Backup Solutions Program (BSP) certifies tape products as being compatible with RMAN.

The Auxiliary Database

• An auxiliary database is a new database created by RMAN from a backup of the target database.
• The auxiliary db can be a complete restore of the target (identical copy) or a subset of the target (TSPITR – tablespace point in time recovery – where one tablespace is pushed back while the remainder of the db is the same).
• An auxiliary db can also be used to create a standby db. A standby db is an exact replica, stored remotely which is continuously updated from the redo generation of the production db. In the event of a disaster, you can switch over to the standby db.

The Flash Recovery Area

• The flash recovery area is used for all recovery and fault tolerance related files. RMAN uses is as a default location for backups. It is also used for redo logfiles, controlfiles, archivelogs and flashback.

Environment Variables and Instance Parameters

• Environment variables like nls_date_format and nls_lang can effect RMAN decisions.
  • If the db is in mount mode then RMAN will determine settings from the controlfile.
  • If the db is in nomount or closed then it will take the values from the environment variables.
• Three parameters within the instance may be of significance:
  • CONTROL_FILE_RECORD_KEEP_TIME – not very important if using the recovery catalog. The data will be transferred from controlfile to recovery catalog whenever RMAN connects to the target. It uses the RESYNC CATALOG command.
  • DB_RECOVERY_FILE_DEST – location of the flash recovery area. RMAN can apply retention policies to files in this area.
  • DB_RECOVERY_FILE_DEST_SIZE – if it fills up, then the db will hang.

Connecting to Databases

• The rman executable can connect to local dbs or remote dbs
  • C:\> rman target sys/oracle@ocp10g catalog rman/rman@catdb
  • … is the same as …
  • C:\> rman
  • RMAN> connect target sys/oracle@ocp10g
  • connected to target database OCP10G (DBID=54237432)
  • RMAN> connect catalog rman/rman@catdb
  • connected to Recovery Catalog database

Configuring Persistent Settings

• To see the current RMAN persistent settings type: SHOW ALL.

Channel Allocation

• At least one channel must be allocated before a backup or restore can be executed.
• Channels can be allocated for each backup/recovery operation or they can be configured with default settings for all operations.
• Control Options:
  • Connect – the instance to which RMAN connects
  • Duration – controls the amount of time for the job, specified in hours and minutes
    • Minimize time – tells the job to run at full speed
    • Minimize load – tells the job to slow down if it is going to finish within the duration
    • Partial – allows RMAN to terminate a job that hasn’t finished within the duration without signalling an error
  • Format – path and filename with variables like %u or %d.
  • Maxopenfiles – limits number of input files that RMAN can open at once. default is 8.
  • Maxpiecesize – specified in bytes (default), kilobytes (k), megabytes (m) or gigabytes (g). restricts the size of pieces that the backup set is divided into
  • Parms – used to set variables for SBT_TAPE channel

What RMAN Will Back Up

• RMAN can exclude certain dbs by using exclude:
  • RMAN> configure exclude for tablespace example;
• RMAN will also exclude dbs that have not changed since the last backup if optimization is turned on.
  • RMAN> configure backup optimization on;
• If RMAN is not configured to backup the controlfile and all copies are lost, then the target db will never get past the NOMOUNT state and recovery will be impossible.

Configuring Retention of Backups

• The default configuration is redundancy 1 which means that RMAN will always attempt to have one backup of every datafile, archive log and controlfile. Delete obsolete will delete all datafile & controlfile copies but not archive logs.
• It is also possible to set the retention policy to none which will disable automatic deletion aka obsolete files.

Overriding Configured Defaults

• Defaults can be overridden inside of run braces { … }

Parameters

• CONTROL_FILE_RECORD_KEEP_TIME – how long the controlfile will keep metadata. Defaults to 7 days.

Views & Tables

• g

Chapter 21 – Managing Globalization in Oracle Databases

• NLS stands for National Language Support but it entails much more than just language.

Globalization Capabilities

Character Sets

• Some applications (MS Word) use the character sets provided with the OS, while Oracle provides its own character sets which means that it is the same on all platforms.
• Single-byte character sets use only one byte (8 bits or less) per character.
• Multi-byte character sets use one, two or three bytes per character.
• Fixed width character sets use the same number of bytes for each character.
• Varying width character sets use a different number of bytes per character.
• Single-byte seven-bit – uses a single byte per character but only 7 of the 8 bits per character
  • 2^7 total possible characters (128)
  • Usually inadequate
  • Oracle default (US7ASCII)
• Single-byte eight-bit – uses a single byte per character and 8 bits per character
  • 2^8 total possible characters (256)
  • Adequate for Western European languages but perhaps not Eastern Europe and definitely not Asian languages (ex. WE8ISO8859P15)
• Fixed-width multibyte
  • Unicode (AL16UTF16)
• Varying-width single-byte
  • Characters may be from one to four bytes
  • Unicode (UTF8)
• Varying-width multibyte
  • May use shift-out code to indicate that the following bytes are double-byte characters and shift-in code to indicate single-byte characters.

Language Support

• The number of languages depends on platform, release & patch level.
• The view V$NLS_VALID_VALUES shows all possible languages (American, German, French, Canadian French, etc.)
• The parameters NLS_LANGUAGE, NLS_DATE_LANGUAGE & NLS_SORT will determine error message language, date language & sort order, respectively.
  • SQL> select * from v$nls_valid_values where parameter=’SORT’;
• BINARY is the default value for NLS_SORT and is poor for any language besides English. It simply sorts by the ASCII numeric value of the character set.

Territory Support

• The territory selected sets a number of parameter defaults for day and week numbering, credit and debit symbols, date formats (nls_date_format), decimal and group numeric separators (nls_numeric_characters), and currency symbols (nls_currency).

Globalization Support Features

• Can be specified at any and all of five levels:
  • Database
  • Instance
  • Client environment
  • Session
  • Statement

Choosing a Character Set

• The character set is used to store data in columns of type: VARCHAR2, CLOB, CHAR and LONG. Changing the character set can destroy data in these columns.
• The National Character Set is a secondary character set specified at database creation time. It is used for columns of datatype: NVARCHAR2, NCLOB and NCHAR.
  • Must be either AL16UTF16 or UTF8. Other unicode sets are not supported.
• The only limitation on the database character set is that it must have either US7ASCII or EBCDIC as a subset since SQL and PL/SQL are stored in these char sets.
• The default character set is US7ASCII and the default national character set is AL16UTF16 (must be AL16UTF16 or UTF8).

Changing Character Sets

• Two tools exist:
  • Database Character Set Scanner (csscan)
    • csscan system/passwd full=y tochar=utf8
    • checks database to see if problems may arise
    • one possible problem is that the new char set may require more bytes and therefore the new data won’t fit in the column
    • the output will show every row that will have problems with the new char set
  • Language and Character Set File Scanner (lcsscan)
    • checks text files to see what language the data is in
    • must be a straight text file
• To change Character Sets of the DB
  • SQL> ALTER DATABASE CHARACTER SET …
  • SQL> ALTER DATABASE NATIONAL CHARACTER SET …
  • The new Character Set must be a superset of the old set but this does not guarantee no corruptions

Globalization Within the Database

• SQL> select * from nls_database_parameters;

Globalization at the Instance Level

• Instance parameter settings will override the database settings.
• NLS_INSTANCE_PARAMETERS show the settings just like NLS_DATABASE_PARAMETERS

Client-Side Environment Settings

• C:\> set nls_lang=Hungarian
• It may be dangerous for clients to set their own NLS settings.
  • Conversion may not be possible: Asian -> US7ASCII
• The conversion between client-side and server-side is done by Oracle Net.

Session-Level Globalization Settings

• A user can set their NLS settings in two ways:
  • SQL> alter session set nls_date_format=’dd.mm.yyyy’;
  • SQL> execute dbms_session.set_nls(‘nls_date_format’,”‘dd.mm.yyyy’”);

Statement Globalization Settings

• The tightest level of control over Globalization is to manage it from within each statement.
  • SQL> select to_char(hiredate,’Day dd, Month YYYY’,'nls_date_language=dutch’), to_char(hiredate, ‘Day dd, Month YYYY’,nls_date_language=german) from scott.emp;
• Statement conversion choices:
  • to_date – nls_date_language, nls_calendar
  • to_number – nls_numeric_characters, nls_currency, nls_dual_currency, nls_iso_currency, nls_calendar
  • to_char, to_nchar – nls_date_language, nls_numeric_characters, nls_currency, nls_dual_currency, nls_iso_currency, nls_calendar

Linguistic Sorting and Selection

• Oracle’s default sort order is binary, which converts characters to their ASCII codes and sorts them accordingly. This is suitable for American english but not for other languages.
• Linguistic sorting means that Oracle will replace each character with a numeric value that represents its sorting value for the chosen language.
• All nls_sort options can be suffixed with _AI or _CI for accent insensitive or case insensitive
  • SQL> alter session set nls_sort=FRENCH_CI;

The Locale Builder

• A graphical tool to create customized Globalization environments
  • $ORACLE_HOME/nls/lbuilder/lbuilder

Using Time Zones

• The Oracle environment can be made time zone aware by using datatypes:
  • TIMESTAMP WITH TIME ZONE – stores the timestamp and time zone it refers to
  • TIMESTAMP WITH LOCAL TIME ZONE – timestamp is converted to db and converted to user’s local time zone on retrieval
• DATE and TIMESTAMP are always normalized to the database time zone and displayed unchanged when retrieved

Parameters

Language Support

• NLS_LANGUAGE – defaults to AMERICAN
• NLS_DATE_LANGUAGE – defaults to AMERICAN
• NLS_SORT – defaults to BINARY which is a poor choice for many languages

Territory Support

• NLS_TERRITORY – defaults to AMERICA
• NLS_CURRENCY – defaults to $
• NLS_DUAL_CURRENCY – defaults to $ (secondary currency for territory)
• NLS_ISO_CURRENCY – defaults to AMERICA
• NLS_DATE_FORMAT – defaults to DD-MM-RR
• NLS_NUMERIC_CHARACTERS – defaults to ., (decimal and comma)
• NLS_TIMESTAMP_FORMAT – defaults to DD-MM-RRHH.MI.SSXFF AM (for columns of type timestamp)
• NLS_TIMESTAMP_TZ_FORMAT – defaults to DD-MM-RRHH.MI.SSXFF AM TZR (for columns of type TIMESTAMP WITH LOCAL TIMEZONE)

Other NLS Settings

• NLS_CALENDAR – defaults to Gregorian
• NLS_COMP – defaults to BINARY (comparison of letters by binary value)
• NLS_LENGTH_SEMANTICS – defaults to BYTE (column length specified in bytes – not chars)
• NLS_NCHAR_CONV_EXCP – defaults to false (won’t show error messages when conversions between different types fail – VARCHAR2 -> NVARCHAR)

Views & Tables

• V$NLS_VALID_VALUES
• V$NLS_PARAMETERS – Globalization settings currently in effect for your session
• V$TIMEZONE_NAMES -list of supported time zones
• NLS_DATABASE_PARAMETERS – shows current values of all NLS settings at db level
• NLS_INSTANCE_PARAMETERS – shows current values of all NLS settings at instance level
• NLS_SESSION_PARAMETERS – shows current values of all NLS settings at session level

Chapter 36 – Automating Administrative Tasks

The Scheduler Architecture

• The DBA_SCHEDULER_JOBS view in the data dictionary holds info for all scheduled jobs.
• The background process CJQ0 monitors the table and launches other processes – Jnnn – to run the jobs.
• The Jnnn process relies on the parameter JOB_QUEUE_PROCESSES, which defaults to 0. If it is set to 0, then jobs won’t run.

Scheduler Objects

Jobs

• A job specifies what to do and when to do it.
• The what can be:
  • Single SQL statement
  • PL/SQL Block
  • PL/SQL Stored Procedure
  • Java stored procedure
  • An external procedure
  • An executable or script on the OS
• The when can be:
  • A pre-defined schedule
  • A pre-defined window
  • An event
  • A time
• Jobs can be created via the dbms_scheduler.create_job procedure
  • All forms of the create_job procedure require job_name
  • Other options:
    • job_type
    • plsql_block
    • stored_procedure
    • executable
    • job_priority (defaults to 3 but can be 1 – 5, with 1 being highest priority)
    • job_action
    • auto_drop (says to execute the job only once – it’s the default if no scheduling info exists) …

Programs

• Programs are like pulling the what out of a job
• Created with the dbms_scheduler.create_program procedure
• Arguments
  • program_name
  • program_type
  • program_action
  • number_of_arguments
  • enabled
  • comments

Schedules

• Schedules are like pulling the when out of a job
• Created with the dbms_scheduler.create_schedule procedure
• Arguments
  • schedule_name
  • start_date
  • repeat_interval
  • end_date
  • comments
• Repeat Interval examples:
  • repeat_interval => ‘freq=hourly; interval=12′
  • repeat_interval => ‘freq=yearly; bymonth=jan,apr,jul,oct; bymonthday=2′
  • repeat_interval => ‘freq=weekly; interval=2; byday=mon; byhour=3; byminute=12′

Job Classes

• A job class is used to associate jobs with consumer groups. This will allow jobs to be run with specified resources, etc. It also controls logging.
• Arguments
  • job_class_name
  • resource_consumer_group
  • service (RAC only – service name of instance to run on)
  • logging_level
  • log_history
  • comments
• Created with dbms_scheduler.create_job_class

Windows

• A window adds functionality to the schedule by giving Oracle a window of time in which to run a job. Jobs scheduled within a window can be run at any time which is chosen by Oracle’s discretion.
• It is possible to nominate a RESOURCE_PLAN to start when the window begins as well as a WINDOW_PRIORITY (HIGH | LOW) to manage overlapping windows.
• It is possible to combine windows into window groups for ease of administration.
• Defaults are WEEKEND_WINDOW & WEEKNIGHT WINDOW.
• Windows cannot have the same name as schedules because they share the same namespace.
• Arguments
  • window_name
  • resource_plan
  • start_date
  • repeat_interval
  • end_date
  • duration
  • window_priority (either high or low)
    • if two windows have the same priority, then the one opened first will have priority
  • comments
• The duration is of the form:
  • ‘days hours:minutes:seconds’ // ‘0 3:30:00′

Privileges

• Example
  • GRANT CREATE ANY JOB TO HR;
• List of privs:
  • CREATE JOB (a user can create his own jobs)
  • CREATE ANY JOB (a user can create jobs in other schemas)
  • EXECUTE ANY PROGRAM
  • EXECUTE ANY CLASS
  • MANAGE SCHEDULER
  • EXECUTE ON <job, program or class>
  • ALTER ON <job, program or schedule>
  • ALL ON <job, program, schedule or class>

Using Programs and Schedules

• After creating a program or job via create_program or create_job, you will also need to enable it (since it is disabled by default)
  • SQL> exec dbms_scheduler.enable(‘prog1′);
• Programs cannot have the same names as jobs because they share the same namespace.
• A job can be executed without a schedule manually:
  • SQL> exec dbms_scheduler.run_job(’savedate’);

Using Classes, Windows and the Resource Manager with Jobs

• Jobs can be configured with the following components:
  • Job classes – jobs are assigned a class (logging)
  • Consumer groups – classes are assigned to consumer groups (CPU, active session pool)
  • Resource plans – consumer groups are part of a resource plan (only one resource plan active at a time)
  • Windows – resource plans and jobs will be active during windows
  • Window groups – windows can be combined into groups for administration purposes

Preconfigured Jobs

• There are two jobs configured by default:
  • PURGE_LOG
  • GATHER_STATS_JOB

Packages

• DBMS_SCHEDULE.
  • CREATE_JOB
  • CREATE_PROGRAM
  • CREATE_SCHEDULE
  • CREATE_JOB_CLASS
  • CREATE_WINDOW

Processes

• CJQ0 – launches Jnnn processes when they are scheduled to run
• Jnnn – each process runs a job (ex. J0001, J0002, …)

Tables & Views

• DBA_SCHEDULER_JOBS – shows scheduled jobs info

Parameters

• JOB_QUEUE_PROCESSES – defaults to 0 which means that jobs won’t run.
• RESOURCE_MANAGER_PLAN – current plan for the instance

Chapter 35 – Managing Oracle Database Resources

The Resource Manager Architecture

• Users are placed in Consumer Groups and Resource Plans to control and manage the allocation of resources across the groups.
• It is configured by default.

Consumer Groups

• Set of users with similar resource requirements
• A user can be a member of multiple groups
• At any given moment, a user session can only have one active group
• Requires a pending area before it can be created.
• There are 5 default groups:
  • SYS_GROUP – only SYSTEM user by default
  • DEFAULT_CONSUMER_GROUP – all users not assigned to group
  • OTHER_GROUPS – all users are member of this group
  • LOW_GROUP – Intended for low-priority sessions
  • AUTO_TASK_CONSUMER_GROUP – intended for running system maintenance jobs

Resource Manager Plans

• Default plans:
  • INTERNAL_PLAN – gives the OTHER_GROUPS 0% of CPU at all levels (equal access to all users). This is the default plan
  • SYSTEM_PLAN
    • 100% to SYS_GROUP at level 1
    • 100% to OTHER_GROUPS at level 2
    • 100% to LOW_GROUP at level 3
  • INTERNAL_QUIESCE – freezes all sessions out (by setting max number of sessions to 0) except for SYS_GROUP

Resource Manager Configuration Tools

• Two packages make up the Resource Manager API:
  • DBMS_RESOURCE_MANAGER_PRIVS – give resource manager privs to users or switch user to another group (GRANT[/REVOKE]_SWITCH_CONSUMER_GROUP or GRANT[/REVOKE]_SYSTEM_PRIVILEGE)
    • dbms_resource_manager_privs.grant_system_privilege(‘Mike’,'ADMINISTER_RESOURCE_MANAGER’,false)
  • DBMS_RESOURCE_MANAGER – create consumer groups, plans and directives (many functions available)
    • dbms_resource_manager.create_consumer_group(‘grp1′,’comment’)
    • a pending area (in the SGA) must be created before the group is added to make sure it is possible (ex. pct’s add up to 100). If the plan is valid, then it is saved to the Data Dictionary.
    • a pending area is not needed to put users into groups.
  • There is no need to add a user to group (only switch) since all users are members of OTHER_GROUPS on creation

Resource Manager Plans

• All plans must include a directive for OTHER_GROUPS or it will not be validated
• Methods to control consumer groups:
  • CPU
    • CPU uses emphasis method b/c it will vary depending on use
  • Number of active sessions
  • Degree of parallelism
  • Operation execution time
  • Idle time
  • Volume of undo data
    • All besides CPU use absolute limits which are not variables
  • Auto consumer group switching (execution time combined with CPU)
• Steps to create a plan:
  • Create pending area
    • SQL> exec dbms_resource_manager.create_pending_area;
  • Create plan
    • SQL> exec dbms_resource_manager.create_plan(plan=>’daytime’,comment=>’reg plan’);
  • Create directives
    • SQL> exec dbms_resource_manager.create_plan_directive(plan=>’daytime’, group_or_subplan=>’sys_group’, cpu_p1=>100);
    • … more directives …
  • Validate pending area:
    • SQL> exec dbms_resource_manager.validate_pending_area;
  • Submit pending area:
    • SQL> exec dbms_resource_manager.submit_pending_area;
  • Activate plan
    • SQL> alter system set resource_manager_plan=daytime;

CPU Method

• There are 8 possible priority levels
• It is impossible to allocate more than 100% of the CPU because the plan will not be saved. It is possible to allocate less than 100%, but there is little point in doing so.
• Another option is to make the group the plan and have other groups as subplans in a heirarchical fashion.

The Active Session Pool Method

• Limits the number of active sessions (not connections!!!). An active session is a session that is running a query or has an uncommitted transaction.
• If parallel processing is enabled, one operation still counts as only one
• By default, a session will be queued indefinitely but a timeout can be set

Limiting the Degree of Parallelism

• Can be used on single- or multi-processor machines.
• To enable parallel processing:
  • PARALLEL_MAX_SERVERS – must be set to create a pool of parallel execution servers
  • Enable parallelism for each table with ALTER TABLE <table_name> PARALLEL
  • Enable parallel DML for your session with ALTER SESSION ENABLE PARALLEL DML
  • Either set PARALLEL_AUTOMATIC_TUNING=TRUE or specify parallelism in each statement
• You can limit the number of parallel processors that each group has access to.

Controlling Jobs by Execution Time

• Jobs are estimated at how long they will run. If optimizer statistics are incorrect, then a job can execute when it wasn’t supposed to (too long).
• Jobs will not timeout. They will either run or be cancelled.

Terminating Sessions by Idle Time

• You can disconnect sessions by idle time or idle blocker time.
• Idle blocker time is how long there have been locks on data.

Restricting Generation of Undo Data

• The UNDO tablespace runs the risk of filling up if programmers poorly design queries.
• You can restrict groups on how much UNDO they are allowed to generate.
• When a group reaches its UNDO limit, the sessions will hang until a transaction is committed, freeing up UNDO space.

Automatic Consumer Group Switching

• You can force a group to switch privileges if their query runs too long.

Additional Features

Quiescing the Database

• Quiescing the database forces all groups besides the SYS_GROUP, to hang. It does so by activating the INTERNAL_QUIESCE plan
  • SQL> alter system quiesce restricted
  • SQL> alter system unquiesce
• The quiesce will only work if a resource plan has been active since startup. The plan must be specified in the s/pfile in order for this to be true

Consumer Group Switching for One Call

• Automatic consumer group switching can only be done via SQL execution time.
• Automatic consumer group switching via Resource Manager is permanent for the session, unless switched back manually.
  • Users can switch their sessions back via procedure in dbms_session or sys can use dbms_resource_manager.switch_consumer_group_for_sess
• Using the dbms_resource_manager API, you can switch consumer groups only for the duration of the call.
  • switch_time_in_call => — automatically switch back to the original consumer group after n seconds

Use of the Ratio CPU Method

• Instead of specifying the percent of CPU allowed to each group, you can simply use ratios between the groups.
• This is helpful when adding new groups because you won’t have to redistribute the the percentages.
• SQL> DBMS_RESOURCE_MANAGER.CREATE_PLAN (PLAN => ’service_level_plan’, CPU_MTH -> ‘RATIO’, COMMENT => ’service level plan’);
  • CPU_MTH can also be ‘EMPHASIS’ which is default

Create a Simple Resource Plan

• exec dbms_resource_manager.create_simple_plan(
  simple_plan=>’plan1′,
  consumer_group1=>’oltp’, group1_cpu=>50…)
• No need for pending_area.
• Level 1 is preconfigured at 100% for SYS_GROUP so all configs start at level 2.
• Bottom level is configured for OTHER_GROUPS at 100%.

Adaptive Consumer Group Mapping

• The two methods EXPLICIT & ORACLE_USER are available by default for determining the user’s consumer group
• Other methods available
  • EXPLICIT – switch groups via command to switch either code or Resource Plan
  • SERVICE_MODULE_ACTION – switch groups depending on service name used to connect, module and action of the code that is being executed. The programmers must embed these names within the code.
  • SERVICE_MODULE – switch groups depending on service name used to connect and program module
  • MODULE_NAME_ACTION – switch groups depending on program module and action being executed
  • MODULE_NAME – switch groups depending on program module
    • For all module or action methods, programmers must embed calls to DBMS_APPLICATION_INFO
  • SERVICE_NAME – switch groups depending on service name used to connect
  • ORACLE_USER – activate group at login depending on Oracle User ID
  • CLIENT_PROGRAM – switch groups depending on user process used to connect
  • CLIENT_OS_USER – switch groups depending on OS username
  • CLIENT_MACHINE – switch groups depending on name of client machine

Views & Tables

• V$SESSION – the column CURRENT_QUEUE_DURATION will show the duration that each queued session has been waiting
• V$RSRC_CONSUMER_GROUP – shows current session info for all consumer groups

Parameters

• RESOURCE_MANAGER_PLAN – used to enable a plan. By default it is not set, which means that INTERNAL_PLAN is the plan in effect.
• PARALLEL_MAX_SERVERS – used to enable parallelism on queries and DML

Chapter 34 – Monitoring and Managing Memory

The System Global Area

• SGA components are sized in granules. A granule is an area of contiguous memory.
  • The exception to this is the log buffer, which is not sized in granules.
  • Regardless of what size you specify for a particular SGA component, it will be rounded up to the next whole granule.
    • The exclusion is again the log buffer
• Made up of the following required components:
  • Shared Pool
  • Log Buffer
  • Db Buffer Cache Default Pool

• Following are optional requirements:
  • Large Pool
  • Streams Pool
  • Java Pool
  • DB Buffer Cache Keep Pool
  • DB Buffer Cache Recycle Pool
  • DB Buffer Cache nK Block Size Pools
• LOG_BUFFER and SGA_MAX_SIZE are two of the only static SGA parameters.

The Shared Pool

• Sized according to the SHARED_POOL_SIZE parameter. If set too low, the instance will not start.
• Has 36 components which are sized automatically.
  • SQL area & Library Cache – recently executed SQL statements & execution plan
  • Data Dictionary Cache (aka row cache) – data dictionary info being used to parse SQL statements
  • Active Session History – recent activity by users which is flushed to the AWR
• There is a Shared Pool Advisor to determine how large it should be.

The Database Buffer Cache

The LRU List and the Checkpoint Queue

• Every buffer in the db buffer cache will be in one of three states: pinned, dirty or free.
  • A pinned buffer is a buffer that is in use.
  • A dirty buffer is one that has not yet been flushed to disk.
  • A free or clean buffer is when the block has been copied into the cache and not yet changed or it has been changed and already flushed to disk.
• There should be many free buffers in the cache at any time. The decision on which buffer to use will be determined by the LRU List. The oldest accessed block will be overwritten.
• Another list is the Checkpoint Queue. This is a list of dirty buffers waiting to be written to disk by DBWn.
• The process that searches for free buffers in the LRU List also notifies the Checkpoint Queue of dirty buffers and adds them to the list. The LRU blocks will be written to disk first.
• When data gets written to disk:
  • A server process takes too long to find a free buffer.
  • The checkpoint queue is too long.
  • Every 3 seconds, DBWn writes dirty buffers to disk.
• The only time that all dirty buffers get written to disk is when a checkpoint occurs (ex. SHUTDOWN, or alter system checkpoint).
• Tablespace checkpoints occur:
  • tablespace is dropped
  • tablespace made read-only
  • tablespace put into hot backup mode
  • tablespace is taken offline
• Log switches do not trigger a checkpoint.

The Default, Keep and Recycle Pools

• Each pool within the DB buffer cache has its own LRU list and checkpoint queue.
• The default pool is the only required pool and has its size determined by DB_CACHE_SIZE.
• To ensure that blocks of segments that are constantly used are always in memory, create a db buffer cache “keep” pool by setting the DB_KEEP_CACHE_SIZE parameter.
  • SQL> alter index emp_name_idx(storage buffer_pool keep);
• To ensure that blocks of segments that are unlikely to be reaccessed are pushed out of memory as quickly as possible, create a db buffer cache “recycle” pool by setting the DB_RECYCLE_CACHE_SIZE parameter.
  • SQL> alter table emp(storage buffer_pool recycle);

The Nonstandard Block Size Pools

• The DB_BLOCK_SIZE parameter is used for formatting the SYSTEM and SYSAUX and temporary tablespace datafiles. It can NEVER be changed.
• In order to create tablespaces with different block sizes you must set the appropriate db buffer cache pools:
  • DB_2K_CACHE_SIZE
  • DB_4K_CACHE_SIZE
  • DB_8K_CACHE_SIZE
  • DB_16K_CACHE_SIZE
  • DB_32K_CACHE_SIZE
  • You cannot set the above parameter if it is already your default. It will throw an error.
• By creating additional buffer pools, you will be able to copy tablespaces between databases with different block sizes.
  • SQL> alter system set db_16k_cache_size=4m;
  • SQL> create tablespace ts_16k datafile ‘ts_16k.dbf’ size 400m blocksize 16k;
• You cannot create a tablespace of nonstandard block size that is of type temporary.
• The keep and recycle buffers can only be of the default block size.

Sizing the Database Buffer Cache

• The cache should be large enough to cache blocks that are repeatedly accessed, but not so large that it caches blocks that are used only once.
• The Database Buffer Cache Advisor is available via V$DB_CACHE_ADVICE

The Log Buffer

• Short term staging area for changes before they are written to the redo logs.
• LGWR writes to disk:
  • When a user commits a transaction
  • When the log buffer is 1/3 full (even if no commit has been issued)
  • Right before DBWn, writes (redo logs must have data before datafiles)
  • Every three seconds
• The LOG_BUFFER parameter determines the size

Other SGA Areas

The Large Pool

• The purpose of the large pool is to reduce strain on the shared pool
• Will be used instead of the shared pool for the following:
  • Shared Server configuration – UGAs will be stored in the large pool instead of shared
  • Parallel execution servers
  • I/O slave processes
  • RMAN
• Size is determined by the dynamic LARGE_POOL_SIZE parameter.
• To monitor usage, view V$SGASTAT

The Java Pool

• Its purpose is to provide room for the runtime memory structures used by a Java application
• It’s technically “optional” but from a practical standpoint, necessary.
• Controlled by three instance parameters:
  • JAVA_POOL_SIZE – dynamic; creates the Java pool and is limited by SGA_MAX_SIZE. Default is 24MB.
  • JAVA_MAX_SESSIONSPACE_SIZE – static; max space for any one session
  • JAVA_SOFT_SESSIONSPACE_LIMIT – static; if a session’s Java pool goes above this limit then a message is written to a trace file

The Streams Pool

• Stores changes that exist in the redo logs in order to propagate changes to other databases at remote sites.
• The changes from the redo logs are physical change records and can only be directly applied to datafiles, not applied logically to tables.

Automatic Shared Memory Management (ASMM)

• ASMM manages and auto-tunes the following components if they are set to zero or not set (if SGA_TARGET has a value):
  • DB buffer cache default pool, DB_CACHE_SIZE
  • Shared pool, SHARED_POOL_SIZE
  • Large pool, LARGE_POOL_SIZE
  • Java pool, JAVA_POOL_SIZE
• If the parameters have been set then Oracle will not lower the values below the setting
• These structures must be set manually:
  • DB buffer cache keep pool, DB_KEEP_CACHE_SIZE
  • DB buffer cache recycle pool, DB_RECYCLE_POOL_SIZE
  • DB buffer cache nonstandard block size pools, DB_nK_CACHE_SIZE
  • Streams pool, STREAMS_POOL_SIZE
  • Log buffer, LOG_BUFFER
• To enable ASMM set the parameter SGA_TARGET
• ASMM will not work if the STATISTICS_LEVEL is set to BASIC. It needs to gather stats in order to monitor and adjust the SGA.
• Use of an spfile allows Oracle to remember the values of the various components for the next startup.
• You cannot change the SGA_TARGET to a value greater than SGA_MAX_SIZE while the instance is running (you will get an error). However, if set in the spfile, then the SGA_MAX_SIZE will be adjusted to SGA_TARGET on startup.
• ASMM starts an additional background process called MMAN. It controls the tuning to the various SGA components.

The Program Global Area (PGA)

• The PGA stores information private to a particular session. It is created at the start of each session and a separate PGA (each of variable sizes) exists for each session.
• Information contained in the PGA is:
  • Sort space for sorting, joining, and aggregating rows (perhaps most important)
  • Bind information
  • Session variables
  • Stack space
  • Cursors
• The UGA is a part of the PGA.
• Two important parameters exist for the PGA:
  • WORKAREA_SIZE_POLICY – [AUTO | MANUAL] Should be set to AUTO
  • PGA_AGGREGATE_TARGET – Total amt of memory used for all PGAs (defaults to 20% of the SGA size)
  • • If all memory in PGA_AGGREGATE_TARGET is used up, then Oracle will take memory from one PGA and distribute it to another
  • • In the rare case that there is no memory that can be pulled away, the session will either use temporary space or break the PGA_AGGREGATE_TARGET setting.
• V$PGASTAT & V$PGA_TARGET_ADVICE are views used to monitor and size the PGA.
• If the WORKAREA_SIZE_POLICY is set to MANUAL, the PGA will rely on the following parameters (if set to AUTO the following will be ignored):
  • SORT_AREA_SIZE
  • HASH_AREA_SIZE
  • BITMAPMERGE_AREA_SIZE
  • CREATE_BITMAP_AREA_SIZE
  • The problem with MANUAL policy is that PGA will grow but never shrink. Oracle will be unable to reassign memory between PGAs.

Parameters

• SGA_MAX_SIZE – SGA can’t exceed this limit
• SHARED_POOL_SIZE
• STATISTICS_LEVEL – must be set to TYPICAL in order to use V$SHARED_POOL_ADVICE
• DB_CACHE_SIZE – size of db buffer cache default pool
• DB_KEEP_CACHE_SIZE – size of db buffer cache keep pool
• DB_RECYCLE_CACHE_SIZE – size of db buffer cache recycle pool
• LOG_BUFFER – size of the log buffer in the SGA
• CPU_COUNT – (depending on the value) determines the size of the LOG_BUFFER
• LARGE_POOL_SIZE – size of the large pool
• WORKAREA_SIZE_POLICY – PGA policy which should be set to AUTO
• PGA_AGGREGATE_TARGET – Total amt of memory used for PGAs (defaults to 20% of the SGA size)

Views & Tables

• V$SGASTAT – sizes of various SGA components
• V$SGAINFO – see granule size
• V$SHARED_POOL_ADVICE – help size your shared pool
• V$DB_CACHE_ADVICE
• V$PGASTAT – see current allocations on the PGA
• V$PGA_TARGET_ADVICE – similar to other advice views; has factor field and predictions about effects