The last post covered Sunday and Monday of OpenWorld 2010, which leaves this one needing to cover Tuesday, Wednesday and Thursday…
Dan Morgan – Developer Coding Strategies for Very Large Databases: Dan Morgan clearly has experience of working with very large databases and his suggestions included: consider the use of individual tablespaces for partitions so that old partitions that will no longer be updated can have the corresponding tablespace made read-only; use of INSTANCE_ID as part of the partition key in RAC environments; at 11.2 take advantage of the release of index storage when an index is made UNUSABLE. The final suggestion I noted down was to avoid the existance of NULL values by using “nonsense values”. This seems to fly in the face of the fact that the use of nonsense values can cause the optimizer problems in estimating cardinality in the absence of histograms. Maybe I have misunderstood the suggestion, but I’d definitely use caution with this.
Richard Foote – Oracle Indexes: Q & A Session: As anticipated this was an excellent session. The key points I noted during the session were: the use of ROWIDs has benefits, but this comes at a price as you can’t then move rows without the associated overhead of updating wherever you store the ROWIDs; for INDEX SKIP SCAN to be useful the leading column must be low cardinality; if you’re not sure about index (A,B) or (B,A) then put the lowest cardinality column first as this creates improved opportunity for INDEX SKIP SCAN and allows for better compression; deletes from indexes are lazy deletes, it just marks the row as deleted, but does not clear the data; heavy DML on a compressed index should be avoided. My final note reads “Separate tablespaces for indexes and data is a myth and always has been. The use of an index is a serial operation and there will be lots of table blocks read for each index block. There is no parallelisation opportunity during the scenario.”
Kevin Closson – Do-It-Yourself Exadata-like Performance: This was an interesting session. The key points I noted where: for DIY Exadata you would need 39 Nehalem EP cores just to match the Exadata offload processing (filter and projection); compression is not a performance feature, it’s a capacity feature; the first and last columns are easy to access, but aside from the last field you pay more for accessing a column later in the column order.
During Kevin’s session I spotted this tweet that reflects the view of many that are primarily interested attending sessions where they will gain technical knowledge.
Cary Millsap – Thinking Clearly about Performance: There’s not much to say about this. Cary is an excellent speaker and a PDF copy of the paper is available from the Method R website. Even thought I’d read the paper before, twice, I thought it was worthwhile attending the session.
After Cary’s presentation I headed over to Thirsty Bear for Oracle Closed World, getting there just in time to catch Kyle Hailey‘s presentation. Clearly very knowledgeable and experienced, Kyle gave an excellent demonstration of the advantages of visualising data, compared to text output.
Maria Colgan – Oracle’s Optimizer: Top Tips for Getting Optimal SQL Execution All the Time: An interesting presentation where the following were covered: check cardinality estimates using SQL Monitor;causes of incorrect statistics; usage of extended statistics. This is definitely a session worth viewing on Oracle OpenWorld On Demand.
Chen Shapira – Everything DBAs should know about TCP/IP: Having followed Chen’s blog (now at Pythian) for a while I felt confident that her presentations would be worth attending. This session did not disappoint and as the turn out was pretty low (often a problem for Unconference sessions) there was plenty of opportunity to ask questions and discuss the material. Chen started by stating, “You need to find the keywords to use when talking to be people in different teams.” I couldn’t agree more with this and this is a factor in me spending a reasonable amount of my “play” time doing “non-DBA” activities. Other points that arose during the presentation are: SQLNET.EXPIRE_TIME and tcp_keepalive_time can be used to keep communication going between client and server in order to stop the firewall killing connections that appear idle. EXPIRE_TIME is not set by default; do a quick calculation before you do anything! If this approach had been followed in the example given then it would have been clear before contracts were signed that the network was not capable of supporting the traffic generated by the database used in a Data Guard configuration across the Atlantic; discussion of how setting ARRAYSIZE/setFetchSize() can make a big impact if lots of data is being selected; use tnsping rather than ping as it is the correct protocol if you want to assess SQL*Net performance; explanation of “advertised window” and the effect of errors on window size. Another presentation that is definitely worth downloading, but unfortunately because it was Unconference rather than OpenWorld it will not be available via OpenWorld on Demand. Discussion after the session revealed the following network related blog recommendationeds:
nmap – blog post from Frits Hoogland
Wireshark – blog post (first of a 7 part series) from Charles Hooper
SQL*Net encryption – blog post from Larent Schneider
Christian Antognini – Join Techniques: Even though the presentation was based on a chapter from Christian’s book, which I had already read (and can’t recommend strongly enough), it was a session that was definitely worth attending. As the full presentation with audio should be available via Oracle OpenWorld on Demand I won’t try to cover the technical details of the session here.
Oracle Closed World – The final day of Oracle Closed World saw some great presentation from Kevin Closson, Chen Shapira and Jeff Needham. Kevin offered to talk about whatever the audience wanted to ask and covered both NUMA and SMT, as well as the odd question about guns! Chen gave a really good presentation on NoSQL. I think some people believed she was trying to encourage it’s use, but to me it appeared to be a great overview of what NoSQL is all about. Jeff talked about the use of Windows and Direct NFS.
Bloggers Meetup – I think good time was had by all and it provided a great opportunity to meet fellow bloggers. Chet Justice aka ORACLENERD has already provided good coverage of the event so I won’t replicate his work here… I came surprisingly close to winning some kind of HP network storage device, but missed out by a few signatures. If I’d have know it would be so close I would have got a few more! All good fun and good to meet people that I’ve only every interacted with electronically before.
Party – Free food, free beer and lots of trouble finding the different groups of people I was trying to hook up with due to a big delay with SMS delivery…
Jean-Pierre Dijcks – Best Practices for Managing Parallel Execution in Concurrent Environments: Had no prior knowledge of Jean-Pierre and as friends went off to different sessions I had a moment or two of doubt, questioning if I was going to regret my choice. It turned out to be the opposite. Both the information and the delivery were really good. The presentation covered use of Services, Resource Manager and the new parallelisation parameters that have come in for 11.2. If my memory serves me well then at least a year ago I’d caught an Exadata talk by Joel Goodman where he touched on the advances in Resource Manager that were on the way. From reports in the field I don’t think there is a particularly great view of Resource Manager and one reason for this is traceability of the decisions it takes. I think it was Dan Morgan that raised this during the presentation and from the response it appears that Jean-Pierre, an Oracle employee, is aware that it would be wise to address this.
Jože Senegačnik - Query Transformation: Possibly the session I’m most glad I attended. The presentation style was great; the content interesting and technical. I didn’t take too many notes as I didn’t want to miss anything. Jože gave a very clear explanation of what both ANTI and SEMI joins are; explained that the optimizer will consider all transformations that are available to it; explained Join Factorization including that Oracle have filed a patent for it; covered that join elimination requires foreign-key constraints. One thing that really jumped out at me was hearing that join elimination is not currently supported for multi-colum primary-key foreign-key constraints and I’ll blog about why this stood out to me very soon. The last slide of the presentation provided a list of references, which is always nice to see, so I’ll definitely be downloading that for further reading.
Saroj Dash - Oracle Exadata: Myths, Discoveries, and Best Practices: I didn’t really get a great deal from this presentation apart from the following: “The architecture of databases changes at 10g because of ASM.”; it is no longer possible to by the Storage Servers on their own and therefore the only option is to buy the Database Machine; don’t use EHCC (Exadata Hybrid Columnar Compression) with tables that will have updates; cost of ownership is reduced due to not having to pay for Oracle Database Server licenses for the CPUs that are on the Storage Cells. Resource Manager allows for better meeting of SLAs. I got the impression that there was some misinformation provided in the presentation that was picked up on my some members of the audience. There was certainly plenty of Q & A after.
Arup Nanda – Analyzing Application Performance in Oracle RAC: Arup is a great presenter and covered how to monitor and trace database sessions very clearly. Anyone that feels they don’t already have a good grasp of how to do this would gain a lot from catching this session via OpenWorld on Demand.
Mark Farnham – Physical Ordering of Data: Is It Ever Useful?: This was a very interesting subject and one that I felt I already knew the answer to from information I’d picked up from others. However, this was a worthwhile session as Mark talked through his testing in order to come up with not just an answer to the question, but numbers that clearly demonstrate that physical ordering can be important. By this point in the conference my brain was not working at full speed, so I’ll be watching this on reply too!
It’s a Wrap – That was all for another year. As I sat drinking beer, eating tacos and listing to the band play in the beautiful San Francisco sunshine, I felt pretty certain that I’ll make the effort to get over again next year…