This discussion is archived
1 4 5 6 7 8 Previous Next 117 Replies Latest reply: May 5, 2008 10:19 AM by 181444 Go to original post RSS
  • 90. Re: query tuning
    CharlesHooper Expert
    Currently Being Moderated
    Addressing a couple typos:
    "with 64KB stripe width" should read "with 64KB stripe depth" - width and depth have two very different meanings. Stripe depth is the size of the stripe on each drive, while stripe width is the size of the stripe across all non-mirrored drives in the array. AS I recall, the SAME document that I referenced suggests a 1MB stripe size, which I interpret to mean the stripe width.

    "we find a block entry" should read "we find a blog entry"

    "what is causing the single block reads?" should read " what is causing the multiple, single block reads?" - a read for a full table scan could be truncated to a single block read if the other blocks needed are already in the buffer cache, but it appears that for cursor #3, at least one, maybe two or more objects are being read one block at a time. This could be caused by an initialization parameter problem.

    User71408, please post the output of the following two SQL statements - they will likely need to be run as a DBA user in the database. The goal is to determine the object being read one block at a time:
    SELECT
      DE.OWNER,
      DE.SEGMENT_NAME,
      DE.SEGMENT_TYPE,
      DE.TABLESPACE_NAME,
      DE.BLOCK_ID,
      DE.BLOCKS
    FROM
      DBA_EXTENTS DE
    WHERE
      DE.FILE_ID= 401
      AND 30207 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS-1;

    SELECT
      DE.OWNER,
      DE.SEGMENT_NAME,
      DE.SEGMENT_TYPE,
      DE.TABLESPACE_NAME,
      DE.BLOCK_ID,
      DE.BLOCKS
    FROM
      DBA_EXTENTS DE
    WHERE
      DE.FILE_ID= 354
      AND 128884 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS-1;
    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 91. Re: query tuning
    Niall Litchfield Journeyer
    Currently Being Moderated
    What if, as I was attempting to imply in the previous
    post, that the OP's file system does not even contain
    SCSI drives, but instead uses drives that do not
    support native command queuing, as would be the case
    for most SATA and IDE drives. Worse yet, what if the
    multiple drives were on the same IDE channel - only
    one IDE drive on a channel can be active at a time.
    The point is that we do not know if the OP has 401+
    files on a single drive, an IDE master/slave chain
    of drives, a 20 drive SCSI RAID 10 array, a SAN with
    200 drives, or something else. All that we know is
    that single block reads seem to be taking a long
    time.
    Whilst I admire your caution Charles, it's rather difficult to postulate any disk technology that will take over an hour to read a single block! It's also unclear how to reconcile this with the elapsed time for the entire query as originally stated at 45 minutes.

    Niall Litchfield
    http://www.orawin.info/
  • 92. Re: query tuning
    CharlesHooper Expert
    Currently Being Moderated
    Whilst I admire your caution Charles, it's rather
    difficult to postulate any disk technology
    that will take over an hour to read a single block!
    It's also unclear how to reconcile this with the
    elapsed time for the entire query as originally
    stated at 45 minutes.

    Niall Litchfield
    http://www.orawin.info/
    <humor>RAID 10 floppy array... quick find that other set of floppy disks so that the computer can read the next block... </humor>

    There is a possible explanation for the 4179109066us time for the SQL*Net message from client wait and the 4063882583us time for the db file sequential read. The clue is found on page 148 of "Optimizing Oracle Performance" in the pseudo-code for procedure wevent. What if the value of ela1 is less than ela0 due to a timer resolution problem with gettimeofday? If I recall correctly, I believe that it is possible that the operating system does not check the hardware clock on every time call (on some platforms), so it is theoretically possible that the operating system occasionally corrects its "software" clock with the hardware clock (on some Windows systems, this drift may not be corrected until a reboot is performed, or until the time is synchronized with the domain).

    This is just a guess, but a little mathematics with powers of 2, assuming that the OP is using a 32 bit system:
    4294967296us = 2^32us = 32 bit unsigned integer max value
    4179109066us = OP's long SQL*Net time
    4063882583us = OP's long single block read time

    Browsing through Metalink finds a couple documents that may be of interest (yes these are old, but there may be others), that might identify other causes:
    Bug No. 777326 USING POST WAIT THE CPU TIME STATISTICS ARE COMPLETELY WRONG
    Bug No. 1704769 V$SESSION_WAIT.SECONDS_IN_WAIT SHOWS IMPOSSIBLY HIGH VALUES
    Bug No. 2717586 UNREALISTIC HUGE VALUES IN V$SYSTEM_EVENT AND V$SESSION_EVENT

    It is, however, odd that the tim= values were not thrown off in the same manner in that portion of the trace file.

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 93. Re: query tuning
    26741 Oracle ACE
    Currently Being Moderated
    <humor>RAID 10 floppy array... quick find that other set of floppy disks so that the computer can read the next block... </humor>
    LOL ! Good one !

    I'd rather not go into the discussion of hard disks, platters, seek/access time,
    striping etc. We just do not know enough of the OP's OS and Hardware.

    As for the timing being wrong, yes these could be a timer resolution issue.
    So, maybe, we should ignore the timings and concentrate on the execution plan
    and consistent gets counts.

    Why do we have so many 'db file sequential read' waits when we see no
    Index Access or Indexe Range||Unique Scan ? Good question !
    Index Fast Full Scan would be 'db file scattered reads' if they have to go out
    to fetch blocks not present in the buffer cache.
    Also, as you rightly point out, some of the multiblock reads can break up into
    single block reads when oracle finds that some or many of the blocks are already
    present in the buffer cache.
    Another issue is that 'db file sequential read' waits can occur when Oracle has
    to read a segment header -- which it does do frequently when looking for the
    free block list. I'm not sure if the temporary segment extent allocations for the
    hash joins which may be overflowing to disk (multipass joins) would manifest
    as 'db file sequential read' waits. {the normal temporary segment operations
    would be 'direct path read' and 'direct path write' events}. But, still, we seem
    to have too many 'db file sequential read' waits and the segments of the trace file
    that the OP has posted do not indicate temporary segments but table/index
    blocks.
    It would be good to take those fileid+blockid for the 'db file sequential read' waits
    and see which segments they really belong to.
  • 94. Re: query tuning
    599921 Newbie
    Currently Being Moderated
    Charles,

    From the above 2 queries it returns " NO rows Returened" as O/P.
  • 95. Re: query tuning
    26741 Oracle ACE
    Currently Being Moderated
    select f.file_id, t.tablespace_name, extent_management, allocation_type
    from dba_data_files f, dba_tablespaces t
    where f.file_id in (401,354)
    and f.tablespace_name=t.tabespace_name
  • 96. Re: query tuning
    599921 Newbie
    Currently Being Moderated
    Hemanth,
    Here is the o/p for ur query.
    FILE_ID  TABLESPACE_NAME  EXTENT_MANAGEMENT ALLOCATION_TYPE
    ----------------------------------------------------------------------------------------------------------------------
    354         UNDOTBS2                  LOCAL                            SYSTEM
    401         UNDOTBS2                  LOCAL                            SYSTEM
    thank you

    Message was edited by:
    User71408
  • 97. Re: query tuning
    599921 Newbie
    Currently Being Moderated
    I gave query as follows.
    SELECT  DE.OWNER,  DE.SEGMENT_NAME,  DE.SEGMENT_TYPE,
    DE.TABLESPACE_NAME,  DE.BLOCK_ID,  DE.BLOCKS FROM  DBA_EXTENTS DE WHERE  DE.FILE_ID in(354,401)
    o/p
    OWNER SEGMENT_NAME      SEGMENT_TYPE      TABLESPACE_NAME   BLOCK_ID    BLOCKS
    
     
    
    SYS   _SYSSMU1$   TYPE2 UNDO  UNDOTBS2    114   7
    
    SYS   _SYSSMU1$   TYPE2 UNDO  UNDOTBS2    57    8
    
    SYS   _SYSSMU2$   TYPE2 UNDO  UNDOTBS2    130   7
    
    SYS   _SYSSMU2$   TYPE2 UNDO  UNDOTBS2    105   8
    
    SYS   _SYSSMU2$   TYPE2 UNDO  UNDOTBS2    65    8
    
    SYS   _SYSSMU3$   TYPE2 UNDO  UNDOTBS2    162   7
    ..
    total 86 rows were displayed.
    Message was edited by:
    User71408
  • 98. Re: query tuning
    26741 Oracle ACE
    Currently Being Moderated
    Read Consistency !

    The underlying tables are being updated while your query is running (ie
    a) Updates begun but not COMMITted before the query started
    b) Updates begun while the query was running and the query needed to read
    blocks which had been updated).

    That is where the 'db file sequential read' waits are coming from -- reading from
    the Undo Segments.

    Is there any chance that you can test the query when there are no or very few
    updates on the underlying tables -- during a quiet period ?
  • 99. Re: query tuning
    599921 Newbie
    Currently Being Moderated
    am sorry . That's not possible...It's daily job..
  • 100. Re: query tuning
    26741 Oracle ACE
    Currently Being Moderated
    Before and after you run the query get the values for
    "data blocks consistent reads - undo records applied" and "transaction tables consistent reads - undo records applied" from v$sesstat. The difference in
    the after and before values indicates the effort that Oracle undertook for read
    conssistency.
  • 101. Re: query tuning
    599921 Newbie
    Currently Being Moderated
    Hemanth,
    I got an idea.Could u pls tell me is it helpful or not.
    I just want to solve this using Multi_Thread concept with XML.I have added the same query which is having the conditions " pack_ind='Y'......total query
    and using the partitions concept.
    then it's taking 38 mins time
    Is it right way to solve this.if correct please guide me in this way also....

    Thank you
  • 102. Re: query tuning
    26741 Oracle ACE
    Currently Being Moderated
    I have NO idea what "Multi_Thread concept with XML" is. I can't help you there.
  • 103. Re: query tuning
    599921 Newbie
    Currently Being Moderated
    Ok.Hemanth.But please guide me in the previous way.. what we did yet...
    I will do it in the previous way...

    Thank you
  • 104. Re: query tuning
    277993 Journeyer
    Currently Being Moderated
    Some questions:

    1. Have you bothered reviewing the desgn of these tables and query (based on your requirements). I have noticed your query is doing CARTESIAN joins and FULL table scans. Are you using all the required conditions or are you using too much that is not required?
    2. How many rows are in each of these tables and how often do they increase or decrease?
    3. Have you gathered new table stats on these tables to reflect the changes? For instance if they have increased or decreased by up to 10%.
    4. Have you tried creating a function-based index on the NVL (CLOSE_DATE,xxx) to see how it will improve the query performance.

    e.g

    CREATE INDEX NVL_CLOSE_DATE_WH ON WAREHOUSE (NVL(CLOSE_DATE,'04-APR-9999'));

    Sorry I cannot go through all the previous posts to see if the answers are there because it is a lot, but if you can answer these questions, that will be excellent.
1 4 5 6 7 8 Previous Next