[20180110]disk file operations.txt

--//调查生产系统相关disk file operations问题，记录一下：

1.环境：
XXXX> @ &r/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

/* Formatted on 2018/1/10 8:47:22 (QP5 v5.269.14213.34769) */
  SELECT DECODE
         (
            p3
           ,0, 'Other'
           ,1, 'Control File'
           ,2, 'Data File'
           ,3, 'Log File'
           ,4, 'Archive Log'
           ,6, 'Temp File'
           ,9, 'Data File Backup'
           ,10, 'Data File Incremental Backup'
           ,11, 'Archive Log Backup'
           ,12, 'Data File Copy'
           ,13, 'Spfile'
           ,17, 'Flashback Log'
           ,18, 'Data Pump Dump File'
           ,'unknown ' || p3
         )
            "File Type"
        ,DECODE
         (
            p1
           ,1, 'file creation'
           ,2, 'file open'
           ,3, 'file resize'
           ,4, 'file deletion'
           ,5, 'file close'
           ,6, 'wait for all aio requests to finish'
           ,7, 'write verification'
           ,8, 'wait for miscellaneous io (ftp, block dump, passwd file)'
           ,9, 'read from snapshot files'
           ,'unknown ' || p1
         )
            "File Operation"
        ,DECODE (p3, 2, p2, -1) file#
        ,COUNT (*)
    FROM dba_hist_active_sess_history
   WHERE event = 'Disk file operations I/O'
GROUP BY p1
        ,p3
        ,DECODE (p3, 2, p2, -1)
        order by count(*) desc;

File Type         File Operation                                                FILE#   COUNT(*)
----------------- -------------------------------------------------------- ---------- ----------
Data File         file open                                                        29        391
Spfile            file close                                                       -1         12
Data File Backup  file deletion                                                    -1          5
Data File         file open                                                        62          4
unknown 8         wait for miscellaneous io (ftp, block dump, passwd file)         -1          2
Data File Backup  file open                                                        -1          1
Data File         file open                                                        34          1
Data File         file open                                                        58          1
unknown 28        file open                                                        -1          1
Data File         file open                                                        27          1
Control File      file creation                                                    -1          1
11 rows selected.

XXXX>  SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header where file#=29;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                               TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ---------------------------------- ---------------
   29        20504546477 2018-01-10 04:27:39          1000296            925702 ONLINE              5424 YES +DATAC1/XXXX/datafile/system01.bdf SYSTEM

--//我们生产系统安装存在2个system表空间文件(我感觉安装太不专业)，不知道为什么29存在大量的file open，为什么数据文件1没有。

SELECT sql_id, COUNT (*)
    FROM dba_hist_active_sess_history
   WHERE event = 'Disk file operations I/O' AND p2 = 29
GROUP BY sql_id;

SQL_ID            COUNT(*)
------------- ------------
459f3z9u4fb3u          393

--//又增加2次。

XXXX> @ &r/sql_id 459f3z9u4fb3u
old   1: select sql_id,sql_fulltext sqltext from gv$sqlarea where sql_id='&1'
new   1: select sql_id,sql_fulltext sqltext from gv$sqlarea where sql_id='459f3z9u4fb3u'
SQL_ID        SQLTEXT
------------- --------------------------------------------------------
459f3z9u4fb3u select value$ from props$ where name = 'GLOBAL_DB_NAME'
459f3z9u4fb3u select value$ from props$ where name = 'GLOBAL_DB_NAME'

--//我估计一连上数据库大量调用这条语句导致的问题，我有点奇怪的是为什么file#=1不存在这样的情况。

XXXX> select sql_id,sql_fulltext sqltext,executions from gv$sqlarea where sql_id='459f3z9u4fb3u';
SQL_ID        SQLTEXT                                                 EXECUTIONS
------------- ------------------------------------------------------- ----------
459f3z9u4fb3u select value$ from props$ where name = 'GLOBAL_DB_NAME'    4711110
459f3z9u4fb3u select value$ from props$ where name = 'GLOBAL_DB_NAME'  263653994

--//不知道为什么做一个记录。查询P3=13的情况(对应spfile文件).

SELECT sql_id, COUNT (*)
    FROM dba_hist_active_sess_history
   WHERE event = 'Disk file operations I/O' AND p3 = 13
GROUP BY sql_id;

SQL_ID            COUNT(*)
------------- ------------
2tp6sujs7jxhp            4
633r6yjw2810x            1
9wj2g5x2ggpya            1
103kaacv094jg            1
151x3u81xwq4b            1
dnk8dw55sjz3h            2
2xaxq1vhryghu            2
7 rows selected.

XXXX> @ &r/sql_id 2tp6sujs7jxhp
SQL_ID        SQLTEXT
------------- -----------------------------------------------------------------------------------
2tp6sujs7jxhp SELECT value FROM v$spparameter WHERE name = 'db_file_name_convert' and ordinal = 1
2tp6sujs7jxhp SELECT value FROM v$spparameter WHERE name = 'db_file_name_convert' and ordinal = 1

XXXX> @ &r/sql_id 633r6yjw2810x
SQL_ID        SQLTEXT
------------- -----------------------------------------------------------------------------------
633r6yjw2810x SELECT value FROM v$spparameter WHERE name = 'standby_file_management' AND sid='*'
633r6yjw2810x SELECT value FROM v$spparameter WHERE name = 'standby_file_management' AND sid='*'

--//感觉奇怪仅仅存在file close 操作的相关等待事件。