Database performance optimization

Optimized content
Database Throughput
Data Volume retrieved by memory per unit time
Database User Response Time
System Service Time
Increase database Throughput
User wait time
Reduces the conflict rate of user access to the same database resource
Performance Optimization process
1. Design of Data Structure Adjustment
Frequently accessed tables: index creation
Which tables are frequently connected: redundant fields are created.
2. Adjust the application Program Structure Design
CS/BS: fewer people and more people
OLTP/OLAP: multiple data updates/queries
3. Adjust Database SQL statements
SQL statements are the key performance of the application system.
4. Adjust the Server Memory Allocation
Save More shared resources
5. Adjust hard disk I/O
6. Adjust Operating System Parameters
SGA (Global shared memory) Sharing pool
The shared pool is the memory area where PL/SQL programs perform syntax analysis, compilation, and execution. The size of the Shared Pool directly affects the database performance. Saves the SQL analysis results, reduces the time required to analyze the same SQL, and improves SQL Execution efficiency.
Avoid '*' in the select clause '*'
* 'Is a convenient method, but it is also a very inefficient method. in fact, Oracle converts '*' into all column names in sequence during parsing. This task is done by querying the data dictionary, which means it takes more time. you do not need to retrieve all data rules each time:
Not exists, not in, table join.
Rule: not exists instead of not in
Rule: replace exists with table join
1. query records that exist in one table but not exists in another table.
2. query all records in multiple tables, and use tables for the fastest Join Operation
-- Query departments without any persons
-- This department number is not found in the employee analysis table.
Select * from Dept where Dept. deptno not in
(Select EMP. deptno from EMP ); -- Not in
Optimization Method
Select * from Dept where not exists -- not exists has better performance than not in
(Select 'A' from EMP where EMP. deptno = Dept. deptno)
Table join
Select * from EMP where EMP. deptno in (select Dept. deptno from Dept)
Select * from EMP where exists (select 'D' from Dept where Dept. deptno = EMP. deptno );
Optimization Method
Select * from EMP join dept on EMP. deptno = Dept. deptno;

Notes for Indexing
The essence of an index is to create an internal index table for quick search.
Indexes can speed up the execution of select statements on the table, but reduce the execution speed of data addition, deletion, and modification statements.
Where the index should be used
 Indexed columns are often queried in the where condition.
 Query returns less data (less than 1/3)
The index should not be used.
 Index creation is not required for small tables.
 When index columns of a table are frequently modified
 When a large amount of data is returned
Use commit as soon as possible, but pay attention to transaction integrity.
Finance Department 1 is Zhang Sanjia, and Finance Department 2 is Zhang San Minus 500.
Zhang San transfers 500 yuan to Li Si.
Use char type for frequently modified Fields
 If a varchar column is often modified and the length of the modified data is different each time, this will cause row migration, which leads to redundant I/O, it should be avoided during database design and adjustment. In this case, it would be better to replace varchar with Char.
 Because char is more efficient than varchar. When you query data in a database, you must first obtain the length of the data for the variable length type field, but not for the long type field. Therefore, the query efficiency is higher than that of the variable length type field.
 In actual development, we recommend that you use char (1) instead of the bit type to improve system compatibility.