Database Performance
Submitted by admin on Sat, 2004-08-07 07:17.
- A good design is still key. Index appropriately and watch row chaining.
- Monitor V$SESSION_WAIT regularly to identify wait conditions. If the SEQ# column stops changing, that event is stuck.
- Monitor locking and latching (V$LOCK, V$LATCH, V$LATCHHOLDER, etc.).
- SQLDBA still needs a lot of work. Server Manager only supports line mode operations on MVS.
- Create enough rollback segments (say #active_transactions/4, but less than 50) in a separate tablespace, cache it and watch WAITS and SHRINKS.
- Use a temporary tablespace with PCTINCREASE=0 and large INITIAL=NEXT extents (but a multiple of SORT_AREA_SIZE).
- DB links will dedicate database workers if you don't use TCP/IP! Big DYNWORK= parameter implications.
- Oracle doesn't support mirrored databases so DSS and OLTP must run on the same database (it's also not possible to have a hot standby database).
- 2PC (two phase commit) is slow, can even cause read blocks (monitor DBA_2PC_PENDING).
- Tune all SQL statements regularly, use EXPLAIN PLAN, TKPROF, DELPHI Monitor, etc.
- Use Oracle7 performance features like HASHING, enforced INTEGRITY constraints, etc. But Oracle still doesn't support CLUSTERED INDEXES.
- Use TRUNCATE instead of DROP and re-CREATE.
- Bad optimizer execution plans can be extremely expensive, data HISTOGRAM's is still not supported by Oracle.
- Be careful with the COST based optimizer. It might be slower and will need manual tuning (hinting). ANALYZE regularly! Consider switching from RULE to COST when Oracle starts to use the cost based optimizer for their dictionary.
- Write a data API (stored proc's, packages) on tables to avoid user SQL being issued against them. This will reduce network I/O and protect data integrity.
- A good SQL coding standard will optimize the use of the shared pool.
»
- Login to post comments