tinmi01

SYSMSG Alerts on Non-RQAble Predicates

Blog Post created by tinmi01 Employee on Oct 28, 2014

1. RQA Awareness

 

In a previous post I discussed the problem of non-RQAble predicates. Recall

that such predicates are "invisible" to the part of the Datacom engine that

performs physical reads. This can seriously hurt performance when reading

indexed data where the non-RQAble predicate (if visible) would have allowed

Datacom to restrict its index scan to a smaller range.


Section 2.1 of the linked post gave some examples of non-RQAble predicate

alerts that I added to the SYSMSG optimization report. These alerts were quite

verbose, and I've since realized that a more concise style is preferable. So the

purpose of my post today is to introduce a new prototype for SYSMSG alerts

on non-RQAble predicates.


2. Examples of New RQA Alerts


These alerts are no longer broken out by join step; and are only issued for

non-RQAble predicates that reference an indexed column.


2.1 First example, no indexed columns


We have created a tiny table,

-- The names table!

CREATE TABLE T1 (ID INTEGER NOT NULL, NAME CHAR(12));

And now issue a query with a very selective predicate,

-- Query #1

SELECT * FROM T1 WHERE UPPER(NAME) = 'JODIE';

Because UPPER(NAME) = 'JODIE' doesn't match the <column> <op> <expression>

pattern, it is a non-RQAble predicate. But there is no index on T1.NAME; so the

optimization report won't contain any alerts for this query.

 

2.2 Second example, high-order index scan


Now we've decided to add an index.

-- Why not?

CREATE INDEX IDX1 ON T1 (NAME);

Upon re-issuing Query #1, we discover in the optimization report,

*******************************  RQA MESSAGES  *******************************

****                                                                                                                            ****

****  YOU MAY BE ABLE TO IMPROVE YOUR QUERY PERFORMANCE BY REDESIGNING      ****

****  ANY CONSTRAINTS AND "OR" BLOCKS THAT INVOLVE THE PREDICATES BELOW.    ****

****                                                                                                                            ****

****  THESE PREDICATES WERE FLAGGED BECAUSE THEY REFERENCE INDEXED              ****

****  COLUMNS; BUT AS WRITTEN COULD NOT RESTRICT INDEX SCAN RANGE.              ****

****                                                                                                                            ****

** PRED#01: "= (CHAR)" WITH ARGS                  <= POSS. HIGH-ORDER IDX RESTRICT  ** 

**  UPPER EXEC                                                                                                              **

**    T1.NAME                                                                                                                  **

**  'JODIE'                                                                                                                      **

****                                                                                                                            ****

*****************************  END RQA MESSAGES  *****************************

 

The first two sentences will always be the same. They alert us that we're

looking at predicates which could be responsible for worse-than-expected

performance from our query. We're also reminded that we need to look at

a predicate's entire "OR" block to understand its RQAbility.

 

3. Comments and RFC

 

These SYSMSG alerts will help us identify when predicate selectivity is not

restricting the range of an index scan. Sometimes we can fix the problem.

(In the above example, if we arranged to only insert names in all caps, we

could remove the UPPER(...) function and have an RQAble predicate.) But

other times there is little to be done, and the SYSMSG alerts will be primarily

informational.


Please let me know of any further improvements we can make to these alerts,

or the optimization report in general.


Outcomes