spool tuneOra7.lis break on today column today new_value _date select to_char(SYSDATE, 'fmMonth DD, YYYY') today from dual; clear breaks prompt prompt TUNING MEMORY ALLOCATION prompt prompt SQL-Statement #1 prompt prompt . Percent Missed should be near zero. If it is more than 1% than prompt . you should do the following... prompt prompt . - allocate additional memory for the library cache prompt . - write identical SQL statements whenever possible prompt prompt . Increase the "INIT" parameters "SHARED_POOL_SIZE" prompt . "OPEN_CURSORS" prompt prompt . If you have no library cache misses, you may still be able to speed prompt . execution calls by setting... "CURSOR_SPACE_FOR_TIME" = TRUE prompt prompt . CAUTION: If you set this parameter to TRUE and the shared pool area prompt . runs out of space, the next user to issue a NEW SQL statement prompt . will get a "no space in shared pool area" error. prompt . Stopping their process... prompt select sum(pins) "Executions", sum(reloads) "Cache Misses while Executing", round(((sum(reloads) / sum(pins)) * 100),3) "Percent Missed" from V$LIBRARYCACHE; prompt prompt SQL-Statement #2 prompt prompt . Percent Missed should be less than 10%. If it is more than 10% than prompt . you should do the following... prompt prompt . Increase the "INIT" parameters "SHARED_POOL_SIZE" prompt select sum(gets) "Data Dictionary Gets", sum(getmisses) "Data Dictionary Get Misses", round(((sum(getmisses) / sum(gets)) * 100),3) "Percent Missed" from V$ROWCACHE; prompt prompt SQL-Statement #3 prompt prompt . The Hit Ratio should be over 90% prompt . If it is not than do the following... prompt prompt . Increase the "INIT" parameters "DB_BLOCK_BUFFERS" prompt column value format 999,999,999,990; select name, value from V$SYSSTAT where name in ('db block gets', 'consistent gets', 'physical reads'); prompt . Hit Ratio = 1 - (physical reads / (db block gets + consistent gets)) select round(((1 - (A.value / (B.value + C.value))) * 100),3) "Hit Ratio" from V$SYSSTAT A, V$SYSSTAT B, V$SYSSTAT C where A.name = 'physical reads' and B.name = 'db block gets' and C.name = 'consistent gets'; prompt prompt TUNING DISK I/O prompt prompt SQL-Statement #1 prompt prompt . Total the I/O for each drive and make sure it is spread across prompt . the drives evenly... prompt column name format a40 set pagesize 100 select Name "Name", Phyrds "Phyrds", Phywrts "Phywrts", phyrds + phywrts "Sum" from V$DATAFILE df, V$FILESTAT fs where df.file# = fs.file# order by name; set pagesize 22 prompt SQL-Statement #2 prompt prompt . Dynamic Extention can cause excessive recursive calls.. prompt . Check for alot of smaller extents and reduce them to one larger extent. prompt . Other causes are... prompt . - Misses on the data dictionary cache prompt . - Firing of database triggers prompt . - Execution of Data Definition Language statements prompt . - Execution of SQL statements within stored procedures, prompt . functions, packages, and anonymous PL/SQL blocks. prompt . - Enforcement of referential integrity constraints. Column value format 999,999,999,990; select name "Name", value "Value" from V$SYSSTAT where name = 'recursive calls'; prompt prompt TUNING CONTENTION prompt prompt SQL-Statement #1 prompt prompt . Tuning Rollback Segment Contention prompt prompt . Percent Waits should be less than 1%. If it is more than 1% for any class prompt . than you should do the following... prompt prompt . Increase the number of rollback segments... prompt prompt . Guide Lines for the Number of rollback segments... prompt prompt . # of Concurrent Transactions (n) | Recommended # of Rollback Segments prompt . ___________________________________|___________________________________ prompt . n < 16 | 4 Rollback Segments prompt . 16 <= n < 32 | 8 Rollback Segments prompt . 32 <= n | n / 4, but no more than 50 R/S select A.class "Class", A.count "Count", sum(B.value) "Total Requests", round(((A.count / sum(B.value)) * 100),3) "Percent Waits" from V$WAITSTAT A, V$SYSSTAT B where A.class in ('system undo header', 'system undo block', 'undo header', 'undo block') and B.name in ('db block gets', 'consistent gets') group by A.class, A.count order by 1; prompt SQL-Statement #2 prompt prompt . Tuning Redo Log Buffer Latches prompt prompt . Value should be near zero. If it is not then do the following... prompt prompt . Increase LOG_BUFFER in increments of 5% until "Value" nears zero. select name "Name", value "Value" from V$SYSSTAT where name = 'redo log space requests'; prompt SQL-Statement #3 prompt prompt . Tuning Redo Log Activity prompt prompt . If ((Misses / Gets) * 100) > 1% or prompt . ((Immediate Misses / (Immediate Gets + Immediate Misses)) * 100) > 1% prompt . for either record than... prompt prompt . Reducing Contention for "Redo Allocation"... prompt . - Decrease the "INIT" parameter "LOG_SMALL_ENTRY_MAX_SIZE"... prompt prompt . Reducing Contention for "Redo Copy"... prompt . - On Multi. CPU systems only... prompt . - Increase the "INIT" parameter "LOG_SIMULTANEOUS_COPIES"... prompt . This parameter should be no larger than (2 * # of CPU's)... prompt . - On all systems... prompt . - Increase "INIT" parameter "LOG_ENTRY_PREBUILD_THRESHOLD"... prompt . This parameter is expressed in bytes... column name format a20 select l.name "Name", gets "Gets", misses "Misses", immediate_gets "Immediate Gets", immediate_misses "Immediate Misses" from V$LATCH l, V$LATCHNAME ln where ln.name in ('redo allocation', 'redo copy') and ln.latch# = l.latch#; prompt prompt ADDITIONAL TUNING CONSIDERATIONS prompt prompt SQL-Statement #1 prompt prompt . Tuning Sorts prompt prompt . If a significant number of sorts are being done on disk than... prompt prompt . Increase the "INIT" parameter "SORT_AREA_SIZE"... select name "Name", value "Value" from V$SYSSTAT where name in ('sorts (memory)', 'sorts (disk)') order by 1; prompt prompt SQL-Statement #2 prompt prompt . Reducing Free List Contention prompt prompt . If Percent Wait is greater than 1% than... prompt prompt . Re-create the table and make "FREELISTS" equal to the number of prompt . Oracle processes that concurrently insert data into the table... select A.class "Class", A.count "Count", sum(B.value) "Total Requests", round(((A.count / sum(B.value)) * 100),3) "Percent Wait" from V$WAITSTAT A, V$SYSSTAT B where A.class = 'free list' and B.name in ('db block gets', 'consistent gets') group by A.class, A.count; prompt prompt SQL-Statement #1 prompt prompt . This tells ammount of Sessions allowed. prompt . This Value can be raised in the init.ora parameters and prompt . will take effect the next time the database is Mounted. prompt select value "Maximum Attaches" from v$parameter where name = 'sessions'; prompt prompt prompt prompt SQL-Statement #2 prompt prompt . This is the amount Being used at the present time. prompt select count(*) "Currently Attached" from v$session ; prompt prompt prompt prompt prompt ADDITIONAL IMPORTANT NOTES AND INIT PARAMETERS... prompt prompt . All Oracle processes should have equal priority... prompt prompt . To increase runtime performance and decrease recovery time the following prompt . can be done with "Checkpoints"... prompt prompt . - Set "INIT" parameter "LOG_CHECKPOINT_INTERVAL" larger than the size prompt . of your largest redo log file... prompt . - Set "INIT" parameter "LOG_CHECKPOINT_TIMEOUT" to zero... prompt . - You may also increase the size of your redo log files so that the prompt . redo log files do not fill up as quick... prompt . - You should also set "INIT" parameter "CHECKPOINT_PROCESS" to "TRUE" prompt . This will free up LGWR when a checkpoint occurs prompt prompt . Other "INIT" parameters to look at... prompt . - "DB_BLOCK_CHECKPOINT_BATCH". prompt . - "DB_FILE_MULTIBLOCK_READ_COUNT". prompt . - "DB_FILE_SIMULTANEOUS_WRITES". prompt . - "DML_LOCKS". prompt . - "ENQUEUE_RESOURCES". prompt . - "OPTIMIZER_MODE". prompt prompt prompt prompt spool off; exit