'statspack'에 해당되는 글 2건

  1. 2010.03.23 Statspack 생성/삭제/Sanpshot생성
  2. 2009.12.17 Statspack Report 간단 분석 방법
ORACLE/TUNING2010. 3. 23. 13:35

Statspack 생성/삭제/Sanpshot생성

반응형
출처 : http://www.urbantree.wo.tc/entry/5-Using-

Statspack 생성

SQL> shutdown abort
SQL> startup

SQL> !ls $ORACLE_HOME/rdbms/admin/sp*
...
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spauto.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spcpkg.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spcreate.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spctab.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spcusr.sql
...

SQL> @?/rdbms/admin/spcreate.sql
..
Enter value for perfstat_password : perfstat
Enter value for default_tablespace : enter
Enter value for temporary_tablespace : enter
..


Statspack 생성 확인

SQL> show user
USER is "PERFSTAT"

SQL> col object_name for a40
SQL> select object_name, object_type from user_objects order by 2;
....
OBJECT_NAME                              OBJECT_TYPE
---------------------------------------- -------------------
V_$DATAGUARD_STATUS                      VIEW
V_$THREAD                                VIEW
V_$PROCESS                               VIEW
V_$BGPROCESS                             VIEW
V_$SESSION                               VIEW
V_$LICENSE                               VIEW
V_$TRANSACTION                           VIEW
V_$BSP                                   VIEW
V_$FAST_START_SERVERS                    VIEW
WEEKNIGHT_WINDOW                         WINDOW
WEEKEND_WINDOW                           WINDOW
MAINTENANCE_WINDOW_GROUP                 WINDOW GROUP
...


수동 Snapshot 생성

SQL> select * from stats$sga;
SQL> exec statspack.snap
SQL> col name for a40
SQL> select * from stats$sga;
   SNAP_ID       DBID INSTANCE_NUMBER NAME                                          VALUE STARTUP_T PAR VERSION
---------- ---------- --------------- ---------------------------------------- ---------- --------- --- -----------------
         1 1235361507               1 Fixed Size                                  1218992
         1 1235361507               1 Variable Size                             100664912
         1 1235361507               1 Database Buffers                          180355072
         1 1235361507               1 Redo Buffers                                2973696

4 rows selected.

SQL> variable no number
SQL> exec :no :=statspack.snap
SQL> print no


자동 Snapshot 생성

SQL> !ls $ORACLE_HOME/rdbms/admin/sp*
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spauto.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spcpkg.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spcreate.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spctab.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spcusr.sql
....

SQL> !more $ORACLE_HOME/rdbms/admin/spauto.sql
...
variable jobno number;
variable instno number;
begin
  select instance_number into :instno from v$instance;
  dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')'
, TRUE, :instno);
  commit;
end;
/
...


Report 생성

SQL> !ls $ORACLE_HOME/rdbms/admin/sp*
...
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sprepcon.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sprepins.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spreport.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sprepsql.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sprsqins.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sptrunc.sql
...

SQL> @?/rdbms/admin/spreport.sql
...

Listing all Completed Snapshots

                                                       Snap
Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
orcl         ORCL                 1 15 Jan 2010 14:16     5
                                 11 15 Jan 2010 14:29     5

※ snap level 이 커질수록 내용이 더욱 자세하다

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 11
End   Snapshot Id specified: 11



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_11.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: enter

...

End of Report ( sp_1_11.lst )

SQL> !ls sp*
SQL> !more sp_1_11.lst


Statspack 삭제

SQL> !ls $ORACLE_HOME/rdbms/admin/sp*
...
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spdoc.txt
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spdrop.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spdtab.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spdusr.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sppurge.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sprepcon.sql
/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/sprepins.sql
...

SQL> @?/rdbms/admin/spdrop.sql
반응형
저작자표시 비영리

'ORACLE > TUNING' 카테고리의 다른 글

AWR - Monitoring & Tuning  (0) 2010.03.23
AWR (Automatic Workload Repository)  (0) 2010.03.23
[Oracle 물리설계] 제5부 영역감시  (0) 2010.02.11
Oracle Hidden Parameter 란  (0) 2010.01.26
FAST_START_MTTR_TARGET  (0) 2010.01.18
Posted by [PineTree]
ORACLE/TUNING2009. 12. 17. 16:26

Statspack Report 간단 분석 방법

반응형
5. 리포트 분석

5.1 Summary Information

Database    DB Id    Instance     Inst Num Startup Time    Release     RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
          3909019673 ora10g              1 14-5월 -06 20:35 10.2.0.1.0  NO
                                           
 
Host  Name:   STORM-NOTEBOOK   Num CPUs:    1        Phys Memory (MB):    1,022
~~~~
 
Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- -------------------
Begin Snap:          1 14-5월 -06 22:03:11      16       3.8
  End Snap:         12 14-5월 -06 22:07:29      16       5.3
   Elapsed:                4.30 (mins)
 
 - 이 부분에는 database ID 및 이름, instance 이름, version 과 같이 statspack report가 수집된 instance에 대한 정보와 report에 이용된 snapshot 정보를 제공합니다.
 

 
5.2 Load Profile

                                  Per Second(초당)  Per Transaction(트랜잭션당)
~~~~~~~~~~~~                       ---------------       ---------------
                  Redo size:              4,402.51             81,132.00
              Logical reads:                 58.97              1,086.79
              Block changes:                  7.77                143.14
             Physical reads:                  0.17                  3.07
            Physical writes:                  1.31                 24.07
                 User calls:                  0.43                  7.93
                     Parses:                  6.58                121.29
                Hard parses:                  0.89                 16.36
                      Sorts:                  5.30                 97.71
                     Logons:                  0.03                  0.64
                   Executes:                 11.62                214.21
               Transactions:                  0.05
 
  % Blocks changed per Read:   13.17    Recursive Call %:    99.72
 Rollback per transaction %:    0.00       Rows per Sort:    10.63

 
 - 이 부분에서는 snapshot interval 사이의 시스템의 workload(작업부하)가 얼마나 되는지를 설명해 주는 부분입니다.
 
 - 다른 스냅샷간에서 작성된 2개 이상의 리포트를 사용하여 업무량을 비교 할 때 유효합니다.
 
 - 즉, Redo 발생 량 및 logical & physical block IO 량 및 parse, sort 정보를 초/트랜잭션 별로 제공을 하고 있어 이 내용을 비교하여 시간대별 작업량의 변동 및 타 시스템과의 workload 비교가 가능합니다.
 
 - Redo size、Block changes、%Blocks changed per read 이 현저하게 증가한 경우라 한다면 insert/update/delete처리가 보다 많이 행해졌다는 것이 된다.
 

 
5.3 Instance Efficiency Percentages (인스턴스 효율)

            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:   99.72    In-memory Sort %:  100.00
            Library Hit   %:   80.63        Soft Parse %:   86.51
         Execute to Parse %:   43.38         Latch Hit %:  100.00
Parse CPU to Parse Elapsd %:   63.41     % Non-Parse CPU:   78.33
 
 
 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   77.87   81.52
    % SQL with executions>1:   68.00   75.73
  % Memory for SQL w/exec>1:   87.01   81.60

   
 이 부분은 시스템 성능 진단 과정에서 어떤 부분에 문제가 있는지를 판별 할 수 있는 정보를 줍니다.
 
  - Buffer nowait
     .process가 buffer 위하여 기다리지 않고 바로 얻은 비율로 다른 process에 의하여 block이 읽기가 마치기를 기다리거나 incompatible mode에 있어 기다린 횟수가 많은 경우 이 값이 떨어지게 됩니다.
  
  - Buffer hit
    .buffer cache hit ratio입니다.
    .Hit Ratio 는 60~70% 이상이어야 하며 수치가 적을 때는 db_cache_size를 점검 해야 합니다.
 
  - Redo Nowait
    .만약 이 비율이 99% 이하의 경우 아래의 내용들을 의심하여 볼 수 있습니다.
     redo log Buffer/File의 크기가 너무 작지 않은가?
     buffer cache에 dirty buffer가 너무 많이 유지되고 있진 않는가?
  
  - In-memory Sort
    .index 생성, sort morge join, order by, group by , 기타 등등의 sort작업시 disk sort 대 memory sort 비율 입니다.
 
  - Library Hit
    .Library Cache의 Hit Ratio는 90%이상이 되어야 합니다.
    .90% 이하이면 Shared Pool Size를 늘려주거나, SQL 문의 이상을 조사해야 합니다.
 
  - Memory Usage % ： 사용된 Shared Pool의 비율
 
  - % SQL with executions>1 ：재 사용된 SQL문 비율
 
  - % Memory for SQL w/exec>1：２회이상 실행된 SQL이 사용한 메모리 비율

 
 
5.4 Top 5 Wait Events

Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
CPU time                                                         5          76.7
control file sequential read                       527           1      1    9.6
db file parallel write                             184           0      2    5.1
db file sequential read                             43           0      5    3.3
control file parallel write                         86           0      2    3.1
          -------------------------------------------------------------
 

 
 이 부분은 시스템이 가진 두드러진 문제점을 간접적으로 설명해 주는 top 5 wait event 정보입니다.
Wait event는 session이 어디서 얼마나 오랫동안 멈춰 있었는지를 설명하는 정보로 가장 문제가 되는 top 5 wait event들에 대한 정보를 제공 합니다.

 
5.4.1 CPU time
 
   - Response Time = Service Time + Wait Time
   - Wait Time = the sum of time spent on Wait Events
   - Service Time = CPU used by this session = CPU Parse + CPU Recursive + CPU Other
   
   - 만약 시스템이 높은 CPU time을 보인다면 statspack report의 ’SQL by Gets section’ 에서 buffer 를 많이 사용하는 SQL문을 대상으로 tuning작업을 하여 Service time을 줄일 수 있습니다.

   - 만약 CPU time에 비하여 높은 wait time을 보이는 경우 시스템은 resource contention이 있다는 것을 의미하며, 높은 wait event를 보이는 부분부터 wait time을 줄임으로써 시스템 전반적인 Response time을 줄일수 있습니다.


5.4.2 Common Wait Event Problem Areas
 5.4.2.1 buffer busy wait
  
   - buffer busy wait event는 oracle process가 사용중인 buffer를 기다리는 상태에서 가지게 되는 이벤트 입니다.
 
   - 일반적으로 buffer busy wait이 심한 경우 hot block에 의한 현상이거나 IO 상의 bottleneck 으로 인한 경우가 가장 흔합니다.\
 
   - 사실 buffer busy wait에 대한 가장 효과적인 해결책은 buffer cache tuning과 SQL tuning이라고 할 수 있습니다.
 
5.4.2.2 direct path write
  
   - Buffer cache를 거치지 않고 PGA의 buffer에서 바로 datafile로 write하는 작업 중 write 요청이 완료되기를 기다리고 있는 상태를 의미합니다.
 
   - 예를 들어 Disk Sort, hash join, Parallel DML operation, direct path insert 등과 같은 작업 시 write complete가 되기를 기다리는 경우 ’direct path write’ wait 상태에 있게 됩니다.

 
5.4.2.3 Log file sync
  
   - Log file sync는 oracle 이 commit 발행 시 관련된 redo record가 buffer에서 redo logfile에 flush 되는 동안 가지게 되는 wait event로 너무 많은 commit request가 있거나 LGWR의 IO 작업이 원활하지 않는 경우 이러한 현상이 발생할 수 있습니다.
 
   - redo logfile과 datafile 및 archive 파일을 분리하여 IO를 분산하거나, 가급적 redologfile을  IO 성능 개선을 유도할 수 있는 장치를 사용한다.
 

5.4.2.4 DB File Scattered Read
 
   - 일반적으로 FULL 테이블 스캔과 관련된 대기를 나타냅니다
 
   - 여기서 대기 개수가 많다는 것은 index가 존재하지 않아 full table scan을 하고 있는지를 확인하여 보아야 하며 index가 존재하더라도 부정확한 통계정보로 인하여 full table scan을 하고 있는지를 확인하여 보아야 합니다.
 

5.4.2.5 DB File Sequential Read
  
   - DB File Sequential Read 는 index 의 rowid 정보를 이용하여 data block을 access할 때 발생 할 수 있는 wait event로 read block은 1개가 됩니다.
 
   - 이 wait event가 심한 경우 아래 사항들을 점검하여 조치하도록 합니다.
    .낮은 Buffer cache hit ratio
    .많은 Data update 작업 후 변경되지 않은 table, index statistics
    .Buffer gets가 높은 SQL 문장에 대한 tuning 여부
    .Partitioning 기법 고려
    .많은 chained rows.
 
 
 
5.5 SQL문에 대한 통계정보
 
  - Statspack Report에는 resource를 많이 사용한 SQL문장 들을 아래와 같이 resource별로 정리하여 주는 section을 제공하고 있어 SQL tuning 대상 선정에 도움을 주고 있습니다.
  - 스냅샷 레벨 0에서는 아래 정보들이 생성되지 않습니다.
 
- SQL Ordered by CPU Time :  CPU를 많이 사용한 문장.
    
- SQL Ordered by Elapsed Time : 실행 시간을 많이 사용한 문장
  
- SQL ordered by Gets : Buffer를 많이 사용한 문장
           
- SQL ordered by Reads : Disk IO를 많이 한 문장
  
- SQL ordered by Executions : 수행 횟수가 많은 문장
    
  - SQL ordered by Parse Calls : soft parse calls
  
- SQL ordered by Sharable Memory :  Library cache 내 많은 memory를 사용하고 있는 문장
 

참고문서
시스템성능 진단을 위한 Statspack 사용방법   - 한국오라클 (주) 제품지원실 이상헌 -
http://blog.naver.com/hirokorea?Redirect=Log&logNo=20023344842

  ================================================
    * 데이터베이스 정보공유 커뮤니티 oracleclub.com
    * 강좌 작성자 : 김정식 (oramaster _at_ naver.com)
  ================================================
※ oracleclub 강좌를 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
※ oracleclub 강좌는 개인의 학습용으로만 사용 할 수 있습니다. 학원 홍보용이나 수익을 얻기 위한 용도로
    사용을 하시면 안됩니다. ^^
반응형
저작자표시 비영리

'ORACLE > TUNING' 카테고리의 다른 글

FAST_START_MTTR_TARGET  (0) 2010.01.18
EXPLAIN PLAN(실행계획) 이란  (0) 2010.01.12
cursor_sharing 파라미터에 대한 테스트  (0) 2009.12.16
Literal SQL 조회하는 방법  (0) 2009.12.05
DML 과 PARALLEL의 관계  (0) 2009.11.06
Posted by [PineTree]

티스토리툴바