This blog has moved here.

Monday, August 06, 2007

PGA Diagnostics

Do not forget a very important Oracle memory buffer which is the PGA (Program Global Area)! Most of us are tempted to look first of all at the SGA, especially at the library cache, whenever users complain that their SQLs run poorly. Of course, the library cache is a proper place to look at for problems, as well as the disk activity, CPU load and so many, many others. However, do NOT forget the PGA, because an inappropriate configuration can lead to a lot of performance issues.


So, reading through the "Database Performance Tuning Guide" I have found the section which talk about the tuning of the PGA. I am reading it quite often but it's still difficult to remember all the related fixed views and queries which are in connection with this topic. Therefore I have decided to put all the nice queries into a script which should give me a rough image of the PGA configuration.


Below is the script which I would like to share with all whom might be interested:



-- PGA Report
-- Displays various statistics regarding the PGA usage

set linesize 140
set pagesize 9999
set heading off
set feedback off

-- general statistics
select 'SECTION 1: GENERAL STATISTICS FOR THE PGA' from dual;
select '=================================================================='
|| '=========================' from dual;

set heading on
select name,
decode(unit, 'bytes', round(value / 1024 / 1024, 2), value) value,
decode(unit, 'bytes', 'MB', '') unit
from v$pgastat;

set heading off
select 'Check the following:' from dual;
select ' - "aggregate PGA auto target" should not be too small in comparison '
|| 'with the "aggregate PGA target parameter"' from dual;
select ' - "global memory bound" should not be lower than 1M' from dual;
select ' - "over allocation count" should be near 0 or should not increasing in time' from dual;
select ' - the bigger "cache hit percentage", the better' from dual;
select '' from dual;

set heading on
SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage
FROM (SELECT name, value cnt, (sum(value) over ()) total
FROM V$SYSSTAT
WHERE name like 'workarea exec%');
set heading off

-- PGA used by all DB processes
select 'SECTION 2: PGA USED BY CURRENT DB PROCESSES (IN MB), ORDER BY PGA_ALLOC_MEM' from dual;
select '=================================================================='
|| '=========================' from dual;
set heading on

break on report;
compute sum label '--> TOTAL' of pga_used_mem on report;
compute sum label '--> TOTAL' of pga_alloc_mem on report;
compute sum label '--> TOTAL' of pga_freeable_mem on report;
compute sum label '--> TOTAL' of pga_max_mem on report;

SELECT PROGRAM,
round(PGA_USED_MEM / 1024 / 1024, 2) pga_used_mem,
round(PGA_ALLOC_MEM / 1024 / 1024, 2) pga_alloc_mem,
round(PGA_FREEABLE_MEM / 1024 / 1024, 2) pga_freeable_mem,
round(PGA_MAX_MEM / 1024 / 1024, 2) pga_max_mem
FROM V$PROCESS
order by pga_alloc_mem desc;

set heading off
select 'The columns have the following meaning:' from dual;
select ' - PGA_USED_MEM = PGA memory currently used by the process' from dual;
select ' - PGA_ALLOC_MEM = PGA memory currently allocated by the process (including free '
|| 'PGA memory not yet released to the operating system by the server process)' from dual;
select ' - PGA_FREEABLE_MEM = Allocated PGA memory which can be freed' from dual;
select ' - PGA_MAX_MEM = Maximum PGA memory ever allocated by the process' from dual;
select '' from dual;

set feedback off;
select 'SECTION 3: USED PGA MEMORY BY CATHEGORIES (VALUES IN MB ORDER DESC BY ALLOCATED_MB).' from dual;
select '=================================================================='
|| '=========================' from dual;
set heading on
select category,
round(sum(allocated) / 1024 / 1024, 2) allocated_mb,
round(sum(used) / 1024 / 1024, 2) used_mb,
round(sum(max_allocated) / 1024 / 1024, 2) max_allocated_mb
from v$process_memory
group by category
order by 2 desc;

set heading off
select '' from dual;

-- workareas histogram
select 'SECTION 4: SQL WORKAREAS HISTOGRAM' from dual;
select '=================================================================='
|| '=========================' from dual;
set heading on
SELECT LOW_OPTIMAL_SIZE/1024 low_kb,
(HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS
FROM V$SQL_WORKAREA_HISTOGRAM
WHERE TOTAL_EXECUTIONS != 0;

set heading off
select '' from dual;

-- active workareas
select 'SECTION 5: CURRENTLY ACTIVE WORKAREAS' from dual;
select '=================================================================='
|| '=========================' from dual;
set heading on
set feedback on
SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
operation_type OPERATION,
trunc(EXPECTED_SIZE/1024) ESIZE,
trunc(ACTUAL_MEM_USED/1024) MEM,
trunc(MAX_MEM_USED/1024) "MAX MEM",
NUMBER_PASSES PASS,
trunc(TEMPSEG_SIZE/1024/1024) TSIZE_MB
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2;
set heading off
set feedback off
select 'The meaning of the above columns is:' from dual;
select ' - SID = the active session identifier' from dual;
select ' - OPERATION = the type of the operation' from dual;
select ' - ESIZE = the expected size for the sql workarea' from dual;
select ' - MEM = Amount of PGA memory (in KB) currently allocated on behalf of this work area.' from dual;
select ' - MAX MEM = Maximum memory amount (in KB) used by this work area' from dual;
select ' - PASS = Number of passes corresponding to this work area (0 if running in OPTIMAL mode)' from dual;
select ' - TSIZE_MB = Size (in megabytes) of the temporary segment used on behalf of this work area. '
|| 'This column is NULL if this work area has not (yet) spilled to disk.' from dual;
select '' from dual;

-- top 10 sql with gurmand sql areas
select 'SECTION 6: OP 10 WORK AREAS REQUIRING MOST CACHE MEMORY' from dual;
select '=================================================================='
|| '=========================' from dual;
set heading on
SELECT *
FROM (SELECT distinct s.sql_id,
substr(s.SQL_TEXT, 1, 50) sql_text,
operation_type,
estimated_optimal_size,
max_tempseg_size
FROM V$SQL_WORKAREA a, V$SQLSTATS s
WHERE a.SQL_ID = s.SQL_ID
ORDER BY estimated_optimal_size)
WHERE ROWNUM <= 10;

set heading off
select 'SECTION 7: SQLs WITH WORK AREAS THAT HAVE BEEN EXECUTED IN ONE OR EVEN MULTIPLE PASSES' from dual;
select '=================================================================='
|| '=========================' from dual;
set heading on
set feedback on
SELECT sql_text, sum(ONEPASS_EXECUTIONS) onepass_cnt,
sum(MULTIPASSES_EXECUTIONS) mpass_cnt
FROM V$SQL s, V$SQL_WORKAREA wa
WHERE s.address = wa.address
GROUP BY sql_text
HAVING sum(ONEPASS_EXECUTIONS+MULTIPASSES_EXECUTIONS)>0;
set feedback off
set heading off

select 'SECTION 8: PGA TARGET ADVCE' from dual;
select '=================================================================='
|| '=========================' from dual;
show parameter pga_aggregate_target;
set heading on
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;


set heading off
select '' from dual;
pause press any key to exit...
exit

Have fun!

Thursday, August 02, 2007

Automatic Shared Memory Management

Well, it's been a while since the last post... However, I'm back and I am going to dig a little bit on the “Automatic Shared Memory Management” 10g feature.


As you already (probably) know, every Oracle instance has a big shared buffer called SGA (System Global Area). This big memory buffer is further divided in several slices, each of them being used for special kind of memory allocations. Till Oracle 9i, the only possibility to adjust their sizes were to manually set a couple of initialization parameters like: DB_CACHE_SIZE, SHARED_POOL_SIZE, JAVA_POOL_SIZE etc. However, in 10g some buffers from the SGA were endowed with a nice capability to resize themselves according to the current workload and other parameters.

The figure below depicts a regular section through the SGA.



As you can see the maximum size upon to which the whole SGA may extend is given by the SGA_MAX_SIZE parameter. However, the SGA_TARGET parameter is the one which actually set the amount of memory to be used by all SGA buffers.
The correlation between the values of the SGA_MAX_SIZE and the SGA_TARGET parameters is important mostly when you query the V$SGA_DYNAMIC_FREE_MEMORY.
Lets have a look onto the following output:



SQL> show parameter sga_target;

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
sga_target big integer 1504M

SQL> select current_size/1024/1024 size_M from v$sga_dynamic_free_memory;

SIZE_M
----------
544

SQL> show parameter sga_max_size

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
sga_max_size big integer 2G

At the very first sight you may think that from the SGA_TARGET which is 1504M, about 544M are free which, of course, is not true because the Oracle server will always try to expand SGA buffers so that the whole SGA_TARGET amount to be allocated. The free memory reported by the V$SGA_DYNAMIC_FREE_MEMORY view is the difference between the SGA_MAX_SIZE and the SGA_TARGET, which should be read as: “I still have 544M free from the whole SGA_MAX_SIZE which I can use for further expansions of the SGA_TARGET”.


A nice view which I also like to query when I want to find out a brief summary of the SGA composition is the V$SGAINFO.



SQL> select * from v$sgainfo;

NAME BYTES RES
----------------------------------- ---------- ---
Fixed SGA Size 2074152 No
Redo Buffers 14700544 No
Buffer Cache Size 671088640 Yes
Shared Pool Size 838860800 Yes
Large Pool Size 16777216 Yes
Java Pool Size 16777216 Yes
Streams Pool Size 16777216 Yes
Granule Size 16777216 No
Maximum SGA Size 2147483648 No
Startup overhead in Shared Pool 100663296 No
Free SGA Memory Available 570425344

11 rows selected.

Please notice that I can also look at the size of the so-called “granules”, which are the allocation units for the SGA memory. The size of one granule varies according to the whole size of the SGA, operation system and platform. You can also find the size of the granule by querying the V$SGA_DYNAMIC_COMPONENTS.



SQL> select granule_size from v$sga_dynamic_components where rownum <= 1;

GRANULE_SIZE
------------
16777216

Another important thing to remember is that if you set values for the parameters which controls the size of the dynamic buffers into the SGA then, those values will be taken as minimum thresholds which means that Oracle server will try to adjust the size of the buffers when necessary but will never decrease them under the manually set value.
Of course, there is also the possibility to disable the “Automatic Shared Memory Management” feature by simply setting the SGA_TARGET value to zero. Likewise, this feature will also be disabled if the STATISTICS_LEVEL parameter is NONE, but this is not recommended at all as it disables other Oracle features too (ADDM, for example).


Well, that's it... not very new information but just a short review.