Unusually high number of current mode reads for a GTT insert

From: VC <boston103_at_hotmail.com>
Date: Tue, 02 Mar 2004 21:58:28 GMT
Message-ID: <8s71c.104613$4o.121280@attbi_s52>

Please take a look at this (9.2.0.4/Solaris):

SQL> create table t11 as select * from all_objects;
SQL> create table t12 as select * from user_objects;
SQL> create table t13 as select object_id, object_name from user_objects

where 1=0;
SQL> create global temporary table t13_tmp as select object_id, object_name from user_objects where 1=0;

SQL> analyze table t11 estimate statistics;
SQL> analyze table t12 estimate statistics;
SQL>
SQL> alter session set events '10046 trace name context forever, level 8';
SQL> insert into t13 select t11.object_id, t12.object_name from t11,t12

where t11.object_id=t12.object_id;

220 rows created.

SQL> insert into t13_tmp select t11.object_id, t12.object_name from t11,t12 where t11.object_id=t12.object_id;

220 rows created.

SQL> exit

The tkprof output for both inserts:

insert into t13 select t11.object_id, t12.object_name from t11,t12 where   t11.object_id=t12.object_id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.03 0.02 0 47 21 220
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 2 0.04 0.04 0 47 21 220

Rows Row Source Operation
------- ---------------------------------------------------

    220 HASH JOIN (cr=46 r=0 w=0 time=26978 us)     221 TABLE ACCESS FULL T12 (cr=5 r=0 w=0 time=648 us)    3030 TABLE ACCESS FULL T11 (cr=41 r=0 w=0 time=7004 us)

---

insert into t13_tmp select t11.object_id, t12.object_name from t11,t12 where   t11.object_id=t12.object_id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.02 0.01 0 0 0 0
Execute 1 0.04 0.04 0 47 230 220
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 2 0.06 0.05 0 47 230 220

Rows Row Source Operation
------- ---------------------------------------------------

    220 HASH JOIN (cr=46 r=0 w=0 time=27218 us)     221 TABLE ACCESS FULL OBJ#(14255) (cr=5 r=0 w=0 time=673 us)    3030 TABLE ACCESS FULL OBJ#(14254) (cr=41 r=0 w=0 time=7291 us)

Please observe the 'current' column. In the case of an insert into the GTT, the number is 10 times higher than the same for an ordinary table (with the identical execution plan).
The same phenomenon happens with real production tables, i.e. the number of consistent reads is much higher with GTTs than with ordinary tables during inserts from a hash join.

In Oracle 8i, there is no such difference.

Thank you for any thoughts.

VC Received on Tue Mar 02 2004 - 15:58:28 CST