This discussion is archived
5 Replies Latest reply: Aug 23, 2013 1:25 AM by user12103911 RSS

Temporary LOBs - bad performance when nocache is used

user12103911 Newbie
Currently Being Moderated

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 Newbie
    Currently Being Moderated

    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(唐波) Explorer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points