Oracle Database optimization and backup

Oracle SQL Performance Optimization:

1. select an appropriate Oracle optimizer
There are three optimizer types in Oracle

A. Rule (Rule-based) B. Cost (cost-based) C. Choose (selectivity)

Set the default optimizer. You can declare the optimizer_mode parameter in the init. ora file, such as rule, cost, choose, all_rows, and first_rows. Of course, you also overwrite SQL statements or sessions.

To use the cost-based optimizer (CBO, cost-based Optimizer), you must run the analyze command frequently to increase the accuracy of object statistics in the database.

If the optimizer mode of the database is set to choose, the actual optimizer mode is related to whether the analyze command has been run. If the table has been analyze, the optimizer mode will automatically become CBO. Otherwise, the database will adopt the rule form optimizer.

By default, Oracle uses the choose optimizer. To avoid unnecessary full table scan, you must avoid using the choose optimizer, instead, we directly use a rule-based or cost-based optimizer.

2. Access Table
Oracle uses two methods to access table records:

A. Full table Scan

Full table scan accesses each record in the table sequentially. Oracle optimizes full table scanning by reading multiple data blocks at a time.

B. Access a table through rowid

You can use rowid-based access to improve the efficiency of accessing tables. rowid contains the physical location information recorded in the table. Oracle uses indexes to connect data with the physical location where data is stored (rowid. Generally, indexes provide a quick way to access rowid. Therefore, queries based on index columns can improve the performance.

3. Share SQL statements
To avoid repeated parsing of the same SQL statement, Oracle stores the SQL statement in memory after the first parsing. The memory in the shared buffer pool of the SGA (system global area) can be shared by all database users. Therefore, when you execute an SQL statement (sometimes called a cursor), if it is exactly the same as the previously executed statement, oracle can quickly obtain parsed statements and the best execution path. This function greatly improves SQL Execution performance and saves memory usage.

Unfortunately, Oracle only provides cache buffering for simple tables. This function is not applicable to multi-table join queries.

The database administrator must set the appropriate parameters for this region in init. ora. When the memory area is larger, more statements can be reserved. Of course, the possibility of sharing is higher.

When you submit an SQL statement to Oracle, Oracle will first find the same statement in the memory. It should be noted that Oracle adopts a strict match between the two. To achieve sharing, the SQL statements must be identical (including spaces and line breaks ).

The database administrator must set the appropriate parameters for this region in init. ora. When the memory area is larger, more statements can be reserved. Of course, the possibility of sharing is higher.

The shared statement must meet three conditions:

A. Character-level comparison: the statements currently executed must be identical to those in the sharing pool.

B. The objects referred to by the two statements must be identical:

C. bind variables must be used in two SQL statements ).

4. Select the most efficient table name sequence (only valid in the rule-based Optimizer)
The Oracle parser processes the table names in the from clause in the order from right to left. Therefore, the table written in the from clause (basic table driving table) is first processed. When the from clause contains multiple tables, You must select the table with the least number of records as the base table. When Oracle processes multiple tables, it uses sorting and merging to connect them. First, scan the first table (the last table in the from clause) and sort the records, and then scan the second table (the last second table in the from clause ), finally, all records retrieved from the second table are merged with the appropriate records in the first table.

If more than three tables are connected for query, You need to select an intersection table as the base table, which is the table referenced by other tables.

5. Connection sequence in the WHERE clause
Oracle uses the bottom-up sequence to parse the WHERE clause. According to this principle, the join between tables must be written before other where conditions. The conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.

6. Avoid '*' in the select clause '*'
When you want to list all columns in the select clause, using dynamic SQL column reference '*' is a convenient method. Unfortunately, this is a very inefficient method. In fact, Oracle converts '*' into all column names in sequence during parsing. This is done by querying the data dictionary, which means it takes more time.

7. Reduce the number of database accesses
When each SQL statement is executed, Oracle performs a lot of internal work: parsing SQL statements, estimating index utilization, binding variables, and reading data blocks. It can be seen that reducing the number of visits to the database can actually reduce the workload of oracle.

8. Use the decode function to reduce processing time
You can use the decode function to avoid repeated scan of the same record or join the same table.

9. Simple Integration and unrelated Database Access
If you have several simple database query statements, you can integrate them into a single query (even if there is no relationship between them)

10. Delete duplicate records

11. Replace Delete with truncate
When deleting records in a table, a rollback segment is usually used to store information that can be recovered. If you do not have a commit transaction, Oracle will recover the data to the State before the deletion (which is precisely the State before the deletion command is executed ).

When truncate is used, the rollback segment no longer stores any recoverable information. After the command is run, the data cannot be restored. Therefore, few resources are called and the execution time is short.

12. Try to use commit as much as possible
As long as possible, use commit as much as possible in the program, so that the program performance is improved, and the demand will be reduced because the resources released by commit

Resources released by commit:

A. Information used to restore data on the rollback segment.

B. The lock obtained by the Program Statement.

C. Space in the redo log buffer.

D. Oracle manages the internal costs of these three types of resources.

13. Calculate the number of records
In contrast, count (*) is slightly faster than count (1). Of course, if you can search by index, the index column count is still the fastest. For example, count (empno)

14. Replace having clause with WHERE clause
Avoid using the having clause. Having filters the result set only after all records are retrieved. This process requires sorting, total, and other operations. If the WHERE clause can be used to limit the number of records, this overhead can be reduced.

15. Reduce table queries
In SQL statements containing subqueries, pay special attention to reducing the number of queries to the table.

16. Improve SQL efficiency through internal functions.

17. Use the table alias (alias)
When connecting multiple tables in an SQL statement, use the table alias and prefix the alias on each column. In this way, the parsing time can be reduced and the syntax errors caused by column ambiguity can be reduced.

18. Replace in with exists
In many basic table-based queries, to meet one condition, you often need to join another table. In this case, using exists (or not exists) usually improves the query efficiency.

19. replace not in with not exists
In a subquery, the not in Clause executes an internal sorting and merging. In either case, not in is the most inefficient (because it executes a full table traversal for the table in the subquery ). To avoid using not in, we can rewrite it into an outer join (outer joins) or not exists.

20. Replace exists with table join
Generally, table join is more efficient than exists.

21. Replace distinct with exists
When you submit a query that contains one-to-many table information (such as the Department table and employee table), avoid using distinct in the select clause. In general, you can consider replacing it with exist.

 

Iv. Cold backup and hot backup, dual-machine hot backup and fault tolerance

Cold backup and Hot Backup

I. Cold backup

　　

Cold backup occurs when the database is shut down normally. When the database is shut down normally, it will provide us with a complete database. This article describes how to copy key files to another location during cold backup. The fastest and safest way to back up Oracle information during cold backup. Advantages of cold backup:

　　

1. It is a very fast backup method (just copy files)

2. Easy to archive (simple copy)

3. It is easy to restore to a certain point in time (you only need to copy the file back)

4. It can be combined with the archiving method to restore the database in the "best state.

5. Low maintenance and high security.

　　

However, cold backup also has the following shortcomings:

　　

1. When used separately, only recovery at a certain time point can be provided.

2. During the whole backup process, the database must be backed up instead of other work. That is to say, in the cold backup process, the database must be closed.

3. If the disk space is limited, it can only be copied to tape and other external storage devices, and the speed will be very slow.

4. data cannot be restored by table or by user.

　　

If possible (mainly for efficiency), back up the information to the disk, start the database (allowing the user to work), and copy the backup information to the tape, the database can also work ). Files that must be copied in cold backup include:

　　

1. All data files

2. All control files

3. All online redo log files

4. init. ora file (optional)

　　

It is worth noting that cold backup must be performed when the database is closed. When the database is turned on, the execution of database file system backup is invalid.

　　

The following is a complete example of cold backup.

　　

(1) shut down the database

Sqlplus/nolog

SQL> connect/As sysdba

SQL> shutdown normal;

　　

(2) Use the Copy command to back up all the time files, redo log files, control files, and initialize parameter files.

SQL> CP

　　

(3) restart the Oracle database

SQL> startup

　　

Ii. Hot Backup

　　

Hot Backup is used to back up the database in archivelog mode when the database is running. Therefore, if you have a cold backup file last night and a hot backup file today, you can use this data to restore more information when a problem occurs. Hot Backup requires that the database be operated in archivelog mode and requires a large amount of archive space. Once the database is running in the archivelog state, you can back up the database. The Hot Backup command file consists of three parts:

　　

1. Backup of one tablespace and one tablespace in a data file.

　　

(1) set the tablespace to the backup state.

(2) back up the data files in the tablespace

(3) restore the tablespace to normal state

　　

2. Back up archive log files

　　

(1) temporarily stop the archiving process

(2) files in the archive Rede log target directory under Log

(3) restart the archive Process

(4) back up the archived redo log file

　　

3. Use the alter database bachup controlfile command to back up and control files. The advantages of Hot Backup are:

　　

1. Data can be backed up at the tablespace or database file level. The backup time is short.

2. The database is still available during Backup.

3. It can be restored in seconds (to a specific point in time ).

4. Restore almost all database entities

5. Recovery is fast. In most cases, apsaradb recovers when it is still working.

　　

Hot Backup is insufficient:

　　

1. No error is allowed; otherwise, the consequences are serious.

2. If the Hot Backup fails, the result cannot be used for restoration at a time point.

3. Because it is difficult to maintain, you must be careful not to "end with failure ".

 

 

 

Dual-machine Hot Standby Implementation Mode

The dual-host hot storage provides two implementation modes: one is based on shared storage devices, and the other is based on non-shared storage devices, which are generally called pure software.

 

Storage-sharing-based hot standby is the most standard solution for hot standby.

 

In this way, two or more servers are used (for more information, see the similarities and differences between dual-host and cluster servers) to use shared storage devices (disk array cabinets or Storage Area Network SAN ). The two servers can adopt different methods, such as mutual backup, master-slave, and parallel. In the course of work, the two servers will provide external services with a virtual IP address, and send service requests to one of the servers according to their work methods. At the same time, the server uses a heartbeat line (usually using a private network) to detect the working status of another server. When one server fails, the other server makes a judgment based on heartbeat detection, switches over and takes over the service. For users, this process is fully automated and completed in a short time, so as not to affect the business. Because shared storage devices are used, the two servers use the same data, which is managed by the dual-host or cluster software.

 

For pure software, the data can be copied to another server in real time through the image software, so that the same data exists on each of the two servers, if one server fails, you can switch to another server in time.

 

For in-depth analysis of this method, see: in-depth analysis of Dual-machine Hot Standby solution in software-only mode

 

The software-only method also has another situation, that is, the server only provides application services and does not save data (for example, only some calculations are performed and used as application servers ). In this case, you do not need to use shared storage devices. Instead, you can directly use dual-host or cluster software. However, this situation has nothing to do with the image software, but it is a small change in the standard hot standby mode.

 

 

 

Working principle of dual-Machine Fault Tolerance

1. Dual-host Fault Tolerance

In terms of working principle, dual-host fault tolerance can be divided into two types: Shared disk array mode and extended image pure software mode. The common features of these two methods are the fault tolerance for the operating system, power supply, CPU, and host motherboard Based on the reliability of key data.

 

The dual-host shared disk array is a clever Calculation of Dual-host Fault Tolerance centered on the disk array. The memory cabinet is connected to two systems through the SCSI line and can be accessed by two systems. Key data is stored in the shared cabinet. during normal operation, the controller is on the primary system. When the primary system is faulty or the primary system is faulty, the system switches to the standby host. After the primary system is repaired, the primary and standby roles are exchanged, and the dual system enters the normal working mode.

 

Dual-host Extended mirror software is a software-only dual-host Fault Tolerance solution. The two systems are connected over Ethernet, and key data is mirrored between the two systems. During normal operation, the control is on the Master System, and the data is mirrored to the backup system in real time. When the primary system fails or the primary system detects a fault, the system control is switched to the standby host. Because Ethernet is used as the data link of the system, the primary system does not interfere with the work of the standby system, and automatically disconnects from and diagnoses and repairs faults in an isolated environment. After the primary system is repaired, control needs to be switched back to the primary system, and data needs to be recovered from the standby system to the primary system. This task is automatically completed in the background, and the application reads data from the standby system without interruption. After the data recovery is complete, the dual-host system enters the normal working mode.

 

The two fault-tolerant methods can ensure data reliability. If you run an application on both the master and slave hosts, you can back up each other.

 

2. Working principle of shared disk array Mode

 

The data of two (or more) servers that use the shared disk array is stored in the same disk array at the same time. Therefore, data replication is not required, you only need to transfer the work of one server to another when it is down, which is relatively simple. Because the data is stored in the same disk array, first, if the data donation of the disk array is bad, all the data is lost, which may cause a single point of failure, in addition, the distance between the server and the disk array cabinet is limited because SCSI connections are usually used.

 

The shared disk array train cabinet mode is generally composed of a monitoring system and a switching system.

(1) Monitoring System

A. scsi detection. The shared disk array mode contains a communication line for detecting heartbeat. The detection result is placed in a 5 MB cell on the shared disk array for monitoring. This cell is generally located in the initial segment of the Cabinet Logical Disk, for a server, the detection information is written in the same way as the record in the Community. Each record contains the following content.

Monitoring status information of the system to the server

On the other server, the status information of the current server is displayed and the record area content is modified.

B. network detection. When a server encounters a problem or a fault, the heartbeat frequency of the Peer server is continuously increased. If the record content is not updated within the minimum heartbeat time, the system status is confirmed twice by network heartbeat detection. When system faults are determined at both the core of the canyon, the shared disk array switches the transaction business of the faulty server to the peer server within the minimum secure switching time to continue running.

C. Switch the system

Network server. The dual-server backend provides a logical IP address, for example, 192.192.192.1, by using the monitoring software to share the disk array in the case of a user's one-hour license. This address can be directly used by any user to access the Internet, when one of the backend servers fails, the other server will replace the IP address of its NIC with 192.192.192.1. In this way, the network at the user end will not be broken due to a server failure.

Database Service. When one of the servers fails, the other server automatically takes over the database and starts the database and applications at the same time, so that the user's database can continue to operate without affecting the user.

Application System. When one server fails, the other server automatically takes over all types of applications and starts the applications so that you can continue the operation without affecting the user.

 

3. Working Principle of software-only image scaling

A software-only software does not need to share a disk array. It stores data in its own servers and copies the data in real time using the mirror engine. When one of the servers is down, the configured server takes over the work of the server. Because data is stored on different servers, it avoids the possibility of single point of failure and increases data security. Servers are connected over the network, so the connections between servers are limited by the distance. Because the data is stored in the hard disks of each server, there is no impact on applications between servers, which improves the efficiency of normal use of servers.

4. Hot Backup

Hot Backup is actually a concept of computer Fault Tolerance Technology, and is the main way to achieve high availability of computer systems. Hot Backup uses the disk mirror technology to store running computer system data and application data on different hard disks at the same time, and refresh the data on different disks when the image changes, this ensures data consistency. When a hard disk in the system fails, the computer can use the image data to avoid the failure of the entire computer system due to a single point of failure (such as a hard disk failure), so as to achieve high availability of the computer system.

Current computer systems generally adopt hot backup mode during system construction. The most typical implementation method is dual-machine hot backup, that is, dual-Machine Fault-tolerant system. During the construction of the dual-Machine Fault-tolerant system, two identical servers are used to run the same operating system and application software (such as database software). The two servers share a disk array and use disk images, the application data is built on the disk array to implement dual-host fault tolerance. One server is designated as a working machine for processing the currently running business, and the other is a backup server. Once a work machine fails, the running business requests are manually (or automatically) switched to the backup server, so that the running business will not be interrupted due to a single point of failure of the system, achieving high system availability.

Hot Backup ensures the high availability of the computer system and ensures some businesses with strong real-time requirements (such as banking and credit card businesses. However, the hot backup method does not solve the problem of data management in all computer systems. For example, if an operator accidentally deletes a file, the hot backup system ensures data consistency, the image file of this file will be deleted at the same time, resulting in data loss. To prevent the damage or loss of useful data due to system faults or human error, data storage management is essential. The core of data storage management is data backup.

 

 

Application of Oracle databases in dual-Machine Fault-tolerant environments

At present, many enterprises that have established and applied information systems are paying attention to improving the availability and reliability of their information systems while continuously improving their system applications. The dual-Machine Fault Tolerance System is widely used by enterprises to ensure high system availability.

The special nature of the work of medical institutions requires that their information systems operate continuously for 7 days × 24 hours. The dual-host Fault Tolerance solution is used to provide a high-availability solution for the system. This article will detail Oracle database applications in the dual-Machine Fault-tolerant environment of the medical information system.

　　

System Configuration

　　

The hardware configuration of the system is as follows:

　　

Master database server: Fujitsu primergy MS-610 server (dual Xeon 700 MHz CPU, 1 GB memory ).

Database Backup Server: Fujitsu team server c870ie gp5c875 (Dual Pentium III 700 MHz CPU, 1 GB memory ).

Fault Tolerant Software: The slha 3.0 software package of Tiandi Corporation.

Disk Array: iqstore r1500 (with two SCSI interfaces ).

Line connection: two servers are connected with a RS-232 serial line and a RJ-45 network line (1 ).

　　

　　

The software configuration is as follows.

Operating System: Windows NT Server 4.0

Server Software Configuration: Windows NT 4.0 Service Pack 5, Internet Explorer 5.0, Microsoft Data Access Component 2.0, and Oracle Database 7.3.3 Enterprise Edition.

　　

Dual-Machine Fault Tolerance Implementation

　　

1. Operating System Installation

　　

We use machine A to represent the database server, and machine B to represent the backup database server. First, physically Connect the required hardware devices and install the Windows NT Server 4.0 operating system and patch pack on their respective servers. Then, go to the Disk Manager and divide the disk array into two logical disks D and E. At this time, both servers can access the disk array.

　　

2. Oracle Database Installation

　　

First, shut down machine B and install the Oracle database on machine A. The installation path is disk D by default, and archive logs are placed on disk E. After the installation is complete, change the Startup Mode of Oracle's three services (the SID is orcl here, so the three services are oracleserviceorcl, oraclestartorcl, and oracletnslistener) to manual and stop the three services. Note: The purpose of manual modification is to enable the three services to be started by the dual-host Fault-Tolerant Software, rather than the operating system.

　　

Then, shut down host a, start host B, format the disk D, and format the Oracle directory created on the disk array by host a. Install the Oracle database on host B, the default installation path is disk D. After installation is complete, you can also change the Startup Mode of the three Oracle services to manual and stop the three services.

　　

Oracle is installed on two servers, and data is stored only on the disk array.

　　

3. Installation of Dual-Machine Fault-Tolerant Software and establishment of Dual-Machine Fault-tolerant environment

　　

The installation of Dual-Machine Fault-Tolerant Software is very simple. You only need to start machine A and machine B and install the software on the two servers respectively. The dual-Machine Fault Tolerance environment is used to control the D, E, and Oracle services on the disk array by the dual-Machine Fault Tolerance software, and switch between the dual-Machine Fault Tolerance software.

　　

Set the database server to active in the "configuration" option of the dual-Machine Fault-Tolerant Software slha, that is, when the database server works normally, the backup server waits. When host a is active, only host a can access the disk array, and host B cannot access the disk array. In this case, the Oracle database server is actually machine A, the IP address of machine A is active IP address, and the host name of machine A is active host name. When machine A cannot work for any reason, the status of machine A is detected by the heartbeat line. At this time, machine B switches to the active status and takes over the disk array. At this time, the Oracle database server is changed to machine B, and the IP address of machine B is the active IP address, at the same time, the host name of machine B is active host name. The above operations are automatically completed by the system. Practice has proved that switching takes a short time and has little impact on the client.

　　

Notes

　　

1. after installing the Oracle database on machine A and then installing the Oracle database on machine B, you must format the disk array D disk instead of deleting the Oracle database on disk D that has been installed on machine, otherwise, unexpected errors may occur, such as Oracle listening service failure;

　　

2. after the Oracle database is installed, You Need To D:/Oracle/orant/Network/admin/listener. the ora file must be modified. The server name must be changed to active host name alias. If this parameter is not modified, the Oracle database user on the client cannot connect to the Oracle database.

　　

3. Add a record to the hosts file so that the active IP address and active host name correspond to each other, so that the system automatically performs resolution. The hosts file is located in the C:/winnt/system32/Drivers/etc directory.

　　

4. Do not force switchover to avoid data errors. If you need to switch between the two servers, you can first go to the svrmgr Oracle server console, use the shutdown command to close the Oracle database, and then switch.

Principle of Dual-Machine Fault Tolerance

　　

The Oracle database is installed on the disk array (Public drives in figure 2) and can be accessed by both servers, but cannot be accessed at the same time. Oracle server for NT provides three services: oracleservicesid, oraclestartsid, and oracletnslistener. When the database server works normally, the database server controls the disk array. At this time, only the server can access the disk array, and the Oracle service on the server is in the active status, in this case, the server assumes the role of active server in Figure 2, and the backup server is in the standby status, that is, backup server in Figure 2.

　　

When the database server fails and cannot work, the dual-host fault tolerance system detects the status of the database server, so that the backup server is automatically activated, takes over the disk array and automatically starts the three services of oracle, for the client, only a brief server restart process occurs, and the accessed data is still the data in the disk array.

　　

Note: It is a dual-host Fault Tolerance software, rather than an operating system, that is, the dual-host Fault Tolerance software, to control the start and stop of the three Oracle services, enables Oracle Database switching between two servers