Thanks a lot. Today Again After the Index GSS , issue disappears. I am not able to get how this is effecting during parsing. Would you please provide me some explain how index GSS/Rebuild help in reducing this . As per my understanding object is piined for definition and released immediately. Today Our Client asked to create a new composite index based on column involved in query. How this is going to reduce reduce the contention. I can understand if any data fetching is happening , it may help in reduced in elapse time. But Creating an Index How it will reduce the contention. PLease Please reply.
We still need to know WHICH latch is losing you time - counts may be irrelevant. See my earlier comment:
"The article of Alex Fatkulin's that Joel Garry referenced has a query linking the row cache object latches to the dictionary cache, and we need to know what the contention is."
We don't know whether the latch problem is the cause or consequence of the CPU utilisation increasing.
A couple of ideas (just for illustration) of why gathering stats might make a difference
a) gathering index stats invalidates dependent cursors - this may release a large number of objects in the library cache which are causing the queue that creates the problem - discard the object, reparse the statement, and there is, for a while, no queue so no contention.
b) gather stats changes the execution plan - making it use less CPU; when CPU utilisation drops turnaround on the latches is quicker (and there is less CPU spent on spinning)
Update: Another idea (for illustration) you've changed your comment about dc_ entries from dc_tables to dc_tablespaces - if you're rolling back then Oracle does a get on dc_tablespaces for every undo record applied (this is just one reason for higher counts). Since your tkprof showed unexpectedly high LIOs for what seems to be a simple index access then you could be seeing lots of interference between sessions attempting cleanout and rollback. This could be a cause of CPU overload that results in latch problems. But we shouldn't speculate until we know about TIME spent on dc_ latch gets - counts don't tell us enough.
PLease Find the Details :
select latch#, child#, sleeps , wait_time from v$latch_children where name='row cache objects' and sleeps > 0 order by sleeps desc; LATCH# CHILD# SLEEPS WAIT_TIME ---------- ---------- ---------- ---------- 280 8 564274776 2942777250155 280 5 1002920 12073403788 280 13 76089 710297346 280 9 50270 237754726 280 4 559 529077 Child# 8 Belong to the dc_users & 5 belong to dc_tablespaces select "WHERE", sleep_count, location from v$latch_misses where parent_name='row cache objects' and sleep_count > 0; WHERE SLEEP_COUNT LOCATION -------------------------------------------------------------------------------- ----------- -------------------------------------------------------------- kqrpre: find obj 167730249 kqrpre: find obj kqrpre: init complete 52 kqrpre: init complete
Today Again for the Same Contention we gather the stats for Index , Latch : row cache reduces immeditely
Looking at the extreme spike for dc_users it does seem likely that that's the one that causes problems when you see the problem, The only thing you could do to make this more obvious is to run the query a couple of times over a few minutes and calculate differences.
The fact that you are doing very little "hard" parsing but plenty of parse calls suggests that something bad is happening (as I may have suggested earlier on) as you search the library cache and do something to validate your right to execute a child cursor. (The reason why I mentioned VPD is that there's a known bug that fits these symptoms.)
Gathering stats invalidates cursors - which means you don't have to spend so much time searching for a valid cursor. You might find that any DDL on the index will have the same effect (one I used to use in the past was to change the PCTFREE - without actually changing the value). It might be worth checking how many child cursors this statement has when contention is occurring (count(*) from v$sql where sql_id = ''') You could also check v$open_cursor to how many sessions have this cursor open at any time - and how many copies each they've managed to acquire.
Have you set the session cursor cache parameter ?
What do the numbers for parse calls, session cursor cache hits and cursor authentications in the interval look like ?
I don't know how other people manage, but I wait until I've completed the page, then switch to "use advanced editor" - see top right corner of form - highlight the text, then select courier new as the font family. (But I cut-n-paste a copy of what I've done into a text document first).
Thanks a lot.
Please Find the Obersvation :
select count(*) from v$sql where sql_id = '6rf2f75wptjrk';
--> count was 19 , as soon as we did the GSS Count Reduced to 4 and gradually increase to 12
-- > Session Cashed Cursor is value :
session_cached_cursors integer 1200
-- > Count Of the DC_USERS Taken at different time during the contention observed.
-- Time Sleep Time -- 11:58 574236392 2967369332806 -- 12:02 574827287 2969900443831 -- 12:10 577951681 2980163642648 -- 12:34 583273451 2990521771589 -- 3:30 593503044 3059362695733
Thanks a lot of help for on this. I am understanding a lot on core DATABASE with the help of you.
At present we are just invalidating the cursor and making the improvement in the performance.We have worked on few things to make Performance better ( to reduce this contention ) :
> Creating A New INdex on the same table.
> Changes are made in code to reduce the concurrent access to this particular table.
I have checked that VPD is implemented in our case. After the recent code changes , three more policies has been applied . Is there any way I can find out when the dba_policies has been implemented. Since I can't see any creation date in DBA_POLICIES.
select * from v$vpd_policy where sql_id = '6rf2f75wptjrk';
SELECT count(*) FROM DBA_POLICIES where enable='YES';
I don't know of an explicit stored date, but dba_policies records the PF_owner, package and function that generate the security predicate, so you could query dba_objects for the package to check its creation date and last DDL date - this MIGHT be an indicator, e.g.:
object_id, created, last_ddl_time
owner = 'XDB'
and object_name = 'DBMS_XDBZ0'
and object_type = 'PACKAGE'