Oracle Database Discussions

If you don’t see, at the bottom of the corresponding execution plan, a Note saying that the SQL profile or SQL patch has been used then this means that the force_matching_signature of the generated recursive query is not equal to the signature of the SQL Patch (SQL Profile)
That’s said I don’t see why a recursive query will refuse to use a SQL Profile just because it is recursively generated. It should react exactly as would react any other user sql_id. I have, though, rarely had to fix a SQL profile on a recursive query
Best regards
Mohamed

Actually, that SQL patch works as advertised (I have tried it on a test DB) on that specific SQL id 091fb1shwqyn8... But apparently, it works only when that query is called as user-level SQL, not when it is run as recursive SQL.
(Remark: it's not a problem of SQL matching signature, even though dbms_sqldiag.create_sql_patch will only create SQL patches with FORCE_MATCHING = 'NO'. Here there is no difficulty in reproducing the exact SQL id.)
Hypothesis: could this query be executed in low-level code, where SQL patches would simply be ignored?
@stephane-parenteau:

1. How bad is the issue? Could you please share details about the plan and query performance? Have dictionary statistics (dbms_stats.gather_dictionary_statistics) been gathered recently?
   (Remark: I have seen irritatingly slow data dictionaries, even with fresh statistics, on DBs with hundreds of thousands of subpartitions, but not to such an extent of having to fix low-level recursive queries like that. I mean, the idea came to mind a few times, but in the end we could live with it, these were not severe or blocking issues.)
   And 2) how is opt_param('_optimizer_push_pred_cost_based','false') going to fix it? Again, could you please share the resulting plan and query performance figures? Thanks!
   Regards,

Hi,
When I saw the performance problem, I ran the session with tkprof to discovered the bad sql (and sql_id) during the dbms_mview.refresh(out_of_place). Here is the trace of the sql.
SQL ID: 091fb1shwqyn8 Plan Hash: 2505069670
select o.owner#, o.name, o.namespace, o.obj#, d.d_timestamp,
nvl(d.property,0), o.type#, o.subname, d.d_attrs
from
dependency$ d, obj$ o where d.p_obj#=:1 and (d.p_timestamp=nvl(:2,
d.p_timestamp) or d.property=2) and o.owner#=nvl(:3,o.owner#) and
d.d_obj#=o.obj# order by o.obj#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 356 0.01 0.01 0 0 0 0
Execute 711 0.41 0.41 0 0 0 0
Fetch 1066 2887.28 2892.07 509 10219610 0 355
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2133 2887.71 2892.50 509 10219610 0 355
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 118 355 SORT ORDER BY (cr=14495 pr=111 pw=0 time=4206796 us starts=1 cost=15 size=231 card=1)
0 118 355 HASH JOIN (cr=14495 pr=111 pw=0 time=4267508 us starts=1 cost=14 size=231 card=1)
2006485 2006485 2006485 NESTED LOOPS (cr=14370 pr=0 pw=0 time=3559117 us starts=1 cost=14 size=231 card=1)
2006485 2006485 2006485 NESTED LOOPS (cr=14370 pr=0 pw=0 time=3213777 us starts=1 cost=14 size=231 card=3)
2006485 2006485 2006485 STATISTICS COLLECTOR (cr=14370 pr=0 pw=0 time=2855170 us starts=1)
2006485 2006485 2006485 VIEW VW_JF_SET$DDE30DF4 (cr=14370 pr=0 pw=0 time=2455015 us starts=1 cost=5 size=591 card=3)
2006485 2006485 2006485 UNION-ALL (cr=14370 pr=0 pw=0 time=1889478 us starts=1)
2006485 2006485 2006485 FILTER (cr=14370 pr=0 pw=0 time=888877 us starts=1)
2006485 2006485 2006485 INDEX FAST FULL SCAN I_OBJ2 (cr=14370 pr=0 pw=0 time=425396 us starts=1 cost=2 size=41 card=1)(object id 6000040)
0 0 0 FILTER (cr=0 pr=0 pw=0 time=4 us starts=1)
0 0 0 INDEX RANGE SCAN I_OBJ2 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=3 size=82 card=2)(object id 6000040)
0 0 0 INDEX RANGE SCAN I_DEPENDENCY1 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=0 card=1)(object id 106)
0 0 0 TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=0 pr=0 pw=0 time=0 us starts=0 cost=3 size=34 card=1)
0 118 355 TABLE ACCESS BY INDEX ROWID BATCHED DEPENDENCY$ (cr=125 pr=111 pw=0 time=1369 us starts=1 cost=3 size=34 card=1)
0 118 355 INDEX RANGE SCAN I_DEPENDENCY2 (cr=3 pr=1 pw=0 time=946 us starts=1 cost=2 size=0 card=1)(object id 107)
Yes I gathered the dict stats.
dbms_stats.gather_dictionary_stats(cascade => true, no_invalidate => false, options => 'GATHER', degree => 5);
I also gathered directly on sys tables, but the xplan and performance are bad also :
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname => 'DEPENDENCY$', estimate_percent => dbms_stats.auto_sample_size, cascade => true, no_invalidate => false, options => 'GATHER', degree => 5);
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname => 'OBJ$', estimate_percent => dbms_stats.auto_sample_size, cascade => true, no_invalidate => false, options => 'GATHER', degree => 5);
If I gathered on DEPENDENCY$ and deleted stat on OBJ$, the xplan and performance are very fast. But like we know it's not recommended and I don't want no stats on OBJ$ ! :
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname => 'DEPENDENCY$', estimate_percent => dbms_stats.auto_sample_size, cascade => true, no_invalidate => false, options => 'GATHER', degree => 5);
execute dbms_stats.delete_table_stats(ownname => 'SYS', tabname => 'OBJ$');
It's not my first time also I saw bad performance on low-level recursive queries. Exemple on database 12c I have had to use _fix_control parameter (recommended from Oracle SR).
When I have this king of performance problem with low-level recursive queries, I search with predicate to find articles about this. Here I search the UNION-ALL and it's why I found the _optimizer_push_pred_cost_based in these articles.
SQL with 'VIEW PUSH PREDICATE' in 12.2 Causing Performance Issue (Doc ID 2641737.1)
Bug 29867728 - UNION ALL PUSHED PREDICATE Is Chosen On 19c When UNION-ALL Plan May Have Less Cost (Doc ID 29867728.8)
https://community.oracle.com/mosc/discussion/4081452/optimizer-push-pred-cost-based-and-or-sql-patch
With _optimizer_push_pred_cost_based=false, the xplan and performance are very fast (like it was on database 12c).
SQL Plan Monitoring Details (Plan Hash Value=450768487)
========================================================================
| Id | Operation | Name |
| | | |
========================================================================
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | DEPENDENCY$ |
| 4 | INDEX RANGE SCAN | I_DEPENDENCY2 |
| 5 | VIEW | VW_JF_SET$DDE30DF4 |
| 6 | UNION-ALL PARTITION | |
| 7 | FILTER | |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ |
| 9 | INDEX RANGE SCAN | I_OBJ1 |
| 10 | FILTER | |
| 11 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ |
| 12 | INDEX RANGE SCAN | I_OBJ1 |
========================================================================
SQL_ID EXECUTIONS BGET BGET_BY_EXEC CPU_BY_EXEC ELAPSED_BY_EXEC
------------- ---------- ---------- ------------ ----------- ---------------
091fb1shwqyn8 359 1113 3 .0001 .0001
If it's not possible to sql patch a low-level recursive queries, instead I will modify the job that executes all the 19c dbms_mview.refresh and this job will execute fastly.
alter session set "_optimizer_push_pred_cost_based"=false;
Thanks.

Oh what a depressing sight! That plan is terrible! (But thanks for sharing the details!)
Everything seems wrong in that plan: wrong join order, and wrong cost estimate for that index FFS on I_OBJ2 to begin with (cost=2). This is so much lower than the actual number of logical reads (cr=14370); any idea about why?
Reminds me of these notes from Jonathan Lewis' blog: https://jonathanlewis.wordpress.com/?s=index_ffs+cost, but here the discrepancy seems just enormous.
Regards,

Since you're on 19c you can pull the plan from memory with the full "hint report", and the "Outline Information". The latter might give you some idea of why the optimizer could apply your hint and still come up with a different plan. Remember that an SQL Plan Baseline (if generated by Oracle) tends to be a long and complex list of hints - a single hint (whether hacked into place as an SQL Profile, Patch of Baseline) to disable one feature is by no means guaranteed to give you the same plan in one system as it did in another.
I've just pulled that SQL_ID from memory on my 19.3 system, and the thing that stands out in the Outline Information is the hint: OPT_PARAM('_optim_peek_user_binds' 'false'), which is not something I've set up, so perhaps it's a session setting that is invoked by the dbms_mview.refresh() procedure that results in the optimizer looking at lots more execution plans using completely different arithmetic from what it does in your test.
Another thing you could do to get some clues is to try executing the query from a foreground session to see if you get your desired plan, then invoke the refresh again to force the background execution, then query v$sql_shared_cursor - because if you get a different execution plan you should get a new child cursor - and that might tell you what's difference about the two environments.

set long 20000
select child_number, con_id, reason from V$sql_shared_cursor where sql_id = '091fb1shwqyn8';

Regards
Jonathan Lewis