Mysterious sql problem

I've got an application thread that is sitting on the same statement for 3-10 hours. When the exact same statement is executed from dbaccess it returns in about 5 minutes. The dbaccess test has been tried with all available connection types, all with similar quick results. All sqexplains from dbaccess and from within the application show the same access path. Outside of the application, this statement is performing normally. Outside of this statement, the application is performing normally. Onstat -u shows that the application thread has consistent but very slow read progression, the lock count is high but remains unchanged, the write count is unchanged, and the vast majority of time is spent in critical section (flags --BPX--). A typical stack trace looks something like. Stack for thread: 7167 sqlexec base: 0xc0000000c961e000 len: 69632 pc: 0x0000000000000000 tos: 0xc0000000c96219e0state: running vp: 3 0x40000000003650b0 oninit :: resume + 0x0 sp=0xc0000000c96219e0 0x40000000008e6137 oninit :: yield_processor_mvp + 0x1f7 sp=0xc0000000c96218b0 delta_sp=304 0x400000000034fe83 oninit :: mt_yield + 0xc3 sp=0xc0000000c9621710 delta_sp=416 0xc0000000be0bfda8 ?unknown? :: ?unknown? + 0x0 sp=0xc0000000c9621660 delta_sp=176 This application has been running for a bit over a year and the issue first surfaced 9 days ago. For a week the statement took hours but eventually finished. Yesterday, the application ran for over 12 hours before dieing due to a long transaction abort. I've had a tech support case open for over a week and don't see a resolution in sight. So, I'm throwing this out to the group. Has anyone seen anything like this before? Any ideas on why the thread is spending massive amounts of time in critical section when it is not currently modifying data? Any other ideas? Thanks, Dave Griffen

Forgot to mention, IDS 9.30.FC3 HP-UX 11.11

Might be useful if you'd give the statement you're having problems with... But, what're you doing? Inserting, updating, deleting? What's the isolation set to, what's the lock mode set to, in a cursor, not in a cursor, is the cursor declared with hold? When's the last time you updated statistics? How many extents are there? Is there an index (on whatever you're doing)? How big are the indexes? What does "set explain" show you? Dave Griffen wrote: >I've got an application thread that is sitting on the same statement for >3-10 hours. When the exact same statement is executed from dbaccess it >returns in about 5 minutes. The dbaccess test has been tried with all >available connection types, all with similar quick results. All sqexplains >from dbaccess and from within the application show the same access path. > >Outside of the application, this statement is performing normally. Outside >of this statement, the application is performing normally. > >Onstat -u shows that the application thread has consistent but very slow >read progression, the lock count is high but remains unchanged, the write >count is unchanged, and the vast majority of time is spent in critical >section (flags --BPX--). > >A typical stack trace looks something like. > >Stack for thread: 7167 sqlexec> base: 0xc0000000c961e000 > len: 69632 > pc: 0x0000000000000000 > tos: 0xc0000000c96219e0 >state: running > vp: 3 > >0x40000000003650b0 oninit :: resume + 0x0 sp=0xc0000000c96219e0 >0x40000000008e6137 oninit :: yield_processor_mvp + 0x1f7 >sp=0xc0000000c96218b0 delta_sp=304 >0x400000000034fe83 oninit :: mt_yield + 0xc3 sp=0xc0000000c9621710 >delta_sp=416 >0xc0000000be0bfda8 ?unknown? :: ?unknown? + 0x0 sp=0xc0000000c9621660 >delta_sp=176 > > >This application has been running for a bit over a year and the issue first >surfaced 9 days ago. For a week the statement took hours but eventually >finished. Yesterday, the application ran for over 12 hours before dieing >due to a long transaction abort. > >I've had a tech support case open for over a week and don't see a resolution >in sight. So, I'm throwing this out to the group. Has anyone seen anything >like this before? Any ideas on why the thread is spending massive amounts >of time in critical section when it is not currently modifying data? Any >other ideas? > >Thanks, >Dave Griffen > > > > >