Re: direct path read & db_file_multiblock_read_count
Date: Sat, 26 Oct 2013 11:05:01 +0200
Message-Id: <92745C5D-D9AA-4759-8A81-6F7A4DDB60B9_at_gmail.com>
Content-Transfer-Encoding: quoted-printable Content-Type: text/plain;
charset=windows-1252
Amir, what does "db_file_multiblock_read_count=128 (this is not set =
exclusively and is being set by the Oracle kernel based on the value of =
db_cache_size)" mean? How is it set in the parameterfile?
The reason for asking is: if you've not set this parameter, or have set =
it to "0", it will be "auto tuning". See a discussion about this from =
Charles Hooper here: =
http://hoopercharles.wordpress.com/2010/04/10/auto-tuned-db_file_multibloc=
k_read_count-parameter/
I _think_, or "have reasons to believe" the parameter is not even = statically set by the oracle instance depending on other settings, but = is totally dynamic by nature, which means it keeps "busyness" into = account, and sets the MBRC depending on (a number of) heuristics. These = heuristics seem to be IO and CPU usage at least.
This seems to be in line with what you are seeing: you have mixed sized = multiblock reads.
Please mind direct path reads can read over the extent border up to a = non-data block, or a block already in cache (the latter is true for = buffer/scattered reads, I haven't proved to myself that this is true for = direct path reads). Typically, a non-data block would be a L1/2/3 free = space bitmap block.
Frits Hoogland
http://fritshoogland.wordpress.com
frits.hoogland_at_gmail.com
Phone: +31 20 8946342
On 26 Oct 2013, at 00:21, Hameed, Amir <Amir.Hameed_at_xerox.com> wrote:
> Hi Folks, > Here is the environment configuration: > - RHEL 6/u4
>=20
> - Cisco UCS 16xcores & 128GB RAM
>=20
> - Oracle RDBMS 11.2.0.3 (Oracle ERP database)
>=20
> - db_cache_size=3D6G
>=20
> - pga_aggregate_target=3D2G
>=20
> - db_file_multiblock_read_count=128 (this is not set = exclusively and is being set by the Oracle kernel based on the value of = db_cache_size)
>=20
>=20
> I am running the following query to force a FTS:
>=20
> select /*+ full(GLL) */ count(*) from GL_JE_LINES GLL > union all > select /*+ full(FA) */ count(*) from FA_BALANCES_REPORTS_ITF FA > ;
>=20
> When I trace the session with 10046/level 8, I see that most of the = 'direct path read' are being done with cnt=16 as shown below: > grep 'direct path read' n22vt2_ora_9850_Linux.trc|awk '{print = $13}'|sort|uniq -c|sort -k 1 -n
> ... > 288 cnt=80 > 307 cntH > 1345 cnt=128 > 3493 cnt=15 > 48942 cnt=16
>=20
> This is quite consistent on all environments on Linux. On Solaris, a = similar type of statement shows that most of the DPR are done with = cnt=128.
>=20
> Does anyone know what might be causing Oracle to choose 16 blocks as = opposed to the larger 128 blocks reads.
>=20
> Thanks, > Amir
>=20
> -- > http://www.freelists.org/webpage/oracle-l
>=20
>=20
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Oct 26 2013 - 11:05:01 CEST