記一次隐式轉化引起的資料庫性能故障

上周給客戶資料庫從Oralce 9.2.0.4更新到10.2.0.5之後，系統穩定運作。但昨天打電話給我說，資料庫出現性能問題，主要表現為儲存送出時非常緩慢，比更新之前慢了好多。正好，同一天，同一個客戶的另一個rac資料庫二号節點當機，需要現場支援。幫助客戶分析好rac當機原因之後，開始分析資料庫性能分析。通常來講，資料庫更新之後，出現業務響應緩慢，一般都是執行計劃變更引起的。由于客戶不能提供性能變壞業務子產品SQL語句，于是隻好從AWR報告開始分析。我們分析問題時，有一點需要注意的是，客戶告之的故障之後，對故障要有一般要有自己的判斷，不能被客戶牽着鼻子走，否則容易誤入歧途，給故障診斷，帶來不利的影響。閑話不說，步入正題，首先分析awr報告。

awr報告采樣自業務高峰期間，具有一定的典型性:

[img]http://dl.iteye.com/upload/attachment/467617/b052f56b-0397-3f1c-80bb-7dd09e5ab777.jpg[/img]

從profile來看，實體讀比例有點高，達到了每秒8,211次。硬解析也偏高，達到了每秒14.09次，這兩項名額也直接導緻了buffer cache命中率和library cache命中率偏低，分别隻有

82.59%和81.31%。名額偏低隻能給我們指明資料庫可能出問題的方向，由于沒有做性能名額baseline，通過這名額偏低資料庫可能存在2個問題：1、SQL執行計劃執行效率有問題，導緻

大量的實體讀。2、硬解析過多，可能引起shared pool中latch的争用。

[img]http://dl.iteye.com/upload/attachment/467619/bb3d021e-ccbf-33db-9f98-1b48446fe3b1.jpg[/img]

通過檢視top 5等待事件，資料庫存在的問題，漸漸浮出水面。可以看到除了CPU TIME之外，read by other session和db file scattered read排在前2位，一般來講，這2個等待事件。同時出現，也就意味着資料庫中正在并發的執行全表掃描，而硬解析過多引起的故障可以暫時不予考慮。

[img]http://dl.iteye.com/upload/attachment/467621/03307c33-2074-3d01-bfd4-a14f27448473.jpg[/img]

一般來講定位全表掃描的語句可以檢視SQL ordered by Gets或者SQL ordered by Reads或者SQL ordered by CPU Time。

通過查找發現在SQL ordered by Gets，SQL ordered by Reads，SQL ordered by CPU Time，以下2條SQL語句排名均占前2位，現在問題越來越明朗了。

[img]http://dl.iteye.com/upload/attachment/467623/881fe9b3-9568-3db5-9fa5-cbbb8bf13178.jpg[/img]

我們着重分析第一條SQL，檢視其的執行計劃，确定有無child，注意到字段BIND_DATA，該SQL可能有綁定變量窺視（bind peeking），在Oracle 11g之前，綁定變量窺視一直是執行計劃不穩定的重要原因之一。

[quote]SQL> select CHILD_NUMBER,LAST_ACTIVE_TIME,BIND_DATA from v$sql where sql_id='cx0dsyvc8gmfp';

CHILD_NUMBER LAST_ACTIVE_TIME BIND_DATA

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

0 2011-04-20 10:08:11 BEDA0A2005004DAE3D43000101C002160BCA041F1501010B0831105E[/quote]

确定child_number為0之後，利用Oracle 10g提供的dbms_xplan.display_awr工具包可以知道此SQL在awr報告裡的執行計劃，在Oracle 9i需要将statspack級别設定成6級之後，才能檢視sql在statspack的執行計劃。可以看到SQL采用了全表掃描的執行計劃，其cost高達15397。注意到字段RYYWBM采用綁定變量指派。

[quote]SQL> select * from table(dbms_xplan.display_awr('cx0dsyvc8gmfp'));

PLAN_TABLE_OUTPUT

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

SQL_ID cx0dsyvc8gmfp

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

SELECT HJDPPBZ,HJDSSXQ,HJDXZ,HJDXQ,HJDDZBM,CLBZ FROM PZT_CZRK_DJ WHERE

RYYWBM=:B1

Plan hash value: 593801944

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

PLAN_TABLE_OUTPUT

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

| 0 | SELECT STATEMENT | | | | 15397 (100)|

| 1 | TABLE ACCESS FULL| PZT_CZRK_DJ | 1 | 97 | 15397 (1)| 00:03:36

PLAN_TABLE_OUTPUT

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

14 rows selected.[/quote]

為避免綁定變量窺視帶來的執行計劃不穩定，可以看到RYYWBM選擇性很好

[quote]SQL> select count(*) from hz2004.PZT_CZRK_DJ;

COUNT(*)

----------

5003467

SQL> select count(distinct RYYWBM) from hz2004.PZT_CZRK_DJ;

COUNT(DISTINCTRYYWBM)

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

5003471[/quote]

進一步檢視得知，RYYWBM為表格PZT_CZRK_DJ的主鍵

[quote]SQL> select INDEX_OWNER,INDEX_NAME,COLUMN_NAME from all_ind_columns where TABLE_OWNER='HZ2004' and TABLE_NAME='PZT_CZRK_DJ';

INDEX_OWNER INDEX_NAME COLUMN_NAME

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

HZ2004 PK_PZT_CZRK_DJ RYYWBM

HZ2004 IDX_PZT_CZRK_DJ_CJSJ CJSJ

HZ2004 IDX_PZT_CZRK_DJ_GMSFHM GMSFHM

HZ2004 IDX_PZT_CZRK_DJ_HJDDZBM HJDDZBM

HZ2004 IDX_PZT_CZRK_DJ_HJDXQ HJDXQ

HZ2004 IDX_PZT_CZRK_DJ_RKBM RKBM

HZ2004 IDX_PZT_CZRK_DJ_XM XM

HZ2004 IDX_PZT_CZRK_DJ_XZDDZBM XZDDZBM

HZ2004 IDX_PZT_CZRK_DJ_HHNBID HHNBID

SQL> select dbms_metadata.get_ddl('INDEX',U.INDEX_NAME,u.owner) from dba_indexes u where u.index_name='PK_PZT_CZRK_DJ';

CREATE UNIQUE INDEX "HZ2004"."PK_PZT_CZRK_DJ" ON "HZ2004"

."PZT_CZRK_DJ" ("RYYWBM")

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTI

STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXE

XTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUP

S 1 BUFFER_POOL DEFAULT)

TABLESPACE "HZ2004_PRM"[/quote]

同時主鍵狀态為有效，這就排除了因主鍵索引失效導緻全表掃描或者綁定變量窺視而導緻全表掃描

[quote]SQL> select status from dba_indexes where owner='HZ2004' and INDEX_NAME='PK_PZT_CZRK_DJ';

STATUS

--------

[color=red]VALID[/color][/quote]

這就奇怪了，唯一索引明明存在，但Oracle為什麼不選擇索引執行呢？

進一步利用dbms_xplan.display_cursor檢視其SQL在shared pool中的執行計劃，可以看到依然是全表掃描，但通過加參數advanced，我們得到了更多詳細的資訊。如綁定變量的值是3302000001005624885，并注意到Oracle對綁定變量值進行了隐式轉換TO_NUMBER("RYYWBM")=:B1。

[quote]SQL> select * from table(dbms_xplan.display_cursor('cx0dsyvc8gmfp',0,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID cx0dsyvc8gmfp, child number 0

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

SELECT HJDPPBZ,HJDSSXQ,HJDXZ,HJDXQ,HJDDZBM,CLBZ FROM PZT_CZRK_DJ WHERE

RYYWBM=:B1

Plan hash value: 593801944

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

PLAN_TABLE_OUTPUT

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

| 0 | SELECT STATEMENT | | | | 48777 (100)|

|* 1 | TABLE ACCESS FULL| PZT_CZRK_DJ | 1 | 89 | 48777 (1)| 00:11:23

PLAN_TABLE_OUTPUT

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1 / [email protected]$1

Outline Data

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

PLAN_TABLE_OUTPUT

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

Peeked Binds (identified by position):

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

1 - :B1 ([color=red]NUMBER[/color]): [color=red]3302000001005624885

[/color]

Predicate Information (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

1 - filter([color=red]TO_NUMBER("RYYWBM")=:B1[/color])

Column Projection Information (identified by operation id):

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

1 - "HJDSSXQ"[VARCHAR2,6], "HJDXZ"[VARCHAR2,150],

"HJDXQ"[VARCHAR2,12], "HJDDZBM"[VARCHAR2,32], "HJDPPBZ"[VARCHAR2,1],

"CLBZ"[VARCHAR2,1]

52 rows selected.[/quote]

為證明了确實是隐式轉換的問題，直接将綁定變量的值以具體值3302000001005624885代入，其執行計劃卻是出人意料的全表掃描。

[quote]SQL> set autotrace traceonly exp

SQL> SELECT HJDPPBZ, HJDSSXQ, HJDXZ, HJDXQ, HJDDZBM, CLBZ FROM hz2004.PZT_CZRK_DJ WHERE RYYWBM=3302000001005624885;

Execution Plan

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

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

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

| 0 | SELECT STATEMENT | | 1 | 93 | 48777 (1)|

| 1 | TABLE ACCESS FULL| PZT_CZRK_DJ | 1 | 93 | 48777 (1)|

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

Note

-----

- 'PLAN_TABLE' is old version[/quote]

馬上檢視其表結構，可以看到RYYWBM為varchar類型

[quote]SQL> desc hz2004.PZT_CZRK_DJ

Name Null? Type

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

RYYWBM NOT NULL [color=red]VARCHAR2(32)[/color]

。。。。[/quote]

到這裡問題應該很明顯了：

由于我們代入的值是number類型3302000001005624885，而在RYYWBM在表中定義為varchar2類型，估計是程式直接以number類型傳入，為了比對number類型

Oracle進行了類型隐式轉換：TO_NUMBER("RYYWBM")。相當于在列RYYWBM建立了函數to_number。考慮以下情況：

如表格test中id列有唯一索引，但由于加上了函數to_number，将使得SQL不能使用索引，而進行全表掃描。

select id from test where to_number(id)=1

如果要使用索引，需要建立函數索引。如

create index test_id_idx on test(to_number(id));

知道了原因之後，處理就很簡單了，即程式傳入varchar類型即可，問題也得到了圓滿解決

[quote]SQL> SELECT HJDPPBZ, HJDSSXQ, HJDXZ, HJDXQ, HJDDZBM, CLBZ FROM hz2004.PZT_CZRK_DJ WHERE RYYWBM='3302000001005624885';

Execution Plan

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

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

---

U)|

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

---

| 0 | SELECT STATEMENT | | 1 | 93 | 1 (

0)|

| 1 | TABLE ACCESS BY INDEX ROWID| PZT_CZRK_DJ | 1 | 93 | 1 (

0)|

| 2 | [color=red]INDEX UNIQUE SCAN[/color] | PK_PZT_CZRK_DJ | 1 | | 1 (

0)|

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

---[/quote]

記一次隐式轉化引起的資料庫性能故障

繼續閱讀

查找算法之二分查找查找算法之二分查找

JAVA高效程式設計指南

CQ V1.0分詞bates(基于雙數組tire樹)—應該是目前最快的中文分詞算法

【資料結構】醫院選址

windows不能在本地計算機上運作oracleDbConsoleorcl

關于SQL語言

[轉]ISUP信令的IAM消息詳細内容

SQL語言基礎：常用的資料查詢語句

筆試面試題目：滑動視窗(二)

交通/城市相關的公開的資料集上學時整理的Xie et al., 2020收集的

資料結構與算法（27）——排序（二）

neo4j之cypher使用文檔

GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？

sqlServer根據經緯查距離

Linux裝置模型（中）之上層容器

Oracle 批量查詢傳入List 傳回List