4 Replies Latest reply on Oct 13, 2013 8:52 PM by pcpaasche

    Difference between 12c In-Memory option and 11g's BUFFER_POOL KEEP

    pcpaasche

      Can anybody please enlighten me in a brief comment on the difference between the new In-Memory option of Oracle 12c and the existing functionality in Oracle 11g where you can store a table permanently in buffer cache by STORAGE (BUFFER_POOL KEEP) as shown in the code sample below?

       

      I have searched the internet for an answer to this, but with no luck.

       

       

      CREATE TABLE t1 (

          my_date   DATE NOT NULL,

          my_number NUMBER(12,10) NOT NULL,

          my_row    NUMBER(12) NOT NULL)

      STORAGE (BUFFER_POOL KEEP);

       

      Source : Oracle Buffer Pool Keep Pool Recycle Pool

        • 1. Re: Difference between 12c In-Memory option and 11g's BUFFER_POOL KEEP
          Alvaro

          Well seeing as it was just announced on OOW I couldn't find any sold technical explanation how exactly the in-memory option will behave regarding data synchronization with the underlying storage.

           

          However, I would guess that the in-memory option will rely on some unique background process to keep cleaning the buffers than the traditional DBWR that does the job when a table is on the KEEP POOL.

           

          Just wild speculation, would also be interested if any of the ACEs hanging around could offer an explanation.

          1 person found this helpful
          • 2. Re: Difference between 12c In-Memory option and 11g's BUFFER_POOL KEEP
            Jonathan Lewis

            Juan Loaiza's presentation is probably available on the Oracle site by now, but in outline: the in-memory component duplicates data (specified tables - perhaps with a restriction to a subset of columns) in columnar format in a dedicated area of the SGA. The data is kept up to date in real time, but Oracle doesn't use undo or redo to maintain this copy of the data because it's never persisted to disc in this form, it's recreated in-memory (by a background process) if the instance restarts. The optimizer can then decide whether it would be faster to use a columnar or row-based approach to address a query.

             

            The intent is to help systems which are mixed OLTP and DSS - which sometimes have many "extra" indexes to optimise DSS queries that affect the performance of the OLTP updates. With the in-memory columnar copy you should be able to drop many "DSS indexes", thus improving OLTP response times - in effect the in-memory stuff behaves a bit like non-persistent bitmap indexing.

             

            Updated 18th Oct: 

            I've been reminded that I think the presentation also included some comments about the way that the code also takes advantage of "vector" (SIMD) instructions at the CPU level to allow the code to evaluate predicates on multiple rows (extracted from the column store, not the row store) simultaneously, and this contributes to the very high rates of data scanning that Oracle Corp. claims.

             

            Regards

            Jonathan Lewis

            • 3. Re: Difference between 12c In-Memory option and 11g's BUFFER_POOL KEEP
              Nikolay Savvinov

              Hi,

               

              specifying KEEP buffer cache pool doesn't guarantee that the table will permanently reside in that pool. It simply assigns the table to a non-default pool where its blocks are likely to stay longer, but all basic algorithms for handling data in these pools are more or less the same (see e.g. Ask Tom "KEEP Buffer Pool "), i.e. if you wanted to, you could've used the RECYCLE pool as KEEP or vice versa.

               

              In-memory option in 12c is entirely different.

               

              Best regards,

                Nikolay

              1 person found this helpful
              • 4. Re: Difference between 12c In-Memory option and 11g's BUFFER_POOL KEEP
                pcpaasche

                Thank's a lot for your helpful answers and sorry for my late feedback ( I thought I should receive emails when someone answered to my post, but I did not...).

                 

                Juan Loaiza's presentation is not available for download yet, but will hopefully be found here in not too distant future: https://oracleus.activeevents.com/2013/connect/sessionDetail.ww?SESSION_ID=9312


                Anyway, I found your comments very useful and I have been enlightened