---学习并行知识并测试
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;
500000 rows created.
Elapsed: 00:00:00.69
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;
499999 rows created.
Elapsed: 00:00:00.63
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;
500000 rows created.
Elapsed: 00:00:00.55
11:36:40 SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
11:36:42 SQL> select count(1) from t_parallel;
COUNT(1)
----------
1499999
Elapsed: 00:00:00.13
11:36:49 SQL>
---------并行创建索引
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;
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
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
15 rows selected.
---经一段时间发现并行进程已经消失,说明oracle已经释放了并行进程占用的资源
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
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;
SID SERIAL# STATUS EVENT P1
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
---查询上述并行等待事件的含义
1* select name,parameter1,parameter2,parameter3 from v$event_name where name i
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
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%');
SID
----------
EVENT
--------------------------------------------------------------------------------
STATE P1 P2 P3
-------------------------------------- ---------- ---------- ----------
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;
Index created.
Elapsed: 00:01:19.25
---如上下为:加大日志后并行创建索引的时间,为何时间更多了
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
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
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
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
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
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
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
EVENT
-------------------------------------------------------------------------------
SQL*Net message to client
23 rows selected.
--通过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.
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.
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.
V$PX_PROCESS --并行进程信息
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.
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.
394
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
