Recently I've got problem with high wait event "cursor: pin s wait on x" a high CPU utilization on CPU. On database we see many child cursors (~300) . Query is very complex (explain plan has ~1000 lines) and generate about 20 different execution plans (bind variables are in use) and sometimes execution plan is very slow.
In V$SQL_SHARED_CURSOR I checked mismatch and on two columns (AUTH_CHECK_MISMATCH and INSUFF_PRIVS) value is 'Y'.
I query is used SYS_CONTEXT function and I though that can be problem with VPD but no.
I also checking by trace 10046 where database spend time but here I found that parse time elapsed is nearly 97% ( but probably through recursive sql database badly count time).
After some investigation on metalink I found BUG 11930680
"If optimizer_secure_view_merging is enabled then some SQL statements may
not be shared due to AUTH_CHECK_MISMATCH / INSUFF_PRIVS even if the
SQL is issued repeatedly by the same user. This can cause excess shared
pool memory use and other contention issues due to the high child cursor
count."
and after patch installation problem has been solved - one child cursor.
Brak komentarzy:
Prześlij komentarz