slow inserts - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 34

Thread: slow inserts

  1. #11
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You cannot compare DB2 performance with Oracle.

    Also, an application developed against one particular RDBMS should NOT be deployed against with another RDBMS.

    If the application commits after every row insert, then definitely Oracle is going to run slowly.

    Finally, if you use 9i, You can increase _bump_highwater_mark_count to higher value in your test system. I think it defaults to 5. This parameter tells Oracle how many blocks should be allocated by advancing HW per freelist.
    Also, consult with Oracle support before you change in PROD env.

    Tamil

  2. #12
    Join Date
    Jan 2006
    Posts
    23
    Changing pctfree = 15 brought me a little bit lower insert rate - 4%, but may be it's not related only to pctfree - just such stage after 10mln of records inserted.
    Tested also initran = although I do not have too many updates, the number of enq: TX - index contention increases if I drop them to 40.
    Calculating hwm and changing it up will allocate a lot of space per segment - I have 140 freelists x 50 for hwm = 7000 blocks...
    Let me try and I'll post my results.
    Please, advise what other changes I could test.
    Thanks a lot,mj

  3. #13
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by mjschwenger
    Changing pctfree = 15 brought me a little bit lower insert rate - 4%, but may be it's not related only to pctfree - just such stage after 10mln of records inserted.
    Tested also initran = although I do not have too many updates, the number of enq: TX - index contention increases if I drop them to 40.
    Calculating hwm and changing it up will allocate a lot of space per segment - I have 140 freelists x 50 for hwm = 7000 blocks...
    Let me try and I'll post my results.
    Please, advise what other changes I could test.
    Thanks a lot,mj
    Post the full statspack report taken 5 min interval here.

    Tamil

  4. #14
    Join Date
    Jan 2006
    Posts
    23

    1st part

    Thanks a lot for your help - here is the snap - about 10 min long, mj
    [PHP]
    STATSPACK report for

    DB Name DB Id Instance Inst Num Release RAC Host
    ------------ ----------- ------------ -------- ----------- --- ----------------
    ORAPERF 1029455018 oraperf 1 10.1.0.4.0 NO aixperfdb

    Snap Id Snap Time Sessions Curs/Sess Comment
    --------- ------------------ -------- --------- -------------------
    Begin Snap: 720 11-Jan-06 15:40:54 65 679.0
    End Snap: 721 11-Jan-06 15:49:54 65 683.1
    Elapsed: 9.00 (mins)

    Cache Sizes (end)
    ~~~~~~~~~~~~~~~~~
    Buffer Cache: 5,312M Std Block Size: 4K
    Shared Pool Size: 960M Log Buffer: 195,313K

    Load Profile
    ~~~~~~~~~~~~ Per Second Per Transaction
    --------------- ---------------
    Redo size: 1,562,471.84 36,845.92
    Logical reads: 28,916.59 681.91
    Block changes: 10,014.51 236.16
    Physical reads: 2,762.25 65.14
    Physical writes: 1,298.34 30.62
    User calls: 7,950.90 187.50
    Parses: 702.14 16.56
    Hard parses: 0.01 0.00
    Sorts: 3.25 0.08
    Logons: 0.03 0.00
    Executes: 4,119.29 97.14
    Transactions: 42.41

    % Blocks changed per Read: 34.63 Recursive Call %: 9.94
    Rollback per transaction %: 0.00 Rows per Sort: 22.07

    Instance Efficiency Percentages (Target 100%)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Buffer Nowait %: 99.95 Redo NoWait %: 100.00
    Buffer Hit %: 90.45 In-memory Sort %: 100.00
    Library Hit %: 100.01 Soft Parse %: 100.00
    Execute to Parse %: 82.95 Latch Hit %: 99.81
    Parse CPU to Parse Elapsd %: 92.63 % Non-Parse CPU: 96.82

    Shared Pool Statistics Begin End
    ------ ------
    Memory Usage %: 40.08 40.10
    % SQL with executions>1: 53.63 53.71
    % Memory for SQL w/exec>1: 51.69 51.93

    Top 5 Timed Events
    ~~~~~~~~~~~~~~~~~~ % Total
    Event Waits Time (s) Call Time
    -------------------------------------------- ------------ ----------- ---------
    db file sequential read 1,491,521 15,412 86.02
    CPU time 1,079 6.02
    db file parallel write 47,953 946 5.28
    log file sync 23,929 231 1.29
    log file parallel write 43,225 177 .99
    -------------------------------------------------------------
    Wait Events DB/Inst: ORAPERF/oraperf Snaps: 720-721
    -> s - second
    -> cs - centisecond - 100th of a second
    -> ms - millisecond - 1000th of a second
    -> us - microsecond - 1000000th of a second
    -> ordered by wait time desc, waits desc (idle events last)

    Avg
    Total Wait wait Waits
    Event Waits Timeouts Time (s) (ms) /txn
    ---------------------------- ------------ ---------- ---------- ------ --------
    db file sequential read 1,491,521 0 15,412 10 65.1
    db file parallel write 47,953 0 946 20 2.1
    log file sync 23,929 0 231 10 1.0
    log file parallel write 43,225 0 177 4 1.9
    SQL*Net break/reset to clien 97,200 0 25 0 4.2
    control file sequential read 2,323 0 10 4 0.1
    class slave wait 2 2 10 4883 0.0
    Queue Monitor Task Wait 2 0 10 4883 0.0
    control file parallel write 230 0 6 24 0.0
    enq: TX - index contention 1,296 0 5 4 0.1
    latch: cache buffers chains 1,032 1,028 2 2 0.0
    buffer busy waits 7,045 0 1 0 0.3
    enq: TX - row lock contentio 1 0 1 1282 0.0
    latch free 204 183 1 3 0.0
    process startup 8 0 0 53 0.0
    latch: library cache 224 0 0 2 0.0
    read by other session 52 0 0 6 0.0
    control file single write 17 0 0 9 0.0
    db file scattered read 22 0 0 4 0.0
    rdbms ipc reply 121 0 0 1 0.0
    LGWR wait for redo copy 947 0 0 0 0.0
    latch: shared pool 35 0 0 1 0.0
    latch: library cache pin 27 0 0 1 0.0
    SQL*Net more data to client 198 0 0 0 0.0
    latch: row cache objects 7 0 0 1 0.0
    enq: SQ - contention 4 0 0 1 0.0
    latch: redo allocation 5 0 0 1 0.0
    enq: HW - contention 4 0 0 1 0.0
    latch: In memory undo latch 3 0 0 1 0.0
    latch: cache buffers lru cha 3 0 0 0 0.0
    buffer deadlock 165 165 0 0 0.0
    latch: enqueue hash chains 1 0 0 0 0.0
    SQL*Net message from client 2,188,940 0 4,012 2 95.6
    queue messages 109 109 532 4883 0.0
    virtual circuit status 18 18 527 29297 0.0
    Queue Monitor Wait 18 18 527 29297 0.0
    wakeup time manager 2 0 527 ###### 0.0
    jobq slave wait 173 170 500 2891 0.0
    SQL*Net message to client 2,188,933 0 4 0 95.6
    SQL*Net more data from clien 5 0 0 17 0.0
    -------------------------------------------------------------
    Background Wait Events DB/Inst: ORAPERF/oraperf Snaps: 720-721
    -> ordered by wait time desc, waits desc (idle events last)

    Avg
    Total Wait wait Waits
    Event Waits Timeouts Time (s) (ms) /txn
    ---------------------------- ------------ ---------- ---------- ------ --------
    db file parallel write 47,954 0 946 20 2.1
    log file parallel write 43,225 0 177 4 1.9
    Queue Monitor Task Wait 2 0 10 4883 0.0
    control file parallel write 234 0 6 24 0.0
    control file sequential read 90 0 1 7 0.0
    process startup 8 0 0 53 0.0
    db file sequential read 98 0 0 2 0.0
    control file single write 18 0 0 9 0.0
    db file scattered read 17 0 0 5 0.0
    rdbms ipc reply 121 0 0 1 0.0
    LGWR wait for redo copy 949 0 0 0 0.0
    latch: cache buffers chains 4 4 0 5 0.0
    latch: redo allocation 4 0 0 0 0.0
    latch: cache buffers lru cha 4 0 0 0 0.0
    buffer busy waits 2 0 0 0 0.0
    rdbms ipc message 23,385 2,612 6,292 269 1.0
    smon timer 54 0 635 11762 0.0
    Queue Monitor Wait 18 18 527 29297 0.0
    -------------------------------------------------------------

    PHP]

  5. #15
    Join Date
    Jan 2006
    Posts
    23

    part2

    PHP Code:
    wakeup time manager                     2          0        527 ######      0.0
    jobq slave wait                       173        170        500   2891      0.0
    SQL
    *Net message to client       2,188,933          0          4      0     95.6
    SQL
    *Net more data from clien            5          0          0     17      0.0
              
    -------------------------------------------------------------
    Background Wait Events  DB/InstORAPERF/oraperf  Snaps720-721
    -> ordered by wait time descwaits desc (idle events last)

                                                                       
    Avg
                                                         Total Wait   wait    Waits
    Event                               Waits   Timeouts   Time 
    (s)   (ms)     /txn
    ---------------------------- ------------ ---------- ---------- ------ --------
    db file parallel write             47,954          0        946     20      2.1
    log file parallel write            43
    ,225          0        177      4      1.9
    Queue Monitor Task Wait                 2          0         10   4883      0.0
    control file parallel write           234          0          6     24      0.0
    control file sequential read           90          0          1      7      0.0
    process startup                         8          0          0     53      0.0
    db file sequential read                98          0          0      2      0.0
    control file single write              18          0          0      9      0.0
    db file scattered read                 17          0          0      5      0.0
    rdbms ipc reply                       121          0          0      1      0.0
    LGWR wait 
    for redo copy               949          0          0      0      0.0
    latch
    cache buffers chains             4          4          0      5      0.0
    latch
    redo allocation                  4          0          0      0      0.0
    latch
    cache buffers lru cha            4          0          0      0      0.0
    buffer busy waits                       2          0          0      0      0.0
    rdbms ipc message                  23
    ,385      2,612      6,292    269      1.0
    smon timer                             54          0        635  11762      0.0
    Queue Monitor Wait                     18         18        527  29297      0.0
              
    -------------------------------------------------------------
    Wait Event Histogram  DB/InstORAPERF/oraperf  Snaps720-721
    -> ordered by event (idle events last)

    Event
    --------------------------------------------------
        
    1 ms     1 4 ms    4 8 ms    8 16 ms    16 32 ms      32ms
    ------------ ------------ ------------ ------------ ------------ ------------
    LGWR wait for redo copy
             944            3            0            0            0            0
    Queue Monitor Task Wait
               0            0            0            0            0            2
    SQL
    *Net break/reset to client
          95
    ,954        1,202           16            3            0            0
    SQL
    *Net more data to client
             198            0            0            0            0            0
    buffer busy waits
           6
    ,969           30           17           19            5            5
    buffer deadlock
             165            0            0            0            0            0
    class slave wait
               0            0            0            0            0            2
    control file parallel write
               0            0            0           16          212            2
    control file sequential read
             611          813          864           34            1            0
    control file single write
               0            0            6           11            0            0
    db file parallel write
           7
    ,356        4,102        6,199       11,964       10,434        7,895
    db file scattered read
               3            6           13            0            0            0
    db file sequential read
          10
    ,503      258,045      761,357      279,182      101,814       80,164
    enq
    HW contention
               3            1            0            0            0            0
    enq
    SQ contention
               2            2            0            0            0            0
    enq
    TX index contention
             909          140          104           67           43           30
    enq
    TX row lock contention
               0            0            0            0            0            1
    latch free
             141            9            0           54            0            0
    latch
    In memory undo latch
               3            0            0            0            0            0
    latch
    cache buffers chains
             809            0            0          223            0            0
    latch
    cache buffers lru chain
               3            0            0            0            0            0
    latch
    enqueue hash chains
               1            0            0            0            0            0
    latch
    library cache
             126           69           27            2            0            0
    latch
    library cache pin
              19            8            0            0            0            0
    latch
    redo allocation
               4            1            0            0            0            0
    latch
    row cache objects
               2            5            0            0            0            0
    latch
    shared pool
    Wait Event Histogram  DB
    /InstORAPERF/oraperf  Snaps720-721
    -> ordered by event (idle events last

  6. #16
    Join Date
    Jan 2006
    Posts
    23
    PHP Code:
    Event
    --------------------------------------------------
        
    1 ms     1 4 ms    4 8 ms    8 16 ms    16 32 ms      32ms
    ------------ ------------ ------------ ------------ ------------ ------------
              
    22           12            1            0            0            0
    log file parallel write
          21
    ,611          636       11,463        9,485           23            7
    log file sync
             399          329        7
    ,015       15,460          682           19
    process startup
               0            0            0            0            0            8
    rdbms ipc reply
             121            0            0            0            0            0
    read by other session
               9           13           22            5            3            0
    Queue Monitor Wait
               0            0            0            0            0           18
    SQL
    *Net message from client
       1
    ,643,818      501,854       16,306       17,491        7,499          866
    SQL
    *Net message to client
       2
    ,185,854            4            0            0            0            0
    SQL
    *Net more data from client
               4            0            0            0            0            1
    dispatcher timer
               0            0            0            0            0            9
    jobq slave wait
               0            0            0            0            0          173
    pmon timer
               2            0            0            0            0          182
    queue messages
               0            0            0            0            0          109
    rdbms ipc message
           5
    ,809        1,895        2,026        3,018        3,202        7,436
    smon timer
               0            0            0            2            1           51
    virtual circuit status
               0            0            0            0            0           18
    wakeup time manager
               0            0            0            0            0            2
              
    -------------------------------------------------------------

    SQL ordered by Gets  DB/InstORAPERF/oraperf  Snaps720-721
    -> Resources reported for PL/SQL code includes the resources used by all SQL
       statements called by the code
    .
    -> 
    End Buffer Gets Threshold:     10000 Total Buffer Gets:      15,614,958
    -> Captured SQL accounts for  101.5of Total Buffer Gets
    -> SQL reported below exceeded  1.0of Total Buffer Gets

                                                         CPU      Elapsd     Old
      Buffer Gets    Executions  Gets per Exec  
    %Total Time (s)  Time (sHash Value
    --------------- ------------ -------------- ------ -------- --------- ----------
          
    1,716,548      168,609           10.2   11.0   371.10   1192.95 2573386356
    Module
    pipe04ac
    INSERT INTO SEP_CONFLICT_REL
    (CONFLICT_IDMATCH_IDPARENT_ENTIT
    Y_ID
    CHILD_ENTITY_IDDEGREE_OF_SEPLIKE_CONFREL_CONFVALUE
    S
    (:1,:2,:3,:4,:5,:6,:7)

          
    1,234,473      128,800            9.6    7.9   338.01    826.63 3352792302
    Module
    pipe04ac
    INSERT INTO ER_DETAIL  
    ER_IDMATCHED_ENTITY_IDMATCHED_TYPE,
     
    MATCHED_ACCT_ID,    MATCHED_KEY_IDINBOUND_KEY_IDSCORE_ADJUS
    TMENT
    MATCH_PRECISIONPRECISION_DESC VALUES  ( :1, :2, :3, :
    4, :5, :6, :7, :8, :)

            
    836,101       84,352            9.9    5.4    92.10    233.17 4101062403
    Module
    pipe02ac
    INSERT INTO SEP_CONFLICT
    (CONFLICT_IDSEP_TEST_IDINBOUND_ENTIT
    Y_ID
    CONFLICT_RULE_IDCONF_ENTITY1CONF_ENTITY2DEGREE_OF_SE
    P
    CONFLICT_STATWEAK_LIKE_SCOREWEAK_REL_SCORECONFLICT_DT)
    VALUES(:1, :2,:3,:4,:5,:6,:7,:"SYS_B_0",:8,:9,:10)

            
    687,347      112,027            6.1    4.4   162.46    447.97 1712256954
    Module
    pipe04ac
    SELECT MAX
    (ROLE_IDFROM SEP_ROLES WHERE DSRC_ACCT_ID = :1

            664
    ,889      112,006            5.9    4.3    28.36     29.64  659324044
    Module
    pipe04ac
    INSERT INTO ER_ENTITY_STATE
    (ER_IDENTITY_IDDSRC_ACCT_IDMAX_
    ADDR_ID
    MAX_ATTR_IDMAX_EMAIL_IDMAX_NAME_IDMAX_NUM_IDMAX
    _ROLE_ID
    VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9)

            
    618,954      112,046            5.5    4.0   238.26    681.01 2878966175
    Module
    pipe04ac
    SELECT MAX
    (ADDR_IDFROM ADDRESS WHERE DSRC_ACCT_ID = :1

            606
    ,310      112,028            5.4    3.9   227.12    712.55 1507829111
    Module
    pipe04ac
    SELECT MAX
    (NAME_IDFROM NAME WHERE DSRC_ACCT_ID = :1

            601
    ,716       33,515           18.0    3.9    92.53    274.08  444121366
    Module
    pipe03ac
    INSERT INTO NUMS
    (NUM_IDDSRC_ACCT_IDENTITY_IDHIST_STATNUM
    _HASH
    NUM_LOCATIONNUM_STATNUM_STAT_DTNUM_TYPE_IDNUM_VAL
    UE
    QC_STATSYS_CREATE_DTSYS_DELETE_DTSYS_LSTUPD_DTVALID_
    FROM_DT
    VALID_THRU_DTVALUES(:1, :2, :3, :4, :5NULLNULLN
    ULL
    , :6, :7, :8, :9NULL, :10NULLNULL)

            
    598,296      112,014            5.3    3.8   275.85    815.74  728889134
    Module
    pipe04ac
    SELECT MAX
    (NUM_IDFROM NUMS WHERE DSRC_ACCT_ID = :1

    SQL ordered by Gets  DB
    /InstORAPERF/oraperf  Snaps720-721
    -> Resources reported for PL/SQL code includes the resources used by all SQL
       statements called by the code
    .
    -> 
    End Buffer Gets Threshold:     10000 Total Buffer Gets:      15,614,958
    -> Captured SQL accounts for  101.5of Total Buffer Gets
    -> SQL reported below exceeded  1.0of Total Buffer Gets

                                                         CPU      Elapsd     Old
      Buffer Gets    Executions  Gets per Exec  
    %Total Time (s)  Time (sHash Value
    --------------- ------------ -------------- ------ -------- --------- ----------
            
    584,401      112,036            5.2    3.7   158.69    450.57 3193297599
    Module
    pipe04ac
    SELECT MAX
    (ATTR_IDFROM ATTRIBUTE WHERE DSRC_ACCT_ID = :1

            547
    ,839       42,847           12.8    3.5    94.48   1023.18 1739503172
    Module
    pipe01ac
    INSERT INTO SEP_RELATIONS
    (RELATION_IDENTITY_IDRELTO_IDLIKE
    _CONF
    REL_CONFDISCLOSEDMATCH_IDVALUES(:1, :2,:3,:4,:5,:"S
    YS_B_0"
    ,:6)

            
    419,157       23,625           17.7    2.7    60.98    372.92 3253887853
    Module
    pipe03ac
    INSERT INTO ADDRESS
    (ADDR_IDADDR1ADDR2ADDR3ADDR_HASHADD
    R_LEFTOVRS
    ADDR_STATADDR_STAT_DTADDR_TYPECARRIER_ROUTEC
    ITY
    COMPANY_NAMECOUNTRYCOUNTRY_CODEDLV_PROBDPBCDSRC_A
    CCT_ID
    ENTITY_IDHIST_STATHOUSEHOLD_IDJOB_TITLELATITUDE,
     
    LONGITUDELOT_CODEPOSTAL_CODEQC_STATSTATESTATE_CODES

            394
    ,454       12,768           30.9    2.5    53.51    180.44 3965780448
    Module
    pipe03ac
    UPDATE DSRC_ACCT  SET ENTITY_ID 
    = :1      ,ACCT_LOADED = :"SYS_B
    _0" 
    WHERE DSRC_ACCT_ID = :2

            359
    ,915       17,501           20.6    2.3    20.47    385.33 2711563501
    Module
    pipe04ac
    INSERT INTO NAME
    (NAME_IDCULTUREDSRC_ACCT_IDENTITY_IDFIRS
    T_NAME
    HIST_STATLAST_NAMELFN_HASHLMN_HASHMID_NAMENAME
    _GEN
    NAME_PFXNAME_SFXNAME_TYPEQC_STATSYS_CREATE_DTSYS
    _DELETE_DT
    SYS_LSTUPD_DTVALID_FROM_DTVALID_THRU_DTVALUES(
    :
    1NULL, :2, :3, :4, :5, :6, :7, :8, :9NULLNULLNULL, :10,

            
    337,392       48,569            6.9    2.2    15.29     15.32 3286148528
    select c
    .nameu.name from conccdefcduseru  where c.co
    n
    # = cd.con# and cd.enabled = :1 and c.owner# = u.user#

            
    337,112       21,341           15.8    2.2    83.19    344.04 3078809437
    Module
    pipe02ab
    INSERT INTO SEP_ROLES
    (ROLE_IDDISCLOSEDDSRC_ACCT_IDENTITY_I
    D
    ROLE_CODE_IDROLE_STATSYS_DELETE_DTVALUES(:1, :2, :3, :4
    , :5, :6NULL)

            
    325,591       17,920           18.2    2.1    48.99    168.17 1848403279
    Module
    pipe03ac
    INSERT INTO ATTRIBUTE
    (ATTR_IDATTR_DTATTR_TYPE_IDATTR_VALUE
    DSRC_ACCT_IDENTITY_IDHIST_STATQC_STATSYS_CREATE_DTSY
    S_DELETE_DT
    SYS_LSTUPD_DTVALID_FROM_DTVALID_THRU_DTVALUES
    (:1NULL, :2, :3, :4, :5, :6, :7, :8NULL, :9NULLNULL)

            
    302,332       17,116           17.7    1.9    32.17    417.41 2888989485 

  7. #17
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Attach the statspack report instead of pasting it part by part.
    I wanted to see complete init.ora parameters.

    Tamil

  8. #18
    Join Date
    Jan 2006
    Posts
    23
    I'm sorry I did not see the attachment option.
    Thanks a lot, mj

  9. #19
    Join Date
    Nov 2003
    Posts
    89
    Not sure but use insert /* +Append */ Or use hints

  10. #20
    Join Date
    Jan 2006
    Posts
    23
    Quote Originally Posted by srt
    Not sure but use insert /* +Append */ Or use hints
    I cannot touch the code - it's in the app (3rd party product). Unfortunatelly there's not even a single database object other then tables and indexes. Not proc, no functions, nothing...
    I asked the development but they are using soem special OCI calls and cannot change it to include "append".
    Thanks a lot,mj

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width