I. Although the SGA component can be adjusted to minimize the occurrence of physical I/O, it is inevitable that disk I/O will be generated during some activities. Note that the I/O activity in the memory is logical, and the I/O activity on the disk is physical. Logical I/O is several thousand times faster than physical I/O. Ii. Source of disk I/O generation: 1. Database writer (dbwo) -- write data files from the cache area in the database buffer cache to the database. 2. database writer (dbwo) -- writes data to the rollback segment to maintain read consistency. user server process -- reads data blocks to 4.log writer (lgwr) in database buffer cache -- writes transaction recovery information from redo log buffer to online redo log 5. archiver (arc0) -- reads the content of the redo log and writes it to the archiving target. 6. temporary large sort write disk operation 3. adjust the two targets of physical I/O: 1. appropriate SGA size 2. perform physical I/O 4 as quickly as possible. increase the physical structure of the database to be adjusted for disk I/O. data File 2. dbwo 3. data block of a single segment 4. sort operations and temporary segments 5. rollback segment 5. select S. file # | 'D' "file # + type", D. name "datafile", S. phyrds, S. phywrts, S. avgiotim, S. miniotim, S. maxiowtm, S. maxiortmfrom v $ filestat S, V $ datafile dwhere S. file # = D. file # unionselect S. file # | 'T' "file # + type", T. name "datafile", S. phyrds, S. phywrts, S. avgiotim, S. miniotim, S. maxiowtm, S. maxiortmfrom v $ filestat S, V $ tempfile twhere S. file # = T. file # order by 1phyrds -- number of physical reads on the data file phywrts -- number of physical writes on the data file avgiotim -- average I/O time miniotim -- Minimum Cost time maxiowtm -- the maximum time it takes for a write operation maxiortm -- the maximum time it takes for a read operation. What we can do is (1 Do not place non-Oracle files on the same disk as Oracle files to avoid potential contention for disk resources! (2) create a local management tablespace (LMT locally managed tablespace) (3) balance the data file I/O-do not place the database segments in a tablespace, such as system. in addition, if possible, place high-frequency I/O data files on different disks or controllers (4) Adjust init. db_file_multiblock_read_count of the ora parameter -- specifies the maximum number of data blocks read by a user server process during full table scan. The default value is 8 select name, value from V $ sysstat where name = 'table scans (long tables) '-- if the statistics are large, full table scans are often performed. Increasing this parameter is expected. dbw0 performance adjustment dbw0 is responsible for writing data files from the cache area in the database buffer cache to the database. Because the data file may reside on a slow device or the efficiency of dbw0 writing dirty buffer is not high, it becomes a waiting event. Select event, total_waits, average_wait from V $ system_event where event in ('buffer busy waits ', 'db file parallel write', 'free buffer waits', 'Write complete waits ') -- If you find that there are too many waits, you may need to adjust the (1) parameter dbwr_io_slaves -- start the database writer subordinate process. The default value is 0 (2) parameter db_writer_processes -- start the additional database writer process. Compared with dbwr_io_slaves, the default value of dbwo is 1 and the maximum value is 10. adjust the segment I/ooracle storage architecture-the database contains one or more tablespaces-The tablespace uses data files to store segments-the segments consist of one or more ranges-the range consists of consecutive Oracle Blocks composition -- o A racle block consists of consecutive operating system blocks. (1) The system and temp tablespaces use the primary block. The db_block_size parameter specifies the block size for other tablespaces by using the blocksize keyword, if this parameter is not specified, the size of the primary block (2) ranges from create table col_cust (cust_id varchar2 (20) to cust_name varchar2 (20 )) pctfree 20 pctused 30 inittrans 5 Storage (initial 250 K next 250 K pctincrease 0 freelists 1) tablespace aap1_data; -- in the preceding SQL section, the value range is 32*8 KB/block = 250 kpctfree 20. Each block reserves 20% of the space to save row update information. (3) do not place application tables and indexes in the system tablespace. (4) set a proper range size through the db_file_multiblock_read_count parameter. The disadvantage is that the tablespace space may be wasted, but the number of reads to I/O may be reduced (5) set reasonable block size 8. adjusting the sorting I/o will result in the sorting operation -- order by--group by--select distinct--Union--intersect--minus--analyze--create index (1) sort sort_area_size in the memory whenever possible (the default value is not recommended) sort_area_retained_sizepga_aggregrate_tagetworkarea_size_policyselect mem. name, mem. value/(disk. value + mem. value) "In-memory sort ratio" from V $ sysstat MEM, V $ sysstat diskw Here mem. name = 'sorts (memory) 'and disk. name = 'sorts (Disk) 'it is recommended to sort more than 95% in memory (2) reduce or avoid disk sorting (a) Union all replace Union to avoid intersect/minus/distinct (B) add the nosort option (3) when using index (c) Create index for the columns referenced by order by and group) use the following statement to query whether a tablespace is temporary or permanent select tablespace_name, contents from dba_tablespaces specifies a temporary tablespace as the default temporary tablespace of all users. Alter database default temporary tablespace temp; the default temporary tablespace is systemselect sess. username, SQL. SQL _tex T, sort. blocksfrom v $ session sess, V $ sqltext SQL, V $ sort_usage sortwhere sess. serial # = sort. session_numand sort. sqladdr = SQL. addressand sort. sqlhash = SQL. hash_valueand sort. blocks> 200; The preceding statement can be used to query SQL statements that cause large sorting. Then, you can create an index or rewrite the statement to eliminate 9. adjust the rollback segment I/O when the user starts a DML transaction, the original version of the modified data is cached in the database buffer cache of SGA. Copies of these buffers are also written to a rollback segment. Oracle uses rollback segment to store the data pre-version generated by the application user performing DML operations. The rollback segment is stored in a tablespace. The rollback segment is also called the Undo segment ). the purpose of the previous version is as follows: 1. restore original data when rollback 2. before commit, other users were provided to access the original data and provided to modify the read consistency of the data. undo uncommitted transactions when an instance fails
