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