[20190506]檢視巢狀與繫結變數.txt

--//生產系統上線遇到的問題,5月1日上線,因為放假的緣故使用的人少,問題沒有暴露出來,5月5號大爆發,我今天5月6日早上開始介入檢視.

--//實際上昨天下午就開始查,因為快下班,同事給的sys秘密不對,無法登入資料庫.

--//今天上午拿到登入使用者也是普通使用者,不過許可權可以檢視許多dba檢視,應該足夠.不過不能抽取awr報表.

--//另外使用system使用者抽取awr報表馬上報ora-03113錯誤,注視乎是有問題那段時間抽取都報錯.

1.環境:

> @ ver1

PORT_STRING VERSION BANNER

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

IBMPC/WIN_NT-8.1.0 10.2.0.3.0 Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod

--//10.2.0.3版本,什麼現在上線還使用10g的產品,問了一下,居然還是32位版本,據說windows 2008R2的版本對方安裝64版失敗.

--//結果使用這個版本.順便看了一下硬體配置32G記憶體,64位版本.

2.問題:

--//實際上同事已經給出主要等待事件是出現大量'library cache lock',導致大量使用者無法執行sql語句.

--//我開始想也許開發可能修改某個儲存過程,導致包失效,看來定位不難.唯獨缺點是現在登入已經看不到這個等待事件.

--//只能做事後分析.

SELECT event, COUNT (*)

FROM DBA_HIST_ACTIVE_SESS_HISTORY

WHERE sample_time BETWEEN '2019/5/5 08:00:00' AND '2019/5/5 12:00:00'

GROUP BY event

ORDER BY 2 DESC;

EVENT COUNT(*)

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

library cache lock 10445

cursor: pin S wait on X 4693

1735

db file scattered read 300

library cache pin 264

read by other session 168

latch: shared pool 149

db file sequential read 74

latch: library cache 24

latch free 3

control file parallel write 2

log file parallel write 1

direct path write 1

log file sync 1

os thread startup 1

--//主要集中在library cache lock和cursor: pin S wait on X.

--//時間放大10倍.

--//10445*10=1066064,1066064/3600 = 296小時.

> @ ev_name 'library cache lock'

old 1: select * from v$event_name where lower(name) like lower('%&&1%')

new 1: select * from v$event_name where lower(name) like lower('%library cache lock%')

EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS

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

213 2032051689 latch: library cache lock address number tries 3875070507 4 Concurrency

216 916468430 library cache lock handle address lock address 100*mode+namespace 3875070507 4 Concurrency

--//10g居然在library cache lock有一個latch.

SELECT p1,count(*)

FROM DBA_HIST_ACTIVE_SESS_HISTORY

WHERE sample_time BETWEEN '2019/5/5 08:00:00' AND '2019/5/5 12:00:00' and EVENT ='library cache lock'

group by p1

order by 2 desc;

P1 COUNT(*)

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

2727911292 7953

2506679444 1216

2729104776 552

2655119196 284

2688420528 263

2222045940 121

2311090624 54

2717330580 1

2734098764 1

> @ 10to16 2727911292

10 to 16 HEX REVERSE16

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

00000000a2989f7c 0x7c9f98a2-00000000

> select kglnaown "Owner", kglnaobj "Object" from x$kglob where kglhdadr='00000000a2989f7c';

no rows selected

--//已經無法查詢到,也就是已經不再共享池,實際上重啟資料庫多次.

3.分析:

--//看到透過sql_id能否發現問題:

SELECT *

FROM v$sqlstats

WHERE sql_id IN (SELECT DISTINCT sql_id

FROM DBA_HIST_ACTIVE_SESS_HISTORY

WHERE sample_time BETWEEN '2019/5/5 08:00:00'

AND '2019/5/5 12:00:00'

AND EVENT = 'library cache lock'

AND sql_id IS NOT NULL);

--//僅僅看到2條語句:

SELECT COUNT (*)

FROM (SELECT *

FROM scm_port_dept_order

WHERE state = 7

AND store_id = '19'

AND EXISTS

(SELECT b.*

FROM zdp_SCM_ORDER_DEPT b

WHERE b.SOLUTION_ID = 'SCM_ORDER_DEPT'

AND b.UNIT_ID = 'DEPARTMENT_SCM'

AND b.USER_ID = '0000001775'

AND b.OPTION_ITEM_ID =

scm_port_dept_order.dept_id)) t;

--//下面開始出現災難...我在toad下按ctrl+e看執行計劃,toad馬上hang在哪裡.我嘗試幾次都是一樣.

--//我自己嘗試執行1次,再看執行計劃就很快,我才想起toad設定被我設定為可以直接看真實的執行計劃的模式,參考連結:

http://blog.itpub.net/267265/viewspace-2220688/

--//這個時候我才發現執行計劃N複雜,我才發現裡面的zdp_SCM_ORDER_DEPT實際上一個異常複雜的檢視.(明顯命名規則不合理)

> select * from dba_objects where object_name='ZDP_SCM_ORDER_DEPT';

OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S

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

xxxxxxxxxx ZDP_SCM_ORDER_DEPT 203756 VIEW 2019-04-18 17:57:42 2019-05-06 15:44:04 2019-05-06:12:47:11 VALID N N N

--//OBJECT_ID=203756.

Select a.object_id, a.object_type, a.object_name,

b.owner ref_owner, b.object_type ref_type, b.object_name ref_name, b.object_id ref_id, b.status ref_status

from sys.DBA_OBJECTS a,

sys.DBA_OBJECTS b,

(Select object_id, referenced_object_id

from (select object_id, referenced_object_id

from public_dependency

where referenced_object_id <> object_id) pd

start with object_id = 203756

connect by prior referenced_object_id = object_id) c

where a.object_id = c.object_id

and b.object_id = c.referenced_object_id

and a.owner not in ('SYS', 'SYSTEM')

and b.owner not in ('SYS', 'SYSTEM')

and a.object_name <> 'DUAL'

and b.object_name <> 'DUAL'

--//注:這條語句我寫不出來,我使用toad自帶SQL Tracker跟蹤在toad的schema browser瀏覽deps(users)結果我不貼出來了,實際上有511行.

--//實際上的情況是檢視裡面1堆檢視,在看還是一堆檢視,一直巢狀7,8層之多才是正是的表,即使最後裡面還是有1些是檢視.

--//我僅僅貼一個圖,僅僅是冰山一角.

[20190506]檢視巢狀與繫結變數.txt

--//正是這樣的複雜檢視,導致做硬分析耗費大量的cpu資源.加上前面的語句都是文字變數,存在大量的硬分析.出現'library cache lock',

--//'cursor: pin S wait on X'就不足為怪.

--//解決方法很簡單執行:

alter system set cursor_sharing=force scope=memory;

--//alter system set cursor_sharing=force scope=spfile;

--//這樣一定程度環境減少硬解析.不過這些僅僅是治標不是治本.只要分析表,簡直就是悲劇的開始...

--//我真心佩服開發,怎麼能想出這麼複雜的檢視.加上大量非繫結變數,導致大量硬解析.而且32位共享池不會很大,這樣導致許多語句反覆解析.

> show sga

Total System Global Area 1258291200 bytes

Fixed Size 1374076 bytes

Variable Size 665454724 bytes

Database Buffers 587202560 bytes

Redo Buffers 4259840 bytes

> select sum( BYTES) from v$sgastat where POOL ='shared pool';

SUM(BYTES)

----------

629169996

--//600M

--//順便說一下下午已經重啟資料庫:

> select OPEN_TIME from v$thread ;

OPEN_TIME

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

2019-05-06 14:37:41

> select sysdate from dual;

SYSDATE

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

2019-05-06 16:27:28

SELECT event, COUNT (*)

FROM V$ACTIVE_SESSION_HISTORY

WHERE sample_time BETWEEN '2019/5/6 14:00:00' AND sysdate

GROUP BY event

ORDER BY 2 DESC;

EVENT COUNT(*)

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

6030

db file sequential read 333

cursor: pin S wait on X 181

db file scattered read 131

log file sync 31

log file parallel write 13

control file parallel write 6

SQL*Net more data from client 5

direct path read 4

db file parallel write 2

SQL*Net more data to client 2

control file sequential read 2

direct path write temp 2

latch: cache buffers chains 2

log buffer space 1

library cache lock 1

sort segment request 1

os thread startup 1

direct path write 1

19 rows selected.

--//library cache lock 等待事件已經消失.

--//很明顯這個資料庫IO還有問題,cursor: pin S wait on X高也是正常的.掃描一下一大堆sql語句要最佳化.

--//不知道對方如何初始化資料庫的,一大堆全表掃描,有一些表僅僅不到1000條記錄,佔了300M(高水位問題)

--//看了這樣的專案真心無語.....................

總結:

--//建議:估計我講沒用...

1.不要把檢視定義搞得這麼複雜.改根本行不通...^_^.

2.合理的使用繫結變數.

3.不要安裝32位版本,至少選擇11.2.0.4 64位版本.

4.重新整理資料匯入資料庫,裡面一些表僅僅幾條記錄,確佔用大量磁碟空間.不知道對方如何初始化資料.

5.最佳化sql語句.

6.疑問:我很奇怪這樣的專案在別的醫院如何跑起來的,只能一種解析,良好的硬體就是一塊遮羞布,掩蓋一切應用的問題.

[20190506]檢視巢狀與繫結變數.txt

相關文章