tuning 2 Diagnostic and Tuning Tools

--maitenance of alert log file
the alert log file consis of chronnological messages and errors;
check the alert log file regularly to:
---detect internal errors and corruption errors
---monitor database operations
---view nondefault initialization parameters
remove or trim log file regularly after checking

--tuning components
the alert log file contains the following information which can be used tuning the database:
---checkpoint start and end times
---incomplete checkpoints
---time to perform archiving
---instance recovery start and complete time
---deadlock and timeout errors

实验：
alter system set log_checkpoints_to_alert=true scope=both;   ---同时修改内存和spfile
---LOG_CHECKPOINTS_TO_ALERT lets you log your checkpoints to the alert files

--Backgroud process trace file
---the oracle server dumps information about errors detected by any backgroud process into trace files
---oracle support uses these these trace file to diagnose and troubleshooting
---these file do not usually contain tuning information
文件在bdump下

--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 staments for that session
---user trace files are created on a per server process basis;
---user trace files can also be created by
----backup control file to trace
----database set events

select sid,serial#,username from v$session;
alter session set sql_trace=true;  --session 能在trace文件中生效
execute dbms_system.set_sql_trace_in_session(sid,serial#,true|false);  --同上

--views,utilites,and tools
OEM
diagnostics and tuning packs
staspack
V$XXX dymatic troubleshooting and performance views
oracle wait envents

--statspack
---installation of statspack using the spcreate.sql script
---collection of statistics execute staspack.snap
---automatic collection of statistics using of spauto.sql
---produce a report using the spreport.sql script
---to collect timing information ,set timed_stastics=true;   
statspack 官方文档：
/u01/oracle/rdbms/admin/spdoc.txt

create tablespace perfstat datafile '/u01/oradata/YZHDBA/perfstat01.dbf' size 512M --创建存放staspack的表空间
@?/rdbms/admin/spcreate  --用来安装statspack
---信息统计
execute statspack.snap
两次监控不要shutdown之间，并且十几分钟执行一次就行了
---产生报告
@?/rdbms/admin/spreport.sql

--statspack  output
--DD and special views
---dba_tables,dba_tab_columns
---dba_clusters
---dba_indexes,index_stats
---index_histogram,dba_tab_histogram
this statistics information is static until you reexecute dbms_stats

--systemwide statistics
v$sysstat v$sgastat
v$event_name v$system_event

--session related statistics
---搞清下面的表的关系
v$session v$sesstat v$statname           
v$session_event v$event_name v$session_wait

--oracle wait events --分析性能瓶颈的一方面
v$event_name

 

--troubleshooting and tuning views
---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
...
---contention

---memory

---user/session

--utlbstat and utlestat

--dba-developed tools
develop your own scripts
use the supplied packeges for tuning
...

--level of statics collection
the initialization parameters that determine the level of statistics collection are:
---statistics_level
---timed_statistics
---timed_os_statistics
---db_cache_advice