Gear up for #AIOUG OTN Yathra’ 2016

Guys,

AIOUG is back again with OTN Yathra’ 2016. It is a series of technology evangelist events organized by All India Oracle Users Group in six cities touring across the length and breadth of the country. It was my extreme pleasure to be the part of it in 2015 and I’m pleased to announce that I’ll be speaking this year too. This year it starts on April 23rd and runs until May 01st, 2016. Check out the event schedule and objectives here http://www.otnyathra.com/

I will be speaking in Bangalore (Apr 24th), Hyderabad (Apr 26th), and Mumbai (Apr 30th). My session abstracts are as below –

1. Backup your databases to the cloud using Oracle Database Backup Service

Oracle Database Backup Service is a secure, scalable, reliable, and on-demand Oracle public cloud storage solution for storing Oracle Database backups. Businesses can access additional storage capacity in minutes with zero hardware investment. The Oracle Database Backup Service can be accessed from anywhere, at anytime, and from any Oracle database server connected to the Internet.

This session will touch upon several aspects of backup cloud service like subscription process, scalability, access, and security. The attendees will learn about the new backup-as-a-service offering that enables customers to store their backups securely in the Oracle cloud via a downloadable backup module that transparently handles the backup and restore operations.
2. Use Oracle BigData SQL to query for All Your Data

Oracle Big Data SQL provides unified query across Oracle database, Hadoop, and NoSQL datastores. It uses query franchising technique to maximize the performance and avoid the pitfalls of language-level federation using query franchising. Oracle BigData SQL uses Hadoop Smart Scan to quantify the performance and minimize data movement. This session will discuss the limitations of language level federation, capabilities of BigData SQL and the latest updates of a cutting-edge technology.

Please register for this event and confirm your availability in the event. I’ll see you there.

Regards

Advertisements

#DB12c feature – Secure External Procedures with DBMS_CREDENTIAL

Oracle Database 12c enables enhanced security for extproc by authenticating it against a user-supplied credential. This new feature allows the creation of a user credential and links it with a PL/SQL library object. Whenever an application calls an external procedure, the extproc process authenticates the connection before loading the shared library.

The DBMS_CREDENTIAL package is used to configure the credential. The CREATE LIBRARY statement has been enhanced for the credential specification.

A new environment variable, ENFORCE_CREDENTIAL, can be specified in extproc.ora to control the authentication by the extproc process. The default value of the parameter is FALSE. Another new environment variable, GLOBAL_EXTPROC_CREDENTIAL, serves as the default credential and is only used when the credential is not specified for a library. If ENFORCE_CREDENTIAL is FALSE and no credential has been defined in the PL/SQL library, there will be no user authentication; this means the extproc process will authenticate by using the privileges of the user running the Oracle server.

The following PL/SQL block creates a credential by using DBMS_CREDENTIAL.CREATE_CREDENTIAL. This credential is built using the ORADEV user:

BEGIN
DBMS_CREDENTIAL.CREATE_CREDENTIAL (
credential_name => 'devhost_auth',
user_name => 'oradev',
password => 'oradev')
END;
/

The library definition will include a new CREDENTIAL clause:

CREATE OR REPLACE LIBRARY myextlib
AS 'HelloWorld.so'
CREDENTIAL devhost_auth
/

When the extproc process reads the call specification and finds the shared library with a secured credential, it authenticates the library on behalf of the credential and then loads it.

 

Note – this post is an excerpt from the book “Advanced Oracle PL/SQL Developer’s Guide – Second Edition

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

Discovering the Author within me: My upcoming book

Happily I would announce the listing of my upcoming book at PacktPub publishers website. The book is expected to be released by May, 2012.

The book is for the Oracle Associate professionals who are aspiring for Professional level exam (1Z0-146 certification).

“The Oracle Advanced PL/SQL Developer Professional Guide helps you master advanced PL/SQL concepts. Besides the clear and precise explanation on advanced topics, it also contains example code and demonstrations, which gives a sense of application and usage to readers. The book gives a deep insight that will help transform readers from mid-level programmers to professional database developers. It aims to cover the advanced features of PL/SQL for designing and optimizing PL/SQL code.”–From PacktPub webpage

http://www.packtpub.com/oracle-advanced-pl-sql-developer-professional-guide/book

Wait until May but surely book your copies in advance.

Thanks to Oracle and Luv u All,

Saurabh

AIOUG Sangam 11

 Hi Guyzzz

Just back from AIOUG Sangam11. Wonderful effort, event, sessions and most important grrat stage to share with some great names. Arup Nanda, Murali Vallath, Kuassi Mensah and many more. Kudos to the arrangement and and event management too.

The Day 1 (9th Dec) started off with registration. Was glad to see our bags and great material. Saw Murali at the doorstep and the day started off.

Murali addressed the session and thanked the event management team and introduced Arup to kick off the session. Arup Nanda ‘The LONGTIME DBA’ was the Show Stopper of the whole event. And he should be !!

Rich Niemiec started off with a session on Rolta. He showed us some pics of Oracle Corp…was amazed to be the part of it.

 

 

 

 

 

 

Arup started off with a session on Profiler in PL/SQL. Great sessions, great views and great information. Kuassi Mensah delivered a nice session on Oracle Net security. Murali too gave a presentation on RAC subject, but couldn’t catch up match as I have no background. Hemant came up with the day to day challenges of a DBA, their role and expectations. Fabulous !!

Day 2 (10th Dec) again saw a long session from Arup until Lunch. Superb stamina and grasp !! Got to know some new concepts on Cache fusion, DBA best practices, and introduced the logic behind Exdata. I could see the roadmap of DBA to DMA (Database Machine Administrator). Again a good one!!

Meanwhile I got introduced to Arup Nanda with a handshake and told him about myself. Told him about my online works and he asked me to get in touch for SELECT journal too. Nice chat time !!

Later I attended session by Hemant on Partitioning. Though it was an elementary one but enough to fill up 1 hour session. The next session by Prabhakar Gongloor was another masterpiece where he showed some screendumps of Oracle 12C. I believe we would be only of those few ones who could see it within 2 months of the version release. I could see him hiding something by saying its ‘Special Technique’ through which EM can connect to DB even if it is down (as far as I could understand).

As I referred one of my colleague for the seminar, I received a coupon worth INR500, redeemable on the same day against the books at the counter. I got one copy of Oracle XML DB.

The conference ended with photo sessions. I too got few with Arup, Bryn, Rich and Mensah. Soon I would publish on my blog. Received a Tshirt from OTN too.

Next year, it would be in August, 2012…I will be happy to volunteer the event and would surely like to see myself at the stage right there 🙂

Once again, Thanks Oracle, Thanks AIOUG !!

By for now

Saurabh