有客户对AWR中报告中出现的一个查询GLOBAL_NAME的SQL存在疑问,详细分析后发现是客户端建立连接后获取权限信息的语句。
客户存在疑问的SQL是:
select value$ from props$ where name = 'GLOBAL_DB_NAME'
而这个SQL出现在AWR报告中执行次数部分,可以看到这个SQL运行次数接近26W次。
Executions
Rows Processed
Rows per Exec
Elapsed Time (s)
%CPU
%IO
SQL Id
SQL Module
SQL Text
546,016
8,942,073
16.38
170.26
28.28
0.00
cm5vu20fhtnq1
select /*+ connect_by_filterin...
270,653
545,943
2.02
125.71
16.34
0.00
select privilege# from sysauth...
259,736
259,753
1.00
64.03
45.20
0.00
select value$ from props$ wher...
259,720
259,716
1.00
35.67
56.40
0.00
select decode(failover_method,...
259,660
259,715
1.00
121.55
50.67
0.00
select SYS_CONTEXT('USERENV', ...
出现在这个报告中前几位的SQL除了第一个以外,剩下的执行次数基本上一致,而第一个显然是其他的2倍,那么这显然不是一个巧合。
根据报告的运行时间:
Snap Id
Snap Time
Sessions
Cursors/Session
Begin Snap:
661
14-Sep-11 13:00:13
318
1.6
End Snap:
666
14-Sep-11 18:00:52
375
1.8
Elapsed:
300.64 (mins)
以及每秒登录数据库的次数:
Per Second
Per Transaction
Per Exec
Per Call
DB Time(s):
3.1
0.7
0.01
0.01
DB CPU(s):
2.4
0.6
0.01
0.01
Redo size:
17,771.2
4,136.4
Logical reads:
70,747.7
16,467.1
Block changes:
103.0
24.0
Physical reads:
156.0
36.3
Physical writes:
49.0
11.4
User calls:
440.1
102.4
Parses:
264.6
61.6
Hard parses:
49.1
11.4
W/A MB processed:
25.7
6.0
Logons:
15.2
3.5
Executes:
262.3
61.0
Rollbacks:
0.0
0.0
Transactions:
4.3
计算数据库中这段时间内登录总次数:
SQL> select 300.64*60*15.2 from dual;
300.64*60*15.2
--------------
274183.68
显然包括SQL:select value$ from props$ where name = 'GLOBAL_DB_NAME'在内的这些执行次数最多的SQL,基本上都是每登录一次就运行一次,而对于SQL_ID为cm5vu20fhtnq1的语句,每次登录需要运行两次。
这个SQL就是Oracle在登录阶段获取权限和连接信息的SQL,而且根据SQL语句在metalink上文档ID 730066.1中也找到的官方的说明:
select value$ from props$ where name = 'GLOBAL_DB_NAME'
select privilege#,level from sysauth$ connect by grantee#=prior privilege#
and privilege#>0 start with grantee#=:1 and privilege#>0
select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'),
SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'SERVICE_NAME'),
INSTANCE_NUMBER, STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN')
from v$instance where INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME')
select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0
ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY= '$'
NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GREGORIAN'
NLS_DATE_FORMAT= 'DD-MON-RR' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= 'BINARY' TIME_ZONE= '+02:00'
NLS_COMP= 'BINARY' NLS_DUAL_CURRENCY= '$' NLS_TIME_FORMAT= 'HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT=
'DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT= 'HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT=
'DD-MON-RR HH.MI.SSXFF AM TZR'
这是10g环境中Oracle在登录阶段会执行的SQL语句,可以看到在当前11.2.0.2环境中,这些SQL大部分没有改变只是去掉了ALTER SESSION语句,取代其的是一个查询SERVICE的语句,此外其中一个增加了HINT。在AWR报告中,这些SQL完整信息为:
cm5vu20fhtnq1
select /*+ connect_by_filtering */ privilege#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
0k8522rmdzg4k
select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0
459f3z9u4fb3u
select value$ from props$ where name = 'GLOBAL_DB_NAME'
5ur69atw3vfhj
select decode(failover_method, NULL, 0 , 'BASIC', 1, 'PRECONNECT', 2 , 'PREPARSE', 4 , 0), decode(failover_type, NULL, 1 , 'NONE', 1 , 'SESSION', 2, 'SELECT', 4, 1), failover_retries, failover_delay, flags from service$ where name = :1
0ws7ahf1d78qa
select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'SERVICE_NAME'), INSTANCE_NUMBER, STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN') from v$instance where INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME')
对于这种大数据量出现的SQL语句,多半都和Oracle内部的递归调用有关,只要仔细分析就不难找到其出处。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-708276/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-708276/