Parallel operations downgraded 1 to 25 pct 12
PX local messages sent 74548
PX local messages recv'd 74128
PX remote messages sent 0
PX remote messages recv'd 0
查看px占用的内存信息—该instance没有分配large pool
SQL> select * from v$sgastat where name like 'PX%';
POOL NAME BYTES
------------ -------------------------- ----------
shared pool PX subheap 11651336
shared pool PX msg pool 103310848
shared pool PX QC deq stats 1480
shared pool PX QC msg stats 2288
shared pool PX subheap desc 256
shared pool PX msg pool struct 1088
shared pool PX server deq stats 1480
shared pool PX server msg stats 2288
案例
create table t as select owner, object_name name from dba_objects where owner in ('SYSMAN','ORDSYS','PUBLIC','SYS');
create table m(owner varchar2(20));
insert into m values('SYS');
--收集统计信息
运行select * from t, m where t.owner=m.owner and m.owner='SYS';
当两个表都不开启并行时
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23736 | 2202K| 55 (4)| 00:00:01 |
|* 1 | HASH JOIN | | 23736 | 2202K| 55 (4)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| M | 1 | 12 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T | 23736 | 1923K| 52 (2)| 00:00:01 |
---------------------------------------------------------------------------
只为表M开启并行alter table t parallel 4;
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23213 | 770K| 17 (6)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 23213 | 770K| 17 (6)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 23213 | 770K| 17 (6)| 00:00:01 | Q1,01 | PCWP | |
| 4 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 5 | PX RECEIVE | | 1 | 4 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 6 | PX SEND BROADCAST | :TQ10000 | 1 | 4 | 2 (0)| 00:00:01 | | S->P | BROADCAST |
|* 7 | TABLE ACCESS FULL| M | 1 | 4 | 2 (0)| 00:00:01 | | | |
| 8 | PX BLOCK ITERATOR | | 23213 | 680K| 14 (0)| 00:00:01 | Q1,01 | PCWC | |
|* 9 | TABLE ACCESS FULL | T | 23213 | 680K| 14 (0)| 00:00:01 | Q1,01 | PCWP | |
---------------------------------------------------------------------------------