Cursor duration temp tables – feature that has been around for some
time – used to materialize the intermediate results of a query into temporary
tables to improve performance for queries that make multiple passes over a
dataset.
With 12.2 database introduced new In-memory cursor duration temp
tables. The switch to in-memory means the queries should see reduction in IO
since each pass over the data set and does not occur addition IO operations to
disk. Incase if insufficient memory to load all data then Oracle would use
local temp segments will be allocated to store the excess data and when an
In-memory cursor duration temp table is queried, it will query both memory and
private temp segments to return the data.
In general the memory used by the In-memory cursor duration temp table
come from PGA pool.
Here is the query we need to run:
select
owner,object_type,trunc(created,'Y'),count(*)
from
big_table
group
by grouping sets(
(owner),
(owner,object_type),
(object_type,trunc(created,'Y')),
(trunc(created,'Y')),(object_type) ) ;
Pre 12.2 – cursor duration temp tables
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 71yfn2zkk91pc, child number 0
-------------------------------------
select
owner,object_type,trunc(created,'Y'),count(*) from big_table
group
by grouping sets( (owner), (owner,object_type),
(object_type,trunc(created,'Y')), (trunc(created,'Y')),
(object_type) )
Plan
hash value: 3370705251
----------------------------------------------------------------------------------------------------------------------------------------------------
|
Id | Operation | Name | Starts | E-Rows |
A-Rows | Buffers | Reads | Writes | OMem |
1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 448 | 29127 |
25151 | 3845 | |
| |
| 1 |
TEMP TABLE TRANSFORMATION | | 1 |
| 448 | 29127 |
25151 | 3845 | |
| |
| 2 |
LOAD AS SELECT | | 1 |
| 0 | 21378 |
17464 | 3840 | 2068K|
2068K| |
| 3 |
TABLE ACCESS FULL |
BIG_TABLE | 1 |
1000K| 1000K| 17475 |
17464 | 0 | |
| |
| 4 |
LOAD AS SELECT | | 1 |
| 0 | 3851 |
3841 | 2 | 1040K|
1040K| |
| 5 |
SORT GROUP BY ROLLUP | | 1 |
1048 | 342 | 3846 |
3841 | 0 | 27648 | 27648
|24576 (0)|
| 6 |
TABLE ACCESS FULL |
SYS_TEMP_0FD9D6E0C_3ADC9D7D | 1 | 1000K|
1000K| 3846 | 3841 |
0 | | | |
| 7 |
MULTI-TABLE INSERT | | 1 |
| 0 |
3851 | 3841 | 2 |
| | |
| 8 |
SORT GROUP BY ROLLUP | | 1 |
27 | 103 | 3843 |
3841 | 0 | 6144 |
6144 | 6144 (0)|
| 9 |
TABLE ACCESS FULL |
SYS_TEMP_0FD9D6E0C_3ADC9D7D | 1
| 1000K| 1000K|
3843 | 3841 | 0 |
| | |
| 10 |
DIRECT LOAD INTO |
SYS_TEMP_0FD9D6E0E_3ADC9D7D | 0 | |
0 | 0 | 0 |
0 | | |
|
| 11 |
DIRECT LOAD INTO |
SYS_TEMP_0FD9D6E0D_3ADC9D7D | 0 | |
0 | 0 | 0 |
0 | | | |
| 12 |
LOAD AS SELECT | | 1 |
| 0 | 11 |
1 | 1 | 1040K|
1040K| |
| 13 |
HASH GROUP BY | | 1 |
1 | 3 | 6 |
1 | 0 | 1149K|
1149K| 759K (0)|
| 14 |
TABLE ACCESS FULL |
SYS_TEMP_0FD9D6E0E_3ADC9D7D | 1
| 27 | 65 |
6 | 1 | 0 |
| | |
| 15 |
VIEW | | 1 |
1075 | 448 | 15 |
4 | 0 | |
| |
| 16 |
VIEW | | 1 |
1075 | 448 | 15 |
4 | 0 | |
| |
| 17 |
UNION-ALL | | 1 |
| 448 | 15 |
4 | 0 | |
| |
| 18 |
TABLE ACCESS FULL |
SYS_TEMP_0FD9D6E0D_3ADC9D7D | 1 | 1048 |
383 | 12 | 4 |
0 | | | |
| 19 |
TABLE ACCESS FULL |
SYS_TEMP_0FD9D6E0E_3ADC9D7D | 1 | 27 |
65 | 3 | 0 |
0 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------
As we see at the top of the plan, we see TEMP TABLE TRANSFORMATION
followed by LOAD AS SELECT, about half the way down the report we can see the
additional LOAD AS SELECT statement against the temp tables containing the base
level information that we need to create the various total combinations using
grouping sets clause.
Moving to 12.2 database – the plan changes like this:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 71yfn2zkk91pc, child number 0
-------------------------------------
select
owner,object_type,trunc(created,'Y'),count(*) from big_table
group by
grouping sets( (owner), (owner,object_type),
(object_type,trunc(created,'Y')), (trunc(created,'Y')),
(object_type) )
Plan hash
value: 3370705251
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |
A-Rows | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 448 | 17513 |
| | |
| 1 |
TEMP TABLE TRANSFORMATION | | 1 |
| 448 |
17513 | | | |
| 2 |
LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6746_1D8EED58 | 1 |
| 0 | 17512 |
1024 | 1024 | |
| 3 |
TABLE ACCESS FULL
| BIG_TABLE
| 1 | 1000K|
1000K| 17511 | |
| |
| 4 |
LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6747_1D8EED58 | 1 |
| 0 | 0 |
1024 | 1024 | |
| 5 |
SORT GROUP BY ROLLUP
|
| 1 | 1048 |
342 | 0 | 27648 | 27648
|24576 (0)|
| 6 |
TABLE ACCESS FULL
| SYS_TEMP_0FD9D6746_1D8EED58 |
1 | 1000K| 1000K|
0 | |
| |
| 7 |
MULTI-TABLE INSERT
| | 1 |
| 0 | 0 |
| | |
| 8 |
SORT GROUP BY ROLLUP
| | 1
| 27 | 103 |
0 | 6144 | 6144 | 6144
(0)|
| 9 |
TABLE ACCESS FULL
| SYS_TEMP_0FD9D6746_1D8EED58 |
1 | 1000K| 1000K|
0 | | | |
| 10 |
DIRECT LOAD INTO (CURSOR DURATION MEMORY)|
SYS_TEMP_0FD9D6748_1D8EED58 | 0
| | 0 |
0 | | | |
| 11 |
DIRECT LOAD INTO (CURSOR DURATION MEMORY)|
SYS_TEMP_0FD9D6747_1D8EED58 | 0
| | 0 |
0 | | | |
| 12 |
LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6747_1D8EED58 | 1 |
| 0 | 0 |
1024 | 1024 | |
| 13 |
HASH GROUP BY
| | 1 |
1 | 3 | 0
| 1149K|
1149K| 730K (0)|
| 14 |
TABLE ACCESS FULL
| SYS_TEMP_0FD9D6748_1D8EED58 |
1 | 27 | 65 |
0 | | | |
| 15 |
VIEW
| |
1 | 1075 | 448 |
0 | | | |
| 16 |
VIEW
| | 1 |
1075 | 448 | 0 |
| | |
| 17 |
UNION-ALL | | 1 |
| 448 | 0 |
| | |
| 18 |
TABLE ACCESS FULL
| SYS_TEMP_0FD9D6747_1D8EED58 |
1 | 1048 | 383 |
0 | | |
|
| 19 |
TABLE ACCESS FULL
| SYS_TEMP_0FD9D6748_1D8EED58 |
1 | 27 | 65 |
0 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------
The global stats from SQL Monitor report from 12.1 database shows this.
Global Stats
==============================================================================================
|
Elapsed | Cpu |
IO | Application | Fetch |
Buffer | Read | Read | Write | Write |
|
Time(s) | Time(s) | Waits(s) |
Waits(s) | Calls | Gets |
Reqs | Bytes | Reqs | Bytes |
==============================================================================================
| 1.76 |
0.96 | 0.80 | 0.00 | 4 |
29126 | 886 | 196MB | 65 |
30MB |
==============================================================================================
And 12.2 database shows this.
Global Stats
===========================================================================
|
Elapsed | Cpu |
IO | Other
| Fetch | Buffer | Read | Read |
|
Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets |
Reqs | Bytes |
===========================================================================
| 2.89 |
1.28 | 0.56 | 1.05 |
4 | 17513 | 250 |
2MB |
===========================================================================
In summary, with this grouping set example we have reduced the amount
of IO and number of IO requests through the use of in-memory cursor duration
temp tables. As with the previous report, you will see continual references to
“LOAD AS SELECT”, however in 12.2 the additional set of keywords which identify
the use of new in-memory cursor duration temp tables.
In the closing note, in-memory cursor duration temp tables are only support
for serial queries and not for parallel executions. For parallel executions,
Oracle switch over to cursor duration temp table(pre 12.2 approach)
demo@PDB1>
set autotrace traceonly explain
demo@PDB1>
select deptno,job,count(*)
2 from
emp
3
group by grouping sets(
4
(job),
5
(deptno,job),
6
(deptno) ) ;
Execution Plan
----------------------------------------------------------
Plan hash
value: 2624106695
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 |
352 | 11
(19)|
| 1 |
TEMP TABLE TRANSFORMATION | | |
| |
| 2 |
MULTI-TABLE INSERT
| | |
| |
| 3 |
SORT GROUP BY ROLLUP
| | 11 |
121 | 4 (25)|
| 4 |
TABLE ACCESS FULL
| EMP | 14 |
154 | 3 (0)|
| 5 |
DIRECT LOAD INTO (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6A5B_3E3A37
| | |
|
| 6 |
DIRECT LOAD INTO (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6A5C_3E3A37
| | |
|
| 7 |
LOAD AS SELECT (CURSOR DURATION MEMORY)
| SYS_TEMP_0FD9D6A5C_3E3A37 | |
| |
| 8 |
HASH GROUP BY
| | 3 |
33 | 3 (34)|
| 9 |
TABLE ACCESS FULL
| SYS_TEMP_0FD9D6A5B_3E3A37 |
11 | 121 | 2
(0)|
| 10 |
VIEW
| | 22 |
704 | 4 (0)|
| 11 |
VIEW
| | 22 |
704 | 4 (0)|
| 12 |
UNION-ALL | | |
| |
| 13 |
TABLE ACCESS FULL
| SYS_TEMP_0FD9D6A5B_3E3A37 |
11 | 121 | 2
(0)|
| 14 |
TABLE ACCESS FULL
| SYS_TEMP_0FD9D6A5C_3E3A37 |
11 | 121 | 2
(0)|
--------------------------------------------------------------------------------------------------------------
demo@PDB1>
demo@PDB1>
demo@PDB1>
select /*+ parallel(e,2) */ deptno,job,count(*)
2 from
emp e
3
group by grouping sets(
4
(job),
5
(deptno,job),
6
(deptno) ) ;
Execution Plan
----------------------------------------------------------
Plan hash
value: 1046219261
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TQ
|IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 |
352 | 10 (20)|
| | |
| 1 |
TEMP TABLE TRANSFORMATION
| | |
| | |
| |
| 2 |
PX COORDINATOR
| | |
| | |
| |
| 3 |
PX SEND QC (RANDOM)
| :TQ10002 | |
| | Q1,02 | P->S | QC (RAND) |
| 4 |
MULTI-TABLE INSERT
| | |
| | Q1,02 | PCWP | |
| 5 |
PX RECEIVE
| | |
| | Q1,02 | PCWP | |
| 6 |
PX SEND ROUND-ROBIN
| :TQ10001 | |
| | Q1,01 | P->P | RND-ROBIN |
| 7 |
VIEW
| | |
| | Q1,01 | PCWP | |
| 8 |
SORT GROUP BY ROLLUP COLLECTOR
| |
11 | 121 | 3
(34)| Q1,01 | PCWP | |
| 9 |
PX RECEIVE
| | 11 |
121 | 3 (34)|
Q1,01 | PCWP | |
| 10 |
PX SEND HYBRID HASH |
:TQ10000 | 11 |
121 | 3 (34)|
Q1,00 | P->P | HYBRID HASH|
| 11 | SORT GROUP BY ROLLUP
DISTRIBUTOR|
| 11 | 121 |
3 (34)| Q1,00 | PCWP | |
| 12 | HASH GROUP BY | | 11 |
121 | 3 (34)|
Q1,00 | PCWP | |
| 13 | PX BLOCK ITERATOR | | 14 |
154 | 2 (0)|
Q1,00 | PCWC | |
| 14 | TABLE ACCESS FULL | EMP | 14 |
154 | 2 (0)|
Q1,00 | PCWP | |
| 15 |
DIRECT LOAD INTO (TEMP SEGMENT MERGE) | SYS_TEMP_0FD9D6A5F_3E3A37 | |
| | Q1,02 | PCWP | |
| 16 |
DIRECT LOAD INTO (TEMP SEGMENT MERGE) | SYS_TEMP_0FD9D6A60_3E3A37 | |
| | Q1,02 | PCWP | |
| 17 |
PX COORDINATOR
| |
| | | |
| |
| 18 |
PX SEND QC (RANDOM)
| :TQ20001 | 3 |
33 | 3 (34)|
Q2,01 | P->S | QC (RAND) |
| 19 |
LOAD AS SELECT (TEMP SEGMENT MERGE)
| SYS_TEMP_0FD9D6A60_3E3A37 |
| | | Q2,01 | PCWP | |
| 20 |
HASH GROUP BY
| | 3 |
33 | 3 (34)|
Q2,01 | PCWP | |
| 21 |
PX RECEIVE | | 3 |
33 | 3 (34)|
Q2,01 | PCWP | |
| 22 |
PX SEND HASH
| :TQ20000 | 3 |
33 | 3 (34)|
Q2,00 | P->P | HASH |
| 23 |
HASH GROUP BY | | 3 |
33 | 3 (34)|
Q2,00 | PCWP | |
| 24 |
PX BLOCK ITERATOR
| | 11 |
121 | 2 (0)|
Q2,00 | PCWC | |
| 25 |
TABLE ACCESS FULL |
SYS_TEMP_0FD9D6A5F_3E3A37 | 11 | 121 |
2 (0)| Q2,00 | PCWP | |
| 26 |
PX COORDINATOR
| | |
| | |
| |
| 27 |
PX SEND QC (RANDOM)
| :TQ30000 | 22 |
704 | 4 (0)|
Q3,00 | P->S | QC (RAND) |
| 28 |
VIEW
| | 22 |
704 | 4 (0)|
Q3,00 | PCWP | |
| 29 |
VIEW
| | 22 |
704 | 4 (0)|
Q3,00 | PCWP | |
| 30 |
UNION-ALL
| | |
| | Q3,00 | PCWP | |
| 31 |
PX BLOCK ITERATOR
| | 11 |
121 | 2 (0)|
Q3,00 | PCWC | |
| 32 |
TABLE ACCESS FULL | SYS_TEMP_0FD9D6A5F_3E3A37 | 11 |
121 | 2 (0)|
Q3,00 | PCWP | |
| 33 |
PX BLOCK ITERATOR
| | 11 |
121 | 2 (0)|
Q3,00 | PCWC | |
| 34 |
TABLE ACCESS FULL
| SYS_TEMP_0FD9D6A60_3E3A37 |
11 | 121 | 2
(0)| Q3,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 2 because of
table property
demo@PDB1>
hi, when I use the grouping sets clause, what do the "view" and "union all" means?
ReplyDeleteit means that group by each set in grouping sets and then union their results?