Oracle mass data accelerates index creation

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.

1. SQL>DescV $ pgastat;
2. Name
3. --------------------------------
4. NAMEName
5. VALUE
6. UNIT
7. ------------------- Statistical item
8. Select*FromV $ pgastat
9. NAMEVALUE UNIT
10. ------------------------------------------------------------
11. Aggregate PGA target parameter 150994944 bytes
12. Aggregate PGA auto target 93579264 bytes
13. GlobalMemory bound 30198784 bytes
14. Total PGA inuse 47017984 bytes
15. Total PGA allocated 56666112 bytes
16. Maximum PGA allocated 58632192 bytes
17. Total freeable PGA memory 2883584 bytes
18. ProcessCount23
19. MaxProcessesCount48
20. PGA memory freed backToOperating System 5177344 bytes
21. Total PGA usedForAuto workareas 0 bytes
22. Maximum PGA usedForAuto workareas 0 bytes
23. Total PGA usedForManual workareas 0 bytes
24. Maximum PGA usedForManual workareas 0 bytes
25. Over allocationCount0
26. Bytes processed 6438912 bytes
27. Extra bytesRead/Written 0 bytes
28. Cache hit percentage 100 percent
29. RecomputeCount(Total) 123

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

1. SQL>DescV $ session_longops
2. Is the name empty? Type
3. -----------------------------------------------------------------
5. SID NUMBER
6. SERIAL # NUMBER
7. OPNAME VARCHAR2 (64)
8. TARGET VARCHAR2 (64)
9. TARGET_DESC VARCHAR2 (32)
10. SOFAR NUMBER
11. TOTALWORK NUMBER
12. UNITS VARCHAR2 (32)
13. START_TIMEDATE
14. LAST_UPDATE_TIMEDATE
15. TIME_REMAINING NUMBER
16. ELAPSED_SECONDS NUMBER
17. CONTEXT NUMBER
18. MESSAGE VARCHAR2 (512)
19. USERNAME VARCHAR2 (30)
20. SQL _ADDRESS RAW (4)
21. SQL _HASH_VALUE NUMBER
22. QCSID NUMBER

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 Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12

1 2 3 Next Page