10 Replies Latest reply: Dec 6, 2012 6:31 PM by yingkuan RSS

    Single block read for Sort

    673580
      I’m creating a temp table with following SQL. Table temp2 is 50GB in size with 120M rows. I noticed during SORT operation , it’s performing lots of single block reads on Temp tablespace. Why it’s doing single block reads on temp??

      Also, Is there any other way to make this quicker?

      I'm on 10gR2 with Auto PGA/SGA. PGA Aggregate is 5GB and SGA is 12GB.
      create table t_temp nologging parallel(degree 4 )
       as
       select /*+ parallel(t1,4) */ * from temp2 partition(P2008)  t1
       order by custkey 
      
      
          SID Wait State    EVENT                               P1                         P2              P3                    SEQ# % TotTime TEventTime(ms)  DistEvnts Avgtime(ms)/Evnt
      ------- ------------- ----------------------------------- -------------------------- --------------- --------------- ---------- --------- -------------- ---------- ----------------
      
         2165 WAITING       direct path read temp               file number= 5001          first dba=      block cnt= 1                     .02           .253          1             .253
                                                                                           88791
      
         2165 WAITING       direct path read temp               file number= 5001          first dba=      block cnt= 1                     .02           .253          1             .253
                                                                                           412771
      
         2165 WAITING       direct path read temp               file number= 5001          first dba=      block cnt= 1                     .02           .253          1             .253
                                                                                           421465
      
         2165 WAITING       direct path read temp               file number= 5001          first dba=      block cnt= 1                     .02           .253          1             .253
                                                                                           691141
      
         2165 WAITING       direct path read temp               file number= 5001          first dba=      block cnt= 1                     .02           .253          1             .253
                                                                                           1295425
      
      Here is  Temp table space properties…
      
      Name                    INITK    NEXTK MINEX MAXEX PCTI CNTS      EXMGMT     ALOCTYPE  SEGMGM STATUS
      -------------------- -------- -------- ----- ----- ---- --------- ---------- --------- ------ --------
      TEMP                     1024     1024     1          0 TEMPORARY LOCAL      UNIFORM   MANUAL ONLINE
      Edited by: user4529833 on Feb 5, 2009 9:05 AM
        • 1. Re: Single block read for Sort
          Timur Akhmadeev
          There are two different ways [direct path reads temp|http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm#sthref729] are performed: asynchronous (if IO subsystem permits) and synchronous. In your case it seems that direct path reads are asynchronous IO requests (because wait time is less than 1 ms), meaning that IO is overlapped with effective work on CPU. In that case, IMHO, Oracle considers that there is no need to have huge IO reads, because it can perform sorting effectively.
          There is hidden [_sort_multiblock_read_count|http://www.orafaq.com/parms/parm1901.htm] which probably should instruct Oracle to use multiblock IO for sorts, but, AFAIR my tests with PX & sort have not confirmed this. All reads during sort were single-block reads, regardless of setting sortmultiblock_read_count.
          What happens in synchronous IO mode I don't know...
          • 2. Re: Single block read for Sort
            Timur Akhmadeev
            I've run a test on 11.1.0.7 on Win XP SP3, and I see some strange results:
            sortmultiblock_read_count=1 => all 'direct path read temp' are single-block reads
            sortmultiblock_read_count=16 => almost all 'direct path read temp' are single-block reads, but there are some 4, 6, 8, 12, 13, 14, 15, 16, 17 and 18 block reads, but there number is fairly small compared to single-block reads. ALso, 18-block reads are the most frequent among non-single block reads.
            With PX situation remains the same.
            I used this as a test:
            alter session set workarea_size_policy=manual;
            alter session set sort_area_size=600000;
            alter session set "_sort_multiblock_read_count"=1;
            drop table tmp cascade constraints purge;
            create table tmp as select * from dba_objects;
            insert into tmp select * from tmp;
            /
            /
            /
            drop table tmp2 cascade constraints purge;
            drop table tmp3 cascade constraints purge;
            alter system checkpoint;
            alter session set events '10046 trace name context forever, level 8';
            create table tmp2 nologging as select * from tmp order by created,object_name desc;
            alter session set "_sort_multiblock_read_count"=16;
            create table tmp3 nologging as select * from tmp order by created,object_name desc;
            alter session set events '10046 trace name context off';
            This is example of raw trace output:
            WAIT #10: nam='direct path read temp' ela= 6 file number=201 first dba=96989 block cnt=1 obj#=68752 tim=429961557791
            WAIT #10: nam='direct path read temp' ela= 6 file number=201 first dba=97460 block cnt=1 obj#=68752 tim=429961558818
            WAIT #10: nam='direct path read temp' ela= 5 file number=201 first dba=97008 block cnt=1 obj#=68752 tim=429961559788
            WAIT #10: nam='direct path read temp' ela= 6 file number=201 first dba=97775 block cnt=2 obj#=68752 tim=429961561431
            WAIT #10: nam='direct path read temp' ela= 5 file number=201 first dba=98508 block cnt=18 obj#=68752 tim=429961561814
            WAIT #10: nam='direct path read temp' ela= 7 file number=201 first dba=98174 block cnt=18 obj#=68752 tim=429961562566
            WAIT #10: nam='direct path read temp' ela= 6 file number=201 first dba=97795 block cnt=1 obj#=68752 tim=429961564803
            WAIT #10: nam='direct path read temp' ela= 5 file number=201 first dba=98129 block cnt=1 obj#=68752 tim=429961565765
            WAIT #10: nam='direct path read temp' ela= 7 file number=201 first dba=98148 block cnt=1 obj#=68752 tim=429961567069
            • 3. Re: Single block read for Sort
              673580
              Thanks Timur..

              One guess(wild one) comes to mind is that, oracle putting all the columns ordered in TEMP segment with rowids and then reading one rowid and then get the row from table and return as sort output. In this case , oracle has no option but to do single block I/O..
              • 4. Re: Single block read for Sort
                601585
                Metalink doc# 4417415 reports similar problem.
                We were also under similar situation in one of our customer sites.
                (But not sure that this is your case)

                The solution you'd like to confirm yourself might be

                - Use manual work area size policy

                - Give big enough sort area and/or sort area retained size.



                ================================
                Dion Cho - Oracle Performance Storyteller

                http://dioncho.wordpress.com (english)
                http://ukja.tistory.com (korean)
                ================================

                Edited by: Dion_Cho on Feb 5, 2009 8:53 PM
                Typo
                • 5. Re: Single block read for Sort
                  673580
                  Thanks Dion! That's very interesting note..

                  However, I don't think solution(using manual PGA) suggested there is the best one...

                  here are lots of advantages of automatic PGA and can't it be tuned off.. I agree that certain big jobs ( if you have just few ) you can choose to turn off automatic PGA but if you have quite a few SQLs that does large sorts/hash joins ( very typical in large DW envrionment ) , this solution does not make much sense..

                  There ought to be better solution than this one.. Anyone else come accorss this??
                  • 6. Re: Single block read for Sort
                    Jonathan Lewis
                    If you need to collect more information about what's happening, you could enable events 10032 and 10033 with an "alter session" command.

                    It's possible that what you're seeing is an unexpected side-effect of the asynchronous I/O that Timur has mentioned, and the 10033 particularly might highlight this: what if you are doing lots of direct path reads in the asynchronous (overlapped) fashion but - for reasons of the way that Oracle reads back sort runs - have to do single block reads some of the time. It may be that the single block reads frequently become visible, but the multiblock reads don't. The way to check this is to compare the total blocks reported in the 10046 trace against the blocks read according to the 10033 trace - you'll have to read the trace and count the 'direct path reads' between lines like 'Merging run at 26a490 for 192 blocks '


                    Regards
                    Jonathan Lewis
                    http://jonathanlewis.wordpress.com
                    http://www.jlcomp.demon.co.uk

                    "Science is more than a body of knowledge; it is a way of thinking"
                    Carl Sagan

                    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
                    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
                    fixed format
                    .                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                    • 7. Re: Single block read for Sort
                      673580
                      Thanks Jonathan.

                      I will do that and post the results... One questions though, how to correct this unexpected side-effect of asynchronous reads... ( Assuming it's the case )

                      how does oracle perform sort for SQLs I have given..

                      1) read & sort the data for custid coulmn with rowids and dump it temp (quite likely)
                      2) read the rowids from temp and start loading row to temp table..
                      • 8. Re: Single block read for Sort
                        Jonathan Lewis
                        user4529833 wrote:
                        Thanks Jonathan.

                        I will do that and post the results... One questions though, how to correct this unexpected side-effect of asynchronous reads... ( Assuming it's the case )
                        If the assumption is correct, then it's not really something that needs to be corrected. Because of the overlapping of I/Os, there may be lots of I/Os that never make it into wait time, so a single process could be going:
                        give me 12 blocks
                        give me 12 blocks
                        give me 12 blocks
                        give me 12 blocks
                        give me a block
                        of which you see only the "give me a block".

                        That's why the 10033 is an important diagnostic - it will help you decide whether or not "everything" is a single block read, or whether the single block reads are a very tiny fraction of the total I/O that also happens to become visible for some reason.
                        how does oracle perform sort for SQLs I have given..

                        1) read & sort the data for custid coulmn with rowids and dump it temp (quite likely)
                        2) read the rowids from temp and start loading row to temp table..
                        I'd have to check the execution to give you a definite answer but the basic strategy would be:
                        <ul>
                        Two sets of PX slaves.

                        Set one reads a chunk of the source table picking up all the relevant columns for the result table.

                        Because of the ORDER BY clause layer one does a range-based distribution of data to the second set of slaves - along the lines of "slave 1 gets custid less than 1M", "slave 2 gets custid between 1M and 2M" and so on. Because entire rows are passed between the two sets of slaves, you can see a LOT of PQ traffic going on. (People sometimes increase the "PX message size" parameter because of the volume of traffic and the size of the rows).

                        Set two slaves accumulate, and sort the incoming data sets. Each sorted row carries ALL the columns, there is no "sort the custkey/rowids only" strategy. The sorting may spill to temp - given your "select *", the amount of temp space used may actually be greater than the size of the source table.

                        Each set two slave creates its own "private table" with its results.

                        The query coordinator links the "private table" metadata in the data dictionay in the right order to create the final table.
                        </ul>


                        Regards
                        Jonathan Lewis
                        http://jonathanlewis.wordpress.com
                        http://www.jlcomp.demon.co.uk

                        "Science is more than a body of knowledge; it is a way of thinking"
                        Carl Sagan

                        To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
                        {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
                        fixed format
                        .                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                        • 9. Re: Single block read for Sort
                          Alex Haralampiev
                          One more thing to consider:

                          Recently we changed db_file_multiblock_read_count in our DW (10.2.0.4 on x64_86 RedHat 2.6.9-55.ELsmp) from 8 to 64 (db_block_size =16K) for 1Mb reads and while that improved significantly our scattered reads it also increased the time spent on direct path read/write temp. On Metalink I have found Doc ID: 330818.1 <I>Increasing db_file_multiblock_read_count Results In Slower Performance Due To More & Smaller Direct Path Waits</I> which might be helpful for your case too. By implementing the ideas described there we improved additionally our large MV create and refresh operations by ~20% in Overall Response time and 50-60% improvement in time for direct path read/write temp write operations.

                          In summary this Doc suggests change of two hidden parameters:
                          1. Set the following parameters in init.ora/spfile for instance-wide effect
                          _smm_auto_max_io_size = 248
                          _smm_auto_min_io_size = 248
                          
                          OR
                          
                          2. At the session level, set the parameters using
                          
                          SQL> alter session set "_smm_auto_max_io_size" = 248;
                          SQL> alter session set "_smm_auto_min_io_size" = 248;
                          
                          Note: from Bug 4047116, the value 248 can be used when I/O size is 256Kb 
                          whereas 1008 can be used when I/O size is 1Mb. Both the min and the 
                          max parameter must be set to the same value.
                          The following is from 10046 traces of complete refresh of large MV with the default settings of these two parameters and after their change to 1008:

                          {color:red}Default Parameter Values
                          smmauto_max_io_size=240
                          smmauto_min_io_size=48
                          {color}
                          Event Name                Non-Idle     Times      Average     Max      Blocks   Average
                                                    Wait Time    Waited    Wait Time  Wait Time            Blocks
                                                     [sec]      Non-Idle                                  
                          direct path read temp:     4,952      2,470,280   0.0020     1.318    4,717,970    1.9
                          direct path write temp:    1,314        102,507   0.0128    11.517    1,055,908   10.3
                          . . .                                 
                          Sub Total:                 9,644      4,203,249        
                          Total Response Time:      11,572        
                          {color:blue}Modified Parameter Values
                          smmauto_max_io_size=1008
                          smmauto_min_io_size=1008
                          {color}
                          Event Name                Non-Idle     Times      Average     Max      Blocks   Average
                                                    Wait Time    Waited    Wait Time  Wait Time            Blocks
                                                     [sec]      Non-Idle                                  
                          direct path read temp:      2,592    1,382,066    0.0018    1.266     5,386,974   3.9
                          direct path write temp:       130       17,672    0.0073    1.146     1,111,967   62.9
                          . . .            
                          Sub Total:                  7,181   3,089,017        
                          Total Response Time:        9,093 sec       
                          As you can see in our tests the number of calls was significantly reduced as the average number of blocks per call went up. If you have a chance to test these settings on your system please let us know if they will reduce the number of "1 block per call" operations.

                          Rgds,

                          Alex Haralampiev, Ph.D, M.Eng, OCP, TCP
                          Data Warehouse Architect
                          HD Supply, Inc.

                          Edited by: user5543678 on Sep 25, 2009 1:57 PM
                          • 10. Re: Single block read for Sort
                            yingkuan
                            Sorry to dig up this ancient thread.
                            Just want to throw in that based my testing, sortmultiblock_read_count doesn't work on our 11.2.0.3 DB.
                            Alex's suggest smmauto_max_io_size worked.
                            By the way, the unit of these parameter is bytes not block.
                            SQL> alter session set "_smm_auto_max_io_size" = 248;
                            SQL> alter session set "_smm_auto_min_io_size" = 248;
                            Edited by: yingkuan on Dec 6, 2012 4:31 PM