Hints, up and down

Last week was AOUG conference 2017. There I attended Lothar Flatz' Any Hint, anywhere. There Lothar mentioned it's not required to have hints in the first / topmost SELECT. Even the sentence itself was clear for me, the consequences and possibilities were not at that time.
During the presentation and some discussion with Lothar afterwards, some possibilities were shown.

---

Preparation:

drop table T1;
create table T1 as 
select rownum as rn, 'A' as const from dual connect by rownum < 1000000;

create unique index T1I1 on T1 (rn);
create index T1I2 on T1 (const);

update T1 set const='B' where rn=42;
create view V1 as select * from T1;

---

SQLs with direct hints:

select /*+ qb_name(test1) */ const from V1
where rn = 42;

with plan

Plan hash value: 4265153043
 
-----------------------------------------------------
| Id  | Operation                   | Name | E-Rows |
-----------------------------------------------------
|   0 | SELECT STATEMENT            |      |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |      1 |
|*  2 |   INDEX UNIQUE SCAN         | T1I1 |      1 |
-----------------------------------------------------

and with another query

select /*+ qb_name(test2) */ avg(rn) average
from V1 
where const='A';

Plan hash value: 3724264953
 
--------------------------------------------
| Id  | Operation          | Name | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT   |      |        |
|   1 |  SORT AGGREGATE    |      |      1 |
|*  2 |   TABLE ACCESS FULL| T1   |    999K|
--------------------------------------------

the hints work like a charm:

select /*+ qb_name(test1) FULL(@"SEL$63695B56" "T1") */ const from V1
where rn = 42;

Plan hash value: 3617692013
 
-------------------------------------------
| Id  | Operation         | Name | E-Rows |
-------------------------------------------
|   0 | SELECT STATEMENT  |      |        |
|*  1 |  TABLE ACCESS FULL| T1   |      1 |
-------------------------------------------

and

select /*+ qb_name(test2) INDEX(@"SEL$0E4D0AA2" "T1"@"SEL$1" ("T1"."CONST")) */ avg(rn) average
from V1 
where const='A';

Plan hash value: 2287954029
 
--------------------------------------------------------------
| Id  | Operation                            | Name | E-Rows |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |        |
|   1 |  SORT AGGREGATE                      |      |      1 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   |    999K|
|*  3 |    INDEX RANGE SCAN                  | T1I2 |    999K|
--------------------------------------------------------------

---

Hints hidden in the view:

When I "hide" the hints inside the view definition:

create or replace view V1 as 
select /*+ FULL(@"SEL$63695B56" "T1") INDEX(@"SEL$0E4D0AA2" "T1"@"SEL$1" ("T1"."CONST")) */ * from T1;

they still work:

select /*+ qb_name(test1) */ const from V1
where rn = 42;

Plan hash value: 3617692013
 
-------------------------------------------
| Id  | Operation         | Name | E-Rows |
-------------------------------------------
|   0 | SELECT STATEMENT  |      |        |
|*  1 |  TABLE ACCESS FULL| T1   |      1 |
-------------------------------------------

and

select /*+ qb_name(test2) */ avg(rn) average
from V1 
where const='A';

Plan hash value: 2287954029
 
--------------------------------------------------------------
| Id  | Operation                            | Name | E-Rows |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |        |
|   1 |  SORT AGGREGATE                      |      |      1 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   |    999K|
|*  3 |    INDEX RANGE SCAN                  | T1I2 |    999K|
--------------------------------------------------------------

---

When you can edit only parts of the query:

Sometimes an application allows only changes on parts of the query, like additional filter in the WHERE clause:
(let's reset the view:)

create or replace view V1 as select * from T1;

As the hint can only be in the SELECT part, we need to create an "artificial" select:

select /*+ qb_name(test1) */ const from V1
where rn = 42
  and 1 = (select /*+ FULL(@"SEL$63695B56" "T1") */1 from dual);

so it works as well:

Plan hash value: 317588836
 
--------------------------------------------
| Id  | Operation          | Name | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT   |      |        |
|*  1 |  FILTER            |      |        |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |
|   3 |   FAST DUAL        |      |      1 |
--------------------------------------------

---

Conclusion:

There are many ways to place a hint, so as long as you can manipulate any part of the query, ther eis a chance to inject a hint.

Method R Profiler 7.0 available

I'm seldom doing product announcements on my blog. So there must be a reason why I do not follow this rule.

Method-R recently released a new version of it's powerful SQL-Trace profiler.
Of course there are many improvements on the profiler engine itself, but as the prodict was very stable already they will only help in rare edge cases, most people would not benefit dramatically.

But there is a new featuere which brings a lot of added value to the tool and improves time to repair/fix/enlightenment for many readers of the trace file:
They added an explanatory text to many sections.
Here an example (from their sample file - slightly edited to better fit the size of this blog):

Your experience duration was dominated by “cell single block physical read” and “CPU: FETCH dbcalls” calls. These are all high-productivity calls; their dominance means that the Oracle Database is working hard for your application. Now you should determine whether all of that work is actually necessary. Consult the Profile by Cursor and Profile by Statement sections for the next step of your diagnosis.

	Subroutine	Duration		Calls	Duration per call (seconds)
		seconds	% R		mean	min	skew	max
1	cell single block physical read	4.644	69.9%	6,899	0.0007	0.0005	▁▁▁█▁▁▁▁▁▁▁	0.028
2	CPU: FETCH dbcalls	2.381	35.8%	7,947	0.0003	0.0000	▁▁▁▁▁▁▁▁▁▁▁▁▁▁▂▁█▁▁	2.071

Unfortunately I did not manage to copy all the nice formatting into this blog, so i created a screenshot to show the original picture.

As you can see there is a new text with more in detail explanation about the Subroutine and where to go next for deeper analysis. I see this as a big advantage for everyone who does not work with SQL-Trace files every day. (well, even for those it's helpful).

There is a very nice demo version available, so if you have a trace file to analyze and want to check, if you see added value by this suggestion, give it a try.

Beside the new Profiler, Method-R has redone their web page as well (and I expect more news to follow). It's worth to invest some minutes and have a glimpse.