有客户对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 /*+ |
|
|
270,653 |
545,943 |
2.02 |
125.71 |
16.34 |
0.00 |
select privilege# |
||
|
259,736 |
259,753 |
1.00 |
64.03 |
45.20 |
0.00 |
select value$ |
||
|
259,720 |
259,716 |
1.00 |
35.67 |
56.40 |
0.00 |
select |
||
|
259,660 |
259,715 |
1.00 |
121.55 |
50.67 |
0.00 |
select |
出现在这个报告中前几位的SQL除了第一个以外，剩下的执行次数基本上一致，而第一个显然是其他的2倍，那么这显然不是一个巧合。
根据报告的运行时间：
|
Snap Id |
Snap Time |
Sessions |
Cursors/Session |
|
|
Begin Snap: |
661 |
14-Sep-11 |
318 |
1.6 |
|
End Snap: |
666 |
14-Sep-11 |
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 |
|
0k8522rmdzg4k |
select privilege# from sysauth$ where (grantee#=:1 or |
|
459f3z9u4fb3u |
select value$ from props$ where name = ‘GLOBAL_DB_NAME’ |
|
5ur69atw3vfhj |
select decode(failover_method, NULL, 0 , ‘BASIC’, 1, ‘PRECONNECT’, |
|
0ws7ahf1d78qa |
select SYS_CONTEXT(‘USERENV’, ‘SERVER_HOST’), SYS_CONTEXT(‘USERENV’, |
对于这种大数据量出现的SQL语句，多半都和Oracle内部的递归调用有关，只要仔细分析就不难找到其出处。