Re: Truncating tables in RAC environment

On 03/15/2006 12:00:15 PM, rjamya wrote:
> I don't know your definition of long time to truncate. I don't see
> much of a problem in a two node RAC. BTW to those interested, KGopal
> mentioned on the list some time ago that truncating GTTs is a very
> very expensive operation in RAC, should be avoided.
>
> Raj
>

Truncating GTT used to be very expensive, period. In version 10.2 something has changed and truncating GTT is extremely quick. Here is an example that demonstrates it:

SQL> create global temporary table emp_temp   2 on commit preserve rows as select * from emp;

Table created.

SQL> insert into emp_temp
  2 select * from emp;

14 rows created.

SQL> insert into emp_temp
  2 select * from emp_temp;

28 rows created.

SQL> / 56 rows created.

SQL> / 112 rows created.

SQL> / 224 rows created.

SQL> /
.........
SQL> / 917504 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from emp_temp;

  COUNT(*)

   1835008

SQL>
SQL> set timing on
SQL> set autotrace on
SQL> truncate table emp_temp;

Table truncated.

Elapsed: 00:00:00.05

Not only does the thing happen extremely quickly (version 9.2 took over a minute for the same thing), it din't produce any trace. Repeated experioment with 10046 shows what happens:

SQL> alter session set events='10046 trace name context forever, level 12';

Session altered.

SQL> alter session set tracefile_identifier='GTT';

Session altered.

SQL> truncate table emp_temp;

Table truncated.

SQL> alter session set events='10046 trace name context off';

Session altered.

Trace shows the following:

BEGIN
  BEGIN
    IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner)) THEN       xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionary_obj_name);     END IF;
  EXCEPTION
    WHEN OTHERS THEN
     null;
  END;
  BEGIN
    IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner, xdb.DBMS_XDBZ.IS_ENABLED_RESMETADATA)) THEN       xdb.XDB_PITRIG_PKG.pitrig_dropmetadata(sys.dictionary_obj_owner, sys.dictionary_obj_name);     END IF;
  EXCEPTION
    WHEN OTHERS THEN
     null;
  END;
END; Basically, RDBMS is invoking non-documented objects from XDB and only queries from SYS owned objects. This very package figures prominently in all security advisories as well as on Pete Finnegan's site. In other words, Oracle started using XML for internal operations. Nobody, however, explains what that package is.

-- 
Mladen Gogala
http://www.mgogala.com

--
http://www.freelists.org/webpage/oracle-l