通过该过程定时收集产生gc event的语句.
grant select on dba_tables to shsnc;
grant select on v_$sql to shsnc;
grant select on v_$session to shsnc;
grant select on dba_tables to shsnc;
grant select on v_$sql to shsnc;
grant select on v_$session to shsnc;
create or replace procedure shsnc.gc_event(TBS_NAME VARCHAR2) is
T_TABLE VARCHAR2(100) := 'GC_EVENT_TAB';
v_sql01 VARCHAR2(2000);
v_sql02 VARCHAR2(2000);
v_sql03 VARCHAR2(2000);
v_sql04 VARCHAR2(2000);
v_sql05 VARCHAR2(2000);
T_COUNT NUMBER;
begin
SELECT COUNT(1) INTO T_COUNT FROM DBA_TABLES WHERE TABLE_NAME = T_TABLE;
IF T_COUNT > 0 THEN
v_sql01 := 'insert into shsnc.' || T_TABLE || ' select SYS_CONTEXT(''USERENV'', ''INSTANCE'') INSTANCE,s.sid,
s.sql_id,
s.username,
s.module,
s.machine,
s.program,
s.status,
s.state,
t.FORCE_MATCHING_SIGNATURE,
t.EXACT_MATCHING_SIGNATURE,
t.sql_fulltext
from v$session s, v$sql t
where s.event like ''gc%''
and s.wait_class = ''Cluster''
and s.sql_id=t.sql_id';
EXECUTE IMMEDIATE v_sql01;
commit;
v_sql04 := 'delete shsnc.' || T_TABLE ||
' a WHERE a.rowid !=(select max(rowid) from shsnc.' ||
T_TABLE || ' b where a.sql_id=b.sql_id)';
execute immediate v_sql04;
commit;
v_sql05 := 'delete shsnc.' || T_TABLE ||
' a WHERE a.FORCE_MATCHING_SIGNATURE !=EXACT_MATCHING_SIGNATURE';
execute immediate v_sql05;
commit;
else
v_sql02 := 'create table shsnc.' || T_TABLE ||
'(instance number,sid number,sql_id VARCHAR2(13),username VARCHAR2(30),module VARCHAR2(64),machine VARCHAR2(64),program VARCHAR2(48),status VARCHAR2(8),state VARCHAR2(19),FORCE_MATCHING_SIGNATURE number,EXACT_MATCHING_SIGNATURE number,sql_fulltext CLOB) tablespace ' ||
TBS_NAME;
execute immediate v_sql02;
v_sql03 := 'insert into shsnc.' || T_TABLE || ' select SYS_CONTEXT(''USERENV'', ''INSTANCE'') INSTANCE,s.sid,
s.sql_id,
s.username,
s.module,
s.machine,
s.program,
s.status,
s.state,
t.FORCE_MATCHING_SIGNATURE,
t.EXACT_MATCHING_SIGNATURE,
t.sql_fulltext
from v$session s, v$sql t
where s.event like ''gc%''
and s.wait_class = ''Cluster''
and s.sql_id=t.sql_id';
EXECUTE IMMEDIATE v_sql03;
COMMIT;
v_sql04 := 'delete shsnc.' || T_TABLE ||
' a WHERE a.rowid !=(select max(rowid) from shsnc.' ||
T_TABLE || ' b where a.sql_id=b.sql_id)';
execute immediate v_sql04;
commit;
v_sql05 := 'delete shsnc.' || T_TABLE ||
' a WHERE a.FORCE_MATCHING_SIGNATURE !=EXACT_MATCHING_SIGNATURE';
execute immediate v_sql05;
commit;
END IF;
END;
/
T_TABLE VARCHAR2(100) := 'GC_EVENT_TAB';
v_sql01 VARCHAR2(2000);
v_sql02 VARCHAR2(2000);
v_sql03 VARCHAR2(2000);
v_sql04 VARCHAR2(2000);
v_sql05 VARCHAR2(2000);
T_COUNT NUMBER;
begin
SELECT COUNT(1) INTO T_COUNT FROM DBA_TABLES WHERE TABLE_NAME = T_TABLE;
IF T_COUNT > 0 THEN
v_sql01 := 'insert into shsnc.' || T_TABLE || ' select SYS_CONTEXT(''USERENV'', ''INSTANCE'') INSTANCE,s.sid,
s.sql_id,
s.username,
s.module,
s.machine,
s.program,
s.status,
s.state,
t.FORCE_MATCHING_SIGNATURE,
t.EXACT_MATCHING_SIGNATURE,
t.sql_fulltext
from v$session s, v$sql t
where s.event like ''gc%''
and s.wait_class = ''Cluster''
and s.sql_id=t.sql_id';
EXECUTE IMMEDIATE v_sql01;
commit;
v_sql04 := 'delete shsnc.' || T_TABLE ||
' a WHERE a.rowid !=(select max(rowid) from shsnc.' ||
T_TABLE || ' b where a.sql_id=b.sql_id)';
execute immediate v_sql04;
commit;
v_sql05 := 'delete shsnc.' || T_TABLE ||
' a WHERE a.FORCE_MATCHING_SIGNATURE !=EXACT_MATCHING_SIGNATURE';
execute immediate v_sql05;
commit;
else
v_sql02 := 'create table shsnc.' || T_TABLE ||
'(instance number,sid number,sql_id VARCHAR2(13),username VARCHAR2(30),module VARCHAR2(64),machine VARCHAR2(64),program VARCHAR2(48),status VARCHAR2(8),state VARCHAR2(19),FORCE_MATCHING_SIGNATURE number,EXACT_MATCHING_SIGNATURE number,sql_fulltext CLOB) tablespace ' ||
TBS_NAME;
execute immediate v_sql02;
v_sql03 := 'insert into shsnc.' || T_TABLE || ' select SYS_CONTEXT(''USERENV'', ''INSTANCE'') INSTANCE,s.sid,
s.sql_id,
s.username,
s.module,
s.machine,
s.program,
s.status,
s.state,
t.FORCE_MATCHING_SIGNATURE,
t.EXACT_MATCHING_SIGNATURE,
t.sql_fulltext
from v$session s, v$sql t
where s.event like ''gc%''
and s.wait_class = ''Cluster''
and s.sql_id=t.sql_id';
EXECUTE IMMEDIATE v_sql03;
COMMIT;
v_sql04 := 'delete shsnc.' || T_TABLE ||
' a WHERE a.rowid !=(select max(rowid) from shsnc.' ||
T_TABLE || ' b where a.sql_id=b.sql_id)';
execute immediate v_sql04;
commit;
v_sql05 := 'delete shsnc.' || T_TABLE ||
' a WHERE a.FORCE_MATCHING_SIGNATURE !=EXACT_MATCHING_SIGNATURE';
execute immediate v_sql05;
commit;
END IF;
END;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29446986/viewspace-1337762/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29446986/viewspace-1337762/