The Best Oracle 12c R1 & R2 New Features Part 1
-
Posted by Quest Customer Learning Team
- Last updated 6/27/19
- Share
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:
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!
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.
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.