|
[SQL과 PL/SQL]SQL이란...
-------------
SQL정의
-------------
1. SQL의 역사
1970년내 중반에 IBM의 프로토타입 RDBMS인 "시스템 R"에서 SQL언어가 개발되었습니다. E.F.Codd 는 "시스템 R"을 "Journal of Research and Development"의 1976년 11월호에서 설명하였습니다.
오라클사는 최초로 상업적으로 활용할 수 있는 SQL의 구현을 1979년에 소개하였습니다. 또한 SQL은 IBM의 DB2와 SQL/DS 데이타베이스 시스템에서도 구현되었습니다.
----------------
장점
----------------
1. 비절차적 언어 :
*한번에 한레코드가 아닌 레코드 집합을 처리합니다.
SQL은 사용자가 보다 높은 레벨의 자료구조를 가지고 작업할 수 있게 합니다. 하나의 레코드를 처리하는 것이 아니라 레코드 집합을 처리합니다. 레코드 집합의 가장 일반적인 형태가 테이블입니다. 모든 SQL문에서 입출력은 집합 단위로 행해집니다. SQL의 집합 성질은 한 SQL 문의 결과가 다른 SQL문의 입력으로 사용되는 것을 허용합니다.
*데이타를 자동으로 찾습니다.
SQL은 사용자가 데이타 접근방법을 지정하도록 요구하지 않습니다. 이 특성은 사용자가 원하는 결과를 얻는데만 집중할 수 있도록 합니다. 모든 SQL문에는 질의 최적화기가 사용됩니다. 질의 최적화기란 RDBMS의 한 부분으로 지정된 데이타를 접근하는 보다 빠른 방법을 결정하는 것입니다. 질의 최적화기는 어떤 색인이 있는지를 알고 있으며 이것들을 적절한 곳에 사용합니다. 즉, 사용자는 테이블에 접근할때 테이블이 색인되어 있는지 혹은 어떤 색인를 가지고 있는지를 알 필요가 없습니다.
2 모든 사용자를 위한 언어
다음과 같은 모든 범위의 사용자들은 모든 종류의 데이타베이스 작업을 위하여 SQL을 사용합니다.
*시스템 관리자,데이타베이스 관리자,응용프로그램머,관리업무 종사자,의사결정지원시스템 종사자, 다양한 일반 사용자SQL은 일관성 있고, 응용력이 좋고, 배우기 쉬운 그렇게 많지 않은 명령어들을 제공합니다. 기본 명령어들은 몇시간 안에 배울 수 있으며, 고급 명령어들도 몇일이면 배울 수 있습니다.
======================
Oracle SQL명령의 형태
======================
A.DML
- 기존 스키마 개체의 데이터를 질의하거나 조작한다.
- Table이나 View에서 행을 삭제하는 Delete, Table이나 View에 행을 추가하는 Insert, Table 이나 View를 잠궈 일시적으로 다른 사용자의 Access를 제한하는 Lock Table, 하나이상의 Table이나 View에서 Data를 읽어 들이는 Select, Table이나 View에 있는 값을 변경하는 Update등이 있다.
B.Transaction 제어 명령어
- DML 명령에 위해 변경된 Data를 관리하고 DML명령문을 Transaction으로 Group화한다.
- 트랜잭션의 변경내용을 영구적으로 만드는 Commit, Transaction의 취소를 하는 RollBack, RollBack할수 있는 시점을 설정하는 SavePoint, 트랜잭션의 속성을 변경하는 Set Transaction등이 있다.
C.DDL
- 스키마개체를 정의하고 구조를 수정또는 삭제한다.
- 스키마게체와 다른 DataBase 구조를 작성,변경,삭제하는 Create, Alter, Drop과 개체의 이름을 변경하는 Rename, 개체의 구조를 제거 하지 않으면서 모든 Data를 삭제하는 Truncate,스키마 개체에 대한 통계를 생성하는 Analyze, 권한과 롤을 부여하거나 철회하는 Grant, Revork, 감사 Option의 설정과 해제를 하는 Audit, NoAudit, Data Dictioanry에 설명을 추가하는 Comment등의 명령이 있다.
D.Session 제어 명령
- 특정 사용자 Session의 속성을 관라한다.
- SQL추적 기능을 활성화 하거나 비활성화 하는것과 같은 기능을 수행하여 현재 세션을 변경하는 Alter Session, 현재 Session에 대한 Role을 활성화 하거나 비활성화 하는 Set Role등의 명령이 포함된다.
E.System 제어명령
- Oracle Server Instance의 속성을 변경한다.
- Alter System이 유일한 명령임.
F.내장 SQL명령
- 프로시저 언어 프로그램에서 DDL 및 DML 및 Transaction 제어 명령문과 공동으로 작업한다.
- 커서를 정의하고 할당/해제하는 DECLARE Cursor, Open , Close와 데이터베이스 이름을 설정하고 ORacle에 접속하는 DECLARE DataBase, Connect, 변수이름을 할당하고 설명자를 초기화 하며 오류와 경고 조건이 초리되는 방법을 지정하는 DECLARE Statement, Describe, Whenever와 SQL명령어를 구문분석하며 DataBase로 부터 Data를 읽어들이는 PREPARE EXECUTE, Execute Immediate, Fetch등의 명령이 있다.
[SQL과 PL/SQL]PL/SQL이란...
==========
PL/SQL
==========
1. 정의
- PL/SQL은 Oracle의 SQL Procedure언어의 확장 판이다. PL/SQL을 사용하여 SQL명령문과 프러시져 구조를 혼합해서 사용할수 있습니다.
- PL/SQL은 프러시져/함수/패키지 같은 PL/SQL 프로그램 단위를 정의하고 실행하는 기능을 제공한다. 프로그램 단위는 익명의 블록(Anonymous block)과 내장 프러시저(Stored procedure)등의 규개의 범주로 나뉘어 진다. Anonymous block은 application내에서 이름을 가지지 않고 database에 저장되지 않는 PL/SQL block이다. Stored procedure는 Oracle이 database에 저장하고 application으로 부터 이름에 의해 호출되는 PL/SQL block이다. Stored procedure를 생성할 때 Oracle은 procedure를 parse하고 그 parsed representation을 database내에 저장한다.
3. 언어 구성자
a .변수와 상수
- 변수 및 상수는 프러시저/함수/패키지 내에 정의할수 있다. SQL또는 PL/SQL에서 변수와 상수를 사용하여 값을 보관하거나 제공할수 있다.
- Server manager와 같은, 일부 interactive tool들은 user의 현재 session에 variable들을 정의하는 것을 허용한다. 그렇게 정의된 variable들은 procedure 나 package들 내에 선언된 variable들에 똑같이 사용될 수 있다.
b. 커서(Cursor)
- 프러시저/함수/패키지 내에서 명시적으로 커서를 선언하여 오라클 데이터를 Record별로 처리할수 있다.
c. 예외사항 처리
- 내부오류나 사용자 오류조건을 명시적으로 정의(Exception 사용)
- 예외사항(오류)이 발생하면 PL/SQL Code의 실행이 중단되며 호출한 Routine이 실행된다.
4. Dynamic SQL in PL/SQL
Dynamic SQL을 이용하여 stored procedure과 anonymous PL/SQL block을 쓸 수 있다. Dynamic SQL 문장은 source program에 embedded 되지 않고 문자열로 저장되어 program 수행 시에 입력되어 만들어진다.
좀 더 일반적인 목적을 가지는 procedure을 만드는 것을 허용한다.
추가적으로, user는 DBMS_SQL package들을 사용하여, 어떠한 data manipulation language(DML) 또는 data definition language(DDL) statement들도 parse할 수 있다. 이것은 PL/SQL을 사용하여 직
접 data definition language statement를 parse할 수 없는 문제를 해결하는데 도움이 된다. 예를 들면, DBMS_SQL package가 제공하는 PARSE procedure를 사용함으로써 stored procedure내에서 DROP TABLE statement를 기동하는 것을 선택할 수 있다.
[다음은 간단한 PL/SQL Block을 이용한 함수작성의 예제임]
FUNCTION authority_chk(uid IN VARCHAR2,compare_string IN VARCHAR2) RETURN VARCHAR2 IS
p_count NUMBER(1);
p_program_id VARCHAR2(20);
p_authority VARCHAR2(1);
BEGIN
/****************************/
/* 단계1 */
/* Full 권한이 있는지 파악 */
/***************************/
SELECT count(*)
INTO p_count
FROM pass1
WHERE emp_id = uid
AND authority = 'Y';
IF p_count > 0 THEN
return 'YES';
END IF;
/***************************************************************/
/* 단계2 */
/* 단위업무에 대한 전체권한이 있는지 파악 */
/***************************************************************/
SELECT count(*)
INTO p_count
FROM pass2
WHERE emp_id = uid
AND authority = 'Y'
AND unit_gbn = ( SELECT unit_gbn
FROM prgm
WHERE program_nm = compare_string);
IF p_count > 0 THEN
return 'YES';
END IF;
/********************************************/
/* 단계3 */
/* 윈도우단위에 대한 전체권한이 있는지 파악 */
/********************************************/
SELECT count(*)
INTO p_count
FROM pass3
WHERE emp_id = uid
AND program_id = ( SELECT program_id
FROM prgm
WHERE program_nm = compare_string)
AND authority = 'Y';
IF p_count > 0 THEN
RETURN 'YES';
END IF;
RETURN 'NO';
EXCEPTION
WHEN No_DATA_FOUND //SELECT .. INTO문에서 SELECT한 자료가 없을때의 처리
RETURN 'No_Data';
WHEN OTHERS THEN //정의하지 않은 기타예외인 경우의 처리
RETURN 'NO';
END;
[Online Redo Log]Online Redo Log란(1)...
======================
OnLine Redo Log
======================
1. Online Redo Log 계획
- Oracle의 모든 Instance에는 Online Redo Log가 있다. 여기에는 DB에서 처리된 모든 Commit된 기록하는 두개 이상의 Log File로 구성된다.(한쪽이 Archive되는 동안 다른 쪽을 사용하기 위해서는 최소한 2개는 되어야 되지 않을까?) BackGround Process인 LGWR는 Transaction 이 Commit될때 마다 SGA의 Redo Log Buffer 에 임시로 저장된 해당 Redo Log 항목을 Online Redo Log File에 기록한다.
- Redo Log File은 순환씩으로 작동하며, 즉 두개의 File로 Onlibe Redo Log가 구성된다면 첫번째 File을 채운다음 두번째 File을 채우고 두번째가 꽉차면 다시 첫번째에 채우는 방식으로 진행한다. 파일이 채워질 때 마다 Log Sequence번호가 할당된다.
2. Online Redo Log의 다중화
- Online Redo Log는 다중화된 Online Redo Log File Group으로 구성되어야 한다. 또한 단일 디스크 고장으로 LGWR 및 DB Instance가 실패하지 않도록 동일 Group의 Member를 여러 디스크에 나누어 저장해야 한다.
- Redo Log를 Archive할 경우 로그멤버를 여러 디스크에 분산시켜 LGWR와 ARCH간의 경합을 제거한다. 예를들어 두개의 이중 온라인 리두로그 멤버 그룹이 있다면 서로 다른 디스크에 각각의 맴버를 저장하고 Archive를 다섯번째 Disk로 설정한다. 이렇게 하면 경합을 줄일수 있다. 또한 DataFile과 Redo Log의 경합을 줄이려면 DataFile과 Redo Log File은 서로 다른 디스크에 있어야 한다.
- LGWR가 최소한 그룹의 한멤버를 성공적으로 기록할수 있다면 (로그스위치또는 그룹에 쓰기가 진행중) 그룹에서 Access할 수 있는 멤버에 대한 기록작업은 정상적으로 진행할 수 있다.
- 그룹이 Archive되지않아 LGWR가 로그스위치에서 다은 그룹을 Access할 수 없는 경우 그룹을 사용할수 있을때까지 DB의 작업은 중단된다.
- LGWR가 쓰려고 할 때 그룹의 모든 멤버에 대해 Access할수 없을때 오류를 돌려주며 DB Instance는 즉시 종료된다. 이러한 경우 LGWR추적파일과 Alert File에 오류 메시지를기록한다.
3. 적정수의 Online Redo Log File
- 구성이 만족스러운지 결정하는 쉬운 방법은 LGWR 추적파일과 DataBase Alert File의 내용을 검사하는 것이다. Check Point가 완료되지 않았거나 그룹이 Archive 되지않아 자주 기다려야 한다는 메시지가 자주 나오면 그룹을 추가래야 한다.
- MaxLogFiles Parameter는 DataBase당 온라인 리두 로그 파일 그룹의 최대수를 나타낸다. 지정하지 않는다면 Oracle은 OS에 의존한다.
- Log_Files Parameter는 현재 인스턴스의 지속기간 동안 리두로그 파일 그룹의 최대수를 일시적으로 감소 시킬수 있으나 Log_Files가 MaxLogFiles를 무효화하여 한계를 증가 시킬 수는 없다.
- MaxLogMembers Parameter응 그룹당 최대 Menber수를 결정한다.
4. 리두로그 그룹 생성
- alter database add logfile (‘log1c’,’log2c’) size 500k;
새로그멤버의 경로명을 포함하지 않으면 DB Server의 기본 Directory에 생성된다. 또 기존 운영체제의 파일을 재사용 하려면 파일의 크기를 지정하지 않아도 된다.
- alter database add logfile group 10 (‘log1c’,’log2c’) size 500k;
또한 Group Option과 Group 번호를 지정할수도 있다. 그룹번호는 1과 maxlogfiles 사이 여야 하며 그룹번호를 건너뛰면 안된다.(즉 10,20,30 등은 안됨), 그렇지 앟으면 DB의 제어파일의 불필요한 저장 영역이 낭비된다.
5. 리두로그 멤버 추가
- alter database add logfile member ‘log2b’ to group 2; 파일명은 지정해야 하지만 크기는 지정안해도 된다. 기존의 멤버의 크기에 의해 결정된다. 다음과 같은 경우도 사용 가능하다.
Alter database add logfile MEMBER ‘log2c’ to (‘log2a’,’log2b’);
즉 to다음에 다른 맴버를 지정할수도 있다.
6. 리두로그 파일의 이름변경 및 위치 재지정
a. DB Back한다.
b. Online Redo LogFile을 새위치로 복사
c. Alter database의 rename구를 이용하여 이름을 변경한다.
Alter database rename file ‘/usr2/log1a’ to ‘/usr3/log1a’
d. DB재 Open
e. 제어파일을 BackUp한다.
7. 리두로그 그룹삭제
- 그룹의 멤버수에 관계없이 인스턴스는 적어도 2개의 리두로그 파일 그룹을 필요로 한다.활성 그룹이 아닌경우에만 리두로그 그룹을 삭제할수 있다.
- 삭제하기전에 리두로그 그룹이 아카이브 되었는지 확인한다.(archive log명령과 list 매개변수를 사용)
svrmgrl>archive log list;
- 리두로그 그룹3을 삭제하는 예제
alter database drop logfile group 3;
8. 리두로그 멤버의 삭제
- 온라인 리두로그 파일을 삭제하여 다중화된 Online Redo Log가 일시적으로 비대칭이 될수도 있다. 예를들어 이중화된 온라인 리두로그 파일 그룹을 사용할 때 다른 모든 그룹이 각각 2개의 멤버를 가지고 있는 경우에도 그룹의 한멤버를 삭제할수 있다. 그러나 모든 그룹이 최소한 드멤버를 가지도록 즉시 수정하여 온라인 리두로그에 대해 가능한 단일 실패지점을 제거해야 한다.
- 그룹의 멤버수에 관계없이 인스턴스는 적어도 2개의 리두로그 파일 그룹을 필요로 한다.
- 삭제할 멤버가 그룹의 마지막 멤버라면 다른 멤버가 유효하게 될때까지 지우면 안된다. 리두로그 파일의 상태를 보려면 v$logfile이라는 View를 살피면 된다.
- 활성 그룹의 일부가 아닌경우에만 온라인 리두멤버를 삭제할수 있다. 활성그룹의 멤버를 삭제하려면 먼저 Log Switch를 강제로 발생시킨다.
- 멤버를 삭제하기전에 속한 그룹이 Archive 되었는지 확인한다.(archive log list;)
- 예]alter database drop logfile member ‘log3c’;
[Online Redo Log]CheckPoint와 LogSwitching...
============================
CheckPoint와 LogSwitching
============================
1 체크포인트와 Log Switch
- 체크포인트는 DBWR가 SGA에 있는 모든 수정된 DataBase Buffer의 내용을 해당 DataFile에 기록하는것이며 로그스위치는 LGWR가 한 온라인 리두로그에 쓰는 것을 중지하고 다른 그룹에 쓰기 시작하는 Event 이다. Oracle은 LogSwitch가 발생하고 LGWR이 쓰기를 시작할때마다 각 Online Redo Log File에 log Sequence Number를 할당한다. 그 온러안 리두로그가 아카이브되면 Archive 된 파일은 그번호를 유지한다.
- 기본적으로 Oracle Instance는 각 Log Switch에서 Check Point를 취하며, 기본적으로 로 그스위치는 현재 온라인 리두로그 그룹이 꽉차면 자동으로 발생한다.
- DataBase CheckPoint간격설정
Log_CheckPoint_Interval : DataBase의 체크포인트가 로그스위치에서만 발생하도록하려면 값을 Online Redo Log File의 Size보다 크게 설정한다. 두개의 로그스위치사이에서Check Point가 발생하게 하려면 Parameter값을 redo log file size보다 적게 준다. Parameter 값은 Oracle Block이 아닌 OS Block수 임. 따라서 운영체제의 블록의 크기를 바이트 단위로 알아야 한다.
예를들면]
a. DataBase Instance의 전체 redo log file크기는 512k
b. OS Block Size는 512 Byte
c. CheclPoint는 리두로그파일의 반이 찼을 때 발생해야 한다면
512k/redo log file / 512byte/os block=약 1000 block/ redo log file
즉 Log_CheckPoint_Interval = 500
- Log_CheckPoint_TimeOut은 체크포인트가 로그스위치에서만 발생하도록 하려면 0, 추가 체크포인트가 두개의 로그스위치 사이에서 발생하도록 하려면 값을 Online Redo Log File 을 채우는 시간보다 짧은 간격으로 설정해야 한다.
- Log Switch를 강제로 시행하는 방법
a.로그스위치를 강제로 시행하면 현재 활성그룹을 비활성화 하고, 로그유지 관리 작업에 사용가능하도록 할수 있다. 예를들어 현재 활성그룹을 삭제하려 해도 비활성화 될 때 까지는 삭제할수 없다.
b.alter system switch log file;이라는 명령으로 시행
- 체크포인트 강제시행
a.alter system checkpoint;라는 명령을 사용하여 시행한다.
b.체크포인트대센 고속체크포인트를 사용하여 DBWR은 입출력당 더많은 수정된 DB Buffer의 내용을 디스크에 기록할수 있다.
2 체크포인트 발생시기
- Log Switch가 일어날때 마다
- Log_CheckPoint_Interval,Log_CheckPoint_TimeOut Parameter의 값에의해 조정
- Online TableSpace의 백업이 시작될 때 그 TableSapce를 구성한는 DataFile에 대해
- 관리자가 TableSpace를 OffLine으로 설정할 경우 연관된 TableSpace의 온라인 DataFile만 CheckPoint발생한다.
- Instance종료(normal, immediate)시
- 강제로 실행(alter system checkpoint;)
3.체크포인트 처리기법
- 체크포인트가 발생할 때 체크포인트 백그라운드 CKPT는 온라인 Redo Log에 기록할 다음 위치를 기억한다음 DBWR에게 SGA에 있는 수정된 DataBase Buffer를 디스크의 DataFile에 쓰라는 신호를 보낸다. 그런다음 CKPT는 최근의 체크포인트를 반영하도록 모든 제어파일과 데이터 파일헤더를 갱신한다.
- 체크포인트가 발생하지 않으면 DBWR는 새 데이터를 넣을 빈 Buffer를 얻기위해 LRU DataBase Buffer를 디스크에 기록한다. 그러나 체크포인트가 진행되면 DBWR는 체크포인트와 진행되는 DB작업에 대한 Data를 데이터 파일에 기록한다. 또한 DBWR는 CheckPoint 작업을 위해 수정된 DataBase Buffer 수를 기록하고 필요할 때 마다 LRU Buffer 를 기록하며 체크포인트에 대한 Dirty Buffer에 기록하는 식으로 체크포인트가 완료될때까지 작업을 계속한다.
4.Redo Log File의 블록점검
- 체크섬을 사용하여 리두로그 파일의 블록을 점검하도록 구성이 가능하다.
- Log_Block_CheckSum을 TRUE로 설정할 경우 리두로그 블록 점검이 활성화됨. Default 는 False- Archive를 시도하는 동안 Redo Log Block에서 훼손 부분을 발견하면 Oracle은 그룹의 다른 Member에서 일기를 시도하며 모든 Member가 훼손되면 Archive가 불가능함
- 위의 경우와 같이 Redo Log중 훼손부분이 있다면 이를 지워 Archive가 가능하게 해야 할것이다. Alter database clear unarchived logfile group 3;의 명령으로 훼손된 부분을 지울수 있다.(주의:Archive되지않은 Redo Log를 지웠다면 DB를 BAckUp해야 한다.)
위에서 unarchived라는 Option을 사용한 이유는 Archive되지 않은 Redo Log이기 때문이다.
5.v$log, v$logfile, v$thread등의 View를 이용하여 온라인 리두로그에 관한 정보를 볼수 있다.(v$thread는 병렬서버용)
|
