Home » Server Options » Replication » Fast Refresh of materialized view too slow (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0)
Fast Refresh of materialized view too slow [message #328958] Mon, 23 June 2008 09:37 Go to next message
ajayselhi
Messages: 4
Registered: June 2008
Junior Member
Our customer's transaction data are stored on the following table on our operational database.

1) accounts
2) transactions
3) transaction_apportionments
4) customer_delivery_items
5) products
6) flight_segment_operations

2,3,and 4 are partitioned tables.

The transaction_apportionments table is the detail table for each transaction and has around 160 million records.
The database cursor which pulls the data from all this tables to display the transactions on the members' statement page on the website does not perform fast enough when the volume of transactions of the member is large.

To overcome this problem and to speed up the cursor we are planning to build a materialized view by joining all the 6 tables and to change the cursor so that it selects the data from the materialized view.

All the base tables are lying on a schema called OCEAN, so all the MV log were created on the same schema. The MV was created on a different schema as this was under development, and we don't have rights on the OCEAN schema. However in LIVE all the base tables and MV logs and MV will reside in the OCEAN schema.

The below are the steps we have taken so far:

1) Created the materialized view log with ROWID, SEQUENCE on all the tables.

create materialized view log on transactions WITH ROWID, SEQUENCE;
create materialized view log on transaction_apportionments WITH ROWID, SEQUENCE;
create materialized view log on customer_delivery_items WITH ROWID, SEQUENCE;
create materialized view log on products WITH ROWID, SEQUENCE;
create materialized view log on flight_segment_operations WITH ROWID, SEQUENCE;
create materialized view log on accounts WITH ROWID, SEQUENCE;

ORACLE created 1 each materialized view log tables for all these base tables. The structure of these log tables were as below

SQL> desc "OCEAN"."MLOG$_TRANSACTION_APPORTIO"
Name Null? Type
------ -------------
M_ROW$$ VARCHAR2(255)
SEQUENCE$$ NUMBER
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)

2) Created materialized view by joining all the tables on keys. The REFRESH FAST ON COMMIT was selected are we want the data to be refreshed on the view as soon as the base tables are updated.
The view was created successfully in about 3.5 hrs which has around 160 million records.

CREATE MATERIALIZED VIEW mv_statement
NOLOGGING
PARALLEL BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT t.rowid trowid,
ta.rowid tarowid,
cdi.rowid cdirowid,
p.rowid prowid,
fso.rowid fsorowid,
a.rowid arowid,
a.owner_cid,
t.tx_id,
t.tx_typ_cd,
t.cre_dt,
t.tx_vlu,
t.cpgn_cd,
t.cre_user_id tcre_user_id,
t.TX_AUDIT_LOC_CD,
t.TX_AUDIT_LYTY_PRG_CD,
ta.chrg_cid,
ta.tx_apmt_id,
ta.dlv_itm_id,
ta.tx_apmt_typ_cd,
ta.tx_apmt_reasn_cd,
ta.apmt_txt,
ta.ccy_cd,
ta.tx_apmt_vlu,
ta.RULE_ID,
cdi.prod_id,
cdi.tm_pd_qty,
cdi.itm_qty,
cdi.cust_ord_id,
cdi.curr_sts_cd,
cdi.line_no,
cdi.cid,
cdi.Retro_ind,
cdi.ON_HOLD_IND,
cdi.PARTNER_REF_Cd,
cdi.SRC_ORG_CD,
cdi.SRC_ORG_CD_TYP_CD,
cdi.curr_sts_dt,
cdi.cre_dt cdi_cre_dt,
cdi.cre_user_id cdi_cre_user_id,
p.src_prod_id,
p.prod_typ_cd,
fso.mkg_aln_cx_cd,
fso.flt_no,
fso.opg_aln_cx_cd,
fso.slg_cls_cd,
fso.cpt_cd,
fso.trip_typ_cd,
fso.stn_from_cd,
fso.stn_to_cd,
fso.uplift_dt
FROM accounts a,
transactions t,
transaction_apportionments ta,
customer_delivery_items cdi,
products p,
flight_segment_operations fso
WHERE a.acc_id = t.acc_id
AND t.tx_id = ta.tx_id
AND ta.dlv_itm_id = cdi.dlv_itm_id (+)
AND cdi.prod_id = p.prod_id (+)
AND p.prod_id = fso.prod_id (+);


2) Created index on the owner_cid column of the Materialized view. This column will be used to select the data of the member from the view.

CREATE index nou_idx_mv_stat_cid on mv_statement(owner_cid) PARALLEL;

3) Created index on all the six ROWID columns (selected from the base tables ) of the Materialized view. These indexes were created to speed-up the FAST REFRESH.
CREATE index nou_idx_mv_stat_fsorowid on mv_statement(FSOROWID) PARALLEL (DEGREE 4);
CREATE index nou_idx_mv_stat_trowid on mv_statement(TROWID) PARALLEL (DEGREE 4);
CREATE unique index u_idx_mv_stat_tarowid on mv_statement(TAROWID) PARALLEL (DEGREE 4);
CREATE index nou_idx_mv_stat_cdrowid on mv_statement(CDIROWID) PARALLEL (DEGREE 4);
CREATE index nou_idx_mv_stat_prowid on mv_statement(PROWID) PARALLEL (DEGREE 4);
CREATE index nou_idx_mv_stat_arowid on mv_statement(AROWID) PARALLEL (DEGREE 4);

4) Also created a concatenated index on all the key columns of the base tables on the Materialized view ( as oracle recommends this for fast refresh).

CREATE unique index u_concat_key_idx_mv_stat on mv_statement(TX_APMT_ID,TX_ID,DLV_ITM_ID,PROD_ID) PARALLEL (DEGREE 4);

5) Altered the MV for doing the logging (The MV was created with NOLOGGING option)
ALTER MATERIALIZED VIEW mv_statement LOGGING;

6) Below are the statistics from the DBMS_MVIEW.EXPLAIN_MVIEW on this MV, which indicates the after any DML on bases tables the MV should be fast refreshed.

CAPABILITY NAME POSSIBLE
REFRESH_COMPLETE Y
REFRESH_FAST Y
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML Y
REFRESH_FAST_AFTER_ANY_DML Y

7) All the indexes on the Materialized view and the Materialized view table was analyzed.

analyze index u_concat_key_idx_mv_stat compute statistics;
analyze index nou_idx_mv_stat_cid compute statistics;
analyze index u_idx_mv_stat_tarowid compute statistics;
analyze index nou_idx_mv_stat_trowid compute statistics;
analyze index nou_idx_mv_stat_arowid compute statistics;
analyze index nou_idx_mv_stat_cdrowid compute statistics;
analyze index nou_idx_mv_stat_prowid compute statistics;
analyze index nou_idx_mv_stat_fsorowid compute statistics;

exec dbms_stats.gather_table_stats( ownname=>'WR15657',tabname=>'MV_STATEMENT',cascade=> true);

Cool Now when we do an UPDATE on one of the base tables and COMMIT the transaction, it takes around 15 minutes to complete the commit.

SQL> UPDATE transaction_apportionments SET tx_apmt_vlu='2896' WHERE tx_apmt_id='1074008472';

1 row updated.

Elapsed: 00:00:00.01
SQL> commit;

Commit complete.

Elapsed: 00:15:25.88

9) I was not sure why did it took so long to do the commit, so i asked our DBAs to generate the trace and repeated the same update and commit. The attached file (oq01_ora_17066.txt) below is the trace log.


It showed that ORACLE was first deleting the record from the MV and during this DELETE operation it was doing a full table scan of the materialized view (MV_STATEMENT ) and the materialized view log (MLOG$_TRANSACTION_APPORTIO )

DELETE FROM "WR15657"."MV_STATEMENT" SNA$
WHERE
"TAROWID" IN (SELECT /*+ NO_MERGE HASH_SJ */ * FROM (SELECT
CHARTOROWID("MAS$"."M_ROW$$") RID$ FROM
"OCEAN"."MLOG$_TRANSACTION_APPORTIO" "MAS$" WHERE "MAS$".SNAPTIME$$ > :1 )
MAS$)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.52 211.46 63 217 34 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.52 211.46 63 217 34 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 718 (WR15657) (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE MV_STATEMENT (cr=8 pr=0 pw=0 time=210588077 us)
1 PX COORDINATOR (cr=8 pr=0 pw=0 time=210587456 us)
0 PX SEND QC (RANDOM) :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
0 HASH JOIN RIGHT SEMI (cr=0 pr=0 pw=0 time=0 us)
0 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)
0 PX SEND BROADCAST :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
1 TABLE ACCESS FULL MLOG$_TRANSACTION_APPORTIO (cr=3 pr=0 pw=0 time=97 us)
0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
0 MAT_VIEW ACCESS FULL MV_STATEMENT (cr=0 pr=0 pw=0 time=0 us)


Rows Execution Plan
------- ---------------------------------------------------
0 DELETE STATEMENT MODE: ALL_ROWS
0 DELETE OF 'MV_STATEMENT'
1 PX COORDINATOR
0 PX SEND (QC (RANDOM)) OF ':TQ10001' [:Q1001]
0 HASH JOIN (RIGHT SEMI) [:Q1001]
0 BUFFER (SORT) [:Q1001]
0 PX RECEIVE [:Q1001]
0 PX SEND (BROADCAST) OF ':TQ10000'
1 TABLE ACCESS (FULL) OF
'MLOG$_TRANSACTION_APPORTIO' (TABLE)
0 PX BLOCK (ITERATOR) [:Q1001]
0 MAT_VIEW ACCESS (FULL) OF 'MV_STATEMENT' (MAT_VIEW)
[:Q1001]


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
reliable message 1 0.00 0.00
enq: KO - fast object checkpoint 1 0.00 0.00
os thread startup 11 0.08 0.92
PX Deq: Join ACK 13 0.00 0.00
PX Deq Credit: send blkd 16 0.00 0.00
PX qref latch 1 0.00 0.00
PX Deq: Parse Reply 6 0.00 0.00
PX Deq: Execute Reply 508 1.96 209.52
PX Deq: Table Q Normal 1 0.00 0.00
PX Deq: Signal ACK 11 0.00 0.00
enq: PS - contention 1 0.00 0.00
********************************************************************************


10) It was then selecting the updated record from the base table and inserting the record in the materialized view (MV_STATEMENT ). During this operation again it was again doing a full table scan of the base table TRANSACTION_APPORTIONMENTS

INSERT INTO "WR15657"."MV_STATEMENT" SELECT /*+ NO_MERGE("JV$") */ /*+ */
"MAS$4".ROWID,"JV$"."RID$","MAS$2".ROWID,"MAS$1".ROWID,"MAS$0".ROWID,
"MAS$5".ROWID,"MAS$5"."OWNER_CID","MAS$4"."TX_ID","MAS$4"."TX_TYP_CD",
"MAS$4"."CRE_DT","MAS$4"."TX_VLU","MAS$4"."CPGN_CD","MAS$4"."CRE_USER_ID",
"MAS$4"."TX_AUDIT_LOC_CD","MAS$4"."TX_AUDIT_LYTY_PRG_CD","JV$"."CHRG_CID",
"JV$"."TX_APMT_ID","JV$"."DLV_ITM_ID","JV$"."TX_APMT_TYP_CD",
"JV$"."TX_APMT_REASN_CD","JV$"."APMT_TXT","JV$"."CCY_CD",
"JV$"."TX_APMT_VLU","JV$"."RULE_ID","MAS$2"."PROD_ID","MAS$2"."TM_PD_QTY",
"MAS$2"."ITM_QTY","MAS$2"."CUST_ORD_ID","MAS$2"."CURR_STS_CD",
"MAS$2"."LINE_NO","MAS$2"."CID","MAS$2"."RETRO_IND","MAS$2"."ON_HOLD_IND",
"MAS$2"."PARTNER_REF_CD","MAS$2"."SRC_ORG_CD","MAS$2"."SRC_ORG_CD_TYP_CD",
"MAS$2"."CURR_STS_DT","MAS$2"."CRE_DT","MAS$2"."CRE_USER_ID",
"MAS$1"."SRC_PROD_ID","MAS$1"."PROD_TYP_CD","MAS$0"."MKG_ALN_CX_CD",
"MAS$0"."FLT_NO","MAS$0"."OPG_ALN_CX_CD","MAS$0"."SLG_CLS_CD",
"MAS$0"."CPT_CD","MAS$0"."TRIP_TYP_CD","MAS$0"."STN_FROM_CD",
"MAS$0"."STN_TO_CD","MAS$0"."UPLIFT_DT" FROM ( SELECT "MAS$"."ROWID" "RID$"
, "MAS$".* FROM "OCEAN"."TRANSACTION_APPORTIONMENTS" "MAS$" WHERE ROWID
IN (SELECT /*+ HASH_SJ */ CHARTOROWID("MAS$"."M_ROW$$") RID$ FROM
"OCEAN"."MLOG$_TRANSACTION_APPORTIO" "MAS$" WHERE "MAS$".SNAPTIME$$ > :1 )
) "JV$", "OCEAN"."FLIGHT_SEGMENT_OPERATIONS" AS OF SNAPSHOT (:2) "MAS$0",
"OCEAN"."PRODUCTS" AS OF SNAPSHOT (:2) "MAS$1",
"OCEAN"."CUSTOMER_DELIVERY_ITEMS" AS OF SNAPSHOT (:2) "MAS$2",
"OCEAN"."TRANSACTIONS" AS OF SNAPSHOT (:2) "MAS$4", "OCEAN"."ACCOUNTS" AS
OF SNAPSHOT (:2) "MAS$5" WHERE "MAS$5"."ACC_ID"="MAS$4"."ACC_ID" AND
"MAS$4"."TX_ID"="JV$"."TX_ID" AND "JV$"."DLV_ITM_ID"="MAS$2"."DLV_ITM_ID"(+)
AND "MAS$2"."PROD_ID"="MAS$1"."PROD_ID"(+) AND "MAS$1"."PROD_ID"=
"MAS$0"."PROD_ID"(+)



call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 584.78 850.93 3398205 3408742 29 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 584.79 850.94 3398205 3408742 29 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 718 (WR15657) (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS OUTER (cr=3408737 pr=3398205 pw=0 time=850924043 us)
1 NESTED LOOPS OUTER (cr=3408733 pr=3398205 pw=0 time=850923995 us)
1 NESTED LOOPS OUTER (cr=3408729 pr=3398205 pw=0 time=850923950 us)
1 NESTED LOOPS (cr=3408725 pr=3398205 pw=0 time=850923861 us)
1 NESTED LOOPS (cr=3408721 pr=3398205 pw=0 time=850923795 us)
1 VIEW (cr=3408390 pr=3398205 pw=0 time=850916377 us)
1 HASH JOIN RIGHT SEMI (cr=3408390 pr=3398205 pw=0 time=850916365 us)
1 TABLE ACCESS FULL MLOG$_TRANSACTION_APPORTIO (cr=3 pr=0 pw=0 time=81 us)
160781138 PARTITION HASH ALL PARTITION: 1 16 (cr=3408387 pr=3398205 pw=0 time=803922923 us)
160781138 TABLE ACCESS FULL TRANSACTION_APPORTIONMENTS PARTITION: 1 16 (cr=3408387 pr=3398205 pw=0 time=643285157 us)
1 PARTITION HASH ITERATOR PARTITION: KEY KEY (cr=331 pr=0 pw=0 time=7408 us)
1 TABLE ACCESS BY LOCAL INDEX ROWID TRANSACTIONS PARTITION: KEY KEY (cr=331 pr=0 pw=0 time=7389 us)
1 INDEX UNIQUE SCAN PK_TRANSACTIONS PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=35 us)(object id 22742)
1 TABLE ACCESS BY INDEX ROWID ACCOUNTS (cr=4 pr=0 pw=0 time=59 us)
1 INDEX UNIQUE SCAN PK_ACCOUNTS (cr=3 pr=0 pw=0 time=45 us)(object id 22375)
1 PARTITION HASH ITERATOR PARTITION: KEY KEY (cr=4 pr=0 pw=0 time=77 us)
1 TABLE ACCESS BY LOCAL INDEX ROWID CUSTOMER_DELIVERY_ITEMS PARTITION: KEY KEY (cr=4 pr=0 pw=0 time=62 us)
1 INDEX UNIQUE SCAN PK_CUSTOMER_DELIVERY_ITEMS PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=46 us)(object id 22727)
1 TABLE ACCESS BY INDEX ROWID PRODUCTS (cr=4 pr=0 pw=0 time=36 us)
1 INDEX UNIQUE SCAN PK_PRODUCTS (cr=3 pr=0 pw=0 time=26 us)(object id 22267)
1 TABLE ACCESS BY INDEX ROWID FLIGHT_SEGMENT_OPERATIONS (cr=4 pr=0 pw=0 time=37 us)
1 INDEX UNIQUE SCAN PK_FLIGHT_SEGMENT_OPERATIONS (cr=3 pr=0 pw=0 time=23 us)(object id 22271)

error during execute of EXPLAIN PLAN statement
ORA-08187: snapshot expression not allowed here

parse error offset: 1448

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 215891 0.35 458.05
db file sequential read 1259 0.05 1.07
********************************************************************************


11) I thought that it was doing a full table scan because there was no index on the M_ROW$$ coulmn of the materialized view log.
So i created the indexes on the M_ROW$$ column of the materialized view log tables

create index idx_mv_log_ta on MLOG$_TRANSACTION_APPORTIO(M_ROW$$);
create index idx_mv_log_t on MLOG$_TRANSACTIONS(M_ROW$$);
create index idx_mv_log_prod on MLOG$_PRODUCTS(M_ROW$$);
create index idx_mv_log_fso on MLOG$_FLIGHT_SEGMENT_OPERA(M_ROW$$);
create index idx_mv_log_cdi on MLOG$_CUSTOMER_DELIVERY_IT(M_ROW$$);
create index idx_mv_log_acc on MLOG$_ACCOUNTS(M_ROW$$);


12) Then i repeated the same update and commit. But it is still taking the same time to complete the commit (refresh the materialized view ). The attached file (ajay_selhi_20080620.txt) is the trace file log.


I also noted that the HASH_SJ hint used during the DELETE and INSERT operation is forcing ORACLE to do the full table scan.
When i don't use this hint and see the explain plan, the plan shows an index unique scan operation, but if i add the HASH_SJ hint then the plan shows a full table
scan.
Is there any specific reason why is the HASH_SJ hint being used in this sqls?

Another thing that is there at the begining of both the trace file is an error,

The following statements encountered a error during parse:
Error encountered: ORA-10980


Has this error anything to do with the refresh time that ORACLE taking at the moment? What can be the right solution for this.

Any help will be much appreciated


Thanks and Regards
Ajay
Re: Fast Refresh of materialized view too slow [message #329254 is a reply to message #328958] Tue, 24 June 2008 13:36 Go to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Hello Ajay,

Before posting any query's please read below link.

http://www.orafaq.com/forum/t/88153/0/

Accorindg your environment. I have some doubts.

1. Are you using Advanced Replication or Basic Replication in your environment.

2. I think you need remove/delete entry's from Materialized View log.

http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96568/rarmviea.htm#94092

For Reference:

http://orafaq.com/node/1897

Babu
http://babudba.blogspot.com
Previous Topic: Replication in Oracle 10g
Next Topic: Problem in configuration of replication
Goto Forum:
  


Current Time: Thu Mar 28 08:39:09 CDT 2024