Re: Simple query opting higher cost path

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

--
http://www.freelists.org/webpage/oracle-l