index는 oracle내의 table data를 access 하기 위한 access patch를 제공하는 segment 입니다.
즉, table의 data access를 index를 참조해서 scan하게 됩니다.
따라서 index 생성시 자주 scan 되는 방식으로 index를 구성하면 굳이 query 시 테이블 데이터를 순차적으로 ordering 할 필요가 없게 됩니다. 다음의 예제 처럼 SCOTT의 EMP table이 SAL의 역순으로 자주 query가 수행된다면 다음처럼 역순으로 index를 생성할 수 있습니다.
create index IDX_EMP on SCOTT.EMP(DEPTNO, SAL DESC);
위의 구문으로 index를 생성한다면 query 시 해당 index를 이용하게끔 만들어 준다면 order by 등의 구문과 그에 따른 sorting 작업은 필요없게 됩니다.
그런데, "SAL DESC"는 어떤 index column 이름을 갖게 될까요?
SAL_DESC 등의 이름을 갖는다면 dba_ind_column에서 대충 해당 column이 table의 어떤 column으로 부터 만들어 졌는지 확인할수 있겠지만, oracle은 이와 같은 index column이름을 SYS_NCnnnnn$라는 형식의 이름으로 생성하게 됩니다. 이와 같이 oracle이 자체적으로 이름을 생성하는 것은 대개 constraint 생성할 때 이름을 지정하지 않는 경우에 많이 볼수 있습니다.
SQL> select index_name, column_name, descend from dba_ind_columns where index_name='I_EMP'
SQL> /
Oracle AWR 데이터는 정해진 interval 마다 oracle statistic 값을 retention 기간만큼 보관하게 됩니다.
따라서 해당 시점에 발생된 statistic 값을 보려면 관련 table에 대해 self join을 해야하는데,
이때 편하게 쓸 수 있는 oracle 함수가 LAG 합수 입니다.
LAG is an analytic function. It provides access to more
than one row of a table at the same time without a self join. Given a
series of rows returned from a query and a position of the cursor, LAG provides access to a row at a given physical offset prior to that position.
If you do not specify offset, then its default is 1. The optional default value is returned if the offset goes beyond the scope of the window. If you do not specify default, then its default is null.
You cannot nest analytic functions by using LAG or any other analytic function for value_expr. However, you can use other built-in function expressions for value_expr.
[참고 ] Oracle 8.1.7 SQL reference [ LAG 함수 ]
간단히 예를 들면..
다음 처럼 DB_TIME 값의 변경 내역을 알고 싶다면 동일한 table에 대한 self join이 필요한데,
이 lag over 함수를 사용하면 join이 필요없어 집니다.
select a.snap_id,a.stat_name,a.value, b.value , a.value - b.value
from
DBA_HIST_SYS_TIME_MODEL a,
DBA_HIST_SYS_TIME_MODEL b
where a.snap_id = b.snap_id + 1
and a.stat_id = 3649082374
and a.stat_id = b.stat_id
/
select snap_id,stat_name,value, lag(value) over (order by snap_id),
value - nvl(lag(value) over (order by snap_id),0)
from DBA_HIST_SYS_TIME_MODEL
where stat_id = 3649082374
/
오라클 운영시 특정 세션이나 프로세스가 resource를 과도하게 소모하거나, 속도 저하 등의 이상이 발생해 다른 세션들 까지 영향을 주는 경우가 종종 발생하곤합니다. 이럴 때 해당 세션이나 프로세스의 문제를 해결하기 위해 시간을 끌다가는 전체 업무에 주는 영향으로 인해 더 큰 문제가 발생할 수 있죠.
다음은 개별 세션이나 프로세스가 이상이 있을때 가징 기본적으로 수집해야 하는 trace/dump를 수행하는 방법입니다. 알아서 관련 정보를 다 수집해 주면 좋겠으나, 일일히 관련 정보를 수집해야 하니 좀 번거로울 수 있겠으나, 이런 작업들을 안해 놓으면 문제가 미궁으로 빠질 수 있으므로, 미리미리 알아 놓는 게 좋겠죠..
오라클은 많은 trace/dump 수행 방법을 제공하고 있는데, 다음의 event 10046, errorstack은 개별 세션의 정보를 수집합니다. 물론 이 두개의 trace/dump를 수행하고 전체적인 systemstate dump나 hanganalyze도 같이 수행을 하면 전체적인 영향까지도 확인 할 수 있겠죠.
10046 event는 일반적인 SQL_TRACE 기능을 수행합니다. 또 특정 level을 지정해주면 해당 세션의 wait event와 bind value까지 tracing 해줍니다.
1 - Enable standard SQL_TRACE functionality (Default)
4 - As Level 1 PLUS trace bind values
8 - As Level 1 PLUS trace waits
12 - As Level 1 PLUS both trace bind values and waits
ERRORSTACK dump은 해당 process의 현재 상태 및 정보를 수집합니다. SQL 구문과 Call stack, errorstack, processstate, context area 정보를 수집합니다.
0 Error stack only (no longer be a valid option)
1 Error stack and function call stack (if implemented)
2 As 1 plus the ProcessState
3 As 2 plus the context area (all cursors and current cursor highlighted)
다음은 oradebug를 이용해 10046 event를 on/off 시키는 방법입니다.
event를 on 시킨 후 충분한 데이터를 수집했다고 판단되면 off 시키면 되며, 결과물은 sql_trace 처럼 tkprof로 정리해서 볼수 있다.
* 10046 tracing on
sqlplus '/as sysdba' oradebug에 세션이나 프로세스를 지정할 때 oracle pid나 o/s pid, 자기자신의 process를 지정할 수 있다.
따라서 위의 3가지 중 하나를 지정하면 된다.
SQL>oradebug setorapid [Oracle process id]
SQL>oradebug setospid [O/S process id]
SQL>oradebug setmypid trace 발생양에 대한 제한을 unlimit로 지정한다.
SQL>oradebug unlimit event 10046을 level 12로 지정한다.
SQL>oradebug Event 10046 trace name context forever, level 12 생성한 trace file이름을 출력한다.
SQL>oradebug tracefile_name
SQL>exit
* 10046 trace off
sqlplus '/as sysdba' 10046 trace 지정시 사용한 oracle pid나 o/s pid, 자기자신의 process를 지정 한다.
SQL>oradebug setorapid [Oracle process id]
SQL>oradebug setospid [O/S process id]
SQL>oradebug setmypid event 10046 설정을 해지한다.
SQL>oradebug Event 10046 trace name context off
SQL>exit
다음은 oradebug를 이용해 ERRORSTACK dump를 수행하는 방법입니다. ERRORSTACK은 해당 시점의 프로세스 정보를 수집하는 것이므로 on/off 단계는 필요 없이 한번만 수행해 주면 됩니다.
* ERRORSTACK dump
sqlplus '/as sysdba' oradebug에 세션이나 프로세스를 지정할 때 oracle pid나 o/s pid, 자기자신의 process를 지정할 수 있다.
따라서 위의 3가지 중 하나를 지정하면 된다.
SQL>oradebug setorapid [Oracle process id]
SQL>oradebug setospid [O/S process id]
SQL>oradebug setmypid trace 발생양에 대한 제한을 unlimit로 지정한다.
SQL>oradebug unlimit errorstack dump를 level 3으로 수행한다.
SQL>oradebug dump errorstack 3 생성한 trace file이름을 출력한다.
SQL>oradebug tracefile_name
SQL>exit
가끔 sqlplus 자체가 수행이 되지 않아 trace/dump 수행이 어려운 경우가 있는데, 이러한 경우 sqlplus의 -prelim option을 사용하거나 debugger tool인 dbx를 사용해서 errorstack을 수행할 수 있습니다.
dbx debugger를 이용한 process의 call stack 보기.
dbx -a $pid
dbx) where
dbx) detach
* 반드시 detach 하지 않을 경우 attach 한 프로세스는 비정상 종료된다.
* -a flag는 해당 프로세스에 attach 하여 dbx 시행하는 동안 해당 프로세스는 실행이 중지되므로 background process에 대한 attach는 피하는 편이 신상에 좋습니다.. --;
dbx debugger를 이용한 core file의 call stack 보기.
dbx program명 core
dbx) where
dbx) quit
위의 내용은 oracle metalink note에서 참고했습니다.
Note.21154.1 EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
Note.37871.1 EVENT: ERRORSTACK - How to get
Note 1058210.6 How to enable 10046 for another session using ORADEBUG
Note 62294.1 The DBMS_SUPPORT Package
Note 35054.1 Interpreting ERRORSTACK output
Note 39817.1 Interpreting RAW 10046 output
오라클에서 수행되는 cursor의 수행 정보와 이와 관련된 각종 statistic 정보를 확인할 수 있는 view가 몇개 있습니다.
다음은 해당 view에 대한 비교와 column 정보들, 그리고 해당 view를 이용한 몇개의 sql script 들입니다.
V$SQL_PLAN_STATISTICS:
각 operation에 대한 execution statistics 정보를 제공한다.
1)EXECUTIONS:
Child cursor의 수행된 횟수 이다. 따라서 동일한 SQL이더라도 optimizer 환경이나 bind variable size 등의 변경에 의해 다른 child cursor를 가질 수 있으므로 이에 대한 고려도 필요하다.
두개의 세션에서 동일한 SQL을 2회 수행하는 경우나, 같은 세션에서 동일한 SQL을 2회 수행한 경우 동일하게 2의 값을 갖는다.
2)LAST_OUTPUT_ROWS and OUTPUT_ROWS:
LAST_OUTPUT_ROWS는 해당 operation에 의해 마지막에 처리된 실제 row 수 이며, OUTPUT_ROWS는 누적 합이다.
3)LAST_DISK_READS and DISK_READS:
LAST_DISK_READS는 해당 operation이 disk를 읽은 횟수를 의미하며, DISK_READS는 누적 합이다.
V$SQL_PLAN_STATISTICS_ALL:
다음의 관련 view의 데이터에 대한 전체적인 정보를 갖는다.
V$SQL_PLAN (all operations, objects, cost, cardinality etc)
V$SQL_PLAN_STATISTICS (execution statistics as above)
V$SQL_WORKAREA (memory usage)
1) estimated_optimal_size
해당 operation을 메모리상에서 처리하기 위해 측정된 memory size (KB)
2) last_memory_used
cursor의 마지막 execution 동안 사용된 memory (KB)
3) active_time
Average active time (in centi-seconds)
다음의 SQL script는 특정 SQL cursor에 대한 plan과 관련 statistic 정보를 보여줍니다.
set pagesize 600
set tab off
set linesize 140
set echo off
set long 4000
col TQID format A4
col "SLAVE SQL" format A95 WORD_WRAP
col address format A12
col sql_hash format A15
col exec format 9999
col sql_text format A75 WORD_WRAP
repfooter off;
set timing off veri off space 1 flush on pause off termout on
numwidth 10;
alter session set "_complex_view_merging"=false;
select hash_value||decode(child_number, 0, '', '/'||child_number)
sql_hash,
sql_text
from v$sql
where child_number= 0 and hash_value= &hashvalue;
select '| Operation | Name | Starts | E-Rows | A-Rows | Buffers |
Reads | Writes | E-Time |' as "Plan Table" from dual
union all /* QWEKLOIPYRTJHH7 */
select
'------------------------------------------------------------------------------------------------------------------------'
from dual
union all
select rpad('| '||substr(lpad(' ',1*(depth))||operation||
decode(options, null,'',' '||options), 1, 33), 34, ' ')||'|'||
rpad(substr(object_name||' ',1, 19), 20, ' ')||'|'||
lpad(decode(starts,null,' ',
decode(sign(starts-1000), -1, starts||' ',
decode(sign(starts-1000000), -1, round(starts/1000)||'K',
decode(sign(starts-1000000000), -1, round(starts/1000000)||'M',
round(starts/1000000000)||'G')))), 8, ' ') || '|' ||
lpad(decode(cardinality,null,' ',
decode(sign(cardinality-1000), -1, cardinality||' ',
decode(sign(cardinality-1000000), -1, round(cardinality/1000)||'K',
decode(sign(cardinality-1000000000), -1, round(cardinality/1000000)||'M',
round(cardinality/1000000000)||'G')))), 8, ' ') || '|' ||
lpad(decode(outrows,null,' ',
decode(sign(outrows-1000), -1, outrows||' ',
decode(sign(outrows-1000000), -1, round(outrows/1000)||'K',
decode(sign(outrows-1000000000), -1, round(outrows/1000000)||'M',
round(outrows/1000000000)||'G')))), 8, ' ') || '|' ||
lpad(decode(crgets,null,' ',
decode(sign(crgets-10000000), -1, crgets||' ',
decode(sign(crgets-1000000000), -1, round(crgets/1000000)||'M',
round(crgets/1000000000)||'G'))), 9, ' ') || '|' ||
lpad(decode(reads,null,' ',
decode(sign(reads-10000000), -1, reads||' ',
decode(sign(reads-1000000000), -1, round(reads/1000000)||'M',
round(reads/1000000000)||'G'))), 8, ' ') || '|' ||
lpad(decode(writes,null,' ',
decode(sign(writes-10000000), -1, writes||' ',
decode(sign(writes-1000000000), -1, round(writes/1000000)||'M',
round(writes/1000000000)||'G'))), 8, ' ') || '|' ||
lpad(decode(etime,null,' ',
decode(sign(etime-10000000), -1, etime||' ',
decode(sign(etime-1000000000), -1, round(etime/1000000)||'M',
round(etime/1000000000)||'G'))), 8, ' ') || '|' as "Explain
plan"
from
(select /*+ no_merge */
p.HASH_VALUE, p.ID, p.DEPTH, p.POSITION, p.OPERATION,
p.OPTIONS, p.COST COST, p.CARDINALITY CARDINALITY,
p.BYTES BYTES, p.OBJECT_NODE, p.OBJECT_OWNER,
p.OBJECT_NAME, p.OTHER_TAG, p.PARTITION_START,
p.PARTITION_STOP, p.DISTRIBUTION, pa.starts,
pa.OUTPUT_ROWS outrows, pa.CR_BUFFER_GETS crgets,
pa.DISK_READS reads, pa.DISK_WRITES writes,
pa.ELAPSED_TIME etime
from
v$sql_plan_statistics_all pa,
V$sql_plan p
where p.hash_value =
&hashvalue
and
p.CHILD_NUMBER= 0
and
p.hash_value = pa.hash_value(+)
and
pa.child_number(+) = 0 )
union all
select
'------------------------------------------------------------------------------------------------------------------------'
from dual;
REM
REM Print slave sql
REM
select /* QWEKLOIPYRTJHH7 */
decode(object_node,null,'', substr(object_node,length(object_node)-3,1)
|| ',' ||
substr(object_node,length(object_node)-1,2)) TQID,
other
"SLAVE SQL"
from v$sql_plan vp
where other is not NULL
and hash_value = &hash_value
and CHILD_NUMBER= 0;
qeps.sql과 qep.sql을 생성해 수행하면 SQL의 성능을 평가하는 8개의 항목에 대해 지정된 갯수의 SQL과 그에 따른 statistic 정보를 보여줍니다. init parameter인 statistics_level를 ALL로 설정해야 정상적인 결과를 보여줍니다.
--
-- Script: qeps.sql
--
-- Script to report the explain plan for the most expensive N SQL statements
-- based on user specified criteria:
--
-- buffer_gets - 1
-- CPU time - 2
-- disk_reads - 3
-- rows_processed - 4
-- executions - 5
-- parse calls - 6
-- Buffers/Exec - 7
-- Cost per row - 8
--
-- Usage: start qeps.sql
--
-- This scripts requires qep.sql in order to function.
-- See Oracle Metalink Note: 550578.1 for more detail.
--
SET ECHO OFF
PROMPT
PROMPT Starting QEPS.SQL
PROMPT
PROMPT NOTES:
PROMPT
PROMPT The database parameter statistics_level should be set to ALL
PROMPT to obtain full statistics in the plan output.
PROMPT
PROMPT Script only works with Oracle 9.2 and above
PROMPT
PROMPT Script must be run from a database account with access to:
PROMPT
PROMPT . gv$sql_plan
PROMPT . gv$sqltext_with_newlines
PROMPT . gv$sql_plan_statistics_all
PROMPT
PROMPT Requires the partner script qep.sql
PROMPT
SET HEAD OFF
SET SERVEROUT OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET TIMING OFF
SET PAUSE OFF
SET PAGESIZE 0
prompt Available Expense Criteria:
prompt
prompt buffer_gets - 1
prompt CPU time - 2
prompt disk_reads - 3
prompt rows_processed - 4
prompt executions - 5
prompt parse calls - 6
prompt Buffers/Exec - 7
prompt Cost per row - 8
prompt
accept sec_opt prompt "Please select required expense criteria [7]: "
accept top_n prompt 'Please enter the number of SQL Statements to report [5]: '
SET TERMOUT OFF
SPOOL gen_plans.sql
SELECT /* QWEKLOIPYRTJHH7 */
'define instid=' ||'''' || inst_id ||'''' || CHR(10) ||
'define hash_value=' ||'''' || hash_value ||'''' || CHR(10) ||
'define address=' ||'''' || address ||'''' || CHR(10) ||
'define child_number='||'''' || child_number ||'''' || CHR(10) ||
'@qep'
FROM
(SELECT inst_id, hash_value, child_number, address, buffer_gets
FROM gv$sql
WHERE sql_text NOT LIKE '%QWEKLOIPYRTJHH7%'
AND (UPPER(sql_text) like 'SELECT%'
OR UPPER(sql_text) like 'UPDATE%'
OR UPPER(sql_text) like 'DELETE%'
OR UPPER(sql_text) like 'INSERT%')
ORDER BY
DECODE('&sec_opt',
NULL,
buffer_gets / decode(greatest(rows_processed,executions),0,1,
greatest(rows_processed,executions)),
1, buffer_gets,
2, cpu_time,
3, disk_reads,
4, rows_processed,
5, executions,
6, parse_calls,
7, buffer_gets / decode(executions,0,1, executions),
8, buffer_gets / decode(greatest(rows_processed,executions),0,1,
greatest(rows_processed,executions))) DESC )
WHERE rownum < DECODE(TO_NUMBER('&&top_n'),NULL, 6, &&top_n + 1)
/
SPOOL OFF
SET HEAD ON PAGESIZE 66
--
-- Setup Sort Opt descr for main report
--
BREAK ON SORTOPT
COLUMN SORTOPT NEW_VALUE SORTOPT_VAR
SELECT /* QWEKLOIPYRTJHH7 */
decode(NVL('&sec_opt',7)
,1,'Buffer Gets',
2,'CPU time',
3,'Disk Reads',
4,'Rows Processed',
5,'Executions',
6,'Parse Calls',
7,'Buffers/Exec',
8,'Cost per Row') SORTOPT
FROM DUAL;
CLEAR BREAKS
COLUMN instance_name NEW_VALUE instance
SELECT instance_name
FROM v$instance;
SPOOL qeps_&instance._&file_date_var
TTITLE
left today skip 2 center 'Top &&top_n Performing SQL Statements
for Database Instance &&instance by &&sortopt_var '
skip 2
SET TERMOUT ON
@gen_plans
spool
spool off
--
-- Script:
qep.sql
--
-- This script is called via the qeps.sql script. Please refer to Oracle
-- Metalink Note: 550578.1 for more detail.
--
set pagesize 600
set tab off
set linesize 133
set echo off
set long 4000
col Inst_ID format 999 heading "Inst ID"
col TQID format A4
col "SLAVE SQL" format A95 WORD_WRAP
col address format A12 heading "SQL Address"
col sql_hash format A15 heading "SQL Hash"
col buffer_gets heading "Buffer|Gets "
col exec format 9999
col disk_reads heading "Disk |Reads"
col sorts heading "Sorts"
col rows_processed heading "Rows |Processed"
col parse_calls heading "Parse|Calls"
col cpu_time format 9999999.99 heading "CPU Time|(Secs)"
col executions format 999,999,999 heading "Execs"
col sql_text format A80 heading "SQL Text"
repfooter off;
SET TIMING OFF VERI OFF SPACE 1 FLUSH ON PAUSE OFF TERMOUT ON NUMWIDTH 10;
ALTER SESSION SET "_complex_view_merging"=FALSE;
SELECT /* QWEKLOIPYRTJHH7 */ inst_id, address,
hash_value||DECODE(child_number, 0,
'', '/'||child_number) sql_hash,
executions,
parse_calls,
buffer_gets,
disk_reads,
rows_processed,
cpu_time/1000000 as cpu_time,
sorts
FROM gv$sql
WHERE inst_id = '&instid'
AND hash_value = '&hash_value'
AND child_number = '&child_number';
set head off
TTITLE OFF
SELECT /* QWEKLOIPYRTJHH7 */ REPLACE(sql_text,CHR(10),' ') as sql_text
FROM gv$sqltext_with_newlines
WHERE inst_id = '&instid'
AND address = '&address'
ORDER BY piece;
set head on
PROMPT
PROMPT C-Rows => Cardinality O-Rows => Output Rows E-Time => Elapsed Time (Secs)
SELECT RPAD('| Operation',45) || RPAD('| Name',25) || ' |C-Rows|O-Rows|IN-OUT| Buffers |'
|| RPAD(' Reads',9) || RPAD('| Writes',10) || RPAD('| E-Time',10) || '|'
AS "Plan Table"
FROM dual
UNION ALL /* QWEKLOIPYRTJHH7 */
SELECT
'-------------------------------------------------------------------------------------------------------------------------------------'
FROM dual
UNION ALL
SELECT RPAD('| '||substr(LPAD(' ',1*(depth))||operation|| DECODE(options, null,'',' '||options), 1, 44), 45, ' ')||'|'||
RPAD(substr(object_name||' ',1, 25), 25, ' ')||'|'||
LPAD(DECODE(cardinality,null,' ',
DECODE(sign(cardinality-1000), -1, cardinality||' ',
DECODE(sign(cardinality-1000000), -1, round(cardinality/1000)||'K',
DECODE(sign(cardinality-1000000000), -1, round(cardinality/1000000)||'M',
round(cardinality/1000000000)||'G')))), 6, ' ') || '|' ||
LPAD(DECODE(outrows,null,' ',
DECODE(sign(outrows-1000), -1, outrows||' ',
DECODE(sign(outrows-1000000), -1, round(outrows/1000)||'K',
DECODE(sign(outrows-1000000000), -1, round(outrows/1000000)||'M',
round(outrows/1000000000)||'G')))), 6, ' ') || '|' ||
RPAD(DECODE(other_tag,
'PARALLEL_TO_SERIAL', ' P->S',
'PARALLEL_FROM_SERIAL', ' P<-S',
'PARALLEL_FROM_REMOTE', ' P<-R',
'PARALLEL_TO_PARALLEL', ' P->P',
'PARALLEL_COMBINED_WITH_PARENT',' PCWP',' '),6,' ') || '|' ||
LPAD(DECODE(crgets,null,' ',
DECODE(sign(crgets-10000000), -1, crgets||' ',
DECODE(sign(crgets-1000000000), -1, round(crgets/1000000)||'M',
round(crgets/1000000000)||'G'))), 9, ' ') || '|' ||
LPAD(DECODE(reads,null,' ',
DECODE(sign(reads-10000000), -1, reads||' ',
DECODE(sign(reads-1000000000), -1, round(reads/1000000)||'M',
round(reads/1000000000)||'G'))), 9, ' ') || '|' ||
LPAD(DECODE(writes,null,' ',
DECODE(sign(writes-10000000), -1, writes||' ',
DECODE(sign(writes-1000000000), -1, round(writes/1000000)||'M',
round(writes/1000000000)||'G'))), 9, ' ') || '|' ||
REPLACE(TO_CHAR(NVL(etime/1000000,0), '99990.99'),' 0.00',' ') ||
'|' AS "Explain plan"
FROM
(SELECT /*+ no_merge */ /* QWEKLOIPYRTJHH7 */
p.HASH_VALUE, p.ID, p.DEPTH, p.POSITION, p.OPERATION,
p.OPTIONS, p.COST COST, p.CARDINALITY CARDINALITY,
p.BYTES BYTES, p.OBJECT_NODE, p.OBJECT_OWNER,
p.OBJECT_NAME, p.OTHER_TAG, p.PARTITION_START,
p.PARTITION_STOP, p.DISTRIBUTION, pa.starts,
pa.OUTPUT_ROWS outrows, pa.CR_BUFFER_GETS crgets,
pa.DISK_READS reads, pa.DISK_WRITES writes,
pa.ELAPSED_TIME etime
FROM gv$sql_plan_statistics_all pa,
gv$sql_plan p
WHERE p.inst_id = '&instid'
AND pa.inst_id = '&instid'
AND p.hash_value = '&hash_value'
AND p.child_number = '&child_number'
AND pa.child_number(+) = '&child_number'
AND p.id = pa.id(+)
AND p.address = pa.address(+)
ORDER BY p.id)
UNION ALL
SELECT
'-------------------------------------------------------------------------------------------------------------------------------------'
FROM dual;
REM
REM Print slave sql
REM
SELECT /* QWEKLOIPYRTJHH7 */
DECODE(object_node,null,'', substr(object_node,length(object_node)-3,1) || ',' ||
substr(object_node,length(object_node)-1,2)) TQID,
other "Slave SQL"
FROM gv$sql_plan vp
WHERE vp.inst_id = '&instid'
AND vp.other IS NOT NULL
AND vp.hash_value = '&hash_value'
AND vp.child_number = '&child_number';
PROMPT
************************************************************************************************************************************
PROMPT
01/25/2008
Top
Performing SQL Statements for Database Instance V102 by
Buffers/Exec
Parse
Buffer Disk Rows CPU Time
Inst
ID SQL Address SQL Hash Execs Calls Gets
Reads Processed (Secs) Sorts
-------
------------ --------------- ------------ ---------- ----------
---------- ---------- ----------- ----------
1
31ADC51C 2327026800 1 1 3835
778 0 .58 0
select
o.owner#,o.obj#,decode(o.linkname,null,
decode(u.name,nul
l,'SYS',u.name),o.remoteowner),
o.name,o.linkname,o.namespace,o.
subname
from user$ u, obj$ o where u.user#(+)=o.owner# and
o.typ
e#=:1
and not exists (select p_obj# from dependency$ where
p_obj
#
= o.obj#) order by o.obj# for
update
Parse
Buffer Disk Rows CPU Time
Inst
ID SQL Address SQL Hash Execs Calls Gets
Reads Processed (Secs) Sorts
-------
------------ --------------- ------------ ---------- ----------
---------- ---------- ----------- ----------
1
319B23DC 1187151836 1 1 920
32 1 .09 0
select
ee.ectx#, o.owner#, u.name, o.name, ee.num_rules,
ee.num_
boxes,
ee.ee_flags from rule_set_ee$ ee, obj$ o, user$ u
where
ee.rs_obj#
= :1 and ee.ectx# = o.obj# and o.owner# =
u.user#
Parse
Buffer Disk Rows CPU Time
Inst
ID SQL Address SQL Hash Execs Calls Gets
Reads Processed (Secs) Sorts
-------
------------ --------------- ------------ ---------- ----------
---------- ---------- ----------- ----------
1
31A63F14 2372567631 1 1 867
803 0 .21 0
select
o.obj#, u.name, o.name, t.spare1,
DECODE(bitand(t
.flags,
268435456), 268435456, t.initrans, t.pctfree$) from
s
ys.obj$
o, sys.user$ u, sys.tab$ t where
(bitand(t.trigflag,
1048576)
= 1048576) and o.obj#=t.obj# and o.owner#
=
u.user#
Parse
Buffer Disk Rows CPU Time
Inst
ID SQL Address SQL Hash Execs Calls Gets
Reads Processed (Secs) Sorts
-------
------------ --------------- ------------ ---------- ----------
---------- ---------- ----------- ----------
1
31A9A254 162926978 1 1 764
113 0 .12 0
select
table_objno, primary_instance, secondary_instance,
owner_
instance
from sys.aq$_queue_table_affinities a where
a.owner_i
nstance
<> :1 and
dbms_aqadm_syscalls.get_owner_instance(a.prima
ry_instance,
a.secondary_instance,
a.owner_instance)
=
:2 order by table_objno for update of a.owner_instance
skip
locked
Plan
Table
-------------------------------------------------------------------------------------------------------------------------------------
|
Operation | Name
|C-Rows|O-Rows|IN-OUT| Buffers | Reads | Writes | E-Time |
-------------------------------------------------------------------------------------------------------------------------------------
|
FOR UPDATE |
| | 0 | | 371 | 19 | 0 | 0.06|
|
TABLE ACCESS BY INDEX ROWID
|AQ$_QUEUE_TABLE_AFFINITIE| | 0 | | 371 | 19
| 0 | 0.06|
| INDEX FULL SCAN
|AQ$_QTABLE_AFFINITIES_PK | | 14 | | 1 | 1
| 0 | |
-------------------------------------------------------------------------------------------------------------------------------------
************************************************************************************************************************************
currently spooling to qeps_V102_20080125_213802.txt
참고 :
Note 186548.1 9i Release 2 Cached cursors information in the row source level
Note 260942.1 Display Execution plans from Statement's in V$SQL_PLAN
Note 550578.1 How to Obtain the most Resource Intensive SQL Execution Plans using the Libr...
AWR table map을 찾던 중 "A Tour of the AWR Tables"라는 NOCOUG Summer Conference에서 소개된 자료를 확인할 수 있었습니다. 제목을 보니 AWR관련 table에 대한 여러 힌트를 얻을 수 있을 듯해서 자료를 보니 "load spike"를 찾기 위한 좀 다른 방법을 설명하고 있네요.
이전 " oracle awr 설정 시 고려해야 할 점" post에서 강호동, 이건희 회장까지 등장시키며 AWR 자료 수집 주기를 15 ~20분으로 줄여야한다고 글을 썼는데, 이 글의 저자는 "In my experience, the hourly interval is appropriate." 라고 하더군요. 저자는 DBA_HIST_SYSSTAT의 DB time, elapsed time 등의 load profile 자료를 사용하지 않고 1초마다 수집되는 ASH의 자료를 이용해 "load spike"를 찾는 방법을 제시하고 있습니다.
오옷 .. 그런 방법이..
근데, 그 외의 성능관련 정보는 어떻게... --;
추가적인 AWR 관련 table을 이용한 문제가 될만한 SQL 찾는 방법 등도 같이 소개합니다.
Average Active Session이 튀는 시간 찾기.
column sample_hour format a16
select
to_char(round(sub1.sample_time, 'HH24'), 'YYYY-MM-DD HH24:MI') as sample_hour,
round(avg(sub1.on_cpu),1) as cpu_avg,
round(avg(sub1.waiting),1) as wait_avg,
round(avg(sub1.active_sessions),1) as act_avg,
round( (variance(sub1.active_sessions)/avg(sub1.active_sessions)),1) as act_var_mean
from
( -- sub1: one row per second, the resolution of SAMPLE_TIME
select
sample_id,
sample_time,
sum(decode(session_state, 'ON CPU', 1, 0)) as on_cpu,
sum(decode(session_state, 'WAITING', 1, 0)) as waiting,
count(*) as active_sessions
from
dba_hist_active_sess_history
where
sample_time > sysdate - (&hours/24)
group by
sample_id,
sample_time
) sub1
group by
round(sub1.sample_time, 'HH24')
order by
round(sub1.sample_time, 'HH24')
;
select
to_char(round(sub1.sample_time, 'MI'), 'YYYY-MM-DD HH24:MI') as sample_minute,
round(avg(sub1.on_cpu),1) as cpu_avg,
round(avg(sub1.waiting),1) as wait_avg,
round(avg(sub1.active_sessions),1) as act_avg,
round( (variance(sub1.active_sessions)/avg(sub1.active_sessions)),1) as act_var_mean
from
( -- sub1: one row per second, the resolution of SAMPLE_TIME
select
sample_id,
sample_time,
sum(decode(session_state, 'ON CPU', 1, 0)) as on_cpu,
sum(decode(session_state, 'WAITING', 1, 0)) as waiting,
count(*) as active_sessions
from
v$active_session_history
where
sample_time > sysdate - (&minutes/1440)
group by
sample_id,
sample_time
) sub1
group by
round(sub1.sample_time, 'MI')
order by
round(sub1.sample_time, 'MI')
;
Oracle AWR 관련 table 중 SQL에 관련된 table 들과 reference key를 그린 그림입니다.
다음의 table join으로 과거의 SQL 수행 정보 등을 찾을 수 있습니다.
아래의 SQL은 Oracle Note에 나온 AWR 수집 데이터에서 SQL 관련 정보를 추출하는 SQL 입니다.
SQL ordered by CPU time:
column sqltext format a65
set pages 60
set lines 132
set trims on
select x.*, dbms_lob.substr(d.sql_text) sqltext
from (
select distinct sql.dbid, sql.sql_id, sum(sql.cpu_time_total)
cpu_total
from dba_hist_sqlstat sql
where sql.snap_id between &begin_id and &end_id
group by
sql.dbid, sql.sql_id
order by
sum(sql.cpu_time_total) desc
) x,
dba_hist_sqltext d
where d.sql_id = x.sql_id
and d.dbid = x.dbid
and rownum <= 10;
SQL ordered by Executions:
column sqltext format a30
select distinct sql.sql_id, dbms_lob.substr(d.sql_text) sqltext, sum(sql.executions_total) from
dba_hist_sqlstat sql,
dba_hist_snapshot s,
dba_hist_sqltext d
where
s.snap_id between &begin_id and &end_id
and rownum <= 10
group by sql.sql_id, dbms_lob.substr(d.sql_text)
SQL ordered by Disk Reads:
column sqltext format a30
select distinct sql.sql_id, dbms_lob.substr(d.sql_text) sqltext, sum(sql.
disk_reads_total) from
dba_hist_sqlstat sql,
dba_hist_snapshot s,
dba_hist_sqltext d
where
s.snap_id between &begin_id and &end_id
and rownum <= 10
group by sql.sql_id, dbms_lob.substr(d.sql_text)
이 그룹에 주어진 평균 수치가 만약 몸무게나 키, 허리 둘레 등에 대한 평균이라고 한다면,
이 그룹 중 강호동처럼 (몸무게가 어떤지는 잘은 모릅니다^^;) 몸무게가 다른 사람들 보다 많이 나가는 사람을 찾을 수 있을가요?
물론 평균 수치가 재산, 보유주택 가격 등에 대한 평균이고 이 중 이건희 회장 같은 사람이 있다면 당연히 그 평균값은 거의 한 사람의 값과 같겠죠. 따라서 일반적이지 않은 사람이 섞여있다고 판단할 수 있습니다.
(이 내용은 [블랙스완]이란 책에서 경제학자들의 일반화 오류에 대한 예입니다. 물론 거긴 강호동이나 이건희 회장은 나오지 않습니다만...ㅋㅋ)
database system 내에서 수행되는 많은 AP들이 있습니다.
AWR은 현황에 대한 분석 뿐 아니라 많은 경우 비 정상적으로 문제를 발생하는 AP 등을 찾는데도 자주 사용됩니다.
대부분의 비정상적인 AP는 일반 AP에 비해 2~3배 정도의 resource를 더 많이 사용하죠.
위의 예처럼 대부분의 수치를 차지하는 이건희 회장 같은 AP가 있다면, 이건 계발에 대한 검증이나 운영장비로의 적용 단계에 문제가 있다고 판단해야 합니다 !! 또 AWR을 보지 않아도 찾아내기 쉽죠.
(개인적으로 이건희 회장을 좋아하진 않지만, 뭐 다른 의도가 있는건 아닙니다. 얼마전 뉴스에서 제일 돈이 많다고해서 부럽기도 하고.. 뭐)
서론이 너무 길었지만,
특정 시간대에 문제의 소지가 있는 AP가 있는지에 대한 판단은 그 표본 범위가 작을 수록 더 쉬워지기 마련입니다.
즉 강호동이 10000명 중 하나일 경우와 10명 중 하나일 경우 전체 평균에 기여하는 부분이 달라지죠.
oracle 10g 부터 AWR이 default로 수행됩니다. 1주일의 보관 주기와 1시간의 수집 주기를 가지고 있죠.
하지만 대부분의 제가 본 AWR 주기는 (얼마 보진 않았지만.. --;) default로 설정되어 사용하고 있습니다.
만약 특정 시간동안 hang이 발생했거나 resource contention이 발생했다면 이에 대한 증거들인 database statistic 값들이 1시간이란 시간 안에서 "나 여기 있어요" 하고 말할 수 있을까요?
AWR의 데이터를 한달에 한번씩 수집해서 "이번달엔 이 정도 일했습니다."라고 말할 용도가 아니라면
적어도 15분 ~ 30분 정도의 수집 주기와 AP 수행 주기의 2배 만큼은 보관해야 합니다.
또 한가지 AWR에는 base-line을 기록할 수 있습니다.
base-line은 말 그대로 기준선이 됩니다. 이 base-line을 사용하면 "막연히 이번달엔 지난달 보다 좀 느려진것 같아요" 대신 "이번달은 지난달 보다 load가 1.5배 정도 더 발생해 전체적인 속도가 늦어졌어요" 라고 말할 수 있겠죠...
다 쓰고 보니 별 내용이 없군요..
하지만 위의 예제는 꼭 써먹구 싶었습니다... ㅋㅋㅋ
* 현재 AWR 설정 값 확인
select snap_interval, retention from dba_hist_wr_control;
병렬 실행의 동적 로드 밸런싱 기능의 이점을 활용
내부에서 모두 자동으로 처리
많은 데이타 스큐가 있을 경우 그 진가가 최대한 발휘
Oracle8i의 경우 하나의 테이블 파티션은 병렬 삽입(PDML) 중 오직 하나의 슬래이브 만을 따른다.
이에 따라 파티 션 간의 데이타 스큐가 발생할 경우, 로드 밸런싱은 불가능하게 된다.
예를 들어 여러 애플리케이션에서 테이블은 날 짜 열 범위에 의해 파티셔닝되며 행은 주로 마지막 파티션에 삽입되다고 할 경우를 마지막 파티션에서 운영되는 슬래 이브는 다른 슬래이브에 비해 훨씬 많은 작업을 해야 한다.
Oracle9i는 인트라 파티션 병렬 기능(intra-partition parallelism)을 채용했다.
여러 슬래이브가 하나의 파티션에서 작업하도록 지원할 경우 성능 병목을 완화하는데 도움이 된다.
이는 병렬 실행의 동적 로드 밸런싱 기능의 이점을 활용하는 것이다.
이는 PDML이 지원될 경우 내부에 서 모두 자동으로 처리된다.
인서트 인트라 파티션 병렬 기능(intra-partition parallelism of Inserts)의 이점은 많은 데이타 스큐가 있을 경우 그 진가가 최대한 발휘된다.
삽입된 데이타가 균일할 경우 일반 병렬 삽입에 비해 이점이 거의 없거나 전혀 없다.
샘플 문제:
Operational Data Store 테이블 SALES_ODS의 행이 월별로 분할되는 fact 테이블 SALES_1999 에 삽입되어야 한다. 데이타 배포는 스큐가 되며 아래와 같은 그래프로 나타난다.
구현:
Oracle8i와 Oracle9i, 모두에서 이는 다음과 같이 구현된다.
ALTER SESSION ENABLE PARALLEL DML;
INSERT INTO SALES_1999 /*+ parallel(SALES_1999, 10) */ SELECT * from SALES_ODS;
다음 표는 증가하는 데이타 양에 따른 병렬 삽입의 경과 시간을 비교한 것이다.
데이타 스큐를 증가시켜 1999년 11월에 거의 스파이크에 도달하게 된다면 이보다 더 큰 성능 이점을 기대할 수 있다. Oracle8i의 병렬 삽입의 성능은 스큐의 증가에 따라 저하되지만 Oracle9i를 이용할 경우 그 성능은 일정하 다. 다음 결과는 보다 많은 스큐가 적용된 데이타 분배에 대한 것이다(아래의 배포 그래프 참조).
Oracle9i에서 인서트 인트라 파티션 병렬 기능(intra-partition parallelism of Inserts)은 다음과 같은 이점을 통해 Oracle8i Parallel Insert(PDML) 보다 나은 성능을 발휘할 수 있다.
1. CPU 활용도 확대 - Oracle9i의 경우 이 작업은 Oracle8i와는 달리 거의 전적으로 CPU에 의존한다
2. 보다 효과적인 IO 대역폭 활용 - Oracle9i의 경우 Oracle8i 보다 높은 tps(transaction per second)를 제공한다
3. 다 높은 IO 처리 성능 - Oracle9i의 경우 Oracle8i보다 더 높은 IO 전송 처리 성능을 제공한다
이 POST는 "오라클 기술백서 : Oracle 9i와 DSS 환경에서의 성능과 확장성"의 일부 내용을 발췌한 것입니다.
Parallel DML은 대량의 table/index에 대해 "speed up"과 "scale up"을 위해 insert,
update, delete ,merge operation에 parallel execution mechanism을 적용시킨
operation을 말합니다. 즉 일반적으로 말하는 parallel query나 parallel direct-path read 등은 포함되지 않는 개념입니다.
Parallel DML은 기본적으로 session에 "enable" 되어 있지 않습니다. PDML과 serial DML의 locking, transaction, disk space requirement 등의 차이에 의해 PDML mode의 "enable"이 요구됩니다.
ALTER SESSION ENABLE PARALLEL DML;
따라서 Parallel DML이 "disable"되있을 경우 parallel hint나 table/index에 degree가 설정되어 있어도 이는 무시되게 됩니다.
물론 PDML mode가 "enable"되어 있어도 parallel hint나 table/index에 대한 degree가 설정되어 있어야 PDML로 수행 가능하다.
한 Transaction은 서로 다른 table에 대해 여러 PDML이 수행될 수 있습니다.
그러나 PDML로 변경된 table에 대해 해당 transaction 내에서 serial/parallel 명령(DML or Query)으로 access를 할 수 없습니다.
즉 commit/rollback 등으로 transaction을 완료 후에야 동일 table에 대한 operation이 가능합니다.
(참조 Note 201978.1 PDML Restrictions on Parallel DML)
Oracle 9i 이후에 intra-partition parallelism 개념이 소개되었습니다.
이 개념은 partition당 한개씩만 수행되는 parallel execution server의 제한을 완화(?) 시키는 개념입니다.
(참고 Note 241376.1 What is Intra-partition parallelism
)
특정 세션의 PDML의 "enable" 여부는 v$session의 PDML_STATUS, PDDL_STATUS, PQ_STATUS column으로 확인 할 수 있습니다.
SQL> SELECT SID,PDML_STATUS, PDDL_STATUS, PQ_STATUS FROM V$SESSION;
SID
PDML_STATUS
PDDL_STATUS
PQ_STATUS
----------
------------
------------
------------
141
DISABLED
DISABLED
DISABLED
143
DISABLED
ENABLED
ENABLED
145
DISABLED
ENABLED
ENABLED
148
DISABLED
ENABLED
ENABLED
150
DISABLED
DISABLED
DISABLED
이 POST는 metalink note를 참고하여 작성되었습니다. Note 201457.1- Introduction to PDML
oracle sequence는 순차적으로 특정 interval로 번호를 부여해 주는 object 입니다.
sequence 생성시 cache option을 줄수 있는데, 이는 지정한 갯수의 sequence를 미리
일정 부분 생성해 library cache영역에 저장해 두는 기능을 합니다.
cache option을 사용하지 않을 경우 매번 next value를 참조할 때 disk I/O를 발생하게
됩니다. 또 sequence관련 row cache lock이 발생하는 경우도 있죠.
이러한 현상을 완화 시키기 위해 자주 사용되는 sequence에 대해서는 cache 기능을 부여하게됩니다. alter sequence seq cache 100;
그러나 sequence 생성시 cache 기능을 사용할 경우 "cache aging out"과 "db re-start"
event로 인해 sequence number가 skip 될 수 있습니다. 더구나 sequence cache는 oracle 7.2 이전엔
row cache에 저장되었으나 7.3 이후 library cache에 저장되어 더 자주 aging-out 현상이 발생하게
됩니다.
이러한 현상을 최소화하기 위해 sequence를 pining 할 수 있습니다.
sequence pin은 (물론 다른 object도 마찬가지지만) dbms_shared_pool package의 keep procedure를
이용할 수 있습니다. 이 package는 default로 설치되지 않으며, dbmspool.sql 수행으로 설치 할 수 있습니다. dbms_shared_pool.keep('seq', 'Q'). 참조 : oracle metalink Note 62002.1 Caching Oracle Sequences
가끔 RAC나 CRS 관련해 문서를 보다보면 I/O fencing이라는 말이 나오는데, 이게 무슨 말일까요?
다음은 Cluster I/O fencing에 대한 설명입니다.
There will be some situations where the leftover write operations from failed database instances reach the storage system after the recovery process starts, such as when the cluster function failed on the nodes, but the nodes are still running at OS level.
Since these write operations are no longer in the proper serial order, they can damage the consistency of the stored data. Therefore, when a cluster node fails, the failed node needs to be fenced off from all the shared disk devices or disk groups.
This methodology is called I/O Fencing, sometimes called Disk Fencing or failure fencing.
가끔 오라클이 대량 작업 중에 죽거나 abort로 내렸을 경우
alert log에 보면 다음과 같이 SMON이 transaction rollback을 하는 메세지를 볼 수 있다.
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as available
:
"음 .. 뭔가 큰 transaction이 있었군.."
"어? 왜 아직도 안끝나지? 언제 끝나는 거야? "
다음의 SQL은 rollback 대상 extent size를 확인할 수 있는 sql 문입니다.
이외에 undo segment header dump로 확인하는 방법도 있긴하지만,
요게 시간 산정하기는 더 편하겠지요..
select KTUXEUSN,KTUXESLT,KTUXESQN,KTUXERDBF,KTUXERDBB,KTUXESTA,KTUXESIZ
from x$ktuxe
where KTUXEUSN in (select segment_id from dba_rollback_segs where segment_name = '_SYSSMU156$')
and KTUXESTA = 'ACTIVE';
기본적으로 제공되던 script 들은 oracle version에 따라 utlbstat/utlestat, statspack, awr report 등의 형태로 제공되고 있습니다.
utlbstat/utlestat은 시작시점과 끝시점에 수행하여 구간의 데이터를 OS상의 text file로 출력하고,
oracle 8i 부터 제공되었던 statspack은 job이나 cron에 등록하여 구간별 데이터를 얻을 수 있게 되었습니다. awr report는 oracle 10g부터 제공되었으며, sql 수행정보등 level에 따라 좀더 다양한 성능관련 정보들을 보여 줍니다.
awr report는 기본적으로 60분 간격으로 7일간의 데이터를 수집, 보관합니다. 이 데이터는 SYSAUX tablespace에 보관되며, 다음의 performance view에 데이터를 저장하게 됩니다.
V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second. V$METRIC - Displays metric information. V$METRICNAME - Displays the metrics associated with each metric group. V$METRIC_HISTORY - Displays historical metrics. V$METRICGROUP - Displays all metrics groups.
DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history. DBA_HIST_BASELINE - Displays baseline information. DBA_HIST_DATABASE_INSTANCE - Displays database environment information. DBA_HIST_SNAPSHOT - Displays snapshot information. DBA_HIST_SQL_PLAN - Displays SQL execution plans. DBA_HIST_WR_CONTROL - Displays AWR settings.
AWR report를 생성하기 위해서는 $ORACLE_HOME/rdbms/admin 밑에 있는 awr로 시작하는 몇몇 script를 수행하여 성능 보고서를 얻게 됩니다. 주로 awrrpt.sql이나 awrsqrpt.sql로 특정 구간의 성능데이터나 특정 구간에서의 SQL 수행정보를 얻곤 하죠.
다음은 awr관련 script 들입니다.
1)The awrrpt.sql SQL script generates an HTML or text report that displays
statistics for a range of snapshot Ids.
2)The awrrpti.sql SQL script generates an HTML or text report that displays
statistics for a range of snapshot Ids on a specified database and instance.
3) The awrsqrpt.sql SQL script generates an HTML or text report that displays
statistics of a particular SQL statement for a range of snapshot Ids. Run this report
to inspect or debug the performance of a SQL statement.
4) The awrsqrpi.sql SQL script generates an HTML or text report that displays
statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL.
5) The awrddrpt.sql SQL script generates an HTML or text report that compares
detailed performance attributes and configuration settings between two selected
time periods.
6) The awrddrpi.sql SQL script generates an HTML or text report that compares
detailed performance attributes and configuration settings between two selected
time periods on a specific database and instance.
awr을 control 하기 위한 몇몇 procedure가 제공되는데,
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_setting 은 interval이나 보관기간에 대한 설정을 위해 사용되며, DBMS_WORKLOAD_REPOSITORY.create_baseline는 성능 판단의 기준이 되는 baseline을 만들때 사용됩니다.
자세한 procedure 사용법은 다음과 같습니다.
How to Modify the AWR SNAP SHOT SETTINGS:
=====================================
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL.
interval => 30); -- Minutes. Current value retained if NULL.
END;
/
Creating the Baseline:
======================
BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline (
start_snap_id => 10,
end_snap_id => 100,
baseline_name => 'AWR First baseline');
END;
/
Dropping the AWR baseline:
==========================
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range(
(row_snap_id=>40,
High_snap_id=>80);
END;
/
DB 관리하면서 가장 긴장되는 순간이 ..
당연히 recovery를 수행해야 하는 순간이죠...
잘못하면 데이터 다 날라가거나,
아니면 restore하는데, 10여시간씩 걸리는데,
한번 잘못 명령어 날리면 ..
상상만 해도 끔찍하죠..ㄷㄷ
다음의 dictionary view는 recovery시 꼭 챙겨봐야 할 dictionary view 입니다.
돌다리도 두들겨 보고 건넙니다.. ^^
DBA_DATA_FILES, V$DATAFILE
: 해당 datafile의 현황 및 현재 상태를 check할 수 있다.
V$DATAFILE_HEADER
: 해당 datafile의 fuzzy 상태를 확인할 수 있다. 만약 fuzzy 상태라고 하면 v$backup에서 정상적으로 end backup이 수행되었는지,
추가로 archive log를 적용해야 하는지의 판단이 필요하다.
V$BACKUP
: 최종에 Online Backup받은 file들에 대한 정보를 가지고 있다.
즉, Hot Backup이 수행되고 있는 Tablepsace가 아직 Backup Mode로 있는지 아니면 Backup이 완료 된 상태인지 확인 할 수 있다.
만약 Online Backup을 수행 하면서 ALTER TABLESPACE ~ END BACKUP; 을 수행하지 않았다면 STATUS가 ACTIVE로 남아있게 되며,
해당 file을 Backup받은 후에 ALTER TABLESPACE ~ END BACKUP command를 실행하지 않은 것이므로 즉시 ALTER TABLESPACE ~ END BACKUP command 를 실행해야 한다.
오라클 모니터링 할 때 모니터링 툴을 많이 사용하시죠.
oracle의 Sql Developer, 웨어밸리의 Orange, 퀘스트의 TOAD, 엑셈의 MaxGuage, 데이타헤븐의 Intuvision,..
GUI tool을 사용하면 한눈에 시스템 전체를 파악하기 용이하고, 마우스 클릭 몇번으로 금방 정보를 확인할 수 있습니다. 그러나 oracle의 data dictionary view나 dynamic performance view의 모든 내용을 포함하기는 어렵고, 또 다 포함하고 있다고 해도 그에 대한 사용 방법에 대해 사용자가 인지하고 있긴 어렵죠.
일반적으로 DBA 들이 주로 사용하는 dictionary view는 열손가락 꼽을 정도 밖에 안되지 않을까 합니다. dba_users, dba_tablespaces, v$tablespace, dba_data_files, v$filestat, v$sysstat, v$session_wait ... (음, 10개는 넘겠군요.. )
그런데 dictionary view를 보면 dba_로 시작하는 view와 v$로 시작하는 view 들이 있습니다.
(물론, all_ 이나 user_, gv$로 시작하는 view 들도 있긴 하죠)
dba_ view와 v$ view의 차이점을 무얼까요?
이 차이에 대해 평소에 별로 생각해 본적이 없어 google에서 찾아 봤더니 oracle faq's blog에 잘 설명 되어 있네요.
V$% views return data from memory structures. Data is lost when the instance is restarted.
DBA_% tables return data from the database's data dictionary (SYSTEM tablespace). Data persists across instance restarts.
v$ view는 memory structure data를 보여주며, dba_ table(?)은 system tablespace 상의 data dictoinary data를 보여줍니다. 10g에서 awr 관련 view들이 만들어져서 dba_hist view 등에서 memory structure statistic data를 system tablespace에 저장해서 보여주기도 하죠.
Static Data Dictionary Views and Dynamic Performance Views are the core of database administration. They are of crucial importance. Whoever wants to learn Oracle database administration has to get familiar with these views.
The Data Dictionary tables contain meta data on objects, privileges, roles, users etc.
Whenever you issue DML statements Oracle consults its data dictionary to make sure a table is there, it has the right columns and you have privileges on it. And whenever you issue DDL statements Oracle updates its data dictionary.
All information needed by an instance on its database is kept in the data dictionary. Except some storage related information which is in the datafile headers and some information in controlfile and spfile which is needed at instance startup.
At runtime the oracle instance stores information about itself, about its CPU, memory or I/O performance, wait statistics and everything else which is going on at the instance in dynamic performance tables. Dynamic performance tables are non persistent. You can’t see past instance startup. After shutdown or instance crash all information contained in dynamic performance tables is lost. *1)
Through static dictionary views users can get at information kept in data dictionary tables.
And through dynamic performance views users can look at non persistent runtime information contained in dynamic performance tables.
But why are those views that important, you might ask? All information necessary for database administration can be found in those static dictionary views. And basically all information necessary for instance diagnostic and tuning can be obtained through those dynamic performance views at runtime.
Of course, GUI tools like Enterprise Manager or SQL Developer offer the same information
in a more user friendly manner. But all those GUI tools rely on static dictionary views and dynamic performance views.
As a DBA you will likely get into situations where there is no GUI tools available. At times you will be alone with your SQL*Plus skills. Your mastery of data dictionary and performance views will make all the difference!
동감입니다 !
tool을 사용하는 것이 더 효율적이라 생각하실지 모르지만
마지막엔 DBA가 직접 sqlplus에서 dictionary view들을 query해야 하는 경우가 허다하죠..
현재 수행한 SQL의 plan을 확읺는 방법 2가지입니다..
select * from table(dbms_xplan.display);
SQL> desc dbms_xplan
FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN DEFAULT
STATEMENT_ID VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
explain plan
for
sql ..
utlxpls.sql 수행
select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
SQL> desc dbms_xplan
FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN DEFAULT
STATEMENT_ID VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER(38) IN DEFAULT
DB_ID NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN DEFAULT
CURSOR_CHILD_NO NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FUNCTION PREPARE_RECORDS RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PAR1 VARCHAR2 IN
PAR2 VARCHAR2 IN
active session들의 현재 wait event와 sql 정보를 buffer get 기준으로 보여주는 sql script 입니다.
세션 모니터링의 기본이죠..
<내용>
set pagesize 250
col sid for 9999
col waiting_on for a90
col serial# for 999999
col prg for a19
col OraPid for 9999
col command for a9
col aa Heading "DB-User" for a8
col bb Heading "OS-Pid" for a7
set pau off
select to_char(sysdate,'MM/DD HH24:MI:SS') dat,s.sid SID,s.serial#,sql_hash_value,
decode(s.command,
'0','NO',
'1','Cr Tab',
'2','Insert',
'3','Select',
'6','Update',
'7','Delete',
'9','Create Idx',
'10','Drop Idx',
'15','Alter Tbl',
'24','Create Proc',
'32','Create Link',
'33','Drop Link',
'36','Create RBS',
'37','Alter RBS',
'38','Drop RBS',
'40','Alter TBS',
'41','Drop TBS',
'42','Alter Sess',
'45','Rollback',
'47','PL/SQL Exe',
'62','Anal Table',
'63','Anal Index',
'85','Truncate') Command,
substr(s.machine,1,8)||'['|| substr(s.program, 1, 9)||']' prg,
round(q.buffer_gets/q.executions,2) getperexec,
substr(
rtrim(w.event) || ': ' ||
rtrim(p1text,' ') || ' ' || to_char(p1) || ',' ||
rtrim(p2text,' ') || ' ' || to_char(p2) || ',' ||
rtrim(p3text,' ') || ' ' || to_char(p3),1,75) ||',waiting:'||wait_time as waiting_on
from v$session s, v$session_wait w,v$sqlarea q
where w.wait_time = 0
and w.sid = s.sid
and s.sql_hash_value = q.hash_value
and event not like '%pmon timer%'
and event not like '%smon timer%'
and event not like '%rdbms ipc message%'
and event not like '%SQL*Net message%'
and event not like '%lock manager wait for%'
and event not like '%slave wait%'
and event not like '%io done%'
and event not like '%pipe get%'
and event not like '%wakeup time manager%'
and event not like '%queue messages%'
order by getperexec
/
tablespace를 만들떄 local managed tablespace를 많이 사용하시죠?
예전의 dictionary-managed 방식에 비해 LMT는 많은 이점이 있죠..
뭐 이점이 있다기 보다는 dictionary-managed 방식의 단점이라고 보는게 더 타당할 지 모르겠네요..^^;
LMT의 extent 관리 방식에는 uniform size와 system managed 방식이 있습니다.
간단히 말하자면, uniform size는 extent size를 정해서 해당 tablespace에 생성되는 segment 들은 동일한 extent size로 설정되게 되고, system managed 방식은 extent size에 대한 설정을 oracle에서 관리해 주는 거죠..
system managed 방식을 쓰면 extent size에 대해 크게 신경을 안써도 되니 좋긴 한데, 오라클에서 어떻게 extent를 관리할까요?
심심해서 함 해봤는데, 엄청난 매카니즘이 숨겨 있더군요.. ㅋ
처음에 64k로 extent가 생성됩니다. 다음 extent도 64K가 생성되죠.. 뭐 이렇게 16개가 만들어지고,
다음은 1024K로 extent가 생성된후... 64개까지 만들어 지고...
..
이런 식으로 extent가 할당되면 1024K가 생성될 때 즈음이면
1024K 이하의 extent는 영원히 짜투리로 남을 수도 있겠죠?
