Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

"Read by other session" wait

Posted on 2010-08-25
4
Medium Priority
?
945 Views
Last Modified: 2013-12-19
Hi,

Oracle 10.2.0.4 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.
ALTER SYSTEM FLUSH SHARED POOL helps.
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?

Thanks
0
Comment
Question by:peledc
[X]
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
4 Comments
 
LVL 8

Expert Comment

by:mustaccio
ID: 33525634
0
 
LVL 1

Author Comment

by:peledc
ID: 33528010
Thanks Mustaccio, unfortunately, this is not the case.
0
 
LVL 1

Accepted Solution

by:
sthompsonaap earned 2000 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.
0
 
LVL 8

Expert Comment

by:mustaccio
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).
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

618 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