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

xplan 2.1.3 is out

Alberto Dell'Era released a ne version of his xplan utility. Version 2.1.3 is out now.
Why is it's worth to mention?
1st he fixed a problem I faced within extraordinary speed (< 4 hrs!):

declare /* xplan_exec_marker */  *** error before main block (
processing XPLAN_OPTIONS ) *** -- main block
                                *
ERROR at line 1:
ORA-06550: line 1, column 34:
PLS-00103: Encountered the symbol "*" when expecting one of the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior

--in setup.lst it's this error:

declare /* xplan_exec_marker */ -- process options
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 28

and 2nd: he didn't even announce it himself, so someone must do it :)