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…
Tag Archives: Gathering
Incremental Statistics Collection in Oracle 12.1.0.2 – A True Story
Recently I came across a really interesting customer case in the UK dealing with Incremental Statistics Collection issues in regards to an upgrade to Oracle Database 12.1.0.2. This is the follow-up blog post to: Incremental Statistics Collection in Oracle 12.1.0.2 – Upgrade Pitfalls A while back I blogged already about Incremental Statistics collection in Oracle Database 12.1.0.2: Incremental Statistics Collection improved in Oracle 12c And you’ll find more information in our documentation and in posts by our optimizer folks: Database SQL Tuning Guide 12c: Gathering Incremental Statistics on Partitioned Objects Incremental Statistics Maintenance – what statistics will be gathered after DML occurs…
New in Oracle 12c: _optimizer_gather_stats_on_load
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 – Page 13 (PDF: 15) http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf…