RE: Why alter table move can repair the problem awr not generated ?
- From: John Hallas <John.Hallas@xxxxxxxxxxxxxxxxxx>
- To: "niall.litchfield@xxxxxxxxx" <niall.litchfield@xxxxxxxxx>, "luolee.me@xxxxxxxxx" <luolee.me@xxxxxxxxx>
- Date: Mon, 6 Jan 2014 11:53:03 +0000
Without going into too much detail on a non-specific question my knowledge (and
I keep meaning to blog about this) is that a scenario as fllows might have
happened
1) There is an automated job which runs each night by default to manage
the AWR and optimizer stats - this is run from the MMON process
2) That job - dbms_stats.purge_stats utility has 5 minutes to do its work
and then it stops wherever it has got to
3) If the SYSAUX table is full it cannot purge data and stops without
giving providing alerting. This might happen for consecutive 2 days lets say on
a database that does not have alerting on
4) The SYSAUX tablespace then has space added
5) The next time dbms_stats.purge_stats runs it starts to move the
outstanding snapshots around and has too many to do in the time window provided
therefore it stops
6) This process then continues every day and continues to fail
Generally the management of both AWR data and historic optimizer stats is not
well handled within Oracle up to 12c.
I have noticed that there is a fix in 11.2.0.4 and in 12.1 that manages the
purging much better and a patch 14373728 is available to address the issue.
My best advice however is to copy the AWR history out elsewhere using the
pre-supplied package SYS.DBMS_SWRF_INTERNAL and then drop the AWR repository
and re-create.
John
www.jhdba.wordpress.com
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Niall Litchfield
Sent: 06 January 2014 06:41
To: luolee.me@xxxxxxxxx
Cc: ORACLE-L
Subject: Re: Why alter table move can repair the problem awr not generated ?
We'll need the error encountered by the AWR snapshot job to determine a
suitable answer. (which may of course be that it was a coincidence). You can
find this (if my memory is good) in dba_scheduler_job_run_details.
On Jan 6, 2014 1:59 AM, "LuoLee.me"
<luolee.me@xxxxxxxxx<mailto:luolee.me@xxxxxxxxx>> wrote:
Dear list,
I meet a problem that awr snapshot didn't generate correctly last week. I
attempt to truncate the table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY, but it didn't
take effect.
The other way, I use the "alter table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY
move" in restricted mode, it became effect. I know the way that truncate the
table could do it sometimes, but this time, it fails.
We all know that "alter table move" could lower the highest watermark, but
why it can repair the awr not generated problem, and why truncate could not
here.
Anyone know the truth ?
Thanks in advance.
______________________________________________________________________
Wm Morrison Supermarkets Plc is registered in England with number 358949. The
registered office of the company is situated at Gain Lane, Bradford, West
Yorkshire BD3 7DL. This email and any attachments are intended for the
addressee(s) only and may be confidential.
If you are not the intended recipient, please inform the sender by replying to
the email that you have received in error and then destroy the email.
If you are not the intended recipient, you must not use, disclose, copy or rely
on the email or its attachments in any way.
This email does not constitute a contract in writing for the purposes of the
Law of Property (Miscellaneous Provisions) Act 1989.
Our Standard Terms and Conditions of Purchase, as may be amended from time to
time, apply to any contract that we enter into. The current version of our
Standard Terms and Conditions of Purchase is available at:
http://www.morrisons.co.uk/gscop
Although we have taken steps to ensure the email and its attachments are
virus-free, we cannot guarantee this or accept any responsibility,
and it is the responsibility of recipients to carry out their own virus checks.
______________________________________________________________________
Other related posts:
