5 Replies Latest reply: Aug 23, 2013 3:25 AM by user12103911 RSS

    Temporary LOBs - bad performance when nocache is used

    user12103911

      Hello.

       

      Please, advise me what could be the reason of bad performance row 8 from the next anonymous block:

       

      declare
          i integer;
          c clob := 'c';
          procedure LTrimSys(InCLOB in clob ) is
              OutCLOB clob;
          begin
              DBMS_LOB.CREATETEMPORARY(OutCLOB, false, DBMS_LOB.call);
              dbms_lob.Copy(OutCLOB, InCLOB, dbms_lob.getlength(InCLOB));
              DBMS_LOB.freetemporary(OutCLOB);
          end;
      begin
          for j in 1 .. 1000 loop
              LTrimSys(c);
          end loop;
      end;
      
      

       

      I have two practically identical databases 10.2.0.4.0 EE 64-bit on Windows

      On first DB I have elapsed time: 4 sec, on second - 0.2 sec

      I didn't find important difference between init parameters (hidden parameters too).

      First DB has more memory (PGA) then second.

       

      Main time events in time of executing anonymous block on first DB are

      PL/SQL execution elapsed time

      DB CPU

      sql execute elapsed time

      DB time

      In second DB - the same but much less

       

      If I use caching of temporary LOBs then both DBs work fine, but I can not understand why first DB works slowly when I use nocache temporary LOBs.

      What can be the reason?

        • 1. Re: Temporary LOBs - bad performance when nocache is used
          user12103911

          Here is example of events from 10.2.0.4 (on linux)

           

          ------------------------------------------------------------------------------------------------------------------------------------------------------

          TYPE, STATISTIC                                                 ,         DELTA, HDELTA/SEC,    %TIME, GRAPH       , NUM_WAITS,  WAITS/SEC,   AVERAGES

          ------------------------------------------------------------------------------------------------------------------------------------------------------

          STAT, physical write total IO requests                          ,          1000,        200,         ,             ,          ,           ,

          STAT, db block gets direct                                      ,          1000,        200,         ,             ,          ,           ,

          STAT, consistent gets                                           ,          1000,        200,         ,             ,          ,           ,

          STAT, consistent gets from cache                                ,          1000,        200,         ,             ,          ,           ,

          STAT, physical writes                                           ,          1000,        200,         ,             ,          ,           ,

          STAT, physical writes direct                                    ,          1000,        200,         ,             ,          ,           ,

          STAT, physical write IO requests                                ,          1000,        200,         ,             ,          ,           ,

          STAT, physical writes direct temporary tablespace               ,          1000,        200,         ,             ,          ,           ,

          TIME, hard parse elapsed time                                   ,          4320,      864us,      .1%, [          ],          ,           ,

          TIME, PL/SQL compilation elapsed time                           ,          3571,    714.2us,      .1%, [          ],          ,           ,

          TIME, parse time elapsed                                        ,          5193,     1.04ms,      .1%, [          ],          ,           ,

          TIME, PL/SQL execution elapsed time                             ,       4599468,   919.89ms,    92.0%, [##########],          ,           ,

          TIME, DB CPU                                                    ,        171940,    34.39ms,     3.4%, [@         ],          ,           ,

          TIME, sql execute elapsed time                                  ,       4599832,   919.97ms,    92.0%, [##########],          ,           ,

          TIME, DB time                                                   ,       4605395,   921.08ms,    92.1%, [##########],          ,           ,

          WAIT, direct path write temp                                    ,          5755,     1.15ms,      .1%, [          ],      1000,        200,     5.76us

          WAIT, SQL*Net message to client                                 ,            15,        3us,      .0%, [          ],         7,        1.4,     2.14us

          WAIT, SQL*Net message from client                               ,        409871,    81.97ms,     8.2%, [W         ],         7,        1.4,    58.55ms


          Other DBs, for example, 9.2.0.6 on windows works much faster

          • 2. Re: Temporary LOBs - bad performance when nocache is used
            botang(唐波)

            Please post your v$pgastat, V$PGA_TARGET_ADVICE_HISTOGRAM

            to see if there are lots of multipass pga access.

            • 3. Re: Temporary LOBs - bad performance when nocache is used
              jgarry

              See slides 30 and 32: Oracle LOB Internals and Performance Tuning

               

              I'm guessing you'll see waits because of the server process having to write the whole chunk and wait until it is done.  With caching you don't have to wait.  But maybe nologging would help.

               

              Edit:  If you would rather know than guess, trace and see Charles' comments here: direct path write temp wait event on temp lob

              • 4. Re: Temporary LOBs - bad performance when nocache is used
                user12103911

                Thanks for answer, but I saw all these links before.

                 

                > See slides 30 and 32: Oracle LOB Internals and Performance Tuning

                This applies to usual column LOBs, I use temporary LOBs, which nologging by default.

                 

                > direct path write temp wait event on temp lob

                As you can see in my example events, I have no problem with direct path write temp wait event - 5.76us for each run.

                • 5. Re: Temporary LOBs - bad performance when nocache is used
                  user12103911

                  I don't think that is the problem. See next outputs:

                   

                  select * from V$PGASTAT order by name

                   

                  NAMEVALUEUNIT
                  PGA memory freed back to OS49016834031616bytes
                  aggregate PGA auto target170893312bytes
                  aggregate PGA target parameter1073741824bytes
                  bytes processed95760297282560bytes
                  cache hit percentage93,43percent
                  extra bytes read/written6724614496256bytes
                  global memory bound107366400bytes
                  max processes count115
                  maximum PGA allocated2431493120bytes
                  maximum PGA used for auto workareas372516864bytes
                  maximum PGA used for manual workareas531456bytes
                  over allocation count102639421
                  process count57
                  recompute count (total)117197176
                  total PGA allocated1042407424bytes
                  total PGA inuse879794176bytes
                  total PGA used for auto workareas757760bytes
                  total PGA used for manual workareas0bytes
                  total freeable PGA memory75694080bytes

                   

                  select * from V$PGA_TARGET_ADVICE_HISTOGRAM where PGA_TARGET_FACTOR = 1

                   

                  PGA_TARGET_FOR_ESTIMATEPGA_TARGET_FACTORADVICE_STATUSLOW_OPTIMAL_SIZEHIGH_OPTIMAL_SIZEESTD_OPTIMAL_EXECUTIONSESTD_ONEPASS_EXECUTIONSESTD_MULTIPASSES_EXECUTIONSESTD_TOTAL_EXECUTIONSIGNORED_WORKAREAS_COUNT
                  10737418241ON2199023255552439804651007900000
                  10737418241ON1099511627776219902325555100000
                  10737418241ON549755813888109951162777500000
                  10737418241ON27487790694454975581388700000
                  10737418241ON13743895347227487790694300000
                  10737418241ON6871947673613743895347100000
                  10737418241ON343597383686871947673500000
                  10737418241ON171798691843435973836700000
                  10737418241ON85899345921717986918300000
                  10737418241ON4294967296858993459100000
                  10737418241ON2147483648429496729500000
                  10737418241ON1073741824214748364700000
                  10737418241ON536870912107374182300000
                  10737418241ON26843545653687091100000
                  10737418241ON134217728268435455037603760
                  10737418241ON6710886413421772700000
                  10737418241ON335544326710886300000
                  10737418241ON167772163355443110010
                  10737418241ON83886081677721510145450101900
                  10737418241ON4194304838860720518210205390
                  10737418241ON20971524194303832108330
                  10737418241ON104857620971514244000424400
                  10737418241ON5242881048575393113703931200
                  10737418241ON2621445242871012220101240
                  10737418241ON1310722621432271200227120
                  10737418241ON65536131071110215001102150
                  10737418241ON327686553500000
                  10737418241ON163843276700000
                  10737418241ON81921638300000
                  10737418241ON4096819100000
                  10737418241ON204840958340961800834096180
                  10737418241ON1024204700000
                  10737418241ON0102300000

                   

                  SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc,

                         onepass_count, round(onepass_count*100/total, 2) onepass_perc,

                         multipass_count, round(multipass_count*100/total, 2) multipass_perc

                  FROM

                         (SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total,

                                 sum(OPTIMAL_EXECUTIONS) optimal_count,

                                 sum(ONEPASS_EXECUTIONS) onepass_count,

                                 sum(MULTIPASSES_EXECUTIONS) multipass_count

                          FROM   v$sql_workarea_histogram);

                   

                  OPTIMAL_COUNTOPTIMAL_PERCONEPASS_COUNTONEPASS_PERCMULTIPASS_COUNTMULTIPASS_PERC
                  12181507016100146042000