oracle performance tuning效能優化學習系列(二)
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle performance tuning效能優化學習系列(三)_補二OracleORM優化
- oracle performance tuning效能優化學習系列(三)OracleORM優化
- oracle performance tuning效能優化學習系列(五)OracleORM優化
- oracle performance tuning效能優化學習系列(四)OracleORM優化
- oracle performance tuning效能優化學習系列(一)OracleORM優化
- oracle performance tuning效能優化學習系列(四)_補OracleORM優化
- oracle performance tuning效能優化學習系列(三)_補一OracleORM優化
- Oracle效能優化視訊學習筆記－效能優化概念(二)Oracle優化筆記
- Oracle學習系列—資料庫優化—效能優化工具Oracle資料庫優化
- 效能優化 - Oracle Tuning 總結 1優化Oracle
- 效能優化 - Oracle Tuning 總結 3 優化統計優化Oracle
- 【OCM】Oracle Database 10g: Performance Tuning（二）OracleDatabaseORM
- oracle 學習總結（效能優化）Oracle優化
- oracle 學習筆記---效能優化學習(1)Oracle筆記優化
- 效能優化 - Oracle Tuning 總結 2-2優化Oracle
- Oracle效能優化視訊學習筆記－效能優化概念(一)Oracle優化筆記
- ORACLE學習筆記--效能優化FAQ。Oracle筆記優化
- oracle 學習筆記---效能優化（1）Oracle筆記優化
- oracle 學習筆記---效能優化（2）Oracle筆記優化
- oracle 學習筆記---效能優化（3）Oracle筆記優化
- oracle 學習筆記---效能優化（4）Oracle筆記優化
- oracle 學習筆記---效能優化（5）Oracle筆記優化
- oracle 學習筆記---效能優化（6）Oracle筆記優化
- oracle 學習筆記---效能優化（7）Oracle筆記優化
- Oracle效能優化-SQL優化(案例二)Oracle優化SQL
- 效能優化篇 - Performance（工具 & api）優化ORMAPI
- 效能優化 - Oracle Tuning 總結 2-1 Statspack優化Oracle
- ORACLE SQL效能優化系列 (一)OracleSQL優化
- oracle.Performance.Tuning筆記OracleORM筆記
- Oracle Advanced Performance Tuning ScriptsOracleORM
- Oracle -- Common Performance Tuning IssuesOracleORM
- 《java學習二》jvm效能優化-----認識jvmJavaJVM優化
- ORACLE學習筆記--效能最佳化二Oracle筆記
- rman backup recovery備份恢復效能優化tuning方法二優化
- Spark學習——效能調優（二）Spark
- oracle效能優化二——作業系統優化Oracle優化作業系統
- Oracle SQL效能優化系列介紹OracleSQL優化
- Oracle Advanced Performance Tuning Scripts(轉)OracleORM