11G High row cache objects contention 集中在DC_USERS

gengyonghui

As you said, "一类是web 程式，主要是literal SQL.", it may be caused by hard parses.
I think you can try to set CURSOR_SHARING to similar or force, then check the performance again.

Karsus

gengyonghui 发表于 2013-11-28 16:58
As you said, "一类是web 程式，主要是literal SQL.", it may be caused by hard parses.
I think you can ...

There is a confused  that  the high contention period and the low contention period

have the similar HARD PARSE COUNTS in AWR and the value only around 140/s.

And Why executing well-binding procedure also encounter heavy  row cache objects contention even after I using  user table  instead of system view.

Yong Huang

> Because we have some privilege consideration that set current schema is not suitable.

Can you elaborate? Why would setting current schema be related to privilege? It's just a shorthand, saving some typing.

Karsus

Last Sunday, we changed the code in some non-bind functions to bind variable, and increase the 'session cached users' from 100 to 200 and decrease the shared_pool from 30G to 15G.

It seems the symptom goes lighter. Latch: row cache objects doesn't appear in TOP 5 until now although I also can see them sometimes.

Karsus

But  from 'select latch#,child#, gets,misses,sleeps from v$latch_children  where name='row cache objects' order by gets desc'

DC_USERS also have highest misses and sleeps...

Yong Huang

You're doing the right thing to change code to use bind variables. They're essential in OLTP.

But that may be unrelated to your row cache objects latch contention, which has more to do with object owner resolution.

(In English, when you say "until" in a negative sentence, you imply what's stated in the main clause reverses: "...doesn't appear in TOP 5 until now" means "it does appear now". You probably meant to say "so far" instead of "until".)

Karsus

Yong Huang 发表于 2013-12-11 00:21
You're doing the right thing to change code to use bind variables. They're essential in OLTP.

But ...

ha...Thanks.