3. Server Concepts and Administering --------------------------------- 3.1 Oracle7 Architecture and tuning ------------------------------- - Oracle database. - Files: database, redo log (2 minimum), control, INIT.ORA. Mirroring: redo log & control. README files. Always allocate tablespace's physical datafiles in STANDARD size (i.e. all the files in database should have the same size. That's why it's easy to manage (to move between physical disk drives) files because of equal sizes. All data files should be placed in the SAME directory on each disk drive. Trace files and alert file are BACKGROUND_DUMP_DESC (usually ...\rdbms\trace) directory. An alert file - a chronological log of message and errors. The alert file should be periodically checked and removed. Trace files are created by Oracle in case of error. They can be formatted with appropriate utility and sent to Oracle technical support for analysis. README files (README.FIRST, README.DOC, readmeunix.doc) are useful for administrator. The should be carefully read before installing. - Compatibility notes. COMPATIBLE INIT.ORA parameter. If Oracle 7.1.3 (and possible higher) is started up without COMPATIBLE parameter in INIT.ORA it works as 7.0, that is some 7.1 features as read only tablespaces CAN NOT be used. To allow using this features COMPATIBLE = 7.1 parameter should be specified in INIT.ORA. - INIT.ORA parameter SINGLE_PROCESS allows multi- or single- instance processing. If SINGLE_PROCESS = TRUE then only ONE user process can be executed at a time. If FALSE - then multiple processes can work with this instance. - INIT.ORA syntax: "\" - continuation character at end of line. "#" = comment at beginning of line Double quotas " can surround parameters value ("param_value"). Useful if param. value contains special character. Several parameters delimited by SPACE can be placed on a single line, for example: PROCESSES 100 LICENSE_MAX_USERS = 5 OPEN_CURSORS = 10 How to keep track of INIT.ORA parameters: 1. Server administrator's Guide (Appendix A), 1993 2. Oracle7 Server Documentation Addendum, 1994 3. README.DOC file on your distribution kit. - Memory/disk space measurement units in Oracle (be CAREFUL there is NO UNIFORM measurement conventions): - megabytes M - kilobytes K - bytes - Oracle blocks. Block=DB_BLOCK_SIZE bytes INIT.ORA - operating system blocks (size of traces files ...) Related topics: - Trace files for server (user) process is determined by USER_DUMP_DEST INIT.ORA parameter. In this case, tracing can be set up on database level (SQL_TRACE=TRUE INIT.ORA par.) or on session level ALTER SESSION SET SQL_TRACE=TRUE). - MAX_DUMP_FILE_SIZE = 500 (OS blocks by default) to limit ANY trace file size, preventing from arbitrary growing of ALERT.LOG - Oracle Instance (SGA + background processes). One instance - one database (exception - parallel server). SGA contains: - buffer pools for database blocks (Database Buffer Cache). Each buffer holds a single Oracle data block. - redo log buffer containing redo log entries, - shared pool for SQL statements and dictionary cache entries. Size of shared pool is determined by SHARED_POOL_SIZE (measured in BYTES) init.ora parameter only one parameter). Shared pool consists of: - library cache holds all SHARED SQL and PL/SQL statements. It contains the private and shared SQL and PL/SQL areas (context area for SELECT statements). SQL statements remain in the library cache until aged out by LRU algorithm. See Library cache consists of shared part and private part. Shared part for each statement contains: - Parse tree: A representation of the results of parsing a SQL statement; - Execution plan: A roadmap Oracle builds containing the plan of how a statement will be run. It is written after each statement is optimized. Contained in PGA for single-threaded and in SGA for multi-threded servers. Private part contains: - The persistent portion that occupies space in the SGA for the life of every cursor associated with SQL statement; - The run-time portion that is acquired when a SQL statement executes, and is released when the statement completes. M.Corey in PH95p#5v1 recommends the following: +--------------------------------+ ! Computer SHARED_POOL_SIZE ! ! Memory ! +--------------------------------+ ! 16M 4000000 ! ! 32M 12000000 ! ! 64M 30000000 ! ! 128M 60000000 ! +--------------------------------+ - Tuning private SQL and PL/SQL area. See [STUN72] pp. 8-4 - 8-5 Rule of thumb: Never allocate more then 60% of your memory to SHARED_POOL_SIZE - Background processes: - LGWR and DBWR, SMON, PMON, ARCH .... - Naming conventions for processes: ORA__DBWR, ORA__LGWR, ... - User and server processes. Server process parse & execute SQL, reads data blocks into shared pool in SGA, returns result to the user process. Two task and single (one) task. Single task - user and server processes are combined into single operating system process. Killing session: - Sessions. ALTER SYSTEM KILL SESSION ',' If an active session cannot be interrupted (due to network I/O or rolling back a transaction) the session can't be killed until the operation completes. If the session can't be killed in 60 seconds it marked as "KILLED" in V$SESSION. Sometimes terms "server process" and "user process" are referred as the same things in Oracle documentation, but it's true with seldom used one task architecture only! - Processing SQL statements. (chapter 4 of A.D.G.) Parse phase: - check whether the statement is already in the shared pool, - checks syntax (parsing), compiling to parse tree (P-code) and store into shared SQL area (library cache). This step also includes querying the data dictionary for object resolution, security privileges and the most effective search path. Shared pool of size SHARED_POOL_SIZE (INIT.ORA) bytes includes the library cache. Also the memory structure called cursor is created. Cursor contains the record of statement status information. Execute phase: - applies parse tree to data buffers; - perform physical or logical read/writes if a sort is required due to count(), group by, DISTINCT ....; Fetch phase: - performs physical or logical reads if required; - retrieves rows of data for a SELECT statement; Notes: SELECT FOR UPDATE does not return data to the user during ?? execute phase, but visit the blocks to take out locks. The number of fetches is determined by number of rows satisfying the query conditions and the array size specified in the tool being used. - Shared SQL area and the shared pool. Shared SQL area is part of the shared pool in the SGA. SQL area content: - shared portion - information used to execute specific SQL statements: - text of the SQL or PL/SQL statement, - parsed form (P-code) of the SQL or PL/SQL statement, - execution plan for SQL and PL/SQL statement. - private portion (???) SQL statements are identical when their text strings (including case, white spaces and comments), referenced objects, types and names of variables are identical. The rest of the shared pool content: - data dictionary cache containing rows of data dictionary information, - cursor state (bind variables) - ONLY for multi-threaded connections. For single threaded server or SQL*Net V1 this info is contained in Program Global Area (PGA). SHARED_POOL_SIZE init.ora parameter - number of bytes in the shared pool. Memory in the SHARED POOL is allocated automatically by Oracle. It's NOT POSSIBLE to allocate memory to SQL area separately from data dictionary cash! Default SHARED_POOL_SIZE which supplied with Oracle installation INIT.ORA file should be increased, sometimes BEFORE installation. Set it to TWICE the suggested default. Monitoring of shared pool: UTLBSTAT.SQL/UTLESTAT.SQL statistics report ([LON94], chapter 6). Package DBMS_SHARED_PULL package helps to monitor shared pool and to KEEP (prevent from swap out) in memory some shared pool objects (Oracle Magazine May/June 1995,pp. 65-70, paper by Mike Ault) Related topics: - CURSOR_SPACE_FOR_TIME = FALSE (by default). (see below) - SESSION_CACHED_CURSORS = n - 7.1 parameter. n - the maximum number of session cursors kept in the cache. LRU algorithm is used to aging out cursors from the cache. ALTER SESSION SET SESSION_CACHED_CURSORS = m; statement can be used to set this parameter for a given session ALTER SESSION SET CLOSE_CACHED_OPEN_CURSOR = {TRUE|FALSE} controls whether cursors opened and cached in memory by PL/SQL (only!) automatically closed at each COMMIT. FALSE signifies that cursors opened by PL/SQL are held open so that subsequiet executions need not open a new cursor. TRUE causes open cursors to be CLOSED at each COMMIT or ROLLBACK. "session cursor cache hits" statistic from V$SESSTAT table counts the number of times a parse call found a cursor into session cursor cache. Oracle uses the shared SQL area to determine if MORE THAN THREE parse requests have been issued on a given statement. If so, Oracle assumes the session cursor associated with the statement should be cached and moves the cursor into the SESSION CURSOR CACHE. Subsequent requests to parse that sql statement by the same session will then find the cursor in the session cursor cache. To enable caching of session cursors, you must set the INIT.ORA parameter SESSION_CACHED_CURSORS that specifies the maximum number of session cursors kept in the cache. A LRU algorithm ages out entries in the session cursor cache to make room for a new entries when needed. The command ALTER SYSTEM set SESSION_CACHED_CURSORS = n To determine whether the session cache is sufficiently large for your instance, your can examine statistic "session cursor cache hits" in the V$SESSTAT view. This statistic counts the number of times a parse call found a cursor in the session cache. If this statistic is a relatively low percentage of the total parse call count for the session, you should consider setting SESSION_CACHED_CURSORS to a larger value. (Oracle7 server documentation addendum pp. 9-10 - 9-11) - Reading data files. DB_FILE_MULTIBLOCK_READ_COUNT - maximum number of blocks read in one I/O during a sequential table scan. Value of this parameter is used by cost based optimizer to build access paths. - Program Global Area (PGA) is allocated and contains info for a each server (user) process. It is allocated when a user connects to Oracle and a session is established. PGA consists of stack space (holds session variables and arrays) and user session data. - PGA is writeable and non-shared, - in case of multi-threaded connections all the bind variable are in the shared pool. Size: PGA's size is OS specific and not dynamic. PGA is allocated at connect time. ???What about variables and arrays in the stack??? The following initialization parameters affect the size of PGAs: - OPEN_LINKS - DB_FILES - LOG_FILES - - Sort areas - portions of memory in which Oracle sorts data. Sort area exists in the memory of an server (user) process. Sort area may grow up but can't exceed limit given in INIT.ORA parameter SORT_AREA_SIZE=n (bytes). During the sort Oracle may perform some tasks that do not involve referencing data in the sort area. In such case Oracle may decrease the size of the sort area by writing some data to the temporary segment on disk and then deallocating the portion of sort area that contained that data. Such de-allocation may occur, for example, when Oracle return control to the application after finishing SELECT statement processing. In this case Oracle will retain in memory at least: SORT_AREA_RETAINED_SIZE=n (bytes, INIT.ORA par.) for subsequent processing. Min. value = 0, max. = SORT_AREA_SIZE If the amount of data does not fit into a sort area, then the data is divided into smaller pieces. Each piece is then sorted individually and called "RUN". After sorting all the RUNS Oracle merges them to produce result. Oracle7 allocate memory in sort area in 8k increments. Memory can be reused after the LAST ROW is fetched (good algorithm). SORT_AREA_RETAINED_SIZE should be a very small number (PH95p#31). You should monitor OS statistics on paging/swapping to assess the trade-off between decreased I/O due to memory sorts and the increased likelihood of paging/swapping. Each time SQL statement needs memory for sorting data SORT_AREA_SIZE bytes are allocated. After that memory can be deallocated to SORT_AREA_RETAINED_SIZE and allocated again up to SORT_AREA_RETAINED_SIZE. After closing cursor all the memory for sort area size is deallocated to 0 bytes. Temporary segments - working space. Sorting is needed when CREATE INDEX, SELECT ... ORDER BY, SELECT DISTINCT, SELECT ... GROUP BY, SELECT ... UNION, SELECT ... INTERSECT, SELECT ... MINUS, unindexed joins, certain correlated subqueries. Any extent in temporary segment should by multiple of SORT_AREA_SIZE. To minimize number of sort runs set the value of SORT_READ_FAC to DB_FILE_MILTIBLOCK_READ_COUNT (causes bigger merge widths). In 7.2.2 or higer INIT.ORA SORT_DIRECT_WRITES=TRUE allows to bypass buffer cache (COMPATIBILITY=7.2.2). Setting to TRUE causes the allocation of several LARGE buffers in memory for direct disk I/O. Youe can set parameters SORT_WRITE_BUFFERS and SORT_WRITE_BUFFER_SIZE to control the naumber and sizes of these buffers. The sort will write directly to disk (bypassing the buffer cache) an entire buffer for each I/O operation. Oracle allocates SORT_WRITE_BUFFERS * SORT_WRITE_BUFFER_SIZE in addition to memory already allocated for the sort area. It is acceptable if have large amount of memory (see [STUN72, p.11-4]). Script to determine sort activities in memory and to disk (applications should be running TWO or THREE working days before): >>> Begin of script select Name, Value from V$SYSSTAT where name like '%sort%'; rem In memory sorts should be greater then disk sorts. rem If more then 25% of sort requests require disk rem space, consider increasing SORT_AREA_SIZE >>> End of script - Database buffer cache. Shared by all processes. Block size - DB_BLOCK_SIZE=n (bytes) at database creation time. Buffer cache size: DB_BLOCK_BUFFERS=m (Oracle blocks) Two lists: dirty list and LRU list. LRU list content: - free buffers, - pinned buffers (currently being accessed), - dirty buffers. When a server process needs to read a block from disk into cache it: - searches LRU list, - looks for a free buffer and - moves dirty buffers to the dirty list The server process stops searching when it finds a free buffer or it has searched a specified number (DB_BLOCK_MAX_SCAN_CNT) of buffers without finding a free buffer. When server process finds a free buffer it moves that buffer to the MRU end of the LRU list. It the processes scans specified number of buffers without finding a free buffer, it then signals the DBWR to write dirty buffers to disk. DB_BLOCK_BUFFERS - size of database buffer cache in BLOCKS. Related topics: DB_BLOCK_LRU_STATISTICS = FALSE(def)/TRUE DB_BLOCK_LRU_EXTENDED_STATISTICS = 0..n - disables or enables compilation of statistics in the X$KCBRBH table during tuning memory allocation (see below and Server Adm.Guide, chapter 21) V$PERFORMANCE view - different aspects of performance, for example, detecting of block swapping. - Rollback (undo) segments. Data consistency through multi-versioning. Update operations. At least one per instance. Logical record in undo segment contains: - transaction ID including SCN (system commit number). It is assigned as the next sequential number when transaction is committed; - file ID; - block ID; - row number; - column number; - old data bytes. Because updates to undo segment blocks occur in SGA, they are protected by entries in the redo log. These entries ensure that any uncommitted transactions will be rolled back during INSTANCE RECOVERY. Update operation and rollback segments: 1. acquire database blocks into database buffer cache; 2. acquire rollback blocks into database buffer cache; 3. place exclusive row locks on rows that are about to change; 4. save rollback data into a rollback segment block; 5. apply changes to the data blocks. Commit makes rollback data inactive. Rollback returns data from the rollback segment block to the data block and clears rollback data. Active and inactive rollback segment entries. Statement level consistency and SET TRANSACTION READONLY Readers don't block writers and writers don't block readers. Overwriting of inactive rollback data. Oracle will guarantee that users will get a read consistent view of data, but NOT THE QUERY WILL COMPLETE. (1555-rollback segment too small, snapshot too old). - Number of bytes generated by transaction in rollback segment can be calculated with the following script >>> Begin of script rem Script UNDO_VAL.SQL set feedback off termout off echo off col Before format 999999999999999 New_Value Before_trans col After format 999999999999999 New_Value After_trans col Result format 999999999999999 hea 'Number of bytes generated' select sum(Writes) Before from V$ROLLSTAT; set feedback off termout on spool UNDO.LST prompt . . . . . Amount of the undo generate by the transaction: prompt + + + Trasaction begins set echo on feedback on @TEST.SQL set feedback off termout on echo off prompt - - - Trasaction ends set termout off select sum(Writes) After from V$ROLLSTAT; set termout on select &After_trans - &Before_trans Result from DUAL; Spool off >>> End of script Rollback segment space management. INITIAL, NEXT, MINEXTENTS and MAXEXTENTS. OPTIMAL storage clause. Shrinking of rollback segment: "If a transaction needs to continue writing rollback information from one extent to another extent in the rollback segment, ORACLE compares the current size of the rollback segment to the segment's optimal size. If the rollback segment is larger then its optimal size and the extents IMMEDIATELY FOLLOWING the extent just filled are inactive, ORACLE deallocates consecutive non-active extents from the rollback segment until the total size of the rollback segment is equal to or as close to but not less then its optimal size." (System Concepts Manual) Recommended number of rollback segments. Who knows... 3 types of rollback segments: - private (ROLLBACK_SEGMENTS = (rbs1, rbs2 ...) INIT.ORA parameter; - public (an instance allocates those segments at startup time (system concepts). Instance attempts to acquire CEIL(TRANSACTIONS/TRANSACTIONS_PER_ROLLBACK_SEGMENT) rollback segments. Oracle parallel server can have ONLY public segments. TRANSACTIONS_PER_ROLLBACK_SEGMENT does NOT actually limit number of transactions that can use a rollback segment; - deferred (contains transaction information on tablespaces that go OFFLINE). When tablespace goes offline so that transactions can not be rollbacked immediately deferred rollback segment is created in SYSTEM (!) tablespace. When this tablespace comes back online deferred RBS data are applied to it and this segment disappear. A transaction can be automatically assigned to a rollback segment based upon LEAST USED criteria of all rollback segments. Discrete transactions (DISCRETE_TRANSACTIONS_ENABLED=TRUE init.ora. Appl. develop. guide, appendix A, [STUN72, 4-8 - 4-18]) DON'T use rollback segments. Managing rollback segments: - It is not possible to create additional object in non-SYSTEM tablespace having only system rollback segment. Therefor, before creating a first non-SYSTEM tablespace you should create an additional rollback segment in SYSTEM tablespace, create tablespaces (including the tablespace for rollback segments), create needed rollback segments and then drop previously created non-SYSTEM rollback segment in SYSTEM tablespace. - SET TRANSACTION USE ROLLBACK SEGMENT rbs; - alter rollback segment RBS1 offline; drop [public] rollback segment RBS1; create [public] rollback segment RBS1 tablespace RBS_T storage (initial 200k next 200k minextent 20 optimal 400k) alter rollback segment RBS1 online; - ROLLBACK_SEGMENTS = (RBS1, ...) INIT.ORA parameter brings private rollback segments online at startup time. (!)Important: a heavy transaction-based system should have at least two tablespaces for rollback segments. The order of rollback segments in ROLLBACK_SEGMENTS INIT.ORA parameter should ensure toggling between tablespaces. E.g. if R1, R2, R3 reside in one tablespace and R4, R5, R6 - in another one then INIT.ORA parameter should be: ROLLBACK_SEGMENTS= (R1,R4,R2,R5,R3,R6) rather then ROLLBACK_SEGMENTS= (R1,R2,R3,R4,R5,R6) - You should always use PRIVATE rollback segment rather then PUBLIC because it is easy managing. Monitoring rollback segments : - select Blocks from DBA_SEGMENTS where Segment_Type = "ROLLBACK" and Segmanet_Name = ... Gives number of extents and actual number of blocks in rollback segment - select N.Name, S.Optsize from V$ROLLNAME N, V$ROLLSTAT S where N.Usn = S.Usn and N.Name='...' Optimal size monitoring. USN - Undo (Rollback) Segment Number. - Table DBA_ROLLBACK_SEGS (Tablespace_Name, Initial_Extent, Next_Extent, Min_Extents, Status, Instance_Num) for monitoring current status. - Space really allocated on rollback segments: select Segement_name, bytes, Extents from SYS.DBA_SEGMENTS where Segment_Type = 'ROLLBACK' or Segment_Type = 'DEFERRED ROLLBACK'; - monitoring dynamic extensions and shrinking: dynamic extensions and shrinks during time interval: UTLBSTAT.SQL and UTLESTAT.SQL, SQL*DBA, V$ROLLSTAT (see below.) - Tuning rollback segments >>> Begin of script set echo off col Name format a30 hea 'Rollback Segment Name' select Name, Waits, Gets, Waits/gets * 100 "Rat%" from V$ROLLSTAT, V$ROLLNAME where V$ROLLSTAT.uSN = V$ROLLNAME.Usn; rem Rat% showld be less then 1% or 2%. Otherwise rem add more rollback segments >>> End of script - Rollback data generated by transaction held in fire TEST.SQL >>> Begin of script set feedback off termout off echo off verify off col Before format 999999999999999 New_Value Before_trans col After format 999999999999999 New_Value After_trans col Result format 999999999999999 hea 'Number of bytes generated' select sum(Writes) Before from V$ROLLSTAT; spool UNDO.LST set termout on prompt . Amount of the undo generated by the transaction: prompt + + + Trasaction begins set echo on @TEST.SQL prompt - - - Trasaction ends set termout off echo off feedback off select sum(Writes) After from V$ROLLSTAT; set termout on select &After_trans - &Before_trans Result from DUAL; set termout off Spool off >>> End of script - rollback segment distribution per transactions/users: >>> Begin of script set termout on set echo off termout off col Id1 format 99999 hea 'RS|ID' Word_w col Rollback_Seg format a9 hea 'RS|NAME' Word_w col Oracle_pid format 99999 hea 'ORACLE|PID' Word_w col System_pid format a8 hea 'SYSTEM|PID' Word_w col Transaction format a8 hea 'Transac|tion'Word_w col Term format a6 hea 'Term|inal' Word_w col Osusr format a8 hea 'OS|User' Word_w col Usr format a8 hea 'User' Word_w rem set termout on prompt Rollback segment distribution per trans./users select trunc(L.Id1/65536) Id1, R.Name Rollback_seg, P.Pid Oracle_pid, P.Spid System_pid, nvl (P.username, 'NO') Transaction, P.Terminal Term, S.Username Usr, S.Osuser Osusr from V$LOCK L, V$PROCESS P, V$ROLLNAME R, V$SESSION S where L.Sid = P.Pid(+) and trunc(L.Id1(+)/65536) = R.Usn and L.Type(+) = 'TX' and L.Sid = S.Sid and L.Lmode = 6 order by R.Name; >>> End of script Choosing optimal number and extent size of rollback segments ([LON94] DBA Handbook, chapter 7) Related topics: CLEANUP_ROLLBACK_ENTRIES INIT.ORA - prevent long transactions from freezing out shorter ones. - Questions and answers Q. What does it mean when a rollback segment shows "NEEDS RECOVERY" A. A rollback segment with status "NEEDS RECOVERY" has failed to rollback an uncommited transaction in it's transaction table. The most common reason for failing to rollback a transaction is because the object is in an offline datafile or tablespace, the table or rollback segment are corrupt. Q: How does oracle determine which rollback segment to use? Is it different between v6 and Oracle7?? A: Oracle assigns rollback segments based on the following rules: 1. always assign to rbs which has least number of active transactions 2. if two or more rbs have the same "least number of active transactions", the assign to the one which is after the last one used. This ensures the undo is kept for a longer time. NOTE: if a transaction is outside the system tablespace, the system rollback segment is not counted in this round-robin method. - DBWR process. Writes all changed buffers to data files. Uses a LRU algorithm to keep most recently used blocks in memory. DBWR defers writes to database files for I/O optimization. DBWR will write dirty buffers to disk when (see also discussion UTLBSTAT/UTLESTAT output): - the dirty list reaches a THRESHOLD LENGTH (_DB_BLOCK_WRITE_BATCH/2) server process detects it and signals DBWR to write. The THRESHOLD LENGTH is defined to be ONE HALF of the value of the _DB_BLOCK_WRITE_BATCH = n (blocks) INIT.ORA parameter (it can be seen through X$KSPPI TABLE RATHER THEN V$PARAMETERS view). In this case single MULTI-BLOCK write is performed. _DB_BLOCK_WRITE_BATCH - is the number of blocks written in multi-block write. If the dirty list does not have _DB_BLOCK_WRITE_BATCH buffers when DBWR is signalled, DBWR searches the LRU list for additional dirty buffers; - a server process scans a specified number (DB_BLOCK_MAX_SCAN_CNT = n (blocks) INIT.ORA parameter) of LRU list without finding a free buffer. Multi-block write is performed in this case also; - a time-out occurs (every 3 seconds). If DBWR has not been signalled to write during specified time (timeout), it signals ITSELF to write. DBWR searches a specified (2*DB_BLOCK_MAX_SCAN_CNT) number of buffers on the LRU list and writes any founded dirty buffers to disk. The next timeout - the new set of buffers. The DBWR signals the LGWR to flush the redo log buffer BEFORE if writes. The number of buffers searched by DBWR at each timeout is TWICE the value of DB_BLOCK_MAX_SCAN_CNT; - a checkpoint occurs. LGWR signals DBWR to write. LGWR specifies WHICH BUFFERS TO WRITE to disk. It is possible to multiple DBWR processes. Rule: one DBWR process per Oracle data disk drive (DATABASE_WRITERS=n). It is recommended to run 2 DBWRs per disk drive used by each oracle instance (PH95p#5v1). Related topics: - DB_BLOCK_CHECKPOINT_BATCH = _DB_BLOCK_WRITE_BATCH/4 (def) Max number of blocks DBWR will write in one batch during checkpoints. - DB_FILE_SIMULTANEOUS_WRITES = 4..24 If OS supports more then one write per device. - Server configurations. Multi-threaded versus dedicated servers. - Log transactions. LGWR and ARCH (archive redo log information) processes. Before data is written to the log file on disk, it is first recorded in the rego log buffer in SGA. Redo log buffer contains: - records indicating what has changed in a row; - records indicating what has been recorded in a rollback segment; - records indicating that a transaction has been committed; - records indicating that a checkpoint has been performed; - records indicating that a distributed transaction has prepared. - Redo log files and redo log buffer. LGWR process. Redo log buffer is circular - when LGWR writes to redo log, server process can write into it. Length in BYTES is LOG_BUFFER (INIT.ORA). Redo entries (see above) are used to rollback changes made to the database when recovery is necessary. The ONLY time that the redo log file is READ is during instance or media RECOVERY. How to determine the optimal value for LOG_BUFFER see section "Determine whether user processes wait for space in the redo log buffer" below. Update operation and the redo log buffer: 1. read database blocks into the database buffer cache; 2. place exclusive row locks on rows that are about to change; 3. acquire a transaction entry IN THE DATA BLOCK and assign transaction (space for the transaction entry should exist in data block); 4. read rollback blocks into the database buffer cache; 5. save rollback data (rollback part of the transaction entry) into a rollback segment block; 6. record before image (re-formatted rollback data) in redo block buffer; 7. apply changes to the data block (date part of the transaction entry); 8. record after image (new data part of the transaction entry) in redo log buffer; Redo log entries from several transactions will be interleaved. LGWR writes redo log buffer entries to the redo log file when: - a commit occurs; - the redo log buffer is ONE THIRD full; - the DBWR needs to clean the buffer blocks for checkpoint; - a timeout occurs (every 3 seconds); - a distributed transaction prepares. Notes: - there is only one redo log per instance; - commit is not confirmed until the transaction has been recorded in the redo log file; - concurrent commits may be piggybacked (so called group commit when all the acquired during writing from redo log buffer into redo log is written in one disk write) for better performance; - redo log block is equal to OS block. - the DBWR always signals the LGWR to flush the buffer before it writes blocks back to the database to protect all changes, - maximum possible number of redo log groups is determined by MAXLOGFILE parameter used in CREATE DATABASE, max. number of member per group - by MAXLOGMEMBERS in CREATE DATABASE. LOG_FILES INIT.ORA parameter may limit number of online redo logs; - to add redo log in environment without groups: ALTER DATABASE ADD LOGFILES ('lf1','lf2') SIZE nnnK; to add new group: ALTER DATABASE ADD LOGFILE GROUP 5 ('lf1','lf2') SIZE nnnK; to add member in group: ALTER DATABASE ADD LOGFILE MEMBER 'lf3' TO GROUP 5; to rename all types of files: ALTER DATABASE RENAME FILE 'f1','f2' TO 'nf1', 'nf2'; to drop group: ALTER DATABASE DROP LOGFILE GROUP 5; to drop member:ALTER DATABASE DROP LOGFILE MEMBER 'lf3'; - Database commits. Commit operations. COMMIT features. Commit steps: - a user issues commit; - a commit record is placed in the redo log buffer; - LGWR acquires all latches on the redo log buffer, allowing current writes to complete; - LGWR flushes the redo log buffers to current log file using multi-block writes if possible; - the user is notified that the transaction has been committed; Commit Features: - LGWR permanently records transactions while allowing DBWR to defer writes and reduce I/O time; - piggybacks redo log records from multiple transactions requesting to commit AT THE SAME TIME into single write; - at most, less then one synchronous write is required per transaction; - the size of transaction does not affect amount of time needed for actual commit operation; - the write is multi-block, if possible; - LGWR writes entire operating system blocks, which may not always be entirely full, to the log file; - Redo log files. Redo log groups. Minimum 2 redo log files. If the redo log files are MIRRORED, the same redo log information is written to multiple online redo log files. Each member (file) of a group should be located on different disks to guard against failure. All members of a group are written to at the same time. Redo log files (or group's members) are written in a circular fashion. All members of a group contains the same information. Group members are updated simultaneously. Each group should contain the same number of members as other groups. - Log switches occurs when: - Oracle switches from one redo log to another (??when??); - LGWR has filled one log file group; - forced by DBA when current redo log needs to be archived, or dropped or moved to a new disk location. (ALTER SYSTEM SWITCH LOGFILE command); - a log switch occurs upon database shutdown; Notes: - A checkpoint automatically occurs at a log switch. - At a log switch the current redo log is assigned a LOG SEQUENCE NUMBER that identifies the information stored in that redo log and is also used for synchronization. This number is also stored in the control file and the header block of each file. - Processing can continue as long as at least one member of a group is available. If a member is found to be damaged or unavailable, messages are written to the LGWR TRACE file and to the ALERT file. - The default installation creates two redo log groups with one file per log. - Checkpoints. DBWR writes all dirty buffers from database buffer cache to disk, guaranteeing that all data blocks MODIFIED SINCE THE PREVIOUS CHECKPOINT are actually written to the database files on disk. The database file are made current as of a given time and the checkpoint is recorded in the control file(s). Checkpoint tells Oracle to FLUSH(!!!) the SGA to ensure system integrity. Hence after checkpoint all the data acquired in the SGA by that moment (hot spots) are LOST! Therefor, redo log files should be AS BIG AS POSSIBLE to minimize SGA flushing. But on the other hand, if the system where to crash, it might take the database a lot LONGER TO RECOVER. [COR95 p.134]. For God sake in 7.x it is NOT CORRECT. Neither checkpoint nor log switch causes SGA flushing! I checked it. Each checkpoint should occur only on log switching. To achieve this set INIT.ORA LOG_CHECPOINT_TIMEOUT = 0 and LOG_CHECKPOINT_INTERVAL to a size greater then redo log file size. Checkpoint occurs: - at every log switch; - a specified number of seconds after the last database checkpoint (LOG_CHECKPOINT_TIMEOUT INIT.ORA parameter); - when a predetermined number of redo log blocks have been written to disk since the last checkpoint (LOG_CHECKPOINT_INTERVAL measured in OS blocks INIT.ORA parameter); - at instance shutdown; - when forced by DBA with ALTER SYSTEM CHECKPOINT command (without a LOG SWITCH and hence faster then ordinary checkpoint with log switching); - when tablespace is taken offline (ALTER TABLESPACE OFFLINE command). Notes: - checkpoints DO NOT HALT ACTIVITY, NOR ARE CURRENT TRANSACTIONS AFFECTED; - During checkpoint and after log switch, LGWR will update the headers of database files and control files (SCN, Log Seq. Number) unless the checkpoint process (CKPT) has been started. - Checkpoints require that instance recovery during warm restart at startup time need only apply changes since last checkpoint. - checkpoints allow an online redo log file to be reused by guaranteeing that all changes stored in the redo log file are also written to the appropriate datafile. - CKPT process. OPTIONAL. Notes: - CKPT updates headers of data and control files after a checkpoint has been completed, offloading this task from LGWR and improving performance for database with multiple files. - CHECKPOINT_PROCESS = TRUE INIT.ORA parameter forced Oracle to start CKPT at database startup. Corey in PH95p#5v.1 recommends to set it to TRUE - More frequent checkpoint will reduce the time necessary for recovering from instance failure, at the expenses of PERFORMANCE. - If you decide to achieve the maximal performance at the expenses of additional disk storage and longer instance recovery , you should ensure that each checkpoint should occur only on log switch. To achieve this set INIT.ORA LOG_CHECPOINT_TIMEOUT = 0 and LOG_CECKPOINT_INTERVAL to a size greater then the redo log file size. - Achieving. Archivelog and noarchivelog modes mode. Optional achiever process (ARCH) copies online redo log files to a designated storage device (tape or disk) once they become full for possible media failure recovery. ARCH operates only when a log switch occurs and when automatic achieving is enabled or when achieving is manually requested. Log sequences number is a part of archived redo log. Related topics: - LOG_ARCHIVE_BUFFERS=n LOG_ARCHIVE_BUFFERS_SIZE=m in OS (redo log) blocks - LOG_ARCHIVE_DEST= (Support of tape archiving depends on O/S.) To override this use ARCHIVE LOG START 'filespec' LOG_ARCHIVE_FORMAT = %s - log sequence number) %t - thread (instance) number. - LOG_ARCHIVE_START = TRUE/FALSE TRUE - automatic archiving, FALSE - manual. ARCHIVE LOG START (or STOP) overrides this parameter. - Common problem: Archivelog destination full (see [COR95], chapter 6 pp. 158-160) If there is enough room on disk to copy online redo log in LOG_ARCHIVE_DEST, then ORA-00257 error arises and it's not possible to proceed with normal work. The following reasons can cause the archive directory to suddenly fill up during normal operating conditions: - The previous night's backup routine, which is supposed to put archived redo logs out to tape and then erase them, for some reason aborted the last time it run - the step that was supposed to erase them never run. - An application has deleted a large number of rows from a very large table, and the redo log files written by that transaction alone created a very large number of archived redo logs. - If the archive log destination drive shares space with other Oracle data files, you may have inadvertently drastically reduced the available space by adding a datafile to tablespace that resides on that drive. This leads into the next few good rules: - you should monitor the available disk space on directory that contains your archived redo logs. If its utilization over climbs over 90%, it requires IMMEDIATE INTERVENTION; - dedicate enough space in your archived log destination to hold at least two full days of archived redo logs; - Archived redo logs should be backed up every day and deleted afterwards. DO NOT RELY ON manual purging of the archived redo log destination. [COR95] tells a very good story on pp 159-160! - Transactions. - Transaction start: - at session start; - at end of previous transaction. - Transaction end: - transaction committed: - COMMIT SQL statement; - DDL statement (CREATE, DROP, ALTER, GRANT,...) that cause an implicit commit; - normal exit from user program with valid LOGOUT; - transaction aborted: - ROLLBACK SQL statement; - user-requested termination (with CONTROL+C for example); - abnormal exit from user program without logout from Oracle; - processor failure; - disk failure. By default, if statement fails Oracle rolls back JUST THAT statement, rather then the entire transaction. - Number of Database Files. Maximal number of datafiles that can be created is the lowest number specified by any of these three items: 1. OS limits. If operating system allows maximum n files per process then n - m Oracle datafiles can be created where m is the sum of control files, redo log files, alert.log and other log files. For each OS concurrent open file limit needs adjusting by means of underlining OS. (See chapter 6 "Show Stoppers" pp. 139-142 of [COR95]). 2. Oracle limit MAXDATAFILES. This parameter is set during database creation time. It's value affects the size of the instance CONTROL FILE. It's possible to increase MAXDATAFILES without re-creating database by rebuilding the control file. To do this you should: alter database backup control file to trace; The SQL script is placed in directory specified by init.ora USER_DUMP_DESTINATION. Extract CREATE CONTROLFILE statement from trace file and change needed values (see script below when sqldba comments are added to the edited control file extracted from trace. Shutdown database and run SQLDBA script from trace: # The following commands will create a new control # file and use it to OPEN the database. # No data other then log history will be lost. # Additional logs may be required for media recovery # of offline data files in case of (1) any of the # datafiles are restored backups or (2) the last # shutdown was not NORMAL or IMMEDIATE. # Use this script only if the current versions of ALL # ONLINE LOGS are available. startup NOMOUNT create CONTROLFILE reuse database NORESETLOGS ARCHIVELOG MAXDATAFILES 30 MAXLOGFILES 20 MAXLOGMEMBERS 4 MAXLOGHISTORY 100 logfile group 1 ( 'G1_logfilename1', 'G1_logfilename2', 'G1_logfilename3', ) size 2M, group 2 ( 'G2_logfilename1', 'G2_logfilename2', 'G2_logfilename3', ) size 2M, datafile 'data_filename1' size 30M, 'data_filename2' size 120M, 'data_filename3' size 30M, 'data_filename4' size 300M; # Recovery is required if (1) any of the datafiles are # restored backups or if (2) the last shutdown was not # NORMAL or IMMEDIATE recover DATABASE; # # All logs need archiving and a log switch is needed: alter system ARCHIVE LOG ALL; # Database can now be opened normally alter database OPEN; 3. DB_FILES=n init.ora maximum number of database files that can be opened at run time for this database. Should be set at value close to MAXDATAFILES. If you have n datafiles and attempt to add one more datafile you get ORA-01547 error message. - Recreating the database. Learn how to create database and practice before you are forced to do so during real-life emergency. - Savepoints. Very convenient feature. ... SAVEPOINT savepoint_name ... ROLLBACK [TO savepoint_name] Maximum number of savepoints per transaction determined by SAVEPOINTS = n INIT.ORA parameter. - Process monitor (PMON) and system monitor (SMON) background processes. PMON: - cleans up ubnormally terminated connections; - rolls back uncommitted transactions; - releases locks help by terminated process; free SGA resources allocated to failed process; - detects deadlocks and automatically resolves by rolling back the transaction. SMON: - performs automatic instance recovery; - maintains the control file (???); - reclaims temporary segment space; - performs the space reorganization (makes one extent out of two or more physically contiguous extents. They can be found in FET$ data dictionary table); Important notes: - PMON, SMON, DBWR and LGWR are all mandatory processes for an Oracle instance to run. - PMON and SMON, unlike DBWR and LGWR CAN'T be controlled by modifying INIT.ORA parameters. It's NOT POSSIBLE to determine when they will wake up. - SNPn, RECO and LCKn background processes. - SNPn processes control the automatic refreshing of snapshots of remote table. Number of SNP processes n is determined by SNAPSHOT_REFRESH_PROCESSES=n parameter. - Recover process, RECO, resolves failures involving in distributed transaction. It will handle failures that have occurred in distributed transaction by continually trying to contact the in-doubt nodes. - Lock processes (LCKn) perform inter-instance locking in a parallel server system (with parallel server option) see PH95 p#159v2. SNPn and RECO will only be present if the distributed option is being used. - Parallel query processing See. PH95p#160v2 - Control file(s) - a small binary file (s) that describe the structure of the database. INIT.ORA parameter CONTROL_FILES=(...,...) must list all the control files. Oracle automatically mirrors all the control files. Control file content and properties: - all necessary database files and log files are identified in the control file; - the name of the database is stored in the control file; - the control file IS REQUIRED to open and access the database. It must be available for writing whenever database is open; - synchronization information needed for recovery is stored inside the control file; - the controlfile's name is specified during CREATE DATABASE; - CREATE CONTROLFILE command should be issued when: a) all the current control files are lost (for subsequent recovery). It's possible to create new control file based on info contained in remaining non-damaged files; b) when change some parameters specified during CREATE DATABASE is needed (for example database name); - Tuning memory allocation. (See above about SGA and PGA). - Before tuning Oracle consider the O/S characteristics: - Reduce paging (swap a part of process onto disk to make room for another processes) and swapping (swap entire process onto disk). - Fit the SGA into main memory. The SGA should be as large as possible. On the other hand, the SGA should NEVER TAKE MORE THEN 50% of available memory. The problem is how to determine when the SGA size is set too high. It is possible only to determine that buffer cache (DB_BLOCK_BUFFERS value) is too high. See the appropriate script in section "Tuning of the buffer cache" below. - Typically, in well tuned system SHARED_POOL_SIZE + DB_BLOCK_BUFFERS should occupies about 90% of SGA total size. - allocate enough memory to individual users - Network characteristics should be considered also It is very difficult job. - Tuning the size of redo log files. Each log switch causes the checkpoint the SGA. Hence redo logs should be very large. Each checkpoint should occur only on log switching. To achieve this set INIT.ORA LOG_CHECPOINT_TIMEOUT = 0 and LOG_CECKPOINT_INTERVAL to a size greater then redo log file size. Views which are contained redo log info: V$LOG and V$LOGFILE - System Global Area (SGA) tuning. Summury information: >>> Begin of script (from OM 1-2.1996 p.64, Gita Gupta) select Name, Bytes from V$SGASTAT where name in ('db_block_buffers', 'log_buffer', 'dictionary cache', 'sql area', 'library cache', 'free memory'); >>> End of script "Free memory" statistics should be LOW rather then high unlike on operating system. Haigh value of "free memory" can indicate that Oracle7 has aged objects out of the shared pool and, as a result, that the shared pool is fragmented. - Tuning Private SQL and PL/SQL Area (from OM 1-2.1996 p.64, Gita Gupta) The number of parse calls your application performs and number of private SQL areas Oracle7 needs to allocate will be determined be whether or not your application reuses private SQL areas for multiple SQL statements. OPEN_CURSORS - number of cursors permitted for a session. - Tuning of the library cache (SHARED_POOL_SIZE). The goal is to PARSE ONES and EXECUTE MANY TIMES. - Sharing SQL statements. there must by CHARACTER-TO-CHARACTER match between the statements being examined and one already in shared pool. Application tuning rule #2: the object being referenced are exactly the same as those objects in a statement that has passed the comparison in Rule #1. There is one exception: let's two users reference an object O of the third user through private synonyms both named O_SYN (e.g. select * from O_SYN) Even though each user has a its own private synonym O_SYN that refers to the SAME object O in the third user schema, these individual private synonyms are actually database objects themselves that is these are DIFFERENT object and Oracle does NOT share appropriate SQL statements (select * from O_SYN) from users A and B, because the objects represented by private synonyms with the same names from a different users are considered to be DIFFERENT! Public synonyms and qualified objects USER_NAME.TABLE_NAME are considered to be the same. From this point of view using private synonyms are very BAD because SQL statements can NOT be shared between processes! Note: if an object referenced in SQL statement in the shared pool is modified, the statement is flagged as invalid. The next time a statement is passed to Oracle that is the same as invalid statements, the old statement will be replaced by the new since the underlying object has been modified. Put the attention on the following (from OM 1-2.1996 p.64, Gita Gupta): - Fragmentation of the shared pool. Occurs during normal database operatoins. If Oracle7 has to load a large object into the shared pool, IT WILL FLUSH OBJECTS THAT ARE CURRENTLY NOT IN USE from the shared pool, usingt an LRU alrorithm. Flushing shared pool objects frees up memory, and the allocation will succeed if Oracle7 can find a SUFFICIENTLY LARGE chunk of CONTIGUOUS memory. So far, chunks of memory are released in ARBITRARY order (!?). If a query references the flushed objects later, it will cause the implicit reparse. - Contention for the library cache latch. - Incorrect sizing. Library cache misses can occur in any of the followins steps inprocessing SQL statement: - PARSE. If parsed representation of the statement does not already exist in the shared SQL area Oracle7 has to pares the statement and allocate a shared SQL area. To avoid reparsing you should either: (1) use properly named bind variables rather then explicitly specifed constants; (2) standartize case and spacing conventions; (3) maximize use of packages and stored procedures; (4) use fully qualified table names or global synonyms (rather then private synonyms) see below. (5) set SESSION_CACHED_CURSORS = TRUE (INIT.ORA) to cache closed cursors within the session, eliminating the need for parsing on a subsequent parse call. - EXECUTE. Library cache misses occure when the parsed representation exists in the library cache bu has been aged out. Monitoring via V$LIBRARYCACHE table. Columns: Pins - increments each time when object definition were accessed for execution Reloads - number of executions resulted in library cache misses causing Oracle to implicitly reparse a statement or block or reload an object definition because it has aged out of library cache. Monitoring scripts: ------------------- >>> BEGIN OF SCRIPT rem Not debugged set termout ON prompt -------------> Library cache monitoring select Namespace, Gets, round (decode(gethits,0,1,gethits) / decode (Pins,0,1,Pins), 3) "Get Hit Ratio", Pins, round (decode(Pinhits,0,1,Pinhits) / decode (Pins,0,1,Pins), 3) "Pin Hit Ratio", Reloads, Invalidations from V$LIBRARYCACHE; rem rem The PIN RATIO should be as close to 1.0 as rem possible. >>> END OF SCRIPT Compare executions (Pins column) and cache misses (Reloads column) from V$LIBRARYCACHE table: >>> Begin of script set termout ON prompt -------------> Library cache monitoring set feedback off echo off termout off col R format 9999.9999 hea 'Ratio %' set echo ON termout ON select sum(Pins) "Executions", sum(Reloads) "Cache misses while executing", sum(Reloads)/sum(Pins) * 100 R from V$LIBRARYCACHE; rem rem If ratio is MORE THEN 1% enlarge library cache rem (SHARED_POOL_SIZE init.ora). >>> End of script - Tuning the library cache. See [STUN72] pp. 8-8 - 8-14 - Reducing fragmentation, monitoring and keeping objects in library cache (SHARED_POOL_SIZE) with DBMS_SHARED_POOL package. The shared pool holds shared SQL and PL/SQL statements (referred to as the library cache), the data dictionary cache, and information on sessions against the database. DBA can monitor the shared pool through CONNECT INTERNAL and via sqldba or by querying v$sqlarea dictionary view. - Reserve and monitoring shared-pool space. There are two additional parameters beginning with 7.1.5: SHARED_POOL_RESERVED_SIZE - if NON-ZERO value (max is 50% of the SHARED_POOL_SIZE) reserves the contiguous portion of the shard pool for PL/SQL intensive operations (e.g compilation of triggers and procedures). SHARED_POOL_RESERVED_MIN_ALLOC - the mimimal size of the object which can be allocated from reserved with parameter SHARED_POOL_RESERVED_SIZE space in shared pool pool. To monitor reserver pool you should examine V$SHARED_POOL_RESERVED, which tracks reserved-pool free memory, used space, request misses, adn failures. - data dictionary cache contains reference info about the database, its structures and its users. DBA may need to visit the size of the shared pool if he or she receives ORA-4031 error (out of shared memory when allocating NUM bytes), when Oracle tries to fined more space in the shared pool Oracle attempts to parse the statement and place it into the library cache. If there is not enough room in the cache, then Oracle makes room for the procedure by removing an existing entry. A RELOAD (see tuning library cache) means the removed entry is re-requested, hence, these entries was parsed more then once. RELOADs should never happen in well tuned system. DBMS_SHARED_POOL package provides you with additional means for managing library cache. It's possible to force Oracle to hold some common used library cache entries (procedures, packages, views ...) in memory. Oracle can NOT remove holded entries from memory to make room for other entries. >>> Begin on script rem rem this procedure shows shared pool objects that rem are larger then 10 kilobytes set serveroutput on size 4000 execute sys.DBMS_SHARED_POOL.sizes(10); rem You can also use V$DB_OBJECT_CACHE view: rem select * from V$DB_OBJECT_CACHE rem where Sharable_mem > 10 rem >>> End on script Keeping/unkeeping in memory: sys.DBMS_SHARED_POOL.keep (Object in varchar2, Type in char default 'P') sys.DBMS_SHARED_POOL.unkeep (Object in varchar2, Type in char default 'P') where Object - Name of the object Type - object's type 'P' - procedure (default) 'C' - cursor 'R' - trigger >>> Begin on script rem Keeping in memory rem rem To retain package sys.STANDARD in memory: execute sys.DBMS_SHARED_POOL.keep('sys.STANDARD'); >>> End on script - Improve performance when there are never library cache misses for given session by CURSOR_SPACE_FOR_TIME init.ora parameter. If the CURSOR_SPACE_FOR_TIME = TRUE then the following algorithm (requiring enough memory installed on you machine) is issued: If some cursor in an application (session) was opened and then closed Oracle DOES NOT de-allocate shared SQL areas for this cursor until AT LEAST ONE CURSOR OPENED by this application exists. This prevents Oracle from checking the library cache area for identical statements for given application (session) and therefor saves time. If CURSOR_SPACE_FOR_TIME = FALSE then the SQL statements remain in the library cache until aged out by usual LRU algorithm. In other words CURSOR_SPACE_FOR_TIME init.ora parameter affects when a statement in shared SQL can be deallocated from the pool. When set to FALSE (the default), Oracle deallocates space held by a statement (and ages out this statement) from the shared pool even if application cursor using that statement are still open. If the amount of available memory is enough such that the shared pool can be sized to hold all application cursors, you may consider setting this parameter to TRUE. A small amount of time is saved during statement execution when the parameter set to TRUE, because Oracle does not have to search the pool to see if the statement is already there. - Tuning of the data dictionary cache (SHARED_POOL_SIZE). In Oracle version 6 there were DC_ parameters which allowed the fine tuning of the data dictionary cache. Unfortunately, there is ONLY ONE init.ora parameter - SHARED_POOL_SIZE. V$ROWCACHE table columns: Parameter - identifies a particular data dictionary item Gets - total number of requests for information on an item Getmisses - number of requests that result in cache misses >>> Begin of script set feedback off termout ON echo ON prompt -----------------> Monitoring DICTIONARY CACHE select sum(Gets) "Data Dictionary Gets", sum(Getmisses) "Data Dict Cache get Misses", sum(Getmisses)/sum(Gets)*100 "Ratio %" from V$ROWCACHE; rem rem NOTES: rem rem You should increase SHARED_POOL_SIZE if the ratio rem is greater then 10-15% rem rem you may decrease SHARED_POOL_SIZE if the ratio is rem considerably less then 10-15% >>> End of script Put a special attention on DC_ parameters: >>> Begin of script set echo off feedback off termout on set verify off prompt -----------------> Monitoring BUFFER CACHE 2 col Parameter format A30 heading "Cache|entry" col Pm format 990.00 heading "Percentage|miss" col Gets format 999,999,990 "Gets" col Getmisses format 999,999,990 "Misses" set echo on select unique Parameter, Gets, /* Read from memory */ Getmisses, /* Read from disk */ Getmisses / (Gets+Getmisses)*100 Pm from V$ROWCACHE where Gets+ Getmisses <> 0; set echo on feedback on termout on rem rem The percentage for the entries Dc_table_grants, rem Dc_user_grants, Dc_users should be < 5% each. rem rem You should increase SHARED_POOL_SIZE if the rem percentage is greater then 5% rem >>> End of script - Tuning of the buffer cache (DB_BLOCK_BUFFERS init.ora). DB_BLOCK_BUFFERS should be set as high as possible. But keep in mind the possibility of swapping. - How to determine that DB_BLOCK_BUFFERS value is set too high and swapping or paging degrade the performance. >>> Begin of script set echo on termout on prompt --------> SGA SWAPPING Monitor (login as SYS) rem We can determine if database block is swapped out by rem examining of the status of the block header via X$BH rem (block headers) table. Status "Free" means the that rem this block is never used. There are two reasons for rem presence of "Free" block: (1) number of users in your rem system is too small and they can't use pool fully or rem (2) blocks marked "Free" are swapped out of the main rem memory. If (2) is the case - reduce DB_BLOCK_BUFFERS. rem select decode (State, 0, 'Free', 1, 'Read and Modified', 2, 'Read and Not Modified', 3, 'Currently Being Read', 'Other' ), Count(*) from X$BH group by decode (State, 0, 'Free', 1, 'Read and Modified', 2, 'Read and Not Modified', 3, 'Currently Being Read', 'Other' ) ; rem rem The larger the number of blocks with status 'Free' rem the more probability that these blocks are swapped rem out of main memory. In case of substantial number rem of 'Free' blocks decrease DB_BLOCK_BUFFERS value. >>> End of script - Monitoring Hit Ratio of buffer pool (DB_BLOCK_BUFFERS) V$SYSSTAT table. Columns: Name (name of statistic), Value. Names of the statistics: "db block gets", "consistent gets" - sum of these statistics is the total number of requests for data, including requests satisfied by access to buffers in memory. "physical reads" - total number of requests for data resulting in access to data files on disks. Example of the SQL*Plus monitoring script calculating hit ratio: Hit Ratio = +-------------------------------------------------------+ | cons. gets + db block gets - phys. reads | | ------------------------------------------ * 100% | | (cons. gets + db block gets) | +-------------------------------------------------------+ = +-------------------------------------------------------+ | phys. reads | | 100% - --------------------------- * 100 | | db block gets + cons. gets | +-------------------------------------------------------+ >>> Begin of script set echo off feedback off termout on prompt ----------------> BUFFER CACHE HIT RATIO MONITORING set verify off col DBG for 999999999999 hea 'db block gets' new_value DBGV col CG for 999999999999 hea 'consistent gets' new_value CGV col PR for 999999999999 hea 'physical reads' new_value PRV select Value DBG from V$SYSSTAT where Name='db block gets'; select Value CG from V$SYSSTAT where Name='consistent gets'; select Value PR from V$SYSSTAT where Name='physical reads'; select 100 - &PRV/(&DBGV+&CGV)*100 "Hit Ratio" from DUAL; set echo on feedback on termout on rem rem NOTE: rem If the hit ratio is less then 60-70% then increase rem DB_BLOCK_BUFFERS init.ora parameter. >>> End of script; - Estimating the performance gain that would result from changing the size of the buffer cache. Do NOT USE DB_BLOCK_LRU_STATISTICS during normal operations because of the negative impact on performance. +------------------------------------------------------+ | All the scripts below MUST be run from SYS account. | +------------------------------------------------------+ - Estimating the performance gains for a Larger Buffer Cache: Set DB_BLOCK_LRU_EXTENDED_STATISTICS=n where n - is the number of buffers to add. Statistics are stored in X$KCBRBH with columns: Indx - one less then the number of buffers that would potentially be added to the cache Max(Indx) = DB_BLOCK_LRU_EXTENDED_STATISTICS-1 Count - the number of additional cache hits that would be obtained by adding additional buffer number INDX+1 to the cache. - Determining the new hit ratio if we add &Buffers_To_Add buffers New Hit Ratio with additional buffer cache hits = +--------------------------------------------------------+ | cons. gets + db block gets | |------------------------------------------------ * 100% | |(cons.gets + db bl.gets) + phys.reads - add.hits | +--------------------------------------------------------+ = +--------------------------------------------------------+ | phys.reads - add.hits | |100% - ----------------------------------------- * 100%| | db bl.gets + cons.gets + phys.reads - add.hits | +--------------------------------------------------------+ >>> Begin of script set feedback off termout on echo off set verify off col DBG for 999999999999 hea 'db block gets'- new_value DBGV col CG for 999999999999 hea 'consistent gets'- new_value CGV col PR for 999999999999 hea 'physical reads'- new_value PRV col ACH for 9999999999999 hea - 'Additional Cache Hits' new_value ACHV accept Buffers_To_Add char - prompt "Number of buffers to add: " prompt prompt Determining the new hit ratio - in case of adding &Buffers_To_Add buffers select Value DBG from V$SYSSTAT where Name='db block gets'; select Value CG from V$SYSSTAT where Name='consistent gets'; select Value PR from V$SYSSTAT where Name='physical reads'; rem &Buffers_To_Add should be <= rem DB_BLOCK_LRU_EXTENDED_STATISTICS select sum(Count) ACH from SYS.X$KCBRBH where Indx < &Buffers_To_Add; select &Buffers_to_Add "Adding Buffers", 100 - (&PRV-&ACHV)/(&DBGV+&CGV+&ACHV)*100 "New Hit Ratio %" from DUAL; Undef buffers_to_add set echo on feedback on termout on >>> End of script; - Group the statistics from X$KCBRBH table in intervals of &Interval buffers: >>> Begin of script set feedback off termout on echo off set verify off rem Interval - is the interval of additinal rem buffers to be added to the cache rem Additional Buffer Cache Hits - add. hits for rem this interval accept Interval char - prompt "Enter number of buffers in interval:" prompt prompt Additional hits per interval select &Interval*trunc(Indx/&&Interval)+1 || ' TO ' || &Interval*(trunc(Indx/&Interval)+1) "Interval", sum(Count) "Additional Buffer Cache Hits" from SYS.X$KCBRBH group by trunc(Indx/&&Interval); undef Interval set echo on feedback on termout on rem Read the output from the bottom up. Add the rem sum of the previous values to the value of rem the current row >>> End of script - Estimating the performance gains for a Smaller Buffer Cache: Set init.ora DB_BLOCK_LRU_STATISTICS = TRUE. Statistics are stored in X$KCBCBH table with columns: Indx - one less then the potential number of buffers in the cache Count - the number of cache hits attributable to buffer number Indx+1 - Determining the new hit ratio if we decrease the size of the buffer cache up to &Reduced_size. In this case we will get "additional cache misses". The new hit ratio should be calculated as: New Hit Ratio with additional buffer cache misses = +--------------------------------------------------------+ | cons. gets + db block gets | |------------------------------------------------ * 100% | |(cons.gets + db bl.gets) + phys.reads + add.miss. | +--------------------------------------------------------+ = +--------------------------------------------------------+ | phys.reads + add.miss. | |100% - ----------------------------------------- * 100%| | db bl.gets + cons.gets + phys.reads + add.miss. | +--------------------------------------------------------+ >>> Begin of script set feedback off termout on echo off set verify off col DBG for 999999999999 hea 'db block gets'- new_value DBGV col CG for 999999999999 hea 'consistent gets'- new_value CGV col PR for 999999999999 hea 'physical reads'- new_value PRV col ACM for 9999999999999 hea - 'Additional Cache Misses' new_value ACMV accept Reduced_size char - prompt "Enter new reduced buffer size: " prompt prompt Determining the new hit ratio - in case of reducing cache to &Reduced_size select Value DBG from V$SYSSTAT where Name='db block gets'; select Value CG from V$SYSSTAT where Name='consistent gets'; select Value PR from V$SYSSTAT where Name='physical reads'; rem &Reduced_size should be < rem DB_BLOCK_BUFFERS select sum(Count) ACM from SYS.X$KCBCBH where Indx >= &Reduced_size; select &Reduced_size "Reduced buffer size", 100 - (&PRV+&ACMV) / (&DBGV+&CGV+&ACMV)*100 "New Hit Ratio %" from DUAL; set echo on feedback on termout on >>> End of script; - Group the statistics from X$KCBCBH table in intervals of &Range: >>> Begin of script rem Interval - is the interval of buffers to be rem removed from cache rem Buffer cache misses - mises per interval accept Interval char - prompt "Enter number of buffers in interval:" prompt prompt Misses per reducing interval select &Interval*trunc(Indx/&&Interval)+1 || ' TO ' || &Interval*(trunc(Indx/&Interval)+1) "Interval", sum(Count) "Buffer Cache Misses" from SYS.X$KCBCBH group by trunc(Indx/&&Interval) order by 1 desc; undef Interval >>> End of script - After re-sizing Oracle memory structures, re-evaluate the performance of the library, data dictionary and buffer caches. If memory for one structure has been reduced, consider increasing the memory allocating for another structure. Tune operating system again to ensure paging and swapping has not become excessive. - Small, frequently accessed tables can be placed to the MRU end of LRU lest if size of this table is less then INIT.ORA parameter CACHE_SIZE_THRESHOLD. (create table (...) cache) - Tuning I/O - very important. Processes related to tuning I/O: - DBWR - LGWR - Server processes Issues affecting performance: - Disk contention - Number of I/O - Dynamic space management (new extents allocation) Note: Tuning memory generally minimizes the amount of I/O performed by the database. Make I/O more efficient by father tuning. - Reduce disk contention. Oracle I/O can be determined from statistics in the V$FILESTAT table with columns: . . . . . . PHYRDS - number of reads from each database file. PHYWRTS - number of writes to each database file. . . . . . . >>> Begin of script set echo on termout on prompt ----------> Determining the number of reads and prompt ----------> of reads and writes to each database file prompt ----------> (access on per-file basis) select Name, Phyrds, Phywrts from V$DATAFILE DF, V$FILESTAT FS where DF.File# = FS.File#; >>> End of script Use your operating system facilities to determine the number of reads and writes to EACH NON-DATABASE FILE. The total I/O for each disk is the total number of reads and writes to all files on the disk. - Access grouped by tablespaces >>> BEGIN OF SRIPT rem Working View (Like in UTLBSTAT, UTLESTAT) create or replace view STATS$FILE_VIEW as select Ts.Name Table_space, I.Name File_Name, X.Phyrds Phys_Reads, X.Phywrts Phys_writes, X.Readtim Phys_rd_Time, X.Writetim Phys_Wrt_Tim, X.Phyblkrd Phys_blks_rd, X.Phyblkwrt Phys_Blks_Wr from V$FILESTAT X, TS$ TS, V$DATAFILE I, FILE$ F where I.File# = F.File# and TS.Ts# = F.Ts# and X.File# = F.File# ; rem rem prompt ----------> Access on per-tablespace basis select Table_space, sum(Phys_rd_Time) Phys_Rd_Time, sum(Phys_Writes) Phys_Writes, sum(Phys_Blks_Wr) Phys_Blks_Wr, sum(Phys_Wrt_Tim) Phys_Wrt_Tim from STATS$FILE_VIEW group by Table_Space order by Table_Space; rem >>> END OF SRIPT - Tuning to reduce disk I/O INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_CUNT should be set so that Oracle7 can read a maximum number of blocks in one I/O operation during a sequential scan. - Distribute I/O (the following recommendations are valid if you have MORE THEN 3-5 disk drives!): - Separate data files and redo log files on different disks: - place frequently accessed data files on separate disks. - place each set of redo log files on a separate disks with little activity. - Separate (stripe) large table data on different disks: >>> Begin of script create tablespace STRIPEDTABLESPACE datafile '' size 500K, '' size 500K, '' size 500K, '' size 500K, '' size 500K; Better solution: create table STRIPEDTABLE ( Col_1 number(5), Col_2 char(3), . . . tablespace STRIPEDTABLESPACE storage ( initial 495k minextents 1 pcincrease 0); alter table STRIPEDTABLE allocate extent (size 495K file >> End of script - Separate tables and indexes on different disks. - Reduce disk I/O not related to the Oracle server. NOTE: Any disks operating at or near FULL CAPACITY are potential sites for disk contention. - Allocate space in data blocks: - Size of Oracle block DB_BLOCK_SIZE init.ora parameter - Data block space is used when row is inserted or updated - Data block space id freed when rows are deleted or updated - Migrated rows (which moved from one data block to another) or chained rows (which span more then one data block) DEGRADE performance. Collecting info about chained and migrating rows and attempting to make them "usual" rows: >>> Begin of script set echo off feedback off termout off set heading off verify off trimout on trimspool on set pagesize 0 linesize 120 recsep off set tab on trimout on set newpage 0 rem rem The name of this file: CHKMIGR.SQL rem rem This script requires an argument rem representing a name of the analyzing table rem You should replace by real rdbms rem directory rem rem The first argument on command line must be a rem table name ([USER.]TABLE). Wildcards (%,_) are rem accepted. If USER portion of a table name is rem not specified the current username is assumed rem rem Note: rem I used command HOST DEL to delete file from the rem current directory. rem On unix system you should use HOST rm command rem instead rem rem Let's parse the parameter against user name rem col Lf Newline col T format a30 hea 'Table Name(s)' New_value Table_name col U format a30 hea 'User Name' New_value User_name col N New_value L select instr ('&1','.') N from DUAL; spool CHKMIGR.TMP select decode (&L, 0, 'select User U, Upper(''&1'') T from DUAL;', 'select upper(substr (''&1'', 1, &L - 1)) U, upper(substr (''&1'', &L + 1)) T from DUAL;') Lf from DUAL; spool off start CHKMIGR.TMP host del CHKMIGR.TMP rem Create table CHAINED_ROWS for Oracle7 to store collecting info: drop table CHAINED_ROWS; rem start :utlchain.sql: create table CHAINED_ROWS ( owner_name varchar2(30), table_name varchar2(30), cluster_name varchar2(30), head_rowid rowid, timestamp date ); rem Store info about chained rows into table CHAINED_ROWS: analyze table &1 list chained rows into CHAINED_ROWS; rem Get ROWIDs of chained and migrated rows: col C format 99999999 hea "Chained|rows" new_value CRC select count (Head_rowid) C from CHAINED_ROWS where Table_name = upper('&table_name'); rem rem Let's check chained rows counter CRC against 0 and rem proceed if CRC > 0 rem spool chained.tmp select 'set termout on' Lf, '-' Lf, 'prompt There is no any chained/migrating rows' Lf, 'prompt in table &1' Lf, '-' Lf, 'Exit' Lf from DUAL where &CRC = 0; rem spool off start chained.tmp host del chained.tmp rem rem There is some chained rows and we now should: set termout on prompt There are &CRC chained rows in table &1 set termout off rem rem 1. save migrating rows from the table &table_name rem into an intermediate table. rem drop table WORKING_CHAINED_ROWS; create table WORKING_CHAINED_ROWS as select * from &1 where Rowid in (select Head_rowid from CHAINED_ROWS); rem rem rem 2. Delete all the migrating rows from the table rem &Table_name (this table may be a master of other rem table and we have to disable all the constraints rem and triggers before that). rem rem To disable and then enable all the disabled rem referential constraints we dynamically create two rem scripts: DISRCONS.TMP and ENARCONS.TMP. The first rem one contains "alter table DISABLE constraint rem " for all the enabled constraints and rem the second one contains "alter table ENABLE rem constraint " for all the previously rem disabled constraints. rem set termout off spool DISRCONS.TMP select unique 'alter table '|| X.Owner ||'.' || X.Table_Name || ' DISABLE constraint ' || X.Constraint_Name || ';' from DBA_CONSTRAINTS X, DBA_CONS_COLUMNS Y where X.R_constraint_name = Y.Constraint_name and X.Owner like '&User_name' and X.R_Owner = Y.Owner and Y.Table_Name like '&Table_name' and X.Constraint_Type = 'R' and X.Status= 'ENABLED'; spool off rem spool ENARCONS.TMP select unique 'alter table '|| X.Owner ||'.' || X.Table_Name || ' ENABLE constraint ' || X.Constraint_Name || ';' from DBA_CONSTRAINTS X, DBA_CONS_COLUMNS Y where X.R_constraint_name = Y.Constraint_name and X.Owner like '&User_name' and X.R_Owner = Y.Owner and Y.Table_Name like '&Table_name' and X.Constraint_Type = 'R' and X.Status= 'ENABLED'; spool off rem rem Disable referential constraints and delete the file start DISRCONS.TMP host del DISRCONS.TMP rem Disable all the triggers for the table: Alter table &1 DISABLE all triggers; rem and delete rows from the table: delete &1 where ROWID in (select Head_rowid from CHAINED_ROWS); rem rem rem 3. Insert all the deleted migrating rows back into rem the original table (INSERT should insert the rows rem as usual non-migrating rows). rem insert into &1 select * from WORKING_CHAINED_ROWS; commit; rem rem 4. Drop the intermediate table and re-enabling rem constraints and triggers for the original table rem drop table WORKING_CHAINED_ROWS; start ENARCONS.TMP host del ENARCONS.TMP rem Alter table &Table_name enable all triggers; rem rem 5. Delete the records from the CHAINED_ROWS table; rem truncate table CHAINED_ROWS; rem rem 6. Analyze the table against migrating or chained rows rem again rem analyze table &1 list chained rows into CHAINED_ROWS; select count (Head_rowid) C from CHAINED_ROWS where Table_name = upper('&table_name'); set termout off drop table CHAINED_ROWS; spool Fincrows.tmp select 'set termout on' lf, '-' Lf, 'prompt &CRC chained rows remain in table &1' lf, 'prompt If migrating rows still exist then:' lf, 'prompt 1. You may have very large PCTFREE.' lf, 'prompt . and you can try to decrease PCTFREE' lf, 'prompt . and export/drop/re-create/import' lf, 'prompt 2. You may need larger DB_BLOCK_SIZE' lf, 'prompt . (a lot of work...)' lf, 'set termout off' lf from DUAL where &CRC > 0; select 'set termout on' lf, '-' Lf, 'prompt Chained rows problem for &1 FIXED!!!' lf, 'set termout off' lf from DUAL where &CRC = 0; spool off start Fincrows.tmp host del Fincrows.tmp exit; >>> End of script - select valid values for PCTFREE and PCTUSED - Choosing PCTFREE: - a low value for PCTFREE can improve the performance of full table scans, because there are fewer block to scan. Choose a low value for tables that are only queried and never modified. - a high value for PCTFREE reduces the likelihood of migration. - Choosing PCTUSED: - A low value for PCTUSED reduces the likelihood of migration as blocks tend to have more free space to accept long updates - A high value for PCTUSED stores data in a more space-efficient manner, but it increases the likelihood of migrating rows. Blocks are added to the free lists more frequently, which requires more processing time to manage the free lists. NOTE: To improve performance by allocating data block storage, it's necessary to know what a database operations are performed MOST OFTEN on EACH table. - Avoid dynamic space management. Dynamic extension occurs when an object exceeds the space allocated in it's initial extents. Oracle must then dynamically create more extents for that object. Dynamic extension: - causes issue of Recursive Calls which - reduce performance Situations which generate recursive Calls: - dynamic extension - misses on the data dictionary cache - firing of database triggers (for Oracle < 7.3) - execution of SQL statements within stored procedures, functions, packages and anonymous PL/SQL blocks. - enforcement of referential integrity constraints. Monitoring the number of recursive calls: >>> Begin of script set echo ON termout ON prompt --------------> RECURSIVE CALLS MONITORING select Name, Value from V$SYSSTAT where Name = 'recursive calls'; rem rem if recursive calls are caused by dynamic extension (to rem determine this you should examine the NUMBER OF rem EXTENTS in existing database tables) then allocate rem larger extents. >>> End of script - Tune contention Contention occurs when multiple processes attempt to access the same resource simultaneously, and thus must wait to access resource. Possible contention problems: - Contention for rollback segments - Contention for redo log buffer latches - Contention for multi-threaded server (MTS) processes - Process contention. - Reduce Rollback Segment Contention Block contention statistics is in V$WAITSTAT table. Column Class contains names of statistics: - 'system undo header' - number of waits for buffers containing header blocks of SYSTEM rollback segment. - 'system undo block' - number of waits for buffers containing blocks other than header blocks of the SYSTEM rollback segment. - 'undo header' - number of waits for buffers containing header blocks of rollback segments other then the SYSTEM rollback segment. - 'undo block' - number of waits for buffers containing blocks other then header blocks of rollback segments other than the SYSTEM rollback segment Determining the contention by following script (it should be run AFTER SUBSTANTIAL TIME SINCE STARTUP): >>> Begin of script set echo ON prompt --------> ROLLBACK SEGMENTS CONTENTION MONITORING set echo off verify off column TW format 99999999999999 heading 'Total Waits'- New_Value TWV column Class format a18 heading 'Statistic''s Name' column Count format 99999999999 hea 'Count' column PT format 99999.99 hea '% of tot' rem Get Total Waits into TWV variable: select sum(nvl(Value,0)) TW from V$SYSSTAT where Name in ('db block gets', 'consistent gets'); rem select Class, Count, nvl(Count,0)/&TWV PT from V$WAITSTAT where Class in ('system undo header', 'system undo block', 'undo header', 'undo block'); set echo on rem rem "% of total" should not be GREATER THEN 1% for ANY rem CLASS. Otherwise you should INCREASE the number of rem rollback segments. rem >>> End of script - Reduce Redo Log buffer contention. - Determine whether user processes wait for space in the redo log buffer by the following script: >>> Begin of script set echo on termout on prompt ------------> REDO LOG BUFFER CONTENTION MONITORING select Name, Value from V$SYSSTAT where Name = 'redo log space requests'; rem The Value should be near 0. If this Value increments rem consistently, INCREASE THE SIZE OF LOG BUFFER BY rem INCREASING THE VALUE OF THE LOG_BUFFER INIT.ORA rem parameter. >>> End of script - Regulate access to the redo log buffer with latches. NORMALLY THIS IS NOT A PROBLEM and should not be tuned! - only one person at a time can allocate space in the redo log buffer. - small entries to be written immediately after space allocation. - copy larger entries in parallel. . / \ / \ / \ / Log_Entry \ / _prebuild \ / _treshold \ Yes \ \--------------------+ \ = / | \ / | \ 0 / | \ ? / | \ / | \./ | | | |No | | | | | +-----------+ | . | / \ | / \ | / Is \ | / there a \ | / previous \ | / entry held \ No | \ for this \----+ | \ process? / | | \ / | | \ / | | \ / | | \ / | | \./ | | | | | | | | | | | +------------------------+ | | | Add the prvious entry | | | | to this entry for the | | | | process into a special | | | | memory buffer. | | | +------------------------+ | | | | | | | | |<----------------+ | . | / \ | / \ | / \ | / Is the \ | / entry \ +-------------------+ | / smaller \ Yes | Hold this entry | | \ then \-------| till the next one | | \ Log_Entry / | for this process | | \ _Prebuild / +-------------------+ | \ _threshold / | \ / | \ / | \./ | | | No | | +-------------+-----------------------------+ | | | +------------------------+ | ALLOCATE REDO ALLOCA- | | TION LATCH (calculate | | and reserve space in | | Redo Log Buffer) | +------------------------+ | | . / \ / \ / Is \ / this entry \ / a "small" \ Yes / entry? (i.e. \ No +------ \ <= \ -------+ | \ Log_Small_Entry / | | \ _Max_size) / | | \ / | | \ / | | \ / | | \./ | | | | | +--------------------+ +--------------------+ | Copy on the Redo | | Release the Redo | | Allocation Latch | | Allocation Latch | +--------------------+ +--------------------+ | | | | +--------------------+ +--------------------+ | Release Redo | | Get and Copy on a | | Allocation Latch | | Redo Copy Latch | +--------------------+ +--------------------+ | | +--------------------+ | Release the Redo | | Copy Latch | +--------------------+ - Latches: Latches are the Oracle method of establishing process ownership of objects that the database needs to use. Latch is the a very efficient internal Oracle lock. Redo Allocation Latch - controls allocation of space for redo entries in the redo log buffer. All processes that write to the redo log buffer must obtain this latch. There is ONLY ONE redo allocation latch. After allocation this latch the server process may copy the entry into the buffer while holding the Redo Allocation Latch. After copying the server process releases the latch. This process is called "copying on redo allocation latch". A process may copy on the redo allocation latch if redo entry is smaller then threshold size (LOG_SMALL_ENTRY_MAX_SIZE in bytes). If greater then 0 Oracle uses: Redo Copy Latch (for multiple_CPU machines ONLY) - if the redo entry is too large to copy on the redo allocation latch, the server process must obtain a redo copy latch before copying the entry into the buffer. While holding a redo copy latch, the server process copies the entry in the buffer and then releases the redo copy latch. If you computer has multiple CPUs, your redo log buffer can have multiple redo copy latches. This allows multiple processes to copy entries to the redo log buffer concurrently. The number of Redo Copy Latches - LOG_SIMULTENEOUS_COPIES (defaulted to CPU_COUNT which defaulted to real number of processors). On a single-CPU computer. LOG_SIMULTENEOUS_COPIES should be set to TWICE the number of CPUs on multi-processor machines. This will reduce any potential contention for the redo copy latch. Latches are the Oracle method of establishing process ownership of objects that the database needs to use. Latch is the a very efficient internal Oracle lock. The objective is to have the HIT_RATIOs as high as possible: this means that when a latch is requested, it is available. The SLEEPS should be as low as possible: this means a latch was requested and could not be supplied. The process that requested the latch went into a sleep state until the needed resource was available. Sleeps should be avoided. If the process can't get the requested latch, rather than sleep, it TIMES OUT. The goal is to have NOWAIT_HIT_RATIO as close to 1 as possible. Important: ACCESS TO THE REDO LOG BUFFER IS REGULATED BY LATCHES of two types: (1)redo copy latch and (2) redo allocation latch. As update occurs, space is allocated in the redo log buffer using REDO ALLOCATION LATCH. There is os only one redo allocation latch. Hence, only one user can allocate space in the redo log buffer at a time. The most information that can be copied on the redo allocation latch at a time is determined by init.ora parameter LOG_SMALL_ENTRY_MAX_SIZE. If the amount of information to be copied is greater then LOG_SMALL_ENTRY_MAX_SIZE then Oracle uses REDO COPY LATCH instead of redo allocation latch. While holding the redo copy the USER PROCESS will fill the redo log buffer with its information. When th proces is done, it then releases the redo copy latch. If you computer with multiple CPUs, then you are allowed to have one redo copy latch per CPU. Number of latches is set through LOG_SMULTANEOUS_COPIES INIT.ORA parameter (default is the number of CPUs). If you hit ratio of redo allocation (from REPORT.TXT) falls below 85%, then you have latch contention. In this case make the parameter LOG_SMALL_ENTRY_MAX_SIZE smaller. By making this value smaller you will cause more activity to happen on the redo copy latch. If you are having redo copy latch contention, then increase the init.ora parameter LOG_SIMULTANEOUS_COPIES. Another way to reduce contention on the redo log latches is to tell Oracle to prebuild the redo entries. Many times, an Oracle user's redo activity is made up of many small pieces. It can be much more efficient to instruct the database to put all the pieces together BEFORE requesting the redo copy latch. It can be done through LOG_ENTRY_PREBUILD_THRESHOLD init.ora parameter (default is 0). - Examining of redo log activity. Each row of V$LATCH table contains statistics for a different type of latch: - willing to wait (WTW)- if the latch requested with this type of request is not available, the requesting process waits a short time and requests the latch again and continues until the latch available - immediate (IMM)- if the latch requested with this type of request is not available, the requesting process does not wait and continues processing. Oracle decides INTERNALLY whether to issue WTW or an IMM requests. Monitor the statistics for the redo allocation latch and the redo copy latch with this script: >>> Begin of script set termout on prompt -------------> MONITOR LATCH CONTENTION set echo off feedback off termout on set verify off col Name format a15 col Gets format 9999999999999999 col Misses format 9999999999 col M_TO_GETS format 9.99 hea 'Rat_1|%' col Immediate_gets format 99999999999999 - hea 'Immediate|gets' col Immediate_Misses format 999999999 - hea 'Immediate|Misses' col M_TO_GM format 9.99 hea 'Rat_2|%' col Sleeps format 9999999999 select Ln.Name, Gets, Misses, decode(Gets,0,0,Misses/Gets*100) M_TO_GETS, Immediate_gets, Immediate_misses, Immediate_misses/ (Immediate_gets+decode(Immediate_misses,0,1, NULL, 1, Immediate_misses) )*100 M_TO_GM, Sleeps from V$LATCH L, V$LATCHNAME LN where LN.Name in ('redo allocation', 'redo copy') and LN.Latch# = L.Latch#; set echo on feedback on termout on rem rem If Rat_1 > 1% or Rat_2 > 1% then contention for a rem latches may affect performance and you should try rem to reduce contention for a latch: rem You should minimize the time that any single rem process holds a latch. To reduce this time, rem reduce amount of data copied on redo rem allocation latch. Decreasing the value of rem the LOG_SMALL_ENTRY_MAX_SIZE (INIT.ORA) rem reduces the number and sizes of redo entries rem copied on redo allocation latch. rem rem On multiple-CPU computers processes copy rem entries to the redo log buffer concurrently. rem The default value of LOG_SIMULTANEOUS_COPIES rem is the number of available CPUs. In case of rem you observed contention (Rat_n > 1%), rem increase the number of redo copy latches rem (value of LOG_SIMULTANEOUS_COPIES). It can rem help to have UP TO TWICE as many redo copy rem latches as CPUs available to your computer. >>> End of script - Tunung the shared pool with the Multi-Threaded Server (MTS). (See [STUN72] pp. 8-16 - 8-17) Oracle stores session information in the shared pool with MTS Session information includes: - private SQL areas; - sort areas. Hence, in case of using MTS SHARED_POOL_SIZE shoul be much more LARGER then for dedicated servers. The size of session information can be found in V$SESSTAT view. The related statistics are: "session memory" - amount of memory in bytes allocated to the session "max session memory" - maximum amount of memory in bytes ever allocated to the session - Querying V$SESSTAT: - All the session (including those using DEDICATED connections as well) >>> Begin of script rem rem Memory allocated to all the sessions at the moment rem select sum(Value) || ' bytes' "Total memory for all sessions" from V$SESSTAT, V$STATNAME where Name = "session memory" and V$SESSTAT.Statistic# = V$STATNAME.Statistic#; >>> End of script >>> Begin of script rem rem Max memory ever allocated to all the sessions rem at the moment rem select sum(Value) || ' bytes' "Total max memory for all sessions" from V$SESSTAT, V$STATNAME where Name = "max session memory" and V$SESSTAT.Statistic# = V$STATNAME.Statistic#; >>> End of script You should periodically save this informatoin into working table and then analyze them... - Reduce Multi-Threaded Server (MTS) contention This script taken from Steve Bobrowski's paper (OM 7-8.1995 p.85) >>> Begin of script rem LISTING 1: Use the GATHER_MTS_STATISTICS rem procedure to monitor the configuration of an rem Oracle server's MTS. Rem MTS Statistic Gathering Rem Author: Animated Learning V2. 0 1995 Rem Rem The following script creates two tables and Rem a stored procedure that one can use to gather Rem statistics about an Oracle server's MTS Rem configuration. Rem Rem ************************************* Rem The following script must be run as SYS Rem ************************************* Rem Rem The MTS_DISP_STSTS table stores data points for Rem MTS dispatchers Rem drop table MTS_DISPATCHER_STATS; create table MTS_DISPATCHER_STATS ( Timepoint DATE, -- Time of data point Disp_name VARCHAR2 (10), --name of dispatcher Disp_prot VARCHAR2 (10), -- corresponding network Cumu_busy REAL, -- cumulative busy time Cumu_idle REAL, -- cumulative idle time Intv_load REAL -- interval load ); Rem Rem The MTS_SERVER_STATS table stores data points for Rem MTS shared servers. Rem drop table MTS_SERVER_STATS; create table MTS_SERVER_STATS ( Timepoint DATE, -- time of data point Server_ct INTEGER -- number of shared servers ); create or replace procedure GATHER_MTS_STATISTICS (Intervals in INTEGER) AS -- -------- -- OVERVIEW -- -- The GATHER_MTS_STATISTICS procedure gathers -- statistics for both -- dicpatchers and shared -- servers in an MTS -- configuration. Run this -- procedure from a session -- that uses a DEDICATED -- server connection to avoid -- skewing statistics -- gathered. -- -- --------------- -- INPUT PARAMETER: -- Intervals - the total number of 15-minute-sampling -- intervals to gather statistics for an MTS. -- For example, a value of 32 would gather 32 -- data points over an 8-hour timespan. -- -- -- -------- -- VARIABLES: -- Prev_busy REAL; -- previous busy count Prev_idle REAL; -- previous idle count Intv_busy REAL; -- interval busy count Intv_idle REAL; -- interval idle count Load REAL; -- interval load -- -- ------- -- CURSORS -- cursor Server is -- cursor for shared server lookups select Count(*) Servers from V$SHARED_SERVER; cursor Dispatcher is -- cursor for dispatcher lookups select Name, Network, Busy, Idle from V$DISPATCHER; -- -- ------------ -- PROGRAM BODY -- begin -- loop to gather data points for the specified -- number of intervals for Counter in 1 .. Intervals loop -- open cursor FOR loop to gather count of -- shared servers for a data point for Status in Server loop -- Each loop inserts insert into MTS_SERVER_STATS -- only one record values (Sysdate, Status.Servers); end loop; -- open cursor for loop to gather dispatcher -- load information for data point for Status in Dispatcher loop -- special if case for first data points to avoid -- divide by zero errors when calculating loads if Counter = 1 then insert into MTS_DISPATCHER_STATS values(Sysdate, status.name, status.network, status.Busy, Status.Idle, NULL); else -- get previous cumulative busy and idle -- information in preparation of calculating -- interval data select Cumu_busy, Cumu_idle into Prev_busy, Prev_idle from MTS_DISPATHER_STATS where Disp_name = Status.Name and Timepoint = (select max(Timepoint) from MTS_DISPATCHER_STATS where Disp_name = Status.Name); -- calculate interval busy and idle by -- subtracting previous data from current data Intv_busy := Status.Busy - Prev_busy; Intv_idle := Status.Idle - Prev_idle; -- special case where busy time is negative or -- zero to avoid divide by zero errors if intv_busy <= 0 then intv_busy := 0.001; -- Very low number end if; Load := Intv_busy / (Intv_busy + Intv_idle) * 100; insert into MTS_DISPATCHER_STATS values ( Sysdate, Status.Name, Status.Network, Status.Idle, Load); end if; end loop; commit work; DBMS_LOCK.SLEEP(900); -- sleep for 900 seconds = -- 15 minutes end loop; end GATHER_MTS_STATISTICS; / >>> End of script This procedure should be executed using dedicated server to prevent skewing the test results. To do this set USE_DEDICATED_SERVER = ON in sqlnet.ora file. - How to tune using this script: - Set MTS_SERVERS and MTS_DISPATCHERS in more or less acceptable values. - set MTS_MAX_SERVERS and MTS_MAX_DISPATCHERS so they do NOT limit the number of shared server and dispatcher processes your Oracle server can start during times of heavy demand. Remember that Oracle server AUTOMATICALLY starts and stops shared servers in order to adjust to changing processing demands placed on an MTS. You can also use ALTER SYSTEM command to manually change the number of dispatchers per protocol. - you need to gather statistics in the two tables with EXECUTE GATHER_MTS_STATISTICS(n); - Analysis queries: For servers select * from MTS_SERVER_STATS; Timepoint Server_ct --------- --------- 27-NOV-95 3 27-NOV-95 5 27-NOV-95 2 27-NOV-95 1 . . . . . . . . . select max(Server_ct), avg(Server_ct) from MTS_SERVER_STATS; Output represents time-stamp and number of shared servers on that moment. Keep in mind that the MINIMUM number of shared servers (MTS_SERVERS) should be fairly close to the AVERAGE number of shared servers to avoid unnecessary shared server process allocation/deallocation. The MTS_MAX_SERVERS shouldn't be so low that it limits the number of shared server needed during times of peak demand. For dispatchers: select * from MTS_DISPATCHER_STATS; select Disp_name, max(Int_load) from MTS_DISPATCHER_STATS group by Disp_name; Simply analyze the load for each dispatcher and define what an excessive load is (let's say 60% to 70%). When you find an excessive load for a dispatcher of particular network protocol, consider starting one or more additional dispatchers. - MTS and alerts for parallel server (quotation from DBMSALERT.SQL): INTERACTION WITH MULTI-THREADED AND PARALLEL SERVER When running with the parallel server AND multi-threaded server, a multi-threaded (dispatcher) "shared server" will be bound to a session (and therefore not shareable) during the time a session has any alerts "registered", OR from the time a session "signals" an alert until the time the session commits. Therefore, applications which register for alerts should use "dedicated servers" rather than connecting through the dispatcher (to a "shared server") since registration typically lasts for a long time, and applications which cause "signals" should have relatively short transactions so as not to tie up "shared servers" for too long. - Reduce process contention. ALTERING PROCESS PRIORITIES MAY DEGRADE PERFORMANCE. - assigning different priorities to Oracle processes (background and user) may cause the operating system to grant uneven processing time, and thus exacerbate the effect of contention. - all user processes must have the default operating system priorities (on VAX/VMS). - Tuning free lists contention (when number of users simultaneously insert data in the same table and index). When tables are created, you can instruct Oracle how much information to keep in memory for blocks that are available for creating records. FREE LIST is the number of block IDs that Oracle will maintain in memory that are candidates for record insertion. After the segment header there is one block for EACH free list group specified in the storage clause of the CREATE TABLE/CREATE INDEX statement. Each block contains information on the free lists in that particular free list group. This and Oracle block classes are presented in [PH95p#159v2/page 2]. Transaction space per block are affected by INITRANS, MAXTRANS, PCTFREE create table and create index parameters for both DATA space and INDEX space. INITRANS, MAXTRANS, PCTFREE create table parameters control the amount of space that keeps track of transactions against the table. The initrans parameter sets aside 23 bytes for each transaction slot in each data block that resides in a table. The space left over for expansion of existing records of the block is called pctfree space. If your applications need to support concurrent access to a table by more then one user, you should increase INITRANS value. Oracle will allocate space in a data block for transactions in these 23-byte slices, and will allocate UP to the number of slots delineated by MAXTRANS. If MAXTRANS set too low then free list BLOCK CONTENTION occurs - transaction space limited in data and index blocks! When a new transaction need a 23-bytes slot in a data block and other transactions have already occupy all the MAXTRANS slots, this new transaction will wait for previous transaction to terminate, Once 23-byte slot is allocated in the block (over INITRANS value) it is NOT DE-ALLOCATED from the block header space as unused data space for both data and index blocks. You may detect free list block contention by the following script : >>> Begin of script set echo off feedback off verify off column TW format 99999999999999 heading 'Total Waits'- New_Value TWV column Class format a18 heading 'Statistic''s Name' column Count format 99999999999 hea 'Count' column PT format 99999.99 hea '% of tot' rem Get Total Waits into TWV variable: select sum(Value) TW from V$SYSSTAT where Name in ('db block gets', 'consistent gets'); rem select Class, Count, nvl(Count,0)/&TWV PT from V$WAITSTAT where Class = 'free list'; set echo on rem rem "% of total" should not be GREATER THEN 1%. If it rem is higher, consider adding more free lists rem rem FREELISTS should be equal to the number of users rem which concurrently INSERTS data in the table. rem You should detect the needed table by analyzing rem ALL the SUSPECTING TABLES. rem >>> End of script This script don't get the names of the hot tables which give the maximal impact on free list contention. To eliminate this contention DBA should increase INITRANS and MAXTRANS for these hot tables. Usually, these tables are known for each application. Hence, it is important for DBA to KNOW the APPLICATIONS. FREELISTS entries for a table should accommodate the number of concurrent processes that will be inserting data into a table To change FREELISTS parameter for a table you need to do the following: 1. Export the data from the table with grants and indexes. 2. Drop the table. 3. Recreate table with increased FREELISTS parameter. 4. Import the data back into the table with IGNORE=Y - Tune checkpoints. Checkpoint frequency can affect performance. Enabling checkpoint process (by CHECKPOINT_PROCES=TRUE): - reduces run-time performance impact of checkpoints. - CKPT updates the data file headers when checkpoint occurs, leaving LGWR free to write redo entries. Two parameters affect checkpoint frequency: LOG_CHECKPOINT_INTERVAL - measured in OPERATING SYSTEM BLOCKS - the number of newly filled redo log file blocks that are needed to trigger checkpoint. If this value is larger then the size of redo logs than a checkpoint only occurs when there is a log switch. The more this interval the more startup time (time needed to recover after crash). On the other hand, high frequency of checkpoints may degrade the performance. Too frequent checkpoints diminish the benefit of delayed database file writes in a batch rather than transaction mode. LOG_CHECKPOINT_TIMEOUT - seconds since the last checkpoint If performance is your concern, set LOG_CHECKPOINT_TIMEOUT to 0 (default) and LOG_CHECKPOINT_INTERVAL to a size greater then the physical redo log file - the checkpoint will occur only on log switching. - Monitoring and tuning using UTLBstat.SQL and UTLEstat.SQL. See also [COR95]. UTLBstat runs at the beginning if the time interval and utlestat - at the end. You should carefully chose this time interval. UTLBstat gathers the initial performance statistics in 9 special tables at the beginning of your observation period. UTLEstat: - gathers the final performance statistics in 7 special tables at the end of your observation period. - does comparison between UTLEstat and UTLBstat tables and stores the results into 7 difference tables - generate ASCII file called REPORT.TXT UTLBstat should not be run immediately after the database has been started because none of the system caches are loaded and this would not be a realistic picture of a running database. The database should NOT be went down between UTLBstat and UTLEstat. The correct time slice must be chosen, for example, the peak processing time. INIT.ORA must contain TIMED_STATISTIC = TRUE to run UTLBstat/UTLEstat. What is good about these utilities that there is no performance hit on the database except for a few minutes to run these program. All these programs do is gather a snapshot of the system at the start and a snapshot an the end. - Interpret the output. - Library cache. You should minimize RELOADS (see "Tuning of the library cache"). Also put the attention on PINHITRATIO and GETPIRATIO columns. Any value < 80% is NOT acceptable. The solution is to increase SHARED_POOL_SIZE parameter. - System statistics The info shown in the UTLEstat output lists total system statistics for the time period, broken down by transaction average. Analyzing report UTLEstat system stat you should calculate the hit ratio: logical reads = consistent gets + db_block_gets hit ratio=(logical_reads-physical reads)/logical reads CONSISTENT GETS, DB_BLOCK_GETS and others should be taken from REPORT.TXT (output of UTLEstat). HIT RATIO must be greater then 80%. Otherwise, increase DB_BLOCK_BUFFERS INIT.ORA parameter - Events. The UTLEstat output shows the system events. You should analyze the following: - Buffer busy waits ratio (waits for resources). Logical reads is calculated based on system statistics in previous section, BUFFER BUSY WAITS - is the last line in the "Events" (this section) output: buffer busy waits ratio=buffer busy waits/logical reads where logical reads = consistent gets + db block gets BUFFER BUSY WAITS RATIO should be < 4%. Otherwise, you should examine the V$WAITSTAT table to determine what kind of waits you encount