Evolving trends and directions for DBAs and Developers: How do DBAs stay marketable?

I visit a large number of organizations every year to deliver training, short term consulting as well as business and technology seminars in the area of database servers, security, software development and middle-tier technology. So I thought I would share some of the trends I have seen in 2008 that I believe will continue in 2009.

I'm always being asked the following questions; "How do I stay marketable?" or "What trends are occurring in technology that impact DBAs and Developers?". The trends are pretty obvious, the question is what conclusions do we draw from them.

IT Continuing Trends for 2009

Some of the noticeable trends:

• Common DBA skills (administration, backup/recovery, tuning) are becoming more of a commodity and easier to outsource every day. A recent quote from a high end recruiter "I don't have any use for technical DBAs. Now applications DBAs are worth their weight in gold".
  
• Oracle Application DBAs (Financials, ERP, Hyperion, ...) are becoming more valuable and marketable every day. This is the skillset of the future, not the basic technical DBA skills.
• Middle-tier applications increase in importance and visibility. The key in most organizations is their middle-tier business applications (vendor and custom). An Oracle database license can be a few million dollars. An Oracle or SAP Financials implementation can cost 100 million dollars. I wonder where an organization will place their emphasis?
  
• Oracle Fusion Middleware skills (Oracle Application Server, web services, BPEL, SOA, XML, ...) are becoming more valuable than ever. As Oracle Fusion applications roll out in the future, these middle-tier skills will increase in marketability and demand.
  
• Middle-tier architecture skills working with application servers, middle-tier caching, web services, J2EE, PHP, Ruby on Rails are increasing in demand.
• Architectural skills and high availability expertise across all tiers are needed more than ever. If a system is slow, there has to be people that can do problem resolution and performance tuning across all tiers.
• Cross platform expertise. DBAs that can support Oracle, MySQL and SQL Server environments are more valuable than a technical DBA that can only support one environment.
• Virtualization will increase significantly. Oracle's VM and Sun's VM (Containers, Zones) will see an increase in database and application servers running production environments using VMs. Sun Containers are a very powerful way of setting up cloning and failover. So VMs not only provide very cost and environmental effective ways of implementing servers but offer significant advantages in administration and high availability. One of the biggest success stories with Sun Containers was an Oracle Hyperion implementation that the consultants told me that can't now imagine implementing this any other way.
• Open source will continue to see significant growth in the next year. The reduced cost and flexibility of open source solutions will significantly help organizations be cost effective and competitive.
  

The best present you can give yourself for the next year is to learn new skills to increase your marketablity and value to organizations.

MySQL Profiling: SQL Tuning

MySQL provides a number of different tools for tuning SQL statements. Some of the key SQL tuning tools include:

• EXPLAIN - Displays execution plans generated by the MySQL Cost Based Optimizer.
• Status Variables - Contains statistics on SQL run time activity.
• Profiling - Contains run time statistics on each phase in the execution of individual SQL statements.
  

Main Phases in Processing a SQL Statement

Profiling allows access to very detailed run time statistics on each phase of processing a SQL command. The main phases include:

• Parsing the SQL statement.
• Generating an execution plan.
• Performing an execution and fetch.
• Cleaning up resources.

Profiling Benefits

Profiling provides the following benefits:

• Ability to understand time spent in each phase of processing a SQL command.
• Displays if the OS performed a disk read because the requested block is not in memory.
• The number of waits that occurred while the thread processed the SQL command.
• Disk activity generated by the SQL statement.
• Resources allocated for processing the SQL command.
• Approximations of time required to process each step of the SQL statement.
• Comparision of execution times relative to other SQL statements.
• If you're into the source code, each C function and line number where it is executed for each step of processing the SQL statement.
  

Profiling Resources in MySQL

The profiling resources available beginning in MySQL 5.1.28 include:

• SHOW PROFILE command.
• SHOW PROFILES command.
• Information_schema.profiling table.

Turning Profiling ON

Profiling is turned OFF (0) by default. Profiling is turned ON (1) at the session level. The PROFILING_HISTORY_SIZE parameter is used to determine how many statements are kept in the history. The default is 15 statements. The query id will change for each SQL statement executed in a session.

mysql> SET PROFILING=1;


SHOW PROFILES

The SHOW PROFILES command will display the query id, the duration and the SQL command executed.

mysql> SHOW PROFILES;
+----------+------------+------------------------------------------------------
| Query_ID | Duration | Query
+----------+------------+--------------------------------------------------------
| 1 | 0.00073900 | SELECT Co.Name, Ci.Name, Ci.Population FROM CountryList Co, CityList Ci WHERE Co.Code = Ci.CountryCode AND Ci.Population > 8000000 |
| 2 | 0.00086100 | SELECT query_id, seq,state, duration, source_function FROM information_schema.profiling WHERE query_id = 1 |
...


SHOW PROFILE

SHOW PROFILE [type [, type] ... ]
 [FOR QUERY n]
 [LIMIT row_count [OFFSET offset]]

type:
 ALL
| BLOCK IO
| CONTEXT SWITCHES
| CPU
| IPC
| MEMORY
| PAGE FAULTS
| SOURCE
| SWAPS

mysql> SHOW PROFILE FOR QUERY 3;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000091 |
| freeing items | 0.000040 |
| logging slow query | 0.000007 |
| cleaning up | 0.000007 |
+--------------------+----------+
4 rows in set (0.00 sec)

INFORMATION_SCHEMA.PROFILING

There are much more interersting queries you can get out of this database object, but this simple query fits in the blog eaily and provides a hint of detailed information available.

mysql> SELECT query_id, duration, state
FROM information_schema.profiling WHERE query_id = 8;
+----------+----------+--------------------+
| query_id | duration | state |
+----------+----------+--------------------+
| 8 | 0.000100 | starting |
| 8 | 0.000068 | Opening tables |
| 8 | 0.000008 | System lock |
| 8 | 0.000013 | Table lock |
| 8 | 0.000028 | init |
| 8 | 0.000015 | optimizing |
| 8 | 0.000016 | statistics |
| 8 | 0.000017 | preparing |
| 8 | 0.001850 | executing |
| 8 | 0.000162 | Sending data |
| 8 | 0.000008 | end |
| 8 | 0.000006 | query end |
| 8 | 0.000069 | freeing items |
| 8 | 0.000034 | removing tmp table |
| 8 | 0.000007 | closing tables |
| 8 | 0.000005 | logging slow query |
| 8 | 0.000007 | cleaning up |
+----------+----------+--------------------+
17 rows in set (0.01 sec)

Thoughts on Profiling

The profiling feature needs to mature more in MySQL, yet this early implementation does provide information that can be useful for evaluating SQL statements. Combining profile information, thread information and session state information together provides different perspectives into the evaluation of SQL processing.