shell中调用sql使用绑定变量举例

最近发现数据库上有部分硬解析较高，现在的数据库服务器硬件性能都非常给力，大内存多核心，硬解析指标不是非常重要了，但是作为找茬的dba还要给应用上上课的，既然你这么用了就该用好。检查硬解析的语句如下：

--查看硬解析的具体语句
select a.sql_id,
       a.MODULE,
       a.PARSING_SCHEMA_NAME,
       a.last_active_time,
       a.last_load_time,
       a.sql_text,
       b.pool_mb,
       b.cnt
  from gv$sqlarea a,
       (select max(sql_id) sql_id,
               FORCE_MATCHING_SIGNATURE,
               round(sum(SHARABLE_MEM / 1024 / 1024)) pool_mb,
               count(1) cnt
          from gv$sqlarea
         where FORCE_MATCHING_SIGNATURE > 0
           and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
         group by FORCE_MATCHING_SIGNATURE
        having count(1) > 100
         order by count(1) desc) b
 where a.sql_id = b.sql_id  --and a.PARSING_SCHEMA_NAME !='SYS'
 order by cnt desc;

--根据快照查看快照期间的平均硬解析
with x as (select 
       c.instance_name,
       a.SNAP_ID,
       b.BEGIN_INTERVAL_TIME,
       b.END_INTERVAL_TIME,
       a.METRIC_NAME,
       round(a.AVERAGE, 2) AVERAGE
  from dba_hist_sysmetric_summary a,dba_hist_snapshot b,gv$instance c
 where a.SNAP_ID = b.SNAP_ID
   and b.instance_number=c.instance_number
   and a.instance_number=c.instance_number
   and a.INSTANCE_NUMBER = b.INSTANCE_NUMBER
   and a.METRIC_NAME in ('Hard Parse Count Per Sec')
   and round(b.BEGIN_INTERVAL_TIME,'hh24')>=trunc(sysdate-1,'dd')+7/24
   AND b.dbid = (SELECT dbid FROM v$database)
   and a.dbid=b.dbid)
select 
x.instance_name 实例,
x.SNAP_ID 采样号,
to_char(x.BEGIN_INTERVAL_TIME, 'yyyy-MM-dd HH24:mi:ss') 开始时间,
to_char(x.END_INTERVAL_TIME, 'yyyy-MM-dd HH24:mi:ss') 结束时间,
x.AVERAGE 平均硬解析次数
from x 
where round(BEGIN_INTERVAL_TIME,'hh24')>=trunc(sysdate-1,'dd')+7/24  
and round(BEGIN_INTERVAL_TIME,'hh24')<=trunc(sysdate,'dd')+1/24
order by x.snap_id;
 

根据以上脚本确认了具体硬解析语句，跟应用反馈，得到的结果是通过shell调用的sql，建议他们在shell使用变量的形式，避免sql执行时每次进行解析，并缓存sql相关信息。

#!/bin/bash

# Oracle数据库连接信息
ORACLE_SID=orcl
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db
ORACLE_USER=scott
ORACLE_PASSWORD=tiger
DATABASE_HOST=192.168.56.201
DATABASE_PORT=1521
DATABASE_SERVICE=orcl

# 要查询的变量值
search_value=$1

# 设置Oracle环境变量
export ORACLE_SID ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH

# 使用sqlplus连接数据库并执行SQL语句

p1=`sqlplus -s "${ORACLE_USER}/${ORACLE_PASSWORD}@${DATABASE_HOST}:${DATABASE_PORT}/${DATABASE_SERVICE}" <<-!!! 
set trim on;
set concat on;
set trimspool on;
set termout on;
set show off;
set echo off;
set head off;
set flush off;
set feedback off;
set linesize 2000

-- 使用绑定变量来避免硬解析,如果是number类型  VARIABLE bv_search_value varchar2(100);处varchar2(100)换成number即可
VARIABLE bv_search_value varchar2(100);
EXEC :bv_search_value := '$search_value';

SELECT *
FROM emp
WHERE ename = :bv_search_value;


EXIT;
!!!`

# 读取并显示查询结果

echo "$p1"

上面是在自己的环境下测试的，在数据库中调用的sql就是

SELECT *
FROM emp
WHERE ename = :bv_search_value;

满足使用绑定变量的要求。