As promised, this is my second instalment of Oracle 12cR2 i.e. 12.2.0.1 Release. In Part 1, i have recapped few of the 12c R1 Features along to some highlights on what was coming. Part 3 will contain other useful new features and will also be my last instalment to New Features Blogging.
In this Part i will try to highlight most of the 12cR2 features which i found to be most awaited ones for DBA's .
I will divide this post in sections like Admin, Backup/Recovery, Performance/Tuning, Grid, RAC, and Network...
The post is not intended to collate all the new features, If you want to read about all of that which have changed in this release probably you might want to read the New Features Guide:
Starting this release you can set the History environment variable to on and get this history of last 100 executed queries by default.
With the new Oracle Database 12c Release 2 (12.2) ability to declare collations for columns, you can declare a column to always be compared in a case-insensitive way. Oracle Database supports case-insensitive collations, such as BINARY_CI or GENERIC_M_CI.
This feature can help developers declare data as case-insensitive and do not have to add explicit uppercasing operations to SQL statements or create function based indexes.
Example:
At table level you can define collation like :-
Or at column level you can modify it like :
For other columns the collation is inherited from the table. Also another important point to remember is the default collation of the table can be changed anytime.
Sharding is a data tier architecture in which data is horizontally partitioned across independent databases. Each database in a shard is hosted on a dedicated server with its own local resources like memory/cpu/disk and flash and bring them in a one virtual pool makes up a single virtual database or Sharded Database.
Following is an illustration of how a table will look in a Sharded database architecture.
I will add more specific contents to each topic here in upcoming blogs when I am done with my test cases on the same. I am in process of evaluating the same and will come up with best practises and other advantages and disadvantages as well...
Interestingly I would also like to evaluate this technology against other No-Sql databases which uses Sharding.
Object : The name of the resource is “session.”
Address : The start address of the memory region being quarantined is 0000000078.
Bytes : The resource is using 9528 bytes of memory in quarantine.
Error : The message of the error that caused the resource to be placed in quarantine is “ORA-00600 internal error code.”
Timestamp : The timestamp shows the date and time of the error.
In 12.2 there is a single “alter table .. modify ..” command to transform a regular table into a partitioned one. The “alter table” includes the partition type and key clauses to alters the table.
The conversion of table can be done in online and offline mode. Below example represents Online operation to convert a non-partition table to partition one.
Example:
For instance, when a client requires database access, the connection broker picks up a server process from the pool and hands it off to the client. The client is directly connected to the server process until the request is served. After the server has finished, the server process is released into the pool. The connection from the client is restored to the broker. Session memory is allocated from the PGA.
The
6.1.2 Cursor-Duration Temporary Tables
Complex queries sometimes process the same query block multiple times. To avoid this scenario, Oracle Database can create temporary tables for the query results and store them in memory for the duration of the cursor called Cursor Duration Temp Tables. For complex operations this optimization enhances the materialization of intermediate results from repetitively used subqueries which improve performance and optimize I/O.
When the database uses cursor-duration temporary tables, the keyword
For More information refer to Oracle Documentation:-
SQL statements commonly include expressions such as (
Example:
COL DBOP_NAME FORMAT a10
COL STATUS FORMAT a10
COL ID FORMAT 999
SELECT DBOP_NAME, DBOP_EXEC_ID AS ID, STATUS, CPU_TIME, BUFFER_GETS
FROM V$SQL_MONITOR WHERE DBOP_NAME IS NOT NULL ORDER BY DBOP_EXEC_ID;DBOP_NAME ID STATUS CPU_TIME GETS
---------- -- ---------- -------- ----
sh_count 1 EXECUTING 24997 65
I have tried to jotted down the new Oracle 12.2 Features. Hope you find this article useful. I am still in process of going through documentation and will soon start testing the same. There are still a lot of other features like SQL, Hot Cloning, Rman, Performance Tuning and Security which can be useful in day to day DBA/ Development. I will come up with detailed discussions on few of the important features in my next Blog.
In this Part i will try to highlight most of the 12cR2 features which i found to be most awaited ones for DBA's .
I will divide this post in sections like Admin, Backup/Recovery, Performance/Tuning, Grid, RAC, and Network...
The post is not intended to collate all the new features, If you want to read about all of that which have changed in this release probably you might want to read the New Features Guide:
Section 1 : SQL
1. Sqlplus Command Line:
1.1 History :
Starting this release you can set the History environment variable to on and get this history of last 100 executed queries by default.
SQL> set history on
SQL> set history 100
SQL> show history
History is ON and set to "100"
History is ON and set to "100"
SQL> history
1 show history
2 show user
3 desc dual
* 4 select * from dual;
1 show history
2 show user
3 desc dual
* 4 select * from dual;
SQL> set history off
SQL> set history off
2. Globalization Support:
This feature can help developers declare data as case-insensitive and do not have to add explicit uppercasing operations to SQL statements or create function based indexes.
Example:
At table level you can define collation like :-
CREATE TABLE emp
(
empno VARCHAR2(5) PRIMARY KEY,
ename VARCHAR2(50),
)
DEFAULT COLLATION BINARY_CI ;
(
empno VARCHAR2(5) PRIMARY KEY,
ename VARCHAR2(50),
)
DEFAULT COLLATION BINARY_CI ;
Or at column level you can modify it like :
ALTER TABLE emp ADD gender VARCHAR2(1) COLLATE BINARY_CI;
For other columns the collation is inherited from the table. Also another important point to remember is the default collation of the table can be changed anytime.
Section 2 : Administration And Availability
2.1 Sharding:
Following is an illustration of how a table will look in a Sharded database architecture.
I will add more specific contents to each topic here in upcoming blogs when I am done with my test cases on the same. I am in process of evaluating the same and will come up with best practises and other advantages and disadvantages as well...
Interestingly I would also like to evaluate this technology against other No-Sql databases which uses Sharding.
2.2 Quarantined Objects:
Object quarantine enables an Oracle database to function even when there are corrupted, unrecoverable objects. The V$QUARANTINE view contains information about quarantined objects.
Object quarantine isolates an object that has raised an error and monitors the object for impacts on the system.
Example: This query shows the resources that are currently quarantined.
COLUMN OBJECT FORMAT A10
COLUMN ADDRESS FORMAT A10
COLUMN BYTES FORMAT 999999999
COLUMN ERROR FORMAT A20
COLUMN TIMESTAMP FORMAT A20
COLUMN ADDRESS FORMAT A10
COLUMN BYTES FORMAT 999999999
COLUMN ERROR FORMAT A20
COLUMN TIMESTAMP FORMAT A20
SELECT OBJECT, ADDRESS, BYTES, ERROR, TIMESTAMP FROM V$QUARANTINE;
OBJECT ADDRESS BYTES ERROR TIMESTAMP
---------- ---------- ---------- --------------------------------
session 0000000078 9528 ORA-00600: internal 11-Nov-16 01.17.42.2
---------- ---------- ---------- --------------------------------
session 0000000078 9528 ORA-00600: internal 11-Nov-16 01.17.42.2
The above columns contains below information:
Object : The name of the resource is “session.”
Address : The start address of the memory region being quarantined is 0000000078.
Bytes : The resource is using 9528 bytes of memory in quarantine.
Error : The message of the error that caused the resource to be placed in quarantine is “ORA-00600 internal error code.”
Timestamp : The timestamp shows the date and time of the error.
2.3 Partitioning Enhancements
2.3.1 New alter table to partitioned table
The conversion of table can be done in online and offline mode. Below example represents Online operation to convert a non-partition table to partition one.
Example:
ALTER TABLE emp MODIFY
PARTITION BY RANGE (emp_no) INTERVAL (80)
( PARTITION Part1 VALUES LESS THAN (100),
PARTITION Part2 VALUES LESS THAN (500)
) ONLINE
UPDATE INDEXES
( IDX1_SAL LOCAL,
IDX2_EMP_NO GLOBAL PARTITION BY RANGE (emp_no)
( PARTITION allothers VALUES LESS THAN (MAXVALUE))
);
PARTITION BY RANGE (emp_no) INTERVAL (80)
( PARTITION Part1 VALUES LESS THAN (100),
PARTITION Part2 VALUES LESS THAN (500)
) ONLINE
UPDATE INDEXES
( IDX1_SAL LOCAL,
IDX2_EMP_NO GLOBAL PARTITION BY RANGE (emp_no)
( PARTITION allothers VALUES LESS THAN (MAXVALUE))
);
2.3.2 New Automatic List Partitioning
Automatic List partitioning is a nice feature which enable creation of on demand list partition on frequently modified table.
Example:
CREATE TABLE emp_List_auto
(
emp_id NUMBER(5),
emp_name VARCHAR2(30),
dept_name VARCHAR2(20),
)
PARTITION BY LIST (dept_name) AUTOMATIC
(PARTITION P_D_Name VALUES ('IT')
);
(
emp_id NUMBER(5),
emp_name VARCHAR2(30),
dept_name VARCHAR2(20),
)
PARTITION BY LIST (dept_name) AUTOMATIC
(PARTITION P_D_Name VALUES ('IT')
);
2.4 In-Memory option
2.4.1 Standby Database
Oracle 12c introduced the in-memory option in release 1 , however the same was not available in active standby. In Release 2 Active Standby's can be configured to use in-memory, allowing us to configure in-memory column store for reports and analytic.2.4.2 In-Memory – Dynamic Resizing
In 12c Release 1 In-Memory Column store was static and incase the same was required to change , database restart was required. In 12.2 we can increase the In-Memory column store size dynamically but the same can't be reduced dynamically.2.5 Dictionary with long identifiers
It was so annoying that object and column names were limited to 30 bytes which might was not be a major concern for all time Oracle Developers/DBA's but for Companies / DBA's involved in Migration projects to migrate the SQL-Server/Sybase databases to Oracle had challenges with this Limit but not anymore, 128 bytes is the new limit!.
2.6 PDB Characterset
With 12c R2 we can set a different characterset per PDB with a Pre-Requisite for the root container to use AL32UTF8 which is the super-set of all others characterset and is also default characterset with 12cR2 release onwards.Section 3. Flashback Pluggable Database
In Database 12.2, flashback can be done on the PDB level with an prerequisite of database been put into local undo mode which also means that each PDB has its own undo tablespace.
Example:
Altering the database to enable local undo, require instance shutdown-startup.
SQL> alter database local undo on;
Database altered.
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL> alter pluggable database pdb1 close immediate;
Pluggable database altered.
SQL> flashback pluggable database pdb1 to timestamp systimestamp - interval '20' minute;
Flashback complete.
SQL> alter pluggable database pdb1 open resetlogs;
Pluggable database altered.
FLASHBACK_ON
------------------
YES
SQL> alter pluggable database pdb1 close immediate;
Pluggable database altered.
SQL> flashback pluggable database pdb1 to timestamp systimestamp - interval '20' minute;
Flashback complete.
SQL> alter pluggable database pdb1 open resetlogs;
Pluggable database altered.
Section 4: Networking
Database Resident Connection Pooling:
Like Shared Server and Dedicated Server Architecture, DRCP is yet another connection management framework which enables less utilization of resources and giving performance benefits. Majorly for applications which use connection pooling.
When the first request is received from a client, the Connection Broker picks an available pooled server and hands off the client connection to the pooled server. If no pooled servers are available,
the Connection Broker creates one. If the pool has reached its maximum size, the client request is placed on the wait queue until a pooled server is available. Releasing database resources involves releasing the pooled server to the pool.
Section 5 : Analytics
Analytic Views
Oracle describes Analytic views to provide a fast and efficient way to create analytic queries of data stored in existing database tables and views. The diagram depicts the pictorial representation of Analytic view.
The model is made up of three objects.
- Dimension Attribute - Defines a dimension along with the hierarchical levels also multiple hierarchies are possible.
- Hierarchy - Defines the hierarchical structure of a dimension with parent/child.
- Analytic View - Brings together the hierarchies and defines the measures.
Section 6 : Security
Internal tablespace encryption
Now it is possible to encrypt internal tablespace i.e. system, sysaux, undo as well.
Section 7 : Performance and Tuning
6.1 SQL Tuning
6.1.1 New Initialization Parameters to controls for adaptive plans and adaptive statistics
The
OPTIMIZER_ADAPTIVE_PLANS initialization parameter enables (default) or disables adaptive plans. The OPTIMIZER_ADAPTIVE_STATISTICS initialization parameter enables or disables (default) adaptive statistics.
6.1.2 Cursor-Duration Temporary Tables
Complex queries sometimes process the same query block multiple times. To avoid this scenario, Oracle Database can create temporary tables for the query results and store them in memory for the duration of the cursor called Cursor Duration Temp Tables. For complex operations this optimization enhances the materialization of intermediate results from repetitively used subqueries which improve performance and optimize I/O.When the database uses cursor-duration temporary tables, the keyword
CURSOR DURATION MEMORY appears in the execution plan.For More information refer to Oracle Documentation:-
6.1.3 Expression tracking
+) or (-) in the query including internal or user defined functions. The Expression Statistics Store (ESS) maintains usage information about expressions identified during compilation and captured during execution.6.1.4 Creating a Database Operation
Using this feature enables DBA or privileged user to monitor a session and find details about the operation going on.
Creating a database operation involves supplying a name and defining its beginning and end times.
Start a database operation by using the
To begin the operation in a different session, specify the combination of SID and
Creating a database operation involves supplying a name and defining its beginning and end times.
Start a database operation by using the
DBMS_SQL_MONITOR.BEGIN_OPERATION function, and end it by using the DBMS_SQL_MONITOR.END_OPERATION procedure.To begin the operation in a different session, specify the combination of SID and
SERIAL#. Example:
COL DBOP_NAME FORMAT a10
COL STATUS FORMAT a10
COL ID FORMAT 999
SELECT DBOP_NAME, DBOP_EXEC_ID AS ID, STATUS, CPU_TIME, BUFFER_GETS
FROM V$SQL_MONITOR WHERE DBOP_NAME IS NOT NULL ORDER BY DBOP_EXEC_ID;DBOP_NAME ID STATUS CPU_TIME GETS
---------- -- ---------- -------- ----
sh_count 1 EXECUTING 24997 65
No comments:
Post a Comment