Re: re-indexing per statement or per row?

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Mon, 8 Apr 2002 11:31:57 +0100
Message-ID: <3cb1719e$0$225$ed9e5944@reading.news.pipex.net>

"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:AaFr8.180099$Yv2.61628_at_rwcrnsc54...

> 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.
>
> I would have to much around a bit, but I think I would do the following:
>
> Create a dummy table with one index.
> trace my session
> insert a couple of 1,000 rows using one statement insert into myTable...

> select...from dba_objects;
> use tkprof to look at the statistics
>
> do the same thing but with 1,000 sql statements (being lazy I would write
> sql to generate the sql and run that)
>
> I would think I could deduce something from that.

I ran this (with a ludicrously low sample size of 5 rows) as follows SQL> @m:\scripts\insert_test
SQL> drop table t1 cascade constraints;

Table dropped.

SQL> create table t1(id number,description varchar2(20)) 2 tablespace users;

Table created.

SQL> create index t_idx1 on t1(id) tablespace indx;

Index created.

SQL> alter session set sql_trace=true;

Session altered.

SQL> insert into t1 values(1,'single inserts');

1 row created.

SQL> insert into t1 values(2,'single inserts');

1 row created.

SQL> insert into t1 values(3,'single inserts');

1 row created.

SQL> insert into t1 values(4,'single inserts');

1 row created.

SQL> insert into t1 values(5,'single inserts');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t1 select object_id,object_type from all_objects where rownum < 6;

5 rows created.

SQL> commit;

Commit complete.

SQL> alter session set sql_trace=true;

Session altered.

SQL>
SQL> exit

all of which produced the following

TKPROF: Release 8.1.7.0.0 - Production on Mon Apr 8 10:55:57 2002

Trace file: ora01676.trc
Sort options: default

count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
****************************************************************************

alter session set sql_trace=true

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

Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 3 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 68

select u.name,o.name, t.update$, t.insert$, t.delete$, t.enabled from
obj$ o,user$ u,trigger$ t where t.baseobject=:1 and t.obj#=o.obj# and o.owner#=u.user# order by o.obj#

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

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 1 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 3 0.00 0.00 0 1 0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

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

      0  SORT ORDER BY
      0   NESTED LOOPS
      0    NESTED LOOPS
      0     TABLE ACCESS BY INDEX ROWID TRIGGER$
      0      INDEX RANGE SCAN (object id 130)
      0     TABLE ACCESS BY INDEX ROWID OBJ$
      0      INDEX UNIQUE SCAN (object id 36)
      0    TABLE ACCESS CLUSTER USER$
      0     INDEX UNIQUE SCAN (object id 11)

****************************************************************************

insert into t1
values
(1,'single inserts')

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

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 9 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 2 0.00 0.00 0 1 9 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 68

insert into t1
values
(2,'single inserts')

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

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 2 0.00 0.00 0 1 3 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 68

insert into t1
values
(3,'single inserts')

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

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 2 0.00 0.00 0 1 3 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 68

insert into t1
values
(4,'single inserts')

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

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 2 0.00 0.00 0 1 3 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 68

insert into t1
values
(5,'single inserts')

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

Parse 1 100.14 100.00 0 0 0

0
Execute 1 0.00 0.00 0 1 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 2 100.14 100.00 0 1 3 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 68

commit

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

Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 2 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 4 0.00 0.00 0 0 2 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 68

select text
from
view$ where rowid=:1

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

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 3 0.00 0.00 0 3 0 1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

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

1 TABLE ACCESS BY USER ROWID VIEW$

insert into t1 select object_id,object_type from all_objects where rownum < 6

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

Parse 1 200.29 200.00 0 0 0 0
Execute 1 100.14 100.00 0 52 7 5
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 2 300.43 300.00 0 52 7 5

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 68

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

      5  COUNT STOPKEY
      5   FILTER
      5    TABLE ACCESS BY INDEX ROWID OBJ$
      6     NESTED LOOPS
      1      TABLE ACCESS FULL USER$
      5      INDEX RANGE SCAN (object id 37)
      0    FIXED TABLE FULL X$KZSPR
      0    FIXED TABLE FULL X$KZSPR
      0    FIXED TABLE FULL X$KZSPR
      0    FIXED TABLE FULL X$KZSPR
      0    FIXED TABLE FULL X$KZSPR
      0    FIXED TABLE FULL X$KZSPR
      0    FIXED TABLE FULL X$KZSPR
      0    FIXED TABLE FULL X$KZSPR
      0    FIXED TABLE FULL X$KZSPR
      0    FIXED TABLE FULL X$KZSPR
      0    FIXED TABLE FULL X$KZSPR
      0    FIXED TABLE FULL X$KZSPR
      0    FIXED TABLE FULL X$KZSPR
      1    FIXED TABLE FULL X$KZSPR
      4    TABLE ACCESS BY INDEX ROWID OBJAUTH$
     24     NESTED LOOPS
     19      FIXED TABLE FULL X$KZSRO
      4      INDEX RANGE SCAN (object id 109)
      0    TABLE ACCESS BY INDEX ROWID IND$
      0     INDEX UNIQUE SCAN (object id 39)




****************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

Parse 9 300.43 300.00 0 0 0 0
Execute 10 100.14 100.00 0 57 30 10
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 19 400.57 400.00 0 57 30 10

Misses in library cache during parse: 6

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 6 0.00 0.00 0 4 0 1

Misses in library cache during parse: 0

10 user SQL statements in session.
2 internal SQL statements in session. 12 SQL statements in session.

Trace file: ora01676.trc
Trace file compatibility: 8.00.04
Sort options: default

       1  session in tracefile.
      10  user  SQL statements in trace file.
       2  internal SQL statements in trace file.
      12  SQL statements in trace file.
      10  unique SQL statements in trace file.
     133  lines in trace file.

Which doesn't appear to give me the index information easily. My guess is that there might be an event which one could set which gives this info directly - but as the events are undocumented....

--
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

******************************************

Received on Mon Apr 08 2002 - 05:31:57 CDT