i am looking at my estat and showing lots of db_file_scattered_reads (full Table Scans). Our parameter = 6 and block size = 4096. I am under the impression, increasing the db_file_multiblock_read_count will help read more blocks into the cache at one time, decreasing IO?
thanks, I kinda seen reference to the 64k. Since these are exceptionally old servers, I had my doubts. Is there a command in Unix i could verify this figure with?
-------------------------------------------------------------------------------
--
-- Script: multiblock_read_test.sql
-- Purpose: find largest actual multiblock read size
--
-- Copyright: (c) Ixora Pty Ltd
-- Author: Steve Adams
--
-- Description: This script prompts the user to enter the name of a table to
-- scan, and then does so with a large multiblock read count, and
-- with event 10046 enabled at level 8.
-- The trace file is then examined to find the largest multiblock
-- read actually performed.
--
-------------------------------------------------------------------------------
@save_sqlplus_settings
alter session set db_file_multiblock_read_count = 32768;
/
column value heading "Maximum possible multiblock read count"
select
value
from
sys.v_$parameter
where
name = 'db_file_multiblock_read_count'
/
prompt
@accept Table "Table to scan" SYS.SOURCE$
prompt Scanning ...
set termout off
alter session set events '10046 trace name context forever, level 8'
/
select /*+ full(t) noparallel(t) nocache(t) */ count(*) from &Table t
/
alter session set events '10046 trace name context off'
/
set termout on
prompt
prompt Maximum effective multiblock read count
prompt ----------------------------------------
host sed -n '/scattered/s/.*p3=//p' &Trace_Name | sort -n | tail -1
trace_name is the trace file name
If you want to know your OS block size check x$kccle
There are scripts missing and, as such, this will do very little, if any, good. Here are all of the pieces:
-------------------------------------------------------------------------------
--
-- Script: multiblock_read_test.sql
-- Purpose: find largest actual multiblock read size
--
-- Copyright: (c) Ixora Pty Ltd
-- Author: Steve Adams
--
-- Description: This script prompts the user to enter the name of a table to
-- scan, and then does so with a large multiblock read count, and
-- with event 10046 enabled at level 8.
-- The trace file is then examined to find the largest multiblock
-- read actually performed.
--
-------------------------------------------------------------------------------
@save_sqlplus_settings
alter session set db_file_multiblock_read_count = 32768;
/
column value heading "Maximum possible multiblock read count"
select
value
from
sys.v_$parameter
where
name = 'db_file_multiblock_read_count'
/
prompt
@accept Table "Table to scan" SYS.SOURCE$
prompt Scanning ...
set termout off
alter session set events '10046 trace name context forever, level 8'
/
select /*+ full(t) noparallel(t) nocache(t) */ count(*) from &Table t
/
alter session set events '10046 trace name context off'
/
set termout on
@trace_file_name
prompt
prompt Maximum effective multiblock read count
prompt ----------------------------------------
set termout off
store set sqlplus_settings replace
clear breaks
clear columns
clear computes
set feedback off
set verify off
set termout on
set define "&"
-------------------------------------------------------------------------------
--
-- Script: accept.sql
-- Purpose: to prompt for a script parameter, but allow a default value
--
-- Copyright: (c) Ixora Pty Ltd
-- Author: Steve Adams
--
-- Synopsis: @accept name prompt default
--
-------------------------------------------------------------------------------
accept _value_entered prompt "&2 [&3] "
column _value_returned new_value &1 noprint
set termout off
select nvl('&_value_entered', '&3') "_value_returned" from dual;
set termout on
undefine 1 2 3 _value_entered
column _value_returned clear
-------------------------------------------------------------------------------
--
-- Script: trace_file_name.sql
-- Purpose: to get the name of the current trace file
--
-- Copyright: (c) Ixora Pty Ltd
-- Author: Steve Adams
--
-- Synopsis: @trace_file_name
--
-- OR
--
-- set termout off
-- @trace_file_name
-- set termout on
-- ... &Trace_Name ...
--
-- Description: This script gets the name of the trace file for the current
-- session. It can be used interactively, or from other scripts.
-- The name is saved in the SQL*Plus define &Trace_Name.
--
-- There are three versions of the query below, because the trace
-- files are named differently depending on the platform. The
-- two incorrect versions should be commented out or deleted.
--
-------------------------------------------------------------------------------
select
d.value || '/ora_' || p.spid || '.trc' trace_file_name,
d.value || '/ora_' || p.spid || '.trc.gz' trace_file_zipped
from
( select
p.spid
from
sys.v_$mystat m,
sys.v_$session s,
sys.v_$process p
where
m.statistic# = 1 and
s.sid = m.sid and
p.addr = s.paddr
) p,
( select
value
from
sys.v_$parameter
where
name = 'user_dump_dest'
) d
/
select
d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name,
d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc.gz' trace_file_zipped
from
( select
p.spid
from
sys.v_$mystat m,
sys.v_$session s,
sys.v_$process p
where
m.statistic# = 1 and
s.sid = m.sid and
p.addr = s.paddr
) p,
( select
t.instance
from
sys.v_$thread t,
sys.v_$parameter v
where
v.name = 'thread' and
(
v.value = 0 or
t.thread# = to_number(v.value)
)
) i,
( select
value
from
sys.v_$parameter
where
name = 'user_dump_dest'
) d
/
select
d.value || '\ora' || lpad(p.spid, 5, '0') || '.trc' trace_file_name,
d.value || '\ora' || lpad(p.spid, 5, '0') || '_trc.gz' trace_file_zipped
from
( select
p.spid
from
sys.v_$mystat m,
sys.v_$session s,
sys.v_$process p
where
m.statistic# = 1 and
s.sid = m.sid and
p.addr = s.paddr
) p,
( select
value
from
sys.v_$parameter
where
name = 'user_dump_dest'
) d
/
I belive the most benefitial thing would be to simply post the URL of Ixora site or of that particular script. Not only is reading a hundread-lines-long script tedious in a mostly-short-messages forum, more important the Ixora site is such an inexhaustible source of Oracle knowlede that it is worth visiting for anyone serious about Oracle. So here it is:
Reply With Quote
Bookmarks