Oracle Optimizer Statistics Advisor in Oracle Database 12.2.0.1

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‘) runs automatically in…

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

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 duplicate…

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> alter pluggable database…

Differences between Automatic Statistics Gathering job and GATHER_SCHEMA_STATS

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:1233203.1 – FAQ: Automatic…