Not that long ago, I gave a tutorial on Explain Plan to our application development staff. One question that came up was how do I go about deciding which SQL statements need tuning? I use a few different approaches and find SQL tuning candidates from different angles.
- I routinely perform code reviews on SQL statements that hit our database. I leverage my experience to quickly look at a SQL statement that is new or has been significantly modified to decide if I need to investigate the SQL further. For instance, if a query on a table is searching on a PK or Unique column, then I can be pretty sure it will run fast. If the SQL statement looks suspect to me, I will run it through Explain Plan and/or time the statement to ensure it runs sufficiently.
- I have Oracle’s Enterprise Manager alert me for resource contention in our Test database system. Any SQL statements I missed during code review can sometimes be found here. If I get an alert of resource contention, I will jump on and see if a SQL statement or two is causing a problem. This is my last chance to catch SQL statements before they hit production.
- I leverage Ignite for Oracle. This product is from Confio, but Confio is now part of Solarwinds. Normally, I don’t plug vendor’s products, but I’ll make an exception in this case. The thing I like about Ignite is that I have it email me a report every Monday. The report contains the Top N offenders based on wait time. It takes me seconds to look at this report. I look for the big bars in the report. In the example screen shot below, you can see a blue bar that immediately grabs your attention. The number to the right is an id value and if you click on it, you can get the SQL text. This is a great way of identifying the SQL statements that need tuning. It is super quick and super easy.
With enhanced length and thickness of the male reproductive levitra price in india system. Canada on the web drug store has always gives cialis online india during this drugs. The symptoms arise without warning, they tadalafil cheap india are at the peak of sexual stimulation. The degree of its absorption is independent of food buying cialis in spain intake.
As you can see, I try to find problematic SQL as they are being developed, when they are in our test system, and even after they hit production.
