Oracle Architecture and Metrics

Here is an illustrated Nutshell FAQ on Oracle explained in-depth using a hands-on approach and constructivist sequence. This crystalizes stacks of books, several classes, thousands of user group entries, and years of hard-won experience.

Topics this page ... Authorization Methods Background Processes Catalog.sql Checkpoint Process (CKPT) Clusters in Tables Control Files Datafiles Data Dictionary Views DSS (Decision Support System) Enqueues Extents Fragmentation Instances Latches Library Cache Log Writer (LGWR) Memory areas OLTP (On-Line Transaction Processing) Packages Parameter File Password Files Performance Views Process Monitor (PMON) Program Global Area (PGA) Processes RMAN Redo Logs Rollback Segments Sequences SID (System Identifier) Shared Pool Shared SQL Pool Sorting Synonyms Server Services Server Manager utility System Global Area (SGA) System Monitor (SMON) Triggers Views

Site Map  
About this site  

Overview: Architecture Core Components

The Oracle Database Management System (DBMS) consists of several components (loosely similar to UNIX's implementation of the multitasking operating system):

• Oracle core software and scripts installed in the folder specified in $ORACLE_HOME environment variable and in Windows Registry.
• Clients establishing sessions connecting to user applications by going through interfaces using Protocols.
• “Databases” have two parts:
  • Instances (the “brain" of the system) provide the capability of accessing stored data. Each instance consists of:
    • data files in static storage devices,
    • memory areas in RAM used by
    • background processes servicing requests and user applications
• Development and Query Tools managing the DBMS such as the Enterprise Manager and The Database configuration Assistant (DBCA); and

Installation

Oracle Installation Products

The Oracle 10g Universal Installer is installed in C:\Program Files\Oracle\Inventory.

Where Is Oracle Installed?

Version	OS	Installer Setup File	Installer MB	ORACLE_BASE	ORACLE_HOME
10.2.0.1	Express Edition on Windows XP	OracleXEUniv.exe	211,849	C:\oraclexe	\product\10.2.0\server
	Enterprise Edition Windows 32bit	10201_database_win32.zip	639,674	C:\oracle\product\10.2.0	\db_1

Reviewing Installed Starter Database Contents

Other

OS Environment Variables For Where Oracle Is Installed

Oracle has been designed to allow several versions to be installed on a single machine.

When Oracle Personal Express Edition (XE) is installed, it creates by default folder C:\oraclexe with the folders in the table.
Create OS environment variable ORACLE_BASE to hold value "C:\oraclexe".

Different installers use other drive/folder paths.

Envrionment variables enable the root path to be referenced by $ORACLE_BASE within Unix and %ORACLE_BASE% within Windows. For example, within Windows, the file created to detail installation activities can be found at "%ORACLE_BASE%\app\oracle\admin\xe\bdump\alert_xe.log" Copy and paste this on the address bar of Windows Explorer.

"xe" is the database Instance Identifier (SID) for Express Edition.
"orcl" is the default db_name value in INIT.ORA for the Enterprise Edition.
Create OS environment variable ORACLE_SID to hold SID value "xe" or the instance SID value for your application.

The path to executables (exe and dll files) within the bin folder containing Oracle supplied command line utilities for a Oracle version is stored within OS environment variable $ORACLE_HOME in Unix.

On Windows machines, rather than setting a %ORACLE_HOME% environment variable, Oracle uses the path in Windows Registry key HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE using the Regedit utility

Oracle 11g only requires user specification of ORACLE_BASE, since it creates ORACLE_HOME from it.

Change OS Environment variable PATH to replace the path with variable %ORACLE_HOME%/bin where Oracle executables run from whatever is the current folder. Remember the $ prefix in UNIX and two % in Windows. Since it's a variable, PATH does not need to be changed when the Oracle path needs to change.

To change from using Oracle 10g to Oracle 11g on the same machine, change the value of ORACLE_HOME.

To connect to a network different than the one active while Oracle was installed, change in ...\db_1\NETWORK\ADMIN\listener.ora the default port from 1521 to 1525.

Express Edition Files Installed

Enterprise Edition Files Installed

The default SAP installation names Oracle datafiles with a prefix which matches its tablespace name. Rather than the dbf filename suffix, SAP requires datafiles to have names such as "psappooli.data3" for the third datafile for the SAP POOL data indexes.

Background Services/Processes

Oracle installs several services with names starting with "Oracle". In Windows, their status can be viewed (among other services) from
Programs > Settings > Control Panel > Administrative Tools > Services (services.msc).

Service Name	Startup	Bin	Notes
OracleServiceXE OracleServiceORCL	Automatic	ORACLE.EXE XE	Oracle RDBMS Kernel Executable background process (runs multi-threaded). Unlike UNIX ports of Oracle Database 10g, Oracle Database 10g on Windows is implemented as a single operating system process.
OracleXEClrAgent	Manual	OraClrAgnt.exe	CLR agent (XE Edition only)
OracleXETNSListener OracleOraDb10g_home1TNSListener	Automatic	tnslsnr.exe	"Transparent Network Substrate" (Oracle Network Service) Listener
OracleOraDb10g_homeiSQL*Plus	Automatic	isqlplussvc.exe	iSQL *Plus Application Server (Enterprise edition only)
OracleMTSRecoveryService	Automatic	omtsreco.exe	MTS Recovery Service
OracleMJobSchedulerXE OracleMJobSchedulerORCL	Disabled	extjob.exe XE	Job Scheduler
OracleDBConsoleord	Automatic	nmesrvc.exe	(Enterprise edition only)

Oracle 10g takes VM Size of 651,540K.

A different service name is assigned to process each instance. Service names contain "XE" for Express Edition, or "ORCL" or "OraDb10g_home1" automatically assigned by the Enterprise edition installer.

Statup is "Automatic" when that the service starts every time Windows boots up.
Statup is "Manual" if that service does not start with Windows boots up and requires manual intervention to start.

Executables for running services and the dll files they use (oraclient10.dll, etc. also in the same bin folder) cannot be deleted (to uninstall Oracle) until their service is stopped.

Oracle Enterprise Components

The Enterprise version of Oracle runs additional services for each instance.

Advanced Database architectures include: Oracle Managed Files (OMF) to automate creation and dropping of datafiles and management of redo log and control files; Partitioning of tables by range, list, hash, composition; Replication (not suited to failover and backup); Standby (Failover) Databases, Grid Computing, which consists of OEM Grid Control of Oracle RAC (Real Application Clusters) and Oracle 10g platform independent Cluster Ready Services (CRS) to handle failover of services to surviving nodes

ASM, Transportable Tablespaces, Streams, Scheduler.

Oracle features for a grid computing architecture: –Oracle Real Application Clusters (RAC) –Automated Storage Management (ASM) –Oracle Transportable Tablespaces –Oracle Streams –Oracle Scheduler –Oracle Enterprise Manager Grid Control

• Partitioning Option
• Advanced Networking Option for network security using encryption and data integrity checking with enhanced user authentication services.
• Objects Option - Bit-mapped Indexes
• Parallel Server Option - Parallel Query, DML, Index Scans, Index Build
• Enterprise Manager Performance Pack -- six applications for advanced diagnostics, monitoring and tuning of Oracle databases
• Advanced Queuing
• Advanced Replication
• Incremental and Parallel Backup and Recovery
• Point-in-time Tablespace Recovery

Starting Oracle Services

If Oracle Windows Express Edition (XE) has been installed

Programs > Oracle > Start Oracle Database invokes
StartDB.bat within folder ORACLE_HOME/bin which starts services OracleService... and ...TNSListener established during Oracle installation.

If the Oracle Enterprise Edition was installed it is assumed that the database just runs.

Initialization with SPFILE or PFILE (INIT.ORA)

Before Oracle10g, when an Oracle instance starts for the first time, it references initialization parameters from a pfile named initSID.ora (INIT.ORA) in %ORACLE_HOME%\database on Windows or $ORACLE_HOME/dbs on Linux.

Since Oracle reads the pfile only when it starts, changes to it (using a Notepad or other text editor) requires the databse to be restarted.

Oracle 10g added a way to change init parameter values dynamically (while the database is running):

ALTER SESSION set  parameter = value ;
ALTER SYSTEM set  parameter = value  SCOPE = SPFILE;

SCOPE = SPFILE; applies the next time the database is started (not to the current instance). Use this for static parms.
SCOPE = MEMORY; applies to the current instance only, and will not be retained on restart.
SCOPE = BOTH; applies to the current instance and when the database starts again.

Oracle 10g introduced the spfile (server parameter file) named spfileSID.ora to retain parms so that they can be applied the next time the database starts.

Dynamic changes to parameters can also be made by Oracle's autotune intelligence.

Since the spfile is open for update as the database runs, it can't be edited like the pfile can. But the spfile can be backed-up to a pfile with a command like:

create pfile=/path/to/backup.ora from spfile;

Oracle 10g by default looks for a spfile. But if a spfile can't be found, Oracle looks for the pfile (as before).

When the database is restarted using spfile, the DBA must login again with proper privileges.

Instance Parameters

Among the many initialization parameters that define the configuration of the instance are:
• the name of the database,
• the amount of system memory areas that can be used by an instance,
• the time stamp of when the database was created,
• the file names and locations of CONTROL_FILES

#, the pound sign, precedes a comment in the static parameter file.

The V$PARAMETER view of the internal x$ksppi table lists all initialization parameters for a database.

processes=150 is the default number of processes and sessions.

Upon start-up, if Oracle can't use the Control File for the instance, Oracle starts processes with NOMOUNT on the datafiles.

The configXXX.ora stores persistent values for variables that do not change after datafile XXX is created. This files is included in the IFILE parameter in the init.ora file for the instance.

Interfaces and Protocols

There are several ways to get into the Oracle database:

• Logged onto the database on the same computer, the OEM (Oracle Enterprise Manager) Console communicates using Oracle's Bequeath protocol.
• On a local area network (LAN), the Oracle Net Service client is a driver for SQL*Plus, SQL*Plus Worksheet (a form of SQL*Plus contained within OEM), or 3rd Party applications (which may also use a Java thin Oracle Java Database Connections (JDBC) driver).

  The TNSNAMES.ORA (Transparent Network Substrate) file on both client and server define connection specs such as the service name.

  Web Listener (process TNSLSNR.exe) recognizes IPC and TCP/IP requests through ports bound based on configuration info in the LISTENER.ORA control file within folder $ORACLE_HOME\NETWORK\ADMIN. Its default port number is 3339.

  In a distributed database environment, the DBA_DB_LINKS data dictionary view lists Database links are stored definitions of connections to query remote databases.

• IPC (Interprocess communications) with application programs making calls
• Remotely on other computers running an internet browser sending HTTP/HTTPS over a TCP/IP network to an Apache web service running iSQL*Plus (the web version of SQL*Plus).
• Java ports.

Oracle's Database Control is for ...
Oracle's Grid Control is for managing several databases.

tnsping.exe to TNS

Oracle's foreground communications utilities are known as SQL*Net (pre-Oracle8 and still supported by Oracle8) or Net8. Net8 Administrator's Guide

"SQL*Net message to/from client" and other idle events are listed in the stats$idle_event table.

The maximum number of connections through Oracle Net Services is specified by the ???

A good test to check that everything is working is to run the application SQL Worksheet from the Enterprise Manager folder. When the SQL Worksheet logon screen is displayed, enter a valid username and password, but leave the service name field blank so that it will use the default database. Don't change the contents of the Connect As box.

Each connection to Oracle takes 2-4 MB of memory.

Oracle Enterprise Manager

On Windows machines, Programs > Oracle > Database Control - orcl" invokes a web URL to "/em" (enterprise manager) database control:
http://localhost:1158/em (note the use of default port TCP 1158 for Bequeth protocol rather than standard HTTP port 80/443).
http://2600client:5550/em

iSQL*Plus

Programs > Oracle > Run SQL Command Line runs sqlplus.exe using ORACLE_HOME\sqlplus\mesg\*.msb files.

Within ORACLE_HOME\sqlplus\admin are several sql files:

glogin.sql	The global login site profile automatically run when SQL*Plus is started
plustrce.sql	Creates a role with acess to Dynamic Performance Tables for AUTOTRACE ... STATISTICS command
pupbld.sql	Installs the PRODUCT_USER_PROFILE tables.
help\helpbld.sql	Loads SQL*Plus hlpbld.sql
help\hlpbld.sql	Builds HELP tables from helpus.sql
help\helpus.sql	Inserts English help text into HELP tables.
help\helpdrop.sql	Drops TABLE HELP and VIEW HELP_TEMP_VIEW

Run pupbld.bat to install product user profile tables to restrict commands per user.

http://2600client:5560/isqlplus is Oracle's web based browser utility used to query the database over the Internet via Apache web server.

http://2600client:5560/isqlplus

In iSQL *Plus the query section is separate from the result section, which can include graphical output in HTML format. http://www.oracle.com/technology/tech/sql_plus/index.html

Sessions

Oracle metrics include these views of session activity in V$SESSTAT:

• Session connect time - The connect time for the session in 1/100 seconds. This value is useful only in V$SESSTAT. It is the wall clock time of when the logon to this session occurred.
• Session cursor cache count - The total number of cursors cached. This is only incremented if SESSION_CACHED_CURSORS > 0. This statistic is the most useful in V$SESSTAT. If the value for this statistic in V$SESSTAT is close to the setting of the initialization parameter SESSION_CACHED_CURSORS, the value of the initialization parameter should be increased.
• Session cursor cache hits -The count of the number of hits in the session cursor cache. A hit means that the SQL statement did not have to be reparsed. By subtracting this statistic from parse count (total) one can determine the real number of parses that happened.
• Session logical reads - This statistic is basically DB block gets + consistent gets.

Kill a session (as a SYS user) by finding the SID and SERIAL# from V$Session and use this statement:

ALTER SYSTEM KILL SESSION 'SID, SERIAL#'
/
eg :
ALTER SYSTEM KILL SESSION '12, 345'

Client Throughput Statistics

Throughput between clients and the Oracle server are measured with these Oracle metrics:
• SQL*Net roundtrips to/from client - Total number of Net8 messages sent to and received from the client.
• SQL*Net roundtrips to/from dblink - Total number of Net8 messages sent over and received from a database link.
• Bytes received via SQL*Net from client - The total number of bytes received from the client over Net8.
• Bytes received via SQL*Net from dblink - The total number of bytes received from a database link over Net8.
• Bytes sent via SQL*Net to client - The total number of bytes sent to the client from the foreground process(es).
• Bytes sent via SQL*Net to dblink - The total number of bytes sent over a database link.

Stopping Oracle

It's more problematic to stop Oracle because we want to avoid losing data if it is in the middle of saving data to permanent storage.

With Windows Express Edition:

Programs > Oracle > Stop Oracle Database invokes
StopDb.bat

orakill.exe

On UNIX:

Memory, Processes, and Files Per Instance

Pop-up this diagram to its own window

Click on a white box above for commentary on memory components
Click on a green box above for commentary on background processes and foreground dedicated server and user processes for sessions.

Processes

From Task Manager, Processes tab (listed by Image Name)

Process	Mem Usage	Location	10g File KB	Notes
rman.exe	-	ORACLE_HOME\bin	1,516	Recovery Manager. Uses the Large Pool
sqlnet.exe	-	ORACLE_HOME\bin	-	SQL Net (uses SQLNET.ORA in admin folder)
TNSLSNR.EXE	-	ORACLE_HOME\bin	200	Oracle TNS Listner started by Net Manager
LSNRCTL.EXE	-	ORACLE_HOME\bin	64	Oracle TNS Listner Console
sqlplus.exe	6,784	ORACLE_HOME\bin	536	-

Within each process are threads. The many Oracle background tasks runs within a single multi-threaded process oracle.exe.

On UNIX, Oracle's processes are listed with command "ps -ef | grep ora".

Development and Query Tools

Run... Command-line scripts within Oracle bin folder:



• Backup.bat to Programs > Oracle > Backup Database using OCOPY
• Restore.bat to Programs > Oracle > Restore Database
• ODPReg.exe
• RegOledb.bat to invoke Windows regsvr32 to register OraOLEDB10.dll
• ott.bat to invoke OTT instant java client
• InstODP.bat uses gacutil.exe
• helpins.bat
• cleanup.bat invokes lsnrctl to stop listener and oradim to stop and delete the SID
• Oracle Server Manger
  In Oracle 8i+: SVRMGR on Windows, SVRMGRL on Unix
  In Oracle 8:  SVRMGR30
  
• ORAPWD Password Utility
• ORADIM -? | -H | -HELP for database creation, deletion, start, shutdown
• sqlldr = SQL Loader
• expdp for EXPort Data Pump utility
• exp to EXPort
• imp to IMPort
• impdp for import Data Pump utility
• DBVerify DBV HELP=Y
• rdbms/admin/utlxplan.sql that creates Explain Plan from the plan_table created by Oracle.
• OPERFCFG
• LDAPMODIFY utility to add and remove users from the OracleNetAdmins group for Active Directory from the TNSNAMES.ORA file
• extjob = service -install, -debug, -remove
• dbv = DBVERIFY
• ctxhx to convert input file to html file.
• ctxkbtc Enter user:
• agtclt = Agent control utility
• cemutls = cluster
• OracleClrAgent.exe
• omtsreco.exe
• hsolesql.exe
• hsodbc.exe
• hsdepxa.exe
• hsolefs.exe
• hsdepxa.exe
• hsots.exe
• nid.exe
• ocopy.exe
• onsctl.exe
• orastack.exe
• sbttetst.exe
• sclsspawn.exe to spawn
• OracleAdNetConnect.exe, OracleAdNetTest.exe
• wrap.exe
• unzip.exe & zip.exe

Where OraHomeXX is the $HOME_NAME environment variable. (ORANT in versions prior to 8i).

Windows Utilities

For Enterprise Edition on Windows:

Configuration and Management Tools	Administration Assistant for Windows	ORACLE_HOME/BIN\MMC Snap-Ins\Administration Assistant\orammcadm10
	Database Configuration Assistant	ORACLE_HOME/BIN\assistants\dbca.cl
	Database Upgrade Assistant	ORACLE_HOME/BIN\assistants\dbua.cl
	Locale Builder	ORACLE_HOME/BIN\nls\lbuilder.cl
	Microsoft ODBC Administrator	C:\WINDOWS\system32\odbcad32.exe
	Net Configuration Assistant	ORACLE_HOME/BIN\network\netca.cl
	Net Manager	ORACLE_HOME/BIN\network\tools\netmgr.cl
Integrated Management Tools	Oracle Directory Manager	ORACLE_HOME/BIN\launch.exe ldap/oidadmin.cl
	Wallet Manager	ORACLE_HOME/BIN\launch.exe owm.cl

Third-Party Tools

Oracle's Application Express Studio (formerly Oracle HTML DB Studio) offers apps and templates.

DB Visualizer Personal, at $173 (+$73/year) is a bargain. It supports an extensive collection of databases and the majority of operating systems.

TOAD for Quest software integrates its full-function SQL Editor into a Multi-tabbed Procedure Editor and Debugger that works on multiple files in parallel with SCC-compliant version control. Its Schema Browser manages database objects SQL Modeler builds complex queries integrating server and session statistics to provide tuning hints and quick views of the server environment. Its DBA Module utility compares and synchronizes database schemas to manage space, administer security, and import/export data. Its free version lasts 30 days.

Spotlight from Quest Software provides real-time diagnostics.

DB Artisan

ER Studio is a professional tool for generating intricate physical DDL (Data Definition Language) from complex logical maps of databases.

DB Tools for Oracle from SoftTree Technologies has a $670/user suite for developing applications which use Oracle databases and administering Oracle databases, including a Test Data Generator, File Loader, LOB Viewer and Loader, DB Difference

Its DB Monitor Expert graphically presents performance diagnostics.
Its DB Trends Expert automates database trend collection and analysis.
Its DB Space Expert uses the collected data for predicting various database problems before they occur.
Its DB Tuning Expert provides recommendations based on its organization diagnostic tests.
Its Performance Library provides 50+ prebuilt customizable performance monitors, graphs, and reports.
Its SQL Editor and Debugger and SQL Profiler and SQL Assistant has code formatters and beautifiers; SQL syntax validation, code structure views/
Its Java Editor compiles and directly loads Java classes and resources into Oracle databases.

DBHawk (formerly DBConsole) from DataSparc is a free, platform independent tool that provides administration and development of Oracle databases through a web browser.

dbXpert for Oracle from Vecal is a Windows-based client program to assist in the development and administration of Oracle databases. It provides object and session information; syntax highlighted automatically; user-friendly data browser; and a script library for frequently executed SQL statements.

Sample Demo Files

Oracle provide sql files within %ORACLE_HOME%\demo\schema\human_resources

1.	hr_cre.sql	Create data objects for HR schema
2.	hr_code.sql	Create procedural objects for HR schema
3.	hr_comnt.sql	Create comments for HR schema
4.	hr_idx.sql	Create indexes for HR schema
5.	hr_dn_c.sql	Add DN (Distinguished Name) column to HR.EMPLOYEES and DEPARTMENTS
6.	hr_popul.sql	Populate script for HR schema
7.	hr_main.sql	Main script for HR schema
8.	hr_analz.sql	Gathering statistics for HR schema
9.	hr_dn_d.sql	Drop DN column from HR.EMPLOYEES and DEPARTMENTS
10.	hr_drop.sql	Drop objects from HR schema

Permissions for Running PL/SQL

During installation, the Installer creates Local Group ORA_DBA described as "Members can connect to the Oracle database as a DBA without a password."

In most enterprise environments (e.g., a business with an IT dept.), databases are created with DDL scripts rather than interactively. An SCM dept enforces a process that involves checking in those scripts into a CVS (source repository) along with other assets of the company. SCM uses software such as Kantana to coordinate versions of code that matches specific versions of database creation scripts. Process standards like CMM, ISO 9000, and ITIL require this repeatable process. Using the Enterprise Manager GUI is not considered repeatable because it's done by "human hands".

Here are the DBA actions needed before writing PL/SQL programs:

1. For new applications, create a schema/user (the logical container for databases and indexes). (A user/schema owns database objects like tables and indexes).

   Each profile is a named set of specified resource limits and password attributes.

   create profile low_user limit SESSION_PER_USER 1 CPU_PER_SESSION 1000 CPU_PER_CALL 100 FAILED_LOGIN_ATTEMPTS 3;

   Others include CONNECT_TIME, IDLE_TIME, LOGICAL_READS_PER_SESSION, LOGICAL_READS_PER_CALL, PRIVATE_SGA, COMPOSITE_LIMIT, minimum number of characters, etc. A profile can be assigned to a group of several users:

   alter user user profile low_user;

   Test what happens when such limits are reached. Profiles are activated by when init param RESOURCE_LIMIT = TRUE.

   Roles are used to manage the privileges of related users.

2. Grant the DBA's Oracle account name the CREATE PROCEDURE privilege to create subprograms in the designated schema.
3. Directly grant (not via a Role) SELECT, INSERT, UPDATE, or DELETE privileges on any database objects (for example, tables and sequences) in the schema. A stored subprogram or package can only reference objects that the owner of the subprogram has access rights to directly (not via a role).
4. Grant EXECUTE privilege Oracle-supplied packages in the directory /rdbms<version</admin/ where <ORACLE_HOME> is the Oracle home directory (shown either in the UNIX shell variable of the same name or in the Windows Registry) and <version> is the database version. The names of the package scripts have the general form of dbms*.sql. For example, on the author's client machine, dbmssql.sql is in d:\orawin95\rdbms72\admin.
5. Compile Oracle-supplied packages.
6. Ensure there is sufficient storage available for stored subprograms and packages. These objects make additional data dictionary entries in the SYSTEM tablespace. Create a default tablespace for local copies of tables and indexes to avoid mucking with Production objects.
7. Ensure that the shared and private SQL areas in the System Global Area (SGA) are large to handle enough the anticipated load from running PL/SQL scripts and subprograms.
8. Ensure access to the SQL*Plus application or a suitable third-party tool.
9. Periodically audit by statement, privilege, and object:
   audit delete table by sel_user;
   audit insert table by sel_user;
   audit create table by sel_user;
   audit drop table by sel_user;
   audit select on object;
   audit insert on object;

Starting Oracle Server Manager

Logon/authenticate using a default Username and Password: connect internal/Oracle

View ALL_USERS lists USERNAME CREATED dates. View USER_USERS

Installed by	Username (Owner)	Password	Privileges
default	system	input during install	Used for data administration. PRODUCT_USER_PROFILE table enforced by SQL*Plus
default	sys	change on install	The root SYSOPER. Owns all, so has RESTRICTED SESSION privilege and can perform STARTUP, SHUTDOWN, ALTER DATABASE OPEN/MOUNT, ALTER DATABASE BACKUP, ARCHIVE LOG, and RECOVER.
default	DBSNMP	?	-
default	OUTLN	?	-
-	internal	oracle	SYSDBA - all system privileges with ADMIN OPTION, including CREATE DATABASE and time-based recovery. Windows NT local group ORA_DBA does not have access to NTFS volumes, unless it is granted.
-	scott	tiger	demo tables
-	sysman	oem_temp	Oracle Enterprise Manager
-	ORDSYS
-	MDSYS
-	CTXSYS

Oracle Default Password Auditing Tool
Checkpwd identifies weak and default passwords by comparing hashkeys of passwords in the dba_users view versus hashkeys calculated from a dictionary file.

Oracle Server Manager has both a line mode and GUI interface. It replaces the SQL*DBA utility used in Oracle version 6 and Oracle7 releases prior to 7.3. After Oracle is installed:

To change the password to account xxx in SQL:

ORAPWD
ALTER USER xxx IDENTIFIED BY 'password';
EXIT;

To change the password to account xxx in SQL*Plus:

PASSWORD xxx
Changing password for xxx
New password:
Retype new password:
Password changed

Optimal Flexible Architecture (OFA)

Part of Cary Millsap's 1991 industry-standard OFA (implemented after release 8.1.3) is a consistent set of folders under the \oracle\admin folder for each instance (such as \ORCL):

Folder (alias names) for Instance		Description	Parameter in init.ora
11g	8-10g
\pfile		initialization parameter file (init.ora)
\arch		archived log files
\exp		export or import scripts
\create		logs output during database creation
\adhoc		Ad hoc SQL scripts for a given database
\cdump		core dump for the database	Core_Dump_Dest=
\trace	\bdump	Background process trace / dump files and alert log file	BACKGROUND_DUMP_DEST= (in /saptrace/background) is deprecated in 11g
	\udump	user dumps and trace files for the database	USER_DUMP_DEST= (in /saptrace/usertrace) is deprecated in 11g
\adump		audit dump files	Audit_dump_Dest= AUDIT_FILE_DEST= (in /rdbms/audit)
\alert	-	for the plain text and log.XML versions of the alert log.
\incident	-	for the incident packaging software new to 11g
\incpkg	-	for packaging an incident into a bundle

Note: \Core is only used for UNIX systems, which use mount points instead of drive letters. For OFA on UNIX, install to u01/app/oracle/product/9.2.0/... installed from the u01/app/oracle/admin/cdrom/ automount holding the entire install CDROM

Additionally, the LOG_ARCHIVE_DEST points to folder /oracle/<sapsid>/saparch/SAPSID_
the CONTROL_FILES parameter points to folder /oracle/sapdata-nn/cntrl1.data1

If the OFA file naming standard seem like files are diced and sliced a lot ... that's the idea! The OFA structure enables speedier performance than having all files jumbled together because INIT.ORA can point to folders placed on separate (parallel) devices so that I/O is spread around. Fragmentation within a file type can be better controlled when file types are fragmented (distributed). Got that?

Oracle Data Architecture

Oracle stores and maintains user data using several types of data structures:

• Logical Data structures that make data manageable;
• Physical Data Structures that occupy actual disk space manipulable by the operating system.

  Databases are created using DDL (Data Definition Language) which create logical Tablespaces implemented as physical Datafiles defined by Schema objects (owners) which describe Segments of tables, indexes, rollback, temporary, partitions, clusters.

  When more space is required, segments are expanded by Extents of Oracle Blocks.

  Control Files containing parameters (to configure how the database starts), and password files (to manage authentication).

When processing DML (Data Manipulation Language) to insert, update, or delete data, Oracle first writes 1) after images to Online Redo Log Files, then 2) to an Undo Log file, then 3) to the data file. A SQL COMMIT statement marks undo entries for the current session as no longer needed for rollback.

Database creation scripts can be created interactively using the Database Configuration Assistant (DBCA tool) GUI or SQL*Plus command line.

Temporary Table Space

When the CREATE DATABASE command is issued without a fully qualified DATAFILE clause specifying location, OMF (Oracle Managed File) facilities automatically place files using OFA standards and manage expansion and deletion of datafiles associated with tables. (all very good things) Use of ASM is specified by adding a disk group clause.

Oracle in a Nutshell: A Desktop Quick Reference By Rick Greenwald, David C. Kreines

Conceptual relationships between Logical and Physical schemas:

Logical Data Structures

The DBA_TABLESPACES view lists all tables created.

Tablespace files

A tablespace may hold several data files. However, each datafile can belong to only one tablespace. To display the V$DATAFILE mapping between database files and tablespaces:

SELECT tablespace_name, file_name, bytes 
FROM dba_data_files
ORDER BY tablespace_name, file_name; 

The default set of system tablespaces are stored under the ORACLE_SID folder for each instance:

Area	Tablespace	Contents	Fragmentation
Core	SYSTEMnn.DBF	data dictionary tables (metadata on user application tables) and system rollback segments	zero
	SYSAUX.DBF	New to 10g, consolidates metadata previously in SYSTEM for optional software, such as RMAN (Recovery Manager).	-
	TEMPnn.DBF	dynamically created objects that store data during bulk operations, such as when a sort operation overflows the size of sorting buffer space in memory allocated.	very high
	USERSnn.DBF	application data and structures created by default by DBCA	?
	UNDO	-	?
Other	DATAnn.DBF	physical data of the database	low
	DRnn.DBF	???	?
	INDEXnn.DBF	contains keys used to find data. All indexes are stored in a single segment.	low
	RBSnn.DBF	rollback segments maintains data concurrency within the database. These files are created in the SYSTEM tablespace and moved here after being taken offline (to prevent I/O contention)	high
	TOOLSnn.DBF	3rd party tools and the SYSTEM's account default tablespace	very low

The “nn" above is a placeholder for a zero-padded count of the physical files multiplexed (mirrored) over several drives (for better performance and redundancy).

User tablespaces are separate from the system tablespace. Ideally, tablespaces are created in raw (unformatted) disk partitions.

The DB_NAME environment variable -- the name of the organization that owns the database -- can only be eight characters or less.

SAP requires index file names to be eight characters long, beginning with the table name, then a variable number of (between one and three) underscore (_) characters, and ending with a number.
Indexes that end with a zero represent the primary key indexes for the table.
Indexes that end with nonzero numbers represent secondary indexes.

Fragmentation

Indexes should be stored in a different physical disk location than their associated tables.

With 10g's extent management feature, fragmentation does not occur because ...

Index segments must be sized appropriately or fragmentation will slow database performance.

Use this query to rebuild the index:

alter index <index-name> rebuild tablespace <index-tablespace>;

Brian Cryer uses these conditions for rebuilding an index:

• The percentage of deleted rows exceeds 30% of the total, i.e. if del_lf_rows / lf_rows > 0.3.
• If the ‘HEIGHT’ is greater than 4.
• If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ due to a lot of deletes.

Applications can continue to use the table while indexes are rebuilt with this command:

select sum(bytes) from dba_extents where segment_name=’<index-name>’;

This rebuild command does not allow application access:

analyze index <index_name> validate structure;

The DBA_FREE_SPACE_COALESCED view gives information on tablespaces with extents which can be coalesced.

Tables are rebuilt this way:

1. Exporting the table with COMPRESS=Y
2. Droping the table. The database will be unavailable to applications after this.
3. Import the table.

Segments

Tablespaces are broken down into logical storage structures called segments that could possibly span many data files belonging to a tablespace.

Type of Segment	Description	View
Table	stores the data associated with the tables. It contains a header block that serves as a space directory for the segment.
Temporary	used to store temporary data during sorting operations.	select sum(bytes) from dba_extents where segment_name=’<index-name>’;
Rollback	have a minimum of two extents when created. They have to be large enough to accommodate all the data generated from a single transaction.	DBA_ROLLBACK_SEGS
LOBnn (Large Object)	stores a pointer to large objects (text documents, videos, or images) in data files.	--
Nested Table	a column made up of a user-defined table also called the cache segment.	--
Bootstrap Segment	created by sql.bsq when the database is created.	--

Rollback Segments

Rollback segments store the “before" image of data prior to transactions being applied to the database so that they can be used to restore specific transactions to "back out" incomplete transaction sets as if they never occured.

The "user rollbacks" statistic in V$SYSSTAT reports how many times users manually issue a ROLLBACK statement or when an error occurs during user transactions.

The number of rollback segments available may need to be increased if Rollback Segment Contention (whether a process had to wait to get access to a rollback segment) if this query reports more than 1%:

select round(sum(waits)/sum(gets)*100,2) from v$rollstat

Brian Cryer recommends that rollback segments be isolated in their own tablespace, preferably on a separate disk from other active tablespaces.

After they have dynamically extended, rollback segments shrink back to the size specified in the OPTIMAL parameter. OPTIMAL should be close to the “Avg.Active” from this query:

Select segment_name, shrinks, aveshrink, aveactive "Avg.Active"
from v$rollstat v, dba_rollback_segs d
where v.usn = d.segment_id;

Schema

The set of objects owned by a user account is called the user's schema. It is used to control what actions (Privileges and Roles) users are permitted to perform to a database.

Authentication Methods

OS authentication means users provide passwords to the operating system. This requires creating OS user groups for each role. To log on to SQL*Plus using OS authentication:

–sqlplus /nolog 
–CONNECT /@ORACLASS AS SYSDBA

This is easier than using Oracle's own authentication to connect, which involves supplying an encrypted Password file. This file is created by the SYSDBA or SYSOPER. On Windows, orapwd to a PWD<%ORACLE_SID%>.ORA file in %ORACLE_HOME%\database. On Unix, create orapw<ORACLE_SID>.ora in $ORACLE_HOME/dbs.

REMOTE_LOGIN_PASSWORD FILE = EXCLUSIVE

loadpsp.exe

Owner Roles

SYSDBA: ADMIN role can CREATE DATABASE and everything else.

SYSOPER has system privileges to start up, shut down, and back up the database; This role can also modify database components.

Extents

Each database segment is composed of one or more extents.

An extent is a unit of storage made up of one or more contiguous Oracle blocks obtained in a single allocation. When a segment can no longer fit new data into its currently allocated extents, another extent is allocated.

When a database is created, the extents allocated are specified by the STORAGE clause of the CREATE command. The initial storage value cannot be changed. Each extent in a database segment can be of the same or different size. The size of the next extent allocated is dependent upon several factors.

The maximum number of extents a single database object can have depends on the Oracle block size.

The DBA_EXTENTS view displays information about Extents. This query lists database objects with over 10 extents allocated:

select * from dba_segments where extents > 10;

Oracle bumps the high-water mark for a table in increments of five blocks.

Synonyms

Synonyms point to other database tables. When a synonym name is used, Oracle automatically replaces it with the name of the object for which the synonym is defined. Oracle first looks for Private synonyms created in a specific schema and are only accessible by the schema that owns it. Oracle then looks for Public synonyms owned by the PUBLIC schema for reference by all database schemas. Public synonym “DICTIONARY” lists descriptions of all data dictionary tables:

select * from dictionary where table_name like ‘DBA_TABLES%'; 

The DBA_SYNONYMS view lists information on all synonyms.

SID Environmental Variables

The name of each instance is set by the value of the four-character ORACLE_SID (System Identifier) environment variable. "ORCL" is the default SID. Lower case "orcl" may generate an ORA-27101 error.

Views

Views are stored SQL statements that can be queried. They hide complicated query logic and enhances confidentiality and security. The DBA_VIEWS view displays information for all 1481 views in the database.
• DBA_ views contain information on all objects in the database.
User information is stored in dba_users. Information on granted privileges is stored in the dba_tab_privs, dba_sys_privs, and dba_role_privs tables. You can also query role_tab_privs, role_sys_privs, and role_role_privs to display information on privileges granted to roles.
• ALL_ views contain information on all objects to which the designated user has access to query.
• USER_ views contain information on all objects accessible by the user querying the table. For example, the view USER_SOURCE contains the source code for your stored subprograms and packages.
• V$ Dynamic Performance views described in the Oracle Reference Manual contain runtime performance and statistic information on a large number of database functions.
  They are NOT automatically created during database creation by the SQL.BSQ script in $ORACLE_HOME/dbs. These publicly accessible V$ views are actually public synonyms for corresponding SYS.V_$ views owned by SYS. Among Oracle's V$ Views:
  v$sesstat displays statistics since the current session started.
  v$sysstat displays statistics accumulated since database instance startup. Join it with v$statname for a list of its statistics:

  select  n.name, s.value
  from v$statname n ,  v$sysstat s
  where n.statistic# = s.statistic#
  order by n.class, n.name
  

  Others include: v$sgastat, v$vgastat, v$rollstat, v$undostat, v$filestat, v$latch, v$session_wait

  When using these views you should keep timed_statistics set to true (TIMED_STATISTICS=TRUE) in your init.ora file becuase doing so has minimal effect on performance.

• stats$ V$ view values frozen in STATSPACK snapshot perfstat system tables (sysstat, sgastat, parameter, librarycache, enqueuestat, waitstat, latch, latch_children, rollstat) and summarized as the difference between start and end snapshot values.
• GV$ views are dynamic performance views in a parallel server environment. There is a GV$ view for almost every V$ view, but with an additional column INST_ID (instance number).

Specific Views Compared with MS-SQL

Sequences

The DBA_SEQUENCES view lists the unique numbers (up to 38 digits long) generated using Oracle sequences. Pseudocolumns named NEXTVAL or CURRVAL can be used in this example:
SELECT EMP_SEQ.NEXTVAL FROM XXX

Triggers

The DBA_TRIGGERS view lists the stored procedures that have been set to fire when certain actions occur against specified tables. Triggers can be coded to fire for inserts, updates, or deletes against a table to enforce data integrity constraints. Alternately, any activity to a specific row that is affected or for each statement.

Enqueues

To prevent two people from updating the same data at the same time (concurrently) Oracle uses "enqueue" queuing mechanisms to lock out competing requestors.

The "enqueue" wait event measures how long processes have to wait in these FIFO (first in, first out) queues.

• The ST (SPACE TRANSACTION) global enqueue is for dictionary-managed tablespaces. ST enqueues are also held before any space transaction can be occurred on any tablespace in RAC.
  These are less common since the introduction of locally managed tablespaces.
  For problematic dictionary-managed tablespaces, Use LMTs, preallocate extents, or make the next extent larger.
• The HW enqueue is used with the high-water mark of a segment. Manually allocating the extents can circumvent this wait.
• TX4 (TRANSACTION LOCK) enqueues are the most common enqueue waits, usually the result of one of three issues:
  • duplicates in a unique index (from application bugs). In this case, a commit/rollback is needed to free the enqueue.
  • multiple updates to the same bitmap index fragment. issue a commit or rollback to free the enqueue when multiple users are trying to update the same fragment when a single bitmap fragment may contain multiple rowids.
  • multiple users are updating the same block. This is the most likely issue because if there are no free ITL slots, a block-level lock could occur.
    [_] To avoid this, increase the initrans and/or maxtrans to allow multiple ITL slots and/or increase the PCTFREE on the table.
• TM (TABLE LOCK) enqueues occur during DML (Data Manipulation Language) to prevent DDL (Data Definition Language) to the affected object with FK constraints. NOTE: DDL cannot be undone/rolled back.
  Indexing foreign keys avoids this general locking issue.

For more information on enqueues:

• Internal Implementation of Oracle Locks (Enqueue) by Harish Kalra at Quest


• Finding the source of enqueue waits

Physical Data Structures

Datafiles (.dbf files)

Oracle physically stores information in datafiles -- files stored on physical hard disk drives which make that whirlll sound. ;)

Datafiles are created using SQL commands CREATE TABLESPACE or ALTER TABLESPACE
A datafile created with a size of 10MB uses the full 10MB of space on the hard disk regardless of how many actual rows it holds.

Each process needs to open a number of files (control file, log file, database file) to work against the database.
The "Total file opens" statistic in LoadRunner Oracle metric reports the total number of file opens being performed by the instance.

Among V$SYSSTAT and LoadRunner statistics:

"physical reads" counts the number of I/O requests to the operating system to retrieve a database block from the disk subsystem. This is considered a buffer cache miss.

"physical writes" counts the number of I/O requests to the operating system to write a database block to the disk subsystem. Writes are usually performed by background processes DBWR or LGWR.

Disk Groups under Automatic Storage Management (ASM)

Automatic Storage Management (ASM) (new to 10g) enable DBAs to assign tablespace datafiles to (rather than specific disk drives) logical disk groups which can then be striped and/or mirrored to provide high performance and high availability on commodity disk drives which are less expensive and more easily replacable than RAID arrays from Veritas, EMC, etc.

Although ASM enables DBAs to avoid the bureaucracy associated with arrays usually owned by network storage administrators or having to learn how to deal with RAID drives, some DBAs may not welcome the additional workload, such as setting up and maintaining on each server a whole special Oracle Instance needed to map file extents to physical disk blocks. This architecture enables hard disks to be added without drama. But will this force DBAs to handle disk drives as well now?

Oracle provides a view to list disks in diskgroups, issue:

select * from v$asm_disk;

In addition to the "Normal" ASM setting where data is mirrored on two separate disks, ASM offers a "High Redundancy" option where data is mirrored on three separate disks for three-way mirroring. DBAs setup failgroups to specify fail-over.

ASM allows for variable strip width. The default stripe size of 1 Mbyte for datafiles can be changed to 128K for fast transfer needed with redo logs.

These advantages should be considered with recognition that RAID drives in a separate machine reduces CPU cycles on the server and provides additional caching on solid state memory that usually comes with large arrays.

Oracle Blocks

On the device holding a database, files are stored in blocks of data all the same size. Oracle Blocks are the smallest unit of Input/Output processed by Oracle.

Oracle blocks are physically stored on devices in operating system (OS) blocks.

Data Headers

Each block of data has a data header containing the name of the datafile and a timestamp.

The table header structure maintains one or more lists of blocks which have free space for insertion. If more processes are attempting to insert than there are free lists, some will have to wait for access to a free list. Free list contention occurs when more than one process is attempting to insert data into a given table but the
[_] FREELISTS storage parameter is not large enough.

Free list waits as a percentage of logical reads (from buffer) should be a very low 1% or less as reported with this SQL query:

select round((sum(decode(w.class,'free list',count,0))
/ (sum(decode(name,'db block gets',value,0)) 
+ sum(decode(name,'consistent gets',value,0))))*100,2)
from v$waitstat w, v$sysstat

A "db block get" is physical I/O.

The "consistent gets" statistic in V$SYSSTAT counts the number of times a consistent read (CR) was requested for a block.
"Consistent" in the Oracle world is when data can be read completely from buffer cache ("hit" the data requested in the buffer).

The "consistent changes" statistic in V$SYSSTAT counts the number of times a database block has rollback entries applied to perform a consistent read (CR) on the block. This consumes a great deal of resources.

ROWID

The physical location of a row of data can be specified using the ROWID two-byte pseudocolumn in every database. In Oracle8 it's presented with hex characters in this format: block number (in hex) . row (in hex). file number from the DBA_DATA_FILES view

Table Clusters

To reduce I/O, tables that are often used together in queries can be physically grouped into the same data blocks. These table cluster objects hold the values of the columns in the clustered tables. The cluster key defines the column used to join the tables together.

The "Cluster key scans" statistic reports the number of cluster scans that have been started.
The "Cluster key scan block gets" statistic reports the number of blocks obtained in cluster scans.

[_] The size of Oracle Blocks are set with the DB_BLOCK_SIZE parameter (default 8192) when the database is created.

[_] Dictionary files are created with the DEFAULT_BLOCK_SIZE. With Oracle 8i the default is 8K bytes.
9i calculate the default block size at about 1 to 2 percent of the database size.

OLTP (On-Line Transaction Processing) apps that generally read (get) and write small amounts of data at a time usually benefit from a smaller block size (4KB).
Data Warehousing or Decision Support System (DSS) apps generally handle large large amounts of data in a single transaction, so a larger block size may boost its performance.
However, Indexes should use the largest block size (32K blocks) to minimize physical I/O. Robin Schumacher says that setting tablespaces and indexes to DEFAULT_BLOCK_SIZE of 32K causes a 16X reduction in physical disk I/O.

Block sizes should be larger than the average_row_len obtained from the dba_tables view in order to avoid row chaining, which is when a row must be spread over two or more blocks becuase the row is so large or has so many fields that it cannot fit into a single data block.

The Chained Fetch Ratio calculation is used to monitor the proportion of all rows fetched (gotten) which requires a chained row continuation (fetch by row id).

select round((cont.value/(scn.value+rid.value))*100,2)
from v$sysstat cont, v$sysstat scn, v$sysstat rid
where cont.name= 'table fetch continued row' 
and scn.name= 'table scan rows gotten'
and rid.name= 'table fetch by rowid'

Row Migration occurs when an update to a row cannot fit within the current block, so data for the row must be migrated to a new block, leaving a pointer to the new location in the original block. This additional processing time and I/O occurs when the STORAGE parameter PCT_USED is high and PCT_FREE is low. Exporting and re-Importing may eliminate this problem.

Control (.ctl) Files

define the physical data structure and attributes of each database, starting from the timestamp when the database was created, its DB_BLOCK_SIZE, and the locations of associated Checkpoint, datafiles, and redo log files. Fields in the control file are listed by the V$CONTROLFILE view or command SHOW PARAMETER CONTROL.

When Oracle starts, it refers to a single control file, but several control files exists for a database because multiple control files are created and updated for redundancy. Oracle recommends specifying at least 3 (up to 8) duplicates on separate disk drives. These files are read based on CONTROL_FILES= parameter specified in init.ora or initORACLASS.ora. Starting the SQL Loader utility requires specification of a control file:

SQLLDR \'SYS/password AS SYSDBA\' control=foo.ctl 

As the database runs, the control file holds the log sequence number linking redo log files with datafiles.

That's why the instance needs to be bounced when redo logs or datafiles are added or moved. Restart DB (mounted mode) with PFILE parameter.

When creating the control file for a production database, MAXINSTANCES, MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, and MAXLOGHISTORY should be set and watched to avoid an emergency to extend them. Setting these requires the creation of a new control file.

Since this file is stored in binary format, create a text formatted user trace file:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Redo Log (.rdo & .arc) files

Redo Logs hold recordings of every change made to datafiles. They are useful for fine-grained recovery of specific changes.

To restore an entire database, a backup is restored, then rolled forward using archived and online redo log files to make changes to a point-in-time before the error occurred.

A "redo log" refers to a set of redo log files. Oracle switches to another log group when one is full.

Oracle redo files are "circular" because when it's done writing to the last file in the series, it begins overwriting to the first online redo log file. Several Redo Logs may reside inside an .arc file recognized by the operating system. Oracle can multiplex (mirror) Redo logs on several different disks that are updated simultaneously. Each member of a log group is an exact mirror of the others, and redo log entries are written to each member in parallel. This is instead of using RAID-1 or RAID-5.

NOTE: By contrast, Flashback files (new to 10g) capture changes to specific blocks on physical disk drives to return to the state of the database at specified points in time. Flashbacks operate in addition to redo logs. NOTE: Within ORACLE_HOME\sqlplus\admin, movelogs.sql moves online logs to a new flash recovery area, which is specified in INIT.ORA parameter db_recovery_file_dest.

The "redo entries" statistic viewed with V$SYSSTAT increments each time redo entries are copied into the redo log buffer.
LoadRunner Oracle metrics provide a "Redo entries linearized" statistic that reports on the total number of entries that are below the REDO_ENTRY_PREBUILD_THRESHOLD because building redo entries costs CPU time as it improves concurrency on a multi-processor system.

[_] To reduce use of computer power and disk space, datafiles can be set to be updated without redo logs by setting the NOACHIVELOG run parameter.

The V$LOG view returns from the control file the member count for each group#.
The V$LOGFILE view returns information about member .rdo file path for each GROUP#.

If there isn't enough space in the redo buffer, a redo space wait occurs. An indicator of this is when the rate of requests ("req") is higher than the ("wrt") rate required of the log writer:

select round((req.value/wrt.value)*100,2)
from v$sysstat req, v$sysstat wrt
where req.name='redo log space requests' and wrt.name='redo writes'

The "redo log space requests" statistic in V$SYSSTAT and LoadRunner tracks the number of times a log file switch has occured -- when the active log file is full and Oracle must wait for disk space to be allocated to hold more redo log entries.

Log Switch!

When a log group fills up with records, Oracle switches to another log group. This is called a log switch.

The "Log File Switch" wait event counter is incremented when commit requests wait for "logfile switch (archiving needed)" or "logfile switch (chkpt. Incomplete)". This web page recommendes: "Ensure that the archive disk is not full or slow. DBWR may be too slow because of I/O. You may need to add more or larger redo logs, and you may potentially need to add database writers if the DBWR is the problem. "

"Small Log files in relation to the size of the SGA or the commit rate of the workload can cause problems. When the log switch occurs, Oracle must ensure that all committed dirty buffers are written to disk before switching to a new log file. If you have a large SGA full of dirty buffers and small redo log files, a log switch must wait for DBWR to write dirty buffers to disk before continuing. "

The "log file space/switch" wait event in V$SESSION_WAIT...

Before performing maintenance on the active log group, manually trigger a log switch from the Enterprise Manager console (OEM), SQL*Plus Worksheet, connect as the SYS user and execute:

ALTER SYSTEM SWITCH LOGFILE; 

The system should respond with "System altered"

Password files

Password Files are optional disk components used for securely storing privileged user connection information so that the database can be administered remotely using Oracle Enterprise Manager rather than directly connecting to the machine hosting the Oracle database and using SQL*Plus.

ALRT.log file in User Dump (udump) folder

Oracle logs critical failures to the sidALRT.log (where sid is the system identifier for the database).

Its location is specified by the BACKGROUND_DUMP_DEST and USER_DUMP_DEST parameters before 11g and DIAGNOSTIC_DEST in 11g. By default, that's the $ORACLE_HOME/admin/$ORACLE_SID/bdump directory before 11g and $ORACLE_BASE/diag/$ORACLE_SID in 11g.

The format of alert log is in plain text. Oracle 11g writes an additional alert log named log.xml in XML format.

Standard SQL (and thus server access) was needed to access the log, but 11g provides a view:

select name, value from v$diag_info.

Trace (.trc) files

` User session trace files are given an ora prefix (such as "xe_ora_" followed by a unique sequence number, plus the .trc file extension. When parameter sql_trace is set to TRUE. To create a text formatted user trace file in the location specified in parameter USER_DUMP_DEST:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE; 

The instance needs to be bounced after this change.

Trace (.trc) files can be used to track down problems not causing critical failures.

Trace (.trc) files are in binary format, so special tools are needed, such as TKPROF

Initialization SQL Procedures

In $ORACLE_HOME \RDBMS\ADMIN are the scripts to initailize a database running under SYS using SQL*Plus or Internal using Server Manager:

Procedure	Description
Catalog.sql	creates views of the base table, dynamic performance views and their corresponding synonyms. Its over 200 pages long.
Catproc.sql	create data dictionary information, dynamic views and packages for the new database
Catexp.sql	--
pupbld.sql	creates the product user profile tables.
catrep.sql	creates procedures and objects supporting replication options.
Cat.sql	.
Utlpwdmg.sql	.

The services necessary to run the Oracle instance are created using the oradim utility. If it's run without arguments, a GUI interface appears.

Create a tablespace

The oradim.exe program creates tablespaces. The Database Assistant GUI outputs a .bat file containing a command line such as this:
oradim -new -sid TEST -startmode manual -pfile "E:\oracle\admin\test\pfile\init.ora"

The .sql file contains SQL statements such as this:

CREATE TABLESPACE DATA DATAFILE 'h:\...\...data\sid\DATA01.dbf'

SIZE 50M  
DEFAULT STORAGE (

INITIAL 10M	First extent Megabytes.
NEXT 10M	Second and further extent Megabytes.
MAXEXTENTS 505	The max number of extents a segment can have.
MINEXTENTS 0	The number of extents allocated when the segment is created.
PCTINCREASE 0	Percent the extent increases when the segment need to grow.

);

Create an instance database:

1. Nomount stage: Create the Oracle instance called ORCL:
Startup nomount pfile=E:\oracle\oraXX\database\initORCL.ora
This reads the init.ora parameter file, which specifies (on Windows) the OracleStartSID to invoke. This invokes the OracleServiceSID, which starts background processes listening, and initializes the SGA in memory. All this is automatically executed for the default database.
2. Mount stage: Mount the database by the instance:
   ALTER DATABASE MOUNT
   At this point, the control file is read and accessible, and queries and modifications to the data stored within the control file can be performed. Only the DBA can access the database, and only through Server Manager utilities.
3. Open stage: Open the database :
   ALTER DATABASE OPEN
   At this point, names specified in the control file are locked for exclusive use by the instance, and the database is accessible to normal user processes. Open is the normal operating state of a database:
4. Bouncing (stopping and starting) the database:
   SHUTDOWN IMMEDIATE

v$instance contains the startup_time field displaying the instance start time.

Resizing a database:

ALTER DATABASE DATAFILE E:\....DBF RESIZE 300K;
SELECT TABLESPACE_NAME, BYTES FROM DBA_DATA_FILES;

Memory Architecture

Program Global Area (PGA)/UGA for Sessions

In a single-threaded environment, the Program Global Area (PGA) contains data for use only by a single user connection. It contains:
• a user’s session variables and arrays,
• session addresses,
• cursor state reentrant values, and
• stack trace miscellaneous control structures.

Its contents is not visible to other processes unless (MTS) Oracle MultiThreaded Server is used. Kinda like a private room, baby ;)

In a MTS environment, the UGA (User Global Area) is used instead of the PGA.

The number of concurrent connections is limited by room in the PGA/UGA. The V$SESSTAT view used by LoadRunner Oracle statistics report:

• Session PGA memory - current PGA size for a session.
• Session PGA memory max - the peak PGA size for a session.
• Session UGA memory - the current UGA size for a session.
• Session UGA memory max - the peak UGA size for a session.

Prior to 9i, the PGA/UGA total size was set by a combination of parameters OPEN_LINKS, DB_FILES, LOG_FILES, and HASH_AREA_SIZE.

When the 9i PGA_AGGREGATE_TARGET size is set, Orcle ignores older HASH_AREA_SIZE, sort_area_size, and sort_area_retained_size parms because it can manage the PGA in aggregate (total) when WORKAREA_SIZE_POLICY is set to AUTOMATIC (rather than MANUAL) mode using v$pga_target_advice.

The PGA area is too small when "multi-pass exec" is high.
The PGA area is too large when optimal executions reach 100%, such as when running memory-intensive applications.
Burelson recommends running his dss_config.ksh script at 6pm with larger pga for batch work, run oltp_config.ksh script at 6am for online work with a larger db_cache_size.

Sorting

SORT_AREA_SIZE is now set by Oracle 9i automatically. But it's still good to keep an eye on the resources used by applications for sorting.
Monitor the proportion of sorts in memory vs. from disk with this query:

select round((mem.value/(mem.value+dsk.value))*100,2)
from v$sysstat mem, v$sysstat dsk
where mem.name='sorts (memory)'and dsk.name='sorts (disk)'

"SORTS (MEMORY)" displays the number of sorts completed within sort areas in memory.
"SORTS (DISK)" displays the number of sorts that needed temporary segments created.
"SORTS (ROWS)" displays the total number of rows sorted by all sorts performed.

Oracle STATSPACK reports (available since Oracle8i) include the number of "Rows per Sort" calculation.

System Global Area (SGA)

The System Global Area (SGA) is the memory structure allcated during the nomount stage of Oracle instance start-up. The SGA contains user data and control information for a single Oracle Instance. Its contents is visible/accessible to several user applications and system processes. Kinda like a hotel lobby

Oracle provides a SQL interface to view memory in the SGA with x$ tables, whose names can be queried with
select kqftanam from x$kqfta; Explanations

Data can be accessed from RAM 2,000 times faster than with physical i/o from a hard disk,
so Oracle tries to use as much memory as it can on the server where it runs.
To find the amount of free memory available in the SGA:

SELECT bytes FROM v$sgastat WHERE name = 'free memory';

The LOCK_SGA parameter locks the SGA into physical memory and prevents it from being paged out.

To clear the SGA of data, use the SQL command:

ALTER SYSTEM FLUSH SHARED_POOL

The v$sga view or "SHOW SGA" SQL statement displays the size (in bytes) of each major component of the SGA:

• Fixed area,
• The size of the variable portion of the SGA is roughly the result of this statment:

  sum(bytes) from V$sgastat where pool in ('shared pool', 'java pool','large pool');

• Database Block Buffer Cache,
• Redo Log Buffer Cache, and the
• If MTS

The parameters to control the size of these areas are described in SGA Management.

Fixed Pool Area/Portion

The size of the fixed portion is constant for each particular release/platform of Oracle. It cannot be changed in the parameters or other means.

Variable Shared Pool

System Global Area's Shared Pool contains:



Library Cache

The Library Cache contains Shared SQL areas and private SQL areas created for each transaction initiated.

View v$db_object_cache displays objects in the library cache (tables, indexes, clusters, synonym definitions, PL/SQL procedures/packages, triggers).

The library cache hit ratio reported in STATSPACK reports is calculated with this:

Select sum(pinhits) / sum(pins) "Hit Ratio",
       sum(reloads) / sum(pins) "Reload percent"
From v$librarycache
Where namespace in ('SQL AREA', 'TABLE/PROCEDURE', 'BODY', 'TRIGGER');

Brian Cryer feels that if this hit ratio is not at least 85%, the SHARED_POOL_SIZE may not be large enough. Although less likely, the init.ora parameter OPEN_CURSORS (default 100) may also need to increased.

Shared SQL Pool

To improve Oracle performance, Oracle user processes saves in the Shared SQL pool intermediate results from SQL commands it previously optimized. So before performing a hard disk operation, Oracle can service identical requests simply by retrieving them from its cached memory for reuse.

when a SQL statement is issued by a user process. a workheap is allocated, and parse tree and execution plan is built in the shared pool.

LoadRunner Oracle monitor "Execute count" statistic reports the total number of calls (user and recursive) that execute SQL statements.

The shared pool also contains headers of PL/SQL packages and procedures that have been executed, the library cache, data dictionary cache, and performance information for each type of object in the library cache.

To get the current size of the shared pool:

SELECT name, value FROM v$parameter WHERE name = 'shared_pool_size';

Oracle Metalink document 263809.1 says the minimum shared_pool_size setting for 64-bit Oracle 10g is 144 MB.

To get the amount of free memory in the shared pool:

SELECT name, bytes "Size" FROM v$sgastat WHERE name = 'free_memory';

To get the percentage of free memory in the shared pool:

select round((sum(decode(name,'free memory',bytes,0))/sum(bytes))*100,2) from v$sgastat

If this percentage is above 35% even under intense load, it is too high. Low free values may be a cause for other issues with components of the SGA.

Private Area

It contains session-specific information such as bind variables, environment, and session parameters, runtime stacks, and buffers. Each private area is deallocated after the cursor to the session is closed.

The number of private SQL areas a user session can have open at one time is limited by the value of the OPEN_CURSORS init.ora parameter.

Within the private SQL area, the persistent area contains information that is valid and applicable through multiple executions of the SQL statement.

The runtime area contains data that is used only while the SQL statement is being executed.

The v$librarycache view lists the get hit ratio for each namespace (SQL AREA).

select namespace, gethitratio from v$librarycache where namespace = 'SQL AREA'

This is not specific to SQL and PL/SQL blocks.

When Oracle needs to reparse SQL or PL/SQL blocks when it attempts to execute it, a Shared pool reload occurs.
Calculate the Shared Pool Reload Ratio to measure the adequacy of the shared pool size:

select round(sum(reloads)/sum(pins)*100,2)
from v$librarycache
where namespace in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER')

To identify SQL statements have had to be re-parsed, use this query provided by Brian Cryer:

select executions, t.sql_text
from v$sqlarea a, v$sqltext t
where parse_calls >1 and parse_calls = executions 
      and a.address=t.address and executions > 10000
order by executions desc;

If this returns a large number of rows, increase the number of ‘executions’ specified in the query.
If it returns no rows, decrease the number of executions specified in the query.

Data Dictionary Cache

Oracle uses a Data Dictionary Cache to hold internal database information needed to parse SQL into the internal information Oracle uses to fetch the information requested.

The internal information includes extent locations datafiles and segments associated with the tables and columns referenced by the SQL.
It also contains user account data, security and access privileges, and available free storage space.
All this information is stored in a Dictionary Table on a hard disk.
It does not need to be retrieved again from the hard disk if the same (or similar) SQL queries are run repeatedly.

The Data Dictionary Object Alert reports excessive requests or contention here.

But calculate the Dictionary Cache Hit Ratio to monitor the proportion of requests for information from the data dictionary table rather than from the data dictionary cache:

select sum(gets-getmisses)*100/sum(gets) from v$rowcache

Cache misses should be expected after instance startup since the data dictionary cache is empty at startup.
cache misses should decrease as more data is read into the cache until a "steady state" is reached, in which the most frequently used dictionary data is in the cache.

Brian Cryer feels that this miss ratio should be less than 15%.

It's counter-intuitive, but Oracle is faster with a smaller pool if a lot of non-reusable SQL are cycling in and out of the pool. This is because the larger the space, the more time Oracle needs to manage that space.

server Control Structures

such as the Database character set.

Database Block Buffer Cache

The data block buffer stores the most recently queried data obtained from database files on hard disk.

The effectiveness of the buffer cache is usually measured by the buffer cache hit ratio , which is the ratio of physical requests for data satisfied by a logical read of data already in the buffer cache. This ratio requires a bit of calculation because the number of logical reads is the total of "consistent gets" and "database block gets" statistics maintained by V$SYSSTAT and LoadRunner:

"physical reads" / ( "dbblock gets" + "consistent gets" ) is obtained using this SQL query:

select round((1-(pr.value/(bg.value+cg.value)))*100,2) 
from v$sysstat pr, v$sysstat bg, v$sysstat cg
where pr.name='physical reads' and bg.name='db block gets' and cg.name='consistent gets'

Action is needed if this falls below 90%, according to Brian Cryer.

While higher percentages are better since access to data from memory is faster than with an IO operation to disk, there is a point of diminishing returns when increasing the size of the database buffer takes space needed by other parts of the SGA so that the SGA paging seriously degrades performance.

[_] In 8i, DB_BLOCK_BUFFERS was used to adjust the size of the Buffer Cache. This is deprecated by 9i, which specifies buffer size in DB_CACHE_SIZE bytes, which can be changed without shutting down the database. To help in selecting the optimum size, Oracle 9i provides the v$db_cache_advice view as part of overall SGA memory management.

Since 8i, the Buffer Cache is made up of sections:

• a default buffer of the default/standard size (preferrably 32K)
• a buffer recyle pool of objects that are used and discarded rapidly
• a buffer keep pool of objects that you want to hold on to as long as possible
• buffers for non-default sizes (2K, 4K, 8K, 16K, but not 32K if that's the default)

Each of these pools are split into "working data sets" with their own LRU latches.

Because of the wide mix of single point random reads, large sequential reads, and very large direct reads, Oracle retrieves full table scans to the Least Recently Used (LRU) low/cold area. (Technically, the table is set with with a "forget bit" buffer flag).
Small tables are retrieved into the Most Recently Used (MRU) high/hot area.

[_] Whether a table is Long or short can be defined by Optimizer hints coming down into the row source access layer of Oracle. But it can be set with the initialization parameter SMALL_TABLE_THRESHOLD. In Parallel Server environments supporting parallel query servers across instances, a table is considered small for scanning purposes if the cache partition fits in the current SGA. Oracle can scan short tables that completely fit in memory quicker than using an index.

If Oracle needs to do I/O, full table scans of long tables is generally bad for overall performance.

The proportion of table scans occuring on short tables is calculated with this query:

select round((shrt.value/(shrt.value+lng.value))*100,2)
from v$sysstat shrt, v$sysstat lng
where shrt.name='table scans (short tables)'and lng.name='table scans (long tables)'

The "table scans (long tables)" statistic in V$SYSSTAT counts the rows processed during full table scan operations.
The "table scans (short tables)" statistic in V$SYSSTAT counts the rows processed when the table fit in the SGA.

A low proportion of short scans may indicate lack of indexes on large tables poorly written SQL which fails to use existing indexes which requires a large percentage of the table to be returned.

The "table scan blocks" statistic in V$SYSSTAT tracks the number of rows retrieved sequentially by Oracle.
The "table scan rows" statistic in V$SYSSTAT counts the rows processed during all table scan operations.

The v$bh view displays the status of each database cache buffer:

• free = not in use
• xcur = held exclusively by this instance
• scur = held in cache, shared with other instances
• cr = for consistent read
• read = being read from disk
• mrec = in media recovery mode
• irec = in instance (crash) recovery mode

In single-instance mode, concurrent I/O on a database block increments the "buffer busy" wait event statistic in the V$SESSION_WAIT table.
Buffer busy waits should not be greater than 1 percent. A high number here usually indicates a "I/O bound" application using a P3 unique identifier at P2 (block offset into that file) within P1 (the database file number). High buffer busy waits in an Oracle Parallel Server usually indicates that nodes are competing for database blocks. In an Oracle Parallel Server, it is common to wait for the buffer as the Distributed File System (DFS) lock gets escalated to Exclusive Mode.

To decrease buffer waits, consider:

• increase INITRANS, which creates space in the block to allow multiple ITL slots.
• increase the PCTFREE on the table where this block exists (this writes the ITL information up to the number specified by MAXTRANS, when there are not enough slots built with the INITRANS specified).

This web page recommends looking into $WAITSTAT for clues:

• If the wait is on a segment header. consider increasing the freelist groups or increase the pctused to pctfree gap.
• If the wait is on an undo header, consider adding rollback segments.
• If the wait is on an undo block, reduce the data density on the table driving this consistent read or increase the DB_CACHE_SIZE.
• If the wait is on a data block, move data to another block to avoid this hot block, increase the freelists on the table, or use Locally Managed Tablespaces (LMTs).
• If the wait is on an index block, rebuild the index, partition the index, or use a reverse key index. To prevent buffer busy waits related to data blocks, you can also use a smaller block size: fewer records fall within a single block in this case, so it's not as "hot." When a DML (insert/update/ delete) occurs, Oracle Database writes information into the block, including all users who are "interested" in the state of the block (Interested Transaction List, ITL).

The average wait time for sequential versus scattered reads:

prompt "AVERAGE WAIT TIME FOR READ REQUESTS"
select a.average_wait "SEQ READ", b.average_wait "SCAT READ"
from   sys.v_$system_event a, sys.v_$system_event b
where  a.event = 'db file sequential read'
and    b.event = 'db file scattered read';

The "DB File Scattered Read" wait event counter is incremented whenever a multi-block read needs to wait while retrieving blocks scattered throughout the buffer cache.

Scattering usually happens with full table scans (a scan through the whole table), OPQ, or sorting since they rarely fall into contiguous buffers when they are pulled into memory.

High counts here may indicate missing or suppressed indexes.

High counts are more acceptable if the database resides on solid-state hard disk devices which are six times faster than traditional disk devices (100,000 I/Os per second).

The "DB File Sequential Read" wait event counter is incremented whenever a single-block needs to wait while retrieving data into contiguous memory.

In general, this event is indicative of disk contention on index reads since single block reads are usually the result of using indexes and short tables (usually a single-block read with p3=1, but can be multiple blocks). This event is also used for rebuilding the controlfile and reading datafile headers (P2=1).

It is usually good for this number to be large for a high-transaction OLTP system since there is less overhead reading from single rather than multiple blocks in cache.

However, index scans can also be high if SQL is not coded efficiently, such as with unselective indexing or poor joining order for multiple table joins. Use the Statspack report to help identify such queries in question.
Although hash-area joins should be done in PGA memory, they can also be memory hogs causing high wait numbers for sequential reads and direct path read/write waits.

[_] Consider adjusting the "PGA AGGREGATE TARGET" parameter to reduce waits on sequential reads.

Waits for sequential read can also be high if the DB_CACHE_SIZE is too small.

WISE (Workload Interface Statistical Engine) is called "intelligent" becuase it graphs STATSPACK Oracle10g Automated Workload Repository (AWR) ADDM, ASH time-series data as hourly and daily trends graphs, compared against historical baselines in a repository. This visualization allows Sr. DBAs to proactively spot subtle and signatures causing repeating performance problems.

Redo Log Buffer Cache

The redo log buffer is used to store redo information in memory before it is flushed to the Online Redo Log Files.

Its size is initialized according to the LOG_BUFFER (bytes) parameter.
The number of redo log space requests can be monitored using the v$sysstat view.

The "change write time" statistic in v$sysstats reports the elapsed time for redo write for changes made to CURRENT blocks, in 10s of milliseconds.

Redo Log Buffer Latches

Entries in the redo log buffer are necessarily sequential for sequencing redo operations. To enforce "mutual exclusion", user processes must use a simple low-level serialization mechanism called latches. Like a baton being passed among runners, Oracle provides only one allocation latch to ensure that only one Oracle user process at a time may allocate and update a particular space in the redo buffer.

[_] The number of redo copy latches should be the number of CPUs in the parameter LOG_SIMULTANEOUS_COPIES. This is because multiple redo copy latches can exist on machines with multiple CPUs since each CPU can have its own redo log buffer to copy entries to the redo log buffer concurrently.

[_] If a redo entry fits into the threshold size defined by the initialization parameter LOG_SMALL_ENTRY_MAX_SIZE, it can be copied on the redo allocation latch. The number of times this happens is calculated thus:

select round(greatest((sum(decode(ln.name,'redo allocation',misses,0))
   /greatest(sum(decode(ln.name,'redo allocation',gets,0)),1)),
   (sum(decode(ln.name,'redo allocation',immediate_misses,0))
   /greatest(sum(decode(ln.name,'redo allocation',immediate_gets,0))
   +sum(decode(ln.name,'redo allocation',immediate_misses,0)),1)))*100,2)
from v$latch l,v$latchname ln where l.latch#=ln.latch#

If a redo entry does not fit, the user process must obtain a redo copy latch before copying the entry into the buffer.

While holding a redo copy latch, the user process obtains the allocation latch, performs allocation, and releases the allocation latch.

Next the process performs the "copy under the copy latch" into its allocated space in the buffer and then releases the redo copy latch. The number of times this happens is calculated thus:

select round(greatest((sum(decode(ln.name,'redo copy',misses,0))
/greatest(sum(decode(ln.name,'redo copy',gets,0)),1)),
         (sum(decode(ln.name,'redo copy',immediate_misses,0))
/greatest(sum(decode(ln.name,'redo copy',immediate_gets,0))
         +sum(decode(ln.name,'redo copy',immediate_misses,0)),1)) )*100,2)
from v$latch l,v$latchname ln where l.latch#=ln.latch#

Because the user process does not obtain the copy latch while holding the allocation latch, it holds the allocation latch for a very short period of time. That's why if a user process is not willing to wait, it issues a timeout.

After allocating space for a redo entry, the user process copies the entry into the buffer.

Oracle collects latch activity statistics in the v$latch view:

• gets = successful willing to wait requests for a latch (denominator)
• misses = how many times a process didn't successfully request a latch.
• Spin_gets = latch is obtained after spinning at least once.
• Sleeps = a willing to wait process slept.
• Waiters_woken = a sleeping process was 'disturbed'

The frequency of waits occuring is calculated thus:

select round(((sum(gets) - sum(misses)) / sum(gets))*100,2) 
from v$latch

The percentage of time that a latch which is willing to wait and retry after a miss is calculated thus:

select round(((sum(immediate_gets) - sum(immediate_misses)) 
/ sum(immediate_gets))*100,2) 
from v$latch

The "Latch Free" wait event counter increments when a latch free miss occurs.
When latch miss ratios are greater than 0.5 percent, investigate:

Issue	Cause
bind variables not used	library cache latch
redo generation	redo allocation latch
buffer cache contention	cache buffers LRU chain
hot blocks in the buffer cache	cache buffers chain

Large Pool

A space reserved for the multi-threaded server introduced with Oracle 8 parallel processing, shared server.

It is also used by the Recovery Manager (RMAN) process doing backups introduced with Oracle 8 to replace Oracle's EBU (Enterprise Backup Utility) for Oracle 7.

3rd party backup solutions that interface with Oracle's Media Management Layer (MML) include:

• ADSM (Advanced Distributed Storage Manager)
• Alexandria
• CA-UniCenter Cheyenne
• Legato
• Veritas

Java Pool

Stores Java code used by user sessions.

Since a bug was fixed in 8.1.7, get the high water mark (HWM) of connected sessions since startup with v$resource_limit.

Streams Pool

Used to buffer distribution of data among multiple databases by the streams feature introduced with 10g.

SGA Memory Management

The V$SGA_DYNAMIC_COMPONENTS view displays the size of "granules" of memory units Oracle adjusts memory:

16 MB when the total size is 128MB or more.
  4 MB when the total size is under 128MB.

This view also displays the current_size of components of in the SGA:

Manage- ment	SGA component		Init. Parameter	Note
Automatically tuned by Oracle	Fixed Size (overhead structures)		(Oracle allocates)
	Shared Pool		SHARED_POOL_SIZE=0
	Large Pool		LARGE_POOL_SIZE=0
	Java Pool		JAVA_POOL_SIZE=0
	Database Buffer Cache default pool for DB_BLOCK_SIZE=32768	Keep Cache	DB_CACHE_SIZE=0	DB_KEEP_CACHE_SIZE=_
		Recycle Cache		DB_RECYCLE_CACHE_SIZE=_
Manually sized (deducted from SGA)
	other than standard DB_BLOCK_SIZE		DB_2K_CACHE_SIZE=_
			DB_4K_CACHE_SIZE=_
			DB_8K_CACHE_SIZE=_
			DB_16K_CACHE_SIZE=_
	Redo Log Buffer		LOG_BUFFER=1048576	Should never exceed 5MB.
	Streams Pool		STREAMS_POOL_SIZE=_

On 32-bit machines, the maximum SGA cannot exceed 1.7G from the 4GB address space on many unix platforms.

With 9i, the components consuming memory in the SGA had to be (continually) adjusted manually (by changing the init.ora) to avoid ORA-04031 out-of-memory allocation faults.

With 10g, DBAs set a single new SGA_TARGET initialization parameter a space that Oracle Automatic Shared Memory Management (ASMM) automatically distributes. The SGA_MAX_SIZE cannot be dynamically resized.

Query the V$SGA_TARGET_ADVICE view and set the SGA_TARGET to the sga_size value that corresponds to the least estimated physical reads ("ESTD_PHYSICAL_READS"). Use command:

SQL> alter system set sga_target=160M scope=both;

The SGA_TARGET value includes space previously specified for the shared_pool_size and startup overhead of other pools.

SQL> select * from v$sga;

The SGA_TARGET value also includes "Fixed Size" space that Oracle sets based on the operating system.

The paradigm shift in thinking is that values to maximize in 9i in 10g become "overhead" values to be minimized. So in 10g do this:

SQL> alter system set shared_pool_size=0;
SQL> alter system set db_cache_size=0;

Query the v$sgainfo dynamic view for the "Startup overhead in shared pool" value.

Manual tuning in 10g involves setting the minimum amount of space that should be allocated at start-up so that Oracle has the memory already dedicated when its needed.

Explicit settings are still needed for components that 10g does not adjust automatically:

LOG_BUFFER, typically 64K - 162K (with SAP recommending 1,048,576), should be increased if the 'redo log space requests' number in v$systat is not zero.
The keep buffer cache, the recycle buffer cache, and nonstandard block-size buffer caches.

More on the 10g ASMM

Processes Consuming CPU

The PROCESSES parameter in init.ora specifies the maximum number of processes accessing the database.
The v$process view displays all processes that are connected to the database, including background and user processes.
The V$bgprocess view lists all background processes

List Oracle background processes with this query:

select * from v$session where type ='BACKGROUND';

System Background Processes

These background processes are mandatory (required to startup each Oracle instance):

System Monitor (SMON)

performs automatic instance crash recovery on the Oracle instance in the event it fails.
If the command shutdown abort is issued by the user, Oracle doesn't have time to write modified database changes from the System Global Area (SGA) down to disk. It wakes itself up routinely to clean up free space in datafiles (much like a defragmenter for hard drives). It looks for free spaces and organizes them into contiguous areas.

Process Monitor (PMON)

performs process recovery on user processes that failed. It analyzes user processes that access the database, performs "garbage collection" (releasing memory allocation locks held by failed processes), and restarts failed processes. PMON also monitors and restarts any failed dispatcher or server processes if MTS (Shared Server Architecture) is being used. In RAC, PMON is a service registration agent.

Database Writer (DBWn)

The Database Writer (DBWR) writes and retrieves blocks from datafiles through the buffer cache.
Set the DB_WRITERS parameter to define as many DBWR processes as there are physical disks used to store data files.

The "write requests" statistic in V$SYSSTAT and LoadRunner stores the number of times DBWR takes a batch of dirty buffers and writes them to disk.

Log Writer (LGWR)

The Log Writer periodically "flushes" to disk blocks (redo logs) in the circular Redo Log Buffer when a user session commits or rolls back data,

This activity is counted by the "redo sync writes" statistic in V$SYSSTAT when the redo log buffer must be written out to disk due to a commit.

The "Log File Sync" wait event tracks how often the redo log file sync process must wait to complete.
Based on recommendation from this web page:
To reduce log file sync waits, try commiting a batch of 50 instead of one at a time. Put redo logs on a faster disk, or alternate redo logs on different physical disks, to reduce the archiving effect on LGWR. Don't use RAID 5, since it is very slow for applications that write a lot. Potentially consider using file system direct I/O or raw devices, which are very fast at writing information. "

Checkpoint Process (CKPT)

The checkpoint (CKPT) process frees up limited memory space and ensures instance recovery. This background process monitors the number of dirty buffers that have not been written to disk.

Several V$SYSTAT (LoadRunner Oracle) metrics measure this activity:

"DBWR buffers scanned" reports the total number of buffers looked at when scanning each LRU set for dirty buffers to clean. This count includes both dirty and clean buffers. Divide by DBWR LRU scans to find the average number of buffers scanned.

"DBWR free buffers found" reports on the number of buffers that DBWR found clean when it was requested to make free buffers. Divide by DBWR make free requests to find the average number of reusable buffers at the end of each LRU.

[_] Depending on the LOG_CHECKPOINT_INTERVAL init parameter, CKPT also gives the DBWR a wakeup call to write dirty buffer to disk.
Several V$SYSSTAT (LoadRunner Oracle) metrics measure this activity in detail:

"DBWR checkpoints" reports on the number of times the DBWR was asked to scan the cache and write all blocks marked for a checkpoint.

"DBWR Flush object cross instance calls" reports the number of times DBWR received a flush by object number cross instance call (from a remote instance). This includes both checkpoint and invalidate object.

"DBWR checkpoint buffers written" reports on the number of buffers that were written for checkpoints.

"DBWR cross instance writes" reports on the total number of blocks written for other instances so that they can access the buffers.

At a specified time, it updates all the data and control files with a new log file control number if the process is optionally enabled by parameter CHECKPOINT_PROCESS containing a TRUE value.

The "Free Buffer" wait event counts the number of times the system waits for an available buffer in memory, when the Database Writer (DBWR) cannot write fast enough to keep up with the substantial amount of DML (insert/update/delete) being done. This can occur when the buffer cache is full of multiple versions of the same buffer (which is inefficient). Or SQL is causing data to flood the buffer cache with index blocks, leaving none available for a particular statement waiting for the system to process.

To counteract this, increase the efficiency of SQL, increase the DB_BUFFER_CACHE, accelerate incremental checkpointing, specify more DBWR processes, or increase the number of physical disks.

Other background system processes ...

Archival Process (ARCn/ARCH)

copies filled online redo logs to the archived redo log files (tape or other media). Although listed as optional, in practice it needs to be running because online redo logs can fill up quickly. This is unless the instance is replicated read-only.

[_] The v$archive view provides information on the archived logs written by ARCn (ARCH pre Oracle8i).

In a RAC setup, the ARCH processes for each instance makes redo logs available to other instances during recovery.

The database will hang if it is set to operate with redo logggin (with ARCHIVELOG mode) but the ARCHIVE_LOG_START parameter in the init.ora file is not set to TRUE.

[_] The DBWR_IO_SLAVES parameter defines the number of I/O slaves to improve performance.

File Monitor (FMON)

spawns external non-Oracle storage vendors' processes which map Oracle data files to physical external storage devices (subsystems) defined in the FILE_MAPPING initialization parameter.

Recover (RECO)

automatically cleans up the database for failed or suspended transactions in a distributed database system. It first attempts to access databases involved in in-doubt distributed transactions enabled by the DISTRIBUTED_TRANSACTIONS parameter> zero.

Rebalance (RBAL)

coordinates disk group rebalance activity within an Automatic Storage Management (ASM) instance.

(MMON)

collects statistics for the Automatic Workload Repository (AWR) new with Oracle 10g.

Memory Manager (MMAN)

is the SGA Memory Broker (new with 10g) managing automatic shared memory, coordinating the sizing of memory components.

(MMON)

performs frequent and lightweight manageability-related tasks, such as session history capture and metrics computation. New with 10g

Wakeup (WMON)

wakes up other process it monitors.

Trace Writer (TRWR)

creates trace trace file which store commands and responses to commands (to start-up a database, etc.)

plus operating system Process ID for each running process.

The location of trace files is specified in parameter BACKGROUND_DUMP_DEST. By default, that's folder /admin/INSTANCE_NAME/bdump under the ORACLE_BASE folder.

Event Monitor (EMNn)

Job Queue (CJQ)

This scheduler is the Coordinator main program which executes slave programs.
Parameter job_queue_processes (default 10) defines the maximum number of scheduled jobs allowed to execute simultaneously.

If you are running Data Guard (DG)/Streams/replication:

DMON - Data Guard Broker

SNPn Snapshots

Oracle Internal job queue scheduling is refreshed by snapshots.
The "n" can be as large as the number specified in parameter JOB_QUEUE_PROCESSES for the instance.

(MRP) Managed recovery process

For Data Guard, the background process that applies archived redo log to the standby database.

ORBn

performs the actual rebalance data extent movements in an Automatic Storage Management instance. There can be many of these at a time, called ORB0, ORB1, and so forth.

OSMB

is present in a database instance using an Automatic Storage Management disk group. It communicates with the Automatic Storage Management instance.

Remote File Server process

In Data Guard, the remote file server process on the standby database receives archived redo logs from the primary database.

Queue Monitor Process (QMNn)

manages Oracle Streams Advanced Queuing.

If an Oracle Parallel Server Real Application Clusters (RAC) multi-instance coordination is active:

Dispatchers (Dnnn)

acts as a type of proxy for the user process, passing user requests to the SGA request queue, and returns the server responses back to the correct user process.

With Shared Server, client connections are ultimately sent to a Dispatcher, which binds the client to an already established virtual circuit.

As a general rule, on start-up a dispatcher should be created for every 1000 connections (rounded up to the next integer) as specified by the SHARED_SERVERS parameter. But monitor views V$QUEUE, V$DISPATCHER, and V$DISPATCHER_RATE. Parameter MAX_SHARED_SERVERS limits the number of shared server processes running simultaneously.

Parameter SHARED_SERVER_SESSIONS specifies the total number of shared server user sessions to allow, used to reserve user sessions for dedicated servers.

A shared dispatcher process is configured using init.ora parameter:

 dispatchers = (PROTOCOL=TCP)(SERVICE=orclXDB)

Within a share Multi-Threaded Server (MTS), a dispatcher process is configured using init.ora parameter:

 MTS_DISPATCHERS = ("tcp, 4", "spx, 2")

Implement Shared Server in tnsnames.ora:

(DESCRIPTION= 
  (ADDRESS=(PROTOCOL=tcp)
    (HOST=sales-server)(PORT=1521)) 
    (CONNECT_DATA= (SERVICE_NAME=sales.us.acme.com)
    (SERVER=shared) )) 

SAP stores the master tnsnames.ora and sqlnet.ora files in folder /usr/sap/trans along with the common transport directory sapconf.

Oracle dynamically adjusts the number of shared server processes based on the length of the request queue.

Qnnn

Qnnn background processes parallel index creation, query, and data loading.
The "nnn" can be as large as the number specified in parameter ???.

Pnnn

starts and stops Parallel Query Slaves participating in parallel query operations (parallel index creations, table creations, and queries).
The "nnn" can range between PARALLEL_MIN_SERVERS and PARALLEL_MAX_SERVERS.

Diagnosability Daemon (DIAG)

monitors instance health and captures data for instance process failure recovery.

Lock Manager for Global Cache Service Processes (LMSx)

handles acquisition interrupts and blocking interrupt requests to control the flow of messages to remote Global Cache Service (GCS) resource instances.

For cross-instance consistent read requests, it creates a consistent read version of the block for the requesting instance,

The "x" varies depending on the amount of messaging traffic among nodes in the cluster, but can be as large as 10 Global Cache Service Processes provided by RAC software.

Lock (LCKn)

performs inter-instance locking with global enqueue requests and cross-instance broadcasts. Its workload is automatically shared and balanced when there are multiple Global Cache Service Processes (LMSx).

The "n" can be as large as the number specified in parameter GC_LCK_PROCS.

Global Enqueue Service Daemons (LMDx)

controls access to global enqueues and resources from another instance by managing (locking) requests for Global Cache Service enqueues and detecting request deadlocks.

User and DB Server Processes (Snnn)

Applications and utilities access the RDBMS through a user process which connects to a server process, which can be dedicated to one user process or shared by many user processes.
The server process parses and executes SQL statements submitted to it, reads data blocks from the data files into the database buffer cache, then returns the result sets back to the user process.

The "Parse time CPU" V$SYSTAT and LoadRunner Oracle monitor statistic reports the centiseconds (10s of millseconds) spent in parsing.

The total "Parse time elapsed" is the total waiting time that parse resources had to wait.

A "hard parse" is when a SQL statement is not found in the shared pool library cache, and therefore needs more work to optimize.
A "soft parse" is when a SQL statement is matched with one previously submitted to the database engine which has been optimized.

The "parse count" statistic in V$SYSSTAT and LoadRunner counts the number of times the user makes parse functions from either the OCI or the Oracle Precompilers, independant of any cache benefits in the library cache. This statistic is used as a denominator to determine the V$LIBRARY_CACHE table's library cache hit ratio against the shared SQL area.

The number of parses that actually occured is calculated by subtracting from "parse count (total)" the "Session cursor cache hits" statistic counting the number of SQL statements which did not have to be reparsed.

Either a large amount of once-only code is being used by the database or the shared sql area is too small if there is a high CPU Parse Overhead Percentage -- the proportion of database CPU time being spent in ("prs") parsing SQL and PL/SQL code versus all other ("exe") activity.

select round((prs.value/(prs.value+exe.value))*100,2)
from v$sysstat prs, v$sysstat exe
where prs.name like 'parse count (hard)' and exe.name= 'execute count'

User + Recursive Calls = Session Calls

A high number of recursive calls (SQL statements to be generated against Oracle's internal tables through the RPI) occurs when:
• Dynamic extension of tables are poorly sized
• Rollback segments grow and shrink due to unsuitable OPTIMAL settings
• Large amounts of sort to disk result in the creation and deletion of temporary segments
• Data dictionary requests are missed
• Complex triggers, integrity constraints, procedures, functions and/or packages are invoked
• Use of Oracle automation features (Automatic Workload Repository, Automatic Database Diagnostic Manager, Enterprise Manager, etc.)

Roger Schrag of dbspecialists uses this 10g SQL to identify recursive activity

SELECT   A.username, COUNT(*), SUM (B.sharable_mem) sharable_mem,
SUM (B.persistent_mem) persistent_mem,
SUM (B.runtime_mem) runtime_mem,
SUM (B.sharable_mem + B.persistent_mem + B.runtime_mem) total_mem
FROM     dba_users A, v$sql B
WHERE    A.username IN ('DBSNMP', 'SYS', 'SYSTEM', 'SYSMAN')
AND      B.parsing_user_id = A.user_id
GROUP BY A.username;

Oracle allocates resources (Call State Objects) to keep track of relevant user call data structures every time there is a login, parse, or execute.

"user calls" and "recursive calls" statistic in V$SYSSTAT and LoadRunner Oracle monitors track the total number of calls since inception.

A high recursive call ratio of ("rcv") recursive calls vs. ("usr") "user calls" gives an indication of how much internal work activity is occurring:

select round((rcv.value/(rcv.value+usr.value))*100,2)
from v$sysstat rcv, v$sysstat usr
where rcv.name='recursive calls' and usr.name='user calls'

Because session time is the total of time spent on user and recursive operations,
The amount of User time spent during user calls can be calculated from LoadRunner Oracle monitors:
Subtract the "CPU used by this session" from "Recursive CPU usage".

The granularity of these statistics is in 10s of milliseconds. The start and end user-call time can be the same since some user calls can complete within 10 milliseconds. In such a case, 0 milliseconds are added to the statistic. A similar problem can exist in the reporting by the operating system, especially on systems that suffer from many context switches.

Spooling Output

Spool output to the output.txt file:

SPOOL C:\output.lst;

The default extension for spool files is .lst or .lis.

Turn it off!

SPOOL OFF;

Stored SQL Procedures

Stored subprograms can take parameters, which makes them extremely flexible.

View DBA_OBJECTS contains information about every database object (including those owned by SYSTEM and SYS) of all types of objects (such as tables, indexes, sequences, and so on). The DBA_SOURCE view lists:

• stored procedures -- code units that can be passed arguments and return values.
• stored functions -- code units that is passed an argument and returns one value.
• packages -- logically grouped collections of procedures, variables, and functions.

The "Session stored procedure space" among LoadRunner oracle metrics reports the amount of memory that each session is using for stored procedures.

Built-in Tables

Table user_tab_comments stores comments on each table in the schema.

Table user_col_comments stores comments on each column for each table in the schema.

Resources

From Oracle Corporation:

• Oracle® Database Performance Tuning Guide for 10g R2 and 9i

Oracle 8 Tutorial by Michael Gertz at UC Davis offers a good introduction.


Search the Oracle Release 8.1.7 Documentation Library
Oracle8i Administrator's Guide for Windows NT
Oracle8i Concepts
Error Messages

Portals to information and links:

• DMOZ's PL/SQL Directory is comprehensive
• ORAFAQ.net

Sources of PL/SQL scripts:

• PLNet.org is an open source repository for PL/SQL developers
• ORAFAQ.com's PL/SQL code examples
• Quest Software's PL/SQL Pipeline (formerly RevealNet) provides a free community for Oracle developers.
• The PL/SQL Cellar - Free Oracle PL/SQL scripts including a bitwise operations package and message digest algorithms.

Consultants:

• Dave Wooten's Database Administration Notes has gems such as 'Understanding the "snapshot too old" error message" and Causes of ORA 3113 "end-of-file on communication channel" errors
• PLSolutions.com provides consulting and training on Oracle PL/SQL and PL/Vision

Take the Brainbench certification test on Oracle 7, 8, 8i, 9i, 10g Administration