Oracle Openworld Directory Index
Dave Ensor Senior Technical Advisor BMC Software
Agenda
Introduction
Self-tuning Memory Management
Automatic Undo Management
Database Resource Manager
Unused Index Identification
Oracle Managed Files
Conclusions
Introduction
Test Environment
Self-management
Goals and Examples
Instance Parameter Management
Memory Made Simple
What, no mention of recovery?
Test environment
Compaq Armada M700
833 MHz (?) Pentium III
448 Mb memory
20 Gb hard disk
MS Windows/2000 Professional SP2
Oracle9i Enterprise Edition
Release 9.0.1.0.1
“General purpose” starter database
Customized to some extent
Self-management
Goals
If the server can successfully manage the situation then it should do so
If success is in doubt then management should be left to “someone you can fire”
Examples from everyday life
ABS, automatic transmissions
Examples from earlier Oracle versions:
V$ROWCACHE
fast_start_io_target
now fast_start_mttr_target
Instance Parameter Management
Three alternative default sources for parameters at startup
New “binary” type, maintained by Oracle
$ORACLE_HOME/dbs/spfile
$ORACLE_HOME/dbs/spfile.ora
Traditional text files, “maintained” by DBA
$ORACLE_HOME/dbs/init
create pfile, create spfile commands convert between formats
scope clause on alter system
memory, spfile or both
New Dynamic Parameters
More parameters can now be changed without instance shutdown and restart, including
but not
Persistent changes to parameters
Useful, but not (yet) perfect
alter system set db_cache_advice = off scope = BOTH;
Worked as expected, but …
alter system set db_cache_size = 50M scope = MEMORY;
Only worked as expected when total size of all caches was less than or equal to size at startup
ORA-02097: parameter cannot be modified …
ORA-00384: Insufficient memory to grow cache
alter system set java_pool_size = 30M scope = SPFILE;
Did not work as expected
ORA-02095: specified … parameter cannot be modified
Memory Made (very) Simple
Caching is good, but paging is not free
Self-tuning Memory Management
Self-tuning PGA Size
closed loop
Assistance with buffer cache tuning
open loop
Self-tuning PGA Size
pga_aggregate_target = integer [K|M|G]
Default value is 0 (off)
Cost is still unclear
Should solve sort_work_area problem
should be kept small with large number of sessions
can benefit from (very) large value with few sessions
“Oracle Corporation strongly recommends switching to the automatic memory management mode, because it is easier to manage and often outperforms a manually-tuned system” Performance Guide and Reference
Buffer Cache Tuning
db_cache_advice = [on | ready | off]
Default value is off
Son of db_block_lru_statistics
Oracle9i Reference says
“CPU and memory overheads are incurred”
Dave Ensor has noticed
CPU overhead < 10% on punitive tests
Causes instance failure on buffer pool resizing
Multiple block sizes
Long sought, but seem to be a “first cut” in 9i
Report from v$db_cache_advice
Automatic Undo Management
‘Traditional’ rollback segments
Complex management issues
Tricky to size correctly, resulting in
ORA-01555 snapshot too old …
Automatic Undo
undo_management = AUTO
undo_retention = 900 # seconds
undo_tablespace = UNDOTBS
“guarantees” no ORA-01555 within 15 minutes of query consistency point
However …
Retention period is not just a minimum target
Older undo for committed transactions will be “lost” even if space not needed for new undo
Similar to optimal in traditional rollback segments
Longer-running queries may fail even in quiet periods
Setting of undo_retention is critical
What’s the problem?
Let’s look at the Error Messages manual
Cause: Rollback records needed by a reader for consistent read are overwritten by other writers.
Action: If in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments.”
easier to set up, and
easier to manage, but
may need to vary undo_retention in some applications
Database Resource Manager
Introduced in Oracle8i
Less brutal than user profiles
Initial Comments
Give nominated groups of users a “guarantee” of a specific share of the available CPU power
Does not control or ration I/O usage
Only limits users in order to meet allocation targets
Resource plans can be extremely complex
Must be enabled from startup in order to use
alter system quiesce restricted;
quiesce restricted
Definitely a management aid
Intended to allow DBA to intervene without causing transaction failure
Pends all other work to allow users SYS or SYSTEM to “do something”
No indication to pended operations
No ability to issue grants or revokes
Except for SYS or SYSTEM objects
Database Resource Manager
CPU management
should only be effective when
active sessions > number of CPU’s
Lack of I/O management
other than undo generation
Increasing concentration of dataservers
need to balance different types of usage
overhead appears low for simple plans
Unused Index Identification
Unused Indexes
Consume space
Lengthen backups
Impose a significant DML extra load
alter index membs_status monitoring usage;
Any query against underlying table is classed as using the index i.e. V$OBJECT_USAGE.USED = 'YES'
Unless, of course, you know different
Very unusual V$ object, a view on a real Oracle table!
Alternative to monitoring usage
Low tech approach may work well enough
e.g.
select INDEX_NAME from USER_INDEXES minus select OBJECT_NAME from V$SQL_PLAN where OBJECT_OWNER = USER;
only detects index usage that is reported in a query plan
omits indexes used only in constraint enforcement
Oracle Managed Files I
Oracle9i can create its datafiles and logfiles in default locations with unique names
Wrongly stated in the Oracle9i Glossary to be RAC (Real Application Cluster) feature
No real difference in ‘management’ of file
Example
SQL> alter system set db_create_file_dest = 'D:\TEMP';
System altered.
SQL> create tablespace XXX;
Tablespace created.
created 100 Mb file D:\TEMP\ORA_XXX_ZY2TFB00.DBF
Oracle Managed Files II
A fairly harmless convenience
Especially useful in package install scripts
Most sensible when all datafiles have to go into the same disk domain
Handy for temporary database extensions
File is automatically deleted when tablespace or logfile is dropped
Do not need including contents and datafiles
Also support create database …
Space management
Locally managed tablespaces are now the default
except for SYSTEM tablespace
extent allocation defaults to automatic
uniform may be better in many (most?) cases
should reduce DBA management load to predicting and reacting to exhaustion of space
autoextend still questionable
unless backing up unused space!
Conclusions
Self-tuning Memory Management
Database Resource Manager
Automatic Undo Management
Oracle Managed Files
Unused Index Identification
Persistent Changes to Instance Parameters