What is the role of OracleSQL optimization?

The following articles mainly describe the projects we need to use when Oracle databases are used for related enterprises, including OracleSQL optimization, there is also a description of the related content of multi-database collaboration. The following describes the specific content.

OracleSQL Optimization

Database optimization is mainly performed by DBA, and the optimization is divided into many steps. Based on experience, the first thing to adjust is the SQL statement written by the programmer, a bad SQL statement, it is not an exaggeration to say that it can cause the entire Oracle to go down. Of course, we should not describe how fragile Oracle is. The first thing we should look at is how OracleSQL is optimized.

In fact, in the development environment or test environment, it is sometimes difficult to find real performance problems, because the data volume in the development environment may be much smaller than the actual data volume in the production environment, even if there is a lot of FTS, efficiency is acceptable. This kind of work method makes tuning difficult. Therefore, after going online, the production system reports a problem, then the programmer tries to simulate the production environment to reproduce the problem and solve it.

For a better test scheme, stress testing is generally included, and a large data volume test is also included. You can simulate a large data volume and then perform the test. This is a good method. The optimization method is to use the SQL Execution Plan to check whether the correct index is used. As discussed above, If you confirm that there is a problem with the index.

Please create an index to solve the problem. If an index has been created but the index is not used, it may be that the table analysis is not in place and you need to perform a new table analysis. You can request DBA for help. If both of the above are done and the index still cannot be used correctly, you need to use the hint function to force the use of the index. This function is complicated and will not be said any more. In actual work, consult DBA.

Sometimes SQL exists in the whole system and it is difficult to extract it separately. There are several methods:

1. Add a breakpoint in the program. When the program runs to the SQL, obtain OracleSQL and analyze it by yourself.

2. Add the output in the program, directly output the SQL statement to the external file, and then analyze it slowly.

3. apply to DBA, enable the Oracle Trace function, and record all SQL statements. However, this method will reduce the efficiency of the entire system. After using this method, remember to adjust the parameters back. What programmers can do is SQL optimization. Other more in-depth system optimization can be avoided.

Multi-database collaboration

The database mentioned here actually refers to the "instances" that are often referred to in Oracle. These instances can be located on the same machine or on different machines. Sometimes "multi-database" can also refer to different users in the same instance. If all the content is in the same instance, it is only divided into different users, this processing method is the simplest. You only need to add the name of another user before the referenced table name. For example, if user1 wants to use the table in user2, you can write it as follows:

 
 

  
  
select * from user2.table_name 
 
 

If you feel that it is inconvenient to include other user names in the application every time or for other reasons, you can replace them in two ways:

1. View

 
 

  
  
Create or replace view table_name as select * from user2.table_name; 
 
 

In this way, you can directly reference the view in user1 to reference the table in user2.

2. Synonyms

 
 

  
  
Create synonym table_name for user2.table_name;  
 
 

This method is more professional than the view. It is generally handled in this way. When table_name is used in user1, it is automatically converted to the corresponding location according to the definition of synonyms.

Another difference with a view is that synonyms can map any object, not just tables, such as packages. This method should be used to replace the view.

If multiple databases are not in the same instance, you need to use DBLINK for connection. You can refer to the create database link method, which is rarely used in general applications.

The above is a default premise that user2 allows user1 to use its resources. If there is no permission, user1 cannot operate the table of user2. in this case, an authorized operation is involved, authorization must be handled by user2. other users with DBA roles cannot do the same.

Use the following statement to grant permissions:

 
 

  
  
grant select on table_name to user1;  
 
 

Note that if user1 has a DBA role, you can directly use the table in user2 without showing permissions. However, if you write a stored procedure in user1, the table referenced in user2 in the function must be displayed with the permission granted. This is very confusing and requires special attention.