15 July 2011

Use LIMIT to "control PGA" in session? (4498)

The 14 July quiz tested your knowledge of the LIMIT clause of BULK COLLECT. We scored as correct the following choice:

Use the LIMIT clause to control the amount of Process Global Area (PGA) memory used by the session running your program.

A player raised the following objection to this:

While limiting the number of rows returned is likely to reduce the amount of memory required, it does not constrain the amount of PGA allocated. Even if I were to agree that it controlled the amount of PGA used by the BULK COLLECT, that is not the same as controlling it for the session.

Before I respond, I would like to offer it up to other players for their comments. What do you think? Should we mark this as incorrect and why? Or do you feel this language is sufficiently clear to warrant a correct scoring?


  1. Hello Steven, All,

    Though maybe "reduce" would have been a word with a "narrower" significance than "control",
    I think that in the context of this quiz they can be considered equivalent.

    Or, put in another way, "reducing" is a particular way of "controlling", especially when it comes to resource consumption issues.

    And, as we know from studying the object relational features, a subtype can always replace its supertype :)

    I personally consider that both terms can be equally acceptable for this quiz, which I think was clear enough for all players for not raising any doubt regarding the choice correctness.

    Thanks & Best Regards,

  2. "Allocation" and "use" are completely different terms.

    "by the session running your program" is also different from "controlling it for the session"

    To me the terminology is different, the context is relevant and I don't see any problem with the way the question is worded.

    Ultimately, you're advising the developer about the ability to blow away your PGA with poor use of a bulk collect.

    Call a spade a spade, good work I reckon, Steven.

  3. I originated this objection.

    The LIMIT clause constrains the number of rows that a BULK COLLECT may return; however, it does not control the amount of PGA memory used. Although limiting the fetches to fewer rows is likely to reduce the amount of PGA used, it is not guaranteed. It is, in fact, entirely possible to exhaust PGA memory when a LIMIT of 1 is specified. One means of doing this is to select a row from a table with a nested table column that will not fit into PGA memory. This is, admittedly, an extreme case.

    It is interesting to note that in the explanation for answer 479 Steven wrote "The LIMIT clause was added to the BULK COLLECT feature to help developers manage the amount of Process Global Area (PGA) used" (emphasis mine). Although manage and control are near synonyms, the former more accurately expresses the effect of the LIMIT clause as influencing the amount memory used as opposed to constraining it.

    I thought for a long while before submitting my responses to this quiz because I understood the influence of the LIMIT clause on PGA memory but was uncomfortable with the wording of answer 471. Some may argue that the presence of the SGA choice (answer 479) indicated that one of the two choices was correct; however, the quiz guidance clearly states that all choices should be evaluated independently of the others.

  4. Hello All,
    For some reason, it looks that "controlling" was interpreted as synonym to "constraining", which I think it is NOT true in general, and also not in the particular case of this quiz.

    It is true that the whole PGA can be exhausted by putting into it one single row ... even without using a BULK fetch with a LIMIT clause at all ...

    But the statement in the quiz simply says that
    "you can use LIMIT to control the amount of PGA" in the obvious sense that using a lower value for LIMIT will cause a lower amount of PGA to be used, which is definitely true in ALL cases.
    How much lower ?
    This we cannot specify with full precision, it depends on how much memory is allocated for each data item (which sometimes changes with the Oracle versions, a.s.o.).
    But, more than probably, the PGA memory occupied by that specific collection will decrease proportionally with the decrease of the LIMIT value.

    Well, if you already fail with an insufficient memory when selecting a single row,
    it means that you should "control" the overall application design, rather than just the BULK COLLECT array size ...

    But, "control", "manage", "reduce", "influence"
    can all be acceptably used in this quiz with the same meaning.

    Thanks & Best Regards,