語句級並行提示
最近才發現並行提示增加了語句級並行的功能。
以前新增並行都是對指定的表新增，最近才發現，如果不加表名，是指定這個語句的並行度：
SQL> create table t_p_i as
2
select *
3
from dba_objects
4
where 1 = 2;
Table created.
SQL> create table t_p_s as
2
select *
3
from dba_objects;
Table created.
SQL> set autot on exp
SQL> insert into t_p_i
2
select *
3
from t_p_s;
13593 rows created.
Execution Plan
----------------------------------------------------------
Plan hash value: 3463104165
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | INSERT STATEMENT |
| 14768 | 2985K| 53
(0)| 00:00:01 |
| 1 |
LOAD TABLE CONVENTIONAL | T_P_I |
| | | |
| 2 |
TABLE ACCESS FULL | T_P_S |
14768 | 2985K| 53
(0)| 00:00:01 |
----------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this
statement (level=2)
SQL> insert into t_p_i
2
select /*+ parallel(t_p_s 4) */ *
3
from t_p_s;
13593 rows created.
Execution Plan
----------------------------------------------------------
Plan hash value: 455351089
----------------------------------------------------------------------------------------
|Id|Operation |Name |Rows |Bytes |Cost (%CPU)| TQ |IN-OUT|PQ Distrib|
----------------------------------------------------------------------------------------
| 0|INSERT STATEMENT | |14768| 2985K| 15
(0)| | |
|
| 1| LOAD TABLE CONVENTIONAL|T_P_I
| | | | |
| |
| 2| PX COORDINATOR |
| | | | |
| |
| 3| PX SEND QC (RANDOM) |:TQ10000|14768| 2985K| 15
(0)| Q1,00| P->S |QC (RAND) |
| 4| PX BLOCK ITERATOR |
|14768| 2985K| 15 (0)| Q1,00| PCWC | |
| 5| TABLE ACCESS FULL |T_P_S
|14768| 2985K| 15 (0)| Q1,00| PCWP | |
----------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this
statement (level=2)
SQL> insert /*+ parallel(t_p_s 4) */ into t_p_i
2
select *
3
from t_p_s;
13593 rows created.
Execution Plan
----------------------------------------------------------
Plan hash value: 3463104165
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | INSERT STATEMENT |
| 14768 | 2985K| 53 (0)| 00:00:01 |
| 1 |
LOAD TABLE CONVENTIONAL | T_P_I |
| | | |
| 2 |
TABLE ACCESS FULL | T_P_S |
14768 | 2985K| 53
(0)| 00:00:01 |
----------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this
statement (level=2)
SQL> commit;
Commit complete.
SQL> alter session enable parallel dml;
Session altered.
SQL> set autot off
SQL> explain plan for
2
insert /*+ parallel(t_p_i 4) */ into t_p_i
3
select *
4
from t_p_s;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 2807692233
---------------------------------------------------------------------------------------
|Id|Operation |Name |Rows |Bytes |Cost (%CPU)| TQ |IN-OUT|PQ Distrib|
--------------------------------------------------------------------------------------
| 0|INSERT STATEMENT | |14768| 2985K| 53
(0)| | |
|
| 1| PX COORDINATOR | |
| | |
| | |
| 2| PX SEND QC (RANDOM) |:TQ10001|14768| 2985K| 53
(0)|Q1,01| P->S |QC (RAND) |
| 3| LOAD AS SELECT |T_P_I
| | | |Q1,01| PCWP | |
| 4| PX RECEIVE | |14768| 2985K| 53
(0)|Q1,01| PCWP | |
| 5| PX SEND
ROUND-ROBIN|:TQ10000|14768| 2985K|
53 (0)| | S->P |RND-ROBIN |
| 6| TABLE ACCESS FULL |T_P_S |14768| 2985K| 53
(0)| | |
|
---------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this
statement (level=2)
17 rows selected.
SQL> explain plan for
2
insert /*+ parallel(t_p_i 4) */ into t_p_i
3
select /*+ parallel(t_p_s 4) */ *
4
from t_p_s;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 2808998595
-------------------------------------------------------------------------------------
|Id|Operation |Name
|Rows |Bytes |Cost (%CPU)| TQ
|IN-OUT|PQ Distrib|
------------------------------------------------------------------------------------
| 0|INSERT STATEMENT | |14768| 2985K| 15
(0)| | |
|
| 1| PX COORDINATOR | |
| | |
| | |
| 2| PX SEND QC (RANDOM)
|:TQ10000|14768| 2985K| 15 (0)|Q1,00| P->S |QC (RAND) |
| 3| LOAD AS SELECT |T_P_I
| | | |Q1,00| PCWP | |
| 4| PX BLOCK ITERATOR | |14768| 2985K| 15
(0)|Q1,00| PCWC | |
| 5| TABLE ACCESS FULL|T_P_S |14768| 2985K| 15
(0)|Q1,00| PCWP | |
-------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this
statement (level=2)
16 rows selected.
SQL> explain plan for
2
insert /*+ parallel(4) */ into t_p_i
3
select *
4
from t_p_s;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 2808998595
-------------------------------------------------------------------------------------
|Id|Operation |Name |Rows |Bytes |Cost (%CPU)| TQ |IN-OUT|PQ Distrib|
-------------------------------------------------------------------------------------
| 0|INSERT STATEMENT | |14768| 2985K| 15
(0)| | |
|
| 1| PX COORDINATOR | |
| | |
| | |
| 2| PX SEND QC (RANDOM)
|:TQ10000|14768| 2985K| 15 (0)|Q1,00| P->S |QC (RAND) |
| 3| LOAD AS SELECT |T_P_I
| | | |Q1,00| PCWP | |
| 4| PX BLOCK ITERATOR | |14768| 2985K| 15
(0)|Q1,00| PCWC | |
| 5| TABLE ACCESS FULL|T_P_S |14768| 2985K| 15
(0)|Q1,00| PCWP | |
-------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this
statement (level=2)
- Degree of Parallelism is 4 because
of hint
17 rows selected.
從這個小例子可以看到，如果指定表級的並行，那麼必須在訪問表的語句中，比如上面的例子中，如果對查詢的表指定並行，將並行的HINT放到INSERT語句中是沒有效果的。
而如果想要INSERT和SELECT同時並行執行，那麼必須在INSERT和SELECT語句中分別指定查詢和插入表的並行度。如果存在多個表的連線，並行設定還會更麻煩。
而透過語句級的並行設定很好的解決了這個問題，透過在第一個命令後新增不帶表名的並行提示，使得這個語句中所有的子句都會使用並行。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-716563/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- RMAN 提示符下執行SQL語句SQL
- php連線mysql並執行sql語句PHPMySql
- alter session enable parallel dml 使DML語句並行執行SessionParallel並行
- 查詢當前正在執行的SQL語句並KILLSQL
- oracle語句練習--初級Oracle
- 關於觸發器在行級和語句級的執行順序問題觸發器
- for語句執行順序
- sql語句批量執行SQL
- FORALL執行DELETE語句delete
- FORALL執行UPDATE語句
- 不同的SQL語句執行時需要申請並持有對應的鎖SQL
- 查詢正在執行的sql語句及該語句執行的時間SQL
- python的with語句，超級強大Python
- mySQL 執行語句執行順序MySql
- flask之控制語句 if 語句與for語句Flask
- MySQL語句執行分析（一）MySql
- MySQL語句執行分析（二）MySql
- sql語句如何執行的SQL
- SQL語句執行順序SQL
- PHP執行批量mysql語句PHPMySql
- FORALL執行UPDATE語句（二）
- 執行大的sql語句SQL
- toad執行sql語句SQL
- 在 PHP 中格式化並高亮 SQL 語句PHPSQL
- 計算節點會對收到的SQL 語句做解析、優化、執行並SQL優化
- JM如何改成幀級並行並行
- Oracle執行語句跟蹤 使用sql trace實現語句追蹤OracleSQL
- 通過分析SQL語句的執行計劃優化SQL語句SQL優化
- 查詢Oracle正在執行的sql語句及執行該語句的使用者OracleSQL
- YCSB擴充套件-語句執行頻率，執行指定的測試查詢語句套件
- ORACLE START WITH 語句的樹級結構例子Oracle
- 強制SQL Server執行計劃使用並行提升在複雜查詢語句下的效能SQLServer並行
- mysql執行sql語句過程MySql
- Mybatis 動態執行SQL語句MyBatisSQL
- mysql的sql語句執行流程MySql
- sql語句執行緩慢分析SQL
- SQL 語句的執行順序SQL
- Select語句執行順序