0

We have had a performance problem on a particular APEX screen, ever since we had to perform a Dataguard failover 10 days ago. I'd put it down to general system load until I investigated more deeply today. The Underlying SQL runs fine, running as the same user, via SQL (<0.1 seconds). Via the application, using the same bind variables it takes > 70s. What is really confusing me is the dbms_xplan output is stating

  • rule based optimizer used (consider using cbo)

in the report. I didn't think that was even possible any more? I'll raise an SR if needed, but before I go down that rabbit hole, has anyone seen this before? Any idea where to look? We did manage to lose a couple of parameters on the failover, I thought I'd found them all but maybe not? It's not a global problem though, most things are working fine. Application hasn't changed in ages according to the DEV team.

Any suggestions, gratefully received!

Improve this question
3
  • Please provide the actual call to dbms_xplan that you used. It's important to get the plan that's actually performing poorly and not a different one, so how you call this matters. Also show us any hints in the query (ensure it doesn't have the RULE hint). Also share the output of SELECT value FROM v$parameter WHERE name = 'optimizer_mode'. Also, FYI, if you do use bind variables testing manually cannot be done simply by replacing teh binds with literals - that's a different SQL and may take different plan. You'd have to mock up the SQL with real binds using a small PL/SQL block to test.
    – Paul W
    Commented Oct 25, 2023 at 12:31
  • Thanks, Call was select plan_table_output from table(dbms_xplan.display_cursor('2rf7fvgx9zj26',null,'all')); Where that was definitely the offending SQL ID. Optimizer_mode is all_rows.
    – Carlovski
    Commented Oct 26, 2023 at 8:35
  • Ensure there aren't multiple child cursors. If there are, be sure to specify the correct child number in the call to display_cursor. I suspect that either your APEX program is altering optimizer_mode in its session or including the RULE hint in the query. You shouldn't be seeing the RBO in use otherwise. Also note what I said above about mocking up a test using PL/SQL so you can use real binds for your performance tests.
    – Paul W
    Commented Oct 26, 2023 at 11:47

1 Answer 1

Reset to default
-1

You are hitting a bug, restart your new primary instance and the use of RBO should go away again. This effects any SQL which is parsed within PL/SQL which was called by SQL (which is how APEX SQL works, but also dbms_schedule jobs).

The bug is fixed in later patches (I assume you’re on a very early 19c patch), so be sure to patch up. Until then, include an instance restart inside your failover process.

-edit- If you can’t afford the downtime for an instance restart, then you can try adding a hint via SQL patch, I’ve had success hinting just dynamic_sampling(2) but hinting first_rows_100 or all_rows should work.

exec dbms_output.put_line(dbms_sqldiag.create_sql_patch(sql_id=>'<SQLID>',hint_text=>'dynamic_sampling(2)'));
Improve this answer
5
  • Thanks - any idea on the bug ID? I couldn't find anything when I looked.. We are on 19.8 - so yeah quite a few patch releases behind. Getting downtime for a restart is tricky (It's a hospital...), though we will have some for the switchback we will be doing soon. I can try the patch (Or we can update the app, I've got a mirrored copy I can play with first anyway), or I can try forcing the good plan.
    – Carlovski
    Commented Oct 26, 2023 at 8:34
  • Bug is 30572816 (although could be additional ones). Sounds like you’d be best off getting a hint in someway for now (if it’s APEX then should be relatively trivial but a SQL Patch is a decent last option). I’m not sure what you mean by mirrored copy, but you should be able to replicate the bug by doing a DG switchover so make sure that’s involved in your testing.
    – Andrew Sayer
    Commented Oct 26, 2023 at 17:57
  • I just meant the dev cloned the app so we can have a play with it without impacting anyone (As we can't replicate this in a non-prod environment).
    – Carlovski
    Commented Oct 27, 2023 at 8:19
  • Interesting, the bug explicitly states when optimizer_mode=first_rows, which isn't the case here. And it wasn't a snapshot standby. It does sound like we might have some variation on the bug though. Will try the hint today, and we are planning to fail back over next week which might just fix it too.
    – Carlovski
    Commented Oct 27, 2023 at 8:23
  • The bug note is very specific but it the actual issue is generic and happens when a standby turns into a primary. I raised a case identical to yours and Oracle support linked it with this one.
    – Andrew Sayer
    Commented Oct 27, 2023 at 16:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.