brunch

매거진 오라클 DB의 첫걸음

라이킷 10 댓글

You can make anything
by writing

C.S.Lewis

계정을 잊어버리셨나요?

by GTPlus Dec 15. 2023

SQL*Plus 바인드 변수, 명령문 추적

Oracle Database SQL*Plus 명령어 활용

안녕하세요. 지티플러스입니다.

이번 글에서는 바인드 변수 사용과 PEFCURSOR 바인드 변수 사용, 명령문 추적에 대해 다룹니다.

바인드 변수 사용

PL/SQL 서브 프로그램에서 사용하는 변수를 SQL*Plus에서 표시하거나 동일한 변수를 여러 서브 프로그램에서 사용하기 원한다고 가정합니다. PL/SQL 서브 프로그램에서 변수를 선언하면 SQL*Plus에서 해당 변수를 화면에 표시할 수 없습니다. SQL*Plus에서 변수에 액세스하려면 PL/SQL에서 바인드 변수를 사용합니다.

바인드 변수는 SQL*Plus에서 작성한 다음 PL/SQL에서 참조하는 변수입니다. SQL*Plus에서 바인드 변수를 작성하면 PL/SQL 서브 프로그램에서 선언된 변수를 사용하는 것처럼 사용할 수 있고 SQL*Plus에서 변수에 액세스할 수 있습니다. 반환 코드 저장이나 PL/SQL 서브 프로그램 디버깅 등에 바인드 변수를 사용할 수 있습니다.

바인드 변수는 SQL*Plus에 의해 인식되기 때문에 SQL*Plus에서 해당 값을 화면에 표시하거나 SQL*Plus에서 실행하는 다른 PL/SQL 서브 프로그램에서 참조할 수 있습니다.

◆ 바인드 변수 작성

SQL*Plus에서 VARIABLE 명령어를 사용하여 다음 예와 같이 바인드 변수를 작성할 수 있습니다.

VARIABLE ret_val NUMBER

이 명령어는 데이터 유형이 NUMBER인 ret_val이라는 바인드 변수를 작성합니다. (한 세션에서 작성된 모든 바인드 변수를 보려면 인수 없이 VARIABLE 명령어만 입력합니다.)

◆ 바인드 변수 참조

다음 예와 같이 바인드 변수 이름 앞에 콜론(:)을 붙이면 PL/SQL에서 참조할 수 있습니다.

:ret_val := 1;

이 명령어는 바인드 변수 ret_val에 값을 할당합니다.

◆ 바인드 변수 화면 표시

SQL*Plus에서 바인드 변수 값이 화면에 표시되게 하려면 다음 예와 같이 SQL*Plus PRINT 명령어를 사용합니다.

PRINT ret_val

이 명령어는 ret_val 바인드 변수를 화면에 표시합니다.

◆ 바인드 변수의 작성, 참조 및 화면 표시

데이터 유형이 NUMBER인 지역 바인드 변수 id를 선언하려면 다음을 입력합니다.

다음으로 작성한 바인드 변수에 "1" 값을 넣습니다.

id 바인드 변수 값 목록을 화면에 표시하려면 다음을 입력합니다.

◆ PEFCURSOR 바인드 변수 사용

SQL*Plus REFCURSOR 바인드 변수를 사용하면 PL/SQL에 포함된 SELECT 문의 결과를 SQL*Plus에서 인출하고 형식을 지정할 수 있습니다.

또한 REFCURSOR 바인드 변수는 내장 프로시저의 PL/SQL 커서 변수를 참조하는 데 사용될 수도 있습니다. 따라서 데이터베이스에 SELECT 문을 저장하고 SQL*Plus에서 SELECT 문을 참조할 수 있습니다.

REFCURSOR 바인드 변수는 내장 함수로부터 반환될 수도 있습니다.

주의: 내장 함수의 반환 값을 REFCURSOR 변수에 지정하려면 Oracle7 릴리스 7.3 이상이어야 합니다.

◆ REFCURSOR 바인드 변수의 작성, 참조 및 화면 표시

REFCURSOR 바인드 변수를 작성, 참조, 화면 표시하려면 먼저 REFCURSOR 데이터 유형의 지역 바인드 변수를 선언합니다.

다음으로 OPEN...FOR SELECT 문에서 바인드 변수를 사용하는 PL/SQL 블록을 입력합니다. 이 명령문은 커서 변수를 열고 질의를 실행합니다.

SQL*Plus dept_sel 바인드 변수를 커서 변수에 바인드 합니다.

SELECT 문의 결과는 SQL*Plus에서 다음과 같이 PRINT 명령어를 사용하여 화면에 표시할 수 있습니다.

PRINT 문은 또한 커서를 종료합니다. 결과를 재출력하려면 PL/SQL 블록을 다시 실행한 다음 PRINT 명령어를 사용해야 합니다.

◆ 내장 프로시저의 REFCURSOR 변수 사용

REFCURSOR 바인드 변수는 프로시저에 매개변수로 전달됩니다. 매개변수의 유형은 REEF CURSOR입니다. 먼저 유형을 정의해야 합니다.

다음으로 OPEN…FOR SELECT문을 포함한 내장 프로시저를 작성합니다.

SQL*Plus 바인드 변수를 매개변수로 사용하여 프로시저를 실행합니다.

바인드 변수를 출력합니다.

이 프로시저는 동일하거나 다른 REFCURSOR 바인드 변수를 사용하여 여러 번 실행할 수 있습니다.

◆ 내장 함수의 REFCURSOR 변수 사용

OPEN...FOR SELECT 문을 포함한 내장 함수를 작성합니다.

함수를 실행합니다.

바인드 변수를 출력합니다.

이 함수는 동일하거나 다른 REFCURSOR 바인드 변수를 사용하여 여러 번 실행할 수 있습니다.

◆ 명령문 추적

SQL 최적기에 의해 사용되는 실행 경로와 명령문 실행 통계에 대한 보고서가 자동으로 작성됩니다. 이 보고서는 SQL DML(즉, SELECT, DELETE, UPDATE 및 INSERT) 문이 성공적으로 실행된 후에 생성되며, 이러한 명령문의 성능을 모니터하고 조정하는 데 유용합니다.

◆ 보고서 제어

AUTOTRACE 시스템 변수를 설정하여 보고서를 제어할 수 있습니다.

SET AUTOTRACE OFF : 기본 값으로 AUTOTRACE 보고서가 생성되지 않습니다.

SET AUTOTRACE ON EXPLAIN : AUTOTRACE 보고서에 최적기 실행 경로만 기록하게 됩니다.

SET AUTOTRACE ON STATISTICS : AUTOTRACE 보고서에 SQL문 실행 통계만 기록됩니다.

SET AUTOTRACE ON : AUTOTRACE 보고서에 최적기 실행 경로와 SQL문 실행 통계가 모두 포함됩니다.

SET AUTOTRACE TRACEONLY : SET AUTOTRACE ON과 유사하지만 사용자 질의 결과가 출력되지 않습니다.

이 기능을 사용하려면 PLUSTRACE 롤을 부여받아야 하며 사용자 스키마에서 PLAN_TABLE 테이블을 작성해야 합니다.

◆ 실행 계획

실행 계획은 SQL 최적기의 질의 실행 경로를 보여줍니다. 두 테이블은 모두 전체 테이블 스캔에 의해 액세스 되고 정렬된 다음 병합됩니다.

실행 계획의 각 행에는 순차적인 행 번호가 붙어 있으며 상위 연산의 행 번호도 표시됩니다. 실행 계획은 다음 순서의 네 열로 구성됩니다.

ID_PLUS_EXP : 각 실행 단계의 행 번호를 보여줍니다.

PARENT_ID_PLUS_EXP : 각 단계와 상위 단계 사이의 관계를 보여줍니다. 대규모 보고서를 작성할 때 유용합니다.

PLAN_PLUS_EXP : 보고서의 각 단계를 보여줍니다.

OBJECT_NODE_PLUS_EXP : 사용된 데이터베이스 링크나 병렬 질의 서버를 보여줍니다.

열 형식은 COLUMN 명령어를 사용하여 변경할 수 있습니다. 예를 들어, PARENT_ID_PLUS_EXP 열이 화면에 표시되지 않게 하려면 다음을 입력합니다.

기본 형식은 사이트 프로파일(예를 들어, glogin.sql)에서 찾을 수 있습니다.

실행 계획 결과는 EXPLAIN PLAN 명령어를 사용하여 생성됩니다.

◆ 통계

통계는 명령문이 실행될 때 기록되며 명령문 실행에 필요한 시스템 자원을 알려줍니다.

통계에서 참조되는 클라이언트는 SQL*Plus입니다. SQL*Net은 Net8 설치 여부와 관계없이 SQL*Plus와 서버 간의 일반적인 프로세스 통신을 참조합니다. 통계 보고서의 기본 형식은 변경할 수 없습니다.

◆ 성능 통계 및 질의 실행 경로를 위한 명령문 추적

다음은 명령문이 버퍼에 들어 있는 경우입니다.

다음과 같이 입력하여 명령문이 자동으로 추적되도록 할 수 있습니다.

주의: 접속 서버의 버전과 구성에 따라 다른 내용이 출력될 수 있습니다.

◆ 질의 데이터를 표시하지 않은 명령문 추적

질의 데이터를 표시하지 않고 성능 통계 및 질의 실행 경로를 위한 명령문 추적의 명령문을 추적하려면 다음을 입력합니다.

이 옵션은 대규모 질의를 조정할 때 질의 보고서를 볼 필요가 없는 경우에 유용합니다.

◆ 병렬 및 분산 질의 추적

병렬 또는 분산 질의에 있는 명령문을 추적하는 경우, 비용 기준 최적기의 예측 행 수(기수)가 실행 계획에 표시됩니다. 일반적으로 각 노드의 비용, 기수, 바이트는 누적된 결과를 나타냅니다. 예를 들어, 조인 노드의 비용에는 조인 연산을 완료하는 비용뿐만 아니라 해당 조인에 포함된 관계에 액세스하는 전체 비용도 포함됩니다. 별표(*)로 표시된 행은 병렬 또는 원격 연산을 나타냅니다. 각 연산은 보고서의 두 번째 부분에서 설명됩니다.

이 보고서의 두 번째 부분은 다음과 같은 순서의 세 열로 구성됩니다.

ID_PLUS_EXP : 각 실행 단계의 행 번호를 보여줍니다.

OTHER_TAG_PLUS_EXP : OTHER_PLUS_EXP 열에서 SQL 문의 기능을 설명합니다.

OTHER_PLUS_EXP : 병렬 서버 또는 원격 데이터베이스에 대한 질의 텍스트를 보여줍니다.

열 형식은 COLUMN 명령어를 사용하여 변경할 수 있습니다. 기본 형식은 사이트 프로파일(예를 들어, glogin.sql)에서 찾을 수 있습니다.

주의: Oracle7 릴리스 7.3 이상에서는 이 보고서의 두 번째 항목을 볼 수 있습니다.