AWR(Automatic Workload Repository) 이란
- Oracle Database 10g 의 내장 저장소.
- 일정한 주기로 모든 주요 통계와 작업 로드 정보에 대한 스냅샷을 만들어 AWR 에 저장함.
-> 수집된 정보로 보고서나 뷰를 만들수 있음.
-> Default 1회 캡처 7일간 보존
- AWR을 이용하여 분석, 튜닝을 위한 데이터베이스 성능 통계정보와 메트릭을 수집.
- 데이터베이스 사용한 정확한 시간을 확인하거나 세션 정보를 저장할 수 있음.
AWR 환경 구성 방법
1) $ORACLE_HOME/rdbms/admin/awrrpt.sql 스크립트를 실행후.
2) 수집된 통계정보와 메트릭 정보를 바탕으로 생선된 리포트를 확인 (HTML, TEXT)
3) DAY 입력
4) 시작 끝 SNAP ID 입력
수집 데이터.
1. DB 대기 이벤트 및 통계정보 : 경합, 메모리, 세그먼트 락, 등...
2. 시스템 통계 정보 : CPU, 디스크, 네트워크
3. 데이터베이스 부하 정보 : 그룹화된 DB 사용 부하의 통계 수치
4. SQL 수행 정보 : SQL 통계 정보 및 실행 계획 정보
5. 활동 세션 정보 : ASH(Active Session History) 를 이요한 해당 스냅샷 시점의 세션 정보 파악
*
AWR 스냅샷 관리
AWR 스냅샷 이란
메모리에서 디스크로 저장하는 시점의 AWR 데이터를 의미함
오라클에서 AWR 스냅샷을 관리하기 위해 DBMS_WORKLOAD_REPOSITORY 패키지를 제공.
DBMS_WORKLOAD_REPOSITORY 패키지
1) 스냅샷 삭제
2) 스냅샷 설정 변경
3) 베이스라인 설정
4) 베이스라인 삭제
*
AWR 데이터 백업 및 복구
AWR 관리 주기 및 테이블스페이스 공간 부족 등으로.. AWR데이터를 삭제 할 경우 => AWR 백업 필요.
이때 awrextr.sql 의 스크립트를 수행하면 데이터펌프를 이용하여 AWR 데이터를 덤프 파일로 저장 할 수 있음.
1. AWR 데이터 백업
$ORACLE_HOME/rdbms/admin/awrextr.sql
[oracle@DBTEST admin]$ ls -al awrextr.sql
-rwxr-x--- 1 oracle oinstall 11082 Mar 24 2009 awrextr.sql
SYS@ORCL AS SYSDBA> @awrextr.sql
~~~~~~~~~~~~~
AWR EXTRACT
~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ This script will extract the AWR data for a range of snapshots ~
~ into a dump file. The script will prompt users for the ~
~ following information: ~
~ (1) database id ~
~ (2) snapshot range to extract ~
~ (3) name of directory object ~
~ (4) name of dump file ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Databases in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id DB Name Host
------------ ------------ ------------
* 1427591565 ORCL DBTEST
The default database id is the local one: '1427591565'. To use this
database id, press <return> to continue, otherwise enter an alternative.
Enter value for dbid: 1427591565 << DB ID입력
Using 1427591565 for Database ID
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 1 << 입력된 날짜의 스냅샷을 봄
Listing the last day's Completed Snapshots
DB Name Snap Id Snap Started
------------ --------- ------------------
ORCL 2980 11 Apr 2016 00:00
2981 11 Apr 2016 00:20
2982 11 Apr 2016 00:40
2983 11 Apr 2016 01:00
2984 11 Apr 2016 01:20
2985 11 Apr 2016 01:40
2986 11 Apr 2016 02:00
2987 11 Apr 2016 02:20
2988 11 Apr 2016 02:40
2989 11 Apr 2016 03:00
2990 11 Apr 2016 03:20
2991 11 Apr 2016 03:40
2992 11 Apr 2016 04:00
2993 11 Apr 2016 04:20
2994 11 Apr 2016 04:40
2995 11 Apr 2016 05:00
2996 11 Apr 2016 05:20
2997 11 Apr 2016 05:40
2998 11 Apr 2016 06:00
2999 11 Apr 2016 06:20
3000 11 Apr 2016 06:40
3001 11 Apr 2016 07:00
3002 11 Apr 2016 07:20
3003 11 Apr 2016 07:40
3004 11 Apr 2016 08:00
3005 11 Apr 2016 08:20
3006 11 Apr 2016 08:40
3007 11 Apr 2016 09:00
3008 11 Apr 2016 09:20
3009 11 Apr 2016 09:40
3010 11 Apr 2016 10:00
3011 11 Apr 2016 10:20
3012 11 Apr 2016 10:40
3013 11 Apr 2016 11:00
3014 11 Apr 2016 11:20
3015 11 Apr 2016 11:40
3016 11 Apr 2016 12:00
3017 11 Apr 2016 12:20
3018 11 Apr 2016 12:40
3019 11 Apr 2016 13:00
3020 11 Apr 2016 13:20
3021 11 Apr 2016 13:40
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 3015 << 펌프받을 시작 Snap Id 입력.
Begin Snapshot Id specified: 3015
Enter value for end_snap: 3020 << 펌프받을 마지막 Snap Id 입력.
End Snapshot Id specified: 3020
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name Directory Path
------------------------------ -------------------------------------------------
DATA_FILE_DIR /u01/app/oracle/product/11.2.0/demo/schema/sales_
history/
DATA_PUMP_DIR /u01/app/oracle/product/11.2.0/rdbms/log/
IMPDP_TEST /home/oracle/dpdump/
LOG_FILE_DIR /u01/app/oracle/product/11.2.0/demo/schema/log/
MEDIA_DIR /u01/app/oracle/product/11.2.0/demo/schema/produc
t_media/
ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/ccr/state
SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/demo/schema/order_
entry/
SUBDIR /u01/app/oracle/product/11.2.0/demo/schema/order_
entry//2002/Sep
TEST_DPUMP /home/oracle/dpdump
TEST_DPUMP_2 /home/oracle/dpdump
TEST_IMPDP /home/oracle/dpdump/impdp
XMLDIR /ade/b/2125410156/oracle/rdbms/xml
Choose a Directory Name from the above list (case-sensitive).
Enter value for directory_name: TEST_DPUMP << 펌프 위치 입력
Using the dump directory: TEST_DPUMP
Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_3015_3020.
To use this name, press <return> to continue, otherwise enter
an alternative.
Enter value for file_name: awr_pump_3015_3020 << 펌프 파일 입력
Using the dump file prefix: awr_pump_3015_3020
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| The AWR extract dump file will be located
| in the following directory/file:
| /home/oracle/dpdump
| awr_pump_3015_3020.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
| *** AWR Extract Started ...
|
| This operation will take a few moments. The
| progress of the AWR extract operation can be
| monitored in the following directory/file:
| /home/oracle/dpdump
| awr_pump_3015_3020.log
|
End of AWR Extract
** 확인
[oracle@DBTEST dpdump]$ ls -al awr_pump_3015_3020.dmp
-rw-r----- 1 oracle oinstall 10416128 Apr 11 13:59 awr_pump_3015_3020.dmp
* AWR 데이터 복구
[oracle@DBTEST admin]$ ls -al awrload.sql
-rwxr-x--- 1 oracle oinstall 10094 Mar 24 2009 awrload.sql
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> @awrload.sql
~~~~~~~~~~
AWR LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ This script will load the AWR data from a dump file. The ~
~ script will prompt users for the following information: ~
~ (1) name of directory object ~
~ (2) name of dump file ~
~ (3) staging schema name to load AWR data into ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name Directory Path
------------------------------ -------------------------------------------------
DATA_FILE_DIR /u01/app/oracle/product/11.2.0/demo/schema/sales_
history/
DATA_PUMP_DIR /u01/app/oracle/product/11.2.0/rdbms/log/
IMPDP_TEST /home/oracle/dpdump/
LOG_FILE_DIR /u01/app/oracle/product/11.2.0/demo/schema/log/
MEDIA_DIR /u01/app/oracle/product/11.2.0/demo/schema/produc
t_media/
ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/ccr/state
SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/demo/schema/order_
entry/
SUBDIR /u01/app/oracle/product/11.2.0/demo/schema/order_
entry//2002/Sep
TEST_DPUMP /home/oracle/dpdump
TEST_DPUMP_2 /home/oracle/dpdump
TEST_IMPDP /home/oracle/dpdump/impdp
XMLDIR /ade/b/2125410156/oracle/rdbms/xml
Choose a Directory Name from the list above (case-sensitive).
Enter value for directory_name: TEST_DPUMP << 복구할 dmp 파일 위치 Directory 입력
Using the dump directory: TEST_DPUMP
Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:
Enter value for file_name: awr_pump_3015_3020 << 덤프 파일 입력 (.dmp 생략)
Loading from the file name: awr_pump_3015_3020.dmp
Staging Schema to Load AWR Snapshot Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The next step is to create the staging schema
where the AWR snapshot data will be loaded.
After loading the data into the staging schema,
the data will be transferred into the AWR tables
in the SYS schema.
The default staging schema name is AWR_STAGE.
To use this name, press <return> to continue, otherwise enter
an alternative.
Enter value for schema_name: << 사용할 스키마 이름 선정 (enter 시 AWR_STAGE )
Using the staging schema name: AWR_STAGE << AWR_STAGE
Choose the Default tablespace for the AWR_STAGE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWR_STAGE users's default tablespace. This is the
tablespace in which the AWR data will be staged.
TABLESPACE_NAME CONTENTS DEFAULT TABLESPACE
------------------------------ --------- ------------------
DATA01 PERMANENT
PBJ_TEMP PERMANENT
RC_TBS01 PERMANENT
SYSAUX PERMANENT *
TBS_A PERMANENT
TBS_B PERMANENT
TBS_C PERMANENT
TBS_D PERMANENT
TBS_RMAN PERMANENT
TBS_SCOTT_IDX PERMANENT
TBS_TEST PERMANENT
USERS PERMANENT
Pressing <return> will result in the recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace: PBJ_TEMP << 복구할 테이블스페이스 선택
Using tablespace PBJ_TEMP as the default tablespace for the AWR_STAGE
Choose the Temporary tablespace for the AWR_STAGE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWR_STAGE user's temporary tablespace.
TABLESPACE_NAME CONTENTS DEFAULT TEMP TABLESPACE
------------------------------ --------- -----------------------
TEMP TEMPORARY *
Pressing <return> will result in the database's default temporary
tablespace (identified by *) being used.
Enter value for temporary_tablespace: TEMP << TEMP 선택
Using tablespace TEMP as the temporary tablespace for AWR_STAGE
... Creating AWR_STAGE user << AWR_STAGE 유저로 펌프 실행시 소유자 sys변경됨
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Loading the AWR data from the following
| directory/file:
| /home/oracle/dpdump
| awr_pump_3015_3020.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
| *** AWR Load Started ...
|
| This operation will take a few moments. The
| progress of the AWR load operation can be
| monitored in the following directory/file:
| /home/oracle/dpdump
| awr_pump_3015_3020.log
|
begin
*
ERROR at line 1:
ORA-20105: unable to move AWR data to SYS << 이 에러는 백업시 db id 와 복구시 db id 가 같기 때문에 발생됨.
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 2709 << sys 로 실행되는 것을 확인
ORA-20107: not allowed to move AWR data for local dbid
ORA-06512: at line 3
... Dropping AWR_STAGE user
End of AWR Load
** 백업시 db id 와 복구시 db id 같은 시점에서
awr_stage user => sys 소유로 변경하는 부분에서 에러가 나는 것임.
변경이 되지 않는 데이터는 모두 삭제가 되어짐.
[oracle@DBTEST dpdump]$ vi awr_pump_3015_3020.log << 펌프 로그 확인
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":
Processing object type TABLE_EXPORT/TABLE/TABLE
Completed 128 TABLE objects in 2 seconds
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "AWR_STAGE"."WRH$_SQL_PLAN" 353.6 KB 858 rows
. . imported "AWR_STAGE"."WRH$_SYSMETRIC_HISTORY" 132.2 KB 2400 rows
. . imported "AWR_STAGE"."WRH$_SQLTEXT" 88.31 KB 96 rows
. . imported "AWR_STAGE"."WRH$_SYSMETRIC_SUMMARY" 82.46 KB 948 rows
. . imported "AWR_STAGE"."WRH$_ENQUEUE_STAT" 37.33 KB 546 rows
. . imported "AWR_STAGE"."WRH$_BG_EVENT_SUMMARY" 25.63 KB 504 rows
. . imported "AWR_STAGE"."WRH$_LATCH":"WRH$_LATCH_1427591565_2404" 10.99 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LATCH":"WRH$_LATCH_1427591565_2476" 10.99 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LATCH":"WRH$_LATCH_1427591565_2578" 10.99 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LATCH":"WRH$_LATCH_1427591565_2650" 10.99 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LATCH":"WRH$_LATCH_1427591565_2722" 10.99 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LATCH":"WRH$_LATCH_1427591565_2794" 10.99 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LATCH":"WRH$_LATCH_1427591565_2866" 10.99 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LATCH":"WRH$_LATCH_1427591565_2938" 10.99 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SQLSTAT":"WRH$_SQLSTA_1427591565_2476" 36.64 KB 0 rows
. . imported "AWR_STAGE"."WRH$_EVENT_HISTOGRAM":"WRH$_EVENT__1427591565_2404" 7.015 KB 0 rows
. . imported "AWR_STAGE"."WRH$_EVENT_HISTOGRAM":"WRH$_EVENT__1427591565_2476" 7.015 KB 0 rows
. . imported "AWR_STAGE"."WRH$_EVENT_HISTOGRAM":"WRH$_EVENT__1427591565_2578" 7.015 KB 0 rows
. . imported "AWR_STAGE"."WRH$_EVENT_HISTOGRAM":"WRH$_EVENT__1427591565_2650" 7.015 KB 0 rows
. . imported "AWR_STAGE"."WRH$_EVENT_HISTOGRAM":"WRH$_EVENT__1427591565_2722" 7.015 KB 0 rows
. . imported "AWR_STAGE"."WRH$_EVENT_HISTOGRAM":"WRH$_EVENT__1427591565_2794" 7.015 KB 0 rows
. . imported "AWR_STAGE"."WRH$_EVENT_HISTOGRAM":"WRH$_EVENT__1427591565_2866" 7.015 KB 0 rows
. . imported "AWR_STAGE"."WRH$_EVENT_HISTOGRAM":"WRH$_EVENT__1427591565_2938" 7.015 KB 0 rows
. . imported "AWR_STAGE"."WRH$_PARAMETER":"WRH$_PARAME_1427591565_2404" 7.406 KB 0 rows
. . imported "AWR_STAGE"."WRH$_PARAMETER":"WRH$_PARAME_1427591565_2476" 7.406 KB 0 rows
. . imported "AWR_STAGE"."WRH$_PARAMETER":"WRH$_PARAME_1427591565_2578" 7.406 KB 0 rows
. . imported "AWR_STAGE"."WRH$_PARAMETER":"WRH$_PARAME_1427591565_2650" 7.406 KB 0 rows
. . imported "AWR_STAGE"."WRH$_PARAMETER":"WRH$_PARAME_1427591565_2722" 7.406 KB 0 rows
. . imported "AWR_STAGE"."WRH$_PARAMETER":"WRH$_PARAME_1427591565_2794" 7.406 KB 0 rows
. . imported "AWR_STAGE"."WRH$_PARAMETER":"WRH$_PARAME_1427591565_2866" 7.406 KB 0 rows
. . imported "AWR_STAGE"."WRH$_PARAMETER":"WRH$_PARAME_1427591565_2938" 7.406 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SEG_STAT":"WRH$_SEG_ST_1427591565_2476" 22.77 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SQLSTAT":"WRH$_SQLSTA_1427591565_2404" 36.64 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SQLSTAT":"WRH$_SQLSTA_1427591565_2578" 36.64 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SQLSTAT":"WRH$_SQLSTA_1427591565_2650" 36.64 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SQLSTAT":"WRH$_SQLSTA_1427591565_2722" 36.64 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SQLSTAT":"WRH$_SQLSTA_1427591565_2794" 36.64 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SQLSTAT":"WRH$_SQLSTA_1427591565_2866" 36.64 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SQLSTAT":"WRH$_SQLSTA_1427591565_2938" 36.64 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SYSSTAT":"WRH$_SYSSTA_1427591565_2404" 6.593 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SYSSTAT":"WRH$_SYSSTA_1427591565_2476" 6.593 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SYSSTAT":"WRH$_SYSSTA_1427591565_2578" 6.593 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SYSSTAT":"WRH$_SYSSTA_1427591565_2650" 6.593 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SYSSTAT":"WRH$_SYSSTA_1427591565_2722" 6.593 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SYSSTAT":"WRH$_SYSSTA_1427591565_2794" 6.593 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SYSSTAT":"WRH$_SYSSTA_1427591565_2866" 6.593 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SYSSTAT":"WRH$_SYSSTA_1427591565_2938" 6.593 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LIBRARYCACHE" 16.87 KB 108 rows
. . imported "AWR_STAGE"."WRH$_MEM_DYNAMIC_COMP" 16.13 KB 96 rows
. . imported "AWR_STAGE"."WRH$_SEG_STAT":"WRH$_SEG_ST_1427591565_2404" 22.77 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SEG_STAT":"WRH$_SEG_ST_1427591565_2578" 22.77 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SEG_STAT":"WRH$_SEG_ST_1427591565_2650" 22.77 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SEG_STAT":"WRH$_SEG_ST_1427591565_2722" 22.77 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SEG_STAT":"WRH$_SEG_ST_1427591565_2866" 22.77 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SEG_STAT":"WRH$_SEG_ST_1427591565_2938" 22.77 KB 0 rows
. . imported "AWR_STAGE"."WRH$_IOSTAT_DETAIL" 16.61 KB 114 rows
. . imported "AWR_STAGE"."WRH$_SEG_STAT":"WRH$_SEG_ST_1427591565_2794" 22.77 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SYSTEM_EVENT":"WRH$_SYSTEM_1427591565_2476" 8.632 KB 0 rows
. . imported "AWR_STAGE"."WRH$_TABLESPACE_SPACE_USAGE" 12.19 KB 96 rows
. . imported "AWR_STAGE"."WRH$_IOSTAT_FILETYPE" 16.86 KB 72 rows
. . imported "AWR_STAGE"."WRH$_PGASTAT" 11.09 KB 96 rows
. . imported "AWR_STAGE"."WRH$_PGA_TARGET_ADVICE" 13.53 KB 84 rows
. . imported "AWR_STAGE"."WRH$_IOSTAT_FUNCTION" 14.44 KB 84 rows
. . imported "AWR_STAGE"."WRH$_STREAMS_POOL_ADVICE" 12.13 KB 120 rows
. . imported "AWR_STAGE"."WRH$_SERVICE_STAT":"WRH$_SERVIC_1427591565_2476" 7.007 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SHARED_POOL_ADVICE" 13.27 KB 66 rows
"awr_pump_3015_3020.log" 314L, 28296C 1,1 Top
. . imported "AWR_STAGE"."WRH$_DB_CACHE_ADVICE":"WRH$_DB_CACHE_AD_MXDB_MXSN" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_DB_CACHE_ADVICE_BL" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_DLM_MISC":"WRH$_DLM_MISC_MXDB_MXSN" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_DLM_MISC":"WRH$_DLM_MI_1427591565_0" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_DLM_MISC_BL" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_DYN_REMASTER_STATS" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_EVENT_HISTOGRAM":"WRH$_EVENT_HISTO_MXDB_MXSN" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_EVENT_HISTOGRAM_BL" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_FILEMETRIC_HISTORY" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_FILESTATXS":"WRH$_FILESTATXS_MXDB_MXSN" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_FILESTATXS_BL" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_IC_CLIENT_STATS" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_IC_DEVICE_STATS" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_INST_CACHE_TRANSFER":"WRH$_INST_CACHE_MXDB_MXSN" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_INST_CACHE_TRANSFER":"WRH$_INST_C_1427591565_0" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_INST_CACHE_TRANSFER_BL" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_INTERCONNECT_PINGS":"WRH$_IC_PINGS_MXDB_MXSN" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_INTERCONNECT_PINGS":"WRH$_INTERC_1427591565_0" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_INTERCONNECT_PINGS_BL" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LATCH":"WRH$_LATCH_MXDB_MXSN" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LATCH_BL" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LATCH_CHILDREN":"WRH$_LATCH_CHILD_MXDB_MXSN" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LATCH_CHILDREN":"WRH$_LATCH__1427591565_1935" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LATCH_CHILDREN_BL" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LATCH_MISSES_SUMMARY":"WRH$_LATCH_MISSE_MXDB_MXSN" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LATCH_MISSES_SUMMARY_BL" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LATCH_PARENT":"WRH$_LATCH_PAREN_MXDB_MXSN" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LATCH_PARENT":"WRH$_LATCH__1427591565_1935" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_LATCH_PARENT_BL" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_MEMORY_RESIZE_OPS" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_MTTR_TARGET_ADVICE" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_OSSTAT":"WRH$_OSSTAT_MXDB_MXSN" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_OSSTAT_BL" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_PARAMETER":"WRH$_PARAMETER_MXDB_MXSN" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_PARAMETER_BL" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_PERSISTENT_SUBSCRIBERS" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_ROWCACHE_SUMMARY":"WRH$_ROWCACHE_SU_MXDB_MXSN" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_ROWCACHE_SUMMARY_BL" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SEG_STAT":"WRH$_SEG_STAT_MXDB_MXSN" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SEG_STAT_BL" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SERVICE_STAT":"WRH$_SERVICE_STAT_MXDB_MXSN" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SERVICE_STAT_BL" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SERVICE_WAIT_CLASS":"WRH$_SERVICE_WAIT_MXDB_MXSN" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SERVICE_WAIT_CLASS_BL" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SESSMETRIC_HISTORY" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SGASTAT":"WRH$_SGASTAT_MXDB_MXSN" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SGASTAT_BL" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SQLSTAT":"WRH$_SQLSTAT_MXDB_MXSN" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SQLSTAT_BL" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_STREAMS_APPLY_SUM" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_STREAMS_CAPTURE" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SYSSTAT":"WRH$_SYSSTAT_MXDB_MXSN" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SYSSTAT_BL" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SYSTEM_EVENT":"WRH$_SYSTEM_EVEN_MXDB_MXSN" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SYSTEM_EVENT_BL" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SYS_TIME_MODEL":"WRH$_SYS_TIME_MO_MXDB_MXSN" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_SYS_TIME_MODEL_BL" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_TABLESPACE_STAT":"WRH$_TABLESPACE_MXDB_MXSN" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_TABLESPACE_STAT_BL" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_WAITCLASSMETRIC_HISTORY" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_WAITSTAT":"WRH$_WAITSTAT_MXDB_MXSN" 0 KB 0 rows
. . imported "AWR_STAGE"."WRH$_WAITSTAT_BL" 0 KB 0 rows
. . imported "AWR_STAGE"."WRM$_BASELINE_DETAILS" 0 KB 0 rows
. . imported "AWR_STAGE"."WRM$_BASELINE_TEMPLATE" 0 KB 0 rows
. . imported "AWR_STAGE"."WRM$_COLORED_SQL" 0 KB 0 rows
. . imported "AWR_STAGE"."WRM$_SNAP_ERROR" 0 KB 0 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Completed 121 CONSTRAINT objects in 1 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Completed 1 REF_CONSTRAINT objects in 0 seconds
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 14:28:21
* AWR 보고서 생성.
- 시작 스냅샷, 종료 스냅샷을 설정하여
구간의 이벤트 발생 획수 및 통계치 차이를 계산하여 보여줌.
* 인스턴스가 종료할 때마다 대기 이벤트 및 통계 수치가 재설정됨.
즉, 스냅샷 구간 사이에 인스턴스가 종료된 적이 있다면 의미 없는 수치임.
- 보고서 생성을 위해 권한이 필요함.
- select any directory 권한
- sys.dbms_workload_repository 패키지 실행 권한
* 사용 용도
$ORACLE_HOME/rdbms/admin
AWR DB 보고서 생성 - awrrpt.sql, awrrpti.sql
AWR DB 비교 보고서 생성 - awrddrpt.sql, awrddrpi.sql
AWR SQL 보고서 생성 - awrsqrpt.sql , awrsqrpi.sql
AWR 정보 검색 보고서 - awrinfo.sql
AWR 보고서 생성 - ashrpt.sql, ashrpti.sql
ADDM 보고서 생성 - addmrpt.sql, addmrpti.sql
기타 스크립트 - awrddinp.sql, awrinput.sql, awrinpnmp.sql
* 수행되는 SQL 에 대한 전체구문 및 실행계획과 실행 통계정보 분석.
* SQI ID 부분은 문제가 되는 쿼리의 SQL ID 입력
SELECT * FROM V$SQL;
SYS@ORCL AS SYSDBA> @awrsqrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1427591565 ORCL 1 ORCL
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text << default : html , text 입력
Type Specified: text
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1427591565 1 ORCL ORCL DBTEST
Using 1427591565 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 1 << 스냅샷 일자 입력
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
ORCL ORCL 2980 11 Apr 2016 00:00 1
2981 11 Apr 2016 00:20 1
2982 11 Apr 2016 00:40 1
2983 11 Apr 2016 01:00 1
2984 11 Apr 2016 01:20 1
2985 11 Apr 2016 01:40 1
2986 11 Apr 2016 02:00 1
2987 11 Apr 2016 02:20 1
2988 11 Apr 2016 02:40 1
2989 11 Apr 2016 03:00 1
2990 11 Apr 2016 03:20 1
2991 11 Apr 2016 03:40 1
2992 11 Apr 2016 04:00 1
2993 11 Apr 2016 04:20 1
2994 11 Apr 2016 04:40 1
2995 11 Apr 2016 05:00 1
2996 11 Apr 2016 05:20 1
2997 11 Apr 2016 05:40 1
2998 11 Apr 2016 06:00 1
2999 11 Apr 2016 06:20 1
3000 11 Apr 2016 06:40 1
3001 11 Apr 2016 07:00 1
3002 11 Apr 2016 07:20 1
3003 11 Apr 2016 07:40 1
3004 11 Apr 2016 08:00 1
3005 11 Apr 2016 08:20 1
3006 11 Apr 2016 08:40 1
3007 11 Apr 2016 09:00 1
3008 11 Apr 2016 09:20 1
3009 11 Apr 2016 09:40 1
3010 11 Apr 2016 10:00 1
3011 11 Apr 2016 10:20 1
3012 11 Apr 2016 10:40 1
3013 11 Apr 2016 11:00 1
3014 11 Apr 2016 11:20 1
3015 11 Apr 2016 11:40 1
3016 11 Apr 2016 12:00 1
3017 11 Apr 2016 12:20 1
3018 11 Apr 2016 12:40 1
3019 11 Apr 2016 13:00 1
3020 11 Apr 2016 13:20 1
3021 11 Apr 2016 13:40 1
3022 11 Apr 2016 14:00 1
3023 11 Apr 2016 14:20 1
3024 11 Apr 2016 14:40 1
3025 11 Apr 2016 15:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 2981
Begin Snapshot Id specified: 2981
Enter value for end_snap: 3025
End Snapshot Id specified: 3025
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: ayr8swhks7n2y << 문제의 sql id 찾아서 입력
SQL ID specified: ayr8swhks7n2y
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_2981_3025.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: awrsqlrp_2981_3025.txt
Using the report name awrsqlrp_2981_3025.txt
WORKLOAD REPOSITORY SQL Report
Snapshot Period Summary
DB Name DB Id Instance Inst Num Startup Time Release RAC
------------ ----------- ------------ -------- --------------- ----------- ---
ORCL 1427591565 ORCL 1 29-Mar-16 22:30 11.2.0.1.0 NO
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 2981 11-Apr-16 00:20:13 30 1.4
End Snap: 3025 11-Apr-16 15:00:38 32 2.3
Elapsed: 880.42 (mins)
DB Time: 0.59 (mins)
SQL Summary DB/Inst: ORCL/ORCL Snaps: 2981-3025
Elapsed
SQL Id Time (ms)
------------- ----------
ayr8swhks7n2y 3
Module: DBMS_SCHEDULER
RLM$SCHDNEGACTION
update sys.scheduler$_job set flags = :1, start_date = :2, dist_flags = :3 wher
e obj# = :4
-------------------------------------------------------------
SQL ID: ayr8swhks7n2y DB/Inst: ORCL/ORCL Snaps: 2981-3025
-> 1st Capture and Last Capture Snap IDs
refer to Snapshot IDs witin the snapshot range
-> update sys.scheduler$_job set flags = :1, start_date = :2, dist_flags...
Plan Hash Total Elapsed 1st Capture Last Capture
# Value Time(ms) Executions Snap ID Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1 105323984 3 14 2984 3021
-------------------------------------------------------------
Plan 1(PHV: 105323984)
----------------------
Plan Statistics DB/Inst: ORCL/ORCL Snaps: 2981-3025
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 3 0.2 0.0
CPU Time (ms) 3 0.2 0.0
Executions 14 N/A N/A
Buffer Gets 48 3.4 0.0
Disk Reads 0 0.0 0.0
Parse Calls 14 1.0 0.0
Rows 14 1.0 N/A
User I/O Wait Time (ms) 0 N/A N/A
Cluster Wait Time (ms) 0 N/A N/A
Application Wait Time (ms) 0 N/A N/A
Concurrency Wait Time (ms) 0 N/A N/A
Invalidations 0 N/A N/A
Version Count 153 N/A N/A
Sharable Mem(KB) 3,724 N/A N/A
-------------------------------------------------------------
Execution Plan
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 1 (100)|
| 1 | UPDATE | SCHEDULER$_JOB | | | |
| 2 | INDEX UNIQUE SCAN| SCHEDULER$_JOB_PK | 1 | 25 | 0 (0)|
-----------------------------------------------------------------------------
Full SQL Text
SQL ID SQL Text
------------ -----------------------------------------------------------------
ayr8swhks7n2 update sys.scheduler$_job set flags = :1, start_date = :2, dist_f
flags = :3 where obj# = :4
Report written to awrsqlrp_2981_3025.txt
SYS@ORCL AS SYSDBA>
* AWR DB 보고서 생성
아래처럼 하면 생성이 됨... 너무 길어서 생략
분석 포인트
1) Load Profile 분석
: snap 구간 동안의 DBMS 성능 통계.
초당 Transaction 및 SQL 호출 수 등을 통해 DBMS Activity 를 분석
2) 메모리 분석
: Shared Pool 및 Buffer Cache 의 hit rate 등 메모리 활용 분석
3) 이벤트 분석
: cpu time, I/O 를 위한 WAIT, LOCK 발생여부 분석
4) TOP sql 분석
3) 이벤트 분석 (cpu time)
SQL ordered by CPU Time DB/Inst: ORCL/ORCL Snaps: 2983-3026
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> %Total - CPU Time as a percentage of Total DB CPU
-> %CPU - CPU Time as a percentage of Elapsed Time
-> %IO - User I/O Time as a percentage of Elapsed Time
-> Captured SQL account for 46.5% of Total CPU Time (s): 48
-> Captured PL/SQL account for 120.3% of Total CPU Time (s): 48
CPU CPU per Elapsed
Time (s) Executions Exec (s) %Total Time (s) %CPU %IO SQL Id
---------- ------------ ---------- ------ ---------- ------ ------ -------------
26.8 1 26.84 55.7 31.7 84.5 11.0 dwn4hw58x75w2
BEGIN SYS.KUPW$WORKER.MAIN('SYS_EXPORT_TABLE_01', 'SYS', 4719360); END;
8.3 852 0.01 17.3 8.8 94.9 5.3 6gvch1xu9ca3g
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN :
= FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date
; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
5.6 1 5.56 11.5 6.9 81.1 14.2 1kq218s4w6svb
BEGIN SYS.KUPW$WORKER.MAIN('SYS_IMPORT_FULL_01', 'SYS', 0); END;
2.7 1 2.73 5.7 2.7 99.3 .0 9474f94wzb5fj
Module: Data Pump Worker
SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7'))
, KU$.OBJ_NUM ,KU$.ANC_OBJ.NAME ,KU$.ANC_OBJ.OWNER_NAME ,KU$.ANC_OBJ.TYPE_NAME ,
KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,KU$.SPARE1 ,
KU$.XMLSCHEMACOLS ,KU$.SCHEMA_OBJ.NAME ,KU$.SCHEMA_OBJ.NAME ,'TABLE' ,KU$.PARENT
1.6 230 0.01 3.4 1.6 99.7 .0 4xmsh8dcaws7y
Module: Data Pump Worker
select nvl((select pname from loader_part_info where parttype = 3 and partpos =
1 and owner = :1 and tname = :2), NULL) from sys.dual
1.5 1 1.51 3.1 1.9 78.7 2.5 2sgn8wr182w9p
BEGIN SYS.KUPM$MCP.MAIN('SYS_EXPORT_TABLE_01', 'SYS', 0, 0); END;
1.4 230 0.01 2.8 1.4 99.4 .2 f6gbtv2xcvmab
Module: Data Pump Worker
DECLARE stmt VARCHAR2(2000); TABLE_NOT_EXIST exception; p
ragma EXCEPTION_INIT(TABLE_NOT_EXIST, -942); BEGIN stmt := 'DRO
P TABLE "ET$001A51120001" PURGE'; EXECUTE IMMEDIATE stmt; EXCEPTION
WHEN TABLE_NOT_EXIST THEN NULL; END;
1.1 230 0.00 2.4 2.4 47.6 56.6 2cr7uz3rttmvs
Module: Data Pump Worker
BEGIN dbms_metadata.get_dpstrm_md (:1, :2, :3, :4, :5, :6, TRUE, TRUE);
END;
* I/O 를 위한 WAIT
SQL ordered by User I/O Wait Time DB/Inst: ORCL/ORCL Snaps: 2983-3026
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> %Total - User I/O Time as a percentage of Total User I/O Wait time
-> %CPU - CPU Time as a percentage of Elapsed Time
-> %IO - User I/O Time as a percentage of Elapsed Time
-> Captured SQL account for 37.6% of Total User I/O Wait Time (s):
-> Captured PL/SQL account for 89.1% of Total User I/O Wait Time (s):
User I/O UIO per Elapsed
Time (s) Executions Exec (s) %Total Time (s) %CPU %IO SQL Id
---------- ------------ ---------- ------ ---------- ------ ------ -------------
3.5 1 3.49 42.6 31.7 84.5 11.0 dwn4hw58x75w2
BEGIN SYS.KUPW$WORKER.MAIN('SYS_EXPORT_TABLE_01', 'SYS', 4719360); END;
1.4 230 0.01 16.7 2.4 47.6 56.6 2cr7uz3rttmvs
Module: Data Pump Worker
BEGIN dbms_metadata.get_dpstrm_md (:1, :2, :3, :4, :5, :6, TRUE, TRUE);
END;
1.0 1 0.97 11.9 6.9 81.1 14.2 1kq218s4w6svb
BEGIN SYS.KUPW$WORKER.MAIN('SYS_IMPORT_FULL_01', 'SYS', 0); END;
0.8 621 0.00 9.4 0.8 9.7 95.5 cvn54b7yz0s8u
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ wher
e obj#=:1 and part=:2 and version=:3 order by piece#
0.5 1 0.47 5.7 0.5 .4 99.5 2ukazg1149bq5
delete from WRH$_DISPATCHER tab where (:beg_snap <= tab.snap_id and tab.
snap_id <= :end_snap and dbid = :dbid) and not exists (select 1 from
WRM$_BASELINE b where (tab.dbid = b.dbid) and
(tab.snap_id >= b.start_snap_id) and (tab.sna
0.5 852 0.00 5.7 8.8 94.9 5.3 6gvch1xu9ca3g
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN :
= FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date
; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
* Gets
SQL ordered by Gets DB/Inst: ORCL/ORCL Snaps: 2983-3026
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> %Total - Buffer Gets as a percentage of Total Buffer Gets
-> %CPU - CPU Time as a percentage of Elapsed Time
-> %IO - User I/O Time as a percentage of Elapsed Time
-> Total Buffer Gets: 4,147,320
-> Captured SQL account for 64.7% of Total
Buffer Gets Elapsed
Gets Executions per Exec %Total Time (s) %CPU %IO SQL Id
----------- ----------- ------------ ------ ---------- ------ ------ -----------
1,194,409 1 1,194,409.0 28.8 31.7 84.5 11.0 dwn4hw58x75
BEGIN SYS.KUPW$WORKER.MAIN('SYS_EXPORT_TABLE_01', 'SYS', 4719360); END;
375,647 852 440.9 9.1 8.8 94.9 5.3 6gvch1xu9ca
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN :
= FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date
; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
330,663 1 330,663.0 8.0 6.9 81.1 14.2 1kq218s4w6s
BEGIN SYS.KUPW$WORKER.MAIN('SYS_IMPORT_FULL_01', 'SYS', 0); END;
280,916 1 280,916.0 6.8 0.5 98.8 7.4 c2p32r5mzv8
BEGIN prvt_advisor.delete_expired_tasks; END;
236,254 230 1,027.2 5.7 1.6 99.7 .0 4xmsh8dcaws
Module: Data Pump Worker
select nvl((select pname from loader_part_info where parttype = 3 and partpos =
1 and owner = :1 and tname = :2), NULL) from sys.dual
*Read
SQL ordered by Reads DB/Inst: ORCL/ORCL Snaps: 2983-3026
-> %Total - Physical Reads as a percentage of Total Disk Reads
-> %CPU - CPU Time as a percentage of Elapsed Time
-> %IO - User I/O Time as a percentage of Elapsed Time
-> Total Disk Reads: 175,427
-> Captured SQL account for 33.8% of Total
Physical Reads Elapsed
Reads Executions per Exec %Total Time (s) %CPU %IO SQL Id
----------- ----------- ---------- ------ ---------- ------ ------ -------------
102,951 1 102,951.0 58.7 31.7 84.5 11.0 dwn4hw58x75w2
BEGIN SYS.KUPW$WORKER.MAIN('SYS_EXPORT_TABLE_01', 'SYS', 4719360); END;
9,952 1 9,952.0 5.7 0.5 98.8 7.4 c2p32r5mzv8hb
BEGIN prvt_advisor.delete_expired_tasks; END;
7,692 7 1,098.9 4.4 0.1 100.6 29.4 4tg8mr2bvy6gr
select smontabv.cnt, smontab.time_mp, smontab.scn, smontab.num_mappings, smon
tab.tim_scn_map from smon_scn_time smontab, (select max(scn) scnmax,
count(*)+sum(NVL2(TIM_SCN_MAP,NUM_MAPPINGS,0)) cnt
from smon_scn_time) smontabv where smontab.scn = smontabv.scnmax
5,350 1 5,350.0 3.0 0.8 81.1 13.1 9yhdmxbq004j9
delete from sys.wri$_optstat_histgrm_history whe
re nvl(:1, savtime) <= savtime and savtime < nvl(:2, savtime+1) and
obj# = nvl(:3, obj#) and intcol# = nvl(:4, intcol#)
5,103 6 850.5 2.9 0.1 99.6 23.1 2w2w2x51dk89q
select o.name from obj$ o where o.type# = 9 and bitand(o.flags, 2048) = 2048 and
o.name like 'SYSFN%' and (sysdate-o.ctime) > 0.0007
4,280 3 1,426.7 2.4 0.1 48.0 31.7 7m6p5t0pwdnch
Module: Data Pump Worker
SELECT /*+rule*/ KU$.OBJ_NUM FROM SYS.KU$_TABLE_OBJNUM_VIEW KU$ WHERE NOT (BITAN
D (KU$.SCHEMA_OBJ.FLAGS,16)=16) AND KU$.BASE_OBJ.NAME LIKE 'WR_$\_%' ESCAPE '\
' AND NOT (KU$.BASE_OBJ.NAME LIKE 'WRI$\_%' ESCAPE '\') AND NOT (KU$.BASE_OBJ
.NAME IN ('WRM$_BASELINE')) AND NOT (KU$.BASE_OBJ.NAME LIKE 'WRR$\_%' ESCAPE
* Physical Reads
SQL ordered by Physical Reads (UnOptimized)DB/Inst: ORCL/ORCL Snaps: 2983-30
-> UnOptimized Read Reqs = Physical Read Reqts - Optimized Read Reqs
-> %Opt - Optimized Reads as percentage of SQL Read Requests
-> %Total - UnOptimized Read Reqs as a percentage of Total UnOptimized Read Reqs
-> Total Physical Read Requests: 119,292
-> Captured SQL account for 20.9% of Total
-> Total UnOptimized Read Requests: 119,292
-> Captured SQL account for 20.9% of Total
-> Total Optimized Read Requests: 1
-> Captured SQL account for 0.0% of Total
UnOptimized Physical UnOptimized
Read Reqs Read Reqs Executions Reqs per Exe %Opt %Total SQL Id
----------- ----------- ---------- ------------ ------ ------ -------------
73,388 73,388 1 73,388.0 0.0 61.5 dwn4hw58x75w2
BEGIN SYS.KUPW$WORKER.MAIN('SYS_EXPORT_TABLE_01', 'SYS', 4719360); END;
9,892 9,892 1 9,892.0 0.0 8.3 c2p32r5mzv8hb
BEGIN prvt_advisor.delete_expired_tasks; END;
3,890 3,890 1 3,890.0 0.0 3.3 1kq218s4w6svb
BEGIN SYS.KUPW$WORKER.MAIN('SYS_IMPORT_FULL_01', 'SYS', 0); END;
3,632 3,632 28 129.7 0.0 3.0 9wygvu6cx2npy
DELETE FROM WRI$_ADV_MESSAGE_GROUPS A WHERE A.TASK_ID = :B2 AND (:B1 IS NULL OR
:B1 = A.EXEC_NAME)
2,866 2,866 1 2,866.0 0.0 2.4 ajymgnp1qnruw
select o.name, o.owner# from obj$ o, type$ t where o.oid$ = t.tvoid and bitand
(t.properties,8388608) = 8388608 and (sysdate-o.ctime) > 0.0007
2,535 2,535 852 3.0 0.0 2.1 6gvch1xu9ca3g
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN :
= FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date
; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
*Executions
SQL ordered by Executions DB/Inst: ORCL/ORCL Snaps: 2983-3026
-> %CPU - CPU Time as a percentage of Elapsed Time
-> %IO - User I/O Time as a percentage of Elapsed Time
-> Total Executions: 377,201
-> Captured SQL account for 51.2% of Total
Elapsed
Executions Rows Processed Rows per Exec Time (s) %CPU %IO SQL Id
------------ --------------- -------------- ---------- ------ ------ -----------
49,129 70,068 1.4 1.0 91.1 .0 cm5vu20fhtn
select /*+ connect_by_filtering */ privilege#,level from sysauth$ connect by gra
ntee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
14,238 14,238 1.0 0.1 95.1 .0 chhpxnrad7v
Module: Data Pump Worker
SELECT C.DEFAULT$,C.PROPERTY FROM SYS.COL$ C WHERE C.OBJ#=:B2 AND C.INTCOL#=:B1
9,840 932 0.1 0.8 19.0 .0 5ms6rbzdnq1
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (n
ext_date <= :2)) or ((last_date is null) and (next_date < :3))) and (field1
= :4 or (field1 = 0 and 'Y' = :5)) and (this_date is null) and ((dbms_logstdby.d
b_is_logstdby = 0 and job < 1000000000) or (dbms_logstdby.db_is_logstdby = 1 an
5,890 5,890 1.0 0.5 98.7 2.7 60uw2vh6q9v
insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,o
ffset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,chars
etform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,
183/*DTYIDS*/,:7,decode(:7,0,null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/
4,637 4,637 1.0 0.1 100.6 .0 089dbukv1aa
SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL
3,905 0 0.0 0.0 89.4 .0 350f5yrnnms
lock table sys.mon_mods$ in exclusive mode nowait
3,905 3,595 0.9 0.2 98.1 .7 g00cj285jmg
update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, del
etes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags
+ :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where ob
j# = :objn
*Parse Calls
SQL ordered by Parse Calls DB/Inst: ORCL/ORCL Snaps: 2983-3026
-> Total Parse Calls: 226,359
-> Captured SQL account for 58.1% of Total
% Total
Parse Calls Executions Parses SQL Id
------------ ------------ --------- -------------
49,129 49,129 21.70 cm5vu20fhtnq1
select /*+ connect_by_filtering */ privilege#,level from sysauth$ connect by gra
ntee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
3,905 3,905 1.73 350f5yrnnmshs
lock table sys.mon_mods$ in exclusive mode nowait
3,905 3,905 1.73 g00cj285jmgsw
update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, del
etes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags
+ :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where ob
j# = :objn
2,367 2,397 1.05 08bqjmf8490s2
SELECT PARAMETER_VALUE FROM MGMT_PARAMETERS WHERE PARAMETER_NAME = :B1
2,347 2,347 1.04 9tgj4g8y4rwy8
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lis
ts,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0),NVL
(bitmapranges,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
2,285 2,285 1.01 bcv9qynmu1nv9
select sys.dbms_standard.dictionary_obj_type from dual
2,021 2,021 0.89 12kw3xcxv1qpg
select sys.dbms_standard.sysevent from dual
2,021 2,021 0.89 4a3ccstvk0ssw
select sys.dbms_standard.dictionary_obj_name from dual
2,021 2,021 0.89 ar9nmtmd28460
select sys.dbms_standard.dictionary_obj_owner from dual
2,021 2,021 0.89 d7y4tdacc7f3j
select SYS_CONTEXT('USERENV','SESSION_USER') from dual
SYS@ORCL AS SYSDBA> @awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1427591565 ORCL 1 ORCL
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: TEXT
Type Specified: text
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1427591565 1 ORCL ORCL DBTEST
Using 1427591565 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
ORCL ORCL 2980 11 Apr 2016 00:00 1
2981 11 Apr 2016 00:20 1
2982 11 Apr 2016 00:40 1
2983 11 Apr 2016 01:00 1
2984 11 Apr 2016 01:20 1
2985 11 Apr 2016 01:40 1
2986 11 Apr 2016 02:00 1
2987 11 Apr 2016 02:20 1
2988 11 Apr 2016 02:40 1
2989 11 Apr 2016 03:00 1
2990 11 Apr 2016 03:20 1
2991 11 Apr 2016 03:40 1
2992 11 Apr 2016 04:00 1
2993 11 Apr 2016 04:20 1
2994 11 Apr 2016 04:40 1
2995 11 Apr 2016 05:00 1
2996 11 Apr 2016 05:20 1
2997 11 Apr 2016 05:40 1
2998 11 Apr 2016 06:00 1
2999 11 Apr 2016 06:20 1
3000 11 Apr 2016 06:40 1
3001 11 Apr 2016 07:00 1
3002 11 Apr 2016 07:20 1
3003 11 Apr 2016 07:40 1
3004 11 Apr 2016 08:00 1
3005 11 Apr 2016 08:20 1
3006 11 Apr 2016 08:40 1
3007 11 Apr 2016 09:00 1
3008 11 Apr 2016 09:20 1
3009 11 Apr 2016 09:40 1
3010 11 Apr 2016 10:00 1
3011 11 Apr 2016 10:20 1
3012 11 Apr 2016 10:40 1
3013 11 Apr 2016 11:00 1
3014 11 Apr 2016 11:20 1
3015 11 Apr 2016 11:40 1
3016 11 Apr 2016 12:00 1
3017 11 Apr 2016 12:20 1
3018 11 Apr 2016 12:40 1
3019 11 Apr 2016 13:00 1
3020 11 Apr 2016 13:20 1
3021 11 Apr 2016 13:40 1
3022 11 Apr 2016 14:00 1
3023 11 Apr 2016 14:20 1
3024 11 Apr 2016 14:40 1
3025 11 Apr 2016 15:00 1
3026 11 Apr 2016 15:20 1
3027 11 Apr 2016 15:40 1
3028 11 Apr 2016 16:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 2983
Begin Snapshot Id specified: 2983
Enter value for end_snap: 3026
End Snapshot Id specified: 3026
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_2983_3026.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: awrrpt_2983_3026.txt
Using the report name awrrpt_2983_3026.txt
WORKLOAD REPOSITORY report for
init.ora Parameters DB/Inst: ORCL/ORCL Snaps: 2983-3026
-> if IP/Public/Source at End snap is different a '*' is displayed
End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
audit_file_dest /u01/app/oracle/admin/orcl/adump
audit_trail DB
compatible 11.2.0.0.0
control_files /u01/app/oracle/oradata/orcl/cont
db_block_size 8192
db_domain
db_name orcl
db_recovery_file_dest /u01/app/oracle/flash_recovery_ar
db_recovery_file_dest_size 5368709120
diagnostic_dest /u01/app/oracle
dispatchers (PROTOCOL=TCP) (SERVICE=ORCLXDB)
log_archive_dest_1 location=/u01/app/oracle/oradata/
log_archive_format name_%t_%s_%r.arc
log_archive_start TRUE
memory_max_target 3305111552
memory_target 3305111552
open_cursors 300
pga_aggregate_target 2765094912
processes 150
remote_login_passwordfile EXCLUSIVE
resource_manager_plan SCHEDULER[0x3009]:DEFAULT_MAINTEN
sga_target 536870912
undo_tablespace UNDOTBS1
-------------------------------------------------------------
Dynamic Remastering Stats DB/Inst: ORCL/ORCL Snaps: 2983-3026
No data exists for this section of the report.
-------------------------------------------------------------
End of Report
Report written to awrrpt_2983_3026.txt
#3. AWR 구현 원리
1. 수집된 성능관련 통계정보가 저장되며 이를 바탕으로 성능 메트릭을 제공함. -> 잠재적인 문제의 원인 추적을 가능하게 해줌
2. MMON 백그라운드 프로세스와 여러개의 슬레이브 프로세스를 통해 자동적으로 매시간별 스냅샷 정보를 수집.
3. 공간 절약을 위해 DEFAULT 7일 후 자동으로 삭제됨.
*
SNAP_INTERVAL = 스냅샷 주기
RETENTION = 보관주기
SYS@ORCL AS SYSDBA> select snap_interval, retention from dba_hist_wr_control;
SNAP_INTERVAL RETENTION
------------------------- -------------------------
+00000 00:20:00.0 +00011 00:00:00.0
BEGIN
dbms_workload_repository.modify_snapshot_settings (
interval => 20, < 20분
retention => 8*24*60 < 8일 x 24시간 x 60분
);
END;
/
SYS@ORCL AS SYSDBA> select snap_interval, retention from dba_hist_wr_control;
SNAP_INTERVAL RETENTION
------------------------- -------------------------
+00000 00:20:00.0 +00008 00:00:00.0
- AWR 은 수집된 통계정보를 저장하기 위해 여러개의 테이블을 사용함.
- 이 테이블들은 모두 SYS스키마 SYSAUX 테이블스페이스 내에 저장되어 있으머,
WRM$_* 또는 WRH$_* 의 네임 포멧을 갖음
* H : Historical M : Metadata
WRM$_* 테이블 : 테이터베이스 및 스냅샷에 관련한 메타데이타 정보.
WRH$_* 테이블 : 실제 수집된 통계정보를 저장.
위 테이블을 바탕으로 DBA_HIST_ 라는 prefix 를 갖는 여러가지 뷰가 제공됨.
DBA_HIST_METRIC_NAME 뷰 레코드에 대한 조회 결과.
컬럼명 데이터 값 컬럼 설명
DBID 1169471561 데이터베이스 ID
GROUP_ID 2 메트릭 그룹 ID
GROUP_NAME System Metrics Long Duration 메트릭 그룹 이름
METRIC_ID 2075 메트릭 ID
METRIC_NAME CPU Usage Per Sec 메트릭 이름
METRIC_UNIT CentiSeconds Per Second 측정단위
## 1초 단위로 CPU 자원이 어떻게 소비되고 있는지 확인
*SD : 표준편차
SYS@ORCL AS SYSDBA> SELECT begin_time, intsize, num_interval, minval, maxval, average, standard_deviation sd
FROM dba_hist_sysmetric_summary WHERE metric_id = 2075 AND ROWNUM <10 ORDER BY 1 DESC ;
BEGIN_TIME INTSIZE NUM_INTERVAL MINVAL MAXVAL AVERAGE SD
------------ ---------- ------------ ---------- ---------- ---------- ----------
28-MAR-16 120002 20 0 .134860949 .079250518 .026379288
24-MAR-16 360005 60 0 .240433531 .076446273 .041232767
22-MAR-16 360005 60 0 .887455454 .148882492 .24025376
22-MAR-16 360005 60 0 .889120733 .151214402 .242426757
22-MAR-16 359905 60 0 .93074438 .163244699 .252672974
22-MAR-16 360005 60 0 .979032473 .159522034 .258510268
22-MAR-16 360006 60 0 .969037469 .163082695 .257820527
20-MAR-16 360005 60 0 15.23997 .417434832 1.95499486
20-MAR-16 360005 60 0 .666006661 .159945071 .187079882
## 얼마나 많은 시간이 실제 작업에 사용되었는지 확인 (인스턴트 시작후 DB 사용 누적치)
SYS@ORCL AS SYSDBA> SELECT stat_name, value FROM v$sys_time_model;
STAT_NAME VALUE
---------------------------------------------------------------- ----------
DB time 49276312
DB CPU 56134401
background elapsed time 258465449
background cpu time 83447272
sequence load elapsed time 17325
parse time elapsed 8110825
hard parse elapsed time 5175096
sql execute elapsed time 43247881
connection management call elapsed time 73059
failed parse elapsed time 861
failed parse (out of shared memory) elapsed time 0
hard parse (sharing criteria) elapsed time 253685
hard parse (bind mismatch) elapsed time 115212
PL/SQL execution elapsed time 10693902
inbound PL/SQL rpc elapsed time 1086235
PL/SQL compilation elapsed time 625219
Java execution elapsed time 0
repeated bind elapsed time 47353
RMAN cpu time (backup/restore) 38996
#수작업으로 스냅샷 생성하기
SYS@ORCL AS SYSDBA> SELECT sysdate FROM dual;
SYSDATE
------------
30-MAR-16
SYS@ORCL AS SYSDBA> SELECT snap_id, begin_interval_time begin, end_interval_time end
2 FROM SYS.DBA_HIST_SNAPSHOT
3 WHERE begin_interval_time > to_date('30-03-2016 04:00:00','dd/mm/yyyy hh24:mi:ss')
4 ORDER BY 1,2;
SNAP_ID BEGIN END
---------- ------------------------------ ------------------------------
2128 30-MAR-16 04.00.20.612 AM 30-MAR-16 04.20.21.151 AM
2129 30-MAR-16 04.20.21.151 AM 30-MAR-16 04.40.21.661 AM
2130 30-MAR-16 04.40.21.661 AM 30-MAR-16 05.00.22.201 AM
2131 30-MAR-16 05.00.22.201 AM 30-MAR-16 05.20.22.732 AM
2132 30-MAR-16 05.20.22.732 AM 30-MAR-16 05.40.23.244 AM
2133 30-MAR-16 05.40.23.244 AM 30-MAR-16 06.00.23.781 AM
2134 30-MAR-16 06.00.23.781 AM 30-MAR-16 06.20.24.301 AM
2135 30-MAR-16 06.20.24.301 AM 30-MAR-16 06.40.24.817 AM
2136 30-MAR-16 06.40.24.817 AM 30-MAR-16 07.00.25.339 AM
2137 30-MAR-16 07.00.25.339 AM 30-MAR-16 07.20.25.875 AM
2138 30-MAR-16 07.20.25.875 AM 30-MAR-16 07.40.26.405 AM
2139 30-MAR-16 07.40.26.405 AM 30-MAR-16 08.00.26.968 AM
2140 30-MAR-16 08.00.26.968 AM 30-MAR-16 08.20.27.509 AM
2141 30-MAR-16 08.20.27.509 AM 30-MAR-16 08.40.28.022 AM
2142 30-MAR-16 08.40.28.022 AM 30-MAR-16 09.00.28.725 AM
2143 30-MAR-16 09.00.28.725 AM 30-MAR-16 09.20.29.296 AM
2144 30-MAR-16 09.20.29.296 AM 30-MAR-16 09.40.29.815 AM
2145 30-MAR-16 09.40.29.815 AM 30-MAR-16 10.00.30.363 AM
2146 30-MAR-16 10.00.30.363 AM 30-MAR-16 10.20.30.967 AM
2147 30-MAR-16 10.20.30.967 AM 30-MAR-16 10.40.31.494 AM
2148 30-MAR-16 10.40.31.494 AM 30-MAR-16 11.00.32.098 AM
2149 30-MAR-16 11.00.32.098 AM 30-MAR-16 11.20.32.707 AM
2150 30-MAR-16 11.20.32.707 AM 30-MAR-16 11.40.33.236 AM
2151 30-MAR-16 11.40.33.236 AM 30-MAR-16 12.00.33.780 PM
2152 30-MAR-16 12.00.33.780 PM 30-MAR-16 12.20.34.294 PM
2153 30-MAR-16 12.20.34.294 PM 30-MAR-16 12.40.34.803 PM
2154 30-MAR-16 12.40.34.803 PM 30-MAR-16 01.00.35.357 PM
2155 30-MAR-16 01.00.35.357 PM 30-MAR-16 01.20.35.878 PM
2156 30-MAR-16 01.20.35.878 PM 30-MAR-16 01.40.36.396 PM
2157 30-MAR-16 01.40.36.396 PM 30-MAR-16 02.00.36.934 PM
2158 30-MAR-16 02.00.36.934 PM 30-MAR-16 02.20.37.460 PM
2159 30-MAR-16 02.20.37.460 PM 30-MAR-16 02.40.37.972 PM
2160 30-MAR-16 02.40.37.972 PM 30-MAR-16 03.00.38.513 PM
2161 30-MAR-16 03.00.38.513 PM 30-MAR-16 03.20.39.044 PM
2162 30-MAR-16 03.20.39.044 PM 30-MAR-16 03.40.39.561 PM
2163 30-MAR-16 03.40.39.561 PM 30-MAR-16 04.00.40.249 PM
2164 30-MAR-16 04.00.40.249 PM 30-MAR-16 04.20.40.804 PM
2165 30-MAR-16 04.20.40.804 PM 30-MAR-16 04.40.41.317 PM
2166 30-MAR-16 04.40.41.317 PM 30-MAR-16 05.00.41.845 PM
40 rows selected.
* 수동 스냅샷 실행
SYS@ORCL AS SYSDBA> execute dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
2167 30-MAR-16 05.00.41.845 PM 30-MAR-16 05.07.00.123 PM << 추가됨
# 수집된 통계 수동 삭제
* (10, 15) 10,15 는 SNAP_ID 범위
exec dbms_workload_repository.drop_snapshot_range(1, 2000);
# 5.베이스라인
- 성능 튜닝 작업을 수행할 떄에는 먼저 일련의 메트릭에 대한 베이스라인(baseline)을 수집하고 튜닝을 위한 변경 작업 수행.
- 베이스라인에 해당하는 스냅샷의 메트릭을 비교하기 위함.
- 베이스라인과 연결된 스냅샷은 삭제되지 않음.
#베이스 라인 조회
SYS@ORCL AS SYSDBA> SELECT dbid, baseline_id, baseline_name, start_snap_id, end_snap_id
FROM dba_hist_baseline;
#베이스라인 생성
exec dbms_workload_repository.create_baseline (2160,2166,'test_baseline_1');
#베이스라인 삭제
exec dbms_workload_repository.drop_baseline ('test_baseline_1');
SYS@ORCL AS SYSDBA> SELECT dbid, baseline_id, baseline_name, start_snap_id, end_snap_id
FROM dba_hist_baseline;
DBID BASELINE_ID BASELINE_NAME START_SNAP_ID END_SNAP_ID
---------- ----------- ---------------------------------------------------------------- ------------- -----------
1427591565 1 test_baseline_1 2160 2166
1427591565 0 SYSTEM_MOVING_WINDOW 2001 2168
SYS@ORCL AS SYSDBA> exec dbms_workload_repository.drop_baseline ('test_baseline_1');
PL/SQL procedure successfully completed.
SYS@ORCL AS SYSDBA> SELECT dbid, baseline_id, baseline_name, start_snap_id, end_snap_id
2 FROM dba_hist_baseline;
DBID BASELINE_ID BASELINE_NAME START_SNAP_ID END_SNAP_ID
---------- ----------- ---------------------------------------------------------------- ------------- -----------
1427591565 0 SYSTEM_MOVING_WINDOW 2001 2168
# 6. ADDM (AUTOMATIC DATABASE DIAGNOSTIC MONITOR) = 오라클 데이터베이스가 제공하는 sql 튜닝 서비스...
- AWR이 데이터베이스로부터 상세한 성능 관련 지표를 주기적으로 수집하여 저장.
- 스냅샷 생성작업이 완료 될 때마다 ADDM 이 호출되어 서로 다른 스냅샷의 데이타와 성능 지표를 비교 분석, 성능 향상에 조언을 줌
- 문제가 발견된 후, ADDM 은 다른 어드바이저 툴(SQL Tuning Advisor 등..) 을 호출하여 해결 방법을 찾아내기도함.
# SQL Tunning Advisor 를 이용한 access 분석 방법
- 오라클은 데이터베이스의 옵티마이저는 가능한 액세스 경로를 여럿 생성한 뒤, 오브젝트 통계정보를 기준으로 가장 적은 비용 하나를 선택하는 방식으로 runtime optimization 을 수행.
-> 하지만, 옵티마이저는 아래와 같은 경우 판단을 못함
- sql 구문의 튜닝이 필요한지
- 통계가 정확한지
- 인덱스 생성해야 하는지
- Advisor는 사용자의 요구사항을 기반으로 고려하여, 성능을 향상시키기 위해 무엇을 해야하는지 알려줌.
ex)
# advisor 권한 부여
SYS@ORCL AS SYSDBA> grant advisor to scott;
Grant succeeded.
# 문제의 쿼리
SYS@ORCL AS SYSDBA> conn scott/scott
Connected.
SCOTT@ORCL > SELECT ename from emp e
WHERE job = 'SALESMAN'
AND comm not in (SELECT comm FROM emp
WHERE ename=e.ename AND comm is not null);
no rows selected
SQL> DECLARE
l_task_id varchar2(30);
l_sql varchar2(2000);
BEGIN
l_sql := 'select ename from emp e where job = :job and comm not in
(select comm from emp where ename=e.ename and comm is not null)';
l_task_id := dbms_sqltune.create_tuning_task( /* dbms_sqltune 패키지 사용 */
sql_text => l_sql,
user_name => 'SCOTT', /* 유저명 대문자 */
scope => 'COMPREHENSIVE',
time_limit => 120,
task_name => 'sql_advisor_test14' /* 테스크 이름 */
);
dbms_sqltune.execute_tuning_task ('sql_advisor_test14'); /* 튜닝 테스크 실행 */
END;
/
# 생성된 데스크 보고서
SCOTT@ORCL > set serveroutput on size 999999
SCOTT@ORCL > set long 999999
SCOTT@ORCL > select dbms_sqltune.report_tuning_task ('sql_advisor_test14') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_ADVISOR_TEST14')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sql_advisor_test14
Tuning Task Owner : SCOTT
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 120
Completion Status : COMPLETED
Started at : 03/30/2016 18:00:18
Completed at : 03/30/2016 18:00:18
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 92www1m9q3z8f
SQL Text : select ename from emp e where job = :job and comm not in
(select comm from emp where ename=e.ename and comm is not null)
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings) /* 권고사항 2가지가 있음 */
-------------------------------------------------------------------------------
1- Statistics Finding /* 1. 에널라이즈 */
---------------------
Table "SCOTT"."EMP" was not analyzed.
Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
2- Restructure SQL finding (see plan 1 in explain plans section) /* 2번째 SQL 구조 */
----------------------------------------------------------------
The optimizer could not unnest the subquery at line ID 1 of the execution
plan.
Recommendation
--------------
- Consider replacing "NOT IN" with "NOT EXISTS" or ensure that columns used
on both sides of the "NOT IN" operator are declared "NOT NULL" by adding
either "NOT NULL" constraints or "IS NOT NULL" predicates.
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- At least one important bind value was missing for this sql statement. The
accuracy of the advisor's analysis may depend on all important bind values
being supplied.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 2561671593
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 6 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 26 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 1 | 20 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT 0 FROM "EMP" "EMP" WHERE "COMM" IS
NOT NULL AND "ENAME"=:B1 AND LNNVL("COMM"<>:B2)))
2 - filter("JOB"=:JOB)
3 - filter("COMM" IS NOT NULL AND "ENAME"=:B1 AND LNNVL("COMM"<>:B2))
-------------------------------------------------------------------------------
*** ASH
(Active Session History)
- 현재 Active 세션 정보를 1초에 한번씩 샘플링 하여 ASH 버퍼에 저장함.
- shared pool 안에 있는 메모리 .cpu 당 2m
select * from v$active_session_history
- MMON 은 자동적으로 60분에 한번씩
Workload repository 로 ASH에 있는 내용을 내려슴
*MMNL(Manageability monitor light) : 66% 가 차면 ASH Buffer 의 내용을 Workload repository 로 내려씀
*활용법
오랜지 행으로 인해 현재 actvie 세션을 얻고자 할때
1. dump to trace file
- setmypid 명령을 이용하면 현재 세션으로 Attach한다.
SYS@ORCL AS SYSDBA> oradebug setmypid
Statement processed.
level 10으로 ash buffer의 전체 내용을 내려받는다.
*
Level은 과거 몇 분간의 이력을 기록할 것인가를 지정한다. 만일 level 10으로 지정하면 과거 10분간의 정보를 기록함
ASH 정보는 Active Session의 목록을 저장하기 때문에 Oracle Hang과 같은 현상이 발생했을 때 사후 분석 용도로 유용함.
SYS@ORCL AS SYSDBA> oradebug dump ashdump 10
Statement processed.
SYS@ORCL AS SYSDBA> update emp set COMM ='1500' where EMPNO='7369';
1 row updated.
SYS@ORCL AS SYSDBA> @ashrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1427591565 ORCL 1 ORCL
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text
Type Specified: text
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1427591565 1 ORCL ORCL DBTEST
Defaults to current database
Using database id: 1427591565
Enter instance numbers. Enter 'ALL' for all instances in a
RAC cluster or explicitly specify list of instances (e.g., 1,2,3).
Defaults to current instance.
Using instance number(s): 1
ASH Samples in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Oldest ASH sample available: 29-Mar-16 22:33:07 [ 18285 mins in the past]
Latest ASH sample available: 11-Apr-16 15:06:30 [ 12 mins in the past]
Specify the timeframe to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter begin time for report:
-- Valid input formats:
-- To specify absolute begin time:
-- [MM/DD[/YY]] HH24:MI[:SS]
-- Examples: 02/23/03 14:30:15
-- 02/23 14:30:15
-- 14:30:15
-- 14:30
-- To specify relative begin time: (start with '-' sign)
-- -[HH24:]MI
-- Examples: -1:15 (SYSDATE - 1 Hr 15 Mins)
-- -25 (SYSDATE - 25 Mins)
Defaults to -15 mins
Enter value for begin_time: 02/23 14:00:00 << 시작 날짜
Report begin time specified: 02/23 14:00:00 << 종료 날짜
Enter duration in minutes starting from begin time:
Defaults to SYSDATE - begin_time
Press Enter to analyze till current time
Enter value for duration: 03/30 14:20:00
Report duration specified: 03/30 14:20:00
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 12
Using 23-Feb-16 14:00:00 as report begin time
Using as report end time
Specify Slot Width (using ashrpti.sql) for 'Activity Over Time' section
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Explanation:
-- In the 'Activity Over Time' section of the ASH report,
-- the analysis period is divided into smaller slots
-- and top wait events are reported in each of those slots.
-- Default:
-- The analysis period will be automatically split upto 10 slots
-- complying to a minimum slot width of
-- 1 minute, if the source is V$ACTIVE_SESSION_HISTORY or
-- 5 minutes, if the source is DBA_HIST_ACTIVE_SESS_HISTORY.
Specify Slot Width in seconds to use in the 'Activity Over Time' section:
Defaults to a value as explained above:
Slot Width specified:
Specify Report Targets (using ashrpti.sql) to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Explanation:
-- ASH Report can accept "Report Targets",
-- like a particular SQL statement, or a particular SESSION,
-- to generate the report on. If one or more report targets are
-- specified, then the data used to generate the report will only be
-- the ASH samples that pertain to ALL the specified report targets.
-- Default:
-- If none of the report targets are specified,
-- then the target defaults to all activity in the database instance.
Specify SESSION_ID (eg: from V$SESSION.SID) report target:
Defaults to NULL:
SESSION report target specified:
Specify SQL_ID (eg: from V$SQL.SQL_ID) report target:
Defaults to NULL: (% and _ wildcards allowed)
SQL report target specified:
Specify WAIT_CLASS name (eg: from V$EVENT_NAME.WAIT_CLASS) report target:
[Enter 'CPU' to investigate CPU usage]
Defaults to NULL: (% and _ wildcards allowed)
WAIT_CLASS report target specified:
Specify SERVICE_HASH (eg: from V$ACTIVE_SERVICES.NAME_HASH) report target:
Defaults to NULL:
SERVICE report target specified:
Specify MODULE name (eg: from V$SESSION.MODULE) report target:
Defaults to NULL: (% and _ wildcards allowed)
MODULE report target specified:
Specify ACTION name (eg: from V$SESSION.ACTION) report target:
Defaults to NULL: (% and _ wildcards allowed)
ACTION report target specified:
Specify CLIENT_ID (eg: from V$SESSION.CLIENT_IDENTIFIER) report target:
Defaults to NULL: (% and _ wildcards allowed)
CLIENT_ID report target specified:
Specify PLSQL_ENTRY name (eg: "SYS.DBMS_LOB.*") report target:
Defaults to NULL: (% and _ wildcards allowed)
PLSQL_ENTRY report target specified:
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is ashrpt_1_.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: awrrp_level10.txt
Using the report name awrrp_level10.txt
Summary of All User Input
-------------------------
Format : TEXT
DB Id : 1427591565
Inst num : 1
Begin time : 23-Feb-16 14:00:00
End time :
Slot width : Default
Report targets : 0
Report name : awrrp_level10.txt
ASH Report For ORCL/ORCL
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
ORCL 1427591565 ORCL 1 11.2.0.1.0 NO DBTEST
CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size
---- ------------------ ------------------ ------------------ ------------------
2 3,138M (100%) 64M (2.0%) 275M (8.8%) 4.0M (0.1%)
Analysis Begin Time: 23-Feb-16 14:00:00
Analysis End Time:
Elapsed Time: (mins)
Begin Data Source: DBA_HIST_ACTIVE_SESS_HISTORY
in AWR snapshot 2432
End Data Source: DBA_HIST_ACTIVE_SESS_HISTORY
in AWR snapshot
Sample Count: 0
Average Active Sessions:
Avg. Active Session per CPU:
Report Target: None specified
Top User Events DB/Inst: ORCL/ORCL (Feb 23 14:00 to )
No data exists for this section of the report.
-------------------------------------------------------------
Top Background Events DB/Inst: ORCL/ORCL (Feb 23 14:00 to )
No data exists for this section of the report.
-------------------------------------------------------------
Top Event P1/P2/P3 Values DB/Inst: ORCL/ORCL (Feb 23 14:00 to )
No data exists for this section of the report.
-------------------------------------------------------------
Top Service/Module DB/Inst: ORCL/ORCL (Feb 23 14:00 to )
No data exists for this section of the report.
-------------------------------------------------------------
Top Client IDs DB/Inst: ORCL/ORCL (Feb 23 14:00 to )
No data exists for this section of the report.
-------------------------------------------------------------
Top SQL Command Types DB/Inst: ORCL/ORCL (Feb 23 14:00 to )
No data exists for this section of the report.
-------------------------------------------------------------
Top Phases of Execution DB/Inst: ORCL/ORCL (Feb 23 14:00 to )
No data exists for this section of the report.
-------------------------------------------------------------
Top SQL with Top Events DB/Inst: ORCL/ORCL (Feb 23 14:00 to )
No data exists for this section of the report.
-------------------------------------------------------------
Top SQL with Top Row Sources DB/Inst: ORCL/ORCL (Feb 23 14:00 to )
No data exists for this section of the report.
-------------------------------------------------------------
Top SQL using literals DB/Inst: ORCL/ORCL (Feb 23 14:00 to )
No data exists for this section of the report.
-------------------------------------------------------------
Top Parsing Module/Action DB/Inst: ORCL/ORCL (Feb 23 14:00 to )
No data exists for this section of the report.
-------------------------------------------------------------
Top PL/SQL Procedures DB/Inst: ORCL/ORCL (Feb 23 14:00 to )
No data exists for this section of the report.
-------------------------------------------------------------
Top Java Workload DB/Inst: ORCL/ORCL (Feb 23 14:00 to )
No data exists for this section of the report.
-------------------------------------------------------------
Top Call Types DB/Inst: ORCL/ORCL (Feb 23 14:00 to )
No data exists for this section of the report.
-------------------------------------------------------------
Top Sessions DB/Inst: ORCL/ORCL (Feb 23 14:00 to )
No data exists for this section of the report.
-------------------------------------------------------------
Top Blocking Sessions DB/Inst: ORCL/ORCL (Feb 23 14:00 to )
No data exists for this section of the report.
-------------------------------------------------------------
Top Sessions running PQs DB/Inst: ORCL/ORCL (Feb 23 14:00 to )
No data exists for this section of the report.
-------------------------------------------------------------
Top DB Objects DB/Inst: ORCL/ORCL (Feb 23 14:00 to )
No data exists for this section of the report.
-------------------------------------------------------------
Top DB Files DB/Inst: ORCL/ORCL (Feb 23 14:00 to )
No data exists for this section of the report.
-------------------------------------------------------------
Top Latches DB/Inst: ORCL/ORCL (Feb 23 14:00 to )
No data exists for this section of the report.
-------------------------------------------------------------
Activity Over Time DB/Inst: ORCL/ORCL (Feb 23 14:00 to )
No data exists for this section of the report.
-------------------------------------------------------------
End of Report
Report written to awrrp_level10.txt