Hello Mohamed,
Maybe another option for you can be to translate the query you want a
special treatment for (thus changing the sql_id):
https://connor-mcdonald.com/2016/10/20/translating-sql-a-migration-tool/
THanks.
În joi, 15 sept. 2022 la 17:40, Mohamed Houri <mohamed.houri@xxxxxxxxx> a
scris:
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>
