This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Jan 20, 2013 6:29 PM by 636309 RSS

querying data from the buffer_cache

636309 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    You cache your buffers and pin your shared pool objects.

    Aman....
  • 14. Re: querying data from the buffer_cache
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points