SYS_OP_C2C 내부 변환으로 인한 Oracle SQL 실행 계획 변경

SYS_OP_C2C 내부 변환으로 인한 Oracle SQL 실행 계획 변경

이 쿼리의 비용이 왜 드는지 궁금합니다.

select * from address a
left join name n on n.adress_id=a.id
where a.street='01';

보다 높음

select * from address a
left join name n on n.adress_id=a.id
where a.street=N'01';

주소 테이블이 다음과 같이 표시되는 위치

ID              NUMBER
STREET          VARCHAR2(255 CHAR)
POSTAL_CODE     VARCHAR2(255 CHAR)

그리고 이름표는 이렇게 생겼습니다.

ID              NUMBER
ADDRESS_ID      NUMBER
NAME            VARCHAR2(255 CHAR)
SURNAME         VARCHAR2(255 CHAR)

설명 계획에 의해 반환된 비용입니다.

'01'에 대한 계획 설명

-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |  3591 |  1595K|    87   (0)| 00:00:02 |
|   1 |  NESTED LOOPS OUTER          |                      |  3591 |  1595K|    87   (0)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL          | ADDRESS              |     3 |   207 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| NAME                 |  1157 |   436K|    47   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | NAME_HSI             |  1157 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("A"."STREET"='01')
   4 - access("N"."ADDRESS_ID"(+)="A"."ID")

N'01'에 대한 계획 설명

-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |   347 |   154K|    50   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER          |                      |   347 |   154K|    50   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL          | ADDRESS              |     1 |    69 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| NAME                 |  1157 |   436K|    47   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | NAME_HSI             |  1157 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(SYS_OP_C2C("A"."STREET")=U'01')
   4 - access("N"."ADDRESS_ID"(+)="A"."ID")

보다시피 N'01'에 대한 비용 쿼리는 '01'에 대한 비용보다 낮습니다.왜 그런지 추측이라도?N'01'은 추가적으로 varchar를 nvarchar로 변환해야 하므로 비용이 더 높아야 합니다(SYS_OP_C2C()).다른 질문은 N'01' 쿼리에 의해 처리된 행이 '01'보다 낮은 이유입니다.

[편집]

• 테address30개의 행이 있습니다.
• 테name1969개의 행이 있습니다.

SYS_OP_C2C입니다.internal function이 것은그 하는 것.implicit conversionvarchar2national character set용사를 TO_NCHAR따라서는 됩니다.따라서 필터는 일반적인 비교를 사용하는 필터에 비해 완전히 변경됩니다.

행 수가 적은 이유는 잘 모르겠지만, 더 많을 수도 있다고 장담할 수 있습니다.비용 견적은 영향을 받지 않습니다.

테스트 사례에서 단계별로 살펴보겠습니다.

SQL> CREATE TABLE t AS SELECT 'a'||LEVEL col FROM dual CONNECT BY LEVEL < 1000;

Table created.

SQL>
SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE col = 'a10';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     5 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

   1 - filter("COL"='a10')

13 rows selected.

SQL>

지금까지는 좋습니다.값이 'a10'인 행이 하나뿐이므로 최적화 도구는 행을 하나로 추정했습니다.

국민 문자 집합 변환으로 보겠습니다.

SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE col = N'a10';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |    50 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |    10 |    50 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

   1 - filter(SYS_OP_C2C("COL")=U'a10')

13 rows selected.

SQL>

이게 어찌된 일이냐?우리는 볼 수 있습니다.filter(SYS_OP_C2C("COL")=U'a10')즉, 내부 기능이 적용되고 그것이 변환된다는 것을 의미합니다.varchar2에 가치를 두는.nvarchar2이제 필터에서 10개의 행을 찾았습니다.

이제 열에 함수가 적용되므로 인덱스 사용도 억제됩니다.다음을 생성하여 조정할 수 있습니다.function-based index해서를 full table scan.

SQL> create index nchar_indx on t(to_nchar(col));

Index created.

SQL>
SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE to_nchar(col) = N'a10';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1400144832

--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |    10 |    50 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T          |    10 |    50 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | NCHAR_INDX |     4 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access(SYS_OP_C2C("COL")=U'a10')

14 rows selected.

SQL>

하지만, 이것이 실행 계획을 비슷하게 만들까요?아니요. 두 개의 다른 문자 세트를 사용하면 필터가 똑같이 적용되지 않을 것이라고 생각합니다.따라서 차이는 있습니다.

내 조사에 따르면,

일반적으로 이러한 시나리오는 애플리케이션을 통해 전송되는 데이터가 nvarchar2 유형이지만 테이블 열이 varchar2인 경우에 발생합니다.따라서 Oracle은 필터 작업에 내부 함수를 적용합니다.데이터를 잘 알고 설계 단계에서 유사한 데이터 유형을 사용하는 것이 좋습니다.

계획을 설명하는 것에 대해 걱정할 때, 표에 현재 통계가 있는지 여부가 중요합니다.통계량이 실제 데이터를 상당히 잘 나타내지 못하면 최적화 도구가 실수를 하고 기수를 잘못 추정합니다.

데이터 사전을 쿼리하여 통계가 수집된 기간을 확인할 수 있습니다.

select table_name, last_analyzed
  from user_tables
 where table_name in ('ADDRESS','NAME');

최적화 도구에서 사용할 통계를 수집하려면 다음을 호출합니다.DBMS_STATS:

begin
   dbms_stats.gather_table_stats(user, 'ADDRESS');
   dbms_stats.gather_table_stats(user, 'NAME');
end;

따라서 통계를 수집한 후에는 다른 설명 계획을 얻을 수 있습니다.아닐 수도 있습니다.

설명 계획의 차이는 주로 최적화 도구가 주소 테이블에서 두 경우에 다르게 찾을 행 수를 추정하기 때문입니다.

첫 번째 경우 동일한 데이터 유형을 가진 동등한 술어가 있습니다. 이는 양호하며 최적화 도구는 종종 이러한 경우에 대해 카디널리티(행 카운트)를 상당히 잘 추정할 수 있습니다.

두 번째 경우 열에 함수가 적용됩니다. 함수 기반 인덱스가 없는 경우 이는 종종 좋지 않으며 최적화 도구가 엉뚱한 추측을 하도록 강제합니다.최적화 도구의 개발자들이 이를 개선하기 위해 노력함에 따라 Oracle 버전에 따라 이 와일드퀘스트는 달라질 것입니다.일부 버전은 "표에 있는 행 수의 5%로 추측합니다."와 같은 것일 수 있습니다.

서로 다른 데이터 유형을 비교할 때 특히 이 경우와 같이 암묵적 변환이 리터럴이 아닌 열에 함수를 만드는 경우에는 암묵적 변환을 피하는 것이 가장 좋습니다.데이터 유형 NVARCHAR2로 값을 얻고 위와 같은 술어로 사용해야 하는 경우에는 값을 열의 데이터 유형으로 명시적으로 변환하는 것이 좋습니다.

select * from address a
left join name n on n.adress_id=a.id
where a.street = CAST( N'01' AS VARCHAR2(255));

물론 문자 그대로의 경우에는 말이 되지 않습니다.여기서는 첫 번째 쿼리를 사용합니다.하지만 변수나 함수 매개변수라면 이런 작업을 위한 사용 사례가 있을 수 있습니다.

첫 번째 쿼리는 3591 행을 반환하고 두 번째 쿼리는 347 행을 반환합니다.따라서 Oracle은 I/O 작업을 덜 필요로 하기 때문에 비용이 적게 듭니다.

와 혼동하지 마십시오.

N'01'은(는) varchar를 nvarchar로 추가 변환해야 합니다.

Oracle은 하나의 하드 구문 분석을 수행한 다음 동일한 쿼리에 대해 소프트 구문 분석을 사용합니다.따라서 오라클의 작동 시간이 길어질수록 오라클의 작동 속도는 빨라집니다.

언급URL : https://stackoverflow.com/questions/26296220/oracle-sql-execution-plan-changes-due-to-sys-op-c2c-internal-conversion