[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;