oracle里面有没有sql语句,Oracle 获取没有使用绑定变量的SQL语句

使用v$sql表中的force_matching_signature字段查询出没有使用绑定变量的SQL语句

FORCE_MATCHING_SIGNATURE

NUMBER

Signature used when theCURSOR_SHARING parameter is set toFORCE

意思是当cursor_sharing =force时的标识

根据force_matching_signature mathces分组，取出前5个执行次数最多的(不一定5条)没有使用绑定变量的sql语句

dense_rank() over(order by count(*) desc) ranking--按照count值倒序排序

with force_mathces as

(select l.force_matching_signature mathces,

max(l.sql_id || l.child_number) max_sql_child,

COUNT(*) COUNT,

dense_rank() over(order by count(*) desc) ranking

from v$sql l

where l.force_matching_signature <> 0

and l.parsing_schema_name <> 'SYS'

group by l.force_matching_signature

having count(*) > 10)

select v.sql_id, v.sql_text, v.parsing_schema_name, fm.mathces, fm.count,fm.ranking

from force_mathces fm, v$sql v

where fm.max_sql_child = (v.sql_id || v.child_number)

and fm.ranking <= 5

order by fm.ranking;

编写contab 任务，定时执行

[oracle@server8 user_stat]$ cat bind.sh

#!/bin/bash

ORACLE_SID=ghsjdb

ORACLE_BASE=/home/u01/app/oracle

ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

export ORACLE_SID ORACLE_BASE ORACLE_HOME TARGET_SID

PATH=$PATH:$ORACLE_HOME/bin:/usr/local/bin

export PATH

NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

export NLS_LANG

sqlplus / as sysdba <

with force_mathces as (select l.force_matching_signature mathces, max(l.sql_id || l.child_number) max_sql_child, COUNT(*) COUNT, dense_rank() over(order by count(*) desc) ranking from v\$sql l where l.force_matching_signature <> 0

AND l.parsing_schema_name <> 'SYS'GROUP by l.force_matching_signature having count(*) > 10)SELECT  v.sql_text,fm.count from force_mathces fm, v\$sql v where fm.max_sql_child = (v.sql_id || v.child_number) and fm.ranking <=10 ORDER by fm.ranking;

exit

EOF

crontab -l

0 10,4 * * *   /home/oracle/user_stat/bind.sh > /home/oracle/user_stat/bind.log

本文乃原创文章，请勿转载。如须转载请详细标明转载出处