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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxx> 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
