Re: performance impact of monitor hint?
Date: Sun, 17 Mar 2013 15:58:04 +0200
Message-ID: <CAMHX9J+nGXiKrEjBNpy_JygYtLPw2a+KkKuggddeian1-y-y1A_at_mail.gmail.com>
The main performance impact of the old GATHER_PLAN_STATISTICS / statistics_level = ALL instrumentation came from the fact that expensive timing (gettimeofday()) system calls were used for getting A-Times of row sources.
That's why the _rowsource_statistics_sampfreq was introduced, to not have to get the timing every switch between row sources, but only at every 128th time (by default). This caused some interesting measurement (or rather extrapolation) errors where the parent row source seemingly used more time than all of its children combined. (By the way, depending on your platform and recency of it, lightweight "fast trap" system calls may be used for getting the current timestamp, which reduces the overhead somewhat - Solaris since long time ago and RHEL 5.4+ for example).
Anyway, the row source timing info in SQL Monitoring comes from ASH
sql_plan_line samples, so this instrumentation is always enabled anyway -
SQL Monitoring will just query ASH for these records. The other data in
V$SQL_MONITOR views doesn't cause huge overhead - it depends on your query,
but a heavy logical IO nested loop (my
lotslios.sql<http://blog.tanelpoder.com/files/scripts/lotslios.sql>)
consumed around 0.6% more CPU with MONITOR hint compared to running with
NO_MONITOR hint. I tested it on a VM and run it only a couple of times, so
this might just be statistical error or due to some other background
activity. Not significant overhead in other words.
Now this was about long running queries, but you should not go and enable
SQL Monitoring for all your short (OLTP) queries with a hint now as the SQL
Monitoring data has to be kept and updated in a shared pool memory
structure - and if all of your 1000 sessions suddenly start updating their
monitoring data for every execution of a SQL, you will end up waiting
for *Real-time
plan statistics latch* contention (and some CPU overhead because so
frequent searching and purging of previous SQL execution's monitoring data).
If you want to get SQL Monitoring like output (in other words: SQL plan * line* level breakdown) then manually query ASH sql_plan_line columns or use my asqlmon.sql <http://blog.tanelpoder.com/files/scripts/ash/asqlmon.sql>script for that.
-- *Tanel Poder* Enkitec (The Exadata Experts) Training <http://blog.tanelpoder.com/seminar/> | Troubleshooting<http://blog.tanelpoder.com/> | Exadata<http://www.amazon.com/Expert-Oracle-Exadata-Apress/dp/1430233923> | Voicee App <http://voic.ee/> On Fri, Mar 15, 2013 at 11:24 PM, coskan gundogar <coskan_at_gmail.com> wrote:Received on Sun Mar 17 2013 - 14:58:04 CET
> Apart from 5+ seconds by default monitored to see the impact Why not try
> with both nomonitor and monitor hint and compare
> On Mar 15, 2013 6:45 PM, "Josh Collier" <Josh.Collier_at_banfield.net> wrote:
> > Does the /*+ monitor */ hint affect performance at all?
> > Thanks for your help!
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-l