s.o.m

55 积分	0 好友	2 主题

发消息

[升级] 如何手动清理v$sql的module信息?

1^#

发表于 2013-7-15 22:05:56 | 查看: 5035| 回复: 3

v$sql中的module由于记录第一次解析时的信息，所以执行相同的SQL计算更改module，其记录在v$sql的值不变化，这里我测试用dbms_shared_pool.purge清除游标信息，但最终结果显示module值还是原来的信息（测试相隔时间久一点是可以清除），
请问如何手动清理单条sql的module信息？

以下是测试过程，环境：RHEL5.2 + ORACLE 10.2.0.4.0 - 64bit

sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on D??úò? 7?? 15 21:20:38 2013
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> alter system flush shared_pool;
System altered.
SQL> set wrap off
SQL> set linesize 190
SQL> col sql_id format a15
SQL> col module format a15
SQL> col action format a15
SQL> col sql_text format a100
SQL> select sql_id, address, hash_value, executions, module, action, sql_text
2 from v$$sqlarea
3 where module like 's_o_m_test%';
no rows selected
SQL> declare
2 v_int varchar(50);
3 BEGIN
4 DBMS_APPLICATION_INFO.set_module(module_name => 's_o_m_test123',
action_name => 'select module');
6 select /* s_o_m */ 1 into v_int from dual;
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> select sql_id, address, hash_value, executions, module, action, sql_text
2 from v$$sqlarea
3 where module like 's_o_m_test%';
SQL_ID ADDRESS HASH_VALUE EXECUTIONS MODULE ACTION SQL_TEXT
--------------- ---------------- ---------- ---------- --------------- --------------- ----------------------------------------------------------------------------------------------------
bunvx480ynf57 0000000090F3D3F8 32127143 1 s_o_m_test123 select module SELECT 1 FROM DUAL
SQL> alter session set events '5614566 trace name context forever';
Session altered.
SQL> exec dbms_shared_pool.purge('0000000090F3D3F8,32127143','C',1);
PL/SQL procedure successfully completed.
----这里看到以上sql游标已经清除了...
SQL> select sql_id, address, hash_value, executions, module, action, sql_text
2 from v$$sqlarea
3 where module like 's_o_m_test%';
SQL_ID ADDRESS HASH_VALUE EXECUTIONS MODULE ACTION SQL_TEXT
--------------- ---------------- ---------- ---------- --------------- --------------- ----------------------------------------------------------------------------------------------------
39q0s0jmf7ggm 0000000097F76E38 1726201331 1 s_o_m_test123 select module SELECT VSIZE(KGLHDADR) FROM X$$KGLOB WHERE ROWNUM < 2
ak92r5092znk7 000000008F5484F8 305123911 1 s_o_m_test123 select module BEGIN dbms_shared_pool.purge('0000000090F3D3F8,32127143','C',1); END;
SQL> declare
2 v_int varchar(50);
3 BEGIN
4 DBMS_APPLICATION_INFO.set_module(module_name => 's_o_m_test369',
action_name => 'select module 1');
select /* s_o_m */ 1 into v_int from dual;
7 END;
8 /
PL/SQL procedure successfully completed.
----再次执行相同sql，手动指定module_name,其module信息显示第一次指定的值，而 EXECUTIONS 是1，...
SQL> select sql_id, address, hash_value, executions, module, action, sql_text
2 from v$$sqlarea
3 where module like 's_o_m_test%';
SQL_ID ADDRESS HASH_VALUE EXECUTIONS MODULE ACTION SQL_TEXT
--------------- ---------------- ---------- ---------- --------------- --------------- ----------------------------------------------------------------------------------------------------
bunvx480ynf57 0000000090F3D3F8 32127143 1 s_o_m_test123 select module SELECT 1 FROM DUAL
39q0s0jmf7ggm 0000000097F76E38 1726201331 1 s_o_m_test123 select module SELECT VSIZE(KGLHDADR) FROM X$$KGLOB WHERE ROWNUM < 2
11yqk8j033gpq 000000008B4BD2A8 1077001910 1 s_o_m_test123 select module declare v_int varchar(50); BEGIN DBMS_APPLICATION_INFO.set_module(module_name => 's_o_m_test369'
ak92r5092znk7 000000008F5484F8 305123911 1 s_o_m_test123 select module BEGIN dbms_shared_pool.purge('0000000090F3D3F8,32127143','C',1); END;