태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

V$SQL_HINT View

오라클 2009.05.04 13:33
Oracle 11g에 참으로 놀라운 View가 추가되었다. V$SQL_HINT!

UKJA@ukja116> desc v$sql_hint
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      NAME                                     VARCHAR2(64)
    2      SQL_FEATURE                              VARCHAR2(64)
    3      CLASS                                    VARCHAR2(64)
    4      INVERSE                                  VARCHAR2(64)
    5      TARGET_LEVEL                             NUMBER
    6      PROPERTY                                 NUMBER
    7      VERSION                                  VARCHAR2(25)
    8      VERSION_OUTLINE                          VARCHAR2(25)

UKJA@ukja116> exec print_table('select * from v$sql_hint');
NAME                          : APPEND                                         
SQL_FEATURE                   : QKSFM_CBO                                      
CLASS                         : APPEND                                         
INVERSE                       : NOAPPEND                                       
TARGET_LEVEL                  : 1                                              
PROPERTY                      : 0                                              
VERSION                       : 8.1.0                                          
VERSION_OUTLINE               : 99.99.99.98                                    
-----------------                                                              
NAME                          : NOAPPEND                                       
SQL_FEATURE                   : QKSFM_CBO                                      
CLASS                         : APPEND                                         
INVERSE                       : APPEND                                         
TARGET_LEVEL                  : 1                                              
PROPERTY                      : 0                                              
VERSION                       : 8.1.0                                          
VERSION_OUTLINE               : 99.99.99.98           
...                        



이 View를 이용하면 Query에서 사용 가능한 Hint들을 Version 별로 알아낼 수 있다. 각 Column들의 정확한 정의는 문서화되어 있지 않지만, 우선 Name, Inverse(역 Hint), Version 정도의 정보만으로도 대단히 유용하다.

마치 10g에서 V$LOCK_TYPE View가 추가되었을 때의 기쁨과도 같은 것을 느꼈다.

UKJA@ukja102> desc v$lock_type
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      TYPE                                     VARCHAR2(64)
    2      NAME                                     VARCHAR2(64)
    3      ID1_TAG                                  VARCHAR2(64)
    4      ID2_TAG                                  VARCHAR2(64)
    5      IS_USER                                  VARCHAR2(3)
    6      DESCRIPTION                              VARCHAR2(4000)


얼마전 MN Lock Contention에 대한 문의를 받았는데, V$LOCK_TYPE View를 이용하면 이 Lock의 정의를 바로 알 수 있다.

UKJA@ukja102> @lock_type MN
TYPE                          : MN
NAME                          : LogMiner
ID1_TAG                       : session ID
ID2_TAG                       : 0
IS_USER                       : NO
DESCRIPTION                   : Synchronizes updates to the LogMiner dictionary
and prevents multiple instances from preparing the same LogMiner session
-----------------

PL/SQL procedure successfully completed.


Oracle이 버전업되면서 이런 Meta 정보들이 더욱 다양하게 제공되었으면 하는 바램이다.



신고

'오라클' 카테고리의 다른 글

Buffer Pinning, Logical Reads, Elapsed Time  (4) 2009.05.19
Bind 변수 값 알아내기  (1) 2009.05.07
V$SQL_HINT View  (0) 2009.05.04
SQL*Net 대기 이벤트에 대한 오해  (3) 2009.04.17
Snapshot Too Old 에러와 SCN_ASCENDING 힌트  (2) 2009.04.10
Trackback 0 : Comment 0

Write a comment

티스토리 툴바