Skip to Main Content
  • Questions

  • In-memory cursor duration temp tables.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: February 27, 2018 - 11:43 am UTC

Last updated: May 01, 2020 - 7:43 am UTC

Version: 12.2

Viewed 10K+ times! This question is

You Asked

Hi

I just ran into interesting article about in-memory cursor duration temp tables:
https://oracle-big-data.blogspot.fi/2017/03/this-post-covers-one-of-new-sql.html

There was mentioned that this feature is available for 12.2 for 'serial queries'.
We are using automatic parallelism for our DW environment, which has worked fine.
Will these parallelized queries benefit from this feature?
Does this feature change parallel queries to run serially ?

Has there been any changes with this in Oracle 18c ?

What else should I have asked (about this) ?

lh

and Connor said...

Currently they are serial, because otherwise we'd have to have a means to *share* the temp data so that all slaves could see it. That either means shared memory territory, or a temp segment. We currently choose the latter (for parallel).

Will these parallelized queries benefit from this feature?

Not really. Although if you check the demo I've put at the end here, you can see we use the same "logic" in terms of deriving an execution plan - it is more just that we need to store that temporary data somewhere that everyone can access.

Does this feature change parallel queries to run serially ?

No.

SQL> create table t nologging tablespace largets
  2  as select d.* from dba_Objects d,
  3   ( select 1 from dual connect by level <= 100 );

Table created.

SQL>
SQL>
SQL> select /*+ gather_plan_statistics */ owner, object_type, trunc(created,'YYYY') yr, count(*)
  2  from t
  3  group by grouping sets
  4  (
  5    (owner),
  6    (owner,object_type),
  7    (object_type,trunc(created,'YYYY')),
  8    (trunc(created,'YYYY')),
  9    (object_type)
 10  );

OWNER                          OBJECT_TYPE             YR          COUNT(*)
------------------------------ ----------------------- --------- ----------
HR                             TYPE                                     100
HR                             VIEW                                     100
...

490 rows selected.

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
SQL_ID  0mcu7kuqur48p, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ owner, object_type,
trunc(created,'YYYY') yr, count(*) from t group by grouping sets (
(owner),   (owner,object_type),   (object_type,trunc(created,'YYYY')),
 (trunc(created,'YYYY')),   (object_type) )

Plan hash value: 3954148600

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                             |      1 |        |    490 |00:00:08.69 |     196K|    181K|  14372 |       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION                  |                             |      1 |        |    490 |00:00:08.69 |     196K|    181K|  14372 |       |       |          |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)   | SYS_TEMP_0FD9D660D_960F92A1 |      1 |        |      0 |00:00:05.06 |     167K|    153K|  14372 |  2068K|  2068K|          |
|   3 |    TABLE ACCESS FULL                        | T                           |      1 |   7905K|   7905K|00:00:00.87 |     153K|    153K|      0 |       |       |          |
|   4 |   LOAD AS SELECT (CURSOR DURATION MEMORY)   | SYS_TEMP_0FD9D660E_960F92A1 |      1 |        |      0 |00:00:01.82 |   14392 |  14373 |      0 |  1024 |  1024 |          |
|   5 |    SORT GROUP BY ROLLUP                     |                             |      1 |   1282 |    371 |00:00:01.82 |   14392 |  14373 |      0 | 27648 | 27648 |24576  (0)|
|   6 |     TABLE ACCESS FULL                       | SYS_TEMP_0FD9D660D_960F92A1 |      1 |   7905K|   7905K|00:00:00.48 |   14392 |  14373 |      0 |       |       |          |
|   7 |   MULTI-TABLE INSERT                        |                             |      1 |        |      0 |00:00:01.80 |   14428 |  14372 |      0 |       |       |          |
|   8 |    SORT GROUP BY ROLLUP                     |                             |      1 |     35 |    117 |00:00:01.80 |   14428 |  14372 |      0 |  6144 |  6144 | 6144  (0)|
|   9 |     TABLE ACCESS FULL                       | SYS_TEMP_0FD9D660D_960F92A1 |      1 |   7905K|   7905K|00:00:00.50 |   14428 |  14372 |      0 |       |       |          |
|  10 |    DIRECT LOAD INTO (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D660F_960F92A1 |      0 |        |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
|  11 |    DIRECT LOAD INTO (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D660E_960F92A1 |      0 |        |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
|  12 |   LOAD AS SELECT (CURSOR DURATION MEMORY)   | SYS_TEMP_0FD9D660E_960F92A1 |      1 |        |      0 |00:00:00.01 |       0 |      0 |      0 |  1024 |  1024 |          |
|  13 |    HASH GROUP BY                            |                             |      1 |      1 |      2 |00:00:00.01 |       0 |      0 |      0 |  1149K|  1149K|  491K (0)|
|  14 |     TABLE ACCESS FULL                       | SYS_TEMP_0FD9D660F_960F92A1 |      1 |     35 |     68 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
|  15 |   VIEW                                      |                             |      1 |   1317 |    490 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
|  16 |    VIEW                                     |                             |      1 |   1317 |    490 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
|  17 |     UNION-ALL                               |                             |      1 |        |    490 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
|  18 |      TABLE ACCESS FULL                      | SYS_TEMP_0FD9D660E_960F92A1 |      1 |   1282 |    422 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
|  19 |      TABLE ACCESS FULL                      | SYS_TEMP_0FD9D660F_960F92A1 |      1 |     35 |     68 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


34 rows selected.

SQL> select /*+ gather_plan_statistics parallel */ owner, object_type, trunc(created,'YYYY') yr, count(*)
  2  from t
  3  group by grouping sets
  4  (
  5    (owner),
  6    (owner,object_type),
  7    (object_type,trunc(created,'YYYY')),
  8    (trunc(created,'YYYY')),
  9    (object_type)
 10  );

OWNER                          OBJECT_TYPE             YR          COUNT(*)
------------------------------ ----------------------- --------- ----------
HR                             TYPE                                     100
HR                             VIEW                                     100
...

490 rows selected.

SQL>
SQL>
SQL> @exp

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
SQL_ID  1pmucckvxxywq, child number 1
-------------------------------------
select /*+ gather_plan_statistics parallel */ owner, object_type,
trunc(created,'YYYY') yr, count(*) from t group by grouping sets (
(owner),   (owner,object_type),   (object_type,trunc(created,'YYYY')),
 (trunc(created,'YYYY')),   (object_type) )

Plan hash value: 4262997987

---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                             |      1 |        |    490 |00:00:04.32 |    1011 |       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION                 |                             |      1 |        |    490 |00:00:04.32 |    1011 |       |       |          |
|   2 |   PX COORDINATOR                           |                             |      1 |        |      4 |00:00:02.23 |     261 | 73728 | 73728 |          |
|   3 |    PX SEND QC (RANDOM)                     | :TQ10000                    |      0 |   7905K|      0 |00:00:00.01 |       0 |       |       |          |
|   4 |     LOAD AS SELECT (TEMP SEGMENT MERGE)    | SYS_TEMP_0FD9D6613_960F92A1 |      0 |        |      0 |00:00:00.01 |       0 |  2068K|  2068K| 2068K (0)|
|   5 |      PX BLOCK ITERATOR                     |                             |      0 |   7905K|      0 |00:00:00.01 |       0 |       |       |          |
|*  6 |       TABLE ACCESS FULL                    | T                           |      0 |   7905K|      0 |00:00:00.01 |       0 |       |       |          |
|   7 |   PX COORDINATOR                           |                             |      1 |        |      4 |00:00:01.09 |      27 | 73728 | 73728 |          |
|   8 |    PX SEND QC (RANDOM)                     | :TQ20001                    |      0 |   1282 |      0 |00:00:00.01 |       0 |       |       |          |
|   9 |     LOAD AS SELECT (TEMP SEGMENT MERGE)    | SYS_TEMP_0FD9D6614_960F92A1 |      0 |        |      0 |00:00:00.01 |       0 |  1040K|  1040K| 1040K (0)|
|  10 |      VIEW                                  |                             |      0 |        |      0 |00:00:00.01 |       0 |       |       |          |
|  11 |       SORT GROUP BY ROLLUP COLLECTOR       |                             |      0 |   1282 |      0 |00:00:00.01 |       0 | 27648 | 27648 |12288  (0)|
|  12 |        PX RECEIVE                          |                             |      0 |   1282 |      0 |00:00:00.01 |       0 |       |       |          |
|  13 |         PX SEND HYBRID HASH                | :TQ20000                    |      0 |   1282 |      0 |00:00:00.01 |       0 |       |       |          |
|  14 |          SORT GROUP BY ROLLUP DISTRIBUTOR  |                             |      0 |   1282 |      0 |00:00:00.01 |       0 |  4096 |  4096 | 2048  (0)|
|  15 |           HASH GROUP BY                    |                             |      0 |   1282 |      0 |00:00:00.01 |       0 |  1022K|  1022K| 1326K (0)|
|  16 |            PX BLOCK ITERATOR               |                             |      0 |   7905K|      0 |00:00:00.01 |       0 |       |       |          |
|* 17 |             TABLE ACCESS FULL              | SYS_TEMP_0FD9D6613_960F92A1 |      0 |   7905K|      0 |00:00:00.01 |       0 |       |       |          |
|  18 |   PX COORDINATOR                           |                             |      1 |        |      6 |00:00:00.92 |      29 | 73728 | 73728 |          |
|  19 |    PX SEND QC (RANDOM)                     | :TQ30002                    |      0 |        |      0 |00:00:00.01 |       0 |       |       |          |
|  20 |     MULTI-TABLE INSERT                     |                             |      0 |        |      0 |00:00:00.01 |       0 |       |       |          |
|  21 |      PX RECEIVE                            |                             |      0 |        |      0 |00:00:00.01 |       0 |       |       |          |
|  22 |       PX SEND ROUND-ROBIN                  | :TQ30001                    |      0 |        |      0 |00:00:00.01 |       0 |       |       |          |
|  23 |        VIEW                                |                             |      0 |        |      0 |00:00:00.01 |       0 |       |       |          |
|  24 |         SORT GROUP BY ROLLUP COLLECTOR     |                             |      0 |     35 |      0 |00:00:00.01 |       0 |  4096 |  4096 | 4096  (0)|
|  25 |          PX RECEIVE                        |                             |      0 |     35 |      0 |00:00:00.01 |       0 |       |       |          |
|  26 |           PX SEND HYBRID HASH              | :TQ30000                    |      0 |     35 |      0 |00:00:00.01 |       0 |       |       |          |
|  27 |            SORT GROUP BY ROLLUP DISTRIBUTOR|                             |      0 |     35 |      0 |00:00:00.01 |       0 |  4096 |  4096 | 2048  (0)|
|  28 |             HASH GROUP BY                  |                             |      0 |     35 |      0 |00:00:00.01 |       0 |  1079K|  1079K|   37M (0)|
|  29 |              PX BLOCK ITERATOR             |                             |      0 |   7905K|      0 |00:00:00.01 |       0 |       |       |          |
|* 30 |               TABLE ACCESS FULL            | SYS_TEMP_0FD9D6613_960F92A1 |      0 |   7905K|      0 |00:00:00.01 |       0 |       |       |          |
|  31 |      DIRECT LOAD INTO (TEMP SEGMENT MERGE) | SYS_TEMP_0FD9D6615_960F92A1 |      0 |        |      0 |00:00:00.01 |       0 |       |       |          |
|  32 |      DIRECT LOAD INTO (TEMP SEGMENT MERGE) | SYS_TEMP_0FD9D6614_960F92A1 |      0 |        |      0 |00:00:00.01 |       0 |       |       |          |
|  33 |   PX COORDINATOR                           |                             |      1 |        |      4 |00:00:00.02 |      26 | 73728 | 73728 |          |
|  34 |    PX SEND QC (RANDOM)                     | :TQ40001                    |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  35 |     LOAD AS SELECT (TEMP SEGMENT MERGE)    | SYS_TEMP_0FD9D6614_960F92A1 |      0 |        |      0 |00:00:00.01 |       0 |  1040K|  1040K| 1040K (0)|
|  36 |      HASH GROUP BY                         |                             |      0 |      1 |      0 |00:00:00.01 |       0 |  1149K|  1149K|  468K (0)|
|  37 |       PX RECEIVE                           |                             |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  38 |        PX SEND HASH                        | :TQ40000                    |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  39 |         HASH GROUP BY                      |                             |      0 |      1 |      0 |00:00:00.01 |       0 |  1149K|  1149K|  469K (0)|
|  40 |          PX BLOCK ITERATOR                 |                             |      0 |     35 |      0 |00:00:00.01 |       0 |       |       |          |
|* 41 |           TABLE ACCESS FULL                | SYS_TEMP_0FD9D6615_960F92A1 |      0 |     35 |      0 |00:00:00.01 |       0 |       |       |          |
|  42 |   PX COORDINATOR                           |                             |      1 |        |    490 |00:00:00.02 |      40 | 73728 | 73728 |          |
|  43 |    PX SEND QC (RANDOM)                     | :TQ50000                    |      0 |   1317 |      0 |00:00:00.01 |       0 |       |       |          |
|  44 |     VIEW                                   |                             |      0 |   1317 |      0 |00:00:00.01 |       0 |       |       |          |
|  45 |      VIEW                                  |                             |      0 |   1317 |      0 |00:00:00.01 |       0 |       |       |          |
|  46 |       UNION-ALL                            |                             |      0 |        |      0 |00:00:00.01 |       0 |       |       |          |
|  47 |        PX BLOCK ITERATOR                   |                             |      0 |   1282 |      0 |00:00:00.01 |       0 |       |       |          |
|* 48 |         TABLE ACCESS FULL                  | SYS_TEMP_0FD9D6614_960F92A1 |      0 |   1282 |      0 |00:00:00.01 |       0 |       |       |          |
|  49 |        PX BLOCK ITERATOR                   |                             |      0 |     35 |      0 |00:00:00.01 |       0 |       |       |          |
|* 50 |         TABLE ACCESS FULL                  | SYS_TEMP_0FD9D6615_960F92A1 |      0 |     35 |      0 |00:00:00.01 |       0 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access(:Z>=:Z AND :Z<=:Z)
  17 - access(:Z>=:Z AND :Z<=:Z)
  30 - access(:Z>=:Z AND :Z<=:Z)
  41 - access(:Z>=:Z AND :Z<=:Z)
  48 - access(:Z>=:Z AND :Z<=:Z)
  50 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2


79 rows selected.

SQL>


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank You for Your prompt and detailed answer,

A reader, February 28, 2018 - 7:26 am UTC

althought it was not what I was hoping for.
But maybe in future...
With clauses and automatic parallelism is powerfull combination in DW databases.
Connor McDonald
March 01, 2018 - 1:09 am UTC

I agree. With the advent of flash, I'd contend that dumping to temporary storage (where that storage is nearly as fast as memory anyway) will be a suitable compromise.

partial paralleism ?

lh, April 30, 2020 - 10:44 am UTC

Hi

Can we control that some parts of the sql-statement are executed in parallel and other parts are not ?

I had in mind situation where I would like to have in-memory cursor duration temp table formed from select statements executed as parallel and then utilized in serial l sections of the sql-statement.

lh
Connor McDonald
May 01, 2020 - 7:43 am UTC

That seems an unusual request, but I imagine you could do something like:

with parallel_part as ( 
 select /*+ parallel materialize */ .....
)
select ...
from parallel_part


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.