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

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

-- 

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