2.问题分析:
--//上个星期看了生产系统awr报表,发现一些问题.原来防水墙不断调用执行如下:
begin :con := "TASSETACL"."QUERYACL"(:sn, :on); end;
--//该语句已经消失,我问了同事,据说升级过该产品,当时awr的记录如下:
SQL ordered by Executions
%CPU - CPU Time as a percentage of Elapsed Time
%IO - User I/O Time as a percentage of Elapsed Time
Total Executions: 23,199,582
Captured SQL account for 34.2% of Total
Executions Rows Processed Rows per Exec Elapsed Time (s)%CPU %IO SQL Id SQL Module SQL Text
6,522,522 6,521,177 1.00 1,660.15 99.4 0 190q1sz3ywrd7 xxxxxx.eee begin :con := "TASSETACL".QUE...
2,228,287 2,228,115 1.00 107.98 99.5 0 g7ytdh9mxt1s0 xxxxxx.eee select count ( :"SYS_B_0" ) fr...
--//而现在变成了如下:
SQL ordered by Elapsed Time
Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
为PL/SQL代码报告的资源包括该代码调用的所有SQL语句所使用的资源。
% Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
%Total - Elapsed Time as a percentage of Total DB time
%CPU - CPU Time as a percentage of Elapsed Time
%IO - User I/O Time as a percentage of Elapsed Time
Captured SQL account for 65.1% of Total DB Time (s): 33,342
Captured PL/SQL account for 41.7% of Total DB Time (s): 33,342
Elapsed Time (s) Executions Elapsed Time per Exec (s) %Total %CPU %IO SQL Id SQL Module SQL Text
5,156.39 33,682 0.15 15.47 98.35 0.01 3pw59r6rkw9gb oraagent.bin@dm01dbadm01.XXX.com (TNS V1- declare PRIVS_ERROR exception;...
5,153.22 33,683 0.15 15.46 98.34 0.01 0xwxau19hznj8 oraagent.bin@dm01dbadm01.XXX.com (TNS V1- begin dbagent.try_login(); end...
5,089.67 134,713 0.04 15.26 98.34 0.00 8vmu6k690g87k oraagent.bin@dm01dbadm01.XXX.com (TNS V1- SELECT UPPER(NVL(PROGRAM, 'nul...
--//原来的问题消失了,但是消耗的时间却增加了,很明显对方的产品没有经过严格的测试,就给用户使用了.
--//取出sql语句,主要便于查看并且格式化如下:
SYS> @ sql_id 3pw59r6rkw9gb
--SQL_ID = 3pw59r6rkw9gb
declare
PRIVS_ERROR exception; --raise error,if rule exception,will trigger privs_error
pragma exception_init(PRIVS_ERROR, -1031);
begin
execute immediate 'begin dbagent.try_login(); end;';
exception
when PRIVS_ERROR then
raise;
when others then
rollback;
end dbagent_logon;;
--//注意后面多了1个分号,是我写的脚本无法区分PL/SQL语句与sql语句造成的问题.
SYS> @sql_id 0xwxau19hznj8
--SQL_ID = 0xwxau19hznj8
begin dbagent.try_login(); end;;
--get login app name
function get_app_name return varchar2 is
begin
collect_app_info;
return l_appname;
end;
--get login app module name
function get_app_module_name return varchar2 is
begin
collect_app_info;
return l_module;
end;
--get login app type
function get_app_type return varchar2 is
begin
collect_app_info;
return l_type;
end;
--get app process number
function get_app_process return number is
begin
collect_app_info;
return l_process;
end;
--//这样就很好解析为什么调用1次执行4次sql_id=8vmu6k690g87k.而实际上
procedure collect_app_info is
begin
if not app_info_collect_status then
select upper(nvl(program, 'null')),
upper(module),
type,
decode(nvl(instr(process, ':'), 0),
0,
nvl(process, 1234),
substr(process, 1, instr(process, ':') - 1)),
osuser,
machine,
SCHEMANAME,
USERNAME,
SERVICE_NAME,
sid,
serial#
into l_appname,
l_module,
l_type,
l_process,
l_osuser,
l_machine,
l_SCHEMANAME,
l_username,
l_service_name,
l_sid,
l_serial#
from sys.v_$session
where sid = sys_context('userenv', 'sid');
end if;
end;
--//已经赋值给对应变量,根本不需要再通过什么函数返回对应值.直接使用对应变量应该可以,当然我没有测试.
--//最简单修改:
procedure collect_app_info is
begin
if not app_info_collect_status then
select userenv('SID') into l_sid from dual;
select upper(nvl(program, 'null')),
upper(module),
type,
decode(nvl(instr(process, ':'), 0),
0,
nvl(process, 1234),
substr(process, 1, instr(process, ':') - 1)),
osuser,
machine,
SCHEMANAME,
USERNAME,
SERVICE_NAME,
sid,
serial#
into l_appname,
l_module,
l_type,
l_process,
l_osuser,
l_machine,
l_SCHEMANAME,
l_username,
l_service_name,
l_sid,
l_serial#
from sys.v_$session
where sid = l_sid;
end if;