RE: toad's query to find index sql

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