【性能优化】 之 HINTS 相关作业
来源：互联网 发布：河南淘宝加盟 编辑：程序博客网 时间：2024/06/06 03:33
做完 优化课程 中HINTS 相关作业后， 感觉这几个作业还是蛮有特点，也能说明一些问题，
就在提交作业的同时一并发布了
**************************************************************************************************
1.写一条SQL，使它通过全表扫描方式的效率优于索引访问，分别给出各自的执行计划。
2.自己构造三条关联查询的SQL，分别适用于nested loop，hash join,merge join 关联,对于每条sql语句，分别通过hint产生其它两种关联方式的执行计划，并比较性能差异。
3.通过append hint来插入数据，演示它和普通插入数据的性能比较。
4.验证Oracle在没有使用hint DRIVING_SITE时，是否会将远程的数据拉到本地执行。
5.用cardinality hint来模拟表中的数据，写一条SQL语句并给出它的执行计划。
==============================================================================
1.写一条SQL，使它通过全表扫描方式的效率优于索引访问，分别给出各自的执行计划。
答：
1.1 建立测试数据
create table t as select * from dba_objects;
select status,count(0) as qty from T group by status
STATUS QTY
-----------------
1 INVALID 450
2 VALID 2279880
1.2 在一个字段中建立索引
CREATE INDEX IDX_T_STATUS ON T(STATUS)
1.3 对比使用索引及全表搜索的执行计划：
从下面的两种方法对比中可以看到，
在使用了索引
1.3.1 使用索引 idx_t_status 时，COST:19054,而进行全表搜索时，COST:8880。
两种方法中其实ROWS,BYTES都是一样的。因为从上面的统计中也可以看到 status ='VALID' 的数据其实是
差不多占到了全表记录了。使用索引后，反而增加了搜索索引的IO成本。
SQL> explain plan for select /*+ index(t idx_t_status)*/ * from T WHERE status ='VALID';
Explained
SQL> select * from table(dbms_xplan.display(null,null,'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 709710412
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1140K| 105M| 19054 (1)
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1140K| 105M| 19054 (1)
|* 2 | INDEX RANGE SCAN | IDX_T_STATUS | 1140K| | 2716 (1)
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STATUS"='VALID')
14 rows selected
1.3.1 没有指定使用索引，系统自动进行了全表搜索
SQL> explain plan for select * from T WHERE status ='VALID';
Explained
SQL> select * from table(dbms_xplan.display(null,null,'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1140K| 105M| 8880 (1)| 00:01:47 |
|* 1 | TABLE ACCESS FULL| T | 1140K| 105M| 8880 (1)| 00:01:47 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"='VALID')
13 rows selected
SQL>
------------------------------------------------------------------------------------------
2.自己构造三条关联查询的SQL，分别适用于nested loop，hash join,merge join 关联,对于每条sql语句，
分别通过hint产生其它两种关联方式的执行计划，并比较性能差异。
答：
2.1 准备测试数据
建立一个小表与一个大表，在大表关联字段中，建立索引。
OBJECT_ID 字段为唯一键值，所以比较适合使用NESTED JOIN,
drop table t;
drop table t1;
create table t as select * from dba_objects;
create table t1 as select * from t where object_id<100;
create index idx_t_id on t(object_id)
2.2 使用默认的执行计划，可以看到是执行计划使用的是嵌套关联（NESTED JOIN)
SQL> explain plan for select t.* from t ,t1 where t1.object_id=t.object_id;
Explained
SQL> select * from table(dbms_xplan.display(null,null,'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3409221634
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98 | 21560 | 199 (0)| 0
| 1 | NESTED LOOPS | | | | |
| 2 | NESTED LOOPS | | 98 | 21560 | 199 (0)| 0
| 3 | TABLE ACCESS FULL | T1 | 98 | 1274 | 3 (0)| 0
|* 4 | INDEX RANGE SCAN | IDX_T_ID | 1 | | 1 (0)| 0
| 5 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 2 (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."OBJECT_ID"="T"."OBJECT_ID")
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- dynamic sampling used for this statement (level=2)
21 rows selected
2.3指定使用 HASH 关联
SQL> explain plan for select /*+ use_hash(t,t1)*/ t.* from t ,t1 where t.object_id=t1.object_id;
Explained
SQL> select * from table(dbms_xplan.display(null,null,'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1444793974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98 | 21560 | 308 (1)| 00:00:04 |
|* 1 | HASH JOIN | | 98 | 21560 | 308 (1)| 00:00:04 |
| 2 | TABLE ACCESS FULL| T1 | 98 | 1274 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T | 78211 | 15M| 304 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
19 rows selected
2.4 指定使用MERGE
SQL> explain plan for select /*+ use_meger(t,t1)*/ t.* from t ,t1 where t.object_id=t1.object_id;
Explained
SQL> select * from table(dbms_xplan.display(null,null,'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2941280097
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98 | 21560 | 1272 (1)| 0
| 1 | MERGE JOIN | | 98 | 21560 | 1272 (1)| 0
| 2 | TABLE ACCESS BY INDEX ROWID| T | 78211 | 15M| 1268 (1)| 0
| 3 | INDEX FULL SCAN | IDX_T_ID | 78211 | | 183 (1)| 0
|* 4 | SORT JOIN | | 98 | 1274 | 4 (25)| 0
| 5 | TABLE ACCESS FULL | T1 | 98 | 1274 | 3 (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")
filter("T"."OBJECT_ID"="T1"."OBJECT_ID")
Note
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- dynamic sampling used for this statement (level=2)
22 rows selected
从上面3个执行计划可以看到。NESTED JOIN 为最优的。成本分别为：
MERGE JOIN Cost=1272
HASH JOIN Cost=308
NESTED JOIN Cost=199
************************************************************************************
2.5 再进行另一个测试，在小表中，插入更多重复数据，并在小表的关联字段中建立索引，看执行计划的变化
INSERT INTO T1
select * from t1
UNION ALL
select * from t1
UNION ALL
select * from t1
UNION ALL
select * from t1
;
COMMIT;
2.6 使用默认的执行计划，可以看到，这时变化成了hash join.
SQL> explain plan for select t.* from t ,t1 where t1.object_id=t.object_id ;
Explained
SQL> select * from table(dbms_xplan.display(null,null,'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1444793974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 490 | 105K| 311 (1)| 00:00:04 |
|* 1 | HASH JOIN | | 490 | 105K| 311 (1)| 00:00:04 |
| 2 | TABLE ACCESS FULL| T1 | 490 | 6370 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T | 78211 | 15M| 304 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
19 rows selected
2.7 使用NESTED JOIN （ use_nl）
SQL> explain plan for select /*+ use_nl(t,t1)*/ t.* from t ,t1 where t1.object_id=t.object_id ;
Explained
SQL> select * from table(dbms_xplan.display(null,null,'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3409221634
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 490 | 105K| 986 (0)| 0
| 1 | NESTED LOOPS | | | | |
| 2 | NESTED LOOPS | | 490 | 105K| 986 (0)| 0
| 3 | TABLE ACCESS FULL | T1 | 490 | 6370 | 6 (0)| 0
|* 4 | INDEX RANGE SCAN | IDX_T_ID | 1 | | 1 (0)| 0
| 5 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 2 (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."OBJECT_ID"="T"."OBJECT_ID")
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- dynamic sampling used for this statement (level=2)
21 rows selected
2.8 使用MERGE JOIN
SQL>
SQL> explain plan for select /*+ use_merge(t,t1)*/ t.* from t ,t1 where t1.object_id=t.object_id ;
Explained
SQL> select * from table(dbms_xplan.display(null,null,'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2941280097
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 490 | 105K| 1275 (1)| 0
| 1 | MERGE JOIN | | 490 | 105K| 1275 (1)| 0
| 2 | TABLE ACCESS BY INDEX ROWID| T | 78211 | 15M| 1268 (1)| 0
| 3 | INDEX FULL SCAN | IDX_T_ID | 78211 | | 183 (1)| 0
|* 4 | SORT JOIN | | 490 | 6370 | 7 (15)| 0
| 5 | TABLE ACCESS FULL | T1 | 490 | 6370 | 6 (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."OBJECT_ID"="T"."OBJECT_ID")
filter("T1"."OBJECT_ID"="T"."OBJECT_ID")
Note
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
- dynamic sampling used for this statement (level=2)
22 rows selected
从上面3个执行计划可以看到。默认 HASH JOIN 为最优的。成本分别为：
MERGE JOIN Cost=1275
HASH JOIN Cost=311
NESTED JOIN Cost=986
因为小表中添加了很多重复的数据，这时已不适合使用嵌套关联。
同时可以看到，两次的测试中，MERGE JOIN 都是最差的。
MERGE JOIN 适合的场景为两个关联表的数据是经过排序后的子表。如以下SQL，执行计划为MERGE JOIN.
select t.* from (select * from t order by object_id) t
,(select * from t1 order by object_id) t1
where t.object_id=t1.object_id
--------------------------------------------------------------------------------
3.通过append hint来插入数据，演示它和普通插入数据的性能比较。
3.1 对表T1 插入数据的代码查看执行计划
SQL> explain plan for insert into t1 select * from t where object_id<300 ;
Explained
SQL> select * from table(dbms_xplan.display(null,null,'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 514881935
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 298 | 61686 | 7 (0)| 0
| 1 | LOAD TABLE CONVENTIONAL | T1 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 298 | 61686 | 7 (0)| 0
|* 3 | INDEX RANGE SCAN | IDX_T_ID | 298 | | 2 (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"<300)
Note
-----
- dynamic sampling used for this statement (level=2)
19 rows selected
3.2 对表T1 设置成NOLOOGIN 模式
SQL> alter table t1 nologging;
Table altered
3.3 再对执行计划中添加 /*+ append */
SQL> explain plan for insert /*+ append */ into t1 select * from t where object_id<300 ;
Explained
SQL> select * from table(dbms_xplan.display(null,null,'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3221007604
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 298 | 61686 | 7 (0)| 0
| 1 | LOAD AS SELECT | T1 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 298 | 61686 | 7 (0)| 0
|* 3 | INDEX RANGE SCAN | IDX_T_ID | 298 | | 2 (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"<300)
Note
-----
- dynamic sampling used for this statement (level=2)
19 rows selected
从上面两次的执行计划可以看出，结果是一样的。以上测试环境为非归档模式。
下面在一个RAC归档模式下，（RAC+ DATAGUARD）看到结果还是一样的，我分析是因为在设置DATAGUARD
时，已设置成：打开Forced Logging模式（alter database force logging; ） ，
那么这时Oracle无论什么操作都进行redo的写入，这刚好与我的测试结果是相符合的。
SQL> show parameter db_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string racdb
SQL> explain plan for insert into t1 select * from t where object_id<300 ;
Explained
SQL> select * from table(dbms_xplan.display(null,null,'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 12 | 2484 | 310 (1)| 00:00:04
| 1 | LOAD TABLE CONVENTIONAL | T1 | | | |
|* 2 | TABLE ACCESS FULL | T | 12 | 2484 | 310 (1)| 00:00:04
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"<300)
Note
-----
- dynamic sampling used for this statement (level=2)
18 rows selected
SQL> alter table t1 nologging;
Table altered
SQL> explain plan for insert /*+ append */ into t1 select * from t where object_id<300 ;
Explained
SQL> select * from table(dbms_xplan.display(null,null,'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 570131543
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 12 | 2484 | 310 (1)| 00:00:04 |
| 1 | LOAD AS SELECT | T1 | | | | |
|* 2 | TABLE ACCESS FULL| T | 12 | 2484 | 310 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"<300)
Note
-----
- dynamic sampling used for this statement (level=2)
18 rows selected
--------------------------------------------------------------------------------
4.验证Oracle在没有使用hint DRIVING_SITE时，是否会将远程的数据拉到本地执行。
4.1 建立远程数据连接：
create public database link REALDB connect to TANG using 'racdb1';
4.2 按默认执行查看执行计划
SQL>set autotrace on
SQL>set autotrace traceonly
SQL> SET LINESIZE 800;
4.3 为了检查两种效果，我在两个表中都建立索引
drop table t;
drop table t1;
create table t as select * from dba_objects;
create table t1 as select * from t where object_id<100;
create index idx_t_id on t(object_id)
create index idx_t_object_id on t(object_id);
select 't',count(0) from t@realdb
union all
select 't1',count(0) from t1;
------------------------------------------
'T' COUNT(0)
1 t 77135
2 t1 98
4.4 使用默认执行计划，调用远程大表进行查询，可以看出，这时是以大表做为远程表，本地小表T1的索引这时起了作用。
SQL> set autotrace on
SQL> set autotrace traceonly ;
SQL> select t.* from t@realdb ,t1 where t.object_id=t1.object_id;
98 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 257474329
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98 | 9898 | 198 (1)| 00:00:03 | | |
| 1 | NESTED LOOPS | | 98 | 9898 | 198 (1)| 00:00:03 | | |
| 2 | INDEX FULL SCAN| IDX_T1_OBJECT_ID | 98 | 294 | 1 (0)| 00:00:01 | | |
| 3 | REMOTE | T | 1 | 98 | 2 (0)| 00:00:01 | REALDB | R->S |
-----------------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
3 - SELECT "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYP
E","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY","NAMESP
ACE","EDITION_NAME" FROM "T" "T" WHERE "OBJECT_ID"=:1 (accessing 'REALDB' )
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
5352 bytes sent via SQL*Net to client
589 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
98 rows processed
4.5 指定远程大表T 做为驱动表，这时把数据，本地小表T1的索引没起作用。这时T的索引起作用。
可以看到，这时的执行成本稍微低一些，这应该是因为表数据量不大的原因。
SQL> select /*+ DRIVING_SITE(t)*/ t.* from t@realdb ,t1 where t.object_id=t1.object_id;
98 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 999555369
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE | | 98 | 10878 | 197 (0)| 00:00:03 | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 98 | 10878 | 197 (0)| 00:00:03 | | |
| 3 | REMOTE | T1 | 98 | 1274 | 1 (0)| 00:00:01 | ! | R->S |
|* 4 | INDEX RANGE SCAN | IDX_T_OBJECT_ID | 1 | | 1 (0)| 00:00:01 | RACDB | |
| 5 | TABLE ACCESS BY INDEX ROWID| T | 1 | 98 | 2 (0)| 00:00:01 | RACDB | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A2"."OBJECT_ID"="A1"."OBJECT_ID")
Remote SQL Information (identified by operation id):
----------------------------------------------------
3 - SELECT "OBJECT_ID" FROM "T1" "A1" (accessing '!' )
Note
-----
- fully remote statement
Statistics
----------------------------------------------------------
8 recursive calls
2 db block gets
7 consistent gets
0 physical reads
0 redo size
5725 bytes sent via SQL*Net to client
589 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
98 rows processed
SQL>
--------------------------------------------------------------------------------
5.用cardinality hint来模拟表中的数据，写一条SQL语句并给出它的执行计划。
接着上一题目的测试，因为优势不明显，这里我使用 cardinality 来模拟更大的数据量，
5.1在第默认使用驱动表的基础上，添加 cardinality 可以看到。数据量上来了，成本也上升。
SQL> select /*+cardinality(t 90000000)*/ t.* from t@realdb ,t1 where t.object_id=t1.object_id;
98 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 257474329
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 114K| 11M| 881 (78)| 00:00:11 | | |
| 1 | NESTED LOOPS | | 114K| 11M| 881 (78)| 00:00:11 | | |
| 2 | INDEX FULL SCAN| IDX_T1_OBJECT_ID | 98 | 294 | 1 (0)| 00:00:01 | | |
| 3 | REMOTE | T | 1167 | 111K| 2 (0)| 00:00:01 | REALDB | R->S |
-----------------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
3 - SELECT /*+ OPT_ESTIMATE (TABLE "T" ROWS=90000000.000000 ) */
"OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYPE","CREATED","
LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY","NAMESPACE","EDITION_
NAME" FROM "T" "T" WHERE "OBJECT_ID"=:1 (accessing 'REALDB' )
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
5352 bytes sent via SQL*Net to client
589 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
98 rows processed
5.2 但使用在以下语句中时，cardinality(t 90000000) 不起作用了。
SQL> select /*+ cardinality(t 90000000) DRIVING_SITE(t) */ t.* from t@realdb ,t1 where t.object_id=t1.object_id;
98 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 999555369
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE | | 98 | 10878 | 197 (0)| 00:00:03 | | |
| 1 | NESTED LOOPS | | | | | | | |
| 2 | NESTED LOOPS | | 98 | 10878 | 197 (0)| 00:00:03 | | |
| 3 | REMOTE | T1 | 98 | 1274 | 1 (0)| 00:00:01 | ! | R->S |
|* 4 | INDEX RANGE SCAN | IDX_T_OBJECT_ID | 1 | | 1 (0)| 00:00:01 | RACDB | |
| 5 | TABLE ACCESS BY INDEX ROWID| T | 1 | 98 | 2 (0)| 00:00:01 | RACDB | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A2"."OBJECT_ID"="A1"."OBJECT_ID")
Remote SQL Information (identified by operation id):
----------------------------------------------------
3 - SELECT "OBJECT_ID" FROM "T1" "A1" (accessing '!' )
Note
-----
- fully remote statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
5725 bytes sent via SQL*Net to client
589 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
98 rows processed
SQL>
经测试，可能是/*+ cardinality(t 90000000) */ 与远程连接@REALDB 有冲突或是不支持的原因。具体还不知道。
- 【性能优化】 之 HINTS 相关作业
- 【性能优化】之 LATCH 作业
- Apache 性能优化相关
- Java性能优化相关
- hashCode相关性能优化
- Android 性能优化相关
- 性能优化相关
- Android性能优化相关
- hashCode相关性能优化
- hashCode相关性能优化
- android性能优化相关
- jquery性能优化相关
- hashCode相关性能优化
- SQL Server查询优化之:使用提示(Hints)
- hibernate3第六章之性能优化相关几个问题_1
- Hints优化查询
- <<Oracle数据库性能优化艺术（第五期）>> 第5周 Hints
- java性能优化相关实例
- google gadget 开发 参考资料
- xargs解读
- 一些做的过的零散的题目(poj)（二）:
- 各大PT网站信息记录（不断更新中）
- mysql的日期字段和插入日期/返回最右边5个字节right
- 【性能优化】 之 HINTS 相关作业
- 【PAT】1065. A+B and C (64bit) (20)
- linux thread_run
- .NET Socket开发之同步Socket实现两例
- linux上开发使用hg时的一些设置
- javax.servlet cannot be resolved to a type
- 配置Solr中文分词器IK以及ansj，支持动态修改用户词库。
- junit做单元测试的两种方法
- Hook MessageBox 进阶 跨进程Hook
