從等待事件找到具體session的sql語句進行調控：

看eygle深入淺出中關於最後sql效能診斷部分，其中提到的幾個案例，對於大表缺少必要索引而導致全表掃描影響效能；大表相應欄位存在索引但是oracle並不走索引，關鍵字中如果存在轉換，很可能oracle會使索引失效（選擇hint都會失效）而選擇全表掃描。

對於大表缺少索引根據v$session和v$session_wait檢視找到sid的等待事件，如果對於大表全表掃描user I/O等待事件db file scattered需要過多關注透過v$sqltext的hash_value和address和v$session的sql_hash_value和sql_address找到sql語句，然後對相應的關鍵列建立合適的索引，減少全表掃描。

這裡自己結合效能診斷和測試例子來模擬下關鍵字轉換刀子索引失效而全表掃描。

Sql>create table test20(id varchar2(10));

SQL> declare

2 begin

3 for i in 1..1000 loop

4 insert into test20 values(to_char(i));

5 commit;

6 end loop;

7 end;

8 /

PL/SQL procedure successfully completed

SQL> create index index_test20 on test20(id);

Index created

SQL> execute dbms_stats.gather_table_stats('xiaoyu','test20');

PL/SQL procedure successfully completed

Test20列id上建立了合適的索引，看看下面的執行計劃

SQL> explain plan for select * from test20 where id='456';

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2611328442

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01

|* 1 | INDEX RANGE SCAN| INDEX_TEST20 | 1 | 3 | 1 (0)| 00:00:01

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("ID"='456')

13 rows selected

Id上存在索引，oracle選擇了高效索引。

SQL> explain plan for select * from test20 where id=456;

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 883513759

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| TEST20 | 1 | 3 | 3 (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(TO_NUMBER("ID")=456)

13 rows selected

由於ID列是varchar2（10）型別的，此時where查詢條件進行了隱式轉換（把varchar2（10）轉換為number型別的），此時oracle會放棄索引而選擇全表掃描然後隱式轉換varchar2（10）為number型別再進行比較，此時我們進行hint下試試看能否啟動索引。

強制hint走索引

select /*+index(test20)*/* from test20 where id=456

SELECT STATEMENT, GOAL = ALL_ROWS 3 1 3

TABLE ACCESS FULL XIAOYU TEST20 3 1 3

此時oracle還是選擇了全表掃描，看來varchar2（10）轉換number已經讓索引無法利用了。

SQL> desc test18;

Name Type Nullable Default Comments

----------- ------------- -------- ------- --------

OBJECT_NAME VARCHAR2(128) Y

OBJECT_ID NUMBER Y

SQL> select b.index_name, b.table_name, a.column_name

2 from user_ind_columns a, user_indexes b

3 where b.table_name = 'TEST18'

4 and a.index_name = b.index_name

5 ;

INDEX_NAME TABLE_NAME COLUMN_NAME

------------------------------ ------------------------------ --------------------------------------------------------------------------------

INDEX_TEST18 TEST18 OBJECT_ID

INDEX_TEST18_OBJ TEST18 OBJECT_NAME

下面再來看看number轉換為varchar2（10）的隱式轉換

SQL> explain plan for select * from test18 where object_id=456;

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2319938169

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)

| 1 | TABLE ACCESS BY INDEX ROWID| TEST18 | 1 | 29 | 2 (0)

|* 2 | INDEX RANGE SCAN | INDEX_TEST18 | 1 | | 1 (0)

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID"=456)

14 rows selected

SQL> explain plan for select * from test18 where object_id='456';

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2319938169

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)

| 1 | TABLE ACCESS BY INDEX ROWID| TEST18 | 1 | 29 | 2 (0)

|* 2 | INDEX RANGE SCAN | INDEX_TEST18 | 1 | | 1 (0)

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID"=456)

14 rows selected

看來即使此時關鍵字上存在隱式轉換，oracle依然選擇了索引，從上述實驗我們需要注意的是，索引關鍵字上必須減少甚至杜絕隱式轉換。

這裡再闡述下上述兩個例子的隱式轉換和索引利用的區別

根據隱式轉換的是如下進行的：

Select * from test20 where id=6789

此時的id為varchar2（10）型別，隱式轉換的原則是select * from test20 where to_number(id)=6789，由於索引上儲存的都是varchar2（10）型別的ID關鍵字值，很自然索引無法利用。看來在select語句中，number和varchar的隱式轉換的優先順序：varchar隱式轉

換為number型別的。

下面就可以很自然的解釋為什麼select * from test18 where object_id=’456’的隱式轉換為：select * from test18 where object_id=to_number(‘456’),此時索引關鍵字並沒有進行轉換，轉換的是字元型’456’隱式轉換為to_number(‘456’)，索引可以高效利用。

下面有關於dml語句的隱式轉換，其實和select語句一樣，varchar2優先轉換為number型別

A：

SQL> explain plan for update test20 set id='7890' where id=123;

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3897430916

-----------------------------------------------------------------------------

-----------------------------------------------------------------------------

| 0 | UPDATE STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |

|* 2 | TABLE ACCESS FULL| TEST20 | 1 | 3 | 3 (0)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter(TO_NUMBER("ID")=123)

14 rows selected

B:

SQL> explain plan for update test18 set object_id=789 where object_id='123';

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 176192607

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | UPDATE STATEMENT | | 1 | 5 | 1 (0)| 00:00:01

|* 2 | INDEX RANGE SCAN| INDEX_TEST18 | 1 | 5 | 1 (0)| 00:00:01

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID"=123)

14 rows selected

還可以測試下關於varchar2型別和date型別的轉換，varchar2隱式轉換為to_date型別。

這裡就不列出例子了。

[@more@]

索引關鍵字的隱式轉換分析

相關文章