Optimize data batch processing performance and batch processing performance

Optimize data batch processing performance and batch processing performance

2014-10-04 BaoXinjian

I. Summary

PLSQL _ performance optimization series 11_Oracle Bulk Collect

There is an overhead for running SQL statements in PL/SQL programs, because SQL statements must be submitted to the SQL engine for processing.

The transfer of control between PL/SQL engine and SQL engine is called context change, but each change has additional overhead.

However, FORALL and bulk collect allow the PL/SQL engine to compress multiple contexts into one, this causes the time required to execute the SQL statement to process multiple rows of records in PL/SQL to plummet.

 

1. How to Use FORALL and bulk collect:

(1). Using FORALL is more efficient than FOR, because the former only switches the context once, while the latter switches between multiple contexts with the same number of loops.

(2). Using bluk collect to retrieve a data set at a time is more efficient than using a cursor entry to retrieve data, especially when the network is not good. However, bluk collect requires a large amount of memory.

2. When batch processing is not used, the system needs to switch the context frequently.

3. After batch processing, the system only needs to switch the context once.

 

Ii. Batch Processing-Bulk Collect

Bulk collect can be used to load the query results to Collections at a time, instead of processing the results one by one Using Cursor.

You can use bulk collect in Select Into, Fetch Into, and Returning Into statements.

Note that when bulk collect is used, all INTO variables must be Collections.

 

1. Use Bulk Collect in Select

Declare type table_forall is table of test_forall % ROWTYPE; v_table table_forall; begin select mub. user_id, mub. user_name bulk collect into v_table FROM mag_user_basic mub WHERE mub. user_id BETWEEN 10000 AND 10100; FORALL idx IN 1 .. v_table.COUNT insert into test_forall VALUES v_table (idx); -- VALUES (v_table (idx ). user_id, v_table (idx ). user_name); Error -- In PL/SQL, BULK In-BIND and RECORD, % ROWTYPE cannot be used together. -- that is to say, BULK In-BIND can only use COMMIT with a simple array; exception when others then rollback; END;

 

2. Use Bulk Collect in Fetch

Declare type table_forall is table of test_forall % ROWTYPE; v_table table_forall; CURSOR c1 is select mub. user_id, mub. user_name FROM mag_user_basic mub WHERE mub. user_id BETWEEN 10000 AND 10100; begin open c1; -- use bulk collect fetch c1 bulk collect into v_table in fetch into; FORALL idx IN 1 .. v_table.COUNT insert into test_forall VALUES v_table (idx); COMMIT; exception when others then rollback; END;

 

3. Use Bulk Collect in Returning

DECLARE   TYPE IdList IS TABLE OF test_forall.User_Id%TYPE;   enums   IdList;   TYPE NameList IS TABLE OF test_forall.user_name%TYPE;   names   NameList;BEGIN   DELETE FROM   test_forall2         WHERE   user_id = 10100     RETURNING   user_id, user_name BULK COLLECT INTO   enums, names;   DBMS_OUTPUT.put_line ('Deleted ' || SQL%ROWCOUNT || ' rows:');   FOR i IN enums.FIRST .. enums.LAST   LOOP      DBMS_OUTPUT.put_line ('User #' || enums (i) || ': ' || names (i));   END LOOP;   COMMIT;EXCEPTION   WHEN OTHERS   THEN      ROLLBACK;END;

 

Iii. Batch Processing-ForAll

FORALL indicates that the PL/SQL engine binds all the members of one or more sets to SQL statements, and then sends the statements to the SQL engine.

 

1. Change For to Forall in batch update

(1). Use the For statement For Update

DECLARE    TYPE NumList IS VARRAY(20) OF NUMBER;    depts NumList := NumList(10, 30, 70, ...);    -- department numbersBEGIN  ...  FOR i IN depts.FIRST..depts.LAST  LOOP    ...    --UPDATE statement is sent to the SQL engine    -- with each iteration of the FOR loop!    UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);  END LOOP:END;

(2). Use the batch processing of the Forall statement for Update

--UPDATE statement is sent to the SQL engine just once, with the entire nested tableFORALL i IN depts.FIRST..depts.LASTUPDATE   emp   SET   sal = sal * 1.10 WHERE   deptno = depts (i);

 

 

Reference: http://www.jb51.net/article/35424.htm

Reference: http://log-cd.iteye.com/blog/411122

 

Maximum number of statements executed by database Batch Processing

When the database processes too many statements at the same time, the database may crash. Therefore, the contents of the database are generally backed up, however, the database can be optimized to improve performance. Mysql database I use can be optimized in mysql database into four parts. I. When installing the database, various software can improve the performance. I. can be changed in the database configuration file, 3. When designing a table, you can optimize the table. For example, a database table should have no more than-sheets, convert strings to numeric types, indexes, and other optimizations as much as possible. 4. Use query statements, when adding statements, you can optimize and improve performance.

What is the concept of database optimization?

Let's briefly talk about software optimization:
SQL: index adjustment: Preconditions, large data volume, frequent read-only queries
Optimization of SQL language, such as query, too many connected tables will affect performance
The rational database design means to design the database according to the database standardization
Oracle internal parameter adjustment:
Environment Parameter Adjustment of the init. ora File
Db_block_size: the size of each data block in the database. The default value is 2048 bytes (2 k). Generally, it should be increased to 4 K and 8 K. Large Databases often use 16 K and 32 k, generally, SGA should also increase.
Shared_pool_size: whether shared_pool_size is suitable can be obtained through monitoring the database or using some SQL statements.
Log_buffer: to reduce the conflict between LGWR and DBWR, The log_buffer of large databases is generally increased manually, generally from 2 M to 3 M.
All environment parameters can be analyzed through system monitoring tools.
In addition:
Modify the size of database Blocks
Application Optimization
Batch Processing Application Optimization
Single SQL statement Optimization
Summary:
Good Database Design

Good Architecture Design

Good platform (hardware platform and network platform)

Reasonable environment parameters (operating system environment parameters and oracle environment parameters)

Reasonable and adequate Indexes

Good dba for continuous optimization (Management)

Oracle: massive databases
SQL server: databases for Small and Medium-sized Enterprises