"Read by other session" wait

Posted on 2010-08-25
Last Modified: 2013-12-19

Oracle on UNIX:
We have recently moved from 40 end users to 150. Since than a query over an annually partitioned table with over 120 million rows is giving us problems.
This query is accessing around 100 rows via a near unique index.
sometimes, near mid day, this query hangs on "read by other session" wait and instead of a 1 seconds response time it takes 3 hours. all of these queries that are executed afterwards are stuck as well.
The explain plan from the dba history tables shows that the index was used.
V$SESSION_LONGOPS does not show full scans or any other long operations.
V$SESSION_WAIT does not show a hot spot or a block that every one is waiting for not even an index.
Querying the V$SQL_BIND_DATA shows bind variables that when executed again, even during the problem period - execute in 1 second.
This query runs from Websphere via jdbc connection.

Any IDs besides hints or outlines for that query?

Any IDs besdes hints or outlines for that query?

Question by:peledc
  • 2

Expert Comment

ID: 33525634

Author Comment

ID: 33528010
Thanks Mustaccio, unfortunately, this is not the case.

Accepted Solution

sthompsonaap earned 500 total points
ID: 33528263
Sounds a lot like bind peeking issue.  Have you tried disabling bind peeking on the table?  You can use method_opt => 'FOR ALL COLUMNS SIZE 1' when gather stats to do so.  Basically it picks a good plan for the first value being passed but all the rest of the values do not work as well with the execution plan so they end up stepping on each other.

Expert Comment

ID: 33530357
>> peledc:   Thanks Mustaccio, unfortunately, this is not the case.

Not sure what you mean by this. The discussion that I referred to explains that "read by other session" wait is just another facet of waiting for physical I/O to complete, and the only way around it is to reduce I/O. May be you can subpartition the table, or cluster it, or tune your queries. May be you don't really need 150 concurrent user sessions and can live with shared connections (MTS).

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle SQL - Query help 7 68
Create Index on a Materialized View 5 35
Alternative to GTT for a temp table for further reuse in Oracle 8 35
Read XML values 8 42
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question