SQL Plan 오류 없이 조회하기

Oracle 2023. 9. 21. 18:58

검색해보면 온통 다음 방식으로 플랜을 조회하는 것으로 설명되어 있다.

EXPLAIN PLAN FOR ${SELECT STMT}
SELECT * FROM table (DBMS_XPLAN.DISPLAY);

그러나 실제로는 커서가

검색해보면 온통 다음 방식으로 플랜을 조회하는 것으로 설명되어 있다.

EXPLAIN PLAN FOR ${SELECT STMT};
SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST');

그러나 다음과 같은 오류가 발생하면서 플랜이 나오지 않는 경우가 발생할 수 있다.

  NOTE: cannot fetch plan for SQL_ID: ***********, CHILD_NUMBER: 0
    Please verify value of SQL_ID and CHILD_NUMBER; 
    It could also be that the plan is no longer in cursor cache (check v$sql_plan)

set serverouput=on 인 경우, 쿼리 수행 이후에 DBMS_OUTPUT 버퍼에 출력할 내용이 있는지

sqlplus가 추가적인 call을 하는 단계가 끼어들면서 이런 현상이 발생하는데,

set serveroutput=off 하면 일반적으로 이런 증상을 해결할 수 있다.

 

이런 현상을 해결하기 위해 DISPLAY_CURSOR() 대신, DISPLAY() 를 사용하라거나

v$sql_plan에서 방금 수행한 SQL을 찾아 cursor와 child_number를 일일이 찾는 방식을 추천하는 글도 많은데

 

이럴 때, 다음과 같이 loop로 묶어버리면 중간에 다른 동작이 개입될 일 없이

항상 플랜을 조회할 수 있다.

--alter session set current_schema=________;
set lines 4000
set pages 0 embedded on
-- execute dbms_session.set_sql_trace(true);
set serveroutput on size unlimited;
alter session set STATISTICS_LEVEL='ALL';
--alter session set optimizer_use_invisible_indexes=true;
--alter session set "_OPTIM_PEEK_USER_BINDS"=true;

-- var v1 varchar2(20);
-- exec :v1 := '________';

begin
for SQLQuery in (
-- /*+ GATHER_PLAN_STATISTICS */

${플랜 조회할 SQL문을 여기에, 맨 끝에 ";" separator 없이}

)
loop
null;
end loop;


-- dbms_xplan query
for SQLPlan in
(select plan_table_output pto from table(dbms_xplan.display_cursor(null, null, 'advanced allstats last +peeked_binds -projection -alias -outline +remote')))
loop
dbms_output.put_line(SQLPlan.pto);
end loop;
end;
/
저작자표시 비영리 변경금지
Posted by in0de
,

ORA-08104 : this index object is being online built or rebuilt.

Oracle 2018. 4. 19. 19:13

online 옵션을 사용해 인덱스를 생성하다가 비정상적으로 종료했을 경우

drop index, alter index rebuild 등의 작업이 다음과 같은 오류 발생하며 아무 것도 할 수 없게 되는 경우가 있다.


ORA-08104: 인덱스 객체 nnnnn 은(는) 온라인 구축 중이거나 재구축 중입니다



이 때 object id nnnnn을 인자로 다음과 같이 clean 할 수 있다.

sys 유저로 수행해야 한다.


declare

v_ret boolean;

begin

v_ret := dbms_repair.online_index_clean(nnnnn);

end;

/


만약, sys 유저로 수행하지 않은 경우는 다음과 같이 오류 발생한다.


ORA-06550: PLS-00201: 'DBMS_REPAIR.ONLINE_INDEX_CLEAN' 식별자가 정의되어야 합니다



저작자표시 비영리 변경금지
Posted by in0de
,

Pipe를 통해 export, load 하기

Oracle 2017. 9. 8. 16:41

데이터를 export 하여 가령 다른 서버로 전송한 후 sqlloader로 import할 때

특히 네트워크 속도가 좋지 않은 경우

덤프파일을 압축하면 전체적으로 시간을 절감할 수 있다.


데이터를 export할 공간이 넉넉하지 않은 경우에도 도움이 될 수 있다.



데이터 export 수행할 쿼리 파일을 생성한다.

-- in export.sql
SET term on
SET echo off
SET UNDERLINE off
SET HEADSEP off
SET TRIMSPOOL on
SET TRIMOUT on
SET COLSEP ","
SET SQLPROMPT ''
SET PAGESIZE 0
SET LINESIZE 32767
SET LONG 999999999
SET TERMOUT OFF
SET FEEDBACK OFF

SPOOL export.pipe;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SELECT

    col1

    ||'@@'|| col2

    ||'@@'|| col3

    ||'@@'|| col4

    ||'@@'||  col5
FROM TBL WHERE 1=1;
SPOOL OFF;
quit;
/

맨 마지막에 quit; / 부분이 없으면, 쿼리를 수행한 후 sqlplus가 종료되지 않고 멍하니 멈춰있게 되므로 유의한다.


sqplus로 데이터 export → pipe 파일 → gzip 으로 direct하여 export와 동시에 압축되도록 한다.

rm -f ./export.pipe

mkfifo ./export.pipe
chmod 770 ./export.pipe
cat ./export.pipe | gzip > ./export.csv.gz &
sqlplus -s ${DBUSER}/${password} @export.sql
rm -f ./export.pipe




반대로, 이 파일을 sqlload할 때는 다음과 같이 수행한다.

-- in import.ctl

LOAD DATA
INFILE import.pipe
BADFILE import.bad
APPEND INTO TABLE TBL
FIELDS TERMINATED BY "@@"
TRAILING NULLCOLS
(

col1

,col2

,col3

,col4 CHAR           -- load하는 타겟 테이블의 칼럼이 VARCHAR2 칼럼일 경우 굳이 명시하자면

,col5 INTEGER       -- load하는 타겟 테이블의 칼럼이 NUMBER일 경우

)


rm -f ./import.pipe

mkfifo ./import.pipe
chmod 770 ./import.pipe
gunzip < ./export.csv.gz > ./import.pipe &

sqlldr userid=${DBUSER}/${password} control=import.ctl errors=100 rows=5000

rm -f ./import.pipe

INFILE을 지정할 때 현재 디렉토리임을 명시하려고 ./import.pipe로 쓰면 오류가 발생하므로

그냥 파일이름만 써줘야 한다.

마찬가지로 sqlldr 커맨드에서 control 파일을 control=./import.ctl 로 지정하면 오류 발생한다.


컨트롤 파일을 통해 여러 옵션을 조정할 필요가 없다면

간단히 sqlldr userid=${DBUSER}/${password} data=import.pipe 로 수행할 수도 있다.

5000건 단위로 commit을 위해 rows=5000을 지정했지만, 실제 수행될 때는 5천 건 단위로 커밋을 찍지 않는데, 이런 경우에는 bindsize 옵션을 추가해볼 수 있다.

저작자표시 비영리 변경금지
Posted by in0de
,

Undo tablespace full로 인한 서비스 실패를 막기 위한 시나리오

Oracle 2017. 8. 2. 23:38

Undo segment를 과도하게 사용하여

예를 들어, 개발한 배치, 개발자의 임의 작업 등이 잘못 진행되고 있다가 뒤늦게 발견하여 cancel했을 경우라던가 할 때

이를 rollback을 하는 중이지만 UNDO tablespace의 unexpire 영역이 반환되지 않아,

정규 배치 등의 서비스가 실패할 가능성이 있는 시나리오에 대응하려면

다음 쿼리들을 통해 문제를 해결하도록 한다.



Rollback 진행 중인 undo segment를 조회하는 쿼리


parallel rollback 일 경우

select * from v$fast_start_transactions where state='RECOVERING';


serial rollback 일 경우 (x$KTUXE(Kernel Transaction Undo Transaction Entry))

select ktuxesiz from sys.xm$ktuxe where ktuxesta = 'ACTIVE' and KTUXECFL = 'DEAD' ;


Undo tablespace의 사용량은 다음과 같이 확인한다.

col msg format a80
WITH TB AS (
    SELECT TABLESPACE_NAME
          ,(SELECT ROUND(SUM(BLOCKS)*8/1024) FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=A.TABLESPACE_NAME) TOT_MB
          ,SUM(DECODE(A.STATUS,'ACTIVE'   ,SIZE_MB,0)) ACTIVE_MB
          ,SUM(DECODE(A.STATUS,'UNEXPIRED',SIZE_MB,0)) UNEXPI_MB
          ,SUM(DECODE(A.STATUS,'EXPIRED'  ,SIZE_MB,0)) EXPIRE_MB
    FROM  (
           SELECT TABLESPACE_NAME, STATUS, ROUND(SUM(BYTES)/1024/1024) SIZE_MB
           FROM   DBA_UNDO_EXTENTS
         --WHERE  tablespace_name in ( select value from v$parameter where name='undo_tablespace')
           GROUP  BY TABLESPACE_NAME, STATUS
          ) A
    GROUP BY TABLESPACE_NAME
)
SELECT to_char(sysdate,'HH24:MI:SS')||' '||TABLESPACE_NAME||' : '||round((active_mb+unexpi_mb+expire_mb)/tot_mb*100)||
       ' % -> Act('||active_mb||') Unexp('||unexpi_mb||') Exp('||expire_mb||') TOT('||TOT_MB||')' MSG,
       round(100*(ACTIVE_MB)/TOT_MB,2),
       round(100*(ACTIVE_MB+UNEXPI_MB)/TOT_MB,2)
FROM TB;


현재 진행되고 있는 rollback의 잔여시간은 다음 쿼리로 대충 추산할 수 있다.

set serveroutput on
declare
    l_start number ;
    l_end   number ;
    r_min   number ;
begin
    select ktuxesiz into l_start from sys.xm$ktuxe where ktuxesta = 'ACTIVE' and KTUXECFL = 'DEAD' ;
    dbms_lock.sleep(60);
    select ktuxesiz into l_end   from sys.xm$ktuxe where ktuxesta = 'ACTIVE' and KTUXECFL = 'DEAD' ;
    r_min := round(l_end / (l_start - l_end)  ) ;
    dbms_output.put_line('['||to_char(sysdate,'HH24:MI:SS')||'] Remain Time : '|| r_min ||'분 '|| round(r_min/60,2)||'시간 / '||(l_start-l_end)||' blocks done / '|| l_end ||' Total blocks remains' );
end ;
/


운영DB에 임시 UNDO Tablespace를 생성한 후, 향후의 트랜잭션들은 이 UNDO tbs를 사용하도록 조치한다.

create undo tablespace undotbs2 datafile '/dev/rdsk/vsp1_000_ra000/dat_000_20G_001';

alter tablespace undotbs2 add datafile '/dev/rdsk/vsp1_000_ra000/dat_000_20G_002';

...

alter tablespace undotbs2 add datafile '/dev/rdsk/vsp1_000_ra000/dat_000_20G_NNN';


alter system set undo_tablespace=undotbs2 ;

이때 UNDO tablespace를 변경하는 것 자체는 금방 수행되므로 긴장하지 않아도 된다.

다만, alert log에 'active transactions found in undo tablespace NN - moved to pending switch-out state 라는 로그가 트랜잭션 당 1 라인씩 계속 발생할 수 있는데, 어떤 사이트의 경우에는 이 메시지가 다량 발생하기도 하였다고 하므로, alert log가 존재하는 path의 가용량을 모니터링하면서 작업 수행하자.

이러한 세션들은 세션에서 commit을 찍고 알아서 종료되길 기다리거나, 아니면 kill하여 처리하면 된다.


UNDO tablespace를 switch 한 후, 이전의 UNDO tbs를 사용하는 채로 commit 되지 않고 남아있는 세션을 찾아본다

select a.usn, a.name, b.status, c.tablespace_name, d.addr, e.sid, e.serial#, e.username, e.program, e.machine, e.osuser
from   v$rollname a, v$rollstat b, dba_rollback_segs c, v$transaction d, v$session e
where  a.usn=b.usn
and    a.name = c.segment_name
and a.usn = d.xidusn
and    d.addr = e.taddr
and b.status = 'PENDING OFFLINE';


rollback이 종료되고 상황이 정상화 되면,

기존 UNDO tablespace를 사용하도록 파라미터를 재설정해주고,

추가했었던 UNDO tablespace를 drop하면 된다.

저작자표시 비영리 변경금지
Posted by in0de
,

오라클 Hybrid Columnar Compression (HCC) 의 제약

Oracle 2016. 4. 6. 15:38

1. 압축률

  • 현재 DB2 DW의 압축률 = 70% 수준 (table 단위의 block compression)
  • 현재 운영 Oracle의 압축률 = 55% 수준 (row 단위 compression)
  • DB2 BLU (columnar)의 압축률 = 95% (hybrid compression)
  • DB2 Netezza (columnar)의 압축률 = 75%
  • 오라클 HCC (columnar)의 압축률 = 85% ~ 92.5% (외부 압축 알고리즘 도입)


    ■ 압축률
    - Query Low : 85%
    - Query High :   
    - Archive Low :
    - Archive High : 92.5%

실질적으로 운영환경에서 사용할 수 있는 압축방식은 제공되는 4 가지 옵션 중
성능의 문제로 인해 Query Low 하나라고 보면 되겠고,

Archive Low/High 압축의 경우는
곧바로 읽을 수 있다는 장점이 있는 마그네틱 테이프를 생각하시면 되겠다.


■ Query Low
    CU (Compression Unit)의 크기 : 32k
    LZO 알고리즘

■ Query High
    CU 크기 : 32~64k
    ZLIB 알고리즘

■ Archive Low
    CU 크기 : 32~256k
    ZLIB 알고리즘


■ Archive High
    BZIP2 알고리즘






2. 성능
압축 시 데이터 크기 자체가 줄어들음으로 인해, 조회 성능은 개선되나,
데이터 ETL 시 불리하다.

■ Insert 성능

query low = 20초
query high = 40초
archive low = 40초
archive high = 240초


■ SQL 쿼리 성능

비압축 = IO bound :171초, CPU bound : 13초
query low = IO bound :91초, CPU bound : 16초
query high = IO bound :73초, CPU bound : 19초
archive low = IO bound :74초, CPU bound : 19초
archive high = IO bound :118초, CPU bound : 33초





3. 기타 기술적인 제약

- 특성 상 데이터의 Sort 상태를 유지하여야 압축률이 높아짐

- 조회한 데이터를 메모리 버퍼에 올리거나, aggregate 또는 sort 동작을 위해 temporary 영역에 넣으려면 압축을 해제해야 함

- Index를 읽어 데이터를 조회할 때, 하나의 CU를 여러 번 압축 해제 해야 하는 문제로 성능이 저하될 수 있음
  ∵ 정렬된 rowid 순서로 select하는 게 아니라, 컬럼 데이터 순서로 정렬된 rowid 포인터로 CU를 읽기 때문

- HCC 압축된 row에 update가 수행되면, 이 데이터는 압축이 풀리면서 기존의 낮은 압축률 공간으로 옮겨지며
  이 과정도 느릴 뿐더러, 향후 조회 시 HCC블럭, OLTP블럭 두 군데를 읽어야 하므로 성능 저하
  업데이트가 있는 테이블에는 HCC를 적용하지 않을 것을 권고

- 하나의 row를 업데이트 하더라도, 그 row가 걸쳐있는 전체 CU를 lock 걸어야 하는데, 이로 인해 빈번한 lock wait 발생 가능, 동시성 저하

- 이를 해결하기 위해서는 Oracle 12c Advanced Compression 옵션을 구매해야 하며, 적용 시 일반적인 RDBMS의 row level locking이 가능해짐

- 그러나, 이 정보를 관리하기 위한 overhead가 발생하여, 약 10% 가량의 데이터 사이즈가 증가함

- partition table의 경우, 압축된 후의 사이즈가 충분히 크지 않으면 direct path read 플랜 (스마트 스캔을 가능하게 하는) 으로 풀리지 않게 되고,
이로인해 결국 스마트 스캔을 하지 않게 되면서, 셀 노드의 CPU로 압축해제 하지 못하고, DB 서버에 CPU 부하를 주게 될 수 있음.
다시 말해, 월별 건수가 애매하게 작으면 DB서버 CPU 부하 상당량 발생 (이 기준점은 알 수 없음)



4. 환경 고려사항


- HCC 압축을 하기 위해서는 모든 insert문을 수정해야 함. INSERT /*+ APPEND */ INTO


- HCC가 적용된 DB의 백업본을 복구하기 위해서도 Exadata가 필요함 (Exadata Storage server 이외의 HW에서는 HCC 사용에 제약이 걸려있어)

- 오라클 ZFS 스토리지 어플라이언스에 복구가 가능하기는 하나, 스토리지를 백업본 크기의 10배를 준비해야 하며,
   ALTER TABLE NOCOMPRESS로 압축을 다 풀기 전에는 조회 불가

저작자표시 비영리 변경금지
Posted by in0de
,

Toad 에서 세션 조회 시 ORA-29275 : 부분 다중바이트 문자

Oracle 2016. 4. 4. 10:51

v$session 다이내믹 퍼포먼스 뷰를 조회하다보면,

ORA-29275 : 부분 다중바이트 문자 오류가 발생하는 경우가 있는데
이는 sys.v$session 뷰의 action varchar2(32 또는 64 byte) 컬럼이 문제이다.

 

이 action 컬럼은 DBMS_APPLICATION_INFO.SET_ACTION 프로시저로
애플리케이션이 하는 일을 사용자가 이름 붙여주는 것으로서,
어떤 SQL 로직의 실행 단계를 태그하고자 할 때 사용할 수 있는 컬럼이다.

 

최초 프로그램이 접속하면 다음과 같이 모듈명과 실행 작업을 지정해주고

BEGIN
  DBMS_APPLICATION_INFO.set_module(module_name => '1. 주문',
                                   action_name => '1.1 주문 접수');
  -- 수행할 SQL
END;
/

 

일련의 실행 단계마다, action 에 대해서

BEGIN
  DBMS_APPLICATION_INFO.set_action(action_name => '1.2 재고 조회');
 
  -- 수행할 SQL
END;
/

와 같이 이름지어주어, 모니터링 시 확인할 수 있도록 하는 컬럼이다.

 

어쨌든 이 컬럼으로 인해 Toad 등의 애플리케이션에서
세션 조회가 불가능한 경우에는 action 컬럼에 대해

TO_SINGLE_BYTE('ACTION') 과 같이 함수를 씌워서 직접 조회해볼 수는 있다.

 

SELECT /*+  RULE */ s.inst_id AS node
   , s.username
   , CASE
   WHEN SUBSTR( s.action, 1, 4 ) = 'FRM:' THEN s.module || '(Form)'
   WHEN SUBSTR( s.action, 1, 4 ) = 'Onli' THEN s.module || '(Form)'
   WHEN SUBSTR( s.action, 1, 4 ) = 'Conc' THEN s.module || '(Conc)'
   ELSE s.module
  END AS module
   , SUBSTR( CASE WHEN SIGN( LENGTHB( s.program ) - 16 ) = 1 THEN SUBSTR( s.program, 1, 16 ) || '..' ELSE s.program END, 1, 16 ) AS program
   , s.osuser||'@'||s.machine AS who
   --, s.port
   , last_call_et
   , TO_CHAR( logon_time, 'mm-dd HH24:MI:SS' ) AS logon_time
   , s.sql_id
   , 'kill -9 ' || p.spid AS OSKILLCMD
   , 'alter system kill session ' || '''' || s.sid || ',' || s.serial# || ',@' || s.inst_id || '''' || ';  --'||p.spid||'' AS ORACLEKILLCMD
   , s.terminal AS USER_INFO
   , SUBSTR( LPAD( TRIM( TO_CHAR( ROUND( p.pga_alloc_mem / 1024 / 1024 ), '999,999' ) ) || 'M', 5, ' ' ), 1, 6 ) AS pga
   --, TRIM( ( SELECT SUBSTR( sql_text, 1, 120 ) FROM v$sql sq WHERE sq.sql_id = s.sql_id AND s.sql_hash_value = sq.hash_value AND ROWNUM = 1 ) ) AS sql_text
   , CASE
   WHEN s.blocking_session IS NULL THEN ''
   ELSE SUBSTR( s.blocking_session_status, 1, 3 ) || '(' || s.blocking_instance || ')' || ( s.blocking_session - 1 )
  END AS BLOCKING_SID
   , s.seconds_in_wait
   , SUBSTR( s.event, 1, 32 ) AS wait_event
   , TO_SINGLE_BYTE( s.action ) AS action
   , ( SELECT  sql_text
    FROM   v$sql sq
    WHERE   sq.sql_id = s.sql_id AND s.sql_hash_value = sq.hash_value AND ROWNUM = 1 )
   AS sql_text
FROM gv$session s, gv$process p
WHERE s.paddr = p.addr
AND  s.inst_id = p.inst_id
AND  s.status = 'ACTIVE'
AND  s.event NOT IN ( 'queue messages'
        , 'pipe get'
        , 'jobq slave wait'
        , 'Streams AQ: waiting for messages in the queue'
        , 'class slave wait')
AND  s.username IS NOT NULL
AND  s.audsid <> USERENV( 'SESSIONID' )                     -- which is not my session
--order by s.inst_id,status1 desc,username1 desc , substr(s.action,1,3) desc, module1,last_call_et
ORDER BY s.inst_id, s.status DESC, last_call_et DESC

/

저작자표시 비영리 변경금지
Posted by in0de
,

티스토리툴바