Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    9

    Lightbulb Unanswered: Associative Arrays...How are they managed?

    Hi I have a question about how the associative arrays(table data structure in pl/sql) are managed by oracle. Does Oracle keep the data in memory and start paging it to disk once they reach a certain size or does it keep certain amount in memory and use some temporary table to which it insert and retrieve data as and when needed.
    This is important to me as I feel that I may end up putting a lot of data in the associative arrays and I know for certain that paging is really slow.

    Thanks for your insight..

    Regards,
    Sumit
    Last edited by sumit192; 01-20-04 at 21:02.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Why bother posting your question because
    "I know for certain that paging is really slow."
    Yes, going to disk is slow.
    Oracle tables are on disk.
    OS pages to disk.
    If/when you don't have enough RAM, response will be slow.
    So what exactly is your question?

  3. #3
    Join Date
    Aug 2001
    Posts
    66
    Question is about associative arrays not relational tables (already answered on OTN, btw) and is a pertinent question IMHO.

    Associative arrays are stored in memory in the PGA (program global area). Exactly how much memory is available will depend on the individual setup - you can check the memory used by your session in v$mystat (or that of other sessions in v$sesstat etc.) e.g.

    SQL> SELECT n.name, s.value
    2 FROM v$statname n, v$mystat s
    3 WHERE s.statistic# = n.statistic#
    4 AND n.name LIKE '%session pga%';

    NAME VALUE
    ---------------------------------------------------------------- ----------
    session pga memory 552952
    session pga memory max 1011704

    SQL>

    Using relatively large amounts of memory in an associative array (and hence session) is typically unscaleable, i.e. this approach is not likely to work effectively across multiple concurrent sessions because of the combined memory requirements.

    The good news is that there are excellent alternatives in in PL/SQL (BULK COLLECT with LIMIT or pipelined functions) and in SQL (global temporary tables) that can provide effective throughput without large memory requirements and hence are eminently more scaleable.
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

  4. #4
    Join Date
    Jan 2004
    Posts
    9

    Smile

    The reason why I had asked this question was :
    1: I am new to using PL/SQL
    2: I am trying to retrieve data that could exceed a billion rows by the end of this year and I have to perform some analysis on that data. Now I was told that Oracle handles the memory management of the PL/SQL associative arrays(A false notion that was corrected thanks to padderz and some other people on the OTN network).

    My way of going about handling the problem would be to retain a certain amount of data in memory(assiciative array) and the rest in a temporary table and fetch the data back from the temporary table when the associateive array becomes empty. I will need to look into BULK COLLECT and the pipelining functions just to see if they can handle a model similar to what I have in mind.

    If any of you could suggest an alternative or provide a code snippet it would be great.

    Padderz thank you for your help,

    Regards,
    Sumit
    Last edited by sumit192; 01-21-04 at 16:16.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Every modern OS implements virtual memory model.
    The result is data will either reside in memory (RAM) or on disk;
    because there is no place else for it to maintain any sort of permanence.
    If/when the size/amount of data exceeds the "available RAM", it must
    end up on disk or an error gets thrown saying "No More Space Available"
    In some case the decision is made by the programmer by explicitly inserting data into a table. In other cases the programmer can use a "memory resident" data structure such a PL/SQL table or temporary global table expect Oracle/OS to manage when it "overflows" to disk.
    Accessing disk is 2 - 3 (or more) orders of magnitude slower than
    accessing RAM. When memory is depleted, you pay the price!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •