[Oracle] v$sqlarea vs v$sql cusor sharing 테스트

 

부모 커서 : SQL문 전체 문장과 같은 정적인 메타데이터를 포함

자식 커서 : 모든 다이나믹 변수를 가짐 

 

v$sql 은 라이브러리 캐시에 캐싱돼 있는 각 child 커서에 대한 수행 통계를 보여 준다. v$sqlarea 는 Parent 커서에 대한 수행통계를 나타내며, 많은 컬럼이 v$sql 을 group by 해서 구한 집계 값이나, v$sql 의 last value 정보를 나타낸다

 

 

Test 용 User 및 테이블 생성

-- 테스트 유저 및 테이블 생성

create user test1 identified by "test1"; 
grant resource, connect to test1; 
GRANT UNLIMITED TABLESPACE TO test1; 

create user test2 identified by "test2"; 
grant resource, connect to test2; 

grant select on test1.tt to test2 ;

create table test1.tt ( col1 varchar2(10), col2 varchar2(10)); 

insert into test1.tt values ( 'a', 'b'); 
commit;

create table test2.tt as select * from test1.tt;

 

case 1 : 한 object를 서로 다른 user가 조회 하는 경우 

 

-- object 주인이 먼저 조회 
-- 조회 USER : TEST1 
select col1, col2 from test1.tt;


-- 부모 커서 조회
SELECT SQL_TEXT, SQL_ID, HASH_VALUE, ADDRESS, VERSION_COUNT, LOADED_VERSIONS, EXECUTIONS, PARSING_SCHEMA_NAME
FROM V$SQLAREA WHERE SQL_TEXT LIKE 'select col1, col2 from test1.tt';


--SQL_TEXT	                        SQL_ID	        HASH_VALUE	ADDRESS	            VERSION_COUNT	LOADED_VERSIONS	EXECUTIONS	PARSING_SCHEMA_NAME
--select col1, col2 from test1.tt	7gzc3zj43rc5d	1211871405	07000100227F2478	1	            1	            1	        TEST1


-- 자식 커서 조회 
SELECT SQL_TEXT, SQL_ID, HASH_VALUE, ADDRESS, CHILD_NUMBER, LOADED_VERSIONS, OPEN_VERSIONS, EXECUTIONS, PARSING_SCHEMA_NAME
FROM V$SQL WHERE SQL_TEXT LIKE 'select col1, col2 from test1.tt';


--SQL_TEXT	                        SQL_ID	        HASH_VALUE	ADDRESS	            CHILD_NUMBER	LOADED_VERSIONS	OPEN_VERSIONS	EXECUTIONS	PARSING_SCHEMA_NAME
--select col1, col2 from test1.tt	7gzc3zj43rc5d	1211871405	07000100227F2478	0	            1	            1	            1	        TEST1


-- object 주인이 아닌 다른 스키마가 그 이후 조회
-- 조회 USER : TEST2
select col1, col2 from test1.tt;


-- 부모 커서 조회
SELECT SQL_TEXT, SQL_ID, HASH_VALUE, ADDRESS, VERSION_COUNT, LOADED_VERSIONS, EXECUTIONS, PARSING_SCHEMA_NAME
FROM V$SQLAREA WHERE SQL_TEXT LIKE 'select col1, col2 from test1.tt';

--SQL_TEXT	                        SQL_ID	        HASH_VALUE	ADDRESS	            VERSION_COUNT	LOADED_VERSIONS	EXECUTIONS	PARSING_SCHEMA_NAME
--select col1, col2 from test1.tt	7gzc3zj43rc5d	1211871405	07000100227F2478	1	            1	            2	        TEST1

--> execution만 늘어남

-- 자식 커서 조회 
SELECT SQL_TEXT, SQL_ID, HASH_VALUE, ADDRESS, CHILD_NUMBER, LOADED_VERSIONS, OPEN_VERSIONS, EXECUTIONS, PARSING_SCHEMA_NAME
FROM V$SQL WHERE SQL_TEXT LIKE 'select col1, col2 from test1.tt';


--SQL_TEXT	                        SQL_ID	        HASH_VALUE	ADDRESS	            CHILD_NUMBER	LOADED_VERSIONS	OPEN_VERSIONS	EXECUTIONS	PARSING_SCHEMA_NAME
--select col1, col2 from test1.tt	7gzc3zj43rc5d	1211871405	07000100227F2478	0	            1	            1	            2	        TEST1

--> execution만 늘어남 하나의 커서를 공유!

 

 

CASE2 : OBJECT 소유자가 다르고 같은 이름의 테이블을 똑같은 문장으로 조회 하는 경우

 

-- 실행 user : test1
select col1, col2 from tt;


-- 부모 커서 조회
SELECT SQL_TEXT, SQL_ID, HASH_VALUE, ADDRESS, VERSION_COUNT, LOADED_VERSIONS, EXECUTIONS, PARSING_SCHEMA_NAME
FROM V$SQLAREA WHERE SQL_TEXT LIKE 'select col1, col2 from tt';

-- SQL_TEXT	SQL_ID	HASH_VALUE	ADDRESS	VERSION_COUNT	LOADED_VERSIONS	EXECUTIONS	PARSING_SCHEMA_NAME
-- select col1, col2 from tt	182t36jrhqt3p	1863017589	0700010022154D90	1	1	1	TEST1


-- 자식 커서 조회 
SELECT SQL_TEXT, SQL_ID, HASH_VALUE, ADDRESS, CHILD_NUMBER, LOADED_VERSIONS, OPEN_VERSIONS, EXECUTIONS, PARSING_SCHEMA_NAME
FROM V$SQL WHERE SQL_TEXT LIKE 'select col1, col2 from tt';

-- SQL_TEXT	SQL_ID	HASH_VALUE	ADDRESS	CHILD_NUMBER	LOADED_VERSIONS	OPEN_VERSIONS	EXECUTIONS	PARSING_SCHEMA_NAME
-- select col1, col2 from tt	182t36jrhqt3p	1863017589	0700010022154D90	0	1	1	1	TEST1


-- 실행 user : test2
select col1, col2 from tt;

-- 부모 커서 조회
SELECT SQL_TEXT, SQL_ID, HASH_VALUE, ADDRESS, VERSION_COUNT, LOADED_VERSIONS, EXECUTIONS, PARSING_SCHEMA_NAME
FROM V$SQLAREA WHERE SQL_TEXT LIKE 'select col1, col2 from tt';


-- SQL_TEXT	SQL_ID	HASH_VALUE	ADDRESS	VERSION_COUNT	LOADED_VERSIONS	EXECUTIONS	PARSING_SCHEMA_NAME
-- select col1, col2 from tt	182t36jrhqt3p	1863017589	0700010022154D90	2	2	2	TEST2

-- 자식 커서 조회 
SELECT SQL_TEXT, SQL_ID, HASH_VALUE, ADDRESS, CHILD_NUMBER, LOADED_VERSIONS, OPEN_VERSIONS, EXECUTIONS, PARSING_SCHEMA_NAME
FROM V$SQL WHERE SQL_TEXT LIKE 'select col1, col2 from tt';

-- SQL_TEXT	SQL_ID	HASH_VALUE	ADDRESS	CHILD_NUMBER	LOADED_VERSIONS	OPEN_VERSIONS	EXECUTIONS	PARSING_SCHEMA_NAME
-- select col1, col2 from tt	182t36jrhqt3p	1863017589	0700010022154D90	0	1	1	1	TEST1
-- select col1, col2 from tt	182t36jrhqt3p	1863017589	0700010022154D90	1	1	1	1	TEST2

--> 완전히 같은 sql 문장임에도 object 소유자가 다르기 때문에 자식 커서를 만들게 된다. 


-- 커서 공유 정보 조회  --> 한 개의 부모커서를 공유 하지 못하는 이유 조회 
select sql_id, address, child_address, child_number, auth_check_mismatch, translation_mismatch
from v$sql_shared_cursor where sql_id = '182t36jrhqt3p'; 

-- SQL_ID	ADDRESS	CHILD_ADDRESS	CHILD_NUMBER	AUTH_CHECK_MISMATCH	TRANSLATION_MISMATCH
-- 182t36jrhqt3p	0700010022154D90	0700010023CD4BB0	0	N	N
-- 182t36jrhqt3p	0700010022154D90	0700010023AD1458	1	Y	Y

 

 

- TRANSLATION_MISMATCH : The base objects of the existing child cursor do not match / 스키마가 다른 경우

- AUTH_CHECK_MISMATCH : Authorization/translation check failed for the existing child cursor / 유저가 다른경우.. --> 둘 다 유사 

   

 

추가 Test  : 커서 Flush 

begin
     DBMS_SHARED_POOL.PURGE( '07000100227F2478,1211871405','C');
end;
/

-- 부모 커서 조회
SELECT SQL_TEXT, SQL_ID, HASH_VALUE, ADDRESS, VERSION_COUNT, LOADED_VERSIONS, EXECUTIONS, PARSING_SCHEMA_NAME
FROM V$SQLAREA WHERE SQL_TEXT LIKE 'select col1, col2 from test1.tt';

--> row 없음 


-- 자식 커서 조회 
SELECT SQL_TEXT, SQL_ID, HASH_VALUE, ADDRESS, CHILD_NUMBER, LOADED_VERSIONS, OPEN_VERSIONS, EXECUTIONS, PARSING_SCHEMA_NAME
FROM V$SQL WHERE SQL_TEXT LIKE 'select col1, col2 from test1.tt';

--> row 없음 

-- 커서 공유 정보 조회
select sql_id, address, child_address, child_number, auth_check_mismatch, translation_mismatch
from v$sql_shared_cursor where sql_id = '7gzc3zj43rc5d'; 

--SQL_ID	ADDRESS	CHILD_ADDRESS	CHILD_NUMBER	AUTH_CHECK_MISMATCH	TRANSLATION_MISMATCH
--7gzc3zj43rc5d	07000100227F2478	07000100221A8C48	0	N	N

--> 정보가 남아 있음...

 

 

결론 : object 소유주가 다른 경우 완전히 똑같은 sql 문이라고 해도 하나의 커서를 공유하지 못하고 자식 커서가 생긴다.

          즉, 동일한 SQL문이지만 다른 메타데이터를 가졌다면 하나의 부모에 여러 개의 자식이 생긴다.

          cursor 정보를 purge 시키더라도 v$sql_shared_cursor 에는 정보가 남아있다.