[20210331]sql_id=459f3z9u4fb3u.txt

[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個資料檔案的情況，無形增加維護難度。