FTS on small Materialized View, should I cache it in the KEEP Pool ?

From: <krislioe_at_gmail.com>
Date: Fri, 6 Jun 2008 00:29:24 -0700 (PDT)
Message-ID: <6e592059-5080-4908-a847-d7921412100a@v26g2000prm.googlegroups.com>


Hi all,

I have a small MV (1773 rows) that is used in a Query JOIN (the query & the explain plan is attached below). Although I already create index for the MV, it is always FTS in the query. I read a Tuning tips, that FTS on small table should be cached in the KEEP POOL, with this command :
ALTER TABLE ITT.MV_CONVERT_UOM STORAGE (BUFFER_POOL KEEP); Should I do this ?

Thank you for your help,
xtanto.

Query & explain PLAN :
SELECT so_id_hdr, product_ord, qty_ord, UOM, MV.UOM_B, MV.UOM_K FROM SALESORDER_D SOD
JOIN MV_CONVERT_UOM MV ON MV.PRODUCT = SOD.PRODUCT_ORD WHERE SO_id_hdr = 31944

[pre]
Plan hash value:

1323612888-----------------------------------------------------------------------------------------------------------------------------------|
Id  | Operation                         | Name             | Rows  |
Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib
|-----------------------------------------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT                  |                  |     5 |
225 |     5  (20)| 00:00:01 |        |      |            ||   1 |  PX
COORDINATOR                   |                  |       |
|            |          |        |      |            ||   2 |   PX
SEND QC (RANDOM)             | :TQ10001         |     5 |   225 |
5  (20)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  ||*  3 |    HASH
JOIN                      |                  |     5 |   225 |     5

(20)| 00:00:01 | Q1,01 | PCWP | || 4 | BUFFER
SORT | | | | | | Q1,01 | PCWC | || 5 | PX RECEIVE | | 5 | 135 | 2
(0)| 00:00:01 | Q1,01 | PCWP | || 6 | PX SEND
BROADCAST | :TQ10000 | 5 | 135 | 2 (0)| 00:00:01 | | S->P | BROADCAST || 7 | TABLE ACCESS BY INDEX ROWID| SALESORDER_D | 5 | 135 | 2 (0)| 00:00:01 | | | ||* 8 | INDEX RANGE SCAN | SALESORDER_D_FKH | 5 | | 1 (0)| 00:00:01 | | | || 9 | PX BLOCK ITERATOR | | 1773 | 31914 | 2 (0)| 00:00:01 | Q1,01 | PCWC | || 10 | MAT_VIEW ACCESS FULL | MV_CONVERT_UOM | 1773 | 31914 | 2 (0)|
00:00:01 | Q1,01 | PCWP |
|-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation
id):---------------------------------------------------    3 -
access("MV"."PRODUCT"="SOD"."PRODUCT_ORD") 8 - access("SOD"."SO_ID_HDR"=31944)

[/pre] Received on Fri Jun 06 2008 - 02:29:24 CDT

Original text of this message