Tuesday, October 22, 2013

Automated snapshot of any table (as well as GTT with delete rows)

Oracle in it's major two platforms (Oracle EBS and Oracle Retail) force massive usage of Global Temporary Tables. It has long been understood that materializing a subquery component can greatly improve SQL performance. Oracle global temporary tables are a great way to accomplish this. Those kind of tables are in most of cases defined with "on commit delete rows", which ensure that all rows are automatically delete on commit/rollback. But however this option ensure easy manipulation with GTT data, it has one big disadvantage-gathering statistic (after gather_stats GTT loose all content-implicit commit in package!).
12c note: Starting in 12c, Oracle will allows private optimizer statistics for global temporary tables, allowing you to invoke session-level dbms_stats to gather statistics specific to your own global temporary table. Prior to 12c, statistics were shared from a master copy of the CBO statistics. This means if you are lucky to gather statistic, internally, CBO statistics on a temporary table created by one session can also be used by other sessions. The problem arises when individual sessions create temporary tables with different characteristics to the level where it would change the execution plan. So we are stucked here. In 11g and backwards, capture data in autonomous transactions. We then move the data from the debug table back to the temporary table to generate Oracle statistics. From there we can import and export statistics to other databases as needed. Alter session set optimizer_dynamic_sampling=4; -> This is more time intensive and your idea would save quite a bit of time.
So basically, without any statistic you are left on your own and only god may help you when some careless developer place a huge amount of data in it and loop through it's content ... So this is why all around internet there are many articles where people admit that the are leaving this feature or move to collections. Must be said, collections are huge performance benefit but not good for uncontrolled processes (placed on some forms processes) because they spend huge amount of memory what may leave to other bigger problems. But If you are in controlled schedules or jobs, collections might be a better choice.
But for all other cases GTT are pretty irreplaceable and Oracle proves it.
So only solution to fight with GTT is to create table snapshots of GTT tables and later set those tables and execution plans. So this is why I decided to write a tool for this purpose.

The solution

As explained in mine older post, DDL in PL/SQL I use mine DDL procedure for controling DDL execution in PL/SQL packages. Here is another example of DDL use, for different purpose, of course ... for controlled dynamically table creation.
CREATE OR REPLACE FUNCTION create_snapshot_table (
/* $Header: create_snapshot_table.prc 1.00 10/11/2011 11:23 damirv $ */
/*--------------------------------------------------------------------------------------------------------------------
 Copyright(C) 2011-2013 Vadas savjetovanje d.o.o. 
 
 NAME    : CREATE_SNAPSHOT_TABLE
 PURPOSE : cretae table replica (empty table)

 Date    : 11.10.2011.
 Author  : Damir Vadas, damir.vadas@gmail.com

 Remarks : 10g ond onwards tested

 Changes (DD.MM.YYYY, Name, CR/TR#):
 -------------------------------------------------------------------------------------------------------------------- */
                                                  p_owner           VARCHAR2,
                                                  p_gtt_table       VARCHAR2,
                                                  p_gtt_snapshot    VARCHAR2,
                                                  p_tablespace      VARCHAR2 DEFAULT NULL)
  RETURN PLS_INTEGER
  AUTHID CURRENT_USER
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  l_i              PLS_INTEGER;
  l_stmt           VARCHAR2 (4000 CHAR);
  l_gtt_snapshot   VARCHAR2 (42 CHAR);
  l_date_ch        VARCHAR2 (18 CHAR);
BEGIN
  SELECT TO_CHAR (SYSDATE, 'yyyymmdd_hh24MISS') INTO l_date_ch FROM DUAL;

  CASE
    WHEN p_gtt_snapshot IS NULL THEN
      l_gtt_snapshot := p_gtt_table || '_' || l_date_ch;
    ELSE
      l_gtt_snapshot := p_gtt_snapshot;
  END CASE;

  CASE
    WHEN NOT p_gtt_snapshot IS NULL THEN
      l_stmt := 'SELECT ''x'' FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || p_owner || '.' || l_gtt_snapshot || ') ';
      ddl (l_stmt, l_i);

      CASE
        WHEN l_i = 1 THEN
          l_stmt := 'DROP TABLE ' || p_owner || '.' || l_gtt_snapshot || ' CASCADE CONSTRAINTS ';
          ddl (l_stmt, l_i);
        ELSE
          NULL;
      END CASE;
    ELSE
      NULL;
  END CASE;

  CASE
    WHEN p_tablespace IS NULL THEN
      l_stmt := 'CREATE TABLE ' || p_owner || '.' || l_gtt_snapshot || ' AS SELECT * FROM ' || p_owner || '.' || p_gtt_table;
    ELSE
      l_stmt := 'CREATE TABLE ' || p_owner || '.' || l_gtt_snapshot || ' TABLESPACE ' || p_tablespace || ' AS SELECT * FROM ' || p_owner || '.' || p_gtt_table;
  END CASE;

  DDL (l_stmt, l_i);
  RETURN l_i;
EXCEPTION
  WHEN OTHERS THEN
    RETURN SQLCODE;
END create_snapshot_table;
/
So, this procedure only create table according input parameters. And the best of all it is not only for GTT tables, but in mine case most of use for that types of table, so here comes it's name. Let me show an example of use of that procedure. First we have some table:
SQL> create table T1 (col1 varchar2(10), col2 integer);

Table created.
Fill it with data and show existance.
SQL> begin
  for i in 1 .. 10 LOOP
    insert into t1 values ('loop '|| to_char(i),i);
  end loop;
  commit;
end;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.


SQL> select table_name, tablespace_name from user_tables WHERE table_name like 'T1%';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T1                             TOOLS
To create snapshot of T1 table, initially similar table structure must exists. For that I use create_snapshot_table procedure. Here is an example:
SQL> set serveroutput on size 1000000;
SQL>
SQL> DECLARE
  2    l_i     PLS_INTEGER;
  3    l_msg   VARCHAR2 (128 CHAR);
  4  BEGIN
  5    l_i := create_snapshot_table (p_owner=>USER, p_gtt_table=> 'T1', p_gtt_snapshot=> 'T1_COPY');
  6    dbms_output.put_line(l_i);
  7  END;
  8  /
1

PL/SQL procedure successfully completed.
"1" means that DDL was executed succesfully. All other negative numbers means SQLCODE of error. So now we have T1 and structure replica of T1->T1_COPY. Here is an example of creating custom name replica in another tablespace:
SQL> DECLARE
  2    l_i     PLS_INTEGER;
  3    l_msg   VARCHAR2 (128 CHAR);
  4  BEGIN
  5    l_i := create_snapshot_table (p_owner=>USER, p_gtt_table=> 'T1', p_gtt_snapshot=> 'T1_COPY', p_tablespace=>'SQLTUNE');
  6    dbms_output.put_line(l_i);
  7  END;
  8  /
1

PL/SQL procedure successfully completed.
Here is an example of creating automated replica name in another tablespace:
SQL> DECLARE
  2    l_i     PLS_INTEGER;
  3    l_msg   VARCHAR2 (128 CHAR);
  4  BEGIN
  5    l_i := create_snapshot_table (p_owner=>USER, p_gtt_table=> 'T1', p_gtt_snapshot=> null, p_tablespace=>'SQLTUNE');
  6    dbms_output.put_line(l_i);
  7  END;
  8  /
1

PL/SQL procedure successfully completed.
And here is another example of fully automated replica name creation in the same tablespace as source table:
SQL> DECLARE
  2    l_i     PLS_INTEGER;
  3    l_msg   VARCHAR2 (128 CHAR);
  4  BEGIN
  5    l_i := create_snapshot_table (p_owner=>USER, p_gtt_table=> 'T1', p_gtt_snapshot=> null, p_tablespace=>null);
  6    dbms_output.put_line(l_i);
  7  END;
  8  /
1

PL/SQL procedure successfully completed.
Now let's take a look of all tables create in previous examples, with their tablespaces:
SQL> select table_name, tablespace_name from user_tables WHERE table_name like 'T1%';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T1                             TOOLS
T1_20130713_192406             SQLTUNE
T1_20130713_192530             TOOLS
T1_COPY                        SQLTUNE

SQL>
As you can see automated name has "sysdate" based value in name, so you can repeat creating this tables endlessly (until 1 sec difference is enough). This may be very helpful in trigger use.
If you implement mentioned logic in GT table after insert trigger, then you may fully automate data savings in GT tables, with filter you preserve. For that I use next procedure:
create or replace function generate_snapshot_trigger (
/* $Header: generate_snapshot_trigger.prc 1.00 10/12/2011 14:54 damirv $ */
/*--------------------------------------------------------------------------------------------------------------------
 Copyright(C) 2011-2013 Vadas savjetovanje d.o.o. 
 
 NAME : GENERATE_SNAPSHOT_TRIGGER
 PURPOSE : generate after insert trigger and snaphot table script
           this method save transactional data for any table (commited or not commited)
           best use with global temporary tables but no limitation on other tables as well!

 Date    : 12.10.2011.
 Author  : Damir Vadas, damir.vadas@gmail.com

 Remarks : 10g ond onwards tested
           p_table_owner        table owner of table we want to save data
           p_table_name         table whose data need to be saved
           p_trigger_disabled   when script is created, should trigger be defined as disabled (default) or not
           p_trigger_name       when null name is automatically created (best for automation)
                                table_owner.p_table_name||'AI$_XX'
           p_include_owner_in_tg trigger is created as "owner.trigger_name" instead of "trigger_name". 
                                have some implications and use of both ways
           p_snapshot_owner     which owner should have snaphot table
                                when null in the same schema as "p_table_owner"
           p_snapshot_table     snapshot table name
                                when null name is automatically created (best for automation)

 Changes (DD.MM.YYYY, Name, CR/TR#):
 -------------------------------------------------------------------------------------------------------------------- */
                                                      p_table_owner IN VARCHAR2,
                                                      p_table_name  IN VARCHAR2,
                                                      p_trigger_disabled IN VARCHAR2 default 'Y',
                                                      p_trigger_name IN VARCHAR2 default null,
                                                      p_include_owner_in_tg IN VARCHAR2 default 'N',
                                                      p_snapshot_owner IN VARCHAR2 DEFAULT null,
                                                      p_snapshot_table IN VARCHAR2 DEFAULT null
                           ) RETURN PLS_INTEGER 
IS
  stmt VARCHAR2(4000);
  
  l_trigger_name varchar2(256 CHAR);
  l_trigger_disabled varchar2(256 CHAR) ;
  l_include_owner_in_tg varchar2(256 CHAR) ;
   
  l_snapshot_owner varchar2(30 CHAR);
  l_snapshot_table varchar2(30 CHAR);
  
  TYPE t_col_table IS TABLE OF VARCHAR2 (30) INDEX BY BINARY_INTEGER;
  t_col_array  t_col_table;  
  l_coll VARCHAR2(2000) DEFAULT '(';
  l_col_data VARCHAR2(2000) DEFAULT '('; 
  l_retVal PLS_INTEGER DEFAULT -20999;   
  
  l_i PLS_INTEGER;
begin
  SELECT column_name BULK COLLECT INTO t_col_array FROM dba_tab_columns WHERE owner = p_table_owner AND table_name = p_table_name ORDER BY column_id;
  CASE WHEN t_col_array.COUNT=0 THEN
    stmt := null;
  ELSE
    FOR i IN 1..t_col_array.COUNT LOOP
      l_coll := l_coll || t_col_array(i) || ',' ;
      l_col_data:= l_col_data || 'p_data(i).' || t_col_array(i) || ',' ;
    END LOOP; 
    l_coll := substr (l_coll,1,length(l_coll)-1) || ')';
    l_col_data := substr (l_col_data,1,length(l_col_data)-1) || ')';
    CASE WHEN p_trigger_name is NULL THEN
      l_trigger_name :=  p_table_owner || '.' || p_table_name || '_AI$_XX';
    ELSE
      l_trigger_name := p_trigger_name;
    END CASE;
    
    CASE WHEN length(l_trigger_name)>30 THEN
      l_trigger_name := SUBSTR (l_trigger_name, -30, 30);
    ELSE
      null;
    END CASE;
    
    CASE WHEN p_include_owner_in_tg = 'N' THEN
        l_include_owner_in_tg :=  'ON '|| p_table_name || chr(10); 
      ELSE
        l_include_owner_in_tg :=  'ON '|| p_table_owner || '.' || p_table_name || chr(10); 
    END CASE;
    
    CASE WHEN p_trigger_disabled='Y' THEN
      l_trigger_disabled := 'DISABLE ' || chr(10);
    ELSE
      l_trigger_disabled := '' ;
    END CASE;
    
    CASE WHEN p_snapshot_owner IS NULL THEN
      l_snapshot_owner := p_table_owner;
    ELSE
      l_snapshot_owner := p_snapshot_owner;
    END CASE;
    
    CASE WHEN p_snapshot_table IS NULL THEN
      l_snapshot_table := p_table_name || '_sht';
    ELSE
      l_snapshot_table := p_snapshot_table;
    END CASE;   
    
    stmt := 'declare tmpVar   NUMBER;  '|| chr(10) ||
    'BEGIN '|| chr(10) ||
    '  DDL (''' || 'CREATE TABLE ' || l_snapshot_owner || '.' || l_snapshot_table || ' AS (SELECT * FROM ' || p_table_owner || '.' || p_table_name ||' WHERE 1=2)'', tmpVar);' || chr(10) ||
    'END;' || chr(10) ||
    '/'  || chr(10) ||
    chr(10) ||
    'CREATE OR REPLACE TRIGGER ' || l_trigger_name || chr(10) ||
    'AFTER INSERT ' || chr(10) ||
    l_include_owner_in_tg ||
    '   REFERENCING NEW AS NEW OLD AS OLD ' || chr(10) ||
    l_trigger_disabled ||
    'DECLARE ' || chr(10) ||
    '  tmpVar   NUMBER;  '|| chr(10) ||
    '      ' || chr(10) ||
    '  TYPE t_data_table IS TABLE OF ' || p_table_name || '%ROWTYPE INDEX BY BINARY_INTEGER;  ' || chr(10) ||
    '  t_data_array  t_data_table;  ' || chr(10) ||
    '  procedure save_it (p_data t_data_table)  ' || chr(10) ||
    '  IS  ' || chr(10) ||
    '    PRAGMA AUTONOMOUS_TRANSACTION;  ' || chr(10) ||     
    '  BEGIN  ' || chr(10) ||  
    '    FORALL i IN 1..p_data.COUNT  ' || chr(10) ||
    '      INSERT INTO ' || l_snapshot_owner || '.' || l_snapshot_table || chr(10) ||
    '        '|| l_coll || chr(10) ||
    '      VALUES ' || chr(10) ||
    '        ' || l_col_data || chr(10) ||
    '      ;' || chr(10) ||
    '    COMMIT;   ' || chr(10) ||
    '    dbms_output.put_line(''OK'');   ' || chr(10) ||
    '  EXCEPTION   ' || chr(10) ||
    '    WHEN OTHERS THEN   ' || chr(10) ||
    '      ROLLBACK;   ' || chr(10) ||
    '      dbms_output.put_line(SQLERRM);   ' || chr(10) ||
    '  END;   ' || chr(10) ||
    '' || chr(10) ||
    'BEGIN   ' || chr(10) ||
    '  SELECT COUNT(*) INTO tmpVar FROM ' || l_snapshot_owner || '.' || l_snapshot_table || '; ' || chr(10) ||
    '  CASE   ' || chr(10) ||
    '    WHEN tmpVar > 0 THEN   ' || chr(10) ||
    '      null;   ' || chr(10) ||
    '    ELSE   ' || chr(10) ||
    '      SELECT * BULK COLLECT INTO t_data_array FROM ' || p_table_owner ||'.'|| p_table_name || ';   ' || chr(10) ||
    '      save_it (t_data_array);   ' || chr(10) ||
    '  END CASE;   ' || chr(10) ||
    'EXCEPTION   ' || chr(10) ||
    '  WHEN OTHERS THEN   ' || chr(10) ||
    '    -- Consider logging the error and then re-raise  ' || chr(10) ||
    '    RAISE;   ' || chr(10) ||
    'END CUSTOMER_PRICES_GTT_AI$;   ' || chr(10) ||
    '/ '
    ;
  END CASE;
  dbms_output.put_line(stmt);
  return nvl(length(stmt),0);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
    return SQLCODE;  
end;
/
The beauty in this solution is that either snapshot DDL and trigger are created, so all can be automated out of the box. Here are some examples.
SET SERVEROUTPUT ON SIZE 1000000;
set define off
set verify off
set feedback off

DECLARE
  l_i   PLS_INTEGER;
BEGIN
  l_i:= generate_snapshot_trigger (p_table_owner                => 'DVADAS',
                                   p_table_name                 => 'Q2'
                                  );
END;
/
Result is:
declare tmpVar   NUMBER;  
BEGIN 
  DDL ('CREATE TABLE DVADAS.Q2_sht AS (SELECT * FROM DVADAS.Q2 WHERE 1=2)', tmpVar);
END;
/

CREATE OR REPLACE TRIGGER DVADAS.Q2_AI$_XX                                        
AFTER INSERT 
ON Q2                                                                             
   REFERENCING NEW AS NEW OLD AS OLD 
DISABLE                                                                           
DECLARE 
  tmpVar   NUMBER;  
      
  TYPE t_data_table IS TABLE OF Q2%ROWTYPE INDEX BY BINARY_INTEGER;  
  t_data_array  t_data_table;  
  procedure save_it (p_data t_data_table)  
  IS  
    PRAGMA AUTONOMOUS_TRANSACTION;  
  BEGIN  
    FORALL i IN 1..p_data.COUNT  
      INSERT INTO DVADAS.Q2_sht
        (NR,LOCATION)
      VALUES 
        (p_data(i).NR,p_data(i).LOCATION)
      ;
    COMMIT;   
    dbms_output.put_line('OK');   
  EXCEPTION   
    WHEN OTHERS THEN   
      ROLLBACK;   
      dbms_output.put_line(SQLERRM);   
  END;   

BEGIN   
  SELECT COUNT(*) INTO tmpVar FROM DVADAS.Q2_sht;                                
  CASE   
    WHEN tmpVar > 0 THEN   
      null;   
    ELSE   
      SELECT * BULK COLLECT INTO t_data_array FROM DVADAS.Q2;   
      save_it (t_data_array);   
  END CASE;   
EXCEPTION   
  WHEN OTHERS THEN   
    -- Consider logging the error and then re-raise  
    RAISE;   
END CUSTOMER_PRICES_GTT_AI$;   
/ 
Example 2:
SET SERVEROUTPUT ON SIZE 1000000;
set define off
set verify off
set feedback off

DECLARE
  l_i   PLS_INTEGER;
BEGIN
  l_i:= generate_snapshot_trigger (p_table_owner                => 'DVADAS',
                                  p_table_name                 => 'Q2',
                                  p_trigger_disabled           => 'N',
                                  p_trigger_name               => NULL,                                  
                                  p_include_owner_in_tg        => 'N',
                                  p_snapshot_owner             => 'DBA_XXX',
                                  p_snapshot_table             => 'XXX_DELETE_ME'
                                  );
END;
/
Result:
declare tmpVar   NUMBER;  
BEGIN 
  DDL ('CREATE TABLE DBA_XXX.XXX_DELETE_ME AS (SELECT * FROM DVADAS.Q2 WHERE 1=2)', tmpVar);      
END;
/

CREATE OR REPLACE TRIGGER DVADAS.Q2_AI$_XX
AFTER INSERT 
ON Q2
   REFERENCING NEW AS NEW OLD AS OLD 
DECLARE 
  tmpVar   NUMBER;  
      
  TYPE t_data_table IS TABLE OF Q2%ROWTYPE INDEX BY BINARY_INTEGER;  
  t_data_array  t_data_table;  
  procedure save_it (p_data t_data_table)  
  IS  
    PRAGMA AUTONOMOUS_TRANSACTION;  
  BEGIN  
    FORALL i IN 1..p_data.COUNT  
      INSERT INTO DBA_XXX.XXX_DELETE_ME                                       
        (NR,LOCATION)
      VALUES 
        (p_data(i).NR,p_data(i).LOCATION)
      ;
    COMMIT;   
    dbms_output.put_line('OK');   
  EXCEPTION   
    WHEN OTHERS THEN   
      ROLLBACK;   
      dbms_output.put_line(SQLERRM);   
  END;   

BEGIN   
  SELECT COUNT(*) INTO tmpVar FROM DBA_XXX.XXX_DELETE_ME;                     
  CASE   
    WHEN tmpVar > 0 THEN   
      null;   
    ELSE   
      SELECT * BULK COLLECT INTO t_data_array FROM DVADAS.Q2;   
      save_it (t_data_array);   
  END CASE;   
EXCEPTION   
  WHEN OTHERS THEN   
    -- Consider logging the error and then re-raise  
    RAISE;   
END CUSTOMER_PRICES_GTT_AI$;   
/ 
In both of previous examples i use:
SELECT COUNT(*) INTO tmpVar FROM  ...
WHEN tmpVar > 0 THEN   
because I want to catch only first insert part and no others. This was important for exam of execution plans in runtime-and this is what mine primary aim was. However, this filter may be adopted to your needs-so if removed all inserts will be saved..

The End

Has to emphasized that for GT tables only use of bulk collection (in the same session) may save data. But this functionality allow to use this solution as well in all other tables as well.
Hope this helps someone.

Cheers!

No comments :

Post a Comment

Zagreb u srcu!

Copyright © 2009-2014 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign