Oracle Database 18c/19c - Salvis

2y ago
84 Views
3 Downloads
1.15 MB
37 Pages
Last View : 1m ago
Last Download : 2m ago
Upload by : Mara Blakely
Transcription

Oracle Database 18c/19cNew Features for Database DevelopersPhilipp Salvisberg@phsalvisberghttps://www.salvis.com/blog

About MeTrivadian since April 2000– Senior Principal Consultant, Partner– Member of the Board of Directors– @phsalvisberg– https://www.salvis.com/blog– https://github.com/PhilippSalvisbergDatabase centric developmentModel Driven Software DevelopmentAuthor of free SQL Developer Extensions: PL/SQL Unwrapper, PL/SQL Cop,utPLSQL, plscope-utils, oddgen and Bitemp Remodeler221.02.2019Oracle Database 18c/19c - New Features for DB Developers

�–––––––––Qualified ExpressionsPolymorphic Table Functions (PTF)[NOT] PERSISTABLEOptional using clause in PTF 19c)JSON– SQL expression returning JSON data– LOB results for json value, json query, json table,json object, json array, json arrayagg, item methods– STRICT keyword– Extended input data types– New item methods: number(only), string(only),boolean(only), size, type– JSON data guide enhancements– JSON data dictionary views– MV ON Statement support for materialized views– JSON EQUAL (ignoring whitespace differences)– Performance improvements on JSON stored in LOBs– Support for longer JSON names in search indexes– Query rewrite for MVs with json table/ exist/ value 19c)– JSON update operations 19c)– Mapping JSON data to/from object/collection types 19c)– SQL/JSON syntax simplifications 19c)321.02.2019Analytic Views EnhancementsSchema Only AccountsData-bound collation supportPrivate Temporary TablesIntegration of AD Services into the Oracle DatabaseModifying Partition StrategyOnline Merging of PartitionsInline External TablesMemoptimized RowStore (Fast Lookup)Approximate Top-N Functions (count, sum, rank)Unique Sequence Numbers Across Shards 19c)In-Memory External Tables on Hive and Bigdata 19c)Bitmap Based Count Distinct Functions (bitmap count, .) 19c)Hybrid Partitioned Tables 19c)Memoptimized RowStore Fast Ingest 19c)DISTINCT option for listagg 19c)Unified Auditing for Top Level Statements only 19c)JDBC––––Accessing PL/SQL Associative Arrays (Keys and Values)REF Cursor as IN Bind VariableJava library for Reactive Streams Ingestion 19c)Set JDBC Properties via JDBC URL 19c)Oracle Database 18c/19c - New Features for DB Developers

About Old FeaturesDeprecatedDesupported– dbms data mining.get% functions– Oracle Multimedia DICOM– dbms xmlquery– Oracle XML DB (dbms xmlschema, )– dbms xmlsave– Oracle Streams 19c)– Oracle Fail Safe 19c)– Oracle Multimedia 19c)– Compilation parameter PLSQL DEBUG 19c)– PRODUCT USER PROFILE Table 19c)– – Non-CDB architecture 20c)– See Database Upgrade Guide for details421.02.2019Oracle Database 18c/19c - New Features for DB Developers

Qualified Expressions521.02.2019Oracle Database 18c/19c - New Features for DB Developers

Idea – Simplify Initialization of Records and ArraysInitialize Associative Array in Oracle Database 12c Release 2:DECLARETYPE t string type IS TABLE OF dept.loc%TYPE INDEX BY SIMPLE INTEGER;t dept t string type;BEGINt dept(10) : 'Accounting';t dept(20) : 'Research';t dept(30) : 'Sales';t dept(40) : 'Operations';dbms output.put line(t dept(10)); -- AccountingEND;621.02.2019Oracle Database 18c/19c - New Features for DB Developers

Initialize Associative Array of StringsDECLARETYPE t string type IS TABLE OF dept.loc%TYPE INDEX BY SIMPLE INTEGER;t dept t string type : t string type (10 'Accounting',20 'Research',30 'Sales',40 'Operations');BEGINdbms output.put line(t dept(10)); -- AccountingEND;721.02.2019Oracle Database 18c/19c - New Features for DB Developers

Initialize RecordDECLARETYPE r dept type IS RECORD (deptno dept.deptno%TYPE,dname dept.dname%TYPE,locdept.loc%TYPE);r dept r dept type : r dept type (deptno 10,dname 'Accounting',loc 'New York');BEGINdbms output.put line(r dept.dname); -- AccountingEND;821.02.2019Oracle Database 18c/19c - New Features for DB Developers

Initialize Associative Array of RecordsDECLARETYPE r dept type IS RECORD (dname dept.dname%TYPE,locdept.loc%TYPE);TYPE t dept type IS TABLE OF r dept type INDEX BY SIMPLE INTEGER;t dept t dept type : t dept type (10 r dept type ('Accounting', 'New York'),20 r dept type ('Research', 'Dallas'),30 r dept type ('Sales', 'Chicago'),40 r dept type ('Operations', 'Boston'));BEGINdbms output.put line(t dept(10).dname); -- AccountingEND;921.02.2019Oracle Database 18c/19c - New Features for DB Developers

Polymorphic Table Functions1021.02.2019Oracle Database 18c/19c - New Features for DB Developers

Idea – Dynamic I/O StructureSELECT * FROM ACCOUNTINGRESEARCHSALESOPERATIONSLOC-------NEW YORKDALLASCHICAGOBOSTONMD5 7A154378BF5D0SELECT * FROM ptf.hashed(emp);EMPNO----73697499 11ENAME-----SMITHALLEN21.02.2019JOBMGR HIREDATESAL COMM DEPTNO MD5 HASH--------- ----- -------- ----- ----- ------ -------------------------------CLERK7902 17.12.8080020 EAB6774264D52D9589A9864B4E168095SALESMAN7698 20.02.81 160030030 650663EFB818F4FE422027A0F8653084Oracle Database 18c/19c - New Features for DB Developers

Passing Named QueriesWITH empdept AS (SELECT e.empno, e.ename, e.deptno, d.dname, e.salFROM emp eJOIN dept dON d.deptno e.deptnoWHERE e.deptno 10)SELECT * FROM ptf.hashed(empdept);EMPNO----77827839793412ENAME DEPTNO DNAMESAL MD5 HASH------ ------ ---------- ----- -------------------------------CLARK10 ACCOUNTING 2450 97BD223F2A7AD85AE0034D1828104C28KING10 ACCOUNTING 5000 7FECE7D987171325B6A09E87E95694D7MILLER10 ACCOUNTING 1300 65D9DD948B3A406B683EAD6BD86FE29021.02.2019Oracle Database 18c/19c - New Features for DB Developers

Predicate PushdownSELECT empno, ename, sal, md5 hashFROM ptf.hashed(emp)WHERE deptno ------------------------------------------------ Id Operation Name Rows Bytes Cost (%CPU) Time --------------------------------------------- 0 SELECT STATEMENT 3 6111 2(0) 00:00:01 1 POLYMORPHIC TABLE FUNCTION HASHED 3 261 2 TABLE ACCESS BY INDEX ROWID BATCHED EMP 3 114 2(0) 00:00:01 * 3 INDEX RANGE SCAN EMP I01 3 1(0) 00:00:01 -through columnsPARTITION BY columns of table semantics PTF1321.02.2019Oracle Database 18c/19c - New Features for DB Developers

Declaration (Interface)CREATE OR REPLACE PACKAGE ptf ASFUNCTION describe(io tab IN OUT dbms tf.table t) RETURN dbms tf.describe t;PROCEDURE fetch rows;FUNCTION hashed(in tab IN TABLE)RETURN TABLE PIPELINEDROW POLYMORPHIC USING ptf;END ptf;1421.02.2019Oracle Database 18c/19c - New Features for DB Developersdescribe function– Called during cursor compilation– Defines output columns– Mandatoryfetch rows procedure– Called during execution 0.n times– Rowset size calculated at runtime( 1024, partitions: 0.n rowsets)– Optionalhashed function– Table argument is mandatory– Additional arguments are optionaland passed to describe, fetch rows– Row or table semantic– Can be a standalone function– Using clause can be omitted in thiscase (19c only)

Definition (Implementation)CREATE OR REPLACE PACKAGE BODY ptf ASFUNCTION describe(io tab IN OUT dbms tf.table t) RETURN dbms tf.describe t ISl new cols dbms tf.columns new t;BEGINFOR i IN 1 . io tab.column.count()LOOPio tab.column(i).pass through : TRUE;io tab.column(i).for read: TRUE;END LOOP;l new cols(1) : dbms tf.column metadata t(name 'md5 hash',type dbms tf.type varchar2,max len 32);RETURN dbms tf.describe t(new columns l new cols);END describe;1521.02.2019PROCEDURE fetch rows ISl row setdbms tf.row set t;l md5 hash dbms tf.tab varchar2 t;l row count pls integer;BEGINdbms tf.get row set(rowset l row set,row count l row count);FOR i IN 1 . l row count LOOPl md5 hash(i) : dbms crypto.hash(src CAST(dbms tf.row to char(l row set, i) AS CLOB),typ dbms crypto.hash md5);END LOOP;dbms tf.put col(1, l md5 hash);END fetch rows;END ptf;Oracle Database 18c/19c - New Features for DB Developers

Schema Only Accounts1621.02.2019Oracle Database 18c/19c - New Features for DB Developers

Idea – User Owning Objects w/o Login CredentialsSource: SQL Language Reference 18c1721.02.2019Oracle Database 18c/19c - New Features for DB Developers

Create SchemaCREATE USER demo schema -- no authentication is defaultDEFAULT TABLESPACE usersTEMPORARY TABLESPACE tempQUOTA UNLIMITED ON users;GRANT connect, resource TO demo schema;create sessionprivilege is required!1821.02.2019Oracle Database 18c/19c - New Features for DB Developers

Create Proxy UserCREATE USER philipp identified BY philipp;GRANT connect to philipp;ALTER USER demo schema GRANT CONNECT THROUGH philipp;1921.02.2019Oracle Database 18c/19c - New Features for DB Developers

Connect Via Proxy User[oracle@odb180 /] sqlplus philipp[demo schema]/philipp Connected to:Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - ProductionVersion 18.5.0.0.0SQL SELECT user, sys context('userenv', 'proxy user') proxy FROM dual;USERPROXY------------------------------ -----------------------------DEMO SCHEMAPHILIPP2021.02.2019Oracle Database 18c/19c - New Features for DB Developers

AdvantagesSchema account cannot be “locked” through– Password expiration– Wrong password entryManaged schema access– No password sharing– Proxy users have control over their passwordsSemantic separation of users and schemas– Users do not own objects– Schemas own objects2121.02.2019Oracle Database 18c/19c - New Features for DB DevelopersThe Pink Database Paradigm (PinkDB)

Private Temporary Tables2221.02.2019Oracle Database 18c/19c - New Features for DB Developers

Idea – Create Temporary In-Memory TableFAQs – the Differences23GlobalPrivateTemporary TableTemporary TableStorage type?Diskin MemoryIndexes?YesNoConstraints (not null, check)?YesNoWorks in read-only environment?NoYesImplicit commit on DDL (create, truncate, drop table)?YesNoDML honor transactions?YesYesStructure visible in other sessions?YesNoPredefined table name prefix?NoYes (ora ptt )Accessible via database link?YesNo21.02.2019Oracle Database 18c/19c - New Features for DB Developers

Example – No Implicit Commit by “CREATE PTT”CREATE TABLE t1 (c1 INTEGER, c2 VARCHAR2(30));INSERT INTO t1 VALUES (1, 'to be committed');COMMIT;INSERT INTO t1 VALUES (2, 'to be rollbacked');CREATE PRIVATE TEMPORARY TABLE ora ptt t2 (c1 INTEGER, c2 VARCHAR2(30))ON COMMIT PRESERVE DEFINITION;ROLLBACK;SELECT * FROM t1;C1 C2---------- -----------------------------1 to be committed2421.02.2019Oracle Database 18c/19c - New Features for DB Developers

Memoptimized RowStore(Fast Lookup)2521.02.2019Oracle Database 18c/19c - New Features for DB Developers

Idea – Fast Primary Key AccessSELECT valueFROM tWHERE key :keyStore table in memoryBypass the SQL execution layerExecute directly in the data access layer2621.02.2019Oracle Database 18c/19c - New Features for DB Developers

Configure DatabaseStore table completely in the SGAMemoptimized Pool– 75% for Table Buffers in Memoptimize Buffer Area– 25% for Primary Keys in Hash IndexConfigure Memoptimized Pool:ALTER SYSTEMSET memoptimize pool size 100MSCOPE SPFILE;SHUTDOWN IMMEDIATESTARTUPSource: Database Concepts 18c2721.02.2019Oracle Database 18c/19c - New Features for DB Developers

Configure Memoptimized TableCREATE TABLE t4 (keyINTEGERNOT NULL,value VARCHAR2(30 CHAR) NOT NULL,CONSTRAINT t4 pk PRIMARY KEY (key))SEGMENT CREATION IMMEDIATEMEMOPTIMIZE FOR READDelayed segment creation is not supportedAdditional step required to populate data into the Memoptimized Pool2821.02.2019Oracle Database 18c/19c - New Features for DB Developers

Populate Table Into Memoptimized PoolBEGINdbms memoptimize.populate(schema name USER,table name 'T4');END;Memoptimized Pool is populated in the backgroundThe “memopt r rows populated” statistics indicates the progressIn a multitenant architecture the population is managed in the CDB!2921.02.2019Oracle Database 18c/19c - New Features for DB Developers

Query with Autotrace – Execution PlanSELECT * FROM t4 WHERE key 42;KEY VALUE---------- -----------------------------42 ----- Id Operation Name Rows Bytes Cost (%CPU) Time --------------------------------------------- 0 SELECT STATEMENT 1 24 2(0) 00:00:01 1 TABLE ACCESS BY INDEX ROWID READ OPTIM T4 1 24 2(0) 00:00:01 * 2 INDEX UNIQUE SCAN READ OPTIM T4 PK 1 1(0) 00:00:01 cate Information (identified by operation ----2 - access("KEY" 42)3021.02.2019Oracle Database 18c/19c - New Features for DB Developers

Query with Autotrace – ----------------------------43 Requests to/from client43 SQL*Net roundtrips to/from client605 bytes received via SQL*Net from client83447 bytes sent via SQL*Net to client3 calls to get snapshot scn: kcmgss2 calls to kcmgcs2 execute count1 memopt r hits1 memopt r lookups44 non-idle wait count2 opened cursors cumulative1 opened cursors current2 parse count (total)1 session cursor cache count1 sorts (memory)2011 sorts (rows)46 user calls3121.02.2019Oracle Database 18c/19c - New Features for DB Developers“consistent gets” 0

Performance 0.17 msper“Context Switch”Bug,Fixed in 19cRun in Docker Container with Oracle Database 18c, Version 18.5 and “ exadata feature on TRUE”3221.02.2019Oracle Database 18c/19c - New Features for DB Developers 11%PerformanceGain

AvailabilityThis feature is available for the following Oracle Database offerings only:Oracle Database Enterprise Edition on Engineered Systems (EE-ES)Oracle Database Exadata Cloud Service (ExaCS)Oracle Database Cloud Service Enterprise Edition –Extreme Performance (DBCS EE-EP)3321.02.2019Oracle Database 18c/19c - New Features for DB Developers

PrerequisitesTable marked as MEMOPTIMIZE FORREADTable is heap-organizedQuery is in the format SELECT column list FROM table WHERE pk value Table has a primary keySTATISTICS LEVEL is not set to ALLPrimary key is not an identity columnNo GATHER PLAN STATISTICS hintTable is not compressedSQL trace is not enabledTable is not reference-partitionedQuery is not executed from PL/SQLTable has at least one segmentQuery is not executed from a Java storedprocedure with default database connectionTable loaded into the memoptimized poolusing dbms memoptimize.populate34Table fits completely in the memoptimized pool21.02.2019Query is executed via OCI (18c, fixed in 19c)Oracle Database 18c/19c - New Features for DB Developers

Core Messages3521.02.2019Oracle Database 18c/19c - New Features for DB Developers

Upgrade – The New Features Are Worth ItPolymorphic Table FunctionsPrivate Temporary TablesSchema Only AccountsQualified ExpressionsMemoptimized RowStore and more 3621.02.2019Oracle Database 18c/19c - New Features for DB Developers

Questions and Answers.Philipp SalvisbergSenior Principal ConsultantTel. 41 58 459 52 .02.2019Oracle Database 18c/19c - New Features for DB Developers

3 21.02.2019 Oracle Database 18c/19c - New Features for DB Developers PL/SQL – Qualified Expressions – Polymorphic Table Functions (PTF) – [NOT] PERSISTABLE – Optional using clause in PTF 19c) JSON – SQL expression returning JSON data – LOB results for json_value, json_query, json_table, json_object, json_array, json_arrayagg, item .

Related Documents:

The Oracle Database 19c enhanced Easy Connect Plus syntax is a superset of Easy Connect that supports more connection options. The syntax is available in Oracle Database drivers (JDBC, ODP.Net, cx_Oracle, node-oracledb etc) that use Oracle Client 19c and connect to Oracle Database 11.2 or later. The Oracle Database 19c Easy Connect Plus syntax is:

Oracle DB 18c -How to Downgrade a 18c Non CDB Database to Previous Release (Doc ID 2416661.1) Oracle 18c -How to Downgrade a Single Pluggable Oracle Database ( PDB ) from 18c

Patching, Backup, Security, Online Cloning, Online Relocation Software as Service Shared metadata, Data location transparency 31 12.1 12.2, 18c, 19c . Oracle Database 19c is now production and available on premises Oracle Database

Oracle Grid Infrastructure 19c installation files for Linux x86-64. This can be downloaded from Oracle site. Search the Internet for “oracle grid infrastructure 19c download”. At the time of this writing, its link is here. This tutorial was implemented using Oracle Grid Infrastructure 19c (version 19.3).

EXTERNAL Upgrading from Oracle 18c to Oracle 19c With activated Oracle TDE Document creation: 28.11.2019 Update: 04.03.2020 The purpose of this document is to provide a comprehensive overview and process flow of a successful

Oracle Database 19c includes new features and enhancements, as well as a long -term support commitment, that make it an attractive upgrade target for existing Oracle databases. Moving to Oracle Database 19c may be part of an effort that includes moving to newly purchased server hardware, migrating to different storage architectures, such as Oracle

Oracle continues to raise the bar with Oracle Database 19c with extensive support for consolidation. Designed for data center environments that are rapidly evolving and changing to keep up with the demands of the business, Oracle Database 19c allows businesses to adopt new technologies quickly while minimizing risk Real Application Testing

Music at Oxford’s annual celebration of carols in the beautiful surroundings of the Cathedral brings together a popular mix of festive cheer and seasonal nostalgia. The Cathedral Choir will sing a range of music centred on the Christmas message, under their new director Steven Grahl, with spirited readings and audience carols to share. Early booking is essential. Tickets from www .