RE: toad's query to find index sql

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Mon, 18 May 2015 20:17:10 -0400
Message-ID: <0e8a01d091c9$26c43030$744c9090$_at_rsiz.com>

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 - 02:17:10 CEST