每条执行的语句都要先经过解析。在解析过程中需要进行语法和语意解析,再生成执行计划,这个过程叫做硬解析,是非常耗时的过程。如果直接从库缓存中找到已经解析过的语句的过程称为软解析。因此应该尽可能地减少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语句中的条件常量改为绑定变量。