TABLE ACCESS INMEMORY FULL – but there may be more
Posted by Martin Bach on November 13, 2015
While preparing to teach a class this week I have had the time to look into the In Memory (cost) option a bit closer. I noticed a few interesting things along the way and would like to share one of these here.
Background
One of the questions I was asking myself was:
“What happens if I scan segments that are within the IM area, and some are not?”
I was primarily thinking of joins in a DWH environment, but in order to keep the test case simple enough and reproducible I decided to go with a partitioned table where the current partition is assigned to the IMCS, and the rest is not. For this test I’m relying on the Swingbench SH schema. All of this runs on my laptop in a VM so I had to be selective when it comes to indexes. I also chose to NOT partition the tables at this stage, I wanted to chose my own partitioning scheme. For reference, here is the command that created the SH schema:
[oracle@oraclelinux7 bin]$ ./shwizard -cl -create -cs //localhost/sh_pdb \ > -dba system -dbap somePassword -nopart -pkindexes -scale 1 -tc 2 -u sh -p sh ... ============================================ | Datagenerator Run Stats | ============================================ Connection Time 0:00:00.005 Data Generation Time 0:04:35.890 DDL Creation Time 0:00:14.063 Total Run Time 0:04:49.962 Rows Inserted per sec 64,707 Data Generated (MB) per sec 5.3 Actual Rows Generated 17,848,007 ...
I wanted to create a range partitioned version of the sales table. After the SH-wizard completed the data distribution is as shown:
SQL> select count(*), trunc(time_id,'yy') year from sales group by trunc(time_id,'yy') order by year;
COUNT(*) YEAR
---------- ---------
130653 01-JAN-95
149319 01-JAN-96
251974 01-JAN-97
326632 01-JAN-98
365547 01-JAN-99
388318 01-JAN-00
393919 01-JAN-01
406703 01-JAN-02
483605 01-JAN-03
566384 01-JAN-04
422289 01-JAN-05
619858 01-JAN-06
782244 01-JAN-07
1099551 01-JAN-08
1249340 01-JAN-09
1346025 01-JAN-10
1690302 01-JAN-11
2028048 01-JAN-12
2028048 01-JAN-13
19 rows selected.
So based on this information I can create a table that has data from 2013 in the IMCS and everything else will be excluded from it. For the sake of the following discussion 2013 is considered the “current” partition. Here is the partitioned sales table’s DDL with my changes to enable my partitioning scheme.
CREATE TABLE SALES_PART
(
PROD_ID NUMBER NOT NULL ENABLE,
CUST_ID NUMBER NOT NULL ENABLE,
TIME_ID DATE NOT NULL ENABLE,
CHANNEL_ID NUMBER NOT NULL ENABLE,
PROMO_ID NUMBER NOT NULL ENABLE,
QUANTITY_SOLD NUMBER(10,2) NOT NULL ENABLE,
SELLER NUMBER(6,0) NOT NULL ENABLE,
FULFILLMENT_CENTER NUMBER(6,0) NOT NULL ENABLE,
COURIER_ORG NUMBER(6,0) NOT NULL ENABLE,
TAX_COUNTRY VARCHAR2(3) NOT NULL ENABLE,
TAX_REGION VARCHAR2(3),
AMOUNT_SOLD NUMBER(10,2) NOT NULL ENABLE
)
partition BY range (time_id)
(
partition p_old VALUES less than (DATE '2001-01-01'),
partition p_2000_2010 VALUES less than (DATE '2011-01-01'),
partition p_2011 VALUES less than (DATE '2012-01-01'),
partition p_2012 VALUES less than (DATE '2013-01-01'),
partition p_2013 VALUES less than (DATE '2014-01-01') inmemory
)
TABLESPACE SH;
SQL> alter session enable parallel dml;
Session altered.
SQL> insert /*+ parallel append */ into sales_part select /*+ parallel */ * from sales
2 /
14728759 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(user,'SALES_PART')
PL/SQL procedure successfully completed.
SQL> select partition_name, num_rows, inmemory
2 from user_tab_partitions
3 where table_name = 'SALES_PART'
4 order by partition_position;
PARTITION_NAME NUM_ROWS INMEMORY
------------------------------ ---------- --------
P_OLD 1612443 DISABLED
P_2000_2010 7369918 DISABLED
P_2011 1690302 DISABLED
P_2012 2028048 DISABLED
P_2013 2028048 ENABLED
So that should give me what I needed. The IMCS was now populated with the segment as well:
SQL> select segment_name, partition_name, bytes_not_populated, populate_status from v$im_user_segments; SEGMENT_NAME PARTITION_NAME BYTES_NOT_POPULATED POPULATE_ -------------------- ------------------------------ ------------------- --------- SALES_PART P_2013 0 COMPLETED
Test
With the setup work complete I am ready to test. First of all, a simple SQL trace should show me what is happening. Note that I am specifically targeting the “current” partition.
SQL> sho user
USER is "SH"
SQL> alter session set events 'sql_trace level 8';
Session altered.
SQL> select /* current_partition */ count(*) from sales_part
2 where time_id > DATE '2013-01-02'
3 and promo_id = 316;
COUNT(*)
----------
3947
SQL> alter session set events 'sql_trace off';
Session altered.
SQL> select value from v$diag_info where name like 'Def%';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/cdb/CDB/trace/CDB_ora_14034.trc
The tkprof’d trace shows this result:
********************************************************************************
select /* current_partition */ count(*) from sales_part
where time_id > DATE '2013-01-02'
and promo_id = 316
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 5 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 117
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=8441 us)
3947 3947 3947 PARTITION RANGE SINGLE PARTITION: 5 5 (cr=3 pr=0 pw=0 time=7583 us cost=214 size=48252 card=4021)
3947 3947 3947 TABLE ACCESS INMEMORY FULL SALES_PART PARTITION: 5 5 (cr=3 pr=0 pw=0 time=6972 us cost=214 size=48252 card=4021)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 6.79 6.79
********************************************************************************
As you can see, my partitioning strategy paid off-only the 5th partition is accessed (that’s p_2013 or the “current” partition based on the data). This partition is the one entirely found in the IMCS.
A result like the one above is what I’d expect and the access path “TABLE ACCESS INMEMORY FULL” does it justice. But now the actual reason for the blog post: mixing scans against segments in memory and on disk. Logging on again I ran the same query but without the restriction to 2013.
SQL> alter session set events 'sql_trace level 8';
Session altered.
SQL> select /* every_partition */ count(*) from sales_part
2 where promo_id = 316;
COUNT(*)
----------
28993
SQL> alter session set events 'sql_trace off';
Session altered.
SQL> select value from v$diag_info where name like 'Def%';
VALUE
----------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/cdb/CDB/trace/CDB_ora_14437.trc
The tkprof’d result is shown here:
select /* every_partition */ count(*) from sales_part
where promo_id = 316
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 3.15 3.84 94878 94901 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 3.15 3.84 94878 94901 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 117
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=94901 pr=94878 pw=0 time=3848392 us)
28993 28993 28993 PARTITION RANGE ALL PARTITION: 1 5 (cr=94901 pr=94878 pw=0 time=4503583 us cost=26217 size=117128 card=29282)
28993 28993 28993 TABLE ACCESS INMEMORY FULL SALES_PART PARTITION: 1 5 (cr=94901 pr=94878 pw=0 time=4254867 us cost=26217 size=117128 card=29282)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
Disk file operations I/O 4 0.00 0.00
db file sequential read 6 0.00 0.01
direct path read 42 0.01 0.18
SQL*Net message from client 2 7.63 7.63
********************************************************************************
Again the access path is clearly indicated as “TABLE ACCESS INMEMORY FULL SALES_PART”. This time it references all 5 partitions (which is expected since I didn’t have a date in my predicate list). And I can see direct path reads plus some other I/O related information! Direct Path Reads are quite likely going to be Smart Scans on Exadata by the way…
Of course “TABLE ACCESS INMEMORY FULL” is correct (because one partition is accessed that way), but I guess that you cannot see the disk-based I/O against the other segments from the regular execution plan as shown in SQLPlus for example .
Is there any other instrumentation I could use?
The tkprof output shows that data retrieval is possible for segments that are not (entirely) found in the IMCS. Where else could I learn about this fact? Session statistics (V$STATNAME, V$SESSTAT, etc) are another useful source of information. I am a great fan of session snapper (who isn’t?). Snapper can be used to display the change in the session counters while the session you are monitoring is running. Another option is Adrian Billington’s mystats script. It can help you capture the changes in session statistics during the execution of a SQL command. I executed the same query again, sandwiched into calls to mystats. Note that the statistics shown next do not match those of to the execution of the query above. I also tried to limit the output only to the relevant ones, output is sorted by statistic name.
------------------------------------------------------------------------------------------ 2. Statistics Report ------------------------------------------------------------------------------------------ Type Statistic Name Value ------ ---------------------------------------------------------------- ---------------- STAT IM scan CUs columns accessed 4 STAT IM scan CUs columns theoretical max 48 STAT IM scan CUs memcompress for query low 4 STAT IM scan CUs predicates applied 4 STAT IM scan CUs predicates received 4 STAT IM scan CUs split pieces 4 STAT IM scan bytes in-memory 47,683,308 STAT IM scan bytes uncompressed 99,118,124 STAT IM scan rows 2,028,048 STAT IM scan rows projected 3,963 STAT IM scan rows valid 2,028,048 STAT IM scan segments minmax eligible 4 STAT consistent gets 95,093 STAT consistent gets direct 94,872 STAT consistent gets examination 4 STAT consistent gets examination (fastpath) 4 STAT consistent gets from cache 221 STAT consistent gets pin 217 STAT consistent gets pin (fastpath) 217 STAT logical read bytes from cache 9,666,560 STAT no work - consistent read gets 94,871 STAT physical read IO requests 749 STAT physical read bytes 777,191,424 STAT physical read total IO requests 749 STAT physical read total bytes 777,191,424 STAT physical read total multi block requests 749 STAT physical reads 94,872 STAT physical reads direct 94,872 STAT session logical reads 111,201 STAT session logical reads - IM 15,149 STAT table scan blocks gotten 94,872 STAT table scan disk non-IMC rows gotten 12,700,711 STAT table scan rows gotten 14,728,759 STAT table scans (IM) 1 STAT table scans (direct read) 4 STAT table scans (long tables) 5 STAT temp space allocated (bytes) 1,048,576 STAT workarea executions - optimal 1 STAT workarea memory allocated 5 ------------------------------------------------------------------------------------------ 3. About ------------------------------------------------------------------------------------------ - MyStats v2.01 by Adrian Billington (http://www.oracle-developer.net) - Based on the SNAP_MY_STATS utility by Jonathan Lewis ========================================================================================== End of report ==========================================================================================
The highlights are:
- You can see the compression ratio of the data in the IMCS (IM scan bytes in-memory and IM scan bytes uncompressed)
- All rows in the partition were valid (the number of IM Scan rows equals the number of rows in the partition)
- One segment was scanned using IM, and 4 were scanned as direct (path) reads. A total of 5 segments were scanned
Hope this helps!
Jonathan Lewis said
It would be interesting to see if it’s possible for the optimizer to do table expansion – choosing one path for the INMEMORY segments and another path for the rest. You could imagine a local index might be appropriate for the “right” amount of data from the non-IM segments while a segment scan would be seen as more efficient for the same amount of data from the IM segment.
Martin Bach said
Hi Jonathan,
thanks for passing by. This is not quite as straight forward as I thought-experimenting with local index on promo_id and other settings but so far haven’t been able to reproduce the desired table expansion … will let you know once I succeed with that.
Martin Bach said
OK it works- I created a local index on promo_id and with that and the correct predicates I get the table expansion. I’ll write it up in another post…