23c free - new hints

Like every new DB version, with new features there are also new hints in V$SQL_HINT. 
In 23c free, there are "only" 16 new hints (compared to 21.9) - and of course, they are not in the documentation (yet). 

NAME	SQL_FEATURE	CLASS	TARGET_LEVEL	PROPERTY	VERSION	VERSION_OUTLINE	sql_feature_hierarchy_path
CELL_TRACE	QKSFM_ALL	CELL_TRACE	1	256	23.1.0		-> ALL
COMPRESS_IMMEDIATE	QKSFM_EXECUTION	COMPRESS_IMMEDIATE	1	0	23.1.0		ALL -> EXECUTION
JSON_QRYOVERGEN_REWRITE	QKSFM_JSON_REWRITE	JSON_QRYOVERGEN_REWRITE	2	0	23.1.0	23.1.0	ALL -> COMPILATION -> JSON_REWRITE
JSON_REFERENCE_FOR_UPDATE	QKSFM_JSON	JSON_REFERENCE_FOR_UPDATE	1	0	23.1.0	23.1.0	ALL -> COMPILATION -> JSON
MULTI_APPEND	QKSFM_EXECUTION	MULTI_APPEND	1	0	23.1.0		ALL -> EXECUTION
NO_COMPRESS_IMMEDIATE	QKSFM_EXECUTION	COMPRESS_IMMEDIATE	1	0	23.1.0		ALL -> EXECUTION
NO_JSON_QRYOVERGEN_REWRITE	QKSFM_JSON_REWRITE	JSON_QRYOVERGEN_REWRITE	2	0	23.1.0	23.1.0	ALL -> COMPILATION -> JSON_REWRITE
NO_MULTI_APPEND	QKSFM_EXECUTION	MULTI_APPEND	1	0	23.1.0		ALL -> EXECUTION
NO_PUSH_GBY_INTO_UNION_ALL	QKSFM_PUSH_GBY_INTO_UNION_ALL	PUSH_GBY_INTO_UNION_ALL	2	16	23.1.0	23.1.0	ALL -> COMPILATION -> CBO -> CBQT -> PUSH_GBY_INTO_UNION_ALL
NO_PUSH_GBY_INTO_UNION_ALL	QKSFM_PUSH_GBY_INTO_UNION_ALL	PUSH_GBY_INTO_UNION_ALL	2	16	23.1.0	23.1.0	ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> PUSH_GBY_INTO_UNION_ALL
NO_SUBGROUP	QKSFM_SUBGROUP	SUBGROUP	2	16	23.1.0	23.1.0	ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> SUBGROUP
NO_SUBSUME	QKSFM_SUBSUME	SUBSUME	2	16	23.1.0	23.1.0	ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> SUBSUME
PUSH_GBY_INTO_UNION_ALL	QKSFM_PUSH_GBY_INTO_UNION_ALL	PUSH_GBY_INTO_UNION_ALL	2	16	23.1.0	23.1.0	ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> PUSH_GBY_INTO_UNION_ALL
PUSH_GBY_INTO_UNION_ALL	QKSFM_PUSH_GBY_INTO_UNION_ALL	PUSH_GBY_INTO_UNION_ALL	2	16	23.1.0	23.1.0	ALL -> COMPILATION -> CBO -> CBQT -> PUSH_GBY_INTO_UNION_ALL
SUBGROUP	QKSFM_SUBGROUP	SUBGROUP	2	16	23.1.0	23.1.0	ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> SUBGROUP
SUBSUME	QKSFM_SUBSUME	SUBSUME	2	16	23.1.0	23.1.0	ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> SUBSUME

I did not find anything in the documentation yet, but this might change over time. 

No hints were removed. 

identify index corruption

One of my customers faced a problem with false results. 
The most remarkable detail was the inconsistency of these wrong results. It depended on the queries he run.  So his first assumption was an error in the SQL parser/optimizer/wherever. 
But as I striped down the correct/wrong SQLs more and more to get a simple testcase, maybe something to provide to Oracle Support, it became clear the SQLs are fine, just one physical representation of the data was wrong!

Yes, even in a perfectly normalized relational database, there is a lot of physically duplicated data. One might think of materialized views, but in fact, every index is an implicit duplication of some data for optimized physical access. 

Each RDBMS promises to keep this duplicated in sync (if the word would have been used in IT in last millenium, they would have called it autonomous ), and each RDBMS fails in doing so sometimes. 

I tried to identify those rows and indexes where information in the index and the row differs. Then the customer needs to understand the data and decide, which data is correct, store this data in the block and re-create all indexes which does not match the tables data. 

The basic idea is to select the rows from the index and from the table and compare it's data. 
Even this sounds easy, Oracles Optimizer does a lot of effort to avoid what it sees as unnecessary work. Getting the same column of the same row from 2 different sources is such an avoidable work. 
I first tried some queries where I get the questionable columns (and their ROWIDs) in a sub-query and then join to the table again retrieving the tables row and value. Something like: 

select /*+ qb_name(main) */ t.pk, t.problematic_column
from TABLE t 
where t.rowid in
  (
  select /*+ qb_name(get_rowid)  */ ti.rowid
  from TABLE ti 
  where ti.problematic_column='123456'
  );

But to avoid the optimizers clevernes, I had to use some hints like NO_MERGE,  NO_UNNEST,  NO_ELIMINATE_SQ(@GET_ROWID), INDEX, MATERIALIZE and there might be even more with higher versions. 

My final approach is maybe not as eligant in pure SQL, but for me it provides more stability:  

with
  FUNCTION get_problematic_column (rid in varchar2) return number IS
   cid number;
  begin
    SELECT problematic_column into cid
    from TABLE t
    where rowid=rid;
    return cid;
  end;
  select /*+ INDEX(ti, SUSPICIOUS_INDEX) qb_name(get_rowid) */
    ti.rowid, ti.pk, ti.problematic_column as problematic_column_in_index,
    get_problematic_column(rowid) as problematic_column_at_ROWID
  from TABLE ti 
  where ti.problematic_column='123456'
;

This is not the most beautiful query, but it should give a good idea. 
The function get_problematic_column does a TABLE ACCESS BY USER ROWID and we can be very sure this will not change and provide the data as it's in the tables block. 
The query get_rowid should access the TABLE via the SUSPICIOUS_INDEX - I only have to ensure the WHERE clause matches the index columns. 

With little adaptions I could test all indices for invalid columns. 

In this case the tables data was valid and one index needed to be rebuilt. Much easier than opening a Service Request at MOS. 

If anyone wants to know the underlying hardware: it's an Exadata cluster (I was never told exact specifications) with proper ASM Diskgroup configuration. 

materialized WITH query

Sometimes I have to improve a SQL query where the same (or similar) subquery is used several times within the whole statement. This leads to many times the tables needs to be visited, even for the same rows.
It also makes the query hard to read.

A real life example I had to deal with this week is something like

SELECT *
FROM ( SELECT columns, aggregate functions
       FROM some tables
       INNER JOIN
       ( SELECT IDa, 0 as IDb, col1
         FROM T1
         UNION 
         SELECT 0, IDb, col col1
         FROM T2 )
       ON some joins
       INNER JOIN
       ( SELECT IDa, 0 as IDb, col2
         FROM T1
         UNION 
         SELECT 0, IDb, col col2
         FROM T2 )
       ON some joins
       INNER JOIN
       ( SELECT IDa, 0 as IDb, col3
         FROM T1
         UNION 
         SELECT 0, IDb, col col3
         FROM T2 )
       ON some joins
       INNER JOIN
       ( SELECT IDa, 0 as IDb, col4
         FROM T1
         UNION 
         SELECT 0, IDb, col col4
         FROM T2 )
       ON some joins
       WHERE some filters
       GROUP BY columns)
WHERE more filters

In this case it's quite visible there are 4 INNER JOINs to the same UNION of 2 tables, only the columns differ.

Fig 1 original PLAN - begin

The optimizer tried it's best and the beginning of the plan looked like Fig1. 3 more iterations with SORT - VIEW - SORT - UNION ALL follow. It's amazing the cost is so low, But even with higher cost there is not much the optimizer could do.


So my idea was to put it into a WITH clause and replace the INNER JOIN select with it.

The WITH clause is

WITH my_inner as (
SELECT IDa, 0 as IDb, col1, col2, col3, col4
FROM T1
UNION ALL
SELECT   0, IDb,      col1, col2, col3, col4
FROM T2
)

 

And when replacing the first INNER JOIN

INNER JOIN
       ( SELECT * from my_inner )

Fig 2 changed PLAN - 1 replacement

It seems the optimizer did not like this as the cost increased.


But I continued with the next replacement:

Fig 3 changed PLAN - 2 replacements

The optimizer "understands" it can created a temporary object (the one which begins with SYS_TEMP_) and then re-uses it later.

With all 4 replacements, the Plan is different now:

Fig 4 changed PLAN - 4 replacements

The cost is still higher than in the original plan, but it's very likely the statement is faster than the original one.
In this case, there was no need to add the MATERIALIZED hint - it was done automatically, for very good reasons.

I like this optimization as it both, improves the readability of the query AND it's performance!

OGB Appreciation Day : manipulating execution plans

Tims call for OGB Appreciation Day 2019 (#ThanksOGB) comes in time this year.

Even unemployed at the moment, I had a chance to help a friend with some problematic SQL statements.

The statements are not that important at all but he possibilities we had to address the problems were great!


First of all an existing execution plan, together with session stats, ASH/AWR and in one case SQLTRACE. None of these sources of information shows all details we required, but all together provided sufficient information to understand the problem.
The probably most comprehensive way to get all these information (except tracing) is sqld360.

Next of course is the system of hints.
There are many discussions if hints are a good and bad thing. It's not me to argue it here.
I appreciate hints exist in Oracle RDBMS as they can be used if arguments in their favor are strong.

Third the possibilities to apply hints to a SQL are huge. Even the statement itself can not be changed, there are stored outlines, SQL Profile, SQL Plan Baselines in SQL Plan Management, SQL Patch, and if some really dirty tricks are required, SQL Translation Framework and other mean tools can be used.

So there are sufficient tools available to improve a statements execution.


The sheer amount of possibilities might be overwhelming or even deterrent. In fact it's not that complicated and results can be achieved in short time.
Writing this, it's always easy to deliver shiny fancy solutions when standing on giants shoulders.
To give some examples, here is a random selection of articles I used during the latest investigations:

Occurence - Jonathan Lewis
Fixing SQL Plans: The hard way – Part 1 - Advait Deo
SQL Profiles - Kerry Osborne
buffer sorts - Jonathan Lewis
Oracle’s OPT_ESTIMATE Hint: Usage Guide - Christo Kutrovsky
Visual SQL Tuning (VST) - Kyle Hailey

I'm grateful for the possibilities we have to fix SQL executions, and I'm also grateful for all those people who share their knowledge, so I can learn from them!

flipping performance

Recently I had a request to check "if there is any problem with the database at <specific times>".
You can imagine, there was no problem. Nothing in alert.log, no tracefiles, no locks or oddities in ASH/AWR.

I had to ask back & forth to get some more information about the issue. The information I got was:
"we use a statement SELECT * FROM table(some_function('P1', 'P2')) - and it took longer than 10 sec at the given times". Of course there were no bind variables used but constants every time.
This situation helps a lot as obviously there is nothing to do with SQL_IDs now, and the real issue is (probably) within the function.
The function just generated 1 SELECT (no BINDs again - but PL/SQL did the "auto-binding").
With this SQL it's easy to identify the SQL_ID.
This SQL_ID has 3 childs with different plans. That is sufficient to check, if the specific times somehow match a flip of plans. This was done by a simle query:

with gash as (
select sql_id, sql_child_number, sample_time, LAG(sql_child_number, 1, 0) OVER (ORDER BY sample_time) AS prev_child
from gv$active_session_history
where sql_id='&sql_id'
order by sample_time
)
select *
from gash
where sql_child_number != prev_child
order by sample_time

For a longer observation dba_hist_active_sess_history can be used as well.

The result easily showed a flip between childs/plans at the given times.

(solution was to generate "outline-hints" with dbms_xplan.display_cursor for the good plan and so hint the SQL inside of some_function).

This was no complex task to analyze or big deal to execute. Just a small example where GUIs might not help so much. By the (little) information given it would have been pure luck to see the problematic pattern in a ASH-graph. As ther was nothing to filter, all the other "noise" in the DB would have wiped the information out.

Sometimes it's good to know the architecture and views, not only the GUI.

PS: The statement above is ugly. A MATCH_RECOGNIZE would be more elegant. Unfortunately this DB is 11.2

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.