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.

Advertisements

OTN Yathra ! A Six City Tour

OTN Yathra starts from 16th February 2013. This is a collaborative effort between OTN & AIOUG. This is a 6 City Tour, the first destination being Delhi. I am presenting in Delhi, Mumbai and Hyderabad. I am excited to present my first ever presentation in Delhi. For the details and registration, please visit :

http://aioug.org

Regards
Vivek

Update on Sangam 12

Resumed work after attending Sangam 12 in Bangalore. The event was a great success and this time, the crowd was more as compared to last year. This is a good news for Oracle Community in India, as more and more people are now taking interest. The entire first day was full of entertainment and technical stuff from Real World Performance Team. It was nice to see Andrew, Tom Kyte and Graham Wood together at the Podium sharing their experiences, answering to participants queries.

This year, I received a “Speaker of the Year” Award and am very much delighted to see the love and affection from my All India Users. I presented on 3rd November on “Ensuring Optimal Performance”. Every Session that I present is a new learning experience for me as well. It requires extensive study and an in-depth knowlegde of the subject. Apart from the technical knowledge, it requires proper Time-Management skill, which (let me be upfront), I lack. This is the reason, last 20 minutes, I had to be a bit fast and therefore, I feel, it was an injustice to my participants. Anyways, as mentioned, this is also a new learning for me. Next time, time-management will be one of my primary focus, which will ensure that whatever I want to communicate to my participants, will be communicated in a timely manner.

Sangam 12 ! All India Oracle User Group Annual Conference

It’s time to be at Bangalore for an Annual Oracle User Group Conference – Sangam 2012. This is scheduled to be on 2nd and 3rd of November 2012. I am presenting a 1 hour session on “Ensuring Optimal Performance”. This is on 3rd of November. Unlike the events in the past, where the Key Speakers speak for the 1st half of the day, this year, the Key Speakers will speak for the entire day. This year, Thomas Kyte will make his second appearance in India and alongside him, the Podium will be shared by Andrew Holdsworth and Graham Wood, both of them are from Real World Performance Team. This will be a complete Interactive Session, with all three experts answering (with live demonstrations) to the questions being asked by the attendees. It will be a great learning, with fun. If not registered yet, register on http://aioug.org.

Last year due to Business commitments, I could not present and I missed this a lot. This year will be a comeback. Looking forward to meet you all at the event.

AIOUG Tech Event in Chennai ! 21st April 2012

A Tech Day is scheduled in Chennai, India on 21st April 2012. I will be presenting a One Hour session on “All about Performance”. Hope to see many of my followers during the event. For registration, visit http://www.aioug.org.

AIOUG Annual Event Sangam 2011

All India Oracle User Group is back with its Annual Conference and this time, it is back to Bangalore. The event, Sangam 2011, will be held on 9th and 10th of December 2011. This time, Arup Nanda will be presenting 2 half days session. Apart from Arup, there are several other speakers who would be presenting and this again makes the event an Interesting one to attend. This time though I am not presenting, I will be attending both the days as it gives me an oppurtunity to meet up the Oracle Community. Visit http://www.aioug.org to register for the event. See you in Bangalore 🙂

What has triggered this Sudden Performance Degradation ? A Generic Question…

It is a well known fact that most of the time, a Performance Tuning Consultant is involved for a Reactive Support which is due to a severe performance issue. During such involvement, one of the generic question that a Performance Tuning Expert or Consultant has to answer is “What has triggered this Sudden Performance Degradation ?” or a Comment like “Oracle Optimizer is an Issue as it triggered this Degradation and everything came back to normalcy after reverting the Optimizer Stats”. The challenge for the Consultant is to prove that Optimizer is not an Issue and it is the Application behaviour or Indexing Strategy that has caused this issue. This Strategy acted as a slow poison and then a point reached when it finally gave up. During one of my presentation at All India Oracle User Group, I presented on Developing a Scalable Application and this was one of my slide, which I felt would be of use for my readers as well.

In this blog, I will answer to the Question and Comment mentioned above. This would be one of the case that can trigger a sudden degradation. The test case will be around Optimizer Statistics. I have been asked this question by the Customers and concerns were raised over the way Optimizer works or use these Statistics. Some of the Concerns raised are :

  1. Why do I generate fresh Stats when I am Satisfied by the Application Performance on current Stats ?
  2. I generated Statistics over the weekend and suddenly the performance degraded. Finally, I reverted back the statistics and everything came back to normalcy.
  3. What is the cause of this Sudden Degradation ?

While lot has been written on Concern 1 (the way Optimizer works and the change in Optimizer behaviour in 10g, I will answer Concern 2, and Concern 3 will be automatically answered. As usual, all this will be well supported and demonstrated by way of a Test Case.

SCENARIO

A Customer gathered Statistics on all the production tables. As per the policy, these statistics are gathered every weekend since last couple of years. On Monday, suddenly the Users started complaining about the slowness. The Onsite DBA’s had a look at the system and complained about the I/O issue. Further investigations revealed that the Execution plans for some of the critical application queries has changed from Index Scan to Full Table Scan. What has changed between Friday and Monday? The DBA’s realised that the only change is Optimizer Statistics. In such cases, if it is possible to identify the suspected table and its stats, then it is very easy to revert the stats of this table else the option left out is to revert the entire statistics and bring back the stats of Friday. Both the fallback plan has its own Implications. After reverting the Statistics, the Application Performance comes back to normalcy. Based on the cause of the Issue and the change to get back to normalcy, it is quite evident that the finger of blame would be on Oracle Optimizer. The Customer then decided not to gather fresh statistics unless the Optimizer Issue is resolved.

While there are various reasons that can cause a Slowness post-fresh statistics generation, one of the top most cause is Inefficient Indexing. If the Indexing Strategy of an Application is not optimal, the performance of that application would show a sudden degradation. Usually, Performance drop is not linear. It is always exponential, when the utilization reaches a threshold where it is difficult to handle the extra (minor) growth, this minor growth is enough to cause a sudden degradation.

TEST CASE & EXPLANATION

To Demonstrate the impact of In-efficient Indexing, we need to make certain assumption. In this case, let us assume that the Production System has just gone live and the production tables were without any rows, except the Master Tables. For our demonstration, I will create a table and populate some data (initially 10 days) and run an application query against that table. The table is setup to create 10 Customer ID’s and each of these customers have 5 transactions a day. We will run an application query to get the product_name and sale_value for a Customer for a day. This query should return 5 rows from initial 500 rows (10 Days x 5 Transactions a day x 10 Customers).

Next, as the system become 60 days old, the same application query is run. In our case, even if the volume increase, the number of rows return by the query remains unchanged. We shall see the significant impact of this Increase in Volume on the Performance and this impact was all of a sudden.

Table Creation Script - Inserts 10 days Data, which means the production is 10 days old

exec dbms_random.seed(0);
drop table vivek_test;
create table vivek_test as
with main as
(select mod(rownum,10) custid,
        round(dbms_random.value(1000,7000),0) sale_value,
        dbms_random.string('A',20) product_name
from    all_objects a
where rownum<=50)
select custid, s_date sale_date, sale_value, product_name
from    main, (select sysdate - level s_date from dual connect by level<=10) ;

Next we Create an Index on CustID and Gather Statistics

create index vivek_test_idx on vivek_test(custid);
exec dbms_stats.gather_table_stats(user,'VIVEK_TEST',cascade=>true,method_opt=>'for all columns size 1');

Once this is done, we execute our Application Query to get data for 09th June 2011

variable b1 varchar2(20);
exec :b1:='06-09-2011';

select /*+ vivek */ custid, product_name, sale_value
from  vivek_test
where custid=1
and   sale_date between to_date(:b1,'MM-DD-YYYY HH24:MI:SS') and to_date(:b1,'MM-DD-YYYY HH24:MI:SS')+1-1/(24*60*60);

    CUSTID PRODUCT_NAME         SALE_VALUE
---------- -------------------- ----------
         1 rMLTDXxxqXOZnqYRJwIn       1380
         1 AOcitwrWNXCxPHzIIIxw       3850
         1 hpalFVIprLxIHDrRgqHb       2287
         1 vkfKdaFoCaHgBCQAFgCY       4364
         1 DAlVYefHAurNoryMikSJ       1125

Runtime Plan

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

SQL_ID        SQL_TEXT                                           BUFFER_GETS EXECUTIONS VERSION_COUNT
------------- -------------------------------------------------- ----------- ---------- -------------
2t8mwg94j89h8 select /*+ vivek */ custid, product_name, sale_val          10          2             1
              ue from  vivek_test where custid=1 and   sale_date
               between to_date(:b1,'MM-DD-YYYY HH24:MI:SS') and
              to_date(:b1,'MM-DD-YYYY HH24:MI:SS')+1-1/(24*60*60

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID  2t8mwg94j89h8, child number 0
-------------------------------------
select /*+ vivek */ custid, product_name, sale_value from  vivek_test where custid=1
and   sale_date between to_date(:b1,'MM-DD-YYYY HH24:MI:SS') and
to_date(:b1,'MM-DD-YYYY HH24:MI:SS')+1-1/(24*60*60)

Plan hash value: 2623397998

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |       |       |     4 (100)|          |
|*  1 |  FILTER                      |                |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| VIVEK_TEST     |     1 |    35 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | VIVEK_TEST_IDX |    50 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_DATE(:B1,'MM-DD-YYYY HH24:MI:SS')=TO_DATE(:B1,'MM-DD-YYYY HH24:MI:SS') AND
              "SALE_DATE"<=TO_DATE(:B1,'MM-DD-YYYY HH24:MI:SS')+1-.0000115740740740740740740740740740
              7407407407))
   3 - access("CUSTID"=1)

The Runtime Plan in this case shows that the Application Query has used an Index on Custid, which is usually an appropriate approach in an OLTP system. The Interesting fact here is a Throwaway, which is, 50 Rows were fetched from an Index and only 1 row was fetched from the Table Access. In this case, 49 rows were THROWNAWAY. The Actual THROWAWAY rows are 45, but again, 45 rows out of 50 rows is 90% of the rows are discarded and this is very bad.

THE REAL PERFORMANCE ISSUE

User Continue to use the system and it is now 60 days old. As per the policy, after the 60th day, fresh Statistics were gathered. Further, the DBA’s also take the backup of the old stats before generating new. As the data is entered, users also continue to execute the application query.

truncate table vivek_test;
exec dbms_random.seed(0);

insert into vivek_test
with main as
(select mod(rownum,10) custid,
        round(dbms_random.value(1000,7000),0) sale_value,
        dbms_random.string('A',20) product_name
from    all_objects a
where rownum<=50)
select custid, s_date sale_date, sale_value, product_name
from    main, (select sysdate - level s_date from dual connect by leveltrue,method_opt=>'for all columns size 1');

exec dbms_stats.create_stat_table(user,'STATS');
exec dbms_stats.export_table_stats(user,'VIVEK_TEST',stattab=>'stats');

variable b1 varchar2(20);
exec :b1:='06-09-2011';

select /*+ vivek */ custid, product_name, sale_value
from  vivek_test
where custid=1
and   sale_date between to_date(:b1,'MM-DD-YYYY HH24:MI:SS') and to_date(:b1,'MM-DD-YYYY HH24:MI:SS')+1-1/(24*60*60);

Run time Plan
Plan hash value: 2623397998
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |       |       |    18 (100)|          |
|*  1 |  FILTER                      |                |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| VIVEK_TEST     |    15 |   525 |    18   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | VIVEK_TEST_IDX |   300 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Check for the Throwaway percentage now, which has gone up considerably. Let us pump in 5 (for 5 working days) days data, and as per the policy, generate fresh statistics. We already have a backup of the previously generated Statistics in STATS table.

truncate table vivek_test;
exec dbms_random.seed(0);

insert into vivek_test
with main as
(select mod(rownum,10) custid,
        round(dbms_random.value(1000,7000),0) sale_value,
        dbms_random.string('A',20) product_name
from    all_objects a
where rownum<=50)
select custid, s_date sale_date, sale_value, product_name
from    main, (select sysdate - level s_date from dual connect by leveltrue,method_opt=>'for all columns size 1');

variable b1 varchar2(20);
exec :b1:='06-09-2011';

select /*+ vivek */ custid, product_name, sale_value
from  vivek_test
where custid=1
and   sale_date between to_date(:b1,'MM-DD-YYYY HH24:MI:SS') and to_date(:b1,'MM-DD-YYYY HH24:MI:SS')+1-1/(24*60*60);

Run time Plan
Plan hash value: 1579208797

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |       |       |    18 (100)|          |
|*  1 |  FILTER            |            |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| VIVEK_TEST |    15 |   525 |    18   (0)| 00:00:01 |
---------------------------------------------------------------------------------

In just 4-5 days, the plan of the query changed from Index Scan to Full Table Scan. Further, the I/O’s of the query also increased from 10 (when it started) to 22 and then now to 63 per execution. The Cost of the Query remains same. In high concurrency, these Full table Scan’s would surely have an impact on the overall performance. Let us now revert the statistics from the backup table and check for the query performance.

Current Stats
new   2: where table_name='VIVEK_TEST'

OWNER                          PAR   NUM_ROWS     BLOCKS LAST_ANAL GLO
------------------------------ --- ---------- ---------- --------- ---
VIVEK                          NO        3250         60 13-JUN-11 YES

Elapsed: 00:00:00.45
SQL> exec dbms_stats.import_table_stats(user,'VIVEK_TEST',stattab=>'STATS');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.04

Reverted Stats
OWNER                          PAR   NUM_ROWS     BLOCKS LAST_ANAL GLO
------------------------------ --- ---------- ---------- --------- ---
VIVEK                          NO        3000         60 13-JUN-11 YES

variable b1 varchar2(20);
exec :b1:='06-09-2011';

select /*+ vivek */ custid, product_name, sale_value
from  vivek_test
where custid=1
and   sale_date between to_date(:b1,'MM-DD-YYYY HH24:MI:SS') and to_date(:b1,'MM-DD-YYYY HH24:MI:SS')+1-1/(24*60*60);

Runtime Plan
Plan hash value: 2623397998

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |       |       |    18 (100)|          |
|*  1 |  FILTER                      |                |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| VIVEK_TEST     |    15 |   525 |    18   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | VIVEK_TEST_IDX |   300 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

After the Statistics were reverted, the application query regenerated the Better plan. This was enough to raise a doubt over the behaviour of Optimizer. On my test database, the actual issue occured on the 61st day. In your case, this may not be the case. For this example, I will also demonstrate you a way to find out on which day the Optimizer will change the plan.

The test case and demonstration clearly explains that the issue here is not because of the Optimizer Statistics, but is because of Inefficient Indexing, which is also evident from the Throwaway percentage mentioned above. To prove this point, I will create a composite index on Custid, sale_date. I will generate a fresh statistics, which will for 65 days. Once the Application query is run, I will pump in 365 days worth of data, regenerate fresh statistics and check for the Execution Plan (and throwaway percentage).

drop index vivek_test_idx;
create index vivek_test_idx on vivek_test(custid, sale_date);
exec dbms_stats.gather_table_stats(user,'VIVEK_TEST',cascade=>true,method_opt=>'for all columns size 1');
variable b1 varchar2(20);
exec :b1:='06-09-2011';

select /*+ vivek */ custid, product_name, sale_value
from  vivek_test
where custid=1
and   sale_date between to_date(:b1,'MM-DD-YYYY HH24:MI:SS') and to_date(:b1,'MM-DD-YYYY HH24:MI:SS')+1-1/(24*60*60);

SQL_ID        SQL_TEXT                                           BUFFER_GETS EXECUTIONS VERSION_COUNT
------------- -------------------------------------------------- ----------- ---------- -------------
2t8mwg94j89h8 select /*+ vivek */ custid, product_name, sale_val           6          1             1
              ue from  vivek_test where custid=1 and   sale_date
               between to_date(:b1,'MM-DD-YYYY HH24:MI:SS') and
              to_date(:b1,'MM-DD-YYYY HH24:MI:SS')+1-1/(24*60*60
              )

Plan hash value: 2623397998
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |       |       |     5 (100)|          |
|*  1 |  FILTER                      |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| VIVEK_TEST     |    15 |   525 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | VIVEK_TEST_IDX |    16 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

The Production is now a year old, with new Index
truncate table vivek_test;
exec dbms_random.seed(0);

insert into vivek_test
with main as
(select mod(rownum,10) custid,
        round(dbms_random.value(1000,7000),0) sale_value,
        dbms_random.string('A',20) product_name
from    all_objects a
where rownum<=50)
select custid, s_date sale_date, sale_value, product_name
from    main, (select sysdate - level s_date from dual connect by leveltrue,method_opt=>'for all columns size 1');

select /*+ vivek */ custid, product_name, sale_value
from  vivek_test
where custid=1
and   sale_date between to_date(:b1,'MM-DD-YYYY HH24:MI:SS') and to_date(:b1,'MM-DD-YYYY HH24:MI:SS')+1-1/(24*60*60);

SQL_ID        SQL_TEXT                                           BUFFER_GETS EXECUTIONS VERSION_COUNT
------------- -------------------------------------------------- ----------- ---------- -------------
2t8mwg94j89h8 select /*+ vivek */ custid, product_name, sale_val           6          1             1
              ue from  vivek_test where custid=1 and   sale_date
               between to_date(:b1,'MM-DD-YYYY HH24:MI:SS') and
              to_date(:b1,'MM-DD-YYYY HH24:MI:SS')+1-1/(24*60*60
              )

Plan hash value: 2623397998
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |       |       |     5 (100)|          |
|*  1 |  FILTER                      |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| VIVEK_TEST     |    15 |   525 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | VIVEK_TEST_IDX |    15 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

With the New Index, the performance is consistent and the performance matrix (I/O’s and throwaway percentage) are stable too. This answers the concern raised by the Customers “What is the cause of Sudden Degration ?”. In most of the cases, the change in behaviour is due to Application. You may notice that the change in number of rows from 3000 to 3250 (a difference of 250 rows) could cause the performance degradation. In this case, the number of blocks remained unchanged. As mentioned earlier, in my test case, it was the 61st day that triggered the plan change, which means, addition of just 50 rows. On an actual OLTP production system, the number of rows inserted on a day into a table is huge.

You may run the following pl/sql block on your test database to check the day on which the execution plan of above query would change.

drop index vivek_test_idx;
create index vivek_test_idx on vivek_test(custid);
This is for 120 Days and Statement Id from plan_table corresponds to the day.

declare
s_date	varchar2(30):=to_char(trunc(sysdate)-1,'MM-DD-YYYY');
e_date  varchar2(30):=to_char(trunc(sysdate)+1-1/(24*60*60),'MM-DD-YYYY');
l_stat	varchar2(1000);
begin
  execute immediate 'delete from plan_table';
  for i in 1..120
  loop
   execute immediate 'truncate table vivek_test';
   insert into vivek_test 
   with main as
   (select mod(rownum,10) custid,
           round(dbms_random.value(1000,7000),0) sale_value,
           dbms_random.string('A',20) product_name
   from    all_objects a
   where rownum<=50)
   select custid, s_date sale_date, sale_value, product_name
   from    main, (select sysdate - level s_date from dual connect by leveltrue,method_opt=>'for all columns size 1');
   l_stat:= 'explain plan set statement_id '||''''||i||''''||' for select custid, sale_value, sale_date from vivek_test where custid=1
            and   sale_date between to_date(:b2,'||''''||'MM-DD-YYYY HH24:MI:SS'||''''||') 
            and to_date(:b3,'||''''||'MM-DD-YYYY HH24:MI:SS'||''''||')';
    execute immediate l_stat using s_date, e_date;
--    dbms_output.put_line(l_stat);
   commit;
  end loop;
end;
/
column operation for a20
column options for a20
select statement_id, operation, options from plan_table
where operation='TABLE ACCESS'
order by to_number(statement_id);

STATEMENT_ID                   OPERATION            OPTIONS
------------------------------ -------------------- --------------------
1                              TABLE ACCESS         BY INDEX ROWID
2                              TABLE ACCESS         BY INDEX ROWID
3                              TABLE ACCESS         BY INDEX ROWID

56                             TABLE ACCESS         BY INDEX ROWID
57                             TABLE ACCESS         BY INDEX ROWID
58                             TABLE ACCESS         BY INDEX ROWID
59                             TABLE ACCESS         BY INDEX ROWID
60                             TABLE ACCESS         BY INDEX ROWID
61                             TABLE ACCESS         FULL <---- From 61st Day, the Plan Changed
62                             TABLE ACCESS         FULL
63                             TABLE ACCESS         FULL
64                             TABLE ACCESS         FULL

120                            TABLE ACCESS         FULL

120 rows selected.

PL/SQL Parallelism made easier in 11g

Today was the first day of the two day AIOUG Event Sangam 2010. Jonathan Lewis was the Key Speaker this year and I presented a session on “Developing a Scalable Application”. One of my slide mentioned about PL/SQL Parallelism. This is in continuation to my previous blog where I posted about Manual Parallelism that takes the ROWID’s of the table and distributes it across multiple chunks. The limitation with Manual Parallelism is that even though a pl/sql block needs to process 5% to 10% of the rows from a huge table, the Manual Parallelism will select and split the ROWIDs of the entire table. This means, many of the chunks may process very less data or almost zero data.

Oracle Database 11g Release 2 introduced a package dbms_parallel_execute, that makes it easier to split a table into multiple chunks, based on ROWID’s or SQL or by Number Column. During my AIOUG session, I demonstrated the use of this package to split a table into multiple chunks and schedule a batch process in parallel. The best part of this package is that the chunks can be split based on a condition and only the chunks that contain to-be-processed data will be created. Thus the rows to be processed by each of the chunks will have uniform distribution.

As mentioned in my earlier blog, I will reiterate the benefit of pl/sql parallelism, which is, optimal use of available resources. In this blog, I will post the test case that I executed during the AIOUG session and this should help you reproduce this in your environment.

In this example, consider a Nightly Batch Job NIGHTLY_JOB and this job processes the rows from VIVEK_TEST Table using an approach which is widely implemented by the developers or the dba’s. This is single job processing the required rows and thus is a very slow and an inefficient approach. There are 7200 rows in this table to be processed and assuming that the business logic in the pl/sql takes atleast 1 second and then moves on to another row. Which means, at a minimum, each row to be processed will take atleast 1 second, thus the entire process will take atleast 7200 second (2 Hours). This is the code of a batch process :

create table vivek_test pctfree 50 pctused 50 as
select * from all_objects a
where rownum=1000 then
    commit;
    l_limit:=0;
  end if;
  end loop;
end;
/
exec nightly_batch;

Once the job is executed, you can monitor the progress of this batch process from another session. V$SESSION will report the progress in MODULE column, as posted by dbms_application_info procedure.

The changes required to parallize the process is pasted below. To monitor the progress and maintain the log of each of the CHUNKS, I have created a process_status table. One thing worth noticing is the PCTFREE and PCTUSED setting, which is set to 50. The reason I have this setting is that the number of chunks to be created is dependant on the number of extents in the table and I wanted multiple extents for my table. In a production environment, number of extents should not be an issue.

create table process_status (
process_name		varchar2(30),
job_id			number,
s_rowid			rowid,
e_rowid			rowid,
start_time		date,
end_time		date,
processed_rows		number,
completed_yn		varchar2(1),
status			varchar2(1000));

create or replace procedure parallel_job(chks in number) is
l_statement	varchar2(1000);
l_jobno		number;
l_retry		number;
l_status	   number;
l_chunkid	   number;
l_srowid	   rowid;
l_erowid	   rowid;
l_anyrows   	boolean;
l_errcode	   pls_integer;
begin

  execute immediate 'truncate table process_status';
  /* CREATE A PARALLEL TASK */
  dbms_parallel_execute.create_task('NIGHT_JOB');

  dbms_parallel_execute.create_chunks_by_rowid('NIGHT_JOB','VIVEK','VIVEK_TEST',true, chks);
 /* INSERT THE CHUNKS INTO PROCESS_STATUS TO LOG THE STATUS OF EACH JOB */
  insert into process_status(process_name, job_id, s_rowid, e_rowid)
  select 'NIGHT_JOB', chunk_id, start_rowid, end_rowid from user_parallel_execute_chunks
  where task_name='NIGHT_JOB' order by 1;
  commit;
  /* Run the Procedure here */
  LOOP
      -- Get a chunk to process; if there is nothing to process, then exit the 
      -- loop;
      DBMS_PARALLEL_EXECUTE.GET_ROWID_CHUNK('NIGHT_JOB',l_chunkid, l_srowid, l_erowid,l_anyrows);
      IF (l_anyrows = false) THEN EXIT; END IF;
      BEGIN
	l_statement:='nightly_batch('||l_chunkid||','||''''||l_srowid||''''||','||''''||l_erowid||''''||');';
	dbms_job.submit(l_jobno,l_statement);
	update process_status set completed_yn='P', start_time=sysdate where job_id=l_chunkid;
	commit;
        DBMS_PARALLEL_EXECUTE.SET_CHUNK_STATUS('NIGHT_JOB',l_chunkid,DBMS_PARALLEL_EXECUTE.PROCESSED);
      EXCEPTION WHEN OTHERS THEN
        DBMS_PARALLEL_EXECUTE.SET_CHUNK_STATUS('NIGHT_JOB', l_chunkid,
          DBMS_PARALLEL_EXECUTE.PROCESSED_WITH_ERROR, SQLCODE, SQLERRM);
	  l_errcode:=sqlcode;
  	update process_status set completed_yn='F', end_time=sysdate, status='FAILED WITH ERROR '||l_errcode||'.' where job_id=l_chunkid;
      END;
  commit;
  END LOOP;
  dbms_parallel_execute.drop_task('NIGHT_JOB');
end;
/

The only change required in our NIGHTLY_BATCH is the introduction of INPUT parameters and update of the process_status at the completion or failure of a chunk.

create or replace procedure nightly_batch(jobid number, low_rowid rowid, high_rowid rowid) is
l_count		number:=0;
l_module	    varchar2(30);
l_limit		number:=0;
l_errcode	    pls_integer;
cursor c_main is
select rowid, owner, object_name, object_type from vivek_test
where nvl(processed_flag,'N')='N'
and	rowid between low_rowid and high_rowid;
begin
  for i in c_main
  loop
  l_count:=l_count+1;
  l_limit:=l_limit+1;
  l_module:=l_count||' out of 7200/40=180';
  dbms_application_info.set_module('PL_SQL',l_module);
  dbms_lock.sleep(1); /* Assuming Processing of a row takes 1 second */
  update vivek_test set processed_flag='Y' where rowid = i.rowid;
  if l_limit>=1000 then
    commit;
    l_limit:=0;
  end if;
  end loop;
  update process_status set completed_yn='Y', end_time=sysdate, processed_rows=l_count 
  where job_id = jobid;
  commit;
  exception when others then
  l_errcode:=sqlcode;
  update process_status set completed_yn='F', status='FAILED WITH '||l_errcode||'.' where job_id = jobid;
  commit;
end;
/
exec parallel_job(100);

Monitor the progress of this job and you would see a significant improvement as the job completes in less than 5 minutes, as against 2 Hours, and utilizes the hardware resources optimally.

Agenda for Sangam 2010, Hyderabad

Got couple of mails from my customer and many of these were interested on a detailed explanation on the optmization done at their site. While some of these will be covered during the presentation, some of these may miss out due to the time slot alloted to me. I have prioritized these optimizations and the interesting ones have been on my ppt’s.

In case, any one wants me to speak on a specific optimization feature, do update the blog (comment) and I would try to relate it to my Real Life Optimizations and present it with good examples.

One Interesting topic to be covered will be “Relation between Column Ordering of a Table and CPU time required to process a column”. This will also feature in an Oracle Connect Magazine to be circulated by AIOUG.

Next, I will also answer a common question raised by a Customer to Performance Tuning Specialist “Till Yesterday, the performance of the system was perfectly fine, How come the performance degraded all of a sudden ?”

AIOUG Sangam 2010 Event

Last year September 2009, AIOUG, for the first time, hosted Sangam 2009 with a key Speaker Thomas Kyte. After the success of Sangam 09, it’s time for Sangam10 and this year Jonathan Lewis will be visiting India for the first time. Sangam 2010 is scheduled in Hyderabad on September 3rd and 4th.

Unlike last year, where I presented three sessions, a session a day, of 1 hour each, this year I am presenting a 1 hour session. Hope to see maximum participation to make it a success. Registrations are open.

AIOUG Sangam 2010 Registration and Agenda

%d bloggers like this: