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.boobooke.com/v/bbk1269
   http://www.boobooke.com/v/bbk1270
   http://www.boobooke.com/v/bbk1271  
   http://www.boobooke.com/v/bbk1272
   http://www.boobooke.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