Oracle Database access Performance Optimization __python

Reader to like:

Developer: If you are doing database development, the content of this article is very suitable, because this article is from the programmer's point of view on database performance optimization.

Architect: If you are a database application architect, you should be aware of the knowledge of this article 90%, or you might be an architect who likes to toss about it.

DBA (Database administrator): The knowledge of large database optimization is very complex, this article only discusses the performance optimization from the programmer's point of view, in addition to the need to understand this knowledge, the DBA needs to delve into the internal architecture of the database to solve the problem.

Introduction

There are many articles on the Web to introduce database optimization knowledge, but most of the articles are only a description of one aspect, and for our programmers this introduction does not have a good grasp of the optimization knowledge, because many of the introduction is only to some specific scenarios optimization, So it's sometimes misleading or confusing the programmer with the idea that it's a mystery to database optimization.

Many programmers always ask how to learn database optimization, there are no good textbooks and so on. In the bookstore also saw a lot of database optimization of professional books, but feel more oriented to DBA or PL/SQL development knowledge, personal feeling is not very suitable for ordinary programmers. And to do the database optimization of the master, not to spend a few weeks, a few months can be achieved, this is not because of the depth of database optimization, but because to do a good job of optimization on the one hand needs to have a very strong technical foundation, the operating system, storage hardware network, database principles, etc. have a relatively solid foundation of knowledge, On the other hand, it is necessary to spend a lot of time on the specific database for practical test and summary.

As a programmer, we may not be aware of the online formal server hardware configuration, we can not be as professional as the DBA to do a variety of practical testing and summary of the database, but we should all know our SQL business logic, we know the SQL access to tables and fields of data, We're actually only concerned about whether our SQL can return results as soon as possible. How the programmer uses known knowledge to optimize the database. How to quickly locate SQL performance problems and find the right direction to optimize.

In the face of these problems, the author sums up some basic optimization principles for programmers, this article will be combined with examples to illustrate the optimization knowledge of database development. A brief introduction of database access optimization rules

To properly optimize SQL, we need to quickly locate the bottleneck point of the energy, which means to quickly find out where our SQL main overhead is. And most of the slowest-performing devices will be the bottleneck. If the network speed may be a bottleneck when downloading, local copy files when the hard disk may be the bottleneck, why these general work we can quickly identify bottlenecks, because we have some basic knowledge of the performance data of these slow devices, such as network bandwidth is 2Mbps , the hard drive is 7200 rpm per minute and so on. Therefore, in order to quickly find the SQL performance bottlenecks, we also need to understand the hardware of our computer system performance indicators, the following figure shows the current mainstream computer performance indicators data.

As you can see from the diagram, there are basically two indicators for each device:

Delay (response time): Indicates the hardware burst processing ability;

Bandwidth (throughput): represents hardware continuous processing capability.

As can be seen from the above diagram, computer system hardware performance from high to generation is:

Cpu--cache (L1-L2-L3)--Memory--SSD hard disk--network--hard disk

Because SSD is still in the rapid development stage, so the content of this article does not involve SSD related application system.

Based on the database knowledge, we can list each kind of hardware main work content:

CPU and Memory: Cache data access, comparison, sorting, transaction detection, SQL parsing, function or logical operation;

Network: Result data transfer, SQL request, remote database access (Dblink);

Hard disk: Data access, data writing, logging, large data ordering, large table joins.

According to the basic performance index of the current computer hardware and the main operating contents in the database, the basic performance optimization rules as shown in the following diagram can be sorted out:

This optimization principle is summed up at 5 levels:

1. Reduce data access (reduce disk access)

2. Return less data (reduce network transport or disk access)

3, reduce the number of interactions (reduce network transmission)

4, reduce server CPU overhead (reduce CPU and memory overhead)

5. Use of additional resources (additional resources)

Because each layer of optimization rules are to solve their corresponding hardware performance problems, so the proportion of performance increase is not the same. The traditional database system design is also as far as possible to provide optimization method for low-speed equipment, so for low-speed equipment problem can be optimized means more, optimization cost is also lower. Any of our SQL performance optimizations should be up to this rule to diagnose problems and propose solutions, not the first thing to think about is adding resources to solve problems.

The following is a reference to optimization effect and cost experience for each optimization rule hierarchy:

p> low

Optimization law	Performance boost	Optimized cost
Reduce data access	1~1000	Low
Return less data /p>	1~100	Low
Reduce interaction	1~2 0	Low
reduce server CPU overhead	1~5
Take more resources	@~10