[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 948
  • Last Modified:

"Read by other session" wait

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
peledc
Asked:
peledc
  • 2
1 Solution
 
peledcAuthor Commented:
Thanks Mustaccio, unfortunately, this is not the case.
0
 
sthompsonaapCommented:
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
 
mustaccioCommented:
>> 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

Independent Software Vendors: 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!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now