The IO capability of the database, which affects the performance of the query, especially the performance of FTS, so for Oracle, this will affect the selection of the execution plan. Physical READS and IO in the database are not a concept. Physical reading statistics in the database is blocks, and the operating system is IO Requests, and the request may be 128K / 256K / 1024K and other data. Of course, may be dozens of k or Less, depending on the requirements of the database. Io Request, simple point is Table Blocks / DB_FILE_MULTIBLOCK_READ_COUNT.
The number and performance of the database's IO, actually related factors include: 1: Different OS hardware, each IO has a limit value, such as the HP UNIX limit value of 256K, sun may be up to 1 - 8m, if db_file_multiblock_read_count * Block_size> This limit is naturally split to more than one IO Request 2: One Io Request cannot span the extent boundary, so the extent size also affects IO Request 3: For file systems, continuous block_id does not mean continuous continuous OS Block Block, which affects the efficiency of the read disks. 4: For the devices such as Disk Array, for the equipment for RAID, for many storage devices, IO has reorganized the reorganization when they arrive here from OS, and there is Cache, which price It is not good to evaluate 5: For AIO, the read and write request of OS is reorganized, more complex
Let's do a DB_FILE_MULTIBLOCK_READ_COUNT and EXTENTS boundaries affect IO test
Test SQL> Drop Table T under Windows; Table has been discarded. SQL> CREATE TABLE T TABLESPACE TEST AS SELECT * FROM DBA_OBJECTS; Table has been created. SQL> INSERT INTO T Select * from T; 6331 lines have been created. SQL> Commit; submit completion. SQL> SELECT FILE_ID, EXTENT_ID, Block_ID, Blocks from DBA_EXTENTS WHERE OWNER = 'Test' and segment_name = 't'; file_id eXtent_id block_id blocks -------------------- ---------- ---------- 4 0 9 324 1 41 324 2 73 324 3 105 324 4 137 32SQL> ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT = 16; Sessions have changed. SQL> ALTER Session Set Events '10046 Trace Name Context Forever, Level 12'; session has changed.
SQL> SQL> Select Count (*) from T; Count (*) ---------- 12662SQL> EXIT from Oracle9i Enterprise Edition Release 9.2.0.1.0 - ProductionWith The Partitioning, OLAP AND ORACLE DATA MINING OPTIONSJSERVER Release 9.2.0.1.0 - Production Interrupt Trace Parsing in Cursor # 1 LEN = 22 Dep = 0 UID = 23 OCT = 3 LID = 23 TIM = 136424432868 HV = 2199322426 AD = '7b3e0898'select count (*) from TEND Of StmtParse # 1: C = 0, E = 21795, P = 0, Cr = 0, Cu = 0, MIS = 1, R = 0, DEP = 0, OG = 4, TIM = 136424432851BINDS # 1: EXEC # 1 : c = 0, E = 4015, P = 0, Cr = 0, Cu = 0, MIS = 0, R = 0, DEP = 0, OG = 4, TIM = 136424456603Wait # 1: Nam = 'SQL * Net Message To Client 'ELA = 11 P1 = 1111838976 P2 = 1 p3 = 0wait # 1: Nam =' DB File Scattered Read 'ELA = 33200 P1 = 4 P2 = 10 P3 = 16wait # 1: Nam =' DB File Scattered Read 'ELA = 21724 p1 = 4 p2 = 26 p3 = 15wait # 1: nam = 'db file scattered read' ELA = 3197 P1 = 4 p2 = 41 p3 = 16wait # 1: nam = 'db file scattered read' ELA = 11001 P1 = 4 p2 = 57 p3 = 16Wait # 1: nam = 'db file scattered read' ELA = 2687 p1 = 4 p2 = 73 p3 = 15FETCH # 1: c = 40057, E = 130616, P = 78, Cr = 160, Cu = 0, MIS = 0, R = 1, DEP = 0, og = 4, TIM = 136424598085sql> Drop Table T; the table has been discarded. SQL> CREATE TABLE T TABLESPACE USERS AS SELECT * FROM DBA_OBJECTS; the table has been created. SQL> INSERT INTO T Select * from T; 6331 lines have been created. SQL> / has been created 12662 lines. SQL> Commit; submit completion.
SQL> SELECT FILE_ID, EXTENT_ID, Block_ID, Blocks from DBA_EXTENTS WHERE OWNER = 'Test' and segment_name = 't'; file_id eXtent_id block_id blocks -------------------- -------------------- 3 0 33 83 1 73 83 2 81 83 3 89 83 4 169 83 5 177 83 6 185 83 7 193 83 8 201 83 9 209 83 10 217 8File_id Extent_id Block_ID Blocks ---------------------------- ---------- 3 11 225 83 12 233 83 13 241 83 14 249 83 15 257 83 16 12169 1283 17 12297 128 18 lines have been selected. SQL> ALTER Session SET DB_FILE_MULTIBLOCK_READ_COUNT = 20; sessions have been changed. SQL> ALTER Session Set Events '10046 Trace Name Context Forever, Level 12'; session has changed.
SQL> SQL> SELECT Count (*) from T; count (*) ---------- 25324SQL> EXIT from Oracle9i Enterprise Edition Release 9.2.0.1.0 - ProductionWith The Partitioning, OLAP AND ORACLE DATA MINING OPTIONSJSERVER Release 9.2.0.1.0 - Production Interrupt C: /> Parsing in Cursor # 1 LEN = 22 Dep = 0 UID = 23 OCT = 3 LID = 23 TIM = 136872504466 HV = 2199322426 AD = '7b3e0898'select count (* ) from Tend of StmtParse # 1: c = 0, E = 1159, P = 0, Cr = 0, Cu = 0, MIS = 1, R = 0, DEP = 0, OG = 4, TIM = 136872504449BINDS # 1: Exec # 1: C = 0, E = 4618, P = 0, Cr = 0, Cu = 0, MIS = 0, R = 0, DEP = 0, OG = 4, TIM = 136872527767Wait # 1: Nam = 'SQL * Net Message to Client 'ELA = 10 p1 = 1111838976 P2 = 1 P3 = 0Wait # 1: Nam =' DB File Scattered Read 'ELA = 27197 P1 = 3 P2 = 36 P3 = 5Wait # 1: Nam =' DB File Scattered Read 'ELA = 23246 P1 = 3 p2 = 73 p3 = 8wait # 1: Nam =' DB File Scattered Read 'ELA = 1346 P1 = 3 P2 = 82 P3 = 7Wait # 1: Nam =' DB File Scattered Read 'ELA = 3372 p1 = 3 p2 = 89 p3 = 8Wait # 1: nam = 'db file scattered read' ELA = 17965 P1 = 3 p2 = 170 p3 = 7Wait # 1: nam = 'db file scattered read' ELA = 2460 p1 = 3 P2 = 177 p3 = 8wait # 1: nam = 'DB file scattered read' ELA = 1403 p1 = 3 p2 = 186 p3 = 7Wait # 1: nam = 'DB file scattered read' ELA = 1759 p1 = 3 p2 = 193 p3 = 8Wait # 1: Nam = 'DB File Scattered Read' ELA = 1444 P1 = 3 p2 = 202 p3 = 7Wait # 1: nam = 'db file scattered read' ELA = 1454 p1 = 3 p2 = 209 p3 = 8wait # 1: nam = 'DB file scattered read' ELA = 1053 p1 = 3 p2 = 218 p3 =
5 By the top P1 File_ID P2 Block_ID P3 Blocks, I can see the number of IO and related blocks. In fact, IO is related to this parameter, but also related to Extent, the IO cannot be connected to EXTENT to: Oracle9i Enterprise Edition Release 9.2.0.1. 0 - ProductionWith The Partitioning, OLAP AND ORACLE DATA MINING OPTIONSJSERVER Release 9.2.0.1.0 - Productionsql> Insert Into T Select * from T; 25324 rows have been created. SQL> Commit; submit completion. SQL> SELECT FILE_ID, EXTENT_ID, Block_ID, Blocks from DBA_EXTENTS WHERE OWNER = 'Test' and segment_namet '; file_id eXtent_id block_id blocks ---------- ------------------------------------------------ -------------- 3 0 33 83 1 73 83 2 81 83 3 89 83 4 169 83 5 177 83 6 185 83 7 193 83 8 201 83 9 209 83 10 217 8File_ID Extent_id block_id blocks ---------- ------------------ ---------- 3 11 225 83 12 233 83 13 241 83 14 249 83 15 257 83 16 12169 1283 17 12297 1283 18 12425 1283 19 12553 128 Has selected 20 lines. SQL> ALTER Session SET DB_FILE_MULTIBLOCK_READ_COUNT = 20; sessions have been changed. SQL> ALTER Session Set Events '10046 Trace Name Context Forever, Level 12'; session has changed.
SQL> Select Count (*) from T; Count (*) ---------------------------------------------------------------------------------- .0.1.0 - Production interrupt C: /> ====================== Parsing in Cursor # 1 g = 22 dep = 0 uid = 23 OCT = 3 LID = 23 TIM = 137237788846 HV = 219322426 AD = '7b3e0898'select count (*) from test of stmtpival # 1: c = 0, E = 230, P = 0, Cr = 0, Cu = 0, MIS = 0 , r = 0, DEP = 0, og = 4, TIM = 137237788829BINDS # 1: EXEC # 1: C = 0, E = 3961, P = 0, Cr = 0, Cu = 0, MIS = 0, R = 0 , DEP = 0, g = 4, TIM = 137237812525Wait # 1: Nam = 'sql * net message to client' ELA = 10 p1 = 1111838976 p2 = 1 p3 = 0wait # 1: nam = 'DB file scattered read' ELA = 29105 p1 = 3 p2 = 36 p3 = 5Wait # 1: nam = 'db file scattered read' ELA = 23441 P1 = 3 p2 = 73 p3 = 8Wait # 1: Nam = 'DB File Scattered Read' ELA = 1372 P1 = 3 P2 = 82 p3 = 7Wait # 1: nam = 'DB file scattered read' ELA = 1549 p1 = 3 p2 = 89 p3 = 8Wait # 1: nam = 'db file scattered read' ELA = 19690 P1 = 3 p2 = 170 P3 = 7Wait # 1: nam = 'DB file scattered read' ELA = 2588 P1 = 3 p2 = 177 p3 = 8Wait # 1: Nam = 'DB File Scattered read 'ELA = 1428 P1 = 3 p2 = 186 P3 = 7Wait # 1: Nam =' DB File Scattered Read 'ELA = 1479 P1 = 3 p2 = 193 P3 = 8Wait # 1: Nam =' DB File Scattered Read 'ELA = 1357 p1 = 3 p2 = 202 p3 = 7Wait # 1: nam = 'DB file scattered read' ELA = 1487 P1 = 3 p2 =
