-
Hi everyone,
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?
db_file_multiblock_read_count = (max_IO_size/db_block_size)
How do I find the max_io_size for aix 4.3.2, unix, rs6000 server?
Thanks,
Kathy
-
Usually the max_IO_size used is 64K. Thus, with a 4K block size your db_file_multiblock_read_count would be set to 16.
David D. Fitzjarrell
Oracle Certified DBA
-
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?
-
courtesy from ixora.com
Code:
-------------------------------------------------------------------------------
--
-- 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 ----------------------------------------
host sed -n '/scattered/s/.*p3=//p' &Trace_Name | sort -n | tail -1
@restore_sqlplus_settings
-------------------------------------------------------------------------------
--
-- Script: save_sqlplus_settings.sql
-- Purpose: to reset sqlplus settings
--
-- Copyright: (c) Ixora Pty Ltd
-- Author: Steve Adams
--
-------------------------------------------------------------------------------
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.
--
-------------------------------------------------------------------------------
column trace_file_name new_value Trace_Name
column trace_file_zipped new_value Trace_Zipped noprint
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
/
clear columns
-------------------------------------------------------------------------------
--
-- Script: restore_sqlplus_settings.sql
-- Purpose: to reset sqlplus settings
--
-- Copyright: (c) Ixora Pty Ltd
-- Author: Steve Adams
--
-------------------------------------------------------------------------------
set termout off
@sqlplus_settings
clear breaks
clear columns
clear computes
set termout on
David D. Fitzjarrell
Oracle Certified DBA
-
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:
http://www.ixora.com.au/home.htm
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
-
OS Block Size
Hi Pando,
You said check x$kccle to find OS Block size.
I checked it which colum v hv to c.
is the column called
LESIZ or LELOS
in my case LESIZ = 40960.
now tell.....
--------------------------
The Time has come ....
-
I cant tell the column now coz I dont have a database handy, there are two columns which you multiply together you get the redo log size, it´s
number of blocks * os block size
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|