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 THENbecause 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!
