Re: BAD PERF. for single row access with unique index

You will note that almost all
of the CPU and elapsed time
for the MESSAGE access is
spent in parsing - Oracle comes
to the correct conclusion about
how to access the table, but
takes a long time getting there.

How many partitions in the table,
and how fast does the query operate
if you repeat it exactly ?

It is more helpful, particularly with
partitioned tables, to run the statement through EXPLAIN PLAN. You can use
utlxpls.sql script to generate an output (or the one on my website) to show the
partition selection details which are not visible in the output from tkprof.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

kerbiquet_at_hotmail.com wrote in message <8jcdkt$s71$1_at_nnrp1.deja.com>...


>Thank you very much for you help,

>

>For both statement Oracle is using the unique index, that looks to me

>the faster way to get back the result as there is a predicate on the 3

>columns of the UNIQUE INDEX. But It's so slow for MESSAGE! I rebuild

>all the index partitions, I got the same perf.

>

>I create an another table MESSAGE_TEST with the same structure,

>partitions, unique index and same data (insert into select from). I get

>much faster result on my test table. What could be the reason? Not

>index fragmentation, I done rebuild. Not table fragmentation I only

>access 1 single row. Any idea? Looks like Oracle is not looking at all

>the 3 columns of the unique index...

>

>Here are the trace for MESSAGE, MESSAGETXT and MESSAGE_TEST:

>************************************************************************

>********

>

>select count(1) from message

>WHERE source = 'A'

>AND day=to_date('16/05/2000','DD/MM/YYYY')

>AND number = 900

>

>call     count       cpu    elapsed       disk      query

>current        rows

>------- ------  -------- ---------- ---------- ---------- ----------

>----------

>Parse        1      1.39       1.37          0          0

>0           0

>Execute      1      0.00       0.00          0          0

>0           0

>Fetch        2      0.00       0.00          0          3

>0           1

>------- ------  -------- ---------- ---------- ---------- ----------

>----------

>total        4      1.39       1.37          0          3

>0           1

>

>Misses in library cache during parse: 1

>Optimizer goal: CHOOSE

>Parsing user id: 25  (OPS$ICB2G)

>

>Rows     Row Source Operation

>-------  ---------------------------------------------------

>      0  SORT AGGREGATE

>      1   INDEX UNIQUE SCAN (object id 2185)

>

>

>Rows     Execution Plan

>-------  ---------------------------------------------------

>      0  SELECT STATEMENT   GOAL: CHOOSE

>      0   SORT (AGGREGATE)

>      1    INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'IU_MESSAGE_1'

>(UNIQUE)

>

>

>

>

>

>************************************************************************

>********

>

>

>

>************************************************************************

>********

>

>select count(1) from message_test

>WHERE source = 'A'

>AND day=to_date('16/05/2000','DD/MM/YYYY')

>AND number = 900

>

>call     count       cpu    elapsed       disk      query

>current        rows

>------- ------  -------- ---------- ---------- ---------- ----------

>----------

>Parse        1      0.06       0.06          0          0

>0           0

>Execute      1      0.00       0.00          0          0

>0           0

>Fetch        2      0.00       0.00          0          3

>0           1

>------- ------  -------- ---------- ---------- ---------- ----------

>----------

>total        4      0.06       0.06          0          3

>0           1

>

>Misses in library cache during parse: 1

>Optimizer goal: CHOOSE

>Parsing user id: 25  (OPS$ICB2G)

>

>Rows     Row Source Operation

>-------  ---------------------------------------------------

>      0  SORT AGGREGATE

>      1   INDEX UNIQUE SCAN (object id 12195)

>

>

>Rows     Execution Plan

>-------  ---------------------------------------------------

>      0  SELECT STATEMENT   GOAL: CHOOSE

>      0   SORT (AGGREGATE)

>      1    INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'IU_MESSAGE_TEST_1'

>(UNIQUE)

>

>

>************************************************************************

>********

>

>************************************************************************

>********

>

>select count(1) from ops$caa.messagetxt

>WHERE source = 'A'

>AND day=to_date('16/05/2000','DD/MM/YYYY')

>AND number = 900

>

>call     count       cpu    elapsed       disk      query

>current        rows

>------- ------  -------- ---------- ---------- ---------- ----------

>----------

>Parse        1      0.11       0.15          0          0

>0           0

>Execute      1      0.00       0.00          0          0

>0           0

>Fetch        2      0.01       0.06          3          3

>0           1

>------- ------  -------- ---------- ---------- ---------- ----------

>----------

>total        4      0.12       0.21          3          3

>0           1

>

>Misses in library cache during parse: 1

>Optimizer goal: CHOOSE

>Parsing user id: 25  (OPS$ICB2G)

>

>Rows     Row Source Operation

>-------  ---------------------------------------------------

>      0  SORT AGGREGATE

>      5   INDEX RANGE SCAN (object id 2357)

>

>

>Rows     Execution Plan

>-------  ---------------------------------------------------

>      0  SELECT STATEMENT   GOAL: CHOOSE

>      0   SORT (AGGREGATE)

>      5    INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'IU_MESSAGETXT_1'

>               (UNIQUE)

>

>************************************************************************

>********

>

>

>Sent via Deja.com http://www.deja.com/

>Before you buy.