Oracle 12c TABLE ACCESS BY INDEX ROWID BATCHED


Few days back exploring 12c DB on my small VM found this little new feature. Here is the output from 11gR2


SQL_ID 3a936zyv7jupf, child number 0
-------------------------------------
SELECT w.WAREHOUSE_NAME, w.LOCATION_ID, i.PRODUCT_ID,i.QUANTITY_ON_HAND
 FROM WAREHOUSES w, INVENTORIES i WHERE i.PRODUCT_ID IN (10,5,1)
 AND w.WAREHOUSE_ID = i.WAREHOUSE_ID

Plan hash value: 2661993828

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 61 (100)| | 2604 |00:00:00.09 | 40 | 30 | | | |
|* 1 | HASH JOIN | | 1 | 2700 | 105K| 61 (2)| 00:00:01 | 2604 |00:00:00.09 | 40 | 30 | 947K| 947K| 1298K (0)|
| 2 | TABLE ACCESS FULL | WAREHOUSES | 1 | 1000 | 26000 | 2 (0)| 00:00:01 | 1000 |00:00:00.01 | 6 | 6 | | | |
| 3 | INLIST ITERATOR | | 1 | | | | | 2604 |00:00:00.07 | 34 | 24 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| INVENTORIES | 3 | 2700 | 37800 | 58 (0)| 00:00:01 | 2604 |00:00:00.07 | 34 | 24 | | | |
|* 5 | INDEX RANGE SCAN | INV_PROD_IX | 3 | 2700 | | 8 (0)| 00:00:01 | 2604 |00:00:00.05 | 17 | 10 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

As you can see it’s a simple query, but the point which i want to make here is since INVENTORIES table is been accesed through rowid’s which we received from it’s child operation i.e. Index range scan. Now till 11gr2 this operation was doing a single IO’s (db file sequential read) to get rowid’s. Now here is same execution tree from 12C


SELECT w.WAREHOUSE_NAME, w.LOCATION_ID, i.PRODUCT_ID,i.QUANTITY_ON_HAND
 FROM WAREHOUSES w, INVENTORIES i
 WHERE i.PRODUCT_ID IN (10,5,1)
 AND w.WAREHOUSE_ID = i.WAREHOUSE_ID

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 4 127 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 0.16 0.19 63 42 0 2604
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.18 0.22 67 169 0 2604

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
 2604 2604 2604 HASH JOIN (cr=42 pr=63 pw=0 time=176064 us cost=64 size=108000 card=2700)
 1000 1000 1000 NESTED LOOPS (cr=7 pr=6 pw=0 time=29011 us)
 1000 1000 1000 NESTED LOOPS (cr=7 pr=6 pw=0 time=21009 us cost=64 size=108000 card=2700)
 1000 1000 1000 STATISTICS COLLECTOR (cr=7 pr=6 pw=0 time=14006 us)
 1000 1000 1000 TABLE ACCESS STORAGE FULL WAREHOUSES (cr=7 pr=6 pw=0 time=7002 us cost=4 size=26000 card=1000)
 0 0 0 INLIST ITERATOR (cr=0 pr=0 pw=0 time=0 us)
 0 0 0 INDEX UNIQUE SCAN INVENTORY_PK (cr=0 pr=0 pw=0 time=0 us cost=8 size=0 card=2700)(object id 92293)
 0 0 0 TABLE ACCESS BY INDEX ROWID INVENTORIES (cr=0 pr=0 pw=0 time=0 us cost=60 size=42 card=3)
 2604 2604 2604 INLIST ITERATOR (cr=35 pr=57 pw=0 time=74024 us)
 2604 2604 2604 TABLE ACCESS BY INDEX ROWID BATCHED INVENTORIES (cr=35 pr=57 pw=0 time=49014 us cost=60 size=37800 card=2700)
 2604 2604 2604 INDEX RANGE SCAN INV_PROD_IX (cr=17 pr=25 pw=0 time=22003 us cost=8 size=0 card=2700)(object id 92372)
Elapsed times include waiting on following events:
 Event waited on Times Max. Wait Total Waited
 ---------------------------------------- Waited ---------- ------------
 SQL*Net message to client 4 0.00 0.00
 Disk file operations I/O 1 0.00 0.00
 db file sequential read 2 0.00 0.00
 db file scattered read 8 0.00 0.00
 SQL*Net message from client 3 0.41 0.80
 SQL*Net more data to client 10 0.00 0.00

If we see there is a new operation called “TABLE ACCESS BY INDEX ROWID BATCHED” which from wait events can see is doing multi block reads and here is data from trace file.


WAIT #140517340780192: nam='db file scattered read' ela= 1000 file#=6 block#=176960 blocks=8 obj#=92372 tim=6142407268
WAIT #140517340780192: nam='db file sequential read' ela= 1000 file#=6 block#=179575 blocks=1 obj#=92372 tim=6142408268
WAIT #140517340780192: nam='db file scattered read' ela= 0 file#=6 block#=176968 blocks=8 obj#=92372 tim=6142455285
WAIT #140517340780192: nam='db file scattered read' ela= 2000 file#=6 block#=176976 blocks=8 obj#=92372 tim=6142923447

count of blocks indeed match with CR on index and moreover doing multi block reads. Now to switch off this feature you can use hints or setup below parameters


SELECT /*+ NO_BATCH_TABLE_ACCESS_BY_ROWID(i)*/ w.WAREHOUSE_NAME, w.LOCATION_ID, i.PRODUCT_ID,i.QUANTITY_ON_HAND
 FROM WAREHOUSES w, INVENTORIES i
 WHERE i.PRODUCT_ID IN (10,5,1)
 AND w.WAREHOUSE_ID = i.WAREHOUSE_ID;

or

alter session set "_optimizer_batch_table_access_by_rowid"=FALSE;

------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 64 (100)| | 2604 |00:00:00.19 | 42 | | | |
|* 1 | HASH JOIN | | 1 | 2700 | 105K| 64 (0)| 00:00:01 | 2604 |00:00:00.19 | 42 | 1160K| 1160K| 1293K (0)|
| 2 | TABLE ACCESS STORAGE FULL | WAREHOUSES | 1 | 1000 | 26000 | 4 (0)| 00:00:01 | 1000 |00:00:00.01 | 7 | | | |
| 3 | INLIST ITERATOR | | 1 | | | | | 2604 |00:00:00.09 | 35 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| INVENTORIES | 3 | 2700 | 37800 | 60 (0)| 00:00:01 | 2604 |00:00:00.07 | 35 | | | |
|* 5 | INDEX RANGE SCAN | INV_PROD_IX | 3 | 2700 | | 8 (0)| 00:00:01 | 2604 |00:00:00.02 | 17 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------

Nice, new feature but note sure how much it will be helpful in OLTP !!!.

About these ads

About Taral
I am Rookie to Oracle Technology so let's see where it goes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: