| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: 2GB limit of memory for Oracle on WIndows 32 bit
On Jul 3, 1:37 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Jul 2, 5:49 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
<SNIP>
> > The first of the above quotes seems to be in agreement with Tom Kyte's
> > "Expert Oracle One on One" book and the Oracle 8 and Oracle 10g R2
> > Concepts manual. The second of the above quotes (from another book by
> > Tom Kyte) seems to be in agreement with the article written by Svend
> > Jensen on Jonathan Lewis's website.
>
> > It makes one wonder... is there a correct answer?
>
> Yes, and it is actually quite simple. There doesn't have to be
> separate _mechanisms_ for the pools. The _behavior_ of the pools is
> influenced by the type of access of the objects in the pools. The
> idea is to segregate the access types - if the pool is going to be
> thrashed, put objects in recycle that thrash. This has the effect of
> lowering thrashing in the other pools. Separating out hot and wam
> areas is more subtle, but still, if you have too many different kinds
> of objects for just depending on the default LRU mechanism, it
> supposedly could help. Reread the references carefully with that in
> mind.
>
> jg
Curiosity is a huge time sink. I wondered if there are actually different mechanisms for the buffering of the KEEP and RECYCLE buffer caches. The test below is rather long, but it seems to indicate, at least on Oracle 10.2.0.3 with an 8KB block size, that there is a difference in the caching mechanisms for the KEEP and RECYCLE buffer caches:
STARTUP
Create 3 tables and indexes in the KEEP buffer pool and the RECYCLE
buffer pool:
CREATE TABLE T1 (
MY_DATE DATE NOT NULL, MY_NUMBER NUMBER(12,10) NOT NULL, MY_ROW NUMBER(12) NOT NULL)
MY_DATE DATE NOT NULL, MY_NUMBER NUMBER(12,10) NOT NULL, MY_ROW NUMBER(12) NOT NULL)
MY_DATE DATE NOT NULL, MY_NUMBER NUMBER(12,10) NOT NULL, MY_ROW NUMBER(12) NOT NULL)
MY_DATE DATE NOT NULL, MY_NUMBER NUMBER(12,10) NOT NULL, MY_ROW NUMBER(12) NOT NULL)
MY_DATE DATE NOT NULL, MY_NUMBER NUMBER(12,10) NOT NULL, MY_ROW NUMBER(12) NOT NULL)
MY_DATE DATE NOT NULL, MY_NUMBER NUMBER(12,10) NOT NULL, MY_ROW NUMBER(12) NOT NULL)
COMMIT;
INSERT INTO
T2
SELECT
TRUNC(SYSDATE)+ROWNUM,
COS(ROWNUM/180*3.141592),
ROWNUM
FROM
DUAL
CONNECT BY
LEVEL<=100000;
COMMIT;
INSERT INTO
T3
SELECT
TRUNC(SYSDATE)+ROWNUM,
COS(ROWNUM/180*3.141592),
ROWNUM
FROM
DUAL
CONNECT BY
LEVEL<=100000;
COMMIT;
INSERT INTO
T1_R
SELECT
TRUNC(SYSDATE)+ROWNUM,
COS(ROWNUM/180*3.141592),
ROWNUM
FROM
DUAL
CONNECT BY
LEVEL<=100000;
COMMIT;
INSERT INTO
T2_R
SELECT
TRUNC(SYSDATE)+ROWNUM,
COS(ROWNUM/180*3.141592),
ROWNUM
FROM
DUAL
CONNECT BY
LEVEL<=100000;
COMMIT;
INSERT INTO
T3_R
SELECT
TRUNC(SYSDATE)+ROWNUM,
COS(ROWNUM/180*3.141592),
ROWNUM
FROM
DUAL
CONNECT BY
LEVEL<=100000;
COMMIT; Create one more table in each of the buffer pools and load with data: CREATE TABLE T4 (
MY_DATE DATE NOT NULL, MY_NUMBER NUMBER(12,10) NOT NULL, MY_ROW NUMBER(12) NOT NULL)
MY_DATE DATE NOT NULL, MY_NUMBER NUMBER(12,10) NOT NULL, MY_ROW NUMBER(12) NOT NULL)
COMMIT;
INSERT INTO
T4_R
SELECT
TRUNC(SYSDATE)+ROWNUM,
COS(ROWNUM/180*3.141592),
ROWNUM
FROM
DUAL
CONNECT BY
LEVEL<=400000;
COMMIT;
Now, let's see what is in the KEEP and RECYCLE pools:
SELECT
DS.BUFFER_POOL,
SUBSTR(DO.OBJECT_NAME,1,9) OBJECT_NAME,
DS.BLOCKS OBJECT_BLOCKS,
COUNT(*) CACHED_BLOCKS
FROM
DBA_OBJECTS DO,
DBA_SEGMENTS DS,
V$BH V
WHERE
DO.DATA_OBJECT_ID=V.OBJD
AND DO.OWNER=DS.OWNER(+)
AND DO.OBJECT_NAME=DS.SEGMENT_NAME(+)
AND DO.OBJECT_TYPE=DS.SEGMENT_TYPE(+)
AND DS.BUFFER_POOL IN ('KEEP','RECYCLE')
GROUP BY
DS.BUFFER_POOL, DO.OBJECT_NAME, DS.BLOCKS
KEEP T2_IND1 256 2 RECYCLE T2_IND1_R 256 3 RECYCLE T2_R 384 2 KEEP T3 384 2 KEEP T3_IND1 256 3 RECYCLE T3_IND1_R 256 23 RECYCLE T3_R 384 64 KEEP T4 1536 1213 KEEP T4_IND1 1024 778 RECYCLE T4_IND1_R 1024 726 RECYCLE T4_R 1536 1175
On data load, the number cached blocks are different between the KEEP and RECYCLE pools.
Now, let's shutdown and startup to clear the cache and collect
statistics on the tables and indexes:
SHUTDOWN IMMEDIATE;
STARTUP
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE);
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2',CASCADE=>TRUE);
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T3',CASCADE=>TRUE);
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T4',CASCADE=>TRUE);
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1_R',CASCADE=>TRUE);
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2_R',CASCADE=>TRUE);
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T3_R',CASCADE=>TRUE);
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T4_R',CASCADE=>TRUE);
Let's see what is in the buffer cache:
SELECT
DS.BUFFER_POOL,
SUBSTR(DO.OBJECT_NAME,1,9) OBJECT_NAME,
DS.BLOCKS OBJECT_BLOCKS,
COUNT(*) CACHED_BLOCKS
FROM
DBA_OBJECTS DO,
DBA_SEGMENTS DS,
V$BH V
WHERE
DO.DATA_OBJECT_ID=V.OBJD
AND DO.OWNER=DS.OWNER(+)
AND DO.OBJECT_NAME=DS.SEGMENT_NAME(+)
AND DO.OBJECT_TYPE=DS.SEGMENT_TYPE(+)
AND DS.BUFFER_POOL IN ('KEEP','RECYCLE')
GROUP BY
DS.BUFFER_POOL, DO.OBJECT_NAME, DS.BLOCKS
RECYCLE T1_IND1_R 256 2 KEEP T4 1536 1233 KEEP T4_IND1 1024 765 RECYCLE T4_IND1_R 1024 866 RECYCLE T4_R 1536 1130
On statistics collection, the number cached blocks are different between the KEEP and RECYCLE pools.
SHUTDOWN IMMEDIATE;
STARTUP
Let's try updating some rows:
UPDATE
T1
SET
MY_ROW=MY_ROW+100
WHERE
MY_DATE BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE+500);
COMMIT;
UPDATE
T2
SET
MY_ROW=MY_ROW+100
WHERE
MY_DATE BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE+500);
COMMIT;
UPDATE
T3
SET
MY_ROW=MY_ROW+100
WHERE
MY_DATE BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE+500);
COMMIT;
UPDATE
T4
SET
MY_ROW=MY_ROW+100
WHERE
MY_DATE BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE+500);
COMMIT;
UPDATE
T1_R
SET
MY_ROW=MY_ROW+100
WHERE
MY_DATE BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE+500);
COMMIT;
UPDATE
T2_R
SET
MY_ROW=MY_ROW+100
WHERE
MY_DATE BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE+500);
COMMIT;
UPDATE
T3_R
SET
MY_ROW=MY_ROW+100
WHERE
MY_DATE BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE+500);
COMMIT;
UPDATE
T4_R
SET
MY_ROW=MY_ROW+100
WHERE
MY_DATE BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE+500);
COMMIT;
Let's see what is in the buffer cache:
SELECT
DS.BUFFER_POOL,
SUBSTR(DO.OBJECT_NAME,1,9) OBJECT_NAME,
DS.BLOCKS OBJECT_BLOCKS,
COUNT(*) CACHED_BLOCKS
FROM
DBA_OBJECTS DO,
DBA_SEGMENTS DS,
V$BH V
WHERE
DO.DATA_OBJECT_ID=V.OBJD
AND DO.OWNER=DS.OWNER(+)
AND DO.OBJECT_NAME=DS.SEGMENT_NAME(+)
AND DO.OBJECT_TYPE=DS.SEGMENT_TYPE(+)
AND DS.BUFFER_POOL IN ('KEEP','RECYCLE')
GROUP BY
DS.BUFFER_POOL, DO.OBJECT_NAME, DS.BLOCKS
KEEP T1 384 6 KEEP T1_IND1 256 5 RECYCLE T1_IND1_R 256 3 RECYCLE T1_R 384 3 KEEP T2 384 6 KEEP T2_IND1 256 5 RECYCLE T2_IND1_R 256 3 RECYCLE T2_R 384 3 KEEP T3 384 6 KEEP T3_IND1 256 5 RECYCLE T3_IND1_R 256 3 RECYCLE T3_R 384 3 KEEP T4 1536 8 KEEP T4_IND1 1024 13 RECYCLE T4_IND1_R 1024 4 RECYCLE T4_R 1536 3
On data update, the number cached blocks are different between the KEEP and RECYCLE pools.
Let's try a larger update on a single table:
UPDATE
T3
SET
MY_ROW=MY_ROW+100
WHERE
MY_DATE BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE+50000);
COMMIT;
UPDATE
T3_R
SET
MY_ROW=MY_ROW+100
WHERE
MY_DATE BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE+50000);
COMMIT;
BUFFER_ OBJECT_NA OBJECT_BLOCKS CACHED_BLOCKS
------- --------- ------------- -------------
KEEP T1 384 6 KEEP T1_IND1 256 5 RECYCLE T1_IND1_R 256 3 RECYCLE T1_R 384 3 KEEP T2 384 6 KEEP T2_IND1 256 5 RECYCLE T2_IND1_R 256 3 RECYCLE T2_R 384 3 KEEP T3 384 548 KEEP T3_IND1 256 5 RECYCLE T3_IND1_R 256 3 RECYCLE T3_R 384 548 KEEP T4 1536 6 KEEP T4_IND1 1024 13 RECYCLE T4_IND1_R 1024 4 RECYCLE T4_R 1536 3
On data update of a single table, the number cached blocks for the table affected by the update is roughly the same.
Let's try performing full tablescans on all of the test tables:
SELECT
COUNT(*)
FROM
T1
WHERE
MY_NUMBER<10;
SELECT
COUNT(*)
FROM
T2
WHERE
MY_NUMBER<10;
SELECT
COUNT(*)
FROM
T3
WHERE
MY_NUMBER<10;
SELECT
COUNT(*)
FROM
T4
WHERE
MY_NUMBER<10;
SELECT
COUNT(*)
FROM
T1_R
WHERE
MY_NUMBER<10;
SELECT
COUNT(*)
FROM
T2_R
WHERE
MY_NUMBER<10;
SELECT
COUNT(*)
FROM
T3_R
WHERE
MY_NUMBER<10;
SELECT
COUNT(*)
FROM
T4_R
WHERE
MY_NUMBER<10;
BUFFER_ OBJECT_NA OBJECT_BLOCKS CACHED_BLOCKS ------- --------- ------------- -------------
KEEP T1 384 2 RECYCLE T1_IND1_R 256 3 RECYCLE T1_R 384 372 KEEP T2 384 176 RECYCLE T2_IND1_R 256 3 RECYCLE T2_R 384 372 KEEP T3 384 373 RECYCLE T3_IND1_R 256 3 RECYCLE T3_R 384 548 KEEP T4 1536 1447 RECYCLE T4_IND1_R 1024 4 RECYCLE T4_R 1536 690
At least in this test case, there is a difference in the caching mechanisms for the KEEP and RECYCLE buffer caches.
Please let me know if there is a mistake in this test case, other than CACHED_BLOCKS exceeds OBJECT_BLOCKS in some cases.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Wed Jul 04 2007 - 21:13:57 CDT
 |
 |