The (M)other (O)f (A)ll (T)uning (S)cripts. A tuning and diagnostics utility for sqlplus co-developed with Tanel Poder. Includes active session sampling and a TOP-style utility for displaying instance activity at regular refresh intervals. Supports versions from 10g Release 2 onwards. September 2010 (updated March 2011)
The first day was my usual mish-mash of managing to attend a few presentations, further slide-polishing and beers with friends.
We kicked off with Kerry Osborne's keynote address which was a walk through IT history, both in a general sense through the move from mainframes and single-vendor proprietary systems and back again, and also in a personal sense as Kerry discussed his own history in IT. Kerry's a fun speaker so he managed to keep everyone's attention well for an hour and raise quite a few laughs.
After a quick (smoking) break, we had Kerry again on Tuning Exadata. I didn't learn too much here but, in fairness, I might have learned a lot more had he not had to answer so many basic Exadata questions which sucked up a lot of the time slot. I heard a few people complain about this afterwards, so it wasn't just me.
Continuing the opening single stream of presentations was Cary Millsap's Thinking Clearly About Performance. Regardless of how many new facts I may (not) learn in this particular presentation, I am virtually guaranteed that if I listen to Cary for an hour, he'll remind me of how we should approach performance problems and always makes me think about things just slightly differently. He's a great speaker - not to be missed.
As soon as we split into two streams, I had my first tough decision to make between Gerwin Hendriksen's GAAP presentation and Thinking Clearly in SQL by Toon Koppelars. In the end I went with Toon on the basis that I can read Gerwin's paper later. It was a good choice as Toon did a great job of making the maths behind solving tricky problems in SQL much more understandable than it had been before for me.
At that point, my day dissolved into a combination of catching up on both sleep and slide preparation then I just had time to attend Stephan Haisley's presentation on Goldengate Performance. I was absolutely determined to attend this because I'd always admired his work but had never heard him present for one reason or another. Sure enough, he's a great presenter and I learned plenty of useful tips that I'll share when I'm back in the office.
The day wrapped up with Netezza Night. Yes, Netezza night. It seemed a strange gig to have them sponsor a Hotsos event and there was a strange little speech for a couple of minutes but, hey, they bought the beer, so I'm not complaining.
Disclosure: I'm attending this year's Hotsos Symposium with the help of the Oracle ACE Director program, which is paying my travel and accommodation expenses. The time off work is at my own expense.
How do you find out if there are any restrictions (or interesting, possibly undocumented, values) for the system parameters ? Once upon a time I used to use a value that was (probably) illegal so that I could find out from the resulting error message what the list of valid values was, for example:
SQL> alter system set optimizer_features_enable='fred'; alter system set optimizer_features_enable='fred' * ERROR at line 1: ORA-00096: invalid value fred for parameter optimizer_features_enable, must be from among 184.108.40.206.1, 220.127.116.11, 10.2.0.4, 10.2.0.3, 10.2.0.2, 10.2.0.1, 10.1.0.6, 10.1.0.5, 10.1.0.4, 10.1.0.3, 10.1.0, 9.2.0, 9.0.1, 9.0.0, 8.1.7, 8.1.6, 8.1.5, 8.1.4, 8.1.3, 8.1.0, 8.0.7, 8.0.6, 8.0.5, 8.0.4, 8.0.3, 8.0.0
This little trick became unnecessary in 10.2 with the arrival of view v$parameter_valid_values. For example (from an 18.104.22.168 instance):
SQL> select ordinal, isdefault, value 2 from v$parameter_valid_values 3 where name = 'optimizer_features_enable' 4 order by ordinal 5 / ORDINAL ISDEFAUL VALUE ---------- -------- --------------------------- 1 FALSE 8.0.0 2 FALSE 8.0.3 3 FALSE 8.0.4 4 FALSE 8.0.5 5 FALSE 8.0.6 6 FALSE 8.0.7 7 FALSE 8.1.0 8 FALSE 8.1.3 9 FALSE 8.1.4 10 FALSE 8.1.5 11 FALSE 8.1.6 12 FALSE 8.1.7 13 FALSE 9.0.0 14 FALSE 9.0.1 15 FALSE 9.2.0 16 FALSE 10.1.0 17 FALSE 10.1.0.3 18 FALSE 10.1.0.4 19 FALSE 10.1.0.5 20 FALSE 10.1.0.6 21 FALSE 10.2.0.1 22 FALSE 10.2.0.2 23 FALSE 10.2.0.3 24 FALSE 10.2.0.4 25 TRUE 22.214.171.124 26 FALSE 126.96.36.199.1 26 rows selected.
But a quick test demonstrates that you don’t get to see the values for hidden parameters – which is why I wrote a quick script to query the underyling x$ structure. Since it’s looking at an x$ it will only work if you’re connected to the SYS schema:
set pagesize 60 set linesize 90 set trimspool on column name_kspvld_values format a40 heading "Name" column value_kspvld_values format a40 heading "Value" column isdefault_kspvld_values format a4 heading "Def" break on name_kspvld_values skip 1 spool valid_values select name_kspvld_values, decode( isdefault_kspvld_values, 'TRUE', '***', 'FALSE', null, '?' ) isdefault_kspvld_values, value_kspvld_values from x$kspvld_values order by name_kspvld_values, ordinal_kspvld_values ; spool off Name Def Value ---------------------------------------- ---- ---------------------------------------- _always_anti_join HASH MERGE NESTED_LOOPS CHOOSE OFF _always_semi_join HASH MERGE NESTED_LOOPS CHOOSE OFF _backup_kgc_scheme *** ZLIB BZIP2 LZO _cluster_library SKGXN CLSS ...
You’ll notice when you run it that it’s only about the parameters that take string values – there’s no information there about default values for numeric parameters or boolean parameters. Unfortunately there are (in my 188.8.131.52 instance) 107 distinct parameters listed in this view but 283 parameters of type 2 in x$ksppi (one of the things underneath v$parameter) so at first sight it looks as if some (quite a lot of) parameters are missing – but that’s because some string parameters (such as control_files, remote_listener, log_archive_dest) are open-ended in their content and can’t be constrained by a simple list of values.
Yes, the title of this blog entry is a come-on. I am ever-so-slightly apologetic (smiley face).
This post follows the longest dry spell in my blogging over the last five years. I haven’t posted since early January and thus I am quite overdue for the next installment in my series regarding the Oracle Database 11g Direct NFS clonedb feature. I set out to make the next installment yesterday but before doing so I visited the analytics for my blog readership to see what’s been happening. I discovered that essentially nobody comes to this blog through Exadata related search terms anymore. That surprised me. Indeed, for the first—what—two or so years after Exadata went into general availability the first page worth of Google search results always included some of my posts. I can’t find any of my Exadata posts in the first several pages Google spoon-feeds me now when I google “Exadata.” This isn’t a wounded-soul post. I do have a point to make. Humor me for a moment while I show the top twenty search terms that have directed readers to my blog since January 1, 2011.
|oracle on flash SSD||188|
|oracle nfs clonedb||182|
|oracle on nfs||122|
|oracle fibre channel||115|
|huge pages allocated||104|
|real application clusters||92|
|automatic memory management||82|
|oracle file systems||75|
|greenplum versus exadata||70|
So, as far as search terms go there seems to be a lack of traffic coming to this site for Exadata-related information. The page views for my Exadata posts are high, but the search terms are lightly-weighted. This means folks generally read Exadata-related material here after being directed for a non-related search term. Oh well. I’d ordinarily say, “so what.” However, it is unbelievable to me how many people ask me questions each and every day that would be unnecessary if not for a quick read of one of the entries I posted before Oracle Open World 2010. That post, entitled Seven Fundamentals Everyone Should Know Before Attending Openworld 2010 Sessions might be better named You Really Need to Know This Little Bit About Exadata Before Anyone Else Tries to Tell You Anything About Exadata. Folks, if you get a moment and you care at all about Exadata, please do read that short blog entry. It will enhance your experience with your sales folks or any other such Exadata advocates. Indeed, who wants to be introduced to a technology solution by the folks trying to sell it to you. Now, don’t get me wrong. I’m not saying Exadata sales folks are prone to offering misinformation. What I’m trying to say is your interaction with sales folks will be enhanced if you don’t find yourself in such remedial space as the very definition of the product and its most basic fundamentals. That leads me to point out some of the folks who have taken the helm from me where Exadata blog content is concerned.
Oaktable Network Members Booting Up Exadata Blogging
Fellow Oaktable Network member Kerry Osborne blogs about Exadata, in addition to his current efforts to write a book on the topic. I’ve seen the content of his book in my role as Technical Editor. I think you will all find it a must-read regarding Exadata because it is shaping up to be a very, very good book. I have the utmost of respect for fellow Oaktable Network members like Kerry. In addition to Kerry, Fritz Hoogland (a recent addition to the Oaktable Network) is also producing helpful Exadata-related content. Oracle’s Uwe Hesse blogs frequently about Exadata-related matters as well. So, there, I’ve pointed out the places people graze for Exadata content these days. But I can’t stop there.
We Believe the Oracle Data Sheets
The content I’ve seen in blogs seems to mostly confirm the performance claims stated in Oracle Data Sheet materials. Let me put it another way. We all know the latest Exadata table/index scan rates (e.g., 25 GB/s HDD full rack or 70GB/s combined Flash + HDD). We’ve seen the Data Sheets and we believe the cited throughput numbers. I have an idea—but first let me put on my sarcasm hat. I’m going to predict that the next person to blog about Exadata will start out by blogging something very close to the following:
My big_favorite_table has many columns and a bazillion rows. On disk it requires 200 gigabytes of storage but with mirroring it takes up 400 gigabytes. When I run the following query—even without Exadata Smart Flash Cache—it only takes eight seconds on my full-rack Exadata configuration to get the result:
SQL> select count(*) from big_favorite_table where pk_number < 0; COUNT(*) ---------- 0
Don’t get me wrong. It is important for folks to validate the Data Sheet numbers with their own personal testing. But folks, please, we believe the light-scan rates are what the marketing literature states. I’m probably not alone in my desire to see blogs on users’ experience in solving particularly complex analytical data analysis problems involving vast amounts of data stored in Exadata. That sort of blogging is where social networking truly ads value—you know, going “beyond the Data Sheet.”
So what does all this have to do with the infrequent nature of my blogging? Well, I’ll just have to leave that for a future entry. And, no, the Data Sheets on Exadata Database Machine are not inaccurate.
Filed under: oracle
Just to annoy those who wonder why on earth I do this. I should point out (and keep pointing out) that
Disclosure: I'm attending this year's Hotsos Symposium with the help of the Oracle ACE Director program, which is paying my
travel and accommodation expenses. The time off work is at my own expense.
I get the feeling that I am Number 4 is trying to target prepubescent boys, the same way Twilight (how I hate that film) has captured the imagination of every young person with ovaries. The big difference is that prepubescent boys like to see things get shot and blown up. As a result, it’s much more palatable to an adult male than the tripe they keep serving up in the Twilight Saga. Gosh that film is bad…
I.a.N.4 has its fair share of teenage angst and everyone is Hollywood pretty, but it didn’t make me vomit and it didn’t have glittering vampires. How I hate glittering vampires…
So if you go in cold, I.a.N.4 is kinda meh. If you cleanse your palate first by watching any Twilight (what a completely awful set of movies) film, you will think I.a.N.4 is awesome and deserves a giant bag of Oscars.
or the Oracle support interface formerly known as Metalink.
In case you haven’t come across it, or if you’ve lost the URL, there is an HTML interface which, on the downside, doesn’t offer all the features but, on the upside, does seem to be a little more robust and a little faster. (I’ve added links to both the flash and html versions in my blogroll.)
It's a tough gig blogging from a conference, particularly this side of a presentation that I'm still (cough) polishing the slides for, but I can't bitch about Twitter decimating the blogging community without occasionally putting the slightest effort into redressing the balance.
After a long trip on Saturday (my solitary concession to Twitter - bitching about travel) I landed in the hotel bar and met the first of the many old friends I'm going to meet this week. Lest this seem like the opportunity for a name-dropping bonanza, I'm going to try to avoid specific names, but there were a variety of Marks there (if I include Marco) and Jacco, who was an unexpected surprise.
I've been upgraded to a suite and although it's a non-smoking one, I simply don't have the heart to ruin things for this lot by going ahead with the promised move.
They had a worse trip in the cargo hold, so it's the least I can do. (I can only imagine the reaction at the turn-down service when they'd carefully drawn the net curtains behind them! I can just imagine the little voices - "Behind us please, otherwise we can't see out ....")
Despite all my best efforts to ensure that this time, really, honestly I *will* have my slides done before the conference, I was there yesterday morning, polishing away and was really disappointed when my 2 hour afternoon nap turned into a 4 hour one finishing just in time for registration. I suppose I needed it, having had little sleep for days.
Walking around the hotel, registration and (yes) the bar, it occurred to me that of all the conferences I attend, this one feels most like a family outing. Again, that could be seen as a case of seeing the same old faces, my famous mates and so on, but in this case, I recognise about a third of the people milling around the hotel, attendees from past conferences and absolutely nothing to do with Oracle Corp, the Oak Table network or user group folk. This is just a bunch of people who (importantly) are all into the same stuff - Oracle performance-related - and come back year after year for more of the same. Add in the fact that we're all stuck in a single venue for presentations, meals, beers and all, it makes for a very intimate atmosphere conducive to informal conversations.
Those damn slides - I can not get them out of my mind and I won't be able to relax until about 11am tomorrow, when I'm done. I've simply bitten off more than I can chew and now that I'm at the 80 slide mark with more to be added, I have a couple of options
1) Drop some subjects and focus on a few properly.
2) Try to emulate Connor McDonald and talk my way convincingly and understandably through the slides at a rate of approximately 150 miles per hour. (If you've ever seen Connor present, you know how difficult that would be to emulate.)
I suspect I'll go for option 1 and, as I was saying to a few friends last night, I rarely go for the level of depth that will impress 3 people in the audience, but the level that will be of most value to most people.
Then, and this is outrageous but relevant name-dropping, just as I finished that very conversation, Tom Kyte walked up, said hello and pointed out that his closing presentation will be on things he's (re)-learned during the conference so that I'd better be able to teach him something new or something like that.
No pressure, then
Damn, I hate the fact that I'm going to have to miss some things later to catch up but obviously I don't hate it enough or I wouldn't keep letting it happen.
Disclosure: I'm attending this year's Hotsos Symposium with the help
of the Oracle ACE Director program, which is paying my
travel and accommodation expenses. The time off work is at my own
It looks like England are going out of their way to make this World Cup interesting, but following them maddening. That ridiculously laboured effort in Nagpur followed by a stunning tie against one of the best two sides in the world before succumbing extremely poorly to the excellent Irish. Yesterday what do we get, a [...]
Just a quick note (because as a smartphone user this sort of thing bothers me a *lot*) to point out that today’s XKCD has an excellent example of how if you are going to anticipate your application’s client requests in some form of intelligent response, you really need to get it right so as not [...]