3-2Tunning Tools

每天检查alert日志
路径检查：show parameter dump

关注ORA-错误

关注点：
Detect internal errors(ORA-600) and block corrption erros
Monitor database operations
View the nondefault initialization parameters

checkpoint start and end times --log_checkpoints_to_alert设置为true
Incomplete checkpoints
Time to perform. archiving
Instance recovery start and complete times
Deadlock and timeout errors

SQL> show parameter log_check

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_checkpoint_interval              integer     0
log_checkpoint_timeout               integer     1800
log_checkpoints_to_alert             boolean     FALSE

User trace file can also be created by:
Backp control file to trace
Database SET EVENTS

查某个session的sql trace ：
本session产生方式：
alter session set sql_trace=TRUE；

其他session，sys用户执行：

execute dbms_system.set_sql_trace_in_session(sid,serial#,TRUE);

控制全局,会影响性能：
show parameter sql_trace

SQL> show parameter sql_trace;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sql_trace boolean FALSE

调优工具和视图：
OEM
diagnostics and tunning packs
statspack
v$xxx dynamic troubleshooting and performace views
dba_xxx dictionary views
oracle wait events
utlbstat.sql and utlestat.sql scripts---被statspack替代

statspack安装和使用
安装spcreate.sql
collection of statistics execute statspack.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

ll $ORACLE_HOME/rdbms/admin/*.txt
cat spdoc.txt
必看文档

先建表空间
create tablespace perfstat datafile '/home/01.dbf' size 500M;
@?/rdbms/admin/spcreate.sql

perfstat 用户密码设置
选定表空间
临时表空间

使用15-30分钟合适：
execute statspack.snap;
execute statspack.snap;

@?/rdbms/admin/spreport.sql

德哥五辑statspack视频

DD and Special Views:
dba_tables,dba_tab_columns
dba_clusters
dba_indexes,index_stats
index_histogram,
dba_tab_histograms

This statistical information is static until you reexecute dbms_stats;

system wide statistics:
v$sysstat
v$sgastat
v$event_name
v$system_event:total waits for an event,all sessions together
每个有多少记录；每个版本不一样；

select * from v$event_name ;
889 rows selected.

session related statistics:
v$session
v$sesstat
v$statname

v$session_event:waits for an event for each session that had to wait
v$session_wait:waits for an event for current active sessions that are waiting

select * from v$sesstat;
380 rows selected.

bbk2121

v$views:
based on x$tables
listed in v$fixed_table

x$ tables

select * from v$fixed_table;
1393 rows selected.

常用视图:
trouble shooting and tunning views
instance/database
v$database
v$instance
v$option
v$parameter
v$backup
v$px_process_sysstat
v$process
v$waitstat
v$system_event
v$nls_parameter

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

统计配置参数level of statics collection：
statitstics_level
timed_statistics
timed_os_statistics
db_cache_advice

SQL> show parameter stat
statistics_level                     string      TYPICAL
timed_os_statistics                  integer     0
timed_statistics                     boolean     TRUE
SQL>

SQL> show parameter db_cache_advice;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string ON

DBA工具箱

ITPub博客

3-2Tunning Tools

最新文章