move table with securefiles extremely slow

From: Anton <djeday84_at_gmail.com>
Date: Wed, 09 Jul 2014 19:09:32 +0400
Message-ID: <53BD5B2C.6030708_at_gmail.com>

Hi guru of oracle-l
faced with slow work of table move

Trace show 1-2 block read of lobs

• 2014-07-09 18:34:01.297 WAIT #4573139760: nam='db file sequential read' ela= 3109 file#=9 block#=296506 blocks=1 obj#=110876 tim=11989028257641 WAIT #4573139760: nam='db file scattered read' ela= 92 file#=9 block#=297589 blocks=2 obj#=110878 tim=11989028258143 WAIT #4573139760: nam='db file scattered read' ela= 120 file#=9 block#=297591 blocks=5 obj#=110878 tim=11989028258424 WAIT #4573139760: nam='db file scattered read' ela= 78 file#=9 block#=297596 blocks=4 obj#=110878 tim=11989028258626 WAIT #4573139760: nam='db file sequential read' ela= 1394 file#=9 block#=286187 blocks=1 obj#=110878 tim=11989028260111 WAIT #4573139760: nam='direct path write' ela= 17865 file number=34 first dba=45524 block cnt=12 obj#=2654924 tim=11989028278779 WAIT #4573139760: nam='db file sequential read' ela= 17960 file#=9 block#=295657 blocks=1 obj#=110876 tim=11989028857634 WAIT #4573139760: nam='db file sequential read' ela= 1513 file#=9 block#=286188 blocks=1 obj#=110878 tim=11989028859590
• 2014-07-09 18:34:02.318 WAIT #4573139760: nam='db file sequential read' ela= 2189 file#=9 block#=295658 blocks=1 obj#=110876 tim=11989029278515 WAIT #4573139760: nam='db file scattered read' ela= 1529 file#=9 block#=286189 blocks=2 obj#=110878 tim=11989029280367 WAIT #4573139760: nam='db file scattered read' ela= 3350 file#=9 block#=286191 blocks=5 obj#=110878 tim=11989029284162 WAIT #4573139760: nam='db file scattered read' ela= 73 file#=9 block#=286196 blocks=2 obj#=110878 tim=11989029284432 WAIT #4573139760: nam='direct path write' ela= 31231 file number=34 first dba=45536 block cnt=9 obj#=2654924 tim=11989029316630 WAIT #4573139760: nam='db file sequential read' ela= 13180 file#=9 block#=282820 blocks=1 obj#=110876 tim=11989029918244 WAIT #4573139760: nam='db file scattered read' ela= 15620 file#=9 block#=302519 blocks=2 obj#=110878 tim=11989029934532 WAIT #4573139760: nam='direct path write' ela= 6132 file number=34 first dba=45545 block cnt=2 obj#=2654924 tim=11989029941261
• 2014-07-09 18:34:03.419 WAIT #4573139760: nam='db file sequential read' ela= 12049 file#=9 block#=282821 blocks=1 obj#=110876 tim=11989030380066 WAIT #4573139760: nam='db file scattered read' ela= 884 file#=9 block#=302521 blocks=2 obj#=110878 tim=11989030381449 WAIT #4573139760: nam='db file scattered read' ela= 959 file#=9 block#=302523 blocks=3 obj#=110878 tim=11989030382657 WAIT #4573139760: nam='direct path write' ela= 92038 file number=34 first dba=45547 block cnt=5 obj#=2654924 tim=11989030475110 WAIT #4573139760: nam='db file sequential read' ela= 1464 file#=9 block#=296508 blocks=1 obj#=110876 tim=11989030904858 WAIT #4573139760: nam='db file scattered read' ela= 3454 file#=9 block#=286206 blocks=2 obj#=110878 tim=11989030908608 WAIT #4573139760: nam='db file sequential read' ela= 79 file#=9 block#=286208 blocks=1 obj#=110878 tim=11989030908861 WAIT #4573139760: nam='db file scattered read' ela= 15456 file#=9 block#=292481 blocks=9 obj#=110878 tim=11989030924488 WAIT #4573139760: nam='direct path write' ela= 23231 file number=34 first dba=45552 block cnt=12 obj#=2654924 tim=11989030948524
• 2014-07-09 18:34:04.548 WAIT #4573139760: nam='db file sequential read' ela= 381 file#=9 block#=295659 blocks=1 obj#=110876 tim=11989031508816 WAIT #4573139760: nam='db file scattered read' ela= 479 file#=9 block#=292490 blocks=2 obj#=110878 tim=11989031509583 WAIT #4573139760: nam='db file scattered read' ela= 488 file#=9 block#=292492 blocks=3 obj#=110878 tim=11989031510225 WAIT #4573139760: nam='direct path write' ela= 6934 file number=34 first dba=45564 block cnt=4 obj#=2654924 tim=11989031517680 WAIT #4573139760: nam='db file sequential read' ela= 1086 file#=9 block#=282822 blocks=1 obj#=110876 tim=11989031943657 WAIT #4573139760: nam='db file scattered read' ela= 115 file#=9 block#=302526 blocks=2 obj#=110878 tim=11989031944153 WAIT #4573139760: nam='db file scattered read' ela= 205 file#=9 block#=302528 blocks=3 obj#=110878 tim=11989031944532 WAIT #4573139760: nam='direct path write' ela= 21918 file number=34 first dba=45447 block cnt=5 obj#=2654924 tim=11989031966984
• 2014-07-09 18:34:05.471 WAIT #4573139760: nam='db file sequential read' ela= 18595 file#=9 block#=282823 blocks=1 obj#=110876 tim=11989032431956 WAIT #4573139760: nam='db file scattered read' ela= 14999 file#=9 block#=302531 blocks=2 obj#=110878 tim=11989032447461 WAIT #4573139760: nam='db file scattered read' ela= 541 file#=9 block#=302533 blocks=2 obj#=110878 tim=11989032448326 WAIT #4573139760: nam='direct path write' ela= 9971 file number=34 first dba=45452 block cnt=4 obj#=2654924 tim=11989032458923 WAIT #4573139760: nam='db file sequential read' ela= 507 file#=9 block#=296509 blocks=1 obj#=110876 tim=11989032896812 WAIT #4573139760: nam='db file scattered read' ela= 86 file#=9 block#=292495 blocks=2 obj#=110878 tim=11989032897142 WAIT #4573139760: nam='db file scattered read' ela= 58 file#=9 block#=292497 blocks=2 obj#=110878 tim=11989032897365 WAIT #4573139760: nam='direct path write' ela= 13133 file number=34 first dba=45456 block cnt=4 obj#=2654924 tim=11989032910842
• 2014-07-09 18:34:06.377 WAIT #4573139760: nam='db file sequential read' ela= 545 file#=9 block#=282824 blocks=1 obj#=110876 tim=11989033337789 WAIT #4573139760: nam='db file scattered read' ela= 90 file#=9 block#=302535 blocks=2 obj#=110878 tim=11989033338167 WAIT #4573139760: nam='db file scattered read' ela= 48 file#=9 block#=302537 blocks=2 obj#=110878 tim=11989033338376 WAIT #4573139760: nam='direct path write' ela= 43299 file number=34 first dba=45460 block cnt=4 obj#=2654924 tim=11989033382335 WAIT #4573139760: nam='db file sequential read' ela= 8824 file#=9 block#=296511 blocks=1 obj#=110876 tim=11989033797492 WAIT #4573139760: nam='db file sequential read' ela= 24 file#=9 block#=292505 blocks=1 obj#=110878 tim=11989033797824
• 2014-07-09 18:34:07.260 WAIT #4573139760: nam='db file sequential read' ela= 411 file#=9 block#=296512 blocks=1 obj#=110876 tim=11989034221162 WAIT #4573139760: nam='db file scattered read' ela= 1266 file#=9 block#=292506 blocks=2 obj#=110878 tim=11989034222717 WAIT #4573139760: nam='db file scattered read' ela= 1720 file#=9 block#=292508 blocks=5 obj#=110878 tim=11989034224660 WAIT #4573139760: nam='db file scattered read' ela= 691 file#=9 block#=292513 blocks=4 obj#=110878 tim=11989034225526 WAIT #4573139760: nam='direct path write' ela= 30881 file number=34 first dba=45464 block cnt=11 obj#=2654924 tim=11989034257077 WAIT #4573139760: nam='db file sequential read' ela= 430 file#=9 block#=296514 blocks=1 obj#=110876 tim=11989034830214 WAIT #4573139760: nam='db file scattered read' ela= 85 file#=9 block#=292517 blocks=2 obj#=110878 tim=11989034830525 WAIT #4573139760: nam='db file scattered read' ela= 378 file#=9 block#=292519 blocks=4 obj#=110878 tim=11989034831056 WAIT #4573139760: nam='direct path write' ela= 18593 file number=34 first dba=45475 block cnt=6 obj#=2654924 tim=11989034849986

select object_name object_type from dba_objects where object_id in (110878,110876);

OBJECT_TYPE

---

SYS_LOB0000110875C00005$$
SYS_LOB0000110875C00007$$

OWNER          |SEGMENT_NAME |MB_SIZE
---------------|----------------------------------------|-------
TBG            |SYS_LOB0000110875C00007$$               | 11342

OWNER          |SEGMENT_NAME |MB_SIZE
---------------|----------------------------------------|-------
TBG            |SYS_LOB0000110875C00005$$               | 2054

5 hour run and sesstat show 700mb redo =(

part of the table ddl
create table ()

  NOCOMPRESS LOGGING
   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)    TABLESPACE "TBG_DATA"
  XMLTYPE COLUMN "SOAP_REQEST" STORE AS SECUREFILE BINARY XML (    TABLESPACE "TBG_LOB" DISABLE STORAGE IN ROW CHUNK 8192    CACHE NOLOGGING NOCOMPRESS KEEP_DUPLICATES    STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645    PCTINCREASE 0
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ALLOW NONSCHEMA DISALLOW ANYSCHEMA   XMLTYPE COLUMN "SOAP_RESPONSE" STORE AS SECUREFILE BINARY XML (    TABLESPACE "TBG_LOB" DISABLE STORAGE IN ROW CHUNK 8192    CACHE NOLOGGING NOCOMPRESS KEEP_DUPLICATES    STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645    PCTINCREASE 0
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ALLOW NONSCHEMA DISALLOW ANYSCHEMA ENABLE ROW MOVEMENT ; database version 11.2.0.3.7 aix 7.1

ps: sorry for my english

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 09 2014 - 17:09:32 CEST