Re: toad's query to find index sql

From: Ls Cheng <exriscer_at_gmail.com>
Date: Tue, 19 May 2015 09:03:52 +0200
Message-ID: <CAJ2-Qb_48a-NtMCqTgaCr_wFvYmpNsrU5j1LqW0-ag6ByEv4ag_at_mail.gmail.com>



oh no, I probably caused wrong impressions. This developer is new, he just started to work a couple of weeks ago and he probably brought in what he think it is useful in his old work spaces.

I have never tried to look such metric because I know full scan is not worse than index scans under certain conditions. What I need to do is to show this guy the point.

I never questioned TOAD's features until today but that is probably because I have only used it to write sql and plsql code.

Thanks

On Tue, May 19, 2015 at 2:17 AM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> Well it was apparently something the developers who approached you were
> proud of, so that it was a goal for them (not you) is what I perceived.
>
>
>
> I recognize you as a fellow seeker of truth in Oracle technology, so I
> offered my best rationale that noodling out the details to make an accurate
> version of that metric would be wasted effort at best and the cause of
> silly work at worst.
>
>
>
> Quest’s tool has a lot of good features. People astute enough (as
> yourself) to notice that it is bogus usually don’t look at that metric in
> the first place and so the query never got caught as a bug.
>
>
>
> I think you could do the effective calculation as count of all executions
> in the denominator and the count of all executions minus the executions you
> considered scan in the numerator.
>
>
>
> Someone else in this thread pointed out that it is an issue of definition
> whether full index scans are considered index support. While they do scan
> everything, they usually smaller than the full table scan even though every
> row may be found (except perhaps all null keys), so they are qualitatively
> different from index range and equality lookups.
>
>
>
> So quite a bit of definition would go into the full spec for the query.
> And even then you’d not have a useful metric in my opinion as I described
> below.
>
>
>
> However it might be worth it in your particular case to debunk the current
> impression your developers have. I’d set a goal of educating your
> developers about some useful metrics and help them identify correct queries
> (whether in Quest or from Oracle or homegrown) to help them assess those
> useful metrics.
>
>
>
> Good luck!
>
>
>
> 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 2:29 PM
> *To:* Mark W. Farnham
> *Cc:* Oracle Mailinglist
> *Subject:* Re: toad's query to find index sql
>
>
>
> Hi
>
> 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
Received on Tue May 19 2015 - 09:03:52 CEST

Original text of this message