Re: Simple query opting higher cost path

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