태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

'2010/10'에 해당되는 글 8건

  1. 2010.10.25 조인 순서 제어하기 (3)
  2. 2010.10.21 비주얼 SQL 튜닝? (6)
  3. 2010.10.20 엑셈의 10월 오라클 퀴즈에 도전하세요! (2)
  4. 2010.10.18 FGA(Fined Grained Auditing) 활용 예제 (1)
  5. 2010.10.12 RAW 값 변환하기
  6. 2010.10.11 Batching NLJ에 대한 오라클 매뉴얼의 설명
  7. 2010.10.06 MOATS - Mother of All Tuning Scripts?
  8. 2010.10.06 PLAN_HASH_VALUE

조인 순서 제어하기

오라클/SQL 튜닝 2010.10.25 14:57
오라클이 제공하는 힌트가 너무 강력해서 가끔 기본적인 것을 지나칠 때가 있습니다. 가끔은 힌트가 없다면 어떻게 실행 계획을 제어할 수 있을까를 고민해보면 재미있는 발견을 할 때가 있습니다.

예를 들어 아래와 같은 5개의 테이블이 있습니다.

create table t1(c1, c2)
as 
select
	 level, level
from dual
connect by level <= 5000;

create table t2(c1, c2)
as 
select
	 level, level
from dual
connect by level <= 4000;

create table t3(c1, c2)
as 
select
	 level, level
from dual
connect by level <= 3000;

create table t4(c1, c2)
as 
select
	 level, level
from dual
connect by level <= 2000;

create table t5(c1, c2)
as 
select
	 level, level
from dual
connect by level <= 1000;

create index t1_n1 on t1(c1);
create index t2_n1 on t2(c1);
create index t3_n1 on t3(c1);
create index t4_n1 on t4(c1);
create index t5_n1 on t5(c1);

exec dbms_stats.gather_table_stats(user, 't1');
exec dbms_stats.gather_table_stats(user, 't2');
exec dbms_stats.gather_table_stats(user, 't3');
exec dbms_stats.gather_table_stats(user, 't4');
exec dbms_stats.gather_table_stats(user, 't5');
테이블 T1, T2, T3, T4, T4를 조인하는 쿼리에서 조인 순서는 어떻게 될까요? 테이블의 데이터 분포를 보면 T1(c1 between 1 and 10 조건 때문에)이 드라이빙 테이블이 되고 그 다음은 T5(1000건), T4(2000건), T3(3000건), T2(4000건)이 됨을 알 수 있습니다.
explain plan for
select 
	*
from
	t1, t2, t3, t5, t4
where
	t1.c1 = t2.c1
	and t1.c1 = t3.c1
	and t1.c1 = t4.c1
	and t1.c1 = t5.c1
	and t1.c2 between 1 and 10
;

--------------------------------------
| Id  | Operation             | Name |
--------------------------------------
|   0 | SELECT STATEMENT      |      |
|*  1 |  HASH JOIN            |      |
|*  2 |   HASH JOIN           |      |
|*  3 |    HASH JOIN          |      |
|*  4 |     HASH JOIN         |      |
|*  5 |      TABLE ACCESS FULL| T1   |
|   6 |      TABLE ACCESS FULL| T5   |
|   7 |     TABLE ACCESS FULL | T4   |
|   8 |    TABLE ACCESS FULL  | T3   |
|   9 |   TABLE ACCESS FULL   | T2   |
--------------------------------------
만일 조인 순서를 T1 -> T2 -> T3 -> T4 -> T5로 만들고 싶다면? 아래와 같이 ORDERED 힌트를 사용하면 되겠죠? 또는 LEADING(t1 t2 t3 t4 t5) 힌트를 사용해도 됩니다.
explain plan for
select /*+ ordered */
	*
from
	t1, t2, t3, t5, t4
where
	t1.c1 = t2.c1
	and t1.c1 = t3.c1
	and t1.c1 = t4.c1
	and t1.c1 = t5.c1
	and t1.c2 between 1 and 10
;

--------------------------------------
| Id  | Operation             | Name |
--------------------------------------
|   0 | SELECT STATEMENT      |      |
|*  1 |  HASH JOIN            |      |
|*  2 |   HASH JOIN           |      |
|*  3 |    HASH JOIN          |      |
|*  4 |     HASH JOIN         |      |
|*  5 |      TABLE ACCESS FULL| T1   |
|   6 |      TABLE ACCESS FULL| T2   |
|   7 |     TABLE ACCESS FULL | T3   |
|   8 |    TABLE ACCESS FULL  | T4   |
|   9 |   TABLE ACCESS FULL   | T5   |
--------------------------------------
만일 힌트를 사용하지 않고(그리고 RBO가 아닌 CBO라고 하면), 조인 순서가 항상 위와 같이 나오게 하고 싶다면 어떻게 해야될까요? 힌트에 익숙해지다보면 이런 간단한 질문에 대한 답이 선뜻 나오지 않는 경우가 많습니다.

가장 전형적인 방법은 아래와 같이 t1.c1 = t3.c1 조인 조건을 t1.c1 + 0*t2.c1 = t3.c1 같은 형태로 사용하는 것입니다. t3.c1에서 조인이 이루어지려면 t2.c1 값을 알아야 하므로 반드시 T2 -> T3 순서로 조인이 됩니다. 비슷한 원리로 조건절을 만들어가면 됩니다.

explain plan for
select 
	*
from
	t1, t2, t3, t4, t5
where
	t1.c1 = t2.c1
	and t1.c1 + 0*t2.c1 = t3.c1  -- t2.c1 값을 알아야 t3.c1 조인 가능
	and t1.c1 + 0*t3.c1= t4.c1  -- t3.c1 값을 알아야 t4.c1 조인 가능
	and t1.c1 + 0*t4.c1 = t5.c1  -- t4.c1 값을 알아야 t5.c1 조인 가능
	and t1.c2 between 1 and 10
;

--------------------------------------
| Id  | Operation             | Name |
--------------------------------------
|   0 | SELECT STATEMENT      |      |
|*  1 |  HASH JOIN            |      |
|*  2 |   HASH JOIN           |      |
|*  3 |    HASH JOIN          |      |
|*  4 |     HASH JOIN         |      |
|*  5 |      TABLE ACCESS FULL| T1   |
|   6 |      TABLE ACCESS FULL| T2   |
|   7 |     TABLE ACCESS FULL | T3   |
|   8 |    TABLE ACCESS FULL  | T4   |
|   9 |   TABLE ACCESS FULL   | T5   |
--------------------------------------
위의 방법을 응용하면 조인 순서를 자유롭게 제어할 수 있습니다. 예를 들어 조인 순서를 T1 -> T2 -> T4 -> T3 -> T5(T4가 T3보다 먼저 조인되게)로 만들려면 어떻게 해야할까요?
explain plan for
select 
	*
from
	t1, t2, t3, t4, t5
where
	t1.c1 = t2.c1
	{ 여기에 어떤 조건이 들어가야 아래의 실행 계획이 나올까요? }
	and t1.c2 between 1 and 10
;

--------------------------------------
| Id  | Operation             | Name |
--------------------------------------
|   0 | SELECT STATEMENT      |      |
|*  1 |  HASH JOIN            |      |
|*  2 |   HASH JOIN           |      |
|*  3 |    HASH JOIN          |      |
|*  4 |     HASH JOIN         |      |
|*  5 |      TABLE ACCESS FULL| T1   |
|   6 |      TABLE ACCESS FULL| T2   |
|   7 |     TABLE ACCESS FULL | T4   |
|   8 |    TABLE ACCESS FULL  | T3   |
|   9 |   TABLE ACCESS FULL   | T5   |
--------------------------------------
정답은 이미 공개된 것이나 마찬가지이기 때문에 여기에 적지는 않겠습니다. 혹시 잠깐 시간내서 도전해보고 싶은 분이 있으면 댓글로 남기셔도 좋겠습니다.

그래도 역시 힌트가 편하긴 편합니다!

저작자 표시
신고

'오라클 > SQL 튜닝' 카테고리의 다른 글

조인 순서에 대한 간단한 테스트  (3) 2010.11.25
조인 순서 제어하기 2  (2) 2010.11.04
조인 순서 제어하기  (3) 2010.10.25
비주얼 SQL 튜닝?  (6) 2010.10.21
Batching NLJ에 대한 오라클 매뉴얼의 설명  (0) 2010.10.11
Trackback 0 : Comments 3
  1. salvation 2010.10.27 00:48 신고 Modify/Delete Reply

    재미있는 글 잘 보았습니다. ^^
    앞으로 올라올 새로운 포스팅을 기대하며..

  2. 드림걸 2010.11.02 11:05 신고 Modify/Delete Reply

    항상 힌트로 제어할려고난 했는데 재밌는 글 읽고갑니다^^

    • 욱짜 2010.11.02 11:09 신고 Modify/Delete

      힌트로는 제어하기 힘든 경우에 위의 방법을 쓸 수 있습니다. 조만간 간단한 사례를 포스팅하겠습니다.

Write a comment


비주얼 SQL 튜닝?

오라클/SQL 튜닝 2010.10.21 13:49
복잡한 SQL 튜닝을 어떻게 하면 손쉽게, 정확하게 자동화할 것인가는 이 업계의 오랜 숙원이자 도전과제입니다. 오라클이 제공하는 자동 SQL 튜닝(Automatic SQL Tuning) 기능조차도 외면받는 걸 보면 이것이 얼마나 어려운 일인지 알 수 있습니다.

Dan Tow의 저서 [SQL Tuning]을 보면 SQL 문장을 비주얼한 다이어그램으로 표현해서 최적의 실행 계획을 찾는 방법을 기술하고 있습니다. 대단히 매력적인 방법이고, 실제로 쿼리 튜닝을 할 때는 우리 머리 속에서는 이런 일이 발생합니다. 이런 개념을 GUI로 구현한 툴이 있어서 소개합니다.

DB Optimizer라는 툴인데요, 국내에 정식 공급되는지는 모르겠습니다. 하여간 이 툴이 제공하는 VST(Visual SQL Tuning) 기능을 보면 SQL 다이어그램 기반의 튜닝 기법을 구현하기 위해 대단한 노력을 기울이고 있음을 알 수 있습니다.

간단한 예를 들어, 아래와 같은 쿼리가 있다고 가정하면

select distinct * from foo.a, foo.c, foo.d, foo.g
WHERE a.planted_date = to_date('02/10/2008','dd/mm/yyyy')
AND a.pears = 'D'
AND a.green_beans = '1'
AND a.planted_date = c.planted_date
AND a.pears = c.pears
AND a.zuchinis = c.zuchinis
AND a.brocoli = c.brocoli
AND a.planted_date = d.planted_date
AND a.pears = d.pears
AND a.harvest_size = d.harvest_size
AND c.oranges = d.oranges
AND c.apples = d.apples
AND (d.lemons = 0 OR d.lemons IS NULL)
AND a.planted_date = g.planted_date
AND a.pears = g.pears
AND a.harvest_size = g.harvest_size
AND c.oranges = g.oranges
AND c.apples = g.apples
AND (g.lemons = 0 OR g.lemons IS NULL)
and a.zuchinis='0236'
ORDER BY a.zuchinis, a.brocoli;
위의 쿼리를 다이어그램으로 표현하면 아래 그림과 같습니다.

전체 로우수가 1,777,110건이고 필터율이 0.674%인 테이블 A를 선행으로 테이블 C를 조인하면 44,106건이 됩니다. A --> C 의 순서로 조인하는 것이 유리할 것이라는 것을 직관적으로 알 수 있습니다(정확한 해석법은 매뉴얼 참조). 이런 방식으로 조인을 이어나가면 아래와 같은 순서가 됩니다.

지나치게 간단한 예이지만, 대단히 흥미롭습니다. 이 정도까지 구현했다는 것이 대단합니다.

DB Optimizer의 SQL Tuning 모듈은 VST 외에도 많은 기능들이 있는데 전체 그림은 아래와 같습니다.

관심있으신 분들은 다운로드받아서 사용해보셔도 좋겠습니다. Dan Tow의 저서 [SQL Tuning]도 기회가 되면 읽어보시구요.

저작자 표시
신고

'오라클 > SQL 튜닝' 카테고리의 다른 글

조인 순서 제어하기 2  (2) 2010.11.04
조인 순서 제어하기  (3) 2010.10.25
비주얼 SQL 튜닝?  (6) 2010.10.21
Batching NLJ에 대한 오라클 매뉴얼의 설명  (0) 2010.10.11
PLAN_HASH_VALUE  (0) 2010.10.06
Trackbacks 2 : Comments 6
  1. proud0 2010.10.22 10:40 신고 Modify/Delete Reply

    제가 알기론 공식 공급되고 있습니다.

    http://www.devgear.co.kr/products/db-optimizer-xe/

    • 욱짜 2010.10.22 19:30 신고 Modify/Delete

      좋은 정보 감사합니다. 최신 버전이 반영되어 있지 않을거로 보니 아직 시장의 반응은 없는 듯 하네요.

  2. 2dong0 2010.10.24 02:02 신고 Modify/Delete Reply

    와 정말 멋진데요~^^ 도스에서 윈도우로 넘어가는 것처럼 혁명 같습니다.^^

    • 욱짜 2010.10.25 13:12 신고 Modify/Delete

      이런 방법론이 확실히 장점이 있는데, 현실적인 문제가 있는거 같습니다. 이 툴을 사용해서 SQL 튜닝을 하려면 SQL Diagram을 작성하고 해석하는 방법을 알아야 하는데 제법 학습이 필요하거든요. 투자가 필요한 셈인데, 그걸 유도하기가 쉽지는 않아 보입니다.

  3. extremedb 2010.10.26 15:22 신고 Modify/Delete Reply

    헐 ~ 이런 툴이 있었네요.
    제가 찾고 있던 종류입니다.
    감사합니다.

    • 욱짜 2010.10.26 15:56 신고 Modify/Delete

      누군가는 이 툴을 보고 눈이 반짝할 줄 알았습니다. ㅎㅎ

      혹시 실제로 사용하시게 되면 기술적인 평가를 해주셔도 좋겠습니다!

Write a comment


엑셈의 10월 오라클 퀴즈에 도전하세요!

오라클/기타 2010.10.20 09:45
지난 번 퀴즈의 실패(?)를 딛고 혼신의 힘을 기울여 만든 퀴즈입니다. 음... 근데 내고 보니 왠지 이번에도 참가자가 적을 것 같다는...

퀴즈 참가자 수는 문제 길이에 반비례하더군요. 문제는 짧고 재미있는 퀴즈를 만들기가 쉽지 않다는 것입니다.

그래도 많이들 도전하시고 상품권도 받아가시길 바랍니다!

저작자 표시
신고
Trackback 0 : Comments 2
  1. 라튜니 2010.10.27 17:32 신고 Modify/Delete Reply

    이번달 퀴즈에 문의사항이 있어 이렇게 질문 드립니다.

    01 SQL> select c1, count(*)
    02 2 from t1
    03 3 group by c1
    04 4 ;
    05
    06 C1 COUNT(*)
    07 ---------- ----------
    08 1 10000
    09
    10 SQL> select /*+ gather_plan_statistics
    11 2 index(t1) */
    12 3 *
    13 4 from
    14 5 t1
    15 6 where
    16 7 c1 = 1
    17 8 and rownum = 1
    18 9 ;
    19
    20 C1 C2
    21 ---------- ----------
    22 1 x ===> '이상함'
    23
    24 ----------------------------------------------------------------------------
    25 | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
    26 ----------------------------------------------------------------------------
    27 | 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.01 | 36 |
    28 |* 1 | COUNT STOPKEY | | 1 | 1 |00:00:00.01 | 36 |
    29 |* 2 | INDEX RANGE SCAN| T1_N1 | 1 | 1 |00:00:00.01 | 36 |
    30 ----------------------------------------------------------------------------
    31
    32 Predicate Information (identified by operation id):
    33 ---------------------------------------------------
    34
    35 1 - filter(ROWNUM=1)
    36 2 - access("C1"=1)

    문제 내용을 보면 update 이후 select 한 값의

    c2값이 x임에도 불구하고

    정답의 update문은 c2를 'xxxxxxxxxx' 로 업데이트 했는데요.

    그럼 중간에 select한 내용과 일치하지 않는거 같아서 질문드립니다.

    수고하세요~

    • 욱짜 2010.10.27 15:29 신고 Modify/Delete

      그렇군요!

      정답을 선택할 때 Logical Reads가 늘어난다는 사실에만 부합하면 정답으로 인정해서 거기까지는 신경쓰지 못했습니다. 이 점은 양해부탁드리겠습니다.

      다음 번에는 좀 더 상세한 부분까지 신경쓰도록 하겠습니다.

Write a comment


FGA(Fined Grained Auditing) 활용 예제

오라클/관리 2010.10.18 16:08
며칠 전 지인으로부터 다급한(?) 문의가 왔습니다.
  • 특정 테이블들에 대해 SELECT, INSERT, UPDATE, DELETE 문장을 어떤 프로그램(가령 SQL*Plus? 토드? 웹?)이 언제 수행했는지 기록할 수 있는 방법이 있을까요?
위와 같은 요구 사항을 만족시키는 가장 좋은 방법은 오라클에 제공하는 감사 기능, 즉 AUDIT를 사용하는 것이겠지요? 그런데 프로그램 종류를 알아야 한다는 요구 사항에서 문제가 생겼습니다.

오라클이 제공하는 AUDIT 데이터에는 프로그램 정보는 제공되지 않습니다. 한가지 방법은 AUDIT가 제공하는 데이터와 AWR이 제공하는 ASH데 대한 히스토리 데이터인 DBA_HIST_ACTIVE_SESS_HISTORY 뷰를 이용해서 프로그램을 알아내는 것인데, 번거롭고 100% 확실한 정보를 제공할 수 없다는 점이 제약이 됩니다.

이런 경우에 이용할 수 있는 것이 FGA(Fined Grained Auditing)입니다. 오라클의 최신 버전에서는 모든 종류의 SQL 문장에 대해 FGA를 수행할 수 있습니다. FGA가 수행되면 그 결과가 SYS.FGA_LOG$ 테이블에 저장되고, DBA_FGA_AUDIT_TRAIL 뷰를 통해 볼 수 있습니다. 단, FGA를 이용한 Audit 데이터에도 프로그램 정보는 제공되지 않습니다. 대신 FGA가 제공하는 핸들러(Handler) 기능을 이용할 수 있습니다.

Audit 이벤트가 발생할 때 사용자가 직접 정의한 핸들러 프로시저를 호출할 수 있는 기능이 제공되는데, 흔히 DBA에게 Alert를 수행할 목적 등으로 활용하도록 설계된 것입니다. 이 기능을 이용해서 프로그램 정보를 포함한 2차 Auditing을 수행하도록 할 수 있습니다.

아래에 간단한 예제가 있습니다.

1. 테스트 버전은 11.2.0.1입니다.

SQL> -- version
SQL> select * from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
2. AUDIT 대상이 되는 사용자는 TPACK이고, 대상 테이블은 T1입니다.
SQL> -- audited user is tpack
SQL> conn tpack/tpack@ukja1120
Connected.

SQL> create table t1(c1 number);

Table created.

SQL> insert into t1 select level from dual connect by level <= 1000;

1000 rows created.
3. AUDIT를 수행할 유저는 UKJA이고, 2차 AUDIT 결과를 T_AUD_LOG 테이블에 저장하기로 합니다. 다음과 같이 핸들러 프로시저를 가지는 FGA 정책(Policy)를 만듭니다.
SQL> -- auditing user = ukja
SQL> conn ukja/ukja@ukja1120
Connected.

SQL> create table t_aud_log
  2  as
  3  select
  4  	sid,
  5  	username,
  6  	program,
  7  	machine,
  8  	rpad('x',30) as table_name,
  9  	rpad('x',10) as stmt_type,
 10  	sysdate as log_date
 11  from v$session
 12  where 1 = 0
 13  ;

Table created.

SQL> -- create "select" event handler
SQL> -- proc_audit_select, proc_audit_update, proc_audit_insert, proc_audit_delete 이렇게 만들어서 사용하시면 모든 종류의 접근을 로깅할 수 있습니다.
SQL> create or replace procedure proc_audit_select(
  2  	object_schema varchar2,
  3  	object_name varchar2,
  4  	policy_name varchar2)
  5  as
  6  	pragma autonomous_transaction;
  7  begin
  8  
  9  	insert into t_aud_log(
 10  		sid,
 11  		username,
 12  		program,
 13  		machine,
 14  		table_name,
 15  		stmt_type,
 16  		log_date)
 17  	select
 18  		sid,
 19  		username,
 20  		program,
 21  		machine,
 22  		object_name,
 23  		'SELECT',
 24  		sysdate
 25  	from
 26  		v$session s
 27  	where
 28  		s.sid = userenv('sid')
 29  	;
 30  
 31  	commit;
 32  
 33  end;
 34  /

Procedure created.

SQL> show errors
No errors.

SQL> -- add policy
SQL> begin
  2  	dbms_fga.add_policy(
  3  		object_schema => 'TPACK',
  4  		object_name => 'T1',
  5  		policy_name => 'T1_AUDIT_SELECT',
  6  		statement_types => 'SELECT',
  7  		handler_schema => 'UKJA',
  8  		handler_module => 'PROC_AUDIT_SELECT'
  9  	);
 10  end;
 11  /

PL/SQL procedure successfully completed.
위와 같이 수행하면 TPACK.T1 테이블에 대해 SELECT가 발생할 때마다 AUDIT가 이루어지고 핸들러 프로시저인 PROC_AUDIT_SELECT가 수행될 것입니다.

4. TPACK.T1 테이블에 대해 SELECT를 수행하고 성공적으로 AUDIT가 이루어졌는지 확인해봅니다.

SQL> -- now select table
SQL> conn tpack/tpack@ukja1120
Connected.

SQL> select count(*) from t1;

  COUNT(*)
----------
      1000

SQL> -- check wheter t_aud_log is being populated
SQL> conn ukja/ukja@ukja1120
Connected.

SQL> set serveroutput on
SQL> exec print_table('select * from t_aud_log');
SID                           : 139
USERNAME                      : TPACK
PROGRAM                       : sqlplus.exe    -- 이 정보때문에 이 고생을!!!
MACHINE                       : POWER_GROUP\UKJAX
TABLE_NAME                    : T1
STMT_TYPE                     : SELECT
LOG_DATE                      : 2010/10/18 15:41:49
-----------------

PL/SQL procedure successfully completed.

SQL> -- check dba_fga_audit_trail and fga_log$
SQL> conn sys/oracle@ukja1120 as sysdba
Connected.

SYS@ukja1120> set serveroutput on
SYS@ukja1120> exec ukja.print_table('select * from dba_fga_audit_trail');
SESSION_ID                    : 8860593
TIMESTAMP                     : 2010/10/18 15:41:49
DB_USER                       : TPACK
OS_USER                       : UKJAX\Administrator
USERHOST                      : POWER_GROUP\UKJAX
CLIENT_ID                     :
ECONTEXT_ID                   :
EXT_NAME                      : UKJAX\Administrator
OBJECT_SCHEMA                 : TPACK
OBJECT_NAME                   : T1
POLICY_NAME                   : T1_AUDIT_SELECT
SCN                           : 46448666
SQL_TEXT                      : select count(*) from t1
SQL_BIND                      :
COMMENT$TEXT                  :
STATEMENT_TYPE                : SELECT
EXTENDED_TIMESTAMP            : 18-OCT-10 03.41.49.968000 PM +09:00
PROXY_SESSIONID               :
GLOBAL_UID                    :
INSTANCE_NUMBER               : 0
OS_PROCESS                    : 3188:5036
TRANSACTIONID                 :
STATEMENTID                   : 8
ENTRYID                       : 1
OBJ_EDITION_NAME              :
DBID                          : 864753741
-----------------

PL/SQL procedure successfully completed.
물론 테스트가 끝나면 불필요한 데이터는 삭제!
SYS@ukja1120> delete from fga_log$;

1 row deleted.

SYS@ukja1120> -- drop policy
SYS@ukja1120> conn ukja/ukja@ukja1120

SQL> begin
  2  	dbms_fga.drop_policy(
  3  		object_schema => 'TPACK',
  4  		object_name => 'T1',
  5  		policy_name => 'T1_AUDIT_SELECT'
  6  	);
  7  end;
  8  /

PL/SQL procedure successfully completed.
조금 번거롭지만, 목적한 바를 성공적으로 달성할 수 있었습니다.

오라클이 제공하는 FGA 기능은 매우 유연하고 강력해서 AUDIT와 관련된 어지간한 요구 사항을 대부분 만족시킬 수 있습니다. 단, 오라클 데이터베이스 내에서 추가적인 작업이 발생하기 때문에 오버헤드가 발생할 우려가 있습니다. 이런 이유 때문에 써드 파티가 제공하는 AUDIT 툴을 구매하는 경우도 많긴 하지만요.

복잡한 AUDIT 요구 사항이 발생할 때 위의 방법을 활용해보시는 것도 좋은 경험이 되겠습니다.

저작자 표시
신고

'오라클 > 관리' 카테고리의 다른 글

FGA(Fined Grained Auditing) 활용 예제  (1) 2010.10.18
tags : ADUIT, DBMS_FGA, FGA
Trackback 0 : Comment 1
  1. 연두곰 2014.01.27 17:46 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment


RAW 값 변환하기

오라클/프로그래밍 2010.10.12 16:26
딕셔너리를 검색하다 보면 RAW 형태로 제공되는 값들이 간혹 있습니다. 가령 USER_TAB_COLS 뷰에서 컬럼의 최소값과 최대값을 조회해보면 다음과 같이 의미를 알 수 없는 RAW 값으로 보여집니다.
SQL> col column_name format a10
SQL> col data_type format a10
SQL> select
  2  	column_name,
  3  	data_type,
  4  	low_value,
  5  	high_value
  6  from
  7  	user_tab_cols
  8  where
  9  	table_name = 'T1'
 10  ;

COLUMN_NAM DATA_TYPE  LOW_VALUE            HIGH_VALUE
---------- ---------- -------------------- --------------------
C1         NUMBER     C102                 C302
C2         VARCHAR2   6D616E79             6F6E65
이 값들은 UTL_RAW 패키지를 사용하면 변환할 수 있습니다.
SQL> select utl_raw.cast_to_number('C102') from dual;

UTL_RAW.CAST_TO_NUMBER('C102')
------------------------------
                             1

SQL> select utl_raw.cast_to_varchar2('6D616E79') from dual;

UTL_RAW.CAST_TO_VARCHAR2('6D616E79')
--------------------------------------------------------------------------------
many
다음과 같이 사용할 수 있겠네요.
SQL> select
  2  	column_name,
  3  	data_type,
  4  	decode(data_type,
  5  		'NUMBER', utl_raw.cast_to_number(low_value)||'',
  6  		'VARCHAR2', utl_raw.cast_to_varchar2(low_value), low_value||'') as low_value,
  7  	decode(data_type,
  8  		'NUMBER', utl_raw.cast_to_number(high_value)||'',
  9  		'VARCHAR2', utl_raw.cast_to_varchar2(high_value), high_value||'') as high_value
 10  from
 11  	user_tab_cols
 12  where
 13  	table_name = 'T1'
 14  ;

COLUMN_NAM DATA_TYPE  LOW_VALUE            HIGH_VALUE
---------- ---------- -------------------- --------------------
C1         NUMBER     1                    10000
C2         VARCHAR2   many                 one
DBMS_STATS 패키지도 동일한 역할을 하는 함수들을 제공합니다. 하지만 인자를 지정하는 방법이 조금 다릅니다. 사용의 편의성을 위해서 다음과 같이 사용자 정의 함수를 만듭니다.
SQL> -- dbms_stats (conversion)
SQL> create or replace function convert_me(p_value in raw, p_type in varchar2)
  2  return varchar2
  3  is
  4     v_number		number;
  5     v_varchar2   varchar2(4000);
  6  begin
  7     if (p_type = 'NUMBER') then
  8        dbms_stats.convert_raw_value(p_value, v_number);
  9        return v_number|| '';
 10     elsif (p_type = 'VARCHAR2') then
 11        dbms_stats.convert_raw_value(p_value,v_varchar2);
 12        return v_varchar2;
 13     /* other data types */
 14     else
 15        return p_value || '';
 16     end if;
 17  end;
 18  /

Function created.

SQL> 
SQL> select convert_me('C102', 'NUMBER') from dual;

CONVERT_ME('C102','NUMBER')
--------------------------------------------------------------------------------
1

SQL> select convert_me('6D616E79', 'VARCHAR2') from dual;

CONVERT_ME('6D616E79','VARCHAR2')
--------------------------------------------------------------------------------
many
그러면 다음과 같이 SQL 문장내에서 사용할 수 있습니다.
SQL> select
  2  	column_name,
  3  	data_type,
  4  	convert_me(low_value, data_type) as low_value,
  5  	convert_me(high_value, data_type) as high_value
  6  from
  7  	user_tab_cols
  8  where
  9  	table_name = 'T1'
 10  ;

COLUMN_NAM DATA_TYPE  LOW_VALUE            HIGH_VALUE
---------- ---------- -------------------- --------------------
C1         NUMBER     1                    10000
C2         VARCHAR2   many                 one
위와 같은 일을 하지 않아도 되게끔 오라클이 알아서 변환해주면 좋을텐데요. 하여간 간혹 위와 같은 변환 작업이 필요한 경우가 있습니다. 그때 잘 활용하면 좋겠습니다.
저작자 표시
신고

'오라클 > 프로그래밍' 카테고리의 다른 글

RAW 값 변환하기  (0) 2010.10.12
Trackback 0 : Comment 0

Write a comment


Batching NLJ에 대한 오라클 매뉴얼의 설명

오라클/SQL 튜닝 2010.10.11 18:57
Oracle 11g의 Batching NLJ에 의해 물리 I/O(Physical I/O)가 발생할 때 정렬이 깨어지는 것처럼 보이는 현상을 이 포스트에서 소개한 바 있습니다.

오늘 우연히 오라클 매뉴얼(Performance Tuning Guide)에서 Oracle 11g의 Batching NLJ에 대해 설명하고 있는 부분을 발견했습니다.

단순하면서도 명확하게 설명되어 있습니다. 이 설명과 더불어 제가 설명한 힌트와 히든 파라미터를 같이 이해하면 운영 시스템에서 문제가 발생했을 때 효과적으로 대처하실 수 있을 거 같습니다.
저작자 표시
신고

'오라클 > SQL 튜닝' 카테고리의 다른 글

조인 순서 제어하기 2  (2) 2010.11.04
조인 순서 제어하기  (3) 2010.10.25
비주얼 SQL 튜닝?  (6) 2010.10.21
Batching NLJ에 대한 오라클 매뉴얼의 설명  (0) 2010.10.11
PLAN_HASH_VALUE  (0) 2010.10.06
tags : Batching NLJ
Trackback 0 : Comment 0

Write a comment


MOATS - Mother of All Tuning Scripts?

오라클/기타 2010.10.06 16:20
MOATS라는 재미있는 유틸리티를 발견했습니다. 특정 인스턴스의 성능에 대한 리포트를 제공하는데요, 심플하면서도 유용합니다.
   SQL> SELECT * FROM TABLE(moats.top(5));

-- 아래 내용이 자동 갱신됨
-- 단, 창의 폭을 최소 110으로 키워야 함

   + INSTANCE SUMMARY ------------------------------------------------------------------------------------------+
   | Instance: ora112           | Execs/s:     2.0 | sParse/s:     0.0 | LIOs/s:  219637.3 | Read MB/s:     0.0 |
   | Cur Time: 13-Aug 19:25:14  | Calls/s:     0.0 | hParse/s:     0.0 | PhyRD/s:      0.5 | Write MB/s:    0.0 |
   | History:  0h 0m 26s        | Commits/s:   0.0 | ccHits/s:     1.5 | PhyWR/s:      2.9 | Redo MB/s:     0.0 |
   +------------------------------------------------------------------------------------------------------------+

   + TOP SQL_ID (child#) -----+ TOP SESSIONS ---------+      + TOP WAITS -------------------------+ WAIT CLASS -+
   |  50% | bwx4var9q4y9f (0) | 71                    |      | 100% | latch: cache buffers chains | Concurrency |
   |  50% | bq2qr0bhjyv1c (0) | 133                   |      |  50% | SQL*Net message to client   | Network     |
   |  50% | 799uuu8tpf6rk (0) | 6                     |      |                                    |             |
   +--------------------------------------------------+      +--------------------------------------------------+

   + TOP SQL_ID ----+ PLAN_HASH_VALUE + SQL TEXT ---------------------------------------------------------------+
   | bwx4var9q4y9f  | 2119813036      | select  /*+ full(a) full(b) use_nl(a b) */  count(*) from  sys.obj$ a,  |
   |                |                 | ys.obj$ b where  a.name = b.name and rownum <= 1000002                  |
   + ---------------------------------------------------------------------------------------------------------- +
   | bq2qr0bhjyv1c  | 644658511       | select moats_ash_ot( systimestamp, saddr, sid, serial#, audsid, paddr,  |
   |                |                 | er#,                                  username, command, ownerid, taddr |
   + ---------------------------------------------------------------------------------------------------------- +
   | 799uuu8tpf6rk  | 2119813036      | select  /*+ full(a) full(b) use_nl(a b) */  count(*) from  sys.obj$ a,  |
   |                |                 | ys.obj$ b where  a.name = b.name and rownum <= 1000001                  |
   + ---------------------------------------------------------------------------------------------------------- +
텍스트 기반의 화면을 제공하기 때문에 SQL*Plus만 수행할 수 있으면 사용할 수 있다는 것이 장점이네요. 우리도 이런 유용한 컴포넌트들을 많이 개발했으면 좋겠습니다.

(아래는 제 PC에 설치한 후 샘플로 실행해본 화면입니다)

저작자 표시
신고
Trackback 0 : Comment 0

Write a comment


PLAN_HASH_VALUE

오라클/SQL 튜닝 2010.10.06 10:55
SQL A과 SQL B의 실행 계획이 같은지 다른지 비교할 수 있는 가장 쉬운 방법은 무엇일까요?

오라클이 제공하는 PLAN_HASH_VALUE가 정답이 아닐까합니다. 이 값은 말그대로 실행 계획에 대한 해시 값입니다. 해시 값이기 때문에 100% 유일성이 보장되지는 않지만, 거의 대부분의 경우 식별자로 사용할 수 있습니다.

오라클에서는 동일한 SQL 텍스트를 가지는 SQL 커서가 복수 개의 실행 계획을 가질 수 있도록 허용합니다. 이런 유연함 덕분에 여러 가지 성능 문제가 나타나지만요.

간단한 예제를 통해 논의해보겠습니다. 이 예제는 Oracle 11g의 Adaptive Cursor Sharing 기능을 이용하고 있습니다.

오라클 버전은 11gR2입니다.

TPACK@ukja1120> select * from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
편향된 데이터를 가지는 테이블 T1을 만듭니다. 컬럼 C1에 대해서 인덱스를 만들고, 히스토그램을 만듭니다.
TPACK@ukja1120> 
TPACK@ukja1120> create table t1
  2  as
  3  select level as c1 from dual connect by level <= 10000
  4  union all
  5  select 1 as c1 from dual connect by level <= 100000
  6  ;

Table created.

TPACK@ukja1120> 
TPACK@ukja1120> create index t1_n1 on t1(c1);

Index created.

TPACK@ukja1120> 
TPACK@ukja1120> exec dbms_stats.gather_table_stats(user, 't1', method_opt=>'for all columns size skewonly');

PL/SQL procedure successfully completed.
"100"의 값이 조건으로 사용될 때는 INDEX RANGE SCAN을 사용합니다.
TPACK@ukja1120> explain plan for
  2  select count(*) from t1 where c1 = 100;

Explained.

TPACK@ukja1120> @plan
TPACK@ukja1120> set line 200
TPACK@ukja1120> select * from table(dbms_xplan.display);
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |     3 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |       |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T1_N1 |     1 |     3 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
"1"의 값을 조건으로 사용할 때는 TABLE FULL SCAN을 사용합니다.
TPACK@ukja1120> explain plan for
  2  select count(*) from t1 where c1 = 1;

Explained.

TPACK@ukja1120> select * from table(dbms_xplan.display);
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |    53   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 99173 |   290K|    53   (4)| 00:00:01 |
---------------------------------------------------------------------------
이제 "100"의 값과 "1"의 값을 반복적으로 사용하면서 쿼리를 수행합니다. 단, 바인드 변수를 사용해서 Adaptive Cursor Sharing이 동작하도록 유도합니다.
TPACK@ukja1120> var b1 number;
TPACK@ukja1120> exec :b1 := 100;

PL/SQL procedure successfully completed.

TPACK@ukja1120> select count(*) from t1 where c1 = :b1;

  COUNT(*)
----------
         1

TPACK@ukja1120> 
TPACK@ukja1120> exec :b1 := 1;

PL/SQL procedure successfully completed.

TPACK@ukja1120> select count(*) from t1 where c1 = :b1;

  COUNT(*)
----------
    100001

TPACK@ukja1120> 
TPACK@ukja1120> exec :b1 := 100;

PL/SQL procedure successfully completed.

TPACK@ukja1120> select count(*) from t1 where c1 = :b1;

  COUNT(*)
----------
         1

TPACK@ukja1120> 
TPACK@ukja1120> exec :b1 := 1;

PL/SQL procedure successfully completed.

TPACK@ukja1120> select count(*) from t1 where c1 = :b1;

  COUNT(*)
----------
    100001

TPACK@ukja1120> 
TPACK@ukja1120> exec :b1 := 100;

PL/SQL procedure successfully completed.

TPACK@ukja1120> select count(*) from t1 where c1 = :b1;

  COUNT(*)
----------
         1

TPACK@ukja1120> 
TPACK@ukja1120> exec :b1 := 1;

PL/SQL procedure successfully completed.

TPACK@ukja1120> select count(*) from t1 where c1 = :b1;

  COUNT(*)
----------
    100001

TPACK@ukja1120> 
TPACK@ukja1120> exec :b1 := 100;

PL/SQL procedure successfully completed.

TPACK@ukja1120> select count(*) from t1 where c1 = :b1;

  COUNT(*)
----------
         1

TPACK@ukja1120> 
TPACK@ukja1120> exec :b1 := 1;

PL/SQL procedure successfully completed.

TPACK@ukja1120> select count(*) from t1 where c1 = :b1;

  COUNT(*)
----------
    100001

네 개의 차일드 커서가 생겼네요. (네 개가 생기는 이유는 무엇일까요? 추가적인 설명 필요!)
TPACK@ukja1120> col sql_id new_value sql_id
TPACK@ukja1120> select sql_id, version_count, plan_hash_value
  2  from v$sqlarea
  3  where sql_text = 'select count(*) from t1 where c1 = :b1';

SQL_ID        VERSION_COUNT PLAN_HASH_VALUE
------------- ------------- ---------------
7dwqb1wjmp5hm             4        73337487
차일드 커서를 나타내는 V$SQL 뷰를 보면 자식의 개수는 네 개이지만 PLAN_HASH_VALUE는 2개가 존재합니다. 즉, 실제 실행 계획은 두 개(INDEX RANGE SCAN + TABLE FULL SCAN) 뿐입니다.
TPACK@ukja1120> select sql_id, child_number, plan_hash_value
  2  from v$sql
  3  where sql_id = '&sql_id';
old   3: where sql_id = '&sql_id'
new   3: where sql_id = '7dwqb1wjmp5hm'

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE
------------- ------------ ---------------
7dwqb1wjmp5hm            0        73337487
7dwqb1wjmp5hm            1        73337487
7dwqb1wjmp5hm            2        73337487
7dwqb1wjmp5hm            3      3724264953
DBMS_XPLAN.DISPLAY_CURSOR 함수를 이용해서 실행 계획을 직접 조회해보면 더 상세한 정보를 얻을 수 있습니다.
TPACK@ukja1120> select * from table(dbms_xplan.display_cursor('&sql_id', null));
old   1: select * from table(dbms_xplan.display_cursor('&sql_id', null))
new   1: select * from table(dbms_xplan.display_cursor('7dwqb1wjmp5hm', null))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7dwqb1wjmp5hm, child number 0
-------------------------------------
select count(*) from t1 where c1 = :b1

Plan hash value: 73337487

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |       |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T1_N1 |     1 |     3 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=:B1)

SQL_ID  7dwqb1wjmp5hm, child number 1
-------------------------------------
select count(*) from t1 where c1 = :b1

Plan hash value: 73337487

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |       |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T1_N1 |     1 |     3 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=:B1)

SQL_ID  7dwqb1wjmp5hm, child number 2
-------------------------------------
select count(*) from t1 where c1 = :b1

Plan hash value: 73337487

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |       |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T1_N1 |     1 |     3 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=:B1)

SQL_ID  7dwqb1wjmp5hm, child number 3
-------------------------------------
select count(*) from t1 where c1 = :b1

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    53 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 99173 |   290K|    53   (4)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("C1"=:B1)


76 rows selected.
PLAN_HASH_VALUE가 실행 계획을 구분하는데 사용될 수 있다는 특징을 잘 이용하면 실행 계획 변경을 추적하는데 사용할 수도 있습니다. 가령 AWR에 저장된 Top SQL과 SQL 텍스트는 동일하지만(SQL_ID) PLAN_HASH_VALUE은 다른 새로운 SQL 문이 등장했다면 실행 계획의 변경이 발생한 것으로 의심해볼 수 있습니다.

단, 이런 현상이 발생했다고 하더라도 하나의 SQL 텍스트가 여러 개의 실행 계획을 가질 수 있기 때문에 보다 정밀한 분석이 필요할 것입니다.

저작자 표시
신고

'오라클 > SQL 튜닝' 카테고리의 다른 글

조인 순서 제어하기 2  (2) 2010.11.04
조인 순서 제어하기  (3) 2010.10.25
비주얼 SQL 튜닝?  (6) 2010.10.21
Batching NLJ에 대한 오라클 매뉴얼의 설명  (0) 2010.10.11
PLAN_HASH_VALUE  (0) 2010.10.06
Trackback 0 : Comment 0

Write a comment

티스토리 툴바