Oracle Database Discussions

Hi, please post it here using proper tags: {noformat}

code here

{noformat}

I would run snapper on that session during normal operation and during the slowness and post the results here. This should give additional info about what's going on here (cursor flushes, hard parses for example).

Additonally, you could run this just when you kick your code running after restart and sample this every few second to see whether the number of child cursors is growing:

SELECT COUNT(*) FROM v$sql WHERE sql_id = '&sql_id_of_your_query';

The hypothesis here is that the slowness happens because the on-commit-refreshes (or bind variables in inserts (you're using binds, right?)) cause existing cursors to be unusable for new executions and new child cursors are constantly parsed under the parent. And this causes the next library cache lookups take longer and longer and when a new child cursor has to be compiled again, finding memory from shared pool gets harder (and causes more and more chunk flushes during memory search).

This is just a hypothesis based on my previous experience with similar problems, but there's no way to be sure without gathering additional data, so I'd run the query above and snapper on the session to get an execution profile.

--
Tanel Poder
http://tech.e2sn.com
http://blog.tanelpoder.com

Hi,


You can see my action plan

library cache: mutex X
According to me Latches are replaced by Mutex not mutex replaced by latch.

How to analyze the mutex contention?

Please generate the AWR report for the duration of 60min when you see mutex x wait event

there will be section called ' mutex sleep section'
Tell on which function its have more sleep and i will help

or

past the whole mutex section in the thread

or

you can generate the system state dump and you can analzye the same


Kind Regards,
Rakesh Jayappa

Hi

I am getting Mutex X and my awr section is as follows , can you tell me what is the issue

Mutex Type Location Sleeps Time (ms)
--------------------- -------------------------------- ------------ ------------
Library Cache kglget1 1 49,355,999 2,490,453
Library Cache kgllkdl1 85 4,779,939 720,141
Cursor Pin kksfbc [KKSCHLFSP2] 75,321 2,228
Library Cache kgllkc1 57 65,367 4,075
Cursor Pin kksLockDelete [KKSCHLPIN6] 61,769 3,263
Library Cache kglpndl1 95 5,586 296
Library Cache kglpin1 4 5,555 238
Library Cache kglpnal1 90 4,073 211
Cursor Pin kksfbc [KKSCHLPIN1] 45 3
Cursor Pin kkslce [KKSCHLPIN2] 4 0
Library Cache kglGetHandleReference 124 1 0
Library Cache kglhdgn1 62 1 0
Library Cache kglhdgn2 106 1 0
-------------------------------------------------------------

Tanel: I can confirm that i see 700 child cursors coming out of same sql_id. Can you please post the resolution to this issue?

Thanks
Kevin