There is a lot of excitement about Oracle upcoming Release of 12c i.e. 12c Release 2. I thought of collating the information available on the web and put it all together to bring some awareness about what is coming in 12c Release 2. Have published Part-2 which contains a lot of information about other 12cR2 new features.
Before we begin with 12c Release 2, lets recollect what major New Features were present in Oracle 12c R1. I wanted to start with this as many of us are still using 11G and are in transition phase to upgrade the databases to 12c.
Some of the highlights of 12c Release 1 New Features are as follows:-
1. SQL and PL/SQL
1) Can drop an Index online.
DROP INDEX ONLINE.
2) Can drop an constraint online.
DROP CONSTRAINT ONLINE .
3) Can Set unused columns online
SET UNUSED COLUMN ONLINE.
4) Make an index visible of invisible to Optimizer.
ALTER INDEX VISIBLE / INVISIBLE .
5) Can Create Multiple indexes on Same Columns.
6) Can Make an Column Visible / Invisible.
SET COLINVISIBLE ON
CREATE TABLE INV (COL1 NUMBER,CL2 NUMBER INVISIBLE);
7) 12c onwards now you can have an identity for a columns where the data in the column is auto generated.
CREATE TABLE IDEN (COL1 NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1000 INCREMENT BY 10));
8) VARCHAR2 is now 32676 bytes.
9) New Read Privilege, Select access can lock FOR UPDATE.
10) New Join syntax, Lateral Clause for inline Views called Lateral Views.
select * from emp e, (select * from dept d where e.deptno=d.deptno)
ORA-00904 - e.deptno Invalid Identifier.
select * from emp e, LATERAL(select * from dept d where e.deptno=d.deptno)
Definition: A lateral view is a view that references columns from a table that is not inside the view.
The above Statement Succeeds with the usage of LATERAL Keyword.
select * from ..... fetch first 10 rows only
select * from ..... offset 10 rows fetch first 10 rows only
select * from ..... offset 10 rows fetch first 0.1 percent rows only
select * from ..... offset 10 rows fetch first 0.1 percent rows with ties
12) Can now move Partition and sub-partition online.
13) Truncate table cascade.
14) Session level Sequences
CREATE SEQUENCE my_seq START WITH 1 INCREMENT BY 1 SESSION;
ALTER SEQUENCE my_seq GLOBAL|SESSION;
15) Now developer can Create a Function or Procedure within a SQL inside WITH Clause.
WITH
PROCEDURE|FUNCTION test1 (…)
BEGIN
<logic>
END;
SELECT <reference_your_function|procedure_here> FROM table_name;
/
16) dbms_sql.return_result , returns formatted results in SQLPLUS.
17) In-Database-Archiving - This option enables to mark records in a table as not active(called later archive records). As default archive records are not visible in Oracle sessions. The records can be later compressed or deleted.
The reason to add such option was to keep both versions of records (active and not active) in the same table instead of making backup(not active) on tape and delete them(not active) from a table.
CREATE TABLE test_tbl( id NUMBER) ROW ARCHIVAL;
2. OPTIMIZER
1) Partial Join Evaluation.
This is a useful feature which gets automatically used by Oracle 12c Optimizer, In Pre-Release to 12c we used IN or EXISTS operator to to accomplish the same but 12c onwards we can use regular joins which can evaluated partially once it finds the information it is looking for.
Ref : http://blog.dbi-services.com/partial-join-evaluation-in-oracle-12c/
2) Null accepting semi-joins
3) Scalar Subquery Unnesting
4) Multi-Table Left Outer Join
5) Adaptive Query Optimization
1. Adaptive Plans
2. Adaptive Parallel Distribution Methods
3. Adaptive Statistics
6) Hybrid Histograms
7) Online statistics gathering
- When an index is created
- CTAS
- IAS
8) Session level statistics on Global Temporary Tables
9) Automatic column group detection.
10) Real Time ADDM
3. DBA - Administration
There has been significant enhancement of features in Administration as well. Few of the useful ones are as follows:1) Online Datafile Movement.
2) Index Compression- 1x to 3x greater.
3) DDL Logging.
4) Temporary Undo.
5) Table level Recovery in RMAN
6) Restricting PGA Size
7) 12c DB upgrade Enhancements, like running upgrades in parallel.
8) Restore/Recover Data files over the network using DB Service.
9) Gathering Stats concurrently on multiple tables
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=4;
SQL> EXEC DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT', 'ALL');
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');
10) Data Redaction: This new feature enables to easily prevent the display of sensitive data to end-users by performing redaction in each application. It’s very simple to implement and very efficient. Data are modified on the-fly right before results are returned to applications.
11) Oracle Caching mode: It enables to force caching of all segments into buffer cache automatically(when the segments are accessed).
ALTER DATABASE FORCE FULL DATABASE CACHING;
12) Automatic BIG Table Caching.
13) Zone Maps: For full table access, zone maps allow I/O pruning of data based on the physical location of the data on disk, acting like an anti-index.
There are many other new features introduced in Oracle 12C Release 1, but with new release of Oracle 12c i.e. Oracle 12c Release 2 more new features have been introduced and are interesting.
12c Release 2 New Feature
***Database for the Cloud***
1. SQL and PL/SQL
1) With 12c Release 2 Table and column names can be up to 128
characters long up from 30 characters.
2) Oracle have now introduced Oracle LIVE-SQL. You can create your own login and use the same, this don't require a database to run your queries. It is a very good tool for developers to test many functionalities of oracle without having a database.
2. DBA-Administration
- Hot Cloning: In 12c R1 pluggable databases offered us with the opportunity to clone databases and put them elsewhere which was really helpful. Now Oracle has gone even further and with Release 2 onwards you can get a running database cloned towards a new instance, without shutting it down or losing data.
- Tenant Relocation: Tenant(PDB) can now be relocated online, then unplug/plug operation introduced in 12c Release 1.
2) Application Container: Most
Pluggable Databases share
application objects mostly code and
metadata. Oracle 12c R2
introduced something called as Application Container to add this as a feature.
This fulfils the cloud objective, As in previous release even with
the addition of the pluggable databases we still needed to update the
applications in all the separate databases. Now one
can have their own separate database
/ pluggable database and
share completely the same application
logic.
It
really makes management easier;
all you need to do is to apply updates to the shared application container.
- Shared Application Objects i.e. Code , Metadata and Data.
-
Updates can be applied to application Container.
3) SQL in Silicon- Super Server: Adds co-processors to all 32 cores of the SPARC M7 that offload and accelerate important data functions, dramatically improving efficiency and performance of database applications. Critical functions accelerated by these new co-processors include memory de-compression, memory scan, range scan, filtering, and join assist. Offloading these functions to co-processors greatly increases the efficiency of each CPU core, lowers memory utilization. This enables
- 3x Faster Joins.
- 10x Faster Expressions
- 60X Faster JSON.
4) In-Memory with Oracle Database Release 2:
2) Dynamic data movement from storage and memory.
3) Sharding : Sharding is an application-managed scaling technique using many independent databases. The data is split into multiple databases (called shards). Each database holds a subset of the data, this can be either be range or hash.
You
can also
split the shards into multiple shards
as the data volume or access to this data grows. Shards then can be replicated
for availability and scalability reasons.
Sharding
be really nicely used for
applications that require extreme scalability. 4) Oracle now increased that number of pluggable database in one CDB to 4096.
5) AWR reports are now available on Active Data Guard standby databases.
I will share the new features of Oracle 12c Release 2 in much details in Part-2 of this post.
3. SOME OTHER FEATURE WHICH MIGHT BE OF YOUR INTEREST
1) Reading Alert Log from SQLPLUS:- In Oracle 11g release, Oracle did some improvement in diagnosability Infrastructure, Starting from 11g, There is a fixed table X$DBGALERTEXT, when you query it, Oracle reads the log.xml from alert directory (which contains all the data what alert.log does), parses it and returns the details back as rows.
SQL> select message_text from X$DBGALERTEXT where rownum <= 20;
SQL> select lpad(' ',lvl,' ')||logical_file file_name from X$DBGDIREXT where rownum <=20;
2) To Write Custom Message/Error we can use below command:-
EXEC dbms_system.ksdwrt(2, 'ORA-00900: Custom message in alert log.');
Summary
More to come in my another blog i.e. PART-2 continuation to this one. Stay Tuned....
