Top 60 Oracle Blogs

Recent comments

January 2011

What Makes a Great Presentation

What makes a great presentation? Everyone successful presenter from Steve Jobs to Tony Robbins have been studied for patterns. Some of the pre-requisites seem to be:

(1) Prepare. Prepare for 100+ hours for an 1 hour presentation
(2) Have a backup. If something goes wrong, have an assistant fix things while you entertain the audience
(3) Have jokes ready, and crack them when you encounter a technical challenge
Well, I was thinking about it while delivering the Oracle Celebrity Seminar series today and saw a reference to Steve Jobs' presentation during iPhone 4 launch where he had a wifi challenge. How convenient for the speakers to say that. Who has the luxury of practicing a speech of 1 hour for 100 hours, or having an "assistant" work things out behind the scenes, or the prowess of a comedian delivering seemingly good jokes turned horrid?
Definitely not me.
I delivered my very first public seminar in October 2002 in Richmond, VA. In the last eight and half years I have presented about 150 such sessions, and 24 day long seminars. Over all that time and sessions I have been been largely applauded; but heckled, humiliated and horrified as well. Laptops not starting, internet not connecting, powerpoint crashing - I have seen it all; not to mention travel plans disrupted. But above all, I have learned, learned from every step of the way to be better as a speaker and more effective to the attendees. There is no secret; but some simple pointers. Here I am going share with you some of those lessons.
(1) Empathize with the Audience
What type of presenter are you - do you know your subject well? Or, you research something and present what you find. I have met some folks who are great in delivering a topic in a flawless manner - any topic, without any mastery. They are eloquent and polished "speakers"; not subject matter experts. If you are lucky enough to be one, good for you; but most folks, including yours truly, are not. Most of us are *not* great speakers. So, would you throw up your arms in frustration and retreat to the cozy corner? 
Not at all. Remember, the audience in a technical setting is not there to listen to your eloquence; but to the substance. Of course, being entertaining helps; but it's not the core. The attendees like the talk if  they can relate with you. You must know the audience members, their needs, their challenges and the thoughts in their mind. If you are one of them, they immediately connect with you and deem your talk it valuable. If you are detached, no matter how valuable your talk is, they are less likely to appreciate.
I start most of my talk with a simple sentence, that I am just like most folks in the audience. I understand their issues, because I face the same every day. I have been fortunate to be able to address an audience like that; perhaps it's not possible in every case. But you must understand the audience's needs and wants and step into their shoes. When they talk about issues, be immediately appreciative, never dismiss it as trivial, try to understand from their perspective and you will be able to see a whole new world. The moment the audience feels that you are in sync with them, they immediately feel comfortable and try to get in sync with you - a win win situation that is the key to a successful presentation.
(2) Be Passionate
Show some passion, a lots of it. You have worked hard on the session and prepared the material meticulously; but all is moot unless you show it at he stage. Believe in the material, each word of it, believe in the usefulness and need to the audience. But just believing in itself does not work; show it. Show the enthusiasm and vigor while describing. The passion you demonstrate is contagious; the audience gets fired up as well. An engaged audience is the best audience.
(3) Show Respect and Appreciation
You have been invited to speak (perhaps you have been reimbursed your expenses and even paid a hefty speaker fee) before this audience. The audience has paid good money to have you speak there. So, they are lucky to have you, right?
A speaker is nothing without an audience. The audience is not lucky, you are. You are fortunate to be taken into the role where the audience has invested in you. Never, forget that. Even if the audience paid nothing in monetary terms, they paid - their time, their valuable time. Never assume that they have come in their own free time. There is no such thing called free time; it's an oxymoron. One uses the time from something more important, which could be a nap, a walk in the beach, spending time with family. The fact that an attendee has forgone all that to listen to you speak is an honor for you. Always remember that and appreciate the audience for attending. Always remember that you are here to earn their respect; and you must respect their biggest investment - their time - in you. You must not disappoint them. Without them you don't exist.
(4) Be Spontaneous
What about practice? If you can afford it, you should definitely practice. But most of us probably don't have that luxury. Relax, that's not important. If you have considered and incorporated the first three advices, especially being passionate, you will feel that you now have an sufficient thrust to launch you into orbit. The rest becomes surprising easy. Practicing may make it perfect; but it also brings in staleness into the delivery. If you are showing demos, you should definitely practice that; but the actual content of your presentation should be left to the stage. I never practice the presentation; but of course, choose what works for you.
(5) Correct Posture and Delivery
Always, always, always maintain eye contact with the audience. Don't look over them, in front of them, and most definitely not at the screen. You are the speaker, not the screen. 
Stand straight; but relaxed. If you slouch, place your thumbs in your pocket, the appearance seems callous and you will kill the passion angle.
Spread your ams wide - a universal sign of acceptance and inclusion. The audience feels more comfortable y feeling included and will be more receptive. Never fold your arms, especially while listening to a question from the audience.
While speaking "project" your voice. That is very different from a conversational tone. You should practice that in front of a mirror. Basically you "throw" your voice away. There should be distinct gaps between words. Imagine you are in an echo chamber and each word is followed by an echo forcing you to pause before speaking the next word. 
When asked a question from the audience, always repeat the question. It's likely that the parts of the audience might not have heard it so the answer will seem completely out of context unless you repeat the question. It will also help explain to the questioner how you interpreted the question.
(6) Spare Details in Slides
Remember, slides are to bring the audience's attention to a point, not to describe the point itself. Never crowd the slides. A few bullet points work best, unless the slides are also the course material. A better alternative is putting the descriptions in the "Notes" section of the slides, which will print; but not shown on screen. When you use a crowded slide, attendees focus more on reading that rather than listening to you.
(7) Have a Backup
If you have a slide deck, back it up on a USB stick. I carry two USB sticks in addition to the deck in the laptop. If you can, send a copy to someone at the meeting site as a backup, just in case. I also burn it into a CD, just in case the USB ports are disabled. Some computers may have that.
I also back up the files into some free online storage - Dropbox, Google Docs and email them to myself. I may not know which sites may be blocked; so options are always better.
What tool did you use? Powerpoint, Open Office, or something else? It's better to ask than assume. If Powerpoint, which version - 2003, 2007 or something else? What happens if you have the slides in an imcompatible mode?
That's why I also carry a PDF version of the presentation (create free PDF by Primo PDF , along with the Acrobat Reader in the same USB stick and the CD. In the worst case, I will be able to show it right from the removable media. I will not be able to use all the fancy features such as animation; but it's better than nothing at all. Make sure you export the fonts when you create the PDF. Not all the fonts may be available in the target computer.
What if you want to show some demo? Will everything work? Don't count on it. Murphy's Law kicks in at the worst possible time. Always run the demo prior to the session, capture the screens using the free CamStudio tool ( It needs a flash player, which may not be present; so you should also take some screenshots using Windows Vista's Sniping Tool or even Alt-PrintScreen. Again, they may not wow the audience, but will save the day.
(8) Have the Right Tools
Are you presenting abroad? Learn about the power requirements of the country - is is 115 volts or 220? Is the power plug the right type? Do you need and have an adapter? Learn about power requirements here:
What about the laser pointer? Do you have one? If you do, do you have an extra battery? Even if you use, always carry a stick pointer. Recently while delivering a session in London, I saw a quite high tech environment - a huge LCD panel instead of the screen and projector. The laser pointer was ineffective. Fortunately I had my collapsible stick pointer.
Finally, don't think of this as ordeal. Have fun. If you fail in making the same effect on the audience, relax; the world will not end. They will not think any less of you. People have other things to worry about; your supposedly fall from pedestal is least of them.
I hope you liked it and found it useful. Let me know other tips you may have for a great presentation.

Shared Server – 4

In earlier posts we looked at v$reqdist and v$queue, which report time spent running tasks, and time spent waiting in the COMMON and DISPATCHER queues.

I mentioned in the previous article that if we see too much time spent in the COMMON queue(s) then perhaps we needed more shared servers. Moving to the other end of the dialogue, one of the reasons why we might spend too much time waiting in a DISPATCHER queue for the result to go back to the user is that we don’t have enough dispatchers – and we can get a clue about this from the view v$dispatcher:

column messages     format 999,999,999,999
column bytes        format 999,999,999,999
column idle         format 999,999,999,999
column busy         format 999,999,999,999
column total_time   format 999,999,999,999
column busy_percent format 999.99

        name, /* network, */ messages, bytes,
        idle, busy,
        idle + busy total_time,
        100 * round(busy/nullif(idle+busy,0),4) busy_percent

NAME         MESSAGES            BYTES             IDLE             BUSY       TOTAL_TIME BUSY_PERCENT
---- ---------------- ---------------- ---------------- ---------------- ---------------- ------------
D000      341,902,864   -1,875,035,869      498,676,896      897,199,945    1,395,876,841        64.28
D001      351,090,918     -860,132,585    1,672,899,708      216,085,537    1,888,985,245        11.44
D002        3,543,576    1,820,366,239        6,602,929           82,744        6,685,673         1.24
D003        5,994,180   -1,007,460,261        6,539,742          145,927        6,685,669         2.18

Unfortunately, it looks as if the critical columns in this view are recorded as 32-bit signed, which means they wrap from positive to negative at about 2,000,000,000 – and this means the figures for D000 and D001 are complete garbage. In my last note I pointed out that I had started up two extra dispatchers on a system that had been running for quite a long time – which is why dispatchers D002 and D003 have such small number compared to the others – they’ve only been running about 18 hours (66,857 seconds).

Clearly, to get some sensible figures, you really need to play around with snapshots and deltas and worry about all the usual problems of collecting information for the right interval. Even so, these figures do show you that D002 and D003 have been idle for most of the time they’ve been up – but you’ll have to take it from me that the 827 seconds and 1,459 seconds they’ve recorded as busy time was a small fraction of a soak test that we were running. It’s not obvious from the absolute figures, but with the background information I have I can say that there was a small benefit from having four dispatchers, but nothing significant.

Note: if we were able to trust the 64.28% figure for dispatcher D000 we could be reasonably confident that we needed at least the second dispatcher simply on the basis of the work being done by D000; but we might also worry about it for another reason – if the dispatcher is very busy, it’s possible that this is just a symptom of the whole machine being busy, in which case it’s possible that the dispatcher isn’t able to get CPU time to do its work.

[Further reading on Shared Server / MTS]

Oracle11g Bitmap-Join IOTs (Us and Them)

With each new database release, nice little improvements and enhanced options continually get added. Since 11g R1, two index related features can finally be used in combination with each other.   To demonstrate, I’m first going to create and populate a so-called “large” Data Warehouse table.      I’ll next create a standard bitmap index [...]

Spam: The ups and downs…

Managing spam forum posts, blog comments and website comments drives me to distraction at times. Having said that it sometimes has its up side.

This morning, whilst clearing a bunch of spam posts and comments I threw my toys out of the pram and declared to everyone present (nobody was in the room with me) that I’m through with the internet and I’m turning everything off. Fast forward a few hours and a couple of new spam comments arrive, both of which were relevant to the threads they were posted on and almost seemed useful and mildly insightful. In fact, the only thing that distinguished them as spam was the posters URL. One was a dieting site and one was a heamaroid treatment. The latter made me giggle (yes, I am that puerile) so I will not divorce the internet quite yet.



Watching Consistent Gets – 10200 Trace File Parser

January 24, 2011 It happened again, another blog article that forced me to stop, think, and … hey, why did Oracle Database do something different than Oracle Database  What is different, even when the OPTIMIZER_FEATURES_ENABLE parameter is set to (or  The number of consistent gets for a SQL statement is significantly different - we did [...]

Quiz Night

I've recently come across an interesting variation of a "famous" ASSM bug. Probably some of you will remember that ASSM bug that was caused by row migrations in larger block sizes (16K/32K).

If you don't remember or don't know what I'm talking about, you can have a look here where Greg Rahn provides a summary of the issue or check My Oracle Support bug description 6918210.

Greg also links to a script originally created by Jonathan Lewis that allows to reproduce the issue at will.

So far the issue was only reproduced on block sizes greater 8K - the variation I've encountered however allows to reproduce the issue on 8K and 4K, possibly also on 2K, but I haven't tested 2K yet.

Below is my version of script. If you compare it to Jonathan's version you'll notice that it is very similar, if not to say almost the same except for additional optional instrumentation, that you can simply un-comment if you've installed my Advanced Oracle Troubleshooting script package that is based on Tanel Poder's awesome "tpt_public" tool set.

The SESSPACK tool can be found in Tanel's tool set (tools/sesspack_0.05_release) and the SNAP_KCBSW package has been developed by Jonathan a long time ago - it can be found here. Note that it only works for versions below 11g - this instrumentation has been "optimized away" in 11g, unfortunately.

In order to reduce the runtime, I've simply limited the number of rows in the table to 50,000 rows.

set echo on timing on

drop table t1;

purge table t1;

TABLESPACE &tblspace;

INSERT --+ append
SELECT TRUNC(dbms_random.VALUE(10000000,100000000)) n1,
FROM dual

BEGIN dbms_stats.gather_table_stats(
ownname => null,
tabname => 'T1');

SELECT num_rows,blocks FROM user_tables WHERE table_name = 'T1';

/* Uncomment for instrumentation
@trci assm_bug



exec sesspack.snap_me

execute snap_kcbsw.start_snap

@46on 8

alter session set events '10046 trace name context forever, level 8';

UPDATE t1 SET n2 = n1;


/* Uncomment for instrumentation
@trci assm_bug_off


alter session set events '10046 trace name context off';

/* Uncomment for instrumentation
set serveroutput on size 1000000 format wrapped
set linesize 120
set trimspool on

execute snap_kcbsw.end_snap

exec sesspack.snap_me

BEGIN dbms_stats.gather_table_stats(
ownname => null,
tabname => 'T1');

SELECT num_rows,blocks FROM user_tables WHERE table_name = 'T1';

/* Uncomment for instrumentation
@trc_orasrp &trc_p &trc_f

@trc_tvdxtat &trc_p &trc_f

Here is the task: You are allowed to modify the script at exactly one single location - the modification can take a maximum of four keywords, which means you can add/modify/remove at most four keywords.

With the correct modification you will be able to reproduce the bug even in 8K and lower block sizes.

So, what to modify and why?

If you want to actually run the script yourself you need to use database versions prior 11.2 because the bug is obviously fixed there - this includes, which interestingly doesn't have the bug fixed.

I've used a 8K/4K ASSM tablespace with UNIFORM 1M extents for my tests, but I don't think that the extent management matters in that case. My test database uses 8K as default block size.

You'll notice the bug when checking the runtime and the trace file. If you encounter the bug, the runtime for the update will be several seconds (more than 10 seconds seen on my test system in some cases) and the number of current mode gets for the update will be in the millions.

If you've enabled the additional instrumentation it will tell you that the reasons for the buffer gets where "ktspfsrch" and "ktspscan_bmb" for most of the gets. You can also take stack traces (e.g. using Tanel's OStackProf tool) if you use more than 50,000 rows to have a longer runtime of the update statement which will show you similar function names on the stack.

If you don't hit the bug, the update usually takes max. 1-2 seconds, and the current mode gets should be far less than one million when sticking to the 50,000 rows.

P.S.: There is more than one correct answer - and it is possible to hit the bug for 8K block sizes with a single keyword modification (full points!).

Update 24th Jan: P.P.S: No takers yet... So here's an additional hint: The issue is caused by row migration...

Update 26th Jan: OK, time to post a quick answer here. As pointed out by Narendra below, simply setting PCTFREE to 0 already was sufficient to reproduce the issue with smaller block sizes. However, there is much more to tell about and therefore this deserves a separate post that I'll publish the next couple of days.

For the time being here are the correct answers that I'm aware of at present:


But as I already said, there is much more, in particular when partitioning comes into the picture - and I hope to cover all these details in the upcoming post.

Teradata CAP定理

参考:Teradata RDBMS SQL Reference - Volume 2 Statement and Transaction Processing

Atomicity A transaction either occurs or it does not. No matter how many component SQL operations are specified within the boundaries of a transaction, they must all complete successfully and commit or they must all fail and rollback. There are no partial transactions.
Consistency A transaction transforms one consistent database state into another. Intermediate inconsistencies in the database are not permitted.
Isolation The operations of any transaction are concealed from all other transactions until that transaction commits.
Durability Once a commit has been made, the new consistent state of the database survives even if the underlying system crashes.



Well I turned in the HCC chapter on the Exadata book last week and of course as is usually the case, I immediately ran across something kind of cool on the topic I just finished writing about. (we still have several editing passes though, so I can add it later). Anyway, although I don’t have time to post much these days, I thought this one would be a quick little snippet. So here it is.

The Compression Advisor is part of the DBMS_COMPRESSION package. Specifically it is the GET_COMPRESSION_RATIO procedure. This procedure is worthy of a separate post but I won’t discuss it here except to say that as of you can use it to test HCC compression ratios on non-Exadata platforms. That’s pretty cool, but what I wanted to tell you about is a handy little function in the same package called GET_COMPRESSION_TYPE. This function can tell you exactly what level of compression has been applied to a single row. This can come in handy for investigating the inner workings of  HCC (or OLTP or BASIC compression for that matter).

As you probably already know, HCC is only applied to records loaded via direct path writes. Any updates cause rows to be migrated out of that storage format into blocks flagged for OLTP compression. Of course OLTP compression on a block only kicks in when a block is “full”. On top of this, altering a table to change it’s compression does not actually change the storage format of any existing records (unless you use the MOVE keyword). So you could load some data and then change the designation (say from QUERY LOW to QUERY HIGH). Rows that are inserted after the change will be stored in the new format (assuming the records are loaded via direct path writes of course). So why am I telling you all this. Well, because I ran across a statement in some Oracle documentation that said you can check to see what compression method a table is stored with by looking at the COMPRESS_FOR column in the DBA_TABLES view. This column does reveal what the table designation is. However, the setting actually only tells you how rows inserted in the future will be compressed. It tells you absolutely nothing about the way current rows are stored.

As for the mechanics, it appears that each row has a bitmask associated with it showing what compression format is being used. So I wrote a little script to give me what I want to see (check_row_comp.sql) using the DBMS_COMPRESSION.GET_COMPRESSION_TYPE function. Here’s an example of its use.

== Note this listing has been updated to fix the bitmask as suggested by Greg in the comments (I had it wrong initially)
SYS@SANDBOX1> !cat check_row_comp.sql
col old_rowid for a20
There is a bit mask that indicates level of compression
10000000 (1) = no compression
01000000 (2) = BASIC/OLTP
00100000 (4) = QUERY HIGH
00010000 (8) = QUERY LOW
00001000 (16) = ARCHIVE HIGH
00000100 (32) = ARCHIVE LOW
old_rowid(rowid) old_rowid,
DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( '&owner', '&table_name', '&rowid'), 
1, 'No Compression',
2, 'Basic/OLTP Compression', 
4, 'HCC Query High',
8, 'HCC Query Low',
16, 'HCC Archive High',
32, 'HCC Archive Low',
'Unknown Compression Level') compression_type
from dual;
SYS@SANDBOX1> select rowid from kso.skew_hcc3 where pk_col = 444444;
1 row selected.
SYS@SANDBOX1> @check_row_comp
Enter value for owner: KSO
Enter value for table_name: SKEW_HCC3
Enter value for rowid: AAAWbXAAIAAGAfiBdz
-------------------- -------------------------
1.929.0              HCC Archive Low
1 row selected.
SYS@SANDBOX1> update kso.skew_hcc3 set col1 = col1*2 where pk_col=444444;
1 row updated.
SYS@SANDBOX1> commit;
Commit complete.
SYS@SANDBOX1> select rowid, old_rowid(rowid) old_rowid from kso.skew_hcc3 where pk_col = 44444;
ROWID              OLD_ROWID
------------------ --------------------
AAAWbXAAIAAF7aUAAA 8.1554068.0
1 row selected.
SYS@SANDBOX1> -- row migrated to file 8
SYS@SANDBOX1> @check_row_comp
Enter value for owner: KSO
Enter value for table_name: SKEW_HCC3
Enter value for rowid: AAAWbXAAIAAF7aUAAA
-------------------- -------------------------
1.929.0              No Compression
1 row selected.

I did a previous post (Proof That Whole CU’s Are Not Decompressed) where I showed row migration to a block flagged for OLTP compression, but of course the OLTP compression would not be applied until the block was sufficiently full. As you can see in the example, the function showed that the record was not actually compressed, even though it had been migrated to a block flagged for OLTP compression.

So the GET_COMPRESSION_TYPE function is handy for showing us how an individual record is actually stored. Of course you can see this by dumping blocks as well, but I think this function is much easier than looking at block dumps. You could also write a script to sample some percentage of the rows in a table to get a feel for how well compressed the rows are, but I will leave that as an exercise for the reader. (please let me know if you undertake that task as it is something I will eventually want to do as well) ;)

Captain Support to the rescue…

It’s Saturday night, South Park is on the TV and all is good. Then the phone rings…

  • Mom: I was looking up the symptoms of assorted diseases on the internet so I can panic about the possibilities of my friends, family and especially grandchildren getting them, then the laptop beeped and turned itself off. I kept trying to turn it on but the fan starts, then it immediately turns itself off.
  • Captain Support: It sounds to me like the power lead is not connected and the battery has run out of juice. Make sure it’s plugged in and turned on at the wall switch and it should be fine. If not, it means either a fuse has gone or the charger is bust. Check it now. Is the power lead connected?
  • Mom: Yes, all three leads are connected.
  • Captain Support: Describe the leads please. Are they rectangular with a prongy looking symbol on them?
  • Mom: Yes.
  • Captain Support: Those are USB cables, not power leads. It sounds like it’s disconnected from the power. Plug in the lead and you’ll be good to go.
  • Mom: I’ve only ever had these three cables in. There are no other cables.
  • Captain Support: Seriously, they are USB cables, not power leads. You must have had another lead plugged in before now.
  • Mom: There has never been another lead plugged into this laptop.
  • Captain Support: Check the back. Is there a round hole that looks like it could have a power lead plugged into it?
  • Mom: Yes. There is a round socket. Oh, I can see a cable down the back of the desk. I pulled the desk out earlier to hoover. It must have come out.
  • Captain Support: Plug it back in, then sacrifice a chicken at dawn on the altar of Captain Support and your problem will be solved.

Humans. Will they never learn?


Captain Support…

Come on you Baggies…

My nine year old nephew has just been picked for the West Bromwich Albion F.C. Development Centre. He was already playing for a local academy side and this is the next step up. It’s worked out really well because the Baggies DC training ground is not too far from where he lives. Much easier than having to drives miles to some of the other Premiership development centers.

I guess now we will have to get him out of the habit of supporting Manchester United and into the Baggies.