1. I cached a table in Oracle12C inmemory as following.
SQL> SELECT A.SEGMENT_NAME,
2 SUM(A.INMEMORY_SIZE) / 1024 / 1024 / 1024,
3 SUM(BYTES) / 1024 / 0124 / 1024,
4 SUM(A.BYTES_NOT_POPULATED)
5 FROM V$IM_SEGMENTS A
6 WHERE A.SEGMENT_NAME = 'TAB_TRADE'
7 GROUP BY A.SEGMENT_NAME;
SEGMENT_NAME SUM(A.INMEMORY_SIZE)/1024/1024/1024 SUM(BYTES)/1024/0124/1024 SUM(A.BYTES_NOT_POPULATED)
---------------- ----------------------------------- ------------------------- --------------------------
TAB_TRADE 10.6615601 373.435484 0
2. When i execute a sql statement to query the table ,it still product much physical read.Why is that happen? I'm really confused ,so please help me. Thanks so much.
SQL> set autotrace on
SQL> set timing on
SQL> select /*+full(a)*/
2 count(*)
3 from TAB_TRADE a
4 where 1 = 1
5 and a.TR_MER = 10000
6 and (a.TR_STATUS = 1 or (a.TR_FEE_FAIL_MER = 1 and a.TR_STATUS = 0))
7 and a.TR_TS_STATUS = 0
8 and (CASE
9 WHEN a.TR_AUTH_TYPE = 3 AND TR_AUTH_OP_TIME is not null THEN
10 a.TR_AUTH_OP_TIME
11 ELSE
12 a.TR_DATETIME
13 END) >= to_date('2018-06-23 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
14 and (CASE
15 WHEN a.TR_AUTH_TYPE = 3 AND TR_AUTH_OP_TIME is not null THEN
16 a.TR_AUTH_OP_TIME
17 ELSE
18 a.TR_DATETIME
19 END) <= to_date('2018-09-21 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
20 and a.TR_CODE != '***';
COUNT(*)
----------
0
Elapsed: 00:00:03.28
Execution Plan
----------------------------------------------------------
Plan hash value: 924049422
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 65893 (6)| 00:00:03 | | |
| 1 | SORT AGGREGATE | | 1 | 38 | | | | |
| 2 | PARTITION RANGE ALL | | 367 | 13946 | 65893 (6)| 00:00:03 | 1 | 59 |
|* 3 | TABLE ACCESS INMEMORY FULL| TAB_TRADE | 367 | 13946 | 65893 (6)| 00:00:03 | 1 | 59 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - inmemory(("A"."TR_STATUS"=0 OR "A"."TR_STATUS"=1) AND "A"."TR_MER"=10000 AND
"A"."TR_TS_STATUS"=0 AND ("A"."TR_STATUS"=1 OR "A"."TR_FEE_FAIL_MER"=1 AND "A"."TR_STATUS"=0) AND CASE
WHEN ("A"."TR_AUTH_TYPE"=3 AND "TR_AUTH_OP_TIME" IS NOT NULL) THEN "A"."TR_AUTH_OP_TIME" ELSE
"A"."TR_DATETIME" END >=TIMESTAMP' 2018-06-23 23:59:59' AND CASE WHEN ("A"."TR_AUTH_TYPE"=3 AND
"TR_AUTH_OP_TIME" IS NOT NULL) THEN "A"."TR_AUTH_OP_TIME" ELSE "A"."TR_DATETIME" END <=TIMESTAMP'
2018-09-21 23:59:59' AND "A"."TR_CODE"<>U'***')
filter("A"."TR_MER_NO"=20455 AND "A"."TR_TS_STATUS"=0 AND ("A"."TR_STATUS"=1 OR
"A"."TR_FEE_FAIL_MER"=1 AND "A"."TR_STATUS"=0) AND CASE WHEN ("A"."TR_AUTH_TYPE"=3 AND
"TR_AUTH_OP_TIME" IS NOT NULL) THEN "A"."TR_AUTH_OP_TIME" ELSE "A"."TR_DATETIME" END >=TIMESTAMP'
2018-06-23 23:59:59' AND CASE WHEN ("A"."TR_AUTH_TYPE"=3 AND "TR_AUTH_OP_TIME" IS NOT NULL) THEN
"A"."TR_AUTH_OP_TIME" ELSE "A"."TR_DATETIME" END <=TIMESTAMP' 2018-09-21 23:59:59' AND
"A"."TR_CODE"<>U'***')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
601 recursive calls
1 db block gets
14564 consistent gets
1317 physical reads
404 redo size
541 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
First up - your query is doing a
lot of recursive calls (601). These are queries the database runs to parse your query, do dynamic sampling, etc.
It's possible that the physical reads are coming from these.
What happens if you execute the query twice? Do you still see the same recursive calls and physical reads?
If you still get physical reads once the recursive calls are gone, remember:
The plan will show an In-Memory full scan if uses the In-Memory Column Store. Even if has to access some rows for the object that are on disk or the traditional buffer cache. Obvious example: when you have some partitions In-Memory and some not.
Martin Bach demonstrates this here:
https://martincarstenbach.wordpress.com/2015/11/13/table-access-inmemory-full-but-there-may-be-more/ Do you have In-Memory enables on all your partitions?
Finally, the plan shown by set autotrace on may not be the real plan. So this may be incorrectly reporting an In-Memory access.
Ensure you get the real plan by running:
set serveroutput off
select /*+ gather_plan_statistics */ <your query> ;
select *
from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));