- 最后登录
- 2015-3-26
- 在线时间
- 148 小时
- 威望
- 84
- 金钱
- 1061
- 注册时间
- 2011-11-26
- 阅读权限
- 50
- 帖子
- 128
- 精华
- 0
- 积分
- 84
- UID
- 96
|
1#
发表于 2013-11-28 13:02:39
|
查看: 5659 |
回复: 16
主机:1个8核心 CPU,lvm(2硬盘),16G 内存
数据库版本以及环境(单机):
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> show parameter parallel
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
parallel_execution_message_size integer 16384
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 160
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_min_time_threshold string AUTO
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_servers_target integer 64
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
奇怪的现象:SQL 不走并行。语句如下。
INSERT INTO /*parallel (t2,6)*/musicdw.songcrosssell_cbf3_201306_01cm t2
SELECT /*+parallel (tt,6)*/tt.msisdn_id,tt.copyright_id,tt.cnt0,tt.cnt1
FROM (SELECT /*+parallel (t1,6)*/t1.msisdn_id,
t1.copyright_id,
t1.cnt0,
t1.cnt1,
ROW_NUMBER ()
OVER (PARTITION BY t1.msisdn_id ORDER BY t1.msisdn_id)
seq_id
FROM (SELECT /*+parallel (a,6),(b,6)*/a.msisdn_id,
b.copyright_id,
chenqy.random_num ('nn') cnt0,
chenqy.random_num ('nn') cnt1
FROM musicdw.user_list a
JOIN musicdw.song_list b ON 1 = 1) t1) tt
WHERE tt.seq_id < MOD (tt.msisdn_id, 211)
AND tt.seq_id > MOD (tt.msisdn_id, 29);
甚至对表启用parallel 但是运行sql后看不到并行状态(v$px_session,linux 进程中无相关并行信息)。但是执行计划中是走并行的。
SQL> explain plan for INSERT INTO /*parallel (t2,6)*/musicdw.songcrosssell_cbf3_201306_01cm t2
SELECT /*+parallel (tt,6)*/tt.msisdn_id,tt.copyright_id,tt.cnt0,tt.cnt1
FROM (SELECT /*+parallel (t1,6)*/t1.msisdn_id,
t1.copyright_id,
t1. 2 3 4 5 cnt0,
t1.cnt1,
ROW_NUMBER ()
OVER (PARTITION BY t1.msisdn_id ORDER BY t1.msisdn_id)
seq_id
FROM (SELECT /*+parallel (a,6),(b,6)*/a.msisdn_id,
6 7 8 9 10 11 b.copyright_id,
chenqy.random_num ('nn') cnt0,
chenqy.random_num ('nn') cnt1
FROM musicdw.user_list a
JOIN musicdw.song_list b ON 1 = 1) t1) tt
WHERE 12 13 14 15 16 tt.seq_id < MOD (tt.msisdn_id, 211)
AND tt.seq_id > MOD (tt.msisdn_id, 29); 17
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2787080057
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 4883M| 17T| | 14M (2)| 74:08:13 | | | |
| 1 | LOAD TABLE CONVENTIONAL | SONGCROSSSELL_CBF3_201306_01CM | | | | | | | | |
| 2 | PX COORDINATOR FORCED SERIAL| | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 4883M| 17T| | 14M (2)| 74:08:13 | Q1,02 | P->S | QC (RAND) |
|* 4 | VIEW | | 4883M| 17T| | 14M (2)| 74:08:13 | Q1,02 | PCWP | |
| 5 | WINDOW SORT | | 4883M| 118G| 145G| 14M (2)| 74:08:13 | Q1,02 | PCWP | |
| 6 | PX RECEIVE | | 4883M| 118G| | 325K (2)| 01:37:39 | Q1,02 | PCWP | |
| 7 | PX SEND HASH | :TQ10001 | 4883M| 118G| | 325K (2)| 01:37:39 | Q1,01 | P->P | HASH |
| 8 | MERGE JOIN CARTESIAN | | 4883M| 118G| | 325K (2)| 01:37:39 | Q1,01 | PCWP | |
| 9 | BUFFER SORT | | | | | | | Q1,01 | PCWC | |
| 10 | PX RECEIVE | | 1000 | 13000 | | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 11 | PX SEND BROADCAST | :TQ10000 | 1000 | 13000 | | 2 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 12 | PX BLOCK ITERATOR | | 1000 | 13000 | | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 13 | TABLE ACCESS FULL| SONG_LIST | 1000 | 13000 | | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 14 | BUFFER SORT | | 4883K| 60M| | 14M (2)| 74:08:13 | Q1,01 | PCWP | |
| 15 | PX BLOCK ITERATOR | | 4883K| 60M| | 221 (3)| 00:00:04 | Q1,01 | PCWC | |
| 16 | TABLE ACCESS FULL | USER_LIST | 4883K| 60M| | 221 (3)| 00:00:04 | Q1,01 | PCWP |
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("TT"."SEQ_ID"<MOD("TT"."MSISDN_ID",211) AND "TT"."SEQ_ID">MOD("TT"."MSISDN_ID",29))
- dynamic sampling used for this statement (level=2)
另一种奇怪的现象:如果单独执行SELECT /*+parallel (t1,6)*/t1.msisdn_id,
t1.copyright_id,
t1.cnt0,
t1.cnt1,
ROW_NUMBER ()
OVER (PARTITION BY t1.msisdn_id ORDER BY t1.msisdn_id)
seq_id
FROM (SELECT /*+parallel (a,6),(b,6)*/a.msisdn_id,
b.copyright_id,
chenqy.random_num ('nn') cnt0,
chenqy.random_num ('nn') cnt1
FROM musicdw.user_list a
JOIN musicdw.song_list b ON 1 = 1) t1
执行计划:
SQL> explain plan for SELECT /*+parallel (t1,6)*/t1.msisdn_id,
t1.copyright_id,
t1.cnt0,
t1.cnt1,
ROW_NUMBER ()
OVER (PARTITION BY t1.msisdn_id ORDER BY t1.msisdn_id)
2 3 4 5 6 7 seq_id
FROM (SELECT /*+parallel (a,6),(b,6)*/a.msisdn_id,
b.copyright_id,
chenqy.random_num ('nn') cnt0,
chenqy.random_num ('nn') cnt1
FROM m 8 9 10 11 12 usicdw.user_list a
JOIN musicdw.song_list b ON 1 = 1) t1 13 ;
SQL> select * from table(dbms_xplan.display());
Plan hash value: 1333373917
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4883M| 118G| | 14M (2)| 74:08:13 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 4883M| 118G| | 14M (2)| 74:08:13 | Q1,02 | P->S | QC (RAND) |
| 3 | WINDOW SORT | | 4883M| 118G| 145G| 14M (2)| 74:08:13 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 4883M| 118G| | 325K (2)| 01:37:39 | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10001 | 4883M| 118G| | 325K (2)| 01:37:39 | Q1,01 | P->P | HASH |
| 6 | MERGE JOIN CARTESIAN | | 4883M| 118G| | 325K (2)| 01:37:39 | Q1,01 | PCWP | |
| 7 | BUFFER SORT | | | | | | | Q1,01 | PCWC | |
| 8 | PX RECEIVE | | 1000 | 13000 | | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10000 | 1000 | 13000 | | 2 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 10 | PX BLOCK ITERATOR | | 1000 | 13000 | | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 11 | TABLE ACCESS FULL| SONG_LIST | 1000 | 13000 | | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 12 | BUFFER SORT | | 4883K| 60M| | 14M (2)| 74:08:13 | Q1,01 | PCWP | |
| 13 | PX BLOCK ITERATOR | | 4883K| 60M| | 221 (3)| 00:00:04 | Q1,01 | PCWC | |
| 14 | TABLE ACCESS FULL | USER_LIST | 4883K| 60M| | 221 (3)| 00:00:04 | Q1,01 | PCWP | |
- dynamic sampling used for this statement (level=2)
第二条语句就可以使用并行查询,很是奇怪不知道为何?
问题:为什么第一条语句不走并行?
在什么情况下SQL不会走并行?
希望热心的朋友帮忙看下,谢谢!
|
|