Wednesday, March 28, 2007

Oracle9i Database: Performance Tuning, 1Z0-033

The Oracle9i Database: Performance Tuning is the second of two required tests to achieve Oracle Certified Professional status.

These notes are from OCP Oracle 9i Database: Performance Tuning Exam Guide by Charles Pack.

This view is a summary of waits for an event by an instance. While V$SESSION_WAIT shows the current waits on the system, V$SYSTEM_EVENT provides a summary of all the event waits on the instance since it started. It is useful to get a historical picture of waits on the system. By taking two snapshots and doing the delta on the waits, you can determine the waits on the system in a given time interval.

This view keeps a summary all buffer waits since instance startup. It is useful for breaking down the waits by class if you see a large number of buffer busy waits on the system.

This contains overall statistics for many different parts of Oracle, including rollback, logical and physical I/O, and parse data. Data from V$SYSSTAT is used to compute ratios, such as the buffer cache hit ratio.

This contains detailed file I/O statistics for each file, including the number of I/Os for each file and the average read time.
This view contains information about file read/write statistics.

This contains detailed latch usage statistics for each latch, including the number of times each latch was requested and the number of times the latch was waited for.
V$LATCH_PARENT contains statistics about parent latches. The columns of V$LATCH_PARENT are identical to those in V$LATCH.
There are multiple latches in the database for some type of latches. V$LATCH provides aggregate summary for each type of latch. To look at individual latches, query the V$LATCH_CHILDREN view.

Methods for collecting statistics:

This view contains detailed information on the system global area (SGA).
SGA contains shared pool (library cache, dictionary cache, sometimes UGA).
If using MTS, configuring large pool can reduce library cache fragmentation.
Each row in this view contains statistics for one type of item kept in the library cache. The item described by each row is identified by the value of the NAMESPACE column. Rows with the following NAMESPACE values reflect library cache activity for SQL statements and PL/SQL blocks:

This view provides object level statistics for objects in the library cache (shared pool). This view provides more details than V$LIBRARYCACHE and is useful for finding active objects in the shared pool.

V$SQLAREA contains up to 1000 characters of the SQL, along with execution statistics.
V$SQLTEXT contains the entire SQL.
V$SQL_PLAN contains the actual plan. It could be useful to capture v$sql_plan for troubleshooting purposes when a SQL stops performing as expected.

DBMS_SHARED_POOL.KEEP will pin objects in the shared pool so they do not age out. alter system flush shared_pool will not clear pinned objects.
SHARED_POOL_RESERVED_SIZE provides space for larger memory allocations and defaults to 5% of the shared pool.
V$SHARED_POOL_RESERVED shows space used, misses, etc. If request_failures > 0 then increase shared_pool_size.

V$ROWCACHE dictionary cache statistics. Usually tuned by increasing shared_pool_size.
UGA, User Global Area. Contains user session data and cursor state. When using shared server, this moves from PGA to shared pool.
LARGE_POOL_SIZE Large pool, when configured, is used for RMAN IO buffers, parallel query message buffers, and UGA when using shared server. Does not have an LRU. Setting parallel_automatic_tuning=true will enable large pool.
If library cache hit is 100%, may set cursor_space_for_time=true.
When using star transformations or DSS queries, use cursor_sharing=exact.

SQL sorts and parallel reads bypass buffer cache, which implies the buffer cache does checkpoints to keep the buffer cache in sync.
Full table scan blocks are placed at end of LRU list.
Buffer cache read of block:
- reading process uses hash function to look for block in buffer cache
-- if found, block moves to MRU
-- if not found, LRU is searched for free buffer, block is read from disk and placed in free buffer
-- if block read from disk is too recent for query read consistency, look in rollback
DBWn writes when:
- dirty list length exceeds threshold
- free block not found in LRU
- every 3 seconds
- LGWR checkpoint
- alter tablespace begin backup / offline
- object is dropped
- shutdown normal / immediate / transactional

DB_CACHE_ADVICE enables statistics gathering for V$DB_CACHE_ADVICE.
SGA_MAX_SIZE is upper bound of dynamic SGA growth.
V$BUFFER_POOL general info for all buffer pools.
V$BUFFER_POOL_STATISTICS statistics for all buffer pools.

LRU latch contention:
cache buffer lru chain
The cache buffer lru chain latches protect the lists of buffers in the cache. When adding, moving, or removing a buffer from a list, a latch must be obtained. Set to 1/2 number of CPUs. Monitor with v$system_event.
Cache buffer chains protect the list and are used when using the list. Monitor v$latch_children for high misses or sleeps.

Redo Log Buffer is a circular buffer in the SGA that holds information about changes made to the database.
- set to at least 1MB
- no real penalty for setting a little too high
- v$sysstat "redo buffer allocation retries" should be near zero
- v$session_wait "log buffer space" should be zero

JAVA_SOFT_SESSIONSPACE_LIMIT writes warning to trace file.
JAVA_MAX_SESSIONSPACE_SIZE hard limit that kills session.
DBWR_IO_SLAVES can simulate async IO.
DB_WRITER_PROCESSES is useful for systems that modify data heavily. It specifies the initial number of database writer processes for an instance. Multiple db writer processes are highly recommended for systems with multiple CPUs (at least one db writer for every 8 CPUs) or multiple processor groups (at least as many db writers as processor groups).

When using workarea_size_policy=auto and pga_aggregate_target, do not set:
V$PGASTAT monitors PGA usage.
V$SYSSTAT where name like 'sort%' for sorts to memory/disk.
V$SORT_SEGMENT info on every sort segment.
V$SORT_USAGE.username shows usage by user.

V$LATCH stats for SGA latching.
Wait event "latch free" is when a process tries for a latch and can not get it, then it sleeps. It spins on multi-cpu machines.
If "willing to wait", the process tries and then spins.
Latch request is "immediate" when it already has one or more latches and needs another. If this latch is not obtained, it releases all latches and starts over.

V$ROLLNAME has names of online rollback segments.
V$ROLLSTAT rollback segments statistics.
V$TRANSACTION useful for joining on UndoSegmentNumber.

What generates undo:
delete generates the most
inserts only writes the rowid
update writes row and index.

V$UNDOSTAT undo consumption.

Automatic undo pfile parameters:

How to switch to a new undo tablespace:
alter system set undo_tablespace=___________
Existing transactions will continue to use the previous tablespace.

PMON Process MONitor cleans up aborted transactions and their locks.

DML_LOCKS=0. Enqueues are disabled and performance is slightly increased. However, you cannot use DROP TABLE, CREATE INDEX, or explicit lock statements such as LOCK TABLE IN EXCLUSIVE MODE.

delayed block cleanout - lock on the row is cleaned by the next transaction doing DML on the row. The lock is not cleaned from the row when the original locking transaction does a commit.

V$LOCK - This view lists the locks currently held by the Oracle database server and outstanding requests for a lock or latch.
V$ENQUEUE_LOCK - This view displays all locks owned by enqueue state objects. The columns in this view are identical to the columns in V$LOCK.
V$LOCKED_OBJECT - This view lists all locks acquired by every transaction on the system.
DBA_BLOCKERS - DBA_BLOCKERS displays a session if it is not waiting for a locked object but is holding a lock on an object for which another session is waiting.
DBA_WAITERS - DBA_WAITERS shows all the sessions that are waiting for a lock, but do not hold locks for which another session is waiting.
utlloctt.sql shows locks in tree display.
DBMS_LOCK allows explicit sequential processing.
breakable parse lock A SQL statement (or PL/SQL program unit) in the shared pool holds a parse lock for each schema object it references. Parse locks are acquired so that the associated shared SQL area can be invalidated if a referenced object is altered or dropped. A parse lock does not disallow any DDL operation and can be broken to allow conflicting DDL operations, hence the name breakable parse lock.

A parse lock is acquired during the parse phase of SQL statement execution and held as long as the shared SQL area for that statement remains in the shared pool.

Shared Server
Oracle Net - tnsnames or other naming service.
init.ora - circuits, dispatchers, max_dispatchers, shared_servers, max_shared_servers, shared_server_sessions

v$queue contains information on the shared server message queues.

User Global Area moves to the shared pool or large pool.
Dispatchers are limited to ~1000 connections each.
Startup/shutdown still requires a dedicated server connection.

Application Tuning
Index cluster contains nulls. No index columns are specified, because the index is automatically built on all the columns of the cluster key. For cluster indexes, all rows are indexed.
hash cluster - direct row access via algorithm.
cluster good for - minimal key updates, tables frequently joined.

B tree index - balanced, when leaf block fills it is split in half, optionally can compress repeated values.
B tree reverse key index - can be useful with sequentially increasing keys to avoid last page contention and excessive index heights. Only useful for exact key lookups, can not be used for range scans.
Function based index - B tree or bitmap. Need query rewrite to use it.
Index Organized Table - requires primary key.
ROW OVERFLOW can split out non-PK columns.
contains logical row ID that is guess to row overflow.
may be partitioned.
may have bitmap index by using a mapping table.

Materialized View
complete - truncate and run query.
fast - apply changes via materialized view log.
force - uses fast if possible, else uses complete.
on commit - automatic refresh after transaction commit.
on demand - manually call dbms_mview.

segment <- extent <- block <- os block
Initial and next extent defined at time of object creation or defaults to tablespace settings.

dbms_space_admin works on locally managed tablespaces.

High water mark is stored in segment header.
High water mark is raised in five block increments.
alter table table_name deallocate unused will drop high water mark if possible.
Temporary segments must use default block size.
PCTFREE how much table space to reserve for future row updates.
PCTUSED when to put table block on free list.
row chaining - row does not fit in an empty block so it is split across blocks.
row migration - when updated row won't fit in it's block and entire row is moved with a pointer remaining.
row migration - seen in statspack as "table fetch continued row" and in v$sysstat.
alter index index_name coalesce consolidates leaf blocks.
alter index index_name rebuild online moves the index so new storage parameters may be specified.
v$object_usage stores results of monitoring table and index access.

Outlines are stored in schema OUTLN.
Outlines are grouped into categories.
DBA_OUTLINES describes all outlines in the database.
DBA_OUTLINE_HINTS describes the set of hints that make up the all the outlines in the database.

Create outlines for all SQL:
alter system set create_stored_outlines=
alter system set create_stored_outlines=FALSE; # Turns it off.

create or replace outline myoutline on SELECT ...


Use the ALTER OUTLINE statement to rename a stored outline, reassign it to a different category, or regenerate it by compiling the outline's SQL statement and replacing the old outline data with the outline created under current conditions.
alter outline myoutline rebuild;

Private outlines:
exc dbms_outln_edit.created_edit_tables
create private outline newoutln from oldoutln; # Clones the outline.

alter session set use_private_outlines=true;
create or replace outline ____ from private ____; # Publish it.

histogram good for skewed data.


dbms_system.set_sql_trace_in_session(SID=>' ', serial#=>' ', sql_trace=>TRUE)

explain plan set statement_id = ' ' for SELECT ...
set autotrace traceonly explain;

v$sql_plan has actual execution plan.
V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache.
V$SQL_PLAN_STATISTICS provides execution statistics at the row source level for each child cursor.

Consumer groups contain like users.
One plan directive may be active in the instance at a time.

The DBMS_RESOURCE_MANAGER package maintains plans, consumer groups, and plan directives.
create_simple_plan (does it all in one step)


dbms_resource_manager_privs.grant_switch_consumer_group (grant to user not role)

alter system set resource_manager_plan= ____;
In pfile, the parm is resource_manager_plan.

v$rsrc_plan displays the names of all currently active resource plans.
v$rsrc_consumer_group displays data related to currently active resource consumer groups.
DBA_RSRC_PLANS lists all resource plans in the database.

Saturday, March 24, 2007

Sun Grid Engine

The April edition of Sys Admin magazine provides an introduction to Sun's open source job scheduler, Grid Engine. While able to effectively coordinate compute tasks across many machines, it also handles job dependencies and could provide pressure to vendors such as AppWorx.

Monday, March 19, 2007

HP-UX Network Interface Check Duplex

How to check duplex setting of network interface on HP-UX 11.11. "-x" is the interface.

bash-3.00# uname -a; lanscan -q ; lanadmin -x 13;lanadmin -x 15
HP-UX host001 B.11.11 U 9000/800 1288319306 unlimited-user license
900 11 0
901 15 13
Speed = 1000 Full-Duplex.
Autonegotiation = On.

Speed = 1000 Full-Duplex.
Autonegotiation = On.

$ /usr/sbin/lanadmin

Mon, Mar 19,2007 09:04:59

Copyright 1994 Hewlett Packard Company.
All rights are reserved.

Test Selection mode.

lan = LAN Interface Administration
menu = Display this menu
quit = Terminate the Administration
terse = Do not display command menu
verbose = Display command menu

Sunday, March 18, 2007

Turn Off Oracle Index Parallel Query

Parallel query can be useful for reducing elapsed run time of Oracle index scans. In some circumstances, parallel query can cause longer elapsed run times.

As the schema owner, determine the index parallel query settings:
SQL> l
1 select degree, instances , count(*)
2 from user_indexes
3* group by degree, instances
SQL> /

--------- ---------------------------------------- ----------
0 0 4
1 1 1303
6 1 8
10 1 3

Run SQL to create a SQL file that will set noparallel:
$ cat bill_ix_alter_nopq_gen.sql
set pages 0 lines 100 feedback off
spool bill_ix_alter_nopq.sql
prompt spool bill_ix_alter_nopq

select 'alter index ' || index_name || ' noparallel;' from user_indexes where degree > '1'
order by 1
spool off

Run the generated SQL file (bill_ix_alter_nopq.sql).

Verify the new settings:
SQL> l
1 select degree, instances , count(*)
2 from user_indexes
3* group by degree, instances
SQL> /

--------- ---------------------------------------- ----------
0 0 4
1 1 1352

Wednesday, March 07, 2007

Monitoring Oracle Data File For Can't Extend

There are several pitfalls when creating SQL that will monitor for a can't extend condition in the Oracle database.

This appears to show a data file larger than the maximum size.

SQL> select substr(FILE_NAME,22,23) as data_file_name, bytes, maxbytes, maxblocks, USER_BYTES, AUTOEXTENSIBLE from dba_data_files where tablespace_name='CUST_BILL_INV_TAX'

------------------------- ---------------- ---------------- ------------ ---------------- ---
cust_bill_inv_tax02.dbf 34,129,051,648 34,359,721,984 4,194,302 34,078,720,000 YES
cust_bill_inv_tax01.dbf 34,358,689,792 34,358,689,792 4,194,176 34,078,720,000 YES
cust_bill_inv_tax03.dbf 16,532,635,648 2,147,483,648 262,144 16,252,928,000 YES

SQL> select * from v$version;

Oracle9i Enterprise Edition Release - 64bit Production
PL/SQL Release - Production
CORE Production
TNS for HPUX: Version - Production
NLSRTL Version - Production

Oracle Note:197244.1 explains this situation.
The MAXBYTES column in DBA_DATA_FILES is not updated when the datafile has been resized. The only column that is updated is BYTES. The MAXBYTES is updated by the ALTER DATABASE command with MAXSIZE option as described above.