Re: Simple query opting higher cost path

Along with this we are seeing _fix_control is also set 14033181:0., _ignore_desc_in_index set as TRUE , _parallel_syspls_obey_force set as FALSE i.e. non default ones. I want to understand what that fix_control does. Btw are these also related to peoplesoft DB setup only and thus can be safely reverted?

On Wed, Oct 20, 2021 at 2:38 PM Pap <oracle.developer35_at_gmail.com> wrote:

> Thank you Jonathan and Mohamed.
>
> Yes , this database is a non peoplesoft one. So do you mean that these
> underscore parameters were not existing in 11.2 at all and thus it means
> 'skip_scan' and 'gby_hash_aggregation' and 'unnest_subquery' were all
> default TRUE and were not in our control to change them. In 19c they
> introduced and added more control. Is this understanding correct?
>
> Then I think, we can definitely revert the _optimizer_skip_scan_enabled
> and _gby_hash_aggregation_enabled to true i.e. the default. Regarding ,
> '_unnest_subquery' its default seems false in this oracle 19c version. So
> was it like that on 11.2 and if it's recommended to keep it as default here
> i.e. false only?
>
>
>
> On Wed, Oct 20, 2021 at 2:16 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> Since the upgrade from 11.2.0.4 doesn't have these non-default settings
>> maybe this is just a DBA using the settings from another system they had
>> previously upgraded; or maybe it's a case of consolidating systems into a
>> single CDB on the upgrade and setting the CDB parameters that ought only to
>> have been set in a Peoplesoft PDB.
>>
>> Regards
>> Jonathan Lewis
>>
>>
>> On Wed, 20 Oct 2021 at 09:24, Mohamed Houri <mohamed.houri_at_gmail.com>
>> wrote:
>>
>>> Hello
>>>
>>> These 3 parameters are exactly the same as those suggested by PeopleSoft
>>> (PS) to be changed. You are using PeopleSoft? isn't it?
>>>
>>> Anyway, after some unsuccessful tests in PRE-PROD, I reset the
>>> *_optimizer_skip_scan_enabled* parameter to its default value and left
>>> the other two parameters as suggested by PS. But I had to intervene several
>>> times on several queries by forcing the use of the unnest subquery via the
>>> hint *opt_param('-_unnest_subquery' 'true')*
>>>
>>> So I don't think it's a good idea to disable the use of *skip scan
>>> indexes* at all
>>>
>>> Best regards
>>> Mohamed
>>>
>>> Le mer. 20 oct. 2021 à 05:25, Pap <oracle.developer35_at_gmail.com> a
>>> écrit :
>>>
>>>> Thank you Andy.
>>>> We used hints to get rid of this plan change for this query. But we are
>>>> seeing multiple queries keep coming with suboptimal plans. So wanted to
>>>> understand if we can get what all such critical optimization parameters has
>>>> been changed during this upgrade. Is there a way to get those? The
>>>> dba_hist_parameter won't show that change because version 11.2 was not
>>>> having those parameters captured in v&parameter view.
>>>>
>>>> On Wed, 20 Oct 2021, 3:16 am Andy Sayer, <andysayer_at_gmail.com> wrote:
>>>>
>>>>> If the rows don’t exist for the underscore parameters in v$parameter
>>>>> (or using show parameter) then they haven’t been changed (or they don’t
>>>>> exist). You would query the underlying fixed tables directly in order to
>>>>> ensure you see how the hidden parameters are set too.
>>>>>
>>>>> I think your focus should be on finding the human responsible for
>>>>> making these sorts of changes and see what the reasoning was. These are big
>>>>> settings to change back so you might be better off just using query level
>>>>> hints (opt_param) until you have investigated the why.
>>>>>
>>>>> Thanks,
>>>>> Andy
>>>>>
>>>>>
>>>>> On Tue, 19 Oct 2021 at 22:01, Pap <oracle.developer35_at_gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Thank you Andy. I queried gv$parameter and saw below. So as it says
>>>>>> ISDEFAULT column is TRUE for all and ISMODIFIED is SYSTEM_MOD, so that
>>>>>> means the parameter is actually modified manually using the alter system
>>>>>> command. And only two of these were having non default values i.e.
>>>>>> skip_scan and hash_aggregation. So mostly these two are modified manually
>>>>>> and should be reverted if not done by oracle recommendation. But
>>>>>> unnest_subquery seems to have default_value as FALSE only, which looks a
>>>>>> little odd though, as that seems to be frequently used in many
>>>>>> optimizations. And again when i tried querying a 11.2.0.4 database
>>>>>> gv$parameter with name as these underscore parameters, somehow i am getting
>>>>>> zero rows. Why.so?
>>>>>>
>>>>>> NAME VALUE
>>>>>> DEFAULT_VALUE ISDEFAULT ISSES_MODIFIABLE ISSYS_MODIFIABLE ISMODIFIED ISADJUSTED ISDEPRECATED
>>>>>>
>>>>>> _unnest_subquery FALSE FALSE TRUE TRUE IMMEDIATE
>>>>>> SYSTEM_MOD FALSE FALSE
>>>>>>
>>>>>> _optimizer_skip_scan_enabled FALSE TRUE TRUE TRUE
>>>>>> IMMEDIATE SYSTEM_MOD FALSE FALSE
>>>>>>
>>>>>> _gby_hash_aggregation_enabled FALSE TRUE TRUE TRUE
>>>>>> IMMEDIATE SYSTEM_MOD FALSE FALSE
>>>>>>
>>>>>> On Wed, Oct 20, 2021 at 1:46 AM Andy Sayer <andysayer_at_gmail.com>
>>>>>> wrote:
>>>>>>
>>>>>>> Underscore parameters should only be set if you have a reason to set
>>>>>>> them and you have checked what Oracle Support has to say about it.
>>>>>>>
>>>>>>> You can see if the parameter is non-default because it appears when
>>>>>>> you do eg `show parameter optimizer_skip`. It is likely that these have
>>>>>>> been manually set.
>>>>>>>
>>>>>>> That said, _optimizer_skip_scan_enabled being false will very
>>>>>>> obviously prevent index skip scans from being used. If a skip scan is
>>>>>>> required for the query to perform well (given the existing indexes) then
>>>>>>> this parameter is going to prevent that good plan. You can create an index
>>>>>>> which doesn’t include the column being skilled to allow for a regular index
>>>>>>> scan plan.
>>>>>>>
>>>>>>> Thanks,
>>>>>>> Andy
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> On Tue, 19 Oct 2021 at 20:53, Pap <oracle.developer35_at_gmail.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>>> Hello Listers, We have encountered slowness with one of the
>>>>>>>> customer databases with a simple SELECT query. And analyzing the plan and
>>>>>>>> outline of the sql it appeared that while we were on version 11.2.0.4, it
>>>>>>>> was using index skip scan and was faster also the cost was lower but post
>>>>>>>> upgrade to 19.9.0.0.0, there are few underscore parameters seems to be
>>>>>>>> disabled along with one fix control and thus it followed a full scan path.
>>>>>>>> So we want to understand if it's expected/ defaults in 19C? or it must have
>>>>>>>> been altered somehow during upgrade itself and so should be reverted back
>>>>>>>> to true?
>>>>>>>>
>>>>>>>> Below underscore parameters appear to be turned off as shown in the
>>>>>>>> query outline.
>>>>>>>>
>>>>>>>> OPT_PARAM('_unnest_subquery' 'false')
>>>>>>>> OPT_PARAM('_optimizer_skip_scan_enabled' 'false')
>>>>>>>> OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
>>>>>>>>
>>>>>>>>
>>>>>>>> ********* Plan on 19.9.0.0.0 *********************
>>>>>>>> SQL Text
>>>>>>>> ------------------------------
>>>>>>>> SELECT D.COL1, D.CLOB2 FROM TAB1 D WHERE D.ID = :1
>>>>>>>>
>>>>>>>> Global Information
>>>>>>>> ------------------------------
>>>>>>>> Status : DONE (ALL ROWS)
>>>>>>>> Instance ID : 1
>>>>>>>> SQL Execution ID : 16782261
>>>>>>>> Execution Started : 10/19/2021 12:38:18
>>>>>>>> First Refresh Time : 10/19/2021 12:38:22
>>>>>>>> Last Refresh Time : 10/19/2021 12:39:25
>>>>>>>> Duration : 67s
>>>>>>>> Fetch Calls : 1
>>>>>>>>
>>>>>>>> Global Stats
>>>>>>>>
>>>>>>>> ===============================================================================
>>>>>>>> | Elapsed | Cpu | IO | Application | Fetch | Buffer |
>>>>>>>> Read | Read |
>>>>>>>> | Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets |
>>>>>>>> Reqs | Bytes |
>>>>>>>>
>>>>>>>> ===============================================================================
>>>>>>>> | 72 | 18 | 53 | 0.00 | 1 | 11M |
>>>>>>>> 87343 | 85GB |
>>>>>>>>
>>>>>>>> ===============================================================================
>>>>>>>>
>>>>>>>> SQL Plan Monitoring Details (Plan Hash Value=1148202243)
>>>>>>>>
>>>>>>>> ==========================================================================================================================================================================
>>>>>>>> | Id | Operation | Name |
>>>>>>>> Rows | Cost | Time | Start | Execs | Rows | Read | Read |
>>>>>>>> Activity | Activity Detail |
>>>>>>>> | | | |
>>>>>>>> (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes |
>>>>>>>> (%) | (# samples) |
>>>>>>>>
>>>>>>>> ==========================================================================================================================================================================
>>>>>>>> | 0 | SELECT STATEMENT | |
>>>>>>>> | | 64 | +4 | 1 | 1 | | |
>>>>>>>> | |
>>>>>>>> | 1 | TABLE ACCESS STORAGE FULL | TAB1 |
>>>>>>>> 1 | 2M | 68 | +1 | 1 | 1 | 87343 | 85GB |
>>>>>>>> 100.00 | Cpu (22) |
>>>>>>>> | | | |
>>>>>>>> | | | | | | | |
>>>>>>>> | direct path read (46) |
>>>>>>>>
>>>>>>>> ==========================================================================================================================================================================
>>>>>>>>
>>>>>>>> Outline Data
>>>>>>>> -------------
>>>>>>>> /*+
>>>>>>>> BEGIN_OUTLINE_DATA
>>>>>>>> IGNORE_OPTIM_EMBEDDED_HINTS
>>>>>>>> OPTIMIZER_FEATURES_ENABLE('19.1.0')
>>>>>>>> DB_VERSION('19.1.0')
>>>>>>>> OPT_PARAM('_unnest_subquery' 'false')
>>>>>>>> OPT_PARAM('_optimizer_skip_scan_enabled' 'false')
>>>>>>>> OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
>>>>>>>> OPT_PARAM('_fix_control' '14033181:0')
>>>>>>>> ALL_ROWS
>>>>>>>> OUTLINE_LEAF(_at_"SEL$1")
>>>>>>>> FULL(_at_"SEL$1" "D"_at_"SEL$1")
>>>>>>>> END_OUTLINE_DATA
>>>>>>>> */
>>>>>>>>
>>>>>>>> ********* Plan on 11.2.0.4 *********************
>>>>>>>>
>>>>>>>> SQL Monitoring Report
>>>>>>>>
>>>>>>>> SQL Text
>>>>>>>> ------------------------------
>>>>>>>> SELECT /*+optimizer_features_enable('11.2.0.4') */ D.COL1, D.CLOB2
>>>>>>>> FROM TAB1 D WHERE D.ID = :b1
>>>>>>>>
>>>>>>>> Global Information
>>>>>>>> ------------------------------
>>>>>>>> Status : DONE (ALL ROWS)
>>>>>>>> Instance ID : 1
>>>>>>>> SQL ID : 09sa822k7qsvn
>>>>>>>> SQL Execution ID : 16777216
>>>>>>>> Execution Started : 10/19/2021 15:13:56
>>>>>>>> First Refresh Time : 10/19/2021 15:13:56
>>>>>>>> Last Refresh Time : 10/19/2021 15:13:56
>>>>>>>> Duration : .032143s
>>>>>>>> Module/Action : SQL*Plus/-
>>>>>>>> Fetch Calls : 2
>>>>>>>>
>>>>>>>> Global Stats
>>>>>>>>
>>>>>>>> ===========================================================================
>>>>>>>> | Elapsed | Cpu | IO | Cluster | Fetch | Buffer | Read |
>>>>>>>> Read |
>>>>>>>> | Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs |
>>>>>>>> Bytes |
>>>>>>>>
>>>>>>>> ===========================================================================
>>>>>>>> | 0.03 | 0.01 | 0.02 | 0.00 | 2 | 54 | 13 |
>>>>>>>> 104KB |
>>>>>>>>
>>>>>>>> ===========================================================================
>>>>>>>>
>>>>>>>> SQL Plan Monitoring Details (Plan Hash Value=191420461)
>>>>>>>>
>>>>>>>> ========================================================================================================================================================================
>>>>>>>> | Id | Operation | Name |
>>>>>>>> Rows | Cost | Time | Start | Execs | Rows | Read | Read |
>>>>>>>> Activity | Activity Detail |
>>>>>>>> | | | |
>>>>>>>> (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes |
>>>>>>>> (%) | (# samples) |
>>>>>>>>
>>>>>>>> ========================================================================================================================================================================
>>>>>>>> | 0 | SELECT STATEMENT | |
>>>>>>>> | | 1 | +0 | 1 | 1 | | |
>>>>>>>> | |
>>>>>>>> | 1 | TABLE ACCESS BY INDEX ROWID | TAB1 |
>>>>>>>> 1 | 6 | 1 | +0 | 1 | 1 | 1 | 8192 |
>>>>>>>> | |
>>>>>>>> | 2 | INDEX SKIP SCAN | TAB1_PK |
>>>>>>>> 1 | 5 | 1 | +0 | 1 | 1 | 12 | 98304 |
>>>>>>>> | |
>>>>>>>>
>>>>>>>> ========================================================================================================================================================================
>>>>>>>>
>>>>>>>> Query Block Name / Object Alias (identified by operation id):
>>>>>>>> -------------------------------------------------------------
>>>>>>>>
>>>>>>>> 1 - SEL$1 / D_at_SEL$1
>>>>>>>> 2 - SEL$1 / D_at_SEL$1
>>>>>>>>
>>>>>>>> Outline Data
>>>>>>>> -------------
>>>>>>>> /*+
>>>>>>>> BEGIN_OUTLINE_DATA
>>>>>>>> IGNORE_OPTIM_EMBEDDED_HINTS
>>>>>>>> OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
>>>>>>>> DB_VERSION('19.1.0')
>>>>>>>> ALL_ROWS
>>>>>>>> OUTLINE_LEAF(_at_"SEL$1")
>>>>>>>> INDEX_SS(_at_"SEL$1" "D"_at_"SEL$1" ("TAB1"."COL3" "TAB1"."ID"))
>>>>>>>> END_OUTLINE_DATA
>>>>>>>> */
>>>>>>>>
>>>>>>>> Predicate Information (identified by operation id):
>>>>>>>> ---------------------------------------------------
>>>>>>>> 2 - access("D"."ID"=:B1)
>>>>>>>> filter("D"."ID"=:B1)
>>>>>>>>
>>>>>>>> Column Projection Information (identified by operation id):
>>>>>>>> -----------------------------------------------------------
>>>>>>>> 1 - "D"."COL1"[VARCHAR2,19], "D"."CLOB2"[LOB,4000]
>>>>>>>> 2 - "D".ROWID[ROWID,10]
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>
>>> --
>>>
>>> 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