新應用上線又見老問題

忙過了上一週上線的日子 終於可以喘口氣了 總結一下目前發現的應用問題.

1 還是發現了低效pl/sql

2 還要加強對系統設計人員對於函式索引的培訓...

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 770,883.85 18,746.07
Logical reads: 19,073.28 463.82
Block changes: 3,656.68 88.92
Physical reads: 698.60 16.99
Physical writes: 278.54 6.77
User calls: 1,313.17 31.93
Parses: 703.90 17.12
Hard parses: 18.46 0.45
Sorts: 48.68 1.18
Logons: 0.29 0.01
Executes: 1,469.75 35.74
Transactions: 41.12

% Blocks changed per Read: 19.17 Recursive Call %: 57.80
Rollback per transaction %: 0.00 Rows per Sort: 115.45

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.26 Redo NoWait %: 100.00
Buffer Hit %: 96.36 In-memory Sort %: 100.00
Library Hit %: 99.23 Soft Parse %: 97.38
Execute to Parse %: 52.11 Latch Hit %: 99.58
Parse CPU to Parse Elapsd %: 10.57 % Non-Parse CPU: 98.97

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 92.75 92.78
% SQL with executions>1: 1.34 0.84
% Memory for SQL w/exec>1: 1.71 1.31

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read 1,145,585 9,031 60.85
buffer busy waits 171,815 1,328 8.95
CPU time 1,323 8.92
global cache null to x 171,035 758 5.11
buffer busy global cache 22,043 504 3.39
-------------------------------------------------------------

[@more@]

DB Tuning Scripts

select t.sql_text
from v$sqltext t
where t.hash_value = (select s.sql_hash_value
from v$session s,
v$process p
where s.paddr=p.addr
and p.spid=&sp_id)
order by t.piece
/

set linesize 120
col program for a40
col event for a40 trunc
select s.sid,s.program,v.event from v$session s,v$session_wait v
where s.sid=v.sid
and v.event not like '%SQL%'
and v.event not like '%message%'
/

col index_name for a40
col column_name for a40
col index_owner for a10
set linesize 120
select index_owner,column_name,index_name from dba_ind_columns
where table_name ='&tb_name'
/

select s.sql_text,
s.open_versions,
s.users_opening,
u.username
from v$sql s,dba_users u
where s.hash_value=&hash_value
and s.PARSING_USER_ID = u.user_id
/

Top sqls

Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
5,554,189 408 13,613.2 45.2 586.95 1370.55 1336051368

最佳化建議:
PKEY_ID,ENTITY_NAME列建立複合索引
create index cfg_history_union_indx01 on CFG_ATTR_MODIFY_HISTORY(PKEY_ID,ENTITY_NAME) tablespace &tbs_name;
Module: (TNS V1-V3)
SELECT * FROM CFG_ATTR_MODIFY_HISTORY WHERE ENTITY_NAME = :B2 AN
D PKEY_ID = :B1

/*
select /*+ INDEX(a PK_CFG_ATTR_MODIFY_HISTORY)*/ * from CFG_ATTR_MODIFY_HISTORY a WHERE ENTITY_NAME ='TB1010' and pkey_id='10100000000000000469'

select * from CFG_ATTR_MODIFY_HISTORY a WHERE ENTITY_NAME ='TB1010' and pkey_id='10100000000000000469'
*/

4,439,293 375 11,838.1 36.1 448.42 1061.30 3601089869
Module: (TNS V1-V3)

檢查儲存過程TB1010_PKG.modify
BEGIN TB1010_PKG.modify( :v1 , :v2 , :v3 , :v4 , :v5 , :v6 , :v
7 , :v8 , :v9 , :v10 , :v11 , :v12 , :v13 , :v14 , :v15 , :v16 ,
:v17 , :v18 , :v19 , :v20 , :v21 , :v22 , :v23 , :v24 , :v25 ,
:v26 , :v27 , :v28 , :v29 , :v30 , :v31 , :v32 , :v33 , :v34 , :
v35 , :v36 , :v37 , :v38 , :v39 , :v40 , :v41 , :v42 , :v43 , :v

1,396,517 8 174,564.6 11.4 210.26 486.19 993868629
檢查儲存過程TB2324_REF_PKG
Module: (TNS V1-V3)
BEGIN TB2324_REF_PKG.del( :v1 , :v2 ,:v3 ) ; END ;

1,208,025 6 201,337.5 9.8 158.99 428.21 3686763671
Module: (TNS V1-V3)
TRIM (CHNL_CUST_COD)列建立基於函式的索引 或者 取掉TRIM (CHNL_CUST_COD)函式(需要確定業務邏輯)

create function based index fbi_CHNL_CUST_idx on TB2303(TRIM (CHNL_CUST_COD)) tablespace &tbs;
DELETE FROM TB2303 WHERE CHNL_NO = TRIM (:B2 ) AND TRIM (CHNL_CUST_COD) = TRIM ('' || :B1 || '')

1,149,681 34 33,814.1 9.4 141.39 317.23 3445670716
Module: (TNS V1-V3)
檢查儲存過程TB1010_PKG
BEGIN TB1010_PKG.Modify( :v1 , :v2 , :v3 , :v4 , :v5 , :v6 , :v
7 , :v8 , :v9 , :v10 , :v11 , :v12 , :v13 , :v14 , :v15 , :v16 ,
:v17 , :v18 , :v19 , :v20 , :v21 , :v22 , :v23 , :v24 , :v25 ,
:v26 , :v27 , :v28 , :v29 , :v30 , :v31 , :v32 , :v33 , :v34 , :
v35 , :v36 , :v37 , :v38 , :v39 , :v40 , :v41 , :v42 , :v43 , :v

1,396,517 8 174,564.6 11.4 210.26 486.19 993868629
檢查儲存過程TB2324_REF_PKG
Module: (TNS V1-V3)
BEGIN TB2324_REF_PKG.del( :v1 , :v2 ,:v3 ) ; END ;

避免to_char(update_ts,'yyyy-mm-dd')='xxxxxxx'這種查詢 會使得相應索引失效