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

--//查找FORCE_MATCHING_SIGNATURE的sql语句.

$ cat ffms.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- File name:   ffms.sql
-- Purpose:     find  force_matching_signature from gv$sql
--
-- Author:      lfree
--
-- Usage:
--     @ ffms FORCE_MATCHING_SIGNATURE <display_record_number>
--
--------------------------------------------------------------------------------

SELECT REPLACE (sql_fulltext, CHR (13), '') c200
  FROM gv$sqlarea
 WHERE FORCE_MATCHING_SIGNATURE = &&1 AND ROWNUM = 1;

prompt
prompt time unit : microsecond
prompt

SELECT *
  FROM (  SELECT sql_id
                ,child_number
                ,inst_id
                ,first_load_time
                ,buffer_gets
                ,executions
                ,elapsed_time
                ,ROUND (elapsed_time / nullif(executions,0),2) avg_ela_time
                ,plan_hash_value
                ,ROUND (buffer_gets / nullif(executions,0),2) lios_per_exec
            FROM gv$sql
           WHERE FORCE_MATCHING_SIGNATURE = &&1
        ORDER BY 4 DESC)
 WHERE ROWNUM <= &&2;

--//测试:
SYS@192.168.100.235:1521/orcl> @ ffms 9685323836218277500 10
C200
--------------------------------------------------------------------------------------
select  a.id,
a.test_no,
a.qc_test_id,
a.test_inst_id,
a.qc_inst_id,
a.qc_sample_id,
a.tenant_id from qc_tran_mapping a,qc_sample b WHERE b.id=a.qc_sample_id
and (a.TEST_INST_ID=1545 or a.TEST_INST_ID=1816) and
( (a.test_no=0 and a.test_no>0) or ( b.batch_no='BIO1 AU2' or a.QC_TEST_ID='BIO1 AU2'))

time unit : microsecond
SQL_ID        CHILD_NUMBER    INST_ID FIRST_LOAD_TIME     BUFFER_GETS EXECUTIONS ELAPSED_TIME PLAN_HASH_VALUE LIOS_PER_EXEC
------------- ------------ ---------- ------------------- ----------- ---------- ------------ --------------- -------------
78apm5qup5mn1            0          1 2024-03-29/10:21:02          10          1         8340       500258294            10
8gdr0adm7w5hb            0          1 2024-03-29/10:20:31          10          1         7766       500258294            10
7usr23k33zmzk            0          1 2024-03-29/10:20:00          10          1        10926       500258294            10
8yqp3f99kgtr1            0          1 2024-03-29/10:19:07          13          1         9824       500258294            13
1xdqq4vy1wyq6            0          1 2024-03-29/10:18:22          10          1         7669       500258294            10
dywp0bt6vgty3            0          1 2024-03-29/10:15:37          17          2         9156       500258294             9
5zhbut3pvbhw1            0          1 2024-03-29/10:13:54          27          3         8353       500258294             9
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
852n7zj0u9vvk            0          1 2024-03-29/09:24:32          17          2         9238       500258294             9
3d0jrhqn7yjw5            0          1 2024-03-29/09:20:55          17          2         8861       500258294             9
g1f910vn117ad            0          1 2024-03-29/09:20:14          23          2         7740       500258294            12
10 rows selected.

--//看看没有使用绑定变量带来的问题,第一次执行基本消耗8XXX微秒,而且这条语句的逻辑读非常小,执行多次时间基本还是在这个量级.
--//看看下划线那行,执行3次,ELAPSED_TIME=8353,可以想象如果大量的sql语句没有使用绑定变量,大量的时间浪费在硬分析sql语句上.
--//聚集起来对服务器性能CPU的影响.

--//不过现在的服务器动不动都是32,64个CPU,许多开发还是不懂并且不重视这个问题,不过最近几年情况要比以前好许多,如果接触优化
--//项目,你可以发现OLTP系统绑定做的好的,优化相对简单许多.