Re: prefixed index - high clustering factor & high cpu
- From: "Ujang Jaenudin" <ujang.jaenudin@xxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx
- Date: Sun, 28 Oct 2007 13:29:39 +0700
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.
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
regards
ujang
--
//www.freelists.org/webpage/oracle-l
Other related posts:
