Oracle … as usual

Oracle by Laurent Leturgez

Oracle 11g, frequency histograms, and character columns …

Recently I worked on a performance issue on two Oracle 11g instances (11.2.0.3 and 11.2.0.4) and I hit a bug concerning histograms and character columns. Oracle 11g is now about 7 years old but it’s a version that is still used in lots of systems .. that’s why I decided to write this blog post.

Well, in Oracle 11g, you have two kind of histograms :

  • Frequency histograms: they are calculated when the number of distinct values (NDV) in a column is lower than the number of buckets in the histogram. Frequency histograms are the most accurate histogram, because they count the exact number of occurrences for each value.
  • Height balanced histograms: they are calculated when the NDV is greater than the number of buckets in the histogram. Height balanced histograms are less accurate but they give some precious information to the optimizer on data distribution.

Histograms help the cost optimizer to take the good decision when the CBO have to choose for an access path or a join. (You can find very interesting papers on this topic by Jonathan Lewis or Mohamed Houri)

The problem I met was related to frequency histograms on character columns (CHAR, VARCHAR2 and NVARCHAR2).

In the dictionary, there are two views that give interesting information about how data is distributed:

  • DBA_TAB_COL_STATISTICS: this view gives information about column statistics (density, NDV, sample_size etc.) and if there’s an histogram on the column.
  • DBA_HISTOGRAMS: this view gives information on column histograms and how data values are distributed

Let’s took an example:

LAURENT @ orcl >; create table foo (id number);

Table created.

LAURENT @ orcl > begin
 2 for i in 1..10
 3 loop
 4 insert into foo select i from dual connect by level <=i; 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. LAURENT @ orcl > commit;

Commit complete.

LAURENT @ orcl > select id,count(*) from foo group by id order by 1;

	ID   COUNT(*)
---------- ----------
	 1	    1
	 2	    2
	 3	    3
	 4	    4
	 5	    5
	 6	    6
	 7	    7
	 8	    8
	 9	    9
	10	   10

10 rows selected.

Now, we gather statistics with a frequency histogram (that’s why I chose 11 buckets):

LAURENT @ orcl > exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'FOO', method_opt=>'FOR COLUMNS ID SIZE 11', estimate_percent=>100);

PL/SQL procedure successfully completed.

LAURENT @ orcl > select table_name,column_name,num_distinct,density,num_nulls,sample_size,histogram
 2 from user_tab_col_statistics
 3 where table_name='FOO';

TABLE_NAME COLUMN_NAM NUM_DISTINCT    DENSITY  NUM_NULLS SAMPLE_SIZE HISTOGRAM
---------- ---------- ------------ ---------- ---------- ----------- ---------------
FOO        ID                   10 .009090909          0          55 FREQUENCY

Now let’s have a look to the DBA_HISTOGRAMS view (or USER or ALL depending on the context)


LAURENT @ orcl > select table_name,column_name,
  2  endpoint_number,endpoint_value,
  3  endpoint_actual_value
  4  from user_histograms
  5  where table_name='FOO';

TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- --------------- -------------- ----------
FOO        ID                       1              1
FOO        ID                       3              2
FOO        ID                       6              3
FOO        ID                      10              4
FOO        ID                      15              5
FOO        ID                      21              6
FOO        ID                      28              7
FOO        ID                      36              8
FOO        ID                      45              9
FOO        ID                      55             10

The ENDPOINT_VALUE represents the column value, for example, and because it’s a frequency histogram, if we’d like to know how many values of 7 there is in the table, we have to take the endpoint_number for this value: 28 and to substract the endpoint_number of the preceding endpoint-value (6) … So there are 28-21=7 lines with the value 7.

Now, you’ve understood this basic … let’s take another example:


LAURENT @ orcl > begin
  2  for i in 1..10000
  3  loop
  4  case mod(i,10)
  5    when 0 then insert into t values(mod(i,10), concat(lpad('X',30,'X'),'01'));
  6    when 1 then insert into t values(mod(i,10), concat(lpad('X',30,'X'),'02'));
  7    when 2 then insert into t values(mod(i,10), concat(lpad('X',30,'X'),'03'));
  8    when 3 then insert into t values(mod(i,10), concat(lpad('X',30,'X'),'04'));
  9    when 4 then insert into t values(mod(i,10), concat(lpad('X',30,'X'),'05'));
 10    when 5 then insert into t values(mod(i,10), concat(lpad('X',30,'X'),'06'));
 11    when 6 then insert into t values(mod(i,10), concat(lpad('X',30,'X'),'07'));
 12    when 7 then insert into t values(mod(i,10), concat(lpad('X',30,'X'),'08'));
 13    when 8 then insert into t values(mod(i,10), concat(lpad('X',30,'X'),'09'));
 14    when 9 then insert into t values(mod(i,10), concat(lpad('X',30,'X'),'10'));
 15  end case;
 16  end loop;
 17  commit;
 18  end;
 19  /

PL/SQL procedure successfully completed.

LAURENT @ orcl > insert into t select 10,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' from dual connect by level <=100000; 

100000 rows created. 

LAURENT @ orcl > commit;

Commit complete.

LAURENT @ orcl > exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'T', method_opt=>'for all columns size 12', estimate_percent=>100);

PL/SQL procedure successfully completed.

LAURENT @ orcl > select table_name,column_name,density,histogram,sample_size from user_tab_col_statistics where table_name='T';

TABLE_NAME COLUMN_NAM    DENSITY HISTOGRAM       SAMPLE_SIZE
---------- ---------- ---------- --------------- -----------
T          ID         4.5455E-06 FREQUENCY            110000
T          V          4.5455E-06 FREQUENCY            110000

And if we have a look to the data distribution of the column V, no problem …


LAURENT @ orcl > select table_name,column_name,
  2  endpoint_number,endpoint_value,
  3  endpoint_actual_value
  4  from user_histograms
  5  where table_name='T' and column_name='V'
  6  order by ENDPOINT_ACTUAL_VALUE;

TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
---------- ---------- --------------- -------------- --------------------------------
T          V                     1000     4.5871E+35 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX01
T          V                     2000     4.5871E+35 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX02
T          V                     3000     4.5871E+35 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX03
T          V                     4000     4.5871E+35 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX04
T          V                     5000     4.5871E+35 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX05
T          V                     6000     4.5871E+35 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX06
T          V                     7000     4.5871E+35 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX07
T          V                     8000     4.5871E+35 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX08
T          V                     9000     4.5871E+35 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX09
T          V                    10000     4.5871E+35 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX10
T          V                   110000     4.5871E+35 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

11 rows selected.

The problem comes when the size of the character column exceeds 32 bytes:


LAURENT @ orcl > drop table t purge;

Table dropped.

LAURENT @ orcl > create table t (id number, v varchar2(64));

Table created.

LAURENT @ orcl > begin
for i in 1..10000
loop
case mod(i,10)
  when 0 then insert into t values(mod(i,10), concat(lpad('X',32,'X'),'01'));
  when 1 then insert into t values(mod(i,10), concat(lpad('X',32,'X'),'02'));
  when 2 then insert into t values(mod(i,10), concat(lpad('X',32,'X'),'03'));
  when 3 then insert into t values(mod(i,10), concat(lpad('X',32,'X'),'04'));
  when 4 then insert into t values(mod(i,10), concat(lpad('X',32,'X'),'05'));
  when 5 then insert into t values(mod(i,10), concat(lpad('X',32,'X'),'06'));
  when 6 then insert into t values(mod(i,10), concat(lpad('X',32,'X'),'07'));
  when 7 then insert into t values(mod(i,10), concat(lpad('X',32,'X'),'08'));
  when 8 then insert into t values(mod(i,10), concat(lpad('X',32,'X'),'09'));
  when 9 then insert into t values(mod(i,10), concat(lpad('X',32,'X'),'10'));
end case;
end loop;
commit;
end;
/

LAURENT @ orcl > insert into t select 10,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' from dual connect by level <=100000; 100000 rows created. LAURENT @ orcl > commit;

Commit complete.

LAURENT @ orcl > exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'T', method_opt=>'for all columns size 12', estimate_percent=>100);

PL/SQL procedure successfully completed.

LAURENT @ orcl > select table_name,column_name,density,histogram,sample_size from user_tab_col_statistics where table_name='T';

TABLE_NAME COLUMN_NAM    DENSITY HISTOGRAM       SAMPLE_SIZE
---------- ---------- ---------- --------------- -----------
T          ID         4.5455E-06 FREQUENCY            110000
T          V          4.5455E-06 FREQUENCY            110000

But … if we have a look to the DBA_HISTOGRAMS view …


LAURENT @ orcl > select table_name,column_name,
  2  endpoint_number,endpoint_value,
  3  endpoint_actual_value
  4  from user_histograms
  5  where table_name='T'
  6  order by COLUMN_NAME,ENDPOINT_ACTUAL_VALUE;

TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
---------- ---------- --------------- -------------- --------------------------------
T          ID                    1000              0
T          ID                    2000              1
T          ID                    3000              2
T          ID                    4000              3
T          ID                    5000              4
T          ID                    6000              5
T          ID                    7000              6
T          ID                    8000              7
T          ID                    9000              8
T          ID                   10000              9
T          ID                  110000             10
T          V                   110000     4.5871E+35

LAURENT @ orcl > select count(*) from t where v=lpad('X',32,'X')||'05';

  COUNT(*)
----------
      1000

LAURENT @ orcl > select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID  aass8rg64h38b, child number 0
-------------------------------------
select count(*) from t where v=lpad('X',32,'X')||'05'

Plan hash value: 2966233522

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      2 |        |      2 |00:00:00.01 |    1416 |
|   1 |  SORT AGGREGATE    |      |      2 |      1 |      2 |00:00:00.01 |    1416 |
|*  2 |   TABLE ACCESS FULL| T    |      2 |   110K |   2000 |00:00:00.01 |    1416 |
-------------------------------------------------------------------------------------

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

   2 - filter("V"='XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX05')

19 rows selected.

LAURENT @ orcl > select count(*) from t where v='XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';

  COUNT(*)
----------
    100000

LAURENT @ orcl > select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID  adnfzsc6buqa3, child number 0
-------------------------------------
select count(*) from t where v='XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'

Plan hash value: 2966233522

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |     708 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |     708 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |   110K |   100K |00:00:00.01 |     708 |
-------------------------------------------------------------------------------------

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

   2 - filter("V"='XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')

19 rows selected.

So, with this kind of difference between E-rows and A-rows, you can easily face performance problems, specially if this column has been indexed. So we have a frequency histogram and it doesn’t help us to determine the cardinality in the character column…

This behaviour has been reported in the Bug 18377553 : POOR CARDINALITY ESTIMATE WITH HISTOGRAMS AND VALUES > 32 BYTES.

In my case, the problem occurred with NVARCHAR2 columns (national character set was AL16UTF16), so when the column was containing more that 16 characters, performance problems occurred.

Hopefully, Oracle has published a one-off patch for this bug (available for 11.2.0.3 and 11.2.0.4 on linux x86-64), or as a workaround, you can remove histogram on these kind of columns (with the method_opt: FOR COLUMNS … SIZE 1)

In 12.1, the problem occurs only when the size of the character column exceeds 64 bits (32 characters under AL16UTF16 character set)


SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE	12.1.0.2.0	Production                                                        0
TNS for Linux: Version 12.1.0.2.0 - Production                                            0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

SQL> create table t (id number, v nvarchar2(64));

Table created.

SQL> begin
for i in 1..10000
loop
case mod(i,10)
  when 0 then insert into t values(mod(i,10), concat(lpad('X',32,'X'),'01'));
  when 1 then insert into t values(mod(i,10), concat(lpad('X',32,'X'),'02'));
  when 2 then insert into t values(mod(i,10), concat(lpad('X',32,'X'),'03'));
  when 3 then insert into t values(mod(i,10), concat(lpad('X',32,'X'),'04'));
  when 4 then insert into t values(mod(i,10), concat(lpad('X',32,'X'),'05'));
  when 5 then insert into t values(mod(i,10), concat(lpad('X',32,'X'),'06'));
  when 6 then insert into t values(mod(i,10), concat(lpad('X',32,'X'),'07'));
  when 7 then insert into t values(mod(i,10), concat(lpad('X',32,'X'),'08'));
  when 8 then insert into t values(mod(i,10), concat(lpad('X',32,'X'),'09'));
  when 9 then insert into t values(mod(i,10), concat(lpad('X',32,'X'),'10'));
end case;
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.

SQL> insert into t select 10,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' from dual connect by level <=100000; 100000 rows created. SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'T', method_opt=>'for all columns size 12', estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL> select table_name,column_name,density,histogram,sample_size from user_tab_col_statistics where table_name='T';

TABLE_NAME COLUMN_NAM    DENSITY HISTOGRAM       SAMPLE_SIZE
---------- ---------- ---------- --------------- -----------
T          ID         4.5455E-06 FREQUENCY            110000
T          V          4.5455E-06 FREQUENCY            110000

SQL> select table_name,column_name,
  2  endpoint_number,endpoint_value,
  3  endpoint_actual_value
  4  from user_histograms
  5  where table_name='T'
  6  order by COLUMN_NAME,ENDPOINT_NUMBER;

TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
---------- ---------- --------------- -------------- ----------------------------------
T          ID                    1000              0 0
T          ID                    2000              1 1
T          ID                    3000              2 2
T          ID                    4000              3 3
T          ID                    5000              4 4
T          ID                    6000              5 5
T          ID                    7000              6 6
T          ID                    8000              7 7
T          ID                    9000              8 8
T          ID                   10000              9 9
T          ID                  110000             10 10
T          V                   110000     1.7849E+33  X X X X X X X X X X X X X X X X X
                                                      X X X X X X X X X X X X X X X

12 rows selected.

Problem with 12.1 databases, the one-off patch has not been released yet and It will be fixed in 12.2.

Slides of my presentation at UKOUG TECH15 conference

The conference was last December, but I was a little bit busy this month …

So here are the slides of my presentation about SIMD extensions inside and outside Oracle 12c during the Super Sunday at UKOUG TECH15 conference.

It was my pleasure to have some great attendees in the audience: Frits Hoogland (ACE Director and Oak Table Member), Ludovico Caldara (Oracle Ace), Luca Canali (Oak Table Member), Mark Fielding (Oracle ACE), Bertrand Drouvot (Oracle Ace), Franck Pachot (Oracle Ace) and all the French guys who were at the conference!

Active Average Session trending in Statspack

Active Average Session (AAS) is one of the first statistic you have to look when you analyze a performance issue.

In Oracle Standard Edition (or Enterprise Edition without Diagnostic pack), you cannot query V$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY views, nor execute ashrpt script.

If you have installed statspack and if you take snapshots, you can find Active Average Session between 2 snapshots.

AAS is calculated with the formula : DB Time / elapsed time. So, if you have recorded a db time of 300 seconds in 900 seconds (elapsed time between two snapshots), AAS is 0,33.

So it’s easy to calculate AAS between two snapshot, but the most important thing is to have AAS trending during a period.

To do that, I wrote a small PL/SQL function based on SYS_TIME_MODEL_DIF function available in STATSPACK package definition (in spcpkg.sql script).

create or replace function SYS_TIME_MODEL_DIF(i_name varchar2, bid number, eid number, db_ident number, inst_num number) RETURN number is

/* Returns the difference between statistics for the Time Model statistic
   name specified for the interval between the begin and end
   snapshot id's (bid, eid)

   In the case the data being queried is from a prior release
   which did not have the statistic requested, this function
   returns 0.
*/

beg_val_missing   boolean := false;
end_val_missing   boolean := false;
bval           number;
eval           number;

cursor SY (i_snap_id number) is
select value
  from stats$sys_time_model      stm
     , stats$time_model_statname tms
where stm.snap_id         = i_snap_id
   and stm.dbid            = db_ident
   and stm.instance_number = inst_num
   and stm.stat_id         = tms.stat_id
   and tms.stat_name       = i_name;

begin
   /* Get start value */
   open SY (bid); fetch SY into bval;
   if SY%notfound then
      beg_val_missing := true;
   end if; close SY;

   /* Get end value */
   open SY (eid); fetch SY into eval;
   if SY%notfound then
      end_val_missing := true;
   end if; close SY;

   if     beg_val_missing = true
      and end_val_missing = true      then

         /* this is likely a newer statitic which did not exist for this database version    */
        return 0;

   elsif     beg_val_missing = true
         and end_val_missing = false  then
         return -1;
   elsif     beg_val_missing = false
         and end_val_missing = true   then
         return -1;
   else

        /* Return difference */
        return eval - bval;

   end if;

end SYS_TIME_MODEL_DIF;
/

When this function has been created, you can call it to get Db time (or any value in the time model) between two snapshots (identified by their snapshot_id : bid (begin id) and eid (end id)) , for example :

SQL&gt; select SYS_TIME_MODEL_DIF('DB time',791,792,1386309985,1) from dual;

SYS_TIME_MODEL_DIF('DBTIME',791,792,1386309985,1)
-------------------------------------------------
 10245630292

SQL&gt; select SYS_TIME_MODEL_DIF('DB CPU',791,792,1386309985,1) from dual;

SYS_TIME_MODEL_DIF('DBCPU',791,792,1386309985,1)
------------------------------------------------
 3698356113

SQL&gt; select SYS_TIME_MODEL_DIF('parse time elapsed',791,792,1386309985,1) from dual;

SYS_TIME_MODEL_DIF('PARSETIMEELAPSED',791,792,1386309985,1)
-----------------------------------------------------------
 211981805

Note that time is given in microseconds.

Next step, I wrote an anonymous PL/SQL script that executes this function accross all the snapshots taken between two snap_id and prints DB Time, DB CPU, and AAS in CSV format (you can adapt this script to adapt your output format):

declare
 -- Constantes
 usec_to_sec number := 1000000;
 day_to_sec number := 86400;
 db_id number := &dbid
 --
 dbtim number;
 dbcpu number;
 cursor SN_tim(sn_id number) is select snap_time from stats$snapshot where snap_id=sn_id;
 cursor snaps(sn1 number, sn2 number) is select snap_id from stats$snapshot where snap_id between sn1 and sn2 order by snap_id;
 b_time date;
 e_time date;
 bid number;
 eid number;
 cur_nf number;
begin
dbms_output.put_line('BEGIN_TIME;END_TIME;DBTIME;DBCPU;AAS');
open snaps(&start,&end);
loop
fetch snaps into bid;
exit when snaps%notfound;
 cur_nf := 0;
 fetch snaps into eid;

 open SN_tim(bid); fetch SN_tim into b_time;
 if SN_tim%notfound then
 cur_nf := 1;
 end if;
 close SN_tim;

 open SN_tim(eid); fetch SN_tim into e_time;
 if SN_tim%notfound then
 cur_nf := 1;
 end if;
 close SN_tim;

 dbtim := SYS_TIME_MODEL_DIF('DB time',bid,eid,db_id,1);
 dbcpu := SYS_TIME_MODEL_DIF('DB CPU',bid,eid,db_id,1);
 if ((dbtim&gt;0 or dbcpu&gt;0) and cur_nf = 0) then
 dbms_output.put_line(b_time||';'||e_time||';'||round(dbtim/usec_to_sec,2) ||';'||round(dbcpu/usec_to_sec,2) ||';'|| round((dbtim/usec_to_sec) / ((e_time-b_time)*day_to_sec),2) );
 else
 dbms_output.put_line(b_time||';'||e_time||';N/A;N/A' );
 end if;
end loop;
end;

Then you can graph it in Excel or Tableau (for example) and you will get the AAS trend:

aas

Keep in touch with My Oracle Support updates

This post is related to My Oracle Support (MOS) and one of the features every DBA has to configure is the Hot Topic email.

This feature will send you an email every day ou week (depending on how you configure it) with all the new stuff published or updated on MOS (Knowledge base article, bugs, alerts …).

Of course, you can filter on the products you want to subscribe and select the number of items you want to receive by category.

To configure this fonction, log in to MOS with your account and click on the “settings” tab, ,the select the “Hot Topic Email” link on the left … see in the screenshot below (green box):

hot_topics

Then, you will see the “hot topics” part and here you can configure it:

  • On the first part (1), you can configure the frequency of the mail and its format. You can also configure the content you want to include in this mail: favorites and Service requests
  • Next, on the second part (2), which is for me the most important part, you will select the products you want to subscribe and what kind of information you want to receive (knowledge articles, alerts, bugs etc.)
  • Finally, on the third part (3), you will choose the number of items the mail will includes. In the previous screenshot, I will receive 25 bugs (for all the products I selected), 25 knowledge articles etc. (Usually the bug and knowledge articles are the most important parts… yes, there are a lot of bugs updated every day ;) )

As a result, I receive this kind of email every day:

Alerts

Alert Product Area Last Updated
Bug 20830449 – Disk corruption possible when a disk media error occurs while synchronous I/O is performed Oracle Exadata Storage Server Software Fri, 26 Jun 2015 18:44 GMT+01:00

 

Bugs

Bug Product Area Bug ID Last Updated
ORACLE PROCESS RUNNING OUT OF OS KERNEL I/O RESOURCES EVEN AFTER APPLY 6343215 Oracle Database – Enterprise Edition 7523755 Sat, 27 Jun 2015 13:10 GMT+01:00
MESSAGES IN AQ$_ALERT_QT_E ARE NOT BEING PURGED IN A RAC CLUSTER Oracle Database – Enterprise Edition 19904389 Sat, 27 Jun 2015 13:10 GMT+01:00
ORA-01792 OCCURED WHEN USING RIGHT OUTER JOIN Oracle Database – Enterprise Edition 9256994 Sat, 27 Jun 2015 12:55 GMT+01:00
ORA-600 [KLAPRS_11]/[KLAPRS_12] BEING HIT DURING IMPDP Oracle Database – Enterprise Edition 6944948 Sat, 27 Jun 2015 12:54 GMT+01:00
ORA-00600 [KDTIGETROW-2] AFTER UPGRADE TO 11.2.0.3 BUNDLE 22 Oracle Database – Enterprise Edition 17343797 Sat, 27 Jun 2015 12:52 GMT+01:00
MERGE SQL WITH BOTH AN INSERT AND UPDATE AND LOGGING ENABLED CAN CAUSE INT. ERR Oracle Database – Enterprise Edition 17397545 Sat, 27 Jun 2015 12:52 GMT+01:00
WRONG PRECISION RETURNED FOR QUERY AFTER DB UPGRADE TO 12.1.0.2 Oracle Database – Enterprise Edition 21241579 Sat, 27 Jun 2015 12:49 GMT+01:00
NUMA MESSAGES IN THE ALERT LOG AFTER 11.2.0.4 UPGRADE Oracle Database – Enterprise Edition 17475024 Sat, 27 Jun 2015 12:49 GMT+01:00
ORA-600 [QKSVCREPLACEVC0] USING SQL TUNING ADVISOR Oracle Database – Enterprise Edition 17401718 Sat, 27 Jun 2015 12:45 GMT+01:00
ORA-600 [QKSVCREPLACEVC0] WHEN AUTOMATIC SQL TUNING ADVISOR EXECUTED Oracle Database – Enterprise Edition 16491690 Sat, 27 Jun 2015 12:45 GMT+01:00
ORA-00600 [QKSVCREPLACEVC0] Oracle Database – Enterprise Edition 13959984 Sat, 27 Jun 2015 12:45 GMT+01:00
INSTANCE CRASHES WITH ORA-600 [KJBRWRDONE:SC2] ON LMS Oracle Database – Enterprise Edition 17027916 Sat, 27 Jun 2015 12:35 GMT+01:00
WITH 11.2.0.4, THE CAPTURE MECHANISM STARTS UP TOO MANY PARALLEL QUERY SERVERS Oracle Database – Enterprise Edition 19587324 Sat, 27 Jun 2015 12:28 GMT+01:00
WAIT DEPENDENCY ON APPLY INSERT AFTER UPGRADE TO 11.2.0.4 Oracle Database – Enterprise Edition 19442102 Sat, 27 Jun 2015 12:28 GMT+01:00
FAILED TO RAISE ORA-1 FOR PK UPDATE WHEN CONSTRAINT=IMMEDIATE Oracle Database – Enterprise Edition 19440386 Sat, 27 Jun 2015 12:28 GMT+01:00
INTEGRATED REPLICAT INVALIDATES DEPENDENT PACKAGES RESULTING IN AN ORA-4068 Oracle Database – Enterprise Edition 19277336 Sat, 27 Jun 2015 12:28 GMT+01:00
PGA MEMORY LEAK ON APPLY SERVER ON KOH-KGHU CALL – KNGLXRCOL Oracle Database – Enterprise Edition 18973548 Sat, 27 Jun 2015 12:28 GMT+01:00
LOGMINER DDL TRACKING NOT SCALABLE, SESSIONS BLOCK EACHOTHER DURING DDL APPLY Oracle Database – Enterprise Edition 16674686 Sat, 27 Jun 2015 12:28 GMT+01:00
MALFORMED KTU-KRVMISC TRANSACTION FINALIZATION MARKER Oracle Database – Enterprise Edition 14705949 Sat, 27 Jun 2015 12:28 GMT+01:00
ORA 7445 [__INTEL_SSSE3_REP_MEMCPY] WITH CLUSTER TABLE CREATION Oracle Database – Enterprise Edition 21041573 Sat, 27 Jun 2015 12:27 GMT+01:00
ORA-600 [17285] RUNNING DIFFERENT APPLICATION PACKAGES Oracle Database – Enterprise Edition 19475971 Sat, 27 Jun 2015 12:09 GMT+01:00
WRONG RESULTS USING FUNCTION BASED INDEX WITH LOWER FUNCTION Oracle Database – Enterprise Edition 18550648 Sat, 27 Jun 2015 12:06 GMT+01:00
PGA MEMORY SPIKE WHEN CONSISTENTLY RUNNING A JAVA STORED PROCEDURE Oracle Database – Enterprise Edition 20806625 Sat, 27 Jun 2015 12:06 GMT+01:00
LOADJAVA FAILS WITH ORA-12154 WHEN USING NAME-VALUE PAIR IN CONNECTION STRING. Oracle Database – Enterprise Edition 21072270 Sat, 27 Jun 2015 12:05 GMT+01:00
ORA-600 [KSSADD: NULL PARENT] DURING HIGH DATABASE ACTIVITY Oracle Database – Enterprise Edition 16590736 Sat, 27 Jun 2015 11:50 GMT+01:00

 

Knowledge Articles

Knowledge Article Product Area Last Updated
Does Weblogic Server support SAML2.0 Single Logout Protocol? Oracle WebLogic Server Sat, 27 Jun 2015 02:52 GMT+01:00
How to use the DBMS_SQLPA (SQL Performance Analyzer) API to test database upgrade using CONVERT SQLSET Oracle Database – Enterprise Edition Sat, 27 Jun 2015 02:37 GMT+01:00
12c ACFS Configuration on ASM Flex Architecture (White Paper) Oracle Database – Enterprise Edition Fri, 26 Jun 2015 23:37 GMT+01:00
Best Practices and Recommendations for RAC databases using very large SGA (e.g. 100 GB) Oracle Database – Enterprise Edition Fri, 26 Jun 2015 23:24 GMT+01:00
Receive “There are Issues with the Configuration of The Static Files…” After Upgrading to APEX 5.0 Oracle Application Express (formerly HTML DB) Oracle REST Data Services Oracle HTTP Server Oracle Database – Enterprise Edition Fri, 26 Jun 2015 23:19 GMT+01:00
Cluster Health Monitor (CHM) FAQ Oracle Database – Enterprise Edition Fri, 26 Jun 2015 23:16 GMT+01:00
ODA: “oakcli show repo” Does Not Show Any Repository After Restart Oracle Database Appliance Software Oracle Database Appliance X3-2 Fri, 26 Jun 2015 22:34 GMT+01:00
NTP leap second event causing Oracle Clusterware node reboot Oracle Database – Enterprise Edition Fri, 26 Jun 2015 22:29 GMT+01:00
ODA (Oracle Database Appliance): Leap Second adjustment impact Oracle Database Appliance Oracle Database Appliance Software Fri, 26 Jun 2015 22:01 GMT+01:00
Bug 20830449 – Disk corruption possible when a disk media error occurs while synchronous I/O is performed Oracle Exadata Storage Server Software Fri, 26 Jun 2015 18:44 GMT+01:00
Exadata 12.1.2.1.2 release and patch (20748218) Oracle Exadata Storage Server Software Fri, 26 Jun 2015 18:40 GMT+01:00
11gR2 Grid Infrastructure Does not Use ulimit Setting Appropriately Oracle Database – Standard Edition Oracle Database – Enterprise Edition Fri, 26 Jun 2015 18:33 GMT+01:00
Exadata Database Machine and Exadata Storage Server Supported Versions Oracle Exadata Hardware Exadata Database Machine X2-2 Hardware Oracle Platinum Services Oracle Exadata Storage Server Software Oracle Database – Enterprise Edition Fri, 26 Jun 2015 18:32 GMT+01:00
Disable SSLv3 And Enable TLSv2 / TLSv1.1/ TLSv1.2 Oracle WebLogic Server Fri, 26 Jun 2015 18:30 GMT+01:00
ORA-7445 Troubleshooting Tool Oracle Database – Enterprise Edition Fri, 26 Jun 2015 17:13 GMT+01:00
ORA-600 Troubleshooting Tool Oracle Database – Enterprise Edition Fri, 26 Jun 2015 16:58 GMT+01:00
hp-ux: Database Instance Startup is Slow in 11gR2 as DBW0 Has High Wait “Disk file operations I/O” Oracle Database – Enterprise Edition Fri, 26 Jun 2015 16:49 GMT+01:00
ODA (Oracle Database Appliance): Unable to Delete VM Oracle Database Appliance Software Fri, 26 Jun 2015 16:49 GMT+01:00
WLS Admin Server Deadlock Detected Waiting To Acquire Lock Java.util.concurrent.CopyOnWriteArrayList Oracle WebLogic Server Fri, 26 Jun 2015 16:43 GMT+01:00
[WLS-10.3.6] -JAXB Marshaller Returns The Class Name Instead Of The Objects In The List Oracle WebLogic Server Fri, 26 Jun 2015 16:14 GMT+01:00
Oracle Advance Security Licence Components Oracle Database – Enterprise Edition Fri, 26 Jun 2015 16:11 GMT+01:00
Steps to Implement Address Windowing Extensions (AWE) / VLM on 32-bit Windows Platforms Oracle Database – Enterprise Edition Fri, 26 Jun 2015 15:50 GMT+01:00
DBMS_AUDIT_MGMT does not release the space occupied by LOB segment Oracle Database – Standard Edition Fri, 26 Jun 2015 15:47 GMT+01:00
WLS 10.3.6.0 Wtc-l10n.jar Missing Oracle WebLogic Server Fri, 26 Jun 2015 15:26 GMT+01:00
Egypt cancels DST in 2015 – Impact on Oracle RDBMS Oracle Database – Standard Edition Oracle Database – Enterprise Edition Fri, 26 Jun 2015 15:21 GMT+01:00

 

 

Oracle 12c Application Continuity and its resources usage

During a recent Oracle Meetup in Paris, I met Martin Bach and Bertrand Drouvot. Martin was here to present on Application Continuity, and we had an interesting discussion about this technology. (Bertrand had recently published an interesting post about Application Continuity in a Dataguard configuration … here).

In the post, I will explain quickly how it works based on an sample code I wrote few months ago. I will explained too application continuity resources allocation, where they are consumed, and how much is the resource overhead.

12c Application Continuity … What is it and how does it work?

Application Continuity is a technology that will improve application availability. Before Oracle 12c, Transparent Application Failover (TAF) or Fast Connection Failover (FCF) coud help you to improve application availability without downtime, but developers usually have to implement a TAF or FCF aware code. And usually, it’s wasn’t the case for many reasons (testing and UAT platform which weren’t RAC platform, specifications weren’t written in this aim etc.).

In 12c, Oracle releases application continuity. This technology will do the same thing with more simplicity and has been developed to handle planned and unplanned outages.

Application Continuity works on Oracle 12c database and with application that use:

  • Oracle Weblogic Server
  • Oracle UCP (Unified Connection Pool)
  • Third party JAVA application server which used the PooledConnection Interface
  • JDBC Thin-driver

The lab …

To test application continuity, I wrote a simple test case based on :

  • a 12c (12.1.0.2) RAC Cluster with a container database (CDB), a Pluggable Database (PDB), and a Database Schema (LAURENT) with on 2 tables and a “spy” trigger.
  • a small Java Code sample.

On the database side, I created a TEST table add fill it with 500000 rows, and another one (SPY_TABLE) which will track the id of the TEST table that have been modified :


SQL> create table test as select rownum id, dbms_random.string('U',20) v from dual connect by level <= 500000;

Table created.

SQL> create table spy_table(d date, inst_name varchar2(16), id number);

Table created.

Row updates tracking is made by a trigger on update on the TEST table:


SQL> create or replace trigger spy_test
 2  before update on test for each row
 3  declare
 4    pragma autonomous_transaction;
 5  begin
 6    insert into spy_table values(sysdate,sys_context('USERENV', 'INSTANCE_NAME'), :old.id);
 7  commit;
 8  end;
 9 /

Trigger created.

To make the test, we have to create a RAC service for our database which have these features:

  • FAILOVER_TYPE has to be set on TRANSACTION to enable Application Continuity
  • COMMIT_FAILOVER has to be set to TRUE (Even if it’s a parameter related to Transaction Guard, you have to set it)
  • REPLAY_INITIATION_TIMEOUT, FAILOVER_RETRIES, FAILOVER_DELAY should be reviewed to configure the delays for the replay (see this link for more information)
[oracle@rac1 JAVA]$ srvctl add service -d cdb -s app -preferred cdb1 -available cdb2 -pdb pdb -j SHORT -B SERVICE_TIME -z 30 -w 10 -commit_outcome TRUE -e TRANSACTION -replay_init_time 1800 -retention 86400 -notification TRUE
[oracle@rac1 JAVA]$ srvctl start service -s app -d cdb

[oracle@rac1 JAVA]$ srvctl status service -d cdb
Service app is running on instance(s) cdb1

[oracle@rac1 JAVA]$ /u01/app/12.1.0/grid/bin/crsctl stat res ora.cdb.app.svc -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cdb.app.svc
      1        ONLINE  ONLINE       rac1                     STABLE
--------------------------------------------------------------------------------

Note: I voluntarily configured the service with a preferred node to be sure It will be always the same I will use for my tests (related to resources). But I recommend you to configure it with all instances as “preferred”.

The Java source code is available at this link.

Procedures and functions are:

  • pressAnyKeyToContinue: it’s a private method that make a pause during the program. This pause can be useful to attach a JConsole or VisualVM to track JVM memory and CPU.
  • getStatValue: this function returns the value of an oracle statistics for the current session.
  • doTx: this is the most important function in this program. It will perform the transaction and it’s in this one you will find the most important thing when we code Java with Application Continuity: the callback. This callback is delimited by two calls: beginRequest and endRequest. In this callback, you will put all of the operation you want to protect against failures. If a planned or unplanned outage occurs during the callback, the transaction will be rolled back and then replayed. Please note that in the callback area, you have to:
    • call the prepareStatement function to assign the SQL statement, otherwise you will throw a SQLRecoverableException during the connection failover.
    • disable autoCommit for the connection.
    • put a commit statement to say where the transaction ends.
    private void doTx(Connection c, int numValue) throws SQLException {
        String updsql = "UPDATE test " +
                        "SET v=UPPER(v) " +
                        "WHERE id=?";
        PreparedStatement pstmt = null;

        /* Beginning of the callback
         * AutoCommit has to be disabled in the callback part.
         */
        ((oracle.jdbc.replay.ReplayableConnection)c).beginRequest();
        pstmt=c.prepareStatement(updsql);
        c.setAutoCommit(false);
        for (int i=0;i<numValue;i++) {
            pstmt.setInt(1,i);
            pstmt.executeUpdate();
        }
        c.commit();
        // End of the Callback.
        ((oracle.jdbc.replay.ReplayableConnection)c).endRequest();
        pstmt.close();
    }

  • In the main function, the most important thing is to use the new OracleDataSourceImpl class instead of OracleDataSource.

Then, I compile the code and run it with the number of updates I want to execute in my transaction.

First run

During the run, I execute a “kill -9” against the PMON process of the instance where my java program is connected to:

[oracle@rac1 JAVA]$ ./launchJava.sh SimpleAC 50000
Instance Name = cdb1
Press any key to continue...
Performing transactions

<<<< KILLED THE PMON PROCESS HERE >>>>

Instance Name = cdb2
CPU used by this session = 38643
session pga memory max = 4140696

We can see that, even with an instance failure which was running a transaction (of 50000 updates), the failover occurs without any error message.

The Spy table shows the results above:

SQL> select inst_name,count(*) from spy_table group by inst_name;

INST_NAME	   COUNT(*)
---------------- ----------
cdb2		      50000
cdb1		       1270

This proves that the transaction has been rolled-back and completely replayed after the connection failover.

Mutable values

When a transaction becomes replayable, a problem comes with what we call a mutable value. If you are interested by the definition you can read this on wikipedia: http://en.wikipedia.org/wiki/Immutable_object. But I prefer to explain this in a different way.

Imagine we do a close test to the previous one, but instead of updating data, we insert lines into a table and in this table, we have a timestamp column which takes the value of systimestamp oracle function when we insert a line. Now an outage occurs, the connection if failed over and the transaction is rolled back and replayed on the other node. If we don’t say to Oracle that this value is immutable, the value will be inserted at the timestamp recorded on the second node and not the original one and this could cause some problems in your data integrity.

This problem occurs with sysdate, systimestamp, sequence values and another function that depends on your process id (and other parameters) : SYS_GUID.

If you want to make your variables immutables, you need to have the KEEP grant:


SQL> show user
USER is "SYS"
SQL> grant keep date time to laurent;

Grant succeeded.

SQL> grant keep sysguid to laurent;

Grant succeeded.

SQL> connect laurent/laurent@rac-scan:1521/app
Connected.

SQL> grant keep sequence on seq1 to laurent;

Grant succeeded.

No pain, no gain … what about resources consumption?

Application continuity is a good thing because, with less code modification your java app will become completely fault tolerant. As the documentation mentions, there’s an overhead on CPU and memory … then, let’s check this !

To test this, I used the same environment than previously but I didn’t kill any process, I just ran it with and without callback definition.

Then I measured different things:

  • CPU used by the database session (statistic: CPU used by this session)
  • Memory consumed by the database session (statistic: session pga memory max)
  • CPU used by the java program
  • Memory consumed by the java program (Java heap memory usage)

CPU and memory consumed by the java program has been measured with this good old Jconsole.

To get the java heap memory usage, I set up the program with a heap size of 512Mb as it was the good value to avoid garbage collection during execution.

Here are the results:

  • With callback defined

[oracle@rac1 JAVA]$ egrep 'beginRequest|endRequest' SimpleAC.java
((oracle.jdbc.replay.ReplayableConnection)c).beginRequest();
((oracle.jdbc.replay.ReplayableConnection)c).endRequest();

[oracle@rac1 JAVA]$ ./compile.sh

[oracle@rac1 JAVA]$ ./launchJava.sh -Xms512M -Xmx512M SimpleAC 50000
Instance Name = cdb1
Press any key to continue...
Performing transactions
Instance Name = cdb1
CPU used by this session = 34859
session pga memory max = 3961144

load1_avecCallback

Note the heap memory usage high water mark is 110Mb and the average CPU is 0,4%

  • With no callback defined
[oracle@rac1 JAVA]$ egrep 'beginRequest|endRequest' SimpleAC.java
//((oracle.jdbc.replay.ReplayableConnection)c).beginRequest();
//((oracle.jdbc.replay.ReplayableConnection)c).endRequest();

[oracle@rac1 JAVA]$ ./compile.sh

[oracle@rac1 JAVA]$ ./launchJava.sh -Xms512M -Xmx512M SimpleAC 50000
Instance Name = cdb1
Press any key to continue...
Performing transactions
Instance Name = cdb1
CPU used by this session = 35138
session pga memory max = 3747576

load1_sansCallback

The Heap usage high water mark is 91Mb and the average CPU is 0,2%.

To sum up, we can tell that there is a memory overhead on the jdbc layer (for my example which was based on a transaction of 50000 updates). The CPU overhead announced by Oracle seems to be trivial. On the database side, no overhead has been found on CPU nor on the memory.

If you search more information about Application Continuity, you can read this paper by Oracle : http://www.oracle.com/technetwork/database/database-cloud/private/application-continuity-wp-12c-1966213.pdf

Follow

Get every new post delivered to your Inbox.

Join 357 other followers