The Best Oracle 12c R1 & R2 New Features – Part 1

    By: Rich Niemiec on May 26, 2017

    The Best Oracle 12c R1 & R2 New Features - Part 1

    By Rich Niemiec, Viscosity North America  ◾ Michelle Malcher, Editor

    This is part one of an article series focused on new Oracle 12c R1 and R2 new features that I cover in my new book, Oracle 12c Release 2 Performance Tuning Tips & Techniques (Oracle Press). This first part will cover upgrade paths, INMEMORY_SIZE, In-Memory Column store, some new useful features, 12cR2 advancements with Approximate Query, Multiple Indexes on the same Column, In-Memory Virtual Columns in 12cR2 and more.

    In the next few parts, we’ll take a look at Pluggable Databases, including 12cR2 PDB refreshes and hot clones, 12cR2 Flashback PDB, In-Memory Column stores (IM) briefly and 12cR2 features, 12c Security Enhancements, what’s new with AWR in 12cR2, 12cR1 & R2 partitioning enhancements, 12c New Hints, and changing compression during impdp. Consider first: The best database company on the planet and its history of great advancements below as you use the best tool on earth to make a difference at your company.

    Oracle Firsts – Innovation to Acquisitions

    *On-premise (Production 12cR2 first came out in late 2016 in the Oracle Cloud)

    1979 First commercial SQL RDBMS

    1983 First 32-bit mode RDBMS 

    1984 First database with read consistency

    1987 First client-server database

    1994 First commercial and multilevel secure database evaluations

    1995 First 64-bit mode RDBMS

    1996 First to break the 30,000 TPC-C barrier

    1997 First Web database

    1998 First Database - Native Java Support; Linux, Breaks 100,000 TPC-C

    2000 First database with XML

    2001 First RDBMS with Real Application Clusters & First middle-tier database cache 

    2004 First True Grid DB & 2005 First FREE Oracle Database (10g Express Edition) 

    2006 First Oracle Support for LINUX Offering 

    2007 Oracle 11g Released!  

    2008 Exadata V1 Server Announced (Oracle buys BEA)

    2009 Oracle buys Sun – Java; MySQL; Solaris; Hardware; OpenOffice, StorageTek

    2010 Oracle announces MySQL Cluster 7.1, Exadata, Exalogic, America’s Cup Win

    2011 X2-2 Exadata, ODA, Exalytics, SuperCluster, Big Data, Cloud, Social Network 

    2012 X3-2 Exadata, Expanded Cloud Offerings, Solaris 11.1  

    2013 Oracle12c Released!  Oracle X3-8 Exadata, Acquisitions (Acme Packet…etc.)!

    2014 Oracle X-4, Acquisitions: Responsys & Corente, IN-MEMORY DB

    2015 X5-2, X5-8, FS1 Flash Array, Acquisitions & Cloud Solutions 

    2016 X6-2 (all flash if you want), X6-8, M7 SuperCluster, Cloud Solutions & Acquisitions, 12cR2 Cloud  

    2017: Production 12cR2 on-premise*

    You can run 12cR2 in the cloud or on-premise:

    image 1_Niemiec

    Oracle Database 12c Release 2 Upgrade Paths:

    Below are upgrade paths to get to 12.2 directly (you must start at 11.2.0.3 or higher) and indirectly.

    Source Database (Direct Path Upgrade) Target Database
    10.1.0.5 (or higher) 11.2.0.4 or 12.1.0.2
    11.1.0.7 (or higher) 11.2.0.4 or 12.1.0.2
    11.2.0.3 (or higher; includes 12.1) 12.2.x

     

    Source Database (Indirect Path Upgrade)

    Upgrade Path for Target  Database

    Target Database

    7.3.3.0.0 (or lower)

    7.3.4.x --> 9.2.0.8

    11.2.0.3+

    8.0.5.0.0 (or lower)

    8.0.6.x --> 9.2.0.8

    11.2.0.3+

    8.1.7.0.0 (or lower)

    8.1.7.4 --> 9.2.0.8

    11.2.0.3+

    9.0.1.3.0 (or lower)

    9.0.1.4 --> 9.2.0.8

    11.2.0.3+

    Database Upgrade Assistant (DBUA)

    The DBUA will check for the following before the upgrade (along with other advantages - see docs for more):

    • Invalid user accounts or roles
    • Invalid data types or invalid objects
    • De-supported character sets
    • Adequate resources (rollback segments, tablespaces, and free disk space)
    • Missing SQL scripts needed for the upgrade
    • Listener running (if Oracle Enterprise Manager Database Control upgrade or configuration is requested)
    • Oracle Database software linked with Database Vault option. If Database Vault is enabled, disable Database Vault before upgrade (Vault installed by default on)
    • Deinstallation Tool integrated with Installation Media.

    The New Version is up and running – life is good!

    image 2_niemiec

    You can also create your 12c database in the cloud. Below, I have two 12.1.0.2 databases and I'm deleting the larger one.

    image 3_niemiec

    Automatic Memory Management (AMM) INMEMORY_SIZE in 12c

    Now there is additionally INMEMORY_SIZE – 12c:

    • Set the size of the In-Memory Column Store with INMEMORY_SIZE
    • PGA_AGGREGATE_LIMIT to set a PGA limit (New 12c)

    Program Global Area (now in MEMORY_TARGET 11g):
    Aggregate PGA PGA_AGGREGATE_TARGET (11g)
    New PGA Limit PGA_AGGREGATE_LIMIT (12c)

    12.1.0.2 – In-Memory Column Store - IM - Initialization Parameter notes:

    • The Database In-Memory (IM) is not enabled if: INMEMORY_SIZE is set to zero!
    • Add space to the SGA_TARGET (or MEMORY_TARGET if used) to accommodate the IM:
      ALTER SYSTEM SET SGA_TARGET=200G scope=both;
    • The IM Column Store (IM) creates an area in the SGA called In-Memory Area. This is an added memory area in addition to the buffer cache and other SGA areas.
    • You'll also see in the INMEMORY (IM) section that there is both an INMEMORY_SIZE at the CDB level and then also INMEMORY_SIZE at the PDB level as well (12.1.0.2 & 12.2) that can be set.

    Some HUGE improvements in 12cR2:

    • In 12c, object names for users, roles, tables, columns, indexes, constraints, etc., have been increased from 30 bytes to 128 bytes with a few limitations.
    • The limit for tablespace names and pluggable databases is still 30 bytes, but others all increase to 128 bytes.
    • You will notice this change in the dictionary views where the VARCHAR2 columns will show as 128 bytes instead of 30 bytes.
    • It also helps in migrations from non-Oracle systems where the name is longer than 30 characters.
    • The best enhancement in 12cR2 is 32K VARCHAR is default. This allows the extending of the VARCHAR data types without having to enable the extended mode specifically (which you had to do in early 12c). The size limit for both VARCHAR2 and NVARCHAR2 is 32K.

    In 12cR2 You can have In-Memory (IM) Virtual Columns:

    The example below shows creating a Virtual Column (YEARLY_SAL):

    create table emp_rich

      (empno number(4),
      sal         number(7,2),
      yearly_sal generated always as (sal*12),
      deptno number(2);

    Table created.

    To put the emp_rich table INMEMORY (in the main IM area IMCU):
    alter table scott.emp_rich INMEMORY;
    (virtual column goes to IM only if below parameter set)

    The following initialization parameter must be set (can set when DB running):
    INMEMORY_VIRTUAL_COLUMNS=ENABLE (set to DISABLE to turn it off)

    To specifically put virtual column INMEMORY (this is a separate area of IM – IMEU):
    alter table scott.emp_rich INMEMORY(yearly_sal);

    IMCU=In-Memory Compression Unit (main IM area); IMEU=In-Memory Expression Unit

    Multiple Types of Indexes on the Same Column(s)

    You can also create more than one index on a column in 12c. It is useful to use different types of indexes for batch, query, or data warehousing at different times. Note the following on this:

    • Can set only ONE index to VISIBLE at a time on the same column
    • Some restrictions apply…see docs for full list
    • You can not create a B-tree AND B-tree cluster index.
    • You can not create a B-tree and an index-organized table (IOT).
    • All indexes ARE MAINTAINED during DML.
    • DML could be slow if TOO MANY indexes are created
    • Great for variable workloads!

    Example for Multiple Types of Indexes on the Same Column(s) (I currently have an index on DEPTNO):

    select       a.table_name, a.index_name,
                    b.column_name, a.uniqueness, a.visibility
    from         user_indexes a, user_ind_columns b
    where      a.index_name = b.index_name
    and          a.table_name = ‘DEPT‘;

    TABLE_NAME  INDEX_NAME COLUMN_NAME UNIQUENESS  VISIBILITY
    ----------------------- ------------------------- ---------------------------- ------------------------ -------------------
    DEPT     DEPT_UNIQUE1 DEPTNO  UNIQUE VISIBLE         

    Try to create a SECOND index on the same column FAILS if the first one is visible:

    create index dept_normal on dept(deptno);
    create index dept_normal on dept(deptno)
                                                            *
    ERROR at line 1:
    ORA-01408: such column list already indexed

    Make the first index invisible (I can now create the second index on the exact SAME column):

    alter index dept_unique1 invisible;
    Index altered.

    create index dept_normal on dept(deptno);
    Index created.

    Multiple Types of Indexes are now on the Same Column with one visible and the other invisible:

    select      a.table_name, a.index_name,
                   b.column_name, a.uniqueness, a.visibility
    from        user_indexes a, user_ind_columns b
    where     a.index_name = b.index_name
    and         a.table_name = ‘DEPT‘;

    TABLE_NAME  INDEX_NAME COLUMN_NAME UNIQUENESS VISIBILITY
    ------------------------ ---------------------------- --------------------------- ------------------------ -------------------
    DEPT  DEPT_UNIQUE1 DEPTNO  UNIQUE  INVISIBLE
    DEPT DEPT_NORMAL DEPTNO  NONUNIQUE  VISIBLE         

    Check the Indexes Views after I create FOUR Indexes on the same column (the bitmap index is visible):

    select    a.table_name, a.index_name,
                  b.column_name, a.uniqueness, a.visibility
    from       user_indexes a, user_ind_columns b
    where    a.index_name = b.index_name
    and        a.table_name = 'DEPT';

    TABLE_NAME INDEX_NAME COLUMN_NAME UNIQUENESS VISIBILITY
    ---------------------- -------------------------------- ------------------------- ---------------------- ----------------
    DEPT   DEPT_UNIQUE1            DEPTNO    UNIQUE  INVISIBLE
    DEPT DEPT_REVERSE   DEPTNO     NONUNIQUE   INVISIBLE
    DEPT DEPT_NORMAL  DEPTNO    NONUNIQUE INVISIBLE
    DEPT   DEPT_BITMAP  DEPTNO  NONUNIQUE VISIBLE

    (Index types: UNIQUE, NORMAL B-TREE, REVERSE KEY, and BITMAP)

    Fetch First or Next 10 rows…

    • Retrieve first rows without scanning everything
    • Faster query to retrieve a certain number of rows
    • Skip some rows (offset) to get to the middle of table for sample.

    FETCH FIRST x ROWS ONLY clause

    or

    OFFSET x ROWS FETCH FIRST x ROWS ONLY

    select count(cust_id)
    from   customers2
    group by cust_id
    offset 10000 rows fetch first 10 rows only;
    (this will skip 10,000 rows and then display the next 10 rows after that)

    Approximate Query – 12c R1 & R2

    • Approximate Query Processing, counts distinct values and adds approximate percentile aggregation. 
    • This allows faster processing of large data sets using approximation instead of exact aggregation.
    • Since this is an aggregation it is not assured to be completely accurate, however, in most cases it is very close and acceptable considering the large performance boost it provides.
    • Note that the results other than approximated value returned are 100 percent accurate, it is only how the query is processed and the amounts that are approximated (for instance the departments with approximately $1M in sales will give the correct departments that are within 97 percent of $1M in sales with 95 percent accuracy … but, NOT give a department with only $100 in sales).
    • Explain Plan: with change from SORT GROUP BY to SORT AGGREGATE APPROX
    • Approximate Query can be as much as 100x+ Faster
      (Depending on the Query)

    In 12c R2, there were additional approximate percentile functions and the capability to reuse approximate aggregations for multiple queries (via materialized views and query rewrite). Toggle to this mode:

    Instead of (100% accurate – 12.1.0.2):

    select count(distinct(empno))
    from emp;

    Use this for speed (97% accurate – 12.1.0.2):

    select approx_count_distinct(empno)
    from emp;

    alter session set approx_for_count_distinct = TRUE;
    (12.2 only – all distinct counts are all approximate) - Careful!

    Approximate Query – 12cR2 Only

    Additional initialization parameters:

    approx_for_aggregation=TRUE
    approx_for_percentile=TRUE

    Additional 12cR2 Approximate Functions:

    • APPROX_COUNT_DISTINCT_DETAIL
    • APPROX_COUNT_DISTINCT_AGG
    • TO_APPROX_COUNT_DISTINCT
    • APPROX_MEDIAN
    • APPROX_PERCENTILE
    • APPROX_PERCENTILE_DETAIL
    • APPROX_PERCENTILE_AGG
    • TO_APPROX_PERCENTILE
    • Also in 12cR2 is support for Materialized Views and Query Rewrite

    In the next few parts, we'll take a look at Pluggable Databases, including 12cR2 PDB refreshes and hot clones, 12cR2 Flashback PDB, In-Memory Column stores (IM), 12c Security Enhancements, AWR, 12c R1 & R2 partitioning enhancements, 12c New Hints, and compression changes for impdp.

    References:

    Oracle12c Release 2 Performance Tuning Tips & Techniques; Richard J. Niemiec (Oracle Press)
    Oracle 12c Beta Documentation & Beta Database
    New Optimizer Features in 11g / In-Memory; Maria Colgan
    www.ioug.org, www.oracle.com, en.wikipedia.org, technet.oracle.com
    Exadata & Other References
    Oracle11g Performance Tuning Tips & Techniques; Rich Niemiec (Oracle Press/McGraw-Hill)

    All companies and product names are trademarks or registered trademarks of the respective owners.

    Neither IOUG nor the author guarantee this document to be error-free. Please provide comments/questions to richniemiec@gmail.com. I am always looking to improve!

    Rich Niemiec ©2017. This document cannot be reproduced without expressed written consent from Rich Niemiec, but may be reproduced or copied for IOUG use.

    Released: May 26, 2017, 8:49 am | Updated: May 30, 2017, 7:39 am
    Keywords: Feature | 12c | best practices | Oracle Database 12c | Oracle Database In Memory | rich niemiec


    Copyright © 2017 Communication Center. All Rights Reserved
    All material, files, logos and trademarks within this site are properties of their respective organizations.
    Terms of Service - Privacy Policy - Contact

    Independent Oracle Users Group
    330 N. Wabash Ave., Suite 2000, Chicago, IL 60611
    phone: 312-245-1579 | email: ioug@ioug.org

    IOUG Logo

    Copyright © 1993-2017 by the Independent Oracle Users Group
    Terms of Use | Privacy Policy