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
,

네트워크 경로에 있는 chm 도움말 파일 내용이 나오지 않는 문제

l33t 2019. 1. 1. 18:15

네트워크 드라이브 경로에 있는 도움말 파일을 열었을 때, 목차는 나오지만 내용이 안나오는 경우는

KB896358 보안 패치가 설치됨에 따른 현상이며

다음 레지스트리 키를 생성하면 *.chm 파일 내용을 정상적으로 볼 수 있다.


Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\HTMLHelp\1.x]

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\HTMLHelp\1.x\HHRestrictions]
"MaxAllowedZone"=dword:00000001

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\HTMLHelp\1.x\ItssRestrictions]
"MaxAllowedZone"=dword:00000001


하지만 해당 패치는 chm을 통해 완전한 시스템 권한을 탈취할 수 있는 보안취약점을 패치하는 것으로

이렇게 해서 사용하는 것이 답은 아닌 것 같다.

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

파티션 삭제 프로시저

DB2 LUW 2018. 6. 25. 11:42

db2에는 아직 ALTER TABLE tblName DROP PARTITION partName 구문이 없기 때문에

1. 파티션을 detach

2. 파티션테이블로부터 detach 된 single table을 drop

의 단계로 진행해야 한다.


이 때, detach 동작은 background에서 asynchronous하게 동작하므로

실제로 detach가 되어 single table로 떨어져나간 시점에 DROP TABLE을 수행하여야 하는데

partition detach가 금방 수행완료될 지 아닐 지는 알 수가 없다.


이러한 고려사항을 포함하여 간편하게 수행할 수 있는 프로시저의 소스는 아래와 같다.


-- db2 -td@ -vf dropPartition.sql

-- in dropPartition.sql

CREATE OR REPLACE PROCEDURE DBA.DROP_PARTITION (IN tblSchema VARCHAR(40), IN tblName VARCHAR(60), IN partName VARCHAR(120))

SPECIFIC DROP_PARTITION

LANGUAGE SQL

BEGIN

DECLARE tblSchema VARCHAR(40) DEFAULT '___________';

DECLARE detachSQL VARCHAR(2000);

DECLARE dropSQL VARCHAR(2000);

DECLARE isTblAbsent INT;


        DECLARE dpID INT;

        DECLARE dpState CHAR;

        DECLARE isNormalState INT DEFAULT 0;


        DECLARE curDPLock CURSOR FOR

                SELECT DATAPARTITIONID, STATUS

                FROM SYSIBM.SYSDATAPARTITIONS DP

                WHERE DP.TABSCHEMA = tblSchema

                AND DP.TABNAME = tblName

                AND (STATUS = 'L' OR STATUS = 'D' OR STATUS = 'I');


        DECLARE CONTINUE HANDLER FOR NOT FOUND

                SET isNormalState = 1;



CALL DBA.chkTblExist (tblSchema, tblName, isTblAbsent);

        IF isTblAbsent = 1

        THEN

                RETURN -1;

        END IF;


SET detachSQL = 'ALTER TABLE '  || tblSchema || '.' || tblName || ' DETACH PARTITION '|| partName || ' INTO ' || tblSchema || '.' || tblName || '_' || partName || '_DETACHED';

--CALL DBMS_OUTPUT.PUT_LINE(detachSQL);

EXECUTE IMMEDIATE detachSQL;

EXECUTE IMMEDIATE 'COMMIT WORK';


        WAIT_LOOP:

        LOOP

                OPEN curDPLock;

                FETCH curDPLock INTO dpID, dpState;

                CLOSE curDPLock;


                IF isNormalState = 1

                THEN

                        LEAVE WAIT_LOOP;

                ELSE

                        ITERATE WAIT_LOOP;

                END IF;

        END LOOP;


SET dropSQL = 'DROP TABLE ' || tblSchema || '.' || tblName || '_' || partName || '_DETACHED';

-- CALL DBMS_OUTPUT.PUT_LINE(dropSQL);

EXECUTE IMMEDIATE dropSQL;

EXECUTE IMMEDIATE 'COMMIT WORK';


RETURN 0;

END@


--

/* Sample Table DDL

DROP TABLE INODE.TMP;

CREATE TABLE INODE.TMP  (

OPRN_DT VARCHAR(8) NOT NULL ,

RIDE_TROP_ID VARCHAR(9) NOT NULL ,

RIDE_STN_NO VARCHAR(4) NOT NULL ,

ALGH_TROP_ID VARCHAR(9) NOT NULL ,

ALGH_STN_NO VARCHAR(4) NOT NULL ,

MTAR_AREA_CD VARCHAR(2) NOT NULL ,

RIDE_NCNT INTEGER NOT NULL WITH DEFAULT 0 ,

ALGH_NCNT INTEGER NOT NULL WITH DEFAULT 0 ,

RIDE_TTPS_NUM BIGINT NOT NULL WITH DEFAULT 0 ,

ALGH_TTPS_NUM BIGINT NOT NULL WITH DEFAULT 0 ,

RIDE_AMT BIGINT NOT NULL WITH DEFAULT 0 ,

ALGH_AMT BIGINT NOT NULL WITH DEFAULT 0 ,

RIDE_VLT_AMT BIGINT NOT NULL WITH DEFAULT 0 ,

DC_AMT BIGINT WITH DEFAULT 0 ,

GNPS_NUM BIGINT NOT NULL WITH DEFAULT 0 ,

TNPS_NUM BIGINT NOT NULL WITH DEFAULT 0 ,

CHPS_NUM BIGINT NOT NULL WITH DEFAULT 0 ,

SNCN_PSNG_NUM BIGINT NOT NULL WITH DEFAULT 0 ,

DSPR_PSNG_NUM BIGINT NOT NULL WITH DEFAULT 0 ,

NTMP_NUM BIGINT NOT NULL WITH DEFAULT 0 ,

FRGN_SNCN_PSNG_NUM BIGINT WITH DEFAULT 0 ,

FRAP_PSNG_NUM BIGINT WITH DEFAULT 0 ,

DW_LST_UPD_DTM VARCHAR(14) NOT NULL )

COMPRESS YES ADAPTIVE

DISTRIBUTE BY HASH(RIDE_STN_NO)

INDEX IN TR_MT16_I01 PARTITION BY RANGE(OPRN_DT)

(PART TMP_P201600 STARTING(MINVALUE) ENDING('20151299') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201601 STARTING('20160101') ENDING('20160199') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201602 STARTING('20160201') ENDING('20160299') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201603 STARTING('20160301') ENDING('20160399') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201604 STARTING('20160401') ENDING('20160499') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201605 STARTING('20160501') ENDING('20160599') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201606 STARTING('20160601') ENDING('20160699') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201607 STARTING('20160701') ENDING('20160799') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201608 STARTING('20160801') ENDING('20160899') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201609 STARTING('20160901') ENDING('20160999') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201610 STARTING('20161001') ENDING('20161099') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201611 STARTING('20161101') ENDING('20161199') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201612 STARTING('20161201') ENDING('20161299') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201701 STARTING('20170101') ENDING('20170199') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201702 STARTING('20170201') ENDING('20170299') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201703 STARTING('20170301') ENDING('20170399') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201704 STARTING('20170401') ENDING('20170499') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201705 STARTING('20170501') ENDING('20170599') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201706 STARTING('20170601') ENDING('20170699') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201707 STARTING('20170701') ENDING('20170799') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201708 STARTING('20170801') ENDING('20170899') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201709 STARTING('20170901') ENDING('20170999') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201710 STARTING('20171001') ENDING('20171099') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201711 STARTING('20171101') ENDING('20171199') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201712 STARTING('20171201') ENDING('20171299') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201801 STARTING('20180101') ENDING('20180199') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201802 STARTING('20180201') ENDING('20180299') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201803 STARTING('20180301') ENDING('20180399') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201804 STARTING('20180401') ENDING('20180499') IN TR_MT16_D01 INDEX IN TR_MT16_I01,

PART TMP_P201805 STARTING('20180501') ENDING('20180599') IN TR_MT16_D01 INDEX IN TR_MT16_I01);

CREATE UNIQUE INDEX INODE.PK_TMP ON KSCCDW.TMP (OPRN_DT ASC, RIDE_TROP_ID ASC, RIDE_STN_NO ASC, ALGH_TROP_ID ASC, ALGH_STN_NO ASC, MTAR_AREA_CD ASC) PARTITIONED;

ALTER TABLE INODE.TMP ADD CONSTRAINT PK_TMP PRIMARY KEY (OPRN_DT,RIDE_TROP_ID,RIDE_STN_NO,ALGH_TROP_ID,ALGH_STN_NO,MTAR_AREA_CD);

*/


-- Sample detach partition SQL

-- db2 "CALL DBA.DROP_PARTITION('INODE','TMP','TMP_P201601')"


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

티스토리툴바