A little experience with oracle optimization
- 2020-11-03 22:38:00
- OfStack
There are many contents and concepts about oracle optimization, but one outline can be summarized as the direction to be considered, and then refined step by step. oracle optimization takes design, development, and adjustment into account in terms of importance.
The first is design, which is the most important part. To paraphrase TOM, "performance is designed, not tweaked." Design is divided into logical design and physical design, logic design should follow 3 NF 1, at the same time also to inverse the standardized design, introduced according to the needs of the application, should be NF design while eliminating data redundancy, but with the standardization of the higher the more table may need to connect, also have an impact on performance, so it is necessary to take into account balance, as for when to 3 NF, when to inverse normalization, if the specific circumstances.
Physical design 1 is stored, such as physical files, online log files and archive log files disk layout, the use of various RAID (piner analysis comprehensive careful) in this respect, the second is to according to the properties of the application, choose file organization way, flexible use of various tables and indexes of oracle types, such as cross table can consider to use IOT, intensive reading and the associated field often use cluster, and global temporary tables, external tables, and so on. In addition to normal B tree indexes, bitmap indexes are considered in low cardinality cases, function-based indexes are introduced when using functions, and so on.
The second is the development phase, such as the usual use of binding variables, foreign keys and indexes, bulk collect, analytic functions, direct path loading, partition, MV, bitmap indexing, parallelization and so on in OLAP applications. There's a lot to go into every single technology.
Last but not least is tuning, which is what you do when you have performance problems. This has led to a complete methodology.
1) Set reasonable performance optimization goals.
2) Measure and record current performance (STATSPACK, AWR, etc.).
3) Determine the current Oracle performance bottleneck (obtain wait events from Oracle $system_event, v$session_event and v$session_wait from Oracle wait interfaces v$system_event, v$session_event and v$session_wait to identify the objects and sql statements that affect performance
).
4) Record the waiting events into the trace file (autotrace, 10046 events, etc.).
5) Determine the current OS bottleneck (sar, iostat, cpustat, mpstat, netstat, top, osview, etc.).
6) Optimize the components needed (application, database, I/O, contention, OS, etc.).
7) Track and implement the change control process.
8) Measure and record current performance
9) Repeat steps 3 to 7 until the optimization goal is met
The above are some broad lines of my experience on Oracle optimization, which can point out the general direction of optimization that needs to be considered. There are a lot of relevant technical details, and each of them should be carefully studied, as summarized by piner: think more, practice more, and summarize more.
Remember another topic, the company after the holiday to make personnel adjustments to the technical department, in other words, is to lay off people, in addition to a network management to ensure that the Internet will not leave the technology of the people, vacation resume preparation, after the holiday to find a job.
The first is design, which is the most important part. To paraphrase TOM, "performance is designed, not tweaked." Design is divided into logical design and physical design, logic design should follow 3 NF 1, at the same time also to inverse the standardized design, introduced according to the needs of the application, should be NF design while eliminating data redundancy, but with the standardization of the higher the more table may need to connect, also have an impact on performance, so it is necessary to take into account balance, as for when to 3 NF, when to inverse normalization, if the specific circumstances.
Physical design 1 is stored, such as physical files, online log files and archive log files disk layout, the use of various RAID (piner analysis comprehensive careful) in this respect, the second is to according to the properties of the application, choose file organization way, flexible use of various tables and indexes of oracle types, such as cross table can consider to use IOT, intensive reading and the associated field often use cluster, and global temporary tables, external tables, and so on. In addition to normal B tree indexes, bitmap indexes are considered in low cardinality cases, function-based indexes are introduced when using functions, and so on.
The second is the development phase, such as the usual use of binding variables, foreign keys and indexes, bulk collect, analytic functions, direct path loading, partition, MV, bitmap indexing, parallelization and so on in OLAP applications. There's a lot to go into every single technology.
Last but not least is tuning, which is what you do when you have performance problems. This has led to a complete methodology.
1) Set reasonable performance optimization goals.
2) Measure and record current performance (STATSPACK, AWR, etc.).
3) Determine the current Oracle performance bottleneck (obtain wait events from Oracle $system_event, v$session_event and v$session_wait from Oracle wait interfaces v$system_event, v$session_event and v$session_wait to identify the objects and sql statements that affect performance
).
4) Record the waiting events into the trace file (autotrace, 10046 events, etc.).
5) Determine the current OS bottleneck (sar, iostat, cpustat, mpstat, netstat, top, osview, etc.).
6) Optimize the components needed (application, database, I/O, contention, OS, etc.).
7) Track and implement the change control process.
8) Measure and record current performance
9) Repeat steps 3 to 7 until the optimization goal is met
The above are some broad lines of my experience on Oracle optimization, which can point out the general direction of optimization that needs to be considered. There are a lot of relevant technical details, and each of them should be carefully studied, as summarized by piner: think more, practice more, and summarize more.
Remember another topic, the company after the holiday to make personnel adjustments to the technical department, in other words, is to lay off people, in addition to a network management to ensure that the Internet will not leave the technology of the people, vacation resume preparation, after the holiday to find a job.