The Technology at Oracle On Demand by Rachana
By User769227-Oracle on Mar 18, 2009
Feature Article: Oracle Customer Services Organisation Blog Week
I still remenber when I got my job offer from Oracle. I was told that I would be joining the "On Demand Group". It made me curious regarding the work culture and technologies I would be working on. As in the past my friends who were to join Oracle were informed about their job profile. It was a pleasant surprise to know that we will be exposed to the latest technologies.
We work on 10g, 10gAS, and 11i, R12, etc… to name a few. We are constantly exposed to the latest and best setups. One database feature that I like the best is “ADDM and SQL Tuning Advisor”. The team to which I belong does troubleshooting and we constantly get service requests for performance issues. Customers raise requests such as: slow system, stuck request, how can we make this sql run faster, etc…
We gear up to put out the fire by doing a step-by-step analysis. Is it network, hardware, incorrectly written query that is the culprit. Believe me narrowing it down takes some much needed ado from our end. ADDM and sql tuning comes in handy when you find that the culprit is a long running sql query. Let me explain what is ADDM in oracle’s own words :
“With Oracle Database 10g, you have one in the form of Automatic Database Diagnostic Monitor (ADDM), a sort of robotic DBA that tirelessly trolls through database performance statistics to identify bottlenecks, analyze SQL statements, and consequently offer various types of suggestions to improve performance, often in conjunction with other "advisors" such as the SQL Tuning Advisor”
SQL tuning is a critical aspect of database performance tuning and generally it was considered as a very complex activity, which was only dealt by experts. In its normal mode, the query optimizer needs to make decisions about execution plans in a very short time. As a result, it may not always be able to obtain enough information to make the best decision. Oracle 10g allows the optimizer to run in tuning mode where it can gather additional information and make recommendations about how specific statements can be tuned further. This process may take several minutes for a single statement; it is intended to be used on high-load resource-intensive statements. Imagine the developer’s condition if he has to rewrite every problematic sql. ADDM basically takes difference between snapshots taken by AWR and helps us analyse the database issues like memory related issues, Disk I/O, CPU bottlenecks, configuation etc... When we are asked to assist with databases that are performing badly. We found that the culprit was a sql statement.
We were able to identify the point in time when the performance issue began so we had a look at the AWR tables to see if there had been any plan changes for the “bad” statements around that time. We found that there was a switch from a good to a bad plan. The bad plan was doing a full table scan while the good plan was using index. DBMS_SQLTUNE package came in handy here. The recommendation offered was to create a SQL Profile that resulted in the exact plan we wanted – it eliminated the full table scan in favor of the index just as the “good” plan had. We accepted the proposed SQL Profile and watched as the long running versions completed while the new executions picked up the new plan. The same process was repeated for several other troublesome queries – all with the same excellent results. All in all sql tuning advisor is a handy tool in any dba’s toolkit.
This article was written by Rachana who is a member of Oracle's On Demand Team