The tradeoff between Oracle tuning performance and security

The tradeoff between performance and security

For database tuning, there is no absolute performance or absolute security. Just as the fish and the cake can not be the same, it is not fully balanced, like the spear and the shield to eliminate the other long. Here are a few more common factors to make a brief description:

1. Diversified control Documents:

Multiple places, which means more secure, one damaged can dump another one to continue to use. But again, the greater the IO pressure, the more often 2 to 3 control files are diversified. For example: Assuming that the 3 control files are very low probability of damage, and then a lot of control files will be meaningless. Because a control file is corrupted, the database immediately crashes, the checkpoint will generate an early warning message, so that the prompt can be made to intervene, as soon as possible to resolve it.

2. Diversity Log Group members:

The more remote log group members, the more it means that the greater the IO pressure, but for the log group, according to the actual production environment to set the appropriate number of log groups, the number of log group members, the size of the log group.

3, often produce checkpoints:

After a checkpoint occurs, if you want to do data recovery, it means that the data will be recovered from the checkpoint, which will mean that the checkpoint is much more and the recovery level is less. Even if the data is lost, the data is written to the data file when the checkpoint occurs. But frequent checkpoints can be large for the performance of IO. Because the dirty blocks are synchronized to disk when a checkpoint occurs, the purpose of designing the dirty block is to modify a dirty block many times, which can be done once when the Dirty block is written to disk. One thing to mention is that modifying the state of the data in memory is much faster than modifying the state of the data on the disk, imagine changing the state of the memory several times, and altering the state of the disk several times directly. To make an extreme assumption, the dirty block triggers a checkpoint every time a minor change is made, and the Dirty block is written to disk to ensure the security of the data, but this destroys the purpose of designing the buffer. Remember that data written to disk is much less efficient than writing memory data.

4. Data file backup:

Without a backup, data loss cannot be recovered. To know clearly, in doing the data backup, will generate a lot of reading and a lot of writing, the process requires a lot of Io, do more to the database has an impact. But the customer will have a maximum recovery time (arrival time, dump link, recover link, tested to meet customer requirements of the longest downtime), such as how long to recover a week of data, to have a backup strategy, before the backup to the customer to coordinate with the If you can, you need to sign some backup agreements to guarantee the obligations and responsibilities of both parties.

5. Open the archive:

The General production library will open the archive, excluding some of the allowed data can be lost in the field environment, the actual work is rarely open archive. Once this is the case, data can often be recovered from other sources. If you export data from a library, you can accept non-archiving. For example, you can not open the archive, such as a library for experimentation or analysis, where the data is imported by another library, this situation can be accepted without open archives.

Similarly, the more paths that are archived, the higher the security, and the greater the performance impact, which is a factor of mutual checks and balances.

6. Data block check:

Example:

Sql> Show Parameter Check

NAME TYPE VALUE

------------------------------------     -----------    ------------------------------

Db_block_checking string FALSE

Db_block_checksum string TRUE

Log_checkpoint_interval integer 0

Log_checkpoint_timeout Integer 1800

Log_checkpoints_to_alert Boolean FALSE

The view can be queried to the db_block_checking parameter:

FALSE: Indicates that when the data is written from the data buffer to disk, only the system table space is required to write down, it needs to be verified, the other table space is not verified;

TRUE: Indicates that all table spaces are validated when the dirty block is written.

The more the database checks, the more IO, but the more secure the data. It depends on the importance of the business.

7. Number of concurrent users and transactions:

This is a discussion of the loss of the system resources, can not single from the resource consumption to judge the system performance problems, because of the use of resources, for example, when the use of many users can accept a lot of user control, in the unit of time can complete a lot of business. In order to achieve this, the load must be large, the performance of a lot of consumption. This is a balance point, it is generally necessary to ensure that the system in a smooth state of operation, as much as possible to assume the user's access is good (that is, the more transaction volume, the better).

There are many factors of optimization, this is just a few points of a simple elaboration, in the blog will gradually through several aspects to expand the optimization. If you are interested, you can take a look.