Suppose I have one very large table, with 250M rows:
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;
I want to do a full scan of this table, with a simple query: 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 |
----------------------------------------------------------------------------------------
I then decide that I want to use the parallel hint to try to speed this up, using 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 | |
-------------------------------------------------------------------------------------------------------------------------
At first glance, this looks to be a big improvement - The total cost of the first statement is 384K, and the total cost of the new statement is 43K, which indicates to me that the cost of the parallel execution statement is taken as the highest of the 10 individual operations that run in parallel, plus some additional cost of the overhead of parallel exeuction.
When I actually run these statements, however, I do not see any time difference - both of them are taking approximately 35 seconds. Why is the second statement not faster?
I have some theories, but I don't know how to verify which of them (if any) is the reason:
PX COORDINATOR FORCED SERIAL instead of PX COORDINATORAdditional Information
The SQL monitor report for the query without the hint:
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 | | |
=======================================================================================================================================================================
The SQL monitor report for the query with the hint:
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 | | |
============================================================================================================================================================================
Even More Information
If the exadata smart scan is disabled:
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 | | |
====================================================================================================================================================================
If I use a more complex query with a group by, then I see some improvement (71 seconds to 61), but still much less than I would expect:
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 | | |
==============================================================================================================================================================================
If we use a query with a lot of sort operations, then we see the parallel execution actually run slower:
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 | . | | |
============================================================================================================================================================================================================```
I found the root problem, the steps involved were:
dba_rsrc_plan_directivesThe code used:
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
The generated sql report:
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 | | |
============================================================================================================================================================================