UXDBX Oracle 12c/18c/19c database

Database Monitoring for Oracle Cloud DB

PGA Tuning

Setting PGA_AGGREGATE_TARGET:

Possible Scenario

you decide to reserve 20% of the total physical memory for the operating system and other non-Oracle applications,  you dedicate 80% of the memory on the system to the Oracle database instance.

Divide the remaining available memory between the SGA and the PGA:

Initial Configuration

OLTP systems

Oracle recommends initially dedicating  20% of the available memory to the PGA 80% to the SGA. 

initial value of the PGA_AGGREGATE_TARGET is then claculates as PGA_AGGREGATE_TARGET = (total_mem * 0.8) * 0.2 

total_mem  =  physical memory available on the system.

DSS systems 

Oracle recommends initially dedicating  50% of the available memory to the PGA 50% to the SGA. 

PGA_AGGREGATE_TARGET = (total_mem * 0.8) * 0.5 

total_mem  =  physical memory available on the system.

PGA_AGGREGATE_TARGET to 640 MB for an OLTP system PGA_AGGREGATE_TARGET to 1600 MB for an DSS system PGA_AGGREGATE_LIMIT – set this to hard limit to avoid paging

Use pga group then PGA stats to monitor current PGA stats in UXDBX:

WORKAREA Optimizations

OPTIMAL_EXECUTIONS– number of times, this work area ran optimally without using temporary tablespace.

OPTIMAL_EXECUTIONS– number of times, this work area ran optimally without using temporary tablespace.

ONEPASS_EXECUTIONS– number of times, this work area used temporary tablespace only once to get it finished.

MULTIPASSES_EXECUTIONS-number of times, this work area used temporary tablespace in multiple times to get it finished.

Add more to the PGA_AGGREGATE_TARGET if multi-pass or one-pass is high

 

 

PGA TARGET ADVICE

to discover the proper value to set PGA_AGGREGATE_TARGET, submit the following metric in UXDBX, pga-pga target advice. As you can see the optimal starting value is 296m, so a good value to set PGA_AGGREGATE_TARGET is 300m. 

 

pga-pga target advice

 

 

PGA SUMMARY: 4 Step Method:

1.) Proper tuning of the PGA is essential for a database that is performing at optimal efficiency. You should start off by setting your  PGA_AGGREGATE_TARGET based on the suggestion from the metric in UXDBX, pga-pga target advice.

2.) check UXDBX caches-pga cache the closer to 100 the better, but at least 90 on DSS systems 95 or better on OLTP systems.

3.) check UXDBX pga-pga workarea percentages, if single pass + multi pass is greater then 10 %, experiment with small increases in PGA_AGGREGATE_TARGET, or try and reduce sorting in the sql from dynamic view v$sqlarea

4.) Monitor in realtime pga-workarea by session muliti-pass, look for sid’s will a high number of passes, check UXDBX v$sqlarea session-user sql and check the analyze. for anomalies

 

No credit card required. Cancel anytime.

Plan’s start at $39.95