오라클 11g DB 재기동시 sql_id 는 남아있을까? (v$sql)

 

OS환경 : Oracle Linux 6.8 (64bit)

 

DB 환경 : Oracle Database 11.2.0.4

 

방법 : 오라클 11g DB 재기동시 sql_id 는 남아있을까? (v$sql)

sql 수행 시 만들어진 sql_id는 v$sql 등의 뷰에서 확인할 수있음

DB가 재기동 되었을 경우에는 남아있는지 여부를 확인하는 테스트

 

 

테스트 데이터 생성

 

1 2 3 4

SQL> conn / as sysdba SQL> create table sql_table as select * from dba_objects;   Table created.

 

 

 

 

sql 수행(5회 실행)

SQL> select * from sql_table where rownum <= 1;
 
OWNER                   OBJECT_NAME                                                            SUBOBJECT_NAME
------------------------------ -----------------------------------------------------------------------------------------------------------------
 OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE          CREATED    LAST_DDL_ TIMESTAMP          STATUS  T G S  NAMESPACE EDITION_NAME
---------- -------------- ------------------- --------- --------- ------------------- ------- - - - ---------- ------------------------------
SYS                   ICOL$                                                                (null)
    20        2 TABLE           15-OCT-19 15-OCT-19 2019-10-15:09:16:39 VALID   N N N         1 (null)
 

 

 

해당 sql의 sql_id 확인

SQL> select sql_id, sql_text from v$sql where sql_text like '%sql_table%';
 
SQL_ID          SQL_TEXT
-------------  -----------------------------------------------------------------------
7hwwnkrrgtcus   select * from sql_table where rownum <= 1
gm1zs82v6put9   select sql_id, sql_text from v$sql where sql_text like '%sql_table%'
 

sql_id가 7hwwnkrrgtcus 인것을 확인할 수 있음

 

 

db 재기동

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
SQL>startup
ORACLE instance started.
 
Total System Global Area 2137886720 bytes
Fixed Size            2254952 bytes
Variable Size         1442842520 bytes
Database Buffers      687865856 bytes
Redo Buffers            4923392 bytes
Database mounted.
Database opened.

 

 

해당 sql의 sql_id로 조회

SQL> select sql_id, sql_text from v$sql where sql_id = '7hwwnkrrgtcus';
 
no rows selected

v$sql에서는 찾아볼수없음 (v$sqlarea 도 마찬가지로 no rows)

 

 

DBA_HIST_SQL_PLAN 뷰에서 조회

SQL> select sql_id from DBA_HIST_SQL_PLAN where sql_id = '7hwwnkrrgtcus';
 
no rows selected

찾아볼수없음

 

 

처음 sql 재수행

SQL> select * from sql_table where rownum <= 1;
 
OWNER                   OBJECT_NAME                                                            SUBOBJECT_NAME
------------------------------ -----------------------------------------------------------------------------------------------------------------
 OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE          CREATED    LAST_DDL_ TIMESTAMP          STATUS  T G S  NAMESPACE EDITION_NAME
---------- -------------- ------------------- --------- --------- ------------------- ------- - - - ---------- ------------------------------
SYS                   ICOL$                                                                (null)
    20        2 TABLE           15-OCT-19 15-OCT-19 2019-10-15:09:16:39 VALID   N N N         1 (null)
 

 

 

해당 sql의 sql_id로 조회

SQL> select sql_id, sql_text from v$sql where sql_id = '7hwwnkrrgtcus';
 
SQL_ID          SQL_TEXT
-------------  -----------------------------------------------------------------------
7hwwnkrrgtcus   select * from sql_table where rownum <= 1

재기동시 v$sql에 sql_id로 검색시 바로 나오진 않지만

해당 쿼리를 재수행하면 해당 sql에 대해 동일한 sql_id가 부여되어서 v$sql에서 조회가 가능함

 

 

추가 테스트

v$sql 의 count 확인 후 재기동 후 v$sql 의 count 확인

 

 

재기동 전 v$sql, v$sqlarea 의 count 확인

SQL> select count(*) from v$sql;
 
  COUNT(*)
----------
       573
 
SQL> select count(*) from v$sqlarea;
 
  COUNT(*)
----------
       569

 

 

db 재기동

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
SQL>startup
ORACLE instance started.
 
Total System Global Area 2137886720 bytes
Fixed Size            2254952 bytes
Variable Size         1442842520 bytes
Database Buffers      687865856 bytes
Redo Buffers            4923392 bytes
Database mounted.
Database opened.

 

 

재기동 후 v$sql, v$sqlarea 의 count 확인

SQL> select count(*) from v$sql;
 
  COUNT(*)
----------
       318
 
SQL> select count(*) from v$sqlarea;
 
  COUNT(*)
----------
       313

재기동 이후에도 약 300개 정도의 데이터가 있음

 

 

파싱한 유저 확인

SQL>
select d.username, count(*) from v$sql v, dba_users d
where v.parsing_user_id = d.user_id
group by d.username
 
USERNAME             COUNT(*)
------------------------------ ----------
SYS                      318
 
SQL>
select d.username, count(*) from v$sqlarea v, dba_users d
where v.parsing_user_id = d.user_id
group by d.username
 
USERNAME             COUNT(*)
------------------------------ ----------
SYS                      313

모두 SYS 임

DB가 기동되면서 또는 운영되면서 시스템 내부적으로 실행되는 쿼리들임

 

 

결론

sql_id는 sql 이 들어왔을 때 오라클 내부 해쉬함수에 의해 만들어진 값임

재기동시 v$sql에 sql_id로 검색시 바로 나오진 않지만

해당 쿼리를 재수행하면 해당 해쉬 함수에 의해 동일한 sql_id가 부여되어서 v$sql에서 조회가 가능함

메모리에(v$sql 등) 있는 sql 커서정보는 장시간 재호출이 없거나 데이터베이스가 재기동될 때 사라짐

 

 

sql_id 가 만들어지는 과정(10g 이후)

Oracle은 MD5를 사용하여 라이브러리 캐시 개체 이름을 hashes하여 128 비트 hash 값을 생성함

Oracle은 MD5 hash의 마지막 64 비트를 취하며 이는 SQL_ID가 됨 (하지만 16 진수 또는 일반 숫자가 아닌 간결함을 위해 base-32로 표시됨)

Oracle은 MD5 hash의 마지막 32 비트를 취하며 이것이 hash 값이됨 (v$sql의 hash_value 컬럼에서 확인할 수 있음)

 

 

원문

Actually, since 10g the full story goes like this:

Oracle hashes the library cache object name with MD5, producing a 128 bit hash value

Oracle takes last 64 bits of the MD5 hash and this will be the SQL_ID (but it’s shown in base-32 for brevity rather than in hex or as a regular number)

Oracle takes last 32 bits of the MD5 hash and this will be the hash value (as seen in v$sql.hash_value)

 

 

참조 : http://www.axiominfo.co.kr/default/article/column.php?com_board_basic=read_form&com_board_idx=120&&com_board_search_code=&com_board_search_value1=&com_board_search_value2=&com_board_page=4&&com_board_id=2&&com_board_id=2

https://rcho.tistory.com/entry/SQLID-HASH-VALUE-%EB%8A%94-%EB%AC%B4%EC%8A%A8-%EC%B0%A8%EC%9D%B4%EB%A5%BC-%EA%B0%80%EC%A7%80%EA%B3%A0-%EC%9E%88%EB%82%98

https://tanelpoder.com/2009/02/22/sql_id-is-just-a-fancy-representation-of-hash-value/

https://ora600tom.wordpress.com/tag/vsql/