I have not found any good writeup on this in web which could lead me to some solution of this problem. I found my own ways to solve it. Thought to write down some of it so that it might be helpful for others.
Let me try to put the long story in a vey short way.
Let me try to put the long story in a vey short way.
If I summarize, these were the sequence of events. One of my friends hit this problem and asked me to take a look at the database. There was a Materialized view which supposed to be refreshed in every few minutes.
- I found that the following query ran slow (took 30 seconds and more) which is a system generated query related to materialized view refresh.
update sys.sumpartlog$ s set s.timestamp = :1, s.scn = :2
where rowid in (select rowid from sumpartlog$ AS OF SNAPSHOT(:3) s1
where s1.bo# = :4 and s1.timestamp >= to_date('4000-01-01:00:00:00',
- From several database reports like AWR, ADDM - I found that, most of the database time was being spent on "soft parse" and the only top queries was the materialized view refresh, was taking more than 50% of database resources most of the times.
- To investigate the soft parse issue, I queried the V$SQL dictionary view. I found that, there were more than 5000 child cursors for the parent cursor. I collected the sql_id of the query. However, there were three other queries having huge humber of child cursors.
- To investigate why child cursor were not being shared, I queried V$SQL_SHARED_CURSOR dictionary view. Out of about 50 reasons for not sharing child cursors, I found that FLASHBACK_CURSOR attribute was being set to 'Y' which means cursor not shared due to flashback query.
- I knew that in flashback queries, the cursors are not shared, that's how it works and Oracle think this the right way of doing flashback queries.
- Materialized view usage the flashback query techniques which is being used to look at old data for refresh operations.
- Then I looked at the Oracle support documentations and found that, some people report this as an Oracle problem. From some earlier release, Oracle started using child cursor concepts I believe. Oracle workaround is to upgrade to 11.2.0.2.0 version where child cursors has come max limits and then automatically discarded. Oracle Doc ID: 1051346.1
- I knew that, in the latest Oracle release, database maintains a list of child cursors, if it crosses the threshold of 100, it discards the child cursors.
- I knew that, too many child cursors sometimes could slow down soft parsing as it needs to loop through the shared pool hash chain to find the right cursor for the right plan.
- Then I thought, if I could get rid of those excessive cursors, the query parse time would be reduced, and thus i improve response time.
- Then I query the V$SQLAREA to find the query address and hash value of those queries having over 5000+ cursors. I wrote a cron job to purge those parent cursors from shared pool periodically - once in a hour using the Oracle dbms_shared_pool package.
DECLARE
addhash varchar2(50);
BEGIN
select address||','||hash_value into addhash from v$sqlarea where sql_id = 'chbgkk6myytz';
dbms_shared_pool.purge(addhash,'C');
END;
/
- That solved this particular parsing problem for the Oracle internal queries used in refreshing Materialized view.
