Oracle Advisor 관련 오라클 메모리 조회 SQL

—  PGA target Advsior 보기

— estd_overalloc_count 가 0인 가장 작은 target_MB 가 적절한 PGA_target 이 된다.

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

  1  select ROUND(pga_target_for_estimate/1024/1024) TARGET_MB,
  2         estd_pga_cache_hit_percentage CACHE_HIT_PERC,
  3         estd_overalloc_count
  4* from   V$PGA_TARGET_ADVICE
SQL> /

 TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
        28             94                  223
        55             97                  107
       110             99                    0
       165            100                    0
       220            100                    0
       264            100                    0
       308            100                    0
       352            100                    0
       396            100                    0
       440            100                    0
       660            100                    0
       880            100                    0
      1320            100                    0
      1760            100                    0

14 rows selected.

— Tablespace별 사용량 보기

select * from dba_tablespace_usage_metrics ;

SQL> select * from dba_tablespace_usage_metrics ;

TABLESPACE_NAME                USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
AMD_TXN_INDX_TS                       128         4194302   .003051759
AMD_TXN_TS                            160         4194302   .003814699
EXAMPLE                             10080         4194302   .240326042
SYSAUX                              95432         4194302   2.27527727
SYSTEM                              89288         4194302   2.12879282
TEMP                                    0         4194302            0
UNDOTBS1                              288         4194302   .006866458
USERS                                 528         4194302   .012588507

8 rows selected.

SQL>

— Advisor 수행 task보기

select * from DBA_ADVISOR_TASKS order by execution_end desc ;

select * from DBA_ADVISOR_TASKS order by execution_end desc ;

— DB cache advisor 보기

SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads

FROM V$DB_CACHE_ADVICE

WHERE name = ‘DEFAULT’

AND block_size = (SELECT value FROM V$PARAMETER WHERE name = ‘db_block_size’)

AND advice_status = ‘ON’ ;

  1  SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads
  2     FROM V$DB_CACHE_ADVICE
  3     WHERE name          = 'DEFAULT'
  4       AND block_size    = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')
  5*      AND advice_status = 'ON'
SQL> /

SIZE_FOR_ESTIMATE BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS
----------------- -------------------- ------------------------- -------------------
               12                 1473                    3.9292             6446307
               24                 2946                    2.4672             4047698
               36                 4419                    1.8712             3069979
               48                 5892                    1.5137             2483457
               60                 7365                    1.3674             2243388
               72                 8838                    1.2782             2097055
               84                10311                    1.2169             1996428
               96                11784                    1.1666             1913892
              108                13257                    1.1217             1840312
              120                14730                     1.076             1765383
              132                16203                    1.0347             1697640
              144                17676                         1             1640633
              156                19149                     .9623             1578808
              168                20622                     .9229             1514217
              180                22095                     .8915             1462560
              192                23568                     .8616             1413646
              204                25041                      .833             1366728
              216                26514                     .8061             1322461
              228                27987                     .7825             1283739
              240                29460                     .7415             1216529

20 rows selected.

— Redo advisor 보기

— Redo log advisor

— init.ora에 Fast_start_mttr_target이 지정되어 있어야 한다.

select target_mttr, estimated_mttr, writes_mttr, optimal_logfile_size from v$instance_recovery;

SQL> select target_mttr, estimated_mttr, writes_mttr, optimal_logfile_size from v$instance_recovery;

TARGET_MTTR ESTIMATED_MTTR WRITES_MTTR OPTIMAL_LOGFILE_SIZE
----------- -------------- ----------- --------------------
          0             14           0

— Segment advisor 수행 흔적 보기

SQL> select * from DBA_AUTO_SEGADV_SUMMARY order by start_time desc;

AUTO_TASKID     SNAPID SEGMENTS_SELECTED SEGMENTS_PROCESSED TABLESPACE_SELECTED TABLESPACE_PROCESSED RECOMMENDATIONS_COUNT START_TIME                                                           END_TIME
----------- ---------- ----------------- ------------------ ------------------- -------------------- --------------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
        151       1951                 0                  6                   0                    0                     0 09-APR-18 10.00.01.199458 PM                                         09-APR-18 10.00.09.011127 PM
        150       1928                 0                  2                   0                    0                     0 08-APR-18 10.08.08.235659 PM                                         08-APR-18 10.08.09.596020 PM
        149       1924                 0                  4                   0                    0                     0 08-APR-18 06.07.54.062494 PM                                         08-APR-18 06.07.55.448212 PM
        148       1920                 0                  2                   0                    0                     0 08-APR-18 02.07.40.162909 PM                                         08-APR-18 02.07.42.742713 PM
        147       1916                 0                  0                   0                    0                     0 08-APR-18 10.07.26.252358 AM                                         08-APR-18 10.07.28.196305 AM
        146       1912                 0                  5                   0                    0                     0 08-APR-18 06.00.02.534671 AM                                         08-APR-18 06.00.04.779501 AM
        145       1904                 0                  4                   0                    0                     0 07-APR-18 10.06.42.617316 PM                                         07-APR-18 10.06.44.426401 PM
        144       1900                 0                  3                   0                    0                     0 07-APR-18 06.06.28.142453 PM                                         07-APR-18 06.06.30.100061 PM
        143       1896                 0                  2                   1                    1                     0 07-APR-18 02.06.14.664473 PM                                         07-APR-18 02.06.22.068583 PM
        142       1892                 0                  2                   0                    0                     0 07-APR-18 10.06.00.846257 AM                                         07-APR-18 10.06.02.770400 AM
        141       1887                 0                  4                   0                    0                     0 07-APR-18 06.00.02.360853 AM                                         07-APR-18 06.00.05.444056 AM
        140       1879                 0                  5                   0                    0                     0 06-APR-18 10.00.02.069407 PM                                         06-APR-18 10.00.07.641728 PM
        139       1855                 0                  5                   0                    0                     0 05-APR-18 10.00.03.522520 PM                                         05-APR-18 10.00.07.597868 PM
        138       1831                 0                  5                   0                    0                     0 04-APR-18 10.00.03.682236 PM                                         04-APR-18 10.00.07.970287 PM
        137       1807                 0                  5                   0                    0                     0 03-APR-18 10.00.10.207422 PM                                         03-APR-18 10.00.28.156111 PM
        136       1783                 0                  5                   0                    0                     0 02-APR-18 10.00.03.339729 PM                                         02-APR-18 10.00.06.276546 PM

16 rows selected.

SQL>

— Segment advisor 수행 권고 보기

— reclaimable_space (tablespace level로 확인 하기)

SQL> select tablespace_name, allocated_space, reclaimable_space,
       trunc(reclaimable_space/allocated_space,2)*100 reclaimable_pct
from   table(dbms_space.asa_recommendations('TRUE', 'TRUE', 'ALL'))
order by reclaimable_pct desc;

— Segment advisor 수행권고 보기 ( Table 별 )

select trunc(reclaimable_space/allocated_space,2) reclaimable_pct,

trunc(reclaimable_space/allocated_space,2) reclaim_raito,

Segment_owner,segment_name,Segment_type,Partition_name, Allocated_space,

Used_space,Reclaimable_space,Chain_rowexcess chian_ratio,substr(Recommendations,1,40) recommendations

from table (dbms_space.asa_recommendations()) a

order by trunc(reclaimable_space/allocated_space,2) desc,to_number(reclaimable_space) desc ;

SQL> l
  1  select trunc(reclaimable_space/allocated_space,2) reclaimable_pct,
  2  trunc(reclaimable_space/allocated_space,2) reclaim_raito,
  3  Segment_owner,segment_name,Segment_type,Partition_name, Allocated_space,
  4  Used_space,Reclaimable_space,Chain_rowexcess chian_ratio,substr(Recommendations,1,40) recommendations
  5  from table (dbms_space.asa_recommendations()) a
  6* order by trunc(reclaimable_space/allocated_space,2) desc,to_number(reclaimable_space) desc

— Auto sga일경우, memory size 변동 결과 보기.

select * from V$SGA_RESIZE_OPS;

SQL> select * from V$SGA_RESIZE_OPS;

COMPONENT                                                        OPER_TYPE     OPER_MODE PARAMETER                                                                        INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS      START_TIME         END_TIME
---------------------------------------------------------------- ------------- --------- -------------------------------------------------------------------------------- ------------ ----------- ---------- --------- ------------------ ------------------
shared pool                                                      STATIC                  shared_pool_size                                                                            0   255852544  255852544 COMPLETE    05-MAR-18          05-MAR-18
large pool                                                       STATIC                  large_pool_size                                                                             0     4194304    4194304 COMPLETE    05-MAR-18          05-MAR-18
java pool                                                        STATIC                  java_pool_size                                                                              0     4194304    4194304 COMPLETE    05-MAR-18          05-MAR-18
streams pool                                                     STATIC                  streams_pool_size                                                                           0     8388608    8388608 COMPLETE    05-MAR-18          05-MAR-18
DEFAULT buffer cache                                             INITIALIZING            db_cache_size                                                                       150994944   150994944  150994944 COMPLETE    05-MAR-18          05-MAR-18
ASM Buffer Cache                                                 STATIC                  db_cache_size                                                                               0    0           0 COMPLETE  05-MAR-18          05-MAR-18
RECYCLE buffer cache                                             STATIC                  db_recycle_cache_size                                                                       0    0           0 COMPLETE  05-MAR-18          05-MAR-18
DEFAULT 2K buffer cache                                          STATIC                  db_2k_cache_size                                                                            0    0           0 COMPLETE  05-MAR-18          05-MAR-18
DEFAULT 4K buffer cache                                          STATIC                  db_4k_cache_size                                                                            0    0           0 COMPLETE  05-MAR-18          05-MAR-18
DEFAULT 8K buffer cache                                          STATIC                  db_8k_cache_size                                                                            0    0           0 COMPLETE  05-MAR-18          05-MAR-18
DEFAULT 16K buffer cache                                         STATIC                  db_16k_cache_size                                                                           0    0           0 COMPLETE  05-MAR-18          05-MAR-18
DEFAULT 32K buffer cache                                         STATIC                  db_32k_cache_size                                                                           0    0           0 COMPLETE  05-MAR-18          05-MAR-18
KEEP buffer cache                                                STATIC                  db_keep_cache_size                                                                          0    0           0 COMPLETE  05-MAR-18          05-MAR-18
DEFAULT buffer cache                                             STATIC                  db_cache_size                                                                               0   150994944  150994944 COMPLETE    05-MAR-18          05-MAR-18

14 rows selected.

By haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

7 thoughts on “Oracle Advisor 관련 오라클 메모리 조회 SQL”

답글 남기기

이메일 주소를 발행하지 않을 것입니다. 필수 항목은 *(으)로 표시합니다