# Presentation: 818326

## Oracle9i Tuning; Now YOU are the Expert

- By Rich Niemiec, TUSC (www.tusc.com)
- (Thanks: Kevin, Sanjay, Dave,  Jon, Randy, Bob, Mike & Pete)

***NASA - July 2003***** **

## Audience Knowledge

- Oracle8.0 Experience ?
- Oracle8i Experience ?
- Oracle9i Experience ?
- Goals
- Target Key Areas of Tuning
- Target tips that are most useful
- Non-Goals
- Learn ALL aspects of Tuning

## Overview

- Statspack
- Oracle Recommendations
- Initialization File
- Helpful V$ & X$ Queries
- Summary

## Tuning in General

- Both an Art and a Science
- Exceptions often rule the day...Not a “one size fits all”
- Hardware & Architecture must be right for your application or difficult to succeed.
- Statspack & Enterprise Manager are best for simple tuning and ongoing maintenance.
- V$/X$ are best for drilling deep into problems
- 3rd Party tools can help with the 2 above points.

## Statspack

## Statspack – Check Regularly

**1.      Top 5 wait events**
**2.      Load Profile**
**3.      Instance Efficiency Hit Ratios**
**4.      Wait Events**
**5.      Latch Waits **
**6.      Top SQL**
- 7.      Instance Activity
- 8.      File I/O
- 9.      Memory Allocation
- 10.    Undo

## Statspack – Miscellaneous notes(See Don Burleson’s book) 

- SQL> @spcreate (system/manager as SYSDBA)
- SQL> alter user PERFSTAT account lock; (also unlock)
- SQL> EXECUTE STATSPACK.MODIFY_STATSPACK_PARAMETER – (i_snap_level=>5, i_buffer_gets_th=>100000, - i_modify_parameter=>'true');
- SQL> execute STATSPACK.SNAP; (do this for start/end)
-     PL/SQL procedure successfully completed.
- SQL> @ORACLE_HOME/rdbms/admin/spreport (also sprepsql)

## Statspack – Header Information

**DB Name         DB Id     Instance     Inst Num Release   Cluster Host**
- ------------    -----------       ------------   --------      -----------  -------    ------------
- ORA9I         2249715753 ora9i                  1       9.0.1.1.1   NO       RJNMOBILE
-                            Snap Id     Snap Time            Sessions
-  		              -------      ------------------        --------
-  Begin Snap:        280          12-Jul-02 23:00:05       10
-     End Snap:        281          13-Jul-02 06:00:01       10
-     Elapsed:           419.93 (mins)
**Cache Sizes (end)**~~~~~~~~~~~~~~~~~     Buffer Cache:       160M      Std Block Size:         8K Shared Pool Size:        64M          Log Buffer:       512K

## Statspack – Header Information

- Ensure that you’re running for the right instance.
- Check the start/end times
- Check the cache sizes
- Could have been changed during the run
- Last page will tell you more on starting/ending values of initialization parameters

## Statspack – Load Profile

**Load Profile****~~~~~~~~~~~~                         Per Second       Per Transaction**
**                                   ****---------------       ---------------**
**                  ****Redo size:             97,586.68              3,134.84**
**              ****Logical reads:             11,667.83                374.81**
**              ****Block changes:                565.40                 18.16**
**             ****Physical reads:                552.02                 17.73**
**            ****Physical writes:                 59.03                  1.90**
**                 ****User calls:                948.91                 30.48**
**                     ****Parses:                 56.54                  1.82**
**                ****Hard parses:                  1.56                  0.05**
**                      ****Sorts:                 38.11                  1.22**
**                     ****Logons:                  0.01                  0.00**
**                   ****Executes:                691.24                 22.21**
**               ****Transactions:                 31.13**
**  ****% Blocks changed per Read:    4.85    Recursive Call %:   13.13**
** ****Rollback per transaction %:    2.28       Rows per Sort:   11.09**

## Statspack - Load ProfileThings to look for... 

- An increase in “redo size,” “block changes” and “% Blocks changed per Read:,” indicates increased DML (insert/update/delete) activity.
- _A Hard Parse_ occurs when a SQL statement is executed and *is* *not* currently in the shared pool. If > 100/second,  CURSOR_SHARING initialization parameter should be potentially used or there may be a Shared Pool sizing problem.
- _A Soft Parse_ occurs when a SQL statement is executed and it *is* currently in the shared pool. If > 300/second,  could indicate program inefficiencies where statements are being parsed over and over again instead of reuse.

## Statspack – Instance Efficiency

**Instance Efficiency Percentages (Target 100%)**
**~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~**
**            ****Buffer Nowait %:   99.99       Redo NoWait %:  100.00**
**            ****Buffer  Hit   %:   95.27    In-memory Sort %:  100.00**
**            ****Library Hit   %:   99.67        Soft Parse %:   97.24**
**         ****Execute to Parse %:   91.82         Latch Hit %:   99.60**
**Parse CPU to Parse Elapsd %:   73.48     % Non-Parse CPU:   99.82**

## Statspack - Instance Efficiency Things to look for... 

- _Buffer NoWait %_ of less than 99 percent. This is ratio of hits on a request for a specific buffer where the buffer was immediately available in memory. If the ratio is low, then could be a (hot) block(s) being contended for that should be found in the Buffer Wait Section.

## Statspack - Instance Efficiency Things to look for... 

- _Buffer Hit %_ of less than 95 percent. This is the ratio of hits on a request for a specific buffer and the buffer was in memory instead of needing to do a physical I/O.
- When this varies greatly one day to the next, further investigation should be done as to the cause.
- If you have unselective indexes that are frequently accessed, it will drive your hit ratio higher, which can be misleading indication of good performance.
- When you effectively tune your SQL and have effective indexes on your entire system, this issue is not encountered as frequently and the hit ratio is a better performance indicator.

## Statspack - Instance Efficiency Things to look for... 

- _Library Hit %_ of less than 95 percent. A lower library hit ratio usually indicates that SQL is being pushed out of the shared pool early (could be due to a shared pool that is too small).
- A lower ratio could also indicate that bind variables are not used or some other issue is causing SQL not to be reused (in which case a smaller shared pool may only be a band-aid that will potentially fix a library latch problem which may result).
- You must fix the problem (use bind variables or CURSOR_SHARING) and then appropriately size the shared pool. I’ll discuss this further when we get to latch issues.

## Statspack - Instance Efficiency Things to look for... 

- _In-Memory Sort %_ of less than 95 percent in OLTP. In an OLTP system, you really don’t want to do disk sorts. Setting the PGA_AGGREGATE_TARGET (or SORT_AREA_SIZE) initialization parameter effectively will eliminate this problem.
- _Soft Parse %_ of less than 95 percent. As covered in the Load Profile section (last section), a soft parse ratio that is below around 80 percent indicates that SQL is not being reused and needs to be investigated.
- _Latch Hit %_ of less than 99 percent is usually a big problem. Finding the specific latch will lead you to solving this issue. More in the Latch Wait section.

## Statspack – Shared Pool Stats Things to look for... 

**Shared Pool Statistics          Begin     End****                              ------  ------****             Memory Usage %:   28.37   29.17****    % SQL with executions>1:   27.77   30.45****  % Memory for SQL w/exec>1:   56.64   67.74**
- Shows the percentage of the shared pool in use and the percentage of SQL statements that have been executed multiple times.  In this case, 30% have multiple executions.
- Combining this data with the Library, Parse and Latch data will help you to size the Shared Pool.

## Statspack – Top Wait Events

**Top 5 Wait Events**
**~~~~~~~~~~~~~~~~~                             Wait     % Total**
**Event                               Waits  Time (cs)   Wt Time**
**---------------------------- ------------ ------------ -------**
**db file sequential read        18,977,104   22,379,571   82.29**
**latch free                      4,016,773    2,598,496    9.55**
**log file sync                   1,057,224      733,490    2.70**
**log file parallel write         1,054,006      503,695    1.85**
**db file parallel write          1,221,755      404,230    1.49**

## Statspack - Top Wait EventsThings to look for... 

- _Wait Problem_ 	_Potential Fix_
- Sequential Read 	Indicates many index reads – tune the 				code (especially joins)
- Scattered Read 	Indicates many full table scans – tune 				the code; cache small tables
- Free Buffer 		Increase the DB_CACHE_SIZE; 				shorten the checkpoint; tune the code
- Buffer Busy 		Segment Header – Add freelists or 				freelist groups (Use ASSM)
- Buffer Busy 		Data Block – Separate ‘hot’ data; use 				reverse key indexes; smaller blocks

## Statspack - Top Wait EventsThings to look for... 

- _Wait Problem_ 	_Potential Fix_
- Buffer Busy 		Data Block (cont.)– Increase initrans 				and/or maxtrans
- Buffer Busy 		Undo Header – Add rollback segments 				or areas
- Buffer Busy 		Undo block – Commit more; Larger 				rollback segments or areas
- Latch Free		Investigate the detail (Covered later)
- Enqueue - ST	Use LMT’s or pre-allocate large extents
- Enqueue - HW	Pre-allocate extents above high water 				mark

## Statspack - Top Wait EventsThings to look for... 

- _Wait Problem_ 	_Potential Fix_
- Enqueue – TX	Increase initrans and/or maxtrans on 				the table or index
- Enqueue - TM	Index foreign keys; Check application 				locking of tables
- Log Buffer Space	Increase the Log Buffer; Faster disks 				for the Redo Logs
- Log File Switch	Archive destination slow or full; Add 				more or larger Redo Logs
- Log file sync		Commit more records at a time; Faster 				Redo Log disks; Raw devices

## Statspack - Top Wait EventsThings to look for... 

- _Wait Problem_ 		_Potential Fix_
- Write complete waits	Add database writers; 						Checkpoint more often; 						buffer cache too small
- Idle Event			Ignore it – A few listed below:
- dispatcher timer (shared server idle event)
- lock manager wait for remote message (RAC idle event)
- pmon timer (Background process idle event)
- PX Deq: need buffer (Parallel query idle event)
- PX Deq: execute msg (Parallel query idle event)
- SQL*Net message from client (User process idle event)

## Statspack – Top 25

- Tuning the top 25 buffer get and top 25 physical get queries has yielded system performance gains of anywhere from 5 percent to 5000 percent.
- The SQL section of the statspack report tells you which queries to potentially tune first.
- The top 10 percent of your SQL statements should not be more than 10 percent of your buffer gets or disk reads.

## Statspack – Top SQL

-  **Buffer Gets    Executions  Gets per Exec  % Total  Hash Value**
**--------------- ------------ -------------- ------- ------------**
**    ****166,697,520        6,514       25,590.7     2.5   1577170159**
** **
**SELECT DISTINCT USERNAME   FROM USER_TEST A, SALES B,CUSTOMER C  **
**WHERE A.USERNAME_TYPE = :b1  AND A.ITEMNO = :b2 C.NAME = A.NAME  AND B.TYPE = C.TYPE  AND NVL(C.STARTDATE,'31-DEC-2002') > :b3 ORDER BY NAME**
**    ****101,306,038        6,510       15,561.6     1.5    613844091**
**SELECT A.ITEMNO, B.LINEITEM, C.LINEITEM_AMT FROM LINES A, LINE_ITEMS B, LINE_ORDERS C WHERE A.LINENO = B.LINENO AND A.LINENO = C.LINENO AND A.ORDERDATE = ’31-DEC-2002’ AND B.TYPE = ‘CURRENT’ ORDER BY CUSTOMERNO**

## Statspack - Latch Waits

- _Latch Free_ – Latches are low-level queueing mechanisms (they’re accurately referred to as mutually exclusion mechanisms) used to protect shared memory structures in the System Global Area (SGA).
- Latches are like locks on memory that are very quickly obtained and released.
- Latches are used to prevent concurrent access to a shared memory structure.
- If the latch is not available, a latch free miss is recorded.

## Statspack - Latch Waits

- _Latch Free_ –
- Most latch problems are related to:
- The failure to use bind variables (library cache latch)
- Redo generation issues (redo allocation latch)
- Buffer cache contention issues (cache buffers lru chain)
- Hot blocks in the buffer cache (cache buffers chains).
- There are also latch waits related to bugs; check MetaLink for bug reports if you suspect this is the case (oracle.com/support).
- When latch miss ratios are greater than 0.5 percent, you should investigate the issue.

## Statspack - Latch Waits

**Latch Activity for DB: ORA9I  Instance: ora9i  Snaps: 1 -2**
**Pct    Avg   Wait                 Pct**
**                              ****Get          Get   Slps   Time       NoWait NoWait**
**Latch                       Requests      Miss  /Miss    (s)     Requests   Miss**
**------------------------ -------------- ------ ------ ------ ------------ ------**
**KCL freelist latch                4,924    0.0                          0**
**cache buffer handles            968,992    0.0    0.0                   0**
**cache buffers chains        761,708,539    0.0    0.4          21,519,841    0.0**
**cache buffers lru chain       8,111,269    0.1    0.8          19,834,466    0.1**
**library cache                67,602,665    2.2    2.0             213,590    0.8**
**redo allocation              12,446,986    0.2    0.0                   0**
**redo copy                           320    0.0                 10,335,430    0.1**
**user lock                         1,973    0.3    1.2                   0**

## Statspack - Latch Waits

**Latch Sleep breakdown for DB: **
**-> ordered by misses desc**
**                               ****Get                                  Spin &**
**Latch Name                    Requests         Misses      Sleeps Sleeps 1->4**
**-------------------------- -------------- ----------- ----------- ------------**
**library cache                  67,602,665   1,474,032   2,935,368 199143/28003**
**                                                                  ****6/582413/412**
**                                                                  ****440/0**
**cache buffers chains          761,708,539     192,942      83,559 110054/82239**
**                                                                  ****/628/21/0**
**redo allocation                12,446,986      25,444       1,135 24310/1133/1**
**                                                                  ****/0/0**
**cache buffers lru chain         8,111,269       6,285       4,933 1378/4881/26**
**                                                                  ****/0/0**
**process allocation                    177           7           7 0/7/0/0/0**
-           -------------------------------------------------------------

## Statspack - Latch Waits

- Latches that are _willing to wait_ try to acquire a latch.  If none are available, it will spin and then request the latch again.  It will continue to do this up to the _SPIN_COUNT initialization parameter (note that spinning costs CPU).
- If it can’t get a latch after spinning up to the _SPIN_COUNT, it will go to sleep.  It will wake up after one centisecond (one hundredth of a second).  It will do this twice.
- It will then start this process again, spinning up to the _SPIN_COUNT and then sleeping for twice as long (two centiseconds).  After doing this again it will double again.  So the pattern is 1,1,2,2,4,4 etc.  It will do this until it gets the latch
- Every time the latch sleeps, it will creates a latch sleep wait. An example of a “willing to wait” latch is a library cache latch.

## Statspack - Latch Waits

- Some latches are _“not willing to wait.”_ This type of latch does not wait for the latch to become available.
- They immediately time out and retry to obtain the latch.
- A redo copy latch is an example of a “not willing to wait” latch.
- A not willing to wait latch will generate information for the immediate_gets and the immediate_misses columns of the V$LATCH view and also in the statspack report.
- The hit ratio for these latches should also approach 99% and the misses should never fall below 1 percent misses.

## Statspack - Latch Waits

_*Gets*_ - The number of times a willing to wait request for a latch was requested and it was available.
_*Misses*_ - The number of times a willing to wait request for latch was initially requested but was not available.
_*Sleeps*_ - The number of a willing to wait request for a latch failed over and over until the spin count was exceeded and the process went to sleep. The number of sleeps may be higher than the misses. Processes may sleep multiple times before obtaining the latch.
_*NoWait Misses*_ - The number of times immediate (not willing to wait) request for a latch was unsuccessful.

## Statspack - Latch Waits Things to look for... 

- _Latch Problem_ 		_Potential Fix_
- Library Cache 		Use bind variables; adjust the 					shared_pool_size
- Shared Pool			Use bind variables; adjust the 					shared_pool_size
- Redo allocation		Minimize redo generation and 					avoid unnecessary commits
- Redo copy			Increase the 								_log_simultaneous_copies
- Row cache objects		Increase the Shared Pool

## Statspack - Latch Waits Things to look for... 

- _Latch Problem_ 		_Potential Fix_
- Cache buffers chain	s	_DB_BLOCK_HASH_BUCKETS 				needs to be increased or prime
- Cache buffers lru chain	Set DB_BLOCK_LRU_LATCHES 				or use multiple buffer pools
- You should configure lru latches so that each buffer pool has ncpus worth of latches. For example if the system has 8 cpus, they should set:
- 		db_block_lru_latches=24 (, i.e. 3 * ncpus)
- 		buffer_pool_keep = buffers:XXXX, lru_latches=8
- 		buffer_pool_recycle = buffers:YYYY, lru_latches=8

## Statspack – Check Regularly

**1.      Top 5 wait events**
**2.      Load Profile**
**3.      Instance Efficiency Hit Ratios**
**4.      Wait Events**
**5.      Latch Waits **
**6.      Top SQL**
- 7.      Instance Activity – sorts/dirty buffers inspected
- 8.      File I/O – At times a problem
- 9.      Memory Allocation – Check for changes
- 10.    Undo

## Use Enterprise Manager Oracle Recommends

**Thanks to Oracle who wrote the information in this section in another presentation**

## Emergency Tuning Method

- Simple seven-step method for tuning emergency performance issues
- Focuses on CPU usage and wait events to diagnose problems
- Assumes system has already undergone proactive tuning such as architecture, memory & SGA, undo, temporary and other auto-tuning features of Oracle

## Step 1

- Establish thresholds in Performance Overview chart
- Thresholds must represent abnormal condition for various statistics, ratios, and waits
- Thresholds can be established as:
- Chart-only flags
- Events

**log file sync**

**View/Edit Threshold**

## Step 2

- Collect all relevant information about problem from user reporting the problem
- Inquire whether problem is in throughput or response time
- Identify the problem application and module
- Determine nature of application/module, e.g., is it DML intensive, or queries, etc.

## Step 3

- Check for common causes
- Changes in init.ora parameter settings
- Application code changes or upgrades resulting in possible changes in execution plans, etc.
- Cyclical workload variations, e.g., end of quarter book closing

## Step 4

- Examine CPU usage
- If system CPU constrained
- Identify session and SQL consuming most CPU
- Tune SQL

## Title is Arial 32-40pt.

- Text is Arial 28 pt., first bullet is red
- Second level is 24 pt.
- Third level
- Fourth level
- Fifth level

**Tune SQL Statement**

- Virtual Index Wizard...       Ctrl+Shift+I
- Hint Wizard...                    Ctrl+Shift+H
- SQL Tuning Wizard...       Ctrl+Shift+T

## Step 5

- If CPU is not the problem then investigate session waits
- Identify the event causing most waits
- Remedy the wait

## Wait Event: Enqueue

**Blocking/Waiting Locking Chart**

**enqueue**

## Enqueue

- ST: Space Transaction Enqueue
- Use locally managed tablespaces
- Increase size of next extent
- HW: High Watermark Enqueue
- Manually allocate extents
- TM: Transaction Management Enqueue
- Create index on foreign key
- TX: Transaction Enqueue
- More frequent commits
- Increase INITTRANS, MAXTRANS

## Text is Arial 28 pt., first bullet is red
Second level is 24 pt.
Third level
Fourth level
Fifth level

- Second level is 24 pt.
- Third level
- Fourth level
- Fifth level

**Wait Event: Buffer Busy Waits**

- Top Objects by Buffer Busy Waits

- buffer busy waits

## Buffer Busy Waits

- Undo header or Undo block
- Use Automatic Undo Management
- Create more, larger rollback segments
- Segment header or data block
- Automatic Segment Space Management
- Increase FREELISTS

## Wait Event: DB File Scattered Read

**Wait Event: DB File Scattered Read**

## SQL Statements Waiting for this Event

## Slide 59

## DB File Scattered/Sequential Read

- Reduce I/O Workload
- Tune SQL statement using SQL Analyze
- Manage workload better
- Enhance I/O bandwidth
- Eliminate I/O hot spots — use SAME
- Add disks per SAME architecture

## Wait Event: Latch Free

**Wait Event: Latch Free**

**Latch Analysis**

## Slide 62

## Step 6 & 7

- Apply remedy for the problem(s) diagnosed.  For multiple remedies, validate each remedy independently, if possible. Repeat process if system still not functioning properly.
- Establish new thresholds if changed.  Take measures to prevent similar emergency from occurring again
- Modify change process
- Use stored outlines
- Review administrative tasks

## Method Summary

- Establish thresholds in Performance Overview chart
- Collect relevant information about problem
- Check for common causes, e.g., init.ora settings, code changes, etc.
- Investigate CPU usage
- Examine Sessions by CPU chart
- Drilldown to session consuming most CPU
- Tune SQL statement being run by the session
- Investigate wait events and take appropriate action
- Apply and validate remedy
- Update thresholds and take measure to avoid similar problem in future

## Method Summary

- Well-known list of common performance bottlenecks
- Oracle recommends tried and true ways for proactive tuning
- Database physical layout
- Undo management
- Tablespace and Segment space management
- Follow Oracle’s Performance Management Method for reactive tuning
- Trend: database is becoming increasingly self-managing

## Memory &Top Init.ora Parameters

## Init.ora Parameters Documented & Total

## The Init.ora over the yearsDocumented & Undocumented

- _Version_		_Doc._      	_Undoc._	_Total __                                                                _
- 	6 			111    	19		130
- 	7 			117      	68		185
- 	8.0			193		119		312
- 	8.1			203		301		504
- 	9.0 		251		436		687
-    9.2	 		257		540		797

## System Global Area - SGA

## 64-Bit advancement ofDirectly addressable memory

- 				_Address Direct_	         _Indirect/Extended_
- _4 Bit:_		16 				(640)
- _8 Bit:_		256 				(65,536)
- _16 Bit:_		65,536 			(1,048,576)
- _32 Bit:_		4,294,967,296
- _64 Bit:_		18,446,744,073,709,551,616
- When the hardware physically implements the theoretical possibilities of 64-Bit, things will dramatically change.... ...moving from 32 bit to 64 bit will be like moving from 4 bit to 32 bit or like moving from 1971 to 2000 overnight.

## System Global Area - SGA

## Use Bind Variables / Latch Issues

- If you don’t use bind variables in your application code you usually end up with latch contention with the *shared_pool* and *library cache* latches.  Oracle9i promises a lock for every block.
- This latch wait time can been reduced by changing just the top couple of executed statements that were using literal SQL instead of bind variables.
- Oracle 8i Release 2 (8.1.6) has an auto-conversion of literals into bind variables... **Oracle9i extends this slightly...**
- CURSOR_SHARING=FORCE  (Default is EXACT; 8iR2)

## Cursor Sharing - 8.1.6 / 9i

- _If v$sqlarea looks like this:_
- select empno from rich778 where empno =451572
- select empno from rich778 where empno =451573
- select empno from rich778 where empno =451574
- select empno from rich778 where empno =451575
- select empno from rich778 where empno =451576
- _Use cursor_sharing=force (sqlarea goes to this):_
- select empno from rich778 where empno =:SYS_B_0

## Oracle 9i -  Cursor_sharing

- _Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production_
- (SIMILAR takes into account the statistics as well...)
**SQL> alter session set cursor_sharing=SIMILAR;**
- System altered.
- _Oracle8i Enterprise Edition Release 8.1.7.0.0 – Production_
- SQL> alter session set cursor_sharing=SIMILAR;
- ERROR: ORA-00096: invalid value SIMILAR for parameter cursor_sharing, must be from among EXACT, FORCE

## Important Init.ora’s

- Setting db_file_multiblock_read_count high helps read more in one I/O... but, too high can cause Oracle’s optimizer to do MORE full table scans.
- _Fix/Override as of 8.05+ (force index use!):_
**OPTIMIZER_INDEX_COST_ADJ =  10 (1-14)**
- If an index-based operation, Oracle will adjust the cost by a factor of OPTIMIZER_INDEX_COST_ADJ/100 before choosing the lowest cost option.

## Important Oracle9i – FYI ONLY! - Top 20

- 1. **DB_CACHE_SIZE** –Initial memory allocated to data cache
- 2. **PGA_AGGREGATE_TARGET** – Soft memory cap for total of all user’s PGA
- 3. **SHARED_POOL_SIZE** – Memory allocated for data dictionary and SQL & PL/SQL
- 4. **SGA_MAX_SIZE** – Maximum memory that the SGA can dynamically grow to
- 5. **OPTIMIZER_MODE** – CHOOSE, RULE, FIRST_ROWS, FIRST_ROWS_*n* or ALL_ROWS
- 6. **CURSOR_SHARING** – Converts literal SQL to SQL with bind variables reducing parse overhead.

## Important Oracle9i – FYI ONLY! - Top 20

- 7. **OPTIMIZER_INDEX_COST_ADJ** – Coarse adjustment between the cost of an index scan and the cost of a full table scan.  Set between 1 and 10 to force index use more frequently.
- 8. **QUERY_REWRITE_ENABLED** – Used to enable Materialized View and Function-Based-Index capabilities
- 9. **DB_FILE_MULTIBLOCK_READ_COUNT** – For full table scans to perform I/O more efficiently
- 10. **DB_BLOCK_SIZE** – Default block size for the database
- 11. **DB_KEEP_CACHE_SIZE** – Memory allocated to keep pool
- 12. **DB_RECYCLE_CACHE_SIZE** – Memory allocated to recycle pool

## Important Oracle9i – FYI ONLY! - Top 20

- 13. **DBWR_IO_SLAVES** (also **DB_WRITER_PROCESSES** if you have async I/O) – Number of writers from SGA to disk for simulated async I/O
- 14. **LARGE_POOL_SIZE** – Total blocks in the large pool allocation for large PL/SQL
- 15. **STATISTICS_LEVEL (9.2)** – Used to enable advisory information and optionally keep additional O/S statistics to refine optimizer decisions
- 16. **HASH_JOIN_ENABLED** – Enables hash joining for fast table joins
- 17. **JAVA_POOL_SIZE** – Memory allocated to the JVM for JAVA stored procedures

## Important Oracle9i – FYI ONLY! - Top 20

- 18. **JAVA_MAX_SESSIONSPACE_SIZE** – Upper limit on memory that is used to keep track of user session state of JAVA classes
- 19. **MAX_SHARED_SERVERS** – Upper limit on shared servers when using shared servers
- 20. **PARALLEL_AUTOMATIC_TUNING** – Enable parallel query automatic tuning capabilities. It also helps to configure the default values for many other parameters
*(Note: The PARALLEL_AUTOMATIC_TUNING parameter is also used by the database to automatically calculate the settings for PARALLEL_ADAPTIVE_MULTI_USER, PARALLEL_MAX_SERVERS, PROCESSES, SESSIONS, and TRANSACTIONS)*

## Enterprise Manager Memory Tuning: Buffer Cache Advisory

- Internal simulation to predict cache “miss” rates for different buffer cache sizes
- Simulation externalized through
- data dictionary view V$DB_CACHE_ADVICE
- a graphical view in Enterprise Manager (EM)
- Also SHARED_POOL & PGA_AGGREGATE_TARGET

## Slide 81

## Slide 82

## Slide 83

## Locally Managed Tablespaces- LMT

- Manage space locally using bitmaps
- Benefits
- no tablespace fragmentation issues
- better performance handling on large segments
- lower contention for central resources, e.g., no ST enqueue contention
- fewer recursive calls
- Implementation
- specify “EXTENT MANAGEMENT LOCAL” clause during tablespace creation
- in-place migration

## Automatic Segment Space Management (ASSM)

- Automatic intra-object space management
- Benefits –
- Eliminates Rollback Segment Management
- simplified administration (no more FREELISTS, FREELIST GROUPS, PCTUSED)
- improved space utilization & concurrency
- enhanced performance
- Can set undo retention time
- If set to longest running query time, no more “Snapshot too old”!
- Implementation
- specify “SEGMENT SPACE MANAGEMENT AUTO” clause during tablespace creation

## Helpful V$/X$ Queries

## V$ Views over the years

- _Version_		_V$ Views_      	_X$ Tables__                                                                  _
- 	6 			23                 	? (35)
- 	7 			72          		126
- 	8.0			132			200
- 	8.1			185			271
- 	9.0			227			352
-    9.2			259			394

## Listing of V$ Views

-     *select   **	**name **from     **	**v$fixed_table**where    **	**name like 'GV%'**order by **	**name;*
*NAME*
*---------------------------------**GV$ACCESS**GV$ACTIVE_INSTANCES**GV$ACTIVE_SESS_POOL_MTH**GV$AQ1**GV$ARCHIVE...*

## Need GV$ - Instance ID

*select    inst_id, (1 - (sum(decode(name, 'physical reads',value,0)) /*
*          **(sum(decode(name, 'db block gets',value,0)) +*
*          **sum(decode(name, 'consistent gets',value,0))))) * 100 “Hit Ratio"*
*from      v$sysstat;*
* *
*INST_ID       Hit Ratio*
*--------------  ------------------*
*             **1  90.5817699*

## Need GV$ - Instance ID

*select    inst_id, (1 - (sum(decode(name, 'physical reads',value,0)) /*
*          **(sum(decode(name, 'db block gets',value,0)) +*
*          **sum(decode(name, 'consistent gets',value,0))))) * 100 “Hit Ratio"*
*from      gv$sysstat*
*group by inst_id;*
* *
*INST_ID       Hit Ratio*
*--------------  ------------------*
*             **1  90.5817699*
*            **2   96.2034537*

## X$ used to create V$

*select   * *
*from     v$fixed_view_definition*
*where    view_name = 'GV$INDEXED_FIXED_COLUMN’;*
*** ***
_*VIEW_NAME                *__*	*_*	*_*VIEW_DEFINITION*_
*gv$indexed_fixed_column   **	**	**select   c.inst_id,   kqftanam,   **	**	**	**	**	**kqfcoidx, kqfconam,kqfcoipo**                         **	**	**	**from     X$kqfco c,   X$kqftat**                         **	**	**	**where    t.indx = c.kqfcotab**                         **	**	**	**and      kqfcoidx != 0** *

## Listing of X$ Tables

*    **select     **	**name**from       **	**v$fixed_table**where      **	**name like 'X%'**order by   **	**name;*
*NAME*
*---------------------------------**X$ACTIVECKPT**X$BH**X$BUFQM**X$CKPTBUF**X$CLASS_STAT...*

## Listing of X$ Indexes

*select    **	**table_name, index_number, column_name *
*from      **	**gv$indexed_fixed_column*
*order by  **	**table_name, index_number, column_name, **	**	**	**column_position;*
*** ***
*TABLE_NAME     INDEX_NUMBER COLUMN_NAME*
*------------------------------ ------------------------------  ------------------------------**X$CLASS_STAT                            1   ADDR**X$CLASS_STAT                            2   INDX**X$DUAL                                        1   ADDR**X$DUAL                                       2    INDX ... *

## V$ - System Information

*SQL> select * from v$version;*
*BANNER*
*-------------------------------------------------------------------------------**Oracle9i Enterprise Edition Release 9.2.0.1.0 – Production**PL/SQL Release 9.2.0.1.0 – Production**CORE    9.2.0.1.0       Production**TNS for 32-bit Windows: Version 9.2.0.1.0 – Production**NLSRTL Version 9.2.0.1.0 – Production** *

## V$ - System Information

*select * *
*from   v$option;*
*PARAMETER                            VALUE*
*-----------------------------------    --------**Advanced replication             TRUE**Application Role                    TRUE**Bit-mapped indexes               TRUE**Block Media Recovery           TRUE**Change Data Capture           TRUE**...*

## V$ - Top 10 as % of All

*select sum(pct_bufgets) percent *
*from (select rank() over ( order by buffer_gets desc )    **	**as rank_bufgets,*
*   **to_char(100 * ratio_to_report(buffer_gets) over **	**(), '999.99') pct_bufgets*
*from   v$sqlarea )*
*where  rank_bufgets < 11;*
*PERCENT*
*------------*
*     **97.07*

##  New Index Features

- Index Usage
- Oracle Introduced the Ability to Monitor the Usage of Indexes
- The Method Deployed will Only Signify if the Index was Used, Not How Often
- Indexes that are Desired to Be Monitored Need to be Individually Turned ON and OFF for Monitoring with the ALTER INDEX Command
- Index Usage can be Viewed in the V$OBJECT_USAGE view

##  New Index Features

- Index Usage
- Checking the Object Usage View

- DESCR v$object_usage
- Name                   Null?    Type
- ---------------------- -------- ----------------------------
- INDEX_NAME             NOT NULL VARCHAR2(30)
- TABLE_NAME             NOT NULL VARCHAR2(30)
- MONITORING                      VARCHAR2(3)
- USED                            VARCHAR2(3)
- START_MONITORING                VARCHAR2(19)
- END_MONITORING                  VARCHAR2(19)

- SELECT * FROM v$object_usage;
- no rows selected

##   New Index Features

- Index Usage
- Start Index Monitoring

- ALTER INDEX HRDT_INDEX1 MONITORING USAGE;
- ALTER INDEX HRDT_INDEX2 MONITORING USAGE;
- ALTER INDEX HRDT_INDEX3 MONITORING USAGE;
- ALTER INDEX HRDT_INDEX4 MONITORING USAGE;

- SELECT index_name, table_name, monitoring, used,
-        start_monitoring, end_monitoring
- FROM   v$object_usage;
- INDEX_NAME  TABLE_NAME MON USE START_MONITORING    END_MONITORING
- ----------- ---------- --- --- ------------------- ------------------
- HRDT_INDEX1 HRS_DETAIL YES NO  10/13/2002 03:11:34
- HRDT_INDEX2 HRS_DETAIL YES NO  10/13/2002 03:11:38
- HRDT_INDEX3 HRS_DETAIL YES NO  10/13/2002 03:11:46
- HRDT_INDEX4 HRS_DETAIL YES NO  10/13/2002 03:11:52

##   New Index Features

- Index Usage
- Query Executed to Access Index HRDT_INDEX1

- SELECT index_name, table_name, monitoring, used,
-        start_monitoring, end_monitoring
- FROM   v$object_usage;
- INDEX_NAME  TABLE_NAME MON USE START_MONITORING    END_MONITORING
- ----------- ---------- --- --- ------------------- ------------------
- HRDT_INDEX1 HRS_DETAIL YES YES 10/13/2002 03:11:34
- HRDT_INDEX2 HRS_DETAIL YES NO  10/13/2002 03:11:38
- HRDT_INDEX3 HRS_DETAIL YES NO  10/13/2002 03:11:46
- HRDT_INDEX4 HRS_DETAIL YES NO  10/13/2002 03:11:52

##   New Index Features

- Index Usage
- End Index Monitoring on HRDT_INDEX4

- ALTER INDEX hrdt_index4 NOMONITORING USAGE;

- SELECT index_name, table_name, monitoring, used,
-        start_monitoring, end_monitoring
- FROM   v$object_usage;
- INDEX_NAME  TABLE_NAME MON USE START_MONITORING    END_MONITORING
- ----------- ---------- --- --- ------------------- -------------------
- HRDT_INDEX1 HRS_DETAIL YES YES 10/13/2002 03:11:34
- HRDT_INDEX2 HRS_DETAIL YES NO  10/13/2002 03:11:38
- HRDT_INDEX3 HRS_DETAIL YES NO  10/13/2002 03:11:46
- HRDT_INDEX4 HRS_DETAIL NO  NO  10/13/2002 03:11:52 10/13/2002 03:16:01

## V$ - What Users are doing...

*select**	**a.sid, a.username, s.sql_text*
*from  **	**v$session a, v$sqltext s*
*where a.sql_address **	**= s.address*
*and   **	**a.sql_hash_value = s.hash_value*
*order **	**by a.username, a.sid, s.piece;*
*SID  USERNAME         SQL_TEXT*
*------ -------------------   ------------------------------------** 11  PLSQL_USER   update s_employee set salary = 10000**  9  SYS                     select a.sid, a.username, s.sql_text **  9  SYS                     from v$session a, v$sqltext**  9  SYS                     where a.sql_address  = s.address ** (...partial output listing)*

## V$ - What Users are doing...

*select **	**a.username, b.block_gets, b.consistent_gets,*
*        **b.physical_reads, b.block_changes, b.consistent_changes*
*from **	**v$session a, v$sess_io b*
*where a.sid = b.sid*
*order **	**by  a.username;*
***USERNAME       BLOCK_GETS  CONSISTENT_GETS  PHYSICAL_READS BLOCK_  CHANGES  CONSISTENT_CHANGES***
***-------------- ----------  ---------------  -------------- ---------------  ------------------******PLSQL_USER         39               72              11              53                   1******SCOTT              11               53              12               0                   0******SYS                14              409              26               0                   0******SYSTEM           8340            10197             291            2558                 419****** ***

## V$ - Library Cache Hit Ratio

- _Cached objects with execute count = 1_
*(Need a Bind variable perhaps)*
- SELECT 	distinct name, type
- FROM 	v$db_object_cache
- GROUP BY name, type
- HAVING 	count(executions) = 1;

## V$ - Library Cache Hit Ratio

- _Unkept, cached objects with execute counts > 1 (pin?):_
- SELECT 	distinct name, type, count(executions)
- FROM 	v$db_object_cache
- WHERE 	kept = ’NO’
- GROUP BY name, type
- HAVING 	count(executions) > 1
- ORDER BY count(executions);

## X$ - % of Current Redo Filled

- SELECT 	ROUND((cpodr_bno/lesiz),2)*100||'%' 		PctCurLogFull
- FROM 	X$kcccp a, X$kccle b
- WHERE 	a.cpodr_seq = leseq;
- PCTCURLOGFULL
- -----------------------------------------
- 97%

## X$ - % of Current Redo Filled

- alter system switch logfile;
-  System altered.
- SELECT 	ROUND((cpodr_bno/lesiz),2)*100||'%' 	  		PctCurLogFull
- FROM 	X$kcccp a, X$kccle b
- WHERE 	a.cpodr_seq = leseq;
- PCTCURLOGFULL
- -----------------------------------------
- 0%

## X$ - Segments in hot buffers

_*Segments Occupying Hot Buffers Touch Count > 10:*_
*SELECT  /*+ use_hash(bh) */**            de.owner ||'.'|| de.segment_name  segment_name,**            de.segment_type  segment_type, de.extent_id     **	**	**     extent#, bh.dbablk - de.block_id+1  block#, bh.tch*
*FROM    dba_extents  de, x$bh  bh*
*WHERE de.file_id = bh.file#*
*AND      bh.dbablk between de.block_id and de.block_id +        **	**   de.blocks – 1*
*AND     bh.tch  > 10*
*ORDER BY bh.tch;*

## Finding Where the Waits Are

- _Query v$session_wait to see what the sessions are waiting on._
- select 	username, V$SESSION_WAIT.sid, event, seq#, seconds_in_wait, wait_time, p1 , p2, p3, state
- from 	V$SESSION_WAIT, V$SESSION
- where 	V$SESSION_WAIT.SID = V$SESSION.SID
- and 	NOT(event like 'SQL%')
- and 	NOT(event like '%message%')
- and 	NOT(event like '%timer%')
- and 	NOT(event like '%pipe get%')
- order by wait_time desc, event;

**Notes:**
Notes:

## X$ - Finding Specific waits

- _Buffer Busy Waits or Write Complete Waits Events:  _
- SELECT 	/*+ ordered */ sid, event, owner, segment_name,
- 			segment_type,p1,p2,p3
- FROM 	v$session_wait sw, dba_extents de
- WHERE 	de.file_id = sw.p1
- AND 		sw.p2 between de.block_id
- 			and de.block_id+de.blocks – 1
- AND 		(event = 'buffer busy waits'
-           		OR event = 'write complete waits')
- AND 		p1 IS NOT null
- ORDER BY event,sid;

## X$ - Finding Specific waits

- col name for a20
- col p1 for a10
- col p2 for a10
- col p3 for a10
- select 	event#,name,parameter1 p1,parameter2 p2,parameter3 p3
- from 	v$event_name
- where name in ('buffer busy waits', 'write complete waits')
-  EVENT# NAME                      P1         P2         P3
- ------------- --------------------         ---------- ---------- ----------
-            143 write complete waits  file#      block#
-            145 buffer busy waits       file#      block#   id

## Combining Trace and Wait Events!

- Querying the V$SESSION_WAIT table gives a point in time view of the waits
- To record all the waits use
**Alter session set events ‘10046 trace name context forever, level *****12*****’;**
- In the trace file you’ll see entries such:
**WAIT #2: nam='db file sequential read' ****ela= 0 p1=1 p2=1135 p3=1**
**#2 - is the SQL that caused the wait**
**P1 - file#, P2 - block#, p3 - number of block read**

**Notes:**
Notes:

## Lots of latch free waits in this one... 

*WAIT #2: nam='latch free' ela= 0 p1=-2147423252 p2=105 p3=0**WAIT #2: nam='latch free' ela= 0 p1=-2147423252 p2=105 p3=1**WAIT #2: nam='latch free' ela= 0 p1=-1088472332 p2=106 p3=0**WAIT #2: nam='latch free' ela= 0 p1=-2147423252 p2=105 p3=0**WAIT #2: nam='latch free' ela= 0 p1=-2147423252 p2=105 p3=1*

## Checking this event ... 

- select  event#, name, parameter1 p1, parameter2 p2, 	  parameter3 p3
- from   v$event_name
- where name in ('latch free');
- EVENT# NAME             P1          P2         P3
- ------------ -------------------- ---------- ---------- ----------
-               3 latch free           address  number  tries

## Last Resort - Block Dumps

- SELECT 	FILE_ID, BLOCK_ID, BLOCKS
- FROM 	DBA_EXTENTS
- WHERE 	SEGMENT_NAME = 'EMP‘
- AND 	OWNER = 'SCOTT';
- FILE_ID   BLOCK_ID     BLOCKS
- -----------   ---------------    ------------         1            50465                  3

## Last Resort - Block Dumps

- ALTER SYSTEM DUMP DATAFILE 5 BLOCK 50465/
- ALTER SYSTEM DUMP DATAFILE 5 BLOCK 50466/
- ALTER SYSTEM DUMP DATAFILE 5 BLOCK 50467/

## Last Resort - Block Dumps

- ALTER SYSTEM DUMP DATAFILE 5 BLOCK MIN 50465 BLOCK MAX 50467;
- _DUMP OUTPUT:_
- row#0[3912] flag: -----, lock: 0col 0; len 7; (7): 43 48 49 43 41 47 4fcol 1; len 6; (6): 00 40 f3 31 00 00col 2; len 6; (6): 00 40 f3 31 00 0fcol 3; len 3; (3): c9 36 0a...
*Need more ... quadratic equations / Guassian distribution*

## Summary

- Statspack
- Oracle Recommendations
- Initialization File
- Helpful V$ & X$ Queries

## Compelling Statistics!

## System Global Area - SGA

##      For More Information

- www.tusc.com
*Oracle9i Performance Tuning Tips & Techniques; Richard J. Niemiec; Oracle Press (May 2003)*

## References

*Oracle Tuning Presentation, Oracle Corporation*
- www.tusc.com, www.oracle.com, www.ixora.com, www.laoug.org, www.ioug.org, technet.oracle.com
*Oracle PL/SQL Tips and Techniques, Joseph P. Trezzo; Oracle Press*
*Oracle9i Web Development, Bradley D. Brown; Oracle Press*
*Special thanks to Steve Adams, Mike Ault, Brad Brown, Don Burleson, Kevin Gilpin, Herve Lejeune, Randy Swanson and Joe Trezzo.*

## Copyright Information

- Neither TUSC, NASA or the author guarantee this document to be error-free.  Please provide comments/questions to rich@tusc.com.
- TUSC © 2003.  This document cannot be reproduced without expressed written consent from an officer of TUSC, but ODTUG may reproduce or copy this.
- _Contact Information_
- Rich Niemiec: 	rich@tusc.com** **
**www.tusc.com**