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?