oracle性能优化学习系列(二)
处理突发性能故障的方法
1,仔细调查性能问题，并收集相关的统计信息；包含：
a,了解用户的反馈信息，吞吐量和响应时间；
自上次系统良好运行以来到底发生了什么变化，可以收集一些性能统计，比如log file,
b,using addm
2,仔细排查与应用系统相关的硬件，比如内存使用，io usage,network性能；cpu usage;
3,确认数据库服务库发生问题是否与cpu相关或主要等什么资源，如与cpu相关，则：
1,如果在os或服务器级别消耗了大量cpu,检查v$sess_time_model
2,如果会话占用过多的buffer get,可检查v$sesstat or v$sqlstats
3,sql执行计划变更
4,初始化参数配置不合理
5,编码变更导致的一些问题或者升级引起的问题
file:///C:/Users/123/Desktop/每天工作明细/文档/oracle文档/oracle11g官方文档/server.112/e16638/build_db.htm#g27214
4,与性能相关一些初始化参数：
1，sga_target如配置，则db_cache_size,shared_pool_size,large_pool_size,java_pool_size则自动分配其大小；
2，processes非常重要，其它一些参数由此而生；
测试如下：
SQL> show parameter processes
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
aq_tm_processes integer
0
db_writer_processes integer
1
gcs_server_processes integer
0
global_txn_processes integer
1
job_queue_processes integer
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
1000
log_archive_max_processes integer
4
processes integer
100
SQL> show parameter processes
NAME TYPE
------------------------------------ ---------------------
VALUE
------------------------------
aq_tm_processes integer
0
db_writer_processes integer
1
gcs_server_processes integer
0
global_txn_processes integer
1
job_queue_processes integer
NAME TYPE
------------------------------------ ---------------------
VALUE
------------------------------
1000
log_archive_max_processes integer
4
processes integer
90
SQL>
sessions参数源于processes；但如使用shared server,这个自动产生的值就明显配置不足了；
Configuring Undo Space
1，可以用v$undostat包含监控调节undo的纺计信息；以此可以估算当前undo是否合理；
2，v$rollstat包含undo tablespace的undo segment的信息；
Sizing Redo Log Files
1，redo log file size极大影响性能；因为数据库writer and archviver processes依赖于此；
2，如redo log更大，则提升更好的性能；而redo尺寸不足增加检查点活动次数，降低性能；
3，redo log file不会影响lwgr性能，它只会影响dbwr and checkpoint的行为；
4，检查点的频率受几个因素影响：
1，redo log file size
2,fast_start_mttr_target参数配置，如此参数限制实例恢复时间，oracle自动按需发生
检查点。
3，最优的redo log file可由v$instance_recovery之optimal_logfile_size获知；
5，一般情况下自100mb到GB的REDO LOG FILE都是合理的；
6，理想的情形是每隔20分log file切换一次；
示例测试：
fast_start_mttr_target指定实例恢复的花费的秒数；
--此参数为实例参数
SQL> show parameter fast_start_mttr
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_mttr_target integer 0
SQL> alter system set fast_start_mttr_target=10;
System altered
SQL> show parameter fast_start_mttr
SQL> insert into t_instance select level,level from dual connect by level<=1000000;
1000000 rows inserted
SQL> insert into t_instance select level,level from dual connect by level<=1000000;
1000000 rows inserted
SQL> insert into t_instance select level,level from dual connect by level<=1000000;
1000000 rows inserted
SQL> insert into t_instance select level,level from dual connect by level<=1000000;
1000000 rows inserted
SQL> insert into t_instance select level,level from dual connect by level<=1000000;
1000000 rows inserted
SQL> insert into t_instance select level,level from dual connect by level<=1000000;
1000000 rows inserted
SQL> insert into t_instance select level,level from dual connect by level<=1000000;
1000000 rows inserted
SQL> insert into t_instance select level,level from dual connect by level<=1000000;
1000000 rows inserted
SQL> insert into t_instance select level,level from dual connect by level<=1000000;
1000000 rows inserted
SQL> rollback;
Rollback complete
--未提交之前与rollback皆会触发log switch;且rollback与insert所写明的数据可能会处于
不同的log file中；
Thread 1 advanced to log sequence 262 (LGWR switch)
Current log# 3 seq# 262 mem# 0: D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO03.LOG
Thread 1 advanced to log sequence 263 (LGWR switch)
Current log# 4 seq# 263 mem# 0: D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO04.LOG
Sat Mar 09 23:43:40 2013
Starting background process SMCO
Sat Mar 09 23:43:40 2013
SMCO started with pid=25, OS id=8284
Sat Mar 09 23:44:19 2013
Thread 1 advanced to log sequence 264 (LGWR switch)
Current log# 5 seq# 264 mem# 0: D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO05.LOG
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_mttr_target integer 10
--alert提示上述参数配置太低，最小配置应为39
Sat Mar 09 23:23:04 2013
FAST_START_MTTR_TARGET 10 is set too low, using minimum achievable MTTR 39 instead.
--配置为60则mttr建议器关闭；
SQL> alter system set fast_start_mttr_target=60;
System altered
Sat Mar 09 23:30:48 2013
MTTR advisory was temporarily turned off because FAST_START_MTTR_TARGET was altered.
ALTER SYSTEM SET fast_start_mttr_target=60 SCOPE=BOTH;
为了提升性能，可同时运行多个alter tablespace add datafile
tablespace
采用assm即automatic segment space manage,即bitmap segment space management;
Creating Temporary Tablespaces
1，优化排序性能；
2，有字典管理和本地管理方式；
3，推荐uniform. 1m的本地管理方式
--查看临时表空间的文件信息
SQL> select * from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- ------- ------------ -------------- ---------- ---------- ------------ ---------- -----------
D:\ORACLE11G_64BIT\ORADATA\ORCL\TEMP01.DBF 1 TEMP 598736896 73088 ONLINE 1 YES 3435972198 4194302 80 597688320 72960
--创建另一个新的临时表空间
SQL> create temporary tablespace temp_1 tempfile 'c:\new_temp.dbf' size 100m;
Tablespace created
--新创建的临时表空间未分配空间
SQL> select * from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- ------- ------------ -------------- ---------- ---------- ------------ ---------- -----------
D:\ORACLE11G_64BIT\ORADATA\ORCL\TEMP01.DBF 1 TEMP 598736896 73088 ONLINE 1 YES 3435972198 4194302 80 597688320 72960
C:\NEW_TEMP.DBF 2 TEMP_1 104857600 12800 ONLINE 1 NO 0 0 0 103809024 12672
--启用临时表空间为自动扩展，而且新建临时表空间已分配了空间
SQL> alter database tempfile 2 autoextend on;
Database altered
SQL> select * from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- ------- ------------ -------------- ---------- ---------- ------------ ---------- -----------
D:\ORACLE11G_64BIT\ORADATA\ORCL\TEMP01.DBF 1 TEMP 598736896 73088 ONLINE 1 YES 3435972198 4194302 80 597688320 72960
C:\NEW_TEMP.DBF 2 TEMP_1 104857600 12800 ONLINE 1 YES 3435972198 4194302 1 103809024 12672
SQL> alter database default tablespace temp_1;
alter database default tablespace temp_1
ORA-12918: Invalid tablespace type for default permanent tablespace
SQL> alter database default temporary tablespace temp_1;
Database altered
SQL> select * from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- ------- ------------ -------------- ---------- ---------- ------------ ---------- -----------
D:\ORACLE11G_64BIT\ORADATA\ORCL\TEMP01.DBF 1 TEMP 598736896 73088 ONLINE 1 YES 3435972198 4194302 80 597688320 72960
C:\NEW_TEMP.DBF 2 TEMP_1 104857600 12800 ONLINE 1 YES 3435972198 4194302 1 103809024 12672
SQL> desc dba_tablespaces;
Name Type Nullable Default Comments
------------------------ ------------ -------- ------- --------------------------------------------------------------------
TABLESPACE_NAME VARCHAR2(30) Tablespace name
BLOCK_SIZE NUMBER Tablespace block size
INITIAL_EXTENT NUMBER Y Default initial extent size
NEXT_EXTENT NUMBER Y Default incremental extent size
MIN_EXTENTS NUMBER Default minimum number of extents
MAX_EXTENTS NUMBER Y
MAX_SIZE NUMBER Y Default maximum size of segments
PCT_INCREASE NUMBER Y Default percent increase for extent size
MIN_EXTLEN NUMBER Y Minimum extent size for the tablespace
STATUS VARCHAR2(9) Y Tablespace status: "ONLINE", "OFFLINE", or "READ ONLY"
CONTENTS VARCHAR2(9) Y Tablespace contents: "PERMANENT", or "TEMPORARY" ---contents表示表空间为永久存储内容还是临时内容；
LOGGING VARCHAR2(9) Y Default logging attribute
FORCE_LOGGING VARCHAR2(3) Y Tablespace force logging mode
EXTENT_MANAGEMENT VARCHAR2(10) Y Extent management tracking: "DICTIONARY" or "LOCAL" －－extent management
ALLOCATION_TYPE VARCHAR2(9) Y Type of extent allocation in effect for this tablespace --extent分配类型
PLUGGED_IN VARCHAR2(3) Y
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6) Y Segment space management tracking: "AUTO" or "MANUAL" --segment space管理类型,分为auto和manual
DEF_TAB_COMPRESSION VARCHAR2(8) Y Default compression enabled or not: "ENABLED" or "DISABLED" --是否开启压缩功能
RETENTION VARCHAR2(11) Y Undo tablespace retention: "GUARANTEE", "NOGUARANTEE" or "NOT APPLY"
BIGFILE VARCHAR2(3) Y Bigfile tablespace indicator: "YES" or "NO"
PREDICATE_EVALUATION VARCHAR2(7) Y Predicates evaluated by: "HOST" or "STORAGE"
ENCRYPTED VARCHAR2(3) Y Encrypted tablespace indicator: "YES" or "NO"
COMPRESS_FOR VARCHAR2(12) Y Default compression for what kind of operations
--contents列，仅system and sysaux表空间为permanent,而undotbs1为undo,temp和temp_1为temporary
SQL> select * from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MAX_SIZE PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FORCE_LOGGING EXTENT_MANAGEMENT ALLOCATION_TYPE PLUGGED_IN SEGMENT_SPACE_MANAGEMENT DEF_TAB_COMPRESSION RETENTION BIGFILE PREDICATE_EVALUATION ENCRYPTED COMPRESS_FOR
------------------------------ ---------- -------------- ----------- ----------- ----------- ---------- ------------ ---------- --------- --------- --------- ------------- ----------------- --------------- ---------- ------------------------ ------------------- ----------- ------- -------------------- --------- ------------
SYSTEM 8192 65536 1 2147483645 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOT APPLY NO HOST NO
SYSAUX 8192 65536 1 2147483645 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO HOST NO
UNDOTBS1 8192 65536 1 2147483645 2147483645 65536 ONLINE UNDO LOGGING NO LOCAL SYSTEM NO MANUAL DISABLED NOGUARANTEE NO HOST NO
TEMP 8192 1048576 1048576 1 2147483645 0 1048576 ONLINE TEMPORARY NOLOGGING NO LOCAL UNIFORM NO MANUAL DISABLED NOT APPLY NO HOST NO
USERS 8192 65536 1 2147483645 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO HOST NO
SELF_LEARN 8192 65536 1 2147483645 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO HOST NO
T_TBS1 8192 65536 1 2147483645 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO HOST NO
T_TBS2 8192 65536 1 2147483645 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO HOST NO
TBS_HANG 8192 65536 1 2147483645 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO HOST NO
TBS_AUTO 8192 65536 1 2147483645 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO HOST NO
TBS_NON_STANDARD 16384 65536 1 2147483645 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO HOST NO
TBS_MORE_FILE 8192 65536 1 2147483645 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO AUTO DISABLED NOT APPLY NO HOST NO
TEMP_1 8192 1048576 1048576 1 2147483645 0 1048576 ONLINE TEMPORARY NOLOGGING NO LOCAL UNIFORM NO MANUAL DISABLED NOT APPLY NO HOST NO
13 rows selected
Creating Temporary Tablespaces
如使用automatic segment space management即assm,则不用pctused,
4.2.1.1 Estimating the Compression factor
Indexing Data
1，先加载数据后创建索引性能最好；
2，sqlloader采用这种方法
3，可采用parallel创建索引
4，sqlloader仅能手工创建索引
Performance Considerations for Shared Servers
1，采用共享服务器模式，则减少了process数量；
2，共享服务器模式适用于断断续续的进程连接模式；
3,开启并行时，可能会话不能自一个共享服务器切换到另一个共享服务器
4,即便客户端请求一个会话也不能切换；因为并非所有用户信息存储在uga中；
如果一个服务器进程处理源于客户端的请求，存储用户信息不在uga中，为了
避免这种情况，每个共享服务器进程必须和每个用户进程保持对应；
Identifying Contention Using the Dispatcher-Specific Views
1,pga是一个进程占用的空间大小
2,uga是一个会话占用的空间大小
3,如果是专用服务器模式，uga存储在pga中;
如果是共享服务器模式，uga存储在sga中；
而此时如配置了large_pool_size，则uga存储在large_pool中，否则存储在shared_pool中
pga 是一个进程的地址空间,
uga是一个session的使用到的部分内存空间..
如果是decicated server模式, uga是在pga中的, 如果是shared server模式下, uga是在sga中的(这是了large_pool的话, 就在Large-_pool中,否则在shared pool中.)
用调度器相关的视图诊断性能竞争问题
Identifying Contention Using the Dispatcher-Specific Views
--调度器进程的一般信息
SQL> desc v$dispatcher;
Name Type Nullable Default Comments
--------- -------------- -------- ------- --------
NAME VARCHAR2(4) Y
NETWORK VARCHAR2(1024) Y
PADDR RAW(8) Y
STATUS VARCHAR2(16) Y
ACCEPT VARCHAR2(3) Y
MESSAGES NUMBER Y
BYTES NUMBER Y
BREAKS NUMBER Y
OWNED NUMBER Y
CREATED NUMBER Y
IDLE NUMBER Y
BUSY NUMBER Y
LISTENER NUMBER Y
CONF_INDX NUMBER Y
SQL> desc v$dispatcher_rate;
Name Type Nullable Default Comments
-------------------- ----------- -------- ------- --------
NAME VARCHAR2(4) Y
PADDR RAW(8) Y
CUR_LOOP_RATE NUMBER Y
CUR_EVENT_RATE NUMBER Y
CUR_EVENTS_PER_LOOP NUMBER Y
CUR_MSG_RATE NUMBER Y
CUR_SVR_BUF_RATE NUMBER Y
CUR_SVR_BYTE_RATE NUMBER Y
CUR_SVR_BYTE_PER_BUF NUMBER Y
CUR_CLT_BUF_RATE NUMBER Y
CUR_CLT_BYTE_RATE NUMBER Y
CUR_CLT_BYTE_PER_BUF NUMBER Y
CUR_BUF_RATE NUMBER Y
CUR_BYTE_RATE NUMBER Y
CUR_BYTE_PER_BUF NUMBER Y
CUR_IN_CONNECT_RATE NUMBER Y
CUR_OUT_CONNECT_RATE NUMBER Y
CUR_RECONNECT_RATE NUMBER Y
MAX_LOOP_RATE NUMBER Y
MAX_EVENT_RATE NUMBER Y
MAX_EVENTS_PER_LOOP NUMBER Y
MAX_MSG_RATE NUMBER Y
MAX_SVR_BUF_RATE NUMBER Y
MAX_SVR_BYTE_RATE NUMBER Y
MAX_SVR_BYTE_PER_BUF NUMBER Y
MAX_CLT_BUF_RATE NUMBER Y
MAX_CLT_BYTE_RATE NUMBER Y
MAX_CLT_BYTE_PER_BUF NUMBER Y
MAX_BUF_RATE NUMBER Y
MAX_BYTE_RATE NUMBER Y
MAX_BYTE_PER_BUF NUMBER Y
MAX_IN_CONNECT_RATE NUMBER Y
MAX_OUT_CONNECT_RATE NUMBER Y
MAX_RECONNECT_RATE NUMBER Y
AVG_LOOP_RATE NUMBER Y
AVG_EVENT_RATE NUMBER Y
AVG_EVENTS_PER_LOOP NUMBER Y
AVG_MSG_RATE NUMBER Y
AVG_SVR_BUF_RATE NUMBER Y
AVG_SVR_BYTE_RATE NUMBER Y
AVG_SVR_BYTE_PER_BUF NUMBER Y
AVG_CLT_BUF_RATE NUMBER Y
AVG_CLT_BYTE_RATE NUMBER Y
AVG_CLT_BYTE_PER_BUF NUMBER Y
AVG_BUF_RATE NUMBER Y
AVG_BYTE_RATE NUMBER Y
AVG_BYTE_PER_BUF NUMBER Y
AVG_IN_CONNECT_RATE NUMBER Y
AVG_OUT_CONNECT_RATE NUMBER Y
AVG_RECONNECT_RATE NUMBER Y
TTL_LOOPS NUMBER Y
TTL_MSG NUMBER Y
TTL_SVR_BUF NUMBER Y
TTL_CLT_BUF NUMBER Y
TTL_BUF NUMBER Y
TTL_IN_CONNECT NUMBER Y
TTL_OUT_CONNECT NUMBER Y
TTL_RECONNECT NUMBER Y
SCALE_LOOPS NUMBER Y
SCALE_MSG NUMBER Y
SCALE_SVR_BUF NUMBER Y
SCALE_CLT_BUF NUMBER Y
SCALE_BUF NUMBER Y
SCALE_IN_CONNECT NUMBER Y
SCALE_OUT_CONNECT NUMBER Y
SCALE_RECONNECT NUMBER Y
--说明未配置共享服务器模式
SQL> select * from v$dispatcher;
NAME NETWORK PADDR STATUS ACCEPT MESSAGES BYTES BREAKS OWNED CREATED IDLE BUSY LISTENER CONF_INDX
---- -------------------------------------------------------------------------------- ---------------- ---------------- ------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
--此视图显示当前，平均，最大不同种类的统计信息
SQL> select * from v$dispatcher_rate;
NAME PADDR CUR_LOOP_RATE CUR_EVENT_RATE CUR_EVENTS_PER_LOOP CUR_MSG_RATE CUR_SVR_BUF_RATE CUR_SVR_BYTE_RATE CUR_SVR_BYTE_PER_BUF CUR_CLT_BUF_RATE CUR_CLT_BYTE_RATE CUR_CLT_BYTE_PER_BUF CUR_BUF_RATE CUR_BYTE_RATE CUR_BYTE_PER_BUF CUR_IN_CONNECT_RATE CUR_OUT_CONNECT_RATE CUR_RECONNECT_RATE MAX_LOOP_RATE MAX_EVENT_RATE MAX_EVENTS_PER_LOOP MAX_MSG_RATE MAX_SVR_BUF_RATE MAX_SVR_BYTE_RATE MAX_SVR_BYTE_PER_BUF MAX_CLT_BUF_RATE MAX_CLT_BYTE_RATE MAX_CLT_BYTE_PER_BUF MAX_BUF_RATE MAX_BYTE_RATE MAX_BYTE_PER_BUF MAX_IN_CONNECT_RATE MAX_OUT_CONNECT_RATE MAX_RECONNECT_RATE AVG_LOOP_RATE AVG_EVENT_RATE AVG_EVENTS_PER_LOOP AVG_MSG_RATE AVG_SVR_BUF_RATE AVG_SVR_BYTE_RATE AVG_SVR_BYTE_PER_BUF AVG_CLT_BUF_RATE AVG_CLT_BYTE_RATE AVG_CLT_BYTE_PER_BUF AVG_BUF_RATE AVG_BYTE_RATE AVG_BYTE_PER_BUF AVG_IN_CONNECT_RATE AVG_OUT_CONNECT_RATE AVG_RECONNECT_RATE TTL_LOOPS TTL_MSG TTL_SVR_BUF TTL_CLT_BUF TTL_BUF TTL_IN_CONNECT TTL_OUT_CONNECT TTL_RECONNECT SCALE_LOOPS SCALE_MSG SCALE_SVR_BUF SCALE_CLT_BUF SCALE_BUF SCALE_IN_CONNECT SCALE_OUT_CONNECT SCALE_RECONNECT
---- ---------------- ------------- -------------- ------------------- ------------ ---------------- ----------------- -------------------- ---------------- ----------------- -------------------- ------------ ------------- ---------------- ------------------- -------------------- ------------------ ------------- -------------- ------------------- ------------ ---------------- ----------------- -------------------- ---------------- ----------------- -------------------- ------------ ------------- ---------------- ------------------- -------------------- ------------------ ------------- -------------- ------------------- ------------ ---------------- ----------------- -------------------- ---------------- ----------------- -------------------- ------------ ------------- ---------------- ------------------- -------------------- ------------------ ---------- ---------- ----------- ----------- ---------- -------------- --------------- ------------- ----------- ---------- ------------- ------------- ---------- ---------------- ----------------- ---------------
SQL>
1，v$dispatcher_rate此视图可用于评估调度器性能，如当前值接近于平均值，小于最大值；
则为最佳调茺器配置
2，如当前值和平均值远小于最大值，则降低调度器进程个数；
3，如当前值和平均值接近于最大值，则增加调度器进程个数；
4，在系统轻淡和高负荷时，检查此视图，可确定一个合理的调度器配置
--示例
--目前未开启调度器，仅在共享服务器下，方使用调度器
SQL> show parameter dispatch
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
dispatchers string
max_dispatchers integer
SQL> alter system set dispatchers=3 scope=spfile;
System altered.
SQL> alter system set max_dispatchers=3 scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--启动库报调度器参数配置不合理，说明在使用不熟知的参数时，一定要先查明用法方能使用
C:\Users\123>sqlplus sys/system as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 10 17:47:11 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORA-00119: invalid specification for system parameter DISPATCHERS
ORA-00111: invalid attribute X
--配置一个pfile如下
spfile=D:\oracle11g_64bit\product\11.2.0\dbhome_1\database\spfileorcl.ora
DISPATCHERS = '(PROTOCOL=TCP)(DISPATCHERS=3)'
--用上述的pfile启动库
SQL> startup pfile=d:\pfileora
ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size 2180024 bytes
Variable Size 503319624 bytes
Database Buffers 2684354560 bytes
Redo Buffers 16982016 bytes
Database mounted.
Database opened.
--新配置的调度器已生效
SQL> show parameter dispatch
NAME TYPE
------------------------------------ -----------------
VALUE
------------------------------
dispatchers string
(PROTOCOL=TCP)(DISPATCHERS=3)
max_dispatchers integer
3
--开启共享服务器后如下视图已填充数据
SQL> select * from v$dispatcher_rate;
NAME PADDR CUR_LOOP_RATE CUR_EVENT_RATE CUR_EVENTS_PER_LOOP CUR_MSG_RATE CUR_SVR_BUF_RATE CUR_SVR_BYTE_RATE CUR_SVR_BYTE_PER_BUF CUR_CLT_BUF_RATE CUR_CLT_BYTE_RATE CUR_CLT_BYTE_PER_BUF CUR_BUF_RATE CUR_BYTE_RATE CUR_BYTE_PER_BUF CUR_IN_CONNECT_RATE CUR_OUT_CONNECT_RATE CUR_RECONNECT_RATE MAX_LOOP_RATE MAX_EVENT_RATE MAX_EVENTS_PER_LOOP MAX_MSG_RATE MAX_SVR_BUF_RATE MAX_SVR_BYTE_RATE MAX_SVR_BYTE_PER_BUF MAX_CLT_BUF_RATE MAX_CLT_BYTE_RATE MAX_CLT_BYTE_PER_BUF MAX_BUF_RATE MAX_BYTE_RATE MAX_BYTE_PER_BUF MAX_IN_CONNECT_RATE MAX_OUT_CONNECT_RATE MAX_RECONNECT_RATE AVG_LOOP_RATE AVG_EVENT_RATE AVG_EVENTS_PER_LOOP AVG_MSG_RATE AVG_SVR_BUF_RATE AVG_SVR_BYTE_RATE AVG_SVR_BYTE_PER_BUF AVG_CLT_BUF_RATE AVG_CLT_BYTE_RATE AVG_CLT_BYTE_PER_BUF AVG_BUF_RATE AVG_BYTE_RATE AVG_BYTE_PER_BUF AVG_IN_CONNECT_RATE AVG_OUT_CONNECT_RATE AVG_RECONNECT_RATE TTL_LOOPS TTL_MSG TTL_SVR_BUF TTL_CLT_BUF TTL_BUF TTL_IN_CONNECT TTL_OUT_CONNECT TTL_RECONNECT SCALE_LOOPS SCALE_MSG SCALE_SVR_BUF SCALE_CLT_BUF SCALE_BUF SCALE_IN_CONNECT SCALE_OUT_CONNECT SCALE_RECONNECT
---- ---------------- ------------- -------------- ------------------- ------------ ---------------- ----------------- -------------------- ---------------- ----------------- -------------------- ------------ ------------- ---------------- ------------------- -------------------- ------------------ ------------- -------------- ------------------- ------------ ---------------- ----------------- -------------------- ---------------- ----------------- -------------------- ------------ ------------- ---------------- ------------------- -------------------- ------------------ ------------- -------------- ------------------- ------------ ---------------- ----------------- -------------------- ---------------- ----------------- -------------------- ------------ ------------- ---------------- ------------------- -------------------- ------------------ ---------- ---------- ----------- ----------- ---------- -------------- --------------- ------------- ----------- ---------- ------------- ------------- ---------- ---------------- ----------------- ---------------
D000 000007FFBD3BB130 7 7 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 423 2657 1 0 0 1 423 2657 0 0 0 0 0 0 0 0 4 706 0 0 0 0 4 353 0 0 0 60000 1000 100 100 100 60000 60000 60000 6000 100 10 10 10 6000 6000 6000
D001 000007FFBD3BC1A0 21 21 1 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 5 2 640 2657 2 0 0 5 640 2657 0 0 0 0 0 0 0 0 6 278 0 0 0 0 6 139 0 0 0 60000 1000 100 100 100 60000 60000 60000 6000 100 10 10 10 6000 6000 6000
D002 000007FFBD3BD210 17 17 1 3 2 605 336 2 0 0 4 605 173 0 0 0 0 0 1 0 0 0 2657 0 0 0 0 0 2657 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 60000 1000 100 100 100 60000 60000 60000 6000 100 10 10 10 6000 6000 6000
SQL> select * from v$dispatcher;
NAME NETWORK PADDR STATUS ACCEPT MESSAGES BYTES BREAKS OWNED CREATED IDLE BUSY LISTENER CONF_INDX
---- -------------------------------------------------------------------------------- ---------------- ---------------- ------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
D000 (ADDRESS=(PROTOCOL=tcp)(HOST=123-PC)(PORT=54609)) 000007FFBD3BB130 WAIT YES 12 4234 0 0 1 12055 10 0 0
D001 (ADDRESS=(PROTOCOL=tcp)(HOST=123-PC)(PORT=54608)) 000007FFBD3BC1A0 WAIT YES 46 6402 0 1 1 12068 1 0 0
D002 (ADDRESS=(PROTOCOL=tcp)(HOST=123-PC)(PORT=54607)) 000007FFBD3BD210 WAIT YES 47 6844 0 1 1 12067 0 0 0
SQL>
解决调度器竞争的几种方法：
1，增加调度度进程
2，开启连接池功能，系统压力增强，通过增加调度器进程并非提升性能；（相关配置在disptchers参数文档中）
3，开启会话多路化功能multiplexing;即通过一个专用的连接管理进程处理多个会话到调度器的工作；
解决共享服务器的竞争问题
1，可通过如下视图；
SQL> desc v$queue;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
PADDR RAW(8) Y
TYPE VARCHAR2(10) Y
QUEUED NUMBER Y
WAIT NUMBER Y --总的等待时间，所有的等待时间皆存储在此视图中
TOTALQ NUMBER Y --总请求次数
SQL> select * from v$queue;
PADDR TYPE QUEUED WAIT TOTALQ
---------------- ---------- ---------- ---------- ----------
00 COMMON 0 0 6
00 COMMON 0 0 6
00 COMMON 0 2 70
000007FFBD3BB130 DISPATCHER 0 0 6
000007FFBD3BC1A0 DISPATCHER 0 2 29
000007FFBD3BD210 DISPATCHER 0 0 46
6 rows selected
--每个请求平均等待时间
SQL> select decode(totalq,0,'no requests',wait/totalq) as "average wait time per request" from v$queue where type='COMMON';
average wait time per request
----------------------------------------
0
0
.014184397163120567375886524822695035461
--目前正在运行的共享服务器进程数
SQL> select count(*) from v$shared_server where status!='QUIT';
COUNT(*)
----------
1
解决与共享服务器竞争的几则方法：
1，先判断是否为内存竞争问题，检查共享池和大池；
2，再评估如下参数是否合理：
?
MAX_DISPATCHERS
?
MAX_SHARED_SERVERS
?
DISPATCHERS
?
SHARED_SERVERS
--最大的调度器进程
SQL> show parameter max_disp
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_dispatchers integer 3
--最大的共享服务器进程，与上述参数有关
SQL> show parameter max_shared_servers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers integer
--共享服务器进程
SQL> show parameter shared_server
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers integer
shared_server_sessions integer
shared_servers integer 1
--调度器进程
SQL> show parameter dispatch
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string (PROTOCOL=TCP)(DISPATCHERS=3)
max_dispatchers integer 3
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-755696/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-755696/
135
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
