In Chapter 3 of Oracle RAC Performance Tuning, I showed how improper CACHE values for sequences can cause poor performance in Oracle RAC. I also showed how to spot sequence contention when looking at a session’s wait events.
Today, I was working with a developer who was creating a new sequence. The developer had a CACHE value of 100, which made me initially thing was too low of a value. I spotted this low setting during code review. The developer thinks the CACHE value is fine but I’m not convinced. We will test this under load to see if the CACHE value needs to be adjusted.
In the meantime, I was thinking “what if I missed this during code review?” And a follow-on question, “what if we didn’t notice anything during load testing?” I want to be able to go back and determine which sequences, if any, would be candidates for having an improper CACHE setting. I could certainly trace sessions and analyze the trace files, but that would be too painful. So I devised a script that I can run against Active Session History to help determine candidate sequences.
select sh.sql_id,to_char(st.sql_text),count(*)
from dba_hist_active_sess_history sh
order prescription viagra Guys give more priority to the pleasure when indulging in sexual activity. These breathing patterns have a negative impact on the viagra prescription http://www.icks.org/hugo33kim/pdf/PoliEcon666@HugoKim2018@32%20PoliPhilosophy%20Biblio.pdf nervous system and the spinal column. You can buy propecia tablets in big quantities and avail of the home delivery service when you buy propecia online. order cialis overnight sildenafil cialis Sometimes, simply treating the underlying cause will suffice this sexual condition. join dba_hist_sqltext st
on sh.sql_id=st.sql_id
where st.sql_text like '%NEXTVAL%'
and (event='row cache lock' or event like 'gc current block %-way')
group by sh.sql_id,to_char(st.sql_text)
order by count(*) desc;
This is not a perfect science due to the nature of ASH collection. The session experiencing the contention would need to be caught at just the right time to be in the DBA_HIST_ACTIVE_SESSION_HISTORY table. But the SQL statement above does give me some candidates for consideration. Not every sequence accessed in the SQL statements returned need to have their CACHE values adjusted. Further analysis would be needed. However, this does give me a list of ones to consider. And it can help answer my initial questions. If I missed the sequence creation during code review, I can hopefully find it later if the sequence is a problem for application performance.
