Re: inefficient sql
From: rjamya <rjamya_at_gmail.com>
Date: Wed, 30 May 2012 10:25:18 -0400
Message-ID: <CAGurbTPhL6_4+JAvQfH_GEo0FFXJ1rptAkcVuO1j+U+Tj=SWOg_at_mail.gmail.com>
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.
Date: Wed, 30 May 2012 10:25:18 -0400
Message-ID: <CAGurbTPhL6_4+JAvQfH_GEo0FFXJ1rptAkcVuO1j+U+Tj=SWOg_at_mail.gmail.com>
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
On Wed, May 30, 2012 at 10:05 AM, Iotzov, Iordan <IIotzov_at_newsamerica.com>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.
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed May 30 2012 - 09:25:18 CDT