Re: toad's query to find index sql

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Mon, 18 May 2015 20:23:38 +0000
Message-ID: <1712862455.1322456.1431980618232.JavaMail.yahoo_at_mail.yahoo.com>

No, it can't.  Just for information I used a variation of the query you posted to compute the ratio of long table scans to short table scans (not the table scan/index scan ratio that TOAD claims).  At the time I thought it provided good evidence for index creation. We live, we learn.
 David Fitzjarrell

Principal author, "Oracle Exadata Survival Guide"  

     On Monday, May 18, 2015 12:30 PM, Ls Cheng <exriscer_at_gmail.com> wrote:    

 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 HiOne 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 Mon May 18 2015 - 22:23:38 CEST