Hi,
I've an insert into a GTT (having only one index for its primary key) that is reading a lot of UNDO (db file sequential read file#=2).
I can't think of a good reason for that.
Here is a tkprof:
SQL ID: 4xwgpzxtgg4s5 Plan Hash: 0
INSERT INTO xxxxx.xxxxxxxxxxxxxxxxxx(INCOUNT,INNUM)
VALUES
(:v1 ,:v2 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 113 0.09 0.09 36 173 838 6830
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 113 0.09 0.09 36 173 838 6830
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 108
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 17 0.00 0.00
SQL*Net message from client 17 0.01 0.04
db file sequential read 36 0.02 0.05
All the are from file#=2:
WAIT #2390071304: nam='db file sequential read' ela= 735 file#=2 block#=46834 blocks=1 obj#=0 tim=3960230031525
WAIT #2390071304: nam='db file sequential read' ela= 713 file#=2 block#=46835 blocks=1 obj#=0 tim=3960230032327
WAIT #2390071304: nam='db file sequential read' ela= 659 file#=2 block#=46836 blocks=1 obj#=0 tim=3960230033125
WAIT #2390071304: nam='db file sequential read' ela= 648 file#=2 block#=46837 blocks=1 obj#=0 tim=3960230034030
EXEC #2390071304:c=0,e=3535,p=4,cr=5,cu=38,mis=0,r=52,dep=0,og=1,plh=0,tim=3960230034104
And there is something else that I can't understand. Each EXEC has only one BIND, such as:
BINDS #2390071304:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=00 csi=00 siz=48 off=0
kxsbbbfp=8e785608 bln=22 avl=02 flg=05
value=12
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=00 csi=00 siz=0 off=24
kxsbbbfp=8e785620 bln=22 avl=05 flg=01
value=7961958
however, the execs reports about 50 rows (as you see the number of rows in the tkprof)
Any idea about the reason for that?
Thanks a lot,
Regards,
Franck.