Re: SQL Performance tuning advice

Hello Paul,

> The SQL is application generated (peoplesoft) and has a number literals which change every time it is run, so SQL Plan management can’t be used.

You can use SQL profiles or SQL patches with force matching - I would recommend the latter.

> Is there a way to find out what differences in SQL plan directives exist between fast and slow environments?

Just create a good old CBO trace (10053 trace name context forever, level 1) in both environments and have a look for lines like "SPD: Directive valid: dirid = 13237011096450186301, state = 1, flags = 1, loc = 1 {EC(91171)[1, 2]}". You can compare the SPDs easily and figure out which one is missing and what this SPD is doing. A translation of the values can be found in my old blog post here: https://community.sap.com/t5/technology-blogs-by-members/oracle-db-optimizer-part-xii-revealing-sql-plan-directive-details-for/ba-p/13162676

> It’s really irritating that I can’t reproduce the issue in a non-production database, so I can’t try things out.

Please be aware that your non-production database has to be exactly the same as applied transformations can influence which SPDs are/can be used and also impact the SPD dynamic sampling results.

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: www.soocs.de
Twitter: _at_OracleSK

> Paul Houghton <dmarc-noreply_at_freelists.org> hat am 27.06.2024 16:18 CEST geschrieben:
>
>
> We have a situation where a group of similar SQLs have changed plan. I can compare with production (where it slowed down) and non-production where it is still fast.
>
> The SQL is application generated (peoplesoft) and has a number literals which change every time it is run, so SQL Plan management can’t be used. Are there any options for controlling the execution plans of parts of SQL statements? SQL Plan Directives seem like the solution here. Apart from seeing how many were used in a plan there doesn’t seem to be any way to find out what they are or what impact they had. I note the non-production (faster) plan (from dbms_xplan) says 19 baselines were used, where production (slower) says there were 18, so I wonder if one has disappeared somehow (though I thought this wasn’t supposed to happen). Is there a way to find out what differences in SQL plan directives exist between fast and slow environments? This is complicated further as we are hitting bug 29937655 meaning we have millions of directives, and setting _fix_control='29937655:ON' doesn’t seem to have helped us. I took a copy of production to try to reproduce the slow plan in a test database, but the fast plan is selected.
>
> We get issues with this area periodically, especially during upgrades, and I tend to spend a long time looking at explain plans without getting very far, so I would appreciate pointers to a good approach. I have identified one part of the plan that is slow, but I am struggling to think how to tell the database to use the faster plan when the SQL keeps changing. Using the SQL from Oracle Base to look at the SQL Plan Directives returns over 300,000 rows, and it is very slow, so it’s not useful to work out what was actually used.
>
> I will take a look at the columns being joined and see if I can add some extended stats to get better cardinality. It’s really irritating that I can’t reproduce the issue in a non-production database, so I can’t try things out.
>
> As I say, if anyone has pointers as to an approach I could take I would be very grateful to hear them!
>
> Database is version 19.23

--
http://www.freelists.org/webpage/oracle-l