Home » RDBMS Server » Server Administration » SGA/PGA history (Oracle 19c EE on Linux)
SGA/PGA history [message #685868] Sat, 09 April 2022 05:20 Go to next message
db_senior
Messages: 12
Registered: July 2021
Junior Member
Hi,

Oracle version: 19c EE

I need to check the statistics on the sga and pga memory to see if reducing it there could be performance issues.

Can I use a query to check the SGA trend during the snapshot interval and retention (dba_hist_snapshot)?

SGA Target Advisory in AWR report provides only some partial information.

I used these Oracle views with this query:

SELECT
        sn.instance_number         ,
        sga.allo sga               ,
        pga.allo pga               ,
        ( sga.allo + pga.allo ) tot,
        Trunc (SN.end_interval_time, 'mi') TIME
FROM
        (
                SELECT
                        snap_id        ,
                        instance_number,
                        Round (SUM (bytes) / 1024 / 1024 / 1024, 3) allo
                FROM
                        dba_hist_sgastat
                GROUP  BY
                        snap_id             ,
                        instance_number) sga,
        (
                SELECT
                        snap_id        ,
                        instance_number,
                        Round (SUM (value) / 1024 / 1024 / 1024, 3) allo
                FROM
                        dba_hist_pgastat
                WHERE
                        name = 'total PGA allocated'
                GROUP  BY
                        snap_id             ,
                        instance_number) pga,
        dba_hist_snapshot sn
WHERE
        sn.snap_id         = sga.snap_id
AND     sn.instance_number = sga.instance_number
AND     sn.snap_id         = pga.snap_id
AND     sn.instance_number = pga.instance_number
ORDER  BY
        sn.snap_id DESC,
        sn.instance_number;
but they only provide PGA history, I don't know how to use them for SGA history

Thanks


[Edit MC: change quote tags to code ones]

[Updated on: Sat, 09 April 2022 08:34] by Moderator

Report message to a moderator

Re: SGA/PGA history [message #685869 is a reply to message #685868] Sat, 09 April 2022 07:39 Go to previous messageGo to next message
EdStevens
Messages: 1375
Registered: September 2013
Senior Member
Let's back up a step. What problem are you trying to solve? Beware of contracting a case of Compulsive Tuning Disorder, and as a result, asking an X-Y Question.
Re: SGA/PGA history [message #685870 is a reply to message #685868] Sat, 09 April 2022 08:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68218
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please use "code" tags and not "quote" ones for your code.
Also feedback in your topics.

Re: SGA/PGA history [message #685871 is a reply to message #685870] Sat, 09 April 2022 12:38 Go to previous message
piripicchio
Messages: 18
Registered: April 2018
Location: Rome
Junior Member
Both the advisors (sga/pga) tell you exactly that, what do you need to know in particular?
Previous Topic: Defragmentation of partitioned indexes to reclaim space.
Next Topic: Patch conflict
Goto Forum:
  


Current Time: Fri Jul 01 05:53:34 CDT 2022