{"id":1179,"date":"2012-09-12T18:44:06","date_gmt":"2012-09-12T16:44:06","guid":{"rendered":"http:\/\/www.ora-solutions.net\/web\/?p=1179"},"modified":"2012-09-12T18:47:25","modified_gmt":"2012-09-12T16:47:25","slug":"datapump-export-suffering-from-oracle-row-migration","status":"publish","type":"post","link":"https:\/\/www.ora-solutions.net\/web\/2012\/09\/12\/datapump-export-suffering-from-oracle-row-migration\/","title":{"rendered":"Datapump Export suffering from Oracle Row Migration"},"content":{"rendered":"<p>Recently, i was troubleshooting a datapump export duration problem. Over the period of 18 months, the duration of a multi-table export increased dramatically. A quick analysis showed that the export duration was mainly dependent on one big table. This 50 GB table with no BLOB\/CLOB\/LONG datatypes took more than 4 hours on a modern system wheras it should not take more than 10 &#8211; 15 minutes. The system was performing ONLY single-block I\/O requests (db file sequential read). I found this strange and started investigating.<\/p>\n<p>Beginning with 11g, you can enable sql_trace (10046) for datapump with the new syntax:<\/p>\n<pre lang=\"SQL\">alter system set events 'sql_trace {process : pname = dw | pname = dm} level=12';<\/pre>\n<p>The trace showed that there were dozens of consecutive single-block I\/O requests against the SAME data block:<\/p>\n<pre lang=\"SQL\"> \r\nWAIT #47053877709656: nam='db file sequential read' ela= 344 file#=41 block#=2556595 blocks=1 obj#=120874 tim=1347023848770469\r\nWAIT #47053877709656: nam='db file sequential read' ela= 6 file#=41 block#=2556595 blocks=1 obj#=120874 tim=1347023848770504\r\nWAIT #47053877709656: nam='db file sequential read' ela= 5 file#=41 block#=2556595 blocks=1 obj#=120874 tim=1347023848770526\r\nWAIT #47053877709656: nam='db file sequential read' ela= 5 file#=41 block#=2556595 blocks=1 obj#=120874 tim=1347023848770548\r\nWAIT #47053877709656: nam='db file sequential read' ela= 4 file#=41 block#=2556595 blocks=1 obj#=120874 tim=1347023848770570\r\nWAIT #47053877709656: nam='db file sequential read' ela= 5 file#=41 block#=2556595 blocks=1 obj#=120874 tim=1347023848770593\r\nWAIT #47053877709656: nam='db file sequential read' ela= 5 file#=41 block#=2556595 blocks=1 obj#=120874 tim=1347023848770617\r\nWAIT #47053877709656: nam='db file sequential read' ela= 4 file#=41 block#=2556595 blocks=1 obj#=120874 tim=1347023848770639\r\n<\/pre>\n<p>No wonder that this does not perform well, right? Next was a block dump of this interesting block from file 52 \/ block 2056439.<\/p>\n<pre lang=\"SQL\"> \r\nselect * from dba_extents where file_id = 41 and 2556595 between block_id and block_id+blocks-1;\r\nalter system dump datafile 41 block 2556595;\r\n<\/pre>\n<p>In the resulting trace file, I verified that i dumped the block from the correct table segment. (Hex 0x1d82a is Dec 120874)<\/p>\n<pre lang=\"SQL\"> \r\nbuffer tsn: 15 rdba: 0x0a6702b3 (41\/2556595)\r\nfrmt: 0x02 chkval: 0x57f3 type: 0x06=trans data\r\nseg\/obj: 0x1d82a csc: 0x0c.1c3abc94 itc: 20 flg: E typ: 1 - DATA\r\n<\/pre>\n<p>Then I had a closer look at the flag bytes.<\/p>\n<p>The flags were:<\/p>\n<ul>\n<li>H: Head Piece<\/li>\n<li>F: First Piece<\/li>\n<li>L: Last Piece<\/li>\n<\/ul>\n<p>Normally, &#8220;H-FL&#8221; is shown to indicate that the whole row is located in one rowpiece in the same block. In this block dump, you can see dozens of rows with &#8220;&#8212;-FL&#8211;&#8221; which means that the head row piece is not here and you see no Head-Piece-only Row Pieces\u00a0 &#8220;&#8211;H&#8212;&#8211;&#8221; . This shows that this segment has suffered from heavy row migration. Probably, this was caused by adding columns after the table creation.<\/p>\n<pre lang=\"SQL\" line=\"1\"> \r\nSID_ora_999.trc | grep \"^tl: \"\r\ntl: 97 fb: ----FL-- lb: 0x0 cc: 20\r\ntl: 88 fb: --H-FL-- lb: 0x0 cc: 20\r\ntl: 97 fb: ----FL-- lb: 0x0 cc: 20\r\ntl: 88 fb: --H-FL-- lb: 0x0 cc: 20\r\ntl: 98 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 89 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 89 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 89 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 89 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 89 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 89 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 89 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 90 fb: --H-FL-- lb: 0x2 cc: 20\r\ntl: 99 fb: ----FL-- lb: 0x2 cc: 20\r\n<\/pre>\n<p>With datapump there are 2 different access methods: EXTERNAL_TABLE and DIRECT_PATH. Usually, the datapump utility decides on it\u00b4s own which method to use. It turned out that with EXTERNAL_TABLE, the table export takes only 10 minutes and does not perform these single-block I\/O. It only appears with DIRECT_PATH.<\/p>\n<p>So, the next step I recommended was to reorganize the table to get rid of row migration and evaluate whether PCTFREE should be increased. Afterwards export durations are back to normal.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Recently, i was troubleshooting a datapump export duration problem. Over the period of 18 months, the duration of a multi-table export increased dramatically. A quick analysis showed that the export duration was mainly dependent on one big table. This 50 GB table with no BLOB\/CLOB\/LONG datatypes took more than 4 hours on a modern system [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[57,5,34],"tags":[],"class_list":["post-1179","post","type-post","status-publish","format-standard","hentry","category-11gr2","category-oracle-database","category-performance-tuning-oracle-database-2"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/1179","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/comments?post=1179"}],"version-history":[{"count":6,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/1179\/revisions"}],"predecessor-version":[{"id":1185,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/1179\/revisions\/1185"}],"wp:attachment":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/media?parent=1179"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/categories?post=1179"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/tags?post=1179"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}