Oracle memory Advisory
-- ************************************************
-- Display Sga target advice
-- ************************************************
Oracle's SGA (System Global Area) Advisory recommendations are generated as part of the Automatic Database Diagnostic Monitor (ADDM) analysis.
The ADDM report includes information about SGA Advisory recommendations
Once the task is executed, you can view the recommendations in Oracle Enterprise Manager or by querying the DBA_ADVISOR_FINDINGS view.
DECLARE
task_id PLS_INTEGER;
BEGIN
task_id := DBMS_ADVISOR.CREATE_TASK('SGA_ADVISOR', 'ADDM', NULL);
DBMS_ADVISOR.CREATE_OBJECT(task_id, 'SYSTEM', 'SYSAUX');
DBMS_ADVISOR.SET_TASK_PARAMETER(task_id, 'TARGET_OBJECT', 'SYSTEM');
DBMS_ADVISOR.EXECUTE_TASK(task_id);
END;
/
or
SET SERVEROUTPUT ON
EXEC DBMS_ADVISOR.CREATE_TASK('ADDM', 'ADDM', NULL, NULL, NULL, NULL);
EXEC DBMS_ADVISOR.EXECUTE_TASK('ADDM');
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SELECT DBMS_ADVISOR.GET_TASK_REPORT('ADDM') AS ADDM_REPORT FROM DUAL;
-- ************************************************
-- Display Memory advice
-- ************************************************
SQL> select * from v$memory_target_advice;
-- ************************************************
-- Display Buffer Cache advice
-- ************************************************
SELECT a.size_for_estimate "Buffer size MB",
a.size_factor "Factor size",
round(a.estd_physical_read_time/1000,2) "Estim. time (s)",
a.estd_physical_read_factor "Estim. time factor",
a.estd_physical_reads "Estim. nb physical read"
FROM sys.v$db_cache_advice a
WHERE a.name='DEFAULT'
ORDER BY a.size_for_estimate;
-- ************************************************
-- Display shared pool advice
-- ************************************************
set lines 100
set pages 999
column c1 heading 'Pool |Size(M)'
column c2 heading 'Size|Factor'
column c3 heading 'Est|LC(M) '
column c4 heading 'Est LC|Mem. Obj.'
column c5 heading 'Est|Time|Saved|(sec)'
column c6 heading 'Est|Parse|Saved|Factor'
column c7 heading 'Est|Object Hits' format 999,999,999
SELECT
shared_pool_size_for_estimate c1,
shared_pool_size_factor c2,
estd_lc_size c3,
estd_lc_memory_objects c4,
estd_lc_time_saved c5,
estd_lc_time_saved_factor c6,
estd_lc_memory_object_hits c7
FROM
v$shared_pool_advice;
SELECT shared_pool_size_for_estimate "Size of Shared Pool in MB",
shared_pool_size_factor "Size Factor",
estd_lc_time_saved "Time Saved in sec"
FROM v$shared_pool_advice;
-- ************************************************
-- Display pga target advice
-- ************************************************
column c1 heading 'Target(M)'
column c2 heading 'Estimated|Cache Hit %'
column c3 heading 'Estimated|Over-Alloc.'
SELECT
ROUND(pga_target_for_estimate /(1024*1024)) c1,
estd_pga_cache_hit_percentage c2,
estd_overalloc_count c3
FROM
v$pga_target_advice;
-- ************************************************
-- Display pga target advice histogram
-- ************************************************
SELECT
low_optimal_size/1024 "Low(K)",
(high_optimal_size+1)/1024 "High(K)",
estd_optimal_executions "Optimal",
estd_onepass_executions "One Pass",
estd_multipasses_executions "Multi-Pass"
FROM
v$pga_target_advice_histogram
WHERE
pga_target_factor = 2
AND
estd_total_executions != 0
ORDER BY
1;
Comments
Post a Comment