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.
Array Size and Transaction Size
Moderators: chulett, rschirm, roy
Array Size and Transaction Size
With regards,
Xpert
Xpert
Re: Array Size and Transaction Size
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
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
Xpert wrote: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.
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.
![Confused :?](./images/smilies/icon_confused.gif)
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Array Size and Transaction Size
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.subrat wrote: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
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.