利用虛擬索引（Virtual Index）最佳化資料庫的案例分析，virtual案例分析

利用虛擬索引（Virtual Index）最佳化資料庫的案例分析，virtual案例分析
當我們在對生產庫做最佳化的時候，主要就是對SQL語句的最佳化，包括語句的等價改寫等，但其中很大一部分情況，又與索引有關。如果能合理利用合適的索引，可以使原本走全表掃描產生的邏輯讀大大降低，提高資料庫的效能。由於Oracle資料庫中的索引本身就要佔用磁碟空間，維護索引需要一定的開銷，如何才能知道建立某個索引，會給資料帶來效能的提升，而又不至於判斷失誤，建立了一個不恰當的索引，最後又不得不刪除呢？這種情況下，我們可以利用Oralce提供的虛擬索引，即nosegment索引，它並不佔用磁碟資源，只是在資料字典中增加一個定義。它為DBA在建立索引對提升資料庫效能的方面提供了一定的參考。下面來看具體測試和分析：
SQL> startupORACLE instance started.
Total System Global Area  835104768 bytesFixed Size                  2232960 bytesVariable Size             675286400 bytesDatabase Buffers          155189248 bytesRedo Buffers                2396160 bytesDatabase mounted.Database opened.
--本測試在11.2.0.3.0環境，與10g略有不同SQL> select * from v$version;
BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE    11.2.0.3.0      ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - Production
--建立測試表fakeind並插入資料SQL> drop table fakeind_test;drop table fakeind_test           *ERROR at line 1:ORA-00942: table or view does not exist

SQL> create table fakeind_test as select * from dba_objects;
Table created.
SQL> insert into fakeind_test select * from fakeind_test;
75540 rows created.
SQL> /
151080 rows created.
SQL> /
302160 rows created.
SQL> select count(*) from fakeind_test;
  COUNT(*)----------    604320
--開始測試，執行查詢SQL> set line 130 pages 130
SQL> select object_id,object_name from fakeind_test where object_id in(select distinct object_id from fakeind_test where object_id>44500 and object_id<45000);
3992 rows selected.

Execution Plan----------------------------------------------------------Plan hash value: 1190425891
-------------------------------------------------------------------------------------| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------|   0 | SELECT STATEMENT     |              |  3816 |   160K|  4667   (1)| 00:00:57 ||*  1 |  HASH JOIN RIGHT SEMI|              |  3816 |   160K|  4667   (1)| 00:00:57 ||   2 |   VIEW               | VW_NSO_1     |  3819 | 49647 |  2333   (1)| 00:00:28 ||*  3 |    TABLE ACCESS FULL | FAKEIND_TEST |  3819 | 19095 |  2333   (1)| 00:00:28 ||   4 |   TABLE ACCESS FULL  | FAKEIND_TEST |   604K|    17M|  2331   (1)| 00:00:28 |-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
   1 - access("OBJECT_ID"="OBJECT_ID")   3 - filter("OBJECT_ID">44500 AND "OBJECT_ID"<45000)

Statistics----------------------------------------------------------         23  recursive calls          0  db block gets      17436  consistent gets          0  physical reads          0  redo size     144488  bytes sent via SQL*Net to client       3445  bytes received via SQL*Net from client        268  SQL*Net roundtrips to/from client          3  sorts (memory)          0  sorts (disk)       3992  rows processed
可以看到，用CTAS建立的測試表fakeind上目前並沒有索引，因此在產生的執行計畫中，該條SQL語句只能走全表掃描
--建立虛擬索引(在普通建立索引命令後加一個nosegmnet即可)SQL> create index ind_fake_id on fakeind_test(object_id) nosegment;
Index created.
--設定隱含參數使虛擬索引生效SQL> alter session set "_use_nosegment_indexes"=true;  --注意必須要寫雙引號，單引號不行

Session altered.
SQL> set autot off
--查看錶是否被分析過SQL> select table_name,last_analyzed from dba_tables where table_name='FAKEIND_TEST';
TABLE_NAME                     LAST_ANALYZED------------------------------ ------------------FAKEIND_TEST
--收集測試表的統計資訊SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'FAKEIND_TEST',degree=>4,estimate_percent=>100,cascade=>true);
PL/SQL procedure successfully completed.
--再次確認表的分析情況SQL> select table_name,last_analyzed from dba_tables where table_name='FAKEIND_TEST';
TABLE_NAME                     LAST_ANALYZED------------------------------ ------------------FAKEIND_TEST                   17-SEP-14
--重新查詢測試表SQL> set autot traceSQL> select object_id,object_name from fakeind_test where object_id in(select distinct object_id from fakeind_test where object_id>44500 and object_id<45000);
3992 rows selected.

Execution Plan----------------------------------------------------------Plan hash value: 2531911586
-----------------------------------------------------------------------------------------------| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT               |              |  3904 |   308K|    12  (17)| 00:00:01 ||   1 |  VIEW                          | VM_NWVW_2    |  3904 |   308K|    12  (17)| 00:00:01 ||   2 |   HASH UNIQUE                  |              |  3904 |   179K|    12  (17)| 00:00:01 ||*  3 |    HASH JOIN                   |              |  3904 |   179K|    11  (10)| 00:00:01 ||*  4 |     INDEX RANGE SCAN           | IND_FAKE_ID  |  3819 | 19095 |     2   (0)| 00:00:01 ||   5 |     TABLE ACCESS BY INDEX ROWID| FAKEIND_TEST |  3819 |   156K|     8   (0)| 00:00:01 ||*  6 |      INDEX RANGE SCAN          | IND_FAKE_ID  |  3819 |       |     2   (0)| 00:00:01 |-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
   3 - access("OBJECT_ID"="OBJECT_ID")   4 - access("OBJECT_ID">44500 AND "OBJECT_ID"<45000)   6 - access("OBJECT_ID">44500 AND "OBJECT_ID"<45000)

Statistics----------------------------------------------------------          1  recursive calls          0  db block gets      17418  consistent gets          0  physical reads          0  redo size     144488  bytes sent via SQL*Net to client       3445  bytes received via SQL*Net from client        268  SQL*Net roundtrips to/from client          1  sorts (memory)          0  sorts (disk)       3992  rows processed
SQL> set autot off
此時利用虛擬索引獲得的執行計畫中，COST從之前的4000多降低到12，執行時間也從57s到1s，由此可以判斷，當加上真實索引後，效能會大大提高。

--建立真實索引
SQL> create index ind_real_id on fakeind_test(object_id);

Index created.
SQL> set autot traceSQL> select object_id,object_name from fakeind_test where object_id in(select distinct object_id from fakeind_test where object_id>45500 and object_id<50000);
35992 rows selected.

Execution Plan----------------------------------------------------------Plan hash value: 2531911586
-------------------------------------------------------------------------------------------------------| Id  | Operation                      | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |-------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT               |              | 41816 |  3307K|       |   548   (1)| 00:00:07 ||   1 |  VIEW                          | VM_NWVW_2    | 41816 |  3307K|       |   548   (1)| 00:00:07 ||   2 |   HASH UNIQUE                  |              | 41816 |  1919K|  2472K|   548   (1)| 00:00:07 ||*  3 |    HASH JOIN                   |              | 41816 |  1919K|       |    53   (2)| 00:00:01 ||*  4 |     INDEX RANGE SCAN           | IND_FAKE_ID  | 34375 |   167K|       |     3   (0)| 00:00:01 ||   5 |     TABLE ACCESS BY INDEX ROWID| FAKEIND_TEST | 34375 |  1409K|       |    49   (0)| 00:00:01 ||*  6 |      INDEX RANGE SCAN          | IND_FAKE_ID  | 34375 |       |       |     3   (0)| 00:00:01 |-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
   3 - access("OBJECT_ID"="OBJECT_ID")   4 - access("OBJECT_ID">45500 AND "OBJECT_ID"<50000)   6 - access("OBJECT_ID">45500 AND "OBJECT_ID"<50000)

Statistics----------------------------------------------------------          1  recursive calls          0  db block gets      11017  consistent gets         82  physical reads          0  redo size    1293055  bytes sent via SQL*Net to client      26908  bytes received via SQL*Net from client       2401  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)      35992  rows processed
雖然建立了真實索引，但資料庫卻仍舊在用虛擬索引，此時COST和TIME反而還上去了一點，那麼需要先禁用虛擬索引
SQL> alter session set "_use_segment_indexes"=false;
--禁用虛擬索引後繼續查看剛才的SQLSQL> select object_id,object_name from fakeind_test where object_id in(select distinct object_id from fakeind_test where object_id>45500 and object_id<50000);
35992 rows selected.

Execution Plan----------------------------------------------------------Plan hash value: 750753197
-------------------------------------------------------------------------------------| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------|   0 | SELECT STATEMENT     |              | 34375 |  1443K|  2414   (1)| 00:00:29 ||*  1 |  HASH JOIN RIGHT SEMI|              | 34375 |  1443K|  2414   (1)| 00:00:29 ||   2 |   VIEW               | VW_NSO_1     | 34375 |   436K|    79   (0)| 00:00:01 ||*  3 |    INDEX RANGE SCAN  | IND_REAL_ID  | 34375 |   167K|    79   (0)| 00:00:01 ||   4 |   TABLE ACCESS FULL  | FAKEIND_TEST |   604K|    17M|  2331   (1)| 00:00:28 |-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
   1 - access("OBJECT_ID"="OBJECT_ID")   3 - access("OBJECT_ID">45500 AND "OBJECT_ID"<50000)

Statistics----------------------------------------------------------          0  recursive calls          0  db block gets      11017  consistent gets          0  physical reads          0  redo size    1293055  bytes sent via SQL*Net to client      26908  bytes received via SQL*Net from client       2401  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)      35992  rows processed
雖然使用真實索引之後，效能提升並不如使用虛擬索引時那樣多，但至少比最初沒有索引的情況下，要快了將近28秒，COST也減少了將近一半，當真實索引建立完畢以後，可以對虛擬索引進行刪除，以免白白佔用一個對象名，刪除文法和刪除普通索引一致。
虛擬索引有幾個要注意的地方：
--虛擬索引並不存在於dba_indexes視圖SQL> select index_name from dba_indexes where index_name='IND_FAKE_ID';
no rows selected
--無法建立與虛擬索引同名的真實索引SQL> create index ind_fake_id on fakeind_test(object_name);create index ind_fake_id on fakeind_test(object_name)             *ERROR at line 1:ORA-00955: name is already used by an existing object
--無法使用alter命令來修改或重建索引SQL> alter index ind_fake_id rename to ind_fake_name;alter index ind_fake_id rename to ind_fake_name*ERROR at line 1:ORA-08114: can not alter a fake index

SQL> alter index ind_fake_id rebuild;alter index ind_fake_id rebuild*ERROR at line 1:ORA-08114: can not alter a fake index
--查看虛擬索引的方法SQL> set autot offSQL> SELECT index_owner, index_name    2  FROM dba_ind_columns  3  WHERE index_name NOT LIKE 'BIN$%'   4  MINUS  5  SELECT owner, index_name   6  FROM dba_indexes;
INDEX_OWNER                    INDEX_NAME------------------------------ ------------------------------SYS                            IND_FAKE_ID

總結：
這個測試其實並沒有做的很完善，SQL語句選取得不好，正常情況下，原有語句所涉及的表至少且肯定會有一個主鍵索引，沒有索引的表在OLTP生產庫中是不太現實的。本文主要是起到一個拋磚引玉的作用，當我們面對一個資料庫最佳化的情境，需要測試建立某個特定條件的索引是否會給系統帶來效能提升，就可以藉助虛擬索引來測試，因為在生產庫中通常不允許隨意建立索引，而維護索引對資料庫而言也是一筆不小的開銷，況且如果索引建立不當，可能使資料庫效能更糟糕。






資料庫案例分析與設計

30rmb可能會有人回答。
 
監控索引

--
ALTER INDEX INDEX1 MONITORING USAGE
ALTER INDEX INDEX1 NOMONITORING USAGE
--