Re: Index Rang Scan - How to Improve?

From: Toon Koppelaars <toon.koppelaars_at_rulegen.com>
Date: Tue, 21 Jun 2011 05:26:42 +0200
Message-ID: <BANLkTi=6ZJ2bxwmFmOLxqXtsNYKvPP1K+A_at_mail.gmail.com>

Could you show us the tkprof-outputs of both scenarios: the full table scan and the index range scan. With wait events (so from a 10046 level 12 sqltrace).

On Tue, Jun 21, 2011 at 4:54 AM, Thiago Maciel <thiagomaciel_at_gmail.com>wrote:

> Folks, i need some help/advice. ETL/DW environment, Oracle 10.2.0.4, AIX
> 5.3; 28 cpus; 164gb ram.
>
> Its a simple query that is part of an ETL routine:
>
> SELECT blablabla ,
> *Y.COLUMN_B*,
> blablabla
> FROM *TABLE_X *PARTITION *(T1)* X,
> *TABLE_Y *Y
> WHERE X.COLUMN_A = 'N'
> AND NVL(to_number(TRIM(X.COLUMN_B)),1) = *Y.COLUMN_A*
> *
> *
> I need to run this query with *8 different sessions* at the same time (*each
> one* will choose a *different partition of the table_x*, like: session 1
> partition (t1), session 2 partition (t3),and so on). *Table_X* is *partitioned
> by hash *with *8 *partitions (*each partition* will vary between *1,5MM*
> and *3MM*) , and *table_y *has around *300MM*. There is an index on
> table_y with the following stats:
>
> INDEX_NAME : INDEX_02
> COLUMNS : 1 ASC *COLUMN_A
> *NOT NULL -- 299.094.710 NDV
> 2 ASC *COLUMN_B
> *NOT NULL -- PK of the table_y
> INDEX_TYPE : NORM
>
> DEGREE : 1
>
> BLEVEL : 3
>
> LEAF_BLOCKS : 1.416.590
>
> DISTINCT_KEYS : 295.327.520
>
> AVG_LEAF_BLOCKS_PER_KEY : 1
> AVG_DATA_BLOCKS_PER_KEY : 1
> CLUSTERING_FACTOR : 265.964.260
>
> BLOCKS_IN_TABLE : 1.289.581
>
> ROWS_IN_TABLE : 300.017.100
> SIZE : 11gb
>
> Tests showed to me that full scan on index_02 or on table_y is the best
> scenario regarding response time (even with lots of time spent on read by
> other session). When i force Oracle to choose index range scan on index_02
> the response time is higher than full scan, either: index or table.
>
> See that the selectivity is "only" between 0,5% - 1,1% (range scan between
> 1,5MM and 3,5MM), and Oracle does not need to touch the table_y.
>
> So my question is: how can i improve the access to the index via range
> scan? Or is there a better solution to this issue? For example, create the
> index with 32kb block size (It seems not so good idea:
> http://richardfoote.wordpress.com/category/index-block-size/)?
>
> Yes,yes i need to improve a lot more the response time of this query
> running with different sessions at the same time.
>
> ** response time of the disks are normals, most of the time between: 4ms
> and 16ms.
> ** Index and Tables in ASSM with 8kb block size (i know its a small block
> size to DW environment).
>
> If you need more information, please let me know it.
>
> Thanks in advance.
>
> Thiago Maciel
>

-- 
Toon Koppelaars
RuleGen BV
Toon.Koppelaars_at_RuleGen.com
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com

(co)Author: "Applied Mathematics for Database Professionals"
www.rulegen.com/am4dp-backcover-text

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

Received on Mon Jun 20 2011 - 22:26:42 CDT