Oracle provides a log of different tools and features to optimize and tune the database. Below you can find the list of performance-tuning tools and features which should be mastered before entering the exam.
SQL Tuning Advisor – is used to optimize SQL statement without modifying it. (one time one sql)
SQL Access Advisor – is used to achieve a performance by recommending indexes, materialized views and partitions for a given workload. (one time multi SQLs)
SQL Performance Analyzer – is used to compare SQL execution results before and after specific changes and provides a report that help you to evaluate and estimate the impact of the change.
SQL Plan Management – is used to make sure to consistent SQL performance by using only accepted execution plans on SQL statements.
Automatic Workload Repository (AWR) – is used to collect and maintain performance statistics of the database.
Automatic Database Diagnostic Monitor (ADDM) – is used to analyze the AWR data for performance problems.
Active Session History (ASH) – gathers sampled data of active sessions and is used to diagnose performance problems.
Memory Advisors – are used to get recommendations on specific memory structures and provide information for tuning them.
V$ Performance Views – are updated continuously and maintained by the database and are used to diagnose performance problems.
Application tracing tools – are used to diagnose performance problems by debugging and tracing a session or the database.
난이도: ★☆☆☆☆
문제 1: AWR Snapshot Retention을 14 days로 설정하라. AWR Snapshot interval을 30분으로 지정하여라. (4분)
Automatic Workload Repository (AWR) – is used to collect a performance data such as wait events, object statistics, time model statistics, system and session statistics, SQL statements and etc. the statistics are available in both memory and database and can be accessed from both SQL*Plus and OEM. AWR takes snapshot of statistics every hour and retention of this historical data is 8 days by default.
난이도: ★☆☆☆☆
문제 2: 두 개의 다른 snapshot을 선택하고, 그것을 토대로 AWR report를 작성하여라. Top wait events를 분석하여라. (5분)
DB CPU, resmgr:plan change, Disk file operations I/O, log file sync이 Total Wait Time기준 가장 큰 시간을 차지하고 있었다. 쿼리 수행 시간인 DB CPU가 최상위 Event인 것은 자연스러운 현상이나, 쿼리 결과를 내기 위해 버퍼 캐시가 아닌 Disk을 찾거나, 변경 데이터를 redo에 기록하는 log file sync이 최상위 Event에 왔다는 것은 튜닝이 필요한 문제 상황이라고 볼 수 있다. 그러나, 본 데이터베이스는 비어 있으며, 어떠한 트랜잭션 및 쿼리가 진행되지 않는 데이터베이스이다.
난이도: ★★☆☆☆
문제 3: CLI환경에서 문제2의 AWR report를 작성하여라. (6분)
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
난이도: ★★☆☆☆
문제 4: 두 개의 서로 다른 periods를 비교하고, comparison report를 작성하여라. (6분)
ADDM analyzes data in AWR, identifies the performance problems and provides recommendations every time the AWR snapshot is taken and the results are saved in the database. (STATISTICS_LEVEL database parameter must be either TYPICAL or ALL). After ADDM completes analysis, the recommendations can be viewd from OEM.
난이도: ★☆☆☆☆
문제 5: Automatic Database Diagnostic Management (ADDM)을 실행하고, 결과를 체크하여라. (5분)
난이도: ★★☆☆☆
문제 6: DBMS_ADDM 패키지를 사용하여 두 스냅샷을 생성하고, report를 분석하여라. (8분)
var task_name VARCHAR2(60);
BEGIN
:task_name := 'Manual_ADDM_task';
DBMS_ADDM.ANALYZE_DB(:task_name, 7, 8);
END;
/
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADDM.GET_REPORT(:task_name) FROM dual;
@$ORACLE_HOME/rdbms/admin/addmrpt.sql
난이도: ★☆☆☆☆
문제 7: ASH report를 OEM 및 CLI 환경에서 실행하고, 결과를 분석하여라. (8분)
Every second, one row for each active session is stored in SGA and appears under V$ACTIVE_SESSION_HISTORY view. Having all the necessary information about the session such as SQL identifier and statement, execution plan, object, file and block number, wait event, module name and etc, you can view detailed statistics from the database for the specific period of time.
/* V$ACTIVE_SESSION_HISTORY is also can be used to get information about active sessions. Use DBA_HIST_ACTIVE_SESS_HISTORY view to get the history of active sessions in the past where ASH information is sampled every 10 seconds. */
@$ORACLE_HOME/rdbms/admin/ashrpt
난이도: ★★☆☆☆
문제 8: OEM을 통해 memory size advice를 확인하고, performance view를 확인하여라. (15분)
- MEMORY_TARGET 파라미터
- SGA, PGA
- Shared pool
- Buffer Cache
SELECT NAME
FROM V$FIXED_TABLE
WHERE NAME LIKE '%ADVICE%';
SELECT sga_size, sga_size_factor, estd_db_time
FROM v$sga_target_advice;
SELECT size_for_estimate, size_factor, buffers_for_estimate, estd_physical_read_time
FROM v$db_cache_advice;
SELECT shared_pool_size_for_estimate, shared_pool_size_factor, estd_lc_memory_objects, estd_lc_memory_object_hits
FROM v$shared_pool_advice;
SELECT pga_target_for_estimate, pga_target_factor, estd_extra_bytes_rw
FROM v$pga_target_advice;
난이도: ★★★☆☆
문제 9: 새로운 session을 생성하고, DBA_OBJECTS 뷰를 쿼리한다. 그리고 다른 session에서 이 쿼리를 trace하여라. (8분)
/* Sometimes, having used all the above-mentioned tuning methods, you still might not find the performance problem of specific users of the database. In this case, you have to trace and debug the session using Oracle’s tracing tools. There are a lot of different ways to trace a session in Oracle. You can use DBMS_MONITOR.SESSION_TRACE_ENABLE, DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION, DBMS_SYSTEM.SET)EV and DBMS_SUPPORT.START_TRACE_IN_SESSION procedures. */
ALTER SESSION SET TRACEFILE_IDENTIFIER = "TRACE_SESSION_2";
#현재 세션의 SID 확인
SELECT sid, serial#
FROM v$session
WHERE sid = sys_context ('USERENV', 'SID');
#특정 세션에 대한 TRACE 정보 수집을 허가
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID => 72, SERIAL_NUM => 10790, WAITS => TRUE, BINDS => FALSE);
# 아무 쿼리를 날려보기
SELECT SYSDATE FROM DUAL;
#트레이스 정보가 제대로 수집되었는지 확인한다.
cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace
ls -ltrh *TR*
more orcl_ora_4061_TRACE_SESSION_1.trc This is the raw format of the trace file.
#TKPROF를 이용하여, 트레이스 정보를 읽을 수 있는 형태로 변환한다.
tkprof orcl_ora_5118_TRACE_SESSION_2.trc trace_output.out SYS=NO
more trace_output.out
