"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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

717 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