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

I wouldn’t have thought there was much of a problem in having the empty schemas use the same index?

That said, it sounds like you just want to gather stats on the table when it has data and lock the statistics (assuming there’s no low/high values to worry about going out of sync). You’re already getting a plan per schema as expected so only the relevant schema is going to pick up on these different statistics.

Thanks,
Andy

On Thu, Sep 15, 2022 at 8:39 AM, Mohamed Houri <mohamed.houri_at_gmail.com> wrote:

> 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