阅读<收获,不止oralce>第五章 索引章节
发现测试环境12c和书中11g的结果不同.一步步排查发现SYS_HUB dblink
建表
create table t1 as select * from dba_objects; create tbale t2 as select * from dba_objects;
查询
select object_id from t1 union select object_id from t2
执行计划 全表扫描 确认 union有排序 但默认走全表扫描
SYS@EMREP> select object_id from t1 union select object_id from t2; 87014 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3008085330 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 157K| 1998K| | 1430 (1)| 00:00:18 | | 1 | SORT UNIQUE | | 157K| 1998K| 3105K| 1430 (1)| 00:00:18 | | 2 | UNION-ALL | | | | | | | | 3 | TABLE ACCESS FULL| T1 | 59568 | 756K| | 339 (1)| 00:00:05 | | 4 | TABLE ACCESS FULL| T2 | 97822 | 1241K| | 339 (1)| 00:00:05 | ------------------------------------------------------------------------------------ Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 45 recursive calls 0 db block gets 2640 consistent gets 0 physical reads 0 redo size 1590723 bytes sent via SQL*Net to client 64319 bytes received via SQL*Net from client 5802 SQL*Net roundtrips to/from client 7 sorts (memory) 0 sorts (disk) 87014 rows processed
create index idx_t1_object on t1(object_id); create index idx_t2_object on t2(object_id);
创建索引后 查看执行计划-还是走全表,和预期不符
Execution Plan ---------------------------------------------------------- Plan hash value: 3008085330 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 157K| 1998K| | 1430 (1)| 00:00:18 | | 1 | SORT UNIQUE | | 157K| 1998K| 3105K| 1430 (1)| 00:00:18 | | 2 | UNION-ALL | | | | | | | | 3 | TABLE ACCESS FULL| T1 | 59568 | 756K| | 339 (1)| 00:00:05 | | 4 | TABLE ACCESS FULL| T2 | 97822 | 1241K| | 339 (1)| 00:00:05 | ------------------------------------------------------------------------------------
查看对应表结构和查询相关列,确认object_id列存在空值 ,查询对应的空值查询是SYS_HUB 的一个dblink SYS@EMREP> desc t1 Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- OWNER VARCHAR2(128) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(128) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(23) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NUMBER EDITION_NAME VARCHAR2(128) SHARING VARCHAR2(18) EDITIONABLE VARCHAR2(1) ORACLE_MAINTAINED VARCHAR2(1) APPLICATION VARCHAR2(1) DEFAULT_COLLATION VARCHAR2(100) DUPLICATED VARCHAR2(1) SHARDED VARCHAR2(1) CREATED_APPID NUMBER CREATED_VSNID NUMBER MODIFIED_APPID NUMBER MODIFIED_VSNID NUMBER
SYS@EMREP> select * from t1 where object_id is null; OWNER ------------------------------------------------------------------------------------------------------------------------ OBJECT_NAME ------------------------------------------------------------------------------------------------------------------------ SUBOBJECT_NAME ------------------------------------------------------------------------------------------------------------------------ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G ---------- -------------- ----------------------- ------------------ ------------------ ------------------- ------- - - S NAMESPACE - ---------- EDITION_NAME ------------------------------------------------------------------------------------------------------------------------ SHARING E O A ------------------ - - - DEFAULT_COLLATION D S CREATED_APPID ---------------------------------------------------------------------------------------------------- - - ------------- CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID ------------- -------------- -------------- SYS SYS_HUB DATABASE LINK 26-JAN-17 VALID N N N NONE N N
SYS@EMREP> SELECT * FROM DBA_DB_LINKS where db_link='SYS_HUB'; OWNER ------------------------------------------------------------------------------------------------------------------------ DB_LINK ------------------------------------------------------------------------------------------------------------------------ USERNAME ------------------------------------------------------------------------------------------------------------------------ HOST ------------------------------------------------------------------------------------------------------------------------ CREATED HID ------------------ --- SYS SYS_HUB SEEDDATA 26-JAN-17 NO
查询相关资料
So there’s a standard out-of-the-box database link in every 12.2 and 18c (and 19c) database. And in a Multitenant environment it is in every container, CDB$ROOT, PDB$SEED and then in all provisioned PDBs as well. But what is the purpose? Actually this database link comes in from the RAC side. It is used to allow rerouting of DML from LEAF nodes which can’t write to the read only instances.You RAC experts may know better about this than I. In case you need to read more about hub and leaf nodes, please see the documentation. Can you delete this database link? I’m not Oracle Support giving official recommendations. Hence, if you are seeking for an official statement, please ask via an SR. Currently there’s no MOS note available. But there’s an unpublished bug 23568687. The database link does exist in all Oracle 12.2.0.1, Oracle 18c and Oracle 19c databases. But in my understanding, as long as you not using RW/RO instance configuration, you can delete the db link with no side effects.
查询 https://support.oracle.com
My Oracle Support Banner How to Drop and Create SYS_HUB (Database Link) ? (Doc ID 2442938.1) Last updated on APRIL 17, 2023 Applies to: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Information in this document applies to any platform. Goal For customers that does not want to have any database link as possible. And SYS_HUB database link is not used in the database. Solution To view full details, sign in with your My Oracle Support account. Don't have a My Oracle Support account? Click to get started! In this Document Goal Solution To drop SYS_HUB database link: To create SYS_HUB database link: References
Copyright (c) 2023, Oracle. All rights reserved. Oracle Confidential. Click to add to Favorites How to Drop and Create SYS_HUB (Database Link) ? (Doc ID 2442938.1) To BottomTo Bottom In this Document Goal Solution To drop SYS_HUB database link: To create SYS_HUB database link: References Applies to: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Information in this document applies to any platform. Goal For customers that does not want to have any database link as possible. And SYS_HUB database link is not used in the database. Solution To drop SYS_HUB database link: 1) To drop SYS_HUB db link, check the database is not used in Read Only and Read Write configuration.(a feature available from 12.2 RAC) For example: SQL> select instance_mode from v$instance; INSTANCE_MO ----------- REGULAR SQL> show parameter instance_mode NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ instance_mode string READ-WRITE SQL> 2) Before dropping the db link, check whether there is any object that uses this db link. For example: SQL> SELECT o.owner , o.object_name , o.object_type , o.status , o.created , o.last_ddl_time FROM dba_objects o , dba_dependencies d WHERE o.owner = d.owner AND o.object_name = d.name AND o.object_type = d.type AND d.referenced_owner = 'SYS' AND d.referenced_name = 'SYS_HUB' AND d.referenced_type = 'DATABASE LINK' / no rows selected SQL> 3) To drop the db link: SQL> drop database link sys_hub ; Database link dropped. SQL> To create SYS_HUB database link: 1) Check "DBMS_PQ_INTERNAL" package and package body is installed. For example, SQL> select owner, object_name, object_type, status, created, last_ddl_time from dba_objects where object_name = 'DBMS_PQ_INTERNAL' and object_type in ('PACKAGE', 'PACKAGE BODY'); OWNER -------------------------------------------------------------------------------- OBJECT_NAME -------------------------------------------------------------------------------- OBJECT_TYPE STATUS CREATED LAST_DDL_TIME ----------------------- ------- ------------------- ------------------- SYS DBMS_PQ_INTERNAL PACKAGE VALID 2017-01-26 14:19:44 2017-01-26 14:19:44 SYS DBMS_PQ_INTERNAL PACKAGE BODY VALID 2017-01-26 14:19:44 2017-01-26 14:19:44 SQL> 2) Create the database link SQL> execute dbms_pq_internal.create_db_link_for_hub ; PL/SQL procedure successfully completed. SQL>
t1和t2表存在空值,索引失效, 切换回oracle11g,验证执行计划走索引,unition操作 会进行排序.索引走full scan/index faset full scan 取决于cost
SYS@EMREP> select object_id from t1 union select object_id from t2; 87014 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2924813283 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 157K| 1998K| | 861 (1)| 00:00:11 | | 1 | SORT UNIQUE | | 157K| 1998K| 3105K| 861 (1)| 00:00:11 | | 2 | UNION-ALL | | | | | | | | 3 | INDEX FAST FULL SCAN| IDX_T1_OBJECT | 59568 | 756K| | 54 (0)| 00:00:01 | | 4 | INDEX FAST FULL SCAN| IDX_T2_OBJECT | 97822 | 1241K| | 54 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 9 recursive calls 0 db block gets 528 consistent gets 386 physical reads 0 redo size 1590723 bytes sent via SQL*Net to client 64319 bytes received via SQL*Net from client 5802 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 87014 rows processed select /*+index(t1)*/ object_id from t1 union 3 select /*+index(t2)*/object_id from t2; 87014 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2450566394 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 157K| 1998K| | 1143 (1)| 00:00:14 | | 1 | SORT UNIQUE | | 157K| 1998K| 3105K| 1143 (1)| 00:00:14 | | 2 | UNION-ALL | | | | | | | | 3 | INDEX FULL SCAN| IDX_T1_OBJECT | 59568 | 756K| | 196 (1)| 00:00:03 | | 4 | INDEX FULL SCAN| IDX_T2_OBJECT | 97822 | 1241K| | 196 (1)| 00:00:03 | ------------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 7 recursive calls 0 db block gets 516 consistent gets 0 physical reads 0 redo size 1590723 bytes sent via SQL*Net to client 64319 bytes received via SQL*Net from client 5802 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 87014 rows processed
