cancel
Showing results for 
Search instead for 
Did you mean: 

Limit size for index tablespaces

Former Member
0 Kudos
136

Hello,

We have a lot of performance issues with queries selecting from tables whose indexes blong to the tablespaces BTABI

This tablespaces is the second biggest one in our system

1-is there any relation between size and performance?

2- Does the size of the index tablespace impact the bufferisation process?

3- is there any maximum for the size of index tablespace above which bufferization won't work

Thanks for your help

Meher

Accepted Solutions (0)

Answers (7)

Answers (7)

Former Member
0 Kudos

Hello,

I think that problem is rather with the index cluster ration who stuck at 82%

May be it is better to close this thread and open new one with problem of cluster ration not increased after reorg job

Thanks

Meher

StephenSun
Advisor
Advisor
0 Kudos

Hello Meher,

Yes, if the query returns two many data and the cluster ratio is bad, then IXSCAN-FETCH will perform a lot of random I/Os for fetching, which could result in bad performance.

To increase cluster ratio, you need use the following reorg syntax:

REORG TABLE <tab_name> INDEX <ind_name_for_clustering>

The reason that the standard table reorganization did not improve cluster ratio is in SAP environments, except some BW tables, we do not define clustering index. So you need above syntax when you want to improve cluster ratio.

Regards

Former Member
0 Kudos

Hello,

Please to let me know if you have any hint

Thanks

Meher

Former Member
0 Kudos

I have to add one point,

if the return is few hundered lines ==> Response times is same in Production as well as Test

if the return from the select is many thousands rows ==> Response time in test environment is 100 times faster than production

Meher

Former Member
0 Kudos

Yes

I am using the same in my test system as production (Test system is a copy of my production system)

But performance is not the same for this standard query.

what is strange for me is :

1- Cluster ratio doesn't increase after reorg program : allways 82%

2- The Test system 100 times faster for this query,

Please let me know if you have any hint or assumption that could be investigated

Best Regards

Meher

Former Member
0 Kudos

Hello

Thanks for these answers:

Yes I did an SQL trace

90% ofruntime is in this line from SQL trace

*127624930 SAPLV45I VBFA

FETCH

7 44347 0 R/3 *

The corresponding query for this Fetch is :

SELECT WHERE "MANDT" = '130' AND "VBELV" = '0030017135' ORDER BY "MANDT", "VBELV", "POSNV", "VBELN", "POSNN", "VBTYP_N" WITH UR -- OPTLEVEL( 5 ) -- QUERY_DEGREE( ...

It is a standard SAP query, that is selecting from VBFA using the primary key !!

An additonal input :

The cluster ratio is 82%, we executed the reorg program sevral times and remain stuck to 82%

SAP told us, it is normal and acceptable to have 82% as cluster ratio

Peformance in our test environment is 100 times fatser with a cluster ration of 100%

Do you think that could be a reason ? how to redress the cluster ratio other than the reorg program?

Thanks

Meher

KayKa
Active Participant
0 Kudos

Hi Meher,

i think the execution plan is ok. You have to use the primary to satisfy the order by clause without sorting.

Do this query with mandt and vbelv return the 90.000 rows ? How many rows does your VBFA have ? Is it the same in test environment ?

I expect that after a reorg the cluster ratio of the pk index has to be near 100%. But 82% will be good enough.

best regards

Kay

Former Member
0 Kudos

Thanks a lot for the answer

it helpful in particular the second part of the answer.

What is exactly buffered :

1- it is all indexes for a table space which is moved to the buffer pool

or

2- The index of a the Table

or

3- or a part of the index relevant to the query

Fucntionally speaking,

1- I have index table space BTABI contains a lot of indexes such as index for VBFA (it is huge 300GB)

2- and then I have index for VBFA whose size is around 3 GB

3- and finally some sales document in VBFA have around 90 000 entries

Do you have an idea What is exactly buffered

Thanks and best regards

Meher

Thanks

Former Member
0 Kudos

You can't buffer your big table/indexes and also not recommended.

Can you please check whether the table statistics are upto date. Also check with your ABAPer whether the index are being used while executing the query. and is there any IO wait on the database disk. Try to enable the sql trace using st05 and do analysis.

KayKa
Active Participant
0 Kudos

Hi Meher,

all data you get from the database came through the buffers. If the data is present in the buffer you get it immediatly, if not, the rdms will read your data from the disk and put it in buffer. After this you get the data from the buffer.

What is exactly buffered :

3- or a part of the index relevant to the query

Only the parts of the index and the table that are necessary for your statement will be load in the buffer.

For example you have a query with "where id = 4711' and the column ID is the primary key. First the optimizer had to decide whjat execution plan has to performed. In the example it is an unique index access. So the rdms fetch some pages from the index in order to find the index entry 4711. Because the index is something like a tree, the rdms step through some index levels to find the leaf pages with 4711. There it finds the information where to find the row in the table.

That will be 1-n pages of the index and another page from the table that is loaded in the buffer and you get the data yiou need.

Next another query with "id = 4712" came in. And now you have a chance to use only the buffer, because the index entry 4712 will reside in the same page as th 4711. And with a little luck (and a good cluster ratio) the data of table lies in the same pages as for 4711. So the second query read the same pages as before but this time buffer can satisfy the whole query.

The rdbms will read some more pages than necessary, this functionality is named prefetch, read in advance or something like this. And with this additional pages in the buffer you get the additional chance to read some queries from the buffers.

Fucntionally speaking,

1- I have index table space BTABI contains a lot of indexes such as index for VBFA (it is huge 300GB)

2- and then I have index for VBFA whose size is around 3 GB

3- and finally some sales document in VBFA have around 90 000 entries

The interesting fact is the 90.000 entries. I expect you have a query on VBFA with some where-clauses that returns up to 90.000 entries.

The question is now, how many index entries you have to read.

As an example you where looks like this

col1 = '4711' and

col2 = 'ACTIVE' and

col3 = '20101215'

And this where gives you 90.000 entries. If you have an index with (col1, col2, col3) then you need only 90.000 index entries to read and afterwards 90.000 rows from the table.

But if you index looks only like this ( col1, col2 ) then your col1,col2 combination can return much more entries than the 90.000 expected. And for every index entry you have to read the row to decide if this row has the right col3-value.

And this can be very slow, perhaps slower than a full-table-scan.

So look into the st04 SQL Cache and examine what is the execution plan and how many pages are read to satisfy your query. Or as mentioned above do a st05 sql trace.

best regards

Kay

KayKa
Active Participant
0 Kudos

Hi Meher,

there is a relation between size and performance. A select on a large table will last longer than on a small table if the execution plan is the same.

The question is how selective are your indexes and/or your queries. The index access is buildt for return a handfull of index-entries. If your select has to check ten-thousands or millions of index-entries, additional access the corresponding rows, you can think the index is too big.

But you have to keep in mind the work, that has to be done. How many rows has to be examinend, how many pages has to be read. And if this work is huge then the buffer can be too small. But the buffer only helps you the second time you need the data from your index and/or your table.

best regards

Kay