如何查询未使用绑定变量SQL的历史执行情况

kiwixing

于 2024-10-10 15:00:00 发布

阅读量177

点赞数 8

分类专栏： SQL优化文章标签： sql oracle 运维 database

本文链接：https://blog.csdn.net/Story_begins/article/details/142795577

版权

SQL优化专栏收录该内容

5 篇文章 0 订阅

订阅专栏

例如某一个批处理，每天执行SQL文本的变量不一样，在数据库中对应不同的sql_id，这种情况

如何查询未使用绑定变量SQL的历史执行情况？

select * from tab where date='20241007'

select * from tab where date='20241008'

select * from tab where date='20241009'

1.gv$active_session_history查询未使用绑定变量SQL的历史执行情况。

alter session set nls_date_format='yyyymmdd hh24:mi:ss';
set line 200 pages 1000
col program for a30
col exec_time for a30
select sql_id,sql_exec_start,sql_exec_id,program,sql_plan_hash_value,max(sample_time)-sql_exec_start  exec_time
from gv$active_session_history
where
 to_char(FORCE_MATCHING_SIGNATURE)='569135988439770609' 
group by sql_id,sql_exec_start,sql_exec_id,program,sql_plan_hash_value
order by exec_time;

2.dba_hist_sqlstat代入FORCE_MATCHING_SIGNATURE值，查询未使用绑定变量SQL的历史资源消耗情况。

set lines 200 pages 1000
col shijian for a12
col inst_id for 99
col execu_d for 999999999999
col bg_d for 9999999999
col dr_d for 9999999999
col et_d for 99999999
col ct_d for 99999999
col io_time for 999999
col clus_time for 999999
col ap_time for 999999
col cc_time for 999999
col et_onetime for 999999
select 
    to_char(b.END_INTERVAL_TIME,'yyyymmdd hh24') shijian,
    a.instance_number inst_id,
    a.plan_hash_value,
    sum(a.EXECUTIONS_DELTA) execu_d,
    sum(a.BUFFER_GETS_DELTA ) bg_d,
    sum(a.DISK_READS_DELTA ) dr_d,
    sum(a.ELAPSED_TIME_DELTA/1000000) et_d,
    sum(a.CPU_TIME_DELTA/1000000)  ct_d,
    sum(IOWAIT_DELTA/1000000) io_time,
    sum(CLWAIT_DELTA/1000000) clus_time,
    sum(APWAIT_DELTA/1000000) ap_time,
    sum(ccwait_delta/1000000) cc_time,
    decode(sum(a.EXECUTIONS_DELTA),0,sum(a.ELAPSED_TIME_DELTA/1000000),
    sum(a.ELAPSED_TIME_DELTA/1000000)/sum(a.EXECUTIONS_DELTA)) et_onetime,
    decode(sum(a.EXECUTIONS_DELTA),0,sum(a.rows_processed_DELTA),sum(a.rows_processed_DELTA)/sum(a.EXECUTIONS_DELTA)) rw_onetime
from     
    dba_hist_sqlstat a,dba_hist_snapshot b
where 
    a.SNAP_ID =b.SNAP_ID
and a.INSTANCE_NUMBER=b.INSTANCE_NUMBER
and to_char(force_matching_signature)='569135988439770609' 
group by 
    to_char(b.END_INTERVAL_TIME,'yyyymmdd hh24'),a.plan_hash_value,a.instance_number
 order by 1;

模拟测试过程

1.测试未使用绑定变量的SQL，并执行SQL语句

select count(*) from dba_objects,dba_users,dba_users where object_id > 123;
select count(*) from dba_objects,dba_users,dba_users where object_id > 456;
select count(*) from dba_objects,dba_users,dba_users where object_id > 789;

2.创建awr快照点

exec dbms_workload_repository.create_snapshot();

3.根据sql文本查询sql信息FORCE_MATCHING_SIGNATURE

select sql_id,sql_text,to_char(force_matching_signature) from v$sql where sql_text like '%object_id > 789%';

SQL_ID        SQL_TEXT                                                                         TO_CHAR(FORCE_MATCHING_SIGNATURE)
------------- -------------------------------------------------------------------------------- ----------------------------------------
g5509f6617bth select count(*) from dba_objects,dba_users,dba_users where object_id > 789       569135988439770609

4.查询sql历史执行情况

alter session set nls_date_format='yyyymmdd hh24:mi:ss';
set line 200 pages 1000
col program for a30
col exec_time for a30
select sql_id,sql_exec_start,sql_exec_id,program,sql_plan_hash_value,max(sample_time)-sql_exec_start  exec_time
from gv$active_session_history
where
to_char(FORCE_MATCHING_SIGNATURE)='569135988439770609'
group by sql_id,sql_exec_start,sql_exec_id,program,sql_plan_hash_value
order by exec_time;

SQL_ID        SQL_EXEC_START    SQL_EXEC_ID PROGRAM                        SQL_PLAN_HASH_VALUE EXEC_TIME
------------- ----------------- ----------- ------------------------------ ------------------- ------------------------------
b4nm0d4a74wb3 20241009 14:25:47    16777216 sqlplus@xingkw (TNS V1-V3)              4050914861 +000000000 00:00:09.523
g5509f6617bth 20241009 14:26:26    16777216 sqlplus@xingkw (TNS V1-V3)              4050914861 +000000000 00:00:10.579
9kfpkkusk5tdx 20241009 14:26:10    16777216 sqlplus@xingkw (TNS V1-V3)              4050914861 +000000000 00:00:11.523
9kfpkkusk5tdx                               sqlplus@xingkw (TNS V1-V3)              4050914861

SYS@db11g> select sql_id,sql_text from v$sql where sql_id in ('b4nm0d4a74wb3','g5509f6617bth','9kfpkkusk5tdx');

SQL_ID        SQL_TEXT
------------- --------------------------------------------------------------------------------
b4nm0d4a74wb3 select count(*) from dba_objects,dba_users,dba_users where object_id > 123
9kfpkkusk5tdx select count(*) from dba_objects,dba_users,dba_users where object_id > 456
g5509f6617bth select count(*) from dba_objects,dba_users,dba_users where object_id > 789

5.查询sql历史执行情况（资源消耗）

set lines 200 pages 1000
col shijian for a12
col inst_id for 99
col execu_d for 999999999999
col bg_d for 9999999999
col dr_d for 9999999999
col et_d for 99999999
col ct_d for 99999999
col io_time for 999999
col clus_time for 999999
col ap_time for 999999
col cc_time for 999999
col et_onetime for 999999
select
    to_char(b.END_INTERVAL_TIME,'yyyymmdd hh24') shijian,
    a.instance_number inst_id,
    a.plan_hash_value,
    sum(a.EXECUTIONS_DELTA) execu_d,
    sum(a.BUFFER_GETS_DELTA ) bg_d,
    sum(a.DISK_READS_DELTA ) dr_d,
    sum(a.ELAPSED_TIME_DELTA/1000000) et_d,
    sum(a.CPU_TIME_DELTA/1000000)  ct_d,
    sum(IOWAIT_DELTA/1000000) io_time,
    sum(CLWAIT_DELTA/1000000) clus_time,
    sum(APWAIT_DELTA/1000000) ap_time,
    sum(ccwait_delta/1000000) cc_time,
    decode(sum(a.EXECUTIONS_DELTA),0,sum(a.ELAPSED_TIME_DELTA/1000000),
    sum(a.ELAPSED_TIME_DELTA/1000000)/sum(a.EXECUTIONS_DELTA)) et_onetime,
    decode(sum(a.EXECUTIONS_DELTA),0,sum(a.rows_processed_DELTA),sum(a.rows_processed_DELTA)/sum(a.EXECUTIONS_DELTA)) rw_onetime
from     
    dba_hist_sqlstat a,dba_hist_snapshot b
where
    a.SNAP_ID =b.SNAP_ID
and a.INSTANCE_NUMBER=b.INSTANCE_NUMBER
and to_char(FORCE_MATCHING_SIGNATURE)='569135988439770609'
group by
    to_char(b.END_INTERVAL_TIME,'yyyymmdd hh24'),a.plan_hash_value,a.instance_number
order by 1;

SHIJIAN      INST_ID PLAN_HASH_VALUE       EXECU_D        BG_D        DR_D      ET_D      CT_D IO_TIME CLUS_TIME AP_TIME CC_TIME ET_ONETIME RW_ONETIME
------------ ------- --------------- ------------- ----------- ----------- --------- --------- ------- --------- ------- ------- ---------- ----------
20241009 14        1      4050914861             3        3438           1        32        32       0         0       0       0         11        1