oracle 效能調整

                                                            oracle 效能調優第3講

1.show parameter dump  檢視日誌檔案存放的路徑(alert_SID.log) 在alert_SID.log中/ORA-,Oracle資料的錯誤一般是以ORA-開頭.ORA-600是oracle的內部錯誤.

2.alert_SID.log只有一個檔案，如果你檢查過以後，確定問題都解決了。那麼你可以拷貝走，或者清空(cat /dev/null> alert_prtts.log)

3.日誌檔案中包含的主要資訊:checkpoint start and end times.incomplete checkpoints等

4.LOG_CHECKPOINTS_TO_ALERT:lets you log you checkpoints to the alert file.default false.把checkpoint資訊加入日誌檔案.

5.show parameter log_ch，檢視log_checkpoints_to_alert;

6.alter system set log_checkpoints_to_alert = true scope=both;
alert-file是與oracle相關，一個oracle 只有一個alert-file.

7.trace file(backgroup trace file) 是與程式相關的。(程式的錯誤資訊放入trace files中)，這些檔案提供給oracle支持者(supports)。這些檔案中沒有調優資訊.(trace File檔案格式:SID-ORA-PID)

8.user trace files:使用者操作的詳細資訊能夠記錄在user trace file中.

 server process tracing can be enabled or disabled at the session or instance level.
 a user trace file contains statistics for traced sql statements in that session.
 user trace files are created on a per server process basis
 user trace file can also be created by :
 -Backup control file to trace
 -Database set events

 

 

                                      oracle 效能調優第4講
         
 alter session set sql_trace=true;//產生trace file檔案

 EXECUTE dbms_system.set_sql_trace_in_session(SID,SERIAL#,TRUE);//產生trace File TRUE eanbale  trace.SID,SERIAL#在v$session表中

 show parameter sql_trace //如果這個引數為true,那麼所有的session都會產生trace 檔案. 這個應該避免.(這樣對效能有很大的影響)

 pitts_ora_6072_.trc(sid_ora_pid_.trc);

 trace檔案的內容研究在以後的學習中會逐步介紹
  Views, Utilities,and Tools.(oralce調優工具)

  1.Oracle Enterprise Manager

  2.Diagnostics and tuning packs(books/pl/sql packages and type reference聯機文件 DBMS_STATS包)

  3.Statspack

  4.v$xxx dynamic troubuleshooting and performance views

  5.dba_xxx dictionary views

  6.Oracle wait events()

  7.utlbstat.sql and utlestat.sql scripts(begin,end)(statspack替代這兩個指令碼)
 
 
  statspack:

   installation of statspack using the spcreate.sql script

   Collection of statistics execute statspace.snap

   automatic collection of statistics using the spauto.sql.script

   Produce a report using the spreport.sql script

   To collect timing information,set TIMED_STATISTICS=TRUE
  
   sql>CREATE TABLESPACE perfstat DATAFILE '/u01/oradata/pitts/perfstat_01.dbf' SIZE 512M;
  
   SQL>@?/rdbms/admin/spcreate.sql     (@表示執行，?表示oracle_home目錄)按照提示輸入使用者密碼，表空間，，臨時表空間.
  
   sql> execute statspack.snap (兩次執行statspack.snap,對這段時間內資料進行採集，可用自動產生報告，具體看oracle文件)
  
   sql>@?/rdbms/admin/spreport.sql(得到報告)
  
   具體介紹statspace video的視訊，德哥的視訊地址:
   http://www.垃圾廣告.com/v/bbk1269
   http://www.垃圾廣告.com/v/bbk1270
   http://www.垃圾廣告.com/v/bbk1271  
   http://www.垃圾廣告.com/v/bbk1272
   http://www.垃圾廣告.com/v/bbk1279

 

 

                              oracle 效能調優第5講
  oracle database 10g performance tuning
 
  DD and spcecial views:
  the following dictionary and spcecial views provide useful statistics after using the dbms_stats package:
  1. dba_tables,dba_tab_columns

  2. dba_clusters

  3. dba_indexes,index_stats

  4. index_histogram,dba_tab_histograms
 
  This statistical information is static until you reexecute dbms_stats.
  oracle 執行一條sql，它有優化器來選擇進行那條路徑的選擇。而優化器如何選擇這條路徑取決與 這些表(資料字典)中的資料。
  這些資料字典中的資料是靜態的。所有如果要發生變化，需要執行dbms_stats來進行統計.
 
  指標表:v$sysstat,v$sgastat,v$system_event,v$event_name
 
  session_related statistics:

  v$session,v$sesstat,v$statname(這三者相關,必須掌握session表的所有意思)

  v$session_wait,v$event_name,v$session_event(書:oracle wait interface- A practical guide to performance diagnostics tuning)

 

                                                         oracle 效能調優第6講
 動態效能檢視表:
 instance/database: v$database,v$instance,v$option,v$parameter,v$backup,v$px_process_sysstat,v$process,v$waitstat,v$system_event
 

 Disk:v$datafile,v$filestat,v$log,v$log_history,v$dbfile,v$tempfile,v$tempstat,v$segment_statistics
 

 Contention:v$lock,v$rollname,v$rollstat,v$waitstat,v$latch

 Memory:v$buffer_pool_statistics,v$db_object_cache,v$librarycache,v$rowcache,v$sysstat,v$sgastat
 

 User/Session:v$lock,v$open_cursor,v$process,v$transaction,v$px_sesstat,v$px_session,v$sesstat
        v$session_event,v$sort_usage,v$session_wait,v$session,v$session_object_cache
 
 DBA-Deleloped Tools
 1.Develop you own scripts(shell程式設計等)

 2.use the supplied packages for tuning

 3.schedule periodic performance checking

 4.Take adavntage of the Enterprise Manager Event service to trace specific situations

 5.take advantage of the oracle enterprise manager job service to :

   a. automate the regular execution of administrative tasks.

   b. apply tasks that automatically solve problems detected by the oracle enterprise manager event service.
  
 Level of statics Collection:
 The initialization parameters that determine the level of statistic collection are:
 1. STATISTICS_LEVEL(ALL/TYPICAL/BASIC)
 
 2. TIMED_STATISTICS(Boolean, IF STATISTICS_LEVEL is set to typical or all then true. if STATISTICS_LEVEL is set to basic then false)
 
 3. TIMED_OS_STATISTICS(Integer)
 
 4. DB_CACHE_ADVICE(ON | READY,OFF)

  Summary:
  in this lesson,you should have learned how to:
  1. Use the alert log file
  2. Get information from background processes trace files
  3. Trace user SQL statements
  4. Collect statistics from dictionary and dynamic performance troubleshooting views
  5. use the statspack utility to collect performance data
  6. retrieve wait events information

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/235507/viewspace-608939/，如需轉載，請註明出處，否則將追究法律責任。