播布客視訊-Performance Tuning筆記(二)Diagnostic and Tuning Tools

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/，如需轉載，請註明出處，否則將追究法律責任。