oh no, I probably caused wrong impressions. This developer is new, he just
started to work a couple of weeks ago and he probably brought in what he
think it is useful in his old work spaces.
I have never tried to look such metric because I know full scan is not
worse than index scans under certain conditions. What I need to do is to
show this guy the point.
I never questioned TOAD's features until today but that is probably because
I have only used it to write sql and plsql code.
Thanks
On Tue, May 19, 2015 at 2:17 AM, Mark W. Farnham <mwf@xxxxxxxx> wrote:
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
