Re: Truncating tables in RAC environment
- From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
- To: rjamya@xxxxxxxxx
- Date: Wed, 15 Mar 2006 23:26:10 -0500
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
