toad's query to find index sql

From: Ls Cheng <exriscer_at_gmail.com>
Date: Mon, 18 May 2015 16:26:29 +0200
Message-ID: <CAJ2-Qb828Nv8J4yurff2iqzkCLLP3ciV-28nHHwantK7rZptcQ_at_mail.gmail.com>



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 Mon May 18 2015 - 16:26:29 CEST

Original text of this message