Pauline, April 21, 2001 - 4:34 pm UTC
John, August 23, 2002 - 12:41 pm UTC
The most of my datafiles have the ratio of less than 2 for the blocks/read. According to the
document, I should set DB_FILE_MULTIBLOCK_READ_COUNT to a higher number. But the db has
the default number 8. why the I/O so bad?
TIA
August 23, 2002 - 2:11 pm UTC
huh?
"huh? "
A reader, August 23, 2002 - 2:52 pm UTC
What I asked was how do you think the i/o rating, which is less than 2 blocks per read. and How could I do better?
August 23, 2002 - 3:16 pm UTC
show me how you are getting this "rating", the huh stems from the fact that I don't really know what you are saying yet. Be a little more elaborate in your information.
do you actually DO operations (like full scans) that would do multi-block io? If not, then you are doing single block IOs (index reads and table access by rowid)
RE: Huh
Mark A. Williams, August 23, 2002 - 3:21 pm UTC
Force full table scans if you really want to get more blocks per read! Index accesses are typically single block reads. If your systems does alot of "access by index rowid" operations, your ratio of reads:blocks will be closer to 1:1, would it not? Why do you feel you need to have more blocks per read?
Unsolicted $0.02...
- Mark
John, August 23, 2002 - 3:53 pm UTC
The infor based on what:
I had done the b/estat on the 8.1.7.3 database:
SVRMGR> Rem I/O should be spread evenly accross drives. A big difference between
SVRMGR> Rem phys_reads and phys_blks_rd implies table scans are going on.
SVRMGR> select table_space, file_name,
2> phys_reads reads, phys_blks_rd blks_read, phys_rd_time read_time,
3> phys_writes writes, phys_blks_wr blks_wrt, phys_wrt_tim write_time,
4> megabytes_size megabytes,
5> round(decode(phys_blks_rd,0,0,phys_rd_time/phys_blks_rd),2) avg_rt,
6> round(decode(phys_reads,0,0,phys_blks_rd/phys_reads),2) "blocks/rd"
7> from stats$files order by table_space, file_name;
TABLE_SPACE FILE_NAME READS BLKS_READ READ_TIME WRITES BLKS_WRT WRITE_TIME MEGABYTES AVG_RT blocks/rd
------------------------------ ------------------------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
CIV_EXTERNAL /p01/oracle/RESUMIX/civ_external.dbf 5 5 1 3 3 0 52 .2 1
CIV_EXTERNAL /p01/oracle/RESUMIX/civ_external1.dbf 98 354 11 3 3 0 105 .03 3.61
CIV_EXTERNAL_IDX /p07/oracle/RESUMIX/civ_external_idx.dbf 3 3 0 3 3 0 52 0 1
CIV_EXTERNAL_IDX /p07/oracle/RESUMIX/civ_external_idx1.dbf 3 3 0 3 3 0 105 0 1
DATA /p05/oracle/RESUMIX/data01.dbf 3 3 0 3 3 0 21 0 1
RBS01 /p03/oracle/RESUMIX/rbs01.dbf 7 7 1 379 379 51 262 .14 1
RBS01 /p03/oracle/RESUMIX/rbs02.dbf 660 660 12 15076 15076 1905 1049 .02 1
RBS01 /p03/oracle/RESUMIX/rbs03.dbf 7 7 4 7 7 2 2097 .57 1
RESUMIX_DATA /p06/oracle/RESUMIX/RESUMIX_DA01.dbf 36800 44137 2032 2000 2000 217 210 .05 1.2
RESUMIX_DATA /p06/oracle/RESUMIX/RESUMIX_DA02.dbf 68744 86837 4940 6735 6735 940 262 .06 1.26
RESUMIX_DATA /p06/oracle/RESUMIX/RESUMIX_DA03.dbf 55963 86182 3177 2391 2391 368 524 .04 1.54
RESUMIX_DATA /p06/oracle/RESUMIX/RESUMIX_DA04.dbf 53812 83904 2416 2653 2653 363 524 .03 1.56
RESUMIX_DATA /p06/oracle/RESUMIX/RESUMIX_DA05.dbf 105890 194626 7968 5071 5071 786 1049 .04 1.84
RESUMIX_DATA /p06/oracle/RESUMIX/RESUMIX_DA06.dbf 21972 43982 1204 1158 1158 117 524 .03 2
RESUMIX_DATA /p06/oracle/RESUMIX/RESUMIX_DA07.dbf 25180 48964 1666 1538 1538 166 524 .03 1.94
RESUMIX_DATA /p06/oracle/RESUMIX/data_08.dbf 177 367 84 72 72 7 1049 .23 2.07
RESUMIX_IMAGE /p05/oracle/RESUMIX/RESUMIX_IM02.dbf 633 633 160 602 602 74 419 .25 1
RESUMIX_IMAGE /p05/oracle/RESUMIX/RESUMIX_IM08.dbf 164 164 59 146 146 13 1049 .36 1
RESUMIX_IMAGE /p05/oracle/RESUMIX/RESUMIX_IM09.dbf 124 124 39 87 87 7 1049 .31 1
RESUMIX_IMAGE /p05/oracle/RESUMIX/RESUMIX_IM10.dbf 13 13 6 3 3 0 1049 .46 1
RESUMIX_IMAGE /p06/oracle/RESUMIX/RESUMIX_IM01.dbf 852 852 217 852 852 101 524 .25 1
RESUMIX_IMAGE /p06/oracle/RESUMIX/RESUMIX_IM08.dbf 338 338 134 332 332 32 839 .4 1
RESUMIX_IMAGE /p07/oracle/RESUMIX/RESUMIX_IM03.dbf 1379 1379 570 1285 1285 137 1049 .41 1
RESUMIX_IMAGE /p07/oracle/RESUMIX/RESUMIX_IM04.dbf 1631 1631 640 1593 1593 170 1049 .39 1
RESUMIX_IMAGE /p07/oracle/RESUMIX/RESUMIX_IM05.dbf 1223 1223 393 1121 1121 109 1049 .32 1
RESUMIX_IMAGE /p07/oracle/RESUMIX/RESUMIX_IM06.dbf 119 119 36 119 119 15 524 .3 1
RESUMIX_IMAGE /p07/oracle/RESUMIX/RESUMIX_IM07.dbf 36 36 10 36 36 4 524 .28 1
RESUMIX_IMAGE /p07/oracle/RESUMIX/RESUMIX_IM09.dbf 15 15 6 3 3 0 1049 .4 1
RESUMIX_IMAGE /p07/oracle/RESUMIX/RESUMIX_IM10.dbf 12 12 7 4 4 0 2097 .58 1
RESUMIX_INDEX /p04/oracle/RESUMIX/RESUMIX05.dbf 30317 60782 9116 15879 15879 2538 1049 .15 2
RESUMIX_INDEX /p04/oracle/RESUMIX/RESUMIX_IN01.dbf 5121 7667 1843 2735 2735 364 210 .24 1.5
RESUMIX_INDEX /p04/oracle/RESUMIX/RESUMIX_IN02.dbf 5674 8158 2253 3364 3364 386 210 .28 1.44
RESUMIX_INDEX /p04/oracle/RESUMIX/RESUMIX_IN03.dbf 4547 9441 1492 2596 2596 337 210 .16 2.08
RESUMIX_INDEX /p04/oracle/RESUMIX/RESUMIX_IN04.dbf 9233 14739 3480 6457 6457 870 419 .24 1.6
RESUMIX_INDEX /p04/oracle/RESUMIX/RESUMIX_IN05.dbf 32202 34792 16506 30753 30753 5905 1049 .47 1.08
SOARS /p07/oracle/RESUMIX/soars.dbf 3 3 0 3 3 0 21 0 1
SYSTEM /p05/oracle/RESUMIX/system01.dbf 1022 1483 353 21 21 1 52 .24 1.45
SYSTEM /p05/oracle/RESUMIX/system02.dbf 1855 2549 542 182 182 52 210 .21 1.37
TEMP /p02/oracle/RESUMIX/temp02.dbf 430 4180 0 724 4071 0 524 0 9.72
TEMP /p02/oracle/RESUMIX/temp_01.dbf 554 5530 0 819 5510 0 524 0 9.98
USERS /p01/oracle/RESUMIX/users01.dbf 3 3 0 3 3 0 21 0 1
August 23, 2002 - 6:59 pm UTC
well, besides being "not very readable", b/estat are out of vogue. Statspack rules.
but you haven't answered the important question.
do you full scan. do you full scan alot. dropping all of your indexes will make that "ratio" go up -- but much like a cache hit ratio, that would be meaningless and counter productive in most cases.
Looks like TEMP is the only place we do multi-block IO meaning your OLTP application "resumix" is using 100% index access and trying to get multiblock IO from that would be similar to getting blood from a stone.
This blind "gotta tune these ratios" is something best left for the birds.
Thanks.
John, August 23, 2002 - 7:53 pm UTC
I am new to site. Need to figure how the application does. So I was not able to answer your question.
There is another performance index out from the report.txt. It is the time spent on read blocks. For the most my datafiles, they are in single digit ms per block( such 5ms/block). How do you think this performance index?
v$filestat
Reader, January 18, 2004 - 11:22 am UTC
Tom, what is the reason for v$filestat does not show info for redolog files on disk? Is there any special reason for this? Thanks.
January 18, 2004 - 1:28 pm UTC
because v$filestat is all about datafiles, not redo log files.
v$log
v$logfile
tell us about logfiles and the system statistics:
redo log space requests
redo log space wait time
redo log switch interrupts
and the system events:
log switch/archive
log file sequential read
log file single write
log file parallel write
log buffer space
log file switch (checkpoint incomplete)
log file switch (archiving needed)
log file switch (clearing log file)
switch logfile command
log file switch completion
log file sync
tell us about logs....
Milind, December 23, 2008 - 12:22 am UTC
Dear Tom,
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 122755896 bytes
Fixed Size 453432 bytes
Variable Size 109051904 bytes
Database Buffers 12582912 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> show parameter timed_statistics
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
timed_statistics boolean FALSE
SQL> set linesize 200
SQL> column name format a40
SQL> SELECT phyrds, phywrts, d.name, f.readtim, f.writetim
2 FROM v$datafile d, v$filestat f
3 WHERE d.file#=f.file# ORDER BY d.name;
PHYRDS PHYWRTS NAME READTIM WRITETIM
---------- ---------- ---------------------------------------- ---------- ----------
3 1 C:\ORACLE\ORADATA\ORA92\CWMLITE01.DBF 0 0
3 1 C:\ORACLE\ORADATA\ORA92\DRSYS01.DBF 0 0
43 1 C:\ORACLE\ORADATA\ORA92\EXAMPLE01.DBF 21 0
3 1 C:\ORACLE\ORADATA\ORA92\INDX01.DBF 0 0
5 1 C:\ORACLE\ORADATA\ORA92\ODM01.DBF 2 0
3 1 C:\ORACLE\ORADATA\ORA92\OEM_REPOSITORY.O 0 0
RA
692 1 C:\ORACLE\ORADATA\ORA92\SYSTEM01.DBF 827 0
3 1 C:\ORACLE\ORADATA\ORA92\TOOLS.DBF 0 0
3 1 C:\ORACLE\ORADATA\ORA92\TOOLS01.DBF 0 0
PHYRDS PHYWRTS NAME READTIM WRITETIM
---------- ---------- ---------------------------------------- ---------- ----------
22 1 C:\ORACLE\ORADATA\ORA92\UNDOTBS01.DBF 37 0
3 1 C:\ORACLE\ORADATA\ORA92\USERS01.DBF 0 0
7 1 C:\ORACLE\ORADATA\ORA92\XDB01.DBF 5 0
3 1 C:\ORACLE\ORADATA\ORA92\XYZ.DBF 0 0
13 rows selected.
Reference manual (Oracle 9i) says that readtim and writetim columns of v$filestat contain 0 if timed_statistics parameter is set to FALSE. However in above output I see the values for these columns. What could be reason?
Warm regards.
Milind