Verifying In-Memory Access

Before You Begin

This 10-minute tutorial shows you how to populate tables in the In-Memory Column Store (IM column store), query them, and then read the execution plan to determine whether the query accessed In-Memory data.

Background

The IM column store maintains copies of tables, partitions, and individual columns in a special compressed columnar format that is optimized for rapid scans. The IM column store resides in the In-Memory Area, which is an optional portion of the system global area (SGA).

You use DDL statements to populate tables and partitions into the IM column store. To find out whether tables and partitions are fully populated, query V$IM_SEGMENTS. To find out whether a query accessed a populated table, look for the keyword INMEMORY in the Operation column of the query plan.

This tutorial illustrates a performance optimization known as a Bloom filter. The database creates an array by scanning one table, and then uses the array to filter rows while scanning another table. In-Memory queries often use Bloom filters.

What Do You Need?

Oracle Database 18c test database
A database administrator account
Sample schemas installed
IM column store enabled
Familiarity with IM column store table creation and population
Familiarity with displaying and reading execution plans

Set Up the Environment

Ensure that the IM column store is enabled by querying its size.

SQL> COL NAME FORMAT a20
SQL> SELECT NAME, VALUE/(1024*1024) AS SIZE_MB FROM V$SGA WHERE NAME='In-Memory Area';

NAME                    SIZE_MB
-------------------- ----------
In-Memory Area              352

Remove any existing cursors and memory buffers.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

Disable adaptive statistics and SQL plan management, so they won't be used when you rerun the sample query.

SQL> ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_STATISTICS=FALSE SCOPE=MEMORY;

System altered.

SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE SCOPE=MEMORY;

System altered.

Set the level of statistics gathering to ALL so that all plan statistics are gathered.
```
SQL> ALTER SYSTEM SET STATISTICS_LEVEL=ALL SCOPE=MEMORY;

System altered.
```

Insert 100,000 rows into customers.

SQL> INSERT INTO sh.customers 
  2    SELECT rownum+104500,'Shivani','Balasubramanian','F',1966,null,'1234 Southland Drive',99922,'Los Angeles',51806,'CA',52567,52790,'777-222-5555',null,null,null,'Customer total',52772,null,null,null,null 
  3    FROM DUAL 
  4    CONNECT BY ROWNUM<100001;

100000 rows created.

SQL> COMMIT

Gather optimizer statistics for sales and customers.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS ('SH', 'SALES');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS ('SH', 'CUSTOMERS');

PL/SQL procedure successfully completed.

Ensure that sales and customers are not currently populated.

SQL> ALTER TABLE sh.sales NO INMEMORY;

Table altered.

SQL> ALTER TABLE sh.customers NO INMEMORY;

Table altered.

Populate sales into the IM Column Store

Enable sales, but not customers, for In-Memory access.
```
SQL> ALTER TABLE sh.sales INMEMORY;

Table altered.
```
The sales table has the default priority level of NONE. The database won't automatically populate the table, so you need to populate manually.

Use the FULL hint to force a full scan of sales, which initiates population.

SQL> SET LINESIZE 170
SQL> SET PAGESIZE 5000
SQL> SELECT /*+ FULL(sales) NO_PARALLEL(sales) */ COUNT(*) FROM sh.sales;

  COUNT(*)
----------
    918843

Confirm that all 16 sales partitions are populated in the IM column store.

SQL> SELECT COUNT(*) FROM V$IM_SEGMENTS WHERE SEGMENT_NAME = 'SALES' AND POPULATE_STATUS = 'COMPLETED';

  COUNT(*)
----------
        16

Test Query Access of the IM Column Store

Join sales to customers, filtering on the cust_last_name column. The PRE_IMPOP comment enables you to identify the query in V$SQL.

SQL> COL cust_last_name FORMAT a15
SQL> COL cust_first_name FORMAT a15
SQL> SELECT /* PRE_IMPOP */ c.cust_id, c.cust_last_name, c.cust_first_name, SUM(s.amount_sold)
  2  FROM sh.sales s, sh.customers c
  3  WHERE s.cust_id = c.cust_id
  4  AND c.cust_last_name LIKE 'Aa%'
  5  GROUP BY c.cust_id, c.cust_last_name, c.cust_first_name
  6  ORDER BY c.cust_id;

The first 5 rows of the output are shown below:

    CUST_ID CUST_LAST_NAME  CUST_FIRST_NAME SUM(S.AMOUNT_SOLD)
---------- --------------- --------------- ------------------
       451 Aaron           Dalila                    17181.64
      1271 Aaron           Tara                      14873.47
      1981 Aaron           Opal                      26258.07
      3404 Aaron           Dolly                     18217.73
      4115 Aaron           Abel                       4872.26
...

Display the query plan.

SQL> SET PAGESIZE 0
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'TYPICAL'));

The complete output is in code1.txt.

Read the plan to determine whether the query accessed the IM column store.

The plan steps are shown below:

------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |       |       |   951 (100)|          |       |       |
|   1 |  SORT GROUP BY                 |           |  3280 |   115K|   951   (2)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                    |           | 22267 |   782K|   950   (2)| 00:00:01 |       |       |
|   3 |    JOIN FILTER CREATE          | :BF0000   |   171 |  4446 |   917   (1)| 00:00:01 |       |       |
|*  4 |     TABLE ACCESS FULL          | CUSTOMERS |   171 |  4446 |   917   (1)| 00:00:01 |       |       |
|   5 |    JOIN FILTER USE             | :BF0000   |   918K|  8973K|    31  (20)| 00:00:01 |       |       |
|   6 |     PARTITION RANGE ALL        |           |   918K|  8973K|    31  (20)| 00:00:01 |     1 |    28 |
|*  7 |      TABLE ACCESS INMEMORY FULL| SALES     |   918K|  8973K|    31  (20)| 00:00:01 |     1 |    28 |

Step 2 indicates a hash join. In Step 4, the database scans customers. The operation TABLE ACCESS FULL does not include the keyword INMEMORY, which means that the database reads from the buffer cache, not the IM column store. Note that the cost of the customers scan is 917, which is 96% of the total query cost of 951.

Step 7 includes the keyword INMEMORY, meaning that the database scans sales in the IM column store. In Step 3, the database creates a Bloom filter named :BF0000. Step 5 indicates that the query uses the Bloom filter created in Step 3 to filter rows while scanning the sales table. The cost of the full scan of sales is only 3% of the cost of the full scan of customers, even though sales has around 6 times as many rows.

Populate customers into the IM Column Store

Flush the shared pool again so that the second query has a similar environment to the first query.
```
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.
```

Enable customers for In-Memory access.

SQL> ALTER TABLE sh.customers INMEMORY;

Table altered.

Use the FULL hint to force a full scan of customers.

SQL> SET LINESIZE 170
SQL> SET PAGESIZE 5000
SQL> SELECT /*+ FULL(customers) NO_PARALLEL(customers) */ COUNT(*) FROM sh.customers;

  COUNT(*)
----------
    155500

Confirm that customers is fully populated in the IM column store.

SQL> SELECT COUNT(*) FROM V$IM_SEGMENTS WHERE SEGMENT_NAME = 'CUSTOMERS' AND POPULATE_STATUS = 'COMPLETED';

  COUNT(*)
----------
         1

Re-Execute the Query

Rerun the join query. The POST_IMPOP comment enables you to identify the query in V$SQL.

SQL> COL cust_last_name FORMAT a15
SQL> COL cust_first_name FORMAT a15
SQL> SELECT /* POST_IMPOP */ c.cust_id, c.cust_last_name, c.cust_first_name, SUM(s.amount_sold)
  2  FROM sh.sales s, sh.customers c
  3  WHERE s.cust_id = c.cust_id
  4  AND c.cust_last_name LIKE 'Aa%'
  5  GROUP BY c.cust_id, c.cust_last_name, c.cust_first_name
  6  ORDER BY c.cust_id;

View the query plan stored in the cursor.

SQL> SET PAGESIZE 0
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'TYPICAL'));

The complete output is shown in code2.txt.

Read the plan to determine whether the query accessed the IM column store.

The plan steps are shown below:

------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |       |       |    71 (100)|          |       |       |
|   1 |  SORT GROUP BY                 |           |  3280 |   115K|    71  (17)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                    |           | 22267 |   782K|    69  (15)| 00:00:01 |       |       |
|   3 |    JOIN FILTER CREATE          | :BF0000   |   171 |  4446 |    36   (6)| 00:00:01 |       |       |
|*  4 |     TABLE ACCESS INMEMORY FULL | CUSTOMERS |   171 |  4446 |    36   (6)| 00:00:01 |       |       |
|   5 |    JOIN FILTER USE             | :BF0000   |   918K|  8973K|    31  (20)| 00:00:01 |       |       |
|   6 |     PARTITION RANGE ALL        |           |   918K|  8973K|    31  (20)| 00:00:01 |     1 |    28 |
|*  7 |      TABLE ACCESS INMEMORY FULL| SALES     |   918K|  8973K|    31  (20)| 00:00:01 |     1 |    28 |

The operations are the same except for Step 4. Now the operation

TABLE
                    ACCESS FULL

includes the keyword INMEMORY, which means that the operation scans customers in the IM column store.

The cost of the full scan of customers is now about half of the total cost of the query, as compared to 96% of the total cost in the first query. The cost of the customers scan is now only slightly higher than the cost of the sales scan. Accessing customers in the IM column store makes the query more efficient.

Compare the Query Times

Query V$SQL for the elapsed times of the two queries.

SQL> SET PAGESIZE 5000
SQL> COL SQL_TEXT FORMAT a45
SQL> SELECT /* SYSQUERY */ SUBSTR(SQL_TEXT,1,45) AS SQL_TEXT, ELAPSED_TIME
  2  FROM V$SQL
  3  WHERE SQL_TEXT LIKE '%IMPOP%'
  4  AND SQL_TEXT NOT LIKE '%SYSQUERY%'
  5  ORDER BY ELAPSED_TIME;

SQL_TEXT                                      ELAPSED_TIME
--------------------------------------------- ------------
SELECT /* POST_IMPOP */ c.cust_id, c.cust_las        51610
SELECT /* PRE_IMPOP */ c.cust_id, c.cust_last       193338

The second query, which accessed only In-Memory data, ran almost 4 times as fast as the first query.

Clean Up

Reset initialization parameters to default values.

SQL> ALTER SYSTEM RESET OPTIMIZER_ADAPTIVE_STATISTICS SCOPE=MEMORY;

System altered.

SQL> ALTER SYSTEM RESET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES SCOPE=MEMORY;

System altered.

SQL> ALTER SYSTEM RESET STATISTICS_LEVEL SCOPE=MEMORY;

System altered.

Remove the In-Memory representations of sales and customers from the IM column store.

SQL> ALTER TABLE sh.sales NO INMEMORY;

Table altered.

SQL> ALTER TABLE sh.customers NO INMEMORY;

Table altered.

Delete the added rows from customers.

SQL> DELETE FROM sh.customers WHERE cust_id > 104500;

100000 rows deleted.

SQL> COMMIT