客戶在升級程式時當機.原因是因為select * from application_versions是查詢這個語句時當機,因為在 application_versions表中儲存了幾十個更新程式的檔案是以blob型別來儲存的，當客戶登入系統時會檢查更新並下載這些程式檔案.

下面是執行升級時所執行語句的跟蹤資訊

TKPROF: Release 11.2.0.3.0 - Development on Thu Oct 11 11:56:47 2012

Trace file: /database/diag/rdbms/xxdyb/xxdyb1/trace/xxdyb1_ora_802824_Jy_Trace_20121011.trc

Sort options: default

********************************************************************************

count = number of times OCI procedure was executed

cpu = cpu time in seconds executing

elapsed = elapsed time in seconds executing

disk = number of physical reads of buffers from disk

query = number of buffers gotten for consistent read

current = number of buffers gotten in current mode (usually for update)

rows = number of rows processed by the fetch or execute call

********************************************************************************

SQL ID: cf06fwacdmgfk Plan Hash: 1546270724

select 'x'

from

dual

call count cpu elapsed disk query current rows

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

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 1 0.00 0.00 0 0 0 1

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

total 3 0.00 0.00 0 0 0 1

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 85 (INSUR_TEST)

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation

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

1 1 1 FAST DUAL (cr=0 pr=0 pw=0 time=3 us cost=2 size=0 card=1)

Rows Execution Plan

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

0 SELECT STATEMENT MODE: ALL_ROWS

1 FAST DUAL

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

SQL*Net message to client 2 0.00 0.00

SQL*Net message from client 2 0.01 0.01

********************************************************************************

SQL ID: fdjxawa50ppb3 Plan Hash: 2249315794

select *

from

application_version

call count cpu elapsed disk query current rows

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

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 100 0.52 1137.37 0 18010 1 99

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

total 102 0.52 1137.37 0 18010 1 99

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 85 (INSUR_TEST)

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation

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

99 99 99 TABLE ACCESS FULL APPLICATION_VERSION (cr=9409 pr=0 pw=0 time=710 us cost=2495 size=4512 card=96)

Rows Execution Plan

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

0 SELECT STATEMENT MODE: ALL_ROWS

99 TABLE ACCESS MODE: ANALYZED (FULL) OF 'APPLICATION_VERSION'

(TABLE)

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

SQL*Net message to client 101 0.00 0.00

SQL*Net message from client 101 10.60 184.38

SQL*Net more data to client 34094 10.73 1136.87

********************************************************************************

但在透過應用程式程式上傳這個程式檔案並儲存在application_versions表中是速度很快.

SQL*Net more data to client 這個等待事件是指示資料庫在不斷地傳送資料到客戶端時的網路問題.

後經地檢查傳送是華為交換機的問題，但上傳更新程式到資料庫中網路沒有問題，但是下載時網路出問題.

SQL*Net more data to client 等待事件造成的效能問題

相關文章