Forum Stats

  • 3,770,212 Users
  • 2,253,082 Discussions
  • 7,875,367 Comments

Discussions

Very slow insert statement

edw_otn
edw_otn Member Posts: 1 Blue Ribbon
edited Mar 20, 2018 3:48AM in General Database Discussions

Oracle 11.2.0.4 on OEL 6.5

Our app admin run some update script from app vendor,

and this script was running for several hours . Also instance  generated lots of archivelogs.

I started sql trace for 15 minutes and found that there is almost the only query.

There are no significant recursive queries in the trace file.

INSERT INTO house_t 

VALUES

(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17,

  :18, :19, :20, :21, :22, :23)

call     count       cpu    elapsed       disk      query    current        rows

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

Parse       70      0.00       0.00          0          0          0           0

Execute     71     23.86     633.03      83564      41375    4597750      355735

Fetch        0      0.00       0.00          0          0          0           0

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

total      141     23.87     633.03      83564      41375    4597750      355735

Why one simple insert needs to use this huge amount of blocks in current mode 64757=4597750/71  and  what rows it touches???

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: 103 

Number of plan statistics captured: 3

Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

         0          0          0  LOAD TABLE CONVENTIONAL  (cr=584 pr=1443 pw=0 time=11388214 us)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  SQL*Net more data from client                9523        0.00          0.08

  db file sequential read                     82252        0.60        603.94

  SQL*Net message to client                      70        0.00          0.00

  SQL*Net message from client                    70        0.00          0.15

  log file switch completion                      4        0.08          0.18

  undo segment extension                          4        0.00          0.01

  log file sync                                   1        0.00          0.00

********************************************************************************

Table has simple structure:

SQL> desc house_t

Name                 Null?    Type

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

POSTALCODE                  VARCHAR2(6)

IFNSFL                      VARCHAR2(4)

TERRIFNSFL                  VARCHAR2(4)

IFNSUL                      VARCHAR2(4)

TERRIFNSUL                  VARCHAR2(4)

OKATO                       VARCHAR2(11)

OKTMO                       VARCHAR2(11)

UPDATEDATE                  DATE

HOUSENUM                    VARCHAR2(20)

ESTSTATUS                   NUMBER(5)

BUILDNUM                    VARCHAR2(10)

STRUCNUM                    VARCHAR2(10)

STRSTATUS                   NUMBER(5)

HOUSEID                     VARCHAR2(36)

HOUSEGUID                   VARCHAR2(36)

AOGUID                      VARCHAR2(36)

STARTDATE                   DATE

ENDDATE                     DATE

STATSTATUS                  NUMBER(5)

NORMDOC                     VARCHAR2(36)

COUNTER                     NUMBER(10)

CADNUM                      VARCHAR2(100)

DIVTYPE                     NUMBER(5)

There are no triggers on this table, no mviews, no defined fga policies, no fgac policies.

Also table has 3 indexes

CREATE UNIQUE INDEX HOUSE_T_IDX0 ON HOUSE_T(HOUSEID);

CREATE INDEX  HOUSE_T_IDX1 ON HOUSE_T (T_HOUSEGUID);

CREATE INDEX HOUSE_T_IDX2 ON HOUSE_T (T_AOGUID);

Raw trace file is full of 'db file sequential read' waits on this 3 indexes and undo blocks.

Table now has about 50M records, size is about 12Gb.

Indexes are about 3-4GB and have blevel=3 in dba_indexes.

I expect that this insert statement can take 20-30 blocks to run, but why thousands ?

Tagged:

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Mar 19, 2018 6:47PM

    It looks like whatever executing it is using array binds, each execution has about 5k rows being inserted. This is probably many times faster than running that statement 355k times.

    There's not much you can do about the work that needs to be done, I guess that using direct path load or parallel DML probably would make it faster but these scripts are supplied by your app vendor - if you want the scripts to be faster then it's them who should make any changes otherwise you'd be running the risk of being unsupported.

    edw_otn
  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond
    edited Mar 20, 2018 2:03AM

    It is about 13blocks per row  (see the number of rows, not the number of executions of the insert statement).

    Hemant K Chitale

    edw_otn
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,790 Gold Crown
    edited Mar 20, 2018 3:48AM
    Hemant K Chitale wrote:It is about 13blocks per row (see the number of rows, not the number of executions of the insert statement).Hemant K Chitale

    To add to Hemant's comment - with 3 indexes at BLEVEL=3 Oracle has to check 4 blocks to find the current version of the relevant index leaf block where it wants to insert, so 3 * 4 is in the right ballpark for the number of current mode gets.

    In passing, the number of disk reads -  which accounts for most of your time - is probably due to reading index leaf blocks to do the inserts, though if you've got flashback enabled it's possible that some of them might be "read for flashback new".

    Regards

    Jonathan Lewis.

    edw_otn
This discussion has been closed.