Oracle optimization experience

There are a lot of content about oracle optimization and the concept is complex, but you can summarize an outline as the direction to be considered, and then gradually refine it. The design, development, and adjustment of oracle optimization should be considered as important.

The first is design. This is the most important part. In the words of TOM, "performance is designed, not adjusted ". The design can be divided into logical design and physical design. The logical design generally follows 3NF, And the inverse Normalization Design should also be introduced according to the needs of the application. Although data redundancy should be eliminated for the NF design, however, the higher the normalization, the more tables that need to be connected, which also has an impact on the performance. Therefore, we need to balance the consideration of when to use 3NF and when to use reverse normalization, depending on the specific situation.

One physical design is storage, such as the disk layout of physical files, online log files, archived log files, and the use of various RAID (in this respect, piner analysis is comprehensive and meticulous ), the second is to select the file organization mode based on the characteristics of the application to flexibly use various oracle tables and index types, for example, you can consider using IOT, read-intensive, and frequently-associated fields in a cross tabulation, as well as global temporary tables and external tables. In addition to the common B-tree indexes, bitmap indexes are considered in the case of low base numbers, and function-based indexes are introduced when functions are used.

The second is the development phase. For example, we often talk about using binding variables, foreign keys and indexes, bulk collect, analysis functions, and direct path loading, use partition, MV, bitmap index, and parallel operations in OLAP applications. There is a lot of content for in-depth research on none of the technologies.

The last step is adjustment. This is the method used in the case of performance problems. This has formed a complete methodology.
1) set up reasonable performance optimization goals.
2) measure and record the current performance (STATSPACK, AWR, etc ).
3) determine the current Oracle performance bottleneck (obtain the wait events from the Oracle wait interfaces v $ system_event, v $ session_event, and v $ session_wait, and then find out the objects that affect the performance and SQL statements.
).
4) record the waiting event to the tracking file (such as autotrace and 10046 event ).
5) determine the current OS bottleneck (sar, iostat, cpustat, mpstat, netstat, top, and osview ).
6) components required for optimization (applications, databases, I/O, contention, OS, etc ).
7) Track and implement the change control process.
8) measure and record the current performance
9) Repeat steps 3 to 7 until the optimization goal is met.

The above is my understanding of some rough lines of Oracle optimization, which can point out the major direction to be considered for optimization, and there are a lot of technical details, each item should be carefully studied, as piner concluded, it involves multiple thoughts, practices, and summaries.

Let's repeat some other things. After the festival, the company will make adjustments to the technical department. To put it bluntly, it will be the award. This time, we will not leave any technical staff except for a network administrator who can ensure the internet access. We will prepare resumes during the holidays, find a job after the festival.