Oracle OpenWorld 2017 Delhi – My Session

I will be presenting at the Oracle OpenWorld 2017, Delhi scheduled for 9th and 10th May 2017. My session detail :

Session ID: CON1092
Session Title: High-Performance Database in Oracle Bare Metal Cloud Services

Hope to see you all at the OOW.

Effective SQL: 61 specific ways of writing effective SQL

Few months back, I completed a technical review of an upcoming book titled “Effective SQL: 61 specific ways of writing effective SQL”. This book focuses on widely used Relational Databases and, no doubt, Oracle is one of these. My review was specifically on Oracle SQL. Got to know from the publisher that this book is scheduled to be out in the market by Friday, 23rd December 2016. The book can be ordered from the following link:

effective-sql-61-specific-ways-to-write-better-sql-9780134578897

 effectivesql

Oracle Open World 2013 : An Enriching Experience….

First of all, let me apologies for a long delay in posting a Blog. This was due to some critical escalations and the work pressure. Work comes first and therefore, it was very critical for me to finish off all the pending in-hand customer tasks.

This year, I had an opportunity to attend Oracle Open World 2013. Being my first visit for this Event in US, I was all excited as I was sure that this will be an opportunity to meet some of the great experts from different parts of the world and also to meet some old friends. The trip met all my expectations. During the event, while I attended many of the technical events (I will talk about these later), I met some of my old friends, like K. Gopalakrishnan (co-author of Oracle Wait Interface).

On the technical front, I attended many sessions and some of these were on Optimizer and Oracle 12c. However, the sessions that kept me glued towards the technical content and speaker presenting style was on “Top 12 Features of Oracle Database 12c” by Thomas Kyte and “Oracle Optimizer Bootcamp” presented jointly by Maria Colgan (Oracle Optimizer Group) and Jonathan Lewis. A session by Connor Mcdonald on “Optimizer Statistics : A Fresh Approach” was also well presented and the attendees enjoyed the session a lot.

While I do not want to write everything about the sessions, the take away that I want the readers to know are :

1. Multitenant Feature of Oracle 12c – This is one of the best feature that is introduced and will facilitate many of the organizations in database consolidation.
2. Adaptive Optimization – This is to ensure that a right (and optimal) execution plan is chosen (or switched) at run time based on the number of rows fetched.
3. Identity Datatype – Oracle Sequences can be attached to this data type to generate repetitive numbers.

All these years, during my presentations, I have discussed on using a right tool for optimization. Developers still use “explain plan for” or “autotrace” to check for the execution plan of their queries. I have demonstrated some of the issues around “explain plan” and this was revisited by Maria Colgan in “Oracle Optimizer Bootcamp”. Every session you attend, you are bound to learn something new and surely enough, I learned something interesting in this session. I am sure not many of my friends and readers know about this and therefore, I will include this in my Sangam 2013 presentation with a thanks to Maria.

I will be presenting a session during this year annual User Group Event “Sangam 2013”. The topic of my presentation is “Real Life Optimization Scenarios and Solutions”. Looking forward for this great event.

Exadata v/s Solid State Disk ! Performance & Scalability Comparison.

During one of my Exadata Presentation, one of the attendees asked about the Performance & Scalability Difference between EXADATA and SSD. He mentioned that the Performance POC for one of their critical application on SSD and Exadata gave similar results. Before replying to his query, I asked him “What was the Average CPU Utilization during both the POC’s ?”. While the answer to my question was interesting, thought of writing a short blog on this debate and at the end will be the response by the attendee to my question.

Lot has been written on the comparison between the two. For example, visit asktom.oracle.com and Kevin Closson’s blog. I will just write on my reply with a brief explanation.

EXADATA is one of the biggest breakthrough in achieving fast computing and therefore, clearly overpowers other technologies around it. While other technologies might give you a similar performance, but investing only for performance is not the only Organization Goal. The primary reason, which I feel, Exadata scores over others is the Scalability Factor. With that said, while Exadata gives Extreme Performance, it improves the Scalabiity as well. SSD and any other technologies are Hardware components, whereas, Exadata is a combination of Hardware and Intelligent Software and this combination makes it Faster and Scalable.

In an Oracle Database or an Application deployed on top of Oracle, Latches are considered to be a Performance and Scalability Inhibitor. Every Block access (into the cache or from the cache), requires Latch and if these latch gets are in access, these excessive visits to the cache will burn the CPU power thus impacting the performance and scalability. Exadata has changed this approach, by introducing a faster hardware that scans the required blocks and an Intelligent Software on top of it, to filter only the required column data directly to the Private Memory Area (PGA) of the User Process. This Intelligence bypasses the buffer cache thus avoiding a costlier step of Latching. This has a huge impact on the CPU Cycles and therefore, if the Database Server has enough CPU Power available, this additional CPU Power can be translated to Scalability.

Usually, in an Oracle Database on a traditional (non-exadata) storage, the blocks are read from the Disk to the Buffer Cache and then the required column data is filtered and fetched to the Private Memory of the User Process. Reading a Block from Disk to Cache, filtering the required data from Cache involves CPU. All of these consume CPU Cycles on the Database Server. As mentioned, in an Exadata Storage, the combination of Hardware and Software works together to eliminate the costlier from the Database Server. From this explanation, it is quite evident that Exadata is not only improves performance, but also provides scalability. All of this is achieved without making any changes in the application. However, further optimization in an Exadata is also possible.

Now to the reply on my Question “What was the Average CPU Utilization during both the POC’s ?”. The reply was that through out the POC, the CPU Utilization on an Exadata Machine was always far below than the POC on SSD. Solid State Disk’s would provided faster access to the blocks that are read from the disk, but will not eliminate the costlier steps of latching the blocks from the Database Server. Therefore, savings in CPU Cycles is not expected. Infact, it may worsen the performance.

In one of the paragraph above, I mentioned about the steps as a part of row fetching that an Oracle Database, on an non-exadata storage, performs on behalf of a User. This is, a block is read from Disk to the Cache and then the required columns and row data is fetched to the Private Memory area, which is to the PGA, of the User Process. This contradicts to the steps that I mentioned in one of my previous blog on Consistent Gets. This blog rectifies the error in my assumption on the steps performed and mentioned in the said previous blog.

Exadata v/s Traditional Storage

My previous blog was on Exadata and I demonstrated the use of Oracle API’s to simulate Exadata Performance on a Traditional Storage. I am now all set to witness the actual performance benefits that Exadata Storage can provide, when compared to other storages. Currently, I am engaged for the same customer where I presented a Simulation that looked encouraging and this time, I am involved in a Benchmarking of an Entire Application Performance on the Exadata Box. The Application is a mix of OLTP and Batch processes and the results are awaited in next few days time. Would share some of the interesting facts soon.

Oracle Enterprise Manager Grid Control ! A must read Book…

My Oracle Colleague, Porus has written a book “Oracle Enterprise Manager Grid Control: Advanced OEM Techniques for the Real World” which has recently been published:

http://www.rampant-books.com/book_1001_advanced_techniques_oem_grid_control.htm

The Book Synopsis is as follows:

Oracle’s Enterprise Manager Grid Control is recognized as the IT Industry’s leading Oracle database administration and management tool. It is unrivalled in its ability to monitor, manage, maintain and report on entire enterprise grids that comprise hundreds (if not thousands) of Oracle databases and servers following an approach that is consistent and repeatable.

However, Enterprise Manager Grid Control may seem daunting even to the most advanced Oracle Administrator. The problem is you know about the power of Enterprise Manager but how do you unleash that power amongst what initially appears to be a maze of GUI-based screens that feature a myriad of links to reports and management tasks that in turn lead you to even more reports and management tasks?

This book shows you how to unleash that power.

Based on the Author’s considerable and practical Oracle database and Enterprise Manager Grid Control experience you will learn through illustrated examples how to create and schedule RMAN backups, generate Data Guard Standbys, clone databases and Oracle Homes and patch databases across hundreds and thousands of databases. You will learn how you can unlock the power of the Enterprise Manager Grid Control Packs, PlugIns and Connectors to simplify your database administration across your company’s database network, as also the management and monitoring of important Service Level Agreements (SLAs), and the nuances of all important real-time change control using Enterprise Manager.

There are other books on the market that describe how to install and configure Enterprise Manager but until now they haven’t explained using a simple and illustrated approach how to get the most out of your Enterprise Manager. This book does just that.

This book covers everything on Enterprise Manager Grid Control 11g. I am sure that this book should help you in managing and maintaining a large Enterprise site using Grid Control.

Cache Buffers Chains Latch Contention…..

Few days back, I got a call from one of my customer about a severe latch contention issue that had been forcing them to restart a database. This phenomenon had been occurring for last past 1 month and for every occurrence the database had to be restarted. This being a critical 24×7 system, for obvious reason, restart was not a solution and when the inhouse dba team gave up on this, I was called upon to investigate the issue.

The database version is 9205 and seeing the statspack during the bad times, it was evident that the contention was on Cache Buffers Chains Latch. In most of the cases, CBC Latch contention come due to inefficient application queries with these consuming huge logical reads per execution. In this case too, as evident from the Statspack, these huge logical reads queries were the primary reason. There were many queries with Millions of Logical Reads and optimizing these would surely give relief as the amount of latching while acquiring block in the cache will come down. While some of the information was available in the Statspack report, I queried x$bh view to check for the block distribution across each CBC Child Latch. For simplicity of the readers, X$BH is an Oracle Internal table that maintains the history of the buffers in the Cache alongwith the hash chains these are allocated to. Usually, the distribution of the blocks or buffers in the cache should be approximately uniform across each hash chain. The Query executed to get the distribution alongwith the output is as under :

This Output is when the system was running perfectly fine without any latch contention.

select * from (
select hladdr, count(*) from x$bh group by hladdr order by 2 desc)
where rownum<=20;

HLADDR             COUNT(*)
---------------- ----------
07000003E2771BA8        197
07000003E661DE70        161
07000003E6305250        157
07000003E661CE90        157
07000003E3F2B0E8        154
07000003E199FA00        153
07000003E4E45FF0        153
07000003E6607150        153
07000003E76A42D0        152
07000003E19A19C0        151
07000003E3DB9F68        151
07000003E5A0F920        151
07000003E78C9D90        151
07000003E76A2310        151
07000003E64E45F0        151
07000003E1A07CE0        150
07000003E4CD7E10        150
07000003E4E45010        150
07000003E4DBA190        150
07000003E3F30048        150

20 rows selected.

As seen from the Good Time output, the buffers protected by each hash chains are nearly uniform. Fortunately, when I was monitoring the system for 2-3 days, I could capture these buffer distribution across each hash chains during Latch Contention and the figures for the top most hash chain looked alarming. The Output captured during the issue is as under :

HLADDR             COUNT(*)
---------------- ----------
07000002570F7300       8734
070000025A786078        364
070000025A5020D8        167
0700000262E9A9E0        167
07000002562DD4D0        164
07000002583D9C00        164
07000002563162C0        161
07000002563BE270        161
07000002571D9AF0        161
0700000262E9C420        161
0700000256341080        161
07000002572C8650        160
07000002583D74A0        160
070000025A838098        160
0700000259557A20        160
07000002562DA010        159
07000002571E2B50        159
07000002582778A0        159
070000025955D600        154
07000002584C1140        151

20 rows selected.

This clearly indicates Hot Block Contention, which means, a block that several processes are trying to access simultaneously. A Long Hash Chain means that a query or set of queries are creating a consistent read image of a particular block and since, a block, even if it has multiple images, will be hashed to a same child latch, a hash chain can grow longer causing latch contention. Next step was to Capture Data relevant to the current issue and therefore the data collected for further investigation was :

  1. Top Blocks / Objects with high Touch Count
  2. Top Running Queries with high Buffer Gets

The First step was to identify the block, and the object it belongs too, with highest Touch Count. This counter is maintained in TCH column of X$BH. The Query used to get this detail is as under :

select a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name
from   	x$bh a, 
	dba_objects b
where  	(a.obj = b.object_id  or  a.obj = b.data_object_id)
and 	hladdr='07000002570F7300' 
order by 4;

From the output (not mentioned in this blog), I could see two objects S_BRANCH and BA_TMP_HOFF as the top most object. While BA_TMP_HOFF (normal heap table) was contributing to the latch contention with highest touch count, the sessions were waiting on S_BRANCH table due to high concurrency on this table.

The second step was to capture the top running queries with high buffer gets per execution and some of these are mentioned below (I have mentioned only few of them and have cut short the text) :

HASH_VALUE SQL_TEXT                                           EXECUTIONS GETS_PER_EXEC
---------- -------------------------------------------------- ---------- -------------
 603848796 SELECT    FN_NEWBRANCH(A.BRANCH) BRANCH,    A.PROD          1       5294194
           UCT_CODE PRODUCT,    B.PRODUCT_DESCRIPTION PROD_DE
           SCRIPTION,    A.CONTRACT_REF_NO CONTRACT_REF_NO,
             A.USER_REF_NO USER_REF_NO,    C.COLLECTION_REF,
              C.BILL_CCY BILL_CCY,    C.BILL_AMT BILL_AMOUNT,
 
 784509366 SELECT    FN_NEWBRANCH(C.BRANCH) BRANCH,    C.PROD          8       2306090
           UCT_CODE PROD_CODE,    C.CONTRACT_REF_NO,    C.USE
           R_REF_NO,    B.PARTY_ID DRAWER_ID,       B.PARTY_N
           AME DRAWER,    B.PARTY_REFNO DRAWER_REF,    A.PART
           Y_ID DRAWEE_ID,    A.PARTY_NAME DRAWEE,    A.PARTY
 
 322980110 SELECT    FN_NEWBRANCH(CSTBS_CONTRACT.BRANCH),              3        811376
           CSTBS_CONTRACT.PRODUCT_CODE,    CSTMS_PRODUCT.PROD
           UCT_DESCRIPTION,    BCTB_CONTRACT_MASTER.CUSTOMER_
           ID,    STTMS_CUSTOMER.CUSTOMER_NAME1,    CSTBS_CON
           TRACT.CONTRACT_REF_NO,    CSTBS_CONTRACT.USER_REF_

POST-INVESTIGATION DISCUSSION WITH THE APPLICATION OWNER

Further, once the Contending Object (S_BRANCH) and Contributor (BA_TMP_HOFF) was found, during the discussion with the Owner of this Production System it was revealed that the load (number of records processed) by a batch processes that is run in 10 parallel streams has gone up by 3 times and all these batch processes heavily access (select and update) BA_TMP_HOFF table.

During the latch contention, almost 95% of the sessions waiting on CBC Latches query S_BRANCH. The application owner further mentioned that, while this table is static, it is heavily queried as it is a BRANCH Master table and is joined to several other tables to get the Name of the Branch. This input was interesting and is a heart of the this blog. Interestingly, the queries captured for the waiting sessions during the latch free waits were of exactly same type i.e. get the branch name from S_BRANCH and were doing 4 Logical I/O’s per executions using a Table Scan via Primary Key Index. Since these were doing only 4 logical I/O’s per execution, these queries were not on top of the list capture above. The top queries captured were based on the Gets Per Executions.

ANALYSIS AND RESOLUTION

It was time implement some of the changes that would give the customer relief from the ongoing issues. WHile optimization of the Batch Processes involving BA_TMP_HOFF table would have required Understanding the Business Logic, Making Changes, Testing and Implementing and would have taken longer, the emphasis was more to implement a change that does not require extensive testing and therefore may take less time. Therefore, the change that would have given some relief was optimizing Queries on S_BRANCH table. The Concern raised by the Application Owner here was “Will Optimization of a Query with 4 Logical I/O’s per Execution give relief from Latch Contention ?”

To resolve the Latch Contention, my focus was not on reducing the I/O’s of the Queries on S_BRANCH from 4 to lesser but was to reduce the concurrency on this object by way of optimization. If you see the top queries captured above (step 2 of the data collected during the latch contention), these queries call a Function FN_NEWBRANCH. The input to this function is Branch Code from child tables and the output is the branch name fetched from S_BRANCH. A Function, and the queries written in the Function, called from the SELECT Statement is executed once for each row fetched by the main body of the query. This means, if the SELECT Query fetches 1000 rows, then the function called in the SELECT Statement, as well as, the queries written in the function will be executed 1000 times. We all know that Latch Contention is a Concurrency Issue and reducing Concurrency by way of Optimization, results in Scalability of the Application. Therefore, my Goal here was to reduce the concurrency on S_BRANCH table and this was done by way of a Simple Change in the query. Since the change was very simple change, it did not require any Application Testing and was therefore immediately Implemented. Since then, it has been almost 20 days, the performance of the production system is stable with No Latch Contention.

OPTIMIZATION

Let us walk through an example that simulates the production concurrency issue. In this test case, I will create two tables EMP and DEPT. DEPT is a Parent table and EMP is a Child Table that references DEPTNO of DEPT. Further, we will create 2 Functions, both doing the same task, to Get the Department Name for a Deptno and these will be called from a SELECT Statement. Since Deptno is a primary key, a query against DEPT to get DNAME will be of 4 Consistent reads. The text of both the Function is same except the Hint in the Queries to easily identify and differentiate from v$sqlarea.

drop table emp;

drop table dept;

exec dbms_random.seed(0);

create table dept as
select 	rownum deptno, dbms_random.string('A',20) dname
from	all_objects
where 	rownum<=100;

alter table dept add constraint pk_dept primary key(deptno);

create table emp as
select	rownum empno, dbms_random.string('A',30) ename,
	round(dbms_random.value(1,100),0) deptno,
	round(dbms_random.value(1000,10000),0) sal
from	all_objects
where 	rownum<=10000;

alter table emp add constraint pk_emp primary key(empno);

alter table emp add constraint deptno_fk foreign key(deptno) references dept;

exec dbms_stats.gather_table_stats(user,'EMP');

exec dbms_stats.gather_table_stats(user,'DEPT');
create or replace function get_dname(i_deptno in number)
return varchar2 as
i_name varchar2(20);
begin
select /*+ ORIGINAL */ dname into i_name from dept where deptno=i_deptno;
return i_name;
end;
/

create or replace function get_dname_o(i_deptno in number)
return varchar2 as
i_name varchar2(20);
begin
select /*+ OPTIMIZED */ dname into i_name from dept where deptno=i_deptno;
return i_name;
end;
/

set autot trace
select get_dname(deptno) deptno,
        ename, sal
from       emp
where deptno in (10,20);

200 rows selected.


Statistics
----------------------------------------------------------
        505  consistent gets <--- I/O's of the Main Body
	...
	...
        200  rows processed <-- Rows Processed 

Optimized Code (calling of Function using Scalar Subquery)

select (select get_dname_o(deptno) from dual) deptno,
	ename, sal
from	emp
where 	deptno in (10,20);

200 rows selected.

Statistics
----------------------------------------------------------
         21  recursive calls
          0  db block gets
        108  consistent gets <-- I/O's of the Main Body
        ...
        ...
        200  rows processed <-- Rows Processed (same as Original)

column sql_text for a50 wrap
select sql_id, sql_text, buffer_gets, executions, version_count
from v$sqlarea where sql_text like 'select get_dname(dept%';

SQL_ID        SQL_TEXT                                           BUFFER_GETS EXECUTIONS VERSION_COUNT
------------- -------------------------------------------------- ----------- ---------- -------------
90wzvddty11u6 select get_dname(deptno) deptno,  ename, sal from          505          1             1
              emp where deptno in (10,20)


select sql_id, sql_text, buffer_gets, executions, version_count
from v$sqlarea where sql_text like 'SELECT /*+ ORIGINAL%';

SQL_ID        SQL_TEXT                                           BUFFER_GETS EXECUTIONS VERSION_COUNT
------------- -------------------------------------------------- ----------- ---------- -------------
dpbdkb6u525g3 SELECT /*+ ORIGINAL */ DNAME FROM DEPT WHERE DEPTN         400        200             1
              O=:B1


select sql_id, sql_text, buffer_gets, executions, version_count
from v$sqlarea where sql_text like 'select (select get_dname%';

SQL_ID        SQL_TEXT                                           BUFFER_GETS EXECUTIONS VERSION_COUNT
------------- -------------------------------------------------- ----------- ---------- -------------
gufauq4hb8tgq select (select get_dname_o(deptno) from dual) dept         108          1             1
              no,         ename, sal from    emp where deptno in
               (10,20)


select sql_id, sql_text, buffer_gets, executions, version_count
from v$sqlarea where sql_text like 'SELECT /*+ OPTIMIZE%';

SQL_ID        SQL_TEXT                                           BUFFER_GETS EXECUTIONS VERSION_COUNT
------------- -------------------------------------------------- ----------- ---------- -------------
14h9s8atq8u26 SELECT /*+ OPTIMIZED */ DNAME FROM DEPT WHERE DEPT           4          2             1
              NO=:B1

The modification in this case was calling a Function using a SCALAR Subquery using Dual. This change brought down the number of executions of the recursive query “SELECT DNAME FROM DEPT WHERE DEPTNO=:B1” from 200 to 2. This is for each deptno. The Query was executed for Deptno in (10,20) and therefore, the function was called only twice and not for each row fetched by the main body of the query. The Original Implementation called the function 200 times as the main body of the query fetched 200 rows. As for the Logical I/O’s, with both the Implementation The I/O for the recursive query on DEPT was 2 per Execution, but the benefits were on Number of Executions with each of these Implementation. The I/O’s of the Main Query on EMP reduced drastically as the number of times a Query on DEPT reduced drastically and this benefit was passed on to the Main Query.

With this Optimization, the benefits observed were :

  1. Reduction in Logical I/O’s for top queries captured above – This means reduction in Latch Gets.
  2. Reduction in Concurrency against S_BRANCH – This means reduction in Latch Gets.
  3. Improved Scalability

This is my new Blog URL

Hi,

I have changed from blogspot to wordpress.com. For unknown reason, my previous blogspot site http://viveklsharma.blogspot.com is not accessible. I have tried contacting support to get the issue resolved, but have not yet received any response. I cannot keep away from writing, so thought of switching over to this.

While, I would try to get my previous blogspot re-activated, for any reason, if this is not done, for the benefit of my reasone, I will repost some of the important and well appreciated write-ups on this site.

%d bloggers like this: