Key Points of Oracle performance adjustment: Database Configuration and I/O Problems

Reduce disk I/O

IO of distributed Disks

Tablespace use local management

1. Distribute files to different devices

A.Separate data files from log files

B.Reduce disk IO irrelevant to the server

C.Evaluate the usage of bare Devices

D.Split table data

2. Use of tablespaces

The system tablespace is retained to the data dictionary object.

Create a local tablespace to avoid space management problems

Distribute tables and indexes to independent tablespaces.

Use independent tablespace rollback

Store large database objects in independent tablespace

Create one or more independent Temporary tablespace

The following database objects should have separate tablespaces: Data Dictionary, rollback segment, index, temporary segment, table, and large object

3. Check IO statistics

Select phyrds, phywrts, d. name from v $ datafile d, v $ filestat f where f. file # = d. file # order by d. name;

Check the files that are most likely to cause disk I/O bottlenecks.

4. Split files

It can be performed through RAID or manual

Alter table table_name allocate extent (datafile 'fiile _ name' size 10 M );

However, manual operations are heavy.

5. Optimize full table scan operations

A.Check how many full tables occur:

Select name, value from v $ sysstat where name like '% table scan % ';

Table scans (short tables)/table scans (long tables) are related to full table scanning. If the value of table scans (long tables) is very high, it means that most table access operations are not indexed, check the application or create an index to ensure that the valid index is in the correct position.

Reasonable DB_FILE_MULTIBLOCK_READ_COUNT can reduce the number of I/O calls required by table scan and improve the performance related to OS ).

B.View the full table scan operation:

Select sid, serial #, opname, target, to_char (start_time, 'hh24: MI: ss') "start", (sofar/totalwork) * 100 "percent_complete" from v $ session_longops;

Associate SQL _hash_value in v $ session_longops with v $ sqltext to query the SQL statements that cause full table scan.

6. Checkpoint

Checkpoint operations: DBWn IO operations; CKPT updates the data file header and control file.

Results of frequent Checkpoint operations: Reduces the time required for recovery and reduces the performance of the system during running.

LGWR writes logs to each log group cyclically. When a log group is full, the oracle server must perform a Checkpoint, which means: DBWn writes all or part of the dirty data blocks covered by the corresponding log into the data file; CKPT updates the data file header and control file. If DBWn does not complete the operation and LGWR needs the same file, LGWR can only wait.

In the OLTP environment, if the SGA is large and the number of checkpoints is small, the disk competition may occur during the checkpoint process. In this case, frequent Checkpoint can reduce the number of dirty data blocks involved in each Checkpoint.

How to adjust the number of checkpoints:

◆ Increase the log file;

◆ Add a log Group to increase the overwrite interval.

7. Log Files

Create appropriate log files to minimize competition;

Provide sufficient Log File groups to eliminate waiting;

Store log files on independent and fast-accessible storage devices. log files can be created on bare devices ). Log files are organized and managed in groups. The contents of log files in each group are identical.

8. archiving log files

If you select archive mode, you must have two or more log groups. When you switch from one group to another, DBWn performs the Checkpoint operation; one log file is archived.

Sometimes an error occurs during Archiving:

ARC0：Beginning to archive log# 4 seq# 2772 Current log# 3 seq# 2773…… ARC0: Failed to archive log# 4 seq# 2772 ARCH: Completed to archiving log#4 seq# 2772

We recommend that you modify the init parameter as follows:

log_archive_max_processes=2 #log_archive_dest = ‘/u05/prodarch’ log_archive_dest_1 = “location=/u05/prodarch MANDATORY’ log_archive_dest_state_1 = enable log_archive_dest_2 = “location=/u05/prodarch2 OPTIONAL reopen=10″ 或其它目录） log_archive_dest_state_2 = enable log_archive_min_succeed_dest=1 log_archive_dest_state_3 = DEFER log_archive_dest_state_4 = DEFER log_archive_dest_state_5 = DEFER

1. Key Points of Oracle performance adjustment: SGA
2. Use script files to manage Oracle databases
3. Guidelines for writing effective transactions in Oracle databases