Diagnostic and Tuning Tools
参考文献:
ORACLE DATABASE 10G Performance Tuning Tisp&Techniques(Interpreing the STATSPACK Output)
Oracle 高校设计 (Thomas Kyte)
Oracle Wait Interface:A practical Guide to Performance Diagnostics & tuning
一、概要
1. alert log file
2. background trace files
3. user trace files
4. OEM
5. STATSPACK
6. dynamic performance views
7. other tools
二、ALERT LOG(chronological log of messages and errors)
1. Detect internal errors and block corruption errors
2. Monitor db operations
3. view the nondefault initialization parameters
4. Remove or trim the alert file
cp alert_sid.log alert_sid.log.bak
cat /dev/null > alert_sid.log
5. Alert log Content
checkpoint start and end times
(alter system set log_checkpoints_to_alert = true scope=both;)
incomplete checkpoints
time to perform. archiving
instance recovery start and complete times
Deadlock and timeout errors
三、 Background Process Trace Files
1. errors detected by BP to trace files.(pmon , smon ,dbwr, dbwn ,lgwr)
2. ORACLE SUPPORT uses these trace files to diagnose and troubleshoot.
3. do not contain tuning info
四、 User Trace Files(user_dump_dest)
1. Server process tracing can be enabled or disable at session or instance level
2. contains statistics for traced SQL statements in that sesssion
3. be created on a per server process basis
4. backup control file to trace and db set events
ALTER SESSION SET sql_trace=TRUE;
or EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID,SERIAL#,TRUE)
or SQL_TRACE(全局PARAMETER)
五、 TOOLS
1. OEM
2. Diagnostics and tuning packs
3. Statspack
4. v$xxx dynamic troubleshooting and performance views
5. dba_xxx dictionary views
6. Wait events
7. ultbstat.sql and utlestat.sql scripts
六、 STATSPACK ($ORACLE_HOME/rdbms/admin/spdoc.txt)
1. installation of statspack using spcreate.sql
2. manual collection using statspack.snap
3. automatic collection using spauto.sql
4. report using the spreport.sql
5. timing info need set timed_statistics=true
七、 DD and important views
1. after reexecute dbms_stats, to check:
dba_tables, dba_tab_columns
dba_clusters
dba_indexes, index_stats
index_historgram, dba_tab_historgrams
This statistical info is static until you reexecute dbms_stats.
2. Systemwide Statistics
v$sysstat (statistics#, name, class, value)
v$sgastat (pool, name, bytes)
v$system_event(event, total_waits, total_timeouts, time_waited, average_wait)
3. Session Statistics
v$session(sid, serial#,username,osuser)
v$sesstat(sid,statistics#,value)
v$statname(statistic#,name,class)
v$session_event(sid,event,total_waits,total_timeouts,time_waited,average_wait,max_wait)
v$session_wait(sid,seq#,event,p1/2/3)
4. Wait Events
v$event_name(event#,name,parameter1,p2,p3)
v$session_event
v$session_wait
v$system_event
5. Special Views
v$xx base on x$ , listed in v$fixed_table
x$tables:not usually queried directly
populated at startup and cleared at shutdown
6. TS and Tuning views
a. INSTANCE/DATABASE
v$database, v$instance, v$option, v$parameter, v$backup
v$px_process_sysstat, v$process, v$waitstat, v$system_event
b. Disk
v$datafile, v$filestat, v$log, v$log_history, v$dbfile v$tempfile,
v$tempstat, v$segment_statistics
c. Contention
v$lock, v$rollname, v$rollstat, v$waitstat, v$latch
d. Memory
v$buffer_pool_statistics, v$db_object_cache,
v$librarycache, v$rowcache, v$sysstat, v$sgastat
e. User/Session
v$lock, v$open_cursor, v$process, v$transaction, v$px_ststat, v$px_session
v$sesstat, v$session_event, v$sort_usage, v$session_wait, v$session, v$session_object_cache
八、 Level of Statisc Collection
1. STATISTICS_LEVEL
TYPICAL(default):collection of all major statistics
ALL:OS statistics and plan execution statistics + TYPICAL
BASIC: disable collection of many of the important statistics
2. TIMED_STATISTICS
TRUE:WHEN STATISTICS_LEVEL IN (TYPICAL,ALL)
FASE:WHEN STATISTICS_LEVEL = BASIC
3. TIMED_OS_STATISTICS
5:WHEN STATISTICS_LEVEL = ALL
0:WHEN STATISTICS_LEVEL IN (TYPICAL,BASIC)
4. DB_CACHE_ADVICE
ON:WHEN STATISTICS_LEVEL IN (TYPICAL,ALL)dvisory is turned on
READY:Advisory is turned off.
OFF:WHEN STATISTICS_LEVEL = BASIC
参考链接:http://www.boo booke.com/bbs/viewthread.php?tid=7630&extra=page%3D1
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9252210/viewspace-627231/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9252210/viewspace-627231/