sql—并行执行未加速exadata中的全表扫描

3lxsmp7m  于 2021-07-29  发布在  Java
关注(0)|答案(2)|浏览(474)

假设我有一张非常大的table，有250m行：

create table example_customers as
select dbms_random.string('x', 100) as first_name
     , dbms_random.string('x', 100) as last_name
  from dual
connect by level <= 250000000;
begin
  dbms_stats.gather_table_stats(user, 'example_customers');
end;

我想用一个简单的查询对这个表进行完整扫描： select count(*) from example_customers ```
PLAN_TABLE_OUTPUT
Plan hash value: 2907982153

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 384K (1)| 00:00:16 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS STORAGE FULL| EXAMPLE_CUSTOMERS | 250M| 384K (1)| 00:00:16 |

然后我决定使用parallel提示来加速这个过程，使用 `select /*+parallel(10)*/ count(*) from example_customers` ```
Plan hash value: 2126708148

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |     1 | 42744   (1)| 00:00:02 |        |      |            |
|   1 |  SORT AGGREGATE                |                   |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR               |                   |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)         | :TQ10000          |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE             |                   |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR         |                   |   250M| 42744   (1)| 00:00:02 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS STORAGE FULL| EXAMPLE_CUSTOMERS |   250M| 42744   (1)| 00:00:02 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------

乍一看，这看起来是一个很大的改进—第一条语句的总开销是384k，新语句的总开销是43k，这向我表明并行执行语句的开销是并行运行的10个单独操作中的最高值，再加上一些额外的并行执行开销。
然而，当我实际运行这些语句时，我看不到任何时间差——它们都需要大约35秒。为什么第二种说法不快？
我有一些理论，但我不知道如何验证其中哪一个（如果有的话）是原因：
10个单独的并行操作中的每一个都在对表进行完全扫描（如xplan所示，xplan列出了返回的250m行）
第一个查询已经达到了数据库在所有操作中读取数据的速度限制，因此第二个查询仍然受到这个限制
并行操作被强制为串行操作（在这种情况下，我希望看到 PX COORDINATOR FORCED SERIAL 而不是 PX COORDINATOR 附加信息
不带提示的查询的sql监视器报告：

SQL Monitoring Report

SQL Text
------------------------------
select count(*) from example_customers

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)                   
 Instance ID         :  1                                 
 Session             :  TESTING (2719:63368)              
 SQL ID              :  2v9j9nz9748xq                     
 SQL Execution ID    :  16777221                          
 Execution Started   :  07/27/2020 13:51:26               
 First Refresh Time  :  07/27/2020 13:51:30               
 Last Refresh Time   :  07/27/2020 13:52:01               
 Duration            :  35s                               
 Module/Action       :  PL/SQL Developer/SQL Window - New 
 Service             :  UCL                               
 Program             :  plsqldev.exe                      
 Fetch Calls         :  1                                 

Global Stats
=============================================================================================================
| Elapsed |   Cpu   |    IO    | Application | Fetch | Buffer | Read  | Read  |  Offload   |    Offload     |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Calls |  Gets  | Reqs  | Bytes | Elig Bytes | Returned Bytes |
=============================================================================================================
|      36 |      34 |     1.39 |        0.00 |     1 |     1M | 11086 |  11GB |       11GB |           11GB |
=============================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=2907982153)
=======================================================================================================================================================================
| Id |          Operation           |       Name        |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read  | Read  |  Mem  | Activity | Activity Detail |
|    |                              |                   | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs  | Bytes | (Max) |   (%)    |   (# samples)   |
=======================================================================================================================================================================
|  0 | SELECT STATEMENT             |                   |         |      |        32 |     +4 |     1 |        1 |       |       |     . |          |                 |
|  1 |   SORT AGGREGATE             |                   |       1 |      |        32 |     +4 |     1 |        1 |       |       |     . |          |                 |
|  2 |    TABLE ACCESS STORAGE FULL | EXAMPLE_CUSTOMERS |    250M | 385K |        36 |     +0 |     1 |     250M | 11086 |  11GB |   7MB |          |                 |
=======================================================================================================================================================================

带有以下提示的查询的sql监视器报告：

SQL Monitoring Report

SQL Text
------------------------------
select /*+parallel(10)*/ count(*) from example_customers

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)                   
 Instance ID         :  1                                 
 Session             :  TESTING (1937:31657)              
 SQL ID              :  882dpyfj3hx4m                     
 SQL Execution ID    :  16777216                          
 Execution Started   :  07/27/2020 13:51:20               
 First Refresh Time  :  07/27/2020 13:51:24               
 Last Refresh Time   :  07/27/2020 13:51:55               
 Duration            :  35s                               
 Module/Action       :  PL/SQL Developer/SQL Window - New 
 Service             :  UCL                               
 Program             :  plsqldev.exe                      
 DOP Downgrade       :  100%                              
 Fetch Calls         :  1                                 

Global Stats
=============================================================================================================
| Elapsed |   Cpu   |    IO    | Application | Fetch | Buffer | Read  | Read  |  Offload   |    Offload     |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Calls |  Gets  | Reqs  | Bytes | Elig Bytes | Returned Bytes |
=============================================================================================================
|      36 |      35 |     1.32 |        0.00 |     1 |     1M | 11086 |  11GB |       11GB |           11GB |
=============================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=2126708148)
============================================================================================================================================================================
| Id |            Operation             |       Name        |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read  | Read  |  Mem  | Activity | Activity Detail |
|    |                                  |                   | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs  | Bytes | (Max) |   (%)    |   (# samples)   |
============================================================================================================================================================================
|  0 | SELECT STATEMENT                 |                   |         |       |         1 |    +35 |     1 |        1 |       |       |     . |          |                 |
|  1 |   SORT AGGREGATE                 |                   |       1 |       |         1 |    +35 |     1 |        1 |       |       |     . |          |                 |
|  2 |    PX COORDINATOR                |                   |         |       |           |        |     1 |          |       |       |     . |          |                 |
|  3 |     PX SEND QC (RANDOM)          | :TQ10000          |       1 |       |         1 |    +35 |     1 |        0 |       |       |     . |          |                 |
|  4 |      SORT AGGREGATE              |                   |       1 |       |        32 |     +4 |     1 |        1 |       |       |     . |          |                 |
|  5 |       PX BLOCK ITERATOR          |                   |    250M | 42744 |        32 |     +4 |     1 |     250M |       |       |     . |          |                 |
|  6 |        TABLE ACCESS STORAGE FULL | EXAMPLE_CUSTOMERS |    250M | 42744 |        36 |     +0 |     1 |     250M | 11086 |  11GB |   7MB |          |                 |
============================================================================================================================================================================

更多信息
如果exadata智能扫描被禁用： select /*+ OPT_PARAM('cell_offload_processing' 'false') PARALLEL(10) */ count(*) from example_customers; ```
SQL Monitoring Report

SQL Text

select /*+ OPT_PARAM('cell_offload_processing' 'false') PARALLEL(10) / count() from example_customers

Global Stats

| Elapsed | Cpu | IO | Application | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |

| 38 | 36 | 2.04 | 0.00 | 0.11 | 1 | 1M | 11086 | 11GB |

SQL Plan Monitoring Details (Plan Hash Value=2126708148)

| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |

| 0 | SELECT STATEMENT | | | | 1 | +38 | 1 | 1 | | | | |
| 1 | SORT AGGREGATE | | 1 | | 1 | +38 | 1 | 1 | | | | |
| 2 | PX COORDINATOR | | | | | | 1 | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | 1 | +38 | 1 | 0 | | | | |
| 4 | SORT AGGREGATE | | 1 | | 35 | +4 | 1 | 1 | | | | |
| 5 | PX BLOCK ITERATOR | | 250M | 42744 | 35 | +4 | 1 | 250M | | | | |
| 6 | TABLE ACCESS STORAGE FULL | EXAMPLE_CUSTOMERS | 250M | 42744 | 38 | +1 | 1 | 250M | 11086 | 11GB | | |

如果我对group by使用更复杂的查询，那么我会看到一些改进（71秒到61秒），但仍然比我预期的要少很多：

SQL Monitoring Report

SQL Text

select substr(surname, 1, 1) , count(*) from example_customers group by substr(surname, 1, 1) order by substr(surname, 1, 1)

Global Stats

| Elapsed | Cpu | IO | Application | Fetch | Buffer | Read | Read | Offload | Offload |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Elig Bytes | Returned Bytes |

| 71 | 70 | 1.39 | 0.00 | 1 | 1M | 11086 | 11GB | 11GB | 11GB |

SQL Plan Monitoring Details (Plan Hash Value=525074000)

| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |

| 0 | SELECT STATEMENT | | | | 66 | +6 | 1 | 5 | | | . | | |
| 1 | SORT GROUP BY | | 4 | 393K | 69 | +3 | 1 | 5 | | | 2048 | | |
| 2 | TABLE ACCESS STORAGE FULL | EXAMPLE_CUSTOMERS | 250M | 385K | 71 | +1 | 1 | 250M | 11086 | 11GB | 7MB | | |

SQL Monitoring Report

SQL Text

select /*+ parallel(10) /substr(surname, 1, 1) , count() from example_customers group by substr(surname, 1, 1) order by substr(surname, 1, 1)

Global Stats

| Elapsed | Cpu | IO | Application | Fetch | Buffer | Read | Read | Offload | Offload |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Elig Bytes | Returned Bytes |

| 61 | 60 | 1.36 | 0.00 | 1 | 1M | 11086 | 11GB | 11GB | 11GB |

SQL Plan Monitoring Details (Plan Hash Value=3312522119)

| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |

| 0 | SELECT STATEMENT | | | | | | 1 | | | | . | | |
| 1 | PX COORDINATOR | | | | | | 1 | | | | . | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 4 | 43519 | 1 | +61 | 1 | 0 | | | . | | |
| 3 | SORT GROUP BY | | 4 | 43519 | 1 | +61 | 1 | 5 | | | 2048 | | |
| 4 | PX RECEIVE | | 4 | 43519 | | | 1 | | | | . | | |
| 5 | PX SEND RANGE | :TQ10000 | 4 | 43519 | 1 | +61 | 1 | 0 | | | . | | |
| 6 | HASH GROUP BY | | 4 | 43519 | 58 | +4 | 1 | 5 | | | 3MB | | |
| 7 | PX BLOCK ITERATOR | | 250M | 42771 | 58 | +4 | 1 | 250M | | | . | | |
| 8 | TABLE ACCESS STORAGE FULL | EXAMPLE_CUSTOMERS | 250M | 42771 | 61 | +1 | 1 | 250M | 11086 | 11GB | 7MB | | |

如果我们使用一个包含大量排序操作的查询，那么我们会发现并行执行实际上运行得比较慢：

SQL Monitoring Report

SQL Text

select first_name , surname , row_number() over (partition by first_name order by surname asc) rn1 , row_number() over (partition by first_name order by surname desc) rn2 , row_number() over (partition by surname order by first_name asc) rn3 , row_number() over (partition by surname order by first_name desc) rn4 from example_customers

Global Stats

| Elapsed | Cpu | IO | Application | Fetch | Buffer | Read | Read | Write | Write | Offload | Offload |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes | Elig Bytes | Returned Bytes |

| 838 | 688 | 150 | 0.00 | 1 | 1M | 59275 | 34GB | 58982 | 34GB | 11GB | 45GB |

SQL Plan Monitoring Details (Plan Hash Value=3818639180)

| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) |

| 0 | SELECT STATEMENT | | | | 246 | +592 | 1 | 100 | | | | | . | . | | |
| 1 | WINDOW SORT | | 250M | 6M | 246 | +592 | 1 | 100 | 1 | 1MB | 18233 | 11GB | 1GB | 11GB | | |
| 2 | WINDOW SORT | | 250M | 6M | 493 | +345 | 1 | 250M | 19507 | 10GB | 14026 | 10GB | 1GB | 10GB | | |
| 3 | WINDOW SORT | | 250M | 6M | 473 | +118 | 1 | 250M | 16086 | 8GB | 15230 | 8GB | 1GB | 8GB | | |
| 4 | WINDOW SORT | | 250M | 6M | 346 | +0 | 1 | 250M | 12595 | 6GB | 11493 | 6GB | 1GB | 6GB | | |
| 5 | TABLE ACCESS STORAGE FULL | EXAMPLE_CUSTOMERS | 250M | 385K | 115 | +2 | 1 | 250M | 11086 | 11GB | | | 7MB | . | | |

SQL Monitoring Report

SQL Text

select /*+ parallel(10) */ first_name , surname , row_number() over (partition by first_name order by surname asc) rn1 , row_number() over (partition by first_name order by surname desc) rn2 , row_number() over (partition by surname order by first_name asc) rn3 , row_number() over (partition by surname order by first_name desc) rn4 from example_customers

Global Stats

| Elapsed | Cpu | IO | Application | Fetch | Buffer | Read | Read | Write | Write | Offload | Offload |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes | Elig Bytes | Returned Bytes |

| 919 | 747 | 172 | 0.00 | 1 | 1M | 116K | 40GB | 72314 | 40GB | 11GB | 51GB |

SQL Plan Monitoring Details (Plan Hash Value=2906577827)

| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) |

| 0 | SELECT STATEMENT | | | | 1 | +435 | 1 | 0 | | | | | . | . | | |
| 1 | PX COORDINATOR | | | | | | 1 | | | | | | . | . | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | 250M | 672K | 1 | +917 | 1 | 0 | | | | | . | . | | |
| 3 | WINDOW SORT | | 250M | 672K | 234 | +684 | 1 | 100 | 1 | 1MB | 18234 | 11GB | 1GB | 11GB | | |
| 4 | WINDOW SORT | | 250M | 672K | 466 | +452 | 1 | 250M | 19507 | 10GB | 16146 | 10GB | 1GB | 10GB | | |
| 5 | PX RECEIVE | | 250M | 672K | | | 1 | | | | | | . | . | | |
| 6 | PX SEND HASH | :TQ10002 | 250M | 672K | 236 | +447 | 1 | 0 | | | | | . | . | | |
| 7 | WINDOW SORT | | 250M | 672K | 480 | +203 | 1 | 250M | 16086 | 8GB | 16015 | 8GB | 1GB | 8GB | | |
| 8 | PX RECEIVE | | 250M | 672K | | | 1 | | | | | | . | . | | |
| 9 | PX SEND HASH | :TQ10001 | 250M | 672K | 245 | +203 | 1 | 0 | | | | | . | . | | |
| 10 | WINDOW SORT | | 250M | 672K | 447 | +1 | 1 | 250M | 69191 | 12GB | 21919 | 12GB | 1GB | 6GB | | |
| 11 | PX RECEIVE | | 250M | 42771 | | | 1 | | | | | | . | . | | |
| 12 | PX SEND RANGE | :TQ10000 | 250M | 42771 | 115 | +4 | 1 | 0 | | | | | . | . | | |
| 13 | PX BLOCK ITERATOR | | 250M | 42771 | 115 | +4 | 1 | 250M | | | | | . | . | | |
| 14 | TABLE ACCESS STORAGE FULL | EXAMPLE_CUSTOMERS | 250M | 42771 | 115 | +4 | 1 | 250M | 11086 | 11GB | | | 7MB | . | | |
============================================================================================================================================================================================================```

sqloracleexadata

2条答案

按热度按时间
qlvxas9a

qlvxas9a1#

我测试你的场景，让我给你看看。由于内存不足，我无法使用“按级别连接”，但我插入了50m条记录，这足以进行比较

SQL> select count(*) from test_perf.example_customers ;

  COUNT(*)
----------
   5000000

Elapsed: 00:00:02.15
SQL> desc test_perf.example_customers
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 VARCHAR2(120 CHAR)
 C2                                                 VARCHAR2(120 CHAR)

SQL>
SQL>
SQL> alter table test_perf.example_customers noparallel ;

Table altered.

Elapsed: 00:00:00.01
SQL>

现在，让我们看看这两个计划，有没有平行

SQL> set autotrace traceonly explain
SQL> select count(*) from test_perf.example_customers ;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2907982153

--------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |     1 | 24110   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE    |                   |     1 |            |          |
|   2 |   TABLE ACCESS FULL| EXAMPLE_CUSTOMERS |  5000K| 24110   (1)| 00:00:02 |
--------------------------------------------------------------------------------

SQL> select /*+parallel(a,10) */ count(*) from test_perf.example_customers a
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2126708148

 -----------------------------------------------------------------------------------------------------------------
    | Id  | Operation              | Name              | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
    -----------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT       |                   |     1 |  2677   (1)| 00:00:01 |        |      |            |
    |   1 |  SORT AGGREGATE        |                   |     1 |            |          |        |      |            |
    |   2 |   PX COORDINATOR       |                   |       |            |          |        |      |            |
    |   3 |    PX SEND QC (RANDOM) | :TQ10000          |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
    |   4 |     SORT AGGREGATE     |                   |     1 |            |          |  Q1,00 | PCWP |            |
    |   5 |      PX BLOCK ITERATOR |                   |  5000K|  2677   (1)| 00:00:01 |  Q1,00 | PCWC |            |
    |   6 |       TABLE ACCESS FULL| EXAMPLE_CUSTOMERS |  5000K|  2677   (1)| 00:00:01 |  Q1,00 | PCWP |            |
    -----------------------------------------------------------------------------------------------------------------

    Note
    -----
       - Degree of Parallelism is 10 because of table property

    SQL>

现在让我们看看到底发生了什么

SQL> set autotrace traceonly
SQL> select count(*) from test_perf.example_customers ;

Elapsed: 00:00:00.45

Execution Plan
----------------------------------------------------------
Plan hash value: 2907982153

--------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |     1 | 24110   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE    |                   |     1 |            |          |
|   2 |   TABLE ACCESS FULL| EXAMPLE_CUSTOMERS |  5000K| 24110   (1)| 00:00:02 |
--------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      68305  consistent gets
          0  physical reads
     358820  redo size
        542  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace traceonly
SQL> select /*+parallel(a,10) */ count(*) from test_perf.example_customers a
  2  ;

Elapsed: 00:00:01.55

Execution Plan
----------------------------------------------------------
Plan hash value: 2126708148

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |     1 |  2677   (1)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE        |                   |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |                   |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000          |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |                   |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |                   |  5000K|  2677   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| EXAMPLE_CUSTOMERS |  5000K|  2677   (1)| 00:00:01 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 10 because of table property

Statistics
----------------------------------------------------------
         31  recursive calls
          0  db block gets
      91667  consistent gets
      64207  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

让我们刷新缓冲区缓存和共享池，然后重试

SQL> select count(*) from test_perf.example_customers ;

  COUNT(*)
----------
   5000000

Elapsed: 00:00:01.44
SQL> select /*+parallel(a,10) */ count(*) from test_perf.example_customers a
  2  ;

  COUNT(*)
----------
   5000000

Elapsed: 00:00:00.66
SQL>

有100米记录

SQL> select count(*) from test_perf.example_customers ;

  COUNT(*)
----------
  10000000

Elapsed: 00:00:11.00
SQL> select /*+parallel(a,16) */  count(*) from test_perf.example_customers a ;

  COUNT(*)
----------
  10000000

Elapsed: 00:00:02.71
SQL>

更新

用平行度策略自动

SQL> alter table test_perf.example_customers noparallel ;

Table altered.

SQL> alter session set parallel_degree_policy=auto ;

Session altered.

SQL> alter session enable parallel query;

Session altered.

SQL> set autotrace traceonly explain
SQL> select /*+parallel */ count(*) from test_perf.example_customers a ;

Execution Plan
----------------------------------------------------------
Plan hash value: 2126708148

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |     1 | 13387   (1)| 00:00:02 |        |      |            |
|   1 |  SORT AGGREGATE        |                   |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |                   |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000          |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |                   |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |                   |  5000K| 13387   (1)| 00:00:02 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| EXAMPLE_CUSTOMERS |  5000K| 13387   (1)| 00:00:02 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
   - parallel scans affinitized for buffer cache

正如你所看到的，50米排的时候你会注意到这种差别，100米排的差别更大。我可以向您保证，在正常情况下，与可序列化查询相比，表越大，并行执行速度就越快。exadata是另一个世界。exadata的智能扫描和单元卸载功能在速度非常快的磁盘上运行，您不会注意到高速i/o带来的差异。key osborne有一篇惊人的文章，其中描述了exadata中存储服务器的惊人功能以及存储索引。
http://kerryosborne.oracle-guy.com/2010/08/10/oracle-exadata-storage-indexes/
在2.5亿行中，您不会注意到，这可能是因为oracle在创建qc和从机时花费的时间，而且它们之间的通信（即内存）与exadata存储层在读取查询时花费的时间大致相同。
抱歉，我无法在exadata中测试；）

赞(0）分享  回复(0）举报  2021-07-29
ukqbszuj

ukqbszuj2#

我发现了根本问题，涉及的步骤是：
sql监视器报表报告尝试使用并行执行的dop降级率为100%
使用dbms\u sqltune.report\u sql\u detail，我能够获得有关执行计划中步骤的附加信息
使用这个，我能够得到px协调器操作的dop降级原因（352）
352有时意味着进程数量不足，但有许多可用的px进程
352有时意味着资源管理器有一个最大dop限制
资源管理器有几个计划，其中一些计划的并行度限制为1英寸 dba_rsrc_plan_directives 通过交换会话的使用者组，并行提示可以按预期工作
并行执行的查询的执行时间从35秒减少到3秒以下
使用的代码：

begin
  dbms_resource_manager.switch_consumer_group_for_sess(session_id => 2690
                                                      ,session_serial => 25388
                                                      ,consumer_group => 'HIGH'
                                                      );

end;
select /*+ parallel */ count(*)
  from EXAMPLE_CUSTOMERS

生成的sql报表：

SQL Monitoring Report

SQL Text
------------------------------
select /*+ parallel */ count(*) from EXAMPLE_CUSTOMERS

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)                   
 Instance ID         :  1                                 
 Session             :  TESTING (2690:25388)              
 SQL ID              :  cr2th8jckamab                     
 SQL Execution ID    :  16777219                          
 Execution Started   :  07/28/2020 00:05:19               
 First Refresh Time  :  07/28/2020 00:05:18               
 Last Refresh Time   :  07/28/2020 00:05:21               
 Duration            :  2s                                
 Module/Action       :  PL/SQL Developer/SQL Window - New 
 Service             :  UCL                               
 Program             :  plsqldev.exe                      
 Fetch Calls         :  1                                 

Global Stats
======================================================================================================================================
| Elapsed |   Cpu   |    IO    | Application | Concurrency |  Other   | Fetch | Buffer | Read  | Read  |  Offload   |    Offload     |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) | Calls |  Gets  | Reqs  | Bytes | Elig Bytes | Returned Bytes |
======================================================================================================================================
|      51 |      36 |     3.08 |        0.00 |        0.00 |       12 |     1 |     1M | 11137 |  11GB |       11GB |           11GB |
======================================================================================================================================

Parallel Execution Details (DOP=25 , Servers Allocated=25)
 Instances  : 2 

============================================================================================================================================================================================
| Instance |      Name      | Type  | Server# | Elapsed |   Cpu   |    IO    | Application | Concurrency |  Other   | Buffer | Read | Read  |  Offload   |    Offload     | Wait Events |  |
|          |                |       |         | Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) |  Gets  | Reqs | Bytes | Elig Bytes | Returned Bytes | (sample #)  |  |
============================================================================================================================================================================================
| 1        | PX Coordinator | QC    |         |    0.48 |    0.05 |          |        0.00 |             |     0.43 |      7 |      |     . |          . |              . |        NaN% |  |
| 1        | p000           | Set 1 |       1 |    1.97 |    1.39 |     0.12 |             |        0.00 |     0.47 |  56158 |  440 | 437MB |      437MB |          437MB |        NaN% |  |
| 1        | p001           | Set 1 |       2 |    2.09 |    1.45 |     0.13 |             |             |     0.51 |  56563 |  443 | 440MB |      440MB |          440MB |        NaN% |  |
| 1        | p002           | Set 1 |       3 |    1.94 |    1.38 |     0.11 |             |             |     0.45 |  54215 |  424 | 422MB |      422MB |          422MB |        NaN% |  |
| 1        | p003           | Set 1 |       4 |    2.05 |    1.19 |     0.11 |             |        0.00 |     0.74 |  44952 |  355 | 350MB |      350MB |          350MB |        NaN% |  |
| 1        | p004           | Set 1 |       5 |    2.09 |    1.47 |     0.13 |             |             |     0.49 |  57279 |  448 | 446MB |      446MB |          446MB |        NaN% |  |
| 1        | p005           | Set 1 |       6 |    2.09 |    1.41 |     0.11 |             |        0.00 |     0.57 |  54826 |  428 | 427MB |      427MB |          427MB |        NaN% |  |
| 1        | p006           | Set 1 |       7 |    2.13 |    1.16 |     0.10 |             |             |     0.87 |  46373 |  363 | 361MB |      361MB |          361MB |        NaN% |  |
| 1        | p007           | Set 1 |       8 |    2.18 |    1.18 |     0.13 |             |             |     0.87 |  46237 |  361 | 360MB |      360MB |          360MB |        NaN% |  |
| 1        | p008           | Set 1 |       9 |    2.10 |    1.39 |     0.13 |             |             |     0.59 |  55364 |  433 | 431MB |      431MB |          431MB |        NaN% |  |
| 1        | p009           | Set 1 |      10 |    2.11 |    1.48 |     0.12 |             |        0.00 |     0.51 |  59575 |  465 | 464MB |      464MB |          464MB |        NaN% |  |
| 1        | p00a           | Set 1 |      11 |    2.02 |    1.21 |     0.10 |             |             |     0.72 |  45981 |  360 | 358MB |      358MB |          358MB |        NaN% |  |
| 1        | p00b           | Set 1 |      12 |    1.98 |    1.20 |     0.11 |             |             |     0.67 |  48583 |  382 | 378MB |      378MB |          378MB |        NaN% |  |
| 1        | p00c           | Set 1 |      13 |    2.03 |    1.36 |     0.13 |             |             |     0.54 |  53580 |  421 | 417MB |      417MB |          417MB |        NaN% |  |
| 1        | p00d           | Set 1 |      14 |    2.17 |    1.22 |     0.10 |             |             |     0.85 |  48413 |  380 | 377MB |      377MB |          377MB |        NaN% |  |
| 1        | p00e           | Set 1 |      15 |    2.04 |    1.17 |     0.10 |             |             |     0.77 |  47261 |  370 | 368MB |      368MB |          368MB |        NaN% |  |
| 1        | p00f           | Set 1 |      16 |    2.07 |    1.18 |     0.11 |             |             |     0.79 |  46365 |  363 | 361MB |      361MB |          361MB |        NaN% |  |
| 2        | p000           | Set 1 |      17 |    1.89 |    1.52 |     0.12 |             |             |     0.25 |  61265 |  481 | 477MB |      477MB |          477MB |        NaN% |  |
| 2        | p001           | Set 1 |      18 |    1.91 |    1.68 |     0.14 |             |        0.00 |     0.09 |  66176 |  519 | 515MB |      515MB |          515MB |        NaN% |  |
| 2        | p002           | Set 1 |      19 |    1.87 |    1.51 |     0.14 |             |             |     0.22 |  66672 |  522 | 519MB |      519MB |          519MB |        NaN% |  |
| 2        | p003           | Set 1 |      20 |    1.99 |    1.66 |     0.14 |             |        0.00 |     0.20 |  64478 |  503 | 502MB |      502MB |          502MB |        NaN% |  |
| 2        | p004           | Set 1 |      21 |    1.96 |    1.70 |     0.15 |             |             |     0.12 |  70023 |  551 | 545MB |      545MB |          545MB |        NaN% |  |
| 2        | p005           | Set 1 |      22 |    1.94 |    1.68 |     0.15 |             |             |     0.11 |  63342 |  496 | 493MB |      493MB |          493MB |        NaN% |  |
| 2        | p006           | Set 1 |      23 |    1.89 |    1.68 |     0.14 |             |             |     0.08 |  72179 |  565 | 562MB |      562MB |          562MB |        NaN% |  |
| 2        | p007           | Set 1 |      24 |    2.07 |    1.74 |     0.14 |             |             |     0.19 |  71812 |  562 | 559MB |      559MB |          559MB |        NaN% |  |
| 2        | p008           | Set 1 |      25 |    1.97 |    1.57 |     0.14 |             |             |     0.26 |  64243 |  502 | 500MB |      500MB |          500MB |        NaN% |  |
============================================================================================================================================================================================

Instance Drill-Down
===========================================================================================================================================================================================================================================
| Instance | Process Names                                                                      | Elapsed |   Cpu   |    IO    | Application | Concurrency |  Other   | Buffer | Read | Read  |  Offload   |    Offload     | Wait Events |
|          |                                                                                    | Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) |  Gets  | Reqs | Bytes | Elig Bytes | Returned Bytes |             |
===========================================================================================================================================================================================================================================
|    1     | QC p000 p001 p002 p003 p004 p005 p006 p007 p008 p009 p00a p00b p00c p00d p00e p00f |      34 |      21 |     1.82 |        0.00 |        0.00 |       11 |   822K | 6436 |   6GB |        6GB |            6GB |             |
|    2     | p000 p001 p002 p003 p004 p005 p006 p007 p008                                       |      18 |      15 |     1.26 |             |        0.00 |     1.52 |   600K | 4701 |   5GB |        5GB |            5GB |             |
===========================================================================================================================================================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=2126708148)
============================================================================================================================================================================
| Id |            Operation             |       Name        |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read  | Read  |  Mem  | Activity | Activity Detail |
|    |                                  |                   | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs  | Bytes | (Max) |   (%)    |   (# samples)   |
============================================================================================================================================================================
|  0 | SELECT STATEMENT                 |                   |         |       |         1 |     +2 |     1 |        1 |       |       |     . |          |                 |
|  1 |   SORT AGGREGATE                 |                   |       1 |       |         1 |     +2 |     1 |        1 |       |       |     . |          |                 |
|  2 |    PX COORDINATOR                |                   |         |       |         2 |     +1 |    26 |       25 |       |       |     . |          |                 |
|  3 |     PX SEND QC (RANDOM)          | :TQ10000          |       1 |       |         2 |     +1 |    25 |       25 |       |       |     . |          |                 |
|  4 |      SORT AGGREGATE              |                   |       1 |       |         2 |     +1 |    25 |       25 |       |       |     . |          |                 |
|  5 |       PX BLOCK ITERATOR          |                   |    250M | 17097 |         2 |     +1 |    25 |     250M |       |       |     . |          |                 |
|  6 |        TABLE ACCESS STORAGE FULL | EXAMPLE_CUSTOMERS |    250M | 17097 |         3 |     +0 |   379 |     250M | 11137 |  11GB | 165MB |          |                 |
============================================================================================================================================================================
赞(0）分享  回复(0）举报  2021-07-29
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com