Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

August 25, 2009

Materialized views and sys.sumdelta$ UPDATE-DELETE performance

Filed under: Oracle Business Intelligence,Oracle Performance — H.Tonguç Yılmaz @ 5:08 pm

On our ODS environment(HP-UX IA 64-bit & Oracle Database EE 11.1.0.7.0 64bit) we have several materialized views whose base tables are loaded with direct path insert(nologging segments+APPEND hint) and these materialized views are refreshed with these options:


dbms_mview.refresh( v_schema_name || '.' || v_mv_name,
		method => 'C',
		atomic_refresh => FALSE,
		parallelism => pin_parallelism );

Lately I started to see sys.sumdelta$ table and its index at our hottest segments list on AWR reports and v$segstats.


Segments by DB Blocks Changes DB/Inst: ODS/ods Snaps: 9159-9166
-> % of Capture shows % of DB Block Changes for each top segment compared
-> with total DB Block Changes for all segments captured by the Snapshot

Tablespace Subobject Obj. DB Block % of
Owner Name Object Name Name Type Changes Capture
---------- ---------- -------------------- ---------- ----- ------------ -------
SYS SYSTEM I_SUMDELTA$ INDEX 4,386,512 21.95
SYS SYSTEM SUMDELTA$ TABLE 2,921,600 14.62
ODS SCRATCH_BT I_SNAP$_SBL_CONTACT INDEX 2,555,296 12.78
ODS SCRATCH_BT I_SNAP$_SBL_PARTY_PE INDEX 1,994,592 9.98
ODS SCRATCH_BT I_SNAP$_MV_SBL_ACCOU INDEX 1,924,304 9.63
------------------------------------------------------------- 

Segments by Buffer Busy Waits DB/Inst: ODS/ods Snaps: 9159-9166
-> % of Capture shows % of Buffer Busy Waits for each top segment compared
-> with total Buffer Busy Waits for all segments captured by the Snapshot

Buffer
Tablespace Subobject Obj. Busy % of
Owner Name Object Name Name Type Waits Capture
---------- ---------- -------------------- ---------- ----- ------------ -------
SYS SYSTEM SUMDELTA$ TABLE 128,275 49.75
SYS SYSTEM I_SUMDELTA$ INDEX 86,186 33.43
SYS SYSTEM JOB$ TABLE 11,130 4.32
ODS SCRATCH_BT I_SNAP$_SBL_CONTACT INDEX 8,112 3.15
ODS SCRATCH_BT I_SNAP$_MV_SBL_ACCOU INDEX 3,968 1.54
-------------------------------------------------------------

This table was getting larger and larger after each base table load for each materialized view and below two queries were called recursively each time:


SQL> select sum(bytes)/(1024*1024) MB from dba_segments
2 where owner = 'SYS' and segment_name = upper('sumdelta$') ;

MB
----------
811

SQL> select count(*) from sys.sumdelta$ t ;

COUNT(*)
----------
9345556

SQL> SELECT plan_table_output
2 FROM TABLE(dbms_xplan.display_cursor('9sbsudzr87ztj')) ;

-------------------------------------
update sys.sumdelta$ s set s.timestamp = :1, s.scn = :2 where
s.tableobj# = :3 and s.timestamp >=
to_date('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS')
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time
--------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 3 (100)|
| 1 | UPDATE | SUMDELTA$ | | | |
|* 2 | INDEX RANGE SCAN| I_SUMDELTA$ | 15758 | 1107K| 2 (0)|00:00:01
--------------------------------------------------------------------------------

SQL> SELECT plan_table_output
2 FROM TABLE(dbms_xplan.display_cursor('9wb7u7x708yvr')) ;

-------------------------------------
DELETE FROM sys.sumdelta$ sd WHERE sd.tableobj# = :1 AND sd.timestamp less than :2
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time
--------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 6189 (100)|
| 1 | DELETE | SUMDELTA$ | | | |
|* 2 | INDEX RANGE SCAN| I_SUMDELTA$ | 1669 | 98471 | 6189 (1)|00:01:27
--------------------------------------------------------------------------------

After a small investigation since execution plan showed that two queries used dynamic sampling, I found that this sys table is excluded from gather_stats_job. Since expected behavior is after refreshes the number of rows at this dictionary table should reduce we decided to open an SR. After some more tests I saw that neither fast refresh nor complete refresh made any difference regarding sumdelta$ table’s number of rows at our environment.

For SR we provided the usual 10046 aspirin and saw that all trace files provided from 10046 event output(as per being run in parallel there is QC and slaves trace files) there were no sumdelta$ reference at all. So this guided us to believe that this dictionary table not getting purged can be a bug.


ALTER SESSION ENABLE PARALLEL DDL ;
ALTER SESSION ENABLE PARALLEL DML ;

-- create table sr_test nologging parallel 8 as select * from src_siebel.s_contact where 1=2;
-- create materialized view mv_sr_test enable query rewrite AS SELECT EMP_NUM, CREATED FROM sr_test ;

Select /*+ parallel(sm,8) parallel_index(sm,8) */ count(*) from sys.sumdelta$ sd ;

COUNT(*)
----------
2601080

insert /*+ append */ into sr_test select * from src_siebel.s_contact where rownum  'C', parallelism => 8, atomic_refresh=>false);

PL/SQL procedure successfully completed

commit ;

select sysdate, user from dual;
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

Select /*+ parallel(sm,8) parallel_index(sm,8) */ count(*) from sys.sumdelta$ sd ;

COUNT(*)
----------
2601188

We started to look for some immediate workaround options, dropping and recreating the materialized views did not reduce the number of rows on the dictionary table. So we decided to leave using materialized views and change them with create table as selects, we started from the most long refreshing ones and 3-4 hours refresh times decreased to minutes with this workaround. But of course query rewrite was the alternative cost of this action, we needed to go over all report queries and change them to use the new tables.

We also tried to truncate the dictionary table first at dummy clone databases and then at our development environment, until now there were no problems experienced, so this truncate can also be scheduled weekly for example. As a result until this bug is fixed we feel lucky to have at least two different workaround options, sometimes things get much more uglier :)

About these ads

3 Comments »

  1. Tonguc, did you see bug 5679992?

    Comment by Yas — August 26, 2009 @ 12:45 pm | Reply

  2. Hi Yasin, yes, thanks.

    Comment by H.Tonguç Yılmaz — August 26, 2009 @ 12:56 pm | Reply

  3. [...] H.Tonguc Yilmaz -Materialized views and sys.sumdelta$ UPDATE-DELETE performance [...]

    Pingback by Blogroll Report 21/08/2009 – 28/08/2009 « Coskan’s Approach to Oracle — September 8, 2009 @ 12:04 pm | Reply


RSS feed for comments on this post. TrackBack URI

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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 79 other followers

%d bloggers like this: