Performance Problem ?

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