DB Name DB Id Instance Inst Num Release OPS Host
---------- ----------- ---------- -------- ---------- ---- ----------
K2 1999167370 k2 1 8.1.5.0.0 NO k2
这是一个8.1.5的数据库系统，通过脚本增强，我们可以在8.1.5的数据库上使用statspack来进行数据库诊断。
Snap Length
Start Id End Id Start Time End Time (Minutes)
-------- -------- -------------------- -------------------- -----------
170 176 25-Feb-03 10:00:11 25-Feb-03 15:00:05 299.90
Cache Sizes
~~~~~~~~~~~
db_block_buffers: 64000
db_block_size: 8192
log_buffer: 8388608
shared_pool_size: 157286400
………………
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ----------------------- -------
db file scattered read 16,842,920 3,490,719 43.32
latch free 844,272 3,270,073 40.58
buffer busy waits 114,421 933,136 11.58
db file sequential read 2,067,910 117,750 1.46
enqueue 464 110,840 1.38
-------------------------------------------------------------
这是一个典型的性能低下的系统，几个重要的等待事件都在Top 5中出现，其中，前3个等待极为显著，需要进行
相应的调整。
在5小时的采样间隔内，其中db file scattered read累计等待时间约10小时，已经成为影响系统性能的主要原因。
了解了这些以后我们就可以进一步察看相关SQL看是否存在可以的SQL语句。
SQL ordered by Gets for DB: K2 Instance: k2 Snaps: 170 - 176
Gets % of
Buffer Gets Executes per Exec Total Hash Value
-------------- ------------ ------------ ------ ------------
SQL statement
------------------------------------------------------------------------------
6,480,163 12 540,013.6 2.4 3791855498
SELECT "PROCESS_REQ"."WORK_ID", "PROCESS_REQ"."STOCK_NO", "PROCESS_R
3,784,566 16 236,535.4 1.4 2932917818
SELECT * FROM FIND_LATER_WO ORDER BY NOTE,ORDER_NO
1,200,976 3 400,325.3 .4 4122791109
SELECT "ITEM_STOCK"."ITEM_NO", "ITEM"."NOTE", "ITEM"
923,944 9 102,660.4 .3 2200071737
SELECT "ITEM_STOCK"."ITEM_NO" , "ITEM_STOCK"."STOCK_NO" ,
921,301 3 307,100.3 .3 2218843294
SELECT "ITEM_STOCK"."ITEM_NO", "ITEM"."NOTE", "ITEM"
911,285 3 303,761.7 .3 1769130587
SELECT "LISTS"."ITEM_NO" , "LISTS"."SUB_ITEM" , "LISTS"
831,439 2 415,719.5 .3 1349577999
SELECT "GROUP_OPER"."ITEM_NO" , "GROUP_OPER"."PROCESS_ID" ,
802,918 1 802,918.0 .3 3613809507
SELECT "LISTS"."ITEM_NO" , "LISTS"."SUB_ITEM" , "ITEM".
800,548 2 400,274.0 .3 2643788247
SELECT "ITEM_STOCK"."ITEM_NO", "ITEM"."NOTE", "ITEM"
666,085 2 333,042.5 .2 3391363608
SELECT "ITEM_STOCK"."ITEM_NO", "ITEM_STOCK"."STOCK_NO",
………..
注意到以上很多查询导致的Buffer Gets都非常庞大，我们非常有理由怀疑索引存在问题，甚至缺少必要的索引。
以上记录的是SQL的片段，通过Hash Value值结合v$sql_text我们可以获得完整的SQL语句。
在这次诊断中，我紧接着去查询的是v$session_longops数据表，一个分组查询的结果如下:
TARGET COUNT(*)
---------------------------------------------------------------- ----------
SA.PPBT_GRAPHOBJTABLE 418
SA.PPBT_PPBTOBJRELATTABLE 53
我们发现这些问题SQL的全表扫描(结合v$session_longops视图中的OPNAME)主要集中在PPBT_GRAPHOBJTABLE和
PPBT_PPBTOBJRELATTABLE两张数据表上。
进一步研究发现这两个数据表上没有任何索引，并且有相当的数据量:
SQL> select count(*) from SA.PPBT_PPBTOBJRELATTABLE;
COUNT(*)
----------
1209017
SQL> select count(*) from SA.PPBT_GRAPHOBJTABLE;
COUNT(*)
----------
2445
在创建了合适的索引后，系统性能得到了大幅提高!
|
