Re: Simple query opting higher cost path

Pap,

I never said that these parameters did not exist before 11.2. On the contrary, they have existed for a long time. All I wanted to say is that you should not prevent the use of *index skip scan path*. Indeed, the last time I tried to follow the PS recommendations, namely, by setting *_optimizer_skip_scan_enabled* to FALSE, I was quickly disappointed since my queries suffered from several slowness problems. The slowness disappeared immediately when I set this parameter back to its default value TRUE. So, go ahead and set this parameter to TRUE unless you have solid evidence to the contrary.

As per regards to the *_unnest_subquery* parameter it all depends on you tests. We asked ourselves this question in the middle of our PS validation tests. Should we set it to TRUE and intervene here and there to set it to FALSE or vice versa.

I can't advise you. In one of my PS projects, I have preferred to use the default value of the *_unnest_subquery* parameter and intervened here and there locally to set it to FALSE via the opt_param hint when it was necessary

Best regards

Mohamed Houri

Le mer. 20 oct. 2021 à 11:20, Pap <oracle.developer35_at_gmail.com> a écrit :

> 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>
>>>>
>>>>

-- 

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