Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: March 03, 2017 - 11:23 pm UTC

Last updated: March 16, 2017 - 1:20 am UTC

Version: 12.2

Viewed 1000+ times

You Asked

I downloaded the developers days VM with the latest 12.2 installation on it, and wanted to try out the in memory feature of 12c. I've read the documentation and done everything I think I need to, however unfortunately my table doesn't seem to want to go in memory (that is, I can't see it in v$im_segments). I've tried forcing a full table scan and it makes no difference. Alto tried bouncing the database but that didn't help.

[oracle@vbgeneric]$ sqlplus hr@orcl

SQL*Plus: Release 12.2.0.1.0 Production on Fri Mar 3 18:12:31 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter table employees inmemory priority critical;

Table altered.

SQL> select * from v$im_segments;

no rows selected

SQL> select * from v$sga;

NAME                      VALUE     CON_ID
-------------------- ---------- ----------
Fixed Size              8794848          0
Variable Size         402656544          0
Database Buffers      654311424          0
Redo Buffers            7979008          0
In-Memory Area       1073741824          0

SQL> show parameter inmemory;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_adg_enabled                 boolean     TRUE
inmemory_clause_default              string
inmemory_expressions_usage           string      ENABLE
inmemory_force                       string      DEFAULT
inmemory_max_populate_servers        integer     1
inmemory_query                       string      ENABLE
inmemory_size                        big integer 1G
inmemory_trickle_repopulate_servers_ integer     1
percent
inmemory_virtual_columns             string      MANUAL
optimizer_inmemory_aware             boolean     TRUE
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga            boolean     FALSE
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 2G
sga_min_size                         big integer 0
sga_target                           big integer 1500M
unified_audit_sga_queue_size         integer     1048576
SQL> set autotrace on
SQL> select /*+full(e)*/ count(*) from employees e;

  COUNT(*)
----------
       107


Execution Plan
----------------------------------------------------------
Plan hash value: 1756381138

--------------------------------------------------------------------------------
-

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

--------------------------------------------------------------------------------
-

|   0 | SELECT STATEMENT            |           |     1 |     3   (0)| 00:00:01
|

|   1 |  SORT AGGREGATE             |           |     1 |            |
|

|   2 |   TABLE ACCESS INMEMORY FULL| EMPLOYEES |   107 |     3   (0)| 00:00:01
|

--------------------------------------------------------------------------------
-



Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        543  bytes sent via SQL*Net to client
        607  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 off
SQL> select * from v$im_segments;

no rows selected

SQL>



What am I doing wrong? The plan shown on autotrace indicates Oracle thinks the table should be inmemory doesn't it? However v$im_segments shows it's not. I didn't capture the actual plan to prove that was the case. Note - I'd already done a full on the employees table previously so all the rows were probably in the buffer cache, hence the 0 physical reads.

and Connor said...

Make your table bigger :-)

From the docs:

"If a segment on disk is 64 KB or less, then it is not populated in the IM column store. Therefore, some small database objects that were enabled for the IM column store might not be populated in it."


Rating

  (5 ratings)

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

Comments

ooopppps...

John Keymer, March 04, 2017 - 8:01 am UTC

When you claim to have read the documentation but clearly haven't read enough! :)

Thanks - got an example working now with a suitably sized table.


Sorry for hijacking the original question somewhat, but do you know of any guides as to a strategy for populating table inmemory in an OLAP environment? We have an environment with a huge amount of memory available - probably enough actually to just throw every table from the key star schema's fully in memory. Is that however a "good" idea? Or should we populate just the large fact tables in memory and let the dimensions come from disk/buffer cache? Or just the key measure and dimension keys off the fact table (i.e. there's probably no need to populate all the "audit" columns on the table, or those that are used purely for within the ETL (and not in end user queries)?

Thanks
Maria Colgan
March 07, 2017 - 1:50 am UTC

We have an in-memory advisor which will give you a starting point.

There's a nice presentation on the general topic of "which" tables here

https://published-rs.lanyonevents.com/published/oracleus2015/sessionsFiles/3563/CON6775_Colgan-CON6775_Best_Practices_For_Getting_Started_With_Oracle_Database_In-Memory.pdf

In memory Aggregation (IMA)

Rajeshwaran, Jeyabal, March 06, 2017 - 2:38 am UTC

....
We have an environment with a huge amount of memory available - probably enough actually to just throw every table from the key star schema's fully in memory. 
Is that however a "good" idea? Or should we populate just the large fact tables in memory and let the dimensions come from disk/buffer cache? 
Or just the key measure and dimension keys off the fact table
....


Having all the required attributes/column from the FACT and DIMENSION table at in-memory will induce INMEMORY AGGREGATION (IMA) (aka Vector Transformation)

inmemory team explains them here https://blogs.oracle.com/In-Memory/entry/getting_started_with_oracle_database5

How vector transformation improves performance ? Below is my test case to show that.

One of the key feature of Inmemory database is it doesn't ship IMCU across the nodes of RAC instance, 
if you are on Engineered system you could make use of DUPLICATE /DUPLICATE ALL sub-clause that helps 
us to duplicate dimension across all the RAC instance, while the FACT table would simply distribute by partition or sub-partition. 
Joins could then takes place entirely within each instance, since all the data needed for each join would be in-memory. 
This is analogous to "Partition wise join" since the entire dimension table resides in each IM column store


https://blogs.oracle.com/In-Memory/entry/oracle_database_in_memory_on2

rajesh@ORA12C> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                        0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production                                   0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

5 rows selected.

rajesh@ORA12C> show parameter inmemory_size

NAME                                          TYPE        VALUE
--------------------------------------------- ----------- -------------------------
inmemory_size                                 big integer 152M
rajesh@ORA12C>
rajesh@ORA12C> select cdb from v$database ;

CDB
---
NO

1 row selected.

rajesh@ORA12C>
rajesh@ORA12C> create table jobs
  2  as
  3  select rownum as job_id,
  4     job_title,
  5     trunc(dbms_random.value(1,100)) as min_sal,
  6     trunc(dbms_random.value(200,300)) as max_sal
  7  from (     select distinct owner as job_title
  8  from all_objects ) ;

Table created.

rajesh@ORA12C>
rajesh@ORA12C> alter table jobs
  2  add constraint jobs_pk
  3  primary key(job_id);

Table altered.

rajesh@ORA12C>
rajesh@ORA12C> create table dept
  2  as
  3  select rownum as deptno,
  4     dept_name,
  5     trunc(dbms_random.value(1,100)) as manager_id,
  6     trunc(dbms_random.value(1,50)) as location_id
  7  from ( select distinct object_type as dept_name
  8     from all_objects) ;

Table created.

rajesh@ORA12C>
rajesh@ORA12C> alter table dept
  2  add constraint dept_pk
  3  primary key(deptno);

Table altered.

rajesh@ORA12C>
rajesh@ORA12C> create table emp
  2  partition by hash(emp_id)
  3  ( partition p1,
  4    partition p2,
  5    partition p3,
  6    partition p4 )
  7  as
  8  select rownum as emp_id,
  9     object_name as first_name,
 10     object_name as last_name,
 11     object_name||'@gmail.com' as email_id,
 12     object_id as phone_no,
 13     created as hire_date,
 14     trunc(dbms_random.value(1,32)) as job_id,
 15     trunc(dbms_random.value(1,5000)) as salary,
 16     trunc(dbms_random.value(1,3000)) as comm,
 17     rownum as manager_id ,
 18     trunc(dbms_random.value(1,39)) as deptno
 19  from all_objects;

Table created.

rajesh@ORA12C> declare
  2     l_empno number;
  3  begin
  4     select max(emp_id) into l_empno
  5     from emp;
  6
  7     execute immediate ' alter session force parallel dml parallel 4';
  8     for i in 1..7
  9     loop
 10             dbms_application_info.set_client_info('i ='||i);
 11             insert /*+ append */ into emp
 12             ( EMP_ID,FIRST_NAME,LAST_NAME,EMAIL_ID,
 13                     PHONE_NO,HIRE_DATE,JOB_ID,SALARY,COMM,MANAGER_ID,
 14                     DEPTNO )
 15             select l_empno+rownum,FIRST_NAME,LAST_NAME,EMAIL_ID,
 16                     PHONE_NO,HIRE_DATE,JOB_ID,SALARY,COMM,MANAGER_ID,
 17                     DEPTNO
 18             from emp ;
 19             l_empno := l_empno + sql%rowcount;
 20             commit;
 21     end loop;
 22     execute immediate ' alter session disable parallel dml';
 23  end;
 24  /

PL/SQL procedure successfully completed.

rajesh@ORA12C>
rajesh@ORA12C> begin
  2     dbms_stats.gather_table_stats(user,'emp',
  3             degree=>2,
  4             method_opt=>'for all columns size 254',
  5             no_invalidate=>false,
  6             cascade=>true );
  7  end;
  8  /

PL/SQL procedure successfully completed.

rajesh@ORA12C> select /*+ parallel(emp,4) */ count(*) from emp;

  COUNT(*)
----------
  11524480

1 row selected.

rajesh@ORA12C> select count(*) from dept;

  COUNT(*)
----------
        39

1 row selected.

rajesh@ORA12C> select count(*) from jobs;

  COUNT(*)
----------
        32

1 row selected.

rajesh@ORA12C> alter table dept inmemory;

Table altered.

rajesh@ORA12C> alter table jobs inmemory;

Table altered.

rajesh@ORA12C> alter table emp inmemory
  2     no inmemory(emp_id,first_name,last_name,
  3             hire_date,email_id,
  4             phone_no,manager_id);

Table altered.

rajesh@ORA12C>
rajesh@ORA12C> column owner format a10
rajesh@ORA12C> column segment_name format a10
rajesh@ORA12C> column partition_name format a10
rajesh@ORA12C> select owner,segment_name,partition_name,inmemory_size
  2  from v$im_segments;

no rows selected

rajesh@ORA12C> alter table emp modify partition p1 inmemory;

Table altered.

rajesh@ORA12C> select count(*) from emp partition(p1);

  COUNT(*)
----------
   2880928

1 row selected.

rajesh@ORA12C>
rajesh@ORA12C> alter table emp modify partition p2 inmemory;

Table altered.

rajesh@ORA12C> select count(*) from emp partition(p2);

  COUNT(*)
----------
   2881300

1 row selected.

rajesh@ORA12C>
rajesh@ORA12C> alter table emp modify partition p3 inmemory;

Table altered.

rajesh@ORA12C> select count(*) from emp partition(p3);

  COUNT(*)
----------
   2883687

1 row selected.

rajesh@ORA12C>
rajesh@ORA12C> alter table emp modify partition p4 inmemory;

Table altered.

rajesh@ORA12C> select count(*) from emp partition(p4);

  COUNT(*)
----------
   2878565

1 row selected.

rajesh@ORA12C> select owner,segment_name,partition_name,inmemory_size
  2  from v$im_segments;

OWNER      SEGMENT_NA PARTITION_ INMEMORY_SIZE
---------- ---------- ---------- -------------
RAJESH     EMP        P4              23527424
RAJESH     EMP        P1              23527424
RAJESH     EMP        P3              23527424
RAJESH     EMP        P2              23527424

4 rows selected.

rajesh@ORA12C>


Tkprof shows me this.

select d.location_id, j.min_sal,j.max_sal,
      sum(e.salary),min(e.comm)
from emp e, dept d, jobs j
where e.deptno = d.deptno
and e.job_id = j.job_id
and d.dept_name in ('EDITION','SEQUENCE')
and j.job_title in ('SYS','PUBLIC')
group by d.location_id, j.min_sal,j.max_sal

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          0          0           0
Execute      1      0.00       0.00          0          6         14           0
Fetch        2      0.04       0.06          2         22          2           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.04       0.08          2         28         16           4

select /*+ no_vector_transform */ d.location_id, j.min_sal,j.max_sal,
      sum(e.salary),min(e.comm)
from emp e, dept d, jobs j
where e.deptno = d.deptno
and e.job_id = j.job_id
and d.dept_name in ('EDITION','SEQUENCE')
and j.job_title in ('SYS','PUBLIC')
group by d.location_id, j.min_sal,j.max_sal

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.15       0.15          0         18          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.17       0.16          0         18          0           4

select /*+ no_vector_transform no_inmemory(e) no_inmemory(d) no_inmemory(j) */
  d.location_id, j.min_sal,j.max_sal,
      sum(e.salary),min(e.comm)
from emp e, dept d, jobs j
where e.deptno = d.deptno
and e.job_id = j.job_id
and d.dept_name in ('EDITION','SEQUENCE')
and j.job_title in ('SYS','PUBLIC')
group by d.location_id, j.min_sal,j.max_sal

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      2.23      15.83     203769     203818          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      2.24      15.84     203769     203818          0           4


Vector Transformation for smarter CPU processing.

Vector Transformation

John Keymer, March 14, 2017 - 8:16 am UTC

Thanks for the info. Does that mean though that if one of my tables happens to be less than 64KB in size then it would preclude the entire query from using vector transformation because it would never go in memory?
Connor McDonald
March 15, 2017 - 3:32 am UTC

No, you can still use it. I'll start with 2 tables, one being really small, but mark them both as inmem

SQL> create table t1 
  2   as select username, user_id, created from dba_users;

Table created.

SQL>
SQL> create table t2 as
  2  select d.* from dba_objects d,
  3  ( select 1 from dual connect by level <= 20 );

Table created.

SQL>
SQL> select segment_name , bytes
  2  from user_segments
  3  where segment_name in ('T1','T2');

SEGMENT_NAME                        BYTES
------------------------------ ----------
T1                                1048576
T2                              257949696

2 rows selected.

SQL>
SQL> select table_name, blocks
  2  from user_tables
  3  where table_name in ('T1','T2');

TABLE_NAME                         BLOCKS
------------------------------ ----------
T2                                  31391
T1                                      5

2 rows selected.

SQL>
SQL> alter table t2 inmemory priority high;

Table altered.

SQL> alter table t1 inmemory priority high;

Table altered.

SQL>
SQL> select count(*) from t2;

  COUNT(*)
----------
   1862200

1 row selected.

SQL> select count(*) from t1;

  COUNT(*)
----------
        52

1 row selected.

SQL>
SQL> select segment_name, bytes, bytes_not_populated
  2  from  v$im_segments;

no rows selected

SQL> select segment_name, bytes, bytes_not_populated
  2  from  v$im_segments;

SEGMENT_NAME                        BYTES BYTES_NOT_POPULATED
------------------------------ ---------- -------------------
T2                              257949696                   0

1 row selected.

SQL>
SQL>
SQL> set autotrace traceonly explain
SQL> select /*+ vector_transform */ t1.user_id, sum(t2.object_id)
  2  from  t1, t2
  3  where t1.username = t2.owner
  4  and   t1.created > date '2010-01-01'
  5  group by t1.user_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 272704472

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                           |    52 |  5616 |  5082  (94)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION        |                           |       |       |            |          |
|   2 |   LOAD AS SELECT                  | SYS_TEMP_0FD9D6606_4B9CA6 |       |       |            |          |
|   3 |    VECTOR GROUP BY                |                           |    52 |  1300 |   101 (100)| 00:00:01 |
|   4 |     KEY VECTOR CREATE BUFFERED    | :KV0000                   |    52 |  1300 |   101 (100)| 00:00:01 |
|*  5 |      TABLE ACCESS INMEMORY FULL   | T1                        |    52 |  1092 |     1   (0)| 00:00:01 |
|   6 |   HASH GROUP BY                   |                           |    52 |  5616 |  4982  (94)| 00:00:01 |
|*  7 |    HASH JOIN                      |                           |    52 |  5616 |  4981  (94)| 00:00:01 |
|   8 |     VIEW                          | VW_VT_AE9E49E8            |    52 |  4316 |  4979  (94)| 00:00:01 |
|   9 |      VECTOR GROUP BY              |                           |    52 |   780 |  4979  (94)| 00:00:01 |
|  10 |       HASH GROUP BY               |                           |    52 |   780 |  4979  (94)| 00:00:01 |
|  11 |        KEY VECTOR USE             | :KV0000                   |  1862K|    26M|  4979  (94)| 00:00:01 |
|* 12 |         TABLE ACCESS INMEMORY FULL| T2                        |  1862K|    19M|   329   (5)| 00:00:01 |
|  13 |     TABLE ACCESS FULL             | SYS_TEMP_0FD9D6606_4B9CA6 |    52 |  1300 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

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

   5 - inmemory("T1"."CREATED">TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       filter("T1"."CREATED">TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   7 - access("ITEM_5"=INTERNAL_FUNCTION("C0") AND "ITEM_6"="C2")
  12 - inmemory(SYS_OP_KEY_VECTOR_FILTER("T2"."OWNER",:KV0000))
       filter(SYS_OP_KEY_VECTOR_FILTER("T2"."OWNER",:KV0000))

Note
-----
   - vector transformation used for this statement



So we still see the plan being available. Now I'll turn off t1 inmemory (so even if it *was* big enough, we're not going to have it in there, and check our query


SQL> set autotrace off
SQL>
SQL> alter table t1 no inmemory;

Table altered.

SQL>
SQL> set autotrace traceonly explain
SQL> select /*+ vector_transform */ t1.user_id, sum(t2.object_id)
  2  from  t1, t2
  3  where t1.username = t2.owner
  4  and   t1.created > date '2010-01-01'
  5  group by t1.user_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 272704472

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                           |    52 |  5616 |  5085  (94)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION        |                           |       |       |            |          |
|   2 |   LOAD AS SELECT                  | SYS_TEMP_0FD9D6608_4B9CA6 |       |       |            |          |
|   3 |    VECTOR GROUP BY                |                           |    52 |  1300 |   103  (98)| 00:00:01 |
|   4 |     KEY VECTOR CREATE BUFFERED    | :KV0000                   |    52 |  1300 |   103  (98)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL            | T1                        |    52 |  1092 |     3   (0)| 00:00:01 |
|   6 |   HASH GROUP BY                   |                           |    52 |  5616 |  4982  (94)| 00:00:01 |
|*  7 |    HASH JOIN                      |                           |    52 |  5616 |  4981  (94)| 00:00:01 |
|   8 |     VIEW                          | VW_VT_AE9E49E8            |    52 |  4316 |  4979  (94)| 00:00:01 |
|   9 |      VECTOR GROUP BY              |                           |    52 |   780 |  4979  (94)| 00:00:01 |
|  10 |       HASH GROUP BY               |                           |    52 |   780 |  4979  (94)| 00:00:01 |
|  11 |        KEY VECTOR USE             | :KV0000                   |  1862K|    26M|  4979  (94)| 00:00:01 |
|* 12 |         TABLE ACCESS INMEMORY FULL| T2                        |  1862K|    19M|   329   (5)| 00:00:01 |
|  13 |     TABLE ACCESS FULL             | SYS_TEMP_0FD9D6608_4B9CA6 |    52 |  1300 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

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

   5 - filter("T1"."CREATED">TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   7 - access("ITEM_5"=INTERNAL_FUNCTION("C0") AND "ITEM_6"="C2")
  12 - inmemory(SYS_OP_KEY_VECTOR_FILTER("T2"."OWNER",:KV0000))
       filter(SYS_OP_KEY_VECTOR_FILTER("T2"."OWNER",:KV0000))

Note
-----
   - vector transformation used for this statement



And we can even run it and check



SQL> set autotrace off
SQL>
SQL>
SQL> select /*+ vector_transform */ t1.user_id, sum(t2.object_id)
  2  from  t1, t2
  3  where t1.username = t2.owner
  4  and   t1.created > date '2010-01-01'
  5  group by t1.user_id;

   USER_ID SUM(T2.OBJECT_ID)
---------- -----------------
       123         277524080
        78          12515600
        76         455244340
        77          15649800
        13            263220
        70          23542880
       112          26827180
        21          36468280
       119         443965680
       133          44408980
       102          63142420
       110          13199020
       135           2022200
        48          20167400
        75        5047456440
         8         114574960
       107          14929040
       131         409317020
       114        6841454800
        99          34824620
        50         412239440
       109         264739000
         7          22023000
        36           1673880
       104         107720000
       105         574160040
        49           1836480
   1279990         534376160
       122         505036740
       106          81633100
       111          65992680
        79        3195864640
        62         156119860
        73         630464400
        82          43107560
        92         414092220
         0        3.2375E+10

37 rows selected.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------
SQL_ID  8ufhrc2vcpd7h, child number 0
-------------------------------------
select /*+ vector_transform */ t1.user_id, sum(t2.object_id) from  t1,
t2 where t1.username = t2.owner and   t1.created > date '2010-01-01'
group by t1.user_id

Plan hash value: 272704472

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                           |       |       |  5085 (100)|          |
|   1 |  TEMP TABLE TRANSFORMATION        |                           |       |       |            |          |
|   2 |   LOAD AS SELECT                  |                           |       |       |            |          |
|   3 |    VECTOR GROUP BY                |                           |    52 |  1300 |   103  (98)| 00:00:01 |
|   4 |     KEY VECTOR CREATE BUFFERED    | :KV0000                   |    52 |  1300 |   103  (98)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL            | T1                        |    52 |  1092 |     3   (0)| 00:00:01 |
|   6 |   HASH GROUP BY                   |                           |    52 |  5616 |  4982  (94)| 00:00:01 |
|*  7 |    HASH JOIN                      |                           |    52 |  5616 |  4981  (94)| 00:00:01 |
|   8 |     VIEW                          | VW_VT_AE9E49E8            |    52 |  4316 |  4979  (94)| 00:00:01 |
|   9 |      VECTOR GROUP BY              |                           |    52 |   780 |  4979  (94)| 00:00:01 |
|  10 |       HASH GROUP BY               |                           |    52 |   780 |  4979  (94)| 00:00:01 |
|  11 |        KEY VECTOR USE             | :KV0000                   |  1862K|    26M|  4979  (94)| 00:00:01 |
|* 12 |         TABLE ACCESS INMEMORY FULL| T2                        |  1862K|    19M|   329   (5)| 00:00:01 |
|  13 |     TABLE ACCESS FULL             | SYS_TEMP_0FD9D6607_4B9CA6 |    52 |  1300 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

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

   5 - filter("T1"."CREATED">TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   7 - access("ITEM_5"=INTERNAL_FUNCTION("C0") AND "ITEM_6"="C2")
  12 - inmemory(SYS_OP_KEY_VECTOR_FILTER("T2"."OWNER",:KV0000))
       filter(SYS_OP_KEY_VECTOR_FILTER("T2"."OWNER",:KV0000))

Note
-----
   - vector transformation used for this statement


39 rows selected.

SQL>
SQL>


"vector_transform" Hint

Rajeshwaran, March 15, 2017 - 9:42 am UTC

SQL> select /*+ vector_transform */ t1.user_id, sum(t2.object_id)
  2  from  t1, t2
  3  where t1.username = t2.owner
  4  and   t1.created > date '2010-01-01'
  5  group by t1.user_id;


the vector_transform should be picked by the optimizer based on the size of the objects and other set of heuristics and not be induced by the hints ? any reason to have "vector_transform" Hint in this sql?
Connor McDonald
March 16, 2017 - 1:18 am UTC

The question was - is a vector transform *prohibited* if one of the tables is not inmem. This shows that it is not.

Underscore Parameters?

John Keymer, March 15, 2017 - 4:11 pm UTC

Thanks for the link to the presentation Maria, very interesting and informative. However:

If you can’t avoid spilling to temp do it efficiently
• Increase size of temp writes from 200KB to 1MB
• Set _smm_auto_max_io_size =1024

How "Safe" is that? We're always very wary of setting underscore parameters.
Connor McDonald
March 16, 2017 - 1:20 am UTC

"We're always very wary of setting underscore parameters"

is a very good approach to take.


As with any underscore parameter, I always advocate logging an SR to get an endorsement, or at least noting the whitepaper etc.

Because next time you're dealing with Support, it avoids a "debate" about why the parameter is there etc etc.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.