Oracle 19c has been released quite a while ago already and some customers already run it in Production. However, as it is the long term supported release, I thought I blog about some interesting information and features around 19c to encourage people to migrate to it.
Download Oracle 19c:
https://www.oracle.com/technetwork/database/enterprise-edition/downloads
or
https://edelivery.oracle.com (search e.g. for “Database Enterprise Edition”)
Docker-Images:
https://github.com/oracle/docker-images/tree/master/OracleDatabase
Oracle provides different offerings for 19c:
On-premises:
– Oracle Database Standard Edition 2 (SE2)
– Oracle Database Enterprise Edition (EE)
– Oracle Database Enterprise Edition on Engineered Systems (EE-ES)
– Oracle Database Personal Edition (PE)
Cloud:
– Oracle Database Cloud Service Standard Edition (DBCS SE)
– Oracle Database Cloud Service Enterprise Edition (DBCS EE)
– Oracle Database Cloud Service Enterprise Edition -High Performance (DBCS EE-HP)
– Oracle Database Cloud Service Enterprise Edition -Extreme Performance (DBCS EE-EP)
– Oracle Database Exadata Cloud Service (ExaCS)
REMARK: When this Blog was released the Autonomous DB offerings provided by Oracle did not run on 19c yet (they actually ran on 18c).
Unfortunately some promising 19c new features are only available on Exadata. If that’s the case (like for Automatic Indexing) then you can still test the feature on EE after setting:
SQL> alter system set "_exadata_feature_on"=TRUE scope=spfile;
and a DB-Restart.
REMARK: DO THAT ON YOUR OWN TESTSYSTEMS ONLY AND USE INTERNAL ORACLE PARAMETERS ONLY WHEN ORACLE SUPPORT RECOMMENDS TO DO SO.
Anyway, there are lots of new features and I wanted to share some interesting of them with you and provide some examples.
REMARK: You may check https://www.oracle.com/a/tech/docs/database19c-wp.pdf as well
1. Automatic Indexing (only available on EE-ES and ExaCS)
Oracle continually evaluates the executing SQL and the underlying tables to determine which indexes to automatically create and which ones to potentially remove.
Documentation:
You can use the AUTO_INDEX_MODE configuration setting to enable or disable automatic indexing in a database.
The following statement enables automatic indexing in a database and creates any new auto indexes as visible indexes, so that they can be used in SQL statements:
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
The following statement enables automatic indexing in a database, but creates any new auto indexes as invisible indexes, so that they cannot be used in SQL statements:
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');
The following statement disables automatic indexing in a database, so that no new auto indexes are created, and the existing auto indexes are disabled:
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');
Show a report of automatic indexing activity:
set serveroutput on size unlimited lines 200 pages 200
declare
report clob := null;
begin
report := DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY();
dbms_output.put_line(report);
end;
/
In a test I ran some statements repeatedly on a table T1 (which contains 32 times the data of all_objects). The table has no index:
SQL> select * from t1 where object_id=:b1;
SQL> select * from t1 where data_object_id=:b2;
After some time indexes were created automatically:
SQL> select table_name, index_name, auto from ind;
TABLE_NAME INDEX_NAME AUT
-------------------------------- -------------------------------- ---
T1 SYS_AI_5mzwj826444wv YES
T1 SYS_AI_gs3pbvztmyaqx YES
2 rows selected.
SQL> select dbms_metadata.get_ddl('INDEX','SYS_AI_5mzwj826444wv') from dual;
DBMS_METADATA.GET_DDL('INDEX','SYS_AI_5MZWJ826444WV')
------------------------------------------------------------------------------------
CREATE INDEX "CBLEILE"."SYS_AI_5mzwj826444wv" ON "CBLEILE"."T1" ("OBJECT_ID") AUTO
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
2. Real-Time Statistics (only available on EE-ES and ExaCS)
The database automatically gathers real-time statistics during conventional DML operations. You can see in the Note-section of dbms_xplan.display_cursor when stats used to optimize a Query were gathered during DML:
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID 7cd3thpuf7jxm, child number 0
-------------------------------------
select * from t2 where object_id=:y
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 24048 (100)| |
|* 1 | TABLE ACCESS FULL| T2 | 254 | 31242 | 24048 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=:Y)
Note
-----
- dynamic statistics used: statistics for conventional DML
3. Quarantine problematic SQL (only available on EE-ES and ExaCS)
Runaway SQL statements terminated by Resource Manager due to excessive consumption of processor and I/O resources can now be automatically quarantined. I.e. instead of letting the SQL run until it reaches a resource plan limit, the SQL is not executed at all.
E.g. create a resource plan which limits SQL-exec-time for User CBLEILE to 16 seconds:
begin
-- Create a pending area
dbms_resource_manager.create_pending_area();
...
dbms_resource_manager.create_plan_directive(
plan => 'LIMIT_RESOURCE',
group_or_subplan => 'TEST_RUNAWAY_GROUP',
comment => 'Terminate SQL statements when they exceed the' ||'execution time of 16 seconds',
switch_group => 'CANCEL_SQL',
switch_time => 16,
switch_estimate => false);
...
-- Set the initial consumer group of the 'CBLEILE' user to 'TEST_RUNAWAY_GROUP'
dbms_resource_manager.set_initial_consumer_group('CBLEILE','TEST_RUNAWAY_GROUP');
end;
/
A SQL-Statement with SQL_ID 12jc0zpmb85tm executed by CBLEILE runs in the 16 seconds limit:
SQL> select count(*) X
2 from kill_cpu
3 connect by n > prior n
4 start with n = 1
5 ;
from kill_cpu
*
ERROR at line 2:
ORA-00040: active time limit exceeded - call aborted
Elapsed: 00:00:19.85
So I quarantine the SQL now:
set serveroutput on size unlimited
DECLARE
quarantine_config VARCHAR2(80);
BEGIN
quarantine_config := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID(
SQL_ID => '12jc0zpmb85tm');
dbms_output.put_line(quarantine_config);
END;
/
SQL_QUARANTINE_1d93x3d6vumvs
PL/SQL procedure successfully completed.
SQL> select NAME,ELAPSED_TIME,ENABLED from dba_sql_quarantine;
NAME ELAPSED_TIME ENA
---------------------------------------- -------------------------------- ---
SQL_QUARANTINE_1d93x3d6vumvs ALWAYS YES
Other CBLEILE-session:
SQL> select count(*) X
2 from kill_cpu
3 connect by n > prior n
4 start with n = 1
5 ;
from kill_cpu
*
ERROR at line 2:
ORA-56955: quarantined plan used
Elapsed: 00:00:00.00
SQL> !oerr ora 56955
56955, 00000, "quarantined plan used"
// *Cause: A quarantined plan was used for this statement.
// *Action: Increase the Oracle Database Resource Manager limits or use a new plan.
–> The SQL does not run for 16 seconds, but is stopped immediately (is under quarantine). You can define the Plan-Hash-Value for which a SQL should be in quarantine and define quarantine thresholds. E.g. 20 seconds for the elapsed time. As long as the resource plan is below those 20 seconds the SQL is under quarantine. If the resource plan is defined to be above 20 seconds execution time limit, the SQL is executed.
4. Active Standby DML Redirect (only available with Active Data Guard)
On Active Data Guard you may allow moderate write activity. These writes are then transparently redirected to the primary database and written there first (to ensure consistency) and then the changes are shipped back to the standby. This approach allows applications to use the standby for moderate write workloads.
5. Hybrid Partitioned Tables
Create partitioned tables where some partitions are inside and some partitions are outside the database (on filesystem, on a Cloud-Filesystem-service or on a Hadoop Distributed File System (HDFS)). This allows e.g. “cold” partitions to remain accessible, but on cheap storage.
Here an example with 3 partitions external (data of 2016-2018) and 1 partition in the DB (data of 2019):
!mkdir -p /u01/my_data/sales_data1
!mkdir -p /u01/my_data/sales_data2
!mkdir -p /u01/my_data/sales_data3
!echo "1,1,01-01-2016,1,1,1000,2000" > /u01/my_data/sales_data1/sales2016_data.txt
!echo "2,2,01-01-2017,2,2,2000,4000" > /u01/my_data/sales_data2/sales2017_data.txt
!echo "3,3,01-01-2018,3,3,3000,6000" > /u01/my_data/sales_data3/sales2018_data.txt
connect / as sysdba
alter session set container=pdb1;
CREATE DIRECTORY sales_data1 AS '/u01/my_data/sales_data1';
GRANT READ,WRITE ON DIRECTORY sales_data1 TO cbleile;
CREATE DIRECTORY sales_data2 AS '/u01/my_data/sales_data2';
GRANT READ,WRITE ON DIRECTORY sales_data2 TO cbleile;
CREATE DIRECTORY sales_data3 AS '/u01/my_data/sales_data3';
GRANT READ,WRITE ON DIRECTORY sales_data3 TO cbleile;
connect cbleile/difficult_password@pdb1
CREATE TABLE hybrid_partition_table
( prod_id NUMBER NOT NULL,
cust_id NUMBER NOT NULL,
time_id DATE NOT NULL,
channel_id NUMBER NOT NULL,
promo_id NUMBER NOT NULL,
quantity_sold NUMBER(10,2) NOT NULL,
amount_sold NUMBER(10,2) NOT NULL
)
EXTERNAL PARTITION ATTRIBUTES (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY sales_data1
ACCESS PARAMETERS(
FIELDS TERMINATED BY ','
(prod_id,cust_id,time_id DATE 'dd-mm-yyyy',channel_id,promo_id,quantity_sold,amount_sold)
)
REJECT LIMIT UNLIMITED
)
PARTITION BY RANGE (time_id)
(
PARTITION sales_2016 VALUES LESS THAN (TO_DATE('01-01-2017','dd-mm-yyyy')) EXTERNAL
LOCATION ('sales2016_data.txt'),
PARTITION sales_2017 VALUES LESS THAN (TO_DATE('01-01-2018','dd-mm-yyyy')) EXTERNAL
DEFAULT DIRECTORY sales_data2 LOCATION ('sales2017_data.txt'),
PARTITION sales_2018 VALUES LESS THAN (TO_DATE('01-01-2019','dd-mm-yyyy')) EXTERNAL
DEFAULT DIRECTORY sales_data3 LOCATION ('sales2018_data.txt'),
PARTITION sales_2019 VALUES LESS THAN (TO_DATE('01-01-2020','dd-mm-yyyy'))
);
insert into hybrid_partition_table values (4,4,to_date('01-01-2019','dd-mm-yyyy'),4,4,4000,8000);
commit;
SQL> select * from hybrid_partition_table where time_id in (to_date('01-01-2017','dd-mm-yyyy'),to_date('01-01-2019','dd-mm-yyyy'));
PROD_ID CUST_ID TIME_ID CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- --------- ---------- ---------- ------------- -----------
2 2 01-JAN-17 2 2 2000 4000
4 4 01-JAN-19 4 4 4000 8000
2 rows selected.
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID c5s33u5kanzb5, child number 0
-------------------------------------
select * from hybrid_partition_table where time_id in
(to_date('01-01-2017','dd-mm-yyyy'),to_date('01-01-2019','dd-mm-yyyy'))
Plan hash value: 2612538111
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 83 (100)| | | |
| 1 | PARTITION RANGE INLIST | | 246 | 21402 | 83 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 2 | TABLE ACCESS HYBRID PART FULL| HYBRID_PARTITION_TABLE | 246 | 21402 | 83 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 3 | TABLE ACCESS FULL | HYBRID_PARTITION_TABLE | | | | |KEY(I) |KEY(I) |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter((SYS_OP_XTNN("HYBRID_PARTITION_TABLE"."AMOUNT_SOLD","HYBRID_PARTITION_TABLE"."QUANTITY_SOLD","HYBRID_PARTITION_TABLE"."PROMO_ID","HYBRID_PARTITION_TABLE"."CHANNEL_ID","HYBRID_PARTITION_TABLE"."TIME_ID","HYBRID_PARTITION_TABLE"."CUST_ID","HYBRID_PARTITION_TABLE"."PROD_ID") AND INTERNAL_FUNCTION("TIME_ID")))
3 - filter((SYS_OP_XTNN("HYBRID_PARTITION_TABLE"."AMOUNT_SOLD","HYBRID_PARTITION_TABLE"."QUANTITY_SOLD","HYBRID_PARTITION_TABLE"."PROMO_ID","HYBRID_PARTITION_TABLE"."CHANNEL_ID","HYBRID_PARTITION_TABLE"."TIME_ID","HYBRID_PARTITION_TABLE"."CUST_ID","HYBRID_PARTITION_TABLE"."PROD_ID") AND INTERNAL_FUNCTION("TIME_ID")))
6. Memoptimized Rowstore
Enables fast data inserts into Oracle Database 19c from applications, such as Internet of Things (IoT), which ingest small, high volume transactions with a minimal amount of transactional overhead.
7. 3 PDBs per Multitenant-DB without having to pay for the Multitenant option
Beginning with 19c it is allowed to create 3 PDBs in a Container-DB without requiring the Mutitenant-Option license from Oracle. As the single- or multi-tenant DB becomes a must in Oracle 20, it is a good idea to start using the container-DB architecture with 19c already.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/11/CBL_web-min-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/JDU_web-min-scaled.jpg)