different physical access method because of disabling Automated Memory Management?
- From: "Martin Berger" <martin.a.berger@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 2 Jan 2009 12:15:51 +0100
Hi List,
once again I'm coming up with a behaviour of Oracle rdbms which I can not
explain to myselve. So I'm asking here for help.
If you are not interrested in theoretical discussions about how oracle
heuristics might work, please excuse this email and stop here.
All others are warmly welcome to read and reply ;-)
My monitoring:
=============
the same statement (select rowid from test.t_keep) is executed different
only because memory_target, sga_target and shared_pool_size are different.
(In addition, db_cache_size and db_keep_cache_size might be of any
interrest).
With automatic memory management the DB uses 'db file sequential read' and
fills up the (keep) buffer cache, with manual memory parameters it uses 'direct
path read' (only 4 'db file sequential read' at the beginning).
Some facts around:
=================
* I bonced the instance between all the test-cases.
Version 11.1.0.7 on a virtual test-machine (RedHat - 32 bit)
table definition and rows:
-------------------------
CREATE TABLE test.t_keep (
id NUMBER,
n1 NUMBER,
n2 NUMBER,
pad VARCHAR2(4000)
) STORAGE (BUFFER_POOL KEEP);
execute dbms_random.seed(0)
INSERT INTO test.t_keep
SELECT rownum AS id,
1+mod(rownum,251) AS n1,
1+mod(rownum,251) AS n2,
dbms_random.string('p',255) AS pad
FROM dual
CONNECT BY level <= 100000
ORDER BY dbms_random.value;
commit;
* all statements run as sys (I'm lazy, alone on the node, etc).
the statements I run:
--------------------
-- to check the blocks of the table
select blocks from dba_tables where table_name='T_KEEP';
BLOCKS
----------
496
-- get obj_id:
select object_id, data_object_id from dba_objects where object_name
='T_KEEP';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
64143 64143
what I run at every testcase:
============================
-- to make sure nothing is cached yet:
select obj, count(*) from x$bh where obj in (64142, 64143) group by obj;
-- test itselve
select rowid from test.t_keep;
-- check cache afterwards:
select obj, count(*) from x$bh where obj in (64142, 64143) group by obj;
With the 'manual' setup I get:
OBJ COUNT(*)
---------- ----------
64143 4
With the 'auto' setup I get:
OBJ COUNT(*)
---------- ----------
64143 452
Additional Infos/thoughts:
=========================
I checked traces:
In 10046 I see the difference of WAITS, but no reason why.
In 10053 I see some smaller changes in derived parameters (as they are not
implicite set in spfile), But I checked them and nothing changed:
* difference in _smm_min_size and _smm_max_size and set it (in manual mode):
select a.ksppinm name, b.ksppstvl value
from sys.x$ksppi a,sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm in ('_smm_max_size','_smm_min_size');
NAME VALUE
-------------- -------
_smm_min_size 184
_smm_max_size 36864
=> but with the same result.
* db_file_multiblock_read_count = 128 (17 in manual mode)
=> same result.
I have put some files on a web-server not to bloat this mail too much:
=====================================================================
'manual': (memory_target, sga_target and shared_pool_size set)
strings spfile => http://berx.at/traces/manual
event 10046 => http://berx.at/traces/BERX2_ora_5081.trc
event 10053 => http://berx.at/traces/BERX2_ora_6938.trc
'auto':
strings spfile => http://berx.at/traces/auto
event 10046 => http://berx.at/traces/BERX2_ora_6262.trc
event 10053 => http://berx.at/traces/BERX2_ora_6632.trc
Can anybody hint me why the acces changes from 'db file sequential read' to
'direct path read' when I change memory/SGA (and buffer) settings?
thanks in advance
Martin
--
//www.freelists.org/archive/oracle-l/
Other related posts:
