Stats Gathering

ORA-1422 and ORA-6512 from SYS.DBMS_STATS in Post Upgrade

Posted on May 4, 2021 by Optimizer Patch Recommendation 3

Ernst Leber sent me an email today. He hit an error at a customer upgrading to Oracle 19.9.0 on Exadata with AutoUpgrade. ORA-1422 and ORA-6512 from SYS.DBMS_STATS in Post Upgrade were signaled – and he better flashed back to the Guaranteed Restore Point. He found even a MOS note but still had questions. So time to blog about it in case you hit this error sequence as well.

What happens, and when does it happen?

At first, this is not an AutoUpgrade issue. You may see the same error stack in the post upgrade …

Continue reading...

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

Posted on November 16, 2020 by Flaws and Pitfalls 3

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...

How to skip a Fixup in AutoUpgrade

Posted on July 22, 2020 by AutoUpgrade 11

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...

Differences between Automatic Statistics Gathering job and GATHER_SCHEMA_STATS

Posted on March 1, 2016 by Oracle Database 12c Statistics 12

Recently a customer raised a question whether there are differences between the Automatic Statistics Gathering job and a manual creation of stats via the GATHER_SCHEMA_STATS procedure.

The results in performance were quite interesting. Performance after an upgrade from Oracle Database 11.2.0.3 to Oracle Database 11.2.0.4 was not good when the automatic stats job got used. But performance changed significantly to the better when schema stats were created with the downside of taking more resources during the gathering.

Is the Automatic Stats Gathering job enabled?

That question can be answered quite easily. There’s a very good [MOS Note has been disappeared] …

Continue reading...

DBMS_STATS. GATHER_DICTIONARY_STATS fails with ORA-20001 ORA-6502 ORA-6512 – Concurrent Stats

Posted on August 21, 2015 by Flaws and Pitfalls Oracle Database 12c 0

I really have to say “Thank you very much” to the people out there alerting me about issues I haven’t seen before. Just in the past week I’ve got to learn about three issues which are related to the upgrade – and I haven’t seen before. Please don’t expect me always to follow up with the progress of your SRs or the related bugs. But your experiences are such an important source for me – so thanks again!

The most recent issue (thanks to Bernd Tuba from MM Warburg) …

Symptom

execute dbms_stats.gather_dictionary_stats

ORA-06502: PL/SQL: numeric or value
… Continue reading...
Mike Dietrich
Oracle Senior Director of Product Manager for Database Upgrade, Migrations and Patching writing just another blog.

Opinions certainly are my own as is this page.

Archives

Categories

Upgrade @YouTube

Subscribe via Email

Join 7,349 other subscribers

Follow me on Twitter