1 4 5 6 7 8 Previous Next 117 Replies Latest reply: May 5, 2008 12:19 PM by 181444 Go to original post RSS
      • 90. Re: query tuning
        Charles Hooper
        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
          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
            Charles Hooper
            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
              <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
                Charles,

                From the above 2 queries it returns " NO rows Returened" as O/P.
                • 95. Re: query tuning
                  26741
                  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
                    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
                      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
                        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
                          am sorry . That's not possible...It's daily job..
                          • 100. Re: query tuning
                            26741
                            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
                              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
                                I have NO idea what "Multi_Thread concept with XML" is. I can't help you there.
                                • 103. Re: query tuning
                                  599921
                                  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
                                    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