I like to move it! Move it! Part 2.

Originally I was planning to do more or less a set of "Migration 101"-style posts. But for the last couple of weeks I've been working on the real migration project. And as a result there are some "war-stories" to be shared here and now.

Part 2. Translator's role

Yesterday I finished a first cut of a very interesting transformation between two repositories. They both represent very conceptually similar creatures - but they also far enough, so pure mechanical transformation is just not possible.

While trying to marry these two repositories, something came to my mind - it is fare to compare my task to the role of a translator! I am doing exactly the same job - reading a set of text in one language, trying to understand it to the best of my knowledge, and putting everything I understood into words of another language.

But how can I validate that "the best of my knowledge" matches the reality? And also how do I judge my work and don't fall into classical foreign literature trap, described by the quote from a Russian poet Yevganiy Evtushenko: "Translation is like a woman. If it is beautiful, it is not faithful. If it is faithful, it is most certainly not beautiful."

The only answer I was able to come also is from the linguistic realm:

•  for each system involved in the migration we need to build it's own Webster Dictionary and Thesaurus. It will allow to explicitly define all involved elements and valid constructs. Also it will clarify links between different core elements on the system.
• based on those separate dictionaries we now can build a SystemA/SystemB dictionary where we can describe how elements are matching. This dictionary allows formalization of our business logic. Otherwise how do we know that, for example "form" in SystemB means "panel" in SystemA, while "form" in SystemA is closer to a "dialog" in the SystemB?

Summary of Part 2: for any migration project it is very important to formally define all involved elements and its meanings (by themselves and in relation to each other). Only based on this knowledge we could later say that our data transformation was logically correct!

DUMMY issues

I've just noticed in one of LinkedIn PL/SQL forums the following question:
- Which one is faster and why SELECT 1 FROM DUAL or SELECT ROWID FROM DUAL?

Yes, it's pretty basic question, but it's interesting enough to be covered. The answer is pretty simple - do not use ROWID at all. At some point Oracle introduced a notion of FAST DUAL (instead of a real table) exactly for performance reasons. Here is a quote from the doc:

*********************************
Beginning with Oracle Database 10g Release 1, logical I/O is not performed on the DUAL table when computing an expression that does not include the DUMMY column. This optimization is listed as FAST DUAL in the execution plan. If you SELECT the DUMMY column from DUAL, then this optimization does not take place and logical I/O occurs.
**********************************

It is obvious, that selecting of ROWID is conceptually the same as selecting of a real column, because it returns a physical property of a row. I understand that the impact may not be that great, but with the significant number of executions is can add up pretty quickly. Also, let's not confuse CBO more than it really needs. :-)

As a proof - explain plans (from dbms_xplan.display) below:

* explain plan for SELECT 1 FROM DUAL

------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)|
------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)|
|   1 |  FAST DUAL       |      |     1 |     2   (0)|
------------------------------------------------------

* explain plan for SELECT ROWID FROM DUAL

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)|
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)|
---------------------------------------------------------------


* explain plan for SELECT DUMMY FROM DUAL

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)|
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)|
---------------------------------------------------------------

Summary:

• goal: we need to avoid unnessessary IO as much as we can
• key thought: do not use DUMMY or ROWID when selecting from DUAL

P.s. I decided to check how much will be the difference and run the following set of variations:

declare
    v_out_nr number;
begin
    for c in 1..10000 loop
       select 1 into v_out_nr from dual;
    end loop;
end; 

declare
    v_out_tx varchar2(1);
begin
    for c in 1..10000 loop
       select dummy into v_out_tx from dual;
    end loop;
end; 

declare
    v_out_rec rowid;
begin
    for c in 1..10000 loop
       select rowid into v_out_rec from dual;
    end loop;
end; 

declare
    v_out_nr number;
begin
    for c in 1..10000 loop
       -- Yes, I've see that too!
       select rownum into v_out_nr from dual; 
    end loop;
end; 

Here are results (an average of 5 runs):

• 1:                 0.88
• RowNum:    1.10
• Dummy:       1.31
• RowID:        1.39

A couple of interesting things:
1. ROWID is even slower than just DUMMY
2. RowNUM is still using FAST DUAL, but introduces extra step in the execution plan (COUNT), which slows it down

Explaining the miracle

Last week I've got a pretty interesting case of why I so much love doing performance tuning: you take the task, spend 15-20 minutes, "do something"(c), present results to the waiting audience - and proudly walk back to your desk while the whole room is staring at your back in disbelief! :-)

Funny part of the whole process - the fix was so obvious that the biggest wonder was: why it wasn't done before... But to be fair, this performance problem was so typical, that I decided to write a special blog post about it from the technical side (since Dr. Paul Dorsey already covered its architectural aspect).

The whole story started in the days of Oracle 9i and JDeveloper 3.x when we've been using BC4J components (now ADF/BC). Since we had a lot of logical transformations between the repository in the database and its visual representation, we introduced a layer of updatable views (with INSTEAD-OF triggers).

BC4J had a very explicit requirement - to be able to do DML against a specified data source, it must have a primary key. Since at any point of time we were working with a limited set of data, I decided that it is safe to introduce composite columns in views serving as primary keys (yes, I tested performance impact - it was negligible). For example, I have a view that displays attributes of the table EMP located in the diagram TEST, but the same table is also located in the diagram DEV. It means that to uniquely identify an attribute I need to concatenate: DiagramID||'_'||AttributeId - only this concatenation is a real primary key.

Years passed, a lot of other people were working with the project, a lot of new functionality added on the top. Unfortunately, at some point developers started to use those original database views as "black boxes" (yes, it is true that they were very complex inside), without understanding (a) why such views were build (b) what was going on inside. As a result they started to join those synthetic views, sometimes even by composite columns.

I don't want to sound too "old-style", but this "black box" approach becomes more and more popular in the current OO-driven IT world: everything is considered a service with some number of ins and outs. But if you have a lot of closely related services - I can give you about 100% warranty that they have internal overlaps and as a result you pay heavy performance price for the comfort of using them. It is true that in some cases this price is acceptable - but in a lot of cases it isn't! And even more importantly - you cannot use "black box" approach if what you applying it to is NOT A BLACK BOX!

Let me explain the very last sentence: unless you very explicitly say so with hint Oracle CBO processes your request as a whole, unnesting all views into a single query. Maybe each of those views is tuned to properly work standalone - but it is very hard to guess how CBO would decide to process them together. And let's not forget about one more thing - because views were designed to work on a limited data set, they contain significant number of PL/SQL functions in the SELECT clause. But we all know that if Oracle rewrite the query, total number of function calls can significantly change.

Overall, not a big surprise: wrong execution plan + thousands of function calls = a lot of time wasted:

call     count       cpu    elapsed       disk      query
------- ------  -------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0
Execute    142      1.96       1.99          0          0
Fetch      154     42.04      42.14          0     174410
------- ------  -------- ---------- ---------- ----------
total      297     44.00      44.14          0     174410

My solution was very strait-forward: break "black boxes" and write a query that would return exactly what's needed here and now:

call     count       cpu    elapsed       disk      query
------- ------  -------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0
Execute    142      0.01       0.03          0          0
Fetch      154      0.20       0.18          1       3266
------- ------  -------- ---------- ---------- ----------
total      297      0.21       0.22          1       3266

Yeah, from 44 seconds to 0.22... I like 200 times improvement!

Summary:

• action: took two crazy views and rewrote it into a simple query
• result: got 200 times improvement and fixed key performance bottleneck
• key thought: views cannot be considered "black boxes" under any circumstances - Oracle just does not work this way!
 

I like to move it! Move it! Part 1.

Talking this Friday with my co-workers I've got an impression that even the best developers may not have a clear idea what does it mean to run a major migration project to or from their systems. To be fair, before I had a very serious involvement in a couple of big data transformations, I was as clueless as anyone else. And the worst part - it is not a matter of skills, it's about a mindset! So, I decided to start a series of posts on migration. Maybe my thoughts will help somebody to shorten the learning curve!

Part 1. Magic eye

If you've run one big migration project, about 9 out of 10 times in the next one you can predict potential issues better than any astrologist. Especially if the source system was in use for more than a couple of years.

For example:
- if you see DATE information, stored as text - there will be some percentage of data corruption. Not only format could be wrong! Don't bee surprised to see "I don't know" there.

- if you see a NUMBER field with the name AGE - unless you cleanse the data, you may have somebody born in the times of Ancient Rome. I am not joking - in the migration project I am doing right now I've got a couple of guys with birthday somewhere around 1 Century AD. Overall, if you are migrating from the system that has been operational in 90-ies, you MUST check for the quality of DATE fields. Sorry, Y2K problem is still around :-)

- if you see generic tables serving as parents in associations - there will be some number of strange relationships. For example, in the table PARTY we store both schools and offices. In the separate table we store school officials. I can bet, that at least one non-school office will have a dean :-)

- if there is a column storing "type" attribute - there will be some number of rows with types that cannot be explained by anybody.

- if there is a structure with start/end dates that normally should describe a logical chain - there will be always be a couple of overlapping ranges that just don't make sense. Or there will be unexplainable gaps!

- if you see ID in the column without associated FK - be prepared for child rows pointing to non-existing objects. 

I could count even more issues, but the pattern is very clear - all of those cases describe business rules not enforced in the database. Developers usually "hope" that application-enforced rules will be sufficient enough to keep proper data quality. Unfortunately, everybody forgets about feeds, services, batch jobs etc that work directly with the database. In that case (unless you have very clear server-side documentation) people do not often even know about existing rules!

Summary of part 1: data quality should be enforced as close to data layer as possible. All special cases should be documented and available to anybody working with the database directly.

Waiting for Collaborate'13

Last two years in Collab'11 and Collab'12 I was coordinating Database Development Boot-camp. That was a lot of fun, especially "mining" though abstracts for best speakers and bargaining with other coordinators about border-line topics. Also I had to learn some baby-sitting tricks, trying to get all of my selected presenters to return white-papers at least somewhat on time. :-) But this year all boot-camps are converted into a smaller number of curricula, so this time I am off my official duty and really can focus on the main goal of anybody coming to the conference - party time! share knowledge!

I am honored to have two of my presentations accepted (by clicking on the links below you can get complete descriptions):

#402 - Top 5 Issues that Cannot be Resolved by DBAs (other than missed bind variable)

Monday, April 8 [5:00 pm - 6:00 pm]

#399 - Data Tracking: On the Hunt for Information About Your Database

Wednesday, April 10 [11:00 am - 12:00 pm]

I've been already talking about the first one (#402) in the previous post - and if it survived NYOUG, I think, it has pretty good chances to be useful and entertaining to anybody.

The second one (#399) is a bit more "targeted" in the nature - it started as my internal answer on the question: how do I teach my Developers-becoming-Senior-Developers to properly use all the information available in the database? The obvious answer was not the one I expected - first I need to TELL them about all the existing sources! Developers usually just don't have a clue (and often don't have access) about all of the richness of Oracle environment - it is considered DBA's realm. This presentation tries to bridge that DBA/Developer gap and show how different layers of information could solve real business needs - but only when used jointly! 

On the other side of sharing the knowledge - trying to figure out what everybody else it doing... It's a perfect storm! Too many great speakers/too new time slots! But I promise to publish my schedule here - maybe, it could be helpful to somebody!

NYOUG Spring 2013

I hate rain. I hate rain with winds blowing in all directions... But that's the cost to pay for going to NYOUG Spring Meeting! And to be fair I was glad to pay - too many good people were expected to be there... I've been presenting at NYOUG since 2002 (my first English-language presentation ever!) and got attached to MY audience...

Anyways, back to the story. Overall, this meeting was heavily DBA-centric from the very beginning. My good friend (and one of the best RAC specialists in the world) Alex Gorbachev did a pretty interesting keynote "Battle Against Any Guess". This notion is pushed by Alex for years, he even founded a special group some years ago with very well-respected list of members (http://www.battleagainstanyguess.com/). But still majority of big corporate IT solutions is badly instrumented and badly understood. So, there is a lot of room for improvement!

The second slot was mine. I've been scheduled against a talk about Oracle Enterprise Manager Cloud Control 12c by Mughees Minhas from Oracle (very corporate-oriented topic) - and as a result I've got about every person from small-to-medium IT environments, both DBAs and Developers. So, my tongue-in-cheek presentation about "Top5 Issues that Cannot be Resolved by DBAs" got absolutely perfect audience! And I've been not surprised when people were constantly nodding their heads when I was outlining my top picks. By the way, here they are:

• Architect's side:
1. "Smart" columns - Storing multiple data elements in the same attribute looks like a good idea until you need to run a report, or until you need to adjust the whole structure.
2. "STUFF" tables - It is very tempting to create a table called PARTY to store objects of all kinds ranging from the company main office building to the name of your great-grandfather from the security clearance form. Yeah, until couple of years later you want to do a data quality check...
3. Inefficient hierarchical structures – It is true that many things can be stored hierarchically. However, managing a lot of hierarchical objects is very expensive and resource-intensive. Unless developers are willing to work with some kind of denormalized structures, the performance “black hole” will be deeper and deeper with the growth of total data volume.
• Developer's side
1. Datatype misuse - If the date field is stored as text, you can forget about effective range scans and cardinalities because, for Oracle between 20123001 and 20130101, there are 7100 numbers and not one day! Poor, poor CBO...
2. User-defined functions misuse - just try to ask youself a question, do you KNOW how many time this function from SELECT will be executed? And even more interesting - what would happen to this number of executions if you add another join to the FROM clause?

After lunch - another great talk by Alex Gorbachev: "Practical Hadoop by Example". Great job (as expected :-) )! If Alex would publish it somewhere - my strong suggestion will be to take a close look. It's a very well-articulated summary of how Hadoop (and BigData concept) currently relates to corporate IT world.

Final presentation of the day for me - yet another talk by Coleman Leviter on XML in the database. Yes, this topic have been covered by multiple speakers multiple times - but still (especially considering how many XML options we have in 11g database) there is a lot of confusion among developers. So, thanks to Coleman for constantly reminding us about proper ways of doing things!


Ok, enough for today...Time to do something useful in the office...h-m-m-m-m... write some PL/SQL?

P.s. In my presentation I briefly mentioned "dynamic sampling" - if anybody interested, here is a very nice overview of this feature by Maria Colgan: https://blogs.oracle.com/optimizer/entry/dynamic_sampling_and_its_impact_on_the_optimizer

Blogging. Resurrection

It was pretty long time since I've posted anything in this blog (for various reasons) - too bad, especially since there was a lot of interesting things in the IT around me worth sharing. Today I officially resurrect this blog - just can't keep myself quiet for so long :-)!

Let's start with some good news - the conference season is slowly but steadily starts to roll! Tomorrow (March 12, 2013) I will be presenting at New York Oracle User Group with my brand-new talk:

Top 5 Issues that Cannot be Resolved by DBAs (other than missed bind variables) [complete schedule]

Here is the abstract: Common knowledge says that if there is something wrong with the database, blame DBAs. Fortunately for database administrators, about 95% of such cases have nothing to do with their job responsibility. Unfortunately, it takes a lot of time to explain this to any manager. And it takes even longer for an organization as a whole to look for real solutions to its IT problems other than asking its DBAs to look for “run_faster=TRUE”. This presentation’s goal is to illustrate the most common “pseudo-DBA” issues and proper ways of resolving them.

This presentation is a bit of a challenge for me, because it's the first time I decided to pick "TopN"-style instead of deep-dive into a topic. To be fair, I've got a bit tired with feeding people different answers, when they cannot figure out core conceptual questions. So, I decided to change the style - I ASK questions first. Yes, I would also show my proposed approaches - but that's secondary! The primary goal here would be to make people THINK.

And it will be definitely entertaining to see, how the NYC crowd (the most challenging one I ever presented to!) react. If I survive this experience - promise to report an aftermath, maybe even some snippets out of my white-paper. :-)

Ave Caesar, morituri te salutant!