튜닝-sql*plus 명령어 모음

[ sql*plus 명령어 모음 ]

0. sqlplus접속

    c:\sqlplus scott/tiger

    c:\sqlplus sys/oracle  as sysdba    --이때 sys 사용자는 반드시 normal, sysdba, sysoper 중 하나로 접속해야 됨

 

1. 화면 설정

    > set linesize 200;   --조회결과를 200 자까지 넓혀줌. (cmd창 width도 조절필요)

 

2. SQL 수행 Log 파일 저장

    > save idx(파일명)  --최근에 수행된 sql문장이 idx.sql 파일에 저장됨

                                --저장파일 위치 : cmd창에서 sqlplus수행했던 경로에 저장 됨

    > save idx replace   --기존에 파일이 존재 시 replace함.

    > ed idx        --저장된 sql문장들을  메모장으로 보여줌

    > @idx         --저장된 파일을 읽어서 실행시켜줌.

    > set sqlprompt "_USER>"  --현재 접속 user id로 prompt를 설정

    > spool on   --spool 설정

    > spool T1(파일명)  --수행된 sql문장, 결과를 T1파일에 저장

                       --저장파일 위치 : cmd창에서 sqlplus수행했던 경로에 저장 됨

                      --spool off를 해야만 파일에 sql수행내역이 저장 됨

    > spool T1 append   --T1파일이 기 존재시 append함

    > spool off   --spool 설정 해제

                      --이때 sql수행내역이 저장 됨

 

3. Prompt

    > SET SQLPROMPT "_USER>"     --prompt를 접속user로 변경

    > set time on/off                          --프롬프트에 현재 시간 조회 및 제외

 

 

4. 실행계획

    . 실행계획 초기 설정 : 별도 문서 존재

    > set autotrace on                          --sql수행시 실행계획 조회 on

    > set timing on                                --sql 수행결과 시간 조회

 

    > show parameter optimizer           --optimizer_mode 보기

       - CHOOSE            : CBO, RBO 자동 선택 옵션

       - ALL_ROWS         : 전체 data가 빨리 나오는 기반으로 실행계획 생성 옵션.

       - FIRST_ROWS     : 첫번째 data가 빨리 나오는 기반으로 실행계획 생성 옵션.

       - FIRST_ROWS_n : n개  data가 빨리 나오는 기반으로 실행계획 생성 옵션.

 

70. 사용자추가 및 사용자내용 변경

    > create user dev

       identified by dev

       password expire;                      --사용자 추가

                                                         --expire : 첫 login시 바로 pw변경

    > alter user dev identified by "tiger";  --dev 사용자에 대한 password 변경

 

    . system> alter user scott     --약 v11부터는 scott 사용자는 존재 하지만 lock했음.

                                                 --그래서 scott사용자를 사용하려면 unlock을 해야 됨

        identified by tiger     --식별자를 tiger 로 설정(pw)

        account unlock;      --scott 계정 unlock 해제 (마지막 라인에는 ; [세미콜론 필요]

 

80. 권한 부여

    > grant create session to dev;

    > grant connect, resource to dev;   --접속권한 부여

    > grant plustrace to dev;              --dev에 plustrace권한 부여 (실행계획 권한부여)

 

 

81. 권한부여(테이블)

    > grant select on scott.emp to dev;  --dev에 scott.emp 테이블 select 권한부여

    > grant create table to dev;              --테이블 생성권한 부여

    > grant all      on scott.emp to dev;  --scott.emp 테이블의 모든 권한을 dev에 부여

 

    > alter user 유저명 default tablespace users quota unlimited on users;  --tablepsace권한부여

 

90. 설치환경 확인

    > show user

    > show parameter

    > show parameter background       --background가 포함된 parameter조회

    > show parameter user_dump_dest --dump 경로

91. 진단도구 

    - background process가 남기는 log

    - 시스템에 대한 이상징후 및 처리 결과등을 남김

    > show parameter user_dump_dest --dump 경로

      --경로path : C:\app\user\diag\rdbms\orcl\orcl\trace

    > show parameter core_dump

    > show parameter 

91. 진단도구2

    - 정의 : 보통 DB Server의 부하는 하루 중 특정시간대가 존재한다.

             그래서 부하가 시작하기 전과 부하가 종료한 후의 상태를 서로 비교해서 DB를 관리할 수 있다.

    - 진단방법 : 성능에 이슈가 있는 시점(시간)에 이유를 찾기 위해서 사용하는 도구

    - 진단정의 : 특정시간대의 DB Server에대한 상태를 관리(테이블에 저장)

    - 성능이슈 : 회사에서 보통 성능이슈가 있는 시간대가 동일(비슷)하다.

                  그래서 성능이슈가 발생하기 이전시점 ~ 종료시점 까지의 DB상태를 점검할 수 있다.

  

   - 성능이슈 이전시점 : 현상태 저장(시작)(테이블을 생성하여 사용되는 SQL문장등의 상태를 저장)

      >@C:\app\user\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlbstat.sql

   - 성능이슈 발생종료시점 : 현상태 저장(종료)(임시테이블의 내용을 Report로 생성. 임시테이블 삭제)

      >@C:\app\user\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlestat.sql

   - 최종결과는 sqlplus를 수행했던 폴더에  'report.txt' 파일로 생성됨

     . 시작시점의 상태와 종료시점의 상태를 저장해서 두 자료를 비교해서 DB의 차이점을 비교 분석

   - 만약, 파일이 만들어지지 않는다면  cmd창을 수행할때 '관리자권한으로 실행'하기로 하면 됨

   - 단점 : . utlbstat.sql 파일을 수행하면 임시테이블인 $ 테이블들이 생성 됨

             . sys 계정으로 만들기 때문에 항상 일반사용자보다 사용/성능에 우선순위가 부여되어

               일반사용자는 성능이 늦어 진다.

             . sys tablespace쪽에 만들어짐

   - 위 단점을 보완 : statspack package 도구 존재

   - 참고 : 진단시작(utlbstat.sql 수행)을 시작했다고 해서 그때 부터 계속 진단테이블에 저장하는것은 아니다.

            이때 당시의 상태를 snapshop형태로 저장해 놓고, 

            이후 특정시점에 이전 snapshop정보와 서로 비교해서 분석자료를 만들어 낸다.

91. 진단도구2 - report.txt 파일 보기

    . 테이블 : stats$lib; 에 대한 결과

    . GETS : 요청

    . GET HIT RATI : 요청에 대한 memory에서 찾은 비율

92. 진단도구2 - statspack package

    - 정의 : 중요한 포인트(특정시점)마다 DB Server의 상태를 저장 함.

             각 시점간의 DB정보를 기준으로 서로 비교해서 차이점을 분석,비교해준다.

    - 장점 : utlbstat.sql 진단도구의 단점을 보안해서 나옴.

    - 파일 : spcreate.sql

    -수행. 진단시작

     . 사용자생성 : PERFSTAT

     . 성능테스트용 : sp package 생성  (desc statspack)

    >@C:\app\user\product\11.2.0\dbhome_1\RDBMS\ADMIN\spcreate.sql

    - db상태 저장

      . snap 과 snap간의 상태를 볼 수 있음.

    PERFSTAT>desc statpack            --생성된 sp package보기

    PERFSTAT>exec statspack.snap;    --현재의 db상태를 snapshop형태로 찍어서 저장

    PERFSTAT>exec statspack.snap;    --매일 하루에 한번식 수행

   - snapshop 정보 보기

     . 위쪽에 snapshop 목록이 보여줌.

     . 각 snapshop간에 db 상태정보를 볼 수 있다.

    PERFSTAT>@C:\app\user\product\11.2.0\dbhome_1\RDBMS\ADMIN\spreport.sql

    - statspace 도구 삭제

    SYS>@C:\app\user\product\11.2.0\dbhome_1\RDBMS\ADMIN\spdrop.sql

    - statspack package : schedular에 등록하기

      . 등록sql :   select instance_number into :instno from v$instance;

  dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24/60,'MI'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);

        . 앞쪽에 있는 날짜는 : 맨 첫 schedular가 시작하는 시간

        . 뒤쪽에 있는 날짜는 : 맨 첫 schedular가 수행된 후 계속 반복되는 수행간격 시간

                                 (예, SYSDATE+1/24/60  이렇게 한 경우 1분 간격으로 계속 수행 됨)

        . 만약, spauto.sql를 수행했는데 ' ..파일을 열 수 없습니다.' 라는 메세지가 나오면

         cmd창을 새로 열어서 'perfstat' user로 재 login하면 됨

         - 참고, autotrace 가 on 상태인 경우에 안될 수 있음. (set autorace off 수행 하면 됨)

    > @C:\app\user\product\11.2.0\dbhome_1\RDBMS\ADMIN\spauto.sql

   - statspace 테이블 목록 보기

     . 단점 : 너무 많이 나옴

   > SELECT * FROM TAB;

   

   - schedular 삭제

   >select job, what from user_jobs;   --job 목록보기

   >DESC DBMS_JOB

   >EXEC DBMS_JOB.REMOVE(23)    --job 제거

99. 기타

    > HOST       --sqlplus를 빠져 나와서 CMD창으로 이동.

                       --cmd창에서   EXIT를 하면 다시 sqlplus창으로 이동 함

 

    > connect sys/oracle as sysdba   --dba권한으로  sys계정 login

    > startup force                       --db restart. sys만 가능

    > ALTER SESSION SET NLS_LANGUAGE='AMERICAN';  --sql수행 후 결과등을 영문으로 조회