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

From: <kerbiquet_at_hotmail.com>
Date: 2000/07/03
Message-ID: <8jpp4n$8rm$1@nnrp1.deja.com>

Again, Thank you very much for your help,

I done those tests on the table MESSAGE (3 million rows, around 10 columns) for the simple statement (select single row using unique index in sqlplus):

144 partitions on the table, only on local index on the table (unique index). total time ~= parsing time ~= 0.06
144 partitions on the table, five local indexes on the table. After each index creation, I analyze the table (compute statistics) and I get a longer parsing time up to 1.44 for 5 indexes for the simple statement.
I drop some partitions, now there are only 90 partitions on the table and still the five local indexes. I analyze the table (compute statistics) and I get a lower but still too long parsing time =~ 0.50 for the simple statement.

Here is the plan with partition detail:

Query Plan

SELECT STATEMENT [CHOOSE] Cost=1
SORT AGGREGATE
INDEX UNIQUE SCAN IU_MESSAGE_1 [ANALYZED] Cost=1 Card=1 Bytes=28 Pt id: 2 Pt start: NUMBER(86) Pt stop: NUMBER(86)

Parsing time are similar if :
+ optimizer_mode=choose, there are compute statistics on the table and
indexes
+ optimizer_mode=rule, there are compute statistics on the table and
indexes
+ optimizer_mode=choose, there are NO statistics on the table and
indexes
+ using hint /*+ index(message iu_message_1) */
+ using hint /* ORDERED */

So it looks like long parsing time is not used by CBO to find best plan but to access the data dictionary.

Using utlestat.sql I check that GET_MISS = 0 (<< GET_REQS) for dictionary cache access and I do not have recursive calls when executing the statement:
SQL>alter system flush shared_pool
SQL>select * from mesfich
2 where sgtqs = 'AG32'
3 and drefmes=to_date('16/05/2000','DD/MM/YYYY') 4* and nuqmes=900

Statistics

        310  recursive calls
          1  db block gets
         75  consistent gets
         12  physical reads
          0  redo size
       1413  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed

Parsing time =0.75

I run the statement with a different value for the WHERE clause :

SQL>select * from mesfich
2 where sgtqs = 'AG32'
3 and drefmes=to_date('16/05/2000','DD/MM/YYYY') 4* and nuqmes=908

Statistics

          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
       1414  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Parsing time=0.45
I have no disk/query for parsing in tkprof report

call     count       cpu    elapsed       disk      query
current        rows

------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.38       0.44          0          0
0           0
Execute      1      0.00       0.01          0          0
0           0
Fetch        2      0.00       0.00          0          3
0           1

------- ------ -------- ---------- ---------- ---------- ----------

total        4      0.38       0.45          0          3
0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 19 (OPS$CAA)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   TABLE ACCESS   GOAL: ANALYZED (BY LOCAL INDEX ROWID) OF
              'MESFICH'
      0    INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'IU_MESFICH_1'

(UNIQUE) I run the same statement with the same value for the WHERE clause :

SQL>select * from mesfich
2 where sgtqs = 'AG32'
3 and drefmes=to_date('16/05/2000','DD/MM/YYYY') 4* and nuqmes=908

Statistics

          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
       1414  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Parsing time~=0
I have no disk/query for parsing in tkprof report

==> So I decrease the parsing time by dropping partitions to 0.50 for this simple statement but for more complex, parsing time is still > 1.00 and more than 95% of the execution time. An alternative would be to change application code in order to use bind variables to limit parsing count but this is costly.
By the way, I can not understand Oracle taking so long time to check the data dictionary for this table, 80 partitions, 5 indexes...

In article <962317124.8857.0.nnrp-10.9e984b29_at_news.demon.co.uk>, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

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

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Jul 03 2000 - 00:00:00 CDT