Re: inefficient sql

  • From: rjamya <rjamya@xxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 30 May 2012 10:25:18 -0400

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.


On Wed, May 30, 2012 at 10:05 AM, Iotzov, Iordan <IIotzov@xxxxxxxxxxxxxxx>wrote:

> LIO to rows is indeed a good ratio to monitor. This ratio is, however,
> practically meaningless for SQLs that do aggregates, such as COUNT, SUM,
> AVG, etc.  Separating aggregate from regular SQLs would be the biggest
> challenge for an automated report.


Other related posts: