Recently, do you have a temporary table in Oracle9i?
Does it bring you a performance improvement? Do you pay attention?
It is better to return. We know that the temporary table can reduce the generation of REDO in DML operations, so that the intermediate result table can bring greater performance. However, if you noticed, in Oracle9i, the temporary table may be more than normal Table also generate more Redo: [Oracle @ Jumper Oracle] $ SQLPLUS Eygle / Eyglesql * Plus: Release 9.2.0.3.0 - Production On Sat Jul 3 16:37:01 2004CopyRight (C) 1982, 2002, Oracle Corporation .? All rights reserved.Connected to: Oracle9i Enterprise Edition release 9.2.0.3.0 - Production ---- Please note that version With the Partitioning, OLAP and Oracle Data Mining optionsJServer release 9.2.0.3.0 - ProductionSQL> create table ccc1? (c1 number); Table created.sql> set autotrace trace stat SQL> INSERT INTO CCC1 Select Rownum from dba_objects; 10468 rows created.statistics ------------------- ------------------------------------- ???????? 73? Recursive calls? ?????? 175? Db block gets ????? 25623? Consistent gets ????????? 36? Physical reads ???? 157336? Redo size ---------- ---------- REDO generated by the regular table, about 154K ???????? 622? BYTES SENT VIA SQL * NET to Client ??????? 547? Bytes Received VIA SQL * NET From client ????????? 3? SQL * NET ROUNDTRIPS TO / FROM Client ????????? 2? Sorts (Memory) ?????????? 0? Sorts (Disk) ????? 10468? Rows ProcessedSQL> Create Global Temporary Table CCC2 (C1 Number); Table Created.SQL> Set Autotrace Trace Statsql> Insert INTO CCC2 SELECT ROWNUM FROM DBA_OBJECTS;
10468 rows created.statistics ---------------------------------------------------------------------------------------------------------------------------------------------------- ------------- ???????????10897? DB Block gets ????? 25653? Consistent gets ??????? 115? Physical Reads ??? 1476012? Redo size -------------------- This is a temporary table, producing approximately 1.4M REDO ?????? ? 622? BYTES SENT VIA SQL * NET To Client ???????? 547? BYTES Received Via SQL * NET from client ????????? 3? SQL * NET ROUNDTRIPS TO / FROM Client ??? ?????? 2? Sorts (memory) ????????? 0? Sorts (disk) ????? 10468? Rows Processed This makes the meaning of using a temporary table in these versions of Oracle9i Not big.? Today, DOWN has an Oracle9205 Patch, test in Oracle9205:? E: / Oracle / ORA92 / BIN> SQLPlus Eygle / Eygle SQL * Plus: Release 9.2.0.5.0 - Production On Saturday 7 Month 3 17:37:22 2004 Copyright (C) 1982, 2002, Oracle Corporation. All Rights Reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production --- Note Version with the partitioning, OLAP AND ORACLE DATA Mining Options JServer Release 9.2.0.5.0 - Production? SQL> CREATE TABLE CCC1 (C1 Number) TABLESPACE EYGLE; table has been created. SQL> SET Auto CCC1 STAT SQL> INSERT INTO CCC1 Select Rownum from DBA_Objects; 6188 rows have been created. Statistics --------------------------------------------------- ---------- 50 Recursive Calls 157 DB Block Gets 3416 Consistent Gets 0 Physical Reads 92764 Redo Size ------------------------- - General Table About 90K Redo 611 Bytes Sent Via Sql * Net To Client 547 BYtes Received Via SQL * Net from Clom Clom Clom Clom Client 3 SQL * Net RoundTrips To / from Clom Cliant 2 Sorts (Memory) 0 Sorts (Disk) 6188 Rows Processed SQL> DROP TABLE CCC1; Table has been discarded. SQL> CREATE GLOBAL TEMPORARY TABLE CCC2 (C1 Number); Table has been created. SQL> SET Auto CCC2 STAT SQL> Insert INTO CCC2 Select Rownum from DBA_Objects; 6188 rows have been created.
Statistics --------------------------------------------------- -------- 9 Recursive Calls 87 DB Block Gets 3402 Consistent Gets 0 Physical Reads 16844 Redo Size ------------------------- Temporary table, only 16K REDO 614 BYTES SENT VIA SQL * NET to Client 547 BYtes Received Via SQL * Net from Client 3 SQL * Net RoundTrips To / From Clom Client 2 Sorts (Memory) 0 Sorts (Disk) 6188 Rows Processed SQL> Oracle in 9205 revised this bug. If you have never encountered this bug, then you are lucky :)? In buglist, found the following instructions: 2874489: Excessive redo generated for Insert As Select Into Global Temporary Tables . Fixed: 9205? Temporary table finally came back !? Saturday, July 3, 2004 17:53 if you have any question, please mail to eye@itpub.net.?
