Statistics

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

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?

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

Follow me on Twitter

Recent Posts