Forcing the usage of a SQL Profile/SPM in one schema

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>



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