Re: toad's query to find index sql

It's not a goal! If this is a goal I would have look for the script ages ago :-)

I just wondered how a tool can offer such chart with an incorrect query and due to that I was curious if such query can be achieved.

Thanks!

On Mon, May 18, 2015 at 4:53 PM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> I question the goal.
>
>
>
> The underlying goal seems to be arriving at some ratio of number of
> queries that are via indexed access as if a higher number is better in some
> way than a lower number.
>
>
>
> Some queries are better served by full table scans.
>
> Some queries are better served by index access.
>
>
>
> The texture of any given application may vary with regard to the
> percentage of queries that are effectively planned for index access.
>
>
>
> Even the best access plan for an individual query may vary over time,
> predicate, competing load, and hardware components.
>
>
>
> A better goal would be to discover queries that could be better executed
> utilizing some indexes (1 or more) that may or may not exist that are
> currently expensive using full table or full partition scans.
>
>
>
> Once found it would be reasonable to evaluate whether the integration of
> the value of less resource cost and elapsed time over all the executions of
> that query justifies the extra cost of maintaining any additional indexes
> required.
>
>
>
> For that goal you might search for a large discrepancy between the number
> of rows scanned from various row sources and the final row count delivered
> (though aggregations could create false positives).
>
>
>
> And while I think arguing about the definition of this metric is akin to
> arguing about whether the first read of data should count in calculations
> of the buffer hit ratio, I agree the query you’re quoting does not match
> the described metric (that I don’t think you should collect because it is
> potentially misleading and not useful.)
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Ls Cheng
> *Sent:* Monday, May 18, 2015 10:26 AM
> *To:* Oracle Mailinglist
> *Subject:* toad's query to find index sql
>
>
>
> Hi
>
> One of my developers came in today and told me that most of the queris in
> their application is 99.99% indexed. He got this numbers because he used
> TOAD to get the statistics. I looked TOAD's query and it is this one:
>
> SELECT SUM (DECODE (NAME, 'table scans (long tables)', VALUE, 0))
> / ( SUM (DECODE (NAME, 'table scans (long tables)', VALUE, 0))
> + SUM (DECODE (NAME, 'table scans (short tables)', VALUE, 0)))
> * 100
> NON_INDEXED_SQL,
> 100
> - SUM (DECODE (NAME, 'table scans (long tables)', VALUE, 0))
> / ( SUM (DECODE (NAME, 'table scans (long tables)', VALUE, 0))
> + SUM (DECODE (NAME, 'table scans (short tables)', VALUE, 0)))
> * 100
> INDEXED_SQL
> FROM V$SYSSTAT
> WHERE 1 = 1
> AND (NAME IN ('table scans (long tables)',
> 'table scans (short tables)'))
>
> I think this query is not correct because it is comparing table scans
> (long tables) vs table scans (short tables) which both are full table scans.
>
> Anyone know any useful statistics to derive the percentage of index scan
> and table scan?
>
> Thanks
>
>

--
http://www.freelists.org/webpage/oracle-l