[20240327]建立完善mfms.sql脚本.txt

--//最近一直在通过FORCE_MATCHING_SIGNATURE定位有问题的SQL语句,这些语句大量没有使用绑定变量,导致不会出现AWR报表里面.
--//建立一个脚本收集共享池的sql语句force_matching_signature相同,sql_id不同的情况.

$ cat mfms.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- File name:   mfms.sql
-- Purpose:     Display sql statement similar to the force_matching_signature identical from gv$sqlarea
--              
-- Author:      lfree
--              
-- Usage:       
--     @ mfms <sql_id_count> <display_record_number>
--
--------------------------------------------------------------------------------
prompt
prompt time unit : microsecond
prompt

SELECT *
  FROM (  SELECT force_matching_signature
                ,COUNT (*)
                ,ROUND (SUM (ELAPSED_TIME), 0) total_time
                ,SUM (EXECUTIONS) sum_exec
                ,ROUND (SUM (ELAPSED_TIME) / NULLIF (SUM (executions), 0), 0) avg_exec_time
                ,ROUND (SUM (ROWS_PROCESSED) / NULLIF (SUM (FETCHES), 0), 2) rows_per_fetch
                ,ROUND (SUM (BUFFER_GETS) / NULLIF (SUM (EXECUTIONS), 0), 2) lios_per_exec
            FROM gv$sqlarea
           WHERE force_matching_signature <> 0
        GROUP BY force_matching_signature
          HAVING COUNT (*) >= &&1
        ORDER BY 2 DESC )
 WHERE ROWNUM <= &&2;

--//测试看看.
--//注:后面的执行时间以及执行次数以及平均执行时间等信息仅仅作为参考.

SYS@192.168.100.235:1521/orcl> @ mfms 5 15
time unit : microsecond

FORCE_MATCHING_SIGNATURE   COUNT(*) TOTAL_TIME   SUM_EXEC AVG_EXEC_TIME LIOS_PER_EXEC
------------------------ ---------- ---------- ---------- ------------- -------------
     4073084857451735172        212    1155039      10228           113             5
     3620518798007503387        118     328145        134          2449            13
     9685323836218277500         50     488949        195          2507             8
     7756258419218828704         39      52610        118           446             2
    10241493000169525925         14      29457        178           165             0
--//注:mfms.sql脚本做了小量修改,增加使用nullif函数以及rows_per_fetch信息.

SYS@192.168.100.235:1521/orcl> select sql_text c100  from gv$sqlarea where FORCE_MATCHING_SIGNATURE=4073084857451735172 and rownum<=5;
C100
----------------------------------------------------------------------------------------------------
SELECT AUDIT_TIME FROM lis_test WHERE barcode = '240325552982'
SELECT AUDIT_TIME FROM lis_test WHERE barcode = '240325546519'
SELECT AUDIT_TIME FROM lis_test WHERE barcode = '240325552983'
SELECT AUDIT_TIME FROM lis_test WHERE barcode = '240309374330'
SELECT AUDIT_TIME FROM lis_test WHERE barcode = '240308362096'

--//没有使用绑定变量语句实际上就好像一个成语,一颗老鼠屎搞坏一锅汤.