Re: prefixed index - high clustering factor & high cpu

From: Ujang Jaenudin <ujang.jaenudin_at_gmail.com>
Date: Sun, 28 Oct 2007 13:29:39 +0700
Message-ID: <3edcb66e0710272329k1d307776wb9a6d8e372c49360@mail.gmail.com>

jonathan,

thanks for the reply, here I paste a piece of trace 10053 (SELECT * WHERE PG_TRANSC_ID=:B1).
the table now partitioned with hash(PG_TRANSC_ID,EAI_TRANSC_ID) the interesting part is resc_io: 9.00 resc_cpu: 68233 now the index is on PG_TRANSC_ID and EAI_TRANSC_ID which is on local partiton.

even when do SELECT * WHERE PG_TRANSC_ID=:B1 and EAI_TRANSC_ID=:A1 the cpu cost still high it is around 27k (with table partitoned) and 30k (without table partition).

is there any clue why the cpu resource was taken so much??

regards
ujang
jakarta - indonesia

BASE STATISTICAL INFORMATION

Table Stats::
  Table: C_PGI_TRANSACTION Alias: C_PGI_TRANSACTION (Using composite stats)     #Rows: 2412099 #Blks: 80065 AvgRowLen: 167.00 Index Stats::
  Index: C_PGI_TRANSACTION_01_IDX Col#: 3     USING COMPOSITE STATS
    LVLS: 2 #LB: 10880 #DK: 557567 LB/K: 1.00 DB/K: 1.00 CLUF: 2380167.00   Index: C_PGI_TRANSACTION_04_IDX Col#: 7     USING COMPOSITE STATS
    LVLS: 2 #LB: 6671 #DK: 4 LB/K: 362.00 DB/K: 8045.00 CLUF: 148625.00   Index: C_PGI_TRANSACTION_05_IDX Col#: 14     USING COMPOSITE STATS
    LVLS: 2 #LB: 6360 #DK: 4 LB/K: 394.00 DB/K: 7798.00 CLUF: 125659.00   Index: C_PGI_TRANSACTION_06_IDX Col#: 1 2     USING COMPOSITE STATS
    LVLS: 2 #LB: 18423 #DK: 2411238 LB/K: 1.00 DB/K: 1.00 CLUF: 2372704.00   Index: C_PGI_TRANSACTION_07_IDX Col#: 2     USING COMPOSITE STATS
    LVLS: 2 #LB: 9464 #DK: 181488 LB/K: 1.00 DB/K: 1.00 CLUF: 798329.00   Index: C_PGI_TRANSACTION_08_IDX Col#: 15     USING COMPOSITE STATS
    LVLS: 2 #LB: 5999 #DK: 5 LB/K: 261.00 DB/K: 4726.00 CLUF: 108328.00   Index: C_PGI_TRANSACTION_09_IDX Col#: 18     USING COMPOSITE STATS
    LVLS: 2 #LB: 20737 #DK: 602932 LB/K: 1.00 DB/K: 1.00 CLUF: 2411711.00   Index: C_PGI_TRANSACTION_10_IDX Col#: 17     USING COMPOSITE STATS
    LVLS: 2 #LB: 6447 #DK: 7 LB/K: 200.00 DB/K: 4839.00 CLUF: 155841.00

SINGLE TABLE ACCESS PATH
  Column (#1): PG_TRANSC_ID(VARCHAR2)
    AvgLen: 21.00 NDV: 421912 Nulls: 0 Density: 2.3702e-06   Table: C_PGI_TRANSACTION Alias: C_PGI_TRANSACTION     Card: Original: 2412099 Rounded: 6 Computed: 5.72 Non Adjusted: 5.72   Access Path: TableScan
    Cost: 17908.90 Resp: 17908.90 Degree: 0

      Cost_io: 17516.00  Cost_cpu: 1052599934
      Resp_io: 17516.00  Resp_cpu: 1052599934
  Access Path: index (skip-scan)

    SS sel: 2.3702e-06 ANDV (#skips): 6     SS io: 5.72 vs. index scan io: 1.00
    Skip Scan rejected
  Access Path: index (RangeScan)
    Index: C_PGI_TRANSACTION_06_IDX
    resc_io: 9.00 resc_cpu: 68233
    ix_sel: 2.3702e-06 ix_sel_with_filters: 2.3702e-06     Cost: 9.03 Resp: 9.03 Degree: 1
  Best:: AccessPath: IndexRange Index: C_PGI_TRANSACTION_06_IDX

Cost: 9.03 Degree: 1 Resp: 9.03 Card: 5.72 Bytes: 0

----------------------------------------------------------------------+-----------------------------------+---------------+


| Id  | Operation                           | Name

| Rows  | Bytes | Cost  | Time      | Pstart| Pstop |

----------------------------------------------------------------------+-----------------------------------+---------------+


| 0   | SELECT STATEMENT                    |

|       |       |     9 |           |       |       |

| 1   |  PARTITION HASH ALL                 |

|     6 |   906 |     9 |  00:00:01 | 1     | 5     |

| 2   |   TABLE ACCESS BY LOCAL INDEX ROWID | C_PGI_TRANSACTION

|     6 |   906 |     9 |  00:00:01 | 1     | 5     |

| 3   |    INDEX RANGE SCAN                 |

C_PGI_TRANSACTION_06_IDX|     6 |       |     3 |  00:00:01 | 1     |
5     |
----------------------------------------------------------------------+-----------------------------------+---------------+

Content of other_xml column

db_version : 10.2.0.3

On 10/27/07, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
> Please supply the tkprof output you are using to get these figures.
>
> Is your "cost about 30k" from the Rowsource Operation output,
> showing "time = 30000 microseconds" ?  Or is it actually from
> a plan_table.
>
> If the latter, then a CPU cost of around 30,000 (Oracle operations)
> is perfectly reasonable for 4 buffer visits to acquire a single row by
> unique index.
>
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentals
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> ----- Original Message -----
> > Subject: RE: prefixed index - high clustering factor & high cpu
>
> >at the tkprof the cpu cost is very high , its about 30k, rows returned
> > is 1 , io cost is 4 , the cost itself only 4.
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
regards
ujang
--
http://www.freelists.org/webpage/oracle-l

Received on Sun Oct 28 2007 - 01:29:39 CDT