What job runs AWR snapshot? Eh?
October 27, 2011 5 Comments
There are a surprising number of online resources propagating the idea that the collecting of AWR snapshots is somehow connected to the GATHER_STATS_JOB (which was responsible for automatically gathering stats in 10g).
Eh?
Collecting AWR snapshots is one of the responsibilities of the background process MMON.
If you have a problem with the automatic snapshots:
– Check whether you can manually snap using DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT.
– Check MMON and alert log / trace files for related messages.
– See Metalink note 1301503.1: “Troubleshooting: AWR Snapshot Collection issues”
This came up in a question on the OTN forums where the OP said that AWR was not snapping automatically but the GATHER_STATS_JOB seemed to be ok. And the obvious first question would be where did you get that idea from?
Sometimes you can’t win on the forums. If you ask a question, it’s not uncommon to be pointed to a google search. But if you google something, you can’t trust everything that’s out there – it gets outdated and sometimes it never was accurate.
OraStory 
From the XE 11g MMON trace file, on a minimal system, barely used:
—————————————————
The sampled session history is constructed by sampling
the target session every 1 second. The sampling process
captures at each sample if the session is in a non-idle wait,
an idle wait, or not in a wait. If the session is in a
non-idle wait then one interval is shown for all the samples
the session was in the same non-idle wait. If the
session is in an idle wait or not in a wait for
consecutive samples then one interval is shown for all
the consecutive samples. Though we display these consecutive
samples in a single interval the session may NOT be continuously
idle or not in a wait (the sampling process does not know).
The history is displayed in reverse chronological order.
sample interval: 1 sec, max history 120 sec
—————————————————
—– END DDE Action: ‘ORA_12751_DUMP’ (SUCCESS, 2 csec) —–
—– END DDE Actions Dump (total 2 csec) —–
*** KEWRAFM1: Error=12751 encountered by Auto Flush Main.
KEBM: MMON action policy violation. ‘AWR Auto Flush Task’ viol=1; err=12751
Thus, Schrödinger’s cat is killed, in no uncertain terms, as an error says to call Oracle support, for a product that is not supported, with an option that cannot be licensed or used.
Note 761298.1: “If the load on the system is extremely high, this is expected, and error can be ignored.”
A little more difficult to ignore when the load is extremely low and the system locks up. I hope that is not expected.
> Schrödinger’s cat
I had to do a search for this term to find out what you were talking about. I read something at wikipedia or somewhere similar and to be honest it went a little over of head.
Then it cropped up again in the paper just the other day, here:
http://www.telegraph.co.uk/science/science-news/8875967/Testing-the-Copenhagen-interpretation-a-matter-of-live-and-dead-cats.html
So I read that article touching on quantum theory and GRWP and I thought of two contrasting things which are relevant to any sort of investigation including performance.
The first is that just because you can come up with a theory that explains everything, doesn’t mean it’s true. That was my main reaction to what I was reading.
And the other is from Sherlock Holmes – “when you have eliminated the impossible, whatever remains, however improbable, must be the truth”.
I was left completely dumbfounded when I told a developer at work that we had no AWR history and he replied by saying that he’d just run dbms_stats. Strange how that seems to have got around.
Stunned. What is there to confuse? I could understand people confusing ASSM ASMM, and even ASM on at least an acronym level. I suppose it is because there is no obvious mechanism (scheduled task) for the scheduled collection (What? The background processes actually do stuff?).
It seems this bad information is an Oracle Press Book “Oracle Tuning 10gR2” by Richard Niemec.So be careful with books too..