db_file_multiblock_read_count
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: db_file_multiblock_read_count

  1. #1
    Join Date
    Nov 2000
    Posts
    173
    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

  2. #2
    Join Date
    Nov 2001
    Location
    Central U.S.
    Posts
    35
    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

  3. #3
    Join Date
    Nov 2000
    Posts
    173
    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?


  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  5. #5
    Join Date
    Nov 2001
    Location
    Central U.S.
    Posts
    35
    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

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    http://www.jlcomp.demon.co.uk is excellent site as well

  8. #8
    Join Date
    May 2001
    Location
    Delhi
    Posts
    340

    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 ....

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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
  •  



Click Here to Expand Forum to Full Width