Possibly random thoughts of a oddly organized dba with a very short attention span


Back from Hotsos ... more work to do

My presentation at the Hotsos Symposium was on March 5th and I received quite a bit of positive feedback on my topic. It seems that there is enough interest to continue pursuing this line of thinking and I'm very excited about the possibilities. However, in an ironic turn of events, I have a pilot that will be perfect for further testing on variation but the details can't be posted due to confidentially stuff. Hopefully there will still be generic information that can be shared.

The Symposium was fabulous as always. Saw many friends and made some new ones. Also received an email from a former coworker who learned of my presentation via Alex G's blog. It's amazing how this blogging world works ...

1. I had to leave Tapio Lahdenmaki's presentation on indexes right after it started due to an important issue at home. I was quite disappointed as it was one of the presentations I had most looked forward to, but fortunately, he provided excellent notes along with his slides.

2. Riyaj Shamsudeem did a very information-packed presentation on a new way to use a 10053 trace. This one may need to be a 2 hour time slot next time, but I like presentations that leave me wanting more.

3. Andy Zitelli's topic on leveraging the extended SQL trace was an eye opener too. I'm going to have to try out his scripts and see if this approach could be applied to our benchmarking and load testing efforts.

4. Dominic Demolino's presentation on using auditing for Change Management and upgrade scripting was another one that got me thinking about an old problem from a new angle. I look forward to experimenting with these scripts too. I had wanted to talk to Dominic about his recent blogs on old parameters in upgraded databases but didn't get the chance. His posts made a very good point and I agree with him: init.ora parameters should be reconsidered at every upgrade. Similarly, sites that use ip node checking in their sqlnet.ora files need to take the time to clean up the old addresses. It does little good to limit connections by ip address if the included address list is never purged.

I actually wrote the notes above in the week after Hotsos, however due to a now resolved family crisis, it's been sitting in 'draft' status ever since. So the info is delayed but will still serve as a reminder for things to follow up on.

As for what I've been up to since Hotsos, here's the short list:

I have set up our first real Oracle 11g install to be used for real development. Thus far, I'm unexpectedly pleased with it considering it's still on the new side. There will be more on this to come.

Have also had the opportunity to benchmark an Oracle competitor for large databases. It's strange to be on a new technology and one without instrumentation at that, but the product looks sufficiently interesting for further tests. There should be more on this later as well.

Continuing to look for ways to add instrumentation to our applications. Lots of thoughts on this, but more research is needed.

Think this post shall be filed under 'to do list' :)


mathewbutler said...

I read your paper. Some interesting points.

How do you see this method being applied to a more OLTP environment? An application would need to capture some performance information in an efficient manner ( something similar to the AWR implementation). Thinking about this, it may even be possible to apply the variance approach to the contents of AWR.

I'm also wondering how to compare the same "job" when different executions may work on vastly different volumes of data. For example a job that might run across a single day every day, abnd then across the whole month at the end of the month. Would these be treated as one job for the purposes of anaysis, or instead as two jobs to capture the two very different uses and volume of data processed?

BTW - I've subscribed to your feed, based on the paper, and content here.

Best Wishes,

Robyn said...

Hi Mathew,

Thanks for the feedback and for subscribing. I started writing here as I worked on the paper and plan to post more as I work to develop the ideas further.

Applying variance analysis to OLTP is the next challenge. (well, one of many) I'm planning on instrumenting one of our applications for testing, and will continue looking at other options for data collection as well.

Your question about the quantity of data is a good one. I tested several approaches to analyzing our dataset and for the purposes of this paper, I decided to run the analysis over one full fiscal year. Our jobs are broken up into daily, weekly and monthly jobs, so a job that runs once a month has a unique job number from the daily version. Therefore, monthly jobs were not compared to daily jobs.

However, some of our jobs do process vastly different volumes of data at different executions. For my data set, analyzing the variance in processing times still helped identify code that needed tuning. I'll be running the same comparison for this fiscal year and will also be testing different types of databases, like OLTP. Hopefully, the testing will lead to an analysis process that can be useful for other systems.

Thank you for your questions and I hope to hear from you again ... Robyn

mathewbutler said...

Todays post reminded me tocome back here to respond. You should post more( or I should monitor comments more closely :o) ).

In the past I've toyed with the idea of comparing runtimes of jobs based on a measure of the data processed. eg:

- number of days in reporting period
- number of customers included in report

The problem is that no one metric is always usable. Sometimes the data distribution across a number days is skewed, or customer usage (and therefore data generation) is skewed.

I think that whatever the metric, it will be system specific, and further, it might require instrumentation in the reporting process to capture whatever metric is to be used.

I worked on a system in the past that used to capture the CPU/ I/O / memory used during the reporting/processing period for a job. Kind of like an application specific STATSPACK or AWR. I'm wondering if some combination of these might be a more general metric that could be used for analysis.

Just thinking out loud.

Best Wishes.

Robyn said...

You are absolutely correct. Almost any metric can be used to measure variance and what is useful for one system may not be for another. I prefer to look at variance in total elapsed time for a process over many executions but variance in the amount of data being processed needs to be considered as a factor with the et measure.

I've working on two new presentation topics and one of them is specifically related to different ways of measuring variance in Oracle performance. There should be more on the blog related to that topic as I start sorting through my stuff to make it useful for others.

Gerwin Hendriksen of AMIS Services BV in the Netherlands had an interesting presentation at Hotsos this year. He had developed a tool for measuring variance in the overall CPU and IO levels and using that info to identify performance bottlenecks. I believe he's planning to develop it further and if I hear any news, I'll post a reference here. Basically he's looking at variance outside the database and I'm looking for it within the database.

Thank you for the encouragement and I do hope to post more frequently :)

Search This Blog


My Blog List

disclaimer ...

The views expressed on this page are mine and do not reflect the opinions of my employer, former employers, other associates or the opinions I may hold 3 days from now.