conn / as sysdba
sho parameter keep_cache
NAME TYPE VALUE
-------------------------- ----------- ------------
db_keep_cache_size big nteger 0
sho parameter db_recycle
NAME TYPE VALUE
-------------------------- ----------- ------------
db_recycle_cache_size big integer 0
-- the keep and recycle caches should be the same size
ALTER SYSTEM SET DB_KEEP_CACHE_SIZE = 32M COMMENT='Altered 16-Jan-2021' SID='*' SCOPE=BOTH;
System altered.
ALTER SYSTEM SET DB_RECYCLE_CACHE_SIZE = 48M COMMENT='Altered 16-Jan-2021' SID='*' SCOPE=BOTH;
System altered.
sho parameter keep_cache
NAME TYPE VALUE
-------------------------- ----------- ------------
db_keep_cache_size big integer 32M
sho parameter db_recycle
NAME TYPE VALUE
-------------------------- ----------- ------------
db_recycle_cache_size big integer 48M
conn uwclass/uwclass@pdbdev
-- 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);
-- load 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_r
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;
-- view 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_ae 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_NAME OBJECT_BLOCKS CACHED_BLOCKS
------- ---------------- ------------- -------------
KEEP T1 384 384
KEEP T1_IND1 256 260
KEEP T2 384 384
KEEP T2_IND1 256 261
KEEP T3 384 384
KEEP T3_IND1 256 261
RECYCLE T4_IND1_R 960 771
RECYCLE T4_R 1440 1187
/* on data load, the number cached blocks are different between the KEEP and RECYCLE pools.
on shutdown and startup to clear the cache and collect statistics on the tables and indexes */
conn / at sysdba
shutdown immediate;
startup;
alter pluggable database pdbdev open;
conn uwclass/uwclass@pdbdev
exec dbms_stats.gather_table_stats('UWCLASS', tabname=>'t1',cascade=>TRUE);
exec dbms_stats.gather_table_stats('UWCLASS', tabname=>'t2',cascade=>TRUE);
exec dbms_stats.gather_table_stats('UWCLASS', tabname=>'t3',cascade=>TRUE);
exec dbms_stats.gather_table_stats('UWCLASS', tabname=>'t4',cascade=>TRUE);
exec dbms_stats.gather_table_stats('UWCLASS', tabname=>'t1_r',cascade=>TRUE);
exec dbms_stats.gather_table_stats('UWCLASS', tabname=>'t2_r',cascade=>TRUE);
exec dbms_stats.gather_table_stats('UWCLASS', tabname=>'t3_r',cascade=>TRUE);
exec dbms_stats.gather_table_stats('UWCLASS', tabname=>'t4_r',cascade=>TRUE);
-- 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_ae 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_NAME OBJECT_BLOCKS CACHED_BLOCKS
------- ---------------- ------------- -------------
KEEP T1 384 384
KEEP T1_IND1 256 260
KEEP T2 384 384
KEEP T2_IND1 256 261
KEEP T3 384 384
KEEP T3_IND1 256 261
RECYCLE T4_IND1_R 960 904
RECYCLE T4_R 1440 1052
-- on statistics collection, the number cached blocks are different between the KEEP and RECYCLE pools
conn / as sysdba
shutdown immediate;
startup;
alter pluggable database pdbdev open;
conn uwclass/uwclass@pdbdev
-- update 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;
-- 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_ae 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_NAME OBJECT_BLOCKS CACHED_BLOCKS
------- ---------------- ------------- -------------
KEEP T1 384 384
KEEP T1_IND1 256 260
RECYCLE T1_IND1_R 256 3
RECYCLE T1_R 384 4
KEEP T2 384 384
KEEP T2_IND1 256 261
RECYCLE T2_IND1_R 256 5
RECYCLE T2_R 384 3
KEEP T3 384 384
KEEP T3_IND1 256 261
RECYCLE T3_IND1_R 256 3
RECYCLE T3_R 384 4
RECYCLE T4_IND1_R 960 904
RECYCLE T4_R 1440 1025
-- on data update, the number cached blocks are different between the KEEP and RECYCLE pools
-- 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;
-- 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_ae 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_NAME OBJECT_BLOCKS CACHED_BLOCKS
------- ---------------- ------------- -------------
KEEP T1 384 384
KEEP T1_IND1 256 228
RECYCLE T1_IND1_R 256 6
RECYCLE T1_R 384 8
KEEP T2 384 383
KEEP T2_IND1 256 228
RECYCLE T2_IND1_R 256 9
RECYCLE T2_R 384 6
KEEP T3 384 498
KEEP T3_IND1 256 237
RECYCLE T3_IND1_R 256 6
RECYCLE T3_R 384 481
RECYCLE T4_IND1_R 960 904
RECYCLE T4_R 1440 525
-- on data update of a single table, the number cached blocks for the table affected by the update is roughly the same.
-- 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;
-- 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_ae 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_NAME OBJECT_BLOCKS CACHED_BLOCKS
------- ---------------- ------------- -------------
KEEP T1 384 384
KEEP T1_IND1 256 228
RECYCLE T1_R 384 2
KEEP T2 384 384
KEEP T2_IND1 256 228
RECYCLE T2_R 384 181
KEEP T3 384 498
KEEP T3_IND1 256 236
RECYCLE T3_R 384 481
RECYCLE T4_R 1440 1412 |
