Oracle 12c Technical Hands-On Workshop

I have been running many tech events and briefings on Oracle 12c for India Partners. This time around, I thought of posting my latest event reviews.

This week, I wrapped up the Oracle 12c Technical Hands-On workshop in Oracle facility, Gurgaon. It was a 2-day event from Oracle Database Product Management. The focus of the workshop was primarily on Oracle 12c Multitenant architecture along with the hands-on labs using a virtualbox image. The audience was comprised of the representatives from the key Oracle partners in the NCR area. I appreciate their interest and in spite of odds like the last night rain and massive traffic, they showed up in time. I was the lead instructor for this event along with my senior colleague Mr. Rick Pandya. Thanks to Rick who has flown from Chicago, to be with me for several partner events in India.

Here was the agenda of the workshop –

  • Introduction to Oracle 12c Multitenant Architecture
  • Administration and Management of Multitenant Databases
  • Cloning, Consolidation, Relocating, Backup/Recovery, Security
  • Migrating to Multitenant Architecture
  • Upgrading to 12c CDB using DBUA
  • Performance Monitoring and Resource Management
  • Heat Maps and ILM, Temporal Validity, In-Database Row archiving

For all reasons, Multitenant was the focus and area of interest. Certain excerpts like share-able components of a Container Database, PDB provisioning, remote cloning, spfile, control file and common users were the area of exploration. Some of the folks were good to find that – in a pluggable container, the instance name (from V$INSTANCE) and db name (from V$DATABASE) appears to be the container name because of the obvious reasons.

The real challenge was the hands-on part of the workshop where the participants were required to carry the high-end configuration laptops, but only few of them could manage it. An 8G laptop with 100G of space was expected to run the exercises on database upgrade, backup and migration. We gave a shot by trimming the vbox image to run on 4G lappe. It did worked but host OS performance was in a toss.

My next stop is in Chennai where I’ll be driving this event for specific Partners. See you all 🙂

Saurabh K. Gupta

Advertisements

Oracle Database 12c PRAGMA UDF and WITH clause enhancements

Here are two interesting enhancements in Oracle database 12c PL/SQL.

PL/SQL subprogram defined using WITH clause of a subquery – Oracle database 12c allows PL/SQL declaration section in the WITH clause. One can define PL/SQL function or procedure into a WITH clause. Functions declared in the PL/SQL declaration section can be invoked instantly in the SELECT statement while the procedures can be invoked from the functions used in the declaration section.

PL/SQL functions defined using PRAGMA UDF – SQL and PL/SQL have different memory representations of values. Therefore, interconversion is involved while “switching” from one engine to other and vice versa. It allows you to define the PL/SQL subprogram outside the SQL statement but matching the performance of an inlined PL/SQL program.

Let us do a small test to see the performance gains-

1. Created a test table T

CREATE TABLE t
(
PK integer not null,
n1 integer not null,
n2 integer not null,
n3 integer not null,
constraint t_PK primary key(PK)
)
/

2. Inserting some random data in the table T using DBMS_RANDOM

DECLARE
commit_count constant pls_integer := 100000;
nof_Rows constant pls_integer := 20*commit_count;
Zero constant integer not null := 0;
THS constant integer not null := 1000;
MIL constant integer not null := THS*THS;
BIL constant integer not null := MIL*THS;
TIL constant integer not null := BIL*THS;

M1 constant integer not null := 2*THS;
M2 constant integer not null := 2*BIL;
Hi constant integer not null := 2*TIL;
BEGIN
DBMS_Random.Seed(To_Char(Sysdate, 'MM-DD-YYYY HH24:MI:SS'));
for j in 1..Nof_Rows loop
declare
n1 integer not null := DBMS_Random.Value(Zero, M1);
n2 integer not null := DBMS_Random.Value(M1, M2);
n3 integer not null := DBMS_Random.Value(M2, Hi);
begin
insert into t(PK, n1, n2, n3) values(j, n1, n2, n3);
end;
if Mod(j, commit_count) = 0 then
commit;
end if;
end loop;
commit;
END;
/

3. Table has undergone good number of transactions; so lets gather the table stats

begin
DBMS_Stats.Gather_Table_Stats('SCOTT', 'T');
end;
/

4. Here is the objective. Let us create the PL/SQL function to display an integer as a multiple of appropriate unit of “Thousand”, “Million”,”Billion” or “Trillion”. We shall do this activity in different fashions to compare the performance. We shall record the timing for each case.

a) Using a conventional pre 12c standalone function to set the BASELINE

CREATE OR REPLACE FUNCTION F_ShowVal_pre12c(n IN integer) return varchar2 is
THS constant integer not null := 1000;
MIL constant integer not null := THS*THS;
BIL constant integer not null := MIL*THS;
TIL constant integer not null := BIL*THS;
BEGIN
RETURN
CASE
WHEN n <= THS-1 then To_Char(n, '999999')||' units'
WHEN n/THS <= THS-1 then To_Char(n/THS, '999999')||' Thousand'
WHEN n/MIL <= THS-1 then To_Char(n/MIL, '999999')||' Million'
WHEN n/BIL <= THS-1 then To_Char(n/BIL, '999999')||' Billion'
ELSE To_Char(n/TIL, '999999')||' Trillion'
END;
END F_ShowVal_pre12c;
/

SET TIMING ON
SELECT F_ShowVal_pre12c(n1) n1, F_ShowVal_pre12c(n2) n2, F_ShowVal_pre12c(n3) n3 FROM t
/

b) Using Pure SQL – Without using a function or 12c enhancement

SET TIMING ON
SELECT PK,
case
when n1 <= 999 then To_Char(n1, '999999')||' units'
when n1/1000 <= 999 then To_Char(n1/1000, '999999')||' Thousand'
when n1/1000000 <= 999 then To_Char(n1/1000000, '999999')||' Million'
when n1/1000000000 <= 999 then To_Char(n1/1000000000, '999999')||' Billion'
Else To_Char(n1/1000000000000, '999999')||' Trillion'
end,
case
when n2 <= 999 then To_Char(n2, '999999')||' units'
when n2/1000 <= 999 then To_Char(n2/1000, '999999')||' Thousand'
when n2/1000000 <=999 then To_Char(n2/1000000, '999999')||' Million'
when n2/1000000000 <=999 then To_Char(n2/1000000000, '999999')||' Billion'
Else To_Char(n2/1000000000000, '999999')||' Trillion'
end,
case
when n3 <= 999 then To_Char(n3, '999999')||' units'
when n3/1000 <= 999 then To_Char(n3/1000, '999999')||' Thousand'
when n3/1000000 <= 999 then To_Char(n3/1000000, '999999')||' Million'
when n3/1000000000 <= 999 then To_Char(n3/1000000000, '999999')||' Billion'
Else To_Char(n3/1000000000000, '999999')||' Trillion'
end
FROM t
/

c) Declaring the PL/SQL function in the subquery’s WITH clause

SET TIMING ON
WITH
function ShowVal(n IN integer) return varchar2 is
THS constant integer not null := 1000;
MIL constant integer not null := THS*THS;
BIL constant integer not null := MIL*THS;
TIL constant integer not null := BIL*THS;
BEGIN
return
case
when n <= THS-1 then To_Char(n, '999999')||' units'
when n/THS <= THS-1 then To_Char(n/THS, '999999')||' Thousand'
when n/MIL <= THS-1 then To_Char(n/MIL, '999999')||' Million'
when n/BIL <= THS-1 then To_Char(n/BIL, '999999')||' Billion'
Else To_Char(n/TIL, '999999')||' Trillion'
end;
end ShowVal;

SELECT showVal(n1) n1, showVal(n2) n2, showVal(n3) n3
FROM t
/

d) Declaring the PL/SQL function using PRAGMA UDF

CREATE OR REPLACE FUNCTION F_ShowVal(n IN integer) return varchar2 is
PRAGMA UDF;
THS constant integer not null := 1000;
MIL constant integer not null := THS*THS;
BIL constant integer not null := MIL*THS;
TIL constant integer not null := BIL*THS;
BEGIN
RETURN
CASE
WHEN n <= THS-1 then To_Char(n, '999999')||' units'
WHEN n/THS <= THS-1 then To_Char(n/THS, '999999')||' Thousand'
WHEN n/MIL <= THS-1 then To_Char(n/MIL, '999999')||' Million'
WHEN n/BIL <= THS-1 then To_Char(n/BIL, '999999')||' Billion'
ELSE To_Char(n/TIL, '999999')||' Trillion'
END;
END F_ShowVal;
/

SET TIMING ON
SELECT F_ShowVal(n1) n1, F_ShowVal(n2) n2, F_ShowVal(n3) n3
FROM t
/

Recorded the timings from the above cases (a), (b), (c) and (d) in the below matrix. Here is the performance comparison from the above scenarios –

plsql