Re: Simple query opting higher cost path

Thank you so much for the guidance here. Understood how the is_default, default_value may not be fully reliable. But yes, in our case as the sql outline itself noted the hint OPT_PARAM('_unnest_subquery' 'false'), so i believe it's truly been set as false(which is again may be set/copied by someone wrongly).

On Wed, Oct 20, 2021 at 11:47 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> The fix_control has the description: "correct ndv for non-popular values
> in join cardinality comp" and has been in existence (and enabled / true /
> on / 1) since at least 12.1.0.2.
>
> Guideline - it doesn't matter what you manage to report by querying
> v$parameter / gv$parameter, if you see an underscore parameter in either of
> them then it's NOT the default value - it's more likely that the report
> code has an error or the internal structure is wrong. In this case I've
> just run up an instance of 19.11.0.0 and you seem to have been unlucky with
> the internals.
>
> For _unnest_subquery on my instance startup, the system value is TRUE, the
> session value is TRUE, is_default reports TRUE - but default_value reports
> FALSE which is clearly incorrect. So you seem to have been unlucky in
> finding a defect in the internal structure that has confused the issue. In
> fact I've just checked and there seem to be about 50 true/false parameters
> in my instance where the values for is_default, system value and default
> value are not self-consistent.
>
> The three parameters do exist in 11.2.0.4, but they don't appear in
> v$parameter because they are underscore parameters and their setting of
> is_default is TRUE.
>
> You need to find out why someone has been messing about with hidden
> parameters, but if a query against gv$parameter in your 11.2.0.4 system
> reports NO underscore parameters then a query against gv$parameter in your
> upgraded (19.9.0.0) system should also show NO underscore parameters.
>
> Regards
> Jonathan Lewis
>
>
>
>
>
>
>
> On Wed, 20 Oct 2021 at 10:20, Pap <oracle.developer35_at_gmail.com> wrote:
>
>> 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