Oracle R & D skills

Author: skate
Time: 2013/03/04

 

Record the summary of Oracle R & D skills. If you have any suggestions, please leave a message to add

Oracle R & D skills

1. Basic Oracle skills
1.1 SQL High Performance Optimization
1.2 table design skills
2. Advanced Oracle skills
Design skills of 2.1 sea Scale
2.2 design architecture from the DBA perspective

1. Basic Oracle skills
1.1 SQL High Performance Optimization
Optimized Data Reading involves a wide range of physical I/O, storage, hardware, and network environments. It also involves logical DBMS environment settings and SQL types, the execution plan, the index type, and the order of the combined index columns are discussed from the programmer's perspective. The database environment is the SQL site, how to efficiently use the DB environment to run efficient SQL statements

• Reduce the resource consumption of a single SQL statement
• Reduce SQL Execution times

Reduce the resource consumption of a single SQL statement
To reduce SQL resource consumption, we need to know how SQL runs in the Oracle environment. What is the execution process?

1. Create a cursor
2. analysis Statement: Performs syntax analysis, checks SQL writing, validation definitions, and permissions, and selects the best execution plan and loads it into the SQL sharing zone. During the analysis statement, Oracle binds variables to implement shared SQL,

To reduce SQL parsing. Therefore, binding variables is an optimization point.
3. Describe the features of the query result set that determine the query result, such as data type, field name, and length.
4. Define query output data to specify the location, size, and data type of the receiving variable corresponding to the queried column value. If necessary, Oracle converts the data type by default.
5. bind variables
6. You can execute parallel operations on creating indexes, creating tables using subqueries, and partition tables by running parallel statements. By consuming more resources, you can quickly execute SQL statements.
7. Run the SQL statement to prepare for execution and start running the SQL statement. This process can be optimized by batch processing.
8. Fetch the queried rows and return the query result set, which is optimized by batch processing.
9. Close the cursor

From the preceding analysis, we can see that we can reduce the consumption of a single SQL resource by using the following methods:

• Bind variables to implement shared SQL
• Optimal Execution Plan Selection
  The optimal Execution Plan determines the table connection mode and data access path. Because data reading methods are classified into continuous and random scanning methods, random scanning greatly affects data reading performance based on the physical characteristics of the disk. We optimize SQL, that is to say, random scanning is switched to continuous range scanning. Partial range scanning means that in continuous range scanning, Oracle can intelligently read only part of the data, rather than all the data. Therefore, regardless of the required data range, the execution speed can be ensured quickly.

Reduce sorting operations and try to replace sorting with indexes
Create a suitable composite index to limit the size of the query result set as much as possible

Reduce SQL Execution times
Reduce SQL analysis by binding variables, and reduce SQL Execution times by optimizing business logic

1.2 table design skills

In the DB system we designed, we need to know what tables are in our system and what are the basic optimization ideas. Here we classify the tables as follows:

1). tables with a small amount of data
2). Large and medium-sized tables for Reference
3). Large and medium-sized tables for managing business behaviors
4). Large tables for storage

1). tables with a small amount of data
Definition: An IO can read the entire table into the memory, which means that the number of blocks in the storage table is smaller than db_file_multiblock_read_count.
Tables are generally dictionary tables and rarely updated. Therefore, you can use Iot tables, data cluster tables (Parent and Child tables), or heap tables.
Features: it is usually placed in an internal loop of a nested loop and needs to be executed multiple times.
Optimization Methods: pctfree, cache, index, and separate database shard

2). Large and medium-sized tables for Reference
Definition: it is mainly used to store data of objects such as business behaviors, subjects, and purposes, such as user information tables.
Features: the stored data is large, mainly in the form of random reading and small-scale data scanning. Generally, the data is read by the primary key or table connection and placed in an internal loop.
Medium; few data are inserted, dominated by select; this table needs to be optimized, and a large number of indexes are usually created
Optimization Method: Create appropriate indexes, partitions, and clustering

3). Large and medium-sized tables for managing business behaviors
Definition: stores transaction activity data of a business. The number of transactions increases over time.
Feature: because the analysis dimensions are very rich, there are a variety of reading types, the data volume is very large, and the increase is very fast, usually in the cycle
Sometimes the data range cannot be reduced through a specific column, so composite indexes are often used.
Optimization Method: Create appropriate indexes, partitions, clustering, and data layers

4). Large tables for storage
Definition: used to store and manage Log Data
Features: The data volume is huge and increases constantly, resulting in a high insertion cost.
Optimization Method: pctfree, partition, separate database shard

 

2. Advanced Oracle skills
Design skills of 2.1 sea Scale
This "massive" has two meanings: a large amount of data and a high execution frequency. For An OLTP system like ours, in fact, each SQL is very little interested in data, as long as we can make every SQL process the data we are interested in and store fresh data for each table, this requires table design and SQL writing. For example, there may be a lot of data that interest SQL in our trader background. If you can, you can consider database sharding to avoid the impact of such individual businesses on the overall system stability.

The massive data volume can be solved in the following aspects:
Partition Table: the advantages of Partition Table splitting are transparent to applications.
Multi-dimensional Splitting: You can split a large table into multiple tables from the time dimension and functional dimension. The corresponding routing rules apply.
After the table is created, a single server still cannot meet the requirements, so we should consider database shard storage.
Intermediate table: Multi-Table association is required for tables with large data volumes. If business permits, you can create intermediate tables and provide results directly.

The high execution frequency is solved in the following aspects:
Data Cache: caches table data at the cache layer and persists to reduce read/write operations on the database.
Simplify/optimize SQL: reduce the resource consumption of a single SQL statement and reduce the SQL response time.
Database sharding: uses database sharding to share the pressure

2.2 design architecture from the DBA perspective
DB is the most prone to bottlenecks in the system. At the beginning of the design, it is much lower to consider the solution than to solve the DB bottleneck, sometimes, because the database bottleneck cannot be solved, we have to re-develop all the systems. DBA summed up its experience in practice. In order to avoid dB bottlenecks, it proposes to supplement the system architecture design from the DBA perspective. When we face huge traffic and data volumes, our system should be simplified and simplified, small.

System design scale
Specify the business scale supported by the system, the capacity supported by the system, and whether the system needs to be expanded.

The most valuable resources in the system are: CPU, memory, Io, and network. I/O is the most important thing. It is a short board and it is easy to appear in bottles.
Refer to the following formula to design a system based on its reference, for example, to design an OLTP system.
PV/(24*3600) * DPV * lvdpv * x * (1-hit) * rwrate = r_iops
PV/(24*3600) * DPV * lvdpv * x * (1-hit) * rwrate = w_iops

Note:
PV: PV/day
DPV: dynamic PV rate, the proportion of dynamic PV in daily PV
Lvdpv: Logical read generated by each dynamic PV
Hit: cache hit rate
Rwdate: system read/write Ratio

For example, we have designed a system that supports 10 million PV and how many I/O is required, and how many disks are required for these I/O operations; how to plan the data storage capacity?

Common hard disk iops reference values:
10.000 "113 rpm sas iops
15.000 "156 rpm sas iops
15.000 "146 rpm sas iops
2,5 "5.400 rpm sata 71 iops
7.200 "rpm SATA 65 iops
15.000 "150 rpm FC iops
Monitoring
Efficient and accurate monitoring of performance and faults can avoid most problems in advance
Plan
Prepare contingency plans for handling emergencies to avoid confusion and misoperations
Service downgrade Protection
A sudden failure in a module prevents cascade impact or ensures normal core services. This can be achieved by reducing module coupling or setting the module start/stop switch.
Prevent data push avalanche
When designing the DB architecture, we should consider the methods used to prevent performance jitter and even access avalanche caused by data push. For example, the front-end high performance
Ability to access the database by KV library and app.
DB load shifting Design
When the traffic volume suddenly increases, the abnormal peak value will have a great impact on the system performance, and even drag the system down. we can avoid this by designing the peak shifting design.
Problems, so that the system can process requests in the optimal performance. For example, the maximum number of requests allowed by the queue design is equal
Database session controls the maximum amount of concurrent processing in the database, so that the database works at the best performance
Efficient use of nosql Databases
Nosql databases are highly effective in dealing with KV environments. to efficiently use nosql databases and reduce access to RDBMS storage databases, you can
Uses RDBMS to actively push changed data to the nosql database. The application master reads nosql and times out to read nosql back-end storage RDBMS.

 

 

 

--- End -----