OracleInstance & database

SGA components and PGA usage

Current size of each SGA pool, then PGA target against what's actually allocated. PGA allocated well above target points at sort- or hash-heavy workloads.

Not yet verified. How scripts are tested

1CLEAR COLUMNS2SET LINESIZE 120 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN component  FORMAT A324COLUMN current_mb HEADING "CURRENT MB" FORMAT 999,999,9905COLUMN min_mb     HEADING "MIN MB"     FORMAT 999,999,9906COLUMN max_mb     HEADING "MAX MB"     FORMAT 999,999,9907COLUMN name       FORMAT A328COLUMN mb         HEADING "MB"         FORMAT 999,999,9909 10SELECT component,11       ROUND(current_size / 1048576) AS current_mb,12       ROUND(min_size / 1048576) AS min_mb,13       ROUND(max_size / 1048576) AS max_mb14FROM v$sga_dynamic_components15WHERE current_size > 016ORDER BY current_size DESC;17 18SELECT name, ROUND(value / 1048576) AS mb19FROM v$pgastat20WHERE name IN ('aggregate PGA target parameter', 'total PGA allocated',21               'maximum PGA allocated', 'total PGA inuse');

Save it as ora-memory.sql and run it with SQL> @ora-memory.

Open in denrepoRelated tool: Init parameter reference

Helps with

• ORA-04030: out of process memory when trying to allocate %s bytes (%s,%s)
• ORA-04031: unable to allocate %s bytes of shared memory ("%s","%s","%s","%s")

More Oracle scripts: Instance & database

• Instance status on every nodeHost, instance, version, status, whether logins are allowed, startup time and archiver state for every instance. A quick RAC-wide check after a…
• Working with the spfileShows which spfile the instance is using, where parameter files live and the order Oracle searches for them, and what SCOPE = MEMORY, SPFILE and BOTH…
• Database and instance overviewOne row per instance: host, version, uptime, role, open mode, archive log mode and whether it's a CDB. The first thing to run when you land on an…
• Non-default initialization parametersEvery parameter that has been changed from its default. Handy for comparing two databases or documenting a build.
• Processes and sessions against their limitsCurrent and peak usage of processes, sessions and transactions since startup. A PEAK % near 100 means the next connection storm will hit ORA-00020.
• Installed components and their statusEverything in DBA_REGISTRY. Anything not VALID after a patch or upgrade needs attention before you hand the database back.