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

[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系统绑定做的好的,优化相对简单许多.