Statistics

How to deal with statistics when you migrate?

Have you done migration projects in the past, or do you have some upcoming projects, and you wonder how you should treat all sorts of statistics in your database? What is the better option? Keep and migrate the existing ones? Regather them right away? A partial-move approach? How to deal with statistics when you migrate? Then this is for you. Sign up to our next Virtual Classroom Seminar on Thursday, June 18 at 14:00h CEST: Statistics and Migrations – Well-Kept Secrets Revealed

How to deal with statistics when you migrate?

Photo by Mika Baumeister on Unsplash

What’s the topic?

Statistics and Migrations – Well-kept Secrets Revealed…

Queries on dictionary tables are slow in Oracle Multitenant?

Posted on February 10, 2025 by Mike.Dietrich Flaws and Pitfalls Single-/Multitenant 12

I love to learn something new every day. And on Friday afternoon last week I learned something new again. It started (as often) with a very simple question from a colleague working from the Oracle Malaga office in Spain. The customer has an issues with some queries being slower than before once they moved from on-prem to the cloud into a PDB. But are queries on dictionary tables are slow in Oracle Multitenant generally?

Queries on dictionary tables are slow in Oracle Multitenant?

Photo by Pascal van de Vendel on Unsplash

What was the case?

The customer ran some queries against dictionary views in the PDB in Oracle Database …

A change in Oracle 12.2 and 19c with MV Refresh Stats Collection

Posted on November 23, 2022 by Mike.Dietrich Best Practice Upgrade / Migration 6

It’s not easy for me to put the topic into a searchable title for this blog post. The feature or change I will write about today came in actually with Oracle Database 12.2.0.1. But since many of you will move directly from Oracle 11.2.0.4 or 12.1.0.2 to 19c, the Long Term Support release, it may affect you as well. So let me explain a change in Oracle 12.2 and 19c with MV Refresh Stats Collection.

A change in Oracle 12.2 and 19c with MV Refresh Stats Collection

Photo by Ralph Mayhew on Unsplash

A customer story

The local Support team asked me a few weeks ago whether I have heard …

Gather Fixed Objects Stats in PDBs as well?

Posted on October 21, 2016 by Mike.Dietrich Optimizer Single-/Multitenant Statistics 9

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

Global Temporary Tables – Change in Oracle 12c

Posted on July 7, 2016 by Mike.Dietrich Behavior Change Optimizer Oracle Database 12c 0

A few weeks back I was copied on an email conversation about a important change with Global Temporary Tables (GTT) in Oracle Database 12c.

Something you need to be aware of when using GTTs in Oracle Database 12.1.0.2:
Prior to this release GTTs shared statistics between sessions. Statistics were SHARED between different sessions. Now since Oracle Database 12c this is different by default – statistics on GTTs are visible to the SESSION only.

This can be changed and altered of course. And there are pros and cons. But as I’m not an optimizer expert I’m glad that …

Have an Excellent Start into 2016!!!

Posted on January 13, 2016 by Mike.Dietrich Off Topic 0

Roy and I and the entire Database Upgrade Team would like to wish you a very good start into 2016!!!

And thanks for your confidence in our work in the past year(s). The Upgrade Your Database – NOW! Blog had over 440,000 hits in 2015 which is quite a surprise as neither Roy nor I do update the blog full time. But the many mails and comments we consistently get demonstrate your interest.

Upgrade Your Database - NOW! Statistics 2015

We will continue to report about things related to upgrades and migrations and performance and testing and the cloud and … and … and regarding Oracle Database …

Query on ALL_SYNONYMS is slow in Oracle Database 12c

Posted on December 22, 2015 by Mike.Dietrich Flaws and Pitfalls Oracle Database 12c Performance Tuning 5

A customer (Thanks Stefano!) alerted me on this issue during a workshop and I did some further investigation. Basic headline is:

Query on ALL_SYNONYMS is very slow in Oracle Database 12.1.0.2 compared to 11g.

The workaround for this 21324443: SLOW QUERY IN 12C ON ALL_SYNONYMS. is:

dbms_stats.gather_table_stats('SYS','OBJ$',estimate_percent
=>100, method_opt => 'for columns flags size 1, spare3 size 254, type# size 254');

and I see that at least 8 other customers opened SRs hitting the same issue.
Does the workaround suit you in any way?

The bug had no progress since it was opened. If you are seeking progress …

New in Oracle 12c: _optimizer_gather_stats_on_load

Posted on October 6, 2015 by Mike.Dietrich Behavior Change Optimizer Oracle Database 12c Parameters 1

Received an email from Roy last night with some performance issues a customer in the US encountered recently during their upgrade testing.

One issue the customer encountered has to do with tons of parallel slaves creating a massive noise on the system when they are doing a CTAS (Create Table As Select) – and the same thing happens with an IAS (Insert Append Select).

What caused this change?

In this case the behavior change is well documented, even though not linked to the responsible underscore parameter.

Oracle White Paper: Best Practices for Gathering Statistics –

Statistics

A change in Oracle 12.2 and 19c with MV Refresh Stats Collection

A customer story

Gather Fixed Objects Stats in PDBs as well?

Global Temporary Tables – Change in Oracle 12c

Have an Excellent Start into 2016!!!

Query on ALL_SYNONYMS is slow in Oracle Database 12c

Query on ALL_SYNONYMS is very slow in Oracle Database 12.1.0.2 compared to 11g.

New in Oracle 12c: _optimizer_gather_stats_on_load

What caused this change?

Archives

Categories

Upgrade @YouTube

RSS Feed for Upgrade your Database – NOW!

Subscribe via Email

Recent Posts