Dec 24, 2009

The story of Rich Text Editor - I did not follow software design principles!

I should remember this incident!

Last week, we added a feature for the mailing system in our application. We replaced the html text area with rich text editor. We used the open source TinyMCE (http://tinymce.moxiecode.com).

The rich text formatting feature is a good thing to have. Although some of the customers asked for it as a suggestion, there was no pressure! To utilize developer time better, I asked my team mate to integrate rich text for the module I am in charge. He did it quick as he is a good developer. Here we violated one of the good design principles - we should not add things until it is really needed (YAGNI).

We thought not to support things like link, tables etc at the very beginning for security reasons. Testing phase ran for about 5 days, and we finally released it in production system. Then the real story began! Some of the users were not able to copy formatted text from word processors to our newly added rich text box. Even if there was no table pasted (we did not support table), but it was showing wrong message to users that there was a table in the rich text.

Further investigation revealed that, for a version of MS word processor and for a specific browser version, the text comes with some tags when it was pasted to the pop up of our rich text editor (users need to paste rich text to a pop up, then need to click on 'add' button to finally add the text to the base form - this pop up cleans up unsupported formatting/tags using regular expressions on users final action 'add'). Here, our cleanup regular expressions failed to catch the comment tags came from word process.

Then we fixed and released in production.

The next day, another user reported the same thing even if we had fixed the known problem. Again, further investigation revealed that for another word processor, when user copy formatted text which was center aligned, some special tags came along with it which our validator did not catch!

Moreover, the page loading time increased about 500 ms. Another users told that spell checker that comes with the browser, did not get enabled by default inside rich text box for Firefox and IE! This is because, the text area had been replaced by the rich text's iFrame.

Life sucks on such situations!

Finally, we decided, we should not support copy-paste rich text from other word editors since we don't know how MS and other word processors will come up with new formatted tags in future. Users will only allowed to paste text as 'plain' text - we will remove all kinds of tags for other text editors. Then, they may like to add formatting inside our rich text editor using the available tools on bar. Actually, this was the actual requirements I told the developer to implement!

We would have to implement this feature at some point in future - seems took the pain a bit earlier :-)

***

Dec 21, 2009

Mimicking Real Application Testing (RAT) with SQL Tuning Set

The idea is simple, testing of backup database itself with production workload and environment. There are cases when we do major changes in database, for example, changing system parameters, upgrading to newer versions or even adding new indexes. But how could we simulate the production environment in new born system so that we can sleep at night ensuring changes was not harmful!

Here, RAT comes into play. The basic idea is, capture workload from production system, replay it on new system and analyze impact and performance.

In 10g Release 2, I don't have whole RAT (only capture is available) - replay and performance analysis features are available only in 11g!



What I could do, create a SQL Tuning Set in production database for a specified duration (like workload capture) and then use this in new system to see how Tuning Advisor and Access Advisor suggest after the changes. New execution plans might be dramatically good or bad!

The step are -

1. Creating SQL Tuning Set at source/production db

BEGIN
dbms_sqltune.create_sqlset(sqlset_name => 'PROD-TS', sqlset_owner =>'SYSTEM');
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB(job_name => 'CREATE_STS_PROD-TS',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE bf VARCHAR2(71);
                begin bf := q''#UPPER(PARSING_SCHEMA_NAME) = ''PROD'' AND ELAPSED_TIME >= 300000.0 #'';
                dbms_sqltune.capture_cursor_cache_sqlset( sqlset_name=>''PROD-TS'', time_limit=>''600'',
                repeat_interval=>''5'',
                basic_filter=>bf, sqlset_owner=>''SYSTEM''); end;',
enabled => TRUE);
END;
/

2. Packing Tuning Set to an exportable format

BEGIN
   DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(
         table_name => 'TAB_PROD_TS');
      
   DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
         sqlset_name => 'PROD-TS',
         staging_table_name => 'TAB_PROD_TS');
END;
/

3. Export from source db

# expdp system directory=dump_dir dumpfile=prod-ts-20091220.dmp tables=TAB_PROD_TS

4. Import into target db

impdp system directory=dump_dir dumpfile=prod-ts-20091220.dmp

5. Unpack the SQL Tuning Set

BEGIN
   DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
         sqlset_name => 'PROD-TS',
         sqlset_owner => '%',
         replace => TRUE,
         staging_table_name => 'TAB_PROD_TS');
        
END;
/

6. Run SQL Tuning Advisor and/or SQL Access Advisor to analyze query performance

We could easily do the analysis using EM on test database. This does not exactly fulfill the RAT things, still, something is always better than nothing!


***

Dec 18, 2009

The Rule of Three - When to Confront Someone!

I was reading this nice article of Harvard Business School. It was a very good one to read and seemed interesting for personnel issues! Preserving here :-)

When to Confront Someone - The Rule of Three
By Peter Bregman

"Should I bother to have the conversation with her? What do you think?" Mike*, a marketing director, was telling me about Anne, one of his employees, who had done a few things to frustrate him. She arrived late to a meeting with a client. Not that late - only ten minutes — still, it didn't look good.

Then, a few days later, she was supposed to email him some information by 4pm and didn't do it until 6pm. I know, he told me, not a big deal. He didn't really need it until the next morning. Still.

And then this morning he received a voicemail from her saying she wouldn't be able to make the conference call they had planned with a colleague in another office. The call was an internal matter. Nothing time sensitive. But she didn't give him a reason and that bothered Mike.

"None of these things are a big deal," Mike told me, "And she's a great employee. But I'm annoyed. Should I say something or shrug it off?"

I have a rule for dealing with these types of situations — times when I'm not sure if it's worth raising an issue. I need a rule because it's often hard to know if something's a big enough deal to address until it's too late and then, well, it's too late. It's already gotten out of hand. On the other hand if I jump on every single issue the first time it comes up then, well, I'll be out of hand.

The first time someone does something that makes me feel uncomfortable, I notice it. The second time, I acknowledge that the first time was not an isolated event or an accident but a potential pattern and I begin to observe more closely and plan my response. The third time? The third time I always speak to the person about it. I call it my rule of three.

If someone makes a joke about my consulting rates — maybe they say something like, "well, with rates like those, it's a good thing you add value (chuckle, chuckle)." I might laugh along with them but I notice my discomfort. The second time I smile but don't laugh. The third time I say "This is the third time you've joked about my rates — I know it's a joke but I also wonder if you feel like they exceed my value. If so, I'd like to talk about it with you."

If you come late to a meeting once, I notice. Three times? I bring it up.

The first time you demonstrate a lack of teamwork, I notice. The third time? I need to better understand your commitment to the group.

I always say some version of, "I've noticed something three times and I want to discuss it with you." That way we both know it's a trend.

Is it OK to talk to them about it the first time? Sure. You don't have to wait. But everyone slips once or twice. Just don't let it go three times without having a conversation. Three is a good rule of thumb because it allows you to act with confidence that it's not all in your head. And in these situations, confidence is critical to your ability to speak with authority.

"So," Mike said to me after I explained my rule of three, "are you saying I should talk to her about it?"

"I can't help but notice you've asked me that same question three times," I said.
"What do you think?"


[*Names have been changed.]

***

Dec 8, 2009

Indexing referenced column of a table

Most of the databases, foreign Keys needs to be indexed otherwise deleting records from parent table will lock down child table(s). Sometimes this may also lead to a extensive locking and even deadlock situation!


Here is a convenient script I needed once to index all referenced columns of a table - say MEDICAL_CONTACT.


set pagesize 0
set linesize 200
SPOOL medical-contact-fk-index.sql


SELECT  'CREATE INDEX ' || a.table_name || '_' || c.column_name || '_I ON ' || a.table_name || '(' || c.column_name
|| ') ONLINE TABLESPACE INDX01_16K COMPUTE STATISTICS;'
FROM user_constraints a, user_constraints b, user_cons_columns c
WHERE a.r_constraint_name = b.constraint_name
AND a.constraint_name = c.constraint_name
AND a.constraint_type='R'
AND b.table_name='MEDICAL_CONTACT';        


SPOOL OFF;


Might needs to modify medical-contact-fk-index.sql to change index names if cross max length for names.

--

Dec 7, 2009

Linking Oracle features

To turn Real Application Testing (RAT) ON, I need to re-link oracle executables with "rat_on" option/flag.

Here are the quick steps -

1. Shutdown Instance
2. Shutdown Listener and EM
3. Run the make file in $ORACLE_HOME/rdbms/lib/

# ls -l *.mk
-rw-r--r--   1 oracle   oinstall  107073 Nov  7  2008 env_rdbms.mk
-rw-r--r--   1 oracle   oinstall   27158 Nov  7  2008 ins_rdbms.mk

# /usr/ccs/bin/make -f ins_rdbms.mk rat_on ioracle

The make file will save the existing version in $ORACLE_HOME/bin appending a 'O' at the end (could be deleted later if there is a disk space pressure) -

# ls -l oracle*
-rwsr-s--x   1 oracle   oinstall 129141416 Dec  1 06:05 oracle
-rwsr-s--x   1 oracle   oinstall 129141416 Nov 17 04:05 oracleO

4. Start Instance and other services.

That's it!

There is another thing with 'relink' that might be needed in the following circumstances -

* New installation failed in relinking phase
* After OS upgrade
* Changes made to the OS system libraries
* Oracle patch applied with explicit relink instruction

"relink" command is available from $ORACLE_HOME/bin and "relink" takes the following parameters values -

all, oracle, network, client, client_sharedlib, interMedia, precomp, utilities, oemagent


From Oracle - 
http://download.oracle.com/docs/cd/B19306_01/server.102/b15658/cnfg_prd.htm#CHDECBHC


This is a sophisticated one, it could damage Oracle installations if not done properly!

***

Dec 4, 2009

Software Design Principles

These are some of my all time favorite design principles I learned my earlier days as a developer. Still, whenever it comes in front of my eyes, I don't miss reading!


Don't Let Architecture Astronauts Scare You
http://www.joelonsoftware.com/articles/fog0000000018.html

KISS principle
http://en.wikipedia.org/wiki/K.I.S.S.

You ain't gonna need it
http://en.wikipedia.org/wiki/YAGNI

***

Dec 3, 2009

Evaluating Jailer - database subsetting tool

We need a small subset of production data out of hundreds of gigabytes - i,e. the records for a test user across all the tables where there is a FK reference. Having the small set of data for all tables, we could  build a database instance on developers' machine quickly. The referential integrity was the main concern. It seemed that Jailer handles things well. It's an open source platform independent tool.

What is Jailer?

Jailer is a tool for database subsetting and sampling, schema browsing, and rendering. It exports consistent, referentially intact row-sets from relational databases. It removes obsolete data without violating integrity.

But the problem I faced - we don't have a common column (for example, user_id or agency_id for which I wanted to pull records from all tables) directly referenced. Even, it's slow with some 100 tables where we have huge amount of data.

However, I love the tool. It's a good one for database analysis and data sampling for small schema!

For details -

http://jailer.sourceforge.net/

***