Re: prefixed index - high clustering factor & high cpu

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

Other related posts: