使用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 <<EOF
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
本文乃原创文章,请勿转载。如须转载请详细标明转载出处