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

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_at_gmail.com> 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>
>
>

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