웹 개발과 관련된 모든 것들

http://www.ex-em.com/web/research/research_pub.php

출처: http://exem.tistory.com/891 [엑셈 이야기]

출처: http://exem.tistory.com/891 [엑셈 이야기]

SQL튜닝의 시작 사용_SQL.zip

ORA-27086: unable to lock file - already in use

출처 : http://osamamustafa.blogspot.kr/2013/02/ora-27086-unable-to-lock-file-already.html

1. cpu 및 실행 시간의 plan을 아래와 같이 수행 후

explain plan for

          select * from dual;

2. 해당 plan 내역 조회

select * from table(dbms_xplan.display);

time(수행시간)이 나오지 않고

plan_table is old version 

에러가 뜨면 나타 나지 않을 때는

조치 방법

* EXPLAIN PLAN 시 이름 주고 조회하기

EXPLAIN PLAN SET STATEMENT_ID='TSH' FOR

SELECT *

FROM   emp e, dept d

WHERE  e.deptno = d.deptno

AND    e.ename  = 'SMITH';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'TSH', 'ALL', NULL));

1 번째 인자 : PLAN TABLE 명으로 NULL 명시해도 됨(기본 사용)

2 번째 인자 : EXPLAIN PLAN SET STATEMENT_ID 에서 사용한 이름 (여러명이 동시 사용시에는 이름이 나와야 정확함) -> NULL은 마지막 값 조회

3. 번째 인자 : DBMS_XPLAN.DISPLAY 에서 상세하게 볼 때 사용하는 옵션으로 TYPICAL, ALL, BASIC 등이 있음

참고 : http://web-dev.tistory.com/662

참고 사이트 : http://stackoverflow.com/questions/25613444/how-to-create-explain-plan-table-on-amazon-rds-database

hp ux 장비에서 ndd 설정하기

- 값 조회

ndd -get /dev/sockets socket_udp_rcvbuf_default

ndd -get /dev/tcp tcp_smallest_anon_port

ndd -get /dev/udp udp_smallest_anon_port

ndd -get /dev/tcp tcp_conn_request_max

ndd -get /dev/tcp tcp_syn_rcvd_max

- 값 설정

ndd -set /dev/sockets socket_udp_rcvbuf_default 1310720

ndd -set /dev/tcp tcp_smallest_anon_port        9000

ndd -set /dev/udp udp_smallest_anon_port        9000

ndd -set /dev/tcp tcp_conn_request_max          10240

ndd -set /dev/tcp tcp_syn_rcvd_max              1024

- 리부팅 후에도 적용하도록 환경 파일 설정(100 ~ 104 번은 중복되지 않도록)

/etc/rc.config.d/nddconf 

TRANSPORT_NAME[100]=sockets

NDD_NAME[100]=socket_udp_rcvbuf_default

NDD_VALUE[100]=1310720

TRANSPORT_NAME[101]=tcp

NDD_NAME[101]=tcp_smallest_anon_port

NDD_VALUE[101]=9000

TRANSPORT_NAME[102]=udp

NDD_NAME[102]=udp_smallest_anon_port

NDD_VALUE[102]=9000

TRANSPORT_NAME[103]=tcp

NDD_NAME[103]=tcp_conn_request_max

NDD_VALUE[103]=10240

TRANSPORT_NAME[104]=tcp

NDD_NAME[104]=tcp_syn_rcvd_max

NDD_VALUE[104]=1024

오라클에서 

EXP -> IMP 하는 방식에는

exp 테이블 스키마 추출

exp id/passwd file=./temp.dmp log=./log.log compress=n rows=n

 imp id/passwd indexfile=create.sql full=y 옵션을 주어

입력하지는 않고 create.sql을 만들어

스크립트 형태로 수동으로 돌릴 수 있다.

다만,

그냥 import 했을 때는

커져버린 초기값을 만나게 되는데...

이것을 수정하는 방법은

initial, next 값 수정!

- 테이블 스페이스 생성시 local로 생성해야만 함 ~

확인 쿼리

SELECT initial_extent, next_extent, pct_increase,

            extent_management, allocation_type

     FROM DBA_TABLESPACES

     WHERE tablespace_name=upper('테이블스페이스명');

- initial 값 수정

1. 분석하기 (emp1 테이블 분석)

analyze table emp1 compute statistics;

2. 분석을 기준으로 사용하지 않는 불럭 초기화

alter table emp1 deallocate unused keep 0;

3. 블럭 사이즈  확인 하는 법

select table_name, initial_extent from user_tables;

** 다른 방법

1. alter and move the table to another tablespace. e.g. (다른 테이블 스페이스로 옮기기)

ALTER TABLE MY_TABLE MOVE TABLESPACE ANOTHER_TABLESPLACE STORAGE (INITIAL 2M NEXT 2M PCTINCREASE 0);

2. alter and move the table back to the original tablesace with the desired initial extent size, e.g. (원 테이블 스페이스에 옮기기)

ALTER TABLE MY_TABLE MOVE TABLESPACE ORIGINAL_TABLESPACE STORAGE (INITIAL 256M NEXT 2M PCTINCREASE 0);

- next 값 수정

위와 동일하며

alter table emp1 storage(next 10M);

으로 가능함 

후암..

잘 다니던 회사를 퇴직하고

오라클이 필요할때... -_-....

어렵다.. 구하기 어렵다..

다행히도 otn 정보는 아직 유효하여

참고 사항을 올린다. ~

1. 오라클 사이트 (https://support.oracle.com ) 방문하여 로그인한다.

   : 예전회사는 OTN 계약을 별도로 했었다... -_-...

 - 로그인 후 우측 상단에 Contact US 를 눌러 아래 화면을 띄운다.

    ※ 참고로 한글 언어셋으로 설정시 안된다는 소문(?)을 들어 영문으로 설정하고 시작하였다.

    Problem Summary : DVD

    => 문제점을 요약하면 : DVD  (ㅋㅋㅋ -_-..)

    Support Identifier : 현재 유효한 8자리 ID값이 출력됨. (우측 상단에 Next를 눌러 넘어감 : 총 3단계!)

 - 2단계 각 화면들이다.

    Problem Type : Software & OS Media Requests

    문제 유형 : 소프트웨어와 OS 미디어 요청

    1) IS this a physical software media shipment request?

       이것은 물리적 소프트웨어 미디어 수송 요청인가?

       -> 난 물리적으로 받고 싶다 -_- Yes ~

    2) Which Product line does the media request involve?

        Oracle Database

        미디어 포함 요청에 어떤 제품 라인을 할것인가?

        난 오라클 10g 이므로 Oracle Database

    3) Are you requesting a required password for a path download?

        NO

        다운로드 경로를 위한 패스워드가 필요한 요청인가?

        다운로드 한 것에 대한 암호를 설정하는 것 같은데.. 필요 없으므로.. NO.... (잘 모르겠음 -_- ㅋ)

   4) Are you requesting a patch on CD/DVD?

      NO

      당신은 CD/DVD의 패치가 필요한가?

      아니요 

      난 설치본이 필요함 -_-... 패치는 otn에서 걍 받을꺼임...

   5) 4번항목의 패치 필요한 내역을 기재하는 것인데.. 없으므로 난 스킵 (*가 아닌것은 필수 아님)

   6) List the product name and version requested for physical media shipment?( EX: E-business Suite 12.1.1...)

       Oracle Database 10G / Oracle Client 10G

       물리적 미디어 배송을 위한 제품 이름과 버전 목록을 요청하라.

       오라클 데이터베이스 서버 10g와 오라클 클라이언트 10G

   7) What is the OS/platform for the requested media?(EX : Windows 32bit. ....)

       IBM Aix Power 32,64 bit / hp up risc 32/64 bit

       요청한 미디어의 OS(운영체제)와 Platform(플렛폼)은 무엇인가?

      IBM Aix Power 시리즈의 32,64 bit와

      HP ux risc 32/64 비트

 8) Are any language required for this shipment?

     YES

     이번 배송을 위한 어떠한 언어라도 괜찮은가?

  11) Ship to Contact Name :

      배송을 위한 이름 : 

12. 배송을 위한 전화번호

     82(국제번호)-10(010이라도 0을 뺌)-xxxx-xxxx

13. 배송을 위한 이메일 주소

     aaa@naaa.aaa

14. 회사명 :

15. 주소1 :

     실제 주소를 알면 -> 다음, 네이버등을 통해 주소 영문 변환으로 검색 뒤 ...

                                  한글 주소를 영문으로 변환하여 붙여넣기 한다.

위와 같이 입력뒤 우측 상단에 Next를 누른다.

아래는 최종 단계인 Contact 확정 단계로

Primary Contact : 이름

Phone Numbers : 전화번호

Email-Address : 이메일 주소

Coontact Method : WEB (웹상에 아래 조회항목을 통해 확인 가능할듯...)

(영어가 약해서 전화오면 챙피해서 죽어버릴지도 모름 -_-....)

위와 같은 최종 확인 뒤 우측 상단에 Submit를 눌러 확정함.

위와 같이 정상 Submit 하고 난뒤 상태를 조회하거나 WEb를 통한 메시지를 확인하고자 할 때는

상단에 Service Requests 메뉴를 클릭뒤

중간부분에 Contact Us Service Reqests 항목에 조회된다.

하나의 포트로 여러 리스너 설정하기...

listener.ora

일부 서버들에서 되고 안되고 문제는 SID_LIST_LISTENER 설정에서 SID_DESC 설정 정보가 없을 때 발생함... (랜덤 -_-ㅋ)

 sqlnet.ora

출처 : http://blog.naver.com/onlywin7788?Redirect=Log&logNo=140155221360

출처 : http://cafe.naver.com/ocmkorea/book2025209/9365

/*

--> 방식 1. (이 sql문은 이 방식임)

        1. 테이블 전체 목록 조회

           SQL> select * from tab;

        2. 수행한 쿼리의 Plan을 조회

           SQL> @xplan.sql

        3. 정확한 통계를 위해 힌트 실행

           SQL> select /*+ gather_plan_statistics */ * from tabs;

        * 오타 나지 않도록 주의함.

--> 방식 2

        1. 실행시 Plan  수집

           SQL> EXPLAIN FOR

                 SELECT * FROM TAB;

        2. 실제 정보 조회

           SQL> select * from tab(dbms_xplan.display());

                or

           SQL> select *

                from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST +alias +outline +predicate'))

/

--> 인자 정보

        dbms_xplan.display_cursor(인자1, 인자2, 인자3);

        인자1 : plan table 명을 입력하며, 따로 지정하지 않았을 때는 기본으로 'PLAN_TABLE'이다.

        인자2 : Execution Plan시 Set STATEMENT_ID를 지정한 경우 이를 불러올 수 있다.

                값이 Null일 경우 마지막에 실행된 문장을 불러온다.

        인자3 : 출력 포맷 형태 지정한다.

--> 세번째 인자의 포맷 정보

        1) 기본 Format Controller : 반드시 적용되어야 하는 기본적인 Controller임.

                                    적용하지 않더라도 자동으로 기본값으로 적용된다.

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

                basic    : 가장 기본적인 포맷으로서 Id, Operation, Object Name을 출력한다.

                typical  : basic 옵션에서 한발 더 나아가서 Optimizer가 예상할 수 있는 모든 것을 보여준다.

                           출력되는 정보로는 예상 Rows, 예상 Bytes, 예상 Temporary Space 사용량, Cost 예상 시간 Predicate Imformat(Operation 별로 Access 및 Filter 정보)이다.

                serial   : typical과 같으나 Parallel 쿼리 사용시 관련정보가 나오지 않는다.

                all      : Plan 정보는 typical과 같으나 Plan이외의 정보 중에서 Outline Data 정보를 제외하고 전부 출력한다.

                advanced : all과 같지만 Peeked Binds, Outline Data, Note 등을 더 보여준다.

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

        2) 세부 Format Controller : 기본 포맷정보에 의해서 표시되거나 생략되는 세부적인 포맷을 Control한다.

                                    이 Control은 +표시로 추가되거나 - 표시로 생략이 가능하다.

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

                alias    : 가장 기본적인 포맷으로서 Id, Operation, Object Name을 출력한다.

                bytes    : basic 옵션에서 한발 더 나아가서 Optimizer가 예상할 수 있는 모든 것을 보여준다.

                           출력되는 정보로는 예상 Rows, 예상 Bytes, 예상 Temporary Space 사용량, Cost 예상 시간 Predicate Information (Operation 별로 Access 및 Filter 정보)이다.

                cost     : typical과 같으나 Parallel쿼리 사용시 관련 정보가 나오지 않는다.

                note     : Plan 정보는 typical과 같으나 Plan이외의 정보 중에서 Outline Data 정보를 제외하고 전부 출력한다.

                outline  : all과 같지만 Peeked Binds, Outline Data, Note 등을 더 보여준다.

                parallel : Parallel 쿼리인 경우 TQ, IN-OUT, PQ Distribute 등의 정보를 Control한다.

                partition : Partition Access가 포함된 경우

                peeked_binds : Bind 변수의 값을 control 한다. 단, _optim_peek_user_binds 파라미터의 값이 true로 되어 있는 경우에만 해당되며 파라미터는 세션 단위로 수정이

                               가능하다. (Explain Plan은 출력되지 않음)

                predicate : Predicate Information을 Control한다. Operation 별로 Access 및 Filter 정보를 나타낸다.

                            일반적인 튜닝시 가장 눈 여겨봐야 할 정보이다.

                projection : Projection Information을 Control한다. Operation 별로 Select 되는 컬럼 정보를 나타낸다.

                remote   : DB Link를 사용할 때 Remote 쿼리의 수행 정보를 Control한다.

                rows     : Plan상의 E-Rows 수를 Control한다.

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

        3) 실행통계 Format Controller : 이 Control을 적용하면 실행시의 PGA 통계를 출력한다.

        이 정보들은 DBMS_XPLAN.DISPLAY 함수에는 적용되지 않는다.

        왜냐하면 Explain Plan은 실제 수행되는 것이 아니므로 실행통계정보가 없기 때문이다.

        또한 DBMS_XPLAN.DISPLAY_CURSOR 이나 DBMS_XPLAN.DISPLAY_AWR등의 함수 수행시에도 GATHER_STATISTICS 힌트를 주거나 아니면 STATISTICS_LEVEL 파라미터를 ALL로 설정해야 출력이 가능하다.

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

                allstats : I/O 통계정보(Buffers, Reads, Writes)와 PGA 통계 정보(OMem, 1Mem, Used-Mem, Used-Tmp, Max-Tmp 등)를 동시에 Control 한다.

                iostats  : I/O 통계정보(Buffers, Reads, Writes)를 Control 한다.

                last     : 실행 통계 출력시 이 Control을 명시하면 가장 마지막에 수행된 실행 통계를 출력한다.

                           이 Control을 명시하지 않으면 실행 통계츼 누적치를 출력하므로 주의가 필요합니다.

                memstats : PGA 통계정보(OMem, 1Mem, Used-Mem, Used-Tmp, Max-Tmp등)를 동시에 Control한다.

                runstats_last : iostats과 last Control 과 동일하다.

                                이 Control은 Oracle 10g Release 1에서만 사용할 수 있다.

                runstats_tot : iostats Control과 동일하다.

                               이 Control은 oracle 10g Release 1 에서만 사용할 수 있다.

                * runstats_last와 runstats_tot를 제외한 4가지의 Control은 Oracle 10g Release 2 이상에서만 사용할 수 있다.

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

        4) format 사용 예제

        Format중 가장 많은 정보를 출력 할 수 있는 포맷은 'advanced allstats last'이며 출력 가능한 모든 내용이 출력되게 되므로 상황이나, 자신에 맞는 포맷을 사용해야 적절함.

        4-1) 쿼리 변형이 없는 단순 쿼리 튜닝의 경우(최대한 단순한 포맷형태 사용)

             SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'allstats last-rows + predicate');

             => 포맷을 'allstats last-rows + predicate'로 주었으므로 예측 Row 수(E-row)가 생략되고 실행통계와 Predicate Information 만 출력된다.

        4-2) 쿼리 변형이 발생하거나 복잡한 쿼리 튜닝시 쿼리 블러과 힌트 정보를 추가로 출력

             SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'allstats last-rows + alias + outline + predicate');

             => 'allstats last-rows + alias + outline + predicate' 포맷을 사용하면 Query Block Name / Object Alias 정보와 Outline Data 정보가 추가로 출력된다.

                + alias는 쿼리블럭 정보를 추가하는 것이며, + Outline는 오라클이 내부적으로 사용하는 힌트정보 출력의 의미이다.

                쿼리 변형이 발생한 경우나 뷰(혹은 인라인뷰) 등을 튜닝할 경우 적합한 옵션이다.

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

        10046 + tkprof 와 동일한 정보를 조회는

        select /*+ gather_plan_statistics */ * from tab;

        select *

        from table(dbms_xplan.display_cursor(null, null, 'advanced ALLSTATS LAST +alias +outline +predicate'))

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

AWR 정보를 이용

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(SQL_ID입력,NULL,NULL, 'ADVANCED ALLSTATS LAST'));

사용 권한 뷰

DISPLAY_CURSOR 함수 : V$SQL_PLAN, V$SESSION, V$SQL_PLAN_STATISTICS_ALL 뷰에 대한 SELECT 권한 필요

DISPLAY_AWR    함수 : DBA_HIST_SQL_PLAN, DBA_HIST_SQLTEXT, V$DATABASE 뷰에 대한 SELECT 권한 필요

DISPLAY_SQLSET 함수 : ALL_SQLSET_STATEMENTS, ALL_SQLSET_PLANS 뷰에 대한 SELECT 권한 필요

*/

select *

  from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST +alias +outline +predicate'))

/