| 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
Repeating the test run on Oracle 10.2.0.2, results inline:
On Jul 4, 10:13 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Jul 3, 1:37 pm, joel garry <joel-ga..._at_home.com> wrote:
> > 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)
> STORAGE (BUFFER_POOL KEEP);
>
> CREATE UNIQUE INDEX T1_IND1 ON T1(MY_DATE)
> STORAGE (BUFFER_POOL KEEP);
>
> CREATE TABLE T2 (
> MY_DATE DATE NOT NULL,
> MY_NUMBER NUMBER(12,10) NOT NULL,
> MY_ROW NUMBER(12) NOT NULL)
> STORAGE (BUFFER_POOL KEEP);
>
> CREATE UNIQUE INDEX T2_IND1 ON T2(MY_DATE)
> STORAGE (BUFFER_POOL KEEP);
>
> CREATE TABLE T3 (
> MY_DATE DATE NOT NULL,
> MY_NUMBER NUMBER(12,10) NOT NULL,
> MY_ROW NUMBER(12) NOT NULL)
> STORAGE (BUFFER_POOL KEEP);
>
> CREATE UNIQUE INDEX T3_IND1 ON T3(MY_DATE)
> STORAGE (BUFFER_POOL KEEP);
>
> CREATE TABLE T1_R (
> MY_DATE DATE NOT NULL,
> MY_NUMBER NUMBER(12,10) NOT NULL,
> MY_ROW NUMBER(12) NOT NULL)
> STORAGE (BUFFER_POOL RECYCLE);
>
> CREATE UNIQUE INDEX T1_IND1_R ON T1_R(MY_DATE)
> STORAGE (BUFFER_POOL RECYCLE);
>
> CREATE TABLE T2_R (
> MY_DATE DATE NOT NULL,
> MY_NUMBER NUMBER(12,10) NOT NULL,
> MY_ROW NUMBER(12) NOT NULL)
> STORAGE (BUFFER_POOL RECYCLE);
>
> CREATE UNIQUE INDEX T2_IND1_R ON T2_R(MY_DATE)
> STORAGE (BUFFER_POOL RECYCLE);
>
> CREATE TABLE T3_R (
> MY_DATE DATE NOT NULL,
> MY_NUMBER NUMBER(12,10) NOT NULL,
> MY_ROW NUMBER(12) NOT NULL)
> STORAGE (BUFFER_POOL RECYCLE);
>
> CREATE UNIQUE INDEX T3_IND1_R ON T3_R(MY_DATE)
> STORAGE (BUFFER_POOL RECYCLE);
>
> Make certain that the two buffer pools are the same size:
> ALTER SYSTEM SET DB_KEEP_CACHE_SIZE=16777216;
> ALTER SYSTEM SET DB_RECYCLE_CACHE_SIZE=16777216;
>
> Now, let's start loading data into the tables:
> INSERT INTO
> T1
> SELECT
> TRUNC(SYSDATE)+ROWNUM,
> COS(ROWNUM/180*3.141592),
> ROWNUM
> FROM
> DUAL
> CONNECT BY
> LEVEL<=100000;
>
> 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)
> STORAGE (BUFFER_POOL KEEP);
>
> CREATE UNIQUE INDEX T4_IND1 ON T4(MY_DATE)
> STORAGE (BUFFER_POOL KEEP);
>
> CREATE TABLE T4_R (
> MY_DATE DATE NOT NULL,
> MY_NUMBER NUMBER(12,10) NOT NULL,
> MY_ROW NUMBER(12) NOT NULL)
> STORAGE (BUFFER_POOL RECYCLE);
>
> CREATE UNIQUE INDEX T4_IND1_R ON T4_R(MY_DATE)
> STORAGE (BUFFER_POOL RECYCLE);
>
> INSERT INTO
> T4
> SELECT
> TRUNC(SYSDATE)+ROWNUM,
> COS(ROWNUM/180*3.141592),
> ROWNUM
> FROM
> DUAL
> CONNECT BY
> LEVEL<=400000;
>
> 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
> ORDER BY
> DO.OBJECT_NAME,
> DS.BUFFER_POOL;
>
> BUFFER_ OBJECT_NA OBJECT_BLOCKS CACHED_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.
10.2.0.2:
BUFFER_ OBJECT_NA OBJECT_BLOCKS CACHED_BLOCKS
------- --------- ------------- -------------
KEEP T1 384 1 KEEP T1_IND1 256 1 RECYCLE T1_IND1_R 256 1 KEEP T2 384 3 KEEP T2_IND1 256 4 RECYCLE T2_IND1_R 256 1 RECYCLE T2_R 384 1 KEEP T3 384 139 KEEP T3_IND1 256 89 RECYCLE T3_IND1_R 256 60 RECYCLE T3_R 384 97 KEEP T4 1536 1055 KEEP T4_IND1 1024 708 RECYCLE T4_IND1_R 1024 738 RECYCLE T4_R 1536 1097
> 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
> ORDER BY
> DO.OBJECT_NAME,
> DS.BUFFER_POOL;
>
> BUFFER_ OBJECT_NA OBJECT_BLOCKS CACHED_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.
10.2.0.2:
BUFFER_ OBJECT_NA OBJECT_BLOCKS CACHED_BLOCKS
------- --------- ------------- -------------
KEEP T4 1536 1164 KEEP T4_IND1 1024 836 RECYCLE T4_IND1_R 1024 813 RECYCLE T4_R 1536 1187
>
> 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
> ORDER BY
> DO.OBJECT_NAME,
> DS.BUFFER_POOL;
>
> 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 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.
10.2.0.2:
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 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
>
> 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.
10.2.0.2:
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 597 KEEP T3_IND1 256 5 RECYCLE T3_IND1_R 256 3 RECYCLE T3_R 384 568 KEEP T4 1536 6 KEEP T4_IND1 1024 13 RECYCLE T4_IND1_R 1024 4 RECYCLE T4_R 1536 3
> 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.
10.2.0.2:
BUFFER_ OBJECT_NA OBJECT_BLOCKS CACHED_BLOCKS
------- --------- ------------- -------------
KEEP T1 384 2 RECYCLE T1_R 384 1 KEEP T2 384 182 RECYCLE T2_R 384 184 KEEP T3 384 378 RECYCLE T3_R 384 376 KEEP T4 1536 1441 RECYCLE T4_R 1536 1441
> 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.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Thu Jul 05 2007 - 08:25:12 CDT
 |
 |