Statistics

Oracle Optimizer Statistics Advisor in Oracle Database 12.2.0.1

Posted on August 22, 2017 by Mike.Dietrich Statistics 32

The Oracle Optimizer Statistics Advisor in Oracle Database 12.2.0.1 is something I planned to write about for quite a while. Roy and I include this topic in the New Features section in our Upgrade / Migrate / Consolidate to Oracle Database 12.2 slide deck for some time. This blog post (in German only) by my former team mate Ulrike Schwinn inspired me to post something by myself.

Oracle Optimizer Statistics Advisor in Oracle Database 12.2.0.1

First of all you’ll find the feature documented in the Oracle Database 12.2 Database SQL Tuning Guide.

The advisor task (‘Statistics Advisor‘) …

Gather SQL Statements from AWR and Cursor Cache without need for Diag and Tuning Packs

Posted on January 26, 2017 by Mike.Dietrich AWR Cloud Statistics 2

When we talk about database upgrades and migrations the most important (and unfortunately time-/resource consuming) activity is testing.

But often testing resources are limited or, even worse, don’t exist. I’ve worked with customers where we migrated a core EBS system off AIX to Linux – but only got a 6 year old Tru64 box for testing purposes (where the recompilation took 20x as long as later on the production environment). Or the classic one: Database is too big thus we test only with 10% of the data.

I know that often your hands are tied. Everybody wants an 1:1 …

Gather Fixed Objects Stats in PDBs as well?

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

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

Differences between Automatic Statistics Gathering job and GATHER_SCHEMA_STATS

Posted on March 1, 2016 by Mike.Dietrich 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] …

Statistics

Oracle Optimizer Statistics Advisor in Oracle Database 12.2.0.1

Oracle Optimizer Statistics Advisor in Oracle Database 12.2.0.1

Gather Fixed Objects Stats in PDBs as well?

Differences between Automatic Statistics Gathering job and GATHER_SCHEMA_STATS

Is the Automatic Stats Gathering job enabled?

Archives

Categories

Upgrade @YouTube

RSS Feed for Upgrade your Database – NOW!

Subscribe via Email

Follow me on Twitter

Recent Posts