{"id":856,"date":"2006-02-25T12:00:00","date_gmt":"2006-02-25T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=856"},"modified":"2006-02-25T12:00:00","modified_gmt":"2006-02-25T12:00:00","slug":"mistakes","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2006\/02\/25\/mistakes\/","title":{"rendered":"Mistakes"},"content":{"rendered":"

It’s the lessons you learn or the things you discover when you make mistakes that can be the most useful. Here’s an example of that and also of why you shouldn’t try to take too many shortcuts. (It might be an interesting exercise for you to see how long you can read through this blog before you spot the error<\/span>)<\/p>\n

Whilst working on the ‘How Many Slaves’ paper for Hotsos, I wanted to try some multi-user tests as I approached the deadline. Now I hate working fast. I can do it, but I much prefer to have my mind and my work organised in advance. In this case, I made a stupid mistake. My intention was to try a large number of user sessions using parallel at various degrees and I was trying to eliminate disk I\/O. One of the disappointments about the paper I alluded to earlier<\/a> was that most of the cases suffered from I\/O bottlenecks.<\/p>\n

So I added setup3.sql to create a number of much smaller tables to work with. It was always a horrible cut-and-paste job but if it did the job and I made it for the deadline, I could live with that. Here’s a section that creates the first two of the smaller tables (leaving test_tab1 and test_tab2 as the bigger tables)<\/p>\n

drop sequence test_seq3;drop table test_tab3;drop sequence test_seq4;drop table test_tab4;
<\/p>create table test_tab3pctfree 90 pctused 10as select test_seq3.nextval PK_ID, MOD(test_seq3.nextval, 10) NUM_CODE,dbms_random.string('A', 100) STR_PADfrom all_objectswhere rownum <= 1000;      
<\/p>create table test_tab4   pctfree 90 pctused 10   as select test_seq4.nextval PK_ID, MOD(test_seq4.nextval, 10) NUM_CODE,   dbms_random.string('A', 100) STR_PAD from all_objects where rownum <= 1000;<\/pre>\n
At this point, I decided that I might as well just create all of the other small tables based on test_tab3, so I changed the code to<\/p>\n
create table test_tab3pctfree 90 pctused 10as select test_seq3.nextval PK_ID, MOD(test_seq3.nextval, 10) NUM_CODE,dbms_random.string('A', 100) STR_PADfrom all_objectswhere rownum <= 1000;     
<\/p>create table test_tab4   pctfree 90 pctused 10   as select * from test_tab3;<\/pre>\n
I then pad the tables out a bit by duplicating the data with INSERT .. SELECT statements (I did say it was a bit clunky!)<\/p>\n
insert \/*+ parallel(test_tab3, 2) append *\/into test_tab3select \/*+ parallel(test_tab3, 2) *\/test_seq3.nextval, NUM_CODE, STR_PADfrom test_tab3;
<\/p>commit;
<\/p>insert \/*+ parallel(test_tab4, 2) append *\/into test_tab4select \/*+ parallel(test_tab4, 2) *\/test_seq4.nextval, NUM_CODE, STR_PADfrom test_tab4;
<\/p>commit;<\/pre>\n
And then these tables are queried by statements similar to this. Note a couple of things about this statement. First, every statement joins test_tab3 to one of the tables from test_tab4 – test_tab10 but the table aliases can make things look confusing. (I can’t believe I came up with this, but it was late at night and when you see the surrounding shell scripts it makes more sense)<\/p>\n
SELECT \/*+ parallel(tt1, 1) parallel(tt2, 1) *\/  MOD(tt1.pk_id + tt2.pk_id, 113), COUNT(*)FROM test_tab3 tt1, test_tab4 tt2WHERE tt1.pk_id = tt2.pk_idGROUP BY MOD(tt1.pk_id + tt2.pk_id ,113)ORDER BY MOD(tt1.pk_id + tt2.pk_id ,113);<\/pre>\n
When I ran the tests, I was confused about the variation in run times but, when I looked at the log and trace files I collected it became clear. Here is the output of one of the quicker queries (although the time differences are small because I’m only running a few sessions here)<\/p>\n
SQL> SELECT \/*+ parallel(tt1, 1) parallel(tt2, 1) *\/ MOD(tt1.pk_id + tt2.pk_id,113), COUNT(*)2  FROM test_tab3 tt1, test_tab4 tt23  WHERE tt1.pk_id = tt2.pk_id4  GROUP BY MOD(tt1.pk_id + tt2.pk_id ,113)5  ORDER BY MOD(tt1.pk_id + tt2.pk_id ,113);
<\/p>MOD(TT1.PK_ID+TT2.PK_ID,113)   COUNT(*)---------------------------- ----------                           0          8                           1          9                           2          9                           3          9                           4          9                           5          9                           6          9                           7          9                           8          9                           9          9                          10          9snipped ...
<\/p>                         109          8                         110          9                         111          8                         112          9
<\/p>113 rows selected.
<\/p>Elapsed: 00:00:07.49<\/pre>\n
And here’s the output of one of the slower queries<\/p>\n
SQL> SELECT \/*+ parallel(tt1, 1) parallel(tt2, 1) *\/ MOD(tt1.pk_id + tt2.pk_id,113), COUNT(*)2  FROM test_tab3 tt1, test_tab6 tt23  WHERE tt1.pk_id = tt2.pk_id4  GROUP BY MOD(tt1.pk_id + tt2.pk_id ,113)5  ORDER BY MOD(tt1.pk_id + tt2.pk_id ,113);
<\/p>MOD(TT1.PK_ID+TT2.PK_ID,113)   COUNT(*)---------------------------- ----------                           0       1104                           1       1111                           2       1106                           3       1111                           4       1106                           5       1111                           6       1107                           7       1110                           8       1107                           9       1110                          10       1108snipped ...                         109       1105                         110       1111                         111       1104\n                         112       1105113 rows selected.
<\/p>Elapsed: 00:00:09.10<\/pre>\n
So the queries are running against different data with different results, but why? Well, when I took the shortcut of just duplicating test_tab3 to create the other 7 tables, I forgot that I would then be using test_seq4 to generate the Primary Key for test_tab4, but that was still set at it’s initial value of 1 because it hadn’t been increased in the same way as test_seq3 because it hadn’t been used in the initial table creation!<\/p>\n
In fact, I found that I was using the wrong sequence in a couple of places!<\/p>\n
So, lessons learned :-<\/p>\n
1) This wouldn’t have happened if I’d had a primary key constraint on the tables –
\nthe error would have been spotted immediately when it failed to insert a duplicate row.<\/p>\n
2) (And I already mention
\nthis in the paper) Collect as much data as you can when running your tests, because it helps with problem diagnosis.<\/p>\n
3) It might *just* be a test, but you probably need to take even more care about scripts of this nature than some ‘real’ w
\nork because the results are so important.<\/p>\n
4) As you approach a deadline, you probably need to slow down and be mor
\ne careful, not speed up and be less careful 😉<\/p>\n
5) The most interesting and useful bit for me at the moment is that, even though similar amounts of data are being read from the buffer cache for the full table scans, I’m starting to see som
\ne useful differences in run times, so I’m discovering the type of issue I was hoping to see, but by accident!<\/p>\n
The
\nonline copy of the paper has been replaced. I know a few people have downloaded it and I’ve had some good feedback so far, bu
\nt I don’t think anyone had run the multi-user tests yet!<\/p>\n","protected":false},"excerpt":{"rendered":"
It’s the lessons you learn or the things you discover when you make mistakes that can be the most useful. Here’s an example of that and also of why you shouldn’t try to take too many shortcuts. (It might be an interesting exercise for you to see how long you can read through this blog… Continue reading Mistakes<\/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-856","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":840,"url":"http:\/\/orcldoug.com\/blog\/2006\/03\/08\/how-many-slaves\/","url_meta":{"origin":856,"position":0},"title":"How Many Slaves?","date":"March 8, 2006","format":false,"excerpt":"Parallel Execution and the Magic of 2That's my second presentation, at 2:15 today. I've thought long and hard about this one and hope I've made the right decision. I was concerned about how to encapsulate a 50+ page paper into a one hour slot. I hate presentations which are full\u2026","rel":"","context":"With 5 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1215,"url":"http:\/\/orcldoug.com\/blog\/2007\/02\/25\/almost-there\/","url_meta":{"origin":856,"position":1},"title":"Almost There","date":"February 25, 2007","format":false,"excerpt":"Well, after months of prevarication, staring into space and waiting hours for about five words to come out, I finally had the breakthrough that I was looking for with the Hotsos Symposium paper today. I wouldn't say it was 12 hours solid work, but I haven't done much else.It's still\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":847,"url":"http:\/\/orcldoug.com\/blog\/2006\/03\/06\/hotsos-day-0\/","url_meta":{"origin":856,"position":2},"title":"Hotsos – Day 0","date":"March 6, 2006","format":false,"excerpt":"Well, that's me registered. Despite all my best efforts to get some sleep this afternoon and hopefully get myself back in sync, it didn't happen. I went for a liquid lunch with David Aldridge who turns out to be the nice guy and cantankerous old British b*gger I expected and\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":932,"url":"http:\/\/orcldoug.com\/blog\/2005\/10\/17\/case-article\/","url_meta":{"origin":856,"position":3},"title":"CASE Article","date":"October 17, 2005","format":false,"excerpt":"I've uploaded the MS Word and PDF versions of a new paper about CASE expressions, which is really just a replacement for the earlier DECODE paper. This one has been an unexpected trial, probably because it was a bit of a chore that I knew I had to get round\u2026","rel":"","context":"With 6 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":939,"url":"http:\/\/orcldoug.com\/blog\/2005\/09\/16\/decode-paper\/","url_meta":{"origin":856,"position":4},"title":"DECODE paper","date":"September 16, 2005","format":false,"excerpt":"Over the past few months I've occasionally pondered what to do about the DECODE paper on this site.On the one hand, it was written 6 years ago when DECODE was more prevalent than CASE so it's out of date.On the other, it's popular and is the main reason by far\u2026","rel":"","context":"With 3 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1124,"url":"http:\/\/orcldoug.com\/blog\/2006\/11\/07\/recovery-design-part-5-wrap-up\/","url_meta":{"origin":856,"position":5},"title":"Recovery Design Part 5 – Wrap-up","date":"November 7, 2006","format":false,"excerpt":"It's been a very mini-series but I hope I've highlighted some of the challenges when designing systems that need to recover from failures quickly. Here are a few ideas and I'm sure others could add their own.Careful planning is essential.Ask tough questions and imagine the worst.Always be aware of the\u2026","rel":"","context":"With 7 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/856","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=856"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/856\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=856"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=856"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=856"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}