Solved

"Read by other session" wait

Posted on 2010-08-25
4
907 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
  • 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 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.
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
performance tunning sql insert - challenging one 2 42
ORA-01008: not all variables bound. 6 37
Oracle Pivot Question 8 43
selective queries 7 21
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

948 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now