I am trying to measure the query execution time in oracle 12c for in-memory database against disc based database. At client side, I am using JDBC to fire the query.
How do I know that whether the query looks for required database in in-memory or in disc? Is there any option that tells the oracle db server that look for the database first into in-memory and then into disc?
How do I know that whether the query looks for required database in in-memory or in disc?
You can see that whether the oracle has looked into the memory or disk for data in the Execution Plan as shown below.
SQL> desc t1
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 VARCHAR2(20)
COL2 VARCHAR2(20)
SQL> alter table t1 inmemory;
Table altered.
SQL> explain plan for select * from t1;
Explained.
SQL> select * from table(dbms_xplan.display());
Plan hash value: 3617692013
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 0 (0)|
| 1 | TABLE ACCESS INMEMORY FULL| T1 | 1 | 24 | |
------------------------------------------------------------------------
8 rows selected.
SQL> alter table t1 no inmemory;
Table altered.
SQL> explain plan for select * from t1;
Explained.
SQL> select * from table(dbms_xplan.display());
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 1 | 24 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
Is there any option that tells the oracle db server that look for the database first into in-memory and then into disc?
Oracle Doc says-
INMEMORY_QUERY is used to enable or disable in-memory queries for the entire database at the session or system level. This parameter is helpful when you want to test workloads with and without the use of the In-Memory Column Store (IM column store).
You disable the in-memory query as:
SQL> alter session set inmemory_query = disable;
You give INMEMORY hint to optimizer to look into the memory for the data as shown below.
select /*+ INMEMORY */ * from t1;