RE: Truncating tables in RAC environment

Someone had suggested in the list that using ..keep storage with truncate should reduce the time. I have tried it but it did not make any difference. In theory it should make some difference because some of the waits in the trace file were enqueue waits and when I translated the "P1" parameter from the raw trace file for the enqueue, it came out to be the HW enqueue, which means that Oracle had to spent some time to acquire this enqueue in order to reset the HWM.

-----Original Message-----
From: Mladen Gogala [mailto:gogala_at_sbcglobal.net] Sent: Wednesday, March 15, 2006 11:26 PM To: rjamya_at_gmail.com
Cc: Hameed, Amir; oracle-l_at_freelists.org Subject: 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 SQL> 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