Home » RDBMS Server » Performance Tuning » Keep buffer pool after restart of the server
Keep buffer pool after restart of the server [message #364499] Mon, 08 December 2008 20:16 Go to next message
snomula
Messages: 4
Registered: October 2008
Junior Member
Hi,

What happens to the blocks in keep buffer pool when database is restarted. I was thinking that Oracle database will reload the object that I have declared storage as keep. But seems like that's not how how database behaves. How can I make sure when I restart the database server it reloads the objects into keep pool which I have declared storage as keep pool?

Thanks
S

Report message to a moderator

Re: Keep buffer pool after restart of the server [message #364509 is a reply to message #364499] Mon, 08 December 2008 23:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nothing happens at instance startup; objects are loaded as for other caches: when they are accessed.

Regards
Michel

Report message to a moderator

Re: Keep buffer pool after restart of the server [message #364641 is a reply to message #364499] Tue, 09 December 2008 07:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The only way to do this that I've ever found is to create an ON STARTUP trigger that runs some code that will perform a select on all the data in the tables that you want in the keep pool.

Report message to a moderator

Re: Keep buffer pool after restart of the server [message #364696 is a reply to message #364641] Tue, 09 December 2008 12:04 Go to previous messageGo to next message
snomula
Messages: 4
Registered: October 2008
Junior Member
Thanks for the response. So I guess I have to force a full scan on the table to load the table into keep pool. Would it be possible for Database to load blocks into keep pool if I just gather stats? Please let me know.

Thanks

Report message to a moderator

Re: Keep buffer pool after restart of the server [message #364836 is a reply to message #364696] Wed, 10 December 2008 03:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yes, analysing the tables will put the blocks into cache.

Here's an example. Run this script and compare the physical reads from the two SELECT statements. The first does perform physical reads, whereas the second reads everything from cache.

If anyone could explain why the consistent_gets are higher on the first query I'd be very grateful.
Script:
drop table test_038;

create table test_038 (col_1  number);

insert into test_038 (select level from dual connect by level <= 10000);

commit;

set autotrace on;

alter system flush buffer_cache;

select count(*) from test_038;

alter system flush buffer_cache;

begin
  dbms_stats.gather_table_stats(ownname => null
                               ,tabname => 'TEST_038');
end;
/

select count(*) from test_038;

set autotrace off;


Results:
SQL> drop table test_038;

Table dropped.

SQL> 
SQL> create table test_038 (col_1  number);

Table created.

SQL> 
SQL> insert into test_038 (select level from dual connect by level <= 10000);

10000 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> set autotrace on;
SQL> 
SQL> alter system flush buffer_cache;

System altered.

SQL> 
SQL> select count(*) from test_038;

  COUNT(*)
----------
     10000


Execution Plan
----------------------------------------------------------
Plan hash value: 2363355014

-----------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_038 | 10000 |     6   (0)| 00:00:01 |
-----------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         47  consistent gets
         21  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> alter system flush buffer_cache;

System altered.

SQL> 
SQL> begin
  2    dbms_stats.gather_table_stats(ownname => null
  3                                 ,tabname => 'TEST_038');
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> 
SQL> select count(*) from test_038;

  COUNT(*)
----------
     10000


Execution Plan
----------------------------------------------------------
Plan hash value: 2363355014

-----------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_038 | 10000 |     6   (0)| 00:00:01 |
-----------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> set autotrace off;
SQL>

Report message to a moderator

Re: Keep buffer pool after restart of the server [message #364837 is a reply to message #364696] Wed, 10 December 2008 03:59 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This is really a non-issue. all of these blocks will br read into the cache as soon as they're used anyway, and the performance impact will almost certainly be masked by the overhead of reading blocks back into the main buffer cache.

Assuming you're not shutting the server down every night, this should only rarely happen anyway.

Report message to a moderator

Previous Topic: Table extents
Next Topic: How to improve the performance of "creating partition".
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jun 28 01:24:20 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.