oracle parallel並行及px檢視view
---學習並行知識並測試
SQL> create table t_parallel(a int,b int);
Table created.
SQL> set time on
11:34:20 SQL> set timing on
11:34:25 SQL> insert into t_parallel select level,level from dual connect by lev
el<=5e5;
11:34:20 SQL> set timing on
11:34:25 SQL> insert into t_parallel select level,level from dual connect by lev
el<=5e5;
500000 rows created.
Elapsed: 00:00:00.69
11:34:45 SQL> commit;
11:34:45 SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
11:35:56 SQL> insert into t_parallel select level+500000,level+500000 from dual
connect by level<5e5;
connect by level<5e5;
499999 rows created.
Elapsed: 00:00:00.63
11:36:08 SQL> commit;
11:36:08 SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
11:36:13 SQL> insert into t_parallel select level+1000000,level+1000000 from dua
l connect by level<=5e5;
11:36:13 SQL> insert into t_parallel select level+1000000,level+1000000 from dua
l connect by level<=5e5;
500000 rows created.
Elapsed: 00:00:00.55
11:36:40 SQL> commit;
11:36:40 SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
11:36:42 SQL> select count(1) from t_parallel;
11:36:42 SQL> select count(1) from t_parallel;
COUNT(1)
----------
1499999
----------
1499999
Elapsed: 00:00:00.13
11:36:49 SQL>
11:36:49 SQL>
---------並行建立索引
11:36:49 SQL> create index idx_t_parallel on t_parallel(a) parallel 3;
11:36:49 SQL> create index idx_t_parallel on t_parallel(a) parallel 3;
Index created.
Elapsed: 00:00:10.09
---在另一會話監控並行建立索引時,並行程式的工作狀況
SQL> select server_name,status,pid,spid,sid,serial# from v$px_process;
SERV STATUS PID SPID SID SERIAL#
---- --------- ---------- ------------------------ ---------- ----------
P004 IN USE 32 6600 7 8
P005 IN USE 33 3788 68 15
P000 IN USE 25 5488 70 12
P001 IN USE 29 4176 72 3
P002 IN USE 30 1520 132 30
P003 IN USE 31 7136 197 7
6 rows selected.
---即便不再使用並行,其構建的並行程式依舊存在,只是status變更為available,sid消失
SQL> select server_name,status,pid,spid,sid,serial# from v$px_process;
SQL> select server_name,status,pid,spid,sid,serial# from v$px_process;
SERV STATUS PID SPID SID SERIAL#
---- --------- ---------- ------------------------ ---------- ----------
P001 AVAILABLE 29 4176
P002 AVAILABLE 30 1520
P005 AVAILABLE 33 3788
P000 AVAILABLE 25 5488
P004 AVAILABLE 32 6600
P003 AVAILABLE 31 7136
---- --------- ---------- ------------------------ ---------- ----------
P001 AVAILABLE 29 4176
P002 AVAILABLE 30 1520
P005 AVAILABLE 33 3788
P000 AVAILABLE 25 5488
P004 AVAILABLE 32 6600
P003 AVAILABLE 31 7136
6 rows selected.
-----檢視並行程式相關指標的統計資訊,如下各引數的詳解:請參閱:
1* select * from V$PX_PROCESS_SYSSTAT
STATISTIC VALUE
-------------------------------------------------- ----------
Servers In Use 0
Servers Available 0
Servers Started 9
Servers Shutdown 9
Servers Highwater 6
Servers Cleaned Up 0
Server Sessions 9
Memory Chunks Allocated 4
Memory Chunks Freed 0
Memory Chunks Current 4
Memory Chunks HWM 4
Buffers Allocated 63
Buffers Freed 63
Buffers Current 0
Buffers HWM 51
-------------------------------------------------- ----------
Servers In Use 0
Servers Available 0
Servers Started 9
Servers Shutdown 9
Servers Highwater 6
Servers Cleaned Up 0
Server Sessions 9
Memory Chunks Allocated 4
Memory Chunks Freed 0
Memory Chunks Current 4
Memory Chunks HWM 4
Buffers Allocated 63
Buffers Freed 63
Buffers Current 0
Buffers HWM 51
15 rows selected.
---經一段時間發現並行程式已經消失,說明oracle已經釋放了並行程式佔用的資源
SQL> select * from v$px_process;
SQL> select * from v$px_process;
no rows selected
SQL>
-----再看並行相關的等待事件及最佳化
SQL> explain plan for create index idx_t_parallel on t_parallel(a) parallel 3;
Explained.
---很明顯執行計劃使用了並行
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan hash value: 1914955365
--------------------------------------------------------------------------------
----------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
----------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 266K| 3388K| 380 (1)|
00:00:05 | | | |
| 1 | PX COORDINATOR | | | | |
| | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 266K| 3388K| |
| Q1,01 | P->S | QC (ORDER) |
| 3 | INDEX BUILD NON UNIQUE| IDX_T_PARALLEL | | | |
| Q1,01 | PCWP | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| 4 | SORT CREATE INDEX | | 266K| 3388K| |
| Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 266K| 3388K| 330 (1)|
00:00:04 | Q1,01 | PCWP | |
| 6 | PX SEND RANGE | :TQ10000 | 266K| 3388K| 330 (1)|
00:00:04 | Q1,00 | P->P | RANGE |
| 7 | PX BLOCK ITERATOR | | 266K| 3388K| 330 (1)|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
00:00:04 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| T_PARALLEL | 266K| 3388K| 330 (1)|
00:00:04 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------
----------------------------------------
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
- estimated index size: 7340K bytes
19 rows selected.
---說明explain plan for不會執行語句,僅查詢執行語句的成本
SQL> select index_name from user_indexes where table_name='T_PARALLEL';
no rows selected
------加大測試表資料量
SQL> select count(*) from t_parallel;
COUNT(*)
----------
11999992
----------
11999992
SQL> insert into t_parallel select * from t_parallel;
11999992 rows created.
SQL> commit;
Commit complete.
----並行建立索引
SQL> create index idx_t_parallel on t_parallel(a) parallel 3;
---另一會話監控並行建立發生的等待事件,系統持續出現如下2個等待事件
select sid,serial#,status,event,p1,p2,p3 from v$session where sid=136;
select sid,serial#,status,event,p1,p2,p3 from v$session where sid=136;
SID SERIAL# STATUS EVENT P1
P2 P3
---- ------- ---------- ---------------------------------------- ---------- ----
------ ----------
136 23 ACTIVE enq: KO - fast object checkpoint 1263468550
65558 1
P2 P3
---- ------- ---------- ---------------------------------------- ---------- ----
------ ----------
136 23 ACTIVE enq: KO - fast object checkpoint 1263468550
65558 1
SQL> /
SID SERIAL# STATUS EVENT P1
P2 P3
---- ------- ---------- ---------------------------------------- ---------- ----
------ ----------
136 23 ACTIVE PX Deq: Execute Reply 200
1 0
P2 P3
---- ------- ---------- ---------------------------------------- ---------- ----
------ ----------
136 23 ACTIVE PX Deq: Execute Reply 200
1 0
---查詢上述並行等待事件的含義
1* select name,parameter1,parameter2,parameter3 from v$event_name where name i
n ('enq: KO - fast object checkpoint','PX Deq: Execute Reply')
SQL> /
n ('enq: KO - fast object checkpoint','PX Deq: Execute Reply')
SQL> /
NAME PARAMETER1 PARAMETER2 PARAMET
ER3
------------------------------ -------------------- -------------------- -------
-------------
enq: KO - fast object checkpoint name|mode 2 0
ER3
------------------------------ -------------------- -------------------- -------
-------------
enq: KO - fast object checkpoint name|mode 2 0
PX Deq: Execute Reply sleeptime/senderid passes
小結:
1,此事件即給後臺程式ckpt傳送,進行某物件檢查點動作,而ckpt通知dbwr去作事.
從而等待回覆的事件
2,此事件最終原因dbwr和chkpt
3,toner coder大師的建議:
a,查詢v$session之dbwr and ckpt的sid,program
b,查詢上述sid及progam在v$session_wait之event
c,根據上述查詢到的event
針對性調優
SQL> select sid,event,state,p1,p2,p3,seconds_in_wait from v$session_wait where s
id in (select sid from v$session where program like '%CKPT%' or program like '%D
BW%');
1,此事件即給後臺程式ckpt傳送,進行某物件檢查點動作,而ckpt通知dbwr去作事.
從而等待回覆的事件
2,此事件最終原因dbwr和chkpt
3,toner coder大師的建議:
a,查詢v$session之dbwr and ckpt的sid,program
b,查詢上述sid及progam在v$session_wait之event
c,根據上述查詢到的event
針對性調優
SQL> select sid,event,state,p1,p2,p3,seconds_in_wait from v$session_wait where s
id in (select sid from v$session where program like '%CKPT%' or program like '%D
BW%');
SID
----------
EVENT
--------------------------------------------------------------------------------
----------
EVENT
--------------------------------------------------------------------------------
STATE P1 P2 P3
-------------------------------------- ---------- ---------- ----------
SECONDS_IN_WAIT
---------------
3
control file sequential read
WAITING 0 20 1
0
-------------------------------------- ---------- ---------- ----------
SECONDS_IN_WAIT
---------------
3
control file sequential read
WAITING 0 20 1
0
----------如下為:未加大日誌之前並行建立索引的時間
15:23:22 SQL> create index idx_t_parallel on t_parallel(a) parallel 3;
15:23:22 SQL> create index idx_t_parallel on t_parallel(a) parallel 3;
Index created.
Elapsed: 00:01:19.25
---如上下為:加大日誌後並行建立索引的時間,為何時間更多了
16:09:38 SQL> create index idx_t_parallel on t_parallel(a) parallel 3;
16:09:38 SQL> create index idx_t_parallel on t_parallel(a) parallel 3;
Index created.
Elapsed: 00:01:25.12
-- enq: CR - block range reuse ckpt
--上述進一步說明oracle各個後臺程式相互密切協作工作的重要性,想要tuning一定要全面分析
----v$session_event請參閱:
-----總共等待次數,最多的事件是與並行有關
1* select event from v$session_event where sid=136 order by total_waits desc
1* select event from v$session_event where sid=136 order by total_waits desc
EVENT
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
PX Deq: Execute Reply
control file sequential read
events in waitclass Other
db file sequential read
PX Deq: Parse Reply
PX Deq: Join ACK
log buffer space
db file scattered read
os thread startup
SQL*Net message to client
SQL*Net message from client
control file sequential read
events in waitclass Other
db file sequential read
PX Deq: Parse Reply
PX Deq: Join ACK
log buffer space
db file scattered read
os thread startup
SQL*Net message to client
SQL*Net message from client
EVENT
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Disk file operations I/O
control file parallel write
log file sync
Data file init write
db file single write
log file switch completion
enq: RO - fast object reuse
buffer busy waits
log file switch (checkpoint incomplete)
SQL*Net break/reset to client
enq: KO - fast object checkpoint
control file parallel write
log file sync
Data file init write
db file single write
log file switch completion
enq: RO - fast object reuse
buffer busy waits
log file switch (checkpoint incomplete)
SQL*Net break/reset to client
enq: KO - fast object checkpoint
EVENT
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
write complete waits
23 rows selected.
---總超時次數
SQL> select event from v$session_event where sid=136 order by total_timeouts de
c;
EVENT
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
PX Deq: Execute Reply
events in waitclass Other
control file sequential read
control file parallel write
write complete waits
buffer busy waits
enq: RO - fast object reuse
enq: KO - fast object checkpoint
log buffer space
log file switch (checkpoint incomplete)
log file switch completion
events in waitclass Other
control file sequential read
control file parallel write
write complete waits
buffer busy waits
enq: RO - fast object reuse
enq: KO - fast object checkpoint
log buffer space
log file switch (checkpoint incomplete)
log file switch completion
EVENT
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
log file sync
db file sequential read
db file scattered read
db file single write
PX Deq: Join ACK
PX Deq: Parse Reply
SQL*Net message to client
SQL*Net message from client
SQL*Net break/reset to client
os thread startup
Disk file operations I/O
db file sequential read
db file scattered read
db file single write
PX Deq: Join ACK
PX Deq: Parse Reply
SQL*Net message to client
SQL*Net message from client
SQL*Net break/reset to client
os thread startup
Disk file operations I/O
EVENT
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Data file init write
23 rows selected.
--平均等待時間
SQL> select event from v$session_event where sid=136 order by average_wait desc
EVENT
-------------------------------------------------------------------------------
SQL*Net message from client
enq: KO - fast object checkpoint
log file switch (checkpoint incomplete)
log file switch completion
buffer busy waits
PX Deq: Execute Reply
enq: RO - fast object reuse
log buffer space
control file sequential read
db file sequential read
os thread startup
enq: KO - fast object checkpoint
log file switch (checkpoint incomplete)
log file switch completion
buffer busy waits
PX Deq: Execute Reply
enq: RO - fast object reuse
log buffer space
control file sequential read
db file sequential read
os thread startup
EVENT
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Disk file operations I/O
db file scattered read
log file sync
PX Deq: Parse Reply
events in waitclass Other
control file parallel write
write complete waits
PX Deq: Join ACK
db file single write
SQL*Net break/reset to client
Data file init write
db file scattered read
log file sync
PX Deq: Parse Reply
events in waitclass Other
control file parallel write
write complete waits
PX Deq: Join ACK
db file single write
SQL*Net break/reset to client
Data file init write
EVENT
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
SQL*Net message to client
23 rows selected.
--透過v$session_event可獲知到底這個sid最多在等待什麼資源,然後有針對性解決即可
--透過v$session_event可獲知到底這個sid最多在等待什麼資源,然後有針對性解決即可
--收錄與並行相關的檢視
v$px_buffer_advice
v$px_instance_group
v$px_process
v$px_process_sysstat
v$px_session
v$px_sesstat
其各個註解如下:
V$PX_BUFFER_ADVICE --適用於並行查詢,由此可重配sga,解決並行查詢記憶體不足(歷史統計)
The V$PX_BUFFER_ADVICE view provides statistics on historical and projected maximum buffer usage by all parallel queries.
You can consult this view to reconfigure SGA size in response to insufficient memory problems for parallel queries.
The V$PX_BUFFER_ADVICE view provides statistics on historical and projected maximum buffer usage by all parallel queries.
You can consult this view to reconfigure SGA size in response to insufficient memory problems for parallel queries.
V$PX_SESSION --並行查詢伺服器會話及組相關資訊
The V$PX_SESSION view shows data about query server sessions, groups, sets, and server numbers. It also displays real-time data about
the processes working on behalf of parallel execution. This table includes information about the requested degree of parallelism (DOP)
and the actual DOP granted to the operation.
The V$PX_SESSION view shows data about query server sessions, groups, sets, and server numbers. It also displays real-time data about
the processes working on behalf of parallel execution. This table includes information about the requested degree of parallelism (DOP)
and the actual DOP granted to the operation.
V$PX_SESSTAT --源於v$px_session和v$sesstat,可理解為並行查詢伺服器的各項指標的資訊
The V$PX_SESSTAT view provides a join of the session information from V$PX_SESSION and the V$SESSTAT table. Thus, all session statistics
available to a standard session are available for all sessions performed using parallel execution.
The V$PX_SESSTAT view provides a join of the session information from V$PX_SESSION and the V$SESSTAT table. Thus, all session statistics
available to a standard session are available for all sessions performed using parallel execution.
V$PX_PROCESS --並行程式資訊
The V$PX_PROCESS view contains information about the parallel processes, including status, session ID, process ID, and other information.
The V$PX_PROCESS view contains information about the parallel processes, including status, session ID, process ID, and other information.
V$PX_PROCESS_SYSSTAT --並行伺服器程式資訊及快取分配統計資訊
The V$PX_PROCESS_SYSSTAT view shows the status of query servers and provides buffer allocation statistics.
The V$PX_PROCESS_SYSSTAT view shows the status of query servers and provides buffer allocation statistics.
V$PQ_SESSTAT
The V$PQ_SESSTAT view shows the status of all current server groups in the system such as data about how queries allocate processes and
how the multiuser and load balancing algorithms are affecting the default and hinted values.
The V$PQ_SESSTAT view shows the status of all current server groups in the system such as data about how queries allocate processes and
how the multiuser and load balancing algorithms are affecting the default and hinted values.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-752258/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle並行操作——並行查詢（Parallel Query）Oracle並行Parallel
- ORACLE 並行(PARALLEL)實現方式及優先順序Oracle並行Parallel
- oracle的Parallel 並行技術OracleParallel並行
- oracle parallel並行_引數parameter_parallel_max_serverOracleParallel並行Server
- Oracle“並行執行”——監控檢視Oracle並行
- 並行（Parallel）並行Parallel
- 【Oracle】gv$px_session 檢視介紹OracleSession
- 【Oracle】並行等待之PX Deq: Join ACKOracle並行
- 【Oracle】並行等待之PX Deq: Execution MsgOracle並行
- 【Oracle】並行等待之PX Deq: Execute ReplyOracle並行
- Oracle 檢視sql開幾個並行OracleSQL並行
- 【Oracle】並行等待之PX Deq Credit: need bufferOracle並行
- 【Oracle】並行等待之PX Deq Credit: send blkdOracle並行
- 【Oracle】並行等待之PX Deq: Signal ACKOracle並行
- 【Oracle】並行等待之PX Idle WaitOracle並行AI
- 【Oracle】並行等待之PX Deq: Table Q NormalOracle並行ORM
- Parallel 並行技術Parallel並行
- Oracle資料庫並行機制Parallel ExecutionOracle資料庫並行Parallel
- ORACLE ORA-00020與parallel並行OracleParallel並行
- 【oracle】使用DBMS_PARALLEL_EXECUTE並行更新表OracleParallel並行
- 檢視錶並行度並行
- 刷物化檢視並行並行
- MySQL View 檢視MySqlView
- 並行閘道器 Parallel Gateway並行ParallelGateway
- 並行處理 Parallel Processing並行Parallel
- oracle10g materialized view物化檢視示例OracleZedView
- 檢視並ORACLE的編碼方式Oracle
- materialized view (物化檢視)ZedView
- 【VIEW】Oracle資料字典檢視之DICT_COLUMNSViewOracle
- ORACLE 程式的作用及檢視Oracle
- Oracle物化檢視及SnapshotOracle
- 檢視Oracle鎖並且釋放鎖Oracle
- 檢視並修改oracle最大連線數Oracle
- ORACLE檢視並修改最大連線數Oracle
- MySQL View（檢視）詳解MySqlView
- 檢視所有VIEW/TABLE in MSSQLViewSQL
- 利用檢視 V$FIXED_VIEW_DEFINITION 可以檢視Oracle很多底層的東西ViewOracle
- MYSQL並行複製（parallel replication部署篇）MySql並行Parallel