Re: Creating indexes takes too long

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Tue, 18 Mar 2003 10:43:58 -0000
Message-ID: <3e76f870$0$4844$ed9e5944@reading.news.pipex.net>

I'd absolutely second howards advice, I ran a similar test to dan's on my laptop (9.2) with what is pretty much an out of the box database (eg sort area size is 512k). I got 2 seconds for the nologging index on a 30k row table and 1min 11s for the 1/ million row table. Now we could all guess what to do, which is effectively the approach of 'modify this parameter see what happens, then try the next one', or you could take advantage of the fact that Oracle will tell you (if you ask it nicely) where it is spending all its time.

To look at my machine (though I think 1 minute to index a 1/2 million row table on a 3 year old laptop is pretty good)

I did the following dbms_support.start_trace gives you much the same information for your session as statspack does for the db. comments of mine are prefixed ###

SQL> @?/rdbms/admin/dbmssupp

Package created.

Package body created.

SQL> conn niall/niall
Connected.
SQL> drop index ix_test2;

Index dropped.

SQL> select count(*) from test2;

COUNT(*)

500000

### here is my 1/2 million row table

SQL> exec dbms_support.start_trace();
BEGIN dbms_support.start_trace(); END;

*
ERROR at line 1:

ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SUPPORT.START_TRACE' must be declared
ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

### oops

SQL> exec sys.dbms_support.start_trace();

PL/SQL procedure successfully completed.

start tracing and collect wait information

SQL> create index ix_test2 on
2 test2(f1,f2,f3,f4,f5) nologging;

Index created.

SQL> exec sys.dbms_support.stop_trace();

PL/SQL procedure successfully completed.

### stop tracing.

now shell out the command line and run tkprof on the trace file generated in user_dump_dest (i just chose the most recent trace file)

I get

Trace file: nl9iwk_ora_840.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
****************************************************************************

create index ix_test2 on
test2(f1,f2,f3,f4,f5) nologging

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

Parse 1 0.01 0.03 0 1 0 0
Execute 1 14.84 58.80 5094 2165 1180 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 2 14.85 58.83 5094 2166 1180 0

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

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total

Waited

Waited ---------- ------------

  db file sequential read                         2        0.03
0.03
  db file scattered read                         60        0.00
0.10
  direct path write                               6        0.00
0.00
  direct path read                              278        0.00
0.01
  log file sync                                   2        0.00
0.00
  SQL*Net message to client                       1        0.00
0.00
  SQL*Net message from client                     1       18.08

18.08

so apart from waiting for the client session which took 18 seconds (thats me not doing anything) I primarily waited for disk access, but I hardly had to wait at all (13 centiseconds). If however I did have a problem it would show up as a large 'time waited'.

So by using statspack or tracing the session and collecting wait information you can see where you are spending your time.

--
Niall Litchfield
Oracle DBA
Audit Commission UK

"Hari Krishna Dara" <hari_newsgroups_at_yahoo.com> wrote in message
news:16dcd029.0303171255.2b24b7fc_at_posting.google.com...
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
news:<pan.2003.03.13.11.37.51.147380_at_yahoo.com.au>...
>
> > Oh dear. Look: Oracle isn't SQl Server, and attempts to make them do the
> > same thing are just doomed to disaster. Never mind that a 10-column
index,
> > representing about 20% of the largest tables you have, sounds like
> > distinctly dodgy design in the first place.
> >
> > But if you must go down this appalling road, then ask yourself what
Oracle
> > is doing that SQL Server isn't: generating redo, perhaps? Generating
undo?
> > Sorting in memory? Or sorting on disk?
> >
> > Try using the 'create index NOLOGGING' syntax to keep the redo to a
> > minimum. Look at your sort_area_size to make sure the sort needed to
> > populate the index isn't hitting disk.
> >
> > Start analyzing what waits are happening on the database during the
index
> > builds (welcome to Statspack).
> >
> > If you don't know how to run statspack, find out (tahiti.oracle.com).
But
> > if you don't know how to interpret statspack, then go to www.oraperf.com
> > and get it analyzed for free there.
> >
> > Regards
> > HJR
>
> Hello Howard,
>
> We are already using NOLOGGING syntax, but we haven't tuned the db
> parameters, so my colleague tried increasing the size of
> sort_area_size to 4mb, and it reduced the time by 1/3rd. I think he is
> going to find out what is the reasonable size for this parameter and
> also what other parameters are going to have an impact. Since none of
> us have done this earlier, we are going one step at a time. Thanks for
> your input, I appreciate it.
>
> Hari

Received on Tue Mar 18 2003 - 04:43:58 CST