different physical access method because of disabling Automated Memory Management?
From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Fri, 2 Jan 2009 12:15:51 +0100
Message-ID: <cd8f74560901020315l4c6ea1denb213c47bb24eb809_at_mail.gmail.com>
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).
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:
'manual': (memory_target, sga_target and shared_pool_size set)
Date: Fri, 2 Jan 2009 12:15:51 +0100
Message-ID: <cd8f74560901020315l4c6ea1denb213c47bb24eb809_at_mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
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.trcCan 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
-- http://www.freelists.org/archive/oracle-l/ ------=_Part_46834_10411560.1230894951496 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi List,<div><br>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.<br>If you are not interrested in theoretical discussions about how oracle heuristics might work, please excuse this email and stop here.<br> All others are warmly welcome to read and reply ;-)<div><br>My monitoring: <br>=============<br>the same statement (<span class="Apple-style-span" style="font-style: italic;">select rowid from test.t_keep</span>) is executed different only because <span class="Apple-style-span" style="font-style: italic;">memory_target</span>, <span class="Apple-style-span" style="font-style: italic;">sga_target </span>and <span class="Apple-style-span" style="font-style: italic;">shared_pool_size </span>are different. (In addition, <span class="Apple-style-span" style="font-style: italic;">db_cache_size </span>and <span class="Apple-style-span" style="font-style: italic;">db_keep_cache_size </span>might be of any interrest).<br> With automatic memory management the DB uses '<span class="Apple-style-span" style="font-style: italic;">db file sequential read</span>' and fills up the (keep) buffer cache, with manual memory parameters it uses '<span class="Apple-style-span" style="font-style: italic;">direct path read</span>' (only 4 '<span class="Apple-style-span" style="font-style: italic;">db file sequential read</span>' at the beginning).</div> <div><br>Some facts around:<br>=================<br><span class="Apple-style-span" style="font-weight: bold;">*</span> I bonced the instance between all the test-cases.<br>Version 11.1.0.7 on a virtual test-machine (RedHat - 32 bit)<br> table definition and rows:<br>-------------------------<br>CREATE TABLE test.t_keep (<br> id NUMBER, <br> n1 NUMBER, <br> n2 NUMBER, <br> pad VARCHAR2(4000)<br>) STORAGE (BUFFER_POOL KEEP);<br>execute dbms_random.seed(0)<br> INSERT INTO test.t_keep<br>SELECT rownum AS id,<br> 1+mod(rownum,251) AS n1,<br> 1+mod(rownum,251) AS n2,<br> dbms_random.string('p',255) AS pad<br>FROM dual<br>CONNECT BY level <= 100000<br>ORDER BY dbms_random.value;<br> commit;</div><div><br><span class="Apple-style-span" style="font-weight: bold;">*</span> all statements run as sys (I'm lazy, alone on the node, etc).</div><div><br>the statements I run:<br>--------------------<br>-- to check the blocks of the table <br> <span class="Apple-style-span" style="font-style: italic;">select blocks from dba_tables where table_name='T_KEEP';</span><br> BLOCKS<br>----------<br> 496<br> <br>-- get obj_id:<br><span class="Apple-style-span" style="font-style: italic;">select object_id, data_object_id from dba_objects where object_name ='T_KEEP';</span><br> OBJECT_ID DATA_OBJECT_ID<br>---------- --------------<br> 64143 64143</div><div><br>what I run at every testcase:<br>============================<br>-- to make sure nothing is cached yet:<br><span class="Apple-style-span" style="font-style: italic;">select obj, count(*) from x$bh where obj in (64142, 64143) group by obj; </span></div> <div><span class="Apple-style-span" style="font-style: italic;"><br></span>-- test itselve<br><span class="Apple-style-span" style="font-style: italic;">select rowid from test.t_keep;</span></div><div><span class="Apple-style-span" style="font-style: italic;"><br> </span>-- check cache afterwards:<br><span class="Apple-style-span" style="font-style: italic;">select obj, count(*) from x$bh where obj in (64142, 64143) group by obj; </span><br><br>With the '<span class="Apple-style-span" style="text-decoration: underline;">manual</span>' setup I get:<br> OBJ COUNT(*)<br>---------- ----------<br> 64143 <span class="Apple-style-span" style="font-weight: bold;">4</span><br> </div><div><br>With the '<span class="Apple-style-span" style="text-decoration: underline;">auto</span>' setup I get:<br> OBJ COUNT(*)<br>---------- ----------<br> 64143 <span class="Apple-style-span" style="font-weight: bold;">452</span><br><br></div><div>Additional Infos/thoughts: <br>=========================<br>I checked traces:<br> In 10046 I see the difference of WAITS, but no reason why.<br>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:</div><div><br><span class="Apple-style-span" style="font-weight: bold;">*</span> difference in <span class="Apple-style-span" style="font-style: italic;">_smm_min_size</span> and <span class="Apple-style-span" style="font-style: italic;">_smm_max_size</span> and set it (in manual mode):</div> <div><br>select a.ksppinm name, b.ksppstvl value <br>from sys.x$ksppi a,sys.x$ksppcv b <br>where a.indx = b.indx <br> and a.ksppinm in ('_smm_max_size','_smm_min_size');<br>NAME VALUE<br>-------------- -------<br> _smm_min_size 184<br>_smm_max_size 36864<br><span class="Apple-style-span" style="font-weight: bold;">=></span> but with the same result.</div><div><br><span class="Apple-style-span" style="font-weight: bold;">*</span> db_file_multiblock_read_count = 128 (17 in manual mode)<br> <span class="Apple-style-span" style="font-weight: bold;">=></span> same result.<br><br>I have put some files on a web-server not to bloat this mail too much:<br>=====================================================================<br> '<span class="Apple-style-span" style="text-decoration: underline;">manual</span>': (memory_target, sga_target and shared_pool_size set)<br>strings spfile => <a href="http://berx.at/traces/manual">http://berx.at/traces/manual</a><br> event 10046 => <a href="http://berx.at/traces/BERX2_ora_5081.trc">http://berx.at/traces/BERX2_ora_5081.trc</a> <br>event 10053 => <a href="http://berx.at/traces/BERX2_ora_6938.trc">http://berx.at/traces/BERX2_ora_6938.trc</a> <br> '<span class="Apple-style-span" style="text-decoration: underline;">auto</span>':<br>strings spfile => <a href="http://berx.at/traces/auto">http://berx.at/traces/auto</a> <br>event 10046 => <a href="http://berx.at/traces/BERX2_ora_6262.trc">http://berx.at/traces/BERX2_ora_6262.trc</a> <br> event 10053 => <a href="http://berx.at/traces/BERX2_ora_6632.trc">http://berx.at/traces/BERX2_ora_6632.trc</a> <br>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?</div> <div><br>thanks in advance<br> Martin</div><div><br>--<br><a href="http://www.freelists.org/archive/oracle-l/">http://www.freelists.org/archive/oracle-l/</a></div></div> ------=_Part_46834_10411560.1230894951496-- -- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 02 2009 - 05:15:51 CST