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