As you say interesting, results below on 9i. The 20,000 undo records for the
10000 statements makes sense (this is the same table with one index). The
figures for the single statements are harder to explain - though they
clearly show the performance advantage. Perhaps I'm just displaying my
ignorance here.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
SQL> set echo on
SQL> set timing on
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
PL/SQL Release 9.0.1.1.1 - Production
CORE 9.0.1.1.1 Production
TNS for 32-bit Windows: Version 9.0.1.1.0 - Production
NLSRTL Version 9.0.1.1.1 - Production
Elapsed: 00:00:00.01
SQL> drop table t1 cascade constraints;
Table dropped.
Elapsed: 00:00:00.00
SQL> create table t1(id number,description varchar2(50))
2 tablespace users;
Table created.
Elapsed: 00:00:00.00
SQL> create index t1_idx1 on t1(id) tablespace indx;
Index created.
Elapsed: 00:00:00.00
SQL>
SQL> BEGIN
2 FOR I IN 1..10000 LOOP
3 INSERT INTO T1 VALUES(I,'LOOP TEST');
4 END LOOP;
5 END;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.08
SQL>
SQL> select USED_UREC,LOG_IO,PHY_IO from v$transaction;
USED_UREC LOG_IO PHY_IO
---------- ---------- ----------
20000 73189 0
Elapsed: 00:00:00.00
SQL>
SQL> ROLLBACK;
Rollback complete.
Elapsed: 00:00:00.05
SQL>
SQL> INSERT INTO T1 SELECT OBJECT_ID,OBJECT_TYPE FROM ALL_OBJECTS
2 WHERE ROWNUM < 10001;
10000 rows created.
Elapsed: 00:00:02.06
SQL>
SQL> select USED_UREC,LOG_IO,PHY_IO from v$transaction;
USED_UREC LOG_IO PHY_IO
---------- ---------- ----------
7437 32513 0
Elapsed: 00:00:00.00
SQL>
SQL> rollback;
Rollback complete.
Elapsed: 00:00:00.02
SQL>
SQL> INSERT /*+ append */ INTO T1 SELECT OBJECT_ID,OBJECT_TYPE FROM
ALL_OBJECTS
2 WHERE ROWNUM < 10001;
10000 rows created.
Elapsed: 00:00:01.04
SQL>
SQL> select USED_UREC,LOG_IO,PHY_IO from v$transaction;
USED_UREC LOG_IO PHY_IO
---------- ---------- ----------
65 582 56
Elapsed: 00:00:00.00
SQL>
SQL> rollback;
Rollback complete.
Elapsed: 00:00:00.00
SQL>
SQL> spoo off
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:1018265083.3365.0.nnrp-12.9e984b29_at_news.demon.co.uk...
>
> Hint for anyone who's following this.
>
> Take a look at v$transaction.used_urec.
>
> Some of the numbers that appear in specific
> cases are very interesting.
>
>
> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
>
>
> Jim Kennedy wrote in message ...
> >Good thought for the day.
> >Hmmm...
> >On the direct path loads part I was going from memory on the
documentation,
> >but that aint't proof.
> >
>
>
>
Received on Mon Apr 08 2002 - 07:27:27 CDT
