Oracle DBA Scripts- PGA management and pga_aggregate_target

Hi Friends,

Here you can find out some useful and important Oracle DBA scripts for managing and administrating Database very easily.

- Advertisement -

Oracle DBA Scripts-1:

How to check hidden PGA parameter setting from Oracle hidden parameters. Following scripts are very useful to get hidden pga parameters. Kindly execute with SYSDBA privilege.

select a.ksppinm name, b.ksppstvl value from sys.x$ksppi a,sys.x$ksppcv b where a.indx = b.indx and a.ksppinm=’_smm_max_size’;

select a.ksppinm name, b.ksppstvl value from sys.x$ksppi a,sys.x$ksppcv b where a.indx = b.indx and a.ksppinm=’_pga_max_size’;

- Advertisement -

SELECT x.ksppinm name,
CASE WHEN x.ksppinm like ‘%pga%’ THEN to_number(y.ksppstvl)/1024
ELSE to_number(y.ksppstvl)
END AS value,
x.ksppdesc description
FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = userenv(‘Instance’)
AND y.inst_id = userenv(‘Instance’)
AND x.indx = y.indx
AND x.ksppinm IN (‘pga_aggregate_target’, ‘_pga_max_size’,
‘_smm_max_size’, ‘_smm_px_max_size’);

Oracle DBA Scripts-2:

How much my PGA space used and temporary tablespace used in database. Following script is showing current PGA space usage and temporary tablespace usage per SID.

SELECT sid, to_char(sysdate,’mi:ss’) time,
round(work_area_size/1048576, 1) work_area_size_mb,
round(max_mem_used/1048576, 1) max_mem_used_mb, number_passes, nvl(tempseg_size/
1048576, 0) tempseg_size_mb
FROM v$sql_workarea_active
ORDER BY sid;

Oracle DBA Scripts-3:

Percentage wise usage of workarea of PGA in database.

ol c1 heading ‘Workarea|Profile’ format a35
col c2 heading ‘Count’ format 999,999,999
col c3 heading ‘Percentage’ format 99
select name c1,count c2,decode(total, 0, 0, round(count*100/total)) c3
from
(
select name,value count,(sum(value) over ()) total
from
v$sysstat
where
name like ‘workarea exec%’);

Oracle DBA Scripts-4:

Finally following script is showing PGA advisory statistics which we are getting in Statspack or AWR reports. This is the manual technique to get PGA advisory without creation of AWR report.

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;

Above all are very useful PGA management Oracle DBA scripts, which are useful in Remote DBA support and Remote DBA services.

Wish you all the best.

Thanks and regards,

Expert Remote DBA service team of Dbametrix Solutions

When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert.

This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.

- Advertisement -