August 2008 – Extract(Read) > Transform(Test&Apply)

How to transfer LONG datatype over DBLINK as VARCHAR?

Siebel as one of sources for our ETL and customer requested S_NOTE_SR.NOTE column with LONG datatype, not a smart idea to transfer this data to DW but customer is usually the king here.

SYS.DBMS_METADATA_UTIL.LONG2VARCHAR($ORACLE_HOME/rdbms/admin/dbmsmetu.sql) function is an undocumented option for this need. It worked for us on 10.2 and I hope if you need it somehow you test it if it will again work for your case. :)


SQL> SELECT column_function
  2    FROM tta01ext_table_cols t
  3   WHERE table_name = 'S_NOTE_SR'
  4     AND table_column = 'NOTE'
  5  ;

COLUMN_FUNCTION
------------------------------------------------------------------------------------------------------------
SYS.dbms_metadata_util.long2varchar@DBLK_SIEBEL(length=>4000,tab=>'SIEBEL.S_NOTE_SR',col=>'NOTE',row=>rowid)

ps: TO_CLOB and DBLINK problem –


-- local test is fine with TO_CLOB
SELECT priv_flg, created, to_char(to_clob(note)), note_type
  FROM src_siebel.s_note_sr
 WHERE rownum < 2; 
 
-- remote test fails with TO_CLOB
SELECT priv_flg, created, to_char(to_clob(note)), note_type
  FROM siebel.s_note_sr@dblk_siebel
 WHERE rownum < 2;
 
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
ORA-02063: önceki line, kaynağı DBLK_SIEBEL

-- remote test is fine with DBMS_METADATA_UTIL.LONG2VARCHAR
SELECT priv_flg,
       created,
       sys.dbms_metadata_util.long2varchar@dblk_siebel(4000,
                                                       'SIEBEL.S_NOTE_SR',
                                                       'NOTE',
                                                       ROWID)
  FROM siebel.s_note_sr@dblk_siebel
 WHERE rownum < 2;

The Rijndael(Advanced Encryption Standard-AES) algorithm to secure TBs of data

The Need Definition –

There are some TBs of your Call Data Records(CDR) to be shared with an external company. Some of the columns in this dataset must be encrypted and this encryption method must guarantee that this columns only can be decrypted by you, the company who owns the data.

The Solution Advised –

After 10g we have DBMS_CRYPTO supplied package and inside this package there is the Rijndael(AES) algorithm which was selected by NIST in October 2000 to become the new official Advanced Encryption Standard(AES) for use within the US Government.

AES is available in two strengths; 128 and 256 bits, and of course the 256-bit version is approximately %50 slower than the 128-bit version, so for the optimum encryption performance 128 bit strength will be enough for this need since to break AES128 encryption one will need 2 ^ 100 amount of keys which will mean months of time even with a super-computer.

And why to do this inside the database but not with a custom C program on operating system for example, the answer is simple as usual; you have the PARALLEL QUERY, HASH JOIN, PARTITIONING, COMPRESSION type of VLDB options already available inside the database you paid for so for the other path you will most probably be re-inventing a dumper wheel within more time and this wheel will born with its maintanance costs on long-term.

A Simple Demostration based on the 10g EM Data Encryption Wizard –

AES128 demo with DBMS_CRYPTO

Some additional reading and references –

Advanced Encryption Standard

Encrypt Your Data Assets By Arup Nanda

How To Encrypt Data in Oracle Using PHP by Larry Ullman

Protect from Prying Eyes: Encryption in Oracle 10g by Arup Nanda

Oracle Openworld 2008 Community Events

There will be special additional events for Oracle customers during Openworld which will provide important opportunities to come together and exchange information.

Sunday, Sep 21 – Blogger Meet-up
Monday, Sep 22 – OTN Night
Tuesday, Sep 23 – Terabyte Club Appreciation Dinner(not sure but ACE Dinner also I guess)
Wednesday, Sep 24 – Appreciation Event
Thursday, Sep 25 – Terabyte Club General Meeting

For example we are a member of the Oracle Data Warehouse Terabyte Club Program and I am very excited for the discussions on general Data Warehouse market trends, member customer case study presentations, functionality requests, future Oracle DW products’ directions and detailed reviews of the key announcements including the most exciting DW-related announcements made during OpenWorld 2008 on Thursday’s meeting.

Yes, there will be at least one important announcement at OOW this year which I am aware of for a while now, which of course I can not write on for now. As a result not only the day times but also the nights will be full during OOW 2008 :)

Oracle OpenWorld and Oracle Develop 2008, Where the Community Rules
September 21-25, Moscone Center, San Francisco

How to choose which sessions to attend at Oracle OpenWorld 2008?

My OOW profile has been updated to include access to Oracle Develop. So I begin to use the schedule builder to select my OOW 2008 sessions.

There are a lot of interesting sessions, so I decided to just go over just the Application Development and Datawarehousing groups to narrow my possible interested sessions, but still it was hard for me to choose several session over their alternatives. Just a small note here, creating a personal schedule does not guarantee those session access and it is advised to arrive at least 10 minutes before session start time to ensure the access.

I still will be needing some additional slots for the Unconference tradition, 11g Exam Cram Session at OCP Lounge, ACE hours at OTN Lounge and of course I want to meet with some of my friends from Oracle Commmunity. I feel like Tom Cruise at Mission Impossible but I am still opened for other session advises :)


Sunday, Sep 21

08:30-10:00 	Getting Started with Oracle Fusion Middleware (IOUG)
10:00-11:30 	Oracle Application Express SIG: Ask the Experts (IOUG)
11:45-12:45 	Using Oracle Database 11g and Oracle Application Express to Change Business Practices and Realize ROI 

13:00-14:30 	Oracle Database Vault for SAP (IOUG)
14:30-15:30 	Efficient Schema Design
15:30-17:00 	OCP Lounge - ORACLE DATABASE 11G EXAM CRAM

Monday, Sep 22

09:00-10:00 	UNCONFERENCE - Oracle Application Express Test Drive for DBAs and PL/SQL Developers (Lead: H.Tonguç Yılmaz)
10:00-11:00 	UNCONFERENCE - Capacity Planning on Oracle DB (Ignacio Ruiz)
11:30-12:30 	Growing a Data Warehouse to 50 Terabytes and Beyond 

13:00-14:00 	Faster and Smarter Data Warehouses with Oracle Database 11g (IOUG)
14:30-15:30 	Data Warehousing at 300 Terabytes on Oracle Real Application Clusters
16:00-17:00 	Best Practices for Deploying a Data Warehouse on Oracle Database 11g
17:30-18:30 	Web 2.0 Development with Oracle Application Express 

Tuesday, Sep 23

09:00-10:00 	Inside Oracle Database 11g Optimizer: Removing the Mystery
10:00-11:00 	UNCONFERENCE - Release 12 Apps DBA 101 (Lead: John Stouffer)
11:30-12:30 	Advanced Performance Diagnostics: What the GUI Doesn’t Tell You

13:00-14:00 	Oracle Database 11g: Stories from a Data Warehouse Implementation
14:00-15:00 	UNCONFERENCE - How to use native database REST Web Services in Oracle 11g by Marcelo F. Ochoa
14:00-15:00 	UNCONFERENCE - So, You Want To Be An Oracle ACE? by Dan Norris
17:00-18:00 	Practical Data Warehouse Experiences with Oracle Database 11g

Wednesday, Sep 24

09:00-10:00 	Successfully Developing and Integrating Applications in Oracle Application Express in Oracle E-Business Suite
11:30-12:30 	Oracle Optimized Warehouse Solutions for Sun: Accelerate Your Data Warehouse Implementation 

13:00-14:00 	The Oracle Database 11g Data Warehouse Toolkit (IOUG)
14:30-16:30 	Keynote: Larry Ellison, Oracle and Mark Hurd, HP
16:00-17:00         UNCONFERENCE - App Server basics for the DBA (Lead: Hans Forbrich)
17:00-18:00 	Building Commercial Software-as-a-Service Applications with Oracle Application Express

Thursday, Sep 25

09:00-10:00 	Dispelling Myths About Oracle Application Express (IOUG)
11:00-12:00 	OTN Lounge - Oracle ACE Office Hours in the OTN Lounge
12:00-13:00 	Oracle's New Database Accelerator: Query Processing Revolutionized 

13:30-14:30 	Oracle Business Intelligence Strategy for the Data-Warehouse-Enabled Enterprise
15:00-16:00 	Oracle Real Application Clusters and QLogic InfiniBand: Yahoo! Large-Scale Data Warehouse

Very short time left now, I will try to do my best to have the best at this beatiful city and event and hope to meet you there. :)

ps: Recently I saw that Arup Nanda has started blogging, after reading his OTN and Oracle Magazine articles this is a great news for me. http://arup.blogspot.com/

another ps: OTN at Oracle OpenWorld: A Condensed Guide

Oracle Develop
September 21-23, San Francisco Marriott

OTN Lounge
Open 9 a.m.-5 p.m., September 21-25, Moscone West, Level 3

Oracle OpenWorld Unconference
Open 9 a.m.-5 p.m., September 21-25, Moscone West, Level 3

OTN Night
7:30-11 p.m., September 22, San Francisco Hilton

The Oracle OpenWorld community experience aggregator

May Dynamic Sampling be an Answer for OLAP Systems’ Statistics Needs?

It is very important to determine when and how often to gather new statistics for Cost Based Optimizer. Especially after 10g since Rule Based Optimizer is now obsolete as a result the Automatics Statistics Gathering mechanism keeps all the statistics current by default after a fresh installation with a scheduled job.


-- Verifying Automatic Statistics Gathering Job
SELECT owner, job_name, enabled 
  FROM DBA_SCHEDULER_JOBS 
 WHERE JOB_NAME = 'GATHER_STATS_JOB';

The default gathering interval is nightly but you can customize this interval for your needs. The frequency of collection intervals should balance the task of providing accurate statistics and the processing overhead incurred by the statistics collection process. If you want you can disable this job and have your own strategy of course.


-- Disabling Automatic Statistics Gathering Job
BEGIN 
  DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB'); 
END; 
/

Or you can manually gather and lock statistics at the table or schema level(LOCK_SCHEMA_STATS) as another option.


-- Manual statistics strategy example
BEGIN 
  DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES'); 
  DBMS_STATS.LOCK_TABLE_STATS('HR', 'EMPLOYEES'); 
END; 
/

You can query the STATTYPE_LOCKED column of DBA_TAB_STATISTICS view to determine if the statistics on the table are locked or not and you may use the UNLOCK_TABLE/SCHEMA_STATS procedures to unlock the statistics on a specified table. An important note here is when you lock the statistics on a table all of the dependent statistics are considered locked like table statistics, column statistics, histograms and dependent index statistics.

In an OLAP environment for tables which are being substantially modified in batch operations such as bulk loads usually DBMS_STATS procedure is called as soon as the load operation completes as a part of that ETL step. Also statistics are needed to be gathered manually after new object creation until the configured custom or Oracle’s Automatic Statistics Gathering job process takes action. The statistics in certain cases on these highly volatile tables can be set to NULL and when Oracle encounters a table with no statistics optimizer dynamically gathers the necessary statistics as part of the query optimization. This 10g new feature is called dynamic sampling and is controlled by the OPTIMIZER_DYNAMIC_SAMPLING parameter which should be set to a value of 2 or higher(the default value is 2).


-- Setting statistics to NULL by deleting and then locking the statistics for Dynamic Sampling
BEGIN 
  DBMS_STATS.DELETE_TABLE_STATS('HR', 'EMPLOYEES'); 
  DBMS_STATS.LOCK_TABLE_STATS('HR', 'EMPLOYEES'); 
END; 
/

Dynamic sampling offers the opportunity to collect statistics for segments with either missing statistics or stale statistics. Where as this approach involves an overhead at query optimization time and this action is repeated for the same objects unless statistics are gathered. This usually is not acceptable for OLTP applications but when it comes to OLAP usually parsing time can be discarded compared to the execution time and also avoiding statistics collection overhead on huge tables may be a high motivation for most of the time.

A 100% dynamic sampling-based OLAP system topic was raised and discussed before by David Aldridge here, so any further comments are welcomed.

Oracle Open World 2008 -ORACLE DATABASE 11G EXAM CRAM CONFIRMATION-

After reading this post on Oracle Certification Blog I decided to try my chance and it did work for me, I have been confirmed for the FREE OCP Oracle Database 11g New Features for Administrators Exam Cram Session at Oracle OpenWorld, San Francisco.(Only confirmed attendees will be allowed into this special Oracle University event)

Until now I didn’t do anything special for preparation and I won’t do anything more than just reading blog and forum posts related to 11g new features. Because I want to test if I can make it or not without any special activity but just consuming the FREE material around me, at the end this is a free session and it is a good opportunity to test this approach I guess. :)

These are the resources I have been reading until now.

* http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/index.html

* http://www.oracle-base.com/articles/11g/Articles11g.php

* https://tonguc.wordpress.com/category/oracle-11g-new-features/

Also I will be around The OCP Lounge, a full service lounge including space for informal meetings exclusively for Oracle Certified Professionals and Masters only, ouuu :)

Many thanks goes to the Oracle University for both activities.

1Z0_050 – Oracle Database 11g: New Features for Administrators

Duration: 105 minutes
Number of Questions: 84
Passing Score: 61%

A Little Story on Selectivity Concept

Oracle’s Query Optimizer’s functions are:
1. Transforming queries
2. Estimating
3. Generating plans

The input to the query transformer is a parsed query block which can be as a complete query, nested subquery or nonmerged view. The main objective of the query transformer is to determine if it is advantageous to change the form of the query so that it enables generation of a better query plan. The transformed query is then sent to the estimator which generates three types of measures:
• Cardinality
• Cost
• And Selectivity

The end goal of the estimator is to estimate the overall cost of a given plan. If statistics are available then the estimator uses them to compute these measures. The information required by the optimizer is higly dependent to accurate statistics which are stored in the data dictionary. If no statistics are available then the optimizer uses either dynamic sampling or an internal default value, depending on the value of the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter.

Here Cardinality represents the number of rows in a row source which can be a base table, a view or the result of a join or GROUP BY operator.

And Cost represents the number of units of work or resources that are used. The query optimizer uses disk I/O, CPU usage and memory usage as units of work.

So lets come to Selectivity, this represents a fraction of rows from a row set which can be a base table, a view or the result of a join or a GROUP BY operator.

When statistics are available the estimator uses them to estimate selectivity, for an equality predicate selectivity is set to the reciprocal of the number of n distinct values of last_name, but if a histogram is available on the equality column then the estimator uses it instead of the number of distinct values. The selectivity depends on the predicate, such as last_name = ‘YILMAZ’ or a combination of predicates such as last_name = ‘YILMAZ’ AND first_name = ‘HASAN TONGUÇ’.

And here comes my little story, my first name Hasan is a very popular religious name in Muslim World, and it was given to me as of my grandfather’s name. My middle name Tonguç is a form of Turkish word Doğuş meaning birth. And my last name Yılmaz means simply who never give up.

During my last two abroad travels to USA and UK I had trouble with my name, I was told that there is a similar person they are after with my first name and last name living in Turkey, shock! But my middle name was the surviving filter in both cases, the Selectivity of my middle name was unique with together my first and last names, thanks to god I was only held for an hour in a room lonely and then left to go in both cases. Now OOW is near and I am nearly sure that I will again be hold and then will thank to my mother for resisting in giving me this Selective middle name :)

New OTN Database Upgrade Forum

After Metalink’s 10g and 11g Upgrade Companions now we also have a dedicated OTN database upgrade forum :)

Forum: Database – Upgrade
– Discuss all aspects of database upgrade from planning and testing through execution and troubleshooting. Also, information about helpful tools and utilities, documentation, and other information to make your database upgrade easier, faster, and less risky. –

Note:601807.1
11g Upgrade Companion

Note:466181.1
10g Upgrade Companion

Some Best Practices of Upgrading Oracle Database
https://tonguc.wordpress.com/2007/08/31/some-best-practices-of-upgrading-oracle-database/

Apex Knowledge Test Written in Apex :)

If you want to have some fun try this application.(login needed as demo/demo)

There are 12 questions, I missed one, maybe soon there can be an Apex certification?
Questions Answered: 12 Correct: 11 Pass
Minimum Required Percent: 60% Percent Correct: 91.7%
Unanswered: 0 Incorrect: 1
Allocated Time in seconds 600 Elapsed Time in seconds 185
Test: APEX General Knowledge Grade: A-

Apex is the most relaxing development tool I used and still use, without any competition, daily I search for new needs&wants just to have the opportunity to develop database centric web based applications with apex in my organization, this is like a virus, each time as I get more experienced with Apex the customers are happier with the results and new requests flow :)

Apex is free, Apex is easy to learn, Apex comes as a part of the 10gR2 XE and 11gR1 SE&EE, if you are a database developer or administrator and looking for an extreme love try Apex!

ps: if you need more fun check this one also :)
http://apex.oracle.com/pls/otn/f?p=35220:2:1940780833701359:START:::GROUP_ID:1

Stored outlines are deprecated in Oracle Database 11g

I hope one day I write a similar post for HINTs, an optimizer world which always guarantees the *best* path for your each query without any additional interrupts..

[from metalink’s 11g Upgrade Companion]
Oracle highly recommends the use of SQL plan baselines instead of the stored outlines after 11gR1. With Oracle Database 11g using the SQL Plan Management (SPM) the optimizer automatically manages plans and ensures that only verified or known plans are used.

SQL Plan Management allows controlled plan evolution by only using a new plan after it has been verified to be perform better than the current plan. You should also use SQL Plan Management as part of your upgrade strategy. Please see the best practices section for more information.
[/from metalink’s 11g Upgrade Companion]

For more details on SQL Plan Management

Oracle Database Performance Tuning Guide – Using SQL Plan Management

SQL Plan Management in Oracle Database 11g