Re: prefixed index - high clustering factor & high cpu

I've already answered your question about the CPU cost.

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

Do you know what the CPU cost represents, and how it measures it. If not, how can you decide that 30,000 is high. If you want some ideas about CPU costs, there's a comment in my book, and I think Christian Antognini (or maybe it was Wolfgang Breitling) has some details on their website.

Note in your execution plan, you are visiting ALL partitions.

If your partition hash is on (PG_TRANSC_ID,EAI_TRANSC_ID) and your query is:

SELECT * WHERE PG_TRANSC_ID=:B1 then you could have ('x','x') in one partition and ('x','y') in another.

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 ----- From: "Ujang Jaenudin" <ujang.jaenudin_at_gmail.com> To: <jonathan_at_jlcomp.demon.co.uk> Cc: <oracle-l_at_freelists.org> Sent: Sunday, October 28, 2007 6:29 AM Subject: 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_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
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.503 / Virus Database: 269.15.12/1096 - Release Date: 27/10/2007
> 11:02
>
>

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