惜分飞

在一次的sql优化中,遇到SYS_OP_C2C函数,通过分析是由于一个表的varchar2和另外一个表的nvarchar2列进行关联导致,通过以下简单使用进行重现.对于这个问题,如果需要使用index,需要创建SYS_OP_C2C的函数index,或者把列类型修改一致.

SQL> create user xff identified by oracle;

用户已创建。

SQL> grant dba to xff;

授权成功。

SQL> create table xff.t1(id number,name varchar2(100));

表已创建。

SQL> insert into xff.t1 select  object_id,object_name from dba_objects;

已创建 89932 行。

SQL> commit;

提交完成。

SQL> create table xff.t2(id number,name nvarchar2(100));

表已创建。

SQL> insert into xff.t2 select  object_id,object_name from dba_objects;

已创建 89933 行。

SQL> commit;

提交完成。


SQL> exec dbms_stats.gather_table_stats('XFF','T1',cascade=>true);

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.gather_table_stats('XFF','T2',cascade=>true);

PL/SQL 过程已成功完成。


SQL> set autot on
SQL>  select count(1) from xff.t1, xff.t2 where t1.name=t2.name;

  COUNT(1)
----------
    160752


执行计划
----------------------------------------------------------
Plan hash value: 906334482

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    74 |       |   760   (1)| 00:00:10 |
|   1 |  SORT AGGREGATE     |      |     1 |    74 |       |            |          |
|*  2 |   HASH JOIN         |      |   146K|    10M|  3256K|   760   (1)| 00:00:10 |
|   3 |    TABLE ACCESS FULL| T1   | 89932 |  2195K|       |   137   (1)| 00:00:02 |
|   4 |    TABLE ACCESS FULL| T2   | 89933 |  4303K|       |   205   (1)| 00:00:03 |
------------------------------------------------------------------------------------

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

   2 - access("T2"."NAME"=SYS_OP_C2C("T1"."NAME"))

还有一种情况也可能发生该转换,比如使用dblink的访问远程库,本地库和远程库字符集不一致.参考:SQL Statements Performed Across Database Links run Slowly. Explain Plan Shows Function SYS_OP_C2C has been Applied to Predicates, and Query uses a Full Table Scan. (Doc ID 2010872.1)

ORACEL 21C版本数据库信息

[oracle@www.xifenfei.com ~]$ ss

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Aug 14 02:05:14 2021
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/XFF/system01.dbf
/u01/app/oracle/oradata/XFF/sysaux01.dbf
/u01/app/oracle/oradata/XFF/undotbs01.dbf
/u01/app/oracle/oradata/XFF/pdbseed/system01.dbf
/u01/app/oracle/oradata/XFF/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/XFF/users01.dbf
/u01/app/oracle/oradata/XFF/pdbseed/undotbs01.dbf
/u01/app/oracle/oradata/XFF/XFFpdb1/system01.dbf
/u01/app/oracle/oradata/XFF/XFFpdb1/sysaux01.dbf
/u01/app/oracle/oradata/XFF/XFFpdb1/undotbs01.dbf
/u01/app/oracle/oradata/XFF/XFFpdb1/users01.dbf

11 rows selected.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
BANNER_LEGACY
--------------------------------------------------------------------------------
    CON_ID
----------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
         0

dul 依旧支持 oracle 12c

[oracle@www.xifenfei.com dul]$ ./dul

Data UnLoader: 12.2.0.0.2 - Internal Only - on Sat Aug 14 02:20:28 2021
with 64-bit io functions and the decompression option

Copyright (c) 1994 2021 Bernard van Duijnen All rights reserved.

 Strictly Oracle Internal Use Only


DUL: Warning: ulimit process stack size is only 33554432
Found db_id = 1608306320
Found db_name = ORCL
DUL> show datafiles;
ts# rf# start   blocks offs open  err file name
  0   1     0   170241    0    1    0 /u01/app/oracle/oradata/XFF/system01.dbf
  1   3     0    74241    0    1    0 /u01/app/oracle/oradata/XFF/sysaux01.dbf
DUL> bootstrap;
Probing file = 1, block = 520
. unloading table                BOOTSTRAP$
DUL: Warning: block number is non zero but marked deferred trying to process it anyhow
      60 rows unloaded
Reading BOOTSTRAP.dat 60 entries loaded
Parsing Bootstrap$ contents
Generating dict.ddl for version 12
 OBJ$: segobjno 18, file 1 block 240
 TAB$: segobjno 2, tabno 1, file 1  block 144
 COL$: segobjno 2, tabno 5, file 1  block 144
 USER$: segobjno 10, tabno 1, file 1  block 208
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$   75192 rows unloaded
. unloading table                      TAB$    2389 rows unloaded
. unloading table                      COL$  130794 rows unloaded
. unloading table                     USER$     129 rows unloaded
Reading USER.dat 129 entries loaded
Reading OBJ.dat 75192 entries loaded and sorted 75192 entries
Reading TAB.dat 2389 entries loaded
Reading COL.dat 130794 entries loaded and sorted 130794 entries
Reading BOOTSTRAP.dat 60 entries loaded

DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 12
 OBJ$: segobjno 18, file 1 block 240
 TAB$: segobjno 2, tabno 1, file 1  block 144
 COL$: segobjno 2, tabno 5, file 1  block 144
 USER$: segobjno 10, tabno 1, file 1  block 208
 TABPART$: segobjno 835, file 1 block 5560
 INDPART$: segobjno 840, file 1 block 5600
 TABCOMPART$: segobjno 857, file 1 block 5736
 INDCOMPART$: segobjno 862, file 1 block 5784
 TABSUBPART$: segobjno 847, file 1 block 5656
 INDSUBPART$: segobjno 852, file 1 block 5696
 IND$: segobjno 2, tabno 3, file 1  block 144
 ICOL$: segobjno 2, tabno 4, file 1  block 144
 LOB$: segobjno 2, tabno 6, file 1  block 144
 COLTYPE$: segobjno 2, tabno 7, file 1  block 144
 TYPE$: segobjno 761, tabno 1, file 1  block 5024
 COLLECTION$: segobjno 761, tabno 2, file 1  block 5024
 ATTRIBUTE$: segobjno 761, tabno 3, file 1  block 5024
 LOBFRAG$: segobjno 868, file 1 block 5832
 LOBCOMPPART$: segobjno 871, file 1 block 5856
 UNDO$: segobjno 15, file 1 block 224
 TS$: segobjno 6, tabno 2, file 1  block 176
 PROPS$: segobjno 127, file 1 block 1320
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$
DUL: Warning: Recreating file "OBJ.ctl"
   75192 rows unloaded
. unloading table                      TAB$
DUL: Warning: Recreating file "TAB.ctl"
    2389 rows unloaded
. unloading table                      COL$
DUL: Warning: Recreating file "COL.ctl"
  130794 rows unloaded
. unloading table                     USER$
DUL: Warning: Recreating file "USER.ctl"
     129 rows unloaded
. unloading table                  TABPART$     492 rows unloaded
. unloading table                  INDPART$     365 rows unloaded
. unloading table               TABCOMPART$       1 row  unloaded
. unloading table               INDCOMPART$       0 rows unloaded
. unloading table               TABSUBPART$      32 rows unloaded
. unloading table               INDSUBPART$       0 rows unloaded
. unloading table                      IND$    3095 rows unloaded
. unloading table                     ICOL$    5657 rows unloaded
. unloading table                      LOB$     712 rows unloaded
. unloading table                  COLTYPE$    3251 rows unloaded
. unloading table                     TYPE$    6053 rows unloaded
. unloading table               COLLECTION$    1450 rows unloaded
. unloading table                ATTRIBUTE$   15958 rows unloaded
. unloading table                  LOBFRAG$      19 rows unloaded
. unloading table              LOBCOMPPART$       0 rows unloaded
. unloading table                     UNDO$      21 rows unloaded
. unloading table                       TS$       6 rows unloaded
. unloading table                    PROPS$      45 rows unloaded
Reading USER.dat 129 entries loaded
Reading OBJ.dat 75192 entries loaded and sorted 75192 entries
Reading TAB.dat 2389 entries loaded
Reading COL.dat 130794 entries loaded and sorted 130794 entries
Reading TABPART.dat 492 entries loaded and sorted 492 entries
Reading TABCOMPART.dat 1 entries loaded and sorted 1 entries
Reading TABSUBPART.dat 32 entries loaded and sorted 32 entries
Reading INDPART.dat 365 entries loaded and sorted 365 entries
Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries
Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries
Reading IND.dat 3095 entries loaded
Reading LOB.dat 712 entries loaded
Reading ICOL.dat 5657 entries loaded
Reading COLTYPE.dat 3251 entries loaded
Reading TYPE.dat
DUL: Notice: Increased the size of DC_TYPES from 4096 to 32768 entries
 6053 entries loaded
Reading ATTRIBUTE.dat 15958 entries loaded
Reading COLLECTION.dat
DUL: Notice: Increased the size of DC_COLLECTIONS from 1024 to 8192 entries
 1450 entries loaded
Reading BOOTSTRAP.dat 60 entries loaded
Reading LOBFRAG.dat 19 entries loaded and sorted 19 entries
Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries
Reading UNDO.dat 21 entries loaded
Reading TS.dat 6 entries loaded
Reading PROPS.dat 45 entries loaded
Database character set is AL32UTF8
Database national character set is AL16UTF16
DUL> unload table sys.props$;
. unloading table                    PROPS$      45 rows unloaded
DUL> exit

Life is DUL without it
[oracle@www.xifenfei.com dul]$ grep NLS_RDBMS_VERSION SYS_PROPS.dat 
|NLS_RDBMS_VERSION| |21.0.0.0.0| |RDBMS version for NLS parameters|

重现sql执行计划SYS_OP_C2C隐式转换

Oracle 21c强制使用cdb模式

Oracle dul 依旧支持Oracle 21c

文章分类

最近发表

重现sql执行计划SYS_OP_C2C隐式转换

Oracle 21c强制使用cdb模式

Oracle dul 依旧支持Oracle 21c

标签云

文章分类

最近发表