NCC Oracle性能排查

性能调优

1. 查询db慢查询执行历史

SELECT SQL_ID AS ID, ELAPSED_TIME/1000.0/1000.0 AS "执行时间（秒）",EXECUTIONS AS "执行次数", ROWS_PROCESSED AS "数据量", 
SQL_FULLTEXT AS SQLTEXT, CPU_TIME/1000.0/1000.0 AS CPU, USER_IO_WAIT_TIME/1000.0/1000.0 AS UWT, CONCURRENCY_WAIT_TIME /1000.0/1000.0 AS CWT 
FROM v$sql
WHERE ELAPSED_TIME > 10000
ORDER by ELAPSED_TIME DESC;

1. 查询正在执行的慢查询语句

SELECT vsession.MACHINE, vsession.TERMINAL, vsession.PROGRAM, vsql.ELAPSED_TIME/1000.0/1000.0 AS "执行时间（秒）",vsql.EXECUTIONS AS "执行次数", vsql.ROWS_PROCESSED AS "数据量", 
vsql.SQL_FULLTEXT AS SQLTEXT, vsql.CPU_TIME/1000.0/1000.0 AS CPU, vsql.USER_IO_WAIT_TIME/1000.0/1000.0 AS UWT, vsql.CONCURRENCY_WAIT_TIME /1000.0/1000.0 AS CWT
FROM v$session vsession, v$sql vsql
WHERE vsession.SQL_ID = vsql.SQL_ID 
AND vsession.USERNAME = 'BTNCC'
AND vsession.STATUS='ACTIVE';

• 需优化的SQL:
  
  • 执行大于20秒

SELECT x.* FROM ( SELECT ROW_NUMBER () OVER ( ORDER BY a.sendtime DESC ) rn, a.*, sm_msg_user.isread, sm_msg_user.pk_message_user FROM sm_msg_approve a INNER JOIN sm_msg_user ON a.pk_message = sm_msg_user.pk_message INNER JOIN ( select billid, billcommiter from pub_wf_instance where WORKFLOW_TYPE in ( 2, 4 ) ) i ON i.billid = a.billid WHERE sm_msg_user.isdelete = 'N' AND sm_msg_user.destination = 'inbox' AND sm_msg_user.pk_user = '1001A11000000000L847' AND sm_msg_user.isread = 'N' AND a.pk_group = '0001A1100000000021CH' AND a.billtype = '4331-01' order by a.sendtime desc ) x WHERE rn > 0 AND rn <= 10

• 大于100秒

SELECT count ( 1 ) FROM sm_msg_approve a WHERE a.msgtype = 'nc' and           a.pk_message in ( SELECT pk_message FROM sm_msg_user WHERE sm_msg_user.isdelete = 'N' AND sm_msg_user.isread = 'N' AND sm_msg_user.destination = 'inbox' AND sm_msg_user.message_type = 'approve' AND sm_msg_user.pk_user = '1001A11000000000L851' ) and a.pk_group =     '0001A1100000000021CH'