Oracle mass data accelerates index creation

Oracle mass data accelerates index creation. database version: OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0

Oracle mass data accelerates index creation. Database version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

Basic information:

Database Version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

Operating system version: CentOS release 5.6

To speed up index creation, consider the following:

Use nologging Parameters

Use parallel Parameters

Use manual pga at the session level and manually adjust sort_area_size

Modify other parameters

Note: hash_area_size is not adjusted manually here. By default, hash_area_size is automatically adjusted according to sort_area_size * 2, so that sort_area_size cannot exceed 1G. So we can directly adjust the sort_area_size parameter here.

Here, we will first record the usage of pga. After 9i, We can query the allocation and usage of pga.V $ pgastatView.

The above explanation is 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 memory available for a single SQL statement
4 total PGA inuse 47017984 bytes: The consumed pga (including all occupied pga, such as workare pl/SQL)
5 total PGA allocated 56666112 bytes: total PGA memory allocated to the current instance.
In general, this value should be smaller than PGA_AGGREGATE_TARGET,
However, if the PGA of the process needs to grow rapidly, it can exceed the limit of PGA_AGGREGATE_TARGET.
6 maximum PGA allocated 58632192 bytes: maximum size that the pga has expanded
7 total freeable PGA memory 2883584 bytes: releasable pga
8 process count 23: current process
9 max processes count 48: process at maximum
10 PGA memory freed back to operating system 5177344 bytes
11 total PGA used for auto workareas 0 bytes: workara size occupied in current auto mode
12 maximum PGA used for auto workareas 0 bytes: maximum workara size occupied in auto Mode
13 total PGA used for manual workareas 0 bytes: workara size occupied in current manual mode
14 maximum PGA used for manual workareas 0 bytes: maximum workara size occupied in manual mode
15 over allocation count 0: Number of times the usage exceeds the pga size
16 bytes processed 6438912 bytes: bytes used by pga
17 extra bytes read/written 0 bytes: bytes written to the temporary segment
18 cache hit percentage 100 percent: bytes processed/(bytes processed + extra bytes read/written)
19 recompute count (total) 123

Global memory bound: maximum memory used by a serial operation
= Min (5% * pga_aggregate_target, 50% * _ pga_max_size, _ smm_max_size ),
When you modify the value of pga_aggregate_target, the Oracle system will
These two values are used to automatically modify the parameter _ smm_max_size. The specific modification rules are as follows:
If _ pga_max_size is greater than 5% * pga_aggregate_target, _ smm_max_size is 5% * pga_aggregate_target.
If _ pga_max_size is less than or equal to 5% * pga_aggregate_target, _ smm_max_size is 50% * _ pga_max_size.

Total PGA in used: the PGA currently in use, which can be obtained from the pga_used_mem field of v $ process.
Select sum (a. PGA_USED_MEM), sum (a. PGA_ALLOC_MEM), sum (a. PGA_MAX_MEM) from v $ process
Total PGA in used, total PGA allocated, and maximum PGA allocated in v $ pgastat
The three values are similar.

Before creating an index, we also need to introduce a viewV $ session_longopsView

The SID and SERIAL # match the values in the v $ session,
OPNAME: indicates the name of the operation that has been executed for a long time. For example, Table Scan
TARGET: The operated object_name. For example: tableA
TARGET_DESC: Description of target
SOFAR: focus on the number of jobs to be completed, such as how many blocks are scanned.
TOTALWORK: the total number of target objects (estimated ). Such as the number of blocks.
UNITS:
START_TIME: process start time
LAST_UPDATE_TIM: The last time set_session_longops was called.
TIME_REMAINING: estimated time required for completion, in seconds
ELAPSED_SECONDS: indicates the last update time from the start time.
CONTEXT:
MESSAGE: a complete description of the operation, including the progress and operation content.
USERNAME: same as in v $ session.
SQL _ADDRESS: Associate v $ SQL
SQL _HASH_VALUE: Associate v $ SQL
QCSID: Used in parallel queries.

For more information about Oracle, see the Oracle topic page? Tid = 12

,