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“並行執行”——監控檢視Oracle並行
- 並行處理 Parallel Processing並行Parallel
- MySQL View 檢視MySqlView
- 並行閘道器 Parallel Gateway並行ParallelGateway
- Oracle Parallel DMLOracleParallel
- Oracle物化檢視的建立及使用（二）Oracle
- Oracle物化檢視的建立及使用（一）Oracle
- 檢視oracle死鎖程式並結束死鎖Oracle
- Oracle資料庫scott使用者建立view檢視許可權Oracle資料庫View
- Oracle檢視執行計劃的命令Oracle
- hive中的 lateral view(側檢視)HiveView
- 【VIEW】Oracle如何查詢固定檢視的定義或底層sql語句ViewOracleSQL
- Oracle並行FAQOracle並行
- 8.0新特性-並行查詢innodb_parallel_read_threads並行Parallelthread
- 詳解PyTorch FSDP資料並行(Fully Sharded Data Parallel)PyTorch並行Parallel
- 【AWR】Oracle awr相關檢視及體系介紹Oracle
- Oracle's Parallel Execution Features(zt)OracleParallel
- Oracle OCP(24)：檢視Oracle
- laravel利用artisan建立view檢視檔案LaravelView
- Oracle如何檢視真實執行計劃（一）Oracle
- parallel: 一個簡單的並行執行Go迴圈的庫Parallel並行Go
- MySQL 8.0新特性-並行查詢innodb_parallel_read_threadsMySql並行Parallelthread
- oracle的並行世界Oracle並行
- Oracle普通檢視和物化檢視的區別Oracle
- Linux 檢視埠占用並Linux
- nancy Unable to find view engine that could render the view 無法找到檢視模板引擎NaNView
- 大型場景中透過監督檢視貢獻加權進行多檢視人物檢測 Multi-View People Detection in Large Scenes via Supervised View-Wise Contribution WeightingView
- 11G R2中的並行執行，dbms_parallel_execute並行Parallel
- 小程式開發基礎-view檢視容器View
- oracle 檢視錶空間Oracle
- 11、Oracle中的檢視Oracle
- C#並行Parallel程式設計模型實戰技巧手冊C#並行Parallel程式設計模型
- 【MEMORY】Oracle記憶體結構資源常用檢視及sqlOracle記憶體SQL
- Oracle檢視sql_id 的歷史執行計劃OracleSQL
- Android日常學習：Android檢視動畫-View AnimationAndroid動畫View
- 什麼是SAP CDS重定向檢視(Redirect View)？View
- 檢視oracle臨時表空間佔用率的檢視Oracle
- 【TUNE_ORACLE】Oracle檢查點（五）建立並利用Statspack定位檢查點故障Oracle
- Oracle檢視歷史TOP SQLOracleSQL