"오라클 플랜 수집 프로그램"의 두 판 사이의 차이
DB CAFE
(→스케쥴 등록) |
(→수집결과 플랜 확인) |
||
251번째 줄: | 251번째 줄: | ||
== 수집결과 플랜 확인 == | == 수집결과 플랜 확인 == | ||
+ | |||
+ | <source lang=sql> | ||
+ | |||
+ | -- SQL PLAN VIEW | ||
+ | select a.sql_id, a.child_number child, | ||
+ | a.id | ||
+ | , a.parent_id pid | ||
+ | ---------------------------------------------------------------- | ||
+ | , lpad(' ', 2*(a.depth))||a.operation || decode(a.id,0,' (Optimizer='||a.optimizer||') ') | ||
+ | || decode(a.options,null,null,' (') || a.options || decode(a.options,null,null,')') | ||
+ | || decode(a.object_name,null,null,' OF ' || a.object_owner||'.'||a.object_name | ||
+ | || ' ('|| a.object_type ||')') | ||
+ | ----------------------------------------------------------------( | ||
+ | || decode(a.cost||a.cardinality||a.bytes,null,null,' (') | ||
+ | || decode(a.cost, null, null, ' Cost='||a.cost) | ||
+ | || decode(a.cardinality, null, null, ' Card='||a.cardinality) | ||
+ | || decode(a.bytes, null, null, ' Bytes='||a.bytes) | ||
+ | || decode(a.cost||a.cardinality||a.bytes,null,null,' )') | ||
+ | ----------------------------------------------------------------) | ||
+ | || decode(a.other_tag,null,null,'(')||a.other_tag||decode(a.other_tag,null,null,')') as "Operation" | ||
+ | --------------------------------------------------------------- | ||
+ | , a.object_type "Type" | ||
+ | ---------------------------------------------------------------- | ||
+ | , a.cardinality "E-Rows" | ||
+ | , a.bytes "E-Bytes" | ||
+ | , a.cost | ||
+ | , a.time "E-Time" | ||
+ | ---------------------------------------------------------------- | ||
+ | , a.partition_start "Pstart" | ||
+ | , a.partition_stop "Pstop" | ||
+ | , a.partition_id "Pid" | ||
+ | ---------------------------------------------------------------- | ||
+ | , a.qblock_name "QB" | ||
+ | , a.access_predicates | ||
+ | , a.filter_predicates | ||
+ | ---------------------------------------------------------------- | ||
+ | --from v$sql_plan a | ||
+ | from TB_TUNE_SQL_PLAN A --v$sql_plan a | ||
+ | where 1 = 1 | ||
+ | AND A.SNAP_ID = (SELECT MAX(SNAP_ID) FROM TB_TUNE_SQL_PLAN B WHERE B.SQL_ID = A.SQL_ID) | ||
+ | and a.sql_id = 'fwyjdtd7dudvr' --and plan_hash_value = '' | ||
+ | and a.child_number = 0 | ||
+ | order by a.sql_id, a.id, a.child_number; | ||
+ | </source> |
2019년 7월 19일 (금) 14:26 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 오라클 플랜수집 / 성능 진단 프로그램 개발[편집]
1.1 SQL 수집 프로그램 테이블[편집]
1.1.1 스냅샷 테이블 TB_TUNE_SNAP[편집]
CREATE TABLE RTIS_DBA.TB_TUNE_SNAP
(
SNAP_ID NUMBER DEFAULT 1 NOT NULL,
SNAP_START_DT VARCHAR2(16 BYTE),
SNAP_END_DT VARCHAR2(16 BYTE),
CREATED DATE DEFAULT SYSDATE NOT NULL,
UPDATED DATE DEFAULT SYSDATE NOT NULL
)
;
CREATE UNIQUE INDEX RTIS_DBA.PK_TUNE_SNAP ON RTIS_DBA.TB_TUNE_SNAP
(SNAP_ID)
;
ALTER TABLE RTIS_DBA.TB_TUNE_SNAP ADD (
CONSTRAINT PK_TUNE_SNAP
PRIMARY KEY
(SNAP_ID)
USING INDEX RTIS_DBA.PK_TUNE_SNAP
ENABLE VALIDATE);
1.1.2 SQL 테이블 TB_TUNE_SQL[편집]
CREATE TABLE RTIS_DBA.TB_TUNE_SQL
(
SQL_ID VARCHAR2(13 BYTE),
SID NUMBER,
HASH_VALUE NUMBER,
SNAP_ID NUMBER,
PLAN_HASH_VALUE NUMBER,
SERIAL# NUMBER,
BIND_DATA RAW(2000),
ADDRESS RAW(8),
MACHINE VARCHAR2(64 BYTE),
OSUSER VARCHAR2(30 BYTE),
ELAPSED_TIME NUMBER,
EXECUTIONS NUMBER,
END_OF_FETCH_COUNT NUMBER,
LAST_ACTIVE_TIME DATE,
CREATED DATE,
UPDATED DATE
);
1.1.3 SQL 쿼리 테이블 TB_TUNE_SQL_TEXT[편집]
CREATE TABLE RTIS_DBA.TB_TUNE_SQL_TEXT
(
SQL_ID VARCHAR2(13 BYTE),
SQL_TEXT VARCHAR2(1000 BYTE),
SQL_FULLTEXT CLOB
)
CREATE INDEX RTIS_DBA.IX_TUNE_SQL_TEXT_CTX ON RTIS_DBA.TB_TUNE_SQL_TEXT
(SQL_FULLTEXT)
INDEXTYPE IS CTXSYS.CONTEXT;
1.1.4 SQL 통계 테이블 TB_TUNE_SQL_STATS[편집]
CREATE TABLE RTIS_DBA.TB_TUNE_SQL_STATS
(
SNAP_ID NUMBER,
SQL_ID VARCHAR2(13 BYTE),
LAST_ACTIVE_TIME DATE,
LAST_ACTIVE_CHILD_ADDRESS RAW(8),
PLAN_HASH_VALUE NUMBER,
PARSE_CALLS NUMBER,
DISK_READS NUMBER,
DIRECT_WRITES NUMBER,
BUFFER_GETS NUMBER,
ROWS_PROCESSED NUMBER,
SERIALIZABLE_ABORTS NUMBER,
FETCHES NUMBER,
EXECUTIONS NUMBER,
END_OF_FETCH_COUNT NUMBER,
LOADS NUMBER,
VERSION_COUNT NUMBER,
INVALIDATIONS NUMBER,
CPU_TIME NUMBER,
ELAPSED_TIME NUMBER,
AVG_HARD_PARSE_TIME NUMBER,
APPLICATION_WAIT_TIME NUMBER,
CONCURRENCY_WAIT_TIME NUMBER,
USER_IO_WAIT_TIME NUMBER,
PLSQL_EXEC_TIME NUMBER,
JAVA_EXEC_TIME NUMBER,
SORTS NUMBER,
SHARABLE_MEM NUMBER,
TOTAL_SHARABLE_MEM NUMBER,
TYPECHECK_MEM NUMBER,
IO_INTERCONNECT_BYTES NUMBER,
PHYSICAL_READ_REQUESTS NUMBER,
PHYSICAL_READ_BYTES NUMBER,
PHYSICAL_WRITE_REQUESTS NUMBER,
PHYSICAL_WRITE_BYTES NUMBER,
CREATED DATE DEFAULT SYSDATE NOT NULL,
UPDATED DATE DEFAULT SYSDATE NOT NULL
)
1.1.5 바인드 변수 테이블 TB_TUNE_SQL_BIND_VAL[편집]
CREATE TABLE RTIS_DBA.TB_TUNE_SQL_BIND_VAL
(
SNAP_ID NUMBER,
ADDRESS RAW(8),
HASH_VALUE NUMBER,
SQL_ID VARCHAR2(13 BYTE),
CHILD_ADDRESS RAW(8),
CHILD_NUMBER NUMBER,
NAME VARCHAR2(120 BYTE),
POSITION NUMBER,
DATATYPE_STRING VARCHAR2(60 BYTE),
WAS_CAPTURED VARCHAR2(3 BYTE),
LAST_CAPTURED DATE,
VALUE_STRING VARCHAR2(4000 BYTE),
VALUE_ANYDATA SYS.ANYDATA,
CREATED DATE,
UPDATED DATE
)
1.1.6 실행계획 테이블 TB_TUNE_SQL_PLAN[편집]
CREATE TABLE RTIS_DBA.TB_TUNE_SQL_PLAN
(
SNAP_ID NUMBER,
ADDRESS RAW(8),
HASH_VALUE NUMBER,
SQL_ID VARCHAR2(13 BYTE),
PLAN_HASH_VALUE NUMBER,
CHILD_ADDRESS RAW(8),
CHILD_NUMBER NUMBER,
TIMESTAMP DATE,
OPERATION VARCHAR2(120 BYTE),
OPTIONS VARCHAR2(120 BYTE),
OBJECT_NODE VARCHAR2(160 BYTE),
OBJECT# NUMBER,
OBJECT_OWNER VARCHAR2(30 BYTE),
OBJECT_NAME VARCHAR2(100 BYTE),
OBJECT_ALIAS VARCHAR2(65 BYTE),
OBJECT_TYPE VARCHAR2(80 BYTE),
OPTIMIZER VARCHAR2(80 BYTE),
ID NUMBER,
PARENT_ID NUMBER,
DEPTH NUMBER,
POSITION NUMBER,
SEARCH_COLUMNS NUMBER,
COST NUMBER,
CARDINALITY NUMBER,
BYTES NUMBER,
OTHER_TAG VARCHAR2(140 BYTE),
PARTITION_START VARCHAR2(256 BYTE),
PARTITION_STOP VARCHAR2(256 BYTE),
PARTITION_ID NUMBER,
OTHER VARCHAR2(4000 BYTE),
DISTRIBUTION VARCHAR2(80 BYTE),
CPU_COST NUMBER,
IO_COST NUMBER,
TEMP_SPACE NUMBER,
ACCESS_PREDICATES VARCHAR2(4000 BYTE),
FILTER_PREDICATES VARCHAR2(4000 BYTE),
PROJECTION VARCHAR2(4000 BYTE),
TIME NUMBER,
QBLOCK_NAME VARCHAR2(30 BYTE),
CREATED DATE,
UPDATED DATE
)
1.2 스케쥴 등록[편집]
BEGIN
SYS.DBMS_SCHEDULER.DROP_JOB
(job_name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER');
END;
/
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,start_date => TO_TIMESTAMP_TZ('2019/04/23 13:30:00.000000 +09:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
,repeat_interval => 'FREQ=MINUTELY;INTERVAL=30'
,end_date => NULL
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'PLSQL_BLOCK'
,job_action => 'BEGIN RTIS_DBA.sp_dbm_sql_gather(); END;'
,comments => 'SQL GATEHRING FOR 5MIN EVERY DAY'
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,attribute => 'RESTARTABLE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,attribute => 'LOGGING_LEVEL'
,value => SYS.DBMS_SCHEDULER.LOGGING_OFF);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,attribute => 'MAX_FAILURES');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,attribute => 'MAX_RUNS');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,attribute => 'STOP_ON_WINDOW_CLOSE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,attribute => 'JOB_PRIORITY'
,value => 3);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,attribute => 'SCHEDULE_LIMIT');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,attribute => 'AUTO_DROP'
,value => TRUE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,attribute => 'RESTART_ON_RECOVERY'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,attribute => 'RESTART_ON_FAILURE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER'
,attribute => 'STORE_OUTPUT'
,value => TRUE);
SYS.DBMS_SCHEDULER.ENABLE
(name => 'RTIS_DBA.SCH_RTIS_SQL_GATHER');
END;
/
1.3 수집결과 플랜 확인[편집]
-- SQL PLAN VIEW
select a.sql_id, a.child_number child,
a.id
, a.parent_id pid
----------------------------------------------------------------
, lpad(' ', 2*(a.depth))||a.operation || decode(a.id,0,' (Optimizer='||a.optimizer||') ')
|| decode(a.options,null,null,' (') || a.options || decode(a.options,null,null,')')
|| decode(a.object_name,null,null,' OF ' || a.object_owner||'.'||a.object_name
|| ' ('|| a.object_type ||')')
----------------------------------------------------------------(
|| decode(a.cost||a.cardinality||a.bytes,null,null,' (')
|| decode(a.cost, null, null, ' Cost='||a.cost)
|| decode(a.cardinality, null, null, ' Card='||a.cardinality)
|| decode(a.bytes, null, null, ' Bytes='||a.bytes)
|| decode(a.cost||a.cardinality||a.bytes,null,null,' )')
----------------------------------------------------------------)
|| decode(a.other_tag,null,null,'(')||a.other_tag||decode(a.other_tag,null,null,')') as "Operation"
---------------------------------------------------------------
, a.object_type "Type"
----------------------------------------------------------------
, a.cardinality "E-Rows"
, a.bytes "E-Bytes"
, a.cost
, a.time "E-Time"
----------------------------------------------------------------
, a.partition_start "Pstart"
, a.partition_stop "Pstop"
, a.partition_id "Pid"
----------------------------------------------------------------
, a.qblock_name "QB"
, a.access_predicates
, a.filter_predicates
----------------------------------------------------------------
--from v$sql_plan a
from TB_TUNE_SQL_PLAN A --v$sql_plan a
where 1 = 1
AND A.SNAP_ID = (SELECT MAX(SNAP_ID) FROM TB_TUNE_SQL_PLAN B WHERE B.SQL_ID = A.SQL_ID)
and a.sql_id = 'fwyjdtd7dudvr' --and plan_hash_value = ''
and a.child_number = 0
order by a.sql_id, a.id, a.child_number;