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

Oracle Tech Challenge at AIOUG

As mentioned in my earlier blog, I would be conducting an Oracle Tech Challenge, alongwith my colleague, at the AIOUG Techday to be held in Mumbai on 18th June 2010. Before the Tech Challenge, I would also be presenting a 1 hour session on “Developing a Scalable Application”. Registration are in progress. Click on the link below to register.

AIOUG TechDay Mumbai, June 18 2010

AIOUG Full Day Technical Event in Mumbai, India

I will be presenting a session on “Performance Optimization” during a Full Day Technical Event organized by All India Oracle User Group in Mumbai.

The link for the detailed agenda is as under :

AIOUG TechDay, Mumbai.

Hope to see you all on June 18th 2010.

Row Cache Object Latch Contention : Improper Implementation of Oracle Feature – Day 2 of AIOUG

On Day 2 of AIOUG, I discussed on Library Cache related latches and some causes of the contention on these latches. One such contention is for Row Cache Object latches that protects the Data Dictionary loaded in the Shared Pool. Dictionary Cache is one of the component of a Shared Pool and all the definitions, required to parse a Cursor, is stored in this cache, in the form of a ROW, thus is protected by Row Cache Object Latches.

The Issue

Almost a year back, I was involved in a major performance issue caused due to “Latch: row cache objects” contention. This was a 2 Node RAC setup running 10.2.0.3 database. The issue was normally reported during the peak hour with CPU Utilization above 95%. During this slowness window, there was a major drip in Transaction per Second and this resulted in an increase in pending volume to be entered into the system.

As mentioned earlier, as “Row Cache Object” latches protect Dictionary Cache information, contention on these latches needs investigating the sub-component of the Dictionary Cache and this can be done thru AWR reports. “Dictionary Cache Stats” section in AWR gives the Get Requests, PCT Miss and other required information for each of the sub-component of Dictionary Cache. This section revealed that the GET REQUESTS on DC_USERS was very high. Another alarming statistics was huge version_counts for the application queries. Since, the number of get requests on DC_Users seemed to be abnormally high, the focus was to know for the potential causes of these high numbers and the one related to this application or database. Further investigation drilled down the issue to Virtual Private Database (VPD). The customer here had implemented VPD and this implementation caused the contention on these latches.

Does it mean VPD is an Issue ?

VPD is a very useful feature as it provides an easy and manageable means of implementing row level security, but there are certain implementation portions, that needs to be considered based on the requirement, and it is this piece, if improperly implemented, can cause a major performance issue. In a nutshell, it is not VPD but the way it was being implemented that caused a major issue at the customer site. We will cover this piece of problematic implementation in this blog, with a test case, that will help you to test this on your test system and validate the results.

A brief explanation of VPD

VPD, also called as Row Level Security or Fine Grained Access Control, is a feature, introduced in Oracle 8i, that allows you to define security policies on tables by way of a function that implies a restriction on the rows that a user can SELECT or UPDATE or INSERT or DELETE, based on the privileges defined for that user. For example, assuming a following hierarchy :
Employee S is from South India Region, N from North India Region, W from West India Region, E from East India Region, I from All India Region and finally, APAC for Asia Pacific. With this herierchy, a requirement like :

  1. User S can View and Manipulate all the rows from South India Region, and,
  2. User N can View and Manipulate all the rows from North India Region, and,
  3. User W can View and Manipulate all the rows from West India Region, and,
  4. User E can View and Manipulate all the rows from East India Region, and,
  5. User I can View and Manipulate all the rows from 1 to 4 i.e.South, North, West and East India Region, while,
  6. User A can View and Manipulate all the rows from 1 to 5, including other countries in Asia Pacific Region.

These restrictions can be implemented by using VPD without much of an effort. Application does not require any changes for these implementations, as these restrictions are applied in the background at the runtime. There are three basic components of VPD. These are :
Policy : A declarative command that determines when and how to apply the policy i.e. during Selects, Insertions, Deletion, Updation or combination of any or all of these.
Policy Function : A Pl/SQL function that is called whenever the condition specified in the policy are met.
Predicate : A string that is generated by the policy function, and the applied to the users’ sql statements. This is an additional Filter predicate generated by the policy function and appended to the sql statement executed by the user, at the runtime.

Once these three are defined as a policy, then this policy can be attached to the tables that requires Security to be implemented. While Policy, Policy Function and Predicate are three main building blocks of VPD, there is another component, that is mostly ignored, called as Policy Type. In most of the cases, this is not defined, which means, it is left as default. If not defined, it defaults to a value depending on the database version and therefore, should not be left undefined. Starting Oracle 10g, the policy type defaults to DYNAMIC. While there are other policy types : SHARED Static, CONTEXT-SENSITIVE and SHARED Context Sensitive. The implementation of each of these depend upon the requirement.

Back to the Issue

In this case, it was the implementation of VPD that caused huge Version Count and both of these had a major impact on the performance due to heavy latch contention. Again, it was not the VPD that caused multiple version counts, but the implementation. The Application team, in this case, while implementing VPD, left POLICY_TYPE to default and this means DYNAMIC. DYNAMIC policy_type implies, dynamic builtup of the predicates at runtime, based on the policy function. The issue with Dynamic policy_type is that with each row processed by the query, the policy function is applied, predicate is evaluated and validated whether the row is to be fetched to the user. This means, the query goes through parse-execute-fetch phase for each and every row processed by the query and this significantly increases a Parse Overhead.

Based on the customer requirement, a better option would have been either Context-Sensitive or Shared Context-Sensitive, that internally implements Dynamic predicate generation, at runtime, but goes through the Parse phase only when the Context of the User Session changes and not for each and every row processed. Further, in this customer case, just changing the Policy_type from Dynamic to Shared Context-Sensitive did not help, as there was a major design flaw in the policy function. Lets walk thru a test case and at the end, I will present some facts from the customer system. The test case simulates the implementation at the customer site and the change suggested, thereafter to improve the performance. The change, as depicted in the test case, is in the policy function and the change in policy_type is already changed to Shared Context-Sensitive.

TEST CASE

For this test case to work, I have created some set of statistics table that will store the latch activity. Schema VIVEK contains an application table TEST_VPD. I further create USER_X schema and already have SCOTT and OE, as an application user schemas, ITDBA schema, that contains Security Table. This table defines the security policies for each application users. All of the VPD implementations pre-requisites are created in ITDBA schema.

Once the pre-requisites are created, we will execute certain queries, against TEST_VPD, from VIVEK : access to all the rows, OE Schema : access to rows pertaining to rcc_num=10, SCOTT Schema : access to rows pertaining to rcc_num=20 and USER_X Schema : access to rows pertaining to rcc_num=30. All these access restrictions are defined in EMP_SEC table under ITDBA Schema and the predicates are applied, at run time, by way of Policy Function GF_GLOBAL_POLICY_OPT_RSTRCT_VW, again under ITDBA Schema. Once these are done, we will run certain queries against our Statistics table and some data dictionary views to check for the performance. The policy function, GF_GLOBAL_POLICY_OPT_RSTRCT_VW, will then be changed to improve the performance and the queries against TEST_VPD and measure the performance.

connect vivek/vivek

drop table latch_stats_start;
drop table latch_stats_end;
drop table test_vpd;
drop user user_x cascade;
drop user itdba cascade;
drop public synonym as_test_vpd;
drop context rcc_ctx;

exec dbms_random.seed(0);
create global temporary table latch_stats_start as
select name, gets from v$latch;

create global temporary table latch_stats_end as
select name, gets from v$latch;

create table test_vpd as
select	dbms_random.string('U',5)||lpad(rownum,5,'0') pan,
	dbms_random.string('U',20) name,
	case when mod(rownum,10)=0 then 'F' else 'M' end sex,
	round(dbms_random.value(10,100),0) rcc_num
from	all_objects
where	rownum between 1 and 10000;
create unique index pk_pan on test_vpd(pan);
exec dbms_stats.gather_table_stats(user,'test_vpd',cascade=>true,method_opt=>'for all columns size 1');
create public synonym as_test_vpd for test_vpd;
grant select on as_test_vpd to public;

create user user_x identified by vivek;
grant connect,resource to user_x;

connect sys/vivek as sysdba
create user itdba identified by vivek;
grant dba to itdba;
grant execute on dbms_rls to itdba;

connect itdba/vivek

create context rcc_ctx using ITDBA.set_rcc_ctx;

create procedure set_rcc_ctx(p_attr in varchar2, p_val1 in varchar2, p_val2 in varchar2, p_val3 in varchar2)
is
begin
   dbms_session.set_context('RCC_CTX',p_attr,p_val1, p_val2, p_val3);
end;
/

create table itdba.emp_sec (user_id varchar2(20), rcc_num number);

insert into emp_sec values('SCOTT',20);
insert into emp_sec values('OE',10);
insert into emp_sec values('USER_X',30);

CREATE OR REPLACE function ITDBA.GF_GLOBAL_POLICY_OPT_RSTRCT_VW(OBJECT_SCHEMA VARCHAR2, OBJECT_NAME VARCHAR2) return varchar2 is
    L_SEL_FLG VARCHAR2(3);
    L_RCC_NUM NUMBER;
    L_USER_ID  VARCHAR2(20);
    l_predicate varchar2(30);
    v_Restricted_Policy varchar2(1);
begin
    IF  L_USER_ID IS NULL AND USER IN ('ITDBA','SYS','SYSTEM','VIVEK') THEN
       RETURN NULL;
    ELSE
       IF L_USER_ID IS NULL THEN
 	  L_USER_ID:=USER;
       END IF;
     if l_user_id in ('USER_X','SCOTT','OE') then v_Restricted_Policy:='Y'; end if;
     IF v_Restricted_Policy = 'Y' THEN
       select rcc_num into l_rcc_num from itdba.emp_sec
       where user_id = l_user_id;
       set_rcc_ctx('RCC_NUM',l_rcc_num, null, null);
	l_predicate:='RCC_NUM in '||sys_context('RCC_CTX','RCC_NUM');
	 return l_predicate;
     ELSE
       select rcc_num into l_rcc_num from itdba.emp_sec
       where user_id = l_user_id;
       set_rcc_ctx('RCC_NUM',l_rcc_num,l_rcc_num+100,99);
	l_predicate:='RCC_NUM in '||sys_context('RCC_CTX','RCC_NUM');
	 return l_predicate;
     END IF;
    END IF;
Exception
  When others then
       Raise_Application_Error (-20000,SQLERRM);
END;
/
begin
dbms_rls.add_policy(object_schema=>'VIVEK',
   object_name=>'test_vpd',
   policy_name=>'RCC_NUM_POLICY',
   function_schema=>'ITDBA',
   policy_function=>'GF_GLOBAL_POLICY_OPT_RSTRCT_VW',
   statement_types=>'INSERT,SELECT,UPDATE,DELETE',
   update_check=>true,
   policy_type=>dbms_rls.SHARED_CONTEXT_SENSITIVE);
end;
/

Now, since the pre-requisite is done, we will execute a query against TEST_VPD from VIVEK, OE, SCOTT and USER_X Schemas to check the rows returned by each of these users. This will help us validate and confirm whether the policy function is applied as per our requirement.

connect vivek/vivek
column pan for a20
column name for a30

select /*+ VIVEK */ * from as_test_vpd where rcc_num=10 and rownum<=3;

select /*+ VIVEK */ * from as_test_vpd  where rcc_num=20 and rownum<=3;

select /*+ VIVEK */ * from as_test_vpd  where rcc_num=30 and rownum<=3;

## User OE only allowed to see 10
----------------------------------

connect oe/vivek

select /*+ VIVEK */ * from as_test_vpd where rcc_num=10 and rownum<=3;
select /*+ VIVEK */ * from as_test_vpd where rcc_num=20 and rownum<=3;
select /*+ VIVEK */ * from as_test_vpd where rcc_num=30 and rownum<=3;

## User SCOTT only allowed to see 20
-------------------------------------
connect scott/tiger

select /*+ VIVEK */ * from as_test_vpd where rcc_num=10 and rownum<=3;
select /*+ VIVEK */ * from as_test_vpd  where rcc_num=20 and rownum<=3;
select /*+ VIVEK */ * from as_test_vpd where rcc_num=30 and rownum<=3;

## User USER_X only allowed to see 30
---------------------------------------

connect user_x/vivek

select /*+ VIVEK */ * from as_test_vpd where rcc_num=10 and rownum<=3;
select /*+ VIVEK */ * from as_test_vpd  where rcc_num=20 and rownum<=3;
select /*+ VIVEK */ * from as_test_vpd  where rcc_num=30 and rownum<=3;

Now for the performance. This is the Original Implementation.

connect vivek/vivek
alter system flush shared_pool;
connect scott/tiger
insert into vivek.latch_stats_start select name, gets from v$latch;
select /*+ VIVEK */ * from as_test_vpd where rownum<=3;
insert into vivek.latch_stats_end select name, gets from v$latch;
connect oe/vivek
select /*+ VIVEK */ * from as_test_vpd where rownum<=3;
connect user_x/vivek
select /*+ VIVEK */ * from as_test_vpd where rownum<=3; 
SQL>set lines 132
SQL> set pages 1000
SQL> column sql_text for a40 wrap
SQL>
SQL> select sql_id, sql_text, executions, child_latch, version_count from v$sqlarea where sql_text like 'select /*+ VIVEK%';

SQL_ID        SQL_TEXT                                 EXECUTIONS CHILD_LATCH VERSION_COUNT
------------- ---------------------------------------- ---------- ----------- -------------
1n62sv9g6nw77 select /*+ VIVEK */ * from as_test_vpd w          3           2             3
              here rownum<=3 
SQL>select sql_id, sql_text, executions, child_latch, child_address from v$sql where sql_text like 'select /*+ VIVEK%';

SQL_ID        SQL_TEXT                                 EXECUTIONS CHILD_LATCH CHILD_AD
------------- ---------------------------------------- ---------- ----------- --------
1n62sv9g6nw77 select /*+ VIVEK */ * from as_test_vpd w          1           2 69EAE56C
              here rownum<=3

1n62sv9g6nw77 select /*+ VIVEK */ * from as_test_vpd w          1           2 69065194
              here rownum<=3

1n62sv9g6nw77 select /*+ VIVEK */ * from as_test_vpd w          1           2 68FEDAE8
              here rownum<=3 
SQL>column predicate for a30
SQL>select sql_id, ADDRESS, PREDICATE from v$vpd_policy where sql_id='1n62sv9g6nw77';

SQL_ID        ADDRESS  PREDICATE
------------- -------- ------------------------------
1n62sv9g6nw77 69EAE56C RCC_NUM in 20
1n62sv9g6nw77 69065194 RCC_NUM in 10
1n62sv9g6nw77 68FEDAE8 RCC_NUM in 30

When the Query was executed from different application users, three in our case, the version_count of the query increased as well. The dynamic performance view V$VPD_POLICY shows the runtime plan, which is Literal in this case and causes a query to be hard parsed. Below mentioned query can be executed to check for the latch statistics.

column name for a30
set lines 200
set pages 1000
select * from (select a.name, b.gets end_gets, a.gets Start_gets, b.gets-a.gets
from	vivek.latch_stats_start a,
	vivek.latch_stats_end b
where	b.name = a.name
order by 4 desc)
where rownum<=10;

MODIFICATION

The code below shows the modification to the function that is applied to the policy. Look for the l_predicate value at the end that is finally returned as an Output to the function.

connect vivek/vivek
CREATE OR REPLACE function ITDBA.GF_GLOBAL_POLICY_OPT_RSTRCT_VW(OBJECT_SCHEMA VARCHAR2, OBJECT_NAME VARCHAR2) return varchar2 is
    L_SEL_FLG VARCHAR2(3);
    L_RCC_NUM NUMBER;
    L_USER_ID  VARCHAR2(20);
    l_predicate varchar2(60);
    v_Restricted_Policy varchar2(1);
begin
    IF  L_USER_ID IS NULL AND USER IN ('ITDBA','SYS','SYSTEM','VIVEK') THEN
       RETURN NULL;
    ELSE
       IF L_USER_ID IS NULL THEN
 	  L_USER_ID:=USER;
       END IF;
     if l_user_id in ('USER_X','SCOTT','OE') then v_Restricted_Policy:='Y'; end if;
     IF v_Restricted_Policy = 'Y' THEN
       select rcc_num into l_rcc_num from itdba.emp_sec
       where user_id = l_user_id;
       set_rcc_ctx('RCC_NUM',l_rcc_num, null, null);
	l_predicate := 'RCC_NUM = sys_context (''RCC_CTX'', ''RCC_NUM'')';
	 return l_predicate;
     ELSE
       select rcc_num into l_rcc_num from itdba.emp_sec
       where user_id = l_user_id;
       set_rcc_ctx('RCC_NUM',l_rcc_num,l_rcc_num+100,99);
       	l_predicate := 'RCC_NUM = sys_context (''RCC_CTX'', ''RCC_NUM'')';
	 return l_predicate;
     END IF;
    END IF;
Exception
  When others then
       Raise_Application_Error (-20000,SQLERRM);
END;
/

Once the change is done, we will execute the query on TEST_VPD from the three application users (USER_X, OE and SCOTT), and compare the latch statistics and version_count.

connect vivek/vivek
alter system flush shared_pool;
connect scott/tiger
insert into vivek.latch_stats_start select name, gets from v$latch;
select /*+ VIVEK */ * from as_test_vpd where rownum<=3;
insert into vivek.latch_stats_end select name, gets from v$latch;
connect oe/vivek
select /*+ VIVEK */ * from as_test_vpd where rownum<=3;
connect user_x/vivek
select /*+ VIVEK */ * from as_test_vpd where rownum<=3;
connect vivek/vivek
Connected.
SQL> set lines 132
SQL> set pages 1000
SQL> column sql_text for a40 wrap
SQL>
SQL> select sql_id, sql_text, executions, child_latch, version_count from v$sqlarea where sql_text like 'select /*+ VIVEK%';

SQL_ID        SQL_TEXT                                 EXECUTIONS CHILD_LATCH VERSION_COUNT
------------- ---------------------------------------- ---------- ----------- -------------
1n62sv9g6nw77 select /*+ VIVEK */ * from as_test_vpd w          3           2             1
              here rownum<=3 
SQL>select sql_id, sql_text, executions, child_latch, child_address from v$sql where sql_text like 'select /*+ VIVEK%';

SQL_ID        SQL_TEXT                                 EXECUTIONS CHILD_LATCH CHILD_AD
------------- ---------------------------------------- ---------- ----------- --------
1n62sv9g6nw77 select /*+ VIVEK */ * from as_test_vpd w          3           2 68BEE27C
              here rownum <=3 
SQL>column predicate for a30
SQL>select sql_id, ADDRESS, PREDICATE from v$vpd_policy where sql_id='1n62sv9g6nw77';

SQL_ID        ADDRESS  PREDICATE
------------- -------- ------------------------------
1n62sv9g6nw77 68BEE27C RCC_NUM = sys_context ('RCC_CT
                       X', 'RCC_NUM')

Improved performance can be clearly seen from the version_count, output of V$VPD_POLICY view and from the latch statistics. Once this was implemented, the customer reported a significant performance improvement, for which the real facts are shown below.



Optimizing Performance Part 2 Matrix


Optimizing Performance Part 2 Top 5 waits

CUSTOMER MATRIX – BEFORE

 

TOP 5 WAITS

 



Optimizing Performance Part 2 Achievement

CUSTOMER MATRIX – AFTER

 

To summarize, Oracle provided features enable us to implement these with great ease. Implementing these require careful study of the requirement and proper implementation, that does not compromise with the performance.