Which factors can affect the performance of Oracle databases?

Oracle is being studied. There are many reasons for the impact on Oracle performance, which can be divided into four aspects:

1. hardware environment

CPU, memory, and network transmission conditions all affect Oracle performance.

The hardware example will not be explained here.

2. The configuration parameters during database running will also affect the performance of oracle.

The following describes the impact of configuration parameters in an example.

Currently, our project is used by a department of a company. After a period of normal operation, the operation is particularly slow (the current server has two CPUs ), by running CPU and memory in Linux, we find that 100% of CPU usage occurs during a period of time, while the utilization of another CPU is low, by balancing the load of the two CPUs, the program running speed returns to normal.

3. unreasonable table structure design will also affect Oracle Performance

The impact of unreasonable table structures on performance also occurs in another project. Currently, there is a table that stores the user's mobile phone number, during statistics, the user's mobile phone number needs to be classified for statistics, while the other table stores the region names that match the first four or the first seven digits of the mobile phone number, at this time, the programmer wrote an SQL statement: Select * From userphone, userarea where (left (userphone. phone, 4) = userarea. ID or left (userphone, 7) = userarea. ID); this statement does not affect the performance when there is a small amount of data in the userphone table in the early stage. However, when userphone reaches 10 thousand, the performance decreases rapidly. After analysis, it is determined that the table structure is not reasonable, so a userarea ID column is added to userphone, and the SQL statement is changed to select * From userphone, userarea where userphone. areaid = userarea. ID), the program running performance returns to normal.

4. For programmers, writing unreasonable SQL statements will also affect Oracle performance.

(1) If the programmer creates a database update transaction and does not commit the transaction, the system will be locked, which will seriously affect the system performance.

(2) In article 3rd, the programmer writes a statement similar to select * From userphone, userarea where (left (userphone. phone, 4) = userarea. ID or left (userphone, 7) = userarea. the statement is not familiar with the SQL running mechanism, but does not analyze the number of data records to be processed by the statement, so no unreasonable table structure is found, this causes a serious reduction in performance.