优化Oracle数据库性能：实战脚本与SQL分析

插入临时表，用于分析问题语句SQL
INSERT INTO FM3EXG.t_sql_test2
SELECT '2015123105' AS batchno ,sysdate cotime,t.sql_text,t. inst_id,t.hash_value ,t.child_number,t. elapsed_time,t.executions FROM gv$sql t WHERE T. PARSING_SCHEMA_NAME= 'FM3EXG';
根据插入临时表的sql，分析语句
SELECT T1. EXECUTIONS EXEC1,T2.EXECUTIONS EXEC2 ,T1. ELAPSED_TIME TIME1 ,T2. ELAPSED_TIME TIME2,T2. EXECUTIONS-T1.EXECUTIONS EXECS ,T2. ELAPSED_TIME-T1. ELAPSED_TIME TIMES ,
  trunc((T2 .ELAPSED_TIME- T1.ELAPSED_TIME )/(T2. EXECUTIONS-T1.EXECUTIONS )/1000, 2) av,trunc(T1. ELAPSED_TIME/T1.EXECUTIONS /1000, 2) av1,trunc(T2 . ELAPSED_TIME/ T2. EXECUTIONS/1000 ,2 ) av2, T1. SQL_TEXT
     FROM (SELECT * FROM fm3exg. t_sql_test2 t1 WHERE T1 .BATCHNO =: BATCH1) T1 LEFT JOIN
(SELECT * FROM fm3exg.t_sql_test2 t2 WHERE T2 . BATCHNO=: BATCH2 ) T2
ON  T1. HASH_VALUE=T2.HASH_VALUE AND t1. INST_ID=T2.INST_ID AND T1. CHILD_NUMBER= T2.CHILD_NUMBER
WHERE T2. EXECUTIONS-T1.EXECUTIONS >0 --and t1.sql_text like 'insert into se_sess%'
AND T2. EXECUTIONS-T1.EXECUTIONS >500
ORDER BY av DESC;
查找插入临时表中的sql（某个时间点），执行次数1000以上，按照耗时排序
SELECT child_number, hash_value,executions ,elapsed_time,(CASE WHEN executions =0 THEN 0 ELSE trunc(elapsed_time/ executions/1000 ,2) END) av ,sql_text FROM fm3exg.t_sql_test2
WHERE batchno=: batch2 AND executions >1000 ORDER BY av DESC;
在临时表中，查找某个sql文本的sql
SELECT (CASE WHEN executions =0 THEN 0 ELSE trunc(elapsed_time /executions/ 1000,2 ) END) av,t.* FROM fm3exg.t_sql_test2 t WHERE sql_text LIKE '%update se_sessioninfoes set agent=:1, category=:2, expired_at=:3, fullname=:4, ip=:5, last_access_at=:6, login_at=:7, os=:8, remark=:9, server_name=:10, username=:11 where id=:12%'  ORDER BY child_number, batchno DESC,executions DESC;
 
SELECT  (CASE WHEN executions =0 THEN 0 ELSE trunc(elapsed_time /executions/ 1000,2 ) END) av,t.* FROM gv$sql t WHERE sql_text LIKE '%update se_sessioninfoes set agent=:1, category=:2, expired_at=:3, fullname=:4, ip=:5, last_access_at=:6, login_at=:7, os=:8, remark=:9, server_name=:10, username=:11 where id=:12%'  ORDER BY child_number,executions DESC;
 
SELECT executions, elapsed_time,(CASE WHEN executions= 0 THEN 0 ELSE trunc(elapsed_time/ executions/1000 ,2) END) av ,sql_text FROM fm3exg.t_sql_test2
WHERE batchno=: batch2 ORDER BY av DESC;
 
INSERT INTO fm3exg.se_sessioninfoes (agent, category, expired_at , fullname, ip, last_access_at , login_at, os, remark, server_name , username, id) VALUES (:1, :2 , : 3, :4 , : 5, :6 , : 7, :8 , : 9, :10 , : 11, :12);
查看child_number出现的原因：针对某个sqlid
SELECT s. child_number, m.position , m. max_length,
decode(m.datatype, 1,'VARCHAR2' ,2, 'NUMBER',m.datatype ) AS datatype
FROM gv$sql s, gv$sql_bind_metadata m
WHERE s. sql_id = 'fsrxwbpsfrfaz'--'c7ztc2wsrpf60'-- 'fsrxwbpsfrfaz'
AND s. child_address = m.address