Performance Problem ?

[gtm]

Hi All,

I am in a trouble doing the performace tuning of a table(Oracle 8.1.7.0) having 200 Millions Records. The application which uses the table creates ad-hoc queries(queries which are not defined beforehand). This leads to create individual indexes on each of the fields (b*tree or bitmap) not composite as queries are not known. The range queries and aggregate queries are very slow and takes upto 9 minutes.

The same application is running on Sybase IQ Server 12 having a similar setup of indexes and gives the result back in 2-6 seconds. I tried to look read about the indexes in Sybase, Sybase has two other kind of indexes which are call HG (High Group ) and HNG(High Non Group). These indexes helps in doing aggregate and range queries and birngs the results back in seconds.


Does any body has an idea to how to go about this problem and suggest something to improve the performance ?.

I would really appreciate help from all of you .


Thanks and Regards,

gtm

[jmodic]

For your situation, I belive you should investigate partitioning and (above all) materialized views.

I don't belive creating separate non-composite indexes for all columns is very efficient way to deal with your situation, unless your ad-hoc queries always reference only one column of that table in a where condition. Remember that oracle can only use one index to access the table row, it can not combine two separate indexes in a data access path. At least you should try to run a tkprof over a trace of a typical session to find out which composite indexes will be most benefitial.

HTH,

[sambavan]

Analyze your tables and indices. When you analyze, choose the estimate statistics, instead of compute statistics. Also set the CURSOR_SHARING from exact to force. IF your optimizer is choose, I would suggest you to change it to FIRST_ROW. But you would want to leave it as CHOOSE if it were of DWH database.

You can also check the type of queries that had been run on your database from querying the V$SQLAREA. Then decide what to do from that point.

Good luck,
Sam

[gtm]

Please see one of the simple query and its explain plan below. Probably this would give an idea to suggest me something.


Thanks and Regards,

gtm

SELECT DISTINCT gender, count(*) FROM customer
WHERE zip BETWEEN '11700' AND '11799'
GROUP BY gender;


Time Taken : 7 min

G COUNT(*)
- ----------
F 499791
I 164926
M 499396


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4266 Card=3 Bytes=18
)

1 0 SORT (GROUP BY) (Cost=4266 Card=3 Bytes=18)
2 1 PARTITION RANGE (ALL)
3 2 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'CUSTOMER' (Cos
t=4171 Card=12435 Bytes=74610)

4 3 BITMAP CONVERSION (TO ROWIDS)
5 4 BITMAP INDEX (RANGE SCAN) OF 'ZIP_IDX'




Statistics
----------------------------------------------------------
23 recursive calls
0 db block gets
156562 consistent gets
129468 physical reads
0 redo size
519 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
3 rows processed

[Halo]

Just wondering:
Gender types of 'F' and 'M' are understood. What in the world is 'I'?? INTERMEDIATE?? hehe

[gtm]

yes, 'I' is something which is in between M and F



gtm

[LND]

b-tree index on (zip) or even (zip, gender) should help.
I mean, index on zip does range query. Distinct is then done by oracle, but as only 3 values exist, it should not be a problem.

Index on (zip, gender) could make sense if much more than 3 genders exists :-).

Partition pruning could help, but for this you need to change query.

[gpsingh]

I see two major things happening:

1) Partition range is all, that means it is accessing all partitions.

2) physical reads is very high, that is too much disk I/O.

TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'CUSTOMER' (Cos
t=4171 , cost is very high.

Drop the index on Zip and do full table access.Or try local non-prefixed index on zip.

Take Care
GP

[gtm]

infact i tried with b*tree index on zip and w/o the index as well, and its more slower. Currently the index on zip is a non-prefix local index. I have also tried with querying in its own partition.

The buffer hit ratio goes into -tive and increasing the buffer cache hasn't helped.

What should i try now ?.

thanks

gtm

[gpsingh]

Write in the table, partition and index details.