20 Ways to Screw Up Your Management Career

I had been reading this old one - which most of us realizes (more or less) from a management point of view. Still good to revise :-)

 20 Ways to Screw Up Your Management Career

Sun Oracle Exadata Version 2

Last week got an opportunity to attend a workshop on Oracle Exadata. About two years ago, when Exadata first released, attended another introductory session presented by Oracle Asia experts. That time I was a bit hopeless with the product as Exadata was specially focused on very big Data Warehouse solutions. I was expecting something that fits with our OLTP + mixed kind of systems and may benefit us.

Now, with the Exadata version 2, it seems that they are probably going to impress everyone in terms of performance and scalability. I found it very exciting this time!

The Exadata version 1 was marketed for Data Warehouse type solutions with very big system - 8 nodes Database Server Grid and 14 nodes Storage Grid (which they are calling now Full Rack). For a small to medium kind of system, this was not perfect and efficient (in terms of money) I believe. After acquiring Sun, Oracle came up with Exadata version 2 with Sun hardware (version 1 was on HP) and emphasis given on OLTP systems as well. More importantly, they came up with different packaging solutions like Half Rack and Quarter Rack which are a kind of thing I was interested in.

The cool features of Exadata are -

* Exadata Query Offloading: Exadata solution offloads data-intensive query processing from Oracle Database servers and does the query processing closer to the data storage. The result is faster parallel data processing and less data movement through higher bandwidth connections. This massively parallel architecture also offers linear scalability.

* Smart Flash Cache: A transparent extension of database buffer cache. Lot more I/O throughput and huge performance improvement. 1 GB costs only $50. We would be able to cache the whole database almost. They say, "This will break random I/O bottleneck by increasing I/O per sec by 20X and doubles user data scan bandwidths".

* Hybrid Columnar Compression (HCC): They say, "Efficient compression increases effective storage capacity and increases user data scan bandwidths by a factor of 10X". Online archiving compression feature will reduce our historical data size about 90%. This will help us to manage old data in one place. No need for separate historical data servers in cheaper disks. Old data partitions will be compressed in online archive mode.

* Exadata Storage Indexes: Eliminate unnecessary I/Os to disk and contributes to the overall performance improvements. These are not actually index, these are kind of filters which intelligently organizes data inside disks. Result is big performance improvement - even with full table scans!


I made a presentation for my other team members to give an overview on Exadata. Here is the summary -

Oracle defines Exadata like this -

* Massively Parallel Grid, the Architecture of the Future

* Extreme Performance: 20X for OLTP and 10X - 100X for data warehouse

* Linear Data Scalability: Same performance for 1 TB and 5 TB of data

* Enterprise Ready: Packaged, readily available. Single support from Oracle for hardware and software

* All Standard: Works transparently with all existing applications

Improvements:

* Millions of transactions per minute
* 10 millions queries per minute
* Billions of rows per minute
* 1 million I/O per second
* 50 GB/sec flash bandwidth
* 21 GB/sec disk bandwidth
* InfiniBand network: 40 Gb per second throughput

Different Exadata Appliances

* Full Rack: 8 Database server and 14 Storage cell server
* Half Rack: 4 Database server and 7 Storage server
* Quarter Rack: 2 Database server and 3 Storage server
* Single Server: 1 Database server and 1 Storage server

Exadata Quarter Rack

* 2 Sun Fire™ X4170 Oracle Database servers
* 3 Exadata Storage Servers (all SAS or all SATA)
* User Data: 6 TB SAS or 21 TB SATA
* 1.1 TB Flash Cache (4 x 96GB PCI Express Flash Cards on each)
* 2 Sun Datacenter InfiniBand Switch, 36-port Managed QDR (40Gb/s) InfiniBand switch
* 1 “Admin” Cisco Ethernet switch
* Keyboard, Video, Mouse (KVM) hardware
* Redundant PDUs
* Single Point of Support from Oracle
* 3 year, 24 x 7, 4 Hr On-site response


Performance Benchmarks
Some of the performance benchmarks shown -

* 1 TB tablespace creation time was about 9 min! On other hardware platforms it will take about 2 hours!
* On full rack, the test query with ‘query offloading’ turned “off” (means simple RAC), took more than 4 min to execute where turning query offloading “on”, took about 5 sec for the same query!
* Another Data loading example shown - 49 GB data loaded from external file in just 1.5 min!


***

Developer's preliminary checklist before code submission for testing

To impose more responsibility to our developers, made a checklist. This is basically a quick checklist we are suggesting to follow before they push their code for testing. Undoubtedly, this has improved the application quality and reduced some load for testing guys!

Some of these are very specific to our web application, may not be applicable for others -


1. Working features: Make sure all the features, you have just developed, works correctly - one feature did not break other.

2. Field length: Be aware of Database field length for corresponding UI fields. For text fields and text boxes, length must be smaller than Database field length (about 10 characters smaller for every 100 char in db). This is needed for dealing with multi-byte character set.

3. Field validation: Required field and other business validation

4. Required field indication: Must have red asterisk

5. Labels: Labels and messages from Application Resource properties file

6. Spelling: Use correct spelling. No typo.

7. Alignment: Fields and labels should be left aligned.

8. Blank space: No space before 'colon' and 'comma' - use space after comma.

9. Default sort order: For any list, there should be a default sort order

10. URL tempering: Access checking on each page, or something similar to make sure user is allowed to see the page he is requesting.

11. HTML and JavaScript escaping

12. JavaScript error: Check for any JavaScript error in the page.

13. Buttons: Button's position. Proper use of 'Back' and 'Cancel'

14. Date: Proper formatting of date

15. Time Zone: Application of Time Zone where Date is there

16. Activity Tracking: For important actions, ensure 'Activity Tracking' to log user activities

17. Placement of Access Link: Place access links in proper places for users

18. Clear user friendly messages: All confirmation, alert and warning messages

19. Access Control/Privilege Checking: For important business information which depends on user privilege, make sure, list that user sees, are based on their access privileges

20. SQL injection: Use bind variables to avoid SQL injection

21. Double submission problems: What happens when user re-submit the request - handle properly.

22. Invalid Request: Handle bookmarks or other invalid requests

23. Cross browser compatibility: Check for at least two browsers - Firefox 3.5 and IE7


***

Oracle best practices for programmers

These are the things I suggest developers to follow in developing applications where Oracle database is being used. Some of these I learned from my experience as a developer, my own and others mistakes, and the rest, I learned as a dba!


1. Proper and Consistent Naming:

*  Keywords in UPPER case
*  Provide prefix for table name to group tables (for example according to functionality/module)
*  Provide constraint names, index names
*  Honor the company specific guidelines and best practices

2. Normalization of Database:

*  Normalize up to 4th Normal Form
*  I would say, first design ideally then you may denormalize according to needs

If you don't remember what you learned in university, here is a good link to remember by example -
http://support.microsoft.com/kb/100139

3. Primary/Unique key:

*  Every table should have a primary and/or unique key
*  Helpful for replication product
*  Key data should not be visible
*  Do not make a string field as primary key

4. Bind Variable:

*  Use bind variables instead of literals. This is specially true for all OLTP system or a mixed system (OLTP+heavy reporting) like ours.

For example – 

SELECT * FROM users WHERE id = 1009;

Instead, use this in jdbc template –

SELECT * FROM users WHERE id = ?;

And then pass your bind parameters.

5. Nested Query: Avoid nesting queries.

For example, I would avoid the following types of queries -

SELECT login_name FROM user WHERE prov_id IN
            (SELECT id FROM agency WHERE id IN
                (SELECT prov_id FROM message WHERE ....)


6. Data Segmentation:

*  Add column by which you can group/segment data for big tables

For example – add agency id in user forms although it may not be needed for the application.

7. Bulk Operation in Batch:

* Frequent insert/update/deletes in batch.

8. Data Sorting:

*  Avoid unnecessary ORDER BY
*  Avoid unnecessary DISTINCT

9. Database Call:

*  Do not execute queries inside a LOOP
*  Do not open connections inside a LOOP

10. IN and EXISTS:

*  Use IN if the sub-query has the selective WHERE clause. For example -

SELECT * FROM employee_details ed
WHERE ed.emp_id IN (SELECT id FROM employee WHERE ssn = ‘2990-030-998’) ;

*  If opposite or the parent query contains selective WHERE, use the EXISTS. For example -

SELECT * FROM employee_details  ed
WHERE EXISTS (SELECT e.id FROM employee e WHERE ed.emp_id = e.id
AND e.city=’Dallas’) ; 


 11. Data Access:

*  Avoid frequent visit to table data – Caching could be a good solution
*  select/update values at a single step for multiple columns. For example -

--Non recommended
UPDATE user SET first_name =’John’ WHERE id = 11;
UPDATE user SET last_name =’Doe’ WHERE id = 11;
UPDATE user SET ssn=’9900-8987-009’ WHERE id = 11;

--Recommended
UPDATE user SET first_name=’John’, last_name=’Doe’, ssn=’ 9900-8987-009’ WHERE id = 11;

12. JOIN:

* Use equi JOIN.
* Use LEFT JOIN where you really need to use, otherwise it could be misleading.
* Join in the order that returns least number of rows to the parent step.
*  Perform filtering option early in the inner sql that makes a join. Now a days, Oracle optimizer tries to make the join efficiently, but may not do so efficiently all the time

13. CASE:

* Use CASE for multiple aggregates. For example -

--Non Recommended
SELECT a.name "Agency", PENDING.cnt "Pending Approval", APPROVED.cnt "Approved"
FROM agency a
LEFT JOIN
(SELECT p.id, p.name, g.status, COUNT(distinct g.form_id) cnt
FROM agency p
LEFT JOIN user_forms g ON (g.prov_id=p.id)
WHERE g.event_date >= :param1 and g.event_date -1 < :param2
GROUP BY p.id,p.name, g.status
ORDER BY p.name, g.status) PENDING ON (prov.id = PENDING.id)
LEFT JOIN
(SELECT p.id, p.name, g.status, COUNT(distinct g.form_id) cnt
FROM agency p
LEFT JOIN user_forms g ON (g.prov_id=p.id)
WHERE g.event_date >= :param1 and g.event_date -1 < :param2
GROUP BY p.id,p.name, g.status
ORDER BY p.name, g.status) APPROVED ON (prov.id = APPROVED.id)
WHERE a.status=1
ORDER BY a.name;


--Recommended
SELECT p.name "Agency", B.PENDING "Pending Approval", B.APPROVED "Approved"
FROM agency A
LEFT JOIN
(SELECT a.id
, COUNT(CASE when  g.status=2 then g.form_id end) "PENDING"
, COUNT(CASE when  g.status=3 then g.form_id end) "APPROVED"
FROM user_forms g, agency a
WHERE g.prov_id=a.id
AND g.event_date >=   TO_DATE('01-may-2009', 'dd-mon-yyyy')
AND g.event_date -1 < TO_DATE('01-aug-2009', 'dd-mon-yyyy')
group by p.id) B ON A.id=B.id
ORDER BY A.name;


14. Use of Function:

*  Be careful of using functions (SUBSTR, TO_CHAR, UPPER etc ) in WHERE/ORDER BY clause –will fail to use an existing index on that column.
*  Create function based index for these cases if really needed.

For example -

WHERE UPPER(first_name) like 'A%'  - this will not use an index on column "first_name" unless it is function based.

15. Indexing: In general –

*  Index all predicates in WHERE clause
*  Join columns
*  Important FKs.
*  For large table (and no heavy updates), for column having low cardinality data, use bitmap index.

For example – “status” (having a range of values 1 to 5) on “form_summary” table is a good candidate for bitmap indexing.

There should have tradeoffs if there are too many insert/updates/deletes. A dba could help more on index tuning.

16. Integer representation for group values:

*  Use number instead of string for a small domain of values. This will be helpful for Bitmap indexing and filter operation.

For example - for different status, use corresponding number representation instead of string values.
Working =1, Submitted=2, Approved=3, Deleted=4

Now queries could be used like –
SELECT * FROM user_forms WHERE status > 1;

17. Keeping summary of child table: 

* In parent table, keeping information about detail table could be a good trick to improve performance.

Table: USER_FORMS (FORM_ID, CONTENT)
Table: ARCHIVE_DATA (FORM_ID, CONTENT)

--Non Recommended
     SELECT uf.form_id, uf.content,
     (SELECT COUNT(*)  FROM archive_data WHERE form_id = ‘AXB-IU78-Y6TRQ’) AS cnt
     FROM user_forms uf
    WHERE uf.form_id = ‘AXB-IU78-Y6TRQ’;
 
--Recommended
Table: USER_FORMS (FORM_ID, CONTENT,  ARCHIVE_COUNT) // add an extra column which will keep archive count.

      SELECT uf.form_id, uf.content, uf.archive_count
      FROM user_forms uf
     WHERE uf.form_id = ‘AXB-IU78-Y6TRQ’;

18. Avoid OR and IN clauses:

*  Too many OR clauses in queries could be an indication of bad data model! Try to find out alternatives – now!

These are what I remember at the moment.

***

Flashback Query - the life saver!

Flashback features are really a very useful thing if we have such a situation. Depending on the undo retention settings, we could travel past in time!

One manager was looking for an old snapshot of records before it was being modified by the user. This is how I was able to retrieve the thing -

* Created a temp table with the data just before the unwanted changes -

CREATE TABLE tmp_users AS
(SELECT * FROM users
AS OF TIMESTAMP TO_TIMESTAMP('27-DEC-09 12:18:00','DD-MON-YY HH24: MI: SS')
WHERE user_id = '7EF3D2SP');

* There is another cool thing is 10g and onwards - one could flashback a whole table to some point in time.

FLASHBACK TABLE users TO TIMESTAMP TO_TIMESTAMP('27-DEC-09 12:18:00','DD-MON-YY HH24: MI: SS');

* Even, if a table is accidentally dropped, nothing to worry about :-)

FLASHBACK TABLE users TO BEFORE DROP;


I know, there is another cool thing called Flashback Database to rewind the whole database to some period in past! This is much simpler than the traditional point in time recovery with archived redo logs - not posting anything on that today.

***

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

***

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!


***

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.]

***