1. 서론

지난 8월, 사내 AI 해커톤에 참여하여 본선진출까지 성공했습니다.
이에 따라 본선 과제를 받았는데요.

본선 과제는 생성형AI 모델을 활용하여 업무에 적용할 수 있는 과제를 발굴하고 개발하는 것입니다.
생성형AI 모델의 예로 ChatGPT의 GPT모델이 있죠.

LLM (Large Language Model)

기본적으로 LM(Language Model) 은 인간의 언어(자연어)를 이해하고 생성할 수 있도록 훈련된 인공지능 모델입니다.
이러한 LM에 대량의 언어데이터를 학습하여 문장구조나 문법, 의미 등을 이해할 수 있도록 한 모델이 LLM(Large Language Model) 입니다.
또한, LLM은 딥러닝 알고리즘과 통계모델링을 활용하는 모델이기 때문에 자연어처리(Natural Language Processing, NLP) 작업을 수행하는 데에도 사용됩니다.

저는 이 자연어처리 기술에 초점을 맞추기로 했어요. 왜냐하면 LLM모델을 활용하는 대부분의 아이디어가 자연어로 입력받고 출력되는 컨셉이 기본이 되고 있거든요. 가령 챗봇이나 AI비서 등이 있겠죠.

그렇다면, 자연어처리를 수행하는 LLM은 어떻게? 우리 인간의 말(자연어)을 이해할 수 있을까요?
답은 본 글의 주제인 Embedding기술에 있습니다.

2. Embedding이란?

Embedding은 텍스트 기반의 자연어를 기계가 이해할 수 있는 실수벡터 형태로 표현하는 기술입니다.
Embedding을 통해 생성된 벡터들을 활용하면 자연어의 의미와 자연어간 문맥을 파악할 수 있습니다.

Embedding을 이용하는 이유는 또 있습니다.
자연어를 단순 인코딩한 숫자형태의 텍스트는 일반적으로 길이가 매우 길고, 가변적이어서 AI 모델이 쉽게 이해하지 못합니다.
이러한 맹점을 보완하기 위해 Embedding을 활용하여 고정된 차원의 벡터형태로 자연어를 변환하는 것입니다.

Word embedding vs Sentence/Document embedding

원본 텍스트를 기준으로 하면 Embedding은 크게 Word Embedding과 Sentence/Document Embedding으로 나눌 수 있습니다.

Word Embedding은 말그대로 입력으로 들어온 자연어를 단어별로 Embedding을 수행하는 것인데요.
전처리 과정에 따라 Word를 어떻게 구성할지 달라지겠지만, 입력으로 들어온 문장형태의 텍스트가 Tokenizer를 통해 Word 단위로 토큰화된다고 가정한다면, Word단위의 토큰별로 Embedding 모델을 통해 실수벡터를 생성하는 방식이 Word Embedding이 될 것입니다.

반면에 Sentence/Document Embedding은 말그대로 문장단위의 Embedding을 수행하는 것인데요.
예를들어 자연어 형태의 텍스트 문장이 입력으로 들어오면, Word마다 임베딩된 실수벡터들을 평균내어 하나의 Embedding으로 집계하는 방식으로 Sentence/Document Embedding을 수행할 수 있습니다.

3. Embedding의 원리

이제 Embedding을 통해 자연어가 실수벡터로 변환되는 과정을 알아보겠습니다.

One-hot Encoding

초기에는 비교적 단순한 방식을 채택했습니다.
모델 학습을 위한 방대한 단어집이 있다고 가정했을 때, 각 단어마다 숫자 인덱스를 부여하고 단어집 내 총 단어갯수만큼의 길이를 가진 벡터에 해당 인덱스에 1을 체크 하고 나머지는 0을 넣는 방식으로 Embedding된 벡터를 생성했습니다.

이러한 방식은 문제가 있었습니다.
Embedding벡터의 차원이 지나치게 커지는 경향이 있어 성능문제를 야기할 수 있고, 이보다 더 큰 문제는 단어간 유사성을 확인할 수 없다는 점이 있었습니다. 단어간 유사성을 알 수 없다는 것은 LLM을 통해 주로 활용되는 검색시스템에서는 치명적인 단점이 될 수 있습니다.

Word2Vec

13년 구글의 연구원들은 Word2Vec이라는 기술을 개발하게 됩니다.
Word2Vec은 One-hot Encoding이 단어간 유사성을 확인할 수 없다는 단점을 보완할 수 있고, 앞서 개발된 NNLM모델에 비해서도 은닉층 제거, 계층적 소프트맥스, 네거티브 샘플링 등과 같은 기술들을 도입하여 효율성도 향상시킨 기술이라고 합니다.

그렇다면 Word2Vec은 어떻게 단어간 유사성을 표현할까요?
이전 One-hot Encoding에서 벡터의 0과 1로 표현하던 방식을 희소표현(Sparse Representation)이라고 한다면,
Word2Vec에서 벡터를 표현하는데 사용하는 방식은 분산표현(Distributed Representation)이라고 하는데,
이는 "비슷한 문맥에서 등장하는 단어들은 비슷한 의미를 가진다." 라는 가정을 전제로 하는 표현방식입니다.
가령, 강아지란 단어는 '귀엽다.', '예쁘다.' 등의 단어가 주로 함께 등장하는데, 해당 단어들을 임베딩 과정을 통해 벡터화한다면, 해당 단어벡터들은 유사한 벡터값을 가지게 됩니다.

위와 같은 Embedding방식은 LLM기반 Learned Embedding 모델을 통해 진행될 수 있는데, Embedding과정에서 모델은 다양한 단어들을 입력받게 되고 그 과정에서 문맥상 의미적으로 유사한 단어들의 Embedding 벡터들 간의 거리는 가깝게, 그렇지 않은 단어들의 Embedding 벡터들 간의 거리는 멀게 만드는 방식으로 의미적 관계를 학습합니다.

이러한 분산표현 방식을 통해 Embedding된 벡터들은 단어집의 단어 갯수만큼의 차원을 가질 필요도 없고, 단어간의 유사도도 표현할 수 있게됩니다.

CBOW (Continuous Bag of Words)

Word2Vec 학습 방식에는 CBOW방식과 Skip-Gram방식 두 가지 방식이 있습니다.
이 중 CBOW방식은 주변 단어들을 기반으로 중간에 있는 단어들을 예측하는 방식입니다. 아래와 같은 예문이 있다고 가정해봅시다.

ex) "The fat cat sat on the mat"
LLM의 CBOW는 ['The', 'fat', 'cat', 'on', 'the', 'mat'], 주변단어로부터 sat, 중심단어를 예측하게 됩니다.
중심단어를 예측하기 위해서 앞, 뒤로 몇개의 단어를 볼지 결정해야 하는데, 이 범위를 Window 라고 합니다.

윈도우를 옆으로 움직여서 중심단어의 선택을 변경해가며 학습을 위한 데이터셋을 만드는데, 이를 슬라이딩 윈도우 라고 합니다.
CBOW의 인공신경망 모델을 도식화하면 아래와 같습니다.

Skip-Gram

Skip-Gram은 CBOW와는 반대로 중심 단어에서 주변 단어를 예측하는 기술입니다. 윈도우의 크기를 2로 가정했을때, 데이터셋은 다음와 같이 구성됩니다.

Skip-Gram의 인경신경망 모델을 도식화하면 아래와 같습니다.

4. Embedding의 활용

Embedding은 크게 아래 두 가지 케이스에 활용될 수 있습니다.

여러 문서들이 존재할 때, 이들 중 하나를 선택하거나 이들을 서로 비교해야하는 경우

해당 Case의 예로 Semantic Search(의미 기반 검색), Recommendation(추천), Clustering(군집화) 등이 있습니다.

A. Semantic Search

Semantic Search는 사용자가 입력한 텍스트 형태의 자연어와 의미적으로 연관성이 높은 문서들을 찾아서 제시해주는 기능입니다.

1. 문서 모음집에 포함되어 있는 각각의 문서에 대한 embedding을 계산하여 별도의 저장소(e.g. local drive, vector database 등)에 저장해 놓음.
2. 입력 텍스트에 대한 embedding을 계산함.
3. 입력 텍스트에 대한 embedding 벡터와 각 문서 embedding 벡터 간의 cosine similarity(코사인 유사도)를 계산하고, 그 값을 기준으로 전체 문서들을 내림차순 정렬함.
4. 정렬 결과 중 상위 k개에 해당하는 문서들의 텍스트를 불러온 뒤 이를 반환함.
   
   

B. Recommendation

Recommendation은 사용자가 현재 보고 있는 문서와 의미적으로 연관성이 높은 다른 문서들을 찾아서 제시해주는 기능입니다.

1. 문서 모음집에 포함되어 있는 각각의 문서에 대한 embedding 벡터를 계산하여 별도의 저장소(e.g. local drive, vector database 등)에 저장해 놓음.
2. 현재 보고 있는 문서의 embedding 벡터와 그 외의 문서들 각각의 embedding 벡터 간의 cosine similarity(코사인 유사도)를 계산하고, 그 값을 기준으로 전체 문서들을 내림차순 정렬함.
3. 정렬 결과 중 상위 k개에 해당하는 문서들의 텍스트를 불러온 뒤 이를 반환함.
   
   

C. Clustering

Clustering은 여러 문서들 간의 의미적 유사성을 바탕으로 이들을 몇 개의 그룹으로 묶어서 정리해 주는 기능입니다. Semantic Search과의 차이는 많은 수의 문서 쌍들의 embedding 벡터간의 거리를 계산해야한다는 것입니다.

Cosine Similarity (코사인 유사도)

코사인 유사도 (Cosine Similarity)는 두 벡터간의 코사인 값을 이용하여 구할 수 있는 두 벡터간의 유사도를 말합니다.
두 벡터간의 방향이 동일한 경우 1, 방향이 90도인 경우 0, 방향이 반대인 경우 -1 값을 가집니다.
즉, 코사인 유사도 값이 1에 가까울 수록 유사도가 높다고 판단할 수 있습니다.

아래는 두 벡터 A, B간의 유사도를 계산하는 수식입니다.

5. Embedding 구현

Embedding에 대해서 알아봤으니, 이제 Embedding 기술을 활용한 간단한 챗봇 샘플을 만들어보겠습니다.

import openai
import faiss
import numpy as np

with open('config.txt', 'r') as file:
    lines = file.readlines()

settins = {}
for line in lines:
    key, value = line.strip().split('=')
    settings[key] = value

azure_openai_api_type = settins.get('API_TYPE')
azure_openai_api_base = settins.get('API_BASE')
azure_openai_api_version = settins.get('API_VERSION')
azure_openai_api_key = settins.get('API_KEY')

Embedding에 활용할 라이브러리를 모두 import 합니다.
import 대상들은 사전에 설치되어있어야 합니다. 저는 Embedding에 GPT모델 활용에 필요한 OpenAI 라이브러리를 사용할 것입니다.
그리고 OpenAI 라이브러리 활용을 위한 Config 정보들을 읽어들입니다.

def get_embedding(text, engine="text-embedding-ada-002"):
    text = text.replace("\n", " ")
    return openai.Embedding.create(input=[text], engine=engine)["data"][0]["embedding"] 

텍스트를 입력받아 Embedding을 수행하여 Embedding 벡터를 반환하는 함수를 선언합니다.
Embedding을 위해 사용할 모델은 OpenAI의 text-embedding-ada-002 모델입니다.

class Chatbot:
    def __init__(self, index, embeddings, documents, system_message):
        self.index = index
        self.embeddings = embeddings
        self.documents = documents
        self.system_message = system_message
        self.chat_history = []

    def get_embedding(self, text engine="text-embedding-ada-002"):
        text = text.replace("\n", " ")
        return openai.Embedding.create(input=[text], engine=engine)["data"][0]["embedding"] 

    def find_similar_document(self, user_embedding):
        _, top_indices = self.index.search(np.array([user_embedding]), 1)
        top_index = top_indices[0][0]
        return self.documents[top_index]

    def chat(self, user_input):
        user_embedding = self.get_embedding(user_input)
        similar_document = self.find_similar_document(user_embedding)
        system_message = self.system_message + " " + similar_document

        messages = [{"role": "system", "content": system_message}]
        for message in self.chat_history:
            messages.append(message)
        message.append({"role": "user", "content": user_input})

        response = openai.ChatCompletion.create(
            engine="gpt-35-turbo",
            messages=message
        )
        assistant_message = response.choices[0].message.content

        self.chat_history.append({"role": "user", "content": user_input})
        self.chat_history.append({"role": "assistant", "content": assistant_message})

        return assistant_message

챗봇 클래스를 선언합니다. 선언된 챗봇의 동작 flow는 아래와 같습니다.

1. 챗봇 인스턴스를 생성하기 위해 아래 파라미터를 입력받아 인스턴스 메모리에 저장합니다.
   1) index: 유사도를 계산하여 가장 유사한 문서의 인덱스를 반환하는 함수 (faiss 라이브러리 활용)
   2) embeddings: 문서들을 임베딩한 벡터 list
   3) documents: 실제 문서 list
   4) system_message: LLM에 System role로 입력할 content 파라미터 값 (프롬프트에 활용)
2. 사용자가 입력한 자연어 메세지를 챗봇 인스턴스의 chat함수에 파라미터로 입력하여 실행시킵니다.
3. 사용자가 입력한 자연어 메세지를 Embedding합니다.
4. 챗봇 인스턴스의 find_similar_document함수를 통해 입력 Embedding벡터와 문서 Embedding벡터들을 비교하여 유사도를 계산하고, 가장 유사한 문서를 반환합니다.
5. 반환된 문서를 system_message에 추가합니다. (system 프롬프트에 활용)
6. 챗봇 인스턴스에 chat_history가 저장되어 있다면, 해당 값을 system_message에 추가합니다.
7. system_message를 system role로, 사용자가 입력한 자연어 메세지를 user role로 GPT모델에 프롬프트 명령을 내립니다. (gpt-35-turbo 모델 활용)
8. 응답 메세지를 챗봇 인스턴스의 chat_history에 저장합니다.
9. 응답 메세지를 반환합니다.
   
   

아래 코드는 챗봇 인스턴스를 생성하여 chat함수를 실행시키는 코드입니다.

documents = [(documents의 텍스트 배열)]
embeddings = [get_embedding(doc) for doc in documents]
embedding_matrix = np.array(embeddings) # numpy.ndarray 타입 변경
index = faiss.IndexFlatL2(embedding_matrix.shape[1]) # 유사도를 계산하여 가장 유사한 문서를 반환하는 함수 생성, 입력 파라미터는 문서 배열의 크기
index.add(embedding_matrix)

system_message = "너는 친절한 챗봇이며 다음 내용을 참고하여 적절히 답변해줘."

chatbot = Chatbot(index, embeddings, documents, system_message)

user_input = "사용자 입력 메세지"
response = chatbot.chat(user_input)
print(response)

위 코드에서 볼 수 있듯이 유사도 계산에는 faiss 라이브러리의 IndexFlatL2 함수가 활용됩니다.

위 코드들을 통해 사전에 문서들의 리스트를 입력받아 생성된 챗봇에 사용자가 자연어를 입력하면 그에 맞게 답변해주는 챗봇을 구현할 수 있습니다.

Reference

• Embedding이란 무엇이고, 어떻게 사용하는가?, 김길호
• 워드투벡터(Word2Vec), 위키독스
• 임베딩(Embedding)이 뭐지?, jeongpil
• LLM (Large Language Model) 이란 무엇인가?
  , Datahunt
• LLM을 품은 클라우드와 AGI 트렌드, 삼성SDS

👉이전글 에서 Regression에 대해 살펴봤습니다.

이번 글에서 살펴볼 Logistic Regression 모델은 True/False 2가지 범주 중 하나의 값을 예측하여 출력하는 이진분류의 가장 기본이 되는 모델입니다.
이번 글은 모두를 위한 딥러닝 강의를 수강한 후 작성하는 글입니다.

Linear Regression

Linear Regression은 아래 사진과 같은 Hypothesis와 Cost Function을 갖습니다.

• Hypothesis : 입출력의 관계를 나타내는 함수(가설), 해당 함수는 학습데이터 기반의 모델학습 과정을 거쳐 확정됩니다.
• Cost Function : 예측(가설)을 통한 출력 값과 실제 값의 차이를 나타내는 함수, Cost Function은 아래 사진에서 나타내는 것처럼 2차 함수 형태의 표를 갖습니다. 해당 Cost Function의 값이 최소가 되는 W를 찾는 것이 모델 학습의 목표입니다.
  
  

위 Linear Regression 모델을 통해 Hypohesis와 Cost Function이 어떻게 구성되는지 쉽게 알 수 있었습니다.

Logistic Regression

Linear Regression의 맹점

Logistic Regression모델은 이진분류에 강점을 가지고 있는 모델입니다.
이진분류(Binary Classification)의 경우에 Linear Regression을 사용한다고 가정해보겠습니다.

위 사진은 공부한 시간에 따른 시험 pass/fail 여부를 예측하는 모델의 Hypothesis를 나타낸 표입니다.
해당 예시를 Linear Regression모델에 적용하여 특정 y값 이상이 되면 pass로 판단할 수 있도록 구성할 수도 있습니다.

하지만 Linear Regression 모델은 맹점이 있습니다.
입력 학습데이터의 편차가 클 경우, 모델 학습이 잘 안될 가능성이 있습니다.
위 예시에서 만약 학습시간이 50으로 큰 값이 들어온다면, 해당 Hypothesis는 기존 기울기보다 우측으로 크게 치우쳐진 표를 그리는 함수가 될 것입니다.
이렇게 되면 낮은 x(hour)값이 입력되어 pass가 나와야하는 상황에서도 fail이 나올 수 있게 됩니다.

이러한 Linear Regression의 맹점을 보완한 모델이 Logistic Regression입니다.

Logistic Regression Hypothesis

Linear Regression의 맹점을 보완하기 위해 학습데이터의 y값은 아무리 큰 x값이 입력되어도 일정 수준(0 or 1)으로 유지가 되어야 합니다.
그래서 아래 사진의 sigmoid함수를 적용하였습니다.

sigmoid함수는 양의 무한대로 갈 수록 1로 수렴하고, 음의 무한대로 갈 수록 0으로 수렴하는 특징을 가지고 있습니다.
즉, 아무리 편차가 큰 입력 값이 들어와도 출력 값은 일정 수준(0 or 1)으로 유지할 수 있게 되는 것이죠.
이러한 sigmoid함수를 적용하여 Hypothesis를 구성하면 아래 사진과 같이 표현할 수 있습니다.

위 사진처럼 sigmoid함수의 x값에 Linear Regression의 Hypothesis를 넣으면
Logistic Regression의 Hypothesis가 구성됩니다.

이를 통해 Logistic Regression모델을 통해 데이터를 학습시키면 보다 정확한 True/False 값을 기대할 수 있게됩니다.

Logistic Regression Cost Function

Logistic Regression의 Hypothesis를 정했으니, 이제 Cost Function을 정해야 합니다.

Logistic Regression의 Hypothesis를 그대로 활용하여 Cost Function을 정한다면,
위 사진에서 보는 것처럼 최소의 Cost를 가지는 W를 찾는데 문제가 발생합니다.

모델 학습과정은 Cost Function그래프에서 왼쪽에서든 오른쪽에서든 기울기를 따라 서서히 내려오면서 최소의 Cost를 가지는 W값을 찾는 과정입니다.
위 사진 속 Linear Regression의 그래프에서 보이는 것처럼 그래프는 매끈하게 구성되어 있기 때문에 최솟값을 찾기 쉽습니다.
하지만 오른쪽 Logistic Regression의 Cost Function그래프는 울퉁불퉁하게 구성되어 있어서 그래프의 기울기만 따라 내려오는 것만으로는 최솟값을 찾기 힘듭니다. (중간의 울퉁불퉁한 곡선에 걸쳐질 확률이 높습니다.)

이를 방지하기 위해 Logistic Regression의 Cost Function은 새롭게 구성되어야 합니다.

Linear Regression의 Cost Function처럼 매끄러운 형태의 그래프를 구성하기 위해
Logistic Regression의 Cost Function에서는 log함수가 사용됩니다. 해당 log함수는 Logistic Regression Hypothesis 자연대수e와 상극인 함수이기 때문입니다.

이를 통해 위 두번째 사진처럼 그래프를 구성할 수 있습니다.
그리고 해당 log함수는 2차 함수처럼 최솟값을 가지는 그래프가 아닌 한쪽으로 편향된 형태를 가지는 그래프이기 때문에,
실제값이 0이냐 1이냐에 따라 함수 구성을 달리하여 Cost Function을 구성하였습니다.
이를 통해 보다 정확한 Cost를 구할 수 있습니다.

예를들어 실제값이 1인 경우에 예측값이 0이 나올경우 Cost는 무한대로 수렴하고,
실제값이 0인 경우에 예측값이 0이 나올경우 Cost는 0으로 수렴하기 때문에 보다 정확한 학습이 이뤄지게 됩니다.
그리고 이러한 Cost Function은 아래 사진처럼 통합하여 표현될 수 있습니다.

Logistic Regression in Tensorflow

Logistic Regression모델은 아래와 같은 Tensorflow코드를 통해 구현될 수 있습니다.
(해당 Tensorflow 버전은 1.0이므로 2.0버전과는 호환이 안될 수 있습니다.)

x_data = [[1, 2], [2, 3], [3, 1], [4, 3], [5, 3], [6, 2]]
y_data = [[0], [0], [0], [1], [1], [1]]

# placeholders for a tensor that will be always fed.
X = tf.placeholder(tf.float32, shape=[None, 2])
Y = tf.placeholder(tf.float32, shape=[None, 2])

W = tf.Variable(tf.random_normal([2, 1]), name='weight')
b = tf.Variable(tf.random_normal([1]), name='bias')

# Hypothesis using sigmoid: tf.div(1., 1. + tf.exp(tf.matmul(X, W)))
hypothesis = tf.sigmoid(tf.matmul(X, W) + b)

# cost/loss function
cost = -tf.reduce_mean(Y * tf.log(hypothesis) + (1 - Y) * tf.log(1 - hypothesis))
train = tf.train.GradientDescentOptimizer(learning_rate=0.01).minimize(cost)

# Accuracy computation
# True if hypothesis > 0.5 else False
predicted = tf.cast(hypothesis > 0.5, dtype=tf.float32)
accuracy = tf.reduce_mean(tf.cast(tf.equal(predicted, Y), dtype=tf.float32))

# Launch graph
with tf.Session() as sess:
    # Initialize TensorFlow variables
    sess.run(tf.global_variables_initializer())

    for step in range(10001):
        cost_val, _ = sess.run([cost, train], feed_dict={X: x_data, Y: y_data})
        if step % 200 == 0:
            print(step, cost_val)

    # Accuracy report
    h, c, a = sess.run([hypothesis, predicted, accuracy], feed_dict={X: x_data, Y: y_data})
    print("\nHypothesis: ", h, "\nCorrect (Y): ", c, "\nAccuracy: ", a)

사내업무 중 쿼리 튜닝작업을 진행하면서 생겼던 작은 고민들에 대해 공유해볼까 합니다.
본 글과 관련하여 잘못된 부분이나 의견이 있으시다면 언제든 댓글로 남겨주세요!

사용 DB는 Oracle입니다.

1. Top 쿼리 튜닝기

23년 봄 어느 날, 인프라 부서로부터 CUI DB Top 쿼리 list를 받았습니다.
저희 파트의 쿼리가 당당하게(?) 1등을 차지했더라구요.
해당 쿼리가 1등을 할 수 있었던 비결을 먼저 파악해보겠습니다.

A. 비효율 성능의 원인

• Buffer Gets

먼저 저는 TOP 쿼리가 어떤 기준으로 추출되는지 파악하였습니다. TOP 쿼리는 Buffer Gets라는 수치를 기준으로 추출되는데요,

Buffer Gets란? SQL이 처리되는 과정에서 엑세스한 메모리 블록의 수를 의미합니다. 해당 수치가 높을수록 CPU에 많은 부하를 주고있다는 것을 의미하죠.

이번 TOP 쿼리 list에서 1등을 차지한 쿼리는 위 Buffer Gets라는 수치가 압도적으로 높았습니다. 그래서 해당 수치를 줄이는 것을 목표로 쿼리 성능개선에 돌입하였습니다.

• 실행계획 ( Explain Plan )

실행계획을 통해 SQL의 처리절차와 상세 성능지표를 확인할 수 있습니다.

실행계획이란? 말그대로 SQL을 DB에서 어떻게 처리할지에 대한 계획입니다. DB에서 SQL이 처리될 때, 파싱 > 최적화 > 실행 과정을 거치는데요.

이 중, 최적화 과정에서 DB Optimizer가 SQL이 실행되는데 필요한 Cost를 계산하여 최적의 실행계획을 수립하게 됩니다. 이러한 실행계획을 참고하여 SQL의 어떤 부분이 많은 Cost를 발생시키는지 상세히 파악할 수 있습니다.

실행계획은 아래 쿼리를 통해 확인할 수 있습니다.

-- 실행계획 작성 쿼리
EXPLAIN PLAN
SET STATEMENT_ID = 'HR_PLAN'
FOR ({ 성능 확인이 필요한 쿼리 });


-- 실행계획 조회 쿼리
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'HR_PLAN', 'ALL'));

실행계획은 아래 포맷으로 추출됩니다.

Plan hash value: 2689199366

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |  9174 |  3252K|       | 16119   (1)| 00:00:01 |
|*  1 |  FILTER                             |                     |       |       |       |            |          |
|   2 |   SORT ORDER BY                     |                     |  9174 |  3252K|  3504K| 16119   (1)| 00:00:01 |
|   3 |    HASH UNIQUE                      |                     |  9174 |  3252K|  3504K| 15405   (1)| 00:00:01 |
|   4 |     NESTED LOOPS                    |                     |  9174 |  3252K|       | 14690   (1)| 00:00:01 |
|   5 |      NESTED LOOPS OUTER             |                     |  9174 |  2893K|       | 14689   (1)| 00:00:01 |
|   6 |       NESTED LOOPS OUTER            |                     |  9174 |  2221K|       | 12854   (1)| 00:00:01 |
|*  7 |        HASH JOIN                    |                     |  9174 |  1549K|       | 11018   (1)| 00:00:01 |
|*  8 |         HASH JOIN                   |                     |  9174 |   689K|       |  6183   (1)| 00:00:01 |
|   9 |          TABLE ACCESS BY INDEX ROWID| PNA_ORTR_DTL        |  9175 |   447K|       |  1352   (1)| 00:00:01 |
|* 10 |           INDEX RANGE SCAN          | PNA_ORTR_DTL_IX_03  |  9175 |       |       |    15   (0)| 00:00:01 |
|* 11 |          TABLE ACCESS FULL          | PNA_NGOSIIF_TXN     | 29602 |   780K|       |  4831   (1)| 00:00:01 |
|* 12 |         TABLE ACCESS FULL           | PNA_NGOSIIF_TXN     |   325K|    29M|       |  4834   (1)| 00:00:01 |
|  13 |        TABLE ACCESS BY INDEX ROWID  | PNA_CUST_ORD_DTL    |     1 |    75 |       |     1   (0)| 00:00:01 |
|* 14 |         INDEX UNIQUE SCAN           | PNA_CUST_ORD_DTL_PK |     1 |       |       |     1   (0)| 00:00:01 |
|  15 |       TABLE ACCESS BY INDEX ROWID   | PNA_CUST_ORD_DTL    |     1 |    75 |       |     1   (0)| 00:00:01 |
|* 16 |        INDEX UNIQUE SCAN            | PNA_CUST_ORD_DTL_PK |     1 |       |       |     1   (0)| 00:00:01 |
|* 17 |      INDEX UNIQUE SCAN              | PNA_PROD_ORD_BAS_PK |     1 |    40 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

......
......
......

1.Operation : SQL이 실행되는 순서를 확인할 수 있습니다.
2.Name : 접근한 테이블이나 Access한 인덱스 등에 대한 정보를 확인할 수 있습니다.
3.Rows : 예상 출력 건수
4.Bytes : 예상 조회 데이터 크기
5.Cost : 예상 쿼리 실행 비용 ( 예상 값들은 실제 값과 차이가 있을 수 있습니다. )

• 무출동 쿼리 및 실행계획 분석

개선대상의 쿼리인 아래 쿼리는 무출동 SMS 발송대상 오더를 조회하는 쿼리입니다. PNA_NGOSIIF_TXN 테이블 LAST_CHG_DT 값을 기준으로 현재날짜( sysdate )보다 이전 날짜 대상의 row를 조회하는데요.

해당 쿼리의 실행계획은 위 실행계획 참고바랍니다.

SELECT DISTINCT D.NGOSI_SEQ
                ,D.ORDR_TRT_NO
                ,D.OBDNG_ID
                ,D.CUST_ORDR_VER_NO
                ,D.SVC_CONT_ID
                ,D.ORDR_TYPE_ID
                ,D.FIRST_CRET_DT
                ,D.ORDR_TRT_TYPE_SEQ
                ,TO_CHAR(D.ORDR_RCP_DATE,'YYYY-MM-DD HH24:MI:SS') AS ORDR_RCP_DATE
                ,D.NGOSI_TYPE_CD
                ,D.TRT_RESLT
                ,D.TRT_RESLT_SBST
                ,TO_CHAR(D.LAST_CHG_DT,'YYYY-MM-DD HH24:MI:SS') AS LAST_CHG_DT
                ,NVL(NVL(NVL(NVL(NVL(NCUST.IST_CNTPLC_TEL_NO, NCUST.GENL_TEL_NO), NCUST.GENL_2_TEL_NO), NCUST.GENL_3_TEL_NO), NCUST.GENL_4_TEL_NO), NVL(NVL(NVL(NVL(CCUST.IST_CNTPLC_TEL_NO, CCUST.GENL_TEL_NO), CCUST.GENL_2_TEL_NO), CCUST.GENL_3_TEL_NO), CCUST.GENL_4_TEL_NO)) AS GENL_TEL_NO          
FROM PNA_ORTR_DTL A
INNER JOIN PNA_PROD_ORD_BAS BAS ON A.CUST_ORDR_NO = BAS.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = BAS.CUST_ORDR_VER_NO AND A.PROD_ORDR_SEQ = BAS.PROD_ORDR_SEQ
INNER JOIN PNA_NGOSIIF_TXN C ON A.CUST_ORDR_NO = C.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = C.CUST_ORDR_VER_NO AND C.NGOSI_TYPE_CD = 'R01' AND C.TRT_RESLT = 'OK'
INNER JOIN PNA_NGOSIIF_TXN D ON A.CUST_ORDR_NO = D.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = D.CUST_ORDR_VER_NO AND A.ORDR_TYPE_ID = D.ORDR_TYPE_ID
LEFT OUTER JOIN PNA_CUST_ORD_DTL NCUST ON A.CUST_ORDR_NO = NCUST.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = NCUST.CUST_ORDR_VER_NO AND A.PROD_ORDR_SEQ = NCUST.PROD_ORDR_SEQ AND NCUST.CHG_BEFAFT_TYPE_CD = '2'         
LEFT OUTER JOIN PNA_CUST_ORD_DTL CCUST ON A.CUST_ORDR_NO = CCUST.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = CCUST.CUST_ORDR_VER_NO AND A.PROD_ORDR_SEQ = CCUST.PROD_ORDR_SEQ AND CCUST.CHG_BEFAFT_TYPE_CD = '1'         
WHERE 1 = 1
AND A.OBDNG_ID = 'R01465'
AND D.TRT_RESLT = 'WAIT'
AND D.LAST_CHG_DT < SYSDATE
ORDER BY D.FIRST_CRET_DT;

위 쿼리의 실행계획을 보면 PNA_NGOSIIF_TXN 테이블에서 2건의 Full Scan을 확인하실 수 있습니다. ( TABLE ACCESS FULL, PNA_NGOSIIF_TXN )

Full Scan은 조회조건에 해당하는 인덱스가 생성되어있지 않아서, 조건에 맞는 row를 찾기 위해 테이블 전체 값을 조회하는 것을 의미합니다. 테이블 전체 Block에 접근하기 때문에 실행계획상의 Cost도 많이 발생하는 것을 확인하실 수 있습니다.

해당 Full Scan을 개선하면 Cost가 감소되어 쿼리 성능이 크게 향상될 것으로 예상되는 상황입니다.

B. 성능개선

인덱스 추가

첫 시도는 인덱스를 추가하여 Full Scan이 아닌 인덱스 스캔이 진행되도록 개선하는 것이었습니다.

추가 인덱스는 아래와 같습니다.

CREATE INDEX CUI_OWN.PNA_NGOSIIF_TXN_IX_04 ON CUI_OWN.PNA_NGOSIIF_TXN (OBDNG_ID ASC, TRT_RESLT ASC, LAST_CHG_DT ASC);
CREATE INDEX CUI_OWN.PNA_NGOSIIF_TXN_IX_05 ON CUI_OWN.PNA_NGOSIIF_TXN (CUST_ORDR_NO ASC, CUST_ORDR_VER_NO ASC, NGOSI_TYPE_CD ASC, ORDR_TYPE_ID ASC);

그리고 운영환경 상에서 실제 성능개선 효과가 있는지 인프라팀이 확인을 요청하였습니다. 결과는 아래와 같습니다.
Buffer Gets : 63948 → 55962

• 인덱스 반영 후 실행계획

  =====================================================================================================================================================================
  | Id |               Operation               |         Name          |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   |  Mem  | Activity | Activity Detail |
  |    |                                       |                       | (Estim) |       | Active(s) | Active |       | (Actual) | (Max) |   (%)    |   (# samples)   |
  =====================================================================================================================================================================
  |  0 | SELECT STATEMENT                      |                       |         |       |         1 |     +0 |     1 |        6 |       |          |                 |
  |  1 |   SORT ORDER BY                       |                       |    9174 | 14136 |         1 |     +0 |     1 |        6 |  2048 |          |                 |
  |  2 |    HASH UNIQUE                        |                       |    9174 | 13421 |         1 |     +0 |     1 |        6 |    2M |          |                 |
  |  3 |     NESTED LOOPS                      |                       |    9174 | 12707 |         1 |     +0 |     1 |        6 |       |          |                 |
  |  4 |      NESTED LOOPS OUTER               |                       |    9174 | 12706 |         1 |     +0 |     1 |        6 |       |          |                 |
  |  5 |       NESTED LOOPS OUTER              |                       |    9174 | 10870 |         1 |     +0 |     1 |        6 |       |          |                 |
  |  6 |        HASH JOIN                      |                       |    9174 |  9035 |         1 |     +0 |     1 |        6 |    1M |          |                 |
  |  7 |         NESTED LOOPS                  |                       |    9174 |  5023 |         1 |     +0 |     1 |       64 |       |          |                 |
  |  8 |          NESTED LOOPS                 |                       |    9175 |  5023 |         1 |     +0 |     1 |       68 |       |          |                 |
  |  9 |           TABLE ACCESS BY INDEX ROWID | PNA_ORTR_DTL          |    9175 |  1352 |         1 |     +0 |     1 |    12134 |       |          |                 |
  | 10 |            INDEX RANGE SCAN           | PNA_ORTR_DTL_IX_03    |    9175 |    15 |         1 |     +0 |     1 |    12134 |       |          |                 |
  | 11 |           INDEX RANGE SCAN            | PNA_NGOSIIF_TXN_IX_05 |       1 |     1 |         1 |     +0 | 12134 |       68 |       |          |                 |
  | 12 |          TABLE ACCESS BY INDEX ROWID  | PNA_NGOSIIF_TXN       |       1 |     1 |         1 |     +0 |    68 |       64 |       |          |                 |
  | 13 |         TABLE ACCESS FULL             | PNA_NGOSIIF_TXN       |    269K |  4011 |         1 |     +0 |     1 |    20973 |       |          |                 |
  | 14 |        TABLE ACCESS BY INDEX ROWID    | PNA_CUST_ORD_DTL      |       1 |     1 |         1 |     +0 |     6 |        6 |       |          |                 |
  | 15 |         INDEX UNIQUE SCAN             | PNA_CUST_ORD_DTL_PK   |       1 |     1 |         1 |     +0 |     6 |        6 |       |          |                 |
  | 16 |       TABLE ACCESS BY INDEX ROWID     | PNA_CUST_ORD_DTL      |       1 |     1 |         1 |     +0 |     6 |        6 |       |          |                 |
  | 17 |        INDEX UNIQUE SCAN              | PNA_CUST_ORD_DTL_PK   |       1 |     1 |         1 |     +0 |     6 |        6 |       |          |                 |
  | 18 |      INDEX UNIQUE SCAN                | PNA_PROD_ORD_BAS_PK   |       1 |     1 |         1 |     +0 |     6 |        6 |       |          |                 |
  =====================================================================================================================================================================

보시는 것처럼 Buffer Gets 상으로 개선효과는 있으나 미미한 상태로 확인되었습니다.
뿐만 아니라 추가 적용한 PNA_NGOSIIF_TXN_IX_05 인덱스는 SCAN이 진행되지만, PNA_NGOSIIF_TXN_IX_04 인덱스는 스캔이 안되고 있었습니다.

4번 인덱스 스캔이 안되고 있는 이유는 해당 쿼리가 대상유무를 조회하는 쿼리이기 때문에
대상이 없을 경우, 인덱스 스캔이 아닌 테이블 Full Scan으로 진행하는 것이 성능상 더 낫다고 DB Optimizer가 판단했을 가능성이 큽니다.

위와 같은 결과로 인해 인덱스 추가로인한 조회성능 향상보단 INSERT/UPDATE/DELETE 성능의 하락이 더 클 것으로 예상이되어
인덱스 추가로 인한 성능개선은 반려하는 것으로 결정하였습니다.

불필요 JOIN문 제거

비즈니스 로직을 들여다봐야할 때라고 생각되었습니다.
그리고 PNA_NGOSIIF_TXN 테이블 JOIN이 두번이나 필요한가?에 대해 고민하기 시작했습니다.
PNA_NGOSIIF_TXN 테이블에 대한 JOIN문으로 아래 두 개의 JOIN문이 있습니다.

INNER JOIN PNA_NGOSIIF_TXN C ON A.CUST_ORDR_NO = C.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = C.CUST_ORDR_VER_NO AND C.NGOSI_TYPE_CD = 'R01' AND C.TRT_RESLT = 'OK'
INNER JOIN PNA_NGOSIIF_TXN D ON A.CUST_ORDR_NO = D.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = D.CUST_ORDR_VER_NO AND A.ORDR_TYPE_ID = D.ORDR_TYPE_ID

첫 번째 JOIN문은 PNA_NGOSIIF_TXN 테이블의 NGOSI_TYPE_CD = 'R01' AND C.TRT_RESLT = 'OK' 조건에 부합하는 CUST_ORDR_NO를 뽑아내기 위한 JOIN문 이구요.
두 번째 JOIN문은 단순 PNA_NGOSIIF_TXN 테이블 JOIN문으로써, 위 첫번째 JOIN문으로 뽑아낸 CUST_ORDR_NO를 대상으로 JOIN을 수행하는 구문입니다.

하지만 중복되는 부분이 있었습니다.
아래 WHERE 절을 보면, 아래와 같은 조건이 있습니다.

AND D.TRT_RESLT = 'WAIT'
해당 조건은 비즈니스로직상 C.NGOSI_TYPE_CD = 'R01' AND C.TRT_RESLT = 'OK' 조건에 부합되는 CUST_ORDR_NO에 대해 모두 설정되어있는 조건들입니다.

그래서 위 첫번째 JOIN문의 조건과 중복되는 조건이라고 할 수 있는데요.
이를 AP코드상으로 확인을 했고, 해당하는 첫번째 JOIN문이 불필요한 JOIN문이라고 판단되어 제외하기로 결정하였습니다.

또한, 중복을 제거하는 구문인 DISTINCT역시 제외하기로 결정하였습니다.
PNA_NGOSIIF_TXN 테이블의 PK인 NGOSI_SEQ 컬럼이 출력대상 컬럼에 포함되어있기 때문입니다.

• 개선후 쿼리

  SELECT D.NGOSI_SEQ,
        D.ORDR_TRT_NO,
        D.OBDNG_ID,
        D.CUST_ORDR_NO,
        D.CUST_ORDR_VER_NO,
        D.SVC_CONT_ID,
        D.ORDR_TYPE_ID,
        D.FIRST_CRET_DT,
        D.ORDR_TRT_TYPE_SEQ,
        TO_CHAR(D.ORDR_RCP_DATE,'YYYY-MM-DD HH24:MI:SS') AS ORDR_RCP_DATE,
        D.NGOSI_TYPE_CD,
        D.TRT_RESLT,
        D.TRT_RESLT_SBST,
        TO_CHAR(D.LAST_CHG_DT,'YYYY-MM-DD HH24:MI:SS') AS LAST_CHG_DT,
        COALESCE(NCUST.IST_CNTPLC_TEL_NO, NCUST.GENL_TEL_NO, NCUST.GENL_2_TEL_NO, NCUST.GENL_3_TEL_NO, NCUST.GENL_4_TEL_NO, CCUST.IST_CNTPLC_TEL_NO, CCUST.GENL_TEL_NO, CCUST.GENL_2_TEL_NO, CCUST.GENL_3_TEL_NO, CCUST.GENL_4_TEL_NO) AS GENL_TEL_NO
  FROM PNA_ORTR_DTL A
  INNER JOIN PNA_PROD_ORD_BAS BAS ON A.CUST_ORDR_NO = BAS.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = BAS.CUST_ORDR_VER_NO AND A.PROD_ORDR_SEQ = BAS.PROD_ORDR_SEQ
  INNER JOIN PNA_NGOSIIF_TXN D ON A.CUST_ORDR_NO = D.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = D.CUST_ORDR_VER_NO AND A.ORDR_TYPE_ID = D.ORDR_TYPE_ID
  LEFT OUTER JOIN PNA_CUST_ORD_DTL NCUST ON A.CUST_ORDR_NO = NCUST.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = NCUST.CUST_ORDR_VER_NO AND A.PROD_ORDR_SEQ = NCUST.PROD_ORDR_SEQ AND NCUST.CHG_BEFAFT_TYPE_CD = '2'
  LEFT OUTER JOIN PNA_CUST_ORD_DTL CCUST ON A.CUST_ORDR_NO = CCUST.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = CCUST.CUST_ORDR_VER_NO AND A.PROD_ORDR_SEQ = CCUST.PROD_ORDR_SEQ AND CCUST.CHG_BEFAFT_TYPE_CD = '1'
  WHERE 1=1
  AND A.OBDNG_ID = 'R00439'
  AND D.TRT_RESLT = 'WAIT'
  AND D.LAST_CHG_DT < SYSDATE
  ORDER BY D.FIRST_CRET_DT;

그리고 아래와 같은 개선효과를 볼 수 있었습니다.

Plan hash value: 798785828

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                     |  9175 |  3010K|       | 10520   (1)| 00:00:01 |
|*  1 |  FILTER                           |                     |       |       |       |            |          |
|   2 |   SORT ORDER BY                   |                     |  9175 |  3010K|  3200K| 10520   (1)| 00:00:01 |
|   3 |    NESTED LOOPS                   |                     |  9175 |  3010K|       |  9860   (1)| 00:00:01 |
|   4 |     NESTED LOOPS OUTER            |                     |  9175 |  2652K|       |  9859   (1)| 00:00:01 |
|   5 |      NESTED LOOPS OUTER           |                     |  9175 |  1980K|       |  8023   (1)| 00:00:01 |
|*  6 |       HASH JOIN                   |                     |  9175 |  1308K|       |  6187   (1)| 00:00:01 |
|   7 |        TABLE ACCESS BY INDEX ROWID| PNA_ORTR_DTL        |  9175 |   447K|       |  1352   (1)| 00:00:01 |
|*  8 |         INDEX RANGE SCAN          | PNA_ORTR_DTL_IX_03  |  9175 |       |       |    15   (0)| 00:00:01 |
|*  9 |        TABLE ACCESS FULL          | PNA_NGOSIIF_TXN     |   325K|    29M|       |  4834   (1)| 00:00:01 |
|  10 |       TABLE ACCESS BY INDEX ROWID | PNA_CUST_ORD_DTL    |     1 |    75 |       |     1   (0)| 00:00:01 |
|* 11 |        INDEX UNIQUE SCAN          | PNA_CUST_ORD_DTL_PK |     1 |       |       |     1   (0)| 00:00:01 |
|  12 |      TABLE ACCESS BY INDEX ROWID  | PNA_CUST_ORD_DTL    |     1 |    75 |       |     1   (0)| 00:00:01 |
|* 13 |       INDEX UNIQUE SCAN           | PNA_CUST_ORD_DTL_PK |     1 |       |       |     1   (0)| 00:00:01 |
|* 14 |     INDEX UNIQUE SCAN             | PNA_PROD_ORD_BAS_PK |     1 |    40 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------


......
......
......

실행계획 Cost상으로 16119 → 10520, 5299의 개선효과를 확인하였습니다.
그리고 쿼리 조건이 바뀌었기 때문에 아래와 같은 조건으로 테스트를 진행하여 개선전/후 쿼리에서 추출되는 row의 갯수와 값들을 비교하였습니다.

• 단수개의 국사조건, LAST_CHG_DT : sysdate + 1
• 단수개의 국사조건, LAST_CHG_DT : sysdate + 10
• 복수개의 국사조건, LAST_CHG_DT : sysdate + 1
• 복수개의 국사조건, LAST_CHG_DT : sysdate + 10
  그리고 SMS발송 및 연관로직들에 대한 테스트도 별도로 진행되어 이상없음을 확인하고 5월 정기적용 진행하였습니다. 이때는 이렇게 행복하게 마무리되는줄 알았습니다.
  
  

국사조건 추가

6월 어느날, TOP 쿼리 list를 또 받았습니다. 위 무출동 쿼리의 2연패였습니다..
하지만 Buffer Gets상으로 다소 개선되었음을 확인하였습니다.

Total Buffer Gets는 448,510,521 → 403,747,191로 감소하였고, 1회 실행당 Buffer Gets를 나타내는 Gets per Exec는 52,884 → 38.103으로 감소하였음을 확인하였습니다.
해당 쿼리의 실행회수가 이전보다 증가되어 Total Buffer Gets상으로는 개선효과가 미미하다고 판단되었으나, Gets per Exec상으로는 적지않은 개선효과가 있었다는 것을 확인하였습니다.
하지만 추가적인 개선이 필요했습니다.

이번에도 역시 Full Scan에 포커싱을 하였습니다. 그리고 안보이던게 보이기 시작했습니다. ( 한창 SQLD 공부하던 시절... )
주테이블인 PNA_ORTR_DTL 테이블엔 국사조건( OBDNG_ID )이 있는데, PNA_NGOSIIF_TXN 테이블에도 똑같이 OBDNG_ID 컬럼이 있음에도 불구하고 동일한 조건이 없는 것이었습니다.
그리고 PNA_NGOSIIF_TXN 테이블에도 OBDNG_ID 컬럼 조건의 인덱스가 있었습니다.
OBDNG_ID 조건을 아래와 같이 PNA_NGOSIIF_TXN 테이블에도 추가하여 실행계획을 확인하였습니다.

SELECT D.NGOSI_SEQ,
        D.ORDR_TRT_NO,
        D.OBDNG_ID,
        D.CUST_ORDR_NO,
        D.CUST_ORDR_VER_NO,
        D.SVC_CONT_ID,
        D.ORDR_TYPE_ID,
        D.FIRST_CRET_DT,
        D.ORDR_TRT_TYPE_SEQ,
        TO_CHAR(D.ORDR_RCP_DATE,'YYYY-MM-DD HH24:MI:SS') AS ORDR_RCP_DATE,
        D.NGOSI_TYPE_CD,
        D.TRT_RESLT,
        D.TRT_RESLT_SBST,
        TO_CHAR(D.LAST_CHG_DT,'YYYY-MM-DD HH24:MI:SS') AS LAST_CHG_DT,
        COALESCE(NCUST.IST_CNTPLC_TEL_NO, NCUST.GENL_TEL_NO, NCUST.GENL_2_TEL_NO, NCUST.GENL_3_TEL_NO, NCUST.GENL_4_TEL_NO, CCUST.IST_CNTPLC_TEL_NO, CCUST.GENL_TEL_NO, CCUST.GENL_2_TEL_NO, CCUST.GENL_3_TEL_NO, CCUST.GENL_4_TEL_NO) AS GENL_TEL_NO
FROM PNA_ORTR_DTL A
INNER JOIN PNA_PROD_ORD_BAS BAS ON A.CUST_ORDR_NO = BAS.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = BAS.CUST_ORDR_VER_NO AND A.PROD_ORDR_SEQ = BAS.PROD_ORDR_SEQ
INNER JOIN PNA_NGOSIIF_TXN D ON A.CUST_ORDR_NO = D.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = D.CUST_ORDR_VER_NO AND A.ORDR_TYPE_ID = D.ORDR_TYPE_ID
LEFT OUTER JOIN PNA_CUST_ORD_DTL NCUST ON A.CUST_ORDR_NO = NCUST.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = NCUST.CUST_ORDR_VER_NO AND A.PROD_ORDR_SEQ = NCUST.PROD_ORDR_SEQ AND NCUST.CHG_BEFAFT_TYPE_CD = '2'
LEFT OUTER JOIN PNA_CUST_ORD_DTL CCUST ON A.CUST_ORDR_NO = CCUST.CUST_ORDR_NO AND A.CUST_ORDR_VER_NO = CCUST.CUST_ORDR_VER_NO AND A.PROD_ORDR_SEQ = CCUST.PROD_ORDR_SEQ AND CCUST.CHG_BEFAFT_TYPE_CD = '1'
WHERE 1=1
AND A.OBDNG_ID = 'R00439'
AND D.OBDNG_ID = 'R00439'
AND D.TRT_RESLT = 'WAIT'
AND D.LAST_CHG_DT < SYSDATE
ORDER BY D.FIRST_CRET_DT;

아래는 실행계획입니다.

Plan hash value: 3620360198

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                       |   775 |   254K|   916   (1)| 00:00:01 |
|*  1 |  FILTER                           |                       |       |       |            |          |
|   2 |   SORT ORDER BY                   |                       |   775 |   254K|   916   (1)| 00:00:01 |
|   3 |    NESTED LOOPS OUTER             |                       |   775 |   254K|   915   (1)| 00:00:01 |
|   4 |     NESTED LOOPS OUTER            |                       |   775 |   197K|   760   (1)| 00:00:01 |
|   5 |      NESTED LOOPS                 |                       |   775 |   140K|   605   (1)| 00:00:01 |
|   6 |       NESTED LOOPS                |                       |   775 |   110K|   604   (1)| 00:00:01 |
|*  7 |        TABLE ACCESS BY INDEX ROWID| PNA_NGOSIIF_TXN       |   775 | 74400 |   293   (0)| 00:00:01 |
|*  8 |         INDEX RANGE SCAN          | PNA_NGOSIIF_TXN_IX_02 |  3101 |       |     5   (0)| 00:00:01 |
|*  9 |        TABLE ACCESS BY INDEX ROWID| PNA_ORTR_DTL          |     1 |    50 |     1   (0)| 00:00:01 |
|* 10 |         INDEX RANGE SCAN          | PNA_ORTR_DTL_IX_01    |     1 |       |     1   (0)| 00:00:01 |
|* 11 |       INDEX UNIQUE SCAN           | PNA_PROD_ORD_BAS_PK   |     1 |    40 |     1   (0)| 00:00:01 |
|  12 |      TABLE ACCESS BY INDEX ROWID  | PNA_CUST_ORD_DTL      |     1 |    75 |     1   (0)| 00:00:01 |
|* 13 |       INDEX UNIQUE SCAN           | PNA_CUST_ORD_DTL_PK   |     1 |       |     1   (0)| 00:00:01 |
|  14 |     TABLE ACCESS BY INDEX ROWID   | PNA_CUST_ORD_DTL      |     1 |    75 |     1   (0)| 00:00:01 |
|* 15 |      INDEX UNIQUE SCAN            | PNA_CUST_ORD_DTL_PK   |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------


......
......
......

결과는 성공이었습니다.
OBDNG_ID 조건을 하나 추가했을 뿐인데, Full Scan이 아닌 인덱스 스캔으로 유도할 수 있었고, Cost를 크게 감소시켰습니다. ( 10520 → 916 )
그리고 6월 정기 반영 후 인프라팀 확인 결과, Gets per Exec 값도 38.103 → 2,350 으로 크게 개선됨을 확인하였습니다.

하지만 쿼리 수행 빈도수는 여전히 높아서 Total Buffer Gets 수치도 여전히 높을 수 있다고 하였습니다.
추후 TOP 쿼리로 해당쿼리가 또 대상으로 잡히면, 쿼리 성능이 아닌 쿼리 수행 빈도수에 포커싱을 할 예정입니다.

C. 배운 점

배운점은 쿼리 개선방안을 "멀리서 찾지말고 가까이서 찾자." 입니다.

비효율 쿼리가 보이면, 인덱스를 추가하고 비즈니스 로직을 보기 전에 아래처럼 기본에 충실한 쿼리인지 먼저 확인할 것입니다.
1.실행계획 확인을 통한 비효율 포인트 파악
2.기존 생성되어 있는 인덱스가 적극 활용되고 있는지? => 이 부분 확인이 선행되지 않아서, 실질적인 쿼리개선까지 많이 돌아왔습니다.
3.쿼리 특징 파악 => 쿼리 특징에 따라 인덱스 추가 여부 결정
4.비즈니스 파악 => 중복 조건이 있는지? 요구사항이 과하게 담겨있는 쿼리인지 등 파악

2. 번외

최근 번호이동지연명령UI 조회쿼리에서 비효율이 발견되었습니다.

결론적으로는 해당 쿼리가 과한 쿼리로 판단되어 두 개의 쿼리로 분리한 후, Full Scan이 일어났던 부분을 단건조회로 전환하는 것을 통해 개선하였지만,
위 쿼리튜닝기에서처럼 조건을 추가하여 쿼리 개선한 결과를 공유드립니다.

• As-Is

Plan hash value: 924581685

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                       |     1 |    13 |       |   164K  (2)| 00:00:09 |
|   1 |  SORT AGGREGATE                           |                       |     1 |    13 |       |            |          |
|   2 |   VIEW                                    |                       |     2 |    26 |       |   164K  (2)| 00:00:09 |
|   3 |    UNION-ALL                              |                       |       |       |       |            |          |
|   4 |     SORT AGGREGATE                        |                       |     1 |   271 |       |            |          |
|*  5 |      HASH JOIN                            |                       |  5760 |  1524K|       | 82309   (2)| 00:00:05 |
|   6 |       NESTED LOOPS OUTER                  |                       |     2 |   528 |       |   240   (1)| 00:00:01 |
|   7 |        NESTED LOOPS OUTER                 |                       |     2 |   444 |       |   239   (1)| 00:00:01 |
|   8 |         NESTED LOOPS OUTER                |                       |     2 |   360 |       |   238   (1)| 00:00:01 |
|   9 |          NESTED LOOPS OUTER               |                       |     2 |   344 |       |   237   (1)| 00:00:01 |
|  10 |           NESTED LOOPS OUTER              |                       |     2 |   316 |       |   236   (1)| 00:00:01 |
|  11 |            NESTED LOOPS ANTI              |                       |     2 |   288 |       |   235   (1)| 00:00:01 |
|  12 |             NESTED LOOPS                  |                       |   168 | 21336 |       |   201   (1)| 00:00:01 |
|  13 |              NESTED LOOPS                 |                       |   168 | 14616 |       |   200   (1)| 00:00:01 |
|  14 |               INLIST ITERATOR             |                       |       |       |       |            |          |
|* 15 |                INDEX UNIQUE SCAN          | TB_OFFICE_PK          |    38 |   266 |       |     1   (0)| 00:00:01 |
|  16 |               INLIST ITERATOR             |                       |       |       |       |            |          |
|* 17 |                TABLE ACCESS BY INDEX ROWID| PNA_ORTR_DTL          |     4 |   320 |       |     5   (0)| 00:00:01 |
|* 18 |                 INDEX RANGE SCAN          | PNA_ORTR_DTL_UK_01    |    13 |       |       |     3   (0)| 00:00:01 |
|* 19 |              INDEX UNIQUE SCAN            | PNA_PROD_ORD_BAS_PK   |     1 |    40 |       |     1   (0)| 00:00:01 |
|* 20 |             TABLE ACCESS BY INDEX ROWID   | PNA_ORTR_PRCS_DTL     |   172K|  2861K|       |     1   (0)| 00:00:01 |
|* 21 |              INDEX UNIQUE SCAN            | PNA_ORTR_PRCS_DTL_PK  |     1 |       |       |     1   (0)| 00:00:01 |
|* 22 |            INDEX UNIQUE SCAN              | PNA_ORTR_TELNO_TXN_PK |     1 |    14 |       |     1   (0)| 00:00:01 |
|* 23 |           INDEX UNIQUE SCAN               | PNA_ORTR_TELNO_TXN_PK |     1 |    14 |       |     1   (0)| 00:00:01 |
|* 24 |          INDEX UNIQUE SCAN                | TB_SVCTYPECODE_PK     |     1 |     8 |       |     1   (0)| 00:00:01 |
|* 25 |         INDEX UNIQUE SCAN                 | PNA_CUST_ORD_DTL_PK   |     1 |    42 |       |     1   (0)| 00:00:01 |
|* 26 |        INDEX UNIQUE SCAN                  | PNA_CUST_ORD_DTL_PK   |     1 |    42 |       |     1   (0)| 00:00:01 |
|  27 |       VIEW                                |                       |   451K|  3089K|       | 82067   (2)| 00:00:05 |
|  28 |        SORT GROUP BY                      |                       |   451K|    25M|       | 82067   (2)| 00:00:05 |
|  29 |         VIEW                              |                       |   879K|    50M|       | 82067   (2)| 00:00:05 |
|* 30 |          FILTER                           |                       |       |       |       |            |          |
|  31 |           WINDOW SORT                     |                       |   879K|    44M|    60M| 82067   (2)| 00:00:05 |
|  32 |            VIEW                           |                       |   879K|    44M|       | 70575   (2)| 00:00:04 |
|  33 |             UNION-ALL                     |                       |       |       |       |            |          |
|* 34 |              TABLE ACCESS FULL            | PNA_ORTR_PRCS_DTL     |   399K|    12M|       | 50861   (2)| 00:00:03 |
|* 35 |              TABLE ACCESS FULL            | PNA_ORTR_SBPRCS_DTL   |   479K|    15M|       | 19714   (3)| 00:00:02 |
|  36 |     SORT AGGREGATE                        |                       |     1 |   271 |       |            |          |
|* 37 |      HASH JOIN                            |                       |  5760 |  1524K|       | 82209   (2)| 00:00:05 |
|  38 |       NESTED LOOPS OUTER                  |                       |     1 |   264 |       |   140   (0)| 00:00:01 |
|  39 |        NESTED LOOPS OUTER                 |                       |     1 |   222 |       |   139   (0)| 00:00:01 |
|  40 |         NESTED LOOPS OUTER                |                       |     1 |   180 |       |   138   (0)| 00:00:01 |
|  41 |          NESTED LOOPS OUTER               |                       |     1 |   172 |       |   137   (0)| 00:00:01 |
|  42 |           NESTED LOOPS OUTER              |                       |     1 |   158 |       |   136   (0)| 00:00:01 |
|  43 |            NESTED LOOPS ANTI              |                       |     1 |   144 |       |   135   (0)| 00:00:01 |
|  44 |             NESTED LOOPS                  |                       |    84 | 10668 |       |   102   (1)| 00:00:01 |
|  45 |              NESTED LOOPS                 |                       |    84 |  7308 |       |   101   (1)| 00:00:01 |
|  46 |               INLIST ITERATOR             |                       |       |       |       |            |          |
|* 47 |                INDEX UNIQUE SCAN          | TB_OFFICE_PK          |    38 |   266 |       |     1   (0)| 00:00:01 |
|* 48 |               TABLE ACCESS BY INDEX ROWID | PNA_ORTR_DTL          |     2 |   160 |       |     3   (0)| 00:00:01 |
|* 49 |                INDEX RANGE SCAN           | PNA_ORTR_DTL_UK_01    |     6 |       |       |     1   (0)| 00:00:01 |
|* 50 |              INDEX UNIQUE SCAN            | PNA_PROD_ORD_BAS_PK   |     1 |    40 |       |     1   (0)| 00:00:01 |
|* 51 |             TABLE ACCESS BY INDEX ROWID   | PNA_ORTR_PRCS_DTL     |   331K|  5503K|       |     1   (0)| 00:00:01 |
|* 52 |              INDEX RANGE SCAN             | PNA_ORTR_PRCS_DTL_PK  |     1 |       |       |     1   (0)| 00:00:01 |
|* 53 |            INDEX UNIQUE SCAN              | PNA_ORTR_TELNO_TXN_PK |     1 |    14 |       |     1   (0)| 00:00:01 |
|* 54 |           INDEX UNIQUE SCAN               | PNA_ORTR_TELNO_TXN_PK |     1 |    14 |       |     1   (0)| 00:00:01 |
|* 55 |          INDEX UNIQUE SCAN                | TB_SVCTYPECODE_PK     |     1 |     8 |       |     1   (0)| 00:00:01 |
|* 56 |         INDEX UNIQUE SCAN                 | PNA_CUST_ORD_DTL_PK   |     1 |    42 |       |     1   (0)| 00:00:01 |
|* 57 |        INDEX UNIQUE SCAN                  | PNA_CUST_ORD_DTL_PK   |     1 |    42 |       |     1   (0)| 00:00:01 |
|  58 |       VIEW                                |                       |   451K|  3089K|       | 82067   (2)| 00:00:05 |
|  59 |        SORT GROUP BY                      |                       |   451K|    25M|       | 82067   (2)| 00:00:05 |
|  60 |         VIEW                              |                       |   879K|    50M|       | 82067   (2)| 00:00:05 |
|* 61 |          FILTER                           |                       |       |       |       |            |          |
|  62 |           WINDOW SORT                     |                       |   879K|    44M|    60M| 82067   (2)| 00:00:05 |
|  63 |            VIEW                           |                       |   879K|    44M|       | 70575   (2)| 00:00:04 |
|  64 |             UNION-ALL                     |                       |       |       |       |            |          |
|* 65 |              TABLE ACCESS FULL            | PNA_ORTR_PRCS_DTL     |   399K|    12M|       | 50861   (2)| 00:00:03 |
|* 66 |              TABLE ACCESS FULL            | PNA_ORTR_SBPRCS_DTL   |   479K|    15M|       | 19714   (3)| 00:00:02 |
---------------------------------------------------------------------------------------------------------------------------

• To-Be

Plan hash value: 949097900

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                        |  1137 |  3185K|       | 59793   (1)| 00:00:04 |
|   1 |  UNION-ALL                                     |                        |       |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID                  | TB_COMMON_CD_BAS       |     1 |    26 |       |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN                           | TB_COMMON_CD_BAS_PK    |     1 |       |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID                  | TB_COMMON_CD_BAS       |     1 |    26 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN                           | TB_COMMON_CD_BAS_PK    |     1 |       |       |     1   (0)| 00:00:01 |
|*  6 |   HASH JOIN RIGHT OUTER                        |                        |   588 |   663K|       | 24637   (1)| 00:00:02 |
|   7 |    TABLE ACCESS FULL                           | PNA_ORDRPRCSSERR_CTG   |   710 |   126K|       |    12   (0)| 00:00:01 |
|*  8 |    HASH JOIN RIGHT OUTER                       |                        |   588 |   558K|       | 24625   (1)| 00:00:02 |
|   9 |     TABLE ACCESS FULL                          | PNA_ORDRPRCSSERR_CTG   |   710 |   126K|       |    12   (0)| 00:00:01 |
|* 10 |     HASH JOIN RIGHT OUTER                      |                        |   588 |   453K|       | 24613   (1)| 00:00:02 |
|  11 |      TABLE ACCESS FULL                         | PNA_ORDRPRCSSERR_CTG   |   710 |   126K|       |    12   (0)| 00:00:01 |
|* 12 |      HASH JOIN RIGHT OUTER                     |                        |   588 |   347K|       | 24601   (1)| 00:00:02 |
|  13 |       TABLE ACCESS FULL                        | PNA_ORDRPRCSSTYPE_CTG  |    23 |   483 |       |    12   (0)| 00:00:01 |
|* 14 |       HASH JOIN RIGHT OUTER                    |                        |   588 |   335K|       | 24589   (1)| 00:00:02 |
|  15 |        TABLE ACCESS FULL                       | PNA_ORDRPRCSSTYPE_CTG  |    23 |   483 |       |    12   (0)| 00:00:01 |
|* 16 |        HASH JOIN RIGHT OUTER                   |                        |   588 |   323K|       | 24577   (1)| 00:00:02 |
|  17 |         TABLE ACCESS FULL                      | PNA_ORDRPRCSSTYPE_CTG  |    23 |   483 |       |    12   (0)| 00:00:01 |
|* 18 |         HASH JOIN RIGHT OUTER                  |                        |   588 |   311K|       | 24565   (1)| 00:00:02 |
|  19 |          TABLE ACCESS FULL                     | PNA_ORDRPRCSSTYPE_CTG  |    23 |   483 |       |    12   (0)| 00:00:01 |
|* 20 |          HASH JOIN                             |                        |   588 |   299K|       | 24553   (1)| 00:00:02 |
|  21 |           NESTED LOOPS OUTER                   |                        |     2 |   890 |       |  3744   (1)| 00:00:01 |
|  22 |            NESTED LOOPS                        |                        |     2 |   806 |       |  3743   (1)| 00:00:01 |
|  23 |             NESTED LOOPS OUTER                 |                        |     2 |   772 |       |  3742   (1)| 00:00:01 |
|  24 |              NESTED LOOPS OUTER                |                        |     2 |   702 |       |  3741   (1)| 00:00:01 |
|  25 |               NESTED LOOPS ANTI                |                        |     2 |   632 |       |  3740   (1)| 00:00:01 |
|  26 |                NESTED LOOPS OUTER              |                        |   168 | 50232 |       |  3706   (1)| 00:00:01 |
|  27 |                 NESTED LOOPS OUTER             |                        |   168 | 39816 |       |  3673   (1)| 00:00:01 |
|  28 |                  NESTED LOOPS                  |                        |   168 | 29400 |       |  3639   (1)| 00:00:01 |
|  29 |                   INLIST ITERATOR              |                        |       |       |       |            |          |
|* 30 |                    TABLE ACCESS BY INDEX ROWID | PNA_ORTR_DTL           |   168 | 20664 |       |  3605   (1)| 00:00:01 |
|* 31 |                     INDEX RANGE SCAN           | PNA_ORTR_DTL_IX_06     | 17432 |       |       |   732   (1)| 00:00:01 |
|  32 |                   TABLE ACCESS BY INDEX ROWID  | PNA_PROD_ORD_BAS       |     1 |    52 |       |     1   (0)| 00:00:01 |
|* 33 |                    INDEX UNIQUE SCAN           | PNA_PROD_ORD_BAS_PK    |     1 |       |       |     1   (0)| 00:00:01 |
|  34 |                  TABLE ACCESS BY INDEX ROWID   | PNA_CUST_ORD_DTL       |     1 |    62 |       |     1   (0)| 00:00:01 |
|* 35 |                   INDEX UNIQUE SCAN            | PNA_CUST_ORD_DTL_PK    |     1 |       |       |     1   (0)| 00:00:01 |
|  36 |                 TABLE ACCESS BY INDEX ROWID    | PNA_CUST_ORD_DTL       |     1 |    62 |       |     1   (0)| 00:00:01 |
|* 37 |                  INDEX UNIQUE SCAN             | PNA_CUST_ORD_DTL_PK    |     1 |       |       |     1   (0)| 00:00:01 |
|* 38 |                TABLE ACCESS BY INDEX ROWID     | PNA_ORTR_PRCS_DTL      |   172K|  2861K|       |     1   (0)| 00:00:01 |
|* 39 |                 INDEX UNIQUE SCAN              | PNA_ORTR_PRCS_DTL_PK   |     1 |       |       |     1   (0)| 00:00:01 |
|  40 |               TABLE ACCESS BY INDEX ROWID      | PNA_ORTR_TELNO_TXN     |     1 |    35 |       |     1   (0)| 00:00:01 |
|* 41 |                INDEX UNIQUE SCAN               | PNA_ORTR_TELNO_TXN_PK  |     1 |       |       |     1   (0)| 00:00:01 |
|  42 |              TABLE ACCESS BY INDEX ROWID       | PNA_ORTR_TELNO_TXN     |     1 |    35 |       |     1   (0)| 00:00:01 |
|* 43 |               INDEX UNIQUE SCAN                | PNA_ORTR_TELNO_TXN_PK  |     1 |       |       |     1   (0)| 00:00:01 |
|  44 |             TABLE ACCESS BY INDEX ROWID        | TB_OFFICE              |     1 |    17 |       |     1   (0)| 00:00:01 |
|* 45 |              INDEX UNIQUE SCAN                 | TB_OFFICE_PK           |     1 |       |       |     1   (0)| 00:00:01 |
|  46 |            TABLE ACCESS BY INDEX ROWID         | TB_SVCTYPECODE         |     1 |    42 |       |     1   (0)| 00:00:01 |
|* 47 |             INDEX UNIQUE SCAN                  | TB_SVCTYPECODE_PK      |     1 |       |       |     1   (0)| 00:00:01 |
|  48 |           VIEW                                 |                        | 46114 |  3467K|       | 20809   (1)| 00:00:02 |
|  49 |            SORT GROUP BY                       |                        | 46114 |  2701K|       | 20809   (1)| 00:00:02 |
|  50 |             VIEW                               |                        | 46114 |  2701K|       | 20809   (1)| 00:00:02 |
|* 51 |              FILTER                            |                        |       |       |       |            |          |
|  52 |               WINDOW SORT                      |                        | 46114 |  2521K|  3456K| 20809   (1)| 00:00:02 |
|  53 |                VIEW                            |                        | 46114 |  2521K|       | 20177   (1)| 00:00:02 |
|  54 |                 UNION-ALL                      |                        |       |       |       |            |          |
|  55 |                  NESTED LOOPS                  |                        | 12176 |   737K|       |  7726   (1)| 00:00:01 |
|  56 |                   NESTED LOOPS                 |                        | 13732 |   737K|       |  7726   (1)| 00:00:01 |
|  57 |                    INLIST ITERATOR             |                        |       |       |       |            |          |
|* 58 |                     TABLE ACCESS BY INDEX ROWID| PNA_ORTR_DTL           |  3433 |   117K|       |  3605   (1)| 00:00:01 |
|* 59 |                      INDEX RANGE SCAN          | PNA_ORTR_DTL_IX_06     | 17432 |       |       |   732   (1)| 00:00:01 |
|* 60 |                    INDEX RANGE SCAN            | PNA_ORTR_PRCS_DTL_PK   |     4 |       |       |     1   (0)| 00:00:01 |
|* 61 |                   TABLE ACCESS BY INDEX ROWID  | PNA_ORTR_PRCS_DTL      |     4 |   108 |       |     1   (0)| 00:00:01 |
|  62 |                  NESTED LOOPS                  |                        | 33938 |  1955K|       | 12451   (1)| 00:00:01 |
|  63 |                   NESTED LOOPS                 |                        | 33938 |  1955K|       | 12451   (1)| 00:00:01 |
|  64 |                    INLIST ITERATOR             |                        |       |       |       |            |          |
|* 65 |                     TABLE ACCESS BY INDEX ROWID| PNA_ORTR_DTL           | 14739 |   460K|       |  3605   (1)| 00:00:01 |
|* 66 |                      INDEX RANGE SCAN          | PNA_ORTR_DTL_IX_06     | 17432 |       |       |   732   (1)| 00:00:01 |
|* 67 |                    INDEX RANGE SCAN            | PNA_ORTR_SBPRCS_DTL_PK |     2 |       |       |     1   (0)| 00:00:01 |
|* 68 |                   TABLE ACCESS BY INDEX ROWID  | PNA_ORTR_SBPRCS_DTL    |     2 |    54 |       |     1   (0)| 00:00:01 |
|  69 |   TABLE ACCESS BY INDEX ROWID                  | TB_COMMON_CD_BAS       |     1 |    26 |       |     1   (0)| 00:00:01 |
|* 70 |    INDEX UNIQUE SCAN                           | TB_COMMON_CD_BAS_PK    |     1 |       |       |     1   (0)| 00:00:01 |
|  71 |   TABLE ACCESS BY INDEX ROWID                  | TB_COMMON_CD_BAS       |     1 |    26 |       |     1   (0)| 00:00:01 |
|* 72 |    INDEX UNIQUE SCAN                           | TB_COMMON_CD_BAS_PK    |     1 |       |       |     1   (0)| 00:00:01 |
|* 73 |   HASH JOIN RIGHT OUTER                        |                        |   549 |   619K|       | 35155   (1)| 00:00:02 |
|  74 |    TABLE ACCESS FULL                           | PNA_ORDRPRCSSERR_CTG   |   710 |   126K|       |    12   (0)| 00:00:01 |
|* 75 |    HASH JOIN RIGHT OUTER                       |                        |   549 |   521K|       | 35143   (1)| 00:00:02 |
|  76 |     TABLE ACCESS FULL                          | PNA_ORDRPRCSSERR_CTG   |   710 |   126K|       |    12   (0)| 00:00:01 |
|* 77 |     HASH JOIN RIGHT OUTER                      |                        |   549 |   423K|       | 35131   (1)| 00:00:02 |
|  78 |      TABLE ACCESS FULL                         | PNA_ORDRPRCSSERR_CTG   |   710 |   126K|       |    12   (0)| 00:00:01 |
|* 79 |      HASH JOIN RIGHT OUTER                     |                        |   549 |   324K|       | 35119   (1)| 00:00:02 |
|  80 |       TABLE ACCESS FULL                        | PNA_ORDRPRCSSTYPE_CTG  |    23 |   483 |       |    12   (0)| 00:00:01 |
|* 81 |       HASH JOIN RIGHT OUTER                    |                        |   549 |   313K|       | 35107   (1)| 00:00:02 |
|  82 |        TABLE ACCESS FULL                       | PNA_ORDRPRCSSTYPE_CTG  |    23 |   483 |       |    12   (0)| 00:00:01 |
|* 83 |        HASH JOIN RIGHT OUTER                   |                        |   549 |   302K|       | 35095   (1)| 00:00:02 |
|  84 |         TABLE ACCESS FULL                      | PNA_ORDRPRCSSTYPE_CTG  |    23 |   483 |       |    12   (0)| 00:00:01 |
|* 85 |         HASH JOIN RIGHT OUTER                  |                        |   549 |   291K|       | 35083   (1)| 00:00:02 |
|  86 |          TABLE ACCESS FULL                     | PNA_ORDRPRCSSTYPE_CTG  |    23 |   483 |       |    12   (0)| 00:00:01 |
|* 87 |          HASH JOIN                             |                        |   549 |   279K|       | 35071   (1)| 00:00:02 |
|  88 |           NESTED LOOPS OUTER                   |                        |     1 |   445 |       |   146   (1)| 00:00:01 |
|  89 |            NESTED LOOPS OUTER                  |                        |     1 |   403 |       |   145   (1)| 00:00:01 |
|  90 |             NESTED LOOPS OUTER                 |                        |     1 |   368 |       |   144   (1)| 00:00:01 |
|  91 |              NESTED LOOPS ANTI                 |                        |     1 |   333 |       |   143   (1)| 00:00:01 |
|  92 |               NESTED LOOPS OUTER               |                        |    42 | 13272 |       |   126   (0)| 00:00:01 |
|  93 |                NESTED LOOPS OUTER              |                        |    42 | 10668 |       |   118   (1)| 00:00:01 |
|  94 |                 NESTED LOOPS                   |                        |    42 |  8064 |       |   109   (0)| 00:00:01 |
|  95 |                  NESTED LOOPS                  |                        |    42 |  5880 |       |   101   (1)| 00:00:01 |
|  96 |                   INLIST ITERATOR              |                        |       |       |       |            |          |
|  97 |                    TABLE ACCESS BY INDEX ROWID | TB_OFFICE              |    38 |   646 |       |     2   (0)| 00:00:01 |
|* 98 |                     INDEX UNIQUE SCAN          | TB_OFFICE_PK           |    38 |       |       |     1   (0)| 00:00:01 |
|* 99 |                   TABLE ACCESS BY INDEX ROWID  | PNA_ORTR_DTL           |     1 |   123 |       |     3   (0)| 00:00:01 |
|*100 |                    INDEX RANGE SCAN            | PNA_ORTR_DTL_UK_01     |     6 |       |       |     1   (0)| 00:00:01 |
| 101 |                  TABLE ACCESS BY INDEX ROWID   | PNA_PROD_ORD_BAS       |     1 |    52 |       |     1   (0)| 00:00:01 |
|*102 |                   INDEX UNIQUE SCAN            | PNA_PROD_ORD_BAS_PK    |     1 |       |       |     1   (0)| 00:00:01 |
| 103 |                 TABLE ACCESS BY INDEX ROWID    | PNA_CUST_ORD_DTL       |     1 |    62 |       |     1   (0)| 00:00:01 |
|*104 |                  INDEX UNIQUE SCAN             | PNA_CUST_ORD_DTL_PK    |     1 |       |       |     1   (0)| 00:00:01 |
| 105 |                TABLE ACCESS BY INDEX ROWID     | PNA_CUST_ORD_DTL       |     1 |    62 |       |     1   (0)| 00:00:01 |
|*106 |                 INDEX UNIQUE SCAN              | PNA_CUST_ORD_DTL_PK    |     1 |       |       |     1   (0)| 00:00:01 |
|*107 |               TABLE ACCESS BY INDEX ROWID      | PNA_ORTR_PRCS_DTL      |   331K|  5503K|       |     1   (0)| 00:00:01 |
|*108 |                INDEX RANGE SCAN                | PNA_ORTR_PRCS_DTL_PK   |     1 |       |       |     1   (0)| 00:00:01 |
| 109 |              TABLE ACCESS BY INDEX ROWID       | PNA_ORTR_TELNO_TXN     |     1 |    35 |       |     1   (0)| 00:00:01 |
|*110 |               INDEX UNIQUE SCAN                | PNA_ORTR_TELNO_TXN_PK  |     1 |       |       |     1   (0)| 00:00:01 |
| 111 |             TABLE ACCESS BY INDEX ROWID        | PNA_ORTR_TELNO_TXN     |     1 |    35 |       |     1   (0)| 00:00:01 |
|*112 |              INDEX UNIQUE SCAN                 | PNA_ORTR_TELNO_TXN_PK  |     1 |       |       |     1   (0)| 00:00:01 |
| 113 |            TABLE ACCESS BY INDEX ROWID         | TB_SVCTYPECODE         |     1 |    42 |       |     1   (0)| 00:00:01 |
|*114 |             INDEX UNIQUE SCAN                  | TB_SVCTYPECODE_PK      |     1 |       |       |     1   (0)| 00:00:01 |
| 115 |           VIEW                                 |                        | 86207 |  6482K|       | 34925   (1)| 00:00:02 |
| 116 |            SORT GROUP BY                       |                        | 86207 |  5051K|       | 34925   (1)| 00:00:02 |
| 117 |             VIEW                               |                        | 86207 |  5051K|       | 34925   (1)| 00:00:02 |
|*118 |              FILTER                            |                        |       |       |       |            |          |
| 119 |               WINDOW SORT                      |                        | 86207 |  4714K|  6456K| 34925   (1)| 00:00:02 |
| 120 |                VIEW                            |                        | 86207 |  4714K|       | 33749   (1)| 00:00:02 |
| 121 |                 UNION-ALL                      |                        |       |       |       |            |          |
| 122 |                  NESTED LOOPS                  |                        | 52269 |  3011K|       | 21297   (1)| 00:00:02 |
| 123 |                   NESTED LOOPS                 |                        | 58956 |  3011K|       | 21297   (1)| 00:00:02 |
| 124 |                    INLIST ITERATOR             |                        |       |       |       |            |          |
|*125 |                     TABLE ACCESS BY INDEX ROWID| PNA_ORTR_DTL           | 14739 |   460K|       |  3605   (1)| 00:00:01 |
|*126 |                      INDEX RANGE SCAN          | PNA_ORTR_DTL_IX_06     | 17432 |       |       |   732   (1)| 00:00:01 |
|*127 |                    INDEX RANGE SCAN            | PNA_ORTR_PRCS_DTL_PK   |     4 |       |       |     1   (0)| 00:00:01 |
|*128 |                   TABLE ACCESS BY INDEX ROWID  | PNA_ORTR_PRCS_DTL      |     4 |   108 |       |     1   (0)| 00:00:01 |
| 129 |                  NESTED LOOPS                  |                        | 33938 |  1955K|       | 12451   (1)| 00:00:01 |
| 130 |                   NESTED LOOPS                 |                        | 33938 |  1955K|       | 12451   (1)| 00:00:01 |
| 131 |                    INLIST ITERATOR             |                        |       |       |       |            |          |
|*132 |                     TABLE ACCESS BY INDEX ROWID| PNA_ORTR_DTL           | 14739 |   460K|       |  3605   (1)| 00:00:01 |
|*133 |                      INDEX RANGE SCAN          | PNA_ORTR_DTL_IX_06     | 17432 |       |       |   732   (1)| 00:00:01 |
|*134 |                    INDEX RANGE SCAN            | PNA_ORTR_SBPRCS_DTL_PK |     2 |       |       |     1   (0)| 00:00:01 |
|*135 |                   TABLE ACCESS BY INDEX ROWID  | PNA_ORTR_SBPRCS_DTL    |     2 |    54 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------------

As-Is의 쿼리를 보시면 PNA_ORTR_PRCS_DTL 테이블과 PNA_ORTR_SBPRCS_DTL 테이블에서 Full Scan이 확인됩니다.
이를 해결하기 위해 PNA_ORTR_DTL 테이블을 추가로 JOIN하였고, 추가 JOIN한 테이블에 조건을 추가로 걸어서 Full Scan을 방지하였습니다. 그리고 To-Be의 결과를 도출할 수 있었습니다. ( Cost : 164,000 → 59,793 )
아래는 수정된 쿼리 이력입니다. ( 전체쿼리는 길이가 너무 길어 첨부하지 않고, 일부만 첨부하겠습니다. )

• As-Is

......
......
......


SELECT
    ORDR_TRT_NO,
    DECODE(ORDR_PRCSS_TRT_STTUS_ID,'2',ORDR_PRCSS_TYPE_ID,'7',ORDR_PRCSS_TYPE_ID,'') AS WRKCSTAT,
    DECODE(ORDR_PRCSS_TRT_STTUS_ID,'0',ORDR_PRCSS_TYPE_ID,'1',ORDR_PRCSS_TYPE_ID,'3',ORDR_PRCSS_TYPE_ID,
        '4',ORDR_PRCSS_TYPE_ID,'5',ORDR_PRCSS_TYPE_ID,'6',ORDR_PRCSS_TYPE_ID,'8',ORDR_PRCSS_TYPE_ID,'') AS WRKSTAT,
    DECODE(ORDR_PRCSS_TRT_STTUS_ID,'2',ORDR_PRCSS_ST_DT,'7',ORDR_PRCSS_ST_DT,TO_DATE('00010101000000', 'yyyymmddhh24miss')) AS WRKCSTATDT,
    DECODE(ORDR_PRCSS_TRT_STTUS_ID,'0',ORDR_PRCSS_ST_DT,'1',ORDR_PRCSS_ST_DT,'3',ORDR_PRCSS_ST_DT,
        '4',ORDR_PRCSS_ST_DT,'5',ORDR_PRCSS_ST_DT,'6',ORDR_PRCSS_ST_DT,'8',ORDR_PRCSS_ST_DT,TO_DATE('99991231232359', 'yyyymmddhh24miss')) AS WRKSTATDT,
    ORDR_PRCSS_TRT_STTUS_ID,
    ORDR_PRCSS_ERR_ID
FROM PNA_ORTR_PRCS_DTL
WHERE
OBDNG_ID IN (
)
AND ORDR_PRCSS_ST_DT IS NOT NULL
AND ORDR_PRCSS_TYPE_ID NOT IN ('LORD', 'PHRD')


......
......
......

• To-Be

......
......
......


SELECT
    ORTR.ORDR_TRT_NO,
    DECODE(PRCS.ORDR_PRCSS_TRT_STTUS_ID,'2',PRCS.ORDR_PRCSS_TYPE_ID,'7',PRCS.ORDR_PRCSS_TYPE_ID,'') AS WRKCSTAT,
    DECODE(PRCS.ORDR_PRCSS_TRT_STTUS_ID,'0',PRCS.ORDR_PRCSS_TYPE_ID,'1',PRCS.ORDR_PRCSS_TYPE_ID,'3',PRCS.ORDR_PRCSS_TYPE_ID,
        '4',PRCS.ORDR_PRCSS_TYPE_ID,'5',PRCS.ORDR_PRCSS_TYPE_ID,'6',PRCS.ORDR_PRCSS_TYPE_ID,'8',PRCS.ORDR_PRCSS_TYPE_ID,'') AS WRKSTAT,
    DECODE(PRCS.ORDR_PRCSS_TRT_STTUS_ID,'2',PRCS.ORDR_PRCSS_ST_DT,'7',PRCS.ORDR_PRCSS_ST_DT,TO_DATE('00010101000000', 'yyyymmddhh24miss')) AS WRKCSTATDT,
    DECODE(PRCS.ORDR_PRCSS_TRT_STTUS_ID,'0',PRCS.ORDR_PRCSS_ST_DT,'1',PRCS.ORDR_PRCSS_ST_DT,'3',PRCS.ORDR_PRCSS_ST_DT,
        '4',PRCS.ORDR_PRCSS_ST_DT,'5',PRCS.ORDR_PRCSS_ST_DT,'6',PRCS.ORDR_PRCSS_ST_DT,'8',PRCS.ORDR_PRCSS_ST_DT,TO_DATE('99991231232359', 'yyyymmddhh24miss')) AS WRKSTATDT,
    PRCS.ORDR_PRCSS_TRT_STTUS_ID,
    PRCS.ORDR_PRCSS_ERR_ID
FROM PNA_ORTR_DTL ORTR
JOIN PNA_ORTR_PRCS_DTL PRCS ON ORTR.ORDR_TRT_NO=PRCS.ORDR_TRT_NO
WHERE
ORTR.OBDNG_ID IN (
    'R00432'
)
AND 10 < (
(
  SYSDATE - ORTR.ORDR_RCP_DATE
) * 24 * 60
)
AND NVL(ORTR.PRVT_ESS_CIRCUIT_TYPE_CD, '*') <> '3'
AND NVL(ORTR.PROD_ID, '*') NOT IN ('0756', '0513')
AND NOT (
    ORTR.PROD_ID = '0757'
    AND ORTR.PRVT_ESS_CIRCUIT_TYPE_CD = '2'
)
AND PRCS.ORDR_PRCSS_ST_DT IS NOT NULL
AND PRCS.ORDR_PRCSS_TYPE_ID NOT IN ('LORD', 'PHRD')
AND ORTR.ORDR_TYPE_ID IN ('PB', 'PC')


......
......
......

앞선 👉글에서 JDK에 대해 알아보았다.
이제 본 글에서는 Java 프로그램을 구동시켜주는 JVM에 대해 상세히 알아보고자 한다.

위 사진은 JVM 아키텍처를 나타낸다.
예제코드를 작성하고, JVM이 해당 예제코드를 어떻게 실행시키는지 보면서 위 아키텍처를 설명해보겠다.

예제 세팅

예제코드는 아래와 같다.

• Main.java

public class Main {
    public static void main(String[] args) {
        Woong woong = new Woong();
        woong.methodA(3);
    }
}

• Woong.java

public class Woong {

    public int methodA(int param) {
        int localVariable = 1;
        int sum = localVariable + param;
        methodB();
        return sum;
    }

    private void methodB() {

    }
}

위 Java코드를 컴파일하여 class파일을 생성해보겠다.
아래 명령어를 실행하면 Main.class와 Woong.class 두 개의 클래스 파일이 생성될 것이다.
javac Main.java Woong.java

생성된 클래스 파일을 HexD나 Hex Viewer로 보면 직접 Byte형태로 볼 수 있지만,
사람이 이해하기 어려운 문법이므로 역어셈블이라는 과정을 거쳐 사람이 이해하기 쉬운 형태로 변환해보겠다.
javap -v -p -s Main.class, javap -v -p -s Woong.class

그리고 아래는 확인할 수 있는 형태의 바이트 코드이다.

• Main.class

Classfile /Users/daewoong/JavaStudy/src/Main.class
  Last modified 2023. 7. 23.; size 318 bytes
  SHA-256 checksum de5de7f896a34cb4c31a490af4edcf325015e7050ecbb9d0bfeb3bda7854859d
  Compiled from "Main.java"
public class Main
  minor version: 0
  major version: 63
  flags: (0x0021) ACC_PUBLIC, ACC_SUPER
  this_class: #14                         // Main
  super_class: #2                         // java/lang/Object
  interfaces: 0, fields: 0, methods: 2, attributes: 1
Constant pool:
   #1 = Methodref          #2.#3          // java/lang/Object."<init>":()V
   #2 = Class              #4             // java/lang/Object
   #3 = NameAndType        #5:#6          // "<init>":()V
   #4 = Utf8               java/lang/Object
   #5 = Utf8               <init>
   #6 = Utf8               ()V
   #7 = Class              #8             // Woong
   #8 = Utf8               Woong
   #9 = Methodref          #7.#3          // Woong."<init>":()V
  #10 = Methodref          #7.#11         // Woong.methodA:(I)I
  #11 = NameAndType        #12:#13        // methodA:(I)I
  #12 = Utf8               methodA
  #13 = Utf8               (I)I
  #14 = Class              #15            // Main
  #15 = Utf8               Main
  #16 = Utf8               Code
  #17 = Utf8               LineNumberTable
  #18 = Utf8               main
  #19 = Utf8               ([Ljava/lang/String;)V
  #20 = Utf8               SourceFile
  #21 = Utf8               Main.java
{
  public Main();
    descriptor: ()V
    flags: (0x0001) ACC_PUBLIC
    Code:
      stack=1, locals=1, args_size=1
         0: aload_0
         1: invokespecial #1                  // Method java/lang/Object."<init>":()V
         4: return
      LineNumberTable:
        line 1: 0

  public static void main(java.lang.String[]);
    descriptor: ([Ljava/lang/String;)V
    flags: (0x0009) ACC_PUBLIC, ACC_STATIC
    Code:
      stack=2, locals=2, args_size=1
         0: new           #7                  // class Woong
         3: dup
         4: invokespecial #9                  // Method Woong."<init>":()V
         7: astore_1
         8: aload_1
         9: iconst_3
        10: invokevirtual #10                 // Method Woong.methodA:(I)I
        13: pop
        14: return
      LineNumberTable:
        line 3: 0
        line 4: 8
        line 5: 14
}
SourceFile: "Main.java"

• Woong.class

Classfile /Users/daewoong/JavaStudy/src/Woong.class
  Last modified 2023. 7. 23.; size 322 bytes
  SHA-256 checksum 9709d10e0bfcde2dd1b9477a8de2f210e322181f9f42835d3c3898b7e213f904
  Compiled from "Woong.java"
public class Woong
  minor version: 0
  major version: 63
  flags: (0x0021) ACC_PUBLIC, ACC_SUPER
  this_class: #8                          // Woong
  super_class: #2                         // java/lang/Object
  interfaces: 0, fields: 0, methods: 3, attributes: 1
Constant pool:
   #1 = Methodref          #2.#3          // java/lang/Object."<init>":()V
   #2 = Class              #4             // java/lang/Object
   #3 = NameAndType        #5:#6          // "<init>":()V
   #4 = Utf8               java/lang/Object
   #5 = Utf8               <init>
   #6 = Utf8               ()V
   #7 = Methodref          #8.#9          // Woong.methodB:()V
   #8 = Class              #10            // Woong
   #9 = NameAndType        #11:#6         // methodB:()V
  #10 = Utf8               Woong
  #11 = Utf8               methodB
  #12 = Utf8               Code
  #13 = Utf8               LineNumberTable
  #14 = Utf8               methodA
  #15 = Utf8               (I)I
  #16 = Utf8               SourceFile
  #17 = Utf8               Woong.java
{
  public Woong();
    descriptor: ()V
    flags: (0x0001) ACC_PUBLIC
    Code:
      stack=1, locals=1, args_size=1
         0: aload_0
         1: invokespecial #1                  // Method java/lang/Object."<init>":()V
         4: return
      LineNumberTable:
        line 1: 0

  public int methodA(int);
    descriptor: (I)I
    flags: (0x0001) ACC_PUBLIC
    Code:
      stack=2, locals=4, args_size=2
         0: iconst_1
         1: istore_2
         2: iload_2
         3: iload_1
         4: iadd
         5: istore_3
         6: aload_0
         7: invokevirtual #7                  // Method methodB:()V
        10: iload_3
        11: ireturn
      LineNumberTable:
        line 4: 0
        line 5: 2
        line 6: 6
        line 7: 10

  private void methodB();
    descriptor: ()V
    flags: (0x0002) ACC_PRIVATE
    Code:
      stack=0, locals=1, args_size=1
         0: return
      LineNumberTable:
        line 12: 0
}
SourceFile: "Woong.java"

위 바이트코드는 크게 세 가지 정보로 분류된다.

• 클래스 정보
• Constant Pool
• Instruction Set

우리는 Constant Pool과 Instruction Set에만 집중하여 JVM을 파악해볼 것이다.
자세한 바이트 코드 정보는 Oracle Doc과 tistory 글에서 확인할 수 있다.

JVM 동작 원리

JVM의 목적 : 바이트코드 형태의 Class 파일을 컴퓨터가 읽을 수 있는 기계어로 번역하여 CPU에 명령을 내림

Java 프로그램이 실행되면 JVM은 먼저 Class Loader를 통해 Class파일을 읽는다.
Class Loader에 의해 읽혀진 Class파일은 검증과정과 초기화과정(static 변수 초기화 등)을 거쳐 Runtime Data Areas의 Method Area라는 메모리 공간에 올려진다.

메모리 공간에 올려진 Class 파일의 바이트코드는 Execution Engine의 Interpreter나 JIT Compiler에 의해 기계어로 번역되어 CPU에 전달된다. 그리고 기계어로 번역될 프로그램 동작과 관련한 정보는 Runtime Data Areas 저장되고, 실시간으로 저장된 정보가 인터프리터에 의해 기계어로 번역되어 CPU로 전달될 것이다.

이제 Runtime Data Areas의 원리에 집중하면서 JVM의 동작을 살펴볼 것이다.

Runtime Data Areas

Runtime Data Areas는 크게 다섯가지 공간으로 분류된다.

1. Method Area : 클래스에 대한 정보 저장 (스레드 공유 공간)
2. Heap : 런타임에 생성되는 모든 객체들의 대한 정보 저장 (스레드 공유 공간)
3. JVM Stacks : 메서드를 실행하기 위한 정보들이 저장되는 공간, Frame 자료구조 활용 (스레드당 1개)
4. PC Registres : 현재 실행되고 있는 명령의 주소를 저장 (스레드당 1개)
5. Native Method Stacks : C나 C++로 작성된 메서드를 실행할 때 사용되는 Stack (스레드당 1개)

JVM Stacks

Class의 메인 메서드가 실행되거나 스레드가 생성되면 JVM Stacks은 하나씩 생성된다.

JVM Stacks은 위 사진처럼 구성되어 있다.
생성된 스레드에서 메서드가 호출될 때마다 메서드 동작에 대한 정보가 Frame 자료구조 형태로 생성되어 JVM Stacks에 쌓인다.
Frame형태로 쌓인 메서드의 동작이 끝나거나 Exception이 발생하면 해당 Frame은 pop된다.

Frame 자료구조에 대해 자세히 알아보자.

• Local Variables Array : 실행된 메서드의 지역변수의 공간이 해당 배열에 생성된다. 선언된 순서대로 1번 인덱스부터 할당된다. 0번 인덱스는 this로 자기자신을 가리킨다.
• Operand Stack : Instruction Set에 따른 피연산값 및 연산의 중간값들을 저장하는 Stack
• Constant Pool : 클래스 내에서 사용되는 상수(constant)들을 담은 테이블

위 Constant Pool은 Class 파일 상의 Constant Pool의 데이터를 가리킨다.
아래는 Main.class의 Constant Pool 이다.

   #1 = Methodref          #2.#3          // java/lang/Object."<init>":()V
   #2 = Class              #4             // java/lang/Object
   #3 = NameAndType        #5:#6          // "<init>":()V
   #4 = Utf8               java/lang/Object
   #5 = Utf8               <init>
   #6 = Utf8               ()V
   #7 = Class              #8             // Woong
   #8 = Utf8               Woong
   #9 = Methodref          #7.#3          // Woong."<init>":()V
  #10 = Methodref          #7.#11         // Woong.methodA:(I)I
  #11 = NameAndType        #12:#13        // methodA:(I)I
  #12 = Utf8               methodA
  #13 = Utf8               (I)I
  #14 = Class              #15            // Main
  #15 = Utf8               Main
  #16 = Utf8               Code
  #17 = Utf8               LineNumberTable
  #18 = Utf8               main
  #19 = Utf8               ([Ljava/lang/String;)V
  #20 = Utf8               SourceFile
  #21 = Utf8               Main.java

#2 = Class #4로 예를들면,
#2 = Class 부분은 Index 및 Type을 가리키고 #4는 참조값을 가리킨다.
#2인덱스는 #4인덱스를 참조하고 있고, #4인덱스는 java/lang/Object를 가리키고 있으므로,
#2인덱스는 java/lang/Object를 가리키고 있다고 봐도 무방하다.

Instruction Set에 따른 JVM 동작

JVM 아키텍처의 각 모듈에 대해 알아보았으니, 이제 동작원리를 알아볼 것이다.

1. 클래스로더에 의해 Class 파일이 Method Area에 올라온다. (스레드가 실행되면서 필요한 클래스들은 필요할 때마다 동적으로 클래스로더에 의해 불려진다.)
2. JVM은 Class 파일을 해석하여 Instruction Set의 순서대로 프로그램을 동작시킨다.

         0: new           #7                  // class Woong
         3: dup
         4: invokespecial #9                  // Method Woong."<init>":()V
         7: astore_1
         8: aload_1
         9: iconst_3
        10: invokevirtual #10                 // Method Woong.methodA:(I)I
        13: pop
        14: return

         0: iconst_1
         1: istore_2
         2: iload_2
         3: iload_1
         4: iadd
         5: istore_3
         6: aload_0
         7: invokevirtual #7                  // Method methodB:()V
        10: iload_3
        11: ireturn

주요 Instruction Set만 알아볼 것이다.

• new : Constant Pool의 #7 인덱스에 해당하는 클래스의 인스턴스를 생성한다. Method Area상의 해당 클래스의 사이즈를 계산하고 Heap 메모리를 할당한다. 그리고 할당된 Heap 메모리에 대한 참조값이 지역변수에 저장된다. (Method Area에 해당 클래스가 없을 경우, 클래스 로더에 의해 Method Area로 해당 클래스가 불려진다.)
• iconst_1 : 정수값 1을 Operand Stack에 올린다.
• istore_2 : Operand Stack에서 값을 꺼내서 Local Variables Array의 2번 인덱스에 저장한다.
• iload_2 : Local Variables Array의 2번 인덱스 값을 Operand Stack에 올린다.
• iadd : Operand Stack상단 두 값을 더한 후에 다시 Operand Stack에 저장한다.

3. Instruction Set의 각 명령어마다 인터프리터에 의해 기계어로 번역되어 실시간으로 실행된다.

클래스 로더에 의해 로드된 Class 파일은 JVM에 의해 위 Flow대로 실행된다.

Garbage Collection

GC(Garbage Collection)는 JVM 메모리를 자동으로 관리해준다. 객체 생명주기에 따라서 자동으로 메모리를 해제시켜주는데,
해당 GC의 원리는 다음 연재될 글에서 살펴보겠다.