[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个数据文件的情况，无形增加维护难度。
[20210331]sql_id=459f3z9u4fb3u.txt
来源：这里教程网
时间：2026-03-03 16:33:46
作者：
编辑推荐：
- [20210331]sql_id=459f3z9u4fb3u.txt03-03
- 【RAC】RAC更换心跳地址和RAC更换存储主要步骤03-03
- 【RMAN】Oracle12c之后，rman备份Dataguard备端恢复可能出现逻辑错误03-03
- [20210401]跟踪sqlplus登录执行了什么.txt03-03
- Oracle运行监控工具Spotlight使用测试03-03
- 【TUNE_ORACLE】Oracle数据库与HugePages（二）HugePages配置和限制03-03
- 运维排查问题常用sql03-03
- 频繁出现Thread 1 cannot allocate new log03-03
下一篇：
相关推荐
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效
2 月 9 日消息，雷神 (THUNDEROBOT) 现已宣布推出基于英
-
制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用
2 月 10 日消息，制造商 Musnap 现已在海外推出一款 Oce
![[BBED]断电异常后修复Oracle数据文件(ORA-00702: bootstrap verison)](https://www.herecours.com/d/file/efpub/2026/03-03/20260303152239841819.jpg?x-oss-process=style/bb "[BBED]断电异常后修复Oracle数据文件(ORA-00702: bootstrap verison)")
热文推荐
- Oracle运行监控工具Spotlight使用测试
Oracle运行监控工具Spotlight使用测试
26-03-03 - 视频竖屏改横屏，用什么剪辑工具，批量改变视频的横竖屏比较快
视频竖屏改横屏，用什么剪辑工具，批量改变视频的横竖屏比较快
26-03-03 - 【DBA】数据库工程师DBA技能图谱
【DBA】数据库工程师DBA技能图谱
26-03-03 - [BBED]断电异常后修复Oracle数据文件(ORA-00702: bootstrap verison)
- redo损坏修复启动数据库办法
redo损坏修复启动数据库办法
26-03-03 - 【Oracle体系结构】 Oracle19C 系统结构介绍
【Oracle体系结构】 Oracle19C 系统结构介绍
26-03-03 - 多个百世快递的物流怎么批量查询的，怎样利用快递批量查询
多个百世快递的物流怎么批量查询的，怎样利用快递批量查询
26-03-03 - 从定位数据块所在ASM磁盘到ASM stripping
从定位数据块所在ASM磁盘到ASM stripping
26-03-03 - oracle执行计划------未走索引，隐式转换的坑
oracle执行计划------未走索引，隐式转换的坑
26-03-03 - 利用vmware创建rac共享磁盘
利用vmware创建rac共享磁盘
26-03-03