Fitting everything into memory? Experiences?

From: Pasi Parkkonen <pasi_at_eigenvalue.com>
Date: Mon, 4 Dec 2000 10:18:36 +0200
Message-ID: <90fk0o$77v$1_at_tron.sci.fi>

Hi there!

I would like to hear Your experiences on the subject.

My goal is to make our production database as fast as possible. I have thought many ways and am now going to set up big enough SGA to fit all table's data into memory.

I'm going to go the simplest(?) way and creating tables with "CACHE" clause on our test environment and try it there. I have calculated the space (I included the source code here in case you're interested, this doesn't affect optimizer!) and I have enough memory for all data + running instance + OS.

What can go wrong with this setup?

-I know that I have to monitor LGWR (and DBWR) in case dirty buffers
writing gets too heavy for disk I/O. But what else should I care about?

All thoughts and insights are welcome!
Reply to this group or directly to me.

Pasi Parkkonen
EigenDBA
EigenValue Ltd.
pasi_at_eigenvalue.com

Source code for calculating space:
(C) Oracle Education, Oracle 8 DBA Course (Modified by me to be more useful. You can import output into Excel, for example)

---

SET SERVEROUTPUT ON;
SET HEAD OFF;
SET PAGESIZE 0; DECLARE

 v_owner       VARCHAR2(30) := 'SCOTT';
 v_segment_name     VARCHAR2(30);
 v_segment_type     VARCHAR2(30) := 'TABLE';
 v_total_blocks     NUMBER;
 v_total_bytes     NUMBER;
 v_unused_blocks     NUMBER;
 v_unused_bytes     NUMBER;
 v_last_used_extent_file_id  NUMBER;
 v_last_used_extent_block_id  NUMBER;
 v_last_used_block    NUMBER;

pl_tablename VARCHAR2(40) := '';

CURSOR c_loop_all_schema_tables IS
 SELECT object_name
 FROM user_objects
 WHERE object_type = v_segment_type;

BEGIN
 dbms_output.enable(20000);
 OPEN c_loop_all_schema_tables;
 FETCH c_loop_all_schema_tables INTO pl_tablename;

 dbms_output.put_line('Table, blocks, blocks above HWM');  WHILE c_loop_all_schema_tables%FOUND LOOP

  dbms_space.unused_space(v_owner     ,
        pl_tablename   ,
        v_segment_type   ,
        v_total_blocks   ,
        v_total_bytes   ,
        v_unused_blocks   ,
        v_unused_bytes   ,
        v_last_used_extent_file_id ,
        v_last_used_extent_block_id ,
        v_last_used_block);

  dbms_output.put_line(pl_tablename || ',' || v_total_blocks || ',' || v_unused_blocks);
-- dbms_output.put_line(INITCAP(v_segment_type) || ': ' || v_owner
||'.'||pl_tablename);
-- dbms_output.put_line('Total blocks: '||TO_CHAR(v_total_blocks));

• dbms_output.put_line('Blocks above HWM: ' || TO_CHAR(v_unused_blocks)); FETCH c_loop_all_schema_tables INTO pl_tablename;

 END LOOP;  CLOSE c_loop_all_schema_tables;
END;
/ Received on Mon Dec 04 2000 - 09:18:36 CET