find Literal SQL

利用FORCE_MATCHING_SIGNATURE捕获非绑定变量SQL

Applies to:

PL/SQL – Version: 8.1.7 to 10.2

Information in this document applies to any platform.

Goal

There is no direct way to query the dictionary for literal SQL only.

However the following example will try to exclude all SQL statements in the

shared pool that do use bind variables.

There still might be situations, with statements using subqueries, where the

example still will show SQL statements using bind variables.

Solution

Create the following PL/SQL block:

http://www.askmaclean.com/archives/%E5%88%A9%E7%94%A8force_matching_signature%E6%8D%95%E8%8E%B7%E9%9D%9E%E7%BB%91%E5%AE%9A%E5%8F%98%E9%87%8Fsql.html

set serveroutput on
set linesize 120
--
-- This anonymous PL/SQL block must be executed as INTERNAL or SYS
-- Execute from : SQL*PLUS
-- CAUTION:
-- This sample program has been tested on Oracle Server - Enterprise Edition
-- However, there is no guarantee of effectiveness because of the possibility
-- of error in transmitting or implementing it. It is meant to be used as a
-- template, and it may require modification.
--
declare
b_myadr VARCHAR2(20);
b_myadr1 VARCHAR2(20);
qstring VARCHAR2(100);
b_anybind NUMBER;
cursor my_statement is
select address from v$sql
group by address;
cursor getsqlcode is
select substr(sql_text,1,60)
from v$sql
where address = b_myadr;
cursor kglcur is
select kglhdadr from x$kglcursor
where kglhdpar = b_myadr
and kglhdpar != kglhdadr
and kglobt09 = 0;
cursor isthisliteral is
select kkscbndt
from x$kksbv
where kglhdadr = b_myadr1;
begin
dbms_output.enable(10000000);
open my_statement;
loop
Fetch my_statement into b_myadr;
open kglcur;
fetch kglcur into b_myadr1;
if kglcur%FOUND Then
open isthisliteral;
fetch isthisliteral into b_anybind;
if isthisliteral%NOTFOUND Then
open getsqlcode;
fetch getsqlcode into qstring;
dbms_output.put_line('Literal:'||qstring||' address: '||b_myadr);
close getsqlcode;
end if;
close isthisliteral;
end if;
close kglcur;
Exit When my_statement%NOTFOUND;
End loop;
close my_statement;
end;
/
/*尝试执行*/
SQL> @find_literal
Literal:select inst_id, java_size, round(java_size / basejava_size,  address: 00000000BC6E94E8
Literal:select reason_id, object_id, subobject_id, internal_instance address: 00000000BC5F1D60
Literal:select  DBID, NAME, CREATED, RESETLOGS_CHANGE#, RESETLOGS_TI address: 00000000BC6000B0
Literal:select di.inst_id,di.didbi,di.didbn,to_date(di.dicts,'MM/DD/ address: 00000000BC530DA8
Literal:      declare          vsn  varchar2(20);             begin  address: 00000000BC85A9F8
Literal:SELECT INCARNATION#, RESETLOGS_CHANGE#, RESETLOGS_TIME, PRIO address: 00000000BC829978
Literal:select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where  address: 00000000BCA84D00
Literal:select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U address: 00000000BC771BF0
Literal: select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_S address: 00000000BC4673A8
Literal:select streams_pool_size_for_estimate s,           streams_p address: 00000000BCA58848
Literal:         select open_mode from v$database address: 00000000BC5DF2D0
Literal:select FORCE_MATCHING_SIGNATURE, count(1)   from v$sql  wher address: 00000000BCA91628
Literal:select inst_id, tablespace_name, segment_file, segment_block address: 00000000BC66EF38
Literal:select sum(used_blocks), ts.ts#   from GV$SORT_SEGMENT gv, t address: 00000000BCAA01B0
Literal:BEGIN DBMS_OUTPUT.ENABLE(NULL); END; address: 00000000BC61D2D8
Literal:select value$ from props$ where name = 'GLOBAL_DB_NAME' address: 00000000BC570500
Literal:select count(*) from sys.job$ where (next_date > sysdate) an address: 00000000BC6C53F8
Literal:select java_pool_size_for_estimate s,           java_pool_si address: 00000000BCA65070
Literal:select local_tran_id, global_tran_fmt, global_oracle_id, glo address: 00000000BC5900B8
Literal:select inst_id,kglnaobj,kglfnobj,kglobt03, kglobhs0+kglobhs1 address: 00000000BC921538
Literal:select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname, address: 00000000BCA83E90
Literal:SELECT * FROM V$SQL address: 00000000BCA58BC0
Literal:SELECT ADDRESS FROM V$SQL GROUP BY ADDRESS address: 00000000BC565BE8
Literal:      begin          dbms_rcvman.resetAll;       end; address: 00000000BC759858
Literal:declare b_myadr VARCHAR2(20); b_myadr1 VARCHAR2(20); qstring address: 00000000BC928FF8
Literal:select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, address: 00000000BC898BF8
Literal:select CONF#, NAME, VALUE from GV$RMAN_CONFIGURATION where i address: 00000000BC8CB7F8
Literal:select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t address: 00000000BC8CDFE8
Literal:select u.name, o.name, trigger$.sys_evts, trigger$.type#  fr address: 00000000BCA877B8
Literal:select id, name, block_size, advice_status,                  address: 00000000BC636B38
Literal:select incarnation#, resetlogs_change#, resetlogs_time,      address: 00000000BCA94250
Literal:select  INSTANCE_NUMBER , INSTANCE_NAME , HOST_NAME , VERSIO address: 00000000BC62A678
Literal:select ks.inst_id,ksuxsins,ksuxssid,ksuxshst,ksuxsver,ksuxst address: 00000000BC8E5440
Literal:select timestamp, flags from fixed_obj$ where obj#=:1 address: 00000000BC916C78
Literal:select size_for_estimate,                      size_factor * address: 00000000BCA5F830
Literal:select shared_pool_size_for_estimate s,          shared_pool address: 00000000BCA5A350
Literal:select  SQL_TEXT , SQL_FULLTEXT , SQL_ID,  SHARABLE_MEM , PE address: 00000000BC76B3A0
Literal:lock table sys.col_usage$ in exclusive mode nowait address: 00000000BCA05978
Literal:select 'x' from dual  address: 00000000BC583818
Literal:      select name, resetlogs_time,              resetlogs_ch address: 00000000BCA9D430
Literal:select inst_id, sp_size, round(sp_size / basesp_size, 4),  k address: 00000000BC65A9F0
Literal:select userenv('Instance'),  icrid, to_number(icrls),        address: 00000000BC692260
Literal:select shared_pool_size_for_estimate, shared_pool_size_facto address: 00000000BCAE0750
Literal:select INST_ID, RMRNO, RMNAM, RMVAL from X$KCCRM where RMNAM address: 00000000BC8CD778
Literal:select metadata from kopm$  where name='DB_FDO' address: 00000000BC9EBB98
Literal:select java_pool_size_for_estimate, java_pool_size_factor,   address: 00000000BC5B27D0
Literal:SELECT INCARNATION#, INCARNATION#, RESETLOGS_CHANGE#, RESETL address: 00000000BC829C48
Literal:select file# from file$ where ts#=:1 address: 00000000BC87CF18
Literal:select A.inst_id, A.bpid, B.bp_name, A.blksz,                address: 00000000BC802248
Literal:lock table sys.mon_mods$ in exclusive mode nowait address: 00000000BC5CBE68
Literal:lock table sys.mon_mods$ in exclusive mode nowait address: 00000000BC5CBE68

本文出自 “无双城” 博客，请务必保留此出处http://929044991.blog.51cto.com/1758347/1262809