extended stats

Overview

When column expressions or multiple columns of one table are included in query predicates, optimizer sometimes gets inaccurate cardinality estimates, therefore, generates sub-optimal plans . 

Oracle documentation provides the following description,

DBMS_STATS enables you to collect extended statistics, which are statistics that can improve cardinality estimates when multiple predicates exist on different columns of a table, or when predicates use expressions.

An extension is either a column group or an expression. Column group statistics can improve cardinality estimates when multiple columns from the same table occur together in a SQL statement. Expression statistics improves optimizer estimates when predicates use expressions, for example, built-in or user-defined functions.

The optimizer uses column group statistics for equality predicates, inlist predicates, and for estimating the GROUP BY cardinality.

create column group stats manually

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS( 
    OWNNAME => 'sh',
    TABNAME => 'customers',
    METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY ' || 
                  'FOR COLUMNS SIZE SKEWONLY (cust_state_province,country_id)' );
END;
/

create column group stats automatically

BEGIN
  DBMS_STATS.SEED_COL_USAGE(
    sqlset_name => null,
    owner_name => null,
    time_limit => 300);
END;
/

--
-- run explain plans for queries in the workload
--

SELECT DBMS_STATS.REPORT_COL_USAGE(
         ownname => 'sh', 
         tabname => 'customers')
FROM   DUAL
;

SELECT DBMS_STATS.CREATE_EXTENDED_STATS(
         ownname => 'sh', 
         tabname => 'customers') 
FROM   DUAL;

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'sh',
    tabname => 'customers');
END;
/

create column expression stats

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    OWNNAME => 'sh', 
    TABNAME => 'customers', 
    METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY ' || 
                  'FOR COLUMNS (LOWER(cust_state_province)) SIZE SKEWONLY'
  );
END;
/

show extended stats

SELECT DBMS_STATS.SHOW_EXTENDED_STATS_NAME( 
        OWNNAME => 'sh',
        TABNAME => 'customers',
        EXTENSION => '(cust_state_province,country_id)') col_group_name
FROM DUAL
;

SELECT e.EXTENSION expression, 
       t.NUM_DISTINCT, 
       t.HISTOGRAM
FROM   DBA_STAT_EXTENSIONS e, 
       DBA_TAB_COL_STATISTICS t
WHERE  e.EXTENSION_NAME = t.COLUMN_NAME
AND    e.TABLE_NAME = t.TABLE_NAME
;

drop extended stats

BEGIN
  DBMS_STATS.DROP_EXTENDED_STATS( 
    OWNNAME => 'sh', 
    TABNAME => 'customers',
    EXTENSION => '(cust_state_province, country_id)' );
END;
/

BEGIN
  DBMS_STATS.DROP_EXTENDED_STATS(
    OWNNAME => 'sh', 
    TABNAME => 'customers', 
    EXTENSION => '(LOWER(cust_state_province))'
  );
END;
/

Create PDB from non-cdb

Overview

To create PDB from non-cdb, you need to follow these steps,

• open non-cdb in read only mode
• generate xml file
• check compatibility
• create PDB from xml file
• run post script, noncdb_to_pdb.sql

Create xml file

$ srvctl stop database -db ORCL -o immediate

SQL> startup nomount
SQL> ALTER DATABASE MOUNT;
SQL> ALTER DATABASE OPEN READ ONLY;

BEGIN
  DBMS_PDB.DESCRIBE(pdb_descr_file => '/tmp/orcl.xml');
END;
/

Check compatibility

SET SERVEROUTPUT ON
DECLARE
  compatible CONSTANT VARCHAR2(3) :=
    CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
      pdb_descr_file => '/tmp/orcl.xml',
      pdb_name => 'NCDB')
    WHEN TRUE THEN 'YES'
    ELSE 'NO' END;
BEGIN
  DBMS_OUTPUT.PUT_LINE(compatible);
END;
/

Create PDB using xml file

$ srvctl stop database -db ORCL -o immediate

SQL> CREATE PLUGGABLE DATABASE ORCLPDB USING '/tmp/orcl.xml';
SQL> alter session set container = ORCLPDB;
SQL> @?/rdbms/admin/noncdb_to_pdb.sql