[Oracle] performance tuning instance-readbyothersession

Event	Waits	Time (s)	Avg wait (MS)	% DB time	Wait Class
Read by other session	4,914,569	9,987	2	45.17	User I/O
Db file sequential read	3,176,031	7,473	2	33.80	User I/O
DB CPU		2,128		9.62
Db file scattered read	4,104,747	721	0	3.26	User I/O
Db file parallel read	10,590	693	65	3.13	User I/O

Starting at three o'clock P.M. every day these days, developers are slow to respond to Oracle. The first wait event is read by other session.
Top 5 Timed Foreground Events
Read by other session is defined as follows:

Read by other session Definition: When information is requested from the database, Oracle will first read the data from disk into the database buffer cache. if two or more sessions request the same information, the first session will read the data into the buffer cache while other sessions wait. in previous versions this wait was classified under the "buffer busy waits" event. however, in Oracle 10.1 and higher this wait time is now broken out into the "read by other session" wait event. excessive waits for this event are typically due to several processes repeatedly reading the same blocks, e.g. specified sessions scanning the same index or parameter Ming full-table scans on the same table. tuning this issue is a matter of finding and eliminating this contention. confio concludes with a summary that "read by other session waits" are very similar to buffer busy waitsWhen a session waits on the "read by other session" event, it indicates a wait for another session to read the data from disk into the Oracle buffer cache. if this happens too often the performance of the query or the entire database can suffer. typically this is caused by contention for "hot" blocks or objects so it is imperative to find out which data is being contended. once that is known this document lists several alternative methods for solving the issue. conclusion: Two or more sessions simultaneously need to load the objects in the hard disk into the data buffer. When one session loads the objects, the other sessions will be in the read by other session waiting state; this is a hot block phenomenon that oracle 10g is separated from the buffer busy waits of oracle 9i.

As explained above, if read by other session appears along with db file sequential read, it usually indicates that there is an index hot block.

The SQL statement that causes index hot blocks is as follows:

 select count(*) from p95169.order_info o  where o.proxyuseruuid =:1 and proxyuseruuid is not null and proxyuseruuid <> 0 and o.ORDERSTATE in ( 0, 1, 3, 2008, 2009, 3008, 3009, 5002, 5003, 5004, 5006, 7001, 7003, 7002, 7004, 7006 )  and o.ORDERCREATEDTIME >= to_char(sysdate - 15 , 'yyyyMMddHH24mmss') and o.ORDERCREATEDTIME <= to_char(sysdate , 'yyyyMMddHH24mmss')  and o.confirmstate in ( 1 ) and o.paystate in ( 1, 0 )  and o.CLINICALDATE <= to_char(sysdate, 'yyyyMMDD') and o.sourceplatid in ( 20 )

By analyzing the execution plan, we can see that it uses the following indexes:

CREATE INDEX "P95169"."IDX_ORDER_HOS_STAT_CLINICAL" ON "P95169"."ORDER_INFO" ("CLINICALDATE" DESC, "ORDERSTATE", "HOSPITALUUID", "ORDERUUID", "RESERVETIMERANGE", "PATIENTNAME", "CONFIRMSTATE", "SOURCEPLATID", "SEEFLAG")

According to the index prefix principle, we can know that the fields used by the Appeal index are the first two columns, and the filtering conditions of the first two columns in the where clause are fixed, if multiple such SQL statements are executed at the same time, the index hot block will be generated, that is, the read by other session will be generated.
We can see that the field changed in the where clause of this SQL statement is proxyuseruuid. Therefore, re-create an index that contains this field to avoid index hot blocks:

CREATE INDEX "P95169"."ORDER_INFO_PROXY" ON "P95169"."ORDER_INFO" ("PROXYUSERUUID", "CLINICALDATE", "ORDERCREATEDTIME")

Now, the problem is solved.