RE: Preventative measures against slow V$RMAN_STATUS queries

From: Deas, Scott <Scott.Deas_at_lfg.com>
Date: Thu, 1 May 2014 14:33:12 +0000
Message-ID: <C1FB7BA65B13C542B2CB1CE5DB8F74AF06BB1B68_at_NC2PWEX501.us.ad.lfg.com>



I've found when querying certain v$ views, setting the optimizer to rule based either for the session or in the specific query helps.

Session: alter session set optimizer_mode=RULE;

Query: /*+ RULE */

Thanks,
Scott

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rich Jesse Sent: Thursday, May 01, 2014 10:26 AM
To: oracle-l_at_freelists.org
Subject: Preventative measures against slow V$RMAN_STATUS queries

Hey all,

The performance problems on the V$RMAN_STATUS fixed view is at it again for me, this time on 11.2.0.3 on AIX 7.1. I've got a bunch of new DBs that I've created (and still creating more) and one of them had performance problems last night with the view. Collecting fixed stats has appeased the performance for now.

MOS 1464844.1 recommends:

exec dbms_stats.gather_fixed_objects_stats(null);
exec dbms_stats.DELETE_TABLE_STATS('SYS','X$KCCRSR');
exec dbms_stats.LOCK_TABLE_STATS('SYS','X$KCCRSR');

My thoughts on this:

  1. I need to address my stats collection of fixed objects.
  2. Is there a downside to deleting/locking the stats on X$KCCRSR as part of my DB creation script?

My gut tells me that the lock-stats workaround will come back to bite me someday, but trying to come up with a more stable/elegant solution while in the middle of an ERP implementation isn't high on my priority list.

Thoughts?

TIA!
Rich

--

http://www.freelists.org/webpage/oracle-l

Notice of Confidentiality: **This E-mail and any of its attachments may contain Lincoln National Corporation proprietary information, which is privileged, confidential, or subject to copyright belonging to the Lincoln National Corporation family of companies. This E-mail is intended solely for the use of the individual or entity to which it is addressed. If you are not the intended recipient of this E-mail, you are hereby notified that any dissemination, distribution, copying, or action taken in relation to the contents of and attachments to this E-mail is strictly prohibited and may be unlawful. If you have received this E-mail in error, please notify the sender immediately and permanently delete the original and any copy of this E-mail and any printout. Thank You.**

--

http://www.freelists.org/webpage/oracle-l Received on Thu May 01 2014 - 16:33:12 CEST

Original text of this message