[20210331]sql_id=459f3z9u4fb3u.txt
--//生产系统使用exadata，执行sql_id=459f3z9u4fb3时，出现Disk file operations IO等待事件，我感觉有必要探究这条语句在哪里
--//执行的。
1.环境：
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.测试：
--//分别以sys，scott用户登录，scott登录后不执行任何sql语句。在我的测试环境很容易确定各个链接的SID。
SYS@book> select * from v$open_cursor where sid=1;
SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- ---- --------- ---------------- ---------- ------------- ------------------------------------------------------------ ------------------- ----------- ----------------------------------------------------------------
0000000086311830 1 SCOTT 000000007E2CBE80 4087094668 g4y6nw3tts7cc BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END; DICTIONARY LOOKUP CURSOR CACHED
0000000086311830 1 SCOTT 000000007D3A8260 3933222116 dyk4dprp70d74 SELECT DECODE('A','A','1','2') FROM DUAL DICTIONARY LOOKUP CURSOR CACHED
0000000086311830 1 SCOTT 000000007E216088 430743165 5qgz1p0cut7mx BEGIN DBMS_OUTPUT.DISABLE; END; DICTIONARY LOOKUP CURSOR CACHED
0000000086311830 1 SYS 000000007DC64DD8 487379649 cm5vu20fhtnq1 select /*+ connect_by_filtering */ privilege#,level from sys SESSION CURSOR CACHED
0000000086311830 1 SYS 000000007D537B00 3819099649 3nkd3g3ju5ph1 select obj#,type#,ctime,mtime,stime, status, dataobj#, flags BUNDLE DICTIONARY LOOKUP CACHED
0000000086311830 1 SYS 000000007E17ACE8 1950821498 459f3z9u4fb3u select value$ from props$ where name = 'GLOBAL_DB_NAME' DICTIONARY LOOKUP CURSOR CACHED
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0000000086311830 1 SCOTT 000000007BECF480 225524178 d6vwqbw6r2ffk SELECT USER FROM DUAL DICTIONARY LOOKUP CURSOR CACHED
0000000086311830 1 SCOTT 000000007E2CC4E0 616533857 cw6vxf0kbz3v1 SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER(' DICTIONARY LOOKUP CURSOR CACHED
0000000086311830 1 SYS 000000007E17A9A8 2194907850 0ws7ahf1d78qa select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U DICTIONARY LOOKUP CURSOR CACHED
0000000086311830 1 SCOTT 000000007CCB94C8 4253530419 7hys3h7ysgf9m SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE F DICTIONARY LOOKUP CURSOR CACHED
0000000086311830 1 SYS 000000007E1E7798 2017311249 5ur69atw3vfhj select decode(failover_method, NULL, 0 , 'BASIC', 1, 'PRECON DICTIONARY LOOKUP CURSOR CACHED
0000000086311830 1 SYS 000000007DCFB050 3873422482 0k8522rmdzg4k select privilege# from sysauth$ where (grantee#=:1 or grante DICTIONARY LOOKUP CURSOR CACHED
12 rows selected.
--//这个测试与链接http://blog.itpub.net/4227/viewspace-708276 看到的情况类似。
--//sql_id = cm5vu20fhtnq1,0k8522rmdzg4k,459f3z9u4fb3u,5ur69atw3vfhj,0ws7ahf1d78qa 都可以在我前面的输出上看到。
3.使用我改写Tcpdumpsql跟踪登录的情况。
--//为了检测begin，我做了小量修改：
#! /bin/bash
/usr/sbin/tcpdump -l -i eth0 -s 0 -A -nn src host $1 and dst port 1521 2>/dev/null | tee -a /tmp/aa1 |sed -u -e "s/^M/!/g;s/^E\.\..\{1,100\}//;s/\.*$//;s/^\.*//" | \
awk '{if (tolower($0) ~ "select" || tolower($0) ~ "begin" || tolower($0) ~ "update" || tolower($0) ~ "delete" ||tolower($0) ~ "alter" || tolower($0) ~ "insert" || $0 ~ "ORA-" ) {p=1;print} \
else if(p == 1 && $0 !~ "^[0-9][0-9]:") {print} else if ($0 ~ "^[0-9][0-9]:") {p=0}}'
--//注意^M的输入，是ctrl+v ctrl+M。
# Tcpdumpsql 192.168.98.6
?....g...........s..............................................scott.....AUTH_SESSKEY`...`3A1A56B67EBFE65E55537ECB3726A647118590C0A489F2BF088EC88E26D1F817BD94F30F6A84C6976398D345E5DC91F9....!...!AUTH_PASSWORD@...@E9422EDB879C06436C11C50960FCDF752E0E9B40338F442F35A5319499B29B69.........AUTH_RTT.....6974....!...!AUTH_CLNT_MEM.....4096....!...!AUTH_TERMINAL.....IKD84BCP.........AUTH_PROGRAM_NM.....sqlplus.exe.........AUTH_MACHINE.....WORKGROUP\IKD84BCP.........AUTH_PID ... 7660:5616.........AUTH_SID!...!Administrator.........AUTH_CONNECT_STRING.....(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.78)(PORT=1521)))(SDU=32768)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=book)(CID=(PROGRAM=E:\app\oracle\product\12.2.0\dbhome_1\bin\sqlplus.exe)(HOST=IKD84BCP)(USER=Administrator)))).........SESSION_CLIENT_CHARSET.....852.........SESSION_CLIENT_LIB_TYPE.....1.........SESSION_CLIENT_DRIVER_NAME.....SQL*PLUS.........SESSION_CLIENT_VERSION ... 203424000.........SESSION_CLIENT_LOBATTR.....1.........AUTH_ACL.....8800.........AUTH_ALTER_SESSION......ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY= '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'YYYY-MM-DD HH24:MI:SS' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= 'BINA.RY' TIME_ZONE= '+08:00' NLS_COMP= 'BINARY' NLS_DUAL_CURRENCY= '$' NLS_TIME_FORMAT= 'HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT= 'YYYY-MM-DD HH24:MI:SS.FF' NLS_TIME_TZ_FORMAT= 'HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT= 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'...........AUTH_LOGICAL_SESSION_ID ... 3AB538BD4E4B444CBDFA98EC68891A66.........AUTH_FAILOVER_ID
?....q.K.........^.a...............]...........!...............................................................................................................................................]SELECT DECODE(USER, 'XS$NULL', XS_SYS_CONTEXT('XS$SESSION','USERNAME'), USER) FROM SYS.DUAL
?....t. .........i..................^ !...........................!................................................................................................................................................BEGIN DBMS_OUTPUT.DISABLE; END;
^.a...........................!................................................................................................................................................SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND (USER LIKE USERID)
?....x...........i..................^!a...........................!................................................................................................................................................SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND ((USER LIKE USERID) OR (USERID = 'PUBLIC')) AND (UPPER(ATTRIBUTE) = 'ROLES')
?....y.^.........i..................^.)...............6...........!...............................................................................................................................................6BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;...........................................................................T........SQL*Plus
?....z.J.........i..................^.q...............,...........!...............................................................................................................................................,SELECT DECODE('A','A','1','2') FROM SYS.DUAL
--//从跟踪看没有看见select value$ from props$ where name = 'GLOBAL_DB_NAME'语句。
--//难道是递归调用BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL);才执行的吗？不对，CURSOR_TYPE='DICTIONARY LOOKUP CURSOR CACHED'.
--//cm5vu20fhtnq1 , 3nkd3g3ju5ph1,459f3z9u4fb3u ,0ws7ahf1d78qa,5ur69atw3vfhj,0k8522rmdzg4k 也没有看到(占6条)。而看到的正好6条。
--//说明前面6条正好是sys用户执行，也就是这6条根本没有经过网络，是某种递归执行的，以sys用户。
$ strings $(which oracle) | grep "GLOBAL_DB_NAME"
SELECT count(*) FROM sys.props$ WHERE name = 'GLOBAL_DB_NAME' AND value$ = :1
GLOBAL_DB_NAME
insert into props$(name,value$,comment$) values ('GLOBAL_DB_NAME', :1, 'Global database name')
select value$ from props$ where name = 'GLOBAL_DB_NAME'
update props$ set value$ = :1 where name = 'GLOBAL_DB_NAME'
SCOTT@book> set verify off
SCOTT@book> @ sqlid cm5vu20fhtnq1
SQL_ID SQLTEXT
------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------
cm5vu20fhtnq1 select /*+ connect_by_filtering */ privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
$ strings $(which oracle) | grep "connect_by_filtering"
select /*+ connect_by_filtering */ privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0 and privilege# in ( %s )
select /*+ connect_by_filtering */ privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with (grantee#=:1 or grantee#=1) and privilege#>0 and privilege# in ( %s )
select /*+ connect_by_filtering */ privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select /*+ connect_by_filtering */ privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with (grantee#=:1 or grantee#=1) and privilege#>0
with insqb as (select * from oraxbrl_lk_arc where xlink_role = :link_role_bind and parent_oid in (select OID from table( DBMS_ORAXBRL_INTERNAL.DTS_Files(:entryuri_bind)) d where d.type = 'LINKBASE') and linkbasetype = :ltype and arc_arcrole = :arc_role_bind) select /*+ no_connect_by_filtering CONNECT_BY_ELIM_DUPS*/ level, o1.elemnode from (select XMLELEMENT(Concept, XMLATTRIBUTES(e.namespaceUri, e.preferredPrefix, e.element_name as name, e.element_id as id, e.element_balance as balance, e.element_periodType as periodType, e.element_abstract as abstract, e.element_nillable as nillable, e.typeuri, e.element_type as typeLocalName, e.substuri as sgUri, e.element_substitutionGroup as sgLocalName, e.element_abspath as elem_href, t3.arc_preferredlabel as preflabel_arcrole)) as elemnode, t3.arc_from_abspath, t3.arc_to_href, t3.arc_order, t3.arc_to_abspath, t3.arc_preferredlabel from oraxbrl_schema_elementmat e, (select distinct t1.ARC_FROM_abspath, t1.ARC_TO_href, t1.ARC_ORDER, t1.arc_to_abspath, t1.arc_preferredlabel from insqb t1 where arc_priority = (select max(arc_priority) from insqb t2 where t2.ARC_FROM_abspath = t1.ARC_FROM_abspath and t2.ARC_TO_abspath = t1.ARC_TO_abspath group by t2.ARC_FROM_abspath, t2.ARC_TO_abspath) and t1.arc_use != 'prohibited' ) t3 where t3.arc_to_abspath = e.element_abspath) o1 connect by prior ARC_TO_abspath = ARC_FROM_abspath start with arc_from_abspath = :root_name ORDER SIBLINGS BY to_number(arc_order), arc_to_abspath
--//其它几条都可以在执行文件oracle 中找到。
4.跟踪看看：
create or replace
trigger sys.TRACE_ALL_LOGINS
after logon on database
begin
execute immediate 'alter session set tracefile_identifier = expdp';
execute immediate 'alter session set events = ''10046 trace name context forever, level 12''';
exception
--
-- if something goes wrong, we still want to allow a login to proceed
--
when others then null;
end;
/
$ grep -i global *EXPDP.trc
$ extractsql.sh book_ora_43824_EXPDP.trc
begin
execute immediate 'alter session set tracefile_identifier = expdp';
execute immediate 'alter session set events = ''10046 trace name context forever, level 12''';
exception
--
-- if something goes wrong, we still want to allow a login to proceed
--
when others then null;
end;
SELECT USER FROM DUAL
BEGIN DBMS_OUTPUT.DISABLE; END;
SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND (USER LIKE USERID)
SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND ((USER LIKE USERID) OR (USERID = 'PUBLIC')) AND (UPPER(ATTRIBUTE) = 'ROLES')
BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
SELECT DECODE('A','A','1','2') FROM DUAL
--//很明显是跟踪不到这些sql语句的。禁用触发器。
5.换一种跟踪方式：
alter system set events 'sql_trace off';
alter system set events 'sql_trace [sql:cm5vu20fhtnq1|3nkd3g3ju5ph1|459f3z9u4fb3u|0ws7ahf1d78qa|5ur69atw3vfhj|0k8522rmdzg4k] bind=true, wait=true';
--//使用sqlplus登录。
alter system set events 'sql_trace off';
$ grep -i global *.trc
book_ora_44100.trc:select value$ from props$ where name = 'GLOBAL_DB_NAME'
$ extractsql.sh book_ora_44100.trc
select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0
select /*+ connect_by_filtering */ privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
select value$ from props$ where name = 'GLOBAL_DB_NAME'
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 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
--//ok，这样可以看到如何执行的调用过程。
SCOTT@book> select * from dba_extents where segment_name in ('SYSAUTH$','I_SYSAUTH1','PROPS$','SERVICE$');
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ -------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
SYS PROPS$ TABLE SYSTEM 0 1 800 65536 8 1
SYS SYSAUTH$ TABLE SYSTEM 0 1 936 65536 8 1
SYS I_SYSAUTH1 INDEX SYSTEM 0 1 960 65536 8 1
SYS SERVICE$ TABLE SYSTEM 0 1 2136 65536 8 1
--//表，索引都很小，生产系统查询也是一样。根本不可能出现生产系统fileno#=29的情况。
--//说句真心话，生产系统根本不应该搞出system表空间出现2个数据文件的情况，无形增加维护难度。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2765851/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
