The importance of DBA in system design and development

The performance or stability of many application systems is not ideal, which is becoming a difficult problem soon after the system is online, and the reasons for these problems often reflect the fact that the people who design these systems are not very familiar with the database itself! and the DBA does not understand the business! This leads to a lot of problems that could have been avoided; On the other hand, with the database self-regulation, the management of the ability to constantly strengthen, and the application is often the biggest killer of the system, so, the scope of the DBA, from only responsible for database server maintenance, and gradually towards the management of application system design, development, is the inevitable trend!

First, the current DBA to the system performance and stability of the adjustment work

The current DBA's tuning of system performance is roughly in several ways:

1, in the hardware level tuning, which is usually direct spending, buy equipment, expansion.

2, in the DB level tuning, such as adjusting initialization parameters, adjust the physical structure of the database.

3, the application of the SQL optimization, such as in the database analysis Statspack, adjust top SQL.

4, only a very few, usually the system stability requirements of a number of companies to apply, before the new application on the line, let the DBA for the full audit and evaluation of SQL.

Problem: In the application system analysis, design, development phase, in the current situation, there is very little DBA participation, and the application system on the line or development work after the basic end, the DBA can do the tuning work is actually very limited.

Performance or stability of many application systems is still not ideal

Many application system performance is not ideal, or the system data will be some difficult to reproduce the strange error, these problems (especially performance problems) are sometimes not reflected in the early system, but as the system running, data increase and gradually become difficult to solve, To the system later on the function expansion and user use of a lot of trouble, the cause of these problems, often embodies a point: the development, design of these systems people do not understand the database! Take an Oracle based application for example, give a brief example:

　The underlying data structure is unreasonable

Due to the lack of professional DBA assistance, many system design of the underlying database table structure problems. And people who have done the system know that the underlying database structure is unreasonable, the cost of transformation is almost equal to a reconstruction! I've seen an OLTP system with a core table that has 100 fields, averaging one record over 8K, if you press Oracle's default 8K block, More than half of the lines must produce a row link!

And, worst of all, people who design this kind of table structure also think they take full advantage of redundancy to reduce the connection between tables, in fact, the person does not know what is the paradigm, what is the update of the exception, according to the paradigm, this table should be divided into two tables, but if you want to change almost all the procedures must be changed! Although the paradigm is not the higher the better, But it is definitely a thing that the design person must be thoroughly digested. In redundancy, it is believed that most DBAs agree that cascading updates are costly, so redundancy should avoid cascading updates, and the use of redundancy in relational database design is by no means an easy skill to master.

Unreasonable underlying database structure design, to the performance of the system buried a heavy time bomb, the system in the customer there less than a year, the amount of data slightly up, performance, stability on the line down, and the cost of restructuring is extremely high, to buy a new server is certainly only a symptom. And what if the underlying datasheet structure is designed by a senior DBA? Of course, if the DBA is fully designed to do the database table structure, the DBA must have a very clear understanding of the business details of the entire system, which, in the case of DBAs, has some difficulty in human resources, After all, the maintenance of online servers has taken up a lot of the resources of DBAs, and leaders often value this more.

Few leaders recognize the role of DBAs in system development design, and are equally important to the stability and performance of the entire system, compared to maintaining online systems and handling DB failures.

SQL Performance Issues

The development of a system usually has nothing to do with DBAs, but if the DBA has enough knowledge of the system, it can make a lot of contributions. For example, check the system business data flow is correct, this need through some means, such as SQLTrace, 10046, and so on, detailed system of the logical implementation of the inspection, on the one hand, to detect the system is too consuming resources or write irregular SQL timely adjustment optimization, on the other hand, Check the system for unreasonable database access, do not go to the line to find the problem, it may have been down. As a simple example, when a page needs to display a list of items in multiple categories, programs tend to make a mistake by reading the same data multiple times with the same SQL and applying it to every list display, if you only read it once, or simply cache it on the web (with the appropriate refresh policy), Can significantly reduce the I/O consumption on DB for a single access to this page. Sometimes even check out the SQL that does not need to be executed at all, and perform frequently in these functions that are irrelevant to them ... At the same time, the data flow check can also detect some deep hidden system bugs, which need to be based on DBA understanding of business details.

Who says the DBA only spends money? If a server I/O load reaches the limit, most people can only choose to expand, most refactoring part of the function to do some optimization, and from the statspack can often be seen, the system's I/O resources are mostly not so frequently executed SQL to occupy, Their single execution is not slow, but the proportion of the system resources are unusually high, these problems, detailed in each business, the inspection of these problems and data flow optimization, is the system resources of the greatest savings, is to save money! This job, perhaps only the DBA can be competent.