Show read internal indicates that reader is very slow in pass 2 . (22686)

Show read internal indicates that reader is in pass 2 and it is very slow. splex user in the database is executing select query with where clause containing rowid.

This can be due to a number of reasons and we need to collect timing debug to investigate.

sp_ctrl> set param sp_ord_debug_flag 0x01040800 (this is a Live Parameter and does not require restart).

Let it run for a 10-15 minutes and get the resulting ord.log.

To unset :
sp_ctrl> reset param sp_ord_debug_flag

Also check the sp_ord session in the database to see what sql it is executing and run an explain plan on this sql and get the details. Some times it can be due to the fact that the read process is taking a long time in fetching key values for a table, in doing so it has to query the database for the rows.

If it is doing query on a tables during slowness, then a way to speed up the query is to do a full table scan on such table so that the data block is placed in SGA. This is subject to the size of SGA being large enough so as not to starve others of the resources and the problem tables not being too large. After doing the full table scan, bounce reader. Reader should show improved performance.