농부지기

교육중 자주쓰는 sql 및 명령어

create index idx_emp_sql on emp(sal);

select index_name, index_type, table_name, uniqueness from user_indexes;

SET LINESIZE 200

SET AUTOTRACE ON

SET SQLPROMPT "_USER>"

SPOOL C:\OracleStudy.TXT APPEND

[ Oracle-튜닝 1일차 ]

고성 (dagda@hanafos.com)

1. 설치

    . 1. my oracle support 비밀번호 : oracle

    . Database Control URL은 https://localhost:1158/em입니다.

    . SID : orcl

2. 계정

   . SYS       : DB 주인 (집주인)

   . SYSTEM : DBA (집사). DB관리자 (pw : system/oracle)

   . SCOTT   : 

1. Performance Tuning

   . Modeling

   . SQL Statement

   . SERVER 증설

   . application

   . OS 변경 및 Upgrade

2. SQL Statement Tuning

    . 튜닝의 대상 : 20vs80  

                     20%의 sql이 80%의 성능을 차지

    . 튜닝의 도구 : EXPLAIN PLAN

                     SQL*TRACE

                     TKPROF UTILITY

                     V$ dynamic performmance view

4. bind 변수

    > var no number

    > begin

          :no := 10;

       end;

    > print no

    > select * from  Emp where deptno = :no;

    > --sql수행결과 조회

    > begin

          :no := 20;

       end;

    > print no

    > select * from  Emp where deptno = :no;

    > --sql수행결과 조회

    --bind된 sql문은 조건값이 변경되어도 하나의 sql로 인식함.

5. PSEUDO Column (수도컬럼 )

   . rowid : row주소 (18자리)

               db에서 가장빨리 조회할 수 있는 조건

               index block에는 pk와 rowid를 같이 관리하고 있음.

   . rownum : row를 disk에서 조회한(읽어낸) 순서

6. connection

    . db는 첫 connection이 맺을 때 상당히 많은 자원을 할당해준다.

      이때 자원을 할당할 때 시간이 소요된다.

      그래서 모든 language들이 connection pool을 관리해서 구축해야 되는 이유다.

===========================================================

1. sql*plus 명령어 저장 및 자동실행

   . glogin.sql

     - sqlplus를 수행도중 set등으로 환경설정했던 명령어들은 sqlplus를 종료하면

       모두 살아짐

     - sqlplus에서 set등으로 환경설정했던 명령어들을 sqlplus접속하는 모든 사용자에게 동시에 자동 설정 되도록 할 수 있음.

     - 해당 파일 : C:\app\user\product\11.2.0\dbhome_1\sqlplus\admin\glogin.sql

    . 파일저장 후자동 set

      > ed c:\OracleStudy2.txt    --메모장으로 open됨

                                             --메모장에 필요한 sqlplus환경설정들 저장

      > @c:\OracleStudy2.txt      --해당 파일을 수행함.

      - glogin.sql은 sqlplus를 사용하는 모든 사용자에게 공통으로 적용됨.

      - @로 실행한 파일처리방식은 나만 sqlplus명령어가 적용됨

===========================================================

2. db alert정보
    . C:\app\user\diag\rdbms\orcl\orcl\trace

       - alert_orcl.log

          . db alert정보임. (db가 on/off되었는지 확인 가능)

          . 주기적으로 backup 및 삭제해줘야 됨

     (이미지 1)  

2. Oracle Server 의 기본구조

    . User Process   : 사용자

    . Server Process : 사용자 연결을 도와주는 Process

    . DataBase buffer cache : 사용자가 처음 한번 조회하면 disk에서 조회 후 data buffer cache에 옴

                                동일한 결과를 원하는 sql인 경우 disk에서 읽지 않고 data buffer cache에서 읽어 옴

    . Shared pool

       - Syntax check : 사용자가 sql로 결과를 요청하면 제일먼저 Syntax검사

                               . sql 문법 검사

                               . schema 권한검사

                               . schema 존재 여부

                               . syntax검사할때 tree구조로 만듬.

       - Optimizer : syntax검사완료 후 실행계획 작성

                         현존하는 최고의 AI (알고리즘)

                         DB가격의 약 1/3 차지함

                         실행계획을 재 사용하지 않으면 cpu점유율이 상당히 상승됨

       - Library Cache에서 sql parsing된 목록 관장

         . cache에 동일한 sql인지 여부 : 대소문자 구분. 조건값 구분. 공백구분

                                                     bind변수를 이용하면 동일한 하나로 인식

         . Hard parsing : sql을 syntax검사 및 실행계획작성

         . Soft Parsing : memory에 존재하는 기존 parsing된 sql문장을 재 활용

1. Library Cache 

   - 수행했던 sql문장이 저장 됨

   - 각 sql이 수행될 실행계획이 저장됨

   - 저장테이블 : V$SQL

      (예, SELECT sql_text, executions FROM V$SQL WHERE sql_text like '%FROM EMP%'; )

       - sql_text : sql문장

       - executions : 수행횟수

    . index 목록

      - select index_name, index_type from user_indexes;

2. Data Buffer Cache

   - Buffer : 이 기종간의 속도차이를 줄이기 위해서 관리하는 영역.

              즉, 느린저장소의 영역을 가져와서 잠시 저장하는 영역

   - Cache : 임시저장소

   - 

4. 진단도구

   . 

5. oracle - with절

   - with절에 sql문자을 기술하면 자료를 조회 후 cashe에 저장 한다.

     그런 후 하위에서는 cashe에서 자료를 바로 가져 온다.

   - view하고는 많이 다르다.

     view를 사용하게 되면 view 기술한 곳에 view의 sql문장 전체를 그대로 복사해서 실행계획이 풀린다.

    -