巡檢時，執行如下sql發現長會話：

SELECT SE.SID,SE.SERIAL#,TO_CHAR(LOGON_TIME,'YYYY-MM-DD HH24:MI:SS'),SE.STATUS,SE.OSUSER,SE.MACHINE,SE.PROGRAM,SE.BLOCKING_SESSION, 
SE.SQL_ID,SE.PREV_SQL_ID ,SE.EVENT,SE.P1TEXT,SE.P1,SE.P2TEXT,SE.P2,SE.P3TEXT,SE.P3,SE.SECONDS_IN_WAIT 
FROM SYS.V_$SESSION  SE , SYS.v_$session_wait SW WHERE SE.SID=SW.SID  AND SE.STATUS='ACTIVE'   AND SE.USERNAME NOT IN ('SYS','SYSMAN','DBSNMP') AND (SE.LAST_CALL_ET/86400) > 1 AND ( TO_CHAR(SYSDATE,'YYYYMMDD') <> TO_CHAR(SE.LOGON_TIME,'YYYYMMDD') OR SYSDATE-LOGON_TIME >= 24/24 );

Output:

從輸出來看，肇事sql_id是08wx0uf3t1gt2，event是latch: cache buffers chains
這個事件主要原因：
1.熱點數據的爭用
2.低效率的sql

從如下查詢的輸出來看，這個sql有大量的邏輯讀，判斷應該是低效率的sql所致

SELECT sql_fulltext,disk_reads,buffer_gets FROM V$SQLAREA WHERE SQL_ID='08wx0uf3t1gt2'

Output:

也可查詢發生熱點塊競爭的對象

select ts#,file#,dbarfil,dbablk,obj ,tch from sys.x$bh where  hladdr in (SELECT p1raw FROM V$SESSION WHERE event='latch: cache buffers chains')
order by tch desc

Output:

可以看出是CES_FACT_INM表

select owner,object_name,object_id,object_type from dba_objects where data_object_id=51983

Output

 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('08wx0uf3t1gt2'))

Ouput:


從上面的輸出來看，盡管對CES_FACT_INM表的訪問走了索引，但從底下的謂詞來看，我們還可以針對filter部分建立一個function index:

 create index IDX_FUNC_CES_FACT_INM onCESUSER.CES_FACT_INM(DECODE(SYS_OP_C2C("DECR_TYPE"),'EX',"SUP_AJU_NO",'27',"SUP_AJU_NO","AJU_NO"));

再次查看執行計劃，發現對CES_FACT_INM表的訪問走了新建的函數索引，而且cost顯著下降

執行很快出結果，用時不到1秒

buffer_gets也顯著下降至136

 SELECT sql_fulltext,disk_reads,buffer_gets FROM V$SQLAREA WHERE SQL_ID='08wx0uf3t1gt2'

Output: