Re: Simple query opting higher cost path

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