Array Size and Transaction Size

[Xpert]

Hi all,

I have used the Array size = 32767 and Transaction size = 20000. And aslo prefetch memory setting = 20000. Could anyone help me whether this combination is correct one or not?

Thanks in advance.

[subrat]

From my memory below is the formula which usually give app. Array size and Transaction Size.

Array size = (Pagesize - 64) / (Rowsize * 1.1).
8192 Bytes = pagesize for DWH
4096 Bytes = pagesize for Mart.
Also transaction size is calculated as Transaction size = Pages per Extent * Array size
DWH table have 64 pages per extent.
Datamart tables have 32 pages per extent

Please Check it with your figures...

Thanks
SK

Hi all,

I have used the Array size = 32767 and Transaction size = 20000. And aslo prefetch memory setting = 20000. Could anyone help me whether this combination is correct one or not?

Thanks in advance.

[chulett]

There's no "correct combination" and I've never seen a formula that worked worked. Page size? Pages per extent? Where does it say every DWH table has "64 pages per extent"? All that seems very specific to a particular environment / setup and not something that would work in a generic fashion.

My only advice is to make the Transaction Size an even multiple of the Array Size. After that, there are too many variables to make a global statement about what's "best" or "correct" for any given situation.

Read up on the "prefetch memory setting" before you go changing it from the default of zero and assigning random values to it. Talk to your DBA if "memory level for top-level rows to be prefetched" doesn't make any sense.

[ray.wurlod]

From my memory below is the formula which usually give app. Array size and Transaction Size.

Array size = (Pagesize - 64) / (Rowsize * 1.1).
8192 Bytes = pagesize for DWH
4096 Bytes = pagesize for Mart.
Also transaction size is calculated as Transaction size = Pages per Extent * Array size
DWH table have 64 pages per extent.
Datamart tables have 32 pages per extent

These are what have been set up for your site. Others may have had their data warehouse and data mart tables sized differently. One size definitely does NOT fit all.