Thank you, Jonathan
I will try to implement a model with your suggestion, test it and get back
with what I have found.
Best regards
Mohamed Houri
Le jeu. 15 sept. 2022 à 16:48, Jonathan Lewis <jlewisoracle@xxxxxxxxx> a
écrit :
Are you doing anything with SQL Profiles or SQL Patches.
If not you could create a SQL Profile or SQL Patch for this query using a
non-default category name and use a logon trigger for that schema to set
the sqltune_category that category name.
If you're using profiles/patches already you could still take the same
approach, but you'd have to duplicate every single existing
profile/category "into" the same non-default category name.
(I think I've tested this for SQL Profiles some time in the past, I don't
think I've tested it for SQL Patches).
Regards
Jonathan Lewis
On Thu, 15 Sept 2022 at 15:40, Mohamed Houri <mohamed.houri@xxxxxxxxx>
wrote:
Hello
In third-party software, we have a query executed in different schemas.
So, each schema will hard parse its proper execution plan and the
non-sharing reason is
- AUTO CHECK MISMATCH
- Authorization Check failed(4)
https://hourim.wordpress.com/2021/07/28/why-my-execution-plan-has-not-been-shared-part-7/
There is no problem so to speak concerning the parsing but in one of the
schemas, says S1, a table, say T1, has millions of rows while in the other
schemas it is almost always empty. The third-party software fills in this
table in the schema S1 during an overnight job and doesn’t gather
statistics. There are no statistics gathered in this application other than
the automatic job launched by Oracle at 22h00 when the table T1 is empty.
While we are okay with a FULL SCAN of T1 in the other schemas, a FULL
SCAN of T1 in S1 is causing severe performance pain.
We have an index that can be used. But
1) Application team can’t add a hint for the index
2) We can’t fix a SQL Profile or SPM baseline with the index
execution plan in schema S1 because it will be used in the other schemas
We asked the editor to foresee a statistics gathering of table T1
immediately after each insert/delete and to differentiate the sql_id by
schemas (adding the schema name for example or a comment )
In the meantime, I was wondering if there is a way to force an execution
plan for a particular sql_id in S1 schema without it being used in the
other schemas
Thanks
--
Houri Mohamed
Oracle DBA-Developer-Performance & Tuning
Visit My - Blog <http://www.hourim.wordpress.com/>
Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
<https://twitter.com/MohamedHouri>
