Oracle 9i PGA use control
We know that Oracle from 9i provides automatic management of PGA, from this way, we can do not have to calculate the cumbersome PGA size regardless of the annoying settings such as sort_area_size, haveh_rea_size. But how to control each session using the size of the PGA, will there be a session to run all PGA, while other sessions can't get the space situation? With this question, we can come to do an experiment.
The experiment is very simple, the initial PGA_AGGREGATE_TARGET is set to 100M, we do a sorted big query, then view the use of PGA. Then modify the PGA_AGGREGATE_TARGET to 1G, do a large query, see the PGA usage space.
SQL> Startup Pfile = / Test / inittest.oraoracle instance start.
Total System Global Area 2710294504 Bytesfixed Size 454632 bytesvariable size 805306368 Bytesdatabase buffers 1903992832 BYtesdatabase mounted.DATABASE OPENED.
SQL> Show Parameter Pganame Type Value ----------------------------- ---- ------------------------------ PGA_AGGREGATE_TARGET BIG INTEGER 100000000
SQL> CONN TESTCONNECTED.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 Memory 243208Session PGA Memory Max 243208 This can be seen when the initial connection is only about 240K
SQL> SET Autotrace Trace;
SQL> SELECT TIM 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) 2 1 Partition Range All) 3 2 Table Access (Full) of 'Test' (COST = 84738 Card = 2932520 BYtes = 173018680) Statistics ------------------------- -------------------------------- 475 Recursive Calls 75 DB Block Gets 930726 Consistent Gets 962505 Physical Reads 60 Redo Size 113681365 BYTES SENT VIA SQL * NET To Client 2198688 BYtes Received Via Sql * Net from Clom Clom Clittle 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 Memory 457496session PGA Memory Max 6355736
After completing a big query, the peak of PGA is about 6m, which may contain some of the PGA's own overhead closing the database, modify the PGA_AGGREGATE_TARGET to 1G, restart the database SQL> conn / as sysdbaconnected.sql> shutdown immediate; data 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, 2002, Oracle Corporation. All Rights Reserved.
Connected to an iDLE Instance.
SQL> startup pfile = / test / inittest.oraORACLE instance started.Total System Global Area 2710294504 bytesFixed Size 454632 bytesVariable Size 805306368 bytesDatabase Buffers 1903992832 bytesRedo Buffers 540672 bytesDatabase mounted.Database opened.SQL> conn test / testConnected.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 Memory 250792session PGA Memory Max 381864
SQL> Show Parameter Pganame Type Value ----------------------------- ---- ------------------------------ PGA_AGGREGATE_TARGET BIG INTEGGREGATE_TARGET BIG INTEGER 1000000000SQL> Set Autotrace Trace; SQL> SELECT TIM 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) Card = 2932520 BYtes = 173018680) 2 1 Partition Range ALL) 3 2 Table Access (Full) of 'Test' (COST = 84738Card = 2932520 BYtes = 173018680)
Statistics --------------------------------------------------- --------- 3740 recursive calls 28 db block gets 931577 consistent gets 955856 physical reads 60 redo size 113681365 bytes sent via SQL * Net to client 2198688 bytes received via SQL * Net from client 199837 SQL * Net roundtrips to / From Client 77 Sorts (Memory) 1 Sorts (Disk) 2997531 Rows ProcessedSql> 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 Memory 456568session PGA Memory Max 50263928
It can be seen that this big query uses nearly 50m PGA space, why only use 6m for the first time, this is because Oracle will limit the maximum space of each session using PGA, each session can use up to 5% The total space of the PGA, so the first time can be used to 5m or so, plus the PGA's own overhead, using a total of 6m. The second time is expanded due to the total PGA size, the PGA size used by Session is almost 5% of the total PGA size. Oracle controls the use of PGA, avoiding a session to occupy most of the PGA. However, there is still a special case, and the session PGA can be used to 30% total PGA space in the case of parallel operation.
