In
my first article on PGA tuning I had explained the concept of
manual/automatic tuning of PGA. In my second article, I had given
guidelines to size the PGA in case of automatic tuning.

In the third and last article on PGA tuning, I will demonstrate both manual and automatic tuning of PGA.

BRIEF OVERVIEW OF THE DEMONSTRATION:

MANUAL TUNING : EFFECT OF VARIOUS SORT AREA SIZES ON SORTING OPERATIONS

— Disable AMM

— Enable manual PGA memory management

— Set sort area size to  different values (64k, 1M, 1G)

— Note the effect on sorts : As sort area size is increased, temporary tablespace usage decreases

AUTOMATIC TUNING

— Enable automatic PGA memory management with pga_aggregate target set to minimum (10M)

— execute query

— Sort spills to disk

— use Pga advisor to estimate correct size of pga

— Increase size og PGA as per advice

— Re execute query

— Sort does not spill to disk

————————————————–

– SAVE CURRENT PARAMETER SETTINGS IN PFILE

conn / as sysdba

create pfile=’/u01/app/oracle/initorcl.ora’ from spfile;

– DISABLE AMM

sho parameter memory_target;

alter system set memory_target=0;

sho parameter memory_target;

– ENABLE MANUAL PGA MEMORY MANAGEMENT

alter system set workarea_size_policy=manual;

alter system set pga_aggregate_target=0 scope=spfile;

startup force;

sho parameter workarea

sho parameter pga_agg

@?/sqlplus/admin/plustrce.sql

grant plustrace to hr;

– CREATE TEST TABLE, GATHER STATS

conn hr/hr

create table t as select * from all_objects;

exec dbms_stats.gather_table_stats( user, ‘T’ );

– END CURRENT SESSION AND START A NEW SESSION TO GET A CONSISTENT ENVIRONMENT IN WHICH NO WORK HAS BEEN DONE YET

conn hr/hr

– FIND OUT SID FOR CURRENT SESSION

HR>select sid from v$mystat where rownum = 1;

sid = 37

– START ANOTHER SESSION FROM WHERE WE CAN MONITOR

MEMORY USAGE FOR SORTING IN EARLIER SESSION

sqlplus / as sysdba

— NOTE DOWN CURRENT MEMORY USED FOR UGA/PGA

AND TEMPORARY TABLESPACE USAGE BY HR’s session

 NOTE THAT TEMPORARY TABLESPACE USAGE = 0

SYSDBA>

select a.name, b.value
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and b.sid = 37
and (a.name like ‘%ga %’
or a.name like ‘%direct temp%’);

NAME                                                                  VALUE

—————————————————————- ———-

session uga memory                                                   147180

session uga memory max                                               147180

session pga memory                                                   841832

session pga memory max                                               841832

physical reads direct temporary tablespace                                0

physical writes direct temporary tablespace                               0

– NOTE THAT DISK SORTS = 0

SYSDBA>

select name, value
from v$sysstat
where name like ‘%sort%';

NAME                                                                  VALUE

—————————————————————- ———-

sorts (memory)                                                         3687
sorts (disk)                                                              0
sorts (rows)                                                          22489

– NOTE THAT TEMPORARY SEGMENT USAGE BY HR = 0 AS NO SORTS HAVE BEEN MADE IN HR SESSION.

SYSDBA>

select username, segtype, blocks, tablespace
from   v$tempseg_usage
where username=’HR';

no rows selected

———————————————-

– SET SORT AREA SIZE = 64K IN HR SESSION AND PERFORM THE SORT

———————————————-

HR>

alter session set sort_area_size = 65536;

– NOTE THAT SORT HAS BEEN PERFORMED ON DISK

set autotrace traceonly statistics

select * from t order by 1, 2, 3, 4;

Statistics

———————————————————-

8  recursive calls
296  db block gets
808  consistent gets
3737  physical reads
0  redo size
3064341  bytes sent via SQL*Net to client
41240  bytes received via SQL*Net from client
3713  SQL*Net roundtrips to/from client
0  sorts (memory)
    1  sorts (disk)

55666  rows processed

— NOTE DOWN CURRENT MEMORY USED FOR UGA/PGA AND TEMPORARY TABLESPACE USAGE BY HR’s SESSION

– NOTE THAT TEMPORARY TABLESPACE HAS BEEN USED

. UGA expanded and then shrank (session uga memory < session uga memory max)

. PGA (1406016) is more than earlier value (841832)

SYSDBA>

select a.name, b.value
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and b.sid = 21
and (a.name like ‘%ga %’
or a.name like ‘%direct temp%’);

NAME                                                                  VALUE

—————————————————————- ———-

session uga memory                                                   274344

session uga memory max                                               331568
session pga memory                                                  1406016
session pga memory max                                              1406016
physical reads direct temporary tablespace                             3737
physical writes direct temporary tablespace                            3737
– NOTE THAT DISK SORTS HAVE INCREASED (FROM 0 TO 10)

select name, value
from v$sysstat
where name like ‘%sort%';

NAME                                                                  VALUE

—————————————————————- ———-

sorts (memory)                                                         8495
sorts (disk)                                                             10
sorts (rows)                                                         221576

———————————————-

– SET SORT AREA SIZE = 1M IN HR SESSION AND PERFORM THE SORT

———————————————-

HR>

alter session set sort_area_size = 1048576;

– NOTE THAT SORTS HAVE AGAIN SPILLED TO DISK AS SORT AREA SIZE IS STILL INSUFFIEICNT.

HR>

set autotrace traceonly statistics;

select * from t order by 1, 2, 3, 4;

Statistics

———————————————————-

8  recursive calls
9  db block gets
808  consistent gets
825  physical reads
0  redo size
3064341  bytes sent via SQL*Net to client
41240  bytes received via SQL*Net from client
3713  SQL*Net roundtrips to/from client
0  sorts (memory)
  1  sorts (disk)

55666  rows processed
— NOTE DOWN CURRENT MEMORY USED FOR UGA/PGA AND TEMPORARY TABLESPACE USAGE BY HR’s SESSION

– NOTE THAT TEMPORARY TABLESPACE USAGE IS REDUCED

( current value – earlier value = 4562 – 3737 = 825

which is less that ealier value (3737))

— UGA EXPANDED FROM EARLIER VALUE OF 274344 TO 1309592 (= MAX VALUE)

— PGA EXPANDED FROM EARLIER VALUE OF 1406016 TO 2483236(= MAX VALUE)

SYSDBA>

select a.name, b.value
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and b.sid = 21
and (a.name like ‘%ga %’
or a.name like ‘%direct temp%’);

NAME                                                                  VALUE

—————————————————————- ———-

session uga memory                                                  1309592
session uga memory max                                              1309592
session pga memory                                                  2483236
session pga memory max                                              2483236
physical reads direct temporary tablespace                             4562
physical writes direct temporary tablespace                            4562

– NOTE THAT TOTAL DISK SORTS (SINCE INSTANCE STRATUP) HAVE INCREASED AS CURRENT SORT SPILLED TO DISK.

SYSDBA>

select name, value
from v$sysstat
where name like ‘%sort%';

NAME                                                                  VALUE

—————————————————————- ———-

sorts (memory)                                                         9781
sorts (disk)                                                             11
sorts (rows)                                                         280487

———————————————-

– SET SORT AREA SIZE = 1G IN HR SESSION AND PERFORM THE SORT

————————————————

HR>

alter session set sort_area_size = 1073741820;

– NOTE THAT ALL SORTS HAVE BEEN PERFORMED IN MEMORY AS SORT AREA SIZE IS SUFFICIENT

HR>

set autotrace traceonly statistics

select * from t order by 1, 2, 3, 4;

Statistics

———————————————————-

1  recursive calls
0  db block gets
808  consistent gets
0  physical reads
0  redo size
3064341  bytes sent via SQL*Net to client
41240  bytes received via SQL*Net from client
3713  SQL*Net roundtrips to/from client
1  sorts (memory)

  0  sorts (disk)

55666  rows processed
— NOTE DOWN CURRENT MEMORY USED FOR UGA/PGA AND TEMPORARY TABLESPACE USAGE BY HR’s SESSION

– NOTE THAT TEMPORARY TABLESPACE USAGE IS NOT AT ALL USED AND SORT IS COMPLETELY PERFORMED IN PGA

(Reads/Writes to temporary tablespace is same as earlier

value (4562))

— UGA AND PGA EXPANDED

SYSDBA>

select a.name, b.value
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and b.sid = 21
and (a.name like ‘%ga %’
or a.name like ‘%direct temp%’);

NAME                                             VALUE

————————–                       ———-
session uga memory           7404100
session uga memory max     7404100
session pga memory          8556012
session pga memory max     8556012
physical reads direct temporary tablespace   4562
physical writes direct temporary tablespace      4562
– DISK SORTS HAVE SAME VALUE (11) AS EARLIER AS SORTING COMPLETELY IN MEMORY ITSELF.

SYSDBA>

select name, value
from v$sysstat
where name like ‘%sort%';

NAME               VALUE

—————–  ———

sorts (memory)     10641
sorts (disk)       11
sorts (rows)       337538

—————————————–

– ENABLE AUTOMATIC PGA MEMORY MANAGEMENT

SET pga_aggregate target to minimum valie i.e. 10M

SYSDBA>

alter system set pga_aggregate_target=10m;

alter system set workarea_size_policy=auto;

sho parameter pga_agg

sho parameter workarea

— EXECUTE QUERY FROM HR SESSION WITH NEW SETTING

NOTE THAT SORT IS SPILLING TO DISK AS THE VALUE OF 10M IS INSUFFICIENT

HR>

select * from t order by 1, 2, 3, 4;

Statistics

———————————————————-
8  recursive calls
9  db block gets
808  consistent gets
827  physical reads
0  redo size
3064341  bytes sent via SQL*Net to client
41240  bytes received via SQL*Net from client
3713  SQL*Net roundtrips to/from client
0  sorts (memory)
          1  sorts (disk)

55666  rows processed

— FIND OUT WHETHER ABOVE QUERY EXECUTION WAS OPTIMAL/ONE PASS/MULTI PASS

– NOTE THAT QUERY WAS NOT EXECUTED IN OPTIMAL MODE (ONE PASS / MULTIPASS EXECS > 0)

HR>

SQL> col sql_text for a25 word_wrapped

      Select SQL_text, sum(OPTIMAL_EXECUTIONS) OPTIMUM_EXECS,
             sum(ONEPASS_EXECUTIONS) ONE_PASS_EXECS, sum(MULTIPASSES_EXECUTIONS) MULTI_PASS_EXECS
      from v$sql s, v$sql_workarea w
      where s.sql_text like ‘select * from t order by 1%’
       and s.sql_id=w.sql_id
      group by sql_text;

– LOOK AT PGA ADVISOR TO FIND OUT IF THE CURRENT SIZE OF PFA IS APPROPRIATE

SQL> SELECT round (PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
       ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
       ESTD_OVERALLOC_COUNT
FROM   v$pga_target_advice;

TARGET_MB   CACHE_HIT_PERC   ESTD_OVERALLOC_COUNT

—————-   ———————    ———————————-

10                     92                                         3

12                     92                                         3

14                     92                                         3

16                     92                                         3

18                     92                                         3

20                     92                                         3

30                     92                                         3

40                     92                                         3

60                     92                                         1

80                    100                                        0
set the PGA_AGGREGATE_TARGET parameter to a value where we avoid any over

allocation, so lowest PGA_AGGREGATE_TARGET value we can set is 80m (where

ESTD_OVERALLOC_COUNT is 0). After eliminating over-allocations, the goal is

to maximize the PGA cache hit Percentage based on your response-time

requirement and memory constraints.

SYSDBA>alter system set pga_aggregate_target=80m;

– AGAIN EXECUTE QUERY IN HR SESSION AND NOTE THAT SORTS HAVE NOT SPILLED TO DISK.

HR> select * from t order by 1, 2, 3, 4;

55666 rows selected.

Statistics

———————————————————-

1  recursive calls
0  db block gets
808  consistent gets
0  physical reads
0  redo size
3064341  bytes sent via SQL*Net to client
41240  bytes received via SQL*Net from client
3713  SQL*Net roundtrips to/from client
1  sorts (memory)

          0  sorts (disk)

55666  rows processed

— FIND OUT WHETHER ABOVE QUERY EXECUTION WAS OPTIMAL/ONE PASS/MULTI PASS

– NOTE THAT QUERY WAS NOT EXECUTED IN OPTIMAL MODE (OPTIMAL EXECS > 0)

SQL> col sql_text for a25 word_wrapped

      Select SQL_text, sum(OPTIMAL_EXECUTIONS) OPTIMUM_EXECS,
             sum(ONEPASS_EXECUTIONS) ONE_PASS_EXECS,
             sum(MULTIPASSES_EXECUTIONS) MULTI_PASS_EXECS
      from   v$sql s, v$sql_workarea w
      where  s.sql_text like ‘select * from t order by 1%’
       and   s.sql_id=w.sql_id
      group by sql_text;
– RESTORE EARLIER STATUS

SYSDBA>

drop table hr.t purge;

create spfile from pfile=’/u01/app/oracle/initorcl.ora';

startup force;

 

References: