Oracle sorting memory

I'm in http://zhidao.baidu.com/question/123262452.html? I am very grateful to zw.ssg for sorting out the questions raised by Fr = MSG.
Answer

 

Sorting memory involves PGA.
When can I use automatic PGA memory management? When can I use manual PGA memory management?

Automatic PGA memory management is suitable for normal system operation during the day, allowing Oracle to automatically manage and allocate PGA memory based on the current load.

When there are few users at night, you can set the current session to use manual PGA memory management during maintenance, so that the current maintenance operation can obtain as much memory as possible and speed up execution.

For example, if the server is running in the automatic PGA memory management mode and a task needs to be sorted and connected to a large table during the night, it can be changed to manual PGA memory management in this operation session temporarily, then allocate a large sort_area_size and hash_area_size (50% or even 80% of memory, to ensure that no other user is used), which can greatly speed up the system operation without affecting the impact on the system during peak hours during the day.

Operation Command

Session-level changes

Alter session set workarea_size_policy = {auto | manaul };

Alter session set sort_area_size = 65536;

Alter session set hash_area_size = 65536;

Application

1. sorting area:

Pga_aggregate_target is 100 mb. A single query can use 5%, that is, the time required for sorting 5 MB.

SQL> Create Table sorttable as select * From all_objects;

The table has been created.

SQL> insert into sorttable (select * From sorttable );

You have created 49735 rows.

SQL> insert into sorttable (select * From sorttable );

You have created 99470 rows.

SQL> set timing on;

SQL> set autotrace traceonly;

SQL> select * From sorttable order by object_id;

Row 198940 has been selected.

Used time: 00: 00: 50.49

Time required to change the sorting area to 30 mb at the session level

SQL> alter session set workarea_size_policy = manual;

The session has been changed.

Used time: 00: 00: 00.02

SQL> alter session set sort_area_size = 30000000;

The session has been changed.

Used time: 00: 00: 00.01

SQL> select * From sorttable order by object_id;

Row 198940 has been selected.

Used time: 00: 00: 10.76

We can see that the time required is reduced from 50.49 seconds to 10.31 seconds, and the speed is significantly improved.

2. Hash area:

Pga_aggregate_target is 100 mb. The time required for table connection is 5% MB for a single query.

SQL> select/* + use_hash (tb1 tb2) */* From sorttable tb1, sorttable tb2 where tb1.object _ id = tb2.object _ id;

Row 49735 has been selected.

Used time: 00: 00: 40.50

Time required to modify the hash area at the session level to 30 mb

SQL> alter session set workarea_size_policy = manual;

The session has been changed.

Used time: 00: 00: 00.01

SQL> alter session set hash_area_size = 30000000;

The session has been changed.

Used time: 00: 00: 00.01

SQL> select/* + use_hash (tb1 tb2) */* From sorttable tb1, sorttable tb2 where tb1.object _ id = tb2.object _ id;

Row 49735 has been selected.

Used time: 00: 00: 04.47

The required time is increased from 40.50 seconds to 4.47 seconds, and the effect is equally obvious.

Note: All the above experiments run a full table scan to ensure that the relevant tables are read into the buffer zone, so as to avoid errors caused by data not being read into the cache.

--- Another URL:
Summary:
1. Using Parallel parameters, 8 CPUs, parallel 6 can be used, and a maximum of 6 CPUs can be used. Normally, no problem occurs (provided that other applications do not occupy resources with more than 2 CPUs)
2. Nologging must be used to increase the speed. (Reduce a large number of redo logs)
3. The maximum size of the common auto PGA is 100 MB, which obviously does not achieve the best performance. You should use manual PGA
Alter session set workarea_size_policy = manual;
Alter session set hash_area_size = 100000; -- hash_area_size is automatically adjusted according to sort_area_size * 2 by default. As a result, sort_area_size cannot exceed 1 GB. If it is set manually, no problem occurs.
Alter session set sort_area_size = 2000000000; -- when the system has enough available memory, the maximum size is 2 GB.
4. Setting a higher value of db_file_multiblock_read_count also improves I/O performance. Ensure that db_cache_size is large enough to avoid the emergence of free_buffer_wait.
5. You can use v $ session_longops to monitor the progress.