性能调优学习笔记（一）

一、性能调优的目的
二、查看数据库报警文件
三、跟踪文件
四、查看数据库性能
1、企业管理器
2、Statspack
3、Dictionary and Dynamic Views
4、Dynamic Troubleshooting and Performance Views
5、Troubleshooting and Tuning Views
6、The utlbstat and utlestat Utilities
7、Level of Statistics Collection[@more@]

一、性能调优的目的
Reducing or eliminating waits
Accessing the least number of blocks
Caching blocks in memory
Minimizing response time
Increasing throughput
Increasing load capabilities
Decreasing recovery time
Instance hit percentages
二、查看数据库报警文件
The location of the alert log file is given by the BACKGROUND_DUMP_DEST parameter
Internal errors (ORA-600) and block corruption errors (ORA-1578 or ORA-1498)
These values are written into the alert log file only if the LOG_CHECKPIONTS_TO_ALERT parameter has been set to True.
三、跟踪文件
1、实例级别
This trace logging is enabled or disabled by the SQL_TRACE initialization parameter. The
default value is False.
2、会话级别
a、指定SESSION
EXECUTE dbms_system.set_sql_trace_in_session(8,12,TRUE);
where 8 and 12 are the system identifier and serial numbers of the connected user.
The dbms_system package is created when the catproc.sql script is run. This script
is located in the following directory:
On UNIX systems
$ORACLE_HOME/rdbms/admin
On Windows
%ORACLE_HOME% dbmsadmin
b、当前会话
To enable the writing of a trace file for your current session issue the command:
ALTER SESSION SET sql_trace=TRUE;
四、查看数据库性能
1、企业管理器
要登录到OMS，有以下包可供使用
Diagnostics Pack
- Lock Monitor
- Performance Manager
- Performance Overview
- Top Sessions
- Top SQL
- Trace Data Viewer
Tuning Pack
- Oracle Expert
- Outline Management
- SQL Analyze
- Tablespace Map
Change Management Pack
- Change Manager

2、Statspack
使用statspack 收集统计信息时建议将该值设置为 TRUE，否则收集的统计信息大约只能起到10%的作用，将timed_statistics 设置为True 所带来的性能影响与好处相比是微不足道的。
用完后设为FALSE。
. Installation of Statspack using the spcreate.sql script
安装过程中会创建一个perfstat的用户，会提示输入密码、默认表空间、临时表空间
如果安装过程中出现错误，那么可以运行spdrop.sql 脚本来删除这些安装脚本建立的对象。
. Collection of statistics execute statspack.snap
语句：begin statspack.snap;end;/--运行一次产生一个快照
内容存储在stats$snapshot视图中。
. Automatic collection of statistics using the spauto.sql script
该脚本的原理是执行一个JOB，定时间隔产生快照
. Produce a report using the spreport.sql script
用perfstat登录，运行脚本生成报表文件
报表内容解析就不详细介绍了，说明见OCP教程。
. To collect timing information, set TIMED_STATISTICS = True

3、Dictionary and Dynamic Views
A、The following dictionary and special views provide
useful statistics after using the dbms_stats package:
. dba_tables, dba_tab_columns：Table data storage within extents and blocks:
. dba_clusters：Cluster data storage within extents and blocks:
. dba_indexes, index_stats：Index data storage within extents and blocks, and indexation usefulness:
. index_histogram, dba_tab_histograms：Non-indexed and indexed columns data distribution:
这些视图中存在的统计信息为DBMS_STAT包执行过后的状态
B、Displaying Systemwide Statistics
. v$sysstat：该系统中包含很多系统状态信息，如redo size。
These statistics are classified by the tuning topic and debugging purpose:
Class 1 refers to general instance activity.
Class 2 refers to redo log buffer activity.
Class 4 refers to locking.
Class 8 refers to database buffer cache activity.
Class 16 refers to OS activity.
Class 32 refers to parallelization.
Class 64 refers to tables access.
Class 128 refers to debugging purposes.
. v$sgastat
. v$event_name:All kinds of wait events are cataloged in
. v$system_event:the total waits for a particular event since instance startup.
C、Displaying Session-Related Statistics
. v$statname和v$sesstat和v$session：display current session information for each user logged on.
Example: Determine the sessions that consume more than 30,000 bytes of PGA memory.
SQL> SELECT username, name, value
2 FROM v$statname n, v$session s, v$sesstat t
3 WHERE s.sid=t.sid
4 AND n.statistic#=t.statistic#
5 AND s.type='USER'
6 AND s.username is not null
7 AND n.name='session pga memory'
8 AND t.value > 30000;
. v$session_event
v$event_name
v$session_wait：lists the resources or events for which active sessions are waiting.
wait_time列值说明：
>0 The session’s last wait time
=0 The session is currently waiting
=-1 The value was less than 1/100 of a second
=-2 The system cannot provide timing information

v$system_event: Total waits for an event, all sessions together
SQL> select *
2 from v$session_event t1,v$event_name t2,v$session_wait t3
3 where t1.SID = t3.SID and t1.EVENT = t2.NAME and t2.NAME = t3.EVENT;
4、Dynamic Troubleshooting and Performance Views
V$ views:
. Based on X$ tables
. Listed in v$fixed_table
. belong to the sys user.
X$ tables:
. Not usually queried directly
. Dynamic and constantly changing
. Names abbreviated and obscure
Populated at startup and cleared at shutdown

5、Troubleshooting and Tuning Views
A、Instance/Database
v$database
v$instance
v$option
v$parameter
v$backup
v$px_process_sysstat：Parallel query system statistics
v$process：Information about currently active processes
v$waitstat：Contention statistics
v$system_event：Total waits for particular events
B、Disk
v$datafile：Data file read/write statistics
v$filestat
v$log
v$log_history
v$dbfile
v$tempfile：Information about file read/write statistics for temporary tablespace data files
v$tempstat
v$segment_statistics：Offers statistics on I/O per segment.
C、Contention
v$lock
v$rollname
v$rollstat：Statistics for all online rollback segments
v$waitstat：Block contention statistics (the TIMED_STATISTICS parameter should be set to True)
v$latch：Statistics for each type of latch
D、Memory
v$buffer_pool_statistics：Buffer pools allocation on the instance
v$db_object_cache：Database objects cached in the library cache
v$librarycache：Library cache performance and activity statistics
v$rowcache：Data dictionary hits and misses activity
v$sysstat：Basic instance statistics
v$sgastat
E、User/Session
v$lock：Locks currently held by the server and outstanding requests for a lock or latch
v$open_cursor：Cursors currently opened and parsed by each session
v$process
v$transaction
v$px_sesstat：Information about the sessions executing SQL statements in parallel
v$px_session
v$sesstat：User session statistics
v$session_event：Information on waits for an event by a session
v$sort_usage：Size of temporary segments and sessions creating them; identification of processes doing disk sorts
v$session_wait：Resources or events for which active sessions are waiting
v$session
v$session_object_cache

6、The utlbstat and utlestat Utilities
是指utlbstat.sql和utlestat.sql这两个脚本，不推荐使用

7、Level of Statistics Collection
The initialization parameters that determine the level of
statistic collection are:
. STATISTICS_LEVEL
可查看视图v$statistics_level，取值如下：
Basic: No advisory or other statistical data is collected. You can manually set other
statistic collection parameters such as TIMED_STATISTICS and DB_CACHE_ADVICE.
Typical: This is the default value. Data is collected for segment level statistics, timed
statistics and all advisories. The value of other statistic collection parameters is overridden.
All: Collection is made of all the Typical level data, the timed operating system
statistics and the row source execution statistics. The value of other statistic collection parameters is overridden.
. TIMED_STATISTICS
. TIMED_OS_STATISTICS：
Specifies the interval (in seconds) at which Oracle
collects operating system statistics when a request is made from the client to the
server or when a request completes.
. DB_CACHE_ADVICE
可查看视图v$db_cache_advice，可选值如下：
- Off
No statistics collected and no memory used.
- Ready
No statistics collected, but memory is allocated. Having DB_CACHE_ADVICE
set to Ready prevents memory errors when needing to collect statistics on
buffer cache utilization.
- On
Statistics collected and memory allocated. Changing the status of
DB_CACHE_ADVICE from Off to On can raise an error if the required memory
is not available.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/594892/viewspace-927383/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/594892/viewspace-927383/