ON Resource Pool Memory Allocation Parameter
I had 2 question on Memory Allocation parameters:
Q1. What are the implications of setting MEMORYSIZE = 3G and MAXMEMORYSIZE = 3G on one pool?
Ans: My understanding is- if our query needs more resource then it CAN'T borrow additional resource from General pool.
Q2. For example, we have a pool with MEMORYSIZE = 1G, MAXMEMORYSIZE = 5G, and our query that needs 5G memory to complete.
a. How much memory will be borrowed from the General pool?
b. What if a query from this pool needs 6G?
Ans: a. 4G will be borrowed from general Pool
b. The resource pool will "REJECT" the query and it would show "ABORT" in resource_rejections system table.
Please clarify me if i am wrong. Thank you.
Regards,
Kushal
0
Comments
Q1. What are the implications of setting MEMORYSIZE = 3G and MAXMEMORYSIZE = 3G on one pool?
Ans: My understanding is- if our query needs more resource then it CAN'T borrow additional resource from General pool.
Euge Ans: Yes your answer is correct but only for the MAXMEMORYSIZE it can borrow more memory. The main implication is that you will have 3 GB that NOBODY else can use, it is call a stand alone pool and only people assigned to that pool will use that 3GB,
2. For example, we have a pool with MEMORYSIZE = 1G, MAXMEMORYSIZE = 5G, and our query that needs 5G memory to complete.
a. How much memory will be borrowed from the General pool?
b. What if a query from this pool needs 6G?
Ans: a. 4G will be borrowed from general Pool
b. The resource pool will "REJECT" the query and it would show "ABORT" in resource_rejections system table.
Euge Ans. a. Yes your are right.
b. The query will be rejected and in the resource_rejeciton you only will see the COUNT of resource_rejected by "Request exceeded high limit " increaded.
If you want to see why the query was rejected you can look at the dc_resource_rejections table with predicate transaction_id = the transaction and statement_id = the statement number.
Hope this helps,
Eugenia
Thanks Eugenia for your answer.
Q1.
Could you pls explain what you mean by "but only for the MAXMEMORYSIZE it can borrow more memory". Do you mean even If we keep MAXMEMORYSIZE = 3G, then also the query can pull additional resource from General pool ?
One additional query, sometimes our job failed with the below error-
"ERROR 4305: Out of system WOS memory during catalog SELECT"
So afer sometime again when we run our job it runs fine. Is there way to check then in the 2nd run there are enough resouces for the query to run successfully?
Our resource pool allocation is shown below:
SELECT NAME,MEMORYSIZE,maxmemorysize FROM RESOURCE_POOLS;
NAME MEMORYSIZE maxmemorysize
general Special: 95%
sysquery 64M
sysdata 100M 1G
wosdata 0% 2G
tm 200M
refresh 0%
recovery 0%
dbd 0%
jvm 0% 2G
sacct_pool 0%
Thanks again!
What it means that the query can't ask more than 3GB because the MAXmemorysize parameter not because you put MEMsize = 3gb.
That is the sysdata pool, you could increase the size so it can borrow from general, the defualt is too small.
Hope this helps.
Eugenia
If the MAXMEMORYSIZE is declared then we the query can't pull resources from General pool. am in correct?
Thank you
It can't pull MORE memory than the declared. It is pulling resources from the General UP to taht value. Does make sense?