Fixed Objects Stats

Fixed Objects Stats Gathering Fails – what do you do now?

A few days ago I blogged about what you could do when Fixed Objects Stats Gathering is slow in an upgrade process. You can skip the preupgrade gathering, and the postupgrade gathering does not happen anymore since the October 2020 version of AutoUpgrade. Still, I came across another issue the other day: Fixed Objects Stats Gathering Fails – what do you do now?

What happened?

Actually this is connected to the Fixed Objects Stats Gathering is slow in an upgrade case. The customer encountered an incredible long runtime. And the support engineer …

Continue reading...

When Fixed Object Stats Gathering takes very long during upgrade …

Over the past days I exchanged several emails with a very important customer regarding Fixed Objects Stats gathering in relation to database upgrade. The customer found out that it takes very long in their environment – over an hour to be precise. Relaxing on the weekend helped me a bit. And this morning, it dawned me that I’ve had two similar cases before already. Plus I didn’t blog about it yet. Now it’s time to tell you a little but about Unified Auditing and the situation when Fixed Object Stats Gathering takes very long during upgrade …

When Fixed Object Stats Gathering takes very long during upgrade ...

Photo by Ayse

Continue reading...

How to skip a Fixup in AutoUpgrade

In this blog post I’d like to demonstrate how to skip a fixup in AutoUpgrade. This can be very useful in cases where you know that an issue may happen. Recently we saw several cases with unexpected long runtime for the gathering of fixed objects stats.

How to skip a Fixup in AutoUpgrade

Photo by Warren Wong on Unsplash

Analyze Mode

During the analyze phase, AutoUpgrade creates a <SID>_checklist.cfg file. You will find it in your job’s prechecks subdirectory, e.g.

/home/oracle/logs/DB12/100/prechecks

My SID is DB12, and 100 is the job number.

This is how the db12_checklist.cfg looks in my example:

[SID]          [DB12]
==========================================
[container]          [DB12]
Continue reading...

Gather Fixed Objects Stats in PDBs as well?

Yesterday I received an interesting customer question:

“Do we have to gather fixed objects stats inside a PDB as well or only in the CDB$ROOT?”

The customer told me he couldn’t find anything in the documentation. Neither could I. And I did check also the usual suspects this morning “Best Practices for Gathering Statistics” – but it does not say anything about PDBs and CDBs.

Therefore I did a short test:

  • Create a PDB in my Oracle 12.1.0.2 CDB:
    SQL> create pluggable database PDB3
         admin user adm identified by adm
         file_name_convert=( '/u02/oradata/CDB1/pdbseed', '/u02/oradata/CDB1/pdb3');
  • Open the PDB:
    SQL> 
Continue reading...

Will gathering fixed object stats reduce recompilation time post upgrade?

Interesting question, isn’t it?
Will the time to recompile invalid objects post upgrade decreased once fixed object stats have been gathered?

First of all fixed object stats on X$-tables won’t be gathered by default [This will change with Oracle 12c where it is part of the Auto Stats Gathering job].
X$ structures are undocumented. V$ views are build on top of them and
should only be used even though it might be sometimes useful to access
X$ tables such as X$BH (buffer headers – contains information describing the current contents of a piece of the buffer cache

Continue reading...