Don’t miss OOW09 Unconference Sessions!

I love the Unconference idea, below is the list of mine just to remind you what you may miss if you do not look at the 2009 Unconference Agenda.

Monday – Oct 12
11am Overlook III: IT Access: Up close with the winning application (Eric Brandenburg, Oracle APEX Developer Challenge Winner)
1pm Overlook II: Chalk & Talk: The Core Performance Fundamentals Of Oracle Data Warehousing (Greg Rahn, Database Performance Engineer, Real-World Performance Group @ Oracle)
2pm Overlook I: Fundamentals of Performance (Oracle ACE Director Cary Millsap)
3pm Overlook II: Oracle Exadata Storage Server FAQ Review and Q&A with Kevin Closson (Performance Architect, Oracle)
4pm Overlook I: Visualization Session – Make your charts more effective for troubleshooting and presenting (Oracle ACE Chen Shapira)

Tuesday – Oct 13
9am Overlook I: What’s New in Eleven … Dot Two (that Oracle won’t be talking about) presented by Oracle ACE Director Daniel Morgan
10am Overlook I: Oracle Indexes: Q & A Discussion With Oracle ACE Director Richard Foote
3pm Overlook I: Extreme Makeover: APEX Edition – Workshop – Scott Spendolini, Oracle ACE Director, Sumner Technologies

Wednesday – Oct 14
10am Overlook II: HA DBA Roundtable: How Do You Make DBA’s Highly Available? (Alex Gorbachev, Paul Vallee, Pythian)
1pm Overlook II: Mod PL/SQL Development Tips – including Google Maps, Spatial Integration and sending HTML formatted emails with graphics (Oracle ACE Director Marcel Kratochvil)
2pm Overlook II: What’s new in OWB 11gR2 – demo and discussion (Mark Rittman and Antonio Romero)

Thursday – Oct 15
9am Overlook II: Deep Dive into Exadata by Kevin Closson and Customer Experiences
10am Overlook I: All About Grouping (Rob van Wijk)

Recent changes

Now I am managing the BI development team focused on CDR ETL, ODS and Data Mining areas, my team is called Revenue Oriented Business Intelligence, cool naming ha? :) Because of my experiences I prefer the “team leader” attribute instead of “team manager”, this change is a very exciting new experience for me.

Last month I blogged twice at our corporate blog in Turkish: http://blog.turkcell.com.tr/tag/oracle/ But I can easily say that during 2009 I become much more a twitter guy(short and brief feeds) than a blogger, you can follow my feeds at http://friendfeed.com/tongucy

Next week I will be at OOW09, my sessions will be focused on data warehousing and exadata as usual, here is a list of the sessions on this focus area: http://www.oracle.com/ocom/groups/public/documents/webcontent/034315.pdf

Also I will be attending to several Terabyte Club meetings and I will try to do my best to attend to the ACE dinner and Bloggers Meetup this year, OOW is a very good opportunity for networking. So if you will also be around see you at OTN Lounge :)

PPT: Data Warehousing with Oracle Database 11g Release 2

There are several exciting new features with 11.2 on BI topics. But it is sad to see hybrid columnar compression NF at beta and now to know that it is only available with Exadata: ORA-64307: hybrid columnar compression is only supported in tablespaces residing on Exadata storage

Still there are very very interesting NFs especially related to PQ, ASM and Partitioning, and here is a summary presentation you may want to check.

ps: Also here you can find some 11.1 new features summary presentations.

Materialized views and sys.sumdelta$ UPDATE-DELETE performance

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 :)

Just married :)

I think and hope I will not be around for a while :)

For details of this huge change in my life please check my personal blog here.

And thanks everybody for their good wishes, warm regards from İstanbul to all.

OracleTurk Hints: Running Huge SQL Files from SQL*Plus and Deleting Huge Amount of Data Best Practices

Last week there were several good hints at local user group’s mailing list. I chosed to mention two of them here for public interest. Lets assume you are provided a SQL script which is ~500 MB from a vendor, with unix fifo’s you may decrease the need of the memory to run this kind of a script.


-- Posted by Ogün Heper; Using FIFO to Run Huge SQL Files from SQL*Plus 

oracle@camel # cat s.sql 
select * from dual;
exit
oracle@camel # mkfifo f.sql
oracle@camel # cat s.sql >f.sql &
[1] 1224
oracle@camel # sqlplus / as sysdba @f.sql

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 24 14:38:07 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options


D
-
X

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[1]+  Done                    cat s.sql >f.sql
oracle@camel # 

Huge INSERTs can be done via direct path with APPEND hint and taking the related segments to NOLOGGING during the load but huge DELETEs and UPDATEs are problematic with Oracle since they produce lots of redo and undo I/O. So a very common question is how to handle these tasks. If you have purchased partitioning feature dropping historic date ranged old partitions is a good workaround for DELETEs. So a very common advise for both huge DELETEs and UPDATEs is a CTAS(Create Table As Select) since it can be done NOLOGGING. Instead of deleting 50 millions of rows out of 250 millions of rows creating a new table with 200 millions of rows with NOLOGGING option may usually be preffered, similar path is always an option for UPDATEs. But the table holding the data here may have lots of grants, triggers etc. and if you do not want to mess up with migrating these metadata to a new table below actions can be a workaround.


-- Posted by Fatih Özerol
-- lets assume we want to delete 41 mil. out of 50 mil. 
-- PQ may decrease the response time if the system is not heavy-loaded already
ALTER SESSION ENABLE PARALLEL DML;
ALTER SESSION ENABLE PARALLEL DDL;
-- at this point you have to make the table read-only not to loose any transaction
-- initially we create a temp table with other 9 mil.
create table tab_tmp nologging parallel  ... as 
select select /*+ parallel(t, some_dop) */.... from ori_tab t where ... ;
-- if the table is an OLTP table and you are running on ARCHIVELOG mode 
-- better to take it to NOLOGGING and PARALLEL
alter table ori_tab nologging parallel some_dop ;
-- at this point setting only the table to nologging is not enough if you have indexes, 
-- you may prefer to set unusable each of them and rebuild them after the load
-- truncate and insert the other 9 mil. rows to the target table
truncate table ori_tab reuse storage ;
insert /*+ append parallel(a, some_dop) */ into ori_tab a nologging 
select /*+ parallel(b, some_dop) */ * from tab_tmp b ;
commit;
-- if the table is an OLTP table revert back the settings
alter table ori_tab NOPARALLEL LOGGING ;
-- at this point you can alter the table to read-write again
-- if any index is set unusable you must rebuild them now

Some additional reading I may advise are:

Direct path inserts, nologging option and index cost(setting indexes to unusable during load) – https://tonguc.wordpress.com/2007/01/20/direct-path-inserts-nologging-option-and-index-cost/

Preventing DDL operations on a table(Read-Only Table prior 11gR1) – https://tonguc.wordpress.com/2007/09/25/preventing-ddl-operations-on-a-table/

Interval Partitioning New Feature and Logging Option Problem

One of my old friends who is an Oracle DBA for at least 15 years always argued with me that “every new feature of Oracle is an evil!” :) He advised that it is not secure to upgrade a production environment to 10gR2 for example unless 11gR2 is released on your platform. Personally I always loved new features and tried to test them as soon as possible. I accept and also experience that there are always bugs around new features especially when they are complemented with some other feature but this is why we have Oracle support.

Recently on our 11.1 ODS production database we saw that the automatic created partitions do not inherit the LOGGING property of neither the table nor the tablespace it belongs to where as it is created always with LOGGING on. If you are trying to benefit from direct path inserts and your database is running on ARCHIVELOG mode and the target segments are LOGGING you will be failing to benefit and producing lots of redo and undo information depending on the size of data you are inserting. So an SR opened, bug escalated, a typical process.

Until 11.1 we had our own interval partitioning maintenance scripts and still we stick to them especially because the automatically created partitions’ naming. This is something I think will be evolving with the future releases since anybody who uses partitioning feature love to name their partitions and is not happy with anonymous partition names. But there is no evil with this and it is the life cycle of any software I think; a new feature comes out – some people test it/consume it and find bugs or report additional feature requests – the producer handles these within future releases or patches.

ps: for exchange of anonymous named partitions you may use EXCHANGE PARTITION FOR clause as a workaround but still with several limitations, you may check this post for more details.