[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 操作的相关等待事件。
