다른 계정으로 구문을 테스트하고 싶을 때 로그인을 다시 할 필요없이 실행 유저를 변경하여 테스트할 수 있다.
| [SQL Server] String or binary data would be truncated. (0) | 2019.02.11 |
|---|---|
| [SQL Server] 테이블 속성 변경 후 저장이 되지 않을 때 (0) | 2019.01.28 |
| [SQL Server] 데이터 파일, 로그 파일 경로 확인 (0) | 2019.01.28 |
| [SQL Server] SSIS Error - Warning: Null value is eliminated by an aggregate or other SET operation. (0) | 2019.01.28 |
| [SQL Server] SQL 서버 로그인 보기, DB 사용자 보기 (0) | 2019.01.28 |
데이터를 INSERT 시 아래와 같은 에러가 발생할 때가 있다.
에러 내용과 같이 "데이터가 잘렸을 것이다" 라는 것은 INSERT 할 데이터가 해당 컬럼보다 클때 발생한다.
컬럼 사이즈를 조절하거나 넣을 데이터 사이즈를 조정해서 넣어주면 해결이 된다.
| [SQL Server] 유저 전환하기 (0) | 2019.02.15 |
|---|---|
| [SQL Server] 테이블 속성 변경 후 저장이 되지 않을 때 (0) | 2019.01.28 |
| [SQL Server] 데이터 파일, 로그 파일 경로 확인 (0) | 2019.01.28 |
| [SQL Server] SSIS Error - Warning: Null value is eliminated by an aggregate or other SET operation. (0) | 2019.01.28 |
| [SQL Server] SQL 서버 로그인 보기, DB 사용자 보기 (0) | 2019.01.28 |
다음과 같이 테이블 속성을 변경하고 저장을 했을 때 삭제 후 다시 생성해야한다는 경고가 뜬다.
이 문제를 해결하기 위해서는 [도구]-[옵션]-[디자이너]-[테이블 및 데이터베이스 디자이너] 항목에서 "테이블을 다시 만들어야 하는 변경 내용 저장 안 함" 체크 해제한다.
| [SQL Server] 유저 전환하기 (0) | 2019.02.15 |
|---|---|
| [SQL Server] String or binary data would be truncated. (0) | 2019.02.11 |
| [SQL Server] 데이터 파일, 로그 파일 경로 확인 (0) | 2019.01.28 |
| [SQL Server] SSIS Error - Warning: Null value is eliminated by an aggregate or other SET operation. (0) | 2019.01.28 |
| [SQL Server] SQL 서버 로그인 보기, DB 사용자 보기 (0) | 2019.01.28 |
데이터 파일과 로그 파일 경로 확인은 GUI, 쿼리를 통해 둘 다 가능하다.
우선 GUI에서는 [속성]-[파일] 에서 경로 뿐만아니라 증가량, 파일 형식, 파일 그룹 등 여러가지 정보를 확인할 수 있다.
다음과 같은 쿼리로도 확인이 가능하다.
| [SQL Server] String or binary data would be truncated. (0) | 2019.02.11 |
|---|---|
| [SQL Server] 테이블 속성 변경 후 저장이 되지 않을 때 (0) | 2019.01.28 |
| [SQL Server] SSIS Error - Warning: Null value is eliminated by an aggregate or other SET operation. (0) | 2019.01.28 |
| [SQL Server] SQL 서버 로그인 보기, DB 사용자 보기 (0) | 2019.01.28 |
| [SQL Server] Service Broker 개요 (0) | 2019.01.28 |
SQL Server 2008 SSIS를 실행하다보면 다음과 같은 에러를 만날 수 있다.
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x00040EDA Description: "Warning: Null value is eliminated by an aggregate or other SET operation.".
실제로 SSMS 에서는 Warning 으로 그치지만 SSIS 에서는 에러로 인식하는 버그성 동작이고 이는 SQL Server 2012 에서 수정이 되었다.
해결 방법은 해당 구문에 다음 구문을 추가하면 해결할 수 있다.
SET ANSI_WARNINGS OFF;
출처: https://stackoverflow.com/questions/13157508/why-does-a-null-aggregate-warning-in-an-sql-query-causes-the-ssis-package-to-fai| [SQL Server] 테이블 속성 변경 후 저장이 되지 않을 때 (0) | 2019.01.28 |
|---|---|
| [SQL Server] 데이터 파일, 로그 파일 경로 확인 (0) | 2019.01.28 |
| [SQL Server] SQL 서버 로그인 보기, DB 사용자 보기 (0) | 2019.01.28 |
| [SQL Server] Service Broker 개요 (0) | 2019.01.28 |
| [SQL Server] Error log 확인 (0) | 2019.01.28 |
Service Broker는 비동기 방식을 통해 서로 메시지를 보내고 받는 기능을 쉽게 구축할 수 있는 응용 프로그램이다. 응용 프로그램에서는 관련된 테스크 집합에 대한 이름인 '서비스'에 메시지를 보내고 내부 테이블 보기인 '큐'로부터 메시지를 받는다.
Service Broker는 응용 프로그램이 모든 메시지를 한 번만 받도록 한다. 이때 메시지는 전송된 순서대로 받는다.
일반적인 대화 방식은 다음과 같다.
Service Broker는 각 대화에 대한 우선 순위를 1(낮음) - 10(높음) 범위에서 설정할 수 있도록 지원한다.
Service Broker 에서 메시지 전달은 트랜잭션 및 비동기 방식으로 수행된다. 트랜잭션이 롤벡될 경우 해당 트랜잭션 내의 모든 Service Broker 작업도 롤백된다.
큐는 데이터베이스 응용 프로그램과 관련하여 두 가지 이점이 있다.
SQL Server와의 통합을 통해 외부 DTC(Distributed Transaction Coordinator)에 대한 추가적인 오버헤드 및 복잡성 없이 틀내잭션 메시지를 사용할 수 있다. 응용 프로그램에서는 트랜잭션을 커밋할 때까지 어떤 작업도 이루어지지 않는다.
데이터, 메시지 및 응용 프로그램 논리가 모두 데이터베이스에 있으면 응용 프로그램의 관리가 쉬워진다.
공통 개발 환경도 데이터베이스 통합의 장점이다. Service Broker 서비스를 구현하는 프로시저는 Transact-SQL 또는 CLR (공용 언어 런타임) 언어 중 하나로 작성할 수 있다.
Service Broker는 메시지 순서, 고유한 배달 및 대화 식별을 자동으로 처리한다. 두 Service Broker의 끝점 사이에 대화가 설정되고 나면 응용 프로그램에서는 각 메시지를 보낸 순서대로 한 번씩만 메시지를 받는다. 사용자 응용 프로그램에서는 추가 코드 없이 순서대로 정확히 한 번씩만 메시지를 처리할 수 있다. 그리고 모든 메시지에 자동으로 식별자를 포함한다. 응용 프로그램에서는 항상 특성 메시지가 속해 있는 대화를 알려 준다.
응용 프로그램에서는 큐에 메시지를 보낸 다음 계속하여 응용 프로그램 처리를 진행하되 Service Broker를 통해 메시지가 대상에 도착했는지 확인할 수 있다. 이처럼 연결이 느슨하면 유연한 일정 예약이 가능하다. 시작자는 여러 메시지를 내보낼 수 있고 여러 대상 서비스에서는 이를 병렬로 처리할 수 있다.
또한 큐 작업을 통해 시스템에서는 더욱 공평하게 처리를 배포할 수 있으므로 서버에 필요한 최대 용량도 줄어든다. 이러면 전반적인 처리량과 성능이 향상된다.
대상을 즉시 사용할 수 없는 경우 메시지는 보내는 데이터베이스의 전송 큐에 남는다. Service Broker에서는 메시지가 전송될 때까지 또는 대화의 수명이 만료될 때까지 메시지를 다시 시도하여 하나의 서비스가 대화 중 특정 시점에 잠시 사용 불가능하게 되더라도 두 서비스 사이의 안정적인 대화가 계속 진행될 수 있도록 한다.
Service Broker에서는 큐 내의 메시지 읽기 작업을 병렬로 처리 시 대화 그룹 잠금을 통해 이러한 문제가 발생하지 않도록 한다.
활성화를 사용하면 응용 프로그램이 큐에 도착하는 메시지 볼륨에 맞게 크기를 동적으로 조정할 수 있다.
Service Broker는 큐의 작업을 모니터링하여 사용 가능한 메시지가 있는 모든 대화에 대해 응용 프로그램이 메시지를 받는지 여부를 확인합니다. Service Broker 활성화는 큐 판독기가 수행할 작업이 있을 때 새 큐 판독기를 시작한다. 큐 판독기가 수행할 작업이 있는 시기를 확인하기 위해 Service Broker에서는 큐의 작업을 모니터링한다. 큐 판독기의 수가 들어오는 트래픽에 일치할 경우 큐는 정기적으로 큐가 비어 있는 상태 또는 큐의 모든 메시지가 다른 큐 판독기에서 처리 중인 대화에 속하는 상태에 이르게 된다. 큐가 일정 기간 동안 이러한 상태에 이르지 않으면 Service Broker는 다른 응용 프로그램 인스턴스를 활성화한다.
응용 프로그램은 데이터베이스 내부에서 실행되는 프로그램과 데이터베이스 외부에서 실행되는 프로그램에 대해 서로 다른 활성화 방법을 사용한다. 데이터베이스 내부의 프로그램에 대해서 Service Broker는 큐에서 지정한 저장 프로시저의 다른 사본을 시작한다. 데이터베이스 외부의 프로그램에 대해서 Service Broker는 활성화 이벤트를 생성한다. 이 이벤트를 모니터링하여 다른 큐 판독기가 필요한 시기를 확인할 수 있다.
Service Broker는 활성화를 통해 시작된 프로그램을 중지하지 않는다. 대신 활성화된 응용 프로그램은 메시지가 도착하지 않은 일정 기간 이후 자동으로 종료되도록 작성된다. 이러한 방법으로 설계된 응용 프로그램을 사용함으로써 서비스에 대한 트래픽 변경으로 인해 여러 응용 프로그램 인스턴스가 동적으로 증가하고 감소할 수 있다. 또한 시스템이 종료되거나 재부팅되면 시스템을 다시 시작할 때 응용프로그램에서 자동으로 큐의 메시지를 읽기 시작한다.
OLTP 시스템과 같이 트리거를 사용하는 응용 프로그램은 Service Broker를 유용하게 활용할 수 있는 경우가 많다. 트리거는 Service Broker 서비스에서 요청이 작동하는 메시지를 대기시킨다. 트리거는 요청된 작업을 실제로 수행하지 않는다. 대신 수행할 작업에 대한 정보가 포함된 메시지를 만들고 해당 작업을 수행하는 서비스에 이 메시지를 보낸다. 그런 다음 트리거는 반환된다.
안정적인 쿼리 처리가 필요한 응용 프로그램은 Service Broker 서비스에 메시지를 보내 쿼리를 전송할 수 있다. 서비스를 구현하는 응용 프로그램은 메시지를 읽고 쿼리를 실행하며 결과를 반환한다. 이 작업은 모두 같은 트랜잭션이 커밋되기 전에 오류가 발생하면 전체 트랜잭션이 롤백되고 메시지가 큐로 반환된다.
큰 원본 집합에서 데이터를 수집하는 응용 프로그램은 Service Broker를 통해 안정적으로 데이터를 수집할 수 있다.
여러 SQL Server 데이터베이스에 액세스하는 대형 응용 프로그램은 Service Broker를 유용하게 활용할 수 있다.
여러 데이터베이스의 정보를 동시에 사용하거나 표시해야 하는 응용 프로그램은 Service Broker를 활용할 수 있다.
대량 일괄 처리를 수행해야 하는 응용 프로그램은 Service Broker에 제공되는 큐 및 병렬 처리 기능을 통해 많은 작업을 신속하고 효율적으로 처리할 수 있다. 응용 프로그램은 처리할 데이터를 Service Broker 큐에 저장한다. 프로그램은 정기적으로 큐에서 데이터를 읽고 해당 데이터를 처리한다. 응용 프로그램은 Service Broker에 제공되는 안정적인 메시징 기능을 통해 요청이 시작된 컴퓨터 이외의 다른 컴퓨터에서 일괄 처리를 수행할 수 있다.
출처: SQL Server Books Online
SQL Server에서 Error log는 SSMS 에서는 [관리]-[Sql Server 로그] 에서 확인이 가능하고 또한, 쿼리로도 확인이 가능하다.
| [SQL Server] SQL 서버 로그인 보기, DB 사용자 보기 (0) | 2019.01.28 |
|---|---|
| [SQL Server] Service Broker 개요 (0) | 2019.01.28 |
| [SQL Server][MSSQL] Cannot use TEXTIMAGE_ON when a table has no text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml or large CLR type columns. (0) | 2019.01.28 |
| [SQL Server] SSIS CSV 파일 날짜로 저장하기 (0) | 2018.09.18 |
| SA, 모든 유저 활성화 및 패스워드 변경 쿼리 (0) | 2017.01.31 |
테이블 생성 시 해당 에러문구가 보이면 TEXTIMAGE_ON 절 삭제
Cannot use TEXTIMAGE_ON when a table has no text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml or large CLR type columns.
CREATE TABLE [dbo].[TABLENAME] (
...
...
) ON [DATA] TEXTIMAGE_ON [DATA] <-----삭제
...
..
| [SQL Server] Service Broker 개요 (0) | 2019.01.28 |
|---|---|
| [SQL Server] Error log 확인 (0) | 2019.01.28 |
| [SQL Server] SSIS CSV 파일 날짜로 저장하기 (0) | 2018.09.18 |
| SA, 모든 유저 활성화 및 패스워드 변경 쿼리 (0) | 2017.01.31 |
| SQLCMD를 이용해 Query 실행 및 결과 출력하기 (0) | 2017.01.12 |
출처: http://scidb.tistory.com/entry/Sort-부하를-좌우하는-두-가지-원리
▶적절한 인덱스가 없을 때 Sort의 부하를 줄이는 방법
▶Pagination에서 Sort의 부하 줄이기
▶주의사항
Order by 절에 의한 Sort의 부하는 성능에 치명적이다. Block I/O의 최소화는 분명 튜닝의 핵심이다. 하지만, Block I/O를 최소화 해도 Sort의 부하가 심하다면 결코 만족스런 성능을 내지 못한다. 특히 “페이징 처리용 SQL에는 Sort를 대신할 수 있는 인덱스가 있어야 성능을 확보할 수 있다”고 많은 튜너들이 주장한다. 맞는 말이다. 그렇게만 된다면 Sort가 전혀 발생하지 않을 테니까. 하지만, 다음과 같은 어려움도 있다.
인덱스 최적화가 힘든 이유
첫 번째, 인덱스를 모든 조회화면의 기준에 맞게 만들려면 테이블마다 많은 수의 인덱스가 필요할 것이다. 두 번째, 운영중인 환경에서 인덱스를 생성 혹은 변경하기는 매우 어렵다. 따라서, 인덱스를 만들기 어렵다면, Sort의 부하를 최소화하는 다른 방법은 없는지를 고려해야 한다. 분명히 방법은 있다. 이 방법을 알지 못한다면 오직 인덱스에만 목숨을 거는 사람이 될 가능성이 높다. 오늘은 인덱스를 전혀 만들지 않은 상태에서 Sort의 부하를 최소화 하는 방법에 대해 알아볼 것이다.
단 한 가지 개념만 안다면, Order By에 의한 Sort의 부하를 이해한 것이다. Sort의 부하량은 면적에 비례한다는 것. 이 개념은 아래와 같이 표현할 수 있다. 참고로 ∝는 비례한다는 의미이다.
Sort의 부하량(PGA 사용량) ∝ 세로(결과 건수) X 가로(컬럼 Size 합계)
공식의 이해가 부족하다
주위의 지인들에게 위의 식을 질문한 결과 거의 모두가 세로에 대해서는 정확히 이해하고 있었다. 즉, Sort할 건수가 많아지면 Sort의 부하가 증가 한다는 것이다. 이에 반해서 가로에 대해서는 정확한 이해를 하는 사람이 드물었다. 대부분, Order By절에 의해 Sort의 부하가 발생하므로 Order By절에 존재하는 컬럼 Size의 합계가 가로라고 생각하는 것이다. 다시 말해, Order By절의 컬럼이 세 개라면, 세 컬럼의 Size를 합친 것이 가로라는 것이다. 과연 그럴까?
위의 주장을 검증하기 위해 테이블을 하나 만들고, 추가적으로 컬럼을 3개 만들자.
CREATE TABLE SALES_T NOLOGGING AS SELECT * FROM SALES;
ALTER TABLE SALES_T ADD (char_100 CHAR(100) DEFAULT 'a' NOT NULL );
ALTER TABLE SALES_T ADD (char_1000 CHAR(1000) DEFAULT 'a' NOT NULL );
ALTER TABLE SALES_T ADD (char_2000 CHAR(2000) DEFAULT 'a' NOT NULL );
추가된 컬럼은 모두 Char Type이며 Default 값이 ‘a’ 이다. Char Type이므로 Default값인 ‘a’가 들어오는 경우 컬럼 size는 각각 100, 1000, 2000 바이트씩 채워진다. 이제 이 컬럼들을 이용하여 SQL을 각각 실행해보자. 100 byte, 1000 byte, 2000 byte 컬럼으로 각각 Sort하여 Sort의 부하가 어떻게 달라지는지 알아보자.
CREATE TABLE SORT_100 NOLOGGING AS
SELECT /*+ full(p) full(c) */
s.prod_id, p.prod_name, s.cust_id, c.cust_first_name,
c.cust_last_name, s.time_id, s.channel_id, s.char_100
FROM sales_t s, customers c, products p
WHERE s.cust_id = c.cust_id
AND s.prod_id = p.prod_id
AND s.prod_id = 30
ORDER BY s.char_100 ;
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 1 | 0 |00:00:01.41 | 7323 | |
| 1 | LOAD AS SELECT | | 1 | 0 |00:00:01.41 | 7323 | 521K (0)|
| 2 | SORT ORDER BY | | 1 | 29282 |00:00:01.23 | 5915 | 4708K (0)|
|* 3 | HASH JOIN | | 1 | 29282 |00:00:01.15 | 5915 | 3471K (0)|
| 4 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 |00:00:00.21 | 1468 | |
| 5 | NESTED LOOPS | | 1 | 29282 |00:00:00.66 | 4447 | |
|* 6 | TABLE ACCESS FULL | PRODUCTS | 1 | 1 |00:00:00.01 | 7 | |
|* 7 | TABLE ACCESS FULL | SALES_T | 1 | 29282 |00:00:00.59 | 4440 | |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("S"."CUST_ID"="C"."CUST_ID")
6 - filter("P"."PROD_ID"=30)
7 - filter("S"."PROD_ID"=30)
100 byte컬럼으로 Sort하니 PGA를 4.7MB 사용하였다. 이제 100 byte보다 10배나 큰 1000 byte 컬럼으로 Sort 하여 PGA 사용량을 비교해보자.
CREATE TABLE SORT_1000 NOLOGGING AS
SELECT /*+ full(p) full(c) */
s.prod_id, p.prod_name, s.cust_id, c.cust_first_name,
c.cust_last_name, s.time_id, s.channel_id, s.char_1000
FROM sales_t s, customers c, products p
WHERE s.cust_id = c.cust_id
AND s.prod_id = p.prod_id
AND s.prod_id = 30
ORDER BY s.char_1000 ;
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 1 | 0 |00:00:02.17 | 13162 | |
| 1 | LOAD AS SELECT | | 1 | 0 |00:00:02.17 | 13162 | 521K (0)|
| 2 | SORT ORDER BY | | 1 | 29282 |00:00:01.75 | 5915 | 30M (0)|
|* 3 | HASH JOIN | | 1 | 29282 |00:00:01.56 | 5915 | 3486K (0)|
| 4 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 |00:00:00.22 | 1468 | |
| 5 | NESTED LOOPS | | 1 | 29282 |00:00:01.05 | 4447 | |
|* 6 | TABLE ACCESS FULL | PRODUCTS | 1 | 1 |00:00:00.02 | 7 | |
|* 7 | TABLE ACCESS FULL | SALES_T | 1 | 29282 |00:00:00.98 | 4440 | |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("S"."CUST_ID"="C"."CUST_ID")
6 - filter("P"."PROD_ID"=30)
7 - filter("S"."PROD_ID"=30)
Sort 컬럼의 size를 100에서 1000 byte로 늘리자 PGA 사용량도 4.7 MB에서 30 MB로 크게 늘었다. 내친김에 Order By절을 2000 byte 컬럼으로 바꿔서 PGA 사용량이 얼마나 늘어나는지 테스트 해보자.
CREATE TABLE SORT_2000 NOLOGGING AS
SELECT /*+ full(p) full(c) */
s.prod_id, p.prod_name, s.cust_id, c.cust_first_name,
c.cust_last_name, s.time_id, s.channel_id, s.char_2000
FROM sales_t s, customers c, products p
WHERE s.cust_id = c.cust_id
AND s.prod_id = p.prod_id
AND s.prod_id = 30
ORDER BY s.char_2000 ;
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 1 | 0 |00:00:03.16 | 19298 | |
| 1 | LOAD AS SELECT | | 1 | 0 |00:00:03.16 | 19298 | 521K (0)|
| 2 | SORT ORDER BY | | 1 | 29282 |00:00:02.06 | 5915 | 58M (0)|
|* 3 | HASH JOIN | | 1 | 29282 |00:00:01.74 | 5915 | 3515K (0)|
| 4 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 |00:00:00.24 | 1468 | |
| 5 | NESTED LOOPS | | 1 | 29282 |00:00:01.19 | 4447 | |
|* 6 | TABLE ACCESS FULL | PRODUCTS | 1 | 1 |00:00:00.02 | 7 | |
|* 7 | TABLE ACCESS FULL | SALES_T | 1 | 29282 |00:00:01.12 | 4440 | |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("S"."CUST_ID"="C"."CUST_ID")
6 - filter("P"."PROD_ID"=30)
7 - filter("S"."PROD_ID"=30)
예상대로 Sort 대상 컬럼을 1000에서 2000 byte로 바꾸자 PGA 사용량이 30 MB에서 58MB 로 약 두 배 늘었다. 위의 결과를 언뜻 보면, Order By절에 존재하는 컬럼 Size의 합계가 가로라고 생각할 수 있다. 왜냐하면, Sort 대상컬럼의 Size에 비례하여 PGA 사용량이 증가되었다고 판단하기 때문이다. 하지만 이런 생각은 절반만 옳고 나머지 절반은 틀렸다. 제대로 된 식은 다음과 같다.
Sort의 부하를 좌우하는 원리
Sort의 부하량(PGA 사용량) ∝ 세로 X 가로
세로: SQL의 결과 건수
가로: Order by 절의 컬럼 size + Order by 절을 제외한 나머지 컬럼의 size
근거 있는 주장인가?
이 공식이 글 전체의 핵심이다. 하지만, 많은 사람들이 위와 같은 가로 세로 개념을 주장할 수 있는 근거가 무엇인지 궁금해한다. 이제 가로가 Order by 절의 컬럼 size + 나머지 컬럼의 size라는 주장에 대한 근거를 보자.
SELECT s.channel_id, s.char_2000
FROM sales_t s
WHERE s.prod_id = 30
ORDER BY s.channel_id;
------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Used-Mem |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29282 |00:00:00.84 | 4440 | |
| 1 | SORT ORDER BY | | 1 | 29282 |00:00:00.84 | 4440 | 56M (0)|
|* 2 | TABLE ACCESS FULL| SALES_T | 1 | 29282 |00:00:00.68 | 4440 | |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("S"."PROD_ID"=30)
Order By절에는 Size가 작은 channel_id 컬럼 뿐이다. 그런데 PGA 사용량은 56 MB나 된다. 세로가 3만 건도 안 되는 집합을 Sort하는데 그 부하는 56 MB나 된다. 이상하지 않은가? 과부하의 이유는 Select절의 char_2000 컬럼 때문이다. 이 컬럼을 Select 절에서 제거하고 다시 실행해 보자.
SELECT s.channel_id
FROM sales_t s
WHERE s.prod_id = 30
ORDER BY s.channel_id;
------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Used-Mem |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29282 |00:00:00.64 | 4440 | |
| 1 | SORT ORDER BY | | 1 | 29282 |00:00:00.64 | 4440 | 424K (0)|
|* 2 | TABLE ACCESS FULL| SALES_T | 1 | 29282 |00:00:00.58 | 4440 | |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("S"."PROD_ID"=30)
Order By절 이외의 컬럼에 주목하라
Select절의 char_2000 컬럼을 제거하자 Sort의 부하는 424K로 급격히 줄어들었다. 왜냐하면, Sort Area에는 Order By절의 컬럼을 Sort할뿐만 아니라 나머지 컬럼 List도 Loading 되기 때문이다. Order By절 뿐만 아니라, Select 절에도 size가 큰 컬럼이 있다면 성능이 급격히 저하됨을 알 수 있다. 지금까지 Sort부하량 공식에 의해 가로는 Order By절 컬럼 Size + Sort 대상 이외의 컬럼 Size가 됨을 증명해 보았다.
이제 이 개념을 실제 SQL 프로그래밍에 적용해보자. 아래는 전형적인 Pagination SQL이다. 아래의 예제에서 처음의 약속을 지키기 위해 인덱스를 만들지도, 사용하지도 않을 것이다.
SELECT *
FROM (SELECT a.*, ROWNUM rnum
FROM (SELECT s.prod_id, p.prod_name, p.prod_category_desc, s.cust_id,
c.cust_first_name, c.cust_last_name, s.time_id, s.channel_id,
s.char_100, s.char_1000, s.char_2000
FROM sales_t s, customers c, products p
WHERE s.cust_id = c.cust_id
AND s.prod_id = p.prod_id
AND s.channel_id = 3
ORDER BY c.cust_first_name, c.cust_last_name, p.prod_category_desc, s.time_id ) a
WHERE ROWNUM <= :v_max_row ) --> 200 대입
WHERE rnum >= :v_min_row ; --> 1 대입
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 200 |00:00:08.71 | 5915 | |
|* 1 | VIEW | | 1 | 200 |00:00:08.71 | 5915 | |
|* 2 | COUNT STOPKEY | | 1 | 200 |00:00:08.71 | 5915 | |
| 3 | VIEW | | 1 | 200 |00:00:08.71 | 5915 | |
|* 4 | SORT ORDER BY STOPKEY| | 1 | 200 |00:00:08.71 | 5915 | 3321K (0)|
|* 5 | HASH JOIN | | 1 | 540K|00:00:06.30 | 5915 | 1176K (0)|
| 6 | TABLE ACCESS FULL | PRODUCTS | 1 | 72 |00:00:00.01 | 7 | |
|* 7 | HASH JOIN | | 1 | 540K|00:00:03.91 | 5908 | 3568K (0)|
| 8 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 |00:00:00.21 | 1468 | |
|* 9 | TABLE ACCESS FULL | SALES_T | 1 | 540K|00:00:01.14 | 4440 | |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNUM">=:V_MIN_ROW)
2 - filter(ROWNUM<=:V_MAX_ROW)
4 - filter(ROWNUM<=:V_MAX_ROW)
5 - access("S"."PROD_ID"="P"."PROD_ID")
7 - access("S"."CUST_ID"="C"."CUST_ID")
9 - filter("S"."CHANNEL_ID"=3)
페이징 처리된 SQL의 Sort 부하량은 3321K 이다. 이제 Sort의 부하를 줄이기 위해 select 절의 모든 컬럼을 제거하자.
SELECT s.prod_id, p.prod_name, p.prod_category_desc, s.cust_id,
c.cust_first_name, c.cust_last_name, s.time_id, s.channel_id,
s.char_100, s.char_1000, s.char_2000
FROM (SELECT a.*, ROWNUM rnum
FROM (SELECT s.rowid as s_rid, p.rowid as p_rid, c.rowid as c_rid
FROM sales_t s, customers c, products p
WHERE s.cust_id = c.cust_id
AND s.prod_id = p.prod_id
AND s.channel_id = 3
ORDER BY c.cust_first_name, c.cust_last_name, p.prod_category_desc, s.time_id ) a
WHERE ROWNUM <= :v_max_row ) a, --> 200 대입
sales_t s, customers c, products p
WHERE s.rowid = a.s_rid
and c.rowid = a.c_rid
and p.rowid = a.p_rid
and rnum >= :v_min_row ; --> 1 대입
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 200 |00:00:06.51 | 6168 | |
| 1 | NESTED LOOPS | | 1 | 200 |00:00:06.51 | 6168 | |
| 2 | NESTED LOOPS | | 1 | 200 |00:00:06.51 | 5969 | |
| 3 | NESTED LOOPS | | 1 | 200 |00:00:06.51 | 5918 | |
|* 4 | VIEW | | 1 | 200 |00:00:06.51 | 5915 | |
|* 5 | COUNT STOPKEY | | 1 | 200 |00:00:06.51 | 5915 | |
| 6 | VIEW | | 1 | 200 |00:00:06.51 | 5915 | |
|* 7 | SORT ORDER BY STOPKEY | | 1 | 200 |00:00:06.51 | 5915 |96256 (0)|
|* 8 | HASH JOIN | | 1 | 540K|00:00:05.81 | 5915 | 1193K (0)|
| 9 | TABLE ACCESS FULL | PRODUCTS | 1 | 72 |00:00:00.01 | 7 | |
|* 10 | HASH JOIN | | 1 | 540K|00:00:03.65 | 5908 | 4514K (0)|
| 11 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 |00:00:00.22 | 1468 | |
|* 12 | TABLE ACCESS FULL | SALES_T | 1 | 540K|00:00:01.06 | 4440 | |
| 13 | TABLE ACCESS BY USER ROWID| CUSTOMERS | 200 | 200 |00:00:00.01 | 3 | |
| 14 | TABLE ACCESS BY USER ROWID | PRODUCTS | 200 | 200 |00:00:00.01 | 51 | |
| 15 | TABLE ACCESS BY USER ROWID | SALES_T | 200 | 200 |00:00:00.01 | 199 | |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("RNUM">=:V_MIN_ROW)
5 - filter(ROWNUM<=:V_MAX_ROW)
7 - filter(ROWNUM<=:V_MAX_ROW)
8 - access("S"."PROD_ID"="P"."PROD_ID")
10 - access("S"."CUST_ID"="C"."CUST_ID")
12 - filter("S"."CHANNEL_ID"=3)
Trade Off가 유리한 경우
Sort 부하량이 3321K에서 96K로 약 34.5배 줄어들었다. 이렇게 ROWID만 남기고 select 절의 모든 컬럼을 제거해도 결과는 같다. 왜냐하면, Sort된 상태로 rowid가 보관되어있기 때문이다. 페이징 처리가 모두 끝나고 200건에 대해서만 rowid로 테이블에 접근하기 때문에 테이블의 중복사용에 의한 비효율은 매우 적다. Buffers 항목을 비교해보면 5915 블록에서 6168 블록으로 비효율은 253 블록(4%) 밖에 차이가 나지 않는다. 하지만 Sort의 부하는 34.5배나 줄어들었다. 약간의 Block I/O를 손해 보더라도 Sort의 부하가 아주 큰 경우는 같은 블록을 중복해서 읽어야 함을 알 수 있다.
장점 + 장점
이렇게 Rowid를 제외한 Select List를 인라인뷰 외부로 빼면, Sort 부하의 최소화 이외에 또 다른 효과를 누릴 수도 있다. 인덱스만 읽고 테이블로의 접근을 하지 않을 수 있다. 즉 Where 조건에 최적화된 인덱스가 존재하고, 그 인덱스가 Order By절 컬럼을 포함 한다면 인라인뷰 내부에서는 테이블 접근을 하지 않는다. 물론 Select List의 모든 컬럼들을 가져오려면 테이블을 접근해야 한다. 하지만 위의 예제처럼 Rowid를 사용했다면 페이징 처리가 끝난 후에, 특정 페이지에 해당하는 건들만 테이블로 접근할 수 있으므로 Random Access 도 최소화 된다. Sort를 최소화 하려고 했더니 Block I/O를 최소화 하는것까지 덤으로 얻을 수 있는 것이다.
주의사항
SELECT 절에 상수나 변수 심지어 NULL이 오더라도 PGA 사용량은 증가하므로 주의해야 한다.
SELECT s.cust_id
FROM sales_t s
ORDER BY s.cust_id;
------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Used-Mem |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 918K|00:00:03.38 | 4440 | |
| 1 | SORT ORDER BY | | 1 | 918K|00:00:03.38 | 4440 | 13M (0)|
| 2 | TABLE ACCESS FULL| SALES_T | 1 | 918K|00:00:01.38 | 4440 | |
------------------------------------------------------------------------------------------
Select 절에 다른 컬럼이 없기 때문에 PGA를 13MB 사용 하였다. 이번에는 Select절에 Null을 추가해보자.
SELECT s.cust_id, null
FROM sales_t s
ORDER BY s.cust_id;
------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Used-Mem |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 918K|00:00:03.48 | 4440 | |
| 1 | SORT ORDER BY | | 1 | 918K|00:00:03.48 | 4440 | 17M (0)|
| 2 | TABLE ACCESS FULL| SALES_T | 1 | 918K|00:00:01.37 | 4440 | |
------------------------------------------------------------------------------------------
Select List에 Null을 추가하자 PGA사용량이 4MB 증가했다. 오라클은 Null이나 상수도 컬럼처럼 취급함을 알 수 있다. 따라서 Order by절이 있는 인라인뷰의 Select List에 상수나 변수 혹은 Null을 집어넣는 것은 Sort의 부하를 증가시킨다. 상수나 변수는 Order By가 있는 인라인뷰에 넣지 말고 외부로 빼서 사용하면 된다.
결론
Sort의 부하를 최소화 하려면 Order By절의 컬럼에만 집중해서는 안되며, 전체 컬럼 List를 바라보아야 한다. 또한 프로그래밍을 할 때 상수 하나, 변수 하나의 위치도 고려해야 최적의 성능을 가진 프로그램이 됨을 알 수 있다. 즉 Sort의 최적화는 튜닝의 문제일 뿐만 아니라 프로그래밍의 문제인 것이다. 이점은 Sort 부하량이 무엇으로 결정되는지 개발자도 알아야 하는 이유가 된다.
면적은 가로와 세로로 구성된다.
Sort의 부하량은 면적의 크기에 비례한다.
Sort의 부하량 ∝ 결과 건수 X 전체 컬럼 Size