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

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

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