[20240309]建立完善fms.sql与dfms.sql脚本.txt

[20240309]建立完善fms.sql与dfms.sql脚本.txt --//自己写一个脚本先收集force_matching_signature相同,sql_id不同的信息. $ cat fms.sql -- Copyright 2023 lfree. All rights reserved. -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions. -------------------------------------------------------------------------------- -- -- File name:  fms.sql  v1.0 -- Purpose:    Query gv$active_session_history Force_Matching_Signature the same, sql_id different of information -- Author:     lfree -- -- Usage: --    @ fms <count(*)_number> <display_record_number> <fromtime> <totime> --   <count(*)_number>=2> -- -- Example: --    @ fms 5 30 &day -- -- Other: --     This script uses only the in-memory GV$ACTIVE_SESSION_HISTORY, use --     @dfms.sql for accessiong the DBA_HIST_ACTIVE_SESS_HISTORY archive -- -------------------------------------------------------------------------------- WITH a1 AS (SELECT sql_id,force_matching_signature, count(*) cnt1   FROM gv$active_session_history  WHERE force_matching_signature <> 0 AND sample_time BETWEEN &3 AND &4  GROUP BY sql_id, force_matching_signature ) ,     a2 AS (SELECT force_matching_signature, count(*) sql_id_count,sum(cnt1) total_seconds   FROM a1  GROUP BY force_matching_signature HAVING count(*) >=  &&1  ORDER BY 3 desc) SELECT force_matching_signature, sql_id_count,total_seconds   FROM a2  WHERE rownum <= &2; $ cat dfms.sql -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions. -------------------------------------------------------------------------------- -- -- File name:  dfms.sql  v1.0 -- Purpose:    Query dba_hist_active_sess_history Force_Matching_Signature the same, sql_id different of information -- Author:     lfree -- -- Usage: --    @ idfms <count(*)_number> <display_record_number> <fromtime> <totime> --   <count(*)__number>=2> -- -- Example: --    @ fms 5 30 &day -- -- Other: --     This script uses only the DBA_HIST_ACTIVE_SESS_HISTORY --     @ fms.sql for accessiong the in-memory GV$ACTIVE_SESSION_HISTORY -- -------------------------------------------------------------------------------- WITH a1 AS (SELECT sql_id,force_matching_signature, count(*) cnt1   FROM dba_hist_active_sess_history  WHERE force_matching_signature <> 0 AND sample_time BETWEEN &3 AND &4 and dbid = (SELECT d.dbid FROM v$database d)  GROUP BY sql_id,force_matching_signature ) ,     a2 AS (SELECT force_matching_signature, count(*) sql_id_count,sum(cnt1)*10 total_seconds   FROM a1  GROUP BY force_matching_signature HAVING count(*) >= &&1  ORDER BY 3 desc) SELECT force_matching_signature, sql_id_count,total_seconds   FROM a2  WHERE rownum <= &2;