Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Very slow insert statement

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, , :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 ?
Answers
-
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.
-
It is about 13blocks per row (see the number of rows, not the number of executions of the insert statement).
Hemant K Chitale
-
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.