Tuesday, December 31, 2019

SANGAM19 : Asia's Largest Oracle User Group Conference

SANGAM19 : Asia's Largest Oracle User Group Conference


Sangam, India's the largest independent Oracle user's group conference organized by All India Oracle Users Group (AIOUG). Every year Sangam is hosting 100+ in-depth technical sessions on new cutting-edge Oracle Technologies. SANGAM is a prestigious conference that provides in-depth Technical Session, Live Demos, Hands-on Labs, Technology Booths, Expert Talks, Fire Chats with Technology Gurus and many more.

In Sangam, Technology Experts and Gurus are sharing their knowledge and experience in person with participants. Over 1000+ participation every year which includes Database admin, Developers, Architects, Solution Designers, Technology Directors, Business Analysts, Delivery Heads, etc.

In Sangam, I had taken session on Cloud-Native Architecture (Design for automation) in which, i had discussed on unique capabilities of cloud and application/infra design on cloud environment. Also to adopt unique capabilities of the cloud one must be aware of microservices, containers, Automate the build, testing, and deployment of the packages, automatically scaling-up and down, distributed processing etc. Designing your application and system in such a way, that can run on any platform.

Cloud-Native Architecture (Design for automation)
https://sangam19.sched.com/event/W1Pa/cloud-native-architecture-design-for-automation

Also I was part of “Expert Panel” in Fire side Chat on Performance Tuning at SANGAM19 Conference where I shared stage and put my view with Tirthankar Lahiri (Senior Vice President, DB In-Memory), Prabhaker Gongloor (Senior Director of Product Management), Karan Dodwal (OCM).
Fireside Chat Oracle Performance Tuning Experts
https://sangam19.sched.com/event/WaWH/fireside-chat-oracle-performance-tuning-experts

I' m honored to receive ”Outstanding Volunteer”  award during Sangam19 from Jenny Tsai . Thank you Sai Janakiram Penumuru for recognizing and appreciate my work towards All India Oracle Users Group (AIOUG) community. I used to speak on various Database Features, Performance Tuning  and Cloud Technologies in AIOUG Pune, Mumbai and Gujarat chapters and I am glad to be a part of volunteering team for AIOUG.





#sangam19 @aioug







Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert

Thursday, December 12, 2019

Performance with CLOB vs VARCHAR2

Performance with CLOB vs VARCHAR2

Oracle has introduce extended data type from 12c onwards, which means one can have more text data in varchar2 and raw data type.

Before 12c varchar2 and raw data type size,
VARCHAR2 : 4000 bytes
NVARCHAR2 : 4000 bytes
RAW : 2000 bytes

12c onwards varchar2 and raw data type size,,
VARCHAR2 : 32767 bytes
NVARCHAR2 : 32767 bytes
RAW : 32767 bytes

By default extended data type is not enable. This can be enable by setting initialize parameter MAX_STRING_SIZE using below command,

ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED;

Above command should require database should be in upgrade mode and require restart after setting this parameter.

Check out detailed steps to enable extended data type here,
http://tanwanichandan.blogspot.com/2018/09/extended-data-type-in-oracle-12c.html

Now, let's compare the performance of CLOB and VARCHAR2
Here I have tested performance of Insert, Update and Select statements as below,

Performance of Insert Statements. CLOB vs VARCHAR2

CLOB Insert Trace




VARCHAR2 Insert Trace



Performance of Update Statements. CLOB vs VARCHAR2

CLOB Update Trace




VARCHAR2 Update Trace




Performance of Select Statements. CLOB vs VARCHAR2

CLOB Select Trace






VARCHAR2 Select Trace





Summary 


Operations
VARCHAR2(32767) (Trace Details)
CLOB (Trace Details)
Remarks
Insert
10  recursive calls
1170  db block gets
31  consistent gets
0  physical reads
436540  redo size
12  recursive calls
1254  db block gets
47  consistent gets
0  physical reads
552836  redo size
VARCHAR2 performance seems faster as compared with CLOB during Insert.
Update
CPU Cost 9%
5  recursive calls
2464  db block gets
259  consistent gets
0  physical reads
888472  redo size
CPU Cost 16%
6  recursive calls
2800  db block gets
352  consistent gets
0  physical reads
1176784  redo size
VARCHAR2

Less CPU,
Less db block gets,
Less consistent gets,
Less redo size
Select
CPU Cost 9%
7  recursive calls
0  db block gets
129  consistent gets
0  physical reads
0  redo size
18522  bytes sent via SQL*Net to client
1350  bytes received via SQL*Net from client
68  SQL*Net roundtrips to/from client
CPU Cost 16%
8  recursive calls
0  db block gets
2263  consistent gets
0  physical reads
0  redo size
751491  bytes sent via SQL*Net to client
296624  bytes received via SQL*Net from client
2002  SQL*Net roundtrips to/from client
VARCHAR2

Less CPU,
Less consistent gets,
Less Recursive Calls,
Less memory,
Less Roundtrips to get data.






Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert