DBMS_XPLAN 패키지를 사용하여 플랜 조회

업무/㏈ 데이타베이스
posted by 지니우스 2011. 7. 5. 14:00
spool 쿼리플랜.txt
set timing on
set linesize 1000
set pagesize 200

-- 각각 쿼리별로 GATHER_PLAN_STATISTICS 힌트를 명시안하고자 할때 세션에 전체적으로 적용함
alter session set statistics_level=all; 

-- 대상 쿼리
SELECT /*+ GATHER_PLAN_STATISTICS */
             *
   FROM ... ;

SELECT *
  FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'advanced allstats last +alias +outline +predicate'));

spool off
exit

10053 trace 3종세트

업무/㏈ 데이타베이스
posted by 지니우스 2010. 6. 11. 09:33
-- (1) trace 시작
alter session set events '10053 trace name context forever, level 1';

-- (2) trace 종료
alter session set events '10053 trace name context off';

-- (3) trc 파일 위치 확인
select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
  from v$process a, v$session b, v$parameter c, v$instance d
 where a.addr = b.paddr
   and b.audsid = userenv('sessionid')
   and c.name = 'user_dump_dest'

sqlplus 상에서 백스페이스 쓸 수 있도록 설정하기

업무/㏈ 데이타베이스
posted by 지니우스 2010. 3. 1. 23:14
stty erase + 컨트롤v + 컨트롤h

OS별 Shared Library PATH 환경변수

업무/Σ 창고
posted by 지니우스 2009. 12. 7. 15:43
출처: http://yamoe.tistory.com/97

AIX                   LD_LIBRARY_PATH
OS/2                 LIBPATH
Windows NT/95  PATH
Solaris              LD_LIBRARY_PATH
Linux                LD_LIBRARY_PATH
HP/UX              SHLIB_PATH

Lock 의 type (V$lock에서의 "type"컬럼에 올수 있는 것들)

업무/㏈ 데이타베이스
posted by 지니우스 2008. 10. 10. 16:23

* MR(Media Recovery , 매체 복구)
* RT(Redo Thread, 리두 쓰레드)
* UN(User Name, 사용자명)
* UL(PL/SQL User Lock , PL/SQL 사용자 잠금(lock))
* DX(Distributed Xaction,분산 활동)
* CF(Control File,컨트롤 화일)
* IS(Instance State,인스턴스 상태)
* FS(File Set,파일 세트)
* IR(Instance Recovery,인스턴스 복구)
* ST(Disk Space Transaction,디스크 공간 트랜잭션)
* TS(Temp Segment,임시세그먼트)
* IV(Libary Cache Invalidation , 라이브러리 캐쉬 무효)
* LS(Log Start or Switch,로그 시작 또는 로그 스위치)
* RW(Row Wait, 행 대기)
* SQ(Sequence Number, 시퀀스 번호)
* TE(Extend Table,테이블 확장)
* TT(Temp Table,임시 테이블)
* TX(Transaction:행레벨 잠금 )
* TM(테이블 레벨 잠금)

부하 걸리는 SQL 중지시키기 위해 SESSION 죽이기

업무/㏈ 데이타베이스
posted by 지니우스 2008. 10. 10. 14:03
1) 해당 쿼리(SQL_ID) 찾기
SELECT * FROM V$SQL
WHERE SQL_TEXT LIKE 'CREATE%'

SQL_ID
------------------
9X2DB38V0ZCKF
CX14DD7TJRXUW

2) 쿼리를 실행한 SESSION 알아내기
SELECT * FROM V$SESSION
WHERE SQL_ID IN ('9X2DB38V0ZCKF','CX14DD7TJRXUW')


SID                          SERIAL#
------------------  -----------------------
60                           1012


3) SESSION 죽이기
ALTER SYSTEM KILL SESSION '60,1012'


FULL TABLE SCAN 속도

업무/㏈ 데이타베이스
posted by 지니우스 2008. 9. 26. 11:01
* 환경
- SunOS nice12 5.10 Generic_127127-11 sun4v sparc SUNW,Netra-CP3260 (CPU COUNT 48)
- ORACLE 10G


* QUERY
SQL> SELECT COUNT(1) FROM SMS_2008;

-------------------------------------------
 Result 
-------------------------------------------
26059566


* 소요시간
 309.89 sec


* 결론

 2600만건의 데이타를 풀스캔할 경우 300초 정도 걸린다.




*** PARALLEL TEST ***

* QUERY
SQL> SELECT /*+ PARALLEL(A,48) */ COUNT(1) FROM SMS_2008 A;


* 소요시간
 112.95 sec


* 결론
 
 병렬처리할 경우 확실히 빠르다. 


Sqlldr (sqlload) Parallel Execution

업무/㏈ 데이타베이스
posted by 지니우스 2008. 9. 25. 15:50

[서론]
1. 오라클 데이터 로딩 퍼포먼스를 극대화 한다.
2. MySQL 데이터 로딩 퍼포먼스를 극대화 한다.

[본론]
Oracle 로딩
sqlldr를 실행할 서버의 CPU 개수와 데이터 파일개수를 일치시켜주자.
나누어 로딩할 파일개수는 조절이 가능하므로 
리소스를 고려하여 파일을 쪼개어 실행한다.

0. 데이터를 넣을 대상 empty table 준비
데이터를 넣을 테이블을 하나 생성한다.
퍼포먼스를 향상 시키려면 direct 모드로 loading을 해야하고,
direct mode로 loading을 하려면, 
테이블은 인덱스를 생성하지 않아야 하며, 인덱스가 기생성되어 있는 테이블에 
로딩을 하는 것이라면 index를 drop 하거나 아래와 같이 disable 시켜준다.

 SQL> drop index index명; --또는 PK로 설정되어 있을 경우,
 SQL> alter table table명 drop constraint primary key; -- 하면된다. 

1. Datafile 준비
DB에 넣을 파일을 4등분하여 4개의 파일로 저장한다.
shell> wc -l file.txt
20000
shell> split -l 5000 -d file.txt file. # 5000라인씩 끊는다고 가정하자.

각각의 파일이름은 /data/file.01, /data/file.02, /data/file.03, /data/file.04 라고 가정하자.

shell> head /data/file.01
홍길동 1024050
김갑순 2384867
이정화 3847293
박경모 3842983


2. Controlfile 준비
[file1.ctl]
load data infile '/data/file1.txt'
truncate into table temp_4parallel -- 실패를 대비해 truncate 구문으로 준비한다.
fieleds terminated by '\t' optionally enclosed by '"'
trailing nullcols
(
col1 char,
col2 char
)

[file2.ctl]
load data infile '/data/file2.txt'
truncate into table temp_4parallel -- 실패를 대비해 truncate 구문으로 준비한다.
fieleds terminated by '\t' optionally enclosed by '"'
trailing nullcols
(
col1 char,
col2 char
)

.. 동일한 방법으로 file3.ctl과 file4.ctl도 생성해준다.

3. 실행파일 준비(필자는 csh로 작성했다)
[fileload.csh]
#!/bin/csh
SQLLDR user/passwd CONTROL=file1.ctl DIRECT=TRUE PARALLEL=TRUE
SQLLDR user/passwd CONTROL=file2.ctl DIRECT=TRUE PARALLEL=TRUE
SQLLDR user/passwd CONTROL=file3.ctl DIRECT=TRUE PARALLEL=TRUE
SQLLDR user/passwd CONTROL=file4.ctl DIRECT=TRUE PARALLEL=TRUE

주의사항 : 반드시 parallel loading은 direct 모드로 실행해야 한다.
(앞서 언급한대로, direct 모드로 실행시에는 index를 disable 또는 drop해야하니 주의하자)

4. 실행
shell> fileload.csh

CPU개수가 4개라고 가정했을 때, 서버로드가 높지 않다면, 4배의 속도향상을 
체험할 수 있을 것이다.

MySQL 로딩
1. 퍼포먼스
MySQL의 로딩 퍼포먼스는 Row의 사이즈 및
Row 의 Count, 즉, 파일의 사이즈에 따라 민감하게 영향을 받는데,
이로 인해 한번에 로딩하는 량을 줄이는 방법으로 퍼포먼스를 극대화 할수 있다.

/data01/mysql/bin/mysqlimport -h [server IP] -udbm -pdbmpw --local -d
user_group /data/file1.txt & # -d 옵션은 오라클의 control 파일에서의 truncate into... 구문과 같다.

/data01/mysql/bin/mysqlimport -h [server IP] -udbm -pdbmpw --local 
user_group /data/file2.txt & # -d 옵션이 생략되면 오라클 control 파일에서 append into.. 구문과 같다.

/data01/mysql/bin/mysqlimport -h [server IP] -udbm -pdbmpw --local 
user_group /data/file3.txt & 

/data01/mysql/bin/mysqlimport -h [server IP] -udbm -pdbmpw --local 
user_group /data/file4.txt &

 이와 같은 방법으로 여러개의 프로세스를 동시에 실행하여 퍼포먼스를 N배만큼 빠르게 할 수 있다.

★ 기타 오라클 명령어 ☆

업무/㏈ 데이타베이스
posted by 지니우스 2008. 9. 19. 16:41
   테이블의 테이블스페이스를 변경하고자 할때  

  SQL> ALTER TABLE 테이블명 MOVE TABLESPACE 테이블스페이스명;


   롤백 세그먼트 확인   
SELECT * FROM DBA_ROLLBACK_SEGS;


   오랜시간 수행되고 있는 트랙잭션 조회   
SELECT * FROM V$SESSION_LONGOPS;


   현재 수행중인 QUERY 조회   
SELECT * FROM V$SQL WHERE SQL_ID = '5tgkrzz2g4n9d';


   Rollback segment 에 대한 Contention  조회   
SELECT RN.NAME, (RS.WAITS/RS.GETS) RBS_HEADER_WAIT_RATIO
FROM V$ROLLSTAT RS, V$ROLLNAME RN
WHERE RS.USN = RN.USN
ORDER BY 1;

=> rbs_header_wait_ratio 가 0.01 보다 크면, rollback segment 갯수를 추가한다


   LOCK걸린 OBJECT 조회   
SELECT * FROM V$LOCKED_OBJECT


   LOCK걸린 SESSION 조회   
SELECT A.SID, A.SERIAL#
  FROM V$SESSION A, V$LOCK B, DBA_OBJECTS C
 WHERE A.SID = B.SID 
   AND B.ID1 = C.OBJECT_ID
   AND B.TYPE = 'TM'                   -- 테이블 레벨 잠금
   AND C.OBJECT_NAME = '테이블명'


   LOCK걸린 SESSION 죽이기   
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' [immediate]


   shutdown 했는데 반응이 없을때     
ps -ef|grep LOCAL=NO |grep -v grep |awk '{print $2}'|xargs kill -9
(LOCAL=NO 죽이면 쭉~~~ 내려갑니다)



   코멘트 달기   

 

* 테이블에 코멘트 달기

comment on table table_name is '코멘트테이블입니다';

 

* 컬럼에 코멘트 달기

comment on colum table_name.column_name is '컬럼에 코멘트를 입력'


(코멘트는 테이블명 및 컬럼에 2,000 byte 를 달 수 있습니다.)




  쿼리 실행시간 기록  


# set timing on



  파일저장  


# spool 파일명

# spool off



  명령프롬프트로 이동 (SQL*Plus에서)  


# host



  스키마에 DEBUG권한 주기   


GRANT DEBUG CONNECT SESSION TO 스키마명;




  스키마 LOCK 풀기   


ALTER USER <SCOTT> ACCOUNT UNLOCK;


  NLS_LANG 조회   


SELECT MAX(VAL1)||'_'||MAX(VAL2)||'.'||MAX(VAL3) AS NLS_LANG
  FROM (
        SELECT DECODE(PARAMETER,'NLS_LANGUAGE',1,'NLS_TERRITORY',2,'NLS_CHARACTERSET',3) AS SEQ
             , DECODE(PARAMETER,'NLS_LANGUAGE',VALUE) AS VAL1
             , DECODE(PARAMETER,'NLS_TERRITORY',VALUE) AS VAL2
             , DECODE(PARAMETER,'NLS_CHARACTERSET',VALUE) AS VAL3
          FROM NLS_DATABASE_PARAMETERS
         WHERE PARAMETER IN ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET')
       )



  bind 값 조회 (10g 이상)   


select * from v$sql_bind_capture



  listener 인식하는데 시간이 소요될 경우 (바로 적용되도록)   


alter system register

오라클 권한(Privileges) & 롤(Roles) 정보

업무/㏈ 데이타베이스
posted by 지니우스 2008. 9. 19. 13:25

1. 권한(Privileges)
  • DBA_SYS_PRIVS
  • SESSION_PRIVS
  • DBA_TAB_PRIVS
  • DBA_COL_PRIVS


2. 롤(Roles)

  • DBA_ROLES : 데이타베이스에 있는 모든 롤
  • DBA_ROLES_PRIVS : 사용자들과 롤들에 부여된 롤들
  • ROLE_ROLE_PRIVS : 롤들에 부여된 롤들
  • DBA_SYS_PRIVS : 사용자들과 롤들에 부여된 시스템권한
  • ROLE_SYS_PRIVS : 롤들에 부여된 시스템권한
  • ROLE_TAB_PRIVS : 롤들에 부여된 객체권한
  • SESSION_ROLES : 현재 활성화되어 있는 사용자의 롤들

 

 

 

티스토리툴바