by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

14S_환경설정




SCOTT/TIGER 스키마 설정
  • 많은 예제는 SCOTT 스키마 테이블 사용
  • 스키마 생성 방법1
    @$ORACLE_HOME/sqlplus/demo/demobld.sql
    
  • 스미카 생성 방법2
    CREATE TABLE EMP
    (EMPNO NUMBER(4) NOT NULL,
     ENAME VARCHAR2(10),
     JOB VARCHAR2(9),
     MGR NUMBER(4),
     HIREDATE DATE,
     SAL NUMBER(7, 2),
     COMM NUMBER(7, 2),
     DEPTNO NUMBER(2)
    );
    
    INSERT INTO EMP VALUES (7369, 'SMITH',  'CLERK',     7902,
    TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
    INSERT INTO EMP VALUES (7499, 'ALLEN',  'SALESMAN',  7698,
    TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
    INSERT INTO EMP VALUES (7521, 'WARD',   'SALESMAN',  7698,
    TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
    INSERT INTO EMP VALUES (7566, 'JONES',  'MANAGER',   7839,
    TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
    INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN',  7698,
    TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
    INSERT INTO EMP VALUES (7698, 'BLAKE',  'MANAGER',   7839,
    TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
    INSERT INTO EMP VALUES (7782, 'CLARK',  'MANAGER',   7839,
    TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
    INSERT INTO EMP VALUES (7788, 'SCOTT',  'ANALYST',   7566,
    TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
    INSERT INTO EMP VALUES (7839, 'KING',   'PRESIDENT', NULL,
    TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
    INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN',  7698,
    TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500,    0, 30);
    INSERT INTO EMP VALUES (7876, 'ADAMS',  'CLERK',     7788,
    TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
    INSERT INTO EMP VALUES (7900, 'JAMES',  'CLERK',     7698,
    TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
    INSERT INTO EMP VALUES (7902, 'FORD',   'ANALYST',   7566,
    TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
    INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK',     7782,
    TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
    
    CREATE TABLE DEPT
    (DEPTNO NUMBER(2),
     DNAME VARCHAR2(14),
     LOC VARCHAR2(13)
    );
    
    INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
    INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
    INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
    INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON ');
    
  • 스키마 생성 추가 작업
    alter table emp add constraint emp_pk primary key (empno);
    alter table dept add constraint dept_pk primary key (deptno);
    alter table emp add constraint emp_fk_dept foreign key (deptno) references dept;
    alter table emp add constraint emp_fk_emp foreign key (mgr) references emp;
    
  • 스키마 삭제
    @$ORACLE_HOME/sqlplus/demo/demodrop.sql
    
환경 설정
  • 대부분 예제는 SQL*Plus 환경에서 수행 됨
  • login.sql
    define _editor=vi
    set serveroutput on size 1000000
    set trimspool on
    set long 5000
    set linesize 100
    set pagesize 9999
    column plan_plus_exp format a80
    column global_name new_value gname
    set termout off
    define gname=idle
    column global_name new_value gname
    select lower(user) || '@' || substr( global_name, 1, decode( dot, 0, length(global_name), dot-1) ) global_name
      from (select global_name, instr(global_name,'.') dot from global_name );
    set sqlprompt '&gname> '
    set termout on
    
SQL*Plus 에서 Autotrace 설정
  • 초기 설정
    -- connect system
    @$ORACLE_HOME/rdbms/admin/utlxplan.sql
    
    CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;
    GRANT ALL ON PLAN_TABLE TO PUBLIC;
    
  • PLUSTRACE ROLE 생성
    -- connect sys
    @$ORACLE_HOME/sqlplus/admin/plustrce.sql
    
    GRANT PLUSTRACE TO PUBLIC;
    
  • 리포트 제어
    설정 리포트
    SET AUTOTRACE OFF 생성 안됨 (기본값)
    SET AUTOTRACE ON EXPLAIN 옵티마이저 실행 경로만 보여줌
    SET AUTOTRACE ON STATISTICS SQL 문 실행 통계정보만 보여줌
    SET AUTOTRACE ON 옵티마이저 실행 경로 + SQL 문 실행 통계정보 보여줌
    SET AUTOTRACE TRACEONLY SET AUTOTRACE ON 과 같으나 사용자 쿼리 결과값을 안보여줌
    SQL> set autotrace off
    SQL> select * from dual;
    
    D
    -
    X
    
    SQL> set autotrace on explain
    SQL> select * from dual;
    
    D
    -
    X
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3543395131
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    SQL> set autotrace on statistics
    SQL> select * from dual;
    
    D
    -
    X
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              2  consistent gets
              0  physical reads
              0  redo size
            522  bytes sent via SQL*Net to client
            520  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> set autotrace on
    SQL> select * from dual;
    
    D
    -
    X
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3543395131
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              2  consistent gets
              0  physical reads
              0  redo size
            522  bytes sent via SQL*Net to client
            520  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL> set autotrace traceonly;
    SQL> select * from dual;
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3543395131
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              2  consistent gets
              0  physical reads
              0  redo size
            522  bytes sent via SQL*Net to client
            520  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL>
    
Statspack 설정
-- connect sys

-- 설치
@$ORACLE_HOME/rdbms/admin/spcreate.sql

-- 제거
@$ORACLE_HOME/rdbms/admin/spdrop.sql
사용자 정의 스크립트
  • Runstats
    • 같은 작업을 하는 두 개의 다른 방법을 비교
      • Wall clock or elapsed time
      • System statistics (v$statname, v$mystat)
      • Latching (v$latch)
    • 설치
      drop table run_stats;
      create global temporary table run_stats 
      ( runid varchar2(15), 
        name varchar2(80), 
        value int )
      on commit preserve rows;
      
      grant select any table to ops$tkyte;
      create or replace view stats
      as select 'STAT...' || a.name name, b.value
            from v$statname a, v$mystat b
           where a.statistic# = b.statistic#
          union all
          select 'LATCH.' || name,  gets
            from v$latch
      	union all
      	select 'STAT...Elapsed Time', hsecs from v$timer;
      
      
      delete from run_stats;
      commit;
      
      create or replace package runstats_pkg
      as
          procedure rs_start;
          procedure rs_middle;
          procedure rs_stop( p_difference_threshold in number default 0 );
      end;
      /
      
      create or replace package body runstats_pkg
      as
      
      g_start number;
      g_run1  number;
      g_run2  number;
      
      procedure rs_start
      is 
      begin
          delete from run_stats;
      
          insert into run_stats 
          select 'before', stats.* from stats;
              
          g_start := dbms_utility.get_cpu_time;
      end;
      
      procedure rs_middle
      is
      begin
          g_run1 := (dbms_utility.get_cpu_time-g_start);
       
          insert into run_stats 
          select 'after 1', stats.* from stats;
          g_start := dbms_utility.get_cpu_time;
      
      end;
      
      procedure rs_stop(p_difference_threshold in number default 0)
      is
      begin
          g_run2 := (dbms_utility.get_cpu_time-g_start);
      
          dbms_output.put_line
          ( 'Run1 ran in ' || g_run1 || ' cpu hsecs' );
          dbms_output.put_line
          ( 'Run2 ran in ' || g_run2 || ' cpu hsecs' );
      	if ( g_run2 <> 0 )
      	then
          dbms_output.put_line
          ( 'run 1 ran in ' || round(g_run1/g_run2*100,2) || 
            '% of the time' );
      	end if;
          dbms_output.put_line( chr(9) );
      
          insert into run_stats 
          select 'after 2', stats.* from stats;
      
          dbms_output.put_line
          ( rpad( 'Name', 30 ) || lpad( 'Run1', 12 ) || 
            lpad( 'Run2', 12 ) || lpad( 'Diff', 12 ) );
      
          for x in 
          ( select rpad( a.name, 30 ) || 
                   to_char( b.value-a.value, '999,999,999' ) || 
                   to_char( c.value-b.value, '999,999,999' ) || 
                   to_char( ( (c.value-b.value)-(b.value-a.value)), '999,999,999' ) data
              from run_stats a, run_stats b, run_stats c
             where a.name = b.name
               and b.name = c.name
               and a.runid = 'before'
               and b.runid = 'after 1'
               and c.runid = 'after 2'
               -- and (c.value-a.value) > 0
               and abs( (c.value-b.value) - (b.value-a.value) ) 
                     > p_difference_threshold
             order by abs( (c.value-b.value)-(b.value-a.value))
          ) loop
              dbms_output.put_line( x.data );
          end loop;
      
          dbms_output.put_line( chr(9) );
          dbms_output.put_line
          ( 'Run1 latches total versus runs -- difference and pct' );
          dbms_output.put_line
          ( lpad( 'Run1', 12 ) || lpad( 'Run2', 12 ) || 
            lpad( 'Diff', 12 ) || lpad( 'Pct', 10 ) );
      
          for x in 
          ( select to_char( run1, '999,999,999' ) ||
                   to_char( run2, '999,999,999' ) ||
                   to_char( diff, '999,999,999' ) ||
                   to_char( round( run1/decode( run2, 0, to_number(0), run2) *100,2 ), '99,999.99' ) || '%' data
              from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
                            sum( (c.value-b.value)-(b.value-a.value)) diff
                       from run_stats a, run_stats b, run_stats c
                      where a.name = b.name
                        and b.name = c.name
                        and a.runid = 'before'
                        and b.runid = 'after 1'
                        and c.runid = 'after 2'
                        and a.name like 'LATCH%'
                      )
          ) loop
              dbms_output.put_line( x.data );
          end loop;
      end;
      
      end;
      /
      
  • 실행
    exec runStats_pkg.rs_start;   -- Runstats 테스트 시작
    exec runStats_pkg.rs_middle;  -- Runstats 중간 부분
    exec runStats_pkg.rs_stop;    -- Runstats 종료 부분 (리포트 출력)
    
  • 실행 데모 (TODO)
    create table t1
    as select * from big_table where 1 = 0;
    
    create table t2
    as select * from big_table where 1 = 0;
    
    exec runstats_pkg.rs_start;
    
    insert into t1
    select *  from big_table
    where rownum <= 1000000;
    
    commit;
    
    exec runstats_pkg.rs_middle;
    
    begin
    
      for x in ( select * from big_table where rownum <= 1000000 )
      loop
        insert into t2 values X;
      end loop;
      
      commit;
    
    end;
    /
    
    exec runstats_pkg.rs_stop(1000000);
    
    -- 결과
     Run1 ran in 120 cpu hsecs
    Run2 ran in 2622 cpu hsecs
    run 1 ran in 4.58% of the time
    
    Name                                  Run1        Run2        Diff
    STAT...execute count                   110   1,000,147   1,000,037
    STAT...opened cursors cumulati         111   1,000,154   1,000,043
    LATCH.shared pool                      417   1,002,657   1,002,240
    STAT...recursive calls                 962   1,011,359   1,010,397
    STAT...db block changes            109,047   2,084,843   1,975,796
    STAT...Effective IO time                 0   2,420,814   2,420,814
    LATCH.cache buffers chains         581,941   5,509,280   4,927,339
    STAT...file io wait time         5,130,525       3,786  -5,126,739
    STAT...undo change vector size   3,842,244  67,926,428  64,084,184
    STAT...redo size               119,240,628 381,261,612 262,020,984
    
    Run1 latches total versus runs -- difference and pct
    Run1        Run2        Diff       Pct
    816,211   6,883,628   6,067,417     11.86%
    
    PL/SQL procedure successfully completed.
    
    
  • Mystat
    • mystat.sql
      set echo off
      set verify off
      column value new_val V
      define S="&1"
      column name format a45
      
      set autotrace off
      select a.name, b.value 
      from v$statname a, v$mystat b
      where a.statistic# = b.statistic#
      and lower(a.name) like '%' || lower('&S')||'%'
      -- and lower(a.name) = lower('&S')
      /
      set echo on
      
    • mystat2.sql
      set echo off
      set verify off
      column diff format a18
      select a.name, b.value Value, to_char(b.value-&V,'999,999,999,999') diff
      from v$statname a, v$mystat b
      where a.statistic# = b.statistic#
      and lower(a.name) like '%' || lower('&S')||'%'
      /
      set echo on
      
    • 데모 (TODO)
      SQL> @mystat "redo size"
      SQL> set echo off
      
      NAME                                               VALUE
      --------------------------------------------- ----------
      redo size                                      609969716
      
      SQL>  update big_table set owner = lower(owner) where rownum <= 1000;
      
      1000 rows updated.
      
      SQL> @mystat2
      SQL> set echo off
      
      NAME                                               VALUE DIFF
      --------------------------------------------- ---------- ------------------
      redo size                                      610066552           96,836
      
      -- 참고
      SQL> @mystat "redo size"
      SQL> set echo off
      
      NAME                                               VALUE
      --------------------------------------------- ----------
      redo size                                      610066552
      redo size for lost write detection                     0
      redo size for direct writes                        32480
      
  • Show_Space
    • 데이터베이스 세그먼트의 공간 활용 정보 출력
    • show_space SPEC
      desc show_space
      
      -- P_SEGNAME   : 테이블/인덱스 세그먼트 이름
      -- P_OWNER     : 스키마 (기본값 : 현재유저)
      -- P_TYPE      : 객체 유형 (기본값 : TABLE)
      -- P_PARTITION : 파티션
      
    • show_space DEMO
      SQL> exec show_space('BIG_TABLE');
      Unformatted Blocks .....................               0
      FS1 Blocks (0-25)  .....................               0
      FS2 Blocks (25-50) .....................               0
      FS3 Blocks (50-75) .....................               0
      FS4 Blocks (75-100).....................               0
      Full Blocks        .....................          14,616
      Total Blocks............................          15,360
      Total Bytes.............................     125,829,120
      Total MBytes............................             120
      Unused Blocks...........................             581
      Unused Bytes............................       4,759,552
      Last Used Ext FileId....................               6
      Last Used Ext BlockId...................       2,413,440
      Last Used Block.........................             443
      
      PL/SQL procedure successfully completed.
      
      항목 의미
      Unformatted Blocks 하이 워터 마크 아래에 할당된 블록 수, 사용된 적이 없는 블록 (Unformatted + Unused = ASSM 내에서 사용된 적이 없는 블록)
      FS1 ~ FS4 Blocks 데이터를 가진 포멧된 블록
      Full Blocks 가득 찬 블록
      Total Blocks, Total Bytes, Total Mbytes 세그먼트의 총 공간 정보
      Unused Blocks, Unused Bytes 사용된 적이 없는 공간 정보
      Last Used Ext Field 데이터가 있는 마지막 익스텐트 파일 ID
      Last Used Ext BlockId 마지막 익스텐트의 시작 블록 ID
      Last Used Block 마지막 익스텐트의 마지막 블록의 블록 ID 오프셋
  • show_space
    create or replace procedure show_space
    ( p_segname in varchar2,
      p_owner   in varchar2 default user,
      p_type    in varchar2 default 'TABLE',
      p_partition in varchar2 default NULL )
    -- this procedure uses authid current user so it can query DBA_*
    -- views using privileges from a ROLE and so it can be installed
    -- once per database, instead of once per user that wanted to use it
    authid current_user
    as
        l_free_blks                 number;
        l_total_blocks              number;
        l_total_bytes               number;
        l_unused_blocks             number;
        l_unused_bytes              number;
        l_LastUsedExtFileId         number;
        l_LastUsedExtBlockId        number;
        l_LAST_USED_BLOCK           number;
        l_segment_space_mgmt        varchar2(255);
        l_unformatted_blocks number;
        l_unformatted_bytes number;
        l_fs1_blocks number; l_fs1_bytes number;
        l_fs2_blocks number; l_fs2_bytes number;
        l_fs3_blocks number; l_fs3_bytes number;
        l_fs4_blocks number; l_fs4_bytes number;
        l_full_blocks number; l_full_bytes number;
    
    	-- inline procedure to print out numbers nicely formatted
    	-- with a simple label
        procedure p( p_label in varchar2, p_num in number )
        is
        begin
            dbms_output.put_line( rpad(p_label,40,'.') ||
                                  to_char(p_num,'999,999,999,999') );
        end;
    begin
       -- this query is executed dynamically in order to allow this procedure
       -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
       -- via a role as is customary.
       -- NOTE: at runtime, the invoker MUST have access to these two
       -- views!
       -- this query determines if the object is a ASSM object or not
       begin
          execute immediate 
              'select ts.segment_space_management
                 from dba_segments seg, dba_tablespaces ts
                where seg.segment_name      = :p_segname
                  and (:p_partition is null or 
                      seg.partition_name = :p_partition)
                  and seg.owner = :p_owner
    			  and seg.segment_type = :p_type
                  and seg.tablespace_name = ts.tablespace_name'
                 into l_segment_space_mgmt
                using p_segname, p_partition, p_partition, p_owner, p_type;
       exception
           when too_many_rows then
              dbms_output.put_line
              ( 'This must be a partitioned table, use p_partition => ');
              return;
       end;
    
    
       -- if the object is in an ASSM tablespace, we must use this API
       -- call to get space information, else we use the FREE_BLOCKS
       -- API for the user managed segments
       if l_segment_space_mgmt = 'AUTO'
       then
         dbms_space.space_usage 
         ( p_owner, p_segname, p_type, l_unformatted_blocks,
           l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
           l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
           l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);
         
         p( 'Unformatted Blocks ', l_unformatted_blocks );
         p( 'FS1 Blocks (0-25)  ', l_fs1_blocks );
         p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
         p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
         p( 'FS4 Blocks (75-100)', l_fs4_blocks );
         p( 'Full Blocks        ', l_full_blocks );
      else
         dbms_space.free_blocks(
           segment_owner     => p_owner,
           segment_name      => p_segname,
           segment_type      => p_type,
           freelist_group_id => 0,
           free_blks         => l_free_blks);
         
         p( 'Free Blocks', l_free_blks );
      end if;
    
      -- and then the unused space API call to get the rest of the 
      -- information
      dbms_space.unused_space
      ( segment_owner     => p_owner,
        segment_name      => p_segname,
        segment_type      => p_type,
        partition_name    => p_partition,
        total_blocks      => l_total_blocks,
        total_bytes       => l_total_bytes,
        unused_blocks     => l_unused_blocks,
        unused_bytes      => l_unused_bytes,
        LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
        LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
        LAST_USED_BLOCK => l_LAST_USED_BLOCK );
    
        p( 'Total Blocks', l_total_blocks );
        p( 'Total Bytes', l_total_bytes );
        p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
        p( 'Unused Blocks', l_unused_blocks );
        p( 'Unused Bytes', l_unused_bytes );
        p( 'Last Used Ext FileId', l_LastUsedExtFileId );
        p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
        p( 'Last Used Block', l_LAST_USED_BLOCK );
    end;
    /
    
  • Big_Table
    create table big_table
    as
    select rownum id, a.*
      from all_objects a
     where 1=0
    /
    alter table big_table nologging;
    
    declare
        l_cnt number;
        l_rows number := 1000000;
    begin
        insert /*+ append */
        into big_table
        select rownum, a.*
          from all_objects a
    	 where rownum <= 1000000;
    
        l_cnt := sql%rowcount;
    
        commit;
    
        while (l_cnt < l_rows)
        loop
            insert /*+ APPEND */ into big_table
            select rownum+l_cnt, 
                   OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
                   OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
                   TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME
              from big_table
             where rownum <= l_rows-l_cnt;
            l_cnt := l_cnt + sql%rowcount;
            commit;
        end loop;
    end;
    /
    alter table big_table add constraint
    big_table_pk primary key(id);
    
    exec dbms_stats.gather_table_stats( user, 'BIG_TABLE', estimate_percent=> 1);
    
코딩 규칙
  • PL/SQL 코드에서 변수 명 설정
    create or replace package body my_pkg
    as
       g_variable varchar2(25);
    
       procedure p( p_variable in varchar2 )
       is
         l_variable varchar2(25);
       begin
         null;
       end;
    end;
    /
    
    -- g_variable : 글로벌 패키지 변수
    -- p_variable : 프로시저 형식 매개 변수
    -- l_variable : 로컬 변수
    
    -- 테이블의 컬럼과 PL/SQL 변수 구분을 위한 PREFIX("g_" / "p_" / "l_")
    
  • 나쁜 예
    create procedure p( ENAME in varchar2 )
    as
    begin
      for x in ( select * from emp where ename = ENAME ) loop
        dbms_output.put_line( x.empno );
      end loop;
    end;
    
    -- EMP.ENAME IS NOT NULL 인 모든 레코드 출력
    -- 회피1 : ENAME => P.ENAME (변수 명시)
    -- 회피2 : ENAME => P_ENAME (변수 이름 변경)
    

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.