shared pool _ select문의 실행 계획

#) shared pool 을 비워주는 명령*2번 

alter system flush shared_pool;

 

#) select문 실행

select * from hr.employees where employee_id = 100;

#) HR 계정으로 로그인 해서 select문 실행

conn hr/hr
SELECT * FROM hr.employees WHERE employee_id = 100;

#) SQL문장의 실행 정보를 조회

-> hr.employees 를 포함한 SQL문장 검색 + v$sql 와 관련된 문장은 제외

-> SQL_ID(SQL문장 식별자),  PARSE_CALLS(조회하는 횟수), LOADS(실행계획), EXECUTIONS(실행하는 횟수)

SELECT sql_id, sql_text, parse_calls, loads, executions
FROM v$sql
WHERE sql_text LIKE '%hr.employees%'
AND sql_text NOT LIKE '%v$sql%';

 

#) HR 계정에서 실행

SELECT * FROM hr.employees WHERE employee_id = 100;
SELECT * FROM hr.employees WHERE employee_id = 101;
SELECT * FROM hr.employees WHERE employee_id = 102;
SELECT * FROM hr.employees WHERE employee_id = 103;
SELECT * FROM hr.employees WHERE employee_id = 104;
SELECT * FROM hr.employees WHERE employee_id = 105;
SELECT * FROM hr.employees WHERE employee_id = 106;

#) 실행정보 확인 

SELECT sql_id, sql_text, parse_calls, loads, executions
FROM v$sql
WHERE sql_text LIKE '%hr.employees%'
AND sql_text NOT LIKE '%v$sql%';

#) SQL_ID에 대한 실행계획 확인

=> employee_id = 103인 실행계획 확인해보기

=> INDEX: rowid 보유

select * from table(dbms_xplan.display_cursor('fsg55tcss0s6m'));

#) 다른 SQL_ID의 실행계획 확인

select * from table(dbms_xplan.display_cursor('16r3qt72smbah'));

#) 컬럼 추가, 실행계획 정보 확인
=> plan_hash_value : 실행계획을 유일하게 식별하는 값

SELECT sql_id, sql_text, parse_calls, loads, executions, plan_hash_value
FROM v$sql
WHERE sql_text LIKE '%hr.employees%'
AND sql_text NOT LIKE '%v$sql%';

#) hash buffer의 위치가 다 다르다는 것을 확인 
=> hash_value: SQL문장의 고유한 해시 값 

SELECT sql_id, sql_text, parse_calls, loads, executions, plan_hash_value, hash_value
FROM v$sql
WHERE sql_text LIKE '%hr.employees%'
AND sql_text NOT LIKE '%v$sql%';

#) plan_hash_value 별로 SQL문장이 10번이상 실행되는지 확인

=> SQL 문장의 실행 횟수를 집계하여 실행 계획이 동일한 SQL 문장들을 식별함

select plan_hash_value, count(hash_value)
from v$sql
group by plan_hash_value
having count(hash_value) >= 10
order by 2 desc;

 

#) plan_hash_value 별로 SQL문장이 2번이상 실행되는지 확인

select plan_hash_value, count(hash_value)
from v$sql
group by plan_hash_value
having count(hash_value) >= 2
order by 2 desc;

#) 2번 이상 중복되는 sql_id로 확인

select sql_id, sql_text
from v$sql
where plan_hash_value = 1833546154;

#) 바인드처리 하기

var b_id number
execute :b_id := 100

SELECT * FROM hr.employees WHERE employee_id = :b_id;

#) 확인해보기

SELECT sql_id, sql_text, parse_calls, loads, executions, plan_hash_value, hash_value
FROM v$sql
WHERE sql_text LIKE '%hr.employees%'
AND sql_text NOT LIKE '%v$sql%';

#) 바인드변수 처리한 SQL_ID 실행계획 확인

select * from table(dbms_xplan.display_cursor('8nwhz4d5y0198'));