10:00-11:30 Memory part I & II 11:30-13:00 13:00-14:00 Memory part III 14:10-15:00 I/O Part I 15:10-16:00 I/O Part II

Size: px

Start display at page:

Download "10:00-11:30 Memory part I & II 11:30-13:00 13:00-14:00 Memory part III 14:10-15:00 I/O Part I 15:10-16:00 I/O Part II"

혜인 증
5 years ago
Views:

1 10:00-11:30 Memory part I & II 11:30-13:00 13:00-14:00 Memory part III 14:10-15:00 I/O Part I 15:10-16:00 I/O Part II

2 I - Memory Part - DB Contents Shared pool Buffer cache Other SGA structures 4/100

3 Shared pool Buffer cache Other SGA structures 5/100 System Global Area (SGA) Redo Log Buffer Large Pool Database Buffer Cache Java Pool Shared Pool 6/100

4 Shared Pool Shared pool Redo Log Buffer Large Pool Database Buffer Cache Java Pool Shared Pool Library cache Shared Data pool dictionary cache UGA SHARED_POOL_SIZE 7/100 Shared Pool Library Cache Dictionary Cache User Global Area (UGA) Large Pool 8/100

5 Library Cache Store SQL statements, PL/SQL blocks to be shared LRU (least recently used) algorithm Prevent statements reparsing 9/100 Library Cache Shared SQL, PL/SQL areas Context area for SELECT statement 2 Context area for SELECT statement 1 SELECT statement 2 SELECT statement 1 SELECT statement 1 10/100

6 Library Cache Tuning Reduce misses > keep parsing to a minimum: Share statements Prevent statements from being aged out Avoid invalidations 11/100 Library Cache Tuning Avoid fragmentation: Reserve space for large mem. reqs. Pin frequently required large objects Eliminate large anonymous PL/SQL blocks Use large pool for Oracle Shared Server cons. 12/100

7 Terminology Gets: (Parse) # of lookups for objects of the namespace Pins: (Execution) # of reads or executions of the objects of the namespace Reloads: (Reparse) # of library cache misses on the execution step, causing implicit reparsing of the statement and block 13/100 Diagnostic Tools for Tuning the Library Cache V$SGASTAT V$LIBRARYCACHE V$SQL V$SQLAREA V$SQLTEXT V$DB_OBJECT_CACHE Shared pool Library cache Shared SQL and PL/SQL Data dictionary cache UGA sp_m_n.lst report.txt Parameters: SHARED_POOL_SIZE, OPEN_CURSORS SESSION_CACHED_CURSORS, CURSOR_SPACE_FOR_TIME 14/100

8 Cursors A cursor is a handle (a name or pointer) for the memory associated with a specific statement. 15/100 16/100 Cursors Shared? V$LIBRARYCACHE.GETHITRATIO: SQL> select gethitratio 2 from v$librarycache 3 where namespace = SQL AREA ; Statements running: SQL> select sql_text, users_executing, 2 executions, loads 3 from v$sqlarea; SQL> select * from v$sqltext 2 where sql_text like 3 'select * from hr.employees where %';

9 Guidelines: Library Cache Reloads Executes PROC1 > 1st pin, 1 load Executes PROC1 > 2nd pin, no reload Executes PROC1 > 3rd pin, no reload Executes PROC1 > 4th pin, no reload 4 pins and no reloads Reloads < 0.01 * the pins: SQL> select sum(pins) "Executions", 2 sum(reloads) "Cache Misses", 3 sum(reloads)/sum(pins) 4 from v$librarycache; If reloads-to-pins ratio > 1%, increase(shared_pool_size). 17/100 Library Cache Guidelines: STATSPACK Report Library Cache Activity for DB: ORA92 Instance: ora92 Snaps: 1-2 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations BODY CLUSTER SQL AREA , TABLE/PROCEDURE /100

10 Invalidations # of times objects ( of the namespace) marked invalid, causing reloads: SQL> select count(*) from hr.employees; SQL> select namespace,pins,reloads,invalidations 2 from v$librarycache; Namespace Pins Reloads Invalidations SQL AREA TABLE/PROCEDURE BODY /100 SQL> ANALYZE TABLE hr.employees COMPUTE STATISTICS; Invalidations SQL> select count(*) from hr.employees; SQL> select namespace,pins,reloads,invalidations 2 from v$librarycache; Namespace Pins Reloads Invalidations SQL AREA TABLE/PROCEDURE BODY /100

11 Sizing Library Cache Global space for stored objs. (packages, views, etc.) Amount of memory used by the usual SQL stmts. Space for large memory reqs. to avoid misses and fragmentation Frequently used objects Large anonymous PL/SQL blocks into small anonymous blocks calling packaged functions 21/100 Cached Execution Plans Oracle server preserves the actual execution plan of a cached SQL statement in memory. When the SQL statement ages out, the corresponding cached execution plan is removed. Benefit : better diagnosis of query performance. 22/100

12 V$SQL_PLAN for Cached Execution Plans V$SQL_PLAN Actual execution plan info. for cached cursors. PLAN_TABLE cols.(w/o LEVEL col.) + extra cols. 23/100 V$SQL for Cached Execution Plans PLAN_HASH_VALUE column hash value built from the corresponding execution plan. to compare cursor plans the same way the HASH_VALUE column for comparing cursor SQL texts. 24/100

13 Example: V$SQL_PLAN SQL> SELECT p.id, p.parent_id, 2 lpad(' ',p.depth*4) p.operation "OPERATION", 3 p.options, p.object_owner "OWNER", 4 p.object_name "OBJECT" 5 FROM v$sql s, v$sql_plan p 6 WHERE s.hash_value = p.hash_value 7 AND s.sql_text like 'select count(*) from hr.employees%' 8 ORDER BY p.id; ID PARENT_ID OPERATION OPTIONS OWNER OBJECT SELECT STATEMENT 1 0 SORT AGGREGATE 2 1 INDEX FULL SCAN HR EMP_ _UK 25/100 Global Space Allocation Stored objects such as packages and views: SQL> SELECT sum(sharable_mem) 2 FROM v$db_object_cache 3 WHERE type in ( PACKAGE, PACKAGE BODY, VIEW ); SUM(SHARABLE_MEM) SQL statements: SQL> select sum(sharable_mem) 2 from V$SQLAREA where executions > 5; SUM(SHARABLE_MEM) /100

14 Large Memory Requirements Satisfy requests for large contiguous memory Reserve contiguous memory within the shared pool V$SHARED_POOL_RESERVED Shared pool Library cache Shared SQL and PL/SQL SHARED_POOL_SIZE SHARED_POOL_RESERVED_SIZE Data dictionary cache UGA 27/100 Tuning the Shared Pool Reserved Space Diagnostic tools for tuning: V$SHARED_POOL_RESERVED Supplied package and procedure: DBMS_SHARED_POOL. ABORTED_REQUEST_THRESHOLD Guidelines Set the parameter SHARED_POOL_RESERVED_SIZE 28/100

15 Keeping Large Objects Find those PL/SQL objects that are not kept in the library cache: SQL> SELECT owner, name, type FROM v$db_object_cache 2 WHERE sharable_mem > AND (type= PACKAGE or type= PACKAGE BODY or 4 type= FUNCTION or type= PROCEDURE ) 5 AND KEPT= NO ; OWNER NAME TYPE SYS STANDARD PACAKGE SYS STANDARD PACAKGE BODY SYS DBMS_UTILITY PACAKGE BODY... 29/100 Keeping Large Objects Pin large packages in the library cache: SQL> EXECUTE dbms_shared_pool.keep( package_name ); SQL> EXECUTE dbms_shared_pool.keep( SYS.STANDARD ); SQL> SELECT owner, name, type FROM v$db_object_cache 2 WHERE sharable_mem > AND (type= PACKAGE or type= PACKAGE BODY or 4 type= FUNCTION or type= PROCEDURE ) 5 AND KEPT= NO ; OWNER NAME TYPE SYS DBMS_UTILITY PACAKGE BODY... 30/100

16 Anonymous PL/SQL Blocks Find the anonymous PL/SQL blocks and convert them into small anonymous PL/SQL blocks that call packaged functions: SQL> select sql_text from v$sqlarea 2 where command_type = 47 3 and length(sql_text) > 400; 31/100 command_type desription insert 3 select 6 update 47 pl/sql execute Other Parameters Affecting the Library Cache OPEN_CURSORS CURSOR_SPACE_FOR_TIME SESSION_CACHED_CURSORS CURSOR_SHARING 32/100

17 Shared Pool Library Cache Dictionary Cache User Global Area (UGA) Large Pool 33/100 Content, Terminology, and Tuning Content: Definitions of dictionary objects Terminology: GETS: # of requests on objects GETMISSES: # of requests resulting in cache misses Goal: Avoid dictionary cache misses 34/100

18 Diagnostic Tools for Data Dictionary Cache Shared pool V$ROWCACHE: PARAMETER GETS GETMISSES Library cache Shared SQL and PL/SQL Data dictionary cache UGA Sp_m_n.lst 35/100 Measuring the Dictionary Cache Statistics Dictionary Cache Stats section of STATSPACK: Percent misses should be very low: < 2% for most data dictionary objects < 15% for the entire data dictionary cache Cache Usage : # of cache entries being used. Pct SGA : the ratio of usage to allocated size for that cache. 36/100

19 Tuning the Data Dictionary Cache SUM(GETMISSES)*100 / SUM(GETS) < 15% SQL> select parameter, gets, getmisses 2 from v$rowcache; PARAMETER GETS GETMISSES dc_objects dc_synonyms /100 Guidelines: Dictionary Cache Misses STATSPACK report output: NAME GET_REQS GET_MISS dc_objects dc_synonyms If too many cache misses, increase SHARED_POOL_SIZE. 38/100

20 Shared Pool Library Cache Dictionary Cache User Global Area (UGA) Large Pool 39/100 UGA and Oracle Shared Server Dedicated server connection: Shared pool Stack space PGA User session data UGA Cursor state Oracle Shared Server connection: no large pool Stack space Shared pool User Cursor PGA session data UGA state V$STATNAME V$SESSTAT V$MYSTAT 40/100 OPEN_CURSORS SESSION_CACHED_CURSORS

21 41/100 Sizing the User Global Area UGA space used by your connection: SQL> select SUM(value) 'bytes' "Total session memory" 2 from V$MYSTAT, V$STATNAME 3 where name = 'session uga memory' 4 and v$mystat.statistic# = v$statname.statistic#; UGA space used by all Oracle Shared Server users: SQL> select SUM(value) 'bytes' "Total session memory" 2 from V$SESSTAT, V$STATNAME 3 where name = 'session uga memory' 4 and v$sesstat.statistic# = v$statname.statistic#; Maximum UGA space used by all users: SQL> select SUM(value) 'bytes' "Total max memory" 2 from V$SESSTAT, V$STATNAME 3 where name = 'session uga memory max' 4 and v$sesstat.statistic# = v$statname.statistic#; Shared Pool Library Cache Dictionary Cache User Global Area (UGA) Large Pool 42/100

22 Large Pool Redo Log Buffer Large Pool Database Buffer Cache Java Pool Shared Pool 43/100 A separate memory area in the SGA, used for memory with: DBWR_IO_SLAVES Backup and restore operations Session memory Parallel query messaging Useful in these situations to avoid performance overhead caused by shrinking the shared SQL cache Parameter : LARGE_POOL_SIZE Shared Pool Buffer Cache Other SGA Structures 44/100

23 Buffer Cache Overview of buffer cache Dynamic SGA allocation DB_CACHE_ADVICE Multiple buffer pools Free list contention 45/100 Server Overview LRU list SGA Checkpoint queue DB buffer cache.. DBWn DB_BLOCK_SIZE DB_CACHE_SIZE DB_KEEP_CACHE_SIZE DB_RECYCLE_CACHE_SIZE Data files 46/100

24 Buffer Cache Physical I/O 47/100 Buffer Cache Parameters (Oracle9i) Independent subcaches Multiple block sizes. DB_BLOCK_SIZE parameter : primary block size (for the SYSTEM tbs) Sizes of the caches for buffers: DB_CACHE_SIZE DB_KEEP_CACHE_SIZE DB_RECYCLE_CACHE_SIZE 48/100

25 Buffer Cache Overview of buffer cache Dynamic SGA allocation DB_CACHE_ADVICE Multiple buffer pools Free list contention 49/100 Dynamic SGA Feature in Oracle9i Allowing the server to change its SGA configuration w/o shutting down the instance. With a dynamic SGA, the Oracle server can modify its physical address space use to respond to the operating system s use of physical memory. A dynamic SGA provides an SGA that will grow and shrink in response to a DBA command. 50/100

26 Granule (Oracle9i) SGA memory is tracked in granules. Unit of contiguous virtual memory allocation. V$BUFFER_POOL size of a granule : 4 MB if estimated_size(sga) < 128 MB 16 MB otherwise 51/100 Allocating Granules at Startup At instance startup, the Oracle server allocates granule entries, one for each granule to support SGA_MAX_SIZE bytes of address space. As startup continues, each component acquires as many granules as it requires. Minimum SGA configuration is 3 granules: 1 granule for fixed SGA (includes redo buffers) 1 granule for the buffer cache 1 granule for the shared pool 52/100

27 Adding Granules to Components A DBA can dynamically increase memory allocation to a component by issuing an ALTER SYSTEM command. Increase of the memory use of a component succeeds only if there are enough free granules to satisfy the request. Memory granules are not freed automatically from another component in order to satisfy the increase. Decrease of a component is possible, but is successful only if the corresponding number of granules remain unused by the component. 53/100 Dynamic Buffer Cache Size Parameters Parameters that specify the size of buffer cache components are dynamic, and can be changed while the instance is running by means of the ALTER SYSTEM: SQL> ALTER SYSTEM SET DB_CACHE_SIZE = 1100M; Each parameter is sized independently. New cache sizes are set to the next granule boundary. limitations: Integer multiple of the granule size. The total SGA size cannot exceed MAX_SGA_SIZE. DB_CACHE_SIZE can never be set to zero. 54/100

28 Example: Increasing the Size of an SGA Component 55/100 Initial parameter values: SGA_MAX_SIZE = 128M DB_CACHE_SIZE = 88M SHARED_POOL_SIZE = 32M SQL> ALTER ALTER SYSTEM SET SET SHARED_POOL_SIZE = 64M; Error message indicating insufficient memory SQL> ALTER ALTER SYSTEM SET SET DB_CACHE_SIZE = 56M; SQL> ALTER ALTER SYSTEM SYSTEM SET SET SHARED_POOL_SIZE = 64M; Error message indicating insufficient memory Check V$BUFFER_POOL to see shrinking completed SQL> ALTER ALTER SYSTEM SET SET SHARED_POOL_SIZE = 64M; The statement is now processed. Deprecated Buffer Cache Parameters 3 parameters: backward compatibility DB_BLOCK_BUFFERS BUFFER_POOL_KEEP BUFFER_POOL_RECYCLE Not combined with the dynamic size params. ORA-00381: cannot use both new and old parameters for buffer cache size specification 56/100

29 Buffer Cache Overview of buffer cache Dynamic SGA allocation DB_CACHE_ADVICE Multiple buffer pools Free list contention 57/100 Dynamic Buffer Cache Advisory Parameter Enable and disable statistics gathering for predicting behavior with different cache sizes Help DBAs size the buffer cache DB_CACHE_ADVICE: SQL> ALTER SYSTEM SET DB_CACHE_ADVICE = OFF ON READY; 58/100

30 V$DB_CACHE_ADVICE 59/100 Predicting the estimated number of physical reads for different cache sizes. The rows also compute a physical read factor, which is the ratio of (# of estimated reads) / (# of reads actually performed during the measurement interval by the real buffer cache). Example: V$DB_CACHE_ADVICE SQL> SELECT size_for_estimate, 2 buffers_for_estimate, 3 estd_physical_read_factor, 4 estd_physical_reads 5 FROM V$DB_CACHE_ADVICE 6 WHERE name = 'DEFAULT 7 AND block_size = ( 8 SELECT value FROM V$PARAMETER 9 WHERE name = 'db_block_size') 10 AND advice_status = 'ON'; 60/100

31 Example: V$DB_CACHE_ADVICE 61/100 Server Process & Buffer Cache Server LRU list SGA Checkpoint queue 3 DB buffer cache DBWn LGWR Data files 62/100

32 DBWn Process & Buffer Cache Server LRU list SGA Checkpoint queue DB buffer cache.. DBWn LGWR Data files 63/100 64/100 Tuning Goals and Techniques Goals: Servers find data in memory 90% hit ratio for OLTP Diagnostic measures Cache hit ratio V$DB_CACHE_ADVICE Tuning techniques: Reduce # of blocks required by SQL statements Increase buffer cache size Use multiple buffer pools Cache tables Bypass the buffer cache for sorting and parallel reads

33 Diagnostic Tools V$BUFFER_POOL_STATISTICS V$BUFFER_POOL DB_CACHE_SIZE DB_KEEP_CACHE_SIZE DB_RECYCLE_CACHE_SIZE V$SYSSTAT SGA LRU list Dirty list Buffer cache Keep buffer pool Recycle buffer pool V$SESSTAT V$SYSTEM_EVENT V$SESSION_WAIT V$BH V$CACHE STATSPACK Report 65/100 66/100 Measuring the Cache Hit Ratio From V$SYSSTAT: SQL> SELECT 1 - (phy.value lob.value dir.value) / ses.value "CACHE HIT RATIO" 2 FROM v$sysstat ses, v$sysstat lob, 3 v$sysstat dir, v$sysstat phy 3 WHERE ses.name = 'session logical reads' 4 AND dir.name = physical reads direct' 5 AND lob.name = 'physical reads direct (lob)' 6 AND phy.name = 'physical reads'; From the STATSPACK report: Statistic Total Per Per Second Trans physical reads 7, physical reads direct 7, Physical reads direct(lob) session logical reads 59, ,278.2

34 Guidelines for Using the Cache Hit Ratio Hit ratio is affected by data access methods: Full table scans Data or application design Large table with random access Uneven distribution of cache hits 67/100 Guidelines to Increase the Cache Size Increase the cache size when: Cache hit ratio is less than 0.9 on OLTP system. There is no undue page faulting. If the previous increase was effective. 68/100

35 Buffer Cache Overview of buffer cache Dynamic SGA allocation DB_CACHE_ADVICE Multiple buffer pools Free list contention 69/100 Using Multiple Buffer Pools LRU lists SGA DB buffer caches RECYCLE pool KEEP pool DEFAULT pool 70/100

36 Defining Multiple Buffer Pools In Oracle9i, Individual pools have their own size defined by DB_CACHE_SIZE, DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE. These parameters are dynamic. Latches are automatically allocated by Oracle RDBMS. In Oracle8i, Pool blocks : DB_BLOCK_BUFFERS. Latches are taken from DB_BLOCK_LRU_LATCHES. At least 50 blocks per latch. 71/100 Enabling Multiple Buffer Pools CREATE INDEX cust_idx STORAGE (BUFFER_POOL KEEP); ALTER TABLE customer STORAGE (BUFFER_POOL RECYCLE); ALTER INDEX cust_name_idx STORAGE (BUFFER_POOL KEEP); 72/100

37 KEEP Buffer Pool Guidelines Goal: Keeping blocks in memory Size: Holds all or nearly all blocks Sizing tool: ANALYZE... ESTIMATE STATISTICS SQL> ANALYZE TABLE hr.countries ESTIMATE STATISTICS; SQL> SELECT table_name, blocks 2 FROM dba_tables 3 WHERE owner = 'HR' 4 AND table_name = 'COUNTRIES'; TABLE_NAME BLOCKS COUNTRIES 14 73/100 74/100 RECYCLE Buffer Pool Guidelines Goal: Eliminating blocks from memory when transactions are completed Size: Holds only active blocks (1/4?) Tool: V$CACHE (catclust.sql) SQL> SELECT owner#, name, count(*) blocks 2 FROM v$cache 3 GROUP BY owner#, name; OWNER# NAME BLOCKS CUSTOMER REGIONS Sum(blocks targeted for RECYCLE) / 4

38 RECYCLE Buffer Pool Guidelines Tool: V$SESS_IO : I/O stat by session SQL> SELECT s.username, 2 io.block_gets, 3 io.consistent_gets, 4 io.physical_reads 5 FROM v$sess_io io, v$session s 6 WHERE io.sid = s.sid ; USERNAME BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS HR /100 Calculating the Hit Ratio for Multiple Pools SQL> SELECT name, 1 - (physical_reads / (db_block_gets + consistent_gets)) "HIT_RATIO" 2 FROM sys.v$buffer_pool_statistics 3 WHERE db_block_gets + consistent_gets > 0; NAME HIT_RATIO KEEP RECYCLE DEFAULT /100

39 Identifying Candidate Pool Segments KEEP Pool Blocks are accessed repeatedly. Segment size is less than 10% of the DEFAULT buffer pool size. RECYCLE Pool Blocks are not reused outside of transaction. Segment size is more than twice the DEFAULT buffer pool size. 77/100 Views with Buffer Pools SQL> SELECT id, name, block_size, buffers 2 FROM v$buffer_pool; ID NAME BLOCK_SIZE BUFFER KEEP RECYCLE DEFAULT /100

40 Caching Tables LRU end Enable caching during full table scans by: Creating the table with the CACHE clause Altering the table with the CACHE clause CACHE hint in a query Guideline: Do not overcrowd the cache. Use a KEEP pool 79/100 Other Cache Performance Indicators From V$SYSSTAT free buffer inspected : # of buffers skipped on LRU list high or increasing SQL> SELECT name, value 2 FROM v$sysstat 3 WHERE name = 'free buffer inspected'; NAME VALUE free buffer inspected /100

41 Other Cache Performance Indicators From V$SYSTEM_EVENT free buffer waits : buffer busy waits : SQL> SELECT event, total_waits 2 FROM v$system_event 3 WHERE event in 4 ('free buffer waits', 'buffer busy waits'); EVENT TOTAL_WAITS free buffer waits 337 buffer busy waits /100 Buffer Cache Overview of buffer cache Dynamic SGA allocation DB_CACHE_ADVICE Multiple buffer pools Free list contention 82/100

42 Free Lists A free list for an object maintains a list of blocks that are available for inserts. The number of free lists for an object can be set dynamically. Single-CPU systems do not benefit greatly from multiple free lists. The tuning goal is to ensure that an object has sufficient free lists to minimize contention. Using Automatic Free Space Management eliminates the need for free lists, thus reducing contention on the database. 83/100 Diagnosing Free List Contention V$WAITSTAT columns: SGA Data buffer cache CLASS segment header COUNT TIME V$SYSTEM_EVENT columns: EVENT buffer busy waits TOTAL_WAITS FREELISTS 84/100

43 Diagnosing Free List Contention V$SESSION_WAIT columns: EVENT buffer busy waits P1 FILE P2 BLOCK P3 ID Server process Server process SGA Data buffer cache DBA_SEGMENTS columns: SEGMENT_NAME SEGMENT_TYPE FREELISTS HEADER_FILE HEADER_BLOCK 85/100 Object ID FREELISTS Resolving Free List Contention 86/100 Query V$SESSION_WAIT. Identify the object and get free lists for the segment from DBA_SEGMENTS. SQL> SELECT s.segment_name, s.segment_type, s.freelists, 2 w.wait_time, w.seconds_in_wait, w.state 3 FROM dba_segments s, v$session_wait w 4 WHERE w.event = buffer busy wait 5 AND w.p1 = s.header_file 6 AND w.p2 = s.header_block; Either: ALTER TABLE... FREELISTS, or Move into an auto-managed tablespace.

44 Auto-management of Free Space Create an auto-managed tablespace: SQL> CREATE TABLESPACE BIT_SEG_TS 2 DATAFILE '$HOME/ORADATA/u04/bit_seg01.dbf' SIZE 1M 3 EXTENT MANAGEMENT LOCAL 4 SEGMENT SPACE MANAGEMENT AUTO; Create a table that uses auto-management of free space: SQL> CREATE TABLE BIT_SEG_TABLE 2 (IDNUM NUMBER) 3 TABLESPACE BIT_SEG_TS; 87/100 Shared Pool Buffer Cache Other SGA Structures 88/100

45 Other SGA Structures Redo log buffer Java pool Java session memory used by a session Configure the instance to use I/O slaves Configure and use multiple DBW processors 89/100 Redo Log Buffer SQL> UPDATE emp 2 SET salary=salary*1.1 3 WHERE empno=574; Redo log buffer Database buffer cache Shared pool Library cache Data dictionary cache Server process User global area LGWR Control files ARCn Data files Redo log files Archived log files 90/100

46 Sizing the Redo Log Buffer LOG_BUFFER parameter Default value: MAX ( 512K, 128K * COUNT(CPU) ) 91/100 Diagnosing Redo Log Buffer Inefficiency SQL> UPDATE emp 2 SET salary=salary*1.1 3 WHERE empno=736; Server process Server process SQL> DELETE FROM emp 2 WHERE empno=7400; LGRW ARCH Redo log files Archived log files 92/100

47 Analyze Redo Log Buffer Efficiency V$SESSION_WAIT Log Buffer Space event Redo log buffer V$SYSSTAT Redo Buffer Allocation Retries statistic 93/100 Redo Log Buffer Tuning Guidelines There should be no Log Buffer Space waits. SQL> SELECT sid, event, seconds_in_wait, state 2 FROM v$session_wait 3 WHERE event = log buffer space ; SID EVENT SECONDS_IN_WAIT STATE log buffer space 112 WAITING 94/100

48 Redo Log Buffer Tuning Guidelines Redo Buffer Allocation Retries value should be near 0, and should be less than 1% of redo entries. SQL> SELECT r.value "Retries", e.value "Entries", 2 r.value/e.value * 100 "Percentage" 3 FROM v$sysstat r, v$sysstat e 4 WHERE r.name = 'redo buffer allocation retries' 5 AND e.name = 'redo entries'; Retries Entries Percentage /100 Reducing Redo Operations Direct Path loading w/o archiving Direct Path loading w/ archiving can use Nologging mode. Direct Load Insert can use Nologging mode. Some SQL statements can use Nologging mode. CREATE TABLE... AS SELECT CREATE INDEX ALTER INDEX... REBUILD 96/100

49 Monitoring Java Pool Memory SQL> SELECT * FROM v$sgastat 2 WHERE pool = 'java pool'; POOL NAME BYTES java pool free memory java pool memory in use Limiting Java session memory JAVA_SOFT_SESSIONSPACE_LIMIT warning JAVA_MAX_SESSIONSPACE_SIZE ORA-29554: unhandled Java out of memory... session killed 97/100 Sizing the SGA For Java SHARED_POOL_SIZE: 8 KB per loaded class 50 MB for loading large JAR files JAVA_POOL_SIZE 20 MB default 50 MB for medium-sized Java application 98/100

50 Multiple I/O Slaves Provide nonblocking asynchronous I/O requests Deployed by the DBW0 process Typically not recommended if asynchronous I/O is available Follow the naming convention ora_innn_sid Turn asynchronous I/O on or off with: DISK_ASYNCH_IO : TRUE/FALSE TAPE_ASYNCH_IO : TRUE/FALSE 99/100 Multiple DBWR Processes Multiple DBWn processes can be deployed with DB_WRITER_PROCESSES (DBW0 to DBW9). Useful for SMP systems with large numbers of CPUs. Multiple processes cannot concurrently be used with multiple I/O slaves. 100/100

51 Tuning DBWn I/O Tune the DB Writer processes by looking at the value of the FREE BUFFER WAITS event SQL> SELECT total_waits 2 FROM v$system_event 3 WHERE event = free buffer waits ; Consider increasing DBWn if high 101/100

62

2 instance database physical storage 2 1 62 63 tablespace datafiles 2 2 64 1 2 logical view control files datafiles redo log files 65 2 3 9i OMF Oracle Managed Files, OMF 9i 9i / / OMF 9i 66 8 1MB 8 10MB