Hi All,
I am working on materialized view refresh tuning.Initially it was complete refresh and used to take more than 90 mins to complete.
I changed it to fast refresh now it is completing fast. Now i have partitioned the base tables gl_balances and gl_code_combinations of column code_combination_id and created a local index on column code_combination_id then i am trying to partition the materialized on the same column to take advantage of partition change tracking.
Size of gl_balances base tables is 40Gb and all others tables sizes are small. In where clause there all the 4 tables are mapped. If i will create the partition only on code_combination_id will i the materialized will become the candidate for partition change tracking. As i know it will be applicable for PCT. I need expert advice on this.
While doing a fast refresh. the refresh takes less time. when there is a change in gl_balances , gl_code_combinations or gl_periods it completes in 20-30 mins. When there is a change in gl_set_of_books tables. It creates a problem here.DEL query takes more than 48 hours to complete.
CREATE MATERIALIZED VIEW apps.BAL_PART
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE as
SELECT GL.GL_CODE_COMBINATIONS21.ROWID C1,GL.GL_BALANCES21.ROWID C2, GL.GL_SETS_OF_BOOKS.ROWID C3,
GL.GL_PERIOD.ROWID C4,
"GL"."GL_BALANCES21"."ACTUAL_FLAG" ,
"GL"."GL_BALANCES21"."CURRENCY_CODE" ,
"GL"."GL_BALANCES21"."PERIOD_NUM" ,
"GL"."GL_BALANCES21"."PERIOD_YEAR" ,
"GL"."GL_BALANCES21"."SET_OF_BOOKS_ID" "SOB_ID",
"GL"."GL_CODE_COMBINATIONS21"."CODE_COMBINATION_ID" "CCID",
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT1" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT10" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT11" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT12" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT13" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT14" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT2" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT3" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT4" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT5" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT6" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT7" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT8" ,
"GL"."GL_CODE_COMBINATIONS21"."SEGMENT9" ,
"GL"."GL_PERIODS"."PERIOD_NAME" ,
NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_CR", 0) Open_Bal_Cr,
NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_CR", 0) +
NVL("GL"."GL_BALANCES21"."PERIOD_NET_CR", 0) Close_Bal_Cr,
NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_DR", 0) Open_Bal_Dr,
NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_DR", 0) +
NVL("GL"."GL_BALANCES21"."PERIOD_NET_DR", 0) Close_Bal_Dr,
NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_DR", 0) -
NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_CR", 0) Open_Bal,
NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_DR", 0) -
NVL("GL"."GL_BALANCES21"."BEGIN_BALANCE_CR", 0) +
NVL("GL"."GL_BALANCES21"."PERIOD_NET_DR", 0) -
NVL("GL"."GL_BALANCES21"."PERIOD_NET_CR", 0) Close_Bal,
NVL("GL"."GL_BALANCES21"."PERIOD_NET_CR", 0) Period_Cr,
NVL("GL"."GL_BALANCES21"."PERIOD_NET_DR", 0) Period_Dr
FROM GL.GL_CODE_COMBINATIONS21,
GL.GL_BALANCES21,
GL.GL_SETS_OF_BOOKS,
GL.GL_PERIODS
WHERE GL.GL_BALANCES21.CODE_COMBINATION_ID =GL.GL_CODE_COMBINATIONS21.CODE_COMBINATION_ID
AND GL.GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID = GL.GL_BALANCES21.SET_OF_BOOKS_ID
AND GL.GL_PERIODS.PERIOD_NUM = GL.GL_BALANCES21.PERIOD_NUM
AND GL.GL_PERIODS.PERIOD_YEAR = GL.GL_BALANCES21.PERIOD_YEAR
AND GL.GL_PERIODS.PERIOD_TYPE = GL.GL_BALANCES21.PERIOD_TYPE
AND GL.GL_PERIODS.PERIOD_NAME = GL.GL_BALANCES21.PERIOD_NAME
AND GL.GL_PERIODS.PERIOD_SET_NAME = GL.GL_SETS_OF_BOOKS.PERIOD_SET_NAME
and gl.GL_CODE_COMBINATIONS21.summary_flag != 'Y'
TRACE 1046 del statement
DELETE FROM "APPS"."apps.BAL_PART" SNA$
WHERE "C3" IN (SELECT /*+ NO_MERGE */ * FROM (SELECT
CHARTOROWID("MAS$"."M_ROW$$") RID$ FROM "GL"."MLOG$_GL_SETS_OF_BOOKS"
"MAS$" WHERE "MAS$".SNAPTIME$$ > :B_ST1 ) AS OF SNAPSHOT(:B_SCN) MAS$)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 17759.00 171782.99 159422121 1267371 2564144739 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 17759.00 171782.99 159422121 1267371 2564144739 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 175 (APPS) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE apps.BAL_PART (cr=0 pr=0 pw=0 time=0 us)
193128740 NESTED LOOPS (cr=592437 pr=592422 pw=0 time=945244160 us cost=339302 size=168 card=1)
3 SORT UNIQUE (cr=7 pr=0 pw=0 time=15832 us cost=2 size=138 card=1)
24 TABLE ACCESS FULL MLOG$_GL_SETS_OF_BOOKS (cr=7 pr=0 pw=0 time=19 us cost=2 size=138 card=1)
193128740 INDEX RANGE SCAN C3BOOKS (cr=592430 pr=592422 pw=0 time=789499200 us cost=339299 size=3318314250 card=110610475)(object id 2114736)
error during execute of EXPLAIN PLAN statement
ORA-08187: snapshot expression not allowed here
parse error offset: 314
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 159520897 2.12 144415.96
latch: cache buffers chains 134 0.06 0.68
latch: undo global data 33 0.02 0.15
latch: object queue header operation 521 0.02 0.53
log file switch (private strand flush incomplete)
532 0.31 28.26
resmgr:cpu quantum 155 1.40 13.49
resmgr:internal state change 25 0.11 2.21
latch free 10 0.00 0.00
latch: cache buffers lru chain 4 0.00 0.00
rdbms ipc reply 489 0.02 0.54
reliable message 587 0.00 0.56
latch: row cache objects 3 0.00 0.00
********************************************************************************
GL_SETS_OF_BOOKS has only 6 rows. I know there is complete refresh as a option which will again take more than 90 mins.
I want to do the fast refresh. Tables rows details below.
SQL> select count(*) from gl.gl_code_combinations21;
COUNT(*)
----------
3075255
SQL> select count(*) from gl.GL_PERIODS;
COUNT(*)
----------
1160
SQL> select count(*) from gl.gl_balances21;
COUNT(*)
----------
477613527
SQL> select count(*) from gl.gl_sets_of_books;
COUNT(*)
----------
6
gl_sets_of_books has less rows. Whenever there is a change then it mapped to huge rows hence during materialized view has delete huge number of rows.
select count(*) from apps.BAL_PART group by C3;
C3 is the rowid which is present in create materialized statement.
COUNT(*)
----------
292927011
210215
69330
184406971
Is there any way to improve the plan. As i created a partition on code_combination_id and local index on code_combination_id which will not help in set_of_books_id case. I dont PCT will help here or not. Is it possible to use PCT refresh by equipartitioning only one column in where clause.
Please assist me in improving refresh of materialized view using fast refresh.
Thanks and Regards,
Edited by: user646034 on Feb 23, 2013 11:13 PM
Edited by: user646034 on Feb 23, 2013 11:19 PM
Edited by: user646034 on Feb 23, 2013 11:46 PM
Edited by: user646034 on Feb 25, 2013 11:46 AM