Making SQL Performance Solutions Stick Webinar Followup

Thanks again to everyone who attended part 2 of my Oracle SQL Performance webinar series entitled Making SQL Performance Solutions Stick. Embarcadero will be posting the recording within the next few days. In the meantime, I have uploaded the slides and scripts.

Presentation (pdf)

Scripts (zip)

Thanks again and don't forget about the final installment in the series coming next month on September 29 entitled Making Impactful Performance Changes.

I hope to see you then!

I was wrong

Thanks to dear Maria Colgan for her intervention, I am publicly admitting the error of my ways and declaring to never use the drug of the optimizer_index_cost_adj parameter again. I publicly admit (again) that I have (on occasion) used an extremely high OICA setting to try and "force" Exadata to use full table scans over index scans. I was wrong and I am sorry.

Snicker...

I've had a great time at the Enkitec Extreme Exadata Expo (E4) this week in Dallas. I eagerly awaited the presentation of Maria Colgan ("The Optimizer Lady") where she promised to "show through the use of real-world examples what you can do to ensure the Optimizer fully understands the capabilities of the platform it is running on without having to mess with initialization parameters or Optimizer hints."

During her presentation today, she did offer a couple of ways (made available in Exadata patch bundles...only one of which is currently available) to help clue the optimizer in on a couple of Exadata features so that it can make adjustments to cost computations. The patch that is available allows you to collect system stats in "Exadata" mode. By doing so, the MBRC system statistic is actually recorded and made available to the optimizer and results in a reduction of the cost of a full scan. This ultimately results in full scans being selected more frequently (versus an index scan operation) which opens up the possibility of more smart scans being able to kick in. As Maria told us, the MBRC value is currently not retained in the system stats values and the optimizer uses a "default" of 8 (and has since Oracle 8 days). The patch allows a system stats collection done using the Exadata mode to collect and retain an actual (more realistic) MBRC value that can provide more accuracy.

I'm still holding out for options to affect specific individual SQL statements on a case-by-case basis versus a more broad brush approach that can cause all SQL to be affected, but this looks like a good start. In the meantime, I promise to do my very best to refrain from resorting to using OICA.

Disclaimer: I'm not promising to *not* use other parameters or hints...yet.  :)

Thanks to everyone who attended E4 and I hope you shared my opinion that it was a fantastic event. I'm already looking forward to doing it again next year!

Learning to read

Not me...(I learned to read quite a few years ago).  :)

My daughter is learning to read and is sounding out words and leaving notes and signs all over the house. I love it! We've been reading several different series of books together (The Magic Treehouse, Junie B. Jones, Ivy & Bean) and she loves them. I'm thrilled to see her developing a love of reading.

But my favorite part of this adventure so far is her attempts at writing. I watch her concentrating so intensely at her desk and writing each letter. When she first started doing this, I was lucky to be able to decipher anything she had written. But now, I can almost always decode what she writes.

This morning she had left this outside my door for me to find.

Can you figure it out? It's "circus ticket" (srkis tikit). She had created a circus in her room with different acts that she performed. But, I had to have a ticket in order to be admitted. I have to say that it was the best s-r-k-i-s I ever attended.  :)

Making SQL Performance Solutions Stick Webinar - August 29

Join me for the 2nd Embarcadero Community webinar in my Performance Series on August 29 entitled "Making SQL Performance Solutions Stick". Registration is now open and I'd love to see you there!

Don't worry if you didn't attend the first session in July. This session will have plenty of new information and does not have any dependency on having to attend the first event.

I'll be talking about:

• How to adjust statistics collection to accommodate for "sensitive SQL".
• The differences between SQL Profiles and SQL Baselines and how to implement them.
• How and when you can fool the optimizer into using a desired execution plan without changing SQL text.

The 3rd and final session, "Making Impactful Performance Changes", will be on September 26 and you can also register for it now as well. I'll post again about that session after the August webinar is behind us.

Hope to see you there!

*** By the way, the last session had several SQL Server attendees, so I just wanted to make sure everyone knows these sessions are Oracle-centric.