Performance Problem ?

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

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,

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

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

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

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



gtm

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.

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

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

Write in the table, partition and index details.

Currently for testing purpose the indexes only exist on
zip, age, gender, income_cd, regions, income and state and all of them are bitmap indexes.


CREATE TABLE customer(
regions NUMBER(2),
zip CHAR(5),
zip4 CHAR(5),
cc_bank CHAR(1),
cc_dept CHAR(1),
loan_ind CHAR(1),
cc_prem CHAR(1),
cc_upscl CHAR(1),
ethnic CHAR(1),
prefix CHAR(1),
f_name CHAR(13),
m_init CHAR(1),
l_name CHAR(16),
apart_ind CHAR(1),
pobox_ind CHAR(1),
urban CHAR(28),
filler CHAR(1),
city CHAR(20),
state CHAR(2),
income NUMBER(4),
income_cd CHAR(1),
single CHAR(1),
avg_prm_dt CHAR(6),
avg_mtch CHAR(2),
don_plan1 CHAR(1),
don_plan2 CHAR(1),
don_maj1 CHAR(1),
don_maj2 CHAR(1),
don_lklyhd CHAR(2),
don_dol_cd CHAR(1),
student CHAR(1),
student_ln CHAR(1),
avg_st_hcr CHAR(8),
dpbc CHAR(3),
hhld_id CHAR(5),
indiv_id CHAR(5),
occupation CHAR(4),
loan_type CHAR(1),
mfdu CHAR(1),
vendor_tag CHAR(1),
mve_rng CHAR(1),
head_hhld CHAR(1),
gender CHAR(1),
driver CHAR(1),
crrt CHAR(5),
mob CHAR(1),
mortgage1 CHAR(1),
mortgage2 CHAR(1),
auto_loan1 CHAR(1),
auto_loan2 CHAR(1),
age NUMBER(3),
age_ind CHAR(1),
dob_year CHAR(4),
dob_mon CHAR(2),
avg_age CHAR(3),
child_ind CHAR(1),
child1 CHAR(1),
child2 CHAR(1),
child3 CHAR(1),
child_num NUMBER(1),
l_sec_crd NUMBER(5),
l_fin_crd NUMBER(4),
geo_state CHAR(2),
geo_county CHAR(3),
geo_census CHAR(6),
geo_bl_grp CHAR(1),
smac CHAR(1),
msa CHAR(4),
adi CHAR(3),
dma CHAR(3),
curadd_dt CHAR(6),
lor NUMBER(2),
date_rept CHAR(6),
apart_no CHAR(5),
listid CHAR(12),
sum_old_tr NUMBER(4,1),
sum_pb_no NUMBER(4,1),
sum_bc_tr NUMBER(4,1),
sum_pb_bal NUMBER(7,1),
sum_bc_bal NUMBER(7,1),
sum_pf_tr NUMBER(4,1),
sum_pf_bal NUMBER(7,1),
sum_mt_tr NUMBER(4,1),
sum_mt_bal NUMBER(7,1),
sum_rt_bal NUMBER(7,1),
sum_ur_bal NUMBER(7,1),
a_auto_dt CHAR(6),
a_auto_hc CHAR(8),
a_mort_dt CHAR(6),
a_mort_hc CHAR(8),
a_home_val CHAR(10),
a_home_eqt CHAR(10),
hme_value CHAR(10),
hme_val_cd CHAR(1),
hme_owner CHAR(1),
refin_mdl NUMBER(5),
fin_306090 CHAR(1),
affinity CHAR(1),
kob1 CHAR(1),
kob2 CHAR(1),
kob3 CHAR(1),
kob4 CHAR(1),
kob5 CHAR(1),
kob6 CHAR(1),
kob7 CHAR(1),
ext_howner CHAR(1),
condo CHAR(1),
mort_amt NUMBER(6),
mort_opdt CHAR(6),
rec_freq CHAR(1),
rec_w30 CHAR(1),
rec_w60 CHAR(1),
rec_w90 CHAR(1),
rec_w120 CHAR(1),
rec_w150 CHAR(1),
rec_w180 CHAR(1),
solo CHAR(3),
hme_own_fl CHAR(1),
direct_buy CHAR(1),
hhld_zip4 CHAR(4),
ext_hownr2 CHAR(1),
mve_add_dt CHAR(6),
mve_eff_dt CHAR(6),
inst_addr CHAR(1),
executive CHAR(1),
phone_ind CHAR(1),
title CHAR(6),
seq_num CHAR(9),
panel CHAR(3),
pm_box CHAR(12),
list_num_b CHAR(3),
address CHAR(30),
filler2 CHAR(3),
zip_act_cd CHAR(1),
coa_index CHAR(1),
add_type CHAR(1),
ncoa_nixie CHAR(1),
sec_add CHAR(12),
aci_hh_id CHAR(15)) NOLOGGING
PARTITION BY RANGE(regions)
(PARTITION P1 VALUES LESS THAN (2) TABLESPACE P1
STORAGE(INITIAL 1024M NEXT 1024M MINEXTENTS 16
MAXEXTENTS UNLIMITED PCTINCREASE 0)
PCTFREE 5 PCTUSED 90,
PARTITION P2 VALUES LESS THAN (3) TABLESPACE P2
STORAGE(INITIAL 2047M NEXT 2047M MINEXTENTS 5
MAXEXTENTS UNLIMITED PCTINCREASE 0)
PCTFREE 5 PCTUSED 90,
PARTITION P3 VALUES LESS THAN (4) TABLESPACE P3
STORAGE(INITIAL 1023M NEXT 1023M MINEXTENTS 10
MAXEXTENTS UNLIMITED PCTINCREASE 0)
PCTFREE 5 PCTUSED 90,
PARTITION P4 VALUES LESS THAN (5) TABLESPACE P4
STORAGE(INITIAL 1023M NEXT 1023M MINEXTENTS 8
MAXEXTENTS UNLIMITED PCTINCREASE 0)
PCTFREE 5 PCTUSED 90,
PARTITION P5 VALUES LESS THAN (6) TABLESPACE P5
STORAGE(INITIAL 2047M NEXT 2047M MINEXTENTS 7
MAXEXTENTS UNLIMITED PCTINCREASE 0)
PCTFREE 5 PCTUSED 90,
PARTITION P6 VALUES LESS THAN (7) TABLESPACE P6
STORAGE(INITIAL 1023M NEXT 1023M MINEXTENTS 13
MAXEXTENTS UNLIMITED PCTINCREASE 0)
PCTFREE 5 PCTUSED 90,
PARTITION P7 VALUES LESS THAN (8) TABLESPACE P7
STORAGE(INITIAL 1023M NEXT 1023M MINEXTENTS 7
MAXEXTENTS UNLIMITED PCTINCREASE 0)
PCTFREE 5 PCTUSED 90,
PARTITION P8 VALUES LESS THAN (9) TABLESPACE P8
STORAGE(INITIAL 2047M NEXT 2047M MINEXTENTS 6
MAXEXTENTS UNLIMITED PCTINCREASE 0)
PCTFREE 5 PCTUSED 90,
PARTITION P9 VALUES LESS THAN (MAXVALUE)
TABLESPACE P9
STORAGE(INITIAL 2047M NEXT 2047M MINEXTENTS 6
MAXEXTENTS UNLIMITED PCTINCREASE 0)
PCTFREE 5 PCTUSED 90);

The only possible solution for this i can think of right now is:

Create a non-prefixed local index partitioned on zip , and use parallel clause , for this u have to enable parallel query parameters and
bitmap_merge_area_size (increase from 1M to equal ro sort_area_size).
and db_file_multiblock_read_count , increase it to 32.

Take care
GP

Your help is really wonderful and your replies are very optimistic.

The above query is using non-prefix bitmap index on zip code. The sort_area_size is 30M,

is it possible to increase the bitmap_merge_area_size to 30M ?

I hope you might have read this thread from the beginning ?, the application which is using this database lets the user create ad-hoc queries and its unfortunate that i can not create composite indexes as i don't know what queries the applicaion is going to build.

Another thing which is coming to my mind is about the hardware performance ?. I checked the server, its Sun Sparc 3000 with lot of smaller disks and with the help of solstice the bigger volumes have been made. What do you thing on this part ?.


thanks


gtm


thanks

i wanted to add one more thing, db_file_multiblock_read_count is alread y32K

I do not think it is a server disk problem, as u see lots of physical reads , anyway u can check if there are any disk i/o waits .

The only option left is parallel query.

Also give a try to this, enable 10053 event and see what the trace file gives u.

This will show what CBO is doing in detail.

I've spend some time calculating row size in bytes(maximum),
assumed block size to be 8k,
assumed that block fits block_size/row_size rows
accounted for pctfree 5
row migration/chaining was not accounted.

Results: got that you read only <2 times more blocks than necessary:
156.562 consistent gets
129.468 physical gets
81.821 my estimated number of blocks occupied by rows returned in your query.

So from my understanding you should try to increase I/O speed and utilize memory, CPU work seems to be moderate for this particular query (try to check block cleanout statistics for session performing your query - those cleanouts may slow done things).
Could it be that your sybase machine keeps data in RAM?

Other issue is that you may need to read much more about sybase in order to reenginner properly the old database - from my knowledge Oracle select is not fastest and other DBMS has some advanced tricks for sql optimization.

Hi,
Is the count(*) necessary?
Try count(1).I think you will definitely have performance increase with the same set of output.
Pls lemme know if it helps
bye

can you explain why count(*) would be slower?

what is interesting in this example is the fact that table is FIXED width.
If sybase is able to utilize this fact then it is no surprise it is fast, for example:

lets have an index on (gender, zip) (not zip, gender!).

a B-tree index can then be represented as:


Level0: M(male), F(female), I(others...)
Level1: zipM1, ...,zipMn; zipF1, ...zipFn; zipI1, ...zipIn
(zips at level1 are sorted for M,F and I entries)

Now, since rows are fixed width, it is just a matter of a simple calculation to find number of rows falling in ranges of (zipMx and zipMy), (zipFx and zipFy) and (zipIx and zipIy).

Hmmm.... This is definitely a tough one. Unfortunately, I have little to add to the fray. I would agree that the row size is important, so making a larger block size may be prudent in this case. As for the specific indexes, you already said that you have no idea what they will be asking. Therefore, trying to craft an index specifically for this request seems irrelevant, as this request is not specifically your problem. I would first assume that you are dealing with something with low rate of updates, or bitmaps will get you in trouble. Given that assumption, multiple simple bitmap indexes are definitely the best idea. Going further with that assumption, parallel operations are a must; Larger block size is warrented; db_file_multiblock_read_count should be high. Also, this table should be thought of as a fact table in and of itself. To this end, think datawarehousing thoughts. This table needs to be more tightly normalized than it is. It should definitely *not* have 5-character zip codes in it. There should be a separate ZipCode_T table that holds the available zip codes with an abstracted, small as possible ID as the PK. Then, this much smaller FK would be in the very large customer table. There are a lot of issues like this that appear to me about this table design. If you are able, you should *really* think about redesigning the majority of this table. If not, oh well :).

The main reason I wanted to post a response here was to de-bunk the Count(*) vs. Count(1) issue - There is NO difference, period.

While I'm at it, cursor sharing is almost pointless in your case. It is not the parsing of the statement that is killing you - it is the size of the data. Besides, you already said they can build almost any select they want - the re-use would probably be low. Not that you can't do cursor sharing, but it is certainly nowhere near your major problem. Same with FIRST_ROWS, especially if you go with the bitmapped index solution where first_rows will not play mostly.

Finally, I guess I should ask how certain you are that partitioning on Region is the best. The *main* advantage of partitioning *from a performance perspective* is for partition elimination and parallelization. Partition elimination is the key. You want to partition on something that is *often* used in the WHERE clause, so the optimizer can easily decide to go after only a single partition or two. Partitioning can actually hurt performance if it is done on a bad field. (and especially if you do not use paralell execution)

Anyway, just things to think about. Sorry I don't have any hard and fast solutions for you - especially as to why Sybase is faster (which is a little shocking :) )

- Chris

as for sybase beeing faster: MS SQL server long ago (1 year?) outperformed oracle 8i. See TOP DB ratings on internet. (HW used for benchmarking could be an issue as well )
MS SQL server is derived from sybase.

As for methods how to do this, then think about:
1) fixed row size
2) various denormalized summaries, e.g. in b-tree index can contain number of rows per particular key or per particular b-tree vertex for summary queries.
3) commit/rollback implementation: oracle is optimized for insert/update (see block cleanout issue). Next thing is that getting block in query mode is pretty expensive as rollback segments has to be visited.
Some systems just block select(at least MS SQL srv version 6? did that for me) , while oracle not, just gives ora-01555 sometimes, but this can be managed via rollback segments.
4) quite advanced optimization and/or HW. A time ago I've been surprised by AS400 DB performance.

While it is true that Oracle and SQLServer keep leap-frogging each other in terms of performance, the same *cannot* be said about Sybase. While it is true that in v. 4.21, they were the same animal, they have diverged *massively* since then, with SQLServer capturing an ever larger portion of the market and Sybase slowly sinking into obscurity.

As for DB2, it *is* a very impressive database (although you couldn't pay me enough to actually work with that dinosaur:) ). With the proper hardware, it can definitely out-perform Oracle. I was assuming, however, that these were similar hardware setups with the same data model on them.

I *am* interested in the difference of indexes, as you and gtm mention. Sybase appears to have some different types. I'd like to know what they really do.

I was also assuming that the rollback and blocking issues would *not* apply. Based again on the use of bitmap indexes, I was assuming that this was at least a read-mostly if not a read-only (batch-filled) database.

I guess the real crux is: Is the sybase database consistently faster, or just on this query? After all, as people have shown, the proper multi-column index can be crafted to make *this particular query* run much, much faster. The problem is, of course, that that single-purpose index would probably not help many other queries. If it is only faster for this particular query, then how important is this query? If very important, add the index.

- Chris

well, comming back to the original query:

it definetly gets to many blocks from DISK, and order of minutes to perform this query is not a surprise.

In fact, I believe block gets can not be dramatically minimized (to get perfromance of seconds instead of minutes, this is 100 difference) if b-tree index is used : oracle still needs to scan at least leaf blocks of the index to calculate rowids. While in the query plan it also gets into table blocks...

So the only way to optimize is to use denormalized stuff or may be bitmap indexes.

From my understanding, bitmap indexes for this only query should be such that NO GETS OF TABLE BLOCKS are required at all: everything should be calculated from bitmap, and bitmaps are not large, they can fit into RAM.

Quote:

---

Originally posted by LND
what is interesting in this example is the fact that table is FIXED width.
If sybase is able to utilize this fact then it is no surprise it is fast, for example:
...SNIP...

---

This will probably not contribute anything usefull for the problem discused in this thread, but I'd like to add this because you repeat this "fact" in a couple of your replise here. What makes you think the record size of this table is fixed? with 99.99% accuracy I can say the records are of variable size in this table. For two reasons:
- it was never said there could be no NULL values in the table
- there are a couple of NUMBER columns which are never stored as fixed size

Just my 2 cents....

[Jmodaic: fixed witd]
I just made this assumption, based on the char usage in the table.

In general it is better to look into sybase to ******** how it works (stores data, retrieves it, etc). Who knows, may be sybase's special indexes (gtm: HG (High Group ) and HNG(High Non Group)) can be modelled in oracle as well.

see about sybase(and other) DW techniques:
http://www.informationweek.com/556/56olbit.htm

Quite interesting paper.

Create a binary index on Gender as this has very low cardinality.

sudip is right, from what is in http://www.gca.net/solutions/whitepa...ve_server.html paper the only analogous thing in Oracle are bitmap indexes.

The Sybase IQ is basically DW (readonly db), thus if Oracle's OLTP oriented DB is able to match Sybase IQ at least 10 times worse it will be quite ok.

Well, if appropriate materialized views are created, then most of the queries as the one above (count-by-gender-by-zip) will finish in a fraction of a second instead of 7 minutes as it takes now. The question is, are you able to forsee the most probable column combinations/agregations for theis ad-hoc querying sistem.

P.S. What I've found most interesting in the article was the date of the publication: December 1995 ;)

Quote:

---

Originally posted by jmodic
P.S. What I've found most interesting in the article was the date of the publication: December 1995 ;)

---

I read many interesting articles on Oracle dated for version6: short and clear, opposite to some V7,8,8i.

However, is it possible and how to have similar performance on Oracle's bitmap indexes(since 6 years already passed) for DSS oriented DB, and ,gtm, what kind of hardware we are talking about?