따봉 구걸남's 블로그

1. 간단확인

/*+ gather_plan_statistics cost_based */

select * from table(dbms_xplan.display_cursor(null /*sql_id*/, null /*child_cursor*/, 'all allstats last'));

2. trace 파일 

alter session set tracefile_identifier='XXX';

alter session set timed_statistics=true;

alter session set statistics_level=all;

alter session set max_dump_file_size=unlimited;

alter session set events '10046 trace name context forever, level 12';

-- sql 실행

alter session set events '10046 trace name context off';

토드를 사용하면 object 의 DDL 정보를 쉽게 확인 할 수 있다. 

그래서 계정의 경우 부여받은 role, 권한 까지  모두 확인할수 있다.

그러나 orange 에서는 그게 안되서 각 권한에 해당하는 sql 을 작성해서 확인해야한다. 

뭐.. 항상 토드를 쓰는 환경으로 갈수는 없는 일이니까  이번 기회에 정리를 하자.

## Role 이 포함한 권한 확인

- select * from dba_tab_privs where grantee='ROLE 이름';

## 유저에게 부여된 권한 확인

- select * from dba_role_privs where grantee='USER이름';

- select * from dba_sys_privs where grantee='USER이름';  ( create view 등) 

## DB object 에 대한 권한을 누가 가지고 있나  ( role 은 제외되고 grantee 만 되나보다)

  select * from dba_tab_privs where table_name= 'PLSQL/TABLE명';

## A 의 object권한을 B 에 동일하게 주기

select 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO 받는사람(B);'

from dba_tab_privs where grantee=주는사람(A);

## userB 에게 없는 userA 의 object 권한을 주기 (A 의 권한이랑 똑같이 B 에게 주기)

권한은 중복으로 줘도 상관이 없지만 중복없이 주는 쿼리는 outer join 이나 차집합으로 구할수 있다.

SELECT 'GRANT '||A.PRIVILEGE||' ON '||A.OWNER||'.'||A.TABLE_NAME||' TO userB;' STMT
  FROM (
        select '유저A 이름' AS NM, GRANTEE,OWNER,TABLE_NAME,GRANTOR,PRIVILEGE,TYPE
          from dba_tab_privs
         where GRANTEE ='userA'
       )A,
       (
        select '유저B 이름' AS NM, GRANTEE,OWNER,TABLE_NAME,GRANTOR,PRIVILEGE,TYPE
          from dba_tab_privs
         where GRANTEE ='userB'
       )B
 WHERE A.OWNER = B.OWNER(+) 
   AND A.TABLE_NAME = B.TABLE_NAME(+)
   AND A.GRANTOR = B.GRANTOR(+)
   AND A.PRIVILEGE = B.PRIVILEGE(+)
   AND B.NM IS NULL
ORDER BY A.OWNER, A.TABLE_NAME, A.PRIVILEGE, A.TYPE

 서브쿼리란 무엇인지 개념과 활용을 알아보자

1. 조인과의 차이 

조인과 서브쿼리는 구조적으로 차이가 있다.
  조인은 A, B 두 집합이 동등한 관계인 반면 서브쿼리는 종속적 관계가 나타난다. 

  Select a.col1, b.col2
    from A, B
   where a.col1 = b.col1 

  조인의 결과는 조인에 성공한 만큼 나온다. 
  A 와 B 의 관계가 1: N 이면 N 만큼 결과가 나오고 1:1 이면 1 만큼 나온다. 
  조인의 한쪽은 인라인 뷰가 될수도 있다.
  이때 둘의 관계는 동일하다고 했으므로 인라인 뷰에서 바깥쪽 테이블 컬럼을 사용할수가 없다. 

  Select a.col1, b.col2
    from A, (select b.col1 from B where a.col1 = b.col)  => error
   where a.col1 = b.col1 

  반면 서브쿼리는 종속적 관계가 나타난다고 했다. 
  쿼리의 결과는 1 : N 관계에도 언제나 1 이다.
  종속적 관계라서 서브쿼리에 A 의 컬럼을 사용할수 있다. 

  select a.col1
    from A
   where exists (select '1' from B where a.col1 = b.col1 )  => ok

 스칼라서브쿼리도 동일한 성질을 갖는다. ( 결과row 가 변하지 않는다, 종속적 관계이다.)


2. 서브쿼리 역할  

 - 확인 : 결과 데이터가 증가하지 않는다. 감소만 할뿐 (N 이상이 되지 않는다.) 
 - 공급 : 주쿼리로 상수화된 결과를 공급

# 선/후 수행 서브쿼리
- 선수행 서브쿼리 : 서브쿼리 테이블이 driving, 모든 값에 엑세스. 
unique 정렬 수행. 메모리에 임시 집합 구성. 
- 후수행 서브쿼리 : driving 에서 조건에 만족하는 한건의 데이터에 대해 
    서브쿼리를 한번 수행. 만족하면 결과로 추출. 
    주 쿼리의 데이터 건수 만큼 반복 수행. 
    
3. 서브쿼리 실행계획 제어

1. NL join -> Hash join

select colC, sum(colD)
 from tab a
 where colA in (select col
   from tab b
    where colB = 'X')
  and colB between '20190101' and '20191010'
 group by colC;
=>

1-1) 힌트

select /*+ USE_HASH(a,@SQ) */
colC, sum(colD)
 from tab a
 where colA in (select /*+ QB_NAME(SQ) */
   col
   from tab b
    where colB = 'X')
  and colB between '20190101' and '20191010'
 group by colC; 
 
 
1-2) 인덱스 사용 못하게
 
select colC, sum(colD)
 from tab a
 where trim(colA) in (select trim(col)
                                   from tab b
                                 where colB = 'X')
  and colB between '20190101' and '20191010'
 group by colC; 
   
1-3) from 절에 풀기
   
select /*+ ORDERED USE_HASH(A,B) */
 a.colC, sum(a.colD)
  from tab a, tab b
 where a.colA = b.col
 and b.colB = 'X'
   and a.colB between '20190101' and '20191010'
 group by a.colC; 

## 메인쿼리 : 서브쿼리 = 1:M 이면 데이터가 증가. => 인라인 뷰로 1:1 관계

select /*+ USE_HASH(A,B) */
 a.colC, sum(a.colD)
  from tab a, 
   ( select col
       from tab b
      where b.colB='X'
   group by col
   )
 where a.colA = b.col
   and a.colB between '20190101' and '20191010'
 group by a.colC; 

2. 조인 순서 변경

- QB_NAME 힌트 => 선수행 서브쿼리

select /*+ PUSH_SUBQ(@SQ) */
colC, sum(colD)
 from tab a
 where colA in (select /*+ QB_NAME(SQ) */
   col
   from tab b
    where colB = 'X')
  and colB between '20190101' and '20191010'
 group by colC; 

- 일반 조인으로 변경 

select /*+ ORDERED USE_HASH(A,B) */
 a.colC, sum(a.colD)
  from tab a, 
   ( select col
       from tab b
      where b.colB='X'
   group by col
   )
 where a.colA = b.col
   and a.colB between '20190101' and '20191010'
 group by a.colC; 

  

- 'USER_A' : 테이블 'TAB_A' 를 소유

- 'USER_B' : 'TAB_A' 를 참조하는 view 'VW_B' 를 소유

- 'USER_C' : 'VW_B' 에 대한 권한을 받으려는 유저

  위의 상황에서 아래와 같이 일반적 방식으로 권한을 주면 발생하는 에러.

  grant select on USER_B.VIEW_B to USER_C;

  => ORA-01720 발생

 아래와 같이 참조되는 테이블(TAB_A)을 뷰의 오너 인 USER_B 에게 with grant option 으로 권한을 먼저 줘야한다. 

  grant select on USER_A.TAB_A to USER_B with grant option;

  grant select on USER_B.VIEW_B to USER_C; 

 문제는 해결될 수 있으나 프로그램의 복잡도가 증가하므로

 A 소유 테이블로 B 소유 뷰가 만들어 지고, 이를 다시 C에게 권한을 주는 일이 없도록 가이드 하는 것이 중요.

'ABC_DEF' 같은 중간에 언더바('_')가 들어간 패턴의 데이터를 검색하고 싶다면 어떻게 해야할까

like 에서 "_" (언더바) 는 한 글자를 의미한다. 

그래서 like '%_%'  이렇게 하면 길이가 한자리 이상의 모든 데이터가 나온다.

이러한 경우를 위해 오라클에서는 ESCAPE 문을 제공한다. 

where column_a like '%#_DEF' ESCAPE '#'  이렇게 하면 '_DEF' 로 끝나는 데이터들이 추출이 된다.

사용하려는 특수문자 앞에 아무 문자나 넣어주고 뒤에 ESCAPE 라고 써주고 뺄 문자를 명시해주면 된다.

즉 # 을 안쓰고 다른 문자를 써도 결과는 동일하다. 

where column_a like '%A_DEF' ESCAPE 'A'

은 위의 쿼리와 결과가 동일하다. 

아래는 SYS_ 로 시작하는 문자를 검색하는 간단한 예제이다.

ADG 구성에서 stand by DB 에서 많이 알려진 버그.

stand by 에서 특정 뷰를 조회하면 ora-4023 에러가 발생한다. 

oracle 에 SR 진행하면 결론은 버그패치를 권고한다. 

문제를 완전해결하는 방법은 버그패치이지만 그닥 서비스에 영향이 없고

버그패치가 부담스럽다면 

alter system flush shared_pool  를 실행한다. 

tnsnames.ora 를 통한 접속 테스트

- 1. 로컬에서 원격지 서버로의 tnsping 이 되는가?

  -1.1 'tnsping 이 된다' 면 접속이 되어야 하는게 정상.

     - 2. tnsping 은 되지만 접속이 안된다면..

         -2.1 tnsnames.ora 에 적힌 정보가 맞는지 확인해본다. 

        ============================================

sqlplus id/password@원격지dbIP:oracleport/dbname

ex) sqlplus scott/tiger@10.10.10.10:1521/orcl

이렇게 직접 입력해서 접속이 된다면 통신에는 아무 문제가 없다. 

        ============================================

        - 2.2 tnsnames.ora 의 경로가 맞는지 확인해본다. 

          -> 유저 설정파일(ex. .bash_profile) 에서 TNS_HOME 경로를 확인한다.

          -> 현재 바라보고 있는 tnsnames.ora 를 편집해서 적용이 되는지 확인해본다. 

  -1.2 'tnsping이 안된다'면 클라이언트와 서버간의 통신에 문제가 있다는 것. 

     방화벽 등을 확인해본다. 

DB에 NAS 를 마운트 시키고 export 백업을 시도하니 아래 같은 에러가 발생했다.

============================================================

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters and Automatic Storage Management options

ORA-39001: invalid argument value

ORA-39000: bad dump file specification

ORA-31641: unable to create dump file "/oraclebackup/full01.dmp"

ORA-27086: unable to lock file - already in use

Linux-x86_64 Error: 37: No locks available

Additional information: 10

============================================================

해당 덤프 디렉토리에 대한 경로나 권한에는 문제가 없음을 확인한다. 

이상이 없다면 OS level 의 문제인것이다. 

[oraPF@perfdb01:/oraclebackup/perf]$service nfslock status

rpc.statd가 정지됨

[root@perfdb01 perf]# service nfslock start

NFS statd를 시작 중:                                       [  OK  ]

다시 expdp 작업을 돌리니 정상동작.

as of timestamp 구문으로 과거시점의 데이터를 조회 할 수가 있다. 

몇가지 테스트를 해보았다. 

1. as of 의 시점은 commit 시 인가? db입력 시 인가?

create table test_fb

( A   varchar2(10),

B   date

);   

SQL>  insert into test_fb values('aaa',sysdate);

1 row created.

SQL>  select TO_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'YY

-------------------

2018-10-23 15:18:17

SQL>  insert into test_fb values('bbb',sysdate);

 select TO_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;

1 row created.

SQL> SQL> 

TO_CHAR(SYSDATE,'YY

-------------------

2018-10-23 15:37:04

SQL> 

SQL>  insert into test_fb values('ccc',sysdate);

 select TO_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;

1 row created.

SQL> SQL> 

TO_CHAR(SYSDATE,'YY

-------------------

2018-10-23 15:40:18

SQL> select a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted  from test_fb;

A      TIME_INSERTED

------ -------------------

aaa    2018-10-23 15:18:10

bbb    2018-10-23 15:37:04

ccc    2018-10-23 15:40:17

SQL>  SELECT a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted FROM test_fb 

AS OF TIMESTAMP TO_TIMESTAMP('2018-10-23 15:39:00', 'YYYY-MM-DD HH24:MI:SS');  

no rows selected

### 두번째와 세번쨰 데이터 입력시점 사이로 as of timestamp 를 써보았지만 결과가 없다.

SQL> commit;

Commit complete.

SQL>  select TO_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'YY

-------------------

2018-10-23 15:41:29

SQL>  SELECT a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted FROM test_fb 

AS OF TIMESTAMP TO_TIMESTAMP('2018-10-23 15:40:00', 'YYYY-MM-DD HH24:MI:SS');   

no rows selected

SQL>   SELECT a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted FROM test_fb 

AS OF TIMESTAMP TO_TIMESTAMP('2018-10-23 16:00:00', 'YYYY-MM-DD HH24:MI:SS');

A      TIME_INSERTED

------ -------------------

aaa    2018-10-23 15:18:10

bbb    2018-10-23 15:37:04

ccc    2018-10-23 15:40:17

SQL>   SELECT a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted FROM test_fb 

AS OF TIMESTAMP TO_TIMESTAMP('2018-10-23 15:45:00', 'YYYY-MM-DD HH24:MI:SS');

A      TIME_INSERTED

------ -------------------

aaa    2018-10-23 15:18:10

bbb    2018-10-23 15:37:04

ccc    2018-10-23 15:40:17

SQL>   SELECT a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted FROM test_fb 

AS OF TIMESTAMP TO_TIMESTAMP('2018-10-23 15:41:00', 'YYYY-MM-DD HH24:MI:SS');

no rows selected

SQL>   SELECT a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted FROM test_fb 

AS OF TIMESTAMP TO_TIMESTAMP('2018-10-23 15:42:00', 'YYYY-MM-DD HH24:MI:SS');

A      TIME_INSERTED

------ -------------------

aaa    2018-10-23 15:18:10

bbb    2018-10-23 15:37:04

ccc    2018-10-23 15:40:17

### 관찰 : statement 완료시점 아닌 commit 시점으로 flashback 한다.

2. 시간차를 두고 데이터를 입력해보고 원하는 시점으로 돌아갈수 있는지 보자.

SQL>  insert into test_fb values('ddd',sysdate);

commit;

select TO_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;

1 row created.

SQL> 

Commit complete.

SQL> 

TO_CHAR(SYSDATE,'YY

-------------------

2018-10-23 16:03:02

SQL>  insert into test_fb values('eee',sysdate);

commit;

select TO_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;

1 row created.

SQL> 

Commit complete.

SQL> 

TO_CHAR(SYSDATE,'YY

-------------------

2018-10-23 16:14:02

SQL>  insert into test_fb values('fff',sysdate);

commit;

select TO_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;

1 row created.

SQL> 

Commit complete.

SQL> 

TO_CHAR(SYSDATE,'YY

-------------------

2018-10-23 16:19:18

SQL> 

SQL> 

SQL> select a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted  from test_fb 

  2  ;

A      TIME_INSERTED

------ -------------------

aaa    2018-10-23 15:18:10

bbb    2018-10-23 15:37:04

ccc    2018-10-23 15:40:17

ddd    2018-10-23 16:03:00

eee    2018-10-23 16:13:59

fff    2018-10-23 16:19:18

6 rows selected.

*** aaa, bbb,ccc 는 입력시점은 다르지만 커밋 시점이 같다. 

     ddd,eee,fff 는 커밋 시점이 다르다.

SQL>  SELECT a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted FROM test_fb 

AS OF TIMESTAMP TO_TIMESTAMP('2018-10-23 16:15:00', 'YYYY-MM-DD HH24:MI:SS');

  2  

A      TIME_INSERTED

------ -------------------

aaa    2018-10-23 15:18:10

bbb    2018-10-23 15:37:04

ccc    2018-10-23 15:40:17

ddd    2018-10-23 16:03:00

eee    2018-10-23 16:13:59

*** 예상대로 16시19분 커밋된 fff 는 나오지 않는다. 

     조금 더 테스트를 해보았다. 어느 순간에 eee 가 안보일지..

SQL>  SELECT a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted FROM test_fb 

AS OF TIMESTAMP TO_TIMESTAMP('2018-10-23 16:14:00', 'YYYY-MM-DD HH24:MI:SS');

  2  

A      TIME_INSERTED

------ -------------------

aaa    2018-10-23 15:18:10

bbb    2018-10-23 15:37:04

ccc    2018-10-23 15:40:17

ddd    2018-10-23 16:03:00

eee    2018-10-23 16:13:59

SQL>  SELECT a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted FROM test_fb 

AS OF TIMESTAMP TO_TIMESTAMP('2018-10-23 16:13:59', 'YYYY-MM-DD HH24:MI:SS');

  2  

A      TIME_INSERTED

------ -------------------

aaa    2018-10-23 15:18:10

bbb    2018-10-23 15:37:04

ccc    2018-10-23 15:40:17

ddd    2018-10-23 16:03:00

eee    2018-10-23 16:13:59

SQL>  SELECT a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted FROM test_fb 

AS OF TIMESTAMP TO_TIMESTAMP('2018-10-23 16:13:58', 'YYYY-MM-DD HH24:MI:SS');

  2  

A      TIME_INSERTED

------ -------------------

aaa    2018-10-23 15:18:10

bbb    2018-10-23 15:37:04

ccc    2018-10-23 15:40:17

ddd    2018-10-23 16:03:00

eee    2018-10-23 16:13:59

*** ㅇㅇ??  뭔가 이상하다.

SQL>  SELECT a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted FROM test_fb 

AS OF TIMESTAMP TO_TIMESTAMP('2018-10-23 16:13:57', 'YYYY-MM-DD HH24:MI:SS');

  2  

A      TIME_INSERTED

------ -------------------

aaa    2018-10-23 15:18:10

bbb    2018-10-23 15:37:04

ccc    2018-10-23 15:40:17

ddd    2018-10-23 16:03:00

SQL>  SELECT a, TO_char(b, 'YYYY-MM-DD HH24:MI:SS') time_inserted FROM test_fb 

AS OF TIMESTAMP TO_TIMESTAMP('2018-10-23 16:13:58', 'YYYY-MM-DD HH24:MI:SS');

  2  

A      TIME_INSERTED

------ -------------------

aaa    2018-10-23 15:18:10

bbb    2018-10-23 15:37:04

ccc    2018-10-23 15:40:17

ddd    2018-10-23 16:03:00

eee    2018-10-23 16:13:59

*** 예상과 다르게 16시13분59초에 커밋된 데이터가 16시13분58초 시점으로 flashback 했을때도 보였다. 

*** sysdate 의 시간과 flashback 이 사용하는 시간이 다른듯하다. 

장애 시 다운타임을 줄이거나, 또는 zero down time 을 목적으로 각 벤더사들은 

저마다 다른 구성의 DB를 사용한다.

대표적으로 많이 쓰이는 구조들을 정리 및 비교 해보자. 

• Oracle

     - rac

특징 : 다수의 인스턴스가 하나의 스토리지를 공유.

        다수의 인스턴스 중 한쪽 인스턴스가 장애가 나도 나머지 인스턴스에서 서비스가 지속된다.

제약 : 다수의 인스턴스가 하나의 스토리지를 사용하기에 중복작업이 발생하지 않기 위한 overhead 가 필요.

• MariaDB

     - replication  

   특징 : 2대 이상의 서버로 구성. Master 에서만 R/W 가능 Slave 에서는 Read 만 가능.

   제약 : Slave 에서는 Read 만 가능하므로 Read 부하만 줄여줄수 있다. 

           두대의 장비로 두대 만큼의 성능개선 효과를 볼수가 없다. 

           장애시 아주 짧은 순간이라 할지라도 failover 되는 down time 이 발생한다. 

    - galera cluster 

  특징 : 최소 3대 이상의 서버로 구성. 서버별 별도의 스토리지. 

          하나의 서버에 이상이 생겨도 다른 서버에서 서비스 지속 가능. 

          무중단 운영 가능. 

  제약 :  각각 서버들이 중복된 DB 를 갖는 구조이기 때문에 스토리지 측면에서 비효율 적임.

           Replication 구조를 asynchronous 라고 하는데 갈레라 클러스터는 다수의 스토리지이기때문에 

           asynchronous 라고 할수 있을것 같지만 synchronous 라고 분류한다. 

• MSSQL

    - MSCS (Microsoft Cluster Server)

    특징 : MS 서버의 기능인 클러스터링으로 두서버를 묶어서 이중화 하는 기술. 

            한쪽서버가 down 이되면 failover 되어 passive 가 active 가 됨.

            스토리지는 하나의 스토리지를 쓴다.

    제약 : 스토리지가 하나인 점에서는 Rac 와 유사하지만 passive 에서는 write 불가하다.

            Master-slave 구조라는 점에서는 Replication 과 유사.

벤더마다 고가용성을 위한 DB 구조의 구현방식이나 용어들이 조금씩 다르다.

그렇지만 공유 스토리지를 사용하는가? 아닌가? multi-master 인가? master-slave? 인가 에 따라 특징을 나눌수있다. 

대개 master-slave 구조에서는 failover 시 down time 이 발생할수 밖에 없고, 

Slave 에서 조회업무는 분산해줄수 있지만 실질적으로 AP 에서 조회만 따로 분리내기가 쉽지않다.

그래서 Slave 의 활용도가 떨어지기 때문에 메인 업무의 DB 구조를 master-slave 로 하는 곳은 많지않다.

mariadb 의 galera cluster 는 무중단 운영이 가능한 멀티마스터 구조를 무료로 사용할 수 있는 것이 강점이다. 

물론 상용에서는 무료로 운영하는데 어려움이 있겠지만..

## 몽고db 추가해보자