ORACLE detects rollback segment contention, table space I/O ratio

1, to detect the rollback segment contention

Select sum (gets), sum (Waits), sum (Waits)/sum (gets) from V$rollstat;

The sum (Waits) value should be less than 1% of the sum (gets) value

The contention of the rollback segment will degrade the performance of the system.

Gets represents the number of times the volume segment was accessed
Waits represents the number of processes that the process waits to return to the volume segment
If the ratio of gets to waits is greater than 2% indicates the existence of competition problems

The number of times the Select Rn.name,rs.gets is accessed, rs.waits waits for the rollback segment block (rs.waits/rs.gets) *100 the hit rate from V$rollstat rs,v$rollname RN

2, monitor the table space I/O ratio

Select Df.tablespace_name name,df.file_name "file", F.phyrds pyr,f.phyblkrd pbr,f.phywrts pyw, F.phyblkwrt PBW
From V$filestat F, dba_data_files df where f.file# = df.file_id order by Df.tablespace_name;

--Monitor the total number of I/O reads in the table space
Select SUM (f.phyrds) Pyr from V$filestat F, dba_data_files df where f.file# = df.file_id;

--Monitor the total number of I/O block reads in the table space
Select SUM (F.PHYBLKRD) PBR from V$filestat F, dba_data_files df where f.file# = df.file_id;

--Monitor the total number of I/O writes in the table space
Select SUM (f.phywrts) pyw from V$filestat F, dba_data_files df where f.file# = df.file_id;

--Monitor the total number of I/O block writes in the table space
Select SUM (f.phyblkwrt) PBW from V$filestat F, dba_data_files df where f.file# = df.file_id;

Number of physical reads (v$filestat.phyrds)
Number of physical writes (V$filestat.phywrites)
Phyrds:number of physical reads done
Phyblkrd:number of physical blocks read

3, monitor the file system I/O ratio

Select substr (a.file#,1,2) "#", substr (a.name,1,30) "name", A.status, A.bytes, B.phyrds, B.phywrts
From V$datafile A, v$filestat b where a.file# = b.file#;

=========================================================

In order to deepen understanding, a simple test was done

Code:--------------------------------------------------------------------------------
Sql> CREATE TABLE toms (str varchar2 (64));

Table has been created.

sql> INSERT into TOMS values (' aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa ');

1 lines have been created.

Sql> commit;

Submit completed.

Sql> select * from Toms;

Str
----------------------------------------------------------------
Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

sql> INSERT INTO TOMS SELECT * from Toms;

1 lines have been created.

Sql>/

2 lines have been created.

Sql>/

4 lines have been created.

Sql>/

8 lines have been created.

Sql>/

16 lines have been created.

Sql>/

32 lines have been created.

Sql>/

.......

Sql>/

8192 lines have been created.

Sql> commit;

Submit completed.

Sql> Select COUNT (*) from Toms;

COUNT (*)
----------
16384

sql> shutdown immediate;
The database has been closed.
The database has been unloaded.
The ORACLE routine has been closed.
Sql> Startup
The ORACLE routine has started.

Total System Global area 89201304 bytes
Fixed Size 453272 bytes
Variable Size 67108864 bytes
Database buffers 20971520 bytes
Redo buffers 667648 bytes
Database loading complete.
The database is already open.

Sql> alter system set DB_FILE_MULTIBLOCK_READ_COUNT=16;

Sql> Select Phyrds,phyblkrd from V$filestat where file#=8;

Phyrds PHYBLKRD
---------- ----------
4 4

Sql> Select COUNT (*) from Toms;

COUNT (*)
----------
16384

Sql>
Sql> Select Phyrds,phyblkrd from V$filestat where file#=8;

Phyrds PHYBLKRD
---------- ----------
17 191

Sql> select 17-4,191-4 from dual;

17-4 191-4
---------- ----------
13 187

Sql> select 187/13 from dual;

187/13
----------
14.3846153

Sql>
Sql> Select Phyrds,phyblkrd from V$filestat where file#=8;

Phyrds PHYBLKRD
---------- ----------
17 191

Sql> alter system set DB_FILE_MULTIBLOCK_READ_COUNT=64;

The system has changed.

Sql> Select COUNT (*) from Toms;

COUNT (*)
----------
16384

Sql>
Sql> Select Phyrds,phyblkrd from V$filestat where file#=8;

Phyrds PHYBLKRD
---------- ----------
20 377

Sql> Select Phyrds,phyblkrd from V$filestat where file#=8;

Phyrds PHYBLKRD
---------- ----------
20 377

Sql> Select (377-191)/3 from dual;

(377-191)/3
-----------
62

Sql> alter system set db_file_multiblock_read_count=128;

The system has changed.

Sql> Select COUNT (*) from Toms;

COUNT (*)
----------
16384

Sql> Select Phyrds,phyblkrd from V$filestat where file#=8;

Phyrds PHYBLKRD
---------- ----------
22 561

Sql> Select (561-377)/2 from dual;

(561-377)/2
-----------
92

Sql>