{"id":1085,"date":"2011-07-18T16:21:47","date_gmt":"2011-07-18T14:21:47","guid":{"rendered":"http:\/\/www.ora-solutions.net\/web\/?p=1085"},"modified":"2011-07-18T16:21:47","modified_gmt":"2011-07-18T14:21:47","slug":"performance-degradation-for-query-on-dba_segments-bytes-in-11gr2","status":"publish","type":"post","link":"https:\/\/www.ora-solutions.net\/web\/2011\/07\/18\/performance-degradation-for-query-on-dba_segments-bytes-in-11gr2\/","title":{"rendered":"Performance Degradation for Query on DBA_SEGMENTS.BYTES in 11gR2"},"content":{"rendered":"

I have been troubleshooting a performance issue in a DWH environment, which is quite interesting. It was a query on DBA_SEGMENTS in 11gR2 with lots of partitions and it was taking almost 10 minutes versus only a few seconds in 10gR2. The problem could be stripped downt to this SQL query:<\/p>\n

select bytes from dba_segments<\/pre>\n
The dba_segment view can be seen in DBA_VIEWS:<\/p>\n
\r\nselect ...\r\n       decode(bitand(segment_flags, 131072), 131072, blocks,\r\n           (decode(bitand(segment_flags,1),1,\r\n            dbms_space_admin.segment_number_blocks<\/strong>(tablespace_id, relative_fno,\r\n            header_block, segment_type_id, buffer_pool_id, segment_flags,\r\n            segment_objd, blocks), blocks)))*blocksize,\r\n      ...\r\nfrom sys_dba_segs\r\n<\/pre>\n
The response time was dominated by Waits for “db file sequential reads” taking almost all of the response time.<\/p>\n
11gR2:<\/strong><\/p>\n
\r\ncall\u00a0\u00a0\u00a0\u00a0 count\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 cpu\u00a0\u00a0\u00a0 elapsed\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 disk\u00a0\u00a0\u00a0\u00a0\u00a0 query\u00a0\u00a0\u00a0 current\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rows\r\n------- ------\u00a0 -------- ---------- ---------- ---------- ----------\u00a0 ----------\r\nParse\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0 0.00\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0.00\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\r\nExecute\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0 0.00\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0.00\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\r\nFetch\u00a0\u00a0\u00a0 12563\u00a0\u00a0\u00a0\u00a0 23.17\u00a0\u00a0\u00a0\u00a0 546.74\u00a0\u00a0\u00a0\u00a0 157447\u00a0\u00a0\u00a0\u00a0 580414\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0 188421\r\n------- ------\u00a0 -------- ---------- ---------- ---------- ----------\u00a0 ----------\r\ntotal\u00a0\u00a0\u00a0 12565\u00a0\u00a0\u00a0\u00a0 23.17\u00a0\u00a0\u00a0\u00a0 546.74\u00a0\u00a0\u00a0\u00a0 157447\u00a0\u00a0\u00a0\u00a0 580414\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0 188421\r\n<\/pre>\n
10gR2:<\/strong><\/p>\n
\r\ncall     count       cpu    elapsed       disk      query    current        rows\r\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\r\nParse        1      0.06       0.06          0          0          0           0\r\nExecute      1      0.00       0.00          0          0          0           0\r\nFetch     5104      1.77       2.54          0      96688          0       76542\r\n------- ------  -------- ---------- ---------- ---------- ----------  ----------\r\ntotal     5106      1.83       2.61          0      96688          0       76542\r\n<\/pre>\n
The 10046 Trace File contains more data regarding those waits:<\/p>\n
\r\nWAIT #4: nam='db file sequential read' ela= 134 file#=21 block#=2313482 blocks=1 obj#=-1 \r\nWAIT #4: nam='db file sequential read' ela= 120 file#=15 block#=2128019 blocks=1 obj#=-1 \r\nWAIT #4: nam='db file sequential read' ela= 128 file#=21 block#=2313490 blocks=1 obj#=-1 \r\nWAIT #4: nam='db file sequential read' ela= 372 file#=21 block#=2313498 blocks=1 obj#=-1 \r\nWAIT #4: nam='db file sequential read' ela= 108 file#=21 block#=2313506 blocks=1 obj#=-1 \r\nWAIT #4: nam='db file sequential read' ela= 130 file#=21 block#=2313514 blocks=1 obj#=-1 \r\nWAIT #4: nam='db file sequential read' ela= 132 file#=21 block#=2313522 blocks=1 obj#=-1 \r\nWAIT #4: nam='db file sequential read' ela= 121 file#=21 block#=2313530 blocks=1 obj#=-1 \r\nWAIT #4: nam='db file sequential read' ela= 158 file#=15 block#=2128003 blocks=1 obj#=-1 \r\nWAIT #4: nam='db file sequential read' ela= 146 file#=21 block#=2313538 blocks=1 obj#=-1 \r\nWAIT #4: nam='db file sequential read' ela= 116 file#=21 block#=2313546 blocks=1 obj#=-1 \r\n(Output modified for formatting)\r\n<\/pre>\n
Strange thing that obj# is -1. I made some block dumps of those blocks and found out that all those blocks (file#\/block#) have something in common: They were all segment header blocks. Another strange thing was that when the query was executed a second time, all those I\/O requests were performed again – so no caching. <\/p>\n
At that point, I opened a Support service request. After a couple of weeks, Support suggested running this for each tablespace of the user segments:<\/p>\n
exec  dbms_space_admin.TABLESPACE_FIX_SEGMENT_EXTBLKS(''); \r\n<\/pre>\n
Finally this fixed the problem. Now, we are trying to find out why this was necessary for a database, that was freshly created with dbca 11.2.0.2 from the seeded template and filled with data pump import.<\/p>\n","protected":false},"excerpt":{"rendered":"
I have been troubleshooting a performance issue in a DWH environment, which is quite interesting. It was a query on DBA_SEGMENTS in 11gR2 with lots of partitions and it was taking almost 10 minutes versus only a few seconds in 10gR2. The problem could be stripped downt to this SQL query:<\/p>\n","protected":false},"author":1,"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,19,5,34],"tags":[],"class_list":["post-1085","post","type-post","status-publish","format-standard","hentry","category-11gr2","category-metalink","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\/1085","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\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/comments?post=1085"}],"version-history":[{"count":10,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/1085\/revisions"}],"predecessor-version":[{"id":1095,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/1085\/revisions\/1095"}],"wp:attachment":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/media?parent=1085"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/categories?post=1085"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/tags?post=1085"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}