RE: inefficient sql

From: Taylor, Chris David <ChrisDavid.Taylor_at_ingrambarge.com>
Date: Wed, 30 May 2012 09:31:08 -0500
Message-ID: <C5533BD628A9524496D63801704AE56D75B279D5CA_at_SPOBMEXC14.adprod.directory>



Good point by Raj. I have an internally developed snapshot capture that I can run over a 24-hour period, or a week or whatever I desire.

The upshot of this data I capture is that I can run a report off of it to list SQL statements by executions and elapsed time per execution. That way I can focus on the SQLs that get executed the most that take the most elapsed time per execution. (Focusing on what the users are waiting on)

It's ugly (at least to me) but it's handy as I also capture all the bind variables for the executions. The binds snapshot table can grow very large very quickly.

Chris Taylor

"Quality is never an accident; it is always the result of intelligent effort." -- John Ruskin (English Writer 1819-1900)

Any views and/or opinions expressed herein are my own and do not necessarily reflect the views of Ingram Industries, its affiliates, its subsidiaries or its employees.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of rjamya Sent: Wednesday, May 30, 2012 9:25 AM
To: oracle-l_at_freelists.org
Subject: Re: inefficient sql

I'd say that you need to define what _inefficient_ means in your environment since your workload is different then everyone else's. The considerations will be different for OLTP/DSS/DW and something else for exadata systems. Many moons ago, I wrote a query that would scan through automatically generated ADDM reports (scanning the views), for last n days, look at all sqls identified as candidates for tuning and list them including how many times they were identified as candidates. Also scanned db parameter changes if those were recommended by ADDM runs as a handy html report. On occasions we found it to be very handy. This was slightly better than looking at a single addm report for us.

However due to employment contract, I had to leave the only copy with my employer, some day I might need to revisit the topic and try to rewrite it.

Raj

--
http://www.freelists.org/webpage/oracle-l
Received on Wed May 30 2012 - 09:31:08 CDT

Original text of this message