有客戶對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	0k8522rmdzg4k		select privilege# from sysauth...
259,736	259,753	1.00	64.03	45.20	0.00	459f3z9u4fb3u		select value$ from props$ wher...
259,720	259,716	1.00	35.67	56.40	0.00	5ur69atw3vfhj		select decode(failover_method,...
259,660	259,715	1.00	121.55	50.67	0.00	0ws7ahf1d78qa		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內部的遞迴呼叫有關，只要仔細分析就不難找到其出處。

連線使用者獲取許可權資訊

相關文章