Highload++ 시베리아 2019의 뒤를 이어 - Oracle의 8가지 작업

Привет!

24월 25일부터 2019일까지 노보시비르스크에서 Highload++ 시베리아 XNUMX 컨퍼런스가 열렸습니다. 보고서 “오라클 컨테이너 데이터베이스(CDB/PDB)와 소프트웨어 개발을 위한 실제 활용”은 잠시 후에 텍스트 버전을 게시할 예정입니다. 멋졌어, 고마워 올렉부닌 조직을 위해, 그리고 온 모든 사람을 위해.

이번 포스팅에서는 오라클 지식을 테스트해 보실 수 있도록 저희 부스에서 겪었던 문제들을 공유해드리고자 합니다. 컷 아래에는 8개의 문제, 답변 옵션 및 설명이 있습니다.

다음 스크립트를 실행한 결과로 표시되는 최대 시퀀스 값은 무엇입니까?

create sequence s start with 1;
 
select s.currval, s.nextval, s.currval, s.nextval, s.currval
from dual
connect by level <= 5;

• 1
• 5
• 10
• 25
• 아니요, 오류가 발생합니다.

답변Oracle 문서에 따르면(8.1.6에서 인용):
단일 SQL 문 내에서 Oracle은 행당 한 번만 시퀀스를 증가시킵니다. 명령문에 시퀀스에 대한 NEXTVAL에 대한 참조가 두 개 이상 포함되어 있으면 Oracle은 시퀀스를 한 번 증가시키고 모든 NEXTVAL 발생에 대해 동일한 값을 반환합니다. 명령문에 CURRVAL 및 NEXTVAL 모두에 대한 참조가 포함된 경우 Oracle은 명령문 내 순서에 관계없이 시퀀스를 증가시키고 CURRVAL 및 NEXTVAL 모두에 대해 동일한 값을 반환합니다.

따라서, 최대값은 줄 수, 즉 5에 해당합니다..

다음 스크립트를 실행하면 테이블에 몇 개의 행이 표시됩니까?

create table t(i integer check (i < 5));
 
create procedure p(p_from integer, p_to integer) as
begin
    for i in p_from .. p_to loop
        insert into t values (i);
    end loop;
end;
/
 
exec p(1, 3);
exec p(4, 6);
exec p(7, 9);

• 0
• 3
• 4
• 5
• 6
• 9

답변Oracle 문서에 따르면(11.2에서 인용):

SQL 문을 실행하기 전에 Oracle은 암시적 저장점(사용자가 사용할 수 없음)을 표시합니다. 그런 다음 문이 실패하면 Oracle은 이를 자동으로 롤백하고 해당 오류 코드를 SQLCA의 SQLCODE에 반환합니다. 예를 들어 INSERT 문이 고유 인덱스에 중복된 값을 삽입하려고 시도하여 오류가 발생하면 해당 문이 롤백됩니다.

클라이언트에서 HP에 전화하는 것도 단일 명령문으로 간주되고 처리됩니다. 따라서 세 개의 레코드를 삽입하여 첫 번째 HP 호출이 성공적으로 완료되었습니다. 두 번째 HP 호출은 오류로 종료되고 삽입한 네 번째 레코드를 롤백합니다. 세 번째 호출이 실패합니다. 테이블에 세 개의 레코드가 있습니다..

다음 스크립트를 실행하면 테이블에 몇 개의 행이 표시됩니까?

create table t(i integer, constraint i_ch check (i < 3));
 
begin
    insert into t values (1);
    insert into t values (null);
    insert into t values (2);
    insert into t values (null);
    insert into t values (3);
    insert into t values (null);
    insert into t values (4);
    insert into t values (null);
    insert into t values (5);
exception
    when others then
        dbms_output.put_line('Oops!');
end;
/

• 1
• 2
• 3
• 4
• 5
• 6
• 7

답변Oracle 문서에 따르면(11.2에서 인용):

검사 제약 조건을 사용하면 테이블의 각 행이 충족해야 하는 조건을 지정할 수 있습니다. 제약 조건을 충족하려면 테이블의 각 행에서 조건을 TRUE 또는 알 수 없음(널로 인해)으로 설정해야 합니다. Oracle이 특정 행에 대한 검사 제약 조건을 평가할 때 조건의 모든 열 이름은 해당 행의 열 값을 참조합니다.

따라서 null 값은 검사를 통과하고 익명 블록은 값 3을 삽입하려고 시도할 때까지 성공적으로 실행됩니다. 그 후 오류 처리 블록은 예외를 지우고 롤백이 발생하지 않습니다. 테이블에는 XNUMX개의 행이 남습니다 1, null, 2 및 다시 null 값을 사용합니다.

어떤 값 쌍이 블록에서 같은 양의 공간을 차지합니까?

create table t (
    a char(1 char),
    b char(10 char),
    c char(100 char),
    i number(4),
    j number(14),
    k number(24),
    x varchar2(1 char),
    y varchar2(10 char),
    z varchar2(100 char));
 
insert into t (a, b, i, j, x, y)
    values ('Y', 'Вася', 10, 10, 'Д', 'Вася');

• A와 X
• B와 Y
• C와 K
• C와 Z
• K와 Z
• 나와 J
• J와 X
• 모두 나열

답변다음은 Oracle에 다양한 유형의 데이터를 저장하는 방법에 대한 설명서(12.1.0.2)에서 발췌한 내용입니다.

CHAR 데이터 유형
CHAR 데이터 유형은 데이터베이스 문자 집합의 고정 길이 문자열을 지정합니다. 데이터베이스를 생성할 때 데이터베이스 문자 집합을 지정합니다. Oracle은 CHAR 열에 저장된 모든 값이 선택한 길이 의미론에서 크기로 지정된 길이를 갖도록 보장합니다. 열 길이보다 짧은 값을 삽입하면 Oracle은 해당 값을 열 길이까지 공백으로 채웁니다.

VARCHAR2 데이터 유형
VARCHAR2 데이터 유형은 데이터베이스 문자 세트에서 가변 길이 문자열을 지정합니다. 데이터베이스를 생성할 때 데이터베이스 문자 집합을 지정합니다. Oracle은 값이 열의 길이를 초과하지 않는 경우 공백 채우기 없이 사용자가 지정한 대로 정확히 VARCHAR2 열에 문자 값을 저장합니다.

NUMBER 데이터 유형
NUMBER 데이터 유형은 1.0 x 10-130에서 1.0 x 10126까지의 절대값을 갖는 양수 및 음수 고정 숫자뿐만 아니라 1.0도 저장합니다. 값이 다음보다 크거나 같은 산술 표현식을 지정하는 경우 10126 x 1이면 Oracle이 오류를 반환합니다. 각 NUMBER 값에는 22~XNUMX바이트가 필요합니다. 이를 고려하여 특정 숫자 데이터 값 NUMBER(p)에 대한 열 크기(바이트)는 다음 공식을 사용하여 계산할 수 있습니다. 여기서 p는 주어진 값의 정밀도입니다. ROUND((길이(p)+s)/2))+1 여기서 숫자가 양수이면 s는 1이고, 숫자가 음수이면 s는 XNUMX입니다.

또한 Null 값 저장에 대한 문서에서 발췌해 보겠습니다.

Null은 열에 값이 없음을 의미합니다. Null은 누락되었거나 알 수 없거나 적용할 수 없는 데이터를 나타냅니다. Null이 데이터 값이 있는 열 사이에 있으면 데이터베이스에 저장됩니다. 이러한 경우 열 길이(1)를 저장하려면 XNUMX바이트가 필요합니다. 행의 후행 Null에는 저장소가 필요하지 않습니다. 새 행 머리글은 이전 행의 나머지 열이 Null이라는 신호를 보내기 때문입니다. 예를 들어 테이블의 마지막 세 열이 Null인 경우 해당 열에 대한 데이터가 저장되지 않습니다.

이러한 데이터를 바탕으로 우리는 추론을 구축합니다. 데이터베이스가 AL32UTF8 인코딩을 사용한다고 가정합니다. 이 인코딩에서는 러시아어 문자가 2바이트를 차지합니다.

1) A 및 X, 필드 a 'Y'의 값은 1바이트를 사용하고, 필드 x 'D'의 값은 2바이트를 사용합니다.
2) B 및 Y, b의 'Vasya' 값은 최대 10자의 공백으로 채워지고 14바이트를 차지하며, d의 'Vasya'는 8바이트를 차지합니다.
3) C 및 K. 두 필드 모두 NULL 값을 가지며 그 뒤에 중요한 필드가 있으므로 1바이트를 차지합니다.
4) C 및 Z. 두 필드 모두 NULL 값을 가지지만 Z 필드는 테이블의 마지막이므로 공간(0바이트)을 차지하지 않습니다. 필드 C는 1바이트를 차지합니다.
5) K와 Z. 이전 사례와 유사합니다. K 필드의 값은 Z – 1에서 0바이트를 차지합니다.
6) I와 J. 문서에 따르면 두 값 모두 2바이트를 차지합니다. 문서에서 가져온 공식을 사용하여 길이를 계산합니다: round( (1 + 0)/2) +1 = 1 + 1 = 2.
7) J 및 X. J 필드의 값은 2바이트를 사용하고, X 필드의 값은 2바이트를 사용합니다.

전체적으로 올바른 옵션은 C 및 K, I 및 J, J 및 X입니다.

T_I 인덱스의 클러스터링 팩터는 대략 얼마입니까?

create table t (i integer);
 
insert into t select rownum from dual connect by level <= 10000;
 
create index t_i on t(i);

• 수십개 정도
• 수백명 정도
• 약 수천
• 수만개 정도

답변Oracle 문서에 따르면(12.1에서 인용):

B-트리 인덱스의 경우 인덱스 클러스터링 요소는 인덱스 값과 관련하여 행의 물리적 그룹화를 측정합니다.

인덱스 클러스터링 요소는 최적화 프로그램이 특정 쿼리에 대해 인덱스 스캔 또는 전체 테이블 스캔이 더 효율적인지 결정하는 데 도움이 됩니다. 낮은 클러스터링 인자는 효율적인 인덱스 스캔을 나타냅니다.

테이블의 블록 수에 가까운 클러스터링 요소는 인덱스 키를 기준으로 테이블 블록에서 행이 물리적으로 정렬되어 있음을 나타냅니다. 데이터베이스가 전체 테이블 스캔을 수행하는 경우 데이터베이스는 인덱스 키로 정렬된 디스크에 저장된 행을 검색하는 경향이 있습니다. 행 수에 가까운 클러스터링 요소는 행이 인덱스 키와 관련하여 데이터베이스 블록 전체에 무작위로 분산되어 있음을 나타냅니다. 데이터베이스가 전체 테이블 스캔을 수행하는 경우 데이터베이스는 이 인덱스 키를 기준으로 정렬된 순서로 행을 검색하지 않습니다.

이 경우 데이터가 이상적으로 정렬되므로 클러스터링 요소는 테이블에서 점유된 블록 수와 같거나 비슷합니다. 8KB의 표준 블록 크기의 경우 약 XNUMX개의 좁은 숫자 값이 하나의 블록에 들어갈 것으로 예상할 수 있으므로 블록 수와 결과적으로 클러스터링 요소는 다음과 같습니다. 수십개 정도.

표준 설정을 사용하는 일반 데이터베이스에서 다음 스크립트가 성공적으로 실행되는 N 값은 무엇입니까?

create table t (
    a varchar2(N char),
    b varchar2(N char),
    c varchar2(N char),
    d varchar2(N char));
 
create index t_i on t (a, b, c, d);

• 100
• 200
• 400
• 800
• 1600
• 3200
• 6400

답변Oracle 문서에 따르면(11.2에서 인용):

논리적 데이터베이스 제한

항목
한도 유형
한계값

색인
인덱싱된 열의 전체 크기
데이터베이스 블록 크기의 75% - 약간의 오버헤드

따라서 인덱스된 열의 전체 크기는 6Kb를 초과해서는 안 됩니다. 다음에 일어날 일은 선택한 기본 인코딩에 따라 다릅니다. AL32UTF8 인코딩의 경우 한 문자가 최대 4바이트를 차지할 수 있으므로 최악의 경우 약 6자가 1500KB에 들어갈 수 있습니다. 따라서 Oracle은 N = 400(최악의 경우 키 길이가 1600자 * 4바이트 + rowid 길이인 경우)에서 인덱스 생성을 허용하지 않습니다. N = 200(또는 그 이하)에서 인덱스 생성은 문제 없이 작동할 것입니다.

APPEND 힌트가 포함된 INSERT 연산자는 직접 모드에서 데이터를 로드하도록 설계되었습니다. 트리거가 걸린 테이블에 적용하면 어떻게 될까요?

• 데이터가 직접 모드로 로드되고 트리거가 예상대로 작동합니다.
• 데이터는 직접 모드로 로드되지만 트리거는 실행되지 않습니다.
• 데이터는 일반 모드로 로드되며 트리거는 정상적으로 작동합니다.
• 데이터는 일반 모드로 로드되지만 트리거는 실행되지 않습니다.
• 데이터가 로드되지 않고 오류가 기록됩니다.

답변기본적으로 이것은 논리의 문제에 가깝습니다. 정답을 찾기 위해 다음과 같은 추론 모델을 제안합니다.

1. 직접 모드에서의 삽입은 SQL 엔진을 우회하여 데이터 블록을 직접 형성하여 수행되므로 빠른 속도가 보장됩니다. 따라서 트리거 실행을 보장하는 것은 불가능하지는 않더라도 매우 어렵고 삽입 속도가 급격히 느려지므로 아무런 의미가 없습니다.
2. 트리거를 실행하지 못하면 테이블의 데이터가 동일한 경우 데이터베이스 전체(다른 테이블)의 상태는 이 데이터가 삽입된 모드에 따라 달라집니다. 이는 분명히 데이터 무결성을 파괴하므로 프로덕션 솔루션으로 적용할 수 없습니다.
3. 요청된 작업을 수행할 수 없는 경우 일반적으로 오류로 처리됩니다. 그러나 여기서 우리는 APPEND가 힌트이고 힌트의 일반적인 논리는 가능한 경우 고려되지만 그렇지 않은 경우 힌트를 고려하지 않고 연산자가 실행된다는 점을 기억해야 합니다.

그래서 예상되는 대답은 데이터는 일반(SQL) 모드로 로드되고 트리거가 실행됩니다.

Oracle 문서에 따르면(8.04에서 인용):

제한 사항을 위반하면 경고나 오류 메시지 없이 기존 삽입 경로를 사용하여 명령문이 순차적으로 실행됩니다. 예외는 트랜잭션에서 동일한 테이블에 두 번 이상 액세스하는 명령문에 대한 제한이며, 이로 인해 오류 메시지가 발생할 수 있습니다.
예를 들어 테이블에 트리거 또는 참조 무결성이 있는 경우 직접 로드 INSERT(직렬 또는 병렬)를 사용하려고 하면 APPEND 힌트는 물론 PARALLEL 힌트 또는 절(있는 경우)도 무시됩니다.

다음 스크립트가 실행되면 어떻게 될까요?

create table t(i integer not null primary key, j integer references t);
 
create trigger t_a_i after insert on t for each row
declare
    pragma autonomous_transaction;
begin
    insert into t values (:new.i + 1, :new.i);
    commit;
end;
/
 
insert into t values (1, null);

• 성공적인 실행
• 구문 오류로 인한 실패
• 오류: 자율 거래가 유효하지 않습니다.
• 최대 통화 중첩 초과 관련 오류
• 외래 키 위반 오류
• 잠금 관련 오류

답변테이블과 트리거는 아주 정확하게 생성되었으며 이 작업으로 인해 문제가 발생해서는 안 됩니다. 트리거의 자율 트랜잭션도 허용됩니다. 그렇지 않으면 로깅이 불가능합니다.

첫 번째 행을 삽입한 후 성공적으로 트리거가 실행되면 두 번째 행이 삽입되고, 트리거가 다시 실행되고, 세 번째 행이 삽입되는 식으로 호출의 최대 중첩을 초과하여 명령문이 실패할 때까지 계속됩니다. 그러나 또 다른 미묘한 점이 작용합니다. 트리거가 실행될 때 처음 삽입된 레코드에 대한 커밋이 아직 완료되지 않았습니다. 따라서 자율 트랜잭션에서 실행되는 트리거는 아직 커밋되지 않은 레코드에 대한 외래 키를 참조하는 행을 테이블에 삽입하려고 시도합니다. 이로 인해 대기(자율 트랜잭션은 데이터를 삽입할 수 있는지 확인하기 위해 기본 트랜잭션이 커밋될 때까지 기다림)가 발생하고 동시에 기본 트랜잭션은 트리거 후 자동 트랜잭션이 계속 작동할 때까지 기다립니다. 교착상태가 발생하고 그 결과 잠금 관련 사유로 자율 트랜잭션이 취소됩니다..

등록된 사용자만 설문 조사에 참여할 수 있습니다. 로그인제발

어려웠나요?

• 두 손가락처럼 나는 즉시 모든 것을 올바르게 결정했습니다.

• 사실은 아닙니다. 제가 몇 가지 질문에 틀렸어요.

• 절반은 맞췄습니다.

• 답을 두 번 추측했습니다!

• 댓글에 적어볼게요

14명의 사용자가 투표했습니다. 10명의 사용자가 기권했습니다.

출처 : habr.com