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)
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 was that most of the cases suffered from I/O bottlenecks.
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)
drop sequence test_seq3;
drop table test_tab3;
drop sequence test_seq4;
drop table test_tab4;
create table test_tab3
pctfree 90 pctused 10
as select test_seq3.nextval PK_ID, MOD(test_seq3.nextval, 10) NUM_CODE,
dbms_random.string('A', 100) STR_PAD
from all_objects
where rownum <= 1000;
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;
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
create table test_tab3
pctfree 90 pctused 10
as select test_seq3.nextval PK_ID, MOD(test_seq3.nextval, 10) NUM_CODE,
dbms_random.string('A', 100) STR_PAD
from all_objects
where rownum <= 1000;
create table test_tab4
pctfree 90 pctused 10
as select * from test_tab3;
I then pad the tables out a bit by duplicating the data with INSERT .. SELECT statements (I did say it was a bit clunky!)
insert /*+ parallel(test_tab3, 2) append */
into test_tab3
select /*+ parallel(test_tab3, 2) */
test_seq3.nextval, NUM_CODE, STR_PAD
from test_tab3;
commit;
insert /*+ parallel(test_tab4, 2) append */
into test_tab4
select /*+ parallel(test_tab4, 2) */
test_seq4.nextval, NUM_CODE, STR_PAD
from test_tab4;
commit;
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)
SELECT /*+ parallel(tt1, 1) parallel(tt2, 1) */
MOD(tt1.pk_id + tt2.pk_id, 113), COUNT(*)
FROM test_tab3 tt1, test_tab4 tt2
WHERE tt1.pk_id = tt2.pk_id
GROUP BY MOD(tt1.pk_id + tt2.pk_id ,113)
ORDER BY MOD(tt1.pk_id + tt2.pk_id ,113);
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)
SQL> SELECT /*+ parallel(tt1, 1) parallel(tt2, 1) */ MOD(tt1.pk_id + tt2.pk_id,
113), COUNT(*)
2 FROM test_tab3 tt1, test_tab4 tt2
3 WHERE tt1.pk_id = tt2.pk_id
4 GROUP BY MOD(tt1.pk_id + tt2.pk_id ,113)
5 ORDER BY MOD(tt1.pk_id + tt2.pk_id ,113);
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 9
snipped ...
109 8
110 9
111 8
112 9
113 rows selected.
Elapsed: 00:00:07.49
And here’s the output of one of the slower queries
SQL> SELECT /*+ parallel(tt1, 1) parallel(tt2, 1) */ MOD(tt1.pk_id + tt2.pk_id,
113), COUNT(*)
2 FROM test_tab3 tt1, test_tab6 tt2
3 WHERE tt1.pk_id = tt2.pk_id
4 GROUP BY MOD(tt1.pk_id + tt2.pk_id ,113)
5 ORDER BY MOD(tt1.pk_id + tt2.pk_id ,113);
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 1108
snipped ...
109 1105
110 1111
111 1104
112 1105
113 rows selected.
Elapsed: 00:00:09.10
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!
In fact, I found that I was using the wrong sequence in a couple of places!
So, lessons learned :-
1) This wouldn’t have happened if I’d had a primary key constraint on the tables –
the error would have been spotted immediately when it failed to insert a duplicate row.
2) (And I already mention
this in the paper) Collect as much data as you can when running your tests, because it helps with problem diagnosis.
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
ork because the results are so important.
4) As you approach a deadline, you probably need to slow down and be mor
e careful, not speed up and be less careful 😉
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
e useful differences in run times, so I’m discovering the type of issue I was hoping to see, but by accident!
The
online 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
t I don’t think anyone had run the multi-user tests yet!