做完 優化課程 中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 相关作业的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。