SYS_HUB小记
阅读<收获,不止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.
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
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/23182478/viewspace-2966768/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
