Read and monitor Oracle related blogs and news sources, all in one place.
Bob Sneed being a hero and not to afraid to dance during the Hotsos Disco Night were others would chicken out…
I never find it very easy to try to capture the atmosphere during a conference, the presenting part, the presentations or the discussions, for example, you could have with the presenters. Hotsos is such a cool and unique event were you have the opportunity, to listen but also to interact. The amount of people that attend isn’t that big, only a few hundreds, but they share all the same passion, the passion to improve on performance, mostly Oracle related. It has only two tracks and its not uncommon that people present and than go afterwards to a presentation to listen in what the other presenter has to say.
Due to the fact that it is manageable in terms of choice, located on a convenient location and well organized, you have the opportunity to pick just the thing you like and most of the time not miss out on “the other” presentation you would have liked to see. Besides that people stay in (overnight in the Hotel), so discussions about the technology, the method or an example during a presentation will be discussed in far more detail than you normally would do, from presenter to presenter or from presenter to the guy that attended and vice versa. The fact that all have the passion for performance or that they realize that performance is a beast with various angles to approach, bounds, and every point, every question is one to be heard and/or discussed. On equal terms. If you have seen my video impressions of Hotsos in 2009, you get a bit of what I am trying to say.
Anyway, not being a native speaker and with my new “flip” at hand (Ultra HD), I just show you how much fun and interesting stuff is going on, probably this will do more right to those presentations than me trying to explain what all those new ideas which were that popped into my head during this years Hotsos Symposium…although…sometimes technology helps and sometimes it just doesn’t…or is it a VMware thing Doug…?
My agenda is not that much different then the one from Doug. The moment it is mentioning RAC related stuff, then I probably would have been attending the other guy.
The Monday started great with a small introduction of Tom Kyte’s generally unknown other qualities…
…Enjoy…
Ohhh…and I wasn’t the one, by the way, that was giggling… On a more serious note, the following clip will show how Tom Kyte handles date time conversions, if remembering birth dates.
Alas I didn’t manage to film a more serious piece of his keynote, so sorry. The “flip” thing was a new attempt to capture the atmosphere and I wasn’t sure how it would workout…and as you noticed, using the zoom function on the “Ultra HD” flip, is not a good idea…
The conceptual presentation of Alex Gorbatchev was cool and well thought out. To give you an impression I have two clips that are a good representation of the humor and the topics that were discussed during his “Battle Against Any Guess” presentation. The first part is a good example why it is sometimes handy to be lazy…
In his more serious section of his presentation, Alex discusses the use of checklists…
As always, Cary Millsap’s presentation was very useful and I really love his way of presenting (it looks easy – but it isn’t – very clearly discussing the topic). In this case the presentation topic was “Lessons Learned” about testing and the boundaries you should respect, if your test set-up should be useful. Apparently he tested his idea’s, once again, this time using his daughter as a test subject addressing the issue: “Can she lift 15 pounds…?”
Besides Sales guys being addressed, Cary sums it up on how cool it is to test stuff until it breaks…
Doug Burns had a very unlucky day. Sometimes the demo setup was working, sometimes it didn’t. Having a presentation that only consisted out of demo’s, and only a few slides for starters at hand, he had to improvise. In the end, as you can see here on his blog site, he had only time for 30 minutes doing his demo’s. Seen the presentation already during Oracle Open World in 2009 during a “Unconference” session, I can only say: wow, what did those people miss out… but alas, that’s the risk of doing demo’s. Sometimes it goes wrong…
So here how Doug tries to save the presentation via going in on more detail on Swingbench and tips and advice while using Oracle Enterprise Manager…or with other words: What you can do with a bit of Scottish humor to save the day…
…and how we finally got to the part were I guy saved Doug’s presentation (regarding the demo’s) using a wireless gadget, that probably fixed the issue with the routing table that messed it all up. Oh and a tip to all those life savers out there: “Never type in your secret password while it is being displayed on the big screen…” (we now you bluffed regarding changing it…). 
The last clip is from Kevin Closson’s presentation while he explains a bit about “Why do we still think a CPU is a CPU…”.
Apparently he likes cuddly toys as well…
Ohhh, did I tell that I did well regarding my presentation? I did. How I know? Due to the fact that I encountered most of my audience afterwards (4) during the Disco Night event on Tuesday and they all liked it (total of 7!). They bought me beer even. Cool. Should do it more often, this presenting thing. The other 200+ people who went to Tanel Poder’s presentation, which is their bad luck. If they only knew what they were missing out on. I mean even Doug was positive in his Scottish way trying to tell me. So in all, not bad for a newbie during Hotsos. At that time slot all 200+ plus 7 people had a good show for their money; if only I could have attended Tanel’s… Hmmm.
PS. Before I forget. I would like to thank Toon Koppelaars for reviewing my presentation and giving me some useful advice.
I went to the DBMS SIG today {DBMS Special Interest Group meeting of the UK Oracle User Group}. Don’t worry, I am not going to run through the set of presentations and make comments on them – although I like reading such entries by others on their blogs, I generally like them due to the style of writing as opposed to getting information out of them. But there is the odd tidbit that can be worth disseminating to as wide an audience as possible and I like to do my bit.
Having said I won’t go through all the talks… 
. No, I just want to comment that all the talks had merit at this meeting and, quite rightly, the meeting was full. This is nice to see as last year SIG attendance fell across the board, due to the “economic climate”. Very daft, in my opinion, as I see SIGs as free training plus networking opportunities (sorry to use the “networking” word” plus different viewpoints, all of which are invaluable at any time and especially valuable when things are hard.
Go to SIGs (or whatever is available in your country) as it is always worth the day invested. Where else can you see half a dozen experts give opinions and also corner them and ask them more stuff as well?
Anyway, the tidbits. First, Jonathan Lewis demonstrated how he goes about solving issues with complex SQL statements. It was terribly feindish and extremly clever and needs incredible in-depth knowledge of the oracle RDBMS… He draws a pseudo Entity Relationship Diagram of the tables involved, adds in some figures on what filtering will achieve and what ratio of records will be involved in table joins and asks the question “what will probably happen” a couple of dozen times. Yes, I lied, it does not need vast knowledge. It needs a clear, simple approach to solving the problem. And an ERD. I love ERDs and rue their general demise. I use exactly the same method myself to investigate complex SQL performance issues {I think I could be accused of trying to ride on shirt-tails here, but honestly, the step I take if an Explain Plan does not help me is to ERD the statement and look at the indexes and ratios between tables to see how I , as a human, would solve the query. Chatting to a few other old lags, it is a relatively universal approach by those of us who have used ERDs}. If you are a member of the UKOUG I strongly recommend downloading the slides to Jonathan’s talk. If you are not a member, maybe Jonathan will present it again at another venue, or you could get him to come along and do a course on tuning. {Jonathan, if you get a gig as a result if this, I want a pint of Marston’s Pedigree, OK?}
{And thanks to Sean malloy for commenting to provide a link to a published version of Jonathan’s method – Jonathan did mention this, highlighting the fact that it is his first real foray into SQL*Server. However, the method is database agnostic. This is the article}
Second tidbit. Adrian Dodman and Owen Ireland (who both look remarkably like Hollywood hearthrobs in their pictures, but different as their in-the-flesh selves, though very decent chaps they are too.) did an excellent talk on VLDB physical standbys, a topic that has particular resonance for myself. They mentioned parallel_execution_message_size. This defaults to 2k, on 10g at least. It is a rubbish setting. No, let me not beat about the bush, it is an utterly rubbish setting. If you use parallel query, parallel recovery or parallel anything-at-all, check out this parameter and, due dilligence allowing, increase it. Try 8k as opposed to 2k and even 16k. The manual on it says the default of 2k/4k is fine. It ain’t. Increasing the value just takes some memory out of the shared pool and, these days, if you can’t afford a few extra KB out of your shared pool, you need to replace your server with something costing about twice as much as a top-end desktop PC. { Why am I so vigorous in my opinion on this parameter? Well, I had a situation a few months back of trying to migrate a database to a new geographic location for a client in Germany. We did a backup/recovery type operation to do this. Applying the redo logs was proving to be a performance issue so Oracle Corp suggested parallel redo log application. It ran a LOT slower than single thread, about 300% slower. However, increasing the parallel_execution_message_size from 2k to 8k made the parallel log application about 400% faster than single thread. ie a dozen times faster. I know from presentations by Christian Antognini and discussions with others that it is a key parameter to getting parallel query to perform well too.}
Last tidbit. Don’t drop the OUTLN user. Yes, I know, why would you? Just don’t, OK? Especially on Oracle 11. If you do, for whatever reason, DO NOT SHUT DOWN THE DATABASE. Call Oracle Support and pray. Thanks go to Peter Mahaffey for that one. Yes he did. It all went terribly wrong for him.
I walked away very impressed and when I was talking to a couple of his attendees at the bar later, it seems I wasn't the only one. I really liked his attitude towards his scripts, too - take them, use and abuse them, knock yourself out! Well, his scripts have helped me many times over the past year. A good guy. Today is the day I fly to New Zealand to present at the NZOUG conference.
I got to the airport 2 hours before the flight as usual, but I’ve been here 3 hours already and I’ve still not boarded the plane. I have a long wait at Dubai, so this delay shouldn’t affect my connection. It’s 7 hours from Birmingham to Dubai, then 18 hours (including a fuel stop in Melbourne) to Auckland. I have a feeling I’ll look like “Dawn of the Dead” when my friend picks me up from Auckland Airport. I’ve not even started yet and I’m already kinda nervous. The thought of 18 hours on a plane is really getting to me. I only hope I can get some sleep on this trip.
It’s about 5 minutes to boarding now so I need to go through my last minute panic session before getting on the plane. 
If there is ever another blog entry it means I made it. If not, it was nice knowing you all…
Cheers
Tim…
I am sure most of you are already familiar with these resources but sometime we all forget where to look for quick help when is really needed.
I find these demos and tutorials very valuable .You can quickly find out what needs to be done to successfully implement particular feature without spending too much time digging Oracle documentation and other resources ( of course I strongly recommend reading Oracle Documentation before doing anything).
You can find them at Online Demos and Online Tutorials
Oracle 11g R2 is also included.
There are two new awr reports in 11gr2, which will be helpful to dba’s in Real Application Clusters Environments (RAC).
This is a cluster wide awr report, so you can see a lot of the information from all the nodes in the same section, and you can also see aggregated statistics from all the instances at the same time (You can see totals, averages and standard deviations).
This is a cluster wide stats diff report (like you had awrddrpt.sql in 11gr1), comparing the stats differences between two different snapshot intervals, across all nodes in the cluster.
These are huge additions to the awr reports, that enable understanding the database performance in real application clusters environments.
In one of last week’s blog articles I asked why a SQL statement that had appeared in a book, was copied to various websites, and was offered as an example of good SQL simply did not work. A couple of readers of this blog took up the challenge to offer suggestions. I think that I found another example of blindly reproducing other’s code without verifying that the code works as expected. What did I find this time?
http://www.orafaq.com/scripts/security/blocktools.txt
rem -----------------------------------------------------------------------
rem Filename: NoTOAD.sql
rem Purpose: Block developers from using TOAD and other tools on
rem production databases.
rem Date: 19-Jan-2004
rem Author: Frank Naude
rem -----------------------------------------------------------------------
CONNECT / AS SYSDBA;
CREATE OR REPLACE TRIGGER block_tools_from_prod
AFTER LOGON ON DATABASE
DECLARE
v_prog sys.v_$session.program%TYPE;
BEGIN
SELECT program INTO v_prog
FROM sys.v_$session
WHERE audsid = USERENV('SESSIONID')
AND audsid != 0 -- Don't Check SYS Connections
AND rownum = 1; -- Parallel processes will have the same AUDSID's
IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
UPPER(v_prog) LIKE '%SQLNAV%' OR -- SQL Navigator
UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
UPPER(v_prog) LIKE '%BUSOBJ%' OR -- Business Objects
UPPER(v_prog) LIKE '%EXCEL%' -- MS-Excel plug-in
THEN
RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed on PROD DB!');
END IF;
END;
/
SHOW ERRORS
If you search the Internet you will find this piece of code, minus the header block, on various websites as recently as 2010, including in this OTN thread. The idea of blocking development tools in a production database could be a valid goal, so I am able to understand the interest in the trigger. What is wrong with the trigger? Is it just a case that SQL*Plus could also be considered a developer tool? What about the Crystal Reports development enviroment? What if the developer created his own development tool to access the database? What if… someone changed the filename of TOAD? What if… the trigger doesn’t actually block Excel without any tricks such as changing Excel’s filename?
The contents of this blog article were tested with Oracle Database 11.1.0.7 with an Oracle 11.1.0.7 client, Excel 2007, and Windows Vista as the client operating system. First, I created the trigger when connected as SYS, and then queried V$SESSION to find that only my SQL*Plus connection was listed.
Then in Excel 2007 I selected Get External Data – From Other Sources:
When prompted, I connected to the database as a normal user:
I was then able to freely execute queries against the database using the Microsoft Query Tool, which would then allow me to very easily return the rows to an Excel spreadsheet.
OK, maybe the above is cheating… a little.
But, what is the point in blocking Excel with the logon trigger, is the trigger supposed to stop someone from firing up an Excel macro that uses ADO to connect to the database and retrieve whatever information the developer would like to see? Kind of like this example:
The logon trigger that appears on several websites did not block my Excel connection – if you take a look at the first screen capture you will see that the logon trigger was created without error, and then I was able to connect with the Microsoft Query Tool and my Excel macro using a normal user’s account. Someone want to try explaining what went wrong? Must be that we need to file a Metalink bug report, after all, I saw this example on the news so it must be true.
Maybe Oracle Database 11.2.0.1 will respond more favorably (Oracle 11.1.0.7 client, Excel 2007, and Windows Vista as the client operating system):
While this code might have worked fine in 2004, it would probably make sense to test this trigger code before copying it to your website. I think that this is worth repeating:
Lesson 1: if you plan to publish something, whether in book form or on the Internet, make certain that what you publish actually works (or at least looks like you put some effort into it) .
Lesson 2: if you plan to copy someone else’s work and post it on your website/blog make certain that what you copy and pass off as your own actually works.
Lesson 3: don’t trust everything that you read on the Internet or in a book without first verifying that the information is correct, even if you find the information on your favorite website.
I wanted to setup a virtualized linux environment on my 64 bit windows 7 laptop. (OEL 5 is Oracle Enterprise Linux 5).
I looked at a few alternatives:
I decided to try Virtual PC on Win 7, mostly because my laptop has the hardware virtualization. Also vmware website was not taking registrations last weekend. Also wanted to see if the lack of linux support would cause any issues.
Today, I have discovered Oracle VM VirtualBox. VirtualBox came over with the Sun acquisition and it seems this will be well supported in the future. See http://www.virtualization.info/2010/01/oracle-vm-30-and-virtualbox-roadmaps.html
If I was starting again I would try Oracle VM VirtualBox. But since I have used Virtual PC on win 7 and my experiences may be of benefit to others, I will describe them.
Does your laptop have Hardware Virtualization?
Here’s a link to the Intel utility to run to see if your CPU supports Hardware Virtualization:
http://www.intel.com/support/processors/tools/piu/
Here’s a link to Microsoft’s instructions to configuring the BIOS to enable Hardware Virtualization:
http://www.microsoft.com/windows/virtual-pc/support/configure-bios.aspx
Steps taken
1. Downloaded “XP mode” and “Virtual PC” from http://www.microsoft.com/windows/virtual-pc/download.aspx
2. Installed “XP mode”. Installed “Virtual PC”. It is not necessary to configure “XP mode”, before using “Virtual PC”
3. Downloaded OEL 5 update 4 dvd iso from http://edelivery.oracle.com/linux
4. Ran the “create virtual machine” wizard. Options 1024MB ram, use computer network selected, named virtual hard disk.
I left the Virtual hard disk with its Total Disk size set to 130GB and dynamic type. My budget is 20GB. OEL 5 does not seem to grab the full size, but supports the dynamic nature of the Virtual hard disk file.
5. Changed the vm settings to point the DVD drive to the OEL 5 dvd iso file.
6. Started the vm
7. Selected default Graphical mode. This resulted in:
8. The issue is that Virtual PC supports 16 bit color, but not 24 bit. The linux install uses 24 bit.
The solution I chose was to do the install in text mode. So after restarting the vm, I typed “linux text”
9 I skipped the media test and chose the following options:
language: english
Keyboard: US
initialize drive
Use GRUB Boot Loader
no boot options
no GRUB password
/dev/hda1 for boot loader
eth0 parameters
gateway 192.168.1.1
no primary, secondary DNS ip
hostname: oel5
timezone: Chicago (counld not find St. Paul or Minneapolis!!)
Root password:
Options: “Software Development” and Customize
Defaults + unselected Games, selected Legacy SW development, Server config tools, system tools, Windows File Server, unselected X Software Development, text-based internet, office/productivity, gnome software development.
10. The installer formatted the virtual disk and did the install in 25 minutes.
The install grew the Virtual Hard Disk to 5GB
11. I changed the vm setting for network to “Shared networking”
Post-install issues
After logging in as root and entering “startx” to see the gnome desktop, I had a few issues.
No Mouse:
The mouse was not working. The solution was to Edit /boot/grub/menu.lst and add “i8042.noloop” to the end of the “kernal” line. See http://arcanecode.com/2007/04/23/ubuntu-704-and-virtual-pc-2007-no-mouse-issue/#comment-8249
800×600 Screen Resolution:
My desktop resolution was set to 800×600 and this was too small. The only available resolutions were 800×600 and 640×480. This was the most painful issue to resolve.
It involved adding some entries to /etc/X11/xorg.conf
I have highlighted the additions/changes (highlights visible at the website)
Section "Device" Identifier "Videocard0" Driver "vesa" EndSection Section "Monitor" Identifier "WXGA+ Inspiron" HorizSync 31.5-90.0 VertRefresh 60.0-60.0 Option "DPMS" EndSection Section "Screen" Identifier "Screen0" Device "Videocard0" Monitor "WXGA+ Inspiron" DefaultDepth 16 SubSection "Display" Viewport 0 0 Depth 16 Modes "1280x1024" "1024x768" "800x600" "640x480" EndSubSection SubSection "Display" Viewport 0 0 Depth 24 Modes "1280x1024" "1024x768" "800x600" "640x480" EndSubSection EndSection
These changes made it possible to achieve a 1280×1024 resolution
Lastly, I also needed to set a static ip address.
Future
<—- Blogroll Report 29/01/2010 – 05/02/2010
1-How to simulate Oracle errors with simulator events
Dennis Yurichev-Oracle RDBMS internal self-testing features
2-How to decode dates in histograms?
Martin Widlake-Format of Date Histograms
3-How to decide the value of datafile resize option ?
Jonathan Lewis-Shrink Tablespace
4-All about network wait events
Kyle Hailey-Oracle: Network Waits
5-When does manual logfile switch trigger complete checkpoint ?
Lua Donghua-ALTER SYSTEM SWITCH LOGFILE and CHECKPOINT
6-When does “alter system archive log current” trigger checkpoint?
Lua Donghua-ALTER SYSTEM ARCHIVE LOG CURRENT and CHECKPOINT
7-How to secure java in Oracle and how to test public revocation?
Paul Wright-Securing Java In Oracle and DBMS_JVM_EXP_PERMS
8-Difference between case and decode in terms of trimming
Timo Raitalaakso-Equality -comparing text
9-How to upgrade ASM 10.2 to 11.2 single instance
Martin Bach-Upgrade ASM 10.2 to 11.2 single instance
10-How to use resumable operations in Oracle
Stewart Bryson-Data Warehouse Fault Tolerance Part 1: Resuming
11-How to restart your ETL process with restore points ?
Stewart Bryson-Data Warehouse Fault Tolerance Part 2: Restarting
12-How to use incremental backup for no logging operations for recovery in DW environments?
Stewart Bryson-Data Warehouse Fault Tolerance Part 2: Restoring
13-Beginners guide to index block dumps and treedumps Part-1
Richard Foote-Index Block Dumps and Index Tree Dumps Part I: (Knock On Wood)
14-NVL and COALESCE performance comparison?
Scott Wesley-Short-circuit Evaluations – moving away from NVL
Scott Wesley-One more COALESCE vs NVL example to finish the week
15-How to drill down and detail sys_time_model data vs2?
Charles Hooper-Working with Oracle’s Time Model Data 3
16-How to put gather_plan_statistics hint without changing the code by sql profiles?
Kerry Osborne-gather_plan_statistics
17-How to check/extract unsuccessful connection attempts in listener_log ?
Martin Bach-Check for non-successful connection attempts in listener.log
18-Why does Oracle use undo when there is already redo logs? (Must read)
Jonathan Lewis-Why Undo?
19-How to configure ASYNC-IO on HP/UX?
John Hallas-OER 27064: cannot perform async I/O to file – HPUX
20-How to convert cross platform single instance to RAC with RCONFIG?
Syed Jaffar Hussain-My experience of converting a cross platform (AIX to HP) single instance database to RAC using RCONFIG tool
21-How to backup Oracle to cloud storage ?
Alisher Yuldashev -Let Your Oracle Backups Be Up in the Clouds
22-Fundemantals of Performance Tuning (Must read)
Cary Milsap-Thinking Clearly About Performance
23-Solutions to Permission problems with fuse and DBFS
Kevin Closson-Oracle Database 11g Database
File System (DBFS). Common DBFS Problems and Solutions. Part II.
24-How to send execution plan to excell and show execution plans on web
Charles Hooper-Automated DBMS_XPLAN, Trace, and Send to Excel
25-Weird Transitive Closure behaviour in execution plan which causes ignorance of hints
Jonathan Lewis-Ignoring Hints-2
26-How to do initial server pool configuration before database creation in 11GR2?
Martin Bach-Server Pool experiments in RAC 11.2
27-Problems with usage of Append_Values and solutions
Gary Myers-Append Values and how not to break the database
28-How does Subquery factoring work?
Tim Hartley-Getting Recursive
Video fragments of my session posted at the end — read on.
I arrived at Omni Mandalay Hotel on Sunday evening with Dan Norris. I was flying through Chicago and it turned out that Dan was on the same flight and only few rows behind me. Small world.
Preparations for the conference were very chaotic on my part and, of course, I didn’t have either of my presentations ready. I was very stressed and getting sick as well — it looked like a complete disaster waiting to happen. I’d like to say that I was feeling like Doug Burns as he often managed to get sick just before a conference. Of course, I worked on my slides for the last few days as well as on the flight and presentation was slowly getting there but boy was I tired!
I quickly said hello to the crowd in the bar on the way to my room and rushed away to do some more damage to my slides. And then I had a brilliant idea — I could still see one of my best mates and do something good about my presentation! I asked Doug if he was interested in the preview (he probably wasn’t interested but he couldn’t say it to me) especially that my session wasn’t on his original agenda. Of course, that would mean that he had to leave a bunch of other good friends and spend some time tete-a-tete. Knowing Doug, this is some of the hardest thing to ask from him but it shows how good of a friend he is! (Plus, everyone thinks that he is anti-social anyway. Shhhh!)
Doug has made my day — while he provided lots of ideas and feedback on few things that I was lucking, he generally approved the idea and confirmed that it wasn’t totally crazy. I guess that was all I needed back then and Doug knew how nervous I was about it. (Thanks mate!)
So I called Sunday a day very early and went to bed before midnight. I really needed some sleep. Woken up by the alarm at 5AM (I woke up few times during the night looking at the clock — making sure I didn’t sleep through) and slides were ready just before lunch. I even managed to do a test run and it took 65 minutes — a wee bit too long for one hour session. But it was good test and I knew I had to be just a bit more concise in few parts.
Mi morning was very productive. Unfortunately, I missed the opening keynote from Tom Kyte. Such a pity! If what Doug wrote is true, Tom was talking about the mistakes we make *because* of our experience and our assumptions. This was exactly one of the points I was making in my Battle Against Any Guess — experience is danger. I wish I could see Tom’s example. Oh well, maybe another time.
I managed to attend half of the Richard Foote’s session on indexes but my mind was far away — with my own slides. Though, I did manage to focus on bitmap indexes part and the myth of bitmap indexes not working well for columns with high cardinality. Very interesting conclusions. I’m still wondering how much overhead updates will do to such bitmap index.
After lunch, it was my turn. I ordered few copies of the latest OakTable book — Expert Oracle Practices: Oracle Database Administration from the Oak Table — that I co-authored with the bunch of other Oakies. I contributed chapter 1 in the book titled just like my presentation — Battle Against Any Guess. The plan was to give a copy away during the presentation and do a draw for another one at the end of the session. I was so nervous that I forgot about it until the end of the session so I just did a draw for two copies. The lucky winners were Lynn-Georgia Tesch and Surendra Anchula. Congratulations! For the rest of you who left the contact details — please stay tuned and we’ll organize few things online.
Now the main topic of this post — my presentation. What’s unusual about this session is that it’s not some technical stuff that I usually do but a more conceptual and motivational talk. Could I pull it off? Well, I think it went fairly well in general even though I did identify few rough places and my lack of English language mastering. Might need to work a little bit more on the flow of the presentation.
We had quite a few good laughs. Later, people in the next hall were asking about it and Dan was making the jokes on the stage so it must have been loud. Anyway, I think nobody fell asleep and I managed to get people thinking about the topic. I received many “thank you” notes yesterday and compliments on a good session so by the end of the day I was more and more pleased. Thanks everyone for attending and especially big thanks to those of you who brought to my attention examples from their own battles. If you have more to discuss — contact me by email (my last name) {at} pythian.com.
Thanks to Marco Gralike for recording some fragments and sharing them. I think he has more to come.
This is the introductory couple minutes. You can definitely notice how nervous I am starting on the stage:
Solving the wrong problem example:
That’s all for now. Stay tuned — more to come.
This post is about the importance of filter statements in execution plans and how they can indicate problems that are not always apparent.
I had a problem recently with some code that had gone rogue – it had been running in a few seconds, being executed every 10 minutes or so. Now it ran until either someone killed it off or it got “snapshot too old” errors. I pretty much knew it was prompted by stats being gathered on the tables in question as we had just gathered stats on the tables in that schema.
The code was something like this (it is not too important what exactly the code was). Oh, and this is on 10.2.0.3, enterprise edition with partitioning.
select accounted, max(recorded_date),count(*)
from W_LCG_OPENING_
where accountid||DATA_SRC_COUNTRY_ID in
(
SELECT accountid||DATA_SRC_COUNTRY_ID FROM W_LCG_OPENING_
minus
SELECT accountid ||DATA_SRC_COUNTRY_ID FROM W_LCG_CLIENT_
)
and 1= 1 – this is pointless, it replaces a check of “run”=”run”
group by accountid,DATA_SRC_COUNTRY_ID
Those tables are quite large, several million rows in each. The code is basically scanning all of the rows in the table as there are no indexes to support the query and it is written in a way that, well, I would not write it.
Digging around in some monitoring tools I use I confirmed that the code has swapped plan
inst SQL_ID Plan Hash First Load execs
---- ------------- ----------- ------------ ------
TOT_ROWS TOT_BG TOT_DR TOT_CPU CREATED_DT
---------- ------------ ----------- -------------- ------------
3 1t7ma4xn3rw46 3183284037 100203 09:10 125
7,854 6,457,885 1208,149 816,476,585 091020 10:27
3 1t7ma4xn3rw46 3127554972 100206 09:13 2
0 1260,936,642 980 94599,678,960 100205 09:19
Version 1 comes back in 5 or 6 seconds. Version 2 does not effectively come back, which is why it records 0 rows. You can see that the Disk Gets are pretty low in version 2 (allowing that it was never left to finish) but Buffer Gets and CPU are both massively up. So much so, it exceeds where I format the numbers with comas (as, if they are THAT big, they need fixing anyway).
I looked at the plans and, though they were different, nothing jumped out at me. So I trimmed down the code and built it up section by section until I saw something significant change. This is my general mode of tuning code if nothing initially occurs to me.
As usual, I started with any scalar or inline code, in this case that SELECT…MINUS…SELECT in the where clause.
SELECT accountid||DATA_SRC_COUNTRY_ID FROM W_LCG_OPENING_ Minus SELECT accountid ||DATA_SRC_COUNTRY_ID FROM W_LCG_CLIENT_
This came back in 3.145 seconds, with all of 62 rows. That is fine.
Now I added back the use of the MINUS code as a WHERE clause to the outer query.
select accountid
from W_LCG_OPENING_
where accountid||DATA_SRC_COUNTRY_ID in
(
SELECT accountid||DATA_SRC_COUNTRY_ID FROM W_LCG_OPENING_
minus
SELECT accountid ||DATA_SRC_COUNTRY_ID FROM W_LCG_CLIENT_
)
This came back in 5.22 seconds with 4468 records. Again, I can live with that, we do not need sub-second response, we need sub-minute response.
So I now added back in the group by accountid…
select accountid,count(*)
from W_LCG_OPENING_
where accountid||DATA_SRC_COUNTRY_ID in
(
SELECT accountid||DATA_SRC_COUNTRY_ID FROM W_LCG_OPENING_
minus
SELECT accountid ||DATA_SRC_COUNTRY_ID FROM W_LCG_CLIENT_
)
group by accountid
This does not come back before I have fetched and consumed a cup of tea. That is a significant change.
I was not expecting this, why would sorting under 5 thousand rows stress a database that can scan multi-million row tables in seconds? There is all that CPU being burned up and huge numbers of buffer gets, but not much in the way of disc IO, so it is probably doing something that can be done in memory a very, very large number of times.
What is the difference in plan? {Oh HELL, they look rubbish – sorry, click on the images to blow them up, I’d suggest right-click and open in a new tab, and, YES, I am working on making my blog a little more “wide and friendly”.}. Good plan first, bad plan second
The view step has disappeared, which was an expensive step so should help not hinder.
The “SORT GROUP BY” has appeared, which it would do as we introduced the “GROUP BY” clause.
The hash join has been replaced with a filter. In a more complex plan you might miss this replacement, you would maybe notice the hash join disappearing but a filter, well, it is checking some simple “WHERE” clause isn’t it?
Well, the hash join was joining the results coming from the two steps feeding into it, the PARTITION RANGE FULL and the VIEW (which is the in-memory construct of the SELECT..MINUS…SELECT statement).
Now the Filter is filtering the results from the PARTITION RANGE ALL with the results from the MINUS. At this point I’d like to highlight that the predicted cardinality and bytes coming back for the steps within the union have reduced by a factor of 100 from the good and bad plans. And I’ll also admit I hid some key detail in the screen shot. I am not showing the access and filter predicates.
The above is a screen shot showing that in the new code there are no filter predicates but an access predicate, for the access to the view (ie the select…minus…select). For easier reading, the full access predicate is below, rather than in the screen shot:
“$nso_col_1″=TO_CHAR(“ACCOUNTID”)||TO_CHAR(“DATA_SRC_COUNTRY_ID”)
However, for the slow code, there are no access predicated but are filter predicates. Again, the screen shot shows that there are predicates and I show the full text below. (Screen shots are from PL/SQL developer, btw).
2- EXISTS ( (SELECT /*+ */ TO_CHAR("ACCOUNTID")
||TO_CHAR("DATA_SRC_COUNTRY_ID")
FROM "W_LCG_OPENING_" "W_LCG_OPENING_"
WHERE TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID")=TO_CHAR(:B1)||TO_CHAR(:B2))
MINUS (SELECT /*+ */ TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID")
FROM "W_LCG_CLIENT_" "W_LCG_CLIENT_"
WHERE TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID")=TO_CHAR(:B3)||TO_CHAR(:B4)))
8- TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID")=TO_CHAR(:B1)||TO_CHAR(:B2)
10- TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID")=TO_CHAR(:B1)||TO_CHAR(:B2)
Basically, the filter is to run the union query for every row from the driving query, passing in the relevant filter clause as an extra predicate to each of the individual queries of the SELECT…MINUS…SELECT
Clever, but not of much help to us as the performance is awful.
I showed this to a colleague and their immediate response was “why does that not show up as a nested loop? How am I supposed to spot that the “filter” is doing so much?” There were a couple of robust Saxon words mixed in with that statement.
So, Beware Filters replacing joins and get in the habit of checking out the filter and access predicates
If you use an old-style template for showing plans {like I do, a guilty sin of mine}, or a GUI where you have not selected that the filter and access predicates be shown, you may well not get them displayed. If you use autotrace in SQL*Plus, you will though:
db3_mw> select accountid,count(*)
2 from W_LCG_OPENING_
3 where accountid||DATA_SRC_COUNTRY_ID in
4 (
5 SELECT accountid||DATA_SRC_COUNTRY_ID FROM W_LCG_OPENING_
6 minus
7 SELECT accountid ||DATA_SRC_COUNTRY_ID FROM W_LCG_CLIENT_
8 )
9 group by accountid
10 /
Execution Plan
----------------------------------------------------------
Plan hash value: 397856216
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cos
t (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 911 | 7288 |
15M (7)| 17:25:41 | | |
| 1 | SORT GROUP BY | | 911 | 7288 |
15M (7)| 17:25:41 | | |
|* 2 | FILTER | | | |
| | | |
| 3 | PARTITION RANGE ALL | | 3153K| 24M| 83
11 (4)| 00:00:34 | 1 | 840 |
| 4 | TABLE ACCESS FULL | W_LCG_OPENING_ | 3153K| 24M| 83
11 (4)| 00:00:34 | 1 | 840 |
| 5 | MINUS | | | |
| | | |
| 6 | SORT UNIQUE NOSORT | | 31535 | 246K| 86
15 (7)| 00:00:35 | | |
| 7 | PARTITION RANGE ALL | | 31535 | 246K| 86
11 (7)| 00:00:35 | 1 | 840 |
|* 8 | TABLE ACCESS FULL | W_LCG_OPENING_ | 31535 | 246K| 86
11 (7)| 00:00:35 | 1 | 840 |
| 9 | SORT UNIQUE NOSORT | | 132 | 1056 |
17 (18)| 00:00:01 | | |
|* 10 | INDEX FAST FULL SCAN| W_LCG_CLIENT__PK | 132 | 1056 |
16 (13)| 00:00:01 | | |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS ( (SELECT /*+ */ TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_C
OUNTRY_ID") FROM
"W_LCG_OPENING_" "W_LCG_OPENING_" WHERE
TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID")=TO_CHAR(:B1)|
|TO_CHAR(:B2))MINUS (SELECT /*+ */
TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID")
FROM "W_LCG_CLIENT_"
"W_LCG_CLIENT_DETAILS" WHERE TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_S
RC_COUNTRY_ID")=TO_CHAR(:B3)||TO_CHAR(:B4)
)))
8 - filter(TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID")=TO_CHAR(:B1)|
|TO_CHAR(:B2))
10 - filter(TO_CHAR("ACCOUNTID")||TO_CHAR("DATA_SRC_COUNTRY_ID")=TO_CHAR(:B1)|
|TO_CHAR(:B2))
What did I do to fix the problem? Well, even though the code originally went bad due to stats being gathered, I could not force it back to a nice plan after an hour or two playing with gathering new stats so, in this case, I used an UNNEST hint.
select accountid,count(*)
from W_LCG_OPENING_
where accountid||DATA_SRC_COUNTRY_ID in
(
SELECT /*+ unnest */ accountid||DATA_SRC_COUNTRY_ID FROM W_LCG_OPENING_
minus
SELECT accountid ||DATA_SRC_COUNTRY_ID FROM W_LCG_CLIENT_
)
group by accounted
And it forced the plan back to the version using a HASH join.
The hinted plan
I’m a little unhappy about that hint, but the code needed fixing and the hint did what I wanted it to {I think it was poor of me to only hint one of the two minus statements and I do not like resorting to hints just because I can’t work out what is “wrong” with the stats – If I work out what is wrong and still need the hint, fair enough}. However, I had to be pragmatic and get the code fixed and working in Live, so it has gone in with a hint
and they aren't Scottish, so I shouldn't generalise. Updated later - it was Cary Millsap who came up with this line. I loved it when he mentioned it a few months ago and still do. Frankly, I kept waiting for the room to empty (I've seen it happen) but the vast majority stuck around to the end. Maybe they were sadists! LOL
RSS
Email
