{"id":1016,"date":"2006-07-12T12:00:00","date_gmt":"2006-07-12T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1016"},"modified":"2006-07-12T12:00:00","modified_gmt":"2006-07-12T12:00:00","slug":"table-reorgs-and-statistics","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2006\/07\/12\/table-reorgs-and-statistics\/","title":{"rendered":"Table reorgs and statistics"},"content":{"rendered":"

While working on the ITL deadlock problem<\/a> (which looks like it’s been fixed by the initrans increase and table rebuild), the developers highlighted another table as hitting this problem in the past. When I investigated, I found that initrans had already been set to 6 so this had obviously happened in the past on the other table. I went back to development and explained that initrans was already set, but they highlighted the fact that the value of CHAIN_CNT was over 4 million on an 11 million row table. They questioned whether it had been reorganised. I suspected not, so I thought I’d put together a simple test case to look at the effects of alter table move. This is on version 9.2.0.7 <\/p>\n

SQL> create user test identified by test2 default tablespace users3 temporary tablespace temp;
User created.<\/p>
SQL> grant connect, resource to test;<\/p>
Grant succeeded.<\/p>
SQL> alter user test quota unlimited on users;<\/p>
User altered.<\/p>
SQL> connect test\/testConnected.<\/p>
SQL> create table test (long_string varchar2(2000));<\/p>
Table created.<\/p><\/pre>\n
So I have a table and I want to force rows to be migrated into other blocks. There are tons of methods I could have used, but this quick and dirty approach worked. First a little PL\/SQL block will insert 10,000 rows with short values in the long_string column. <\/p>\n
SQL> begin2 for ctr in 1 .. 10000 loop3 insert into test values (to_char(ctr));4 end loop;5 end;6 \/
PL\/SQL procedure successfully completed.<\/p>
SQL> commit;<\/p>
Commit complete.<\/p><\/pre>\n
I’m going to analyze the table to see if there are any chained or migrated rows in the table. N.B. I have to use ANALYZE TABLE here because DBMS_STATS doesn’t populate the CHAIN_CNT column in dba_tables. <\/b>We use DBMS_STATS exclusively to gather optimiser statistics, but the table under investigation had obviously been analysed at some point in the past, otherwise the CHAIN_CNT column wouldn’t have been populated. Anyway, let’s have a look at the results.<\/p>\n
<\/p>\n
SQL> analyze table test compute statistics;
Table analyzed.<\/p>
SQL> select table_name, num_rows, chain_cnt from user_tables;<\/p>
TABLE_NAME                     NUM_ROWS   CHAIN_CNT------------------------------ ---------- ----------TEST                                10000          0<\/p><\/pre>\n
There are no migrated rows at this point, but I can force rows to be migrated by increasing significantly the length of every newly-inserted row. I’ll use the LPAD function to increase the length to 500 characters.<\/p>\n
SQL> update test set long_string = lpad(long_string, 500);
10000 rows updated.<\/p>
SQL> commit;<\/p>
Commit complete.<\/p><\/pre>\n
Now I need to check that rows have been migrated. <\/p>\n
SQL> analyze table test compute statistics;
Table analyzed.<\/p>
SQL> select table_name, num_rows, chain_cnt from user_tables;<\/p>
TABLE_NAME NUM_ROWS CHAIN_CNT------------------------------ ---------- ----------TEST 10000 9983<\/p><\/pre>\n
The test table now contains lots of migrated rows, just like the production table that we’re investigating. If I use alter table move to rebuild the table and change initrans to 6 while I’m at it, there should be no more migrated rows but, more important for the purposes of my test, I can see how dba_tables looks afterwards. <\/p>\n
SQL> alter table test move initrans 6;
<\/p>Table altered.
<\/p>SQL> select table_name, num_rows, chain_cnt from user_tables;
<\/p>TABLE_NAME NUM_ROWS CHAIN_CNT------------------------------ ---------- ----------TEST
<\/p><\/pre>\n<\/p>\n
The ALTER TABLE MOVE command has cleared out the statistics for the table. This makes sense because the reorg invalidates the statistics to a large extent, but it does lead to a few important points<\/p>\n
In our case, the table can’t have been rebuilt or the CHAIN_CNT value would be either null or 0, because we run DBMS_STATS exclusively. Well, at least we can’t tell<\/em> if the table was rebuilt when initrans was increased to 6. We’re going to rebuild it, to be sure.<\/p>\n
When rebuilding objects using ALTER TABLE MOVE, you need to remember to regather statistics using your normal procedures.<\/p>\n
Remember that DBMS_STATS does not<\/em> populate CHAIN_CNT properly, so you need to use ANALYZE. This is a bit frustrating and I’ll probably blog about that again later.<\/p>\n
Finally I’ll check that there are no migrated rows remaining in the table now that it’s been rebuilt <\/p>\n
SQL> analyze table test compute statistics;
Table analyzed.<\/p>
SQL> select table_name, num_rows, chain_cnt from user_tables;<\/p>
TABLE_NAME NUM_ROWS CHAIN_CNT------------------------------ ---------- ----------TEST 10000 0<\/p><\/pre>\n","protected":false},"excerpt":{"rendered":"
While working on the ITL deadlock problem (which looks like it’s been fixed by the initrans increase and table rebuild), the developers highlighted another table as hitting this problem in the past. When I investigated, I found that initrans had already been set to 6 so this had obviously happened in the past on the… Continue reading Table reorgs and statistics<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1016","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1017,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/13\/alter-table-move-and-table-stats\/","url_meta":{"origin":1016,"position":0},"title":"alter table … move and table stats","date":"July 13, 2006","format":false,"excerpt":"Howard Rogers left a comment on my last blog, showing an example of using alter table ... move on a 10gR2 database on Linux. In his example, unlike mine, the table rebuild did not nullify the table's statistics. I admit I was surprised myself when I ran my example yesterday\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1062,"url":"http:\/\/orcldoug.com\/blog\/2006\/08\/21\/resumable-storage-allocation\/","url_meta":{"origin":1016,"position":1},"title":"Resumable Storage Allocation","date":"August 21, 2006","format":false,"excerpt":"At the moment I'm writing a short 9i\/10g New Features seminar for the developers at work. The idea is to run through lots of new features very quickly to see if anything lights their candle and they can then go off and investigate further afterwards.It occurred to me while I\u2026","rel":"","context":"With 9 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":992,"url":"http:\/\/orcldoug.com\/blog\/2006\/06\/13\/production-call-out\/","url_meta":{"origin":1016,"position":2},"title":"Production Call-out","date":"June 13, 2006","format":false,"excerpt":"On Saturday morning, we ran into a problem on one of our 9.2.0.7 Production databases during an Extract, Transform and Load (ETL) batch process.I was called at 5:15am to look at a job that normally runs in a few minutes but had failed after 30 or 40 minutes when trying\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1481,"url":"http:\/\/orcldoug.com\/blog\/2009\/04\/09\/diagnosing-locking-problems-using-ash-part-4\/","url_meta":{"origin":1016,"position":3},"title":"Diagnosing Locking Problems using ASH \u2013 Part 4","date":"April 9, 2009","format":false,"excerpt":"Some features in this post require a Diagnostics Pack license.No sooner had I finished part 3 with some conclusions than I thought of another example I should have included and then someone else made a comment in an email which suggested another. (Thanks, JB!) \"It's probably worth pointing out that\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1562,"url":"http:\/\/orcldoug.com\/blog\/2010\/02\/17\/statistics-on-partitioned-tables-part-1\/","url_meta":{"origin":1016,"position":4},"title":"Statistics on Partitioned Tables – Part 1","date":"February 17, 2010","format":false,"excerpt":"If you've ever worked on large databases that use partitioned and subpartitioned tables, you'll be aware that there are significant challenges in maintaining up-to-date\/appropriate statistics. We've encountered a few problems at work recently and I decided it would be an idea to put together a series of posts covering the\u2026","rel":"","context":"With 11 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":973,"url":"http:\/\/orcldoug.com\/blog\/2005\/06\/10\/the-importance-of-ofa\/","url_meta":{"origin":1016,"position":5},"title":"The importance of OFA","date":"June 10, 2005","format":false,"excerpt":"When discussing Cary Millsap and his work (for example during this week's user group presentation or when teaching courses), I've often said that I wish he was more widely known for one of my favourite pieces of work - the various Optimal Flexible Architecture documents and standards. I think one\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1016","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=1016"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1016\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1016"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1016"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1016"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}