LITERAL SQL 찾기 - 심화

Literal SQL을 검색한다는 것은 논리상 동일한 SQL임에도 불구하고 지속적으로 Hard Parsing 을 발생키시는 SQL을 찾는것이다. 보통 Literal Sql을 찾을때는 아래 쿼리를 사용한다.

 

select *
from (
  select parsing_schema_name, sql_id, sql_text, executions
       , sum(executions) over (partition by force_matching_signature ) executions_sum
       , row_number() over (partition by force_matching_signature order by sql_id desc) rnum
       , count(*) over (partition by force_matching_signature ) cnt
       , force_matching_signature
  from   gv$sqlarea s
  where  force_matching_signature != 0
)
where  cnt > 5
--and    rnum = 1
order by cnt desc, sql_text

​

​

[1] Force_matching_signature 활용한 Literal Sql 찾기 - SQL에 상수값만 존재

[ SQL 질의 ]
SELECT /* aa.bb.retreive.AAAAA1 */ * FROM EMP WHERE EMPNO = '7931' AND JOB = 'CLERK';
SELECT /* aa.bb.retreive.AAAAA1 */ * FROM EMP WHERE EMPNO = '7892' AND JOB = 'CLERK';
SELECT /* aa.bb.retreive.AAAAA1 */ * FROM EMP WHERE EMPNO = '7893' AND JOB = 'CLERK';

select *
from (
  select parsing_schema_name, sql_id, sql_text, executions       
       , sum(executions) over (partition by force_matching_signature ) executions_sum
       , row_number() over (partition by force_matching_signature order by sql_id desc) rnum
       , count(*) over (partition by force_matching_signature ) cnt
       , EXACT_MATCHING_SIGNATURE
       , force_matching_signature
  from   gv$sqlarea s
  where  force_matching_signature != 0
   and PARSING_SCHEMA_NAME = 'KTH' 
   and sql_text like '%aa.bb.retreive.AAAAA1%'
   AND SQL_TEXT NOT LIKE '%v$%'
)
where  cnt > 2
order by cnt desc, sql_text
;

 

Literal SQL을 발생시키는 SQL이 조회되었다. 바인드 변수 처리하면 커서 1개 생성되어 공유가 되어지므로 성능상 유익하다.

SQL에 상수 값만으로 사용되어지면 FORCE_MATCHING_SIGNATURE 값이 동일하게 조회됨

​

​​

​

​

[2] Force_matching_signature 활용한 Literal Sql 찾기 - SQL에 상수값과 바인드 변수 혼재

※ 변화가 많은 값을 Literal 값이면서, 바인드 변수가 혼재 되어 있는 경우 
SELECT /* aa.bb.retreive.AAAAA2 */ * FROM EMP WHERE EMPNO = '7864' AND JOB = :1 ;
SELECT /* aa.bb.retreive.AAAAA2 */ * FROM EMP WHERE EMPNO = '7865' AND JOB = :1 ;
SELECT /* aa.bb.retreive.AAAAA2 */ * FROM EMP WHERE EMPNO = '7866' AND JOB = :1 ;

select *
from (
  select parsing_schema_name, sql_id, sql_text, executions
       , sum(executions) over (partition by force_matching_signature ) executions_sum
       , row_number() over (partition by force_matching_signature order by sql_id desc) rnum
       , count(*) over (partition by force_matching_signature ) cnt
       , force_matching_signature
  from   gv$sqlarea s
  where  force_matching_signature != 0
    and PARSING_SCHEMA_NAME = 'KTH' 
   and sql_text like '%aa.bb.retreive.AAAAA2%'
   AND SQL_TEXT NOT LIKE '%v$%'
)
where  cnt > 2
order by cnt desc, sql_text
;

 

쿼리 결과 건수는 0 이다. 인라인 뷰 안의 SQL만 별도로 수행한 결과 FORCE_MATCHING_SIGNATURE값이 모두 다르기 때문이다. 왜 조회 결과 나오지 않았을까? force_matching_signature는 Literal 값을 바인드 변수화 한 이후에 만든 signature 값인데 왜그럴까?

아래 영문 인용문과 같이 SQL에 "바인드 값"과 "Literal(상수) 값"이 혼재되어 있으면 Literal 값을 바인드 변수화 하지 않기 때문이다. 상세히 들어가면 force_matching_signature 값은 SQL에 Literal 값을 Bind 변수로 변환하는 작업(bind transformation)을 한 후에 도출되는 hash 값 인데, SQL에 "바인드"와 "상수" 값이 혼재 되어있으면 no bind transformation 한 후에 hash 값이 계산한다는 의미이다. 그러므로 위 sql로 Literal Sql을 찾는다면 [2] 번과 같은 예제는 찾을 수 없게 된다. bind transformation 작업이 없으므로 모두 다른 SQL로 인식하여 SIGNATURE값이 계산되기 때문이다.

 

SQL에 상수 값과 바인드 값이 혼재되어 사용되면, FORCE_MATCHING_SIGNATURE 값이 서로 다르게 조회됨

 

​If TRUE this causes SQL Profiles to target all SQL statements which have the sametext after normalizing all literal values into bind variables.
(Note that if a combination of literal values and bind values is used in a SQL statement, no bind transformation occurs.)
This is analogous to the matching algorithm used by the "FORCE" option of theCURSOR_SHARING parameter. If FALSE, literals are not transformed.
This is analogous to thematching algorithm used by the "EXACT" option of the CURSOR_SHARING parameter.
- dbms_sqltun 패키지 내부 주석

 

※ 참고

: 바인드 변수와 Literal 값은 혼재 되어 있더라도 force_matching_signature가 동일한 경우의 수

   바인드 변수와 "변화가 없는" Literal 값인 경우

SELECT /*aa.bb.retreive.AAAAA4*/*FROM EMP WHERE EMPNO=:1 AND JOB='CLREK';
SELECT /*aa.bb.retreive.AAAAA4*/*FROM EMP WHERE EMPNO=:1 AND JOB='CLREK';
SELECT /*aa.bb.retreive.AAAAA4*/*FROM EMP WHERE EMPNO=:1 AND JOB='CLREK';

:1 바인드 변수와 'CLERK' Literal 값이 혼재 되어 있다. 
'CLERK' Literal 값을 bind transformation 하지 않고 force_matching_signature 값을 구한다. 
그런데 이때는 no bind transformation 하더라도 동일한 TEXT를 유지하므로 위 3개 SQL문은 
동일한 force_matching_signature, SQLID를 가지게 된다.

 

  

[ 3 ] 대안

[ 대안 1 ]
  - SQL식별자가 SQL 상단에 있다면,  SQL 식별자 경우의 수보다 많아 보이는 경우 Literal SQL인지 확인하기

SELECT REGEXP_SUBSTR( SQL_TEXT, '/\*.*?\*/') AS "SQL식별자"
    , COUNT(*) "SQL수"
FROM v$sqlarea 
where 1 = 1
  AND SQL_TEXT NOT LIKE '%v$%' 
  and sql_text like '%aa.bb.retreive.AAAAA2%'
  AND PARSING_SCHEMA_NAME = 'KTH' 
  AND REGEXP_SUBSTR( SQL_TEXT, '/\*.*?\*/') IS NOT NULL 
  -- and sql_id not in ( force_matching_signature로 찾은 hardParsing sqlid )
GROUP BY REGEXP_SUBSTR( SQL_TEXT, '/\*.*?\*/')
HAVING COUNT(*) > 2   
;


[ 대안 2 ] 
  - 전통적인 Literal SQL 찾기 - SQL TEXT 적당한 앞 자리수 지정후 group BY 하기 

SELECT substr( SQL_TEXT, 1, 100) AS "SQL식별자"
    , COUNT(*) "SQL수"
FROM v$sqlarea 
where 1 = 1
  AND SQL_TEXT NOT LIKE '%v$%' 
  and sql_text like '%aa.bb.retreive.AAAAA2%'
  AND PARSING_SCHEMA_NAME = 'KTH' 
  AND REGEXP_SUBSTR( SQL_TEXT, '/\*.*?\*/') IS NOT NULL 
  -- and sql_id not in ( force_matching_signature로 찾은 hardParsing sqlid )
GROUP BY substr( SQL_TEXT, 1, 100)
HAVING COUNT(*) > 2   
;


[ 대안 3 ] 
 - '문자열' 쓰여진 상수 값을 '1' 처리 후 sqltext를 group by하여 동일 SQL 인데 커서가 많은 것 추출

SELECT /* aa.bb.retreive.AAAAA3 */ * FROM EMP WHERE EMPNO = '7864' AND JOB = :1 AND SAL = '5000';
SELECT /* aa.bb.retreive.AAAAA3 */ * FROM EMP WHERE EMPNO = '7865' AND JOB = :1 AND SAL = '5000';
SELECT /* aa.bb.retreive.AAAAA3 */ * FROM EMP WHERE EMPNO = '7866' AND JOB = :1 AND SAL = '5000';

SELECT SQL, COUNT(*) 
 FROM
   ( 
    select regexp_replace( sql_text,'''[^'']*''',':1') SQL
        , force_matching_signature
    FROM v$sqlarea 
    where 1 = 1
      AND SQL_TEXT NOT LIKE '%v$%' 
      and sql_text like '%aa.bb.retreive.AAAAA3%'
  -- and sql_id not in ( force_matching_signature로 찾은 hardParsing sqlid )
    )
GROUP BY SQL
HAVING COUNT(*) > 2 
;

​

 

[ 결론 ]

Literal Sql을 찾는 방법은 아래 2가지로 분류로 판단되며, 상황에 따라 유연하게 사용하면 되겠다. 예를 들면 1번으로 빠르게 FORCE_MATCHING_SIGNATURE를 이용하여 Literal SQL 찾고 바인드 변수화 가이드하기.  2번 검색시 1번에서 도출된 SQL은 제외 한 후 Literal Sql을 찾아 2차 가이드하기.

​

1. FORCE_MATCHING_SIGNATURE 이용

  1) 순수 Literal SQL

  2) 바인드 변수와 변화가 없는 Literal SQL

​

2. SQL_TEXT 이용

  1) SQL 식별자 Group by

  2) SQLTEXT SUBSTR 후 Group by

  3) SQLTEXT 상수값 제거 후 Group by

 

원글 : https://cafe.naver.com/oraclebank/1104