OraFAQ Forum: Performance Tuning » Simple SQL with High Concurrency Wait Time (cursor: pin S wait on X)

Home » RDBMS Server » Performance Tuning » Simple SQL with High Concurrency Wait Time (cursor: pin S wait on X) (Oracle 10gR2 on AIX 6.1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Simple SQL with High Concurrency Wait Time (cursor: pin S wait on X) [message #518706]

Fri, 05 August 2011 09:22

eclipsis
Messages: 14
Registered: May 2011

Junior Member

Hello,

In our system, we have a PL/SQL process which runs to move data from a source table to a destination table. During the move, many validations and several calculations are performed. These validations and calculations mostly use smallish look-up tables. They either lookup a value based on key fields, or do a count to make sure a value is valid. I have checked the execution plan for these queries and they are all good. Most validation make use of only the index and don't even need to touch the data itself. All of the lookups/calculations also make use of the index.

We have 1000 partitions in the source and destination table. The above PL/SQL process takes the partition name as a parameter and processes the corresponding data. We run 30 of these jobs in parallel.

When I monitor these sessions, I see a significant amount of wait time between two wait events - "cursor: pin S wait on X" and "latch: cache buffers chains". I am focusing on the first as it has more time than the second.

Below is an example of one of the calculations. This is the SQL_TEXT from V$SQL:

SELECT col_name 
FROM LDS_TABLE_NAME 
WHERE key1 = SUBSTR (:B4 , 1, 3) 
AND key2 = SUBSTR (:B3 , 1, 3) 
AND key3 = :B2 
AND :B1 BETWEEN EFF_DT AND DISC_DT

The query itself is not a problem. The plan is good (using index) and execution time is very good. But when 30 are running together, we start to see issues. As the processes are running, I can watch v$sql and see concurrency wait time increase as well as parses.

   FETCHES EXECUTIONS END_OF_FETCH_COUNT PARSE_CALLS BUFFER_GETS CONCURRENCY_WAIT_TIME USER_IO_WAIT_TIME   CPU_TIME
---------- ---------- ------------------ ----------- ----------- --------------------- ----------------- ----------
   2112817    2112499            2112850         102    29784490               1507725          38037686  439643287

And run again about 60 seconds later I see

   FETCHES EXECUTIONS END_OF_FETCH_COUNT PARSE_CALLS BUFFER_GETS CONCURRENCY_WAIT_TIME USER_IO_WAIT_TIME   CPU_TIME
---------- ---------- ------------------ ----------- ----------- --------------------- ----------------- ----------
   2277899    2277586            2277934         108    32105322               1607383          39232922  468924597

All the while, the resource utilization is very low, making this seem like a huge bottleneck. I think it comes down to the parsing - parsing causes one session to put a lock while the others wait. So the question is, why is this statement being parsed each time?

Any tips or suggestions are greatly appreciated!