Applied Analytics Part 1

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.

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.

FND_TOP/sql script descriptions

There are a lot of useful scripts provided by Oracle to assist with management of the E-Busness Suite. Several such scripts are located in the $FND_TOP/sql directory.

Some of the scripts I have used include
afcmrrq.sql - to display information about concurrent manager processing.
wfstatus.sql - to provide information to Oracle support about workflow processing.

My Oracle Support article Oracle Applications Object Library SQL scripts [ID 108185.1] gives some detailed information on these scripts. This note should be reviewed in order to get a better understanding of the provided scripts. Oracle may have already provided a script you were thinking of creating.

What is that E-Business Suite module?

The module name FASDPAPV is running slow.

Great, what does that mean?

While looking at performance data for Oracle EBS in the system you will see the name of the offending module which is needed to perform searches on My Oracle Support. However, to determine what part of the application is affected it may be helpful to translate that module name to a more descriptive name that provides some insight as to where the module is being used.

The names of the modules do provide some hint as to their purpose. Usually the short name for the Application is included at the begining of the Module name. An Apps DBA can determine the Application from the short name. The Action will indicate if the module is a form or concurrent program.

To retrieve the descriptive name of the module you can run an additional query against the database. I've included some queries below for Forms and Concurrent Programs. These queries will also return a descriptive name of the calling application.

For Form modules:

select app_desc.application_name, app_desc.description, form_desc.user_form_name, form_desc.description
from fnd_form form, fnd_form_tl form_desc, fnd_application_tl app_desc
where form.application_id=app_desc.application_id
and form.form_id=form_desc.form_id
and form.form_name=:Insert_Form_Name

For Concurrent Program modules:

select app_desc.application_name, app_desc.description, prog_desc.user_concurrent_program_name, prog_desc.description
from fnd_concurrent_programs prog, fnd_concurrent_programs_tl prog_desc, fnd_application_tl app_desc
where prog.application_id=app_desc.application_id
and prog.concurrent_program_id=prog_desc.concurrent_program_id
and prog.concurrent_program_name=:Insert_Concurrent_Program_Name

Using the query above, the FASDPAPV module is a concurrent program Mass Depreciation Adjustment Preview Report running for the Assets program.

Optimizing Oracle SQL Class

I have completed the Hotsos Optimizing Oracle SQL, Intensive class this past week. The class was held in San Francisco, taught by Ric Van Dyke. Having worked with Oracle for so long, I am familiar with basic SQL Tuning concepts. This class was a deep dive into advanced topics related to SQL and the Oracle Optimizer.

After completing the course I was surprised at how much I learned during my week. In my most recent job I did less SQL tuning than I had in the past. Although most of my older knowledge was still valid, Ric covered several new items that updated my understanding of the optimizer engine.

Some of the key items I took back from the class include the following:
1. The importance of using Logical I/Os as a key metric for tuning.
2. The best way to quickly interpet the lengthy and confusing Oracle trace files.
3. Numerous tips to improve performance of queries.
4. New ways to structure SQL statements to be even more efficent.
5. A better understanding of how cool analytics really are.

I'd recommend this class for DBAs and Developers.

One Week down

I've completed my first week as a Hotsos employee. The first week has been an exciting and rewarding experience. Most of my time has been spent learning the HAWCS tool. This tool provides some really powerful monitoring of the overall performance of an E-Business Suite instance.

HAWCS will identify where most of your database activity occurs and what dates/times are the most active. That information is a critical starting point to managing the performance of the E-Business Suite. Over the coming weeks I will be spending a lot more time with this product.

I've been most impressed with the options with monitoring Concurrent Manager processes. For most EBS shops the Concurrent Manager is the primary consumer of resources. Being able to quickly identify potential issues with the Managers is a must for Applications DBAs. From what I've seen so far, the HAWCS tool certainly delivers.

I have also done some work troubleshooting a VM Template install of E-Business Suite. These VM templates are a great way to quickly get a sandbox or small test instance up and running. I still wouldn't recommend doing any serious development/testing from the templates and certainly not running a production instance. In any case, they are a good place to start working with EBS.

Tips for using vi editor

There are several places online where you can find a list of shortcuts of vi commands. If you are a user of vi, I recommend printing out a copy of the shortcuts and keeping them near your computer. For this blog post I will not provide a list shortcuts, I will discuss a few of the lesser known shortcuts that I use frequently.

I like to use vi to help create one-off scripts to help accomplish tasks. For example, assume you are given a list of 80 tables to truncate. You have a list of table names, but that is all. Using vi, you can perform the following steps to create a script.

Assuming your database is running on a Linux/UNIX server, log into that server as a user that can sqlplus into the database. Use vi to create an SQL file. Paste the list of table names into the file. Now you can do the following to change the line
          employee
into the SQL command 
          truncate table employee;

Replace the begining of the line with "truncate table " and replace the end of the line with ";". After those changes you will have altered a list of tables into a list of SQL commands. To change the beginning of the line you can use the vi command:
          :g/^/s//truncate table /g
To change the end of line you can use
          :g/$/s//;
The beginning of the line is represented by "^" while the end of line is "$". You can use the replace command to replace any text. Many people are not aware of using the end of line and start of line characters in this way. This trick can allow you to quickly create scripts to accomplish many different tasks.

Other vi commands I use frequently include
"." - this will repeat the command you have just run
"/" and "n" - this will search for a string, pressing "n" will search for next instance of the string.
"yy" and "p" - this will yank a line of text, the "p" will paste the text where at your cursor location.


What are some of your favorite vi tips and tricks?