본문 바로가기
DB/DBA

DBA 유용한 정보

javamix 2009. 7. 17. 17:20

1. DISK READ가 과도한 SQL문
shared_pool_size가 작거나 sql문장의 튜닝이 안되어  disk가 read가 많은 sql문을 찾는다.
select disk_reads, sql_text
  from v$sqlarea
where disk_reads > 10000 order by disk_reads desc

2. 주요 MEMORY 사용 내역
--DB의 주요 메모리 사용을 보여준다. DB가 사용하는 메모리는 v7.3의 경우 OS메모리의 2/5 를,
--v8.x 버젼의 경우 1/2 정도를 할당해 주는 것이 좋다.
select name, value
  from v$parameter
where name in('db_block_buffers','db_block_size','shared_pool_size','sort_area_size')

3. Literal SQL 찾기
SELECT substr(sql_text,1,40) "SQL",
            count(*), sum(executions) "Execs"
  FROM v$sqlarea
GROUP BY substr(sql_text,1,40)
 HAVING count(*) > 10
order by 2 desc;
select s.sql_Text
  from v$sql s,
        (
          select substr(sql_text, 1,&&size) sqltext, count(*)
            from v$sql
          group by substr(sql_text, 1,&&size)
         having count(*) > 10
        ) D
where substr(s.sql_text,1,&&size) = D.sqltext ;

4. max extents 변경
alter index ADJUSTMENT_IDX0
storage
(
maxextents unlimited
);

5. 데이터베이스 복구
치명적인 오류가 아닐 경우 아래와 같이 처리하여 복구가 가능하다.
> startup mount;
> recover;
> alter database open;

6. 특정 파티션의 데이터 보기
select * from orders partition (orders_1001)

7. tablespace 의 삭제
1. 데이타를 가지고 있는 테이블스페이스는 INCLUDING CONTENTS 옵션 필수.
2. 컨트롤 파일 내에 있는 파일 포인터만 삭제됩니다. (데이터 파일은 존재)
3. 데이타베이스 파일은 여전히 존재하므로 운영체제 레벨에서 명시적으로 삭제 해야함.
4. 데이터 딕셔너리의 내용이 변경 됩니다.
5. TableSpace를 삭제 하기 전에 테이블스페이스를 오프라인 상태로 할 것을 권장함.
* 참고 및 실행 예제
- INCLUDING CONTENTS : TableSpace에 Data가 이미들어있을 경우 들어있는 내용을 포함해서 모두 삭제 합니다.
- CASECADE CONSTRAINTS : Primary Key가 설정되어 있는 경우에 child 의 Foriegn Key를 삭제하고 Tablespace를 삭제 합니다.
SQL> DROP TABLESPACE indxtran INCLUDING CONTENTS ;
* 참고 (비 활성화(OFFLINE) 테이블 스페이스)
- 오프라인 상태 테이블스페이스의 데이타에는 접근 할 수가 없음.
- SYSTEM 테이블스페이스와 활성화된 롤백세그먼트를 가진 모든 테이블스페이스는
오프라인 상태가 될 수 없음.
- 테이블스페이스가 오프라인상태가 됐을때 오라클 서버는 모든 관련된 모든 데이터 파일을 오프라인 상태로 만듭니다.
- 테이블스페이스는 세가지 모드로 오프라인 상태가 될 수 있음.
* normal : 디폴드 값, 테이블스페이스의 모든 데이터 파일에 대해 체크 포인트를 수행한다.
* immediate : CheckPoint를 수행하지 않는다.
* temporary : 테이블스페이스의 ONLINE데이터 파일에 대해 체크 포인트를 수행한다.
예제)
SQL> ALTER TABLESPACE app_data OFFLINE;

8. import 샘플
--USER 를 변경하여 테이블을 생성하고 데이터를 IMPORT한 예제.
imp user/password file=/usr82/oraback/TCC_031213.dmp fromuser=tcc00 touser=back00 buffer=8192000 ignore=n commit=y i
ndexes=N tables=receiptdetail

9. 파티션 분할하는 방법
> alter table TABLE_NAME split partition PARTITIONED_TABLE_NAME
at (20031132) into (
partition NEW_PARTITIONED_TABLE_NAME1 tablespace TABLESPACE_NAME,
partition NEW_PARTITIONED_TABLE_NAME2 tablespace TABLESPACE_NAME) ;
1. partition table 확인
> select * from DBA_TAB_PARTITIONS where table_name = 'ORDERS' ;
2. ORDERS_1001 파티션 테이블이 2010년01월01일로 되어 있으므로
2003년11월31일까지와 2003년12월31일까지의 두개 파티션으로 분할한다.
> alter table ORDERS split partition ORDERS_1001 at (20031132) into (
partition ORDERS_0311 tablespace TCCDATA,
partition ORDERS_1001_1 tablespace TCCDATA ) ;
> alter table ORDERS split partition ORDERS_1001 at (20031232) into (
partition ORDERS_0312 tablespace TCCDATA,
partition ORDERS_1001_2 tablespace TCCDATA ) ;
3. > drop table ORDERS drop partition ORDERS_1001_1 ;
> drop table ORDERS drop partition ORDERS_1001_2 ;

10. analyze
analyze는 cost-base or choose mode에서 사용가능하다.
--check optimizer mode
select value from v$parameter where name = 'optimizer_mode'
--check whether beging analyzed or not
select tablespace_name, table_name, last_analyzed from dba_tables where owner = 'TCC00'
--하나씩 삭제
> ANALYZE TABLE WEB3PL.IF_ORDERS DELETE STATISTICS ;
--한꺼번에 analyze 하거나 삭제하거나... (사용자는 항상 대문자로...)
exec dbms_utility.analyze_schema('TCC00','DELETE')
exec dbms_utility.analyze_schema('&1','COMPUTE');
--analyze
ANALYZE TABLE TCC00."ORDERDETAIL" COMPUTE STATISTICS FOR TABLE ;
ANALYZE TABLE TCC00."ORDERS" COMPUTE STATISTICS FOR TABLE ;

11. EXPORT를 SPLIT하여 받기
목적 : EXPORT시 2 GB 이상을 받지 못하는 문제를 해결하기 위하여 사용함.
주의 : UNIX 상에서 KORN SHELL (KSH) 에서 사용하여야 한다.
1. KORN SHELL로 변경
# ksh
2. EXPORT (compress는 제거해도 된다.)
# echo|exp file=>(compress|split -b 1024m - expdmp-) userid=sys/manager
tables = pd_wbl log=pd_wbl.log
3. IMPORT (uncompress는 제거해도 된다.)
# echo|imp file=<(cat expdmp-*|uncompress) userid=sys/manager
tables=pd_wbl ignore=y commit=yc
 
12. 아카이브 모드 설정하기
1. 데이터베이스 모드를 확인한다.
> select log_mode from sys.v$database ;
2. initSID 파일을 변경한다.
log_archive_start = true
log_archive_dest = disk$rdbms:[oracle.archive]
log_archive_format = "T%TS%S.ARC"
3. 오라클 종료 후 마운트 한다.
> shutdown ;
> startup mount ;
4. 아카이브 모드로 변경한다. (노아키이브모드 : alter database noarchivelog)
> alter database archivelog ;
5. 오픈한다.
> alter database open ;
6. 데이터베이스 모드를 확인한다.
> select log_mode from sys.v$database ;
7. 강제로 스위치를 발생시켜 로그가 생기도록 한다.
> alter system switch logfile ;
8. 파라미터 파일을 확인한다.
> show parameter archive ;

------------------------------------------------------------------------
NOARCHIVE MODE에서 ARCHIVE MODE로 전환시
다음 과정을 따르지 않을 경우 alert 파일에 ARC0: media recovery disabled
오류가 나타나므로 처음 1회는 아래와 같이 startup 시켜준다.
1. initSID.ora 파일을 편집한다.
- LOG_ARCHIVE_START=TRUE
- LOG_ARCHIVE_FORMAT=arch_%t_%s.arc
- LOG_ARCHIVE_DEST="/usr2/oracle/admin/ultra/arch"
2. 시스템을 shutdown 시킨다.
3. SQL> startup mount;
4. SQL> alter database archivelog;
5. SQL> alter database open
6. 확인 --> SQL> archive log list;

13. extents 오류가 발생가능한 objects 검색
select seg.tablespace_name, seg.segment_type, seg.segment_name, seg.extents, spc.max_size, seg.next_extent
from (select tablespace_name, sum(bytes), max(bytes) as max_size from dba_free_space group by tablespace_name) spc,
dba_segments seg where spc.tablespace_name = seg.tablespace_name and spc.max_size < seg.next_extent
and seg.owner = 'TCC00'

14. FULL EXPORT
--mknod 를 이용하여 named pipe 를 만든다.
$mknod /tmp/exp_pipe p
ORACLE_HOME=/usr11/app/oracle/product/817; export ORACLE_HOME
ORACLE_SID=GLSTCC; export ORACLE_SID
NLS_LANG=American_America.KO16KSC5601; export NLS_LANG
PATH=$ORACLE_HOME/bin:$PATH; export PATH
cd /usr77/data/BACKUP
compress </usr77/data/BACKUP/exp_pipe> GLSTCC_`date +'%y%m%d'`.dmp.Z &
exp system/manager file=/usr77/data/BACKUP/exp_pipe volsize=7168000000 log=/usr77/data/BACKUP/GLSTCC_`date +'%y%m%d'`.log full=y buffer=5048000

15. CRONTAB 에서 PROCEDURE 호출하기
CRONTAB 에서 PROCEDURE를 호출하는 스크립트 작성하기
===================================================
PURPOSE
-------
CRONTAB 에서 PROCEDURE를 호출하는 예제와 사용방법을 살펴보기로 한다.

Explanation & Example
---------------------
1. cron file 환경 정의
vi /var/adm/cron/cron.allow
---> oracle

2. crontab 에서 호출하는 내용
vi /var/spool/cron/crontabs/oracle file 내용 확인
7 * * * * /app/oracle/product/816/test.sh > /app/oracle/product/816/test.log
(매 7분에 test.sh 스크립트를 수행)

3. test.sh 내용

export ORACLE_HOME=/app/oracle/product/816
export ORACLE_SID=CWGMISD
/app/oracle/product/816/bin/sqlplus -s sale/salesale < !
set timing on
set serveroutput on
execute proc_sba_history;
exit 0

4. 주의사항
위 test.sh 화일에서 ORACLE_HOME 과 ORACLE_SID 를 기술하지 않으면
다음과 같은 에러가 발생할 수 있다.
ERROR:
ORA-12545: Connect failed because target host or object does not exist
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}] | [INTERNAL]
where <logon> ::= <username>[/<password>][@<connect_string>] | /
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}] | [INTERNAL]
where <logon> ::= <username>[/<password>][@<connect_string>] | /
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

Reference Documents
-------------------
<Note:1885.1

16. LOG SWITCH 발생시키기
SQL> alter system switch logfile;

17. 트랜잭션별 RBS 사용내역 확인
현재의 transaction 이 사용하는 rollback segment 는 어떤 것일까?
1) PROCESS ID 로 check 하고 싶을 때 :
SELECT r.name "ROLLBACK SEGMENT NAME ",
l.sid "ORACLE PID",
p.spid "SYSTEM PID ",
NVL ( p.username , 'NO TRANSACTION'),
p.terminal
FROM v$lock l, v$process p, v$rollname r, v$session s
WHERE l.sid = s.sid and s.paddr=p.addr
AND TRUNC (l.id1(+)/65536) = r.usn
AND l.type(+) = 'TX'
AND l.lmode(+) = 6
ORDER BY r.name

2) SESSION ID 별로 check 하고 싶을 때
select s.sid, s.serial#, s.username, r.name "ROLLBACK SEG"
from v$session s, v$transaction t, v$rollname r
where s.taddr=t.addr
and t.xidusn = r.usn;
특정 rollback segment를 사용하는 session을 kill하고 싶은 경우
alter system kill session 'sid, serial#';
 
18. 파티션 추가하기
ALTER TABLE ORDERS
ADD PARTITION ORDERS_0308 VALUES LESS THAN ('20030832 ')
STORAGE(INITIAL 52428800 NEXT 52428800 PCTINCREASE 0) TABLESPACE TCCDATA;
ALTER TABLE ORDERDETAIL
ADD PARTITION ORDERDETAIL_0308 VALUES LESS THAN ('20030832 ')
STORAGE(INITIAL 52428800 NEXT 52428800 PCTINCREASE 0) TABLESPACE TCCDATA;
ALTER TABLE RECEIPT
ADD PARTITION RECEIPT_0308 VALUES LESS THAN ('20030832 ')
STORAGE(INITIAL 52428800 NEXT 52428800 PCTINCREASE 0) TABLESPACE TCCDATA;
ALTER TABLE RECEIPTDETAIL
ADD PARTITION RECEIPT_0308 VALUES LESS THAN ('20030832 ')
STORAGE(INITIAL 52428800 NEXT 52428800 PCTINCREASE 0) TABLESPACE TCCDATA;

19. 테이블스페이스 늘리기
ALTER TABLESPACE TCCINDEX ADD
DATAFILE '/usr66/oradata/GLSTCC/glstcc_data88.idx' SIZE 2000M
AUTOEXTEND OFF
 
20. 중복데이터 확인
SELECT *  FROM ST_SCAN_TEMP A
WHERE ROWID > (SELECT MIN(ROWID)
  FROM ST_SCAN_TEMP B
WHERE A.GB = B.GB
    AND A.SCAN_DT   = B.SCAN_DT
    AND A.SCAN_HMS = B.SCAN_HMS
    AND A.AWBL_NO  = B.AWBL_NO)
ORDER BY A.AWBL_NO;
 
21. DEAD LOCK 테스트 및 Parameter
1. dead lock관련된 parameter는 init.ora에 아래의 값으로 지정하시면 됨
   _lm_dd_interval=1
   앞에 under score가 있는겁니다.(hidden parameter)
2.test 방법...
A session
delete from dept where deptno = 10;
B session
delete from dept where deptno = 20;
A session
delete from dept where deptno = 20;
waiting....
B session
delete from dept where deptno = 10;
waiting....
---> 이때 deadlock detect가 빨리 일어나야 합니다.

22. DEAD LOCK 찾기
Select a.serial#, a.sid, a.username, b.id1, c.sql_text
  from v$session a, v$lock b, v$sqltext c
where b.id1 in( select distinct e.id1
                      from v$session d, v$lock e
                   where d.lockwait = e.kaddr)
   and a.sid = b.sid
   and c.hash_value = a.sql_hash_value
   and b.request = 0;
  
  
23. SNAPSHOT

--현재사용중인 스냅샷 확인
select * from sys.dba_snapshots;
 
--스냅샷으로 테이블을 만들면 OBJECT VIEW를 만든다.
SELECT * FROM ALL_MVIEW_ANALYSIS;

--스냅샷 만들기
=========
사용 예 :
=========
**************************************************
[1] Simple snapshot 과 Snapshot log (on Master):
**************************************************
MASTER SITE (REMOTE)
--------------------
Create a snapshot log on the master table:
sqlplus scott/tiger
create snapshot log on emp
tablespace users
storage (initial 10K pctincrease 0)
pctfree 5;
 
NOTE: init.ora 파라미터는 필요하지 않다.

SNAPSHOT SITE (LOCAL)
---------------------
1. 다음처럼 initSID.ora 파라미터를 설정한다.
snapshot_refresh_interval = 20
snapshot_refresh_processes = 2
snapshot_refresh_keep_connections = true
open_cursors = 250

2. sqldba
connect internal
shutdown
startup (init.ora 파라미터 적용)

3. sqlplus scott/tiger
create database link chicago
connect to scott identified by tiger -- remote, master table의 owner/passwd 지정.
using 'chicago';
NOTE:SQL*Net V2 의 'chicago'는 tnsnames.ora에 정의되어 있어야 한다.
4. sqlplus scott/tiger
create snapshot emp_snap
pctfree 5
pctused 60
refresh fast
start with sysdate
next sysdate + (1/288) /* 5 분마다 refresh */
as select * from emp@chicago;

MASTER SITE (REMOTE)
--------------------
1. sqlplus scott/tiger
insert into emp (empno,ename,job,hiredate,deptno)
values (1234, 'SCOTT','DBA',sysdate,10);
commit;
SNAPSHOT SITE (LOCAL)
---------------------
1. sqlplus scott/tiger (or simply switch to client window)
select count(*) from emp_snap;
snapshot 이 자동으로 refresh 될 때까지 반복한다.(약 5 분)
이 방법은 fast refresh 가 작동됨을 보여준다.
(2) REFRESH COMPLETE OPTION 을 이용한 SNAPSHOT.
=====================
SNAPSHOT SITE (LOCAL)
=====================
Snapshot 생성
---------------
SQL) connect system/manager
Connected.
SQL) grant resource to saj;
Grant succeeded.
Database Link 생성
--------------------
 
SQL) create database link aixlink
2 connect to system identified by manager
3 using 't:tcaix:V716';
Database link created.

Create the Snapshot
-------------------
SQL) connect saj/saj
Connected.
SQL) create snapshot deptsnap2
2 pctfree 5
3 pctused 60
4 refresh complete
5 start with sysdate
6 next sysdate + 1/(288*20)
/* REFRESH EVERY 15 SECONDS */
7 as select * from system.dept@aixlink;
 
Snapshot created.
 
24. LOCK PROCESS 확인
--LOCK OBJECT 찾기
select /*+ rule */
username un, osuser ou, substr(s.machine,1,10) mc,
s.sid sid , s.serial# ser, l.type ty,
decode(lmode,1,'NONE',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') mh,
decode(request,1,'NONE',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') mr,
o.name ob, l.ctime ct, id1,
decode(s.command,0,null,2,'insert',6,'update',7,'delete',s.command) sql,
s.process pgm_pss
from v$lock l, v$session s, sys.obj$ o
where l.sid = s.sid
and l.id1 = o.obj#(+)
and username is not null
order by id1,sid
 
--LOCK SESSION 확인
SELECT S.SID, S.SERIAL#, P.SPID, P.PID, P.PROGRAM, V.EVENT, V.P1, V.P2, V.P3, S.PROGRAM
FROM V$SESSION S, V$PROCESS P, V$SESSION_WAIT V
WHERE S.PADDR = P.ADDR
AND S.SID = V.SID
AND EVENT NOT LIKE '%message%'
AND EVENT NOT LIKE '%timer%'
 
--LOCK 원인
select a.sid,
decode(a.type,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
a.type) lock_type,
decode(a.lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(a.lmode)) mode_held,
decode(a.request,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(a.request)) mode_requested,
to_char(a.id1) lock_id1, to_char(a.id2) lock_id2
from v$lock a
where (id1,id2) in
(select b.id1, b.id2 from v$lock b where b.id1=a.id1 and
b.id2=a.id2 and b.request>0)
 
 
--LOCK SQL 확인
SELECT S.PROCESS, S.SID, S.SERIAL#, S.STATUS, S.SERVER, S.SCHEMANAME,
S.OSUSER, S.MACHINE, S.TERMINAL, S.PROGRAM, T.SQL_TEXT, T.OPTIMIZER_MODE, T.OPTIMIZER_COST
FROM V$SESSION S, V$SQL T, V$PROCESS P WHERE S.SQL_ADDRESS = T.ADDRESS AND S.SQL_HASH_VALUE = T.HASH_VALUE
AND S.PADDR = P.ADDR
AND P.SPID = '324'
 
--LOCK TABLE 찾기
select b.username username, c.sid sid, c.owner object_owner,
c.object object, b.lockwait, a.sql_text SQL
from v$sqltext a, v$session b, v$access c
where a.address=b.sql_address
and a.hash_value=b.sql_hash_value
and b.sid = c.sid
and c.owner != 'SYS'
 
##########################################################################################
 
--현재 Lock Process 를 확인한다.
--col waiting_on for a75
--col serial# for 999999
--col prg for a13
--col OraPid for 9999
--col command for a10
--col aa Heading "DB-User" for a8
--col bb Heading "OS-Pid" for a6
--set pau off
--set linesize 150
 
select s.sid SID, s.serial#, decode(s.command,
'0','NO', '1','Create 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.program, 1, 30) prg, p.spid bb,
substr(
rtrim(w.event) || ': ' ||
rtrim(p1text,' ') || ' ' || to_char(p1) || ',' ||
rtrim(p2text,' ') || ' ' || to_char(p2) || ',' ||
rtrim(p3text,' ') || ' ' || to_char(p3),1,75) as waiting_on
from v$process p, v$session s, v$session_wait w
where w.wait_time = 0
and w.sid = s.sid
and s.paddr = p.addr
and s.sid <> 14
and event not like '%pmon timer%'
and event not like '%rdbms ipc%'
and event not like '%smon timer%'
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 '%queue messages%'
and event not like '%wakeup time%'
order by 6,1

25. PARTITIONED TABLE 생성
--table partition 생성하기
 
--partitioned table 확인
select * from DBA_TAB_PARTITIONS order by table_name
 
--각 partition별 현황
select * from DBA_SEGMENTS where owner = 'NELS'
and segment_type = 'TABLE PARTITION'
and segment_name like 'PD_WBL%' order by partition_name
 
--extent 요구 space
select partition_name, bytes/(1024*1024) as total, extents,
initial_extent/(1024*1024) as init, next_extent/(1024*1024) as next
from DBA_SEGMENTS where owner = 'NELS'
and segment_type = 'TABLE PARTITION' and segment_name like 'PD_WBL%'
order by partition_name
 
--table partition 생성
alter table PD_WBL
add partition 'PD_WBL_0201
values less than ('20020132')
storage (initial 200M next 40M pctincrease 0) tablespace DTS_PD;

26.  INSTANCE 관리
--INSTANCE
SELECT * FROM SYS.GV_$INSTANCE

27. PARTITIONED TABLE 현황
--partitioned table 리스트 확인
select
segment_name, partition_name, tablespace_name,
trunc(initial_extent/(1024*1024),0) || ' M' initsize,
trunc(next_extent/(1024*1024),0) || ' M' initsize, extents,
trunc(bytes/(1024*1024),0) || ' M' propsize
from DBA_SEGMENTS where owner = 'NELS' and segment_type = 'TABLE PARTITION'
order by segment_name, partition_name

[출처] PARTITIONED TABLE 현황 |작성자 송가리

'DB/DBA' Related Articles

티스토리툴바