IT실전교육,탑크리에듀교육센터: 오라클예문

레이블이 오라클예문인 게시물을 표시합니다. 모든 게시물 표시

2013년 11월 3일 일요일

스프링,아이바티스트랜잭션예제[Spring Framework3.X,Transaction,iBATIS, @Transactional]

스프링,아이바티스트랜잭션예제[Spring Framework3.X,Transaction,iBATIS, @Transactional]

스프링의 트랜잭션 관리 방법중 @Transactional 애노테이션을 이용하여 iBATIS와 연동하는 간단 예제이다.(오라클 emp 테이블에 Data 1건 insert..)

먼저 Spring MVC 프로젝트 하나 생성하자.

1. pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.mungchung</groupId>
<artifactId>sample</artifactId>
<name>abc</name>
<packaging>war</packaging>
<version>1.0.0-BUILD-SNAPSHOT</version>
<properties>
  <java-version>1.6</java-version>
  <org.springframework-version>3.0.6.RELEASE</org.springframework-version>
  <org.aspectj-version>1.6.9</org.aspectj-version>
  <org.slf4j-version>1.5.10</org.slf4j-version>
</properties>
<dependencies>

  
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-context</artifactId>
   <version>${org.springframework-version}</version>
   <exclusions>
    
    <exclusion>
     <groupId>commons-logging</groupId>
     <artifactId>commons-logging</artifactId>
    </exclusion>
   </exclusions>
  </dependency>
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-webmvc</artifactId>
   <version>${org.springframework-version}</version>
  </dependency>
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-tx</artifactId>
   <version>${org.springframework-version}</version>
  </dependency>
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-jdbc</artifactId>
   <version>${org.springframework-version}</version>
  </dependency>
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-orm</artifactId>
   <version>${org.springframework-version}</version>
  </dependency>
  <dependency>
   <groupId>org.apache.ibatis</groupId>
   <artifactId>ibatis-sqlmap</artifactId>
   <version>2.3.4.726</version>
  </dependency>
  <dependency>
   <groupId>commons-dbcp</groupId>
   <artifactId>commons-dbcp</artifactId>
   <version>1.2.2</version>
  </dependency>
  <dependency>
   <groupId>xerces</groupId>
   <artifactId>xercesImpl</artifactId>
   <version>2.9.1</version>
  </dependency>
  <dependency>
   <groupId>cglib</groupId>
   <artifactId>cglib</artifactId>
   <version>2.2</version>
   <type>jar</type>
   <scope>compile</scope>
  </dependency>

<dependency>
   <groupId>org.aspectj</groupId>
   <artifactId>aspectjrt</artifactId>
   <version>${org.aspectj-version}</version>
  </dependency>

<dependency>
   <groupId>org.slf4j</groupId>
   <artifactId>slf4j-api</artifactId>
   <version>${org.slf4j-version}</version>
  </dependency>
  <dependency>
   <groupId>org.slf4j</groupId>
   <artifactId>jcl-over-slf4j</artifactId>
   <version>${org.slf4j-version}</version>
   <scope>runtime</scope>
  </dependency>
  <dependency>
   <groupId>org.slf4j</groupId>
   <artifactId>slf4j-log4j12</artifactId>
   <version>${org.slf4j-version}</version>
   <scope>runtime</scope>
  </dependency>
  <dependency>
   <groupId>log4j</groupId>
   <artifactId>log4j</artifactId>
   <version>1.2.16</version>
   <exclusions>
    <exclusion>
     <groupId>javax.mail</groupId>
     <artifactId>mail</artifactId>
    </exclusion>
    <exclusion>
     <groupId>javax.jms</groupId>
     <artifactId>jms</artifactId>
    </exclusion>
    <exclusion>
     <groupId>com.sun.jdmk</groupId>
     <artifactId>jmxtools</artifactId>
    </exclusion>
    <exclusion>
     <groupId>com.sun.jmx</groupId>
     <artifactId>jmxri</artifactId>
    </exclusion>
   </exclusions>
   <scope>runtime</scope>
  </dependency>

<dependency>
   <groupId>javax.inject</groupId>
   <artifactId>javax.inject</artifactId>
   <version>1</version>
  </dependency>

<dependency>
   <groupId>javax.servlet</groupId>
   <artifactId>servlet-api</artifactId>
   <version>2.5</version>
   <scope>provided</scope>
  </dependency>
  <dependency>
   <groupId>javax.servlet.jsp</groupId>
   <artifactId>jsp-api</artifactId>
   <version>2.1</version>
   <scope>provided</scope>
  </dependency>
  <dependency>
   <groupId>javax.servlet</groupId>
   <artifactId>jstl</artifactId>
   <version>1.2</version>
  </dependency>

<dependency>
   <groupId>junit</groupId>
   <artifactId>junit</artifactId>
   <version>4.7</version>
   <scope>test</scope>
  </dependency>
</dependencies>
<build>
  <plugins>
   <plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-compiler-plugin</artifactId>
    <configuration>
     <source>${java-version}</source>
     <target>${java-version}</target>
    </configuration>
   </plugin>
   <plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-war-plugin</artifactId>
    <configuration>
     <warName>abc</warName>
    </configuration>
   </plugin>
   <plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-dependency-plugin</artifactId>
    <executions>
     <execution>
      <id>install</id>
      <phase>install</phase>
      <goals>
       <goal>sources</goal>
      </goals>
     </execution>
    </executions>
   </plugin>
   <plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-resources-plugin</artifactId>
    <version>2.5</version>
    <configuration>
     <encoding>UTF-8</encoding>
    </configuration>
   </plugin>
  </plugins>
</build>
</project>

2. 컨트롤러

package onj.edu.transaction;

import java.util.Locale;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

@Controller
public class HomeController {
@Autowired
private TransactionMain transactionMain;

@RequestMapping(value = "/hello", method = RequestMethod.GET)
public String home(Locale locale, Model model) {
  String msg = "";
  try {
   msg = transactionMain.insert();
  } catch (Throwable e) {
   msg = "Transaction 오류";
   e.printStackTrace();
  }
  model.addAttribute("msg", msg );
  return "onj";
}
}

3. 트랜잭션 메인 클래스

package onj.edu.transaction;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

public class TransactionMain {
@Autowired
private Tran1 tr1;

@Transactional(propagation = Propagation.REQUIRED)
public String insert() throws Throwable {
tr1.insertTest();

return "Transaction Success!!";
}
}

4. 트랜잭션 처리 클래스(간단히 EMP 테이블에 한건 인서트)

package onj.edu.transaction;

import java.util.HashMap;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.ibatis.SqlMapClientTemplate;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

public class Tran1 {
@Autowired
private SqlMapClientTemplate sqlMapClientTemplate;

@Transactional(propagation = Propagation.REQUIRED)
public void insertTest() {
  HashMap<String, String> hashMap = new HashMap<String, String>();
  hashMap.put("empno", "101");
  hashMap.put("ename", "오엔제이");

sqlMapClientTemplate.insert("sql.empinsert1", hashMap);
}
}

5. web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">

<context-param>
  <param-name>contextConfigLocation</param-name>
  <param-value>/WEB-INF/spring/root-context.xml</param-value>
</context-param>

<listener>
  <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>

<servlet>
  <servlet-name>onjServlet</servlet-name>
  <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
  <init-param>
   <param-name>contextConfigLocation</param-name>
   <param-value>/WEB-INF/spring/onjServlet-context.xml</param-value>
  </init-param>
  <load-on-startup>1</load-on-startup>
</servlet>

<servlet-mapping>
  <servlet-name>onjServlet</servlet-name>
  <url-pattern>*.html</url-pattern>
</servlet-mapping>

</web-app>

6. /spring/root-context.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
  http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
  http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">

<tx:annotation-driven proxy-target-class="true"/>

<context:component-scan base-package="onj.edu.transaction">
  <context:exclude-filter type="annotation" __EXPRESSION__="org.springframework.stereotype.Controller" />
</context:component-scan>

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
  destroy-method="close">
  <property name="driverClassName">
   <value>oracle.jdbc.driver.OracleDriver</value>
  </property>
  <property name="url">
   <value>jdbc:oracle:thin:@192.168.0.7:1521:onj</value>
  </property>
  <property name="username">
   <value>scott</value>
  </property>
  <property name="password">
   <value>tiger</value>
  </property>
</bean>

    <bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
        <property name="configLocation" value="classpath:/sql-map-config.xml"/>
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <bean id="sqlMapClientTemplate" class="org.springframework.orm.ibatis.SqlMapClientTemplate">
        <property name="sqlMapClient" ref="sqlMapClient"/>
    </bean>

</beans>

7. /spring/onjServlet-context.xml

<annotation-driven/>
<tx:annotation-driven proxy-target-class="true"/>

<resources mapping="/resources/**" location="/resources/" />

<beans:bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<beans:property name="prefix" value="/views/" />
<beans:property name="suffix" value=".jsp" />
</beans:bean>

<context:component-scan base-package="onj.edu.transaction"/>

</beans:beans>

8. src/main/resources/sql-map-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

9. SLQ매퍼, src/main/resources/sqlmap/sql.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com/DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap namespace="sql">
<insert id="empinsert1" parameterClass="hashMap">
INSERT INTO emp (empno, ename) VALUES (#empno#, #ename#)
</insert>
</sqlMap>

10.마지막으로 view 역할을 하는 jsp(/views/onj.jsp)

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ page session="false" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<head>
<title>Home</title>
</head>
<body>
//트랜잭션처리결과 출력
<P> ${msg} </P>
</body>
</html>

[출처] 오라클자바커뮤니티 - http://www.oraclejavanew.kr/bbs/board.php?bo_table=LecSpring&wr_id=303

[개강임박강좌, 오프라인교육장에 오시면 보다 자세히 배울 수 있습니다.]

오라클자바커뮤니티에서 운영하는 개발자 전문교육 ,개인80%환급(www.onjprogramming.co.kr)

[주간]
  [11/13]SQL초보에서실전전문가까지
  [11/13]안드로이드개발자과정
  [11/18]Spring3.X, MyBatis, Hibernate실무과정
  [11/18]iPhone 하이브리드 앱 개발 실무과정

[평일야간]
  [11/08]C#,ASP.NET마스터
  [11/08]Spring3.X, MyBatis, Hibernate실무과정
  [11/12]iPhone 하이브리드 앱 개발 실무과정
  [11/14]JAVA&WEB프레임워크실무과정

[주말]
  [11/09]C#,ASP.NET마스터
  [11/09]JAVA&WEB프레임워크실무과정
  [11/09]Spring3.X, MyBatis, Hibernate실무과정
  [11/09]웹퍼블리싱 마스터
  [11/16]PL/SQL,오라클힌트,SQL튜닝,사례연구
  [11/16]ASP.NET4.0 MVC 프로그래밍

2013년 10월 23일 수요일

오라클 SQL 튜닝의 도구 – SQL*TRACE와 TKPROF [ORACLE강좌]

Oracle의 SQL*TRACE는 사용자가 실행 한 SQL문에 대해 구문분석(Parsing), 실행(execute), 추출(fetch) 부분으로 나누어 각 단계에서 걸리는 Overhead와 시간 등의 통계 정보를 일정한 형태로 저장 합니다. EXPALIN PLAN에서 제공하는 정보와 더블어 CPU/IO의 필요량, 실행계획의 각 단계에서의 레코드 개수등의 정보도 확인 가능 합니다. EXPLAIN PLAN 명령어와 함께 자주 사용되는 튜닝의 도구 입니다.

SQL*TRACE나 TKPROF를 실행 했을 때의 결과는 이해하기가 쉽지 않지만 강력한 튜닝의 도구 입니다. SQL*TRACE에 의해 분석되는 결과는 바이너리 형태로 운영체제의 파일 시스템에 생성 됩니다. 물론 바이너리 이므로 결과를 직접 눈으로 보면 이해가 되지 않지만 TKPROF 유틸리티를 이용하여 텍스트 파일 형태로 변환 시켜 확인이 가능 합니다.

SQL*TRACE의 결과는 데이터베이스 전체 또는 특정 세션에 대해 적용 할 수 있는데 데이터베이스 전체에 트레이스를 적용하면 실제 Application 수행에 추가적인 부하를 가져오므로 특별한 경우를 제외하고 전체 데이터베이스 시스템에 TRACE를 거는 것은 삼가 해야 합니다. 대부분은 특정 세션에 대해서만 부분적으로 활성화 하여 사용 합니다.

SQL*TRACE의 사용

SQL TRACE를 사용하기 전에 몇 가지 설정이 필요한데 먼저 초기파일에서 USER_DUMP_DEST 파라미터를 확인해야 합니다. 이 매개변수는 TRACE를 실행 할 때 생성되는 파일의 위치를 설정 하는 것입니다. 또한 시간 정보를 TRACE 항목에 추가할려면 TIMED_STATISTICS 항목을 TRUE로 해야 하거나 SQL*Plus등에서는 alter session set timed_statistics=true 라고 해주어야 합니다. 아래에 자세히 확인 하도록 합니다.

TIMED_STATISTICS

시간 통계 정보에 대해 수집여부를 결정, 기본값은 false
세션레벨에서는 alter session set timed_statistics=true라고 하면 됩니다.

MAX_DUMP_FILE_SIZE

TRACE의 결과로 생기는 바이너리 파일의 최대 사이즈를 단위는 블록 입니다. 기본값은 500 블록 입니다. 또한 세션 레벨에서 다음과 같이 지정 가능 합니다. Alter session set max_dump_file_size = 800(800개의 시스템 블록)

USER_DUMP_DEST

TRACE의 결과로 생기는 바이너리 파일의 위치를 지정 합니다. 세션레벨에서는 alter session set user_dump_dest = “C:\oracle\admin\wink\udump” 등으로 지정 합니다.

위의 세개의 파라미터를 init.ora 파일에 지정하였다면 SQL*TRACE의 시작을 전체 데이터베이스에서 할건지 세션 레벨에서 할건지를 정할 수가 있습니다. 인스턴스 레벨에서 할려면 init.ora 파일에서 SQL_TRACE 항목을 TRUE로 설정하면 되구요 세션 레벨에서 할려면 alter session set sql_trace = true 라고 하면 됩니다.

자 이제 실습을 위해 위의 3개의 매개변수를 init.ora 에 설정토록 합니다.

MAX_DUMP_SIZE = 800
TIMED_STATISTICS = TRUE
USER_DUMP_DEST = C:\oracle\admin\wink\udump

다음을 따라 하도록 합니다.

SQL> conn / as sysdba
연결되었습니다.
SQL> shutdown immediate
데이터베이스가 닫혔습니다.
데이터베이스가 마운트 해제되었습니다.
ORACLE 인스턴스가 종료되었습니다.
SQL> startup open
ORACLE 인스턴스가 시작되었습니다.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
데이터베이스가 마운트되었습니다.
데이터베이스가 열렸습니다.

SQL>conn scott/tiger

SQL> alter session set sql_trace=true;

세션이 변경되었습니다.

SQL> select job,avg(sal) from emp
2 group by job
3 having avg(sal) > (select avg(sal) from emp
4 where job = 'SALESMAN');

JOB AVG(SAL)
--------- ----------
ANALYST 3000
MANAGER 2758.33333
PRESIDENT 5000

session에서 trace를 중지

SQL> alter session set sql_trace=false;

SQL*Plus를 종료하고 c:\oracle\admin\wink\udump에 가보면 trc 파일이 생겼을 것
입니다. 저의 경우 DB SID가 wink이므로 wink_ora_3316.trc 와 같은 파일이 생겼습니다.

TKPROF를 이용하여 TRACE파일을 텍스트 파일로 변경 하기

TKPROF Utility를 이용하면 매우 유용한 분석 정보를 얻을 수 있습니다. 즉 TKPROF의 결과 파일은 트레이스가 실행되는 동안 프로세스에 의해 데이터베이스에서 실행된 작업에 대한 요약 정보 입니다.

텍스트 파일의 내용을 보면 PARSE, EXECUTION, FETCH시 작업을 실행 한 횟수, CPU 사용 시간, 검색된 행이 무엇인지, SQL이 수행된 총 소요시간, DISK IO 블록 수, 조건을 만족하는 전체 행의 수, 수행된 SQL문이 사용한 SGA 영역의 크기, SQL문장의 실행 계획, 해당 세션에서 작업했던 전체 작업에 대한 CPU, 메모리, 블록의 크기 등의 정보를 확인 할 수 있습니다.

SQL문을 해석하기 위해서는 아래의 단계가 필요 합니다.

파싱(parse)

SQL문을 실행 계획으로 번역 하는 것을 말합니다. 해당 SQL을 실행 하는데 필요한 적절한 권한, 컬럼이 있는지, 참조된 객체에 관한 확인 등의 작업이 이루어지게 됩니다.

실행(execution)

오라클에 의해 SQL문을 실제 실행 한 것을 말합니다.

추출/인출(fetch)

쿼리에 의해 추출된 레코드를 이여기 합니다. Select 문에서만 이용 됩니다.

다음은 TKPROF의 통계 정보 컬럼 입니다.

Count : 분석, 실행, 추출을 몇번 했는지를 나타 냅니다.
CPU : 분석, 실행, 추출에 대한 CPU 처리 시간(CURSOR를 공유하면 분석단계의 처리 시간은 0 입니다.)
Elapsed : 분석, 실행, 추출 처리 단계별로 처리된 소요 시간
Disk : 테이블의 데이터를 읽기 위해 데이터 파일로부터 읽어 들인 블록 수
Query : SELECT로 데이터를 읽어 올 때 이미 다른 사용자에 의해 같은 데이터가 사용 되었다면 그 블록에서 데이터를 가져옵니다.
Current : 메모리에 저장된 데이터를 가지고 오기 위해 읽은 버퍼의 블록 수(update, insert, delete 후 select 했을 때)

TKPROF를 실행하기 위한 문법

Explain = 사용자계정/패스워드(명시된 사용자에 대해 EXPLAIN PLAN 실행)
Print = n (트레이스 파일내의 분석된 SQL문의 수를 n 만큼만 제한할 때 이용)
Record = 파일명(트레이스 파일내에 분석된 SQL문을 지정한 파일에 저장)
Sort=option(트레이스 파일내에 분석된 SQL문을 지정한 옵션에 의해 정렬)
Sys=[NO](트레이스 파일내에 생성된 SQL 문장 중에 오라클 서버가 내부적인 작업을 위해 실행한 SQL문장을 출력 시 포함 할건지를 결정)
Table=스키마.테이블명(실행 계획을 지정한 테이블에 저장)

이전의 SQL*TRACE에 의해 생긴 바이너리 파일을 TKPROF를 이용하여 분석을 해보도록 하겠습니다.

명령프롬픝에서 다음과 같이 실행 합니다.(TRACE 파일이 만들어진 곳에서 실행)

C:\oracle\admin\wink\udump>tkprof wink_ora_3316.trc sql1.tkp sys=no explain=scot
t/tiger

TKPROF: Release 9.2.0.1.0 - Production on 목 Dec 16 01:33:23 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

다음은 sql1.tkp 파일의 내용 입니다.

TKPROF: Release 9.2.0.1.0 - Production on 목 Dec 16 01:33:23 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Trace file: wink_ora_3316.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

alter session set sql_trace=true

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59 (SCOTT)
********************************************************************************

아래는 사용자가 실행한 SQL 문장 입니다.

select job,avg(sal) from emp
group by job
having avg(sal) > (select avg(sal) from emp
where job = 'SALESMAN')

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 6 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 6 0 3

Misses in library cache during parse: 1 이 값이 0이라는 의미는 실행한 SQL문이 이전에 실행 된적이 없었음을 나타 냅니다.
Optimizer goal: CHOOSE 옵티마이저 모드 입니다.
Parsing user id: 59 (SCOTT)

Rows Row Source Operation
------- ---------------------------------------------------
3 FILTER
5 SORT GROUP BY
14 TABLE ACCESS FULL EMP
1 SORT AGGREGATE
4 TABLE ACCESS FULL EMP

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
3 FILTER
5 SORT (GROUP BY)
14 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'EMP'
1 SORT (AGGREGATE)
4 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'EMP'

********************************************************************************

alter session set sql_trace=false

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.00 0 0 0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59 (SCOTT)

********************************************************************************
아래의 TOTAL은 전체 작업 결과에 대한 분석 결과 입니다.

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 3 0.01 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 6 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.01 0.01 0 6 0 3

Misses in library cache during parse: 1

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.01 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.00 0 3 0 1

Misses in library cache during parse: 0

3 user SQL statements in session.
1 internal SQL statements in session.
4 SQL statements in session.
1 statement EXPLAINed in this session.
********************************************************************************
Trace file: wink_ora_3316.trc
Trace file compatibility: 9.00.01
Sort options: default

1 session in tracefile.
3 user SQL statements in trace file.
1 internal SQL statements in trace file.
4 SQL statements in trace file.
4 unique SQL statements in trace file.
1 SQL statements EXPLAINed using schema:
SCOTT.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
54 lines in trace file.

[출처] 오라클자바커뮤니티 - http://www.oraclejavanew.kr/bbs/board.php?bo_table=LecOrccleTun&wr_id=5

[개강확정강좌]오라클자바커뮤니티에서 운영하는 개발자 전문교육 ,개인80%환급(www.onjprogramming.co.kr)

[주말]
  [10/26]C#,ASP.NET마스터
  [10/26]Spring3.X, MyBatis, Hibernate실무과정
  [11/2]JAVA&WEB프레임워크실무과정

[평일야간]
  [10/29]C#,ASP.NET마스터
  [10/25]Spring3.X, MyBatis, Hibernate실무과정
  [10/31]JAVA&WEB프레임워크실무과정

[주간]
  [11/4]Spring3.X, MyBatis, Hibernate실무과정

2013년 8월 9일 금요일

오라클의 각 Table은 내부적으로 ROWID 라는 의사 열을 가진다. 보통 SELECT등으로 Table을 질의하면 출력되지 않으나 컬럼 명으로 ROWID라는 예약어를 사용하면 읽어 들일 수 있다.

오라클자바커뮤니티에서 설립한 오엔제이프로그래밍 실무교육센터

(오라클SQL, 튜닝, 힌트,자바프레임워크, 안드로이드, 아이폰, 닷넷 실무전문 강의)

www.onjprogramming.co.kr

ROWID에는 Table의 각 행에 대해 물리적인 주소라고 이해 하면 될 것이다. 오라클7에서 ROWID에는 파일번호, 블록번호, 블록 내 ROW번호 3가지 정보를 가지고 있었지만 오라클8 이상부터 ROWID에는 객체의 고유번호를 포함하고 있다는 것은 참고로 알아두자. 데이터를 검색할 때 ROWID를 이용하여 검색 한 다면 가장 빨리 원하는 자료를 검색하는 것이 가능하다.
아래를 보면 앞의 중복된 데이터를 지우는 질의가 이해 될 것이다. 같은 주민등록번호 와 이름을 가지더라도 rowid는 틀리다. 이 특징을 이용하여 중복 데이터를 찾는 것이다.

SQL> select rowid, jumin, name from addrbook;
ROWID JUMIN NAME
------------------ -------------- ------------
AAAJMZAABAAAPEKAAA 111111-2222222 홍길동
AAAJMZAABAAAPEKAAB 333333-4444444 가길동
AAAJMZAABAAAPEKAAC 111111-2222222 홍길동

[출처] 오라클자바커뮤니티 - http://www.oraclejavanew.kr/bbs/board.php?bo_table=LecOracle&wr_id=25

2013년 8월 3일 토요일

Explain Plan - 오라클힌트, Oracle Hint-실행계획 SQL연산(INLIST ITERATOR)

실행계획 SQL연산(INLIST ITERATOR)

구로디지털 오엔제이프로그래밍실무교육센터

www.onjprogramming.co.kr

인덱스 컬럼이 IN-LIST구에 나타나는 경우의 ROW 연산 입니다. INLIST ITERATOR는 IN-LIST의 인수 만큼 반복연산을 수행 합니다.

SQL> desc emptest;

이름 널? 유형

------------------ -------- --------------------------

EMPNO NUMBER

DEPTNO NUMBER

ENAME VARCHAR2(46)

ADDR VARCHAR2(44)

SAL NUMBER

SQL> select count(*) from emptest;

COUNT(*)

----------

2500000

SQL> select index_name, table_name from user_indexes

where table_name like 'EMPTEST';

INDEX_NAME TABLE_NAME

------------------------------ ------------------------------

IDX_EMPTEST_ADDR EMPTEST

IDX_EMPTEST_DEPTNO EMPTEST

ADDR 컬럼으로 인덱스가 있다. Addr 컬럼을 이용해 보자.

SQL> select empno, ename

2 from emptest

3 where addr in ('서울1','서울10001');

EMPNO ENAME

---------- ----------------------------------------------

1 홍길동1

10001 홍길동10001

경 과: 00:00:00.00

Execution Plan

----------------------------------------------------------

| 0 | SELECT STATEMENT

| 1 | INLIST ITERATOR

| 2 | TABLE ACCESS BY INDEX ROWID| EMPTEST

|* 3 | INDEX RANGE SCAN | IDX_EMPTEST_ADDR

위의 는 INLIST ITERATOR를 나타내게 하기 위해 넣은 힌트 구문이며 힌트 구문을 사용하지 않는 다면 아래와 같은 실행 계획이 수립됩니다.

SQL> select empno, ename

2 from emptest

3 where addr in ('서울1','서울10003');

EMPNO ENAME

---------- ----------------------------------------------

1 홍길동1

10003 홍길동10003

경 과: 00:00:00.00

--------------------------------------------------------------------

| 0 | SELECT STATEMENT

| 1 | INLIST ITERATOR

| 2 | TABLE ACCESS BY INDEX ROWID| EMPTEST

|* 3 | INDEX RANGE SCAN | IDX_EMPTEST_ADDR |

[출처] 오라클자바커뮤니티 - http://www.onjprogramming.co.kr/oraclejavanew/oraclejava/bbs/board.php?bo_table=LecHINT&wr_id=74

[Oracle Hint]ACCESS 경로를 변경하는 힌트(NO_EXPAND) , 오라클힌트강좌 CCESS 경로를 변경하는 힌트(NO_EXPAN)

[Hint]ACCESS 경로를 변경하는 힌트(NO_EXPAN)

NO_EXPAND 힌트는 CBO(COST BASED Optimizer) 모드에서 OR 조건이나 IN List등을 사용할 때 OR확장 (Concatenation등을)을 막는 것인데 실행 계획에 Concatenation등이 보이는 경우 이곳이 일어나지 않도록 처리해 줍니다.

구로디지털 오엔제이프로그래밍실무교육센터

www.onjprogramming.co.kr

즉 OR를 UNION-ALL로 풀지 말고

아래의 예를 보죠~

[형식]

실습을 위해 먼저 옵티마이저 모드를 RULE로 바꾼 후 …

왜 바꾸냐 하면? EMP 테이블의 경우 데이터 양이 적으므로 OR를 사용하더라도 FULL SCAN하는 실행계획을 만들어 내므로 고의로 CONCATENATION을 만들어 내기 위해 RULE BASED Optimizer Mode로 변경하는 것입니다. 물론 옵티마이저 모드를 CHOOSE로 한 후 테이블의 통계 정보를 삭제하는 경우에도 동일 합니다.

alter session set optimizer_mode=rule;

SELECT ename, sal

FROM EMP

WHERE JOB = 'CLERK'

OR JOB = 'SALESMAN'

-----------------------------------------------------------------

Operation Object Name Rows Bytes Cost

-----------------------------------------------------------------

SELECT STATEMENT Optimizer Mode=RULE

CONCATENATION

TABLE ACCESS BY INDEX ROWID SCOTT.EMP

INDEX <st1:placetype w:st="on">RANGE</st1:placetype> SCAN SCOTT.IDX_EMP_JOB

TABLE ACCESS BY INDEX ROWID SCOTT.EMP

INDEX <st1:placetype w:st="on">RANGE</st1:placetype> SCAN SCOTT.IDX_EMP_JOB

SELECT

ENAME, SAL

FROM EMP

WHERE JOB = 'CLERK'

OR JOB = 'SALESMAN'

Operation Object Name Rows Bytes Cost

SELECT STATEMENT Optimizer Mode=RULE 6 2

INLIST ITERATOR

TABLE ACCESS BY INDEX ROWID SCOTT.EMP 6 96 2

INDEX <st1:placetype w:st="on">RANGE</st1:placetype> SCAN SCOTT.IDX_EMP_JOB 6 1

[실습]

- 실습을 위한 예제 테이블 및 데이터는 아래 링크에서 확인 바랍니다.

http://www.onjprogramming.co.kr/oraclejavanew/oraclejava/bbs/board.php?bo_table=LecOrccleTun&wr_id=53&page=0&sca=&sfl=&stx=&sst=&sod=&spt=0&page=0

myemp1 : 1000만건

myemp1_old : 100만건

mydept : 5건

테스트환경 : oracle 11g

아래와 같은 결과를 내기 위한 힌트 구문을 이해하고 왜 inlist iterator가 빠른지 느껴 보라.

먼저 인덱스를 하나 만들자.

SQL> create index idx_myemp1_sal on myemp1(sal);

인덱스가 생성되었습니다.

n 옵티마이저가 사용 못하도록 일단 숨기고…

SQL> alter index idx_myemp1_sal invisible;

인덱스가 변경되었습니다.

n 인덱스가 없는 경우엔 무조건 FULL SCAN을 한다.

SQL> select count(*) from myemp1

2 where sal = 100000

3 or sal = 200000

4 or sal = 300000;

COUNT(*)

----------

경 과: 00:00:08.85

-----------------------------------------------------------------------------

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 17035 (2)| 00:03:25 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

|* 2 | TABLE ACCESS FULL| MYEMP1 | 15 | 75 | 17035 (2)| 00:03:25 |

-----------------------------------------------------------------------------

SQL> select count(*) from myemp1

2 where sal = 100000

3 or sal = 200000

4 or sal = 300000;

COUNT(*)

----------

경 과: 00:00:08.82

Execution Plan

----------------------------------------------------------

Plan hash value: 3929728256

-------------------------------------

| Id | Operation | Name |

-------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | SORT AGGREGATE | |

|* 2 | TABLE ACCESS FULL| MYEMP1 |

n 인덱스를 다시 보이도록 하자.

SQL> alter index idx_myemp1_sal visible;

인덱스가 변경되었습니다.

SQL> select /*+ index(myemp1 idx_myemp1_sal) */ count(*) from myemp1

2 where sal = 100000

3 or sal = 200000

4 or sal = 300000;

COUNT(*)

----------

경 과: 00:00:00.01

-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 5 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| 2 | INLIST ITERATOR | | | | | |

|* 3 | INDEX RANGE SCAN| IDX_MYEMP1_SAL | 15 | 75 | 5 (0)| 00:00:01 |

-------------------------------------------------------------------------------------

n 아래처럼 힌트를 안써도 CBO인 경우 알아서 인덱스를 사용한다.

SQL> select count(*) from myemp1

2 where sal = 100000

3 or sal = 200000

4 or sal = 300000;

COUNT(*)

----------

경 과: 00:00:00.00

-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 5 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| 2 | INLIST ITERATOR | | | | | |

|* 3 | INDEX RANGE SCAN| IDX_MYEMP1_SAL | 15 | 75 | 5 (0)| 00:00:01 |

-------------------------------------------------------------------------------------

SQL> select count(*) from myemp1

2 where sal = 100000

3 or sal = 200000

4 or sal = 300000;

COUNT(*)

----------

경 과: 00:00:00.01

---------------------------------------------

| Id | Operation | Name |

---------------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | SORT AGGREGATE | |

| 2 | CONCATENATION | |

|* 3 | INDEX RANGE SCAN| IDX_MYEMP1_SAL |

|* 4 | INDEX RANGE SCAN| IDX_MYEMP1_SAL |

|* 5 | INDEX RANGE SCAN| IDX_MYEMP1_SAL |

---------------------------------------------

where절에 3개의 or 만으로는 inlist iterrator와 concatenation을 비교해 보기 어려워

이번에는 sal > 900000 이라는조건을 하나 더 줘보자. 그리고 통계정보도 보기 위해

set autotrace on 이라고 하자.

SQL>set autotrace on;

SQL> select count(*) from myemp1

2 where sal = 100000

3 or sal = 200000

4 or sal = 300000

5 or sal > 900000;

COUNT(*)

----------

5500010

경 과: 00:00:02.53

---------------------------------------------

| Id | Operation | Name |

---------------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | SORT AGGREGATE | |

| 2 | CONCATENATION | |

|* 3 | INDEX RANGE SCAN| IDX_MYEMP1_SAL |

|* 4 | INDEX RANGE SCAN| IDX_MYEMP1_SAL |

|* 5 | INDEX RANGE SCAN| IDX_MYEMP1_SAL |

|* 6 | INDEX RANGE SCAN| IDX_MYEMP1_SAL |

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

12973 consistent gets

12963 physical reads

0 redo size

438 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select /*+ index(myemp1 idx_myemp1_sal) */ count(*) from myemp1

2 where sal = 100000

3 or sal = 200000

4 or sal = 300000

5 or sal > 900000;

COUNT(*)

----------

5500010

경 과: 00:00:01.50

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 12745 (1)| 00:02:33 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| 2 | CONCATENATION | | | | | |

| 3 | INLIST ITERATOR | | | | | |

|* 4 | INDEX RANGE SCAN| IDX_MYEMP1_SAL | 15 | 75 | 5 (0)| 00:00:01 |

|* 5 | INDEX RANGE SCAN | IDX_MYEMP1_SAL | 5499K| 26M| 12740 (1)| 00:02:33 |

--------------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

12972 consistent gets

0 physical reads

0 redo size

438 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

4번 index range scan 후 concatenation 했을 때는 physical read가 발생하며

inlist iterator의 경우 physical read가 0이다… 그리고 시간도 조금 단축되고

확인 바란다. 쿼리가 단문인 경우 별차이 없다고 느끼겠지만 장문의 반복적인 쿼리 에서는 많은 성능 차이가 날 것이다. 그리고 CBO인 경우 no_expend를 사용 안하더라도 인덱스가 있는 경우 알아서 inlist iterator 연산을 수행하도록 실행계획을 작성하니 별 걱정 안해도 될 것 같다.

[출처] 오라클자바커뮤니티 - http://www.onjprogramming.co.kr/oraclejavanew/oraclejava/bbs/board.php?bo_table=LecHINT&wr_id=86