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>
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 | 500:00:01 | Q1,01 | PCWP |
(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)|
|-----------------------------------------------------------------------------------------------------------------------------------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