1 2 Previous Next 26 Replies Latest reply: Jan 20, 2013 8:29 PM by 636309 RSS

    querying data from the buffer_cache

    636309
      Hi,
      I'm running an experiment on my own home database to understand how data is retrieved from the buffer_cache. Using Oracle 10g 10.2.0.3.0. I've populated 2 tables with 10million records each. The table structure is as follows.

      create table bigtable(col1 varchar2(50), col2 varchar2(50))
      create table bigtablechild(col1 varchar2(50), col2 varchar2(50))

      bigtablechild.col1 is a foreign key to bigtable.col1 and there are no indexes on the tables.

      When I run this query, it takes about 30 s to return data each time it runs. I can also see hard drive activity running on my handy hard drive monitor utility each time. I thought that once the data is loaded into the buffer_cache, the query wouldn't scan the hard disk for data. Could someone please help me understand what is happening?

      select b.col2 from bigtable a, bigtablechild b where a.col1 = b.col1 and a.col1 = 'ABC8876'
      -------------------------------------------------------------------------------------------------------
      | Id  | Operation          | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
      -------------------------------------------------------------------------------------------------------
      |*  1 |  HASH JOIN         |               |      1 |      5 |      5 |00:00:23.00 |   93671 |  90663 |
      |*  2 |   TABLE ACCESS FULL| BIGTABLE      |      1 |      1 |      1 |00:00:14.26 |   57799 |  54931 |
      |*  3 |   TABLE ACCESS FULL| BIGTABLECHILD |      1 |      5 |      5 |00:00:08.74 |   35872 |  35732 |
      -------------------------------------------------------------------------------------------------------
      Edited by: arizona9952 on Jan 19, 2013 9:38 AM
        • 1. Re: querying data from the buffer_cache
          Etbin
          I thought that once the data is loaded into the buffer_cache, the query wouldn't scan the hard disk for data.
          Have you enough memory to contain both tables entirely ? Your plan shows a FTS will be performed on both tables.

          Regards

          Etbin
          • 2. Re: querying data from the buffer_cache
            636309
            Etbin wrote:
            Have you enough memory to contain both tables entirely ?
            I have 2 GB of RAM on my computer. How would I know if I have enough?
            • 3. Re: querying data from the buffer_cache
              Etbin
              Sorry, never had DBA privileges, so I can't tell you where to look to find out your Oracle installation parameters (memory pool areas ... ).

              Regards

              Etbin

              Edited by: Etbin on 19.1.2013 17:55

              I missed your query.
              Create an index on bigtable.col1 and you'll see much less disk activity
              • 4. Re: querying data from the buffer_cache
                stefan nebesnak
                You can find out storage space used by a table:
                SELECT segment_name, SUM(bytes)/1024/1024 MB 
                FROM dba_segments 
                WHERE segment_type like '%TABLE%' 
                and segment_name in ('BIGTABLE','BIGTABLECHILD')
                group by segment_name;
                • 5. Re: querying data from the buffer_cache
                  JohnWatson
                  You need to read up on the CACHE clause of CREATE TABLE (or ALTER TABLE) and the effect of setting CACHE or NOCACHE for large and small tables. Hint: by default, your tables are not being cached.
                  • 6. Re: querying data from the buffer_cache
                    Srini Chavali-Oracle
                    Gather statistics on the two tables, then query AVG_ROW_LEN in DBA_TABLES for the two tables, multiply the two AVG_ROW_LEN values by 10 million each, sum up the two resulting numbers to get an estimate (in bytes) of how much data will be loaded in the database buffers.

                    Execute the "show sga" command after logging in with SYSDBA privileges - http://docs.oracle.com/cd/E11882_01/server.112/e16638/memory.htm#PFGRF94261 - look for the value of "database buffers" in the output - this value should be greater than the result determined from the calculation above. If it is not, then data will be aged from the database buffers and will need to be read from disk again.

                    HTH
                    Srini
                    • 7. Re: querying data from the buffer_cache
                      Aman....
                      It's not the o/s RAM that would be used for the caching of the data but the amount of RAM that youhave designated as buffer cache within your SGA , that would be used for it. You are probably running the tests on large tables and you don't have enough sized buffer cache. It's correct that with the buffer cache, next time, the access is done from the buffer cache provided it holds the data. Using some small tables you can see this behavior like below,
                      SQL*Plus: Release 11.2.0.3.0 Production on Sat Jan 19 22:48:14 2013
                      
                      Copyright (c) 1982, 2011, Oracle.  All rights reserved.
                      
                      startConnected to an idle instance.
                      
                      SQL> startup
                      SP2-0734: unknown command beginning "startstart..." - rest of line ignored.
                      SQL> startup
                      ORACLE instance started.
                      
                      Total System Global Area  263639040 bytes
                      Fixed Size                  1344312 bytes
                      Variable Size             205524168 bytes
                      Database Buffers           54525952 bytes
                      Redo Buffers                2244608 bytes
                      Database mounted.
                      Database opened.
                      SQL> set autot trace stat
                      select             
                      SP2-0042: unknown command "s" - rest of line ignored.
                      SQL> select * from scott.emp;
                      
                      14 rows selected.
                      
                      
                      Statistics
                      ----------------------------------------------------------
                               57  recursive calls
                                0  db block gets
                               87  consistent gets
                               11  physical reads
                                0  redo size
                             1454  bytes sent via SQL*Net to client
                              419  bytes received via SQL*Net from client
                                2  SQL*Net roundtrips to/from client
                                6  sorts (memory)
                                0  sorts (disk)
                               14  rows processed
                      
                      SQL> /      
                      
                      14 rows selected.
                      
                      
                      Statistics
                      ----------------------------------------------------------
                                0  recursive calls
                                0  db block gets
                                7  consistent gets
                                0  physical reads
                                0  redo size
                             1454  bytes sent via SQL*Net to client
                              419  bytes received via SQL*Net from client
                                2  SQL*Net roundtrips to/from client
                                0  sorts (memory)
                                0  sorts (disk)
                               14  rows processed
                      You can see that the next time when the query is run, there are no physical IO's but only logical ones.

                      HTH
                      Aman....
                      • 8. Re: querying data from the buffer_cache
                        JohnWatson
                        Aman.... wrote:
                        It's not the o/s RAM that would be used for the caching of the data but the amount of RAM that youhave designated as buffer cache within your SGA , that would be used for it. You are probably running the tests on large tables and you don't have enough sized buffer cache. It's correct that with the buffer cache, next time, the access is done from the buffer cache provided it holds the data. Using some small tables you can see this behavior like below,
                        Using small tables, yes. They are smaller than smalltable_threshold. It is all to do with the way the CACHE clause changes the running of a full table scan, not to do with the size of the buffer cache.
                        • 9. Re: querying data from the buffer_cache
                          JohnWatson
                          Srini Chavali wrote:
                          Gather statistics on the two tables, then query AVG_ROW_LEN in DBA_TABLES for the two tables, multiply the two AVG_ROW_LEN values by 10 million each, sum up the two resulting numbers to get an estimate (in bytes) of how much data will be loaded in the database buffers.

                          Execute the "show sga" command after logging in with SYSDBA privileges - http://docs.oracle.com/cd/E11882_01/server.112/e16638/memory.htm#PFGRF94261 - look for the value of "database buffers" in the output - this value should be greater than the result determined from the calculation above. If it is not, then data will be aged from the database buffers and will need to be read from disk again.

                          HTH
                          Srini
                          No, all this is irrelevant. By default, Oracle will not cache the scan of a table if it is larger than the small table threshold. If it did, the results would be disastrous: my full scan of a multi-gigabyte table would take over thhe entire buffer cache. You wouldn't want that, would you?
                          • 10. Re: querying data from the buffer_cache
                            Aman....
                            John,

                            Thanks, I completely forgot about this parameter but I guess, putting this into the picture just makes the whole thing a lot more complex :) .
                            http://hoopercharles.wordpress.com/2010/06/17/_small_table_threshold-parameter-and-buffer-cache-what-is-wrong-with-this-quote/
                            http://dioncho.wordpress.com/2009/04/22/strong-doubt-on-small-table_small_table_threshold/
                            http://jonathanlewis.wordpress.com/2011/03/24/small-tables/

                            Regards
                            Aman....
                            • 11. Re: querying data from the buffer_cache
                              stefan nebesnak
                              You can also permanently pin your table to subset of the over all buffer pool SGA with DBMS_SHARED_POOL.KEEP
                              alter table BIGTABLE storage (buffer_pool keep);
                              select t.owner, t.name, t.type, t.kept, t2.cache from v$db_object_cache t, dba_tables t2
                              where t.NAME = t2.table_name
                              and t.NAME in ('BIGTABLE','BIGTABLECHILD')
                              and t.TYPE like '%TABLE%';
                              • 12. Re: querying data from the buffer_cache
                                JohnWatson
                                stefan nebesnak wrote:
                                You can also permanently pin your table to subset of the over all buffer pool SGA with DBMS_SHARED_POOL.KEEP
                                alter table BIGTABLE storage (buffer_pool keep);
                                select t.owner, t.name, t.type, t.kept, t2.cache from v$db_object_cache t, dba_tables t2
                                where t.NAME = t2.table_name
                                and t.NAME in ('BIGTABLE','BIGTABLECHILD')
                                and t.TYPE like '%TABLE%';
                                There are so many mistakes in this thread. DBMS_SHARED_POOL.KEEP and V$DB_OBJECT_CACHE have nothing to do with caching tables in the keep pool. The example above doesn't even create a keep pool.
                                • 13. Re: querying data from the buffer_cache
                                  Aman....
                                  You cache your buffers and pin your shared pool objects.

                                  Aman....
                                  • 14. Re: querying data from the buffer_cache
                                    Srini Chavali-Oracle
                                    John - thanks for the correction - you are right - there are internal mechanisms (like smalltable_threshold) that will prevent the buffer cache from being "flooded" by FTS.

                                    Srini
                                    1 2 Previous Next