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;

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.

-----查看并行进程相关指标的统计信息,如下各参数的详解:请参阅:
http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_2114.htm
  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

--查询11g官方手册,未查到,google发现如下文档:
http://liusnowc.blog.163.com/blog/static/3578708920127201048863/

小结:
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请参阅:
http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_3020.htm#REFRN30226

-----总共等待次数,最多的事件是与并行有关
  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.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-752258/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-752258/