Oracle 9i PGA usage control

Oracle 9i PGA usage control

We know that Oracle has provided the automatic PGA management function since 9i. From then on, we can skip the annoying settings such as sort_area_size and hash_area_size, and do not need to compute the tedious PGA size. But how can we control the size of PGA used by each session? Will there be a situation where one session runs out of all PGA while other sessions do not have space?
With this question, we can do an experiment.

The experiment procedure is very simple. The initial pga_aggregate_target is set to 100 m. We will make a large sorting query and then view the space used by PGA. Then modify pga_aggregate_target to 1 GB. In a large query, view the space used by PGA.

SQL> startup pfile =/test/inittest. ora
Oracle instance started.

Total system global area 2710294504 bytes
Fixed size 454632 bytes
Variable Size 805306368 bytes
Database buffers 1903992832 bytes
Redo buffers 540672 bytes
Database mounted.
Database opened.

SQL> show parameter PGA
Name type value
-----------------------------------------------------------------------------
Pga_aggregate_target big integer 100000000
 

SQL> conn test/test
Connected.
SQL> select name, value from V $ statname A, V $ mystat B where a. Statistic # = B. Statistic # and A. name like '% PGA % ';

NAME value
--------------------------------------------------------------------------
Session PGA apsaradb for memory 243208
Session PGA memory Max 243208
We can see that the PGA uses around K during the initial connection.

SQL> set autotrace trace;

SQL> select title from test order by starts, ends, title, zoo, city;
2997531 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose (cost = 129273 card = 2932520 bytes = 173018680)
1 0 sort (order by) (cost = 129273 card = 2932520 bytes = 173018680)
2 1 partition range (all)
3 2 Table Access (full) of 'test' (cost = 84738 card = 2932520 bytes = 173018680)

Statistics
----------------------------------------------------------
475 recursive cballs
75 dB block gets
930726 consistent gets
962505 physical reads
60 redo size
113681365 bytes sent via SQL * Net to client
2198688 bytes encoded ed via SQL * Net From Client
199837 SQL * Net roundtrips to/from client
37 sorts (memory)
1 sorts (Disk)
2997531 rows processed

SQL> set autotrace off;
SQL> select name, value from V $ statname A, V $ mystat B where a. Statistic # = B. Statistic # and A. name like '% PGA % ';
NAME value
--------------------------------------------------------------------------
Session PGA apsaradb for memory 457496
Session PGA memory Max 6355736

After a large query, the usage peak of PGA is about 6 MB, which may include some of the costs of PGA.
Shut down the database, modify pga_aggregate_target to 1 GB, and restart the database.
SQL> Conn/As sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
Oracle instance shut down.
SQL> exit

$ Sqlplus "/As sysdba"

SQL * Plus: Release 9.2.0.4.0-production on Sun Jul 18 11:46:53 2004

Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup pfile =/test/inittest. ora
Oracle instance started.

Total system global area 2710294504 bytes
Fixed size 454632 bytes
Variable Size 805306368 bytes
Database buffers 1903992832 bytes
Redo buffers 540672 bytes
Database mounted.
Database opened.
SQL> conn test/test
Connected.
SQL> select name, value from V $ statname A, V $ mystat B where a. Statistic # = B. Statistic # and A. name like '% PGA % ';

NAME value
--------------------------------------------------------------------------
Session PGA apsaradb for memory 250792
Session PGA memory Max 381864

SQL> show parameter PGA
Name type value
-----------------------------------------------------------------------------
Pga_aggregate_target big integer 1000000000
SQL> set autotrace trace;
SQL> select title from test order by starts, ends, title, zoo, city;
2997531 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose (cost = 112995 card = 2932520 bytes = 173018680)
1 0 sort (order by) (cost = 112995 card = 2932520 bytes = 173018680)
2 1 partition range (all)
3 2 Table Access (full) of 'test' (cost = 84738 card = 2932520 bytes = 173018680)

Statistics
----------------------------------------------------------
3740 recursive cballs
28 dB block gets
931577 consistent gets
955856 physical reads
60 redo size
113681365 bytes sent via SQL * Net to client
2198688 bytes encoded ed via SQL * Net From Client
199837 SQL * Net roundtrips to/from client
77 sorts (memory)
1 sorts (Disk)
2997531 rows processed

SQL> set autotrace off;
SQL> select name, value from V $ statname A, V $ mystat B where a. Statistic # = B. Statistic # and A. name like '% PGA % ';

NAME value
--------------------------------------------------------------------------
Session PGA apsaradb for memory 456568
Session PGA memory Max 50263928

We can see that this big query uses a space of nearly 50 MB of PGA. Why is it only 6 MB for the first time? This is because Oracle limits the maximum space of PGA for each session,
Each session can use a total space of up to 5% PGA. Therefore, the space of about 5 MB can be used for the first time. In addition to the cost of the PGA, a total of 6 MB is used. Second
Because the total PGA size is increased, the PGA size used by the session is about 5% of the total PGA size. Oracle controls the usage of the PGA well and avoids a session
It occupies most of the PGA.
However, there is still a special case. In parallel operations, the session PGA can use up to 30% of the total PGA space.