The Top x query can be applied to administrative queries. This common analytic query will come in handy if you are looking for top resource consumers
For example to see the top 10 largest tables in the database, the following query can be used:
Select owner, segment_name, segment_type, bytes
from (
Select owner, segment_name, segment_type, bytes,
rank () over
(order by bytes desc) as rank
from dba_segments)
where rank <= 10
This query can also be used against system tables such as v$sqlstats and v$segment_statistics to obtain performance information about the database. Using rank you can easily select the data you want.
Thursday, March 31, 2011
Friday, March 11, 2011
Hotsos Symposium 2011
I'm back home from Hotsos Symposium 2011. This was the first symposium I attended. Like many other first time attendees, my immediate thought at the end of the event was "I can't believe I haven't come to this before."
The small size of the symposium made it much easier to feel like a part of the event. The larger events can sometimes leave attendees feeling like just another number. At Hotsos Symposium that was not the case.
The sessions were first rate. Many of the presenters are some of the top level presenters available. The sessions are performance centric which lends itself to broader topics such as system management, application developement, and capacity planning.
Rather than give details on the specific sessions I attended, I'd rather talk about the main trends I saw discussed. Certain topics were discussed in multiple sessions. These key topics were:
1. Statistics
There were numerous discussions about the importance of statistics. With the Cost Based Optimizer, Oracle is very dependent on having accurate statistics. If the statistics are not valid, then there can be significant impacts to system performance. The discussions covered new statistics features in 11g as well as how to validate existing statistics.
2. AWR/ASH
Multiple presenters discussed ways to mine the AWR data to solve problems as well as proactively monitor systems. These presentions hit home with me because I often focus on AWR data during troubleshooting and performance planning. One point of concern is that AWR/ASH require the Database Diagnostic Pack. Really Oracle, we have to pay extra to get access to useful diagnostic data? As another attendee said, "Use of these tools should be mandatory!"
3. Capacity planning, performance forecasting
These topics are a big area of concern for people these days. The sessions on these topics were often the most mathematically inclined talks. People should appreciate how difficult this can be. Workload impact growth is non-linear. In fact as the performance impact begins to occur the rate of growth of impact is exponential. Dr. Neil Gunther discussed how to forecast increased workload impact using a sample size of at least 6 data load tests.
Another thing that stood out for me was the humor in the presentations. A few of my favorite moments were when presenters were able to make a strong point in a funny way. In Kerry Osborne's presentation he presented on user trends which he admitted was not based on actual data, but represented his views on the topic. Frederich Pfeiffer had a good line about hardware sizing (note item #3 above). Discussing the importance of using realistic data in test envronments for capacity forecasts he said, "If there is a performance problem after going into production, you can't suggest reverting to the test data because that ran fine." Other humourist presentations included Gwen Shapira. and Dan Fink.
Beyond the fantastic presentations and Training Day with Karen Morton, the evening events were quite entertaining. The Monday night event sponsored by Netzella was a blast. On Tuesday night Hotsos provided a Cajun themed party that was very enjoyable. All of the other little things such as quality of the food, room quality, etc were first rate.
Next year will be the tenth anniversiry of the Symposium. This will be a great event, I strongly recommend attending.
The small size of the symposium made it much easier to feel like a part of the event. The larger events can sometimes leave attendees feeling like just another number. At Hotsos Symposium that was not the case.
The sessions were first rate. Many of the presenters are some of the top level presenters available. The sessions are performance centric which lends itself to broader topics such as system management, application developement, and capacity planning.
Rather than give details on the specific sessions I attended, I'd rather talk about the main trends I saw discussed. Certain topics were discussed in multiple sessions. These key topics were:
1. Statistics
There were numerous discussions about the importance of statistics. With the Cost Based Optimizer, Oracle is very dependent on having accurate statistics. If the statistics are not valid, then there can be significant impacts to system performance. The discussions covered new statistics features in 11g as well as how to validate existing statistics.
2. AWR/ASH
Multiple presenters discussed ways to mine the AWR data to solve problems as well as proactively monitor systems. These presentions hit home with me because I often focus on AWR data during troubleshooting and performance planning. One point of concern is that AWR/ASH require the Database Diagnostic Pack. Really Oracle, we have to pay extra to get access to useful diagnostic data? As another attendee said, "Use of these tools should be mandatory!"
3. Capacity planning, performance forecasting
These topics are a big area of concern for people these days. The sessions on these topics were often the most mathematically inclined talks. People should appreciate how difficult this can be. Workload impact growth is non-linear. In fact as the performance impact begins to occur the rate of growth of impact is exponential. Dr. Neil Gunther discussed how to forecast increased workload impact using a sample size of at least 6 data load tests.
Another thing that stood out for me was the humor in the presentations. A few of my favorite moments were when presenters were able to make a strong point in a funny way. In Kerry Osborne's presentation he presented on user trends which he admitted was not based on actual data, but represented his views on the topic. Frederich Pfeiffer had a good line about hardware sizing (note item #3 above). Discussing the importance of using realistic data in test envronments for capacity forecasts he said, "If there is a performance problem after going into production, you can't suggest reverting to the test data because that ran fine." Other humourist presentations included Gwen Shapira. and Dan Fink.
Beyond the fantastic presentations and Training Day with Karen Morton, the evening events were quite entertaining. The Monday night event sponsored by Netzella was a blast. On Tuesday night Hotsos provided a Cajun themed party that was very enjoyable. All of the other little things such as quality of the food, room quality, etc were first rate.
Next year will be the tenth anniversiry of the Symposium. This will be a great event, I strongly recommend attending.
Subscribe to:
Posts (Atom)