Enterprise development using Oracle databases (III)

Iii. SQL 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 Program A bad SQL statement written by a member can cause the entire Oracle to go down. This is not an exaggeration. Of course, we should not describe how fragile oracle is, the first thing we should look at is how SQL 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 the index has a problem, 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 apply for DBA assistance. 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 to the program. When the program runs into the SQL, extract the SQL 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.
all programmers can do is SQL optimization. Other more in-depth system optimization methods can be avoided.
4. multi-database collaboration
databases mentioned here actually refer to "instances" that are often referred to in Oracle. These instances can be located on the same machine, it can also be located 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 names of other users before the referenced table name. For example, if user1 wants to use the table in user2, you can write it as select * From user2.table _ name.
if you think 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, the view is directly referenced in user1, you can reference the table in user2
2. synonym
Create synonym table_name for user2.table _ name;
this method is more professional than the view, and is generally handled in this way. When table_name is used in user1, automatically go to the corresponding location according to the definition of the synonym.
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.
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.
V. troubleshooting
If you write too many programs, errors will inevitably occur, and databases are no exception. Errors are not terrible. You only need to find the correct solution in time.
Some practices of recording SQL statements have been mentioned above. When SQL is run, the database generally reports a ora error, such as ora-00600, and gives a brief description, however, the description text is not too detailed. How can we locate the problem based on the clues? If your database server is located on a Unix or Linux server, you need to telnet to log on to the server. The system provides an oerr command line tool to conveniently find out the specific meaning of the error, for example, oerr ora 600. If your database server is on a Windows server, you can only view the help documentation without such a tool.

Although you can see the details of the Error Using the oerr tool, after a period of trial, you will find that the prompts provided by the oerr tool are relatively simple, and some even cannot be seen, in this case, other solutions will be used:
1. Google and other search engines
This is a very cost-effective solution. Based on the powerful features of search engines, you can find a lot of relevant information. Maybe someone has the same problem as you, and the following is the solution.

2. Metalink
This is a paid website officially provided by Oracle. Users can search for information on the website (massive amounts of data, a large amount of information). If they cannot find a solution, they can also ask questions, oracle experts will answer the question. This is an authoritative solution to Oracle problems, but the disadvantage is that if you do not have an account, it will be equal to zero.
You can ask dBA for help with some error prompts that you cannot understand, because some error prompts may be SQL problems on the surface, but they may be caused by the database itself. If you can directly see SQL-independent errors such as "cannot expand", "tablespace full", or "rollback segment old" from the error message, you can directly seek DBA help.
If you are deploying a bsschema system, you may find that when you use a client tool to connect to the server, everything is normal, but the web program cannot connect to the database, and the error "OCI requires 8.1.7 or later" is reported. This is an ambiguous error message. Obviously, all the clients we have installed have met its requirements. What is the problem? The problem is that you need to assign a permission to the Oracle ora92 directory, for example:

If you try this authorization, you will find that this user already exists and has been authorized! But why not? Here, we need a technique to remove the check box "read and run", select the check box, and then "OK", that is, re-assign permissions. Depending on the machine performance, it may take a few seconds or minutes.
After the above ups and downs, we can do it. If the access is still unavailable, restart the Web server (not the database server) to solve the problem.
If you do not have this directory for Oracle10g, You can authorize the entire installation directory of oracle.
6. Character Set Problems
If the database server is installed in a Chinese environment and the client is also running in a Chinese environment, you will not need to read this part. However, if the installation is complex, especially when the server needs to support multiple languages and multi-character sets, how the client works together becomes a problem.
To know how the client is configured, you need to know what standards the server is arranged based on. You can use the following SQL to query:
Select * From database_properties
Here, nls_characterset refers to the server-side character set. For example, if the result displayed on the server is zhs16gbk, the client needs to be configured according to this standard. For our developers, the client is generally a Windows platform. This configuration needs to be modified in the registry. The Registry path is: My computer \ HKEY_LOCAL_MACHINE \ SOFTWARE \ oracle \ home0 \ nls_lang
For example, the key value of my machine is simplified chinese_china.zhs16gbk (note that spaces are retained), indicating the Chinese Character Set of Simplified Chinese. From the character set perspective, the server and the client are currently matched. If the character set matches, the queried content will not be garbled; otherwise, you will see a lot of text that you don't want to see.
Generally, to achieve multi-language support, the character set is set to utf8, which can support multiple types of text. If the character set on the server and the client is inconsistent, a problem may occur, but the problem is mainly displayed, which does not affect the system much. For example, if a local language name is entered in Russia, it may not be displayed in the Chinese system.
How many character sets Does Oracle Support? I haven't counted them. If the zhs16gbk doesn't work, just try utf8. There is also an exception. Some earlier versions of Oracle only support the Western European character set. It is a single-byte character set, and the high byte is empty. This character set is incompatible with zhs16gbk. In this case, you must set the client to the Western European character set. Character sets have an inclusive relationship. As long as they conform to this inclusive relationship, there is no difference between the two ends. For example, zhs16gbk and utf8 are inclusive.
Well, I have said so much about my experience in my actual work. I hope it will be helpful to everyone and I hope everyone can become a real master.