語句級並行提示
最近才發現並行提示增加了語句級並行的功能。
以前新增並行都是對指定的表新增，最近才發現，如果不加表名，是指定這個語句的並行度：
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle語句練習--初級Oracle
- flask之控制語句 if 語句與for語句Flask
- 不同的SQL語句執行時需要申請並持有對應的鎖SQL
- mySQL 執行語句執行順序MySql
- sql語句如何執行的SQL
- Select語句執行順序
- SQL語句執行順序SQL
- MySQL語句執行分析（一）MySql
- MySQL語句執行分析（二）MySql
- 在 PHP 中格式化並高亮 SQL 語句PHPSQL
- 計算節點會對收到的SQL 語句做解析、優化、執行並SQL優化
- Oracle執行語句跟蹤 使用sql trace實現語句追蹤OracleSQL
- Laravel ORM SQL 語句查詢、檢視，附贈 IDE ORM 語法提示LaravelORMSQLIDE
- 查詢Oracle正在執行的sql語句及執行該語句的使用者OracleSQL
- mysql 語句的執行順序MySql
- python怎樣執行js語句PythonJS
- Mybatis 動態執行SQL語句MyBatisSQL
- takes_ownership(s)語句執行
- 後臺執行SQL語句(oracle)SQLOracle
- mysql執行sql語句過程MySql
- sql語句執行緩慢分析SQL
- mysql的sql語句執行流程MySql
- SQL 語句的執行順序SQL
- eslint 效驗 switch case語句縮排警告提示處理EsLint
- 分支語句和迴圈語句
- 【SQL】14 UNION 操作符、SELECT INTO 語句、INSERT INTO SELECT 語句、CREATE DATABASE 語句、CREATE TABLE 語句SQLDatabase
- DM7審計之語句級審計
- if語句
- if 語句
- java連線oracle執行sql語句JavaOracleSQL
- 一條更新語句的執行流程
- Laravel 獲取執行的sql語句LaravelSQL
- MySQL cron定時執行SQL語句MySql
- 【Java】面試官靈魂拷問：if語句執行完else語句真的不會再執行嗎？Java面試
- insert into select語句與select into from語句
- Mybatis原始碼解析之執行SQL語句MyBatis原始碼SQL
- PostgreSQL的insert語句執行過程分析SQL
- MYSQL 中 exists 語句執行效率變低MySql
- 使用 locust 對 mysql 語句進行壓測MySql