Comparing the effort: Trace instructions and the conclusion

To complete our studies on Comparing the efford in databases, we study one more feature:

10) Trace instructions

The task Trace instructions refers to how to capture and analyze the instructions that are coming to the database via an application, system, tool or specific database feature.

Oracle has several levels of trace from the most basic to the most complex. Oracle allo the instructions sent to it be consulted via virtual tables views (V $ prefix) and do not contain the values of the search parameters commonly used in the WHERE clause that are supplied by the user through the application. In SQL Server you can use a tool called SQL Server Profiler which has features such as filters, templates, sorting, and other events without having to configure any options inside the database. Both MySQL and PostgreSQL have configuration parameters that must be enabled in the configuration files before the server is started in order to log the instructions. Additionally, you need to manipulate the log file of instructions later for a more detailed analysis which makes the task of tracking and analysis a little more difficult in these two databases in relation to others.
A comparison of the level of difficulty to the task of Trace instructions is shown in Table 1.


Table 1. Comparison of the level of difficulty to the task of tracking instructions (trace).

Conclusion

Finally, the comparison of the degree of difficulty for each task in each of the databases is shown in Table 2.


Table 2. General Table of comparison difficulty degree x Task x Database.

This article presented a comparison of the difficulty for performing common administrative tasks in the four major relational databases currently used: Oracle, SQL Server, PostgreSQL and MySQL.

Initially it was defined what would be the point of view adopted in the comparison, i.e. the comparison was carried out according to the opinion of a junior DBA who had to learn to perform tasks in the databases. This benchmark is important to indicate that all the values attributed to difficulties in the executions of tasks are subjective and may not always indicate a consensus among professionals.

Then it was explained what are the three values used in the comparison of tasks: Easy, Medium and Hard. For each task, each database received one of these values. The comparison itself was performed on ten tasks performed in the day-to-day life of a DBA who works in an environment with the databases mentioned. Each task was presented through a short description and a simple statement and summary of how to accomplish this task in four databases taking into account the resources available to do so.

Finally, the comparison could indicate some trends in accordance with the tasks and the reference adopted: the free and open source databases, in general, have a difficulty level higher than the proprietary databases, probably due to lack of graphics user interfaces and the amount of effort required to perform some tasks, something that has already been discovered and treated by vendors. Since the proprietary databases received the same amount of difficult reviews and different amounts of Easy evaluations, indicating that even with a paid product one can still found tasks that need to be improved from the viewpoint of ease of implementation. Another interesting factor was the same assessment for Task 4 - Import and Export data. This assessment was equal to all databases because of the great similarity in the commands used to import and export a data file in CSV format directly to and from the tables of the database.

An overview of the table with the difficulties presented in this article suggests, at least superficially, that there are more tasks with the easy degree of implementation tasks than the difficult degree of implementation. This kind of conclusion may suggest that the work of a DBA is relatively simple, but it is worth mentioning that every situation where you need to perform some tasks listed may have different due to complexity factors that were not taken into account. Thus it is always recommended to take into account operational factors such as task execution time, resources, priorities, workplace rules, regulations and procedures of the company before making a definitive conclusion about how easy it is to be a DBA.

(If you want to learn more about databases, go to our database online courses)

References
List of online documentation for the Oracle 10g Realase 2 (10.2):
http://www.oracle.com/pls/db102/portal.all_books
Official documentation of SQL Server 2008 R2:
http://msdn.microsoft.com/en-us/library/ms130214.aspx
Official documentation of PostgreSQL 8.4:
http://www.postgresql.org/docs/8.4/static/index.html
Official documentation of MySQL 5.5:
http://dev.mysql.com/doc/refman/5.5/en/