Shulou(Shulou.com)06/01 Report--

1 adjust the design: best practices

1.1 do as little work as possible

The process involved in each step of the application should be simplified. Emphasize the functionality of the application and the ability to meet business performance requirements.

1. Strive to eliminate logical reading in application design

If multiple users need to read the same data, it should be stored in a table or in a package variable.

Modify the application design to eliminate unnecessary command execution.

The extremely high hit rate of the buffer can be identified as performing too many logical reads.

It is best to use wait-based adjustments in 11g and 12c.

2. strive to avoid round-trip access to the database in application design

The back-and-forth communication between the database server, the application server, and the terminal computer should be adjusted.

Store frequently needed values in local variables to avoid repeatedly querying the database.

3. For the report system, the data is stored according to the query mode of the user.

Create maintenance materialized views or report tables to avoid data format conversion for users.

4. Avoid repeated connections to the database

Keep the database connection open and reuse

5. Use the index correctly

Bitmap indexes should not be used in OLTP applications

Do not index a column if it has very few different values

1.2 do the work as simple as possible

1. Execution at the atomic level

For performing batch operations, temporary tables should be created to store data for each step

Decomposing the operation into smaller components can solve the performance problem

2. Eliminate unnecessary sorting

SORT ORDER BY is that no data is returned until the collection operation is completed.

UNION also implements the sorting recommendation to replace it with UNION ALL.

3. Eliminate the need to use undo operations

Application design should avoid frequently querying data that others may change at the same time.

1.3 tell the database what you need to know

1. Keep up to date with statistics

By default, ASG collects statistics from 10:00 to 6 p.m. (maintenance window) and throughout the day on weekends

When the bulk loaded table size increases by more than 10%, it can be collected manually.

12c allows statistics to be collected in parallel in a pattern table or in a partition of a table

Use of session level or system and parameters before manual collection to optimize performance

2. Use tips where needed

Selecting the most efficient query execution path based on cost Optimizer (CBO)

1.4 maximize throughput in the environment

1. Use the appropriate database block size 8KB

2. Design throughput rather than disk capacity

3. Avoid using temporary periods

Perform all sorts in memory as much as possible

1.5 deal with data separately

1. Use zoning

2. Use materialized views

3. Use parallelization

1.6 correct testing

1. Use a large amount of data to test

Contention may occur when splitting leaf blocks and maintaining indexes

2. Use many concurrent user tests

Confirm whether concurrent users are causing deadlocks, data consistency, and performance problems

3. The influence of test index on loading times.

Adding an index results in the overhead of DML operations

4. All tests can be repeated

Must be able to reconstruct the dataset used, actions performed, expected results, and recorded results

The common reasons for not following testing standards are time and cost.

1.7 deliverables of the standard

1. Entity relationship diagram

Indicates the identity relationship between the entities that make up the application

2. Physical database diagram

Physical tables generated from entities and columns generated by their attributes

3. Initial value of space demand

4. Adjustment goals for query and transaction processing

The response time for each operation that is most used in the application must be defined and approved.

Establish reasonable goals and stretch goals

Establish control boundaries for query and transaction performance according to different objectives

5. Security requirements

Specify the account structure and related authorization used by the application

6. Data requirements

Define data entry and retrieval methods

Describe the backup and recovery requirements of the application and compare it with the enterprise database backup plan

7. Query execution plan

The steps that need to be completed for the database to execute the query

8. Acceptance testing process

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.