Monday, October 26, 2020
    Home Oracle Internals Oracle DBA Scripts- PGA management and pga_aggregate_target

    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
    select name,value count,(sum(value) over ()) total
    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,
    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




    - Advertisement -
    - Advertisment -

    Most Popular

    Five Reasons Why Database Outsourcing

    The blog post explains why database outsourcing and remote dba support becomes most popular during present time of Covid-19 Pandemic.

    Hardware of Database Server

    The blog post explains how to choose best hardware of your database server for getting high performance and security.

    oracle 19c new features – Automatic Indexing

    A major Oracle 19c autonomous database new Feature automatic indexing using DBMS_AUTO_INDEX some details and how to article.

    Differences Raw Device, ASM Device and CFS

    The article explains basic differences of ASM device, raw device and Oracle cluster file system cfs with fundamentals

    Recent Comments