达梦8 查找系统中有没有使用绑定变量的语句

每条执行的语句都要先经过解析。在解析过程中需要进行语法和语意解析，再生成执行计划，这个过程叫做硬解析，是非常耗时的过程。如果直接从库缓存中找到已经解析过的语句的过程称为软解析。因此应该尽可能地减少SQL语句的硬解析次数。

生产系统中由于历史原因可能存在大量没有使用绑定变量的SQL。通过如下语句可以快速找到此类SQL:

select node_addr,min_sql_id,
(select substr(sql_text,1,35)  || decode(substr(sql_text,36,1),'','',' .....')  from v$sqltext where sql_id=min_sql_id) min_sql_text,
(select substr(sql_text,1,35)  || decode(substr(sql_text,36,1),'','',' .....')  from v$sqltext where sql_id=max_sql_id) max_sql_text,cnt
from
 (
select node_addr,count(*) cnt,min(sql_id) min_sql_id,max(sql_id) max_sql_id
from v$sql_plan_node
where operation='NSET2'
group by node_addr having count(*) > 1
)  order by cnt desc 
limit 10

行号       NODE_ADDR          MIN_SQL_ID  MIN_SQL_TEXT                              MAX_SQL_TEXT                              CNT
---------- ------------------ ----------- ----------------------------------------- ----------------------------------------- --------------------
1          0x00000000646F69F0 372         select id from t1 where id=2              select id from t1 where id=5000           4999
2          0x000000007F9F3FE8 230         select COMMENT$ from SYSCOLUMNCOMME ..... select COMMENT$ from SYSCOLUMNCOMME ..... 9
3          0x0000000111F611B8 222         select * from t2 where id=1;              select * from t2 where 1=id;              5
4          0x0000000122DFF708 5464        select cnt,min_sql_id,max_sql_id, ( ..... select min_sql_id,max_sql_id,cnt, ( ..... 3
5          0x000000007F9FAA00 350         select node_addr,count(*) from v$sq ..... select node_addr,count(*) cnt from  ..... 2
6          0x000000007FA0A138 5451        select distinct sql_id,a.node_addr  ..... select distinct sql_id,a.node_addr  ..... 2
7          0x000000007F9FB218 5374        select node_addr from v$sql_plan_no ..... select node_addr from v$sql_plan_no ..... 2
8          0x000000007FA7A438 5371        select sql_text,node_addr from (sel ..... select sql_text,node_addr from (sel ..... 2
9          0x000000007F9FB8F8 346         select node_addr,count(*) from v$sq ..... select node_addr,count(*) from v$sq ..... 2
10         0x000000007F9F5510 234         select distinct pln_addr from v$sql ..... select  distinct node_addr from v$s ..... 2

10 rows got

为了排版方便，sql_text只显示前35位。如果长度超过35位则截断，后面用“...”表示。查看SQL全部请查v$sqltext视图。以第二行为例：

SQL> select sql_text from v$sqltext where sql_id=230;

行号       SQL_TEXT
---------- ------------------------------------------------------------------------------------------------------------
1          select COMMENT$ from SYSCOLUMNCOMMENTS where SCHNAME='SYS' and TVNAME='V$SQL_PLAN_NODE' and COLNAME='SQL_ID'

查看所有SQL请查v$sql_plan_node视图，以第二行为例：

SQL> select a.sql_id,b.sql_text from v$sql_plan_node a ,v$sqltext b
2   where node_addr='0x000000007F9F3FE8'
3   and a.sql_id=b.sql_id
4   /

行号       SQL_ID      SQL_TEXT
---------- ----------- ----------------------------------------------------------------------------------------------------------------
1          365         select COMMENT$ from SYSCOLUMNCOMMENTS where SCHNAME='SYS' and TVNAME='V$SQLTEXT' and COLNAME='LINK_ADDR'
2          230         select COMMENT$ from SYSCOLUMNCOMMENTS where SCHNAME='SYS' and TVNAME='V$SQL_PLAN_NODE' and COLNAME='SQL_ID'
3          231         select COMMENT$ from SYSCOLUMNCOMMENTS where SCHNAME='SYS' and TVNAME='V$SQL_PLAN_NODE' and COLNAME='NODE_ADDR'
4          232         select COMMENT$ from SYSCOLUMNCOMMENTS where SCHNAME='SYS' and TVNAME='V$SQL_PLAN_NODE' and COLNAME='PLN_ADDR'
5          322         select COMMENT$ from SYSCOLUMNCOMMENTS where SCHNAME='SYS' and TVNAME='V$SQL_PLAN_NODE' and COLNAME='OPERATION'
6          323         select COMMENT$ from SYSCOLUMNCOMMENTS where SCHNAME='SYS' and TVNAME='V$SQL_PLAN_NODE' and COLNAME='HAS_INVOKE'
7          362         select COMMENT$ from SYSCOLUMNCOMMENTS where SCHNAME='SYS' and TVNAME='V$SQLTEXT' and COLNAME='SQL_ADDR'
8          363         select COMMENT$ from SYSCOLUMNCOMMENTS where SCHNAME='SYS' and TVNAME='V$SQLTEXT' and COLNAME='SQL_TEXT'
9          364         select COMMENT$ from SYSCOLUMNCOMMENTS where SCHNAME='SYS' and TVNAME='V$SQLTEXT' and COLNAME='HASH_VALUE'

9 rows got

与开发团队确认是否可以修改程序，将这些类似SQL语句中的条件常量改为绑定变量。