Re: Simple query opting higher cost path

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