Increase the speed at which indexes are created in Oracle's massive data

Basic information Situation:

DB version: Oracle database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

Operating system version: CentOS release 5.6

Speed up index creation the main consideration is from the point of view:

• Using the nologging parameter
• Using parallel parallel parameters
• Use the manual PGA at the session level to manually adjust the Sort_area_size
• Modify other parameters

Note : We do not manually adjust hash_area_size,hash_area_size here by default automatically adjusts to sort_area_size*2, resulting in sort_area_size not exceeding 1G. So here we directly adjust the sort_area_size parameter.

This is where the PGA usage is recorded first, and the V$pgastat view can be queried for the PGA assignment and use after 9i.

[SQL]View Plaincopy

2. sql> desc V$pgastat;
4. Name
6. --------------------------------
8. Name names
10. Value values
12. Unit units
14. -------------------Statistical items
16. SELECT * FROM v$pgastat
18. NAME VALUE UNIT
20. ---------------------------------------- ---------- ----------
22. Aggregate PGA Target parameter 150994944 bytes
24. Aggregate PGA Auto target 93579264 bytes
26. Global Memory bound 30198784 bytes
28. Total PGA inuse 47017984 bytes
30. Total PGA Allocated 56666112 bytes
32. Maximum PGA allocated 58632192 bytes
34. Total freeable PGA Memory 2883584 bytes
36. Process Count
38. Max Processes Count
40. PGA memory freed back to OS 5177344 bytes
42. Total PGA used for auto workareas 0 bytes
44. Maximum PGA used for auto workareas 0 bytes
46. Total PGA used for manual workareas 0 bytes
48. Maximum PGA used for manual workareas 0 bytes
50. Over allocation count 0
52. Bytes processed 6438912 bytes
54. Extra bytes Read/written 0 bytes
56. Cache hit Percentage percent
58. Recompute count (total) 123

For the above explanations are as follows

1 aggregate PGA target parameter 150994944 bytes:pga_aggregate_target
2 aggregate PGA Auto target 93579264 bytes: The remaining memory that can be used by the workspace.
3 Global Memory bound 30198784 bytes: maximum RAM for single SQL
4 Total PGA inuse 47017984 bytes: The PGA that is being consumed (including all occupied PGA, Workare PL/SQL, etc.)
5 Total PGA Allocated 56666112 bytes: The amount of PGA memory allocated by the current instance.
In general, this value should be less than pga_aggregate_target,
But if the process requires a fast growth of the PGA, it can be in excess of the pga_aggregate_target limit value
6 maximum PGA allocated 58632192 bytes:p GA ever expanded to the maximum value
7 Total freeable PGA memory 2883584 bytes: Free PGA
8 Process Count 23: Current Process
9 Max Processes Count 48: The maximum time process
PGA memory freed back to OS 5177344 bytes
Total PGA used for auto workareas 0 bytes: Workara size occupied in current auto mode
Maximum PGA used for auto Workareas 0 bytes:auto mode occupied workara size maximum
Total PGA used for manual workareas 0 Bytes: Manual size occupied in current Workara mode
Maximum PGA used for manual Workareas 0 bytes:manual mode occupied workara size max
Allocation Count 0: number of uses exceeding PGA size
Bytes processed 6438912 bytes:p ga bytes Used
Extra bytes read/written 0 Bytes: bytes written to the temporary segment
Cache hit percentage percent:bytes processed/(bytes processed+extra bytes read/written)
Recompute count (total) 123

Global memory bound: the maximum RAM that a serial operation can use
=min (5%*pga_aggregate_target,50%*_pga_max_size,_smm_max_size),
When you modify the value of the parameter Pga_aggregate_target, the Oracle system is based on the Pga_aggregate_target and _pga_max_size
These two values are automatically modified by the parameter _smm_max_size. The rules for specific modifications are:
If _pga_max_size is greater than 5%*pga_aggregate_target, then _smm_max_size is 5%*pga_aggregate_target.
If _pga_max_size is less than or equal to 5%*pga_aggregate_target, then _smm_max_size is 50%*_pga_max_size.

Total PGA in used: the PGA currently in use, which can be obtained from the v$process pga_used_mem field
Select SUM (A.PGA_USED_MEM), sum (A.PGA_ALLOC_MEM), sum (A.PGA_MAX_MEM) from V$process a
Total PGA in used, total PGA allocated, maximum PGA allocated in V$pgastat
These 3 values are almost

Before we perform the creation of the index, we also introduce a view V$session_longops view

[SQL]View Plaincopy

2. sql> desc v$session_longops
4. is the name empty? Type
6. ----------------------------------------- -------- ----------------
10. SID number
12. serial# number
14. Opname VARCHAR2 (64)
16. TARGET VARCHAR2 (64)
18. Target_desc VARCHAR2 (32)
20. Sofar number
22. TotalWork number
24. UNITS VARCHAR2 (32)
26. Start_time DATE
28. Last_update_time DATE
30. Time_remaining number
32. Elapsed_seconds number
34. CONTEXT number
36. MESSAGE VARCHAR2 (512)
38. USERNAME VARCHAR2 (30)
40. Sql_address RAW (4)
42. Sql_hash_value number
44. Qcsid number

Where Sid and Serial# are matched with v$session,
Opname: Refers to the operation name that is executed for a long time. For example: Table Scan
TARGET: The object_name being manipulated. such as: TableA
Target_desc: Describe the content of target
Sofar: This is a need to focus on, indicating the number of jobs that have to be completed, such as how many blocks were scanned.
TotalWork: Refers to the total number of target objects (expected). such as the number of blocks.
UNITS:
Start_time: Start time of the process
Last_update_tim: The last time the Set_session_longops was called
time_remaining: Estimated how much time is needed to complete the unit in seconds
elapsed_seconds: Refers to the time from start operation to last update
CONTEXT:
MESSAGE: A complete description of the operation, including progress and action content.
USERNAME: The same as in V$session.
Sql_address: Association V$sql
Sql_hash_value: Association V$sql
Qcsid: Used primarily in parallel queries.

The following tests are officially started

1. Create a random number using a Python script

Import Random

‘‘‘

Created on 2012-3-26

@author: Jscn-xw

‘‘‘

For j in Range (1,10):

For I in Range (1,10000000):

Print Random.randint (100000000,999999999), Random.randint (100000000,999999999)

2. Create a test table

Sql> CREATE TABLE Tbim (Id1 number,id2varchar2) nologging;

3. Load enters data

3.1 Creating a control file (TBIM.CTL)

Load data

--infile '/home/oracle/bi_logfile.txt '

into table Tbim

Append

Fields terminated by '

Optionally enclosed by ' "'

Trailing Nullcols

(

ID1,

Id2

)

3.2 Sqlldr into the database

[Email protected]]$ sqlldr userid=security/security control=tbim.ctldata=/home/oracle/tbim.bcp

4 Testing

Sql> Set Timing on

Sql> Select COUNT (*) from Tbim;

COUNT (*)

----------

400000000

elapsed:00:00:06.57

4.1 What parameters are not tested create speed

Sql> CREATE index Id1_ind on Tbim (ID1) tablespace imindex;

Index created.

elapsed:00:16:23.51

At this point, observe the changes in the temporal table space, and we note that the Temporal table space is constantly increasing. Also pay attention to the changes in the V$session_longops view.

Focus on changes and values of Sofar, time_remaining, elapsed_seconds fields

4.2 Plus nologing Parameters

sql> DROP Index Id1_ind;

Sql> CREATE index Id1_ind on Tbim (ID1) tablespace imindex nologging;

Index created.

elapsed:00:16:40.20

4.3 Plus parallel parameters

sql> DROP Index Id1_ind;

Sql> CREATE index Id1_ind on Tbim (ID1) tablespace imindex nologging parallel 4;

Index created.

elapsed:00:09:03.74

Feeling parallel is not reliable, and nologging effect is not very obvious, at least for ORACLE11GR2.

4.4 Adjustment Sort_area_size

Sql> alter session setworkarea_size_policy=manual;

Sql> alter session setworkarea_size_policy=manual;

Sql> alter session setsort_area_size=2000000000;

Sql> alter session setsort_area_size=2000000000;

Sql> CREATE index Id1_ind on Tbim (ID1) tablespace imindex nologging parallel 4;

Index created.

elapsed:00:08:12.79

This effect is still quite obvious.

4.5 Modifying other parameters

Number of blocks read at one time when modifying a full table scan Db_file_multiblock_read_count

Size of the direct path IO, 10351 event level 128

Disable Block checksum/checking

Alternative sorting algorithm _newsort_type

Sql> alter session setdb_file_multiblock_read_count=1024;

Sql> alter session setdb_file_multiblock_read_count=1024;

Sql> alter session SET Events ' 10351trace name context forever, Level 128 ';

Sql> alter session setsort_area_size=2000000000;

Sql> alter session setsort_area_size=2000000000;

Sql> alter session Set "_sort_multiblock_read_count" = 128;

Sql> alter session Set "_sort_multiblock_read_count" = 128;

Sql> alter session enable parallel DDL;

Sql> alter session Setdb_block_checking=false;

Sql> alter system setdb_block_checksum=false;

Sql> CREATE index Id1_ind on Tbim (ID1) tablespace imindex nologging parallel 4;

Index created.

elapsed:00:07:37.57

5. Summary

I can speed up the creation of indexes by:

1) In addition, the number of parallel queries can be adjusted appropriately (generally not more than 8);

2) Index and table separation, separate temporal table space;

3) Adjust the table to nologging state, or specify nologging when creating an index;

4) We can adjust the relevant parameters of the database to accelerate the creation of index speed, examples are as follows:

Sql> alter session setdb_file_multiblock_read_count=1024;

Sql> alter session setdb_file_multiblock_read_count=1024;

Sql> alter session SET Events ' 10351trace name context forever, Level 128 ';

Sql> alter session setsort_area_size=2000000000;

Sql> alter session setsort_area_size=2000000000;

Sql> alter session Set "_sort_multiblock_read_count" = 128;

Sql> alter session Set "_sort_multiblock_read_count" = 128;

Sql> alter session enable parallel DDL;

Sql> alter session Setdb_block_checking=false;

Sql> alter system setdb_block_checksum=false;

The above adjustment generally can speed up the creation speed of more than 40%

Increase the speed at which indexes are created in Oracle's massive data