查詢資料庫裡執行大於2s的語句
使用該過程可以定期查詢出資料庫裡執行大於2s的語句
CREATE OR REPLACE procedure SYS.versiononline_larges2s_sql is
version_table varchar2(50);
TEMPCOUNT NUMBER;
T_TABLE VARCHAR2(100) := 'LARGE2S_TABLE02';
v_sql01 VARCHAR2(2000);
v_sql02 VARCHAR2(2000);
v_sql03 VARCHAR2(2000);
BEGIN
select COUNT(*)
into TEMPCOUNT
from dba_tables
where table_name = T_TABLE;
If TEMPCOUNT > 0 Then
v_sql01 := ' insert into ' || T_TABLE ||
' select *
from (SELECT A.SQL_ID,
A.sql_fulltext,
A.FIRST_LOAD_TIME,
A.ELAPSED_TIME,
A.ELAPSED_TIME / decode(A.EXECUTIONS, 0, 1, A.EXECUTIONS) EE,
A.EXECUTIONS,
B.MACHINE,
B.PROGRAM,
A.LAST_ACTIVE_TIME,
A.FORCE_MATCHING_SIGNATURE,
A.EXACT_MATCHING_SIGNATURE
FROM V$SQL A, V$ACTIVE_SESSION_HISTORY B
WHERE A.SQL_ID = B.SQL_ID
and B.machine in (''c4w28'',''tcsm1'')
and a.first_load_time between
TO_DATE(''20141105 09 :00 :00'',
''YYYYMMDD HH24 :MI :SS'') AND
TO_DATE(''20141115 17 :59 :00'',
''YYYYMMDD HH24 :MI :SS'')
AND A.LAST_ACTIVE_TIME BETWEEN
TO_DATE(''20141105 09 :00 :00'',
''YYYYMMDD HH24 :MI :SS'') AND
TO_DATE(''20141115 17 :59 :00'',
''YYYYMMDD HH24 :MI :SS'')
AND B.PROGRAM NOT LIKE ''sqlplus%''
and B.PROGRAM NOT LIKE ''plsqlde%''
AND B.PROGRAM NOT LIKE ''oracle@%''
and A.SQL_ID NOT IN (SELECT SQL_ID
FROM ' ||
T_TABLE || ')
order by EE DESC)
WHERE EE > 2000000 ';
EXECUTE IMMEDIATE v_sql01;
dbms_output.put_line(v_sql01);
COMMIT;
v_sql02:='delete sys.'||T_TABLE||' a where rowid v_sql03:='delete sys.'||T_TABLE||' a where FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE';
EXECUTE IMMEDIATE v_sql02;
commit;
EXECUTE IMMEDIATE v_sql03;
COMMIT;
else
execute immediate 'create table sys.'||T_TABLE||' (sql_id VARCHAR2(13),sql_fulltext CLOB,first_load_time VARCHAR2(38),elapsed_time number,
ee number,executions number,machine VARCHAR2(64),program VARCHAR2(48),last_active_time DATE,FORCE_MATCHING_SIGNATURE number,EXACT_MATCHING_SIGNATURE number) tablespace users';
v_sql01 := ' insert into ' || T_TABLE ||
' select *
from (SELECT A.SQL_ID,
A.sql_fulltext,
A.FIRST_LOAD_TIME,
A.ELAPSED_TIME,
A.ELAPSED_TIME / decode(A.EXECUTIONS, 0, 1, A.EXECUTIONS) EE,
A.EXECUTIONS,
B.MACHINE,
B.PROGRAM,
A.LAST_ACTIVE_TIME,
A.FORCE_MATCHING_SIGNATURE,
A.EXACT_MATCHING_SIGNATURE
FROM V$SQL A, V$ACTIVE_SESSION_HISTORY B
WHERE A.SQL_ID = B.SQL_ID
and B.machine in (''c4w28'',''tcsm1'')
AND A.FIRST_LOAD_TIME BETWEEN
TO_DATE(''20141105 09 :00 :00'',
''YYYYMMDD HH24 :MI :SS'') AND
TO_DATE(''20141115 17 :59 :00'',
''YYYYMMDD HH24 :MI :SS'')
AND A.LAST_ACTIVE_TIME BETWEEN
TO_DATE(''20141105 09:00:00'',
''YYYYMMDD HH24:MI:SS'') AND
TO_DATE(''20141115 17 :59 :00'',
''YYYYMMDD HH24 :MI :SS'')
AND B.PROGRAM NOT LIKE ''sqlplus%''
and B.PROGRAM NOT LIKE ''plsqlde%''
AND B.PROGRAM NOT LIKE ''oracle@%'' and A.SQL_ID NOT IN (SELECT SQL_ID
FROM ' ||
T_TABLE || ')
order by EE DESC)
WHERE EE > 2000000';
EXECUTE IMMEDIATE v_sql01;
COMMIT;
v_sql02:='delete sys.'||T_TABLE||' a where rowid v_sql03:='delete sys.'||T_TABLE||' a where FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE';
EXECUTE IMMEDIATE v_sql02;
commit;
EXECUTE IMMEDIATE v_sql03;
COMMIT;
end if;
end versiononline_larges2s_sql;
/
CREATE OR REPLACE procedure SYS.versiononline_larges2s_sql is
version_table varchar2(50);
TEMPCOUNT NUMBER;
T_TABLE VARCHAR2(100) := 'LARGE2S_TABLE02';
v_sql01 VARCHAR2(2000);
v_sql02 VARCHAR2(2000);
v_sql03 VARCHAR2(2000);
BEGIN
select COUNT(*)
into TEMPCOUNT
from dba_tables
where table_name = T_TABLE;
If TEMPCOUNT > 0 Then
v_sql01 := ' insert into ' || T_TABLE ||
' select *
from (SELECT A.SQL_ID,
A.sql_fulltext,
A.FIRST_LOAD_TIME,
A.ELAPSED_TIME,
A.ELAPSED_TIME / decode(A.EXECUTIONS, 0, 1, A.EXECUTIONS) EE,
A.EXECUTIONS,
B.MACHINE,
B.PROGRAM,
A.LAST_ACTIVE_TIME,
A.FORCE_MATCHING_SIGNATURE,
A.EXACT_MATCHING_SIGNATURE
FROM V$SQL A, V$ACTIVE_SESSION_HISTORY B
WHERE A.SQL_ID = B.SQL_ID
and B.machine in (''c4w28'',''tcsm1'')
and a.first_load_time between
TO_DATE(''20141105 09 :00 :00'',
''YYYYMMDD HH24 :MI :SS'') AND
TO_DATE(''20141115 17 :59 :00'',
''YYYYMMDD HH24 :MI :SS'')
AND A.LAST_ACTIVE_TIME BETWEEN
TO_DATE(''20141105 09 :00 :00'',
''YYYYMMDD HH24 :MI :SS'') AND
TO_DATE(''20141115 17 :59 :00'',
''YYYYMMDD HH24 :MI :SS'')
AND B.PROGRAM NOT LIKE ''sqlplus%''
and B.PROGRAM NOT LIKE ''plsqlde%''
AND B.PROGRAM NOT LIKE ''oracle@%''
and A.SQL_ID NOT IN (SELECT SQL_ID
FROM ' ||
T_TABLE || ')
order by EE DESC)
WHERE EE > 2000000 ';
EXECUTE IMMEDIATE v_sql01;
dbms_output.put_line(v_sql01);
COMMIT;
v_sql02:='delete sys.'||T_TABLE||' a where rowid v_sql03:='delete sys.'||T_TABLE||' a where FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE';
EXECUTE IMMEDIATE v_sql02;
commit;
EXECUTE IMMEDIATE v_sql03;
COMMIT;
else
execute immediate 'create table sys.'||T_TABLE||' (sql_id VARCHAR2(13),sql_fulltext CLOB,first_load_time VARCHAR2(38),elapsed_time number,
ee number,executions number,machine VARCHAR2(64),program VARCHAR2(48),last_active_time DATE,FORCE_MATCHING_SIGNATURE number,EXACT_MATCHING_SIGNATURE number) tablespace users';
v_sql01 := ' insert into ' || T_TABLE ||
' select *
from (SELECT A.SQL_ID,
A.sql_fulltext,
A.FIRST_LOAD_TIME,
A.ELAPSED_TIME,
A.ELAPSED_TIME / decode(A.EXECUTIONS, 0, 1, A.EXECUTIONS) EE,
A.EXECUTIONS,
B.MACHINE,
B.PROGRAM,
A.LAST_ACTIVE_TIME,
A.FORCE_MATCHING_SIGNATURE,
A.EXACT_MATCHING_SIGNATURE
FROM V$SQL A, V$ACTIVE_SESSION_HISTORY B
WHERE A.SQL_ID = B.SQL_ID
and B.machine in (''c4w28'',''tcsm1'')
AND A.FIRST_LOAD_TIME BETWEEN
TO_DATE(''20141105 09 :00 :00'',
''YYYYMMDD HH24 :MI :SS'') AND
TO_DATE(''20141115 17 :59 :00'',
''YYYYMMDD HH24 :MI :SS'')
AND A.LAST_ACTIVE_TIME BETWEEN
TO_DATE(''20141105 09:00:00'',
''YYYYMMDD HH24:MI:SS'') AND
TO_DATE(''20141115 17 :59 :00'',
''YYYYMMDD HH24 :MI :SS'')
AND B.PROGRAM NOT LIKE ''sqlplus%''
and B.PROGRAM NOT LIKE ''plsqlde%''
AND B.PROGRAM NOT LIKE ''oracle@%'' and A.SQL_ID NOT IN (SELECT SQL_ID
FROM ' ||
T_TABLE || ')
order by EE DESC)
WHERE EE > 2000000';
EXECUTE IMMEDIATE v_sql01;
COMMIT;
v_sql02:='delete sys.'||T_TABLE||' a where rowid v_sql03:='delete sys.'||T_TABLE||' a where FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE';
EXECUTE IMMEDIATE v_sql02;
commit;
EXECUTE IMMEDIATE v_sql03;
COMMIT;
end if;
end versiononline_larges2s_sql;
/
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29446986/viewspace-1331273/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 小覷資料庫(SqlServer)查詢語句執行過程資料庫SQLServer
- 資料庫查詢語句資料庫
- MySQL資料庫詳解(一)SQL查詢語句是如何執行的?MySql資料庫
- ORACLE 資料庫 查詢語句與DML語句Oracle資料庫
- 資料庫查詢優化:使用explain分析sql語句執行效率資料庫優化AISQL
- mybatis執行查詢語句查詢不到結果,控制檯輸出sql語句複製到資料庫中能查到資料MyBatisSQL資料庫
- 查詢執行慢的SQL語句SQL
- 查詢正在執行的SQL語句SQL
- 查詢資料庫中的topsql語句資料庫SQL
- 查詢正在執行的sql語句及該語句執行的時間SQL
- 一條查詢語句的執行流程
- Laravel 框架查詢執行的 SQL 語句Laravel框架SQL
- 查詢Oracle正在執行的SQL語句OracleSQL
- 查詢sql語句執行次數SQL
- DQL(Date Query Language)資料庫查詢語句資料庫
- MySQL資料庫:7、SQL常用查詢語句MySql資料庫
- 查詢Oracle正在執行的sql語句及執行該語句的使用者OracleSQL
- 查詢Oracle正在執行和執行過的SQL語句OracleSQL
- SQL 查詢語句的執行順序解析SQL
- 查詢orcale執行的SQL語句記錄SQL
- MySQL 查詢常用操作(0) —— 查詢語句的執行順序MySql
- 關於Room資料庫,拼寫模糊查詢語句遇到的問題OOM資料庫
- YCSB擴充套件-語句執行頻率,執行指定的測試查詢語句套件
- 資料庫設計與查詢語句的優化資料庫優化
- 查詢資料庫隱含引數的sql語句資料庫SQL
- SQL server資料庫表碎片比例查詢語句SQLServer資料庫
- 一條 SQL 查詢語句是如何執行的?SQL
- SQL Server 查詢歷史執行的SQL語句SQLServer
- MySQL關於根據日期查詢資料的sql語句MySql
- 找到Oracle資料庫中效能最差的查詢語句BSOracle資料庫
- MySQL 查詢語句執行過程淺析MySql
- T-sql語句查詢執行順序SQL
- GaussDB SQL查詢語句執行過程解析SQL
- 輸入的查詢 SQL 語句,是如何執行的?SQL
- Mysql 資料庫 -------- SQL語句進階查詢 ------- 前部分MySql資料庫
- Oracle資料庫SQL語句執行過程Oracle資料庫SQL
- 查詢當前正在執行的SQL語句並KILLSQL
- oracle查詢語句執行計劃中的表消除Oracle