Use of PGA and UGA memory

Use of statistical memory from the PGA and UGA of Thomas Kyte, using different sizes of the sorting area to view the PGA, UGA, and physical reads of different memory uses

Session 1: Create a test table and test different sort sizes

CREATE TABLE T as SELECT * from All_objects;exec dbms_stats.gather_table_stats (user, 't');

Create a test table

Create Run_query script: To control the size of the memory parameter sort area (sort_area_size) and the sorting of the table

Connect Glltabspace/oracleset serveroutput offset echo oncolumn sid new_val Sidselect SID from V$mystat where rownum = 1;a  Lter session Set Workarea_size_policy=manual;alter session Set Sort_area_size = &1;prompt run @reset_stat &sid and @watch_stat in another session Here!pauseset termout Offselect * from T ORDER by 1, 2, 3, 4;set termout onprompt run @wat Ch_stat in another session Here!pause

Run_query

Session 2: View the different sort sizes in session 1 to get Pga\uga, physical reads.

Reset_stat script: Record Pga\uga, physical read usage, and difference compared to previous

CREATE TABLE Sess_stats (name VARCHAR2 (+), value number, diff number); variable sid Numberexec:sid: = &1

Reset_stat

Watch_stat Script: Statistics Pga\uga, physical read usage, and writing tables created by Reset_stat scripts

Merge into sess_statsusing (select A.name, b.value from  v$statname A, v$sesstat b where a.statistic# = b.statistic#
   
    and B.sid =: Sid and   (A.name like '
    %ga%'        or a.name like '
    %direct temp%')) Curr_statson (sess_s Tats.name = curr_stats.name) When matched then  update set diff = curr_stats.value-sess_stats.value,             value = Curr_ Stats.valuewhen not matched then  insert (name, Value, diff)  Values  (curr_stats.name, Curr_stats.value, Nu ll)/select name, case when       name "
    %ga%" then            round (value/1024,0)            Else value        end Kbytes_writes, case when       name is '
    %ga%' then            round (diff/1024,0)            else value        end diff_ Kbytes_writes from  sess_stats order by name;
   

Watch_stat

Set the sort area to 64KB (65536), view Pga,uga

##############################################################     

set the sort area to 64KB (65536), view Pga,uga

############################################################## #会话1: [email protected]> @run_query 65536 is connected.       [Email protected]> column SID new_val Sid[email protected]> select Sid from V$mystat where rownum = 1; Sid----------1[email protected]> Alter session set WORKAREA_SIZE_POLICY=MANUAL, session changed.  [email protected]> alter session Set Sort_area_size = &1; Original value 1:alter session set Sort_area_size = &1 New value 1: Alter session Set Sort_area_size = 65536 session has changed.          Glltabspac[email protected]> prompt Run @reset_stat &sid and @watch_stat in another session Here!run @reset_stat 1 and @watch_stat in another session here! [Email protected]> Pause#新建一个会话, for session 2, when the table is not yet sorted, view the initial Pga\uga# @reset_stat 1, 1 is the SID in session 1[Email protected]> @reset_stat 1 table has been created. The PL/SQL process has completed successfully. [Email protected]> @watch_stat6 Line has been merged. NAME kbytes_writes Diff_kbytes_writes---------------------------------------------------------------- ------------- ------------------Physical reads direct temporary tablespace 0 0physical writes direct TE                                                        Mporary tablespace 0 0session PGA memory 1185session PGA Memory Max 1185session UGA Memo                                                     Ry 370session UGA Memory max 434 6 rows have been selected.#此时说明: Today's session uses UGA=370KB, Max 434kb,pga=1185kb, max value 1185kb

#会话1: Press ENTER to continue sorting the table

[Email protected]> set Termout off[email protected]> prompt Run @watch_stat in another session Here!run @watch_stat In another session here! [Email protected]> Pause

#会话2:

[Email protected]> @watch_stat6 Line has been merged. NAME                                                             kbytes_writes diff_kbytes_writes------------------------------------------------------------------ -----------------------------Physical reads direct temporary tablespace                                3181               3181physical writes Direct Temporary tablespace                               3181               3181session PGA memory                                                         993               -192session PGA memory Max                                                    1249                 64session UGA Memory                                                         498                128session UGA memory Max                                                     562                128 has selected 6 rows.

#说明: Large read and write of temporary tablespace

PGA:993KB, Maximum value 1249kb

UGA:498KB, Maximum value 562kb

sort area is 64kb

Set the sort area to 1MB (1048576) View Pga,uga

NAME                                                             kbytes_writes diff_kbytes_writes------------------------------------------------------------------ -----------------------------Physical reads direct temporary tablespace                                1064               1064physical writes direct Temporary tablespace                               1064               1064session PGA memory                                                         801                  0session PGA memory Max                                                    2209                  0session UGA Memory                                                         370                  0session UGA memory max                                                    1344                  0 has selected 6 rows.

Sort Area 1MB

I am seeing a decrease in the number of I/O in the temporary table space

Set the sort area to 1G (1073741820) View Pga,uga

NAME                                                             kbytes_writes diff_kbytes_writes------------------------------------------------------------------ -----------------------------Physical reads direct temporary tablespace                                   0                  0physical writes direct Temporary tablespace                                  0                  0session PGA memory                                                         737                  0session PGA memory Max                                                   10849              10112session UGA Memory                                                         306                  0session UGA memory max                                                   10282               9977

Sort Area 1G

When the sorting area is 1g, the actual size of the UGA uses 10MB, at this time the number of temporary tablespace I/O is 0, indicating that the sort area of memory is not allocated at the beginning, how many allocations are required, but not exceeding the sort_area_size size

Usage of PGA and UGA memory