Performance

Hello everyone,

with 12c we have a very nice new option; Temp Undo for GTTs (Global Temporary Tables). as you know temporary tables are used for storing temporary data. those tables data are stored in TEMP tablespace so they are not vital. Temporary tables can generates very little redo data so they are faster than heap tables. if you experience an instance crash those data will be lost but as I said those data is not vital.

even we say gtts generates less redo, they still cause to generate redo because of UNDO! temporary table data does not need to recover after an instance crash but you might need to use a “rollback”. whenever you run a DML on a table Oracle will copy the original data to UNDO tablespace in case you need to rollback your operation and if you run a rollback, original data will be copied back to table from UNDO tablespace. By the way, that is why commit is so fast and rollback takes as much as the dml operation itself.

So, whenever you run a DML, oracle genrates 2 basic group of redo data. one for the change of table blocks itself and one for the change of undo blocks. GTTs does not generate (at least very little) redo data so they will have a better performance but your dml will cause original data to copy to UNDO and that will cause an UNDO block change and that will cause to redo log data. GTTs cause to generate redo log data indirectly. After your dml on the GTTs if you issue a rollback original data will be copied from undo to GTT.

With 12c, we have an option to choose where undo data to be written and we can choose to write undo data to temporary tablespace itself. Point is almost there won’t be any redo log data and that will make our GTTs much more faster. To achieve that you need to set a new database parameter called TEMP_UNDO_ENABLED. by default this parameter’s value is false and you can set in system or session.

Let’s demonstrate this but I would like to add a note before we go! if you use a GTT on a session, parameter’s value change won’t affect the result. before using the GTT you must set temp_undo_enabled parameter (or simply set in system level)

first to check the redo size I wrote a small package that will show us used redo size difference between the previous call of this package and current.

Create Or Replace Package Pkg_Get_Stats As
  X_Last_Redo_Size Number;
  
  Procedure Sp_Get_Redo_Size ;
End;
/

Create Or Replace Package Body Pkg_Get_Stats As
  
  Function Sf_Get_Stat_Value(P_Statname Varchar2) Return Varchar2 As
    X_Value Varchar2(4000);
  Begin
    Select Trunc(Value /1024, 4)
    Into   X_Value
    From   V$mystat M 
             Join V$statname S On (M.Statistic# = S.Statistic#) 
    Where Name Like P_Statname;
    
    Return X_Value;
  End;
  
  Procedure Sp_Get_Redo_Size  As
    X_Curr_Redo_Size Number;
  Begin
    X_Curr_Redo_Size := Trunc(To_Number(Sf_Get_Stat_Value('redo size')) /1024, 4);

    Dbms_Output.Put_Line(
                         'REDO DIFFERENCE: ' || 
                               To_Char(X_Curr_Redo_Size - Nvl(X_Last_Redo_Size,0) ,'fm999G999G999G999G999G999G999D9999') 
                        );
    X_Last_Redo_Size := X_Curr_Redo_Size;
  End Sp_Get_Redo_Size;
End;
/

Create Or Replace Package Pkg_Get_Stats As

X_Last_Redo_Size Number;

Procedure Sp_Get_Redo_Size ;

End;

Create Or Replace Package Body Pkg_Get_Stats As

Function Sf_Get_Stat_Value(P_Statname Varchar2) Return Varchar2 As

X_Value Varchar2(4000);

Begin

Select Trunc(Value /1024, 4)

Into X_Value

From V$mystat M

Join V$statname S On (M.Statistic# = S.Statistic#)

Where Name Like P_Statname;

Return X_Value;

End;

Procedure Sp_Get_Redo_Size As

X_Curr_Redo_Size Number;

Begin

X_Curr_Redo_Size := Trunc(To_Number(Sf_Get_Stat_Value('redo size')) /1024, 4);

Dbms_Output.Put_Line(

'REDO DIFFERENCE: ' ||

To_Char(X_Curr_Redo_Size - Nvl(X_Last_Redo_Size,0) ,'fm999G999G999G999G999G999G999D9999')

);

X_Last_Redo_Size := X_Curr_Redo_Size;

End Sp_Get_Redo_Size;

End;

whenever you call pkg_get_stats.sp_get_redo_size procedure, it will write the difference yo dbms_output.

so Let’s start with a new fresh sqlplus session (always use sqlplus for tests because UIs might do some extra jobs which can affect your results). My test case will show us three things.

1- How much redo generated for dml operations (including table create command )
2- How much temp space is being used by that session
3- How much time required to complete the dmls

session1 without using temp undo:

show parameter temp_undo_enabled;

--NAME                                 TYPE        VALUE
-------------------------------------- ----------- ------------------------------
--temp_undo_enabled                    boolean     <strong>FALSE</strong>

set serveroutput on;
var start_time varchar2(20);

drop table gtmp_x ;
create global temporary table gtmp_x (id number, mydata varchar2(1000)) on commit preserve rows;

exec :start_time := to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
exec pkg_get_stats.sp_get_redo_size;

insert into gtmp_X select level lvl, lpad('x', 1000, 'x') mydata from dual connect by level <= 500000;
commit;
update gtmp_x set id = id +1;
commit;
delete gtmp_x ;
commit;

select (sysdate-to_date(:start_time,'yyyy-mm-dd hh24:mi:ss'))*24*60*60 total_time from dual;
--TOTAL_TIME
------------
--        24

exec pkg_get_stats.sp_get_redo_size;
--REDO DIFFERENCE: 733.5287

select sum(blocks*8)/1024 from v$sort_usage where SESSION_ADDR = (select saddr from v$session where sid = userenv('SID'));
--SUM(BLOCKS*8)/1024
--------------------
--               559

truncate table gtmp_x;

show parameter temp_undo_enabled;

--NAME TYPE VALUE

-------------------------------------- ----------- ------------------------------

--temp_undo_enabled boolean <strong>FALSE</strong>

set serveroutput on;

var start_time varchar2(20);

drop table gtmp_x ;

create global temporary table gtmp_x (id number, mydata varchar2(1000)) on commit preserve rows;

exec :start_time := to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');

exec pkg_get_stats.sp_get_redo_size;

insert into gtmp_X select level lvl, lpad('x', 1000, 'x') mydata from dual connect by level <= 500000;

commit;

update gtmp_x set id = id +1;

commit;

delete gtmp_x ;

commit;

select (sysdate-to_date(:start_time,'yyyy-mm-dd hh24:mi:ss'))*24*60*60 total_time from dual;

--TOTAL_TIME

------------

-- 24

exec pkg_get_stats.sp_get_redo_size;

--REDO DIFFERENCE: 733.5287

select sum(blocks*8)/1024 from v$sort_usage where SESSION_ADDR = (select saddr from v$session where sid = userenv('SID'));

--SUM(BLOCKS*8)/1024

--------------------

-- 559

truncate table gtmp_x;

I suppressed dml results. as you can see job completed in 24 seconds, generated ~733 KB redo and used 559KB temp space. Total generated data is around 1292KB.

Now let’s run the same code after enabling temp undo:

session 2 with temp undo:

alter session set temp_undo_enabled=true;
show parameter temp_undo_enabled;

--NAME                                 TYPE        VALUE
-------------------------------------- ----------- ------------------------------
--temp_undo_enabled                    boolean     TRUE

set serveroutput on;
var start_time varchar2(20);

drop table gtmp_x ;
create global temporary table gtmp_x (id number, mydata varchar2(1000)) on commit preserve rows;

exec :start_time := to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');
exec pkg_get_stats.sp_get_redo_size;

insert into gtmp_X select level lvl, lpad('x', 1000, 'x') mydata from dual connect by level <= 500000;
commit;
update gtmp_x set id = id +1;
commit;
delete gtmp_x ;
commit;

select (sysdate-to_date(:start_time,'yyyy-mm-dd hh24:mi:ss'))*24*60*60 total_time from dual;
--TOTAL_TIME
------------
--        15

exec pkg_get_stats.sp_get_redo_size;
--REDO DIFFERENCE: 0.0012

select sum(blocks*8)/1024 from v$sort_usage where SESSION_ADDR = (select saddr from v$session where sid = userenv('SID'));
--SUM(BLOCKS*8)/1024
--------------------
--               1118

truncate table gtmp_x;

alter session set temp_undo_enabled=true;

show parameter temp_undo_enabled;

--NAME TYPE VALUE

-------------------------------------- ----------- ------------------------------

--temp_undo_enabled boolean TRUE

set serveroutput on;

var start_time varchar2(20);

drop table gtmp_x ;

create global temporary table gtmp_x (id number, mydata varchar2(1000)) on commit preserve rows;

exec :start_time := to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss');

exec pkg_get_stats.sp_get_redo_size;

insert into gtmp_X select level lvl, lpad('x', 1000, 'x') mydata from dual connect by level <= 500000;

commit;

update gtmp_x set id = id +1;

commit;

delete gtmp_x ;

commit;

select (sysdate-to_date(:start_time,'yyyy-mm-dd hh24:mi:ss'))*24*60*60 total_time from dual;

--TOTAL_TIME

------------

-- 15

exec pkg_get_stats.sp_get_redo_size;

--REDO DIFFERENCE: 0.0012

select sum(blocks*8)/1024 from v$sort_usage where SESSION_ADDR = (select saddr from v$session where sid = userenv('SID'));

--SUM(BLOCKS*8)/1024

--------------------

-- 1118

truncate table gtmp_x;

as you can see total run time decreased to 15 from 24 which is a huge gain for performance. almost no redo generated but of course temp usage increased even the total amount (1118KB) is less than previous (1292 KB).

PROS:
1- You will probably get faster execution time! I say probably because if your undo tablespace has much more faster disk structures than temp then you might experience a performance loss! on my system they have same disk structure and disk performance.
2- You will generate less redo which is so much important. Don’t think this is just a performance issue. if you have a data guard on a distance location, every redo log data will be shipped to that location over network. decreasing the generated redo size will decrease your network traffic also the amount of job on data guard db because unnecessary temp table insert/update/delete operations won’t be run. Again a performance gain for everything.
3- You will need less UNDO space since your GTTs won’t use it anymore.

CONS:

1- You will need more space on TEMPORARY tablespace because it will use for undo data of GTTs
2- your TEMPORARY tablespaces should be on fast disks so you can get the performance gain.

In conclusion, I can’t wait to see the active usage results and I will active this on a customer’s db these days. I will also share the information about active usage.

thanks to all.

Edit: I already used this new feature on one of my customer’s report and got very good results. Performance of the report increased around %10 and redo size increased significantly but I want to be cautious before set this parameter database level and I use it on session level for specific operations.

No need to follow standards.

Performance

Using Temp instead of Undo for GTTs