Posts Tagged 3e
During the OCM Preparation Workshop today in Bucharest, we got into a discussion about parallel inserts vs. direct load with append hint, where I said something like: “I think a parallel insert is always* also a direct load.” Most attendees were satisfied with that, but some still looked a bit sceptical. And I was also not 100% sure about it. While the others practiced, I did a brief research and came up with this answer from Tom Kyte and quoted him: “Parallel is always a direct path, if you go parallel, you will be appending.” Everybody in the room was immediately convinced – only I felt a bit uncomfortable with the outcome myself. After all, I don’t want to teach people to blindly follow my or any other guys claims without checking it if possible. And as with so many other claims about Oracle, it is possible to prove (or falsify) it with little effort:
SQL> grant dba to adam identified by adam; Grant succeeded. SQL> connect adam/adam Connected. SQL> create table t as select 'TEST' as col1 from dual connect by level<=1e6; Table created. SQL> select bytes/1024/1024 from user_segments; BYTES/1024/1024 --------------- 13 SQL> delete from t; 1000000 rows deleted. SQL> commit; Commit complete. SQL> insert into t select 'TEST' as col1 from dual connect by level<=1e6; 1000000 rows created. SQL> commit; Commit complete. SQL> select bytes/1024/1024 from user_segments; BYTES/1024/1024 --------------- 13
The above shows that a conventional insert reuses the empty space before the high water mark of the table and does not allocate additional extents. Now I’m going to do a parallel insert:
SQL> delete from t; 1000000 rows deleted. SQL> commit; Commit complete. SQL> alter table t parallel; Table altered. SQL> alter session enable parallel dml; Session altered. SQL> insert into t select 'TEST' as col1 from dual connect by level<=1e6; 1000000 rows created. SQL> commit; Commit complete. SQL> select bytes/1024/1024 from user_segments; BYTES/1024/1024 --------------- 24.375
QED! Obviously, the parallel insert appended rows after the high water mark – it was a direct load, in other words. That is in itself not so spectacular, but that little anecdote illustrates the in my opinion proper way to deal with technical questions about Oracle: Always try to back up your assumptions respectively claims with proof instead of relying only on (your or others) authority.
In other words: Don’t believe it, test it!
*Addendum: Never say always, I always say: If you include the NOAPPEND hint to the parallel insert, it will not be done as a direct load. Thank you guys for pointing that out!
After years of teaching and studying (holding a Bachelor’s Degree in Educational Science), I think I’m now able to put my experience & conclusions into the this formular:
Even a complex technical topic (like an Oracle Database) should be explained to the audience in an easy understandable way. That is of course only possible if the instructor knows his stuff thoroughly but also resists the temptation to blind the audience with his deep knowledge in order to appear clever. Things get complex by themselves soon enough. The explanations must be technically correct, though, but they may be simplified. Teaching will always show a model of the reality – and that model should hold water after the knowledge of the audience expands.
Two extremes that illustrate what should be avoided here: 1) “The Expert” The instructor is deeply involved in some complex technical matter and tells many specific details about that to the audience that they can hardly understand or benefit from, which the instructor does not recognize. 2) “The Ignorant” The instructor knows little about the subject (reading introductions during breaks) and just fantasizes answers if the questions are too difficult.
It is not enough to show only slides in order to achieve a good understanding! Demonstrations will not only make the teaching more lively, they will also prove (or sometimes falsify even) the statements of the instructor. They should be reproducible, so that the audience can see for themselves. A claim that essentially only says: “I know that because I’m an expert, trust me!” is not acceptable.
The ultimate goal of the instructor should be to empower the recipients to use the explained technology themselves in an effective and efficient way. Accompanying practices during a course help a lot to reach that goal. In absence of practices, demonstrations are the second best way to empower the audience. They should realize that they can do these things also, not only the instructor. Teaching is not supposed to be a sales pitch for products or services!
That’s already it – easy, right? Some hard work involved under the covers, though…
My best days are when I encounter former attendees of my courses, telling me how much they liked it and that they actually could make use of the contents and implemented this and that feature during a certain project. It happens every now and then, when I think to myself: Empowered – Good Job